the error continues even making a backup
the output of the query is:
PD: here another reports with this https://www.postgresql.org/message-id/flat/AM4PR1001MB1284080FE140548F158BFE779CDA0%40AM4PR1001MB128...
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 :
Hope this helps.
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;
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
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.
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)
thanks and regards
If solution satifsfies your requirement then can you please accept the solution?