Search Postgresql Archives

dblink() cursor error/issue (TopMemoryContext)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello all,

I'm trying to code a function to copy rows from one machine to another
using dblink and cursors:

...
perform dblink_connect ('dbname=db1...host=othermachine.com');
perform dblink_open ('cur_other1', 'SELECT col1 FROM tab1');

loop
  fnd := 0;
  for rec in
      -- grab a 1000 rows at a time
      SELECT col1 FROM dblink_fetch ('cur_other1', 1000)
      AS tab1 (col1 text)
  loop
      begin
          INSERT INTO tab1 (col1) VALUES (rec.col1);
          ...
      exception when unique_violation then
         -- ignore dups
      end;
      fnd := 1
  end loop;
  if fnd = 0 then
     exit;
  end if;
end loop;

perform dblink_close ('cur_other1');
perform dblink_disconnect();


This runs fine for a while, then starts vomiting:

TopMemoryContext: 44175408 total in 5388 blocks; 94224 free (5394 chunks);
44081184 used
SPI Plan: 3072 total in 2 blocks; 2000 free (0 chunks); 1072 used
SPI Plan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
SPI Plan: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used
SPI Plan: 3072 total in 2 blocks; 1328 free (0 chunks); 1744 used
SPI Plan: 7168 total in 3 blocks; 3896 free (0 chunks); 3272 used
SPI Plan: 7168 total in 3 blocks; 3896 free (0 chunks); 3272 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 7168 total in 3 blocks; 1504 free (0 chunks); 5664 used
PL/PgSQL function context: 8192 total in 1 blocks; 6928 free (5 chunks);
1264 used
SPI Plan: 3072 total in 2 blocks; 1808 free (0 chunks); 1264 used
SPI Plan: 1024 total in 1 blocks; 96 free (0 chunks); 928 used
SPI Plan: 3072 total in 2 blocks; 1664 free (0 chunks); 1408 used
SPI Plan: 3072 total in 2 blocks; 1312 free (0 chunks); 1760 used
PL/PgSQL function context: 24576 total in 2 blocks; 12112 free (10
chunks); 12464 used
SPI Plan: 15360 total in 4 blocks; 7640 free (0 chunks); 7720 used
Operator class cache: 8192 total in 1 blocks; 4872 free (0 chunks); 3320 used
Record information cache: 8192 total in 1 blocks; 1800 free (0 chunks);
6392 used
SPI Plan: 3072 total in 2 blocks; 1576 free (0 chunks); 1496 used
SPI Plan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
SPI Plan: 3072 total in 2 blocks; 1760 free (0 chunks); 1312 used
SPI Plan: 3072 total in 2 blocks; 1856 free (0 chunks); 1216 used
SPI Plan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
SPI Plan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
SPI Plan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
SPI Plan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
PL/PgSQL function context: 24576 total in 2 blocks; 7784 free (16 chunks);
16792 used
CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
Rendezvous variable hash: 8192 total in 1 blocks; 3848 free (0 chunks);
4344 used
PLpgSQL function cache: 24596 total in 2 blocks; 5904 free (0 chunks);
18692 used
TopTransactionContext: 8380416 total in 10 blocks; 3213936 free (0
chunks); 5166480 used
SPI Exec: 8192 total in 1 blocks; 7992 free (0 chunks); 200 used
ExecutorState: 8192 total in 1 blocks; 3080 free (0 chunks); 5112 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
SPI Proc: 8192 total in 1 blocks; 6520 free (5 chunks); 1672 used
CurTransactionContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
AfterTriggerEvents: 8192 total in 1 blocks; 8136 free (0 chunks); 56 used
ExecutorState: 24576 total in 2 blocks; 4472 free (4 chunks); 20104 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used

...

repeat above until 150GB+ logfile, then ctrl-c

On the tty where I've called the function, after hitting ctrl-c, I get:

...
ERROR:  out of memory
DETAIL:  Failed on request of size 1291220.
...
PANIC:  ERRORDATA_STACK_SIZE exceeded
...

I'm trying to use cursors so that I don't run out of memory - yet I seem
to be running out of memory anyway.

Doing this the other way round works OK:

perform dblink_connect('dbname=db1...host=othermachine.com');
for rec in
        SELECT col1 FROM tab1  -- this uses cursors in function auto'ally
loop
    perform dblink_exec ('INSERT INTO tab1 ..'||rec.col1||'...');
    ...
end loop;
perform dblink_disconnect();
...


I must be doing something stupid here.

Any comments are welcome.

Regards
Henry



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux