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;

DROP TABLE TESTTBL;

/* I do NOT commit here */

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

/* I do NOT commit here */

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

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

COMMIT;

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

SET AUTODDL ON;

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

/* 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;

/* This is just to show the two identical records :-) */

SELECT * FROM TESTTBL;

---

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

Regards,
Bozzy.


--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> You are very confused. In the second script, you set Autoddl off

(meaning

> that the work remains uncommitted until you explicitly commit it).

Then

> you dropped the table; without committing that, you created it
> again; then, without committing it, you added a constraint to it;

then,

> without committing anything yet, you started a new transaction

(because DDL

> runs in its own transaction) and added records to a table that (from

the

> POV of the new transaction) is still the old table (that you have

dropped

> but not committed). It's anyone's guess what the state of things

might

> have been as that script progressed.
>
> Was there any special purpose to this peculiar exercise? What did

you seek

> to demonstrate?
>
> If you seriously want to create and modify metadata, do it in an

orderly

> fashion, in a way that you are in control of the state of the
> metadata. The default behaviour of isql scripts for DDL (autoDDL

on) is to

> start a transaction, execute a DDL statement and commit that
> statement. Start another transaction, execute another DDL statement

and

> commit that statement...and so on.
>
> If you include DML statements in a script, isql starts a different
> transaction that is NOT affected by autoDDL. Setting AutoDDL on

does *not*

> cause your DML transaction to get "autocommitted".
>
> You must apply DML to committed DDL objects. Weird things happen

when you

> mess about as you did, with metadata ghosts hanging around in an
> uncommitted transaction. >>>"As designed".<<< The wise and worldly

simply

> avoid running scripts that perform both DDL and DML, unless they are
> totally in command of what will eventuate during execution of the

script

> commands. You're not.
>
> ./hb