Norman Dunbar 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.
Ann W. Harrison answers:
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.