drop if exists

From time to time the suggestion crops up of allowing a DROP IF EXISTS
... syntax. This seems not unreasonable, and I just spent a few minutes
looking at what might be involved. Especially in the case of a table,
view, sequence and index the changes look like they would be very modest
indeed, and not enormously greater in the case of a type, domain,
conversion and schema.

Is this worth doing? Would it be acceptable?

cheers

andrew

On Fri, Oct 14, 2005 at 08:29:43PM -0400, Andrew Dunstan wrote:
> From time to time the suggestion crops up of allowing a DROP IF
> EXISTS ... syntax. This seems not unreasonable, and I just spent a
> few minutes looking at what might be involved. Especially in the
> case of a table, view, sequence and index the changes look like they
> would be very modest indeed, and not enormously greater in the case
> of a type, domain, conversion and schema.
>
> Is this worth doing? Would it be acceptable?

Yes, and yes, in my case :)

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

Andrew Dunstan wrote:
>
> From time to time the suggestion crops up of allowing a DROP IF EXISTS
> ... syntax. This seems not unreasonable, and I just spent a few minutes
> looking at what might be involved.

What about "CREATE IF NOT EXISTS" (CINE)? If we support DROP IF EXISTS
(DIE), is the other one going to be supported too?

How does this play with schemas? I assume DIE drops the table in any
schema in the search path. What if there's more than one; drop the
first one? CINE creates the schema in the first schema in the path,
just like CREATE.

Also, DIE does not need to lock the table afterwards because it won't
exist, but CINE needs to keep a lock until transaction commit.

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)

On Fri, 2005-10-14 at 20:29 -0400, Andrew Dunstan wrote:
> From time to time the suggestion crops up of allowing a DROP IF EXISTS
> ... syntax. This seems not unreasonable, and I just spent a few minutes
> looking at what might be involved. Especially in the case of a table,
> view, sequence and index the changes look like they would be very modest
> indeed, and not enormously greater in the case of a type, domain,
> conversion and schema.

I would rather have a 'rollback or release savepoint' command which
would rollback to the savepoint if there was an error or release it
otherwise.

This way any command or combination of commands could be aborted or
continued as a group in a statically defined script.

--

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:

> Andrew Dunstan wrote:
>
| >
| > From time to time the suggestion crops up of allowing a DROP IF EXISTS
| > ... syntax. This seems not unreasonable, and I just spent a few minutes
| > looking at what might be involved.
>
> What about "CREATE IF NOT EXISTS" (CINE)? If we support DROP IF EXISTS
> (DIE), is the other one going to be supported too?
>
> How does this play with schemas? I assume DIE drops the table in any
> schema in the search path. What if there's more than one; drop the
> first one? CINE creates the schema in the first schema in the path,
> just like CREATE.
>
> Also, DIE does not need to lock the table afterwards because it won't
> exist, but CINE needs to keep a lock until transaction commit.

The "DROP IF EXISTS" is usually used in database creation scripts in
order to rebuild contents of schema. The parallel "CREATE IF EXISTS"
does not sound as useful feature.

DROP ... that table
CREATE ... that table

DROP ... that index
CREATE ... that index

etc.

It would also be MySQL compatible if DROP IF EXISTS were implemented.

Jari

Rod Taylor wrote:

>On Fri, 2005-10-14 at 20:29 -0400, Andrew Dunstan wrote:
>
>
>> From time to time the suggestion crops up of allowing a DROP IF EXISTS
>>... syntax. This seems not unreasonable, and I just spent a few minutes
>>looking at what might be involved. Especially in the case of a table,
>>view, sequence and index the changes look like they would be very modest
>>indeed, and not enormously greater in the case of a type, domain,
>>conversion and schema.
>>
>>
>
>I would rather have a 'rollback or release savepoint' command which
>would rollback to the savepoint if there was an error or release it
>otherwise.
>
>This way any command or combination of commands could be aborted or
>continued as a group in a statically defined script.
>
>
>

I don't see that they are mutually exclusive, although one could achieve
the effect this way.

cheers

andrew

Alvaro Herrera wrote:

>Andrew Dunstan wrote:
>
>
>>From time to time the suggestion crops up of allowing a DROP IF EXISTS
>>... syntax. This seems not unreasonable, and I just spent a few minutes
>>looking at what might be involved.
>>
>>
>
>What about "CREATE IF NOT EXISTS" (CINE)? If we support DROP IF EXISTS
>(DIE), is the other one going to be supported too?
>
>

Maybe. But I am not sure they need to be done together.

>How does this play with schemas? I assume DIE drops the table in any
>schema in the search path. What if there's more than one; drop the
>first one?
>

Yes. Just like now. My idea was that at the point where it currently
errors out because the object exists, we would instead simply fall
through and take no action.

>CINE creates the schema in the first schema in the path,
>just like CREATE.
>
>
>Also, DIE does not need to lock the table afterwards because it won't
>exist, but CINE needs to keep a lock until transaction commit.
>
>

Right. That's one reason I thought of starting with the DIE case ;-)

cheers

andrew

On Fri, Oct 14, 2005 at 10:32:02PM -0300, Alvaro Herrera wrote:
> What about "CREATE IF NOT EXISTS" (CINE)? If we support DROP IF EXISTS
> (DIE), is the other one going to be supported too?

CINE already exists sortof, it's called CREATE OR REPLACE. Although the
effect is obvious for functions, it seems to me that it would be cool
to make a CREATE OR REPLACE TABLE that simply does nothing if the table
already exists with the right format.

ISTM that most of the sitautions people are talking about here ivolving
recreating the table exactly as is directly afterwards.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Rod Taylor wrote:
>> I would rather have a 'rollback or release savepoint' command which
>> would rollback to the savepoint if there was an error or release it
>> otherwise.
>>
>> This way any command or combination of commands could be aborted or
>> continued as a group in a statically defined script.

> I don't see that they are mutually exclusive, although one could achieve
> the effect this way.

For single-command transactions, you don't actually need either one.
Ignoring the error message from the failed DROP works fine.

If you're trying to wrap the creation of a whole schema in an outer
transaction, though, you need one or the other --- and Rod's suggestion
is definitely more flexible.

I think the main argument in favor of DROP IF EXISTS is that people
coming from MySQL are accustomed to having it.

regards, tom lane

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Alvaro Herrera wrote:
>> Also, DIE does not need to lock the table afterwards because it won't
>> exist, but CINE needs to keep a lock until transaction commit.

> Right. That's one reason I thought of starting with the DIE case ;-)

That argument seems pretty wrongheaded to me --- if there was a table
and DIE dropped it, you *will* be holding a lock until commit. DROP
can be rolled back, remember? CINE will need to keep a lock too, at
least in the cases where it creates or modifies the table, though you
could possibly choose to drop the lock immediately if there's no change.

I don't see any real use for CINE --- it's too nonintuitive about what
will happen. Does it adjust the table definition to match if different?
Does it truncate away the data inside the table (it certainly must if it
changes the table definition)? If so, what about foreign keys? The
implication of that command name is that nothing happens if the table
exists, regardless of definition or contents. Which seems a pretty
useless behavior.

We know that DIE is a convenient, useful semantics because people keep
asking for it. I think CINE has no track record.

regards, tom lane

--On Samstag, Oktober 15, 2005 17:20:06 +0200 Martijn van Oosterhout
<kleptog(at)svana(dot)org> wrote:

> Although the
> effect is obvious for functions, it seems to me that it would be cool
> to make a CREATE OR REPLACE TABLE that simply does nothing if the table
> already exists with the right format.

Hmm i don't think this is the right semantic, because CREATE OR REPLACE for
other objects does indeed something, simply ignoring an existing object
isn't what i would expect.

--
Thanks

Bernd

Tom Lane wrote:

>
>We know that DIE is a convenient, useful semantics because people keep
>asking for it. I think CINE has no track record.
>
>
>
>

You have confirmed my initial instinct. I will get this done for 8.2.

cheers

andrew

Andrew Dunstan wrote:

>
>
> Alvaro Herrera wrote:
>
>> Andrew Dunstan wrote:
>>
>>
>>> From time to time the suggestion crops up of allowing a DROP IF
>>> EXISTS ... syntax. This seems not unreasonable, and I just spent a
>>> few minutes looking at what might be involved.
>>>
>>
>>
>> What about "CREATE IF NOT EXISTS" (CINE)? If we support DROP IF EXISTS
>> (DIE), is the other one going to be supported too?
>>
>>
>
> Maybe. But I am not sure they need to be done together.
>
>> How does this play with schemas? I assume DIE drops the table in any
>> schema in the search path. What if there's more than one; drop the
>> first one?
>
>
> Yes. Just like now. My idea was that at the point where it currently
> errors out because the object exists, we would instead simply fall
> through and take no action.
>
>> CINE creates the schema in the first schema in the path,
>> just like CREATE.
>>
>>
>> Also, DIE does not need to lock the table afterwards because it won't
>> exist, but CINE needs to keep a lock until transaction commit.
>>
>>
>
> Right. That's one reason I thought of starting with the DIE case ;-)
>
> cheers
>
> andrew
>
>
In real world scenarios having "CREATE IF NOT EXISTS" or "drop if
exists" on basic objects is just not enough.
I'll try to explain:

when writing sql scripts for "database upgrade", I very often have this
case:

1. check if object exists at all, if not, create it
2. Check if table is of the last version (does it have all required fields)
3. If table is older version, then create new fields, constraints etc...
4. Sometimes even I have to do something with the data or table
structure depending on
some database setting (data from my settings table)
etc...

So, if only tables, functions and other base objects are supported by
new syntax, I'll still
have to write temporary functions or use pgbash (like I do now).
The really good thing would be to have implemented "IF" statement in
general, but I understand that this is
not a trivial task at all.

Just my two cents..

Regards !