[SQL] there is already a transaction in progress ?

[Jean-David Beyer]

It probably shows I am new to postgreSQL. I recently started running this
instead of DB2, and am converting the applications I already wrote. These
use ecpg.

The problem I have concerns transactions. I have an application (the first
one I am converting) that inserts a lot of stuff into three tables. (It is
normalizing a .tsv file from a spreadsheet.) The program is in C++.

The structure of the program is, I think,
    ...
    dbBase stock_database(STOCK_DB); // Constructor opens connection
    ...
    EXEC SQL SET AUTOCOMMIT = off; // Just in case.
    ...
    while(input.next()) {          // Process each line of the file.
    ...
        cerr << "BEGIN WORK" << endl;
        EXEC SQL BEGIN WORK;
    ...
        [insert stuff]
        [if error] {
                cerr << "ROLLBACK WORK" << endl;
                EXEC SQL ROLLBACK WORK;
                continue;
         }
    ...
        [if no error] {
                cerr << "COMMIT WORK" << endl;
                EXEC SQL COMMIT WORK;
        }
    }
    ...
    [dbBase destructor closes the connection to the postmaster]

I have shortened the program to run three iterations instead of the normal
30,000 or so, and I get this output:

BEGIN WORK
COMMIT WORK
BEGIN WORK
COMMIT WORK
BEGIN WORK
COMMIT WORK

and it inserts the three items; I can see them with psql.

The trouble is that the /src/dbms/dataB/pgsql/pg_xlog says this:

2007-08-18 07:26:28 EDT LOG:  autovacuum: processing database "stock"
2007-08-18 07:27:20 EDT WARNING:  there is already a transaction in progress
2007-08-18 07:27:20 EDT WARNING:  there is already a transaction in progress
2007-08-18 07:27:20 EDT WARNING:  there is already a transaction in progress
2007-08-18 07:28:20 EDT LOG:  autovacuum: processing database "stock"

The autovacuum is just the regular stuff. I put the timestamps into the
logfiles because it was otherwise too difficult to see what was what.

I restarted the postgres system (/etc/rc.d/init.d/postgres restart) in case
some leftover transaction was lying around -- though I am not sure this is
enough.

I cannot believe this is normal. Do incomplete transactions persist around a
shutdown and restart of postmaster? And if so, how do I clear the lost
transaction?

BTW, when I test this, I DELETE FROM all the tables, and reset all the
sequences with this kind of thing:

ALTER SEQUENCE company_company_id_seq
    RESTART WITH 10000;

before running the test program.

-- 
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 07:45:01 up 9 days, 11:07, 3 users, load average: 4.15, 4.21, 4.13

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq