Re: there is already a transaction in progress ?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jean-David Beyer wrote:
> 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.
>
Sorry: false alarm.

Just outside of a loop I called a function that does a query on the
database. This silently begins a transaction, just as in DB2.

But I did not realize this in postgreSQL because I changed around the code a
bit to combine each iteration of the loop into a single transaction instead
of a bunch of smaller ones. Thus I implicitly began a transaction with the
one function call that I forgot did a query on the database and thus began a
transaction. (And NO, I do not want to be able to do nested transactions.)

- --
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 11:55:01 up 9 days, 15:17, 6 users, load average: 4.04, 4.06, 4.07
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org

iD8DBQFGxyAaPtu2XpovyZoRAjb6AKCtP5urRKntgL8+k729hLy1PoUEvwCgv6XL
qmfWYjSYPWxsg5h/J1c1rIE=
=acAS
-----END PGP SIGNATURE-----