Re: [Firebird-devel] DROP TABLE

[Ann W. H. <aha...@ib...>]

At 06:10 PM 3/11/2001 +0700, Andy Canfield wrote:

>Thank you for your response. In trying to build a minimal test case I 
>discovered that I was not calling isc_free_statement().

Yes.  Any prepared statement will hold an existence lock on
everything it touches - tables, indexes, procedures, etc.

> >From your resopnse I conclude that the design rule is:
>[1] CREATE TABLE must be in a separate transaction from any use of that table,

Yes.  The table is created at commit.

>[2] any use of that table must be in a separate transaction from the DROP 
>TABLE

That I am less certain of - it may be that freeing all prepared
statements would be adequate.  However, absent some testing and
study of code (not the day for that for me) I must agree that your
statement is probably true.

>[3] they can be in the same connection.

Absolutely.

>We should do something about being able to update the documentation.

Yes.

>The use of temporary tables  ... <clear statement of problem deleted>

OK, here's my suggestion.  Create a new table to hold the master information,
including a column for the indicator.  Move the data (if necessary) and
delete the old table.  Create a view of the new table with the same name
as the old table.  The view should exclude the indicator, making it identical
to the old table.  That view is fully up-datable, so you don't need to build
triggers and all that stuff.  Only the update program uses the new table
name.  If necessary, you can set protections to keep others from seeing the
indicator.

You said that the flag data isn't part of the "information base" and so
it should not be a permanent part of the database.  I would argue that
it's part of the normal operation and can be included as a bit of
housekeeping.

Adding and dropping a column is less expensive than adding and dropping
tables except that there is a 256 iteration limit on significant changes
to tables, so you'll run out of format versions fairly quickly.

Regards,

Ann
www.ibphoenix.com
We have answers.