pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedthere is already a transaction in progress ?
2+ messages / 1 participants
[nested] [flat]
* there is already a transaction in progress ?
@ 2007-08-18 12:16 Jean-David Beyer <jeandavid8@verizon.net>
2007-08-18 16:36 ` Re: there is already a transaction in progress ? Jean-David Beyer <jeandavid8@verizon.net>
0 siblings, 1 reply; 2+ messages in thread
From: Jean-David Beyer @ 2007-08-18 12:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: there is already a transaction in progress ?
2007-08-18 12:16 there is already a transaction in progress ? Jean-David Beyer <jeandavid8@verizon.net>
@ 2007-08-18 16:36 ` Jean-David Beyer <jeandavid8@verizon.net>
0 siblings, 0 replies; 2+ messages in thread
From: Jean-David Beyer @ 2007-08-18 16:36 UTC (permalink / raw)
To: pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-08-18 16:36 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-18 12:16 there is already a transaction in progress ? Jean-David Beyer <jeandavid8@verizon.net>
2007-08-18 16:36 ` Jean-David Beyer <jeandavid8@verizon.net>
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