From: Ronan Dunklau <ronan.dunklau@dalibo.com>
To: Jim Nasby <Jim.Nasby@bluetreble.com>
Cc: 'pgsql-hackers' <pgsql-hackers@postgresql.org>
Subject: Re: pg_dump  / copy bugs with "big lines" ?
Date: Tue, 31 Mar 2015 10:46:18 +0200
Message-ID: <7521105.6Ff26iknIn@ronan.dunklau.fr>
User-Agent: KMail/4.14.3 (Linux/3.19.1-gentoo; KDE/4.14.3; x86_64; ; )
In-Reply-To: <5519E025.3030704@BlueTreble.com>
References: <1836813.YmyOrS99PX@ronan.dunklau.fr>
 <5519E025.3030704@BlueTreble.com>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart1915225.DrpaYbJJR7";
 micalg="pgp-sha256"; protocol="application/pgp-signature"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


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

Le lundi 30 mars 2015 18:45:41 Jim Nasby a =E9crit :
> On 3/30/15 5:46 AM, Ronan Dunklau wrote:
> > Hello hackers,
> >=20
> > I've tried my luck on pgsql-bugs before, with no success, so I repo=
rt
> > these
> > problem here.
> >=20
> > The documentation mentions the following limits for sizes:
> >=20
> > Maximum Field Size      1 GB
> > Maximum Row Size        1.6 TB
> >=20
> > However, it seems like rows bigger than 1GB can't be COPYed out:
> >=20
> > 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
> >=20
> > Then, trying to dump or copy that results in the following error:
> >=20
> > ro=3D# COPY test_text TO '/tmp/test';
> > ERROR:  out of memory
> > D=C9TAIL : Cannot enlarge string buffer containing 536870913 bytes =
by
> > 536870912 more bytes.
> >=20
> > In fact, the same thing happens when using a simple SELECT:
> >=20
> > ro=3D# select * from test_text ;
> > ERROR:  out of memory
> > D=C9TAIL : Cannot enlarge string buffer containing 536870922 bytes =
by
> > 536870912 more bytes.
> >=20
> > In the case of COPY, the server uses a StringInfo to output the row=
. The
> > problem is, a StringInfo is capped to MAX_ALLOC_SIZE (1GB - 1), but=
 a row
> > should be able to hold much more than that.
>=20
> Yeah, shoving a whole row into one StringInfo is ultimately going to
> limit a row to 1G, which is a far cry from what the docs claim. There=
's
> also going to be problems with FE/BE communications, because things l=
ike
> pq_sendbyte all use StringInfo as a buffer too. So while Postgres can=

> store a 1.6TB row, you're going to find a bunch of stuff that doesn't=

> work past around 1GB.
>=20
> > So, is this a bug ? Or is there a caveat I would have missed in the=

> > documentation ?
>=20
> I suppose that really depends on your point of view. The real questio=
n
> is whether we think it's worth fixing, or a good idea to change the
> behavior of StringInfo.
>=20

> StringInfo uses int's to store length, so it could possibly be change=
d,
> but then you'd just error out due to MaxAllocSize.
>=20
> Now perhaps those could both be relaxed, but certainly not to the ext=
ent
> that you can shove an entire 1.6TB row into an output buffer.

Another way to look at it would be to work in small chunks. For the fir=
st test=20
case (rows bigger than 1GB), maybe the copy command could be rewritten =
to work=20
in chunks, flushing the output more often if needed.

For the conversion related issues, I don't really see any other solutio=
n than=20
extending StrinigInfo to allow for more than 1GB of data. On the other =
hand,=20
those one can easily be circumvented by using a COPY ... WITH binary.

>=20
> The other issue is that there's a LOT of places in code that blindly
> copy detoasted data around, so while we technically support 1GB toast=
ed
> values you're probably going to be quite unhappy with performance. I'=
m
> actually surprised you haven't already seen this with 500MB objects.
>=20
> So long story short, I'm not sure how worthwhile it would be to try a=
nd
> fix this. We probably should improve the docs though.
>=20

I think that having data that can't be output by pg_dump is quite surpr=
ising,=20
and if this is not fixable, I agree that it should clearly be documente=
d.

> Have you looked at using large objects for what you're doing? (Note t=
hat
> those have their own set of challenges and limitations.)

Yes I do. This particular customer of ours did not mind the performance=
=20
penalty of using bytea objects as long as it was convenient to use.=20

>=20
> > We also hit a second issue, this time related to bytea encoding.
>=20
> There's probably several other places this type of thing could be a
> problem. I'm thinking of conversions in particular.

Yes, thats what the two other test cases I mentioned are about: any con=
version=20
leadng to a size greater than 1GB results in an error, even implicit=20=

conversions like doubling antislashes in the output.

=2D-=20
Ronan Dunklau
http://dalibo.com - http://dalibo.org
--nextPart1915225.DrpaYbJJR7
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

iQEcBAABCAAGBQJVGl7eAAoJEHSCNJt9TqXn6yQH+wetDTvrhyrWuTAdE5PwkamS
AvA9VMrRgGFCFiPCq8aw3QbDob1wM0tE7JdZfofa9Um8Na8FF7y3IxixA/Nh/BvC
QGGK3fs1e8OPY35dZUeaqkVkNmkwRTeZ8Ou/3kj0b+f3R5Kr6uLpaYYdr/JNS0qy
oud2PkpFZ0fzL+Vo2d48yZOaYnS6ogAW+OcUHCJBAKpcBZWLFqFIodm+y4TlLSAT
7oU7e51it8g1d82YeB9C5Gr5EkDC2Gtq4YrMb9dVCS9pFRj5NIGiy8WwxgSvXoac
L2qryTo5eOCgmJsiOONKp8VNjhMwT8k491AX1vjbxilaH6FlcTVW21LuDz+ylhM=
=PPc9
-----END PGP SIGNATURE-----

--nextPart1915225.DrpaYbJJR7--