hi @dhananjayjejur, thanks for response
the server with more memory:
total used free shared buff/cache available Mem: 64265 1546 401 16767 62317 45404 Swap: 4095 15 4080
show shared_buffers
16GB
show work_mem
64MB
show maintenance_work_mem
800MB
when i try to load video from python
#load_video.py import psycopg2 conn_string = "host='localhost' dbname='testutf8' user='enterprisedb' password='rootroot' port=5444" conn = psycopg2.connect(conn_string) mi_video=open('/tmp/a2','rb').read() cursor = conn.cursor() ###query = "INSERT INTO public.videos(id,dato) VALUES (%s,%s);" % (2, psycopg2.Binary(mi_video)) cursor.execute("INSERT INTO public.videos(id,dato) VALUES (%s,%s);", (2, psycopg2.Binary(mi_video))) conn.commit() ###calling the script python load_video.py Traceback (most recent call last): File "load_video.py", line 11, in <module> cursor.execute("INSERT INTO public.videos(id,dato) VALUES (%s,%s);", (2, psycopg2.Binary(mi_video))) psycopg2.OperationalError: out of memory DETAIL: Cannot enlarge string buffer containing 0 bytes by 1180388329 more bytes.
and when i try to load/unload with lo_import/lo_export
i got OK result
bytea_import and lo_export_bfile are described in previous posts
insert into videos select 2, bytea_import ('/tmp/a2')
select public.lo_export_bfile (dato, '/tmp/video2') from videos where id=2
when i try to query :
select * from videos where id =2 ;
ERROR: invalid memory alloc request size 1180388273
whe i try to select other video of smaller size, works well
can you reproduce it?
Regards
PD: i can share the video if you need it
Hi astolongo,
It shows out of memory error here.
psycopg2.OperationalError: out of memory
It appears to be insufficient amount of free memory on the server to proceed data.
Thanks,
Ninad
hi @nshah, @dhananjayjejur, @Tushar-Takate, @chaitalirs,
Will be something related with this report:
https://www.postgresql.org/message-id/flat/15503-0ea185f3f6ad9b99%40postgresql.org
regards!!!
Hi astolongo,
We are seeing "out of memory" error here. out of memory only appears when Postgres is incapable to acquire required amount of memory. Hence, It doesn't appear to be scope of mentioned bug here.
Thanks,
Ninad
hi @nshah, you see "out of memory" from python script
but in EDB Postgres, when i try to do SELECT , i got the same error report
here
https://www.postgresql.org/message-id/flat/15503-0ea185f3f6ad9b99%40postgresql.org
select * from videos where id =2 ; ERROR: invalid memory alloc request size 1180388273
Hi astolongo,
Thank you for your response. Is this complete error text you shared with us? Do you receive any out of memory error in OS messages files(equivalent to /var/log/messages in linux)?
Thanks,
Ninad
hi @nshah @dhananjayjejur @Tushar-Takate @chaitalirs
i got the same error report
here
https://www.postgresql.org/message-id/flat/15503-0ea185f3f6ad9b99%40postgresql.org
Here are the video with my problem with all steps and logs , you can download from drive:
https://drive.google.com/open?id=1p3bKtJ_KoE6Tg24DisJuzBOSrJY4NXcx
please try to reproduce
Thanks
regards
PD: If have problem with video or need more information, let know please
Hi @asotolongo,
Thank you for for sharing detail about your issue with video. This would be more helpful for analysis. We will look into issue and get back to you.
Thanks,
Ninad Shah
Hi @asotolongo,
The root cause for the memory issue you are facing is that you are selecting the output of a bytea column in text mode, it requires more memory than the size of the underlying datum.
The limit for such memory allocations in postgres is 1GB. The allocation needed here, per error message, is at least 1480811149 bytes, i.e. 1.4GB.
We do not recommend postgres as a video file store, if you absolutely have to store, it works.
However, you wouldn’t want to access it via psql using SELECT in text format.
Also, could you please let us know what is your use case with SELECT * FROM videos WHERE id = 3?
Please let us know in case of any issues/queries.
Regards,
Sudhir
Hi @asotolongo,
Hope you are doing great.
Kindly let us know if you are satisfied with the solution provided in the last communication.
Warm Regards,
Dhananjay