From: Ronan Dunklau <ronan.dunklau@dalibo.com>
To: 'pgsql-hackers' <pgsql-hackers@postgresql.org>
Subject: pg_dump  / copy bugs with "big lines" ?
Date: Mon, 30 Mar 2015 12:46:47 +0200
Message-ID: <1836813.YmyOrS99PX@ronan.dunklau.fr>
User-Agent: KMail/4.14.3 (Linux/3.19.1-gentoo; KDE/4.14.3; x86_64; ; )
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart5255355.XiiSzW2eZu";
 micalg="pgp-sha256"; protocol="application/pgp-signature"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


--nextPart5255355.XiiSzW2eZu
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="iso-8859-1"

Hello hackers,

I've tried my luck on pgsql-bugs before, with no success, so I report t=
hese=20
problem here.

The documentation mentions the following limits for sizes:

Maximum Field Size      1 GB
Maximum Row Size        1.6 TB

However, it seems like rows bigger than 1GB can't be COPYed out:

ro=3D# create table test_text (c1 text, c2 text);
CREATE TABLE
ro=3D# insert into test_text (c1) VALUES (repeat('a', 536870912));
INSERT 0 1
ro=3D# update test_text set c2 =3D c1;
UPDATE 1

Then, trying to dump or copy that results in the following error:

ro=3D# COPY test_text TO '/tmp/test';
ERROR:  out of memory
D=C9TAIL : Cannot enlarge string buffer containing 536870913 bytes by 5=
36870912=20
more bytes.

In fact, the same thing happens when using a simple SELECT:

ro=3D# select * from test_text ;
ERROR:  out of memory
D=C9TAIL : Cannot enlarge string buffer containing 536870922 bytes by 5=
36870912=20
more bytes.

In the case of COPY, the server uses a StringInfo to output the row. Th=
e=20
problem is, a StringInfo is capped to MAX_ALLOC_SIZE (1GB - 1), but a r=
ow=20
should be able to hold much more than that.

So, is this a bug ? Or is there a caveat I would have missed in the=20
documentation ?

We also hit a second issue, this time related to bytea encoding.

This test case is a bit more complicated, since I had to use an externa=
l=20
(client) program to insert my data. It involves inserting a string that=
 fit=20
into 1GB when encoded in escape format, but is larger than that in hex,=
 and=20
another string which fits in 1GB using the hex format, but is larger th=
an that=20
in escape:

from=20psycopg2 import connect
from=20io import BytesIO

conn =3D connect(dbname=3D"ro")
cur =3D conn.cursor()
fullcontent =3D BytesIO()

# Write a binary string that weight less
# than 1 GB when escape encoded, but more than
# that if hex encoded
for i in range(200):
    fullcontent.write(b"aaa" * 1000000)
fullcontent.seek(0)
cur.copy_from(fullcontent, "test_bytea")

fullcontent.seek(0)
fullcontent.truncate()

# Write another binary string that weight
# less than 1GB when hex encoded, but more than
# that if escape encoded
cur.execute("SET bytea_output =3D 'hex'")
fullcontent.write(b"\\\\x")
for i in range(300):
    fullcontent.write(b"00" * 1000000)
fullcontent.seek(0)
cur.copy_from(fullcontent, "test_bytea")

cur.execute("COMMIT;")
cur.close()

I couldn't find an invocation of pg_dump which would allow me to dump b=
oth=20
lines:

ro@ronan_laptop /tmp % PGOPTIONS=3D"-c bytea_output=3Descape" pg_dump  =
=2DFc  >=20
/dev/null
pg_dump: Dumping the contents of table "test_bytea" failed: PQgetResult=
()=20
failed.
pg_dump: Error message from server: ERROR:  invalid memory alloc reques=
t size=20
1200000001
pg_dump: The command was: COPY public.test_bytea (c1) TO stdout;
ro@ronan_laptop /tmp % PGOPTIONS=3D"-c bytea_output=3Dhex" pg_dump  -Fc=
  >=20
/dev/null
pg_dump: Dumping the contents of table "test_bytea" failed: PQgetResult=
()=20
failed.
pg_dump: Error message from server: ERROR:  invalid memory alloc reques=
t size=20
1200000003
pg_dump: The command was: COPY public.test_bytea (c1) TO stdout;


Using a COPY with binary format works:

ro=3D# COPY test_bytea TO '/tmp/test' WITH BINARY;


There seems to be a third issue, with regards to escape encoding: the=20=

backslash character is escaped, by adding another backslash. This means=
 that a=20
field which size is less than 1GB using the escape  sequence will not b=
e able=20
to be output once the backslash are escaped.

For example, lets consider a string consisting of 300000000 '\' charact=
ers:


ro=3D# select length(c1) from test_bytea;
  length  =20
=2D----------
 300000000
(1 ligne)

ro=3D# select length(encode(c1, 'escape')) from test_bytea ;
  length  =20
=2D----------
 600000000
(1 ligne)

ro=3D# set bytea_output to escape;
SET
ro=3D# copy test_bytea to '/tmp/test.csv' ;=20

ERROR:  out of memory
D=C9TAIL : Cannot enlarge string buffer containing 1073741822 bytes by =
1 more=20
bytes.

I think pg_dump should not error out on any data which was valid upon=20=

insertion. It seems the fix would be non-trivial, since StringInfo stru=
ctures=20
are relying on a limit of MaxAllocSize. Or am I missing something ?

Thank you.

=20
=2D-=20
Ronan Dunklau
http://dalibo.com - http://dalibo.org
--nextPart5255355.XiiSzW2eZu
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: This is a digitally signed message part.
Content-Transfer-Encoding: 7Bit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQEcBAABCAAGBQJVGSm6AAoJEHSCNJt9TqXnE0IIAKuBUI7rg7NEERjpKvDjlUMU
/mSGfqOXSe+YE81nb1qAxgNoc87m+FCZ9+TdY0Ak1/1G2vQoQU4kxhFN8n8jnIwO
n9VSmbJNVaTm3jSUK7U9UzT5JZCm42r6z/I0ILKkXLdwmz8LGaY/uQFmt129gRCu
mHbRgqDoM99zRxDpuZYHq8kLyg8HibU9w5FUXZrtW266bieafFgRo2ckiquRo9hK
AehkN40zXIKnZiR2Q8bDg8XScoVClCHmWtLx+S9tSxvYh9vWVgtUdXGhcM27eZg1
Y9Hw9EkiRfJvzQF5CYN/0V8QbfT+V2TgtDPwRuOuxvRLljnzJ+dlmHYw2uvEH7A=
=EEP3
-----END PGP SIGNATURE-----

--nextPart5255355.XiiSzW2eZu--