Hi Jim,

> [This is dual posted to firebird-devel, but I would like to have the
> discussion on firebird-architect.]
>
> I would like to make all DDL operations atomic, essentially deprecating
> the current metadata transaction appearance. In specific, I propose the
> following for Vulcan: SQL metadata operations would be performed on a
> transient, internal, invisible transaction committed or rolled back when
> the statement processing is complete. The net result is that DDL
> operations would be atomic and outside of transaction scope.
>
> The engine code in all versions of Interbase and Firebird does not
> handle access to uncommitted metadata. All engine metadata access is
> performed through a semi-magic system transaction that bypasses
> transaction control. The engine, however, has no code to manage access
> to uncommitted objects. This hasn't been a serious problem in the past
> because most access has been through components that acquire metadata
> under transaction control and consequently never generate references to
> committed metadata. Preprocessed programs and components emitting BLR
> level requests leave the engine exposed to erratic operation, in memory
> metadata corruption, engine crashes, or possibly worse. In Interbase
> and Firebird the problem is probably more theoretical then practical --
> it can be demonstrated by non privileged user code, but that user code
> would have to be hand tailored by a knowledgeable individual to show the
> problem.
>
> The problem is much more severe in Vulcan. Prior versions of Firebird
> had a DSQL implementation that maintained separate metadata caches for
> each client that effective prevented manifestations of the problem. In
> Vulcan, however, SQL processing uses internal engine metadata, turning a
> theoretical problem in Firebird into a major practical problem in Vulcan.
>
> The only adverse impact that I am aware of would be the loss of the
> ability to backout a sequence of uncommitted DDL operations, a
> non-standard behavior at best.

Well, for example, Database Workbench uses this ability to alter
several things at once for a table in the GUI.

You can, for example, add a couple of columns, drop a few,
modify and add some triggers. Next press "Save" and all actions
get saved or backed out when something critical happens. This
"critical" part can be a typo in a trigger, for example.

How would this work with Vulcan? My guess is, that it doesn't -
right?

>I suspect the scope is limited to the
> problems of restarting update scripts during application upgrade
> procedures. I believe an "upgrade" DDL verb would be a better way to
> address this need.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com