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@slonkar

 

the error continues even making a backup

dump_error.png

 

the output of the query is:

 

select_OK143.png

 

 

Regards 

 

 

PD: here another reports with this https://www.postgresql.org/message-id/flat/AM4PR1001MB1284080FE140548F158BFE779CDA0%40AM4PR1001MB128...

EDB Team Member

Re: problems with writing / reading a data bytea

Hi @asotolongo,

pg_dump will try to export the data in the flat file instead of an original video file format. That is the reason you are getting "ERROR: invalid memory alloc request size" error.

 

Hence, we request you to use lo_export.

Kindly refer few lines from PostgreSQL document :

\lo_export loid filename
Reads the large object with OID loid from the database and writes it to filename. Note that this is subtly different from the server function lo_export, which acts with the permissions of the user that the database server runs as and on the server's file system.

Also kindly refer below link for the document :

https://www.postgresql.org/docs/9.6/app-psql.html

 

 

Hope this helps.

 

 

Silver Adventurer

Re: problems with writing / reading a data bytea

hi @dhananjayjejur

 

in previus post i said , the only way to export this data is whit lo_export

 

i use this code: and function fine:

 

CREATE OR REPLACE FUNCTION public.lo_export_bfile (
    data bytea,
    filename text)
  RETURNS integer AS
$ BODY $
DECLARE
 l_oid oid;
  lfd integer;
  lsize integer;
  msg text;
  msg_detail text;
  msg_err text;
  INV_WRITE int: = 131072;
BEGIN
 l_oid: = lo_create (0);
  lfd: = lo_open (l_oid, INV_WRITE); - writing mode
  lsize: = lowrite (lfd, data);
  PERFORM lo_close (lfd);
  PERFORM lo_export (l_oid, filename);
  PERFORM lo_unlink (l_oid);
  return lsize;
  EXCEPTION
     WHEN OTHERS THEN
      GET STACKED DIAGNOSTICS msg = message_text, msg_detail = pg_exception_detail, msg_err = returned_sqlstate;
      RAISE EXCEPTION 'Error:%,%,%', msg, msg_detail, msg_err;
END;
$ BODY $
  LANGUAGE plpgsql VOLATILE
  COST 100;

 

EDB Team Member

Re: problems with writing / reading a data bytea

Hi asotolongo,

 

I hope lo_export fulfills your requirement. 

 

Thank you for your update.

Silver Adventurer

Re: problems with writing / reading a data bytea

hi @dhananjayjejur@ from  begining, lo_import and lo_export work for me, i was asking for the select option and de dump option

 

thanks to all

Highlighted
EDB Team Member

Re: problems with writing / reading a data bytea

Hi asotolongo,

 

We have consulted regarding this issue with our Engineering team and following are their inputs.

 

1. The root cause for the memory issue you are facing is that you are selecting the output of a bytea column in text mode(i.e. either for SELECT or pg_dump), it requires more memory than the size of the underlying datum.  

2. The limit for such memory allocations in Postgres is 1GB. 

 

As when you try to store a video file in Postgres database lo_import and lo_export will perfectly fine. However, whenever you try to retrieve the data via SELECT or pg_dump it will give and memory error(except small video files).

 

As you have already mentioned in the previous comments that you are storing the video files more than 500MB. Hence, you will always get the following error " ERROR: invalid memory alloc request size " while SELECT and pg_dump.

 

Also as you re-iterated in the previous comment that lo_import and lo_export are working fine in your case. Hence, we are de-escalated this case from our internal Engineering team.

 

Hope this clears your doubts.

 

Silver Adventurer

Re: problems with writing / reading a data bytea

hi @dhananjayjejur, thanks for all,
i was thinking that was a bug, but if you say it is the normal behaivor, because it requires more memory than the size of the underlying datum, okok,
i continue working with lo_import and lo_export,
no more select and pg_dump with binary data (with more than 500 MB) Smiley Sad


thanks and regards

EDB Team Member

Re: problems with writing / reading a data bytea

Hi @asotolongo

 

If solution satifsfies your requirement then can you please accept the solution?

 

Thanks,

Ninad

Silver Adventurer

Re: problems with writing / reading a data bytea

hi @nshah, i already accept the answer

 

regards