At 11:04 AM 5/04/2005 +0000, you wrote:

>Hey wait wait wait...
>
>I think all your discussion starts from a bad consideration, or maybe
>I don't know the difference between a "normal" transaction and a "DDL"
>transaction, if there's any. You said I've added two records to a
>table that's still the old table, but I HAVE COMMITTED the DDL
>transaction BEFORE inserting the records.
>
>Let's see the second script and what I think it should do. Please then
>correct me where I'm wrong, if I'm wrong...
>
>---
>
>/* I don't want other transactions to see I'm dropping the */
>/* table, so I want to drop it and recreate it in its own */
>/* transaction, committing the whole operation at the end. */
>/* If you'd like to know exactly WHY I want to do so, I'll */
>/* have to start a new thread, 'cause it's a long story... */
>
>SET AUTODDL OFF;

This prevents the transaction that is started for the first DDL statement
from being automatically committed.

>DROP TABLE TESTTBL;
>
>/* I do NOT commit here */

OK, so far, you have an uncommitted DDL transaction, which has deleted the
metadata definition for the original TESTTBL.

At this point, the data in TESTTBL are earmarked for deletion. However, in
the committed state of the database (that all other transactions can see)
the original table and its data still exist. However, at this point, the
system table records and the data records are locked: another transaction
cannot drop the original table nor delete or update any of the records.

>CREATE TABLE TESTTBL (
>TESTFLD INTEGER NOT NULL,
>NEWFLD INTEGER);
>
>/* I do NOT commit here */

And, because *this transaction* sees that the original TESTTBL is "gone",
the addition of the new record versions to the system tables is allowed.

>ALTER TABLE TESTTBL ADD CONSTRAINT PK_TESTTBL PRIMARY KEY (TESTFLD);

Now - this is where your problems begin. ALTER TABLE on an uncommitted
table object is not disallowed; yet the constraint is not added, because
the table is not yet committed in the database. (Committing DDL statements
causes various things to happen in the metadata (creating domains for the
fields, writing and validating records in a number of system tables, etc.)
that will then make it possible to do other operations, such as creating
indexes. For the PK constraint to "take", it would have to be possible to
create a unique index on TESTFLD but, so far, TESTFLD is nothing but a
network of pending definitions...)

This is one of the "foolproofing" areas that has been discussed: that
ALTER TABLE statement should somehow be made to cause an exception because
the object it is requesting to alter is still ephemeral; but it
doesn't; the tool relies on the assumption that AUTODDL is on.

>/* Finally, I'M COMMITTING the DDL transaction, */
>/* or at least I think to do so... Am I wrong here? */
>
>COMMIT;

Yes, finally that commits the transaction. So, now, the database has the
new version of the table (empty) but not the PK constraint.

In fact, as far as a PK constraint goes, the constraint *would* exist if
you had defined it as part of the table definition, either:

CREATE TABLE TESTTBL (
TESTFLD INTEGER NOT NULL PRIMARY KEY,
NEWFLD INTEGER);

or

CREATE TABLE TESTTBL (
TESTFLD INTEGER NOT NULL,
NEWFLD INTEGER,
CONSTRAINT PK_TESTTBL PRIMARY KEY (TESTFLD));

>/* This line is totally ininfluent, I've put it */
>/* only in order to reset to the default autoddl on */
>
>SET AUTODDL ON;

Yes, it has no effect on any statements that have already been executed
while autoddl was off.

>/* The two INSERTs should see the NEW table, 'cause */
>/* the DDL transaction has been committed before. */

Yes; but the unique index for the primary key constraint does not exist.

>/* If you prefer, just to not to mix DDL and DML */
>/* scripts, you can put the following lines on another */
>/* script, or input them manually, maybe even from */
>/* another isql session, or from anoter tool, from */
>/* another host, it doesn't matter! */
>
>INSERT INTO TESTTBL (TESTFLD) VALUES (1);
>INSERT INTO TESTTBL (TESTFLD) VALUES (1); /* ACCEPTED!!! */
>
>COMMIT;

That's allowed, because there is no constraint to prevent it.

>So, considering that the bad things have to do ONLY with the DDL
>transaction, where's my fault?

The nett effect is that ALTER TABLE can't alter a table whose definition
isn't committed yet. The problem (for you) is that you didn't see an
exception to tell you that the conditions weren't right for performing an
ALTER TABLE; execution of the script simply went past it. Commit finally
committed what was there, pending to be committed, i.e. the drop table and
the create table.

As a side-note, there is another DDL syntax that works like your two
statements: RECREATE TABLE. It drops the named table (if it exists) and
then proceeds to set up the new definition with the same table name. Again,
with this, you will encounter the same issue if you prevent the statement
from committing before issuing an ALTER TABLE.

./hb