-----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----- ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate