On 03/26/2012 01:54 PM, Andrew Dunstan wrote:

On 03/26/2012 01:34 PM, Tom Lane wrote:
Andrew Dunstan<and...@dunslane.net>  writes:
On 03/26/2012 01:06 PM, Heikki Linnakangas wrote:
Is it possible this job is inserting and then updating (or deleteing)
the row it just inserted and doing a large number of such
insert/update operations all within the same transaction? Or perhaps
it's updating the same row over and over again?
It's all in a single transaction. In fact the solution I'm currently
testing and seems to be working involves breaking it up into batches of
a few thousand LOs restored per batch.
Hm. The test case is just a straight pg_restore of lots and lots of LOs?
What pg_dump version was the dump made with?

8.4.8, same as the target. We get the same issue whether we restore direct to the database from pg_restore or via a text dump.

Following this up, the workaround of making small batches of LOs did solve that memory issue. Here's what I did:

   pg_restore --list full_export.dmp | grep BLOB > bloblist
   pg_restore --use-list=bloblist full_export.dmp | \
       perl -n e  ' $n++  if /lo_open/; ' \
            -e ' print " end; begin;\n" if (/lo_open/ && ($n % 1000 ==
   0)); ' \
            -e ' print ;' |  \
       psql -t -q -v ON_ERROR_STOP "dbname=adtest" > /dev/null

That's a fairly ugly hack to have to use.



Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to