(Yahoo and stopped sending messages to be from architect for a
while and I just reactivated to respond. So I've missed some
of the conversation on this so far. I hope the points raised
here are not too much of a duplication.)

> I would like to make all DDL operations atomic, essentially
> deprecating the current metadata transaction appearance.

...

Personally I dont have a big problem with this idea. The
reality with most significant metadata changes has been that
commits are needed are specific points anyway. Points that
have been dictated by the engine requirements rather than by
script commit/rollback convenience.

My own experience is that significant metadata change has
always been something that you do in a strictly controlled
manner; everyone off the system, backup, apply the changes.
On failure restore, on success let everyone back on.

I would suggest that the change you are proposing makes this
sort of process (on production systems) almost mandatory. If
I have 10000 lines of SQL script I dont want to have to write
9999 different back-out scripts to undo changes from all
possible failure points.

OK, so it need not be that bad, the point being that the only
"Undo" option would be to write back-out scripts and it could
be interesting to see just how well they might work. (ie: Do
you think that "Object In Use" will be a problem in Vulcan,
especially in regard to running a back-out script?)


What is the visibility of these atomic DDL operations? If
a script has DDL intersperced with DML will the DDL changes
be visible if the DML transaction is Concurrency (the default
isolation)? - see also my backup notes below.


That was all about "significant" metadata change. The main
potential problem I can see are the less significant items,
the metadata changes that applications sometimes make
on-the-fly:
- grant/revoke
- trigger inactive/active
- others?

The change you propose would require different error handling
to be installed in applications. Instead of (just) rollback
on error, it may require a back-out script. I dont think this
is necessarily a big problem, just an observation.


Another observation is about EXECUTE STATEMENT and the fact
that it can be used to issue DDL. Your change could effect
the way this should be implemented in stored procedures.


Another factor that occurs to me is about backups.

I dont know exactly how gbak and nbackup work, but my DBak
utility reads everything out of a source database under a
single concurrency isolated transaction. And "everything"
includes the RDB$ metadata tables (which I later use to build
DDL scripts to reconstruct the database).

Does the change that you propose change the visibility of
metadata to such transactions? (Is it likely that a backup
could obtain an inconsistent snapshot of metadata as a result
of these changes?)

--
Geoff Worboys
Telesis Computing