Re: large INSERT leads to "invalid memory alloc"

Hello

2012/11/25 Stefan Froehlich <postgresql(at)froehlich(dot)priv(dot)at>:
> While converting a mysql database into postgres, I stumbled over the
> following problem:
>
> | 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);
>
> As the value for "filesize" suggests, this is a very large BYTEA
> (formerly: LONGBLOB) entry with about 300 MB. This is untypical, all
> other fields are about a couple of MB and don't make any problems.
> This very line leads to:

usually you need 2-3 times memory than is query size for parsing and
execution - and you probably raise a internal check of max allocation
- it expects so any alloc over 1G is strange.

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.

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/

Regards

Pavel Stehule

>
> | sfroehli(at)host:~$ psql dbname < statement.sql
> | Password:
> | ERROR: invalid memory alloc request size 1073741824
>
> I have not found any configuration directive similar to mysqls
> "max_allowed_packet" to increase the buffer size. And besides, I
> don't understand, why postgres wants to allocate 1 GB to store
> 300 MB (which take 600 MB of ASCII text in the decode()-string).
>
> Any idea how to put this into the target database?
>
> 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