Thread: drop if exists

drop if exists

From
Andrew Dunstan
Date:
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


Re: drop if exists

From
David Fetter
Date:
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@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!


Re: drop if exists

From
Alvaro Herrera
Date:
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)


Re: drop if exists

From
Rod Taylor
Date:
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.

-- 



Re: drop if exists

From
Jari Aalto
Date:
Alvaro Herrera <alvherre@alvh.no-ip.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



Re: drop if exists

From
Andrew Dunstan
Date:

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


Re: drop if exists

From
Andrew Dunstan
Date:

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


Re: drop if exists

From
Martijn van Oosterhout
Date:
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@svana.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.

Re: drop if exists

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.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


Re: drop if exists

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.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


Re: drop if exists

From
Bernd Helmle
Date:
--On Samstag, Oktober 15, 2005 17:20:06 +0200 Martijn van Oosterhout 
<kleptog@svana.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


Re: drop if exists

From
Andrew Dunstan
Date:

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


Re: drop if exists

From
Darko Prenosil
Date:

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 !