Date: Sat, 18 Aug 2007 12:36:42 -0400
From: Jean-David Beyer <jeandavid8@verizon.net>
Subject: Re: there is already a transaction in progress ?
In-reply-to: <46C6E328.9040008@verizon.net>
To: pgsql-sql@postgresql.org
Message-id: <46C7201A.8020601@verizon.net>
Organization: Institute for Regimented Whimsey
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7bit
References: <46C6E328.9040008@verizon.net>
User-Agent: Thunderbird 1.5.0.9 (X11/20070317)

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