cancel
Showing results for 
Search instead for 
Did you mean: 

problems with writing / reading a data bytea

SOLVED
Silver Adventurer

Re: problems with writing / reading a data bytea

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

 

EDB Team Member

Re: problems with writing / reading a data bytea

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

Silver Adventurer

Re: problems with writing / reading a data bytea

Tags (1)
EDB Team Member

Re: problems with writing / reading a data bytea

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

Silver Adventurer

Re: problems with writing / reading a data bytea

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

 

 

EDB Team Member

Re: problems with writing / reading a data bytea

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

Silver Adventurer

Re: problems with writing / reading a data bytea

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

EDB Team Member

Re: problems with writing / reading a data bytea

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

Highlighted
EDB Team Member

Re: problems with writing / reading a data bytea

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

EDB Team Member

Re: problems with writing / reading a data bytea

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