Re: large INSERT leads to "invalid memory alloc"

2012/11/25 Stefan Froehlich <postgresql(at)froehlich(dot)priv(dot)at>:
> On Sun, Nov 25, 2012 at 06:57:22PM +0100, Pavel Stehule wrote:
>> > | INSERT INTO a (id, fkid, displayorder, name, description, internal, mimetype, mimedata, filesize) VALUES (73,6,5, E'Seefeld.rar', E'Seefeld',0, E'application/octet-stream',decode('5261...0700', 'hex'),311484587);
>
>> Attention - BYTEA is not BLOB and although physical limit is 1G - real
>> limit is significantly less - depends on RAM - 7years ago we found so
>> practical limit is about 20MB.
>
> Oops, that's not too much. In the docs I've seen a 4-byte length
> descriptor, thus expected a size limit of 4 GB and felt quit safe
> with a maximum size of 300 MB.
>

you didn't read well - it a 4byte header - but some bites are
reserved. so theoretical limit is 1G

>> If you need more, use blobs instead or you can divide value to more blocks
>> http://www.fuzzy.cz/en/articles/storing-files-in-a-postgresql-database/
>
> Yes, storing large data objects in the file system is advisable for
> several reasons - we've had the same discussion times ago with mysql as
> well. But the decision was made to keep it in the database (and there is
> only one object of this size anyways). Rewriting the framework is not an

> option at the moment.

It highly depends on RAM and on used API - if you use prepared
statements and binary transmission, you probably significantly reduce
memory usage.

But I think so +/- 50MB is practical - and LO interface will be faster
and better.

Regards

Pavel

>
> If I fail to migrate this into postgresql, we'd rather cancel the
> transition.
>
> Stefan
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general