Thread: BUG #2265: CREATE TABLE when AUTOCOMMIT is OFF

The following bug has been logged online:

Bug reference:      2265
Logged by:          Gerhard Lutz
Email address:      gerhard.lutz@mbtech-group.com
PostgreSQL version: 8.1.3
Operating system:   Windows XP SP2
Description:        CREATE TABLE when AUTOCOMMIT is OFF
Details:

I want to upgrade my PostgreSQL server from 8.0.4 to 8.1.3.
My application connects to the database via ODBC.
After connecting to the database I set the AUTOCOMMIT mode to OFF.

::SQLSetConnectAttr(g_hDBConnection, SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF), 0);

Now, if I want do create a table via ODBC, e.g.

"CREATE TABLE db_version ( hv integer, uv integer, nv integer, r integer,
rel integer)"

, I get the error message

"Error while executing the query;
ERROR:  current transaction is aborted, commands ignored until end of
transaction block (7)"

In PostgreSQL 8.0.4 I was able to create a table in AUTOCOMMIT mode OFF
without any error. Is this a bug in 8.1.3?

On Thu, Feb 16, 2006 at 01:21:18PM +0000, Gerhard Lutz wrote:
> "Error while executing the query;
> ERROR:  current transaction is aborted, commands ignored until end of
> transaction block (7)"

Some previous command failed so no more commands in this transaction
will be allowed.  If you don't know what command failed then you
could configure query logging.

> In PostgreSQL 8.0.4 I was able to create a table in AUTOCOMMIT mode OFF
> without any error. Is this a bug in 8.1.3?

The error is apparently happening before the CREATE TABLE command.
Let's see what earlier command failed and why.

--
Michael Fuhr

Please copy the pgsql-bugs mailing list on replies.  Also, if you've
submitted multiple bug reports then please keep the discussion of
each bug in their respective threads.  I'm replying only to 2265
because I'm not as familiar with the other problems.

On Fri, Feb 17, 2006 at 03:09:48PM +0100, Gerhard.Lutz@mbtech-group.com wrote:
> Now I configured query logging and I saw what was wrong.
> First I executed
>
> SELECT rel FROM versions
>
> but the 'versions' table didn't exist. So I thought that I can create this
> 'versions' table in the
> same transaction. In PostgreSQL 8.0.4 this worked, but in 8.1.3 I got the
> error message.

When you say that "this" worked in 8.0.4 but not in 8.1.3, what
exactly do you mean by "this"?  If the SELECT failed then any
subsequent command in the same transaction should also have failed;
8.0 and 8.1 shouldn't differ in that respect, at least not in the
backend.  If you can demonstrate otherwise then please post a
repeatable test case (i.e., a set of SQL statements that can be run
against an empty database in a new session or a new transaction).
Try running tests with psql as well as with ODBC to see if they
behave differently.

> When executing COMMIT after the SELECT statement, I was able to do the
> CREATE TABLE.
> I think that this behaviour is desired in 8.1.3, am I right? It surely has
> to do with the two-phase commit.

Are you using two-phase commit?  Why do you think it's "surely"
involved?  Let's see a complete test case before jumping to
conclusions.

--
Michael Fuhr