Dunbar, Norman wrote:

>
> I'm QA'ing a script for an app we have here which is creating a pile of
> tables and indices etc, and has no COMMIT after any of these.
>

As Dmitry S. says, yes, you must commit DDL statements. Otherwise
the changes will be undone when your connection ends. But there's
more.

In DML Firebird makes changes and performs constraint checking
as statements are issued - there's no deferred work. You issue
an update statement and the results are checked for uniqueness,
referential correctness, nullness, and all other constraints
right then. If the data checks are all satisfied, the new row
is stored - linked backward to the old data.

DDL is different. Much of the work in creating tables and
indexes is done at commit time. Internally, it's known as
"deferred work". The rows in RDB$RELATIONS, RDB$FIELDS
etc. are stored as you go, but the new entries in RDB$PAGES
and RDB$FORMATS that make the table work aren't done until
you commit. That's a relic of the very old, not very good
idea of handling DDL like data. If you're creating a table
by first creating any necessary domains (RDB$FIELDS), then
storing an RDB$RELATIONS record for the table, then storing
one RDB$RELATION_FIELDS record for each column in the table,
and you instantiate the results immediately, you end up with
one RDB$FORMATS record for the table with not fields, and
one for each field stored. Messy, especially since you only
get 256 formats ... So, Jim decided that DDL would be
processed when the table definition was complete and committed.

Index creation is also deferred to commit time. If you watch
gbak's verbose restore of a database, it grinds for a long
time after the commit. It's using a fast-load algorithm to
load all the indexes.

If I were doing it now, having abandoned the idea of direct
DDL updates as too clunky for human use, I'd create tables
completely at the time of the create table statement and
keep them private - just like normal data - the transaction
that created the table could use it before it was committed,
but other transactions would be aware of it only if they
tried to create another table of the same name.

Haven't thought much about indexes... you do want other
transactions to start using them (for write if not for read)
as soon as they exist.

So, short answer, listen to Dmitry and commit your DDL
statements like other data changes.

Cheers,

Ann