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:
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

Search Discussions

  • Pavel Stehule at Nov 25, 2012 at 5:58 pm
    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
  • Stefan Froehlich at Nov 25, 2012 at 7:39 pm

    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.
    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.

    If I fail to migrate this into postgresql, we'd rather cancel the
    transition.

    Stefan
  • Pavel Stehule at Nov 25, 2012 at 8:10 pm

    2012/11/25 Stefan Froehlich <postgresql@froehlich.priv.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@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-general
  • Tom Lane at Nov 25, 2012 at 8:11 pm

    Stefan Froehlich writes:
    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.
    I replicated this case and found that the immediate cause of the problem
    is addlit() in the scanner, which is trying to double its work buffer
    size until it's larger than the literal string --- so even though the
    string is "only" 600MB, it tries to make a 1GB buffer.

    We could fix that particular case but there are doubtless others.
    It's not really a good idea to be pushing query texts of hundreds of
    megabytes through the system. Quite aside from whether you'd hit the
    1GB-per-alloc hard limit, the system is likely to make quite a few
    copies of any constant value in the process of parsing/planning a query.

    You might have better luck if you treated the large value as an
    out-of-line parameter instead of a literal constant. Aside from dodging
    the costs of a very large query string and a large Const value, you
    could send the parameter value in binary and avoid hex-to-binary
    conversion costs.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedNov 25, '12 at 5:19p
activeNov 25, '12 at 8:11p
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2018 Grokbase