Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #4445: Atomic transaction problem

BUG #4445: Atomic transaction problem

From
"Santosh"
Date:
The following bug has been logged online:

Bug reference:      4445
Logged by:          Santosh
Email address:      sbhujbal@starentnetworks.com
PostgreSQL version: 8.2.0
Operating system:   Sun-Solaris
Description:        Atomic transaction problem
Details:

Hi All,

Following are the details of my application setup:
Platform: Sun-solaris 9
Application written in 'C++'.
Postgres interface: libpq 'C' interface.
Postgres version: 8.2.0

I have two tables, 'old' and 'current' in database.
'current' table contains latest one months records and 'old' table contains
records older than one month.
My application is runtime adding new columns in both the tables.
That operation is atomic like:
sql="BEGIN TRANSACTION;";
PQexec (pgConn, sql);
sql="ALTER TABLE old ADD COLUMN new_col bigint;";
PQexec (pgConn, sql);
sql="ALTER TABLE current ADD COLUMN new_col bigint;";
PQexec (pgConn, sql);
sql="END TRANSACTION;";
PQexec (pgConn, sql);

Because of some reason I have shutdown the postgres with -m (fast shutdown)
option.
At the same time my application was adding the columns in tables.
Because of the shutdown, column gets added in 'old' table but not in
'current' table.
Now there is mismatch in schemas of both the tables.
Ideally this should not happen as the operation is atomic.

Following is the postgres log from log file:
============================================================================
=
2008-09-24 04:08:39 CEST  LOG:  received fast shutdown request
2008-09-24 04:08:39 CEST  LOG:  aborting any active transactions
2008-09-24 04:08:39 CEST bsdb FATAL:  terminating connection due to
administrator command
2008-09-24 04:08:39 CEST bsdb STATEMENT:  ALTER TABLE current ADD COLUMN
new_col bigint;
2008-09-24 04:08:39 CEST  LOG:  shutting down
2008-09-24 04:08:40 CEST  LOG:  database system is shut down
============================================================================
=

What is the reason of above postgres behavior?
Is there any known, reported issue/bug in postgres version 8.2.0?
Is this issue fixed in latest postgres releases?

Thanks in advance.

Santosh.

Re: BUG #4445: Atomic transaction problem

From
Tom Lane
Date:
"Santosh" <sbhujbal@starentnetworks.com> writes:
> My application is runtime adding new columns in both the tables.
> That operation is atomic like:
> sql="BEGIN TRANSACTION;";
> PQexec (pgConn, sql);
> sql="ALTER TABLE old ADD COLUMN new_col bigint;";
> PQexec (pgConn, sql);
> sql="ALTER TABLE current ADD COLUMN new_col bigint;";
> PQexec (pgConn, sql);
> sql="END TRANSACTION;";
> PQexec (pgConn, sql);

Have you simply omitted error checking from your report, or did your
code actually not bother to check for errors?  If for some odd reason
the BEGIN command failed, and you kept going anyway, then the subsequent
commands would have been executed in their own transactions.

I tried the reported case here, just to verify, and an ALTER TABLE in a
transaction that is then aborted by fast shutdown definitely does not
take effect.  So I think this is pilot error of some sort.

> Is there any known, reported issue/bug in postgres version 8.2.0?

8.2.0?  Yes, quite a few.
http://www.postgresql.org/docs/8.2/static/release.html
But this report doesn't match any of them.

            regards, tom lane