1

I'm using PostgreSQL 9.5 and trying to import XML data file to database. To load the XML I use this function from here

CREATE OR REPLACE FUNCTION bytea_import(p_path text, p_result out bytea) as
$$
DECLARE 
  l_oid oid;
BEGIN 
  SELECT lo_import(p_path) INTO l_oid;
  SELECT lo_get(l_oid) INTO p_result;
  PERFORM lo_unlink(l_oid);
END;
$$
LANGUAGE plpgsql;

I successfully load data from a small XML file, but when I try a 4GB XML file, it returns error:

SQL Error [54000]: ERROR: large object read request is too large  
Where: SQL statement "SELECT lo_get(l_oid)" PL/pgSQL function
bytea_import(text) line 6 at SQL statement.

It seems lo_get cannot read/extract very large data. It's said that since version 9.4, PostgreSQL can handle up to 4TB, so how can I import very large XML data file to PostgreSQL 9.5?

Thanks in advance

BonZ
  • 11
  • 1
  • 2

1 Answers1

1

The lo_import is successful, creating a 4GB large object.

But the hard limit for the bytea datatype is 1GB, so these contents are not transferable into a bytea.

Daniel Vérité
  • 31,182
  • 3
  • 72
  • 80