Hello
2012/11/25 Stefan Froehlich <postgresql@froehlich.priv.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@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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general