Firebird

If it is common this issue, may be is time to force COMMIT on DDL?

> -----Mensaje original-----
> De: firebird-devel-admin@...
> [mailto:firebird-devel-admin@... nombre de
> Samofatov, Nickolay
> Enviado el: Martes, 27 de Abril de 2004 01:43 p.m.
> Para: firebird-devel@...
> Asunto: RE: [Firebird-devel] possible bug in FB1.5.0.4306
>
>
> Hi!
>
> > >>One difference - I added commits after the two alter table
> > statements.
> > >>FB1.5.0.4306 on Win2K.
> >
> > >>Helen
> >
> > The lack of a commit after the metadata update seems to be at
> > the root of the problem.
> > If you run the example exactly as I gave it, do you get the
> > same results?
>
> Lack of commit between metadata update and its usage normally leads to
> database corruption.
> This issue is known, and fixing it requires some work -- marking objects
> as modified and unusable until DFW handler performs real metadata work
> at commit time.
>
> Nickolay
>
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: The Robotic Monkeys at ThinkGeek
> For a limited time only, get FREE Ground shipping on all orders of $35
> or more. Hurry up and shop folks, this offer expires April 30th!
> http://www.thinkgeek.com/freeshipping/?cpg297
> Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel

At 02:28 PM 27/04/2004 -0600, you wrote:
>If it is common this issue, may be is time to force COMMIT on DDL?

You already can - just include the statement SET AUTODDL ON at the 
beginning of your script. If you are running metadata updates as DSQL from 
applications, you can take care of it just as with any transaction.

I hope nobody decides to force commit on DDL.  It would make it impossible 
to write metadata update programs for remote customers with a 
rollback-all-on-exception option, which is a must.

I can't think what Nickolay thinks needs fixing about the current 
situation.  IMO, it is correct (and quite clever) that DDL and DML in 
scripts should run in separate transactions;  and that designers be aware 
that uncommitted metadata changes are not visible to DML statements in the 
same script.

Simply, I don't think it should be messed with.  If people want to perform 
DML in a DDL script, let them be responsible for handling both DDL and DML 
commits.

Helen

Hi!

> You already can - just include the statement SET AUTODDL ON=20
> at the beginning of your script. If you are running metadata=20
> updates as DSQL from applications, you can take care of it=20
> just as with any transaction.
>=20
> I hope nobody decides to force commit on DDL.  It would make=20
> it impossible to write metadata update programs for remote=20
> customers with a rollback-all-on-exception option, which is a must.

There is a number of reasons to use DFW and this is one of them.

> I can't think what Nickolay thinks needs fixing about the=20
> current situation.  IMO, it is correct (and quite clever)=20
> that DDL and DML in scripts should run in separate=20
> transactions;  and that designers be aware that uncommitted=20
> metadata changes are not visible to DML statements in the same script.

Ugh. I think the limitation you propose is too tough. I often write
data-driven DDL scripts and they would be broken by such limitation.
From the engine POV enough restriction is to do forbid mixing DML and
DDL for individual objects, i.e. forbid DML on table after you did DDL
on in till commit time.

> Simply, I don't think it should be messed with.  If people=20
> want to perform DML in a DDL script, let them be responsible=20
> for handling both DDL and DML commits.

Agree. But preventing of possibility of database corruption is good to
have. But this is very low priority issue for now, AFAIU.

> Helen

Nickolay

Hello,

>>Simply, I don't think it should be messed with.  If people 
>>want to perform DML in a DDL script, let them be responsible 
>>for handling both DDL and DML commits.
>>    
>>
>
>Agree. But preventing of possibility of database corruption is good to
>have. But this is very low priority issue for now, AFAIU.
>  
>

I'm not sure if i understand it correctly. But if it is possible to 
corrupt a database with official DDL and DML statements, then i consider 
this a serious bug. What kind of corruption we're talking about? Is it a 
real physical and not recoverable corruption?
Regardless of if it is "clever" or not to mix DDL and DML statements in 
one transaction it never should lead to a corruption of the database. If 
there is a limitation in the engine, ok. But then treat it as such and 
raise at least an error or simply don't allow it. If the occuring 
corruption is of that unrecoverable kind, i would not define it as a 
very low priority issue. Firebird users are not necessarily firebird 
experts. It is likely that somebody will at some time forget a single 
commit statement. The worst case i will expect from such a "failure" is 
that my alterations do not work. But i would be very disappointed if 
such a little mistake will trash my database or the data in it.

cu
Jens

> Agree. But preventing of possibility of database corruption is good to
> have. But this is very low priority issue for now, AFAIU.

This low "priority issue" cost me a full day of downtime for more than half
of my clients when they ran the auto exe and metadata update feature of my
software. It lead to some very embarresing explaining and quite a lot of
head scratching on my part.
IMHO, it should move up the priority list a bit.

>What kind of corruption we're talking about? Is it a
> real physical and not recoverable corruption?

In some cases the corruption involved random fields being overwritten with
the data meant for the new column. In others it just meant that the metadata
change commited, but the data update was lost - a less serious problem. The
database itself was never corrupted beyond repair.

"Samofatov, Nickolay" <Nickolay@...> wrote:
>
> > I can't think what Nickolay thinks needs fixing about the
> > current situation.  IMO, it is correct (and quite clever)
> > that DDL and DML in scripts should run in separate
> > transactions;  and that designers be aware that uncommitted
> > metadata changes are not visible to DML statements in the same script.
>
> Ugh. I think the limitation you propose is too tough. I often write
> data-driven DDL scripts and they would be broken by such limitation.
> From the engine POV enough restriction is to do forbid mixing DML and
> DDL for individual objects, i.e. forbid DML on table after you did DDL
> on in till commit time.

Agreed. I thought about such a check too.

> > Simply, I don't think it should be messed with.  If people
> > want to perform DML in a DDL script, let them be responsible
> > for handling both DDL and DML commits.
>
> Agree. But preventing of possibility of database corruption is good to
> have. But this is very low priority issue for now, AFAIU.

I'd say "low", not "very low" ;-) Our DDL management is quite tricky
internally and this fix should make FB more reliable. People shouldn't be
able to corrupt databases via SQL, regardless of their responsibility.


Dmitry

atlantic wrote:
> In some cases the corruption involved random fields being overwritten with
> the data meant for the new column. In others it just meant that the metadata
> change commited, but the data update was lost - a less serious problem. The
> database itself was never corrupted beyond repair.

I have too agree. I have tried the same thing. Everything worked fine on 
my test machine, but after releasing it to 120 customers, everything got 
very nasty. I some cases they got an error message and ended up with 
partial updated metadata. Others did not get a error message, but the 
"random fields being overwritten" bug (and I think it is a bug) did its 
magic.

At 11:13 AM 28/04/2004 +0400, you wrote:
>"Samofatov, Nickolay" <Nickolay@...> wrote:
> >
> > > I can't think what Nickolay thinks needs fixing about the
> > > current situation.  IMO, it is correct (and quite clever)
> > > that DDL and DML in scripts should run in separate
> > > transactions;  and that designers be aware that uncommitted
> > > metadata changes are not visible to DML statements in the same script.
> >
> > Ugh. I think the limitation you propose is too tough. I often write
> > data-driven DDL scripts and they would be broken by such limitation.
> > From the engine POV enough restriction is to do forbid mixing DML and
> > DDL for individual objects, i.e. forbid DML on table after you did DDL
> > on in till commit time.
>
>Agreed. I thought about such a check too.
>
> > > Simply, I don't think it should be messed with.  If people
> > > want to perform DML in a DDL script, let them be responsible
> > > for handling both DDL and DML commits.
> >
> > Agree. But preventing of possibility of database corruption is good to
> > have. But this is very low priority issue for now, AFAIU.
>
>I'd say "low", not "very low" ;-) Our DDL management is quite tricky
>internally and this fix should make FB more reliable. People shouldn't be
>able to corrupt databases via SQL, regardless of their responsibility.

These guys who are complaining about the weird things happening when they 
sent out scripts that tried to do DML involving objects with uncommitted 
metadata will have to accept that a "fix" will stop them in their 
tracks.  If they do it the wrong way, it just won't work at all.  That is 
the *right* solution, no way can I dispute that.

But please, Dmitry, don't throw the baby out with the bathwater.

Currently, provided one writes a well-behaved set of scripts, isql's 
separation of the transactions is very good for "hands-off" metadata 
updates at customer sites.  This isn't rocket science, it's not about 
"secret knowledge", it's about plain ol' common sense and plain ol' good 
testing.  And it *is* clever the way isql does it now.

Helen

Wednesday, April 28, 2004, 2:19:59 PM, Helen Borrie wrote:

HB> These guys who are complaining about the weird things happening when they
HB> sent out scripts that tried to do DML involving objects with uncommitted 
HB> metadata will have to accept that a "fix" will stop them in their 
HB> tracks.  If they do it the wrong way, it just won't work at all.  That is 
HB> the *right* solution, no way can I dispute that.

HB> But please, Dmitry, don't throw the baby out with the bathwater.

HB> Currently, provided one writes a well-behaved set of scripts, isql's 
HB> separation of the transactions is very good for "hands-off" metadata 
HB> updates at customer sites.  This isn't rocket science, it's not about 
HB> "secret knowledge", it's about plain ol' common sense and plain ol' good 
HB> testing.  And it *is* clever the way isql does it now.

  Especially if to satisfy user's sclerosis FB will be forced to spend
noticeable time to validate all metadata related to any DML statement.
Seems we have already enough delays on checking access rights for
triggers on table mentioned in the statement, objects mentioned in
this triggers, objects mentioned in triggers for mentined objects,
objects mentioned... 


Best regards,
Alexander

> Seems we have already enough delays on checking access rights for
> triggers on table mentioned in the statement, objects mentioned in
> this triggers, objects mentioned in triggers for mentined objects,
> objects mentioned... 

No, after Nickolay's improvements it is running really fast :-)

Daniel Urban

atlantic wrote:

>>Agree. But preventing of possibility of database corruption is good to
>>have. But this is very low priority issue for now, AFAIU.
>>    
>>
>
>This low "priority issue" cost me a full day of downtime for more than half
>of my clients when they ran the auto exe and metadata update feature of my
>software. It lead to some very embarresing explaining and quite a lot of
>head scratching on my part.
>IMHO, it should move up the priority list a bit.
>  
>
The problem really boils down to this:

Somone applied DDL changes to a live database while it was being used.  
To make it reliable, this is what should happen:

Full backup of database, BEFORE the DDL changes are added.
Put database into shutdown or maintenance mode, meaning only SYSDBA can 
access it.
Apply DDL changes.
Commit DDL changes.
Take database out of maintenance mode.
Install new version of software on one computer, do some tests of most 
important functions to make sure nothing goes tilt.
Install new software on remaining computers.

Of course if it goes tilt, simply restore the backup of the database, 
put the old software back on the machine you were testing with,
and contact the developer to fix it.  Schedule enough down time for the 
system before hand that you can do the work intended. 

Paul

On Wed, 28 Apr 2004, Paul Schmidt <wogsterca@...> wrote:
> 
> Somone applied DDL changes to a live database while it was being used.  
> To make it reliable, this is what should happen:
> 
> Full backup of database, BEFORE the DDL changes are added.
> Put database into shutdown or maintenance mode, meaning only SYSDBA can 
> access it.
> Apply DDL changes.
> Commit DDL changes.
> Take database out of maintenance mode.
> Install new version of software on one computer, do some tests of most 
> important functions to make sure nothing goes tilt.
> Install new software on remaining computers.

Surely you don't mean this?  If I take you literally, it would mean that
every CREATE TABLE statement should be preceded and followed by a full
backup.  To make it realiable, that is, for some value of reliable.  

Or maybe I don't get the joke....

--jkl

> Surely you don't mean this?  If I take you literally, it would mean
> that every CREATE TABLE statement should be preceded and followed by a
> full backup.

And what's wrong with this in production environment? You do not change
schema in production too often. And when you do, you better do a backup
before. Do I miss something?
Roman

At 28/04/2004 09:13, you wrote:
>"Samofatov, Nickolay" <Nickolay@...> wrote:
> >
> > > I can't think what Nickolay thinks needs fixing about the
> > > current situation.  IMO, it is correct (and quite clever)
> > > that DDL and DML in scripts should run in separate
> > > transactions;  and that designers be aware that uncommitted
> > > metadata changes are not visible to DML statements in the same script.
> >
> > Ugh. I think the limitation you propose is too tough. I often write
> > data-driven DDL scripts and they would be broken by such limitation.
> > From the engine POV enough restriction is to do forbid mixing DML and
> > DDL for individual objects, i.e. forbid DML on table after you did DDL
> > on in till commit time.
>
>Agreed. I thought about such a check too.
>
> > > Simply, I don't think it should be messed with.  If people
> > > want to perform DML in a DDL script, let them be responsible
> > > for handling both DDL and DML commits.
> >
> > Agree. But preventing of possibility of database corruption is good to
> > have. But this is very low priority issue for now, AFAIU.
>
>I'd say "low", not "very low" ;-) Our DDL management is quite tricky
>internally and this fix should make FB more reliable. People shouldn't be
>able to corrupt databases via SQL, regardless of their responsibility.

For experienced users it could be low, but not for novice users, and if FB 
succeed then it will be a lot of novice users.

People who try to switch from other databases will not understand those 
problems and this will give a very bad publicity to FB.

And from the theoretical POV any database corruption breaks Durability (the 
D in ACID).

So, if we wish to see FB widely used and its users base expanded, the 
metadata problems needs to be solved ASAP.

Just my opinion.


>Dmitry

Blas Rodriguez Somoza

On Wed, 28 Apr 2004, "Roman Rokytskyy" <rrokytskyy@...> wrote:
> > Surely you don't mean this?  If I take you literally, it would mean
> > that every CREATE TABLE statement should be preceded and followed by a
> > full backup.
> 
> And what's wrong with this in production environment? You do not change
> schema in production too often. And when you do, you better do a backup
> before. Do I miss something?

They're two separate questions, that's all.  My production practices and
yours might differ, depending on our availability requirements and our
tolerance for data loss.  That's entirely separate from what the server
requires for reliable operation.  

FWIW, I have seen application designs that rely on regular DDL
manipulation.  I'm thinking in particular of maintaining two sets of
tables, one "production" and one "shadow".  The shadow set is updated when
the vendor sends new data, a process that may take hours and may fail due
to faults in the data.  Once the shadow tables have been updated, they're
"swapped" into production by renaming them.  

I don't use such techniques myself, but it's not unheard of.  

--jkl

> They're two separate questions, that's all.  My production practices
> and yours might differ, depending on our availability requirements and
> our tolerance for data loss.  That's entirely separate from what the
> server requires for reliable operation.

Agree. That was just an excerpt from "best practices" for metadata
update/upgrade.
> FWIW, I have seen application designs that rely on regular DDL
> manipulation.  I'm thinking in particular of maintaining two sets of
> tables, one "production" and one "shadow".  The shadow set is updated
> when the vendor sends new data, a process that may take hours and may
> fail due to faults in the data.  Once the shadow tables have been
> updated, they're "swapped" into production by renaming them.

Rename by direct manipulation of system tables in a running database?
Walking on the edge, isn't it? I wouldn't be surprised if system tables
are at some point made read-only, but that's another story. I suspect that
those people know what they do and are ready to be held responsible for
any data loss. But you are right, I've heard about such techniques before.
Personally I use dynamic table creation in my Java application. Each EJB
container is able to do this dynamically. But backing up the production
database before update/upgrade still makes sense to me.
Roman

Just to add my bit in this ...

> > > > I can't think what Nickolay thinks needs fixing about the
> > > > current situation.  IMO, it is correct (and quite clever)
> > > > that DDL and DML in scripts should run in separate
> > > > transactions;  and that designers be aware that uncommitted
> > > > metadata changes are not visible to DML statements in the same
script.
> > >
> > > Ugh. I think the limitation you propose is too tough. I often write
> > > data-driven DDL scripts and they would be broken by such limitation.
> > > From the engine POV enough restriction is to do forbid mixing DML and
> > > DDL for individual objects, i.e. forbid DML on table after you did DDL
> > > on in till commit time.
> >
> >Agreed. I thought about such a check too.
> >
> > > > Simply, I don't think it should be messed with.  If people
> > > > want to perform DML in a DDL script, let them be responsible
> > > > for handling both DDL and DML commits.
> > >
> > > Agree. But preventing of possibility of database corruption is good to
> > > have. But this is very low priority issue for now, AFAIU.
> >
> >I'd say "low", not "very low" ;-) Our DDL management is quite tricky
> >internally and this fix should make FB more reliable. People shouldn't be
> >able to corrupt databases via SQL, regardless of their responsibility.
>
> For experienced users it could be low, but not for novice users, and if FB
> succeed then it will be a lot of novice users.
>
> People who try to switch from other databases will not understand those
> problems and this will give a very bad publicity to FB.
>
> And from the theoretical POV any database corruption breaks Durability
(the
> D in ACID).
>
> So, if we wish to see FB widely used and its users base expanded, the
> metadata problems needs to be solved ASAP.

Simply put -- if mixing DML and DDL causes database
corruption: raise an exception when it happens.

Getting the database corrupted by issueing VALID statements
is plain stupid.

With regards,

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

Blas Rodriguez Somoza wrote:

> For experienced users it could be low, but not for novice users, and 
> if FB succeed then it will be a lot of novice users.
>
> People who try to switch from other databases will not understand 
> those problems and this will give a very bad publicity to FB.
>
> And from the theoretical POV any database corruption breaks Durability 
> (the D in ACID).


Thank you Blas. That is the point i wanted to put my finger on, but 
doesn't found the right words.

> So, if we wish to see FB widely used and its users base expanded, the 
> metadata problems needs to be solved ASAP.


Or, if it can't be fixed soon, at least prevent users from doing harm to 
their data. E.g. don't allow this action and return an error.
Currently, metadata changes on a fb-database which is in use, already is 
limited. One limit more or less won't make a significant.difference in 
the usability. But the lack of durability will make a big difference.

> Just my opinion.


Mine too.

cu
Jens

Wednesday, April 28, 2004, 5:49:46 PM, Martijn Tonies:

MT> Simply put -- if mixing DML and DDL causes database
MT> corruption: raise an exception when it happens.

  Martijn, this is really very simply - to check on _each_ and _every_
DML statement that in the history of it's transaction were not DDL
statements performed. Suggestions on implementation?

MT> Getting the database corrupted by issueing VALID statements
MT> is plain stupid.

  Of course. But _database_ is not corrupted. Data is. Exactly the
same way as another sclerotic person (sometimes me) can ussue Update
statement forgetting Where.

Best regards,
Alexander

Doctor, if I make this movement, I feel pain!
Well... don't make this movement...

> Wednesday, April 28, 2004, 5:49:46 PM, Martijn Tonies:
>
> MT> Simply put -- if mixing DML and DDL causes database
> MT> corruption: raise an exception when it happens.
>
>   Martijn, this is really very simply - to check on _each_ and _every_
> DML statement that in the history of it's transaction were not DDL
> statements performed. Suggestions on implementation?

Well, there are more than 1 problem. For example, when doing
a largish DDL script to create objects and so on, it would be
nice to have them all, or none at all. However, from what I've
understood, issues can arise when creating indices or FKs for
tables that haven't been COMMITted yet. From a user POV,
this doesn't make sense. Especially for updating production
databases...

> MT> Getting the database corrupted by issueing VALID statements
> MT> is plain stupid.
>
>   Of course. But _database_ is not corrupted. Data is.

Data corruption is different from database corruption? How so?

>Exactly the
> same way as another sclerotic person (sometimes me) can ussue Update
> statement forgetting Where.

Isn't a "rollback" a beautiful thing.


With regards,

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

Hi Martijn,

>> Exactly the same way as another sclerotic person (sometimes me) can
>> ussue Update statement forgetting Where.

> Isn't a "rollback" a beautiful thing.

It is. But the point is, if you do stuff like the above you usually
realize your mistake exactly half a second AFTER you hit Commit :-)


Greetings,
Paul Vinkenoog

Alexander V.Nevsky wrote:

>Wednesday, April 28, 2004, 5:49:46 PM, Martijn Tonies:
>
>MT> Simply put -- if mixing DML and DDL causes database
>MT> corruption: raise an exception when it happens.
>
>  Martijn, this is really very simply - to check on _each_ and _every_
>DML statement that in the history of it's transaction were not DDL
>statements performed. Suggestions on implementation?
>
>MT> Getting the database corrupted by issueing VALID statements
>MT> is plain stupid.
>
>  Of course. But _database_ is not corrupted. Data is. Exactly the
>same way as another sclerotic person (sometimes me) can ussue Update
>statement forgetting Where.
>  
>

That are two different things. If i forget to put a where clause on an 
update, then this will lead to a predictable corruption of data. And 
this will be predictable on every dbms. This is a commonplace, and every 
developer knows what happens in such a case.
But if i make a correct update statement and afterwards some 
unpredictable random columns are filled with wrong data that is a bug. 
For a user who is not that familiar with firebird internals, how should 
he know, that DDL and DML in the same transaction context will corrupt 
his data in that manner?

AFAIK it is not forbidden to mix those statements inside the same 
transaction context by the SQL standard, isn't it? So i think there are 
three possibilities:

1.) fix the metadata issue at all (much work, i think. May be better 
deffered to some future release)
2.) do not allow mixing of DML and DDL inside one transaction 
(eventually even to much work, but i can't estimate it)
3.) Write a big fat "known issue/limit" in the release notes (not too 
much work, i think)

To clarify, why i reply on this topic:
I've never mixed DDL and DML and i will not do it in the future. So, 
theoretically, i don't care if this bug will be fixed or not. But as i 
see how the firebird "staff" reacts on such a (in fact it was only a) 
bug report (it has some flavour of "dumb user bashing"), i fear there 
are some more "hidden" traps which i may fall into, because i'm not that 
firebird expert.

cu
Jens

Wednesday, April 28, 2004, 6:33:00 PM, Martijn Tonies wrote:

MT> Well, there are more than 1 problem. For example, when doing
MT> a largish DDL script to create objects and so on, it would be
MT> nice to have them all, or none at all.

  Agreed.

MT>  However, from what I've
MT> understood, issues can arise when creating indices or FKs for
MT> tables that haven't been COMMITted yet.

  In my practice usually if I touched by DML inside this script one of
the tables for FK before it's creation. Select from this tables for
example. Or used tool can hold more than 1 opened transaction, because
of multy-windowed interface or own internal logic making hints to user
;) AFAIU most often reason for famous "table in use" is - "master"
index should be free of any locks, read or write, to create FK. Any
access to table violates this condition. Sometimes even commited access
and we should re-connect. IMHO this mean locks are not always released
in time, and I have a doubt this can be easy and soon checked in all
code.


>> MT> Getting the database corrupted by issueing VALID statements
>> MT> is plain stupid.
>>
>>   Of course. But _database_ is not corrupted. Data is.

MT> Data corruption is different from database corruption? How so?

  My understanding of database corruption is page-level corruption -
broken index/violated constraint, lost data page, cross-referenced
page etc. In case of DML on uncommited altered table we have data
which should be placed in one column in fact is placed in another
column. Many programmer's mistakes can produce this and IMO this
does'nt mean database is corrupted.

>>Exactly the
>> same way as another sclerotic person (sometimes me) can ussue Update
>> statement forgetting Where.

MT> Isn't a "rollback" a beautiful thing.

  Yes, and for metadata changes too ;)

Best regards,
Alexander

Wednesday, April 28, 2004, 7:10:56 PM, Jens Zurawski wrote:

JZ> 1.) fix the metadata issue at all (much work, i think. May be better
JZ> deffered to some future release)
JZ> 2.) do not allow mixing of DML and DDL inside one transaction 
JZ> (eventually even to much work, but i can't estimate it)
JZ> 3.) Write a big fat "known issue/limit" in the release notes (not too 
JZ> much work, i think)

  Jens, take my signature for this. It is one of bugs in Borland's IB
documentation and not only one. And IMHO it is really not a simple task
to change this behaviour of all IB/FB versions don't making serious
impact on performance and without ODS changes. But is release notes
for new version proper place for description of missed in Borland
documentation gotchas? I really don't know. On Russain site
 http://www.ibase.ru we have section of the FAQ which is titled in uppercase
"DON'T DO IT" and place recommendations of this kind there.

JZ> But as i see how the firebird "staff" reacts on such a (in fact it
JZ> was only a) bug report

  Jens, the only one person who is among Firebird "staff" and warned
against hasty steps in this direction is Helen. I, for example am
ordinary noisy chatterer who is afraid his applications performance
will be slowed twice to protect inattentive script writers. I'll make
more efforts to keep silence :)

Best regards,
Alexander

> MT> Well, there are more than 1 problem. For example, when doing
> MT> a largish DDL script to create objects and so on, it would be
> MT> nice to have them all, or none at all.
> 
>   Agreed.
> 
> MT>  However, from what I've
> MT> understood, issues can arise when creating indices or FKs for
> MT> tables that haven't been COMMITted yet.
> 
>   In my practice usually if I touched by DML inside this script one of
> the tables for FK before it's creation. Select from this tables for
> example. Or used tool can hold more than 1 opened transaction, because
> of multy-windowed interface or own internal logic making hints to user
> ;) AFAIU most often reason for famous "table in use" is - "master"
> index should be free of any locks, read or write, to create FK. Any
> access to table violates this condition. Sometimes even commited access
> and we should re-connect. IMHO this mean locks are not always released
> in time, and I have a doubt this can be easy and soon checked in all
> code.

When this is from the same transaction [doing DML and
metadata changes], I think this _should_ be possible.

> >> MT> Getting the database corrupted by issueing VALID statements
> >> MT> is plain stupid.
> >>
> >>   Of course. But _database_ is not corrupted. Data is.
> 
> MT> Data corruption is different from database corruption? How so?
> 
>   My understanding of database corruption is page-level corruption -
> broken index/violated constraint, lost data page, cross-referenced
> page etc. In case of DML on uncommited altered table we have data
> which should be placed in one column in fact is placed in another
> column. 

I think this is a database corruption cause its not a logical mistake
[by the programmer]. Even if the table has an uncommitted column
(added), and the server accepts it that I put data in it, then there
should be data in it. This is not a logical mistake - if the server
behaves otherwise, it's a Firebird error.

>Many programmer's mistakes can produce this and IMO this
> does'nt mean database is corrupted.


> >>Exactly the
> >> same way as another sclerotic person (sometimes me) can ussue Update
> >> statement forgetting Where.
> 
> MT> Isn't a "rollback" a beautiful thing.
> 
>   Yes, and for metadata changes too ;)

In that case, the darn thing should be fixed :-)

With regards,

Martijn Tonies

Wednesday, April 28, 2004, 7:59:37 PM, Martijn Tonies wrote:

MT> When this is from the same transaction [doing DML and
MT> metadata changes], I think this _should_ be possible.

  Martijn, nor I neither you have'nt enough understanding of modules
interrelations, calls, cross and recursive calls etc, so I suggest
private conversations if you have interest to chat with me on this
subject ;) You know, one clever guy recently answered in fb-support on
similar question (AFAIU to prevent long senseless flame) "Because
adding a FK will raise an error if it isn't an exclusive connection."
;) 

Best regards,
Alexander

I like the way it is now (server allows mix of DML and DDL) because I send
updates to my customers this way (lucky me, I have never get corrupted
data). I think that it should be fixed but, as someone said, this is very
hard to do that, then, as another one said, add a warning on release notes
on this subject.

I hope I dont forget it in the next database upgrade I sent to my customers.

Alexander V.Nevsky wrote:

>Wednesday, April 28, 2004, 7:10:56 PM, Jens Zurawski wrote:
>
>JZ> 1.) fix the metadata issue at all (much work, i think. May be better
>JZ> deffered to some future release)
>JZ> 2.) do not allow mixing of DML and DDL inside one transaction 
>JZ> (eventually even to much work, but i can't estimate it)
>JZ> 3.) Write a big fat "known issue/limit" in the release notes (not too 
>JZ> much work, i think)
>
>  Jens, take my signature for this. It is one of bugs in Borland's IB
>documentation and not only one. And IMHO it is really not a simple task
>to change this behaviour of all IB/FB versions don't making serious
>impact on performance and without ODS changes. 
>

Ok, i'm not in the place to say how much work it is and how much impact 
the fix will have on performance. I'll accept the argument, that a quick 
fix of that issue is not "quick" at all and/or will result in 
unacceptable performance loss. But then i think a warning in an adequat 
"DON'T DO IT" section is essential. And apart from that, a mid term 
solution for this issue is more than low priority IMHO. Regardless of 
the fact that this issue is introduced by Borland, Firebird takes the 
legacy from IB. Firebird inherits not only the good stuff, it also 
inherits the responsibility. At least if it will be a serious 
alternative to IB.

>But is release notes
>for new version proper place for description of missed in Borland
>documentation gotchas? I really don't know. On Russain site
>www.ibase.ru we have section of the FAQ which is titled in uppercase
>"DON'T DO IT" and place recommendations of this kind there.
>  
>

Unfortunatly i speak no russian. And reading russian is also somewaht 
unsatisfying ;-) It don't have to be the realease notes, but some 
document which is in the release package.

>JZ> But as i see how the firebird "staff" reacts on such a (in fact it
>JZ> was only a) bug report
>
>  Jens, the only one person who is among Firebird "staff" and warned
>against hasty steps in this direction is Helen. I, for example am
>ordinary noisy chatterer who is afraid his applications performance
>will be slowed twice to protect inattentive script writers. I'll make
>more efforts to keep silence :)
>  
>

Ok, i'll get trapped by the charm and the well-known gentleness of Helen 
and the "very low priority" from Nickolay. I'll confess more calmness in 
the future ;-) I'll don't expect you to be more silent.

cu
Jens

> To clarify, why i reply on this topic:
> I've never mixed DDL and DML and i will not do it in the future. So, 
> theoretically, i don't care if this bug will be fixed or not. But as i 
> see how the firebird "staff" reacts on such a (in fact it was only a) 
> bug report (it has some flavour of "dumb user bashing"), i fear there 
> are some more "hidden" traps which i may fall into, because i'm not that 
> firebird expert.

That exactly how I feel. 
Submitting bug reports is getting pretty scary!
Regards
Gary Benade

> Especially if to satisfy user's sclerosis FB will be forced to spend
> noticeable time to validate all metadata related to any DML statement.

> I, for example am
> ordinary noisy chatterer who is afraid his applications performance
> will be slowed twice to protect inattentive script writers

Alexander

I hardly think this bug should cause me to be cast in the rather charming
light you have cast.

Its a bug. Thats all. I have been doing what I do for a long time. I hope I
have saved some other poor soul who has deadlines to meet the trouble of
figuring out what happend to his data one day when he or she makes the same
mistake. Diagnosing the cause was not an easy task, but I did it anyway, for
evrybody elses benefit, not mine, because I will never make this "mistake"
again.

Next time I discover a bug I might not be so keen to take the precious time
to bring it to the attention of the developers because of thoughtless people
like yourself.

And no, you dont get a :)

Yours,
Gary Benade

Wednesday, April 28, 2004, 11:50:06 PM, atlantic wrote:

a> I hardly think this bug should cause me to be cast in the rather charming
a> light you have cast.

   I have'nt intention to cast anybody in any light. Sorry, I did'nt
used smiles and confused even my old friend Martijn.

a> Its a bug. Thats all. I have been doing what I do for a long time. I hope I
a> have saved some other poor soul who has deadlines to meet the trouble of
a> figuring out what happend to his data one day when he or she makes the same
a> mistake. Diagnosing the cause was not an easy task, but I did it anyway, for
a> evrybody elses benefit, not mine, because I will never make this "mistake"
a> again.

   Unfortunately, it's a feature. And it was there since IB creation.
And I knew about it only stepping on it too 8 years ago. Wrong
conception - perhaps. Not documented - yes. And you could easy save
much of your time if asked in fb-support first time when you feel
something is wrong. Warning on this subject is given at least monthly
there and several times by me too.

a> Next time I discover a bug I might not be so keen to take the precious time
a> to bring it to the attention of the developers because of thoughtless people
a> like yourself.

a> And no, you dont get a :)

   As you wish. I understand your frustration stepping on this rake and
respect persons who prefer discover as much as possible themselves
before asking in support list. And will try to avoid humor in the
future to don't provoke off-topic messages here.

Best regards,
Alexander

James K. Lowden wrote:

>On Wed, 28 Apr 2004, Paul Schmidt <wogsterca@...> wrote:
>  
>
>>Somone applied DDL changes to a live database while it was being used.  
>>To make it reliable, this is what should happen:
>>
>>Full backup of database, BEFORE the DDL changes are added.
>>Put database into shutdown or maintenance mode, meaning only SYSDBA can 
>>access it.
>>Apply DDL changes.
>>Commit DDL changes.
>>Take database out of maintenance mode.
>>Install new version of software on one computer, do some tests of most 
>>important functions to make sure nothing goes tilt.
>>Install new software on remaining computers.
>>    
>>
>
>Surely you don't mean this?  If I take you literally, it would mean that
>every CREATE TABLE statement should be preceded and followed by a full
>backup.  To make it realiable, that is, for some value of reliable.  
>
>Or maybe I don't get the joke....
>
>  
>
No, just preceeded, no need for a backup after, but it isn't always a 
bad idea if the updates are time consuming or require lots of little 
steps be accomplished to get them installed.  When your dealing with 
production data, your dealing with the potential of losing millions if 
not billions of dollars worth of data, not something you want to 
undertake lightly.  

Paul

Paul Schmidt wrote:

> James K. Lowden wrote:
>
>> On Wed, 28 Apr 2004, Paul Schmidt <wogsterca@...> wrote:
>>  
>>
>>> Somone applied DDL changes to a live database while it was being 
>>> used.  To make it reliable, this is what should happen:
>>>
>>> Full backup of database, BEFORE the DDL changes are added.
>>> Put database into shutdown or maintenance mode, meaning only SYSDBA 
>>> can access it.
>>> Apply DDL changes.
>>> Commit DDL changes.
>>> Take database out of maintenance mode.
>>> Install new version of software on one computer, do some tests of 
>>> most important functions to make sure nothing goes tilt.
>>> Install new software on remaining computers.
>>>   
>>
>>
>> Surely you don't mean this?  If I take you literally, it would mean that
>> every CREATE TABLE statement should be preceded and followed by a full
>> backup.  To make it realiable, that is, for some value of reliable. 
>> Or maybe I don't get the joke....
>>
>>  
>>
> No, just preceeded, no need for a backup after, but it isn't always a 
> bad idea if the updates are time consuming or require lots of little 
> steps be accomplished to get them installed.  When your dealing with 
> production data, your dealing with the potential of losing millions if 
> not billions of dollars worth of data, not something you want to 
> undertake lightly. 
> Paul
>
He is saying if you run 5 DDL statements, you should issue a commit 
after each one.  Not just
one at the end. 

-- 
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com
http://sourceforge.net/projects/dbexpressplus

On Wed, 28 Apr 2004, "Alexander V.Nevsky" <ded@...> wrote:
> But is release notes
> for new version proper place for description of missed in Borland
> documentation gotchas? 

Absolutely, for the purpose of drawing attention to it.  It should also be
in the main documentation, when that day arrives.  Not many people
consulting documentation care about who owned the code when such-and-such
a bug/feature was introduced.  

--jkl

Thomas Miller wrote:

>
>
> Paul Schmidt wrote:
>
>> James K. Lowden wrote:
>>
>>> On Wed, 28 Apr 2004, Paul Schmidt <wogsterca@...> wrote:
>>>  
>>>
>>>> Somone applied DDL changes to a live database while it was being 
>>>> used.  To make it reliable, this is what should happen:
>>>>
>>>> Full backup of database, BEFORE the DDL changes are added.
>>>> Put database into shutdown or maintenance mode, meaning only SYSDBA 
>>>> can access it.
>>>> Apply DDL changes.
>>>> Commit DDL changes.
>>>> Take database out of maintenance mode.
>>>> Install new version of software on one computer, do some tests of 
>>>> most important functions to make sure nothing goes tilt.
>>>> Install new software on remaining computers.
>>>>   
>>>
>>>
>>>
>>> Surely you don't mean this?  If I take you literally, it would mean 
>>> that
>>> every CREATE TABLE statement should be preceded and followed by a full
>>> backup.  To make it realiable, that is, for some value of reliable. 
>>> Or maybe I don't get the joke....
>>>
>>>  
>>>
>> No, just preceeded, no need for a backup after, but it isn't always a 
>> bad idea if the updates are time consuming or require lots of little 
>> steps be accomplished to get them installed.  When your dealing with 
>> production data, your dealing with the potential of losing millions 
>> if not billions of dollars worth of data, not something you want to 
>> undertake lightly. Paul
>>
> He is saying if you run 5 DDL statements, you should issue a commit 
> after each one.  Not just
> one at the end.

Actually he didn't say that, he said even creating a table, he didn't 
state whether he was creating 5 tables, 25 tables or 1 table.  If you 
look at my initial response, I stated the steps as being backup, remove 
database from production use, apply changes, commit changes, run some 
tests, reenable database.  This could be one DDL statement or 65,536 DDL 
statements.    Personally I would create all of the tables, commit, 
create all of the generators, commit, create the triggers, commit, 
create extranious indexes, commit.  If your using foreign keys (I 
usually don't), then commit each table  so that the foreign keys don't 
run into trouble.  Mind you, by the time it gets to updating the 
production database, you have tested the changes on a test copy of the 
database.  The real key here is that you don't touch production without 
knowing that what your doing is going to work.  The backup is a just in 
case process. 

Paul

Paul Schmidt wrote:
> ...  Mind you, by the time it gets to updating the 
> production database, you have tested the changes on a test copy of the 
> database.  The real key here is that you don't touch production without 
> knowing that what your doing is going to work.  The backup is a just in 
> case process.
> Paul

So what you are saying is that I have to:

Remotely logon on to every one of our 120+ customers and:

1) Backup the database.
2) Restore a test dabase.
3) Test changes on the testdatabase.
4) and if OK, apply to production database.

If 1-4 takes 30 min (our customes database is >2G in size), I have to 
allocate 60 hours, and since it is running in production, I have to do 
it during sleeping hour.

Mind you, that I have tested my script with success on my own database, 
and then it later failed on a customers database.

I have learn my lesson. Every single ';' is followed by commit, but to 
me, it is still a bug.

Please warn people like me in the release notes "DON'T DO THIS!".

Brian Vraamark wrote:

> Paul Schmidt wrote:
>
>> ...  Mind you, by the time it gets to updating the production 
>> database, you have tested the changes on a test copy of the 
>> database.  The real key here is that you don't touch production 
>> without knowing that what your doing is going to work.  The backup is 
>> a just in case process.
>> Paul
>
>
> So what you are saying is that I have to:
>
> Remotely logon on to every one of our 120+ customers and:
>
> 1) Backup the database.
> 2) Restore a test dabase.
> 3) Test changes on the testdatabase.
> 4) and if OK, apply to production database.
>
> If 1-4 takes 30 min (our customes database is >2G in size), I have to 
> allocate 60 hours, and since it is running in production, I have to do 
> it during sleeping hour.
>
> Mind you, that I have tested my script with success on my own 
> database, and then it later failed on a customers database.
>
> I have learn my lesson. Every single ';' is followed by commit, but to 
> me, it is still a bug.
>
> Please warn people like me in the release notes "DON'T DO THIS!".

I think it is a bug too and the FB should do a Commit after every DDL 
statement.  I don't have this problem with Oracle
because I believe that is exactly what it does.

-- 
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com
http://sourceforge.net/projects/dbexpressplus

Thursday, April 29, 2004, 10:10:26 PM, Brian Vraamark wrote:

BV> Mind you, that I have tested my script with success on my own database,
BV> and then it later failed on a customers database.

BV> I have learn my lesson. Every single ';' is followed by commit, but to 
BV> me, it is still a bug.

BV> Please warn people like me in the release notes "DON'T DO THIS!".

  Brian and All, let's put all in proper place and stop noise here.

1. Metadata changes should be made in exlusive access mode, like we it
or not.
2. Script (not a statements within it but script as a whole) should be
tested on developer's database.
3. It is recommended to have restorable backup before performing
script on production database. Not necessary to sit and wait it's
finish subsequently for every customer, we can write batch which
perform backup, perform script and on errors call us. If you are
pretty sure customer's database is clean from corruptions or tons of
garbage which can disturb script execution, or changes are small
enough and you are sure you can repair possible damage, you can don't
make backup, reasonability of the risk level is part of your
professionalism.

  All said above is support area and common sense area. If you think
developers of databases on Oracle can recklessly make what they want
with their databases, you are mistaken. AFAIK, it perform DDL in
separated autocommited transactions, in FB this is optional within
isql utility. As a reward we can roll back portion of DDL if turn this
option off.

4. It is not necessary commit after each statement, but DDL changes
should be commited before you perform DML statements related to
changed objects. If script can't be separated into two parts, DML
after DDL, but is mix of DDL and DML segments, each segment should be
commited before performing next. I'm not sure how another database
engines reacts on opposite, but in general it is in area of common
sense for me too. 

5. First specific FB-related point - in the section of metadata
changes Foreign Keys can be created only if changes of both tables are
commited. I will be glad if this will be changed somewhere in time but
I can live with it and can't call this "bug".

6. The only real bug is - all is commited but FK can't be created and
we are forced to reconnect.

  BTW, if script passed test on developer's database, it can fail on
customer's one in case if database is already corrupted or is
performed not in exclusive mode.
  Personally my conclusion. I don't see tragedy. Some inconvenience,
yes. World is'nt perfect place and can and should be improved by
chances step by step. And seems idea to include into distributive "DO
NOT DO IT" is good. But not in Release Notes, in separated file. But
it should be discussed in another list IMO and prepared not by
developers team, they have enough work.

Best regards,
Alexander

Hi,

This is just my 2 cents.

At April 29, 2004, 15:23, Thomas Miller wrote:

> I think it is a bug too and the FB should do a Commit after every DDL
> statement.  I don't have this problem with Oracle
> because I believe that is exactly what it does.

Here's what is mentioned in the SQL-2003 standards:
[Start Quote...]
An SQL-transaction (transaction) is a sequence of executions of
SQL-statements that is atomic with respect to recovery. That is to
say: either the execution result is completely successful, or it has
no effect on any SQL-schemas(DDL) or SQL-data(DML).

If there is no current SQL-transaction, execution of a
transaction-initiating statement will initiate one.

Every SQL-transaction is terminated by either a commit statement or a
rollback statement. The execution of either of these statements may be
implicit.

An SQL-transaction has a transaction state. Certain properties of the
transaction state are set by the execution of SQL-statements. Such
SQL-statements may be executed only when there is no SQL-transaction
current. On the first occasion, at or after a transaction is
initiated, that the SQL-client connects to, or sets the connection to,
an SQL-server, the properties are sent to that SQL-server.
[...End Quote]

It might be that when a DML statement is executed, it would have to
check if there is an uncommitted DDL statement that precedes it. And,
if the condition exists, automatically perform a COMMIT and start a
new transaction. And, the inverse should be implemented as well(when a
DDL statement is encountered check for DML preceding statement). But,
this would probably mean a change in the API so that the calling
application would get the new transaction handle when this occurs.

-- 
Best regards,
 Daniel Rail
 Senior System Engineer
 ACCRA Group Inc. (www.accra.ca)
 ACCRA Med Software Inc. (www.filopto.com)

Hi, Alexander!

> 6. The only real bug is - all is commited but FK can't be=20
> created and we are forced to reconnect.

Do you have a testcase for this? I thought I fixed all this crap in
1.5.0 already.

MET_clear_cache does circular dependency scan pass and is capable to
release all unused metadata references, but maybe I forget to call it in
a place or two.
This routine is the heavy hammer used for indices management because MET
doesn't maintain enough information to reconstruct usage graph for
index. We call this routine when DFW attempts to perform metadata
operation on index with non-zero reference count. Procedures use more
fine-tuned approach of circular dependency tracking implemented in
MET_procedure_in_use.

> Alexander

Nickolay

Thursday, April 29, 2004, 11:21:22 PM, Daniel Rail wrote:

DR> It might be that when a DML statement is executed, it would have to
DR> check if there is an uncommitted DDL statement that precedes it. And,
DR> if the condition exists, automatically perform a COMMIT and start a
DR> new transaction. And, the inverse should be implemented as well(when a
DR> DDL statement is encountered check for DML preceding statement). But,
DR> this would probably mean a change in the API so that the calling
DR> application would get the new transaction handle when this occurs.

  Daniel, IMO it is
  
a) inefficient because each statement must check history of entire
transaction
b) incovenient, because takes away opportunity to commit/rollback DDL
blocks
c) breaks many existing applications
d) dangerous in the sense of mentioned "inverse" - DML goes out of
client control on commit/rollback.

  IMO the best solution was suggested by Dmitry - ODS change. Mark
changed records in system tables, clear mark on commit/rollback and
check mark on prepare of DML, this record will be visited anyway.

Best regards,
Alexander

Thursday, April 29, 2004, 11:36:35 PM, Samofatov, Nickolay wrote:

>> 6. The only real bug is - all is commited but FK can't be
>> created and we are forced to reconnect.

SN> Do you have a testcase for this? I thought I fixed all this crap in
SN> 1.5.0 already.

   Nickolay, I for a long time did'nt made mass metadata changes, so
can't speak for sure. I'll hammer test database tomorrow on 1.5.1 and
let you know.

Best regards,
Alexander

Hi, Alexander!

> >> 6. The only real bug is - all is commited but FK can't be=20
> created and=20
> >> we are forced to reconnect.
>=20
> SN> Do you have a testcase for this? I thought I fixed all=20
> this crap in=20
> SN> 1.5.0 already.
>=20
>    Nickolay, I for a long time did'nt made mass metadata=20
> changes, so can't speak for sure. I'll hammer test database=20
> tomorrow on 1.5.1 and let you know.

Ok. BTW, I plan to rebuild prerelease Linux builds today.

> Alexander

Nickolay

Isn't possible to create a flag for each transaction?

When a transaction starts, the flag value is DML, so any DML can be
executed. After a DDL this flag changes to DDL, so engine raises an
exception when executing DML. I'm not a core developer, but I think checking
transaction flag  for each statement won't drop the performance so much.

With regards,
David

""Alexander V.Nevsky"" <ded@...> escreveu na mensagem
news:938702661.20040429233811@......
> Thursday, April 29, 2004, 11:21:22 PM, Daniel Rail wrote:
>   Daniel, IMO it is
>
> a) inefficient because each statement must check history of entire
> transaction
> b) incovenient, because takes away opportunity to commit/rollback DDL
> blocks
> c) breaks many existing applications
> d) dangerous in the sense of mentioned "inverse" - DML goes out of
> client control on commit/rollback.

Hi,

At April 29, 2004, 16:38, Alexander V.Nevsky wrote:

> Thursday, April 29, 2004, 11:21:22 PM, Daniel Rail wrote:

DR>> It might be that when a DML statement is executed, it would have to
DR>> check if there is an uncommitted DDL statement that precedes it. And,
DR>> if the condition exists, automatically perform a COMMIT and start a
DR>> new transaction. And, the inverse should be implemented as well(when a
DR>> DDL statement is encountered check for DML preceding statement). But,
DR>> this would probably mean a change in the API so that the calling
DR>> application would get the new transaction handle when this occurs.

>   Daniel, IMO it is
  
> a) inefficient because each statement must check history of entire
> transaction
> b) incovenient, because takes away opportunity to commit/rollback DDL
> blocks
> c) breaks many existing applications
> d) dangerous in the sense of mentioned "inverse" - DML goes out of
> client control on commit/rollback.

>   IMO the best solution was suggested by Dmitry - ODS change. Mark
> changed records in system tables, clear mark on commit/rollback and
> check mark on prepare of DML, this record will be visited anyway.

Actually, I was just outlining what it would involve if it were to be
implemented. BUT, I totally agree with you(and Dmitry) and I should've
mentioned what my position on the subject is.

It is also good to see some alternatives and be able to study and
select which one makes sense to implement.

-- 
Best regards,
 Daniel Rail
 Senior System Engineer
 ACCRA Group Inc. (www.accra.ca)
 ACCRA Med Software Inc. (www.filopto.com)

Alexander V.Nevsky wrote:

>  Brian and All, let's put all in proper place and stop noise here.
>
>1. Metadata changes should be made in exlusive access mode, like we it
>or not.
>
No, it should be always be safe to perform metadata updates at any 
time.  I'm not necessarily saying that it is, but that it should be, and 
if it isn't, then it's a bug.

>3. It is recommended to have restorable backup before performing
>script on production database. Not necessary to sit and wait it's
>finish subsequently for every customer, we can write batch which
>perform backup, perform script and on errors call us. 
>
This may be good practice, but it shouldn't be necessary.  Metadata 
updates should be safe.  Any script can fail because of user error, but 
it should always be possible to fix the error and continue.

The converse -- depending on metadata updates to remain pending between 
DDL statements -- is not safe.

>If you are
>pretty sure customer's database is clean from corruptions or tons of
>garbage which can disturb script execution, or changes are small
>enough and you are sure you can repair possible damage, you can don't
>make backup, reasonability of the risk level is part of your
>professionalism.
>
Again, that may be good DBA practice, but the original stated goal of 
Interbase was to make DBAs unnecessary.

>5. First specific FB-related point - in the section of metadata
>changes Foreign Keys can be created only if changes of both tables are
>commited. I will be glad if this will be changed somewhere in time but
>I can live with it and can't call this "bug".
>
It sounds like a bug, it smells like a bug, it ain't a feature, so I'd 
call it a bug.

-- 

Jim Starkey
Netfrastructure, Inc.
978 526-1376

By the way, why posting from Atkin take so long? I've posted a message 3
days ago and just arrived today...

With regards,
David

""Alexander V.Nevsky"" <ded@...> escreveu na mensagem
news:938702661.20040429233811@......
> Thursday, April 29, 2004, 11:21:22 PM, Daniel Rail wrote:

Brian Vraamark wrote:

> Paul Schmidt wrote:
>
>> ...  Mind you, by the time it gets to updating the production 
>> database, you have tested the changes on a test copy of the 
>> database.  The real key here is that you don't touch production 
>> without knowing that what your doing is going to work.  The backup is 
>> a just in case process.
>> Paul
>
>
> So what you are saying is that I have to:
>
> Remotely logon on to every one of our 120+ customers and:
>
> 1) Backup the database.
> 2) Restore a test dabase.
> 3) Test changes on the testdatabase.
> 4) and if OK, apply to production database.
>
> If 1-4 takes 30 min (our customes database is >2G in size), I have to 
> allocate 60 hours, and since it is running in production, I have to do 
> it during sleeping hour. 

Okay, suppose you decide not to do it, you just log into your customers  
database, and make your changes, oh oh, something just blew up in your 
face, your customer is now dead in the water, their business can't 
function because it depends on your software, they now have  their 
employees sitting on their hands for a week, which you try and put the 
pieces back together.  The customer loses $150,000 in wages and 
$4,000,000 in sales, not to mention that they will lose part of their 
reputation and customer base.   They are going to do one thing, call 
their lawyer, unless your contract is as solid as a concrete outhouse,  
they are going to come after you, looking for compensation.   How long 
can your business survive if you need to pay $20,000,000 to an 
ex-customer when half an hour's work would have prevented it.

Paul





>
>
> Mind you, that I have tested my script with success on my own 
> database, and then it later failed on a customers database.
>
> I have learn my lesson. Every single ';' is followed by commit, but to 
> me, it is still a bug.
>
> Please warn people like me in the release notes "DON'T DO THIS!".
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: Oracle 10g
> Get certified on the hottest thing ever to hit the market... Oracle 
> 10g. Take an Oracle 10g class now, and we'll give you the exam FREE.
> http://ads.osdn.com/?ad_id=3149&alloc_id=8166&op=click
> Firebird-Devel mailing list, web interface at 
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>

On Thu, 29 Apr 2004, Jim Starkey <jas@...> wrote:
> Metadata 
> updates should be safe.  Any script can fail because of user error, but 
> it should always be possible to fix the error and continue.
> 
> The converse -- depending on metadata updates to remain pending between 
> DDL statements -- is not safe.

Could you please clarify that?  By "not safe", do you mean according to
SQL-92, or by design, or according to the current implementation?  Or do
you mean just that it's generally bad practice?  

Coming from Sybase with its write-ahead log, I'm accustomed to thinking of
DDL and DML as being pretty much interchangeable from a commit/rollback
perspective.  AFAIK, that's consistent with the standard.  

--jkl

Paul Schmidt wrote:
> Okay, suppose you decide not to do it, you just log into your customers  
> database, and make your changes, oh oh, something just blew up in your 
> face, your customer is now dead in the water, their business can't 
> function because it depends on your software, they now have  their 
> employees sitting on their hands for a week, which you try and put the 
> pieces back together.  The customer loses $150,000 in wages and 
> $4,000,000 in sales, not to mention that they will lose part of their 
> reputation and customer base.   They are going to do one thing, call 
> their lawyer, unless your contract is as solid as a concrete outhouse,  
> they are going to come after you, looking for compensation.   How long 
> can your business survive if you need to pay $20,000,000 to an 
> ex-customer when half an hour's work would have prevented it.
> 
> Paul

1) Not half an hour. Half an hour x 120. We hope to reach 300 customers 
within the next 2 years.

2) We have insurrence. We pay BIG BUCKS for it. That dosen't mean that 
we LIKE corrupting databases, but mixing DML and DDL in Firebird is one 
of the reasons why we have insurrence (I am human, I make mistakes!).

3) Our customers is dentists. They are supposed to make teeth, not 
working as some super DBA. For years I have been told that 
Interbase/Firebird dosen't need a DBA? Are you saying that this is not true?

In our product the dentist calls function A then function B. In your 
world the dentist can call function B then function A, resulting in 
crashing the system and we call it a feature. I would prefere an error 
message!

You are right. I don't want an ex-customer when half an hour's work 
would have prevented it. BUT I DID NOT KNOW mixing DML and DDL would 
kill my database (I do now :-) ).

I still think it is a BUG, but if the firebird developers says it is a 
feature, it is OK with me. They are making my business possible and I am 
only paying a small (not enough :-) ) amount through my membership.

Brian

> When a transaction starts, the flag value is DML, so any DML can be
> executed. After a DDL this flag changes to DDL, so engine raises an
> exception when executing DML. I'm not a core developer, but I think
checking
> transaction flag  for each statement won't drop the performance so much.

David, you may be on to something there.

- Flag starts as DML, so any DDL or DML can be executed withing that
transaction
- When a DML is performed, flag changes so that only more DML can be
performed, no DDL

The only question is, what happens if DDL is performed and then a DML? Would
metadata changes on a table with uncommited data changes cause the same
problem? If so, we sit with the following situation:

- Flag starts as NULL, so any DDL or DML can be executed withing that
transaction

- When a DML is performed:
if flag is NULL, flag sets to DML.
if flag is DML operation succeeds.
if flag is DDL exception is raised.

- When a DDL is performed:
if flag is NULL, flag sets to DDL.
if flag is DDL operation succeeds.
if flag is DML exception is raised.

This essentially means that a transaction would be either DDL or DML based
on the first script.
In either case, at least an error can be raised and the data integrity would
not be compromised

Regards
Gary Benade

> This essentially means that a transaction would be either DDL or DML based
> on the first script.
> In either case, at least an error can be raised and the data integrity
would
> not be compromised

This may be too restrictive. To keep from breaking existing logic the state
flags would have to be on a  table level so that a DML and a DDL could be
performed in the same transaction providing they access different tables.

Regards
Gary Benade

Friday, April 30, 2004, 12:05:00 AM, Samofatov, Nickolay wrote:

>> >> 6. The only real bug is - all is commited but FK can't be
>> created and 
>> >> we are forced to reconnect.
>> 
>> SN> Do you have a testcase for this? I thought I fixed all 
>> this crap in 
>> SN> 1.5.0 already.
>> 
>>    Nickolay, I for a long time did'nt made mass metadata 
>> changes, so can't speak for sure. I'll hammer test database 
>> tomorrow on 1.5.1 and let you know.

   Congratulations. Could'nt reach this making capricious mixes of DDL
and DML commited in time in exclusive mode. Even creating circular
references like T1 <- T2 <- T3 <- T1. BTW, my sclerosis whispers to me
that this was impossible on FB1 but last time it often fools me and I
already have'nt FB1 to check this. Had'nt checked restorability of such
a structures too, I'm not so perverted to use this in real life :)

SN> Ok. BTW, I plan to rebuild prerelease Linux builds today.

   As you perhaps remember from previous life, I can't participate in
testing until 5 May ;)

Best regards,
Alexander

David Y. Bonaf=E9 wrote:

>Isn't possible to create a flag for each transaction?
>
>When a transaction starts, the flag value is DML, so any DML can be
>executed. After a DDL this flag changes to DDL, so engine raises an
>exception when executing DML. I'm not a core developer, but I think chec=
king
>transaction flag  for each statement won't drop the performance so much.
> =20
>
It almost certainly is not a performance problem.  It is, however, what=20
is known in the business as a "wart on a wart".  It is also impossible=20
to implement because the engine itself must perform DML operations to=20
perform the DDL request.

Transaction states are generally bad.  Ephemeral transaction states are=20
worse.  Batching DDL statements to take advantage of transient,=20
incomplete execution is next to impossible to design, and puts all sorts=20
of restrictions on future development rightfully constrained to preserve=20
past achitecturally sanctioned behaviors.  It would also require=20
different transaction semantics for DML operations emiminating from=20
clients and the engine itself.

Define work exists solely to support direct update of system tables in=20
which a single logical DDL operation is necessarily performed by a=20
sequence of DML operations.  If system tables are made client read-only,=20
there is no reason to making DDL operatings under transaction control.

--=20

Jim Starkey
Netfrastructure, Inc.
978 526-1376

Friday, April 30, 2004, 6:12:20 PM, Jim Starkey wrote:

JS> If system tables are made client read-only,
JS> there is no reason to making DDL operatings under transaction control.

   Is'nt opportunity to rollback DDL a reason? BTW, I don't understand
internal machinery, but perhaps marking object altered or created within
transaction context by different marks can solve problem of creation
FK on newly created table references on another newly created table
when both are not commited too - we know both tables are empty and
perhaps this can be used to miss some steps which are necessary for
old tables?

Best regards,
Alexander

Alexander V.Nevsky wrote:

>Friday, April 30, 2004, 6:12:20 PM, Jim Starkey wrote:
>
>JS> If system tables are made client read-only,
>JS> there is no reason to making DDL operatings under transaction control.
>
>   Is'nt opportunity to rollback DDL a reason? BTW, I don't understand
>internal machinery, but perhaps marking object altered or created within
>transaction context by different marks can solve problem of creation
>FK on newly created table references on another newly created table
>when both are not commited too - we know both tables are empty and
>perhaps this can be used to miss some steps which are necessary for
>old tables?
>  
>
How often do you rollback a DDL statement?  This is just nonsense.  The 
right approach
is to commit all DDL statements as they happen.  If you write a bad DDL 
statement
on a database you haven't backup, then shame on you.  There are so many 
other things
that really need this energy, and this isn't it.  Corrupting database vs 
rollback DDL is
a no brainer decision.  I don't want my database accidently corrupted, 
period.

-- 
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com
http://sourceforge.net/projects/dbexpressplus

Friday, April 30, 2004, 6:48:09 PM, Thomas Miller wrote:

TM> How often do you rollback a DDL statement?

  Regularly. When debugging scripts.

TM>   This is just nonsense.  The
TM> right approach
TM> is to commit all DDL statements as they happen.  If you write a bad DDL 
TM> statement
TM> on a database you haven't backup, then shame on you.

  On production database - yes. On developer's - you never make
mistakes? Though, having modern hard drives we usually can make copy
before DDL debugging. Restore is'nt option for me, I can't wait for 4
hours.

TM>   There are so many
TM> other things
TM> that really need this energy, and this isn't it.

  I'm confused. Are you argueing to don't touch this can of worms and
let it live as it lives now? ;)

TM>   Corrupting database vs 
TM> rollback DDL is
TM> a no brainer decision.  I don't want my database accidently corrupted, 
TM> period.

  Solid argue. But I thought we are searching solution which can
support both.

Best regards,
Alexander

Friday, April 30, 2004, 12:45:26 AM, Daniel Rail wrote:

>> Thursday, April 29, 2004, 11:21:22 PM, Daniel Rail wrote:

DR> It is also good to see some alternatives and be able to study and
DR> select which one makes sense to implement.

  Sertainly, and serious discussion of advantages and disadvantages of
alternatives is necessary for this selection.

offtopic on:

  I have a feeling I often unwillingly insult people in
English-speaking forums. In technical discussion I praise solution of
my bitterest enemy if I like it and abuse solution of my closest
friend if not. For me it is normal. Well, I notched "don't say must,
say should" but don't understand this - no one Russian being sober
will not build personal sentense like "you should make this so" when
it can be said impersonally like "this need to be done so" and we have
only one verb of this kind. If I make another mistakes of this kind,
Daniel, or anyone (why I write this here and not in private), please,
let me know privately.

offtopic off

  Thanks for your patience.

Best regards,
Alexander

Alexander V.Nevsky wrote:

>Friday, April 30, 2004, 6:48:09 PM, Thomas Miller wrote:
>
>TM> How often do you rollback a DDL statement?
>
>  Regularly. When debugging scripts.
>
>TM>   This is just nonsense.  The
>TM> right approach
>TM> is to commit all DDL statements as they happen.  If you write a bad DDL 
>TM> statement
>TM> on a database you haven't backup, then shame on you.
>
>  On production database - yes. On developer's - you never make
>mistakes? 
>
Just about never.  I don't believe in scripts.  We break our scripts 
into little objects.  So if
I have to change something, I only need to test one object, not the 
whole script.

>Though, having modern hard drives we usually can make copy
>before DDL debugging. Restore is'nt option for me, I can't wait for 4
>hours.
>  
>
We have a database just for this purpose.  We don't care if it goes 
bad.  We have a copy of the
file.  We delete the bad file and copy the original.  Back to testing in 
3 or 4 minutes.

>TM>   There are so many
>TM> other things
>TM> that really need this energy, and this isn't it.
>
>  I'm confused. Are you argueing to don't touch this can of worms and
>let it live as it lives now? ;)
>  
>
No.  I am saying for such a small feature, make it reliable now, and go 
back and revisit it later when
other more important things still need to be added / fixed.  Change it 
to commit each now.

>TM>   Corrupting database vs 
>TM> rollback DDL is
>TM> a no brainer decision.  I don't want my database accidently corrupted, 
>TM> period.
>
>  Solid argue. But I thought we are searching solution which can
>support both.
>
>Best regards,
>Alexander
>  
>
I guess I am saying, is this such an important problem that we want to 
search now?  I would like
to see discussions on including the passwords in the data file, not in 
its own separate one.  A
hook for adding in compression and encryption on the server and client 
easily.  Getting all
the API exposed through SQL (well maybe not all of it, but most of it).  
To me this stuff
is ten times more important then rolling back DDL statements.  I would 
prefer a way to
run DDL statements when someone else is actually using the server.  This 
is very frustrating
when you have to kick everyone out to do almost any DDL work.  Adding 
drop xxxx
cascade so when I want to drop a table, it automatically drops the 
indexes, triggers, views
and procedures.  These are things more important to me.

I don't expect many of them to even get a second look for 2.0.  That is 
because most of it
isn't important to enough people. 

I think the first question needs to be, "Do we need this flexibility 
now"  If so, why?  I don't
have it in Oracle and do fine there.

-- 
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com
http://sourceforge.net/projects/dbexpressplus

""atlantic"" <hobbit@...> escreveu na mensagem
news:001601c42e92$7fa9bdb0$640aa8c0@......
> David, you may be on to something there.
>
> - Flag starts as DML, so any DDL or DML can be executed withing that
> transaction
> - When a DML is performed, flag changes so that only more DML can be
> performed, no DDL

Not exactly, a transaction starts with a DML flag, so both DML and DDL can
be executed. When a DDL is executed, its flag changes to DDL, so only DDL
can be executed till the transaction ends. The data changed by previous DML
commands still belongs to the same transaction. Eg.:

*********
DML_COMMAND;
DDL_COMMAND;
DML_COMMAND; /* raises an exception */

commit;
*********
DML_COMMAND;
DDL_COMMAND;
DDL_COMMAND; /* It's OK, only DML after a DDL is blocked */

commit;
*********

If this aproach is inconvenient, I'd like to hear from a core developer
about his arguments.

With regards,
David

> This may be too restrictive. To keep from breaking existing logic the
state
> flags would have to be on a  table level so that a DML and a DDL could be
> performed in the same transaction providing they access different tables.

Building on this: considering the fact that any single statement on a
database of hundreds of tables would only access a small portion of those
tables, maintaining a flag for each table would be ineffecient.

Stacks would possibly be more efficient:

Two empty stacks, DDL and DML, would be created for each new transaction,
and follow this sort of logic.

When a DDL statement is encountered :-
Is table is in the DML stack?
Yes - give error message and abort.
No - add table to DDL stack if it isnt there already and execute statement.

When a DML statement is encountered :-
Is table is in the DDL stack?
Yes - give error message and abort.
No - add table to DML stack if it isnt there already and execute statement.

I'm sure there is a lot more to it than this, but this was bothering all
night long.

Regards
Gary Benade

Brian Vraamark wrote:

> Paul Schmidt wrote:
>
>> Okay, suppose you decide not to do it, you just log into your 
>> customers  database, and make your changes, oh oh, something just 
>> blew up in your face, your customer is now dead in the water, their 
>> business can't function because it depends on your software, they now 
>> have  their employees sitting on their hands for a week, which you 
>> try and put the pieces back together.  The customer loses $150,000 in 
>> wages and $4,000,000 in sales, not to mention that they will lose 
>> part of their reputation and customer base.   They are going to do 
>> one thing, call their lawyer, unless your contract is as solid as a 
>> concrete outhouse,  they are going to come after you, looking for 
>> compensation.   How long can your business survive if you need to pay 
>> $20,000,000 to an ex-customer when half an hour's work would have 
>> prevented it.
>>
>> Paul
>
>
> 1) Not half an hour. Half an hour x 120. We hope to reach 300 
> customers within the next 2 years.


>
> 2) We have insurrence. We pay BIG BUCKS for it. That dosen't mean that 
> we LIKE corrupting databases, but mixing DML and DDL in Firebird is 
> one of the reasons why we have insurrence (I am human, I make mistakes!).
>
> 3) Our customers is dentists. They are supposed to make teeth, not 
> working as some super DBA. For years I have been told that 
> Interbase/Firebird dosen't need a DBA? Are you saying that this is not 
> true?
>
> In our product the dentist calls function A then function B. In your 
> world the dentist can call function B then function A, resulting in 
> crashing the system and we call it a feature. I would prefere an error 
> message!
>
> You are right. I don't want an ex-customer when half an hour's work 
> would have prevented it. BUT I DID NOT KNOW mixing DML and DDL would 
> kill my database (I do now :-) ).
>
> I still think it is a BUG, but if the firebird developers says it is a 
> feature, it is OK with me. They are making my business possible and I 
> am only paying a small (not enough :-) ) amount through my membership. 

Not quite, if you have a completely canned package, without 
customization then you may be able to skip individual client testing, 
but if you do a lot of customization so that there are differences 
between one customer and another, then yeah you would need to do it for 
each one.  One thing you could look at is, using an intelligent 
installer, the installer does a backup of the database, does a test 
restore, checks the return codes, if they are okay, runs a regression 
test, then if that works, it loads the new application onto the client 
machine.    If at any point, it finds something that isn't kosher, then 
it gives up reports an error code, and leaves things as it found them.  
You never specified what kind of project it was, so one must assume it's 
a big custom monster.

Paul

David Y. Bonaf=E9 wrote:

>Not exactly, a transaction starts with a DML flag, so both DML and DDL c=
an
>be executed. When a DDL is executed, its flag changes to DDL, so only DD=
L
>can be executed till the transaction ends. The data changed by previous =
DML
>commands still belongs to the same transaction. Eg.:
>
>
>If this aproach is inconvenient, I'd like to hear from a core developer
>about his arguments.
>
> =20
>
The guys who interprete the DDL, particularly DYN, have to do DML to=20
update the system tables to reflect the DDL changes, and they have to do=20
it as part of the user transaction.  So without inventing some complete=20
crock of trusted vs. untrusted DML, the proposal is unimplementable.

If DDL were implemented inside the engine as opposed to layered, this=20
wouldn't be an issue, but neither would DDL be under transaction control.

--=20

Jim Starkey
Netfrastructure, Inc.
978 526-1376

Jim Starkey wrote:

>
> If DDL were implemented inside the engine as opposed to layered, this 
> wouldn't be an issue, but neither would DDL be under transaction control.
>
And what is the huge benefit from having DDL under transaction control?  
The only thing that I have seen is it
saves time in debugging a long script, because you can role it back.  I 
say, get rid of long scripts.  Use multiple
short ones.  Is rollback of DDL such a benefit that we should live with 
all the other problems and headaches?

My vote would be no.

-- 
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com
http://sourceforge.net/projects/dbexpressplus

> > If DDL were implemented inside the engine as opposed to layered, this
> > wouldn't be an issue, but neither would DDL be under transaction
control.
> >
> And what is the huge benefit from having DDL under transaction control?
> The only thing that I have seen is it
> saves time in debugging a long script, because you can role it back.  I
> say, get rid of long scripts.  Use multiple
> short ones.  Is rollback of DDL such a benefit that we should live with
> all the other problems and headaches?
>
> My vote would be no.

My vote would be "yes". :-)

Any DDL that involves doing multiple tasks to get a single thing
done should be able to get rolled-back.

With regards,

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

Martijn Tonies wrote:

>>>If DDL were implemented inside the engine as opposed to layered, this
>>>wouldn't be an issue, but neither would DDL be under transaction
>>>      
>>>
>control.
>  
>
>>And what is the huge benefit from having DDL under transaction control?
>>The only thing that I have seen is it
>>saves time in debugging a long script, because you can role it back.  I
>>say, get rid of long scripts.  Use multiple
>>short ones.  Is rollback of DDL such a benefit that we should live with
>>all the other problems and headaches?
>>
>>My vote would be no.
>>    
>>
>
>My vote would be "yes". :-)
>
>Any DDL that involves doing multiple tasks to get a single thing
>done should be able to get rolled-back.
>
>With regards,
>
>Martijn Tonies
>Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
>Server.
>Upscene Productions
>http://www.upscene.com
>
>  
>
Oracle doesn't allow this.  How about SQL Server, MySQL, DB2, etc.    
Are we trying to provide
a feature that no one else does?  If so, why aren't the other guys doing 
it too?  Is this a feature we
want our best spending 3 + weeks on to give us?  I get 10x more 
frustrated with not being able
to make DDL changes because there is one other connection to FB!!!

-- 
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com
http://sourceforge.net/projects/dbexpressplus

Martijn Tonies wrote:

>>>If DDL were implemented inside the engine as opposed to layered, this
>>>wouldn't be an issue, but neither would DDL be under transaction
>>>My vote would be "yes". :-)
>>>
>>>Any DDL that involves doing multiple tasks to get a single thing
>>>done should be able to get rolled-back.
>>>
>>>      
>>>

Do you really believe that defining the states between execution and 
commit for a couple of dozen DDL statements is really a good idea?  If 
the states are undefined, they have to be considered as implementation 
artifacts subject to change without notice.  If they are defined, future 
development is obligated to respect them which will make internal 
revision problematic and future development difficult.

For example, the existing code base handles SQL DDL "outside the box", 
generating DYN streams to do the actual work.  In Vulcan, SQL DDL is 
"inside the box", and no longer needs either DYN for DFW for its 
implementation.  The next time any significant feature gets added to a 
DDL object, it's going to make sense to implement it "natively" rather 
than going through the pain and suffering of extending DYN and reworking 
the already tortorous DFW mechanism.  If the intermediate states of DDL 
between execution and commit become part of the product definition, we 
are stuck with DYN and DFW forever. 

DDL operations in a system running multi-user are difficult enough in 
their own right.  Requiring the product to perpetuate ad hoc behaviors 
left over from a previous era just doesn't make sense to me.

-- 

Jim Starkey
Netfrastructure, Inc.
978 526-1376

Hi, Jim!

> For example, the existing code base handles SQL DDL "outside=20
> the box", generating DYN streams to do the actual work.  In=20
> Vulcan, SQL DDL is "inside the box", and no longer needs=20
> either DYN for DFW for its implementation.

Are you planning to fix/rewrite DDL handling in Vulcan?
This is good idea generally, but to put DDL out of transaction control a
couple things need to be fixed.
If you look at Oracle (which has DDL outside of transaction control)
you'll find that it has wonderful mechanism of metadata invalidation.

I.e. if you need to define some circular metadata structure you just do
so and it becomes valid as soon as you complete it.
While you do it partially defined structure is marked invalid in
database metadata catalog. I'm talking about recursive procedures,
objects, packages, computed fields, views, etc.

Defining circular metadata structures with Firebird would be torturous
experience without DFW mechanism which allows to define complex
structure fully before issuing commit statement.
Thus it has to be replaced with something clever if dropped.


> Jim Starkey

Nickolay

Samofatov, Nickolay wrote:

>>For example, the existing code base handles SQL DDL "outside 
>>the box", generating DYN streams to do the actual work.  In 
>>Vulcan, SQL DDL is "inside the box", and no longer needs 
>>either DYN for DFW for its implementation.
>>    
>>
>
>Are you planning to fix/rewrite DDL handling in Vulcan?
>This is good idea generally, but to put DDL out of transaction control a
>couple things need to be fixed.
>If you look at Oracle (which has DDL outside of transaction control)
>you'll find that it has wonderful mechanism of metadata invalidation.
>  
>
It's not on my plate, but I'm sure it will be on somebody's.

-- 

Jim Starkey
Netfrastructure, Inc.
978 526-1376

On Mon, 03 May 2004, Thomas Miller <tmiller@...> wrote:
> And what is the huge benefit from having DDL under transaction control? 
> 

SQL-92 compliance, afaict.  E.g.:

<quote>
	 The following SQL-statements are transaction initiating SQL-
         statements, i.e., if there is no current transaction, and a
state-
         ment of this class is executed, a transaction is initiated:

         -  All SQL-schema statements
	...
</quote>

X3H2-92-154/DBL CBR-002, section 4.22.
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

--jkl

AFIK PostgreSQL supports rollback of DDL?


Christoph

> -----Original Message-----
> From: Thomas Miller [mailto:tmiller@...]
> Sent: Tuesday, 4 May 2004 12:52 AM
> To: firebird-devel@...
> Subject: Re: [Firebird-devel] possible bug in FB1.5.0.4306
>=20
>=20
> Martijn Tonies wrote:
>=20
> >>>If DDL were implemented inside the engine as opposed to=20
> layered, this
> >>>wouldn't be an issue, but neither would DDL be under transaction
> >>>     =20
> >>>
> >control.
> > =20
> >
> >>And what is the huge benefit from having DDL under=20
> transaction control?
> >>The only thing that I have seen is it
> >>saves time in debugging a long script, because you can role=20
> it back.  I
> >>say, get rid of long scripts.  Use multiple
> >>short ones.  Is rollback of DDL such a benefit that we=20
> should live with
> >>all the other problems and headaches?
> >>
> >>My vote would be no.
> >>   =20
> >>
> >
> >My vote would be "yes". :-)
> >
> >Any DDL that involves doing multiple tasks to get a single thing
> >done should be able to get rolled-back.
> >
> >With regards,
> >
> >Martijn Tonies
> >Database Workbench - developer tool for InterBase, Firebird,=20
> MySQL & MS SQL
> >Server.
> >Upscene Productions
> >http://www.upscene.com
> >
> > =20
> >
> Oracle doesn't allow this.  How about SQL Server, MySQL, DB2, etc.   =20
> Are we trying to provide
> a feature that no one else does?  If so, why aren't the other=20
> guys doing=20
> it too?  Is this a feature we
> want our best spending 3 + weeks on to give us?  I get 10x more=20
> frustrated with not being able
> to make DDL changes because there is one other connection to FB!!!
>=20
> --=20
> Thomas Miller
> Delphi Client/Server Certified Developer
> BSS Accounting & Distribution Software
> BSS Enterprise Accounting FrameWork
>=20
> http://www.bss-software.com
> http://sourceforge.net/projects/dbexpressplus
>=20
>=20
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by: Oracle 10g
> Get certified on the hottest thing ever to hit the market...=20
> Oracle 10g.=20
> Take an Oracle 10g class now, and we'll give you the exam FREE.
> http://ads.osdn.com/?ad_id=3D3149&alloc_id=3D8166&op=3Dclick
> Firebird-Devel mailing list, web interface at=20
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>=20

> AFIK PostgreSQL supports rollback of DDL?

So does MSSQL Server, for some DDL, not for all.

> > Oracle doesn't allow this.  How about SQL Server, MySQL, DB2, etc.
> > Are we trying to provide
> > a feature that no one else does?  If so, why aren't the other
> > guys doing
> > it too?  Is this a feature we
> > want our best spending 3 + weeks on to give us?  I get 10x more
> > frustrated with not being able
> > to make DDL changes because there is one other connection to FB!!!

That's another problem.

A problem - IMO - is that when something fails, like adding an
FK, but you already dropped one, you cannot rollback that change.

If Fb can make it so that _this always works_ - things are easier :-)


With regards,

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

Hi,

At May 3, 2004, 21:45, James K. Lowden wrote:

> On Mon, 03 May 2004, Thomas Miller <tmiller@...> wrote:
>> And what is the huge benefit from having DDL under transaction control?
>> 

> SQL-92 compliance, afaict.  E.g.:

> <quote>
> 	 The following SQL-statements are transaction initiating SQL-
>          statements, i.e., if there is no current transaction, and a
> state-
>          ment of this class is executed, a transaction is initiated:

>          -  All SQL-schema statements
> 	...
> </quote>

> X3H2-92-154/DBL CBR-002, section 4.22.
> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

And, if you read section 4.28 in the above document(or 4.32 in
SQL-99 or 4.35 in SQL-2003), you will notice that how DDL and DML
statements are supported within the same transaction is implementation
defined.

Here's the paragraph indicating it(it's the same text in all 3
standards):

[Start quote...]

It is implementation-defined whether or not the execution of an
SQL-data statement is permitted to occur within the same
SQL-transaction as the execution of an SQL-schema statement. If it
does occur, then the effect on any open cursor or deferred constraint
is implementation-defined. There may be additional
implementation-defined restrictions, requirements, and conditions. If
any such restrictions, requirements, or conditions are violated, then
an implementation-defined exception condition or a completion
condition warning with an implementation-defined subclass code is
raised.

[...End quote]

So, I think what probably needs to be done is to outline what are the
SQL statement restrictions, requirements and conditions that exist in
Firebird. And, then it might be easier to see if some can be
eliminated, without severe consequences or performance loss, in a
future version and which ones might have to stay in place, if
eliminating them means that a severe performance loss has to be
encountered.

This was my 2 cents.

-- 
Best regards,
 Daniel Rail
 Senior System Engineer
 ACCRA Group Inc. (www.accra.ca)
 ACCRA Med Software Inc. (www.filopto.com)