From: Jean-David Beyer <jeandavid8@verizon.net>
To: pgsql-sql@postgresql.org
Subject: there is already a transaction in progress ?
Date: Sat, 18 Aug 2007 08:16:40 -0400
Message-ID: <46C6E328.9040008@verizon.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jeandavid8@verizon.net
Subject: Re: there is already a transaction in progress ?
In-Reply-To: <46C6E328.9040008@verizon.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox