drop if exists

On Nov 14, 2005, at 23:25 , Andrew Dunstan wrote:

Ther attached patch is for comment. It implements "drop if exists"
as has recently been discussed. Illustration:

Nifty! Thanks for working this up, Andrew!

andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=# drop table if exists blurflx;
DROP TABLE

I'm not sure what other DBMS' return in this situation (and kindly
ignore this suggestion if it's specified or otherwise determined),
but perhaps the output could be TABLE "blurlx" DOES NOT EXIST
(without the ERROR) or something more informative, rather than DROP
TABLE. It reminds me of the old behavior of outputting COMMIT even in
the case of transaction failure. I find the current behavior of
outputting ROLLBACK in the case of transaction failure more useful.

Michael Glaesemann
grzm myrealbox com

-----Original Message-----
From: pgsql-patches-owner@postgresql.org
[mailto:pgsql-patches-owner@postgresql.org] On Behalf Of
Michael Glaesemann
Sent: 14 November 2005 14:54
To: Andrew Dunstan
Cc: Patches (PostgreSQL)
Subject: Re: [PATCHES] drop if exists

On Nov 14, 2005, at 23:25 , Andrew Dunstan wrote:

Ther attached patch is for comment. It implements "drop if exists"
as has recently been discussed. Illustration:

Nifty! Thanks for working this up, Andrew!

andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=# drop table if exists blurflx;
DROP TABLE

I'm not sure what other DBMS' return in this situation (and kindly
ignore this suggestion if it's specified or otherwise determined),
but perhaps the output could be TABLE "blurlx" DOES NOT EXIST
(without the ERROR) or something more informative, rather than DROP
TABLE. It reminds me of the old behavior of outputting COMMIT
even in
the case of transaction failure. I find the current behavior of
outputting ROLLBACK in the case of transaction failure more useful.

Yes, a notice would certainly be nice:

andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=# drop table if exists blurflx;
NOTICE: table "blurflx" does not exist
DROP TABLE

Regards, Dave.

Andrew Dunstan <andrew@dunslane.net> writes:

andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=# drop table if exists blurflx;
DROP TABLE

If I read MySQL's documentation correctly, they emit a NOTE (equivalent
of a NOTICE message I suppose) when IF EXISTS does nothing because the
table doesn't exist. Seems like we should do likewise --- your second
example here seems actively misleading. That is, I'd rather see

andrew=# drop table if exists blurflx;
NOTICE: table "blurflx" does not exist, skipping
DROP TABLE

regards, tom lane

OK, now it looks like this:

andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=# drop table if exists blurflx;
NOTICE: table "blurflx" does not exist, skipping
DROP TABLE
andrew=# create table blurflx ( x text);
CREATE TABLE
andrew=# drop table if exists blurflx;
DROP TABLE
andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=#

revised patch attached.

cheers

andrew

Tom Lane wrote:

Removed from queue. Andrew is committing it.

---------------------------------------------------------------------------

Andrew Dunstan wrote:

OK, now it looks like this:

andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=# drop table if exists blurflx;
NOTICE: table "blurflx" does not exist, skipping
DROP TABLE
andrew=# create table blurflx ( x text);
CREATE TABLE
andrew=# drop table if exists blurflx;
DROP TABLE
andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=#

revised patch attached.

cheers

andrew

Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=# drop table if exists blurflx;
DROP TABLE

If I read MySQL's documentation correctly, they emit a NOTE (equivalent
of a NOTICE message I suppose) when IF EXISTS does nothing because the
table doesn't exist. Seems like we should do likewise --- your second
example here seems actively misleading. That is, I'd rather see

andrew=# drop table if exists blurflx;
NOTICE: table "blurflx" does not exist, skipping
DROP TABLE

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Will we get this functionality for ALL objects?

Bruce Momjian wrote:

Christopher Kings-Lynne said:

Will we get this functionality for ALL objects?

The patch does these: table, view, index, sequence, schema, type, domain,
and conversion. The reason is that these are all dealt with using the same
bit of the grammar, and the first 4 are pretty much completely done by the
same code.

I think anything else will have to be done individually, although the
pattern can be copied.

Perhaps we should take bids on what should/should not be covered.

cheers

andrew

On Nov 17, 2005, at 11:45 , Christopher Kings-Lynne wrote:

I think anything else will have to be done individually, although the
pattern can be copied.
Perhaps we should take bids on what should/should not be covered.

Everything should be covered, otherwise it's just annoying for
users...

Including objects that already have CREATE OR REPLACE?

Michael Glaesemann
grzm myrealbox com

I think anything else will have to be done individually, although the
pattern can be copied.

Perhaps we should take bids on what should/should not be covered.

Everything should be covered, otherwise it's just annoying for users...

Chris

Including objects that already have CREATE OR REPLACE?

I assume so - CREATE OR REPLACE doesn't drop things - only creates or
replaces them.

Chris

Christopher Kings-Lynne wrote:

I think anything else will have to be done individually, although the
pattern can be copied.

Perhaps we should take bids on what should/should not be covered.

Everything should be covered, otherwise it's just annoying for users...

Well, that's arguably more than I originally signed up for ;-) See

http://archives.postgresql.org/pgsql-hackers/2005-10/msg00632.php

There are currently DROP commends for the following 21 objects
(according to the docs).

AGGREGATE
CAST
CONVERSION
DATABASE
DOMAIN
FUNCTION
GROUP
INDEX
LANGUAGE
OPERATOR
OPERATOR CLASS
ROLE
RULE
SCHEMA
SEQUENCE
TABLE
TABLESPACE
TRIGGER
TYPE
USER
VIEW

If the consensus is to add this to all of them, then I propose to apply
the patch I have (with a slight fix for an oversight in the case of
domains, plus docs and tests) for the 8 cases and start working on the
remaining 13 as time permits. To be honest, I have not even looked at
those 13 cases.

One motivation for this, besides general utility, is to ease MySQL
migrations, btw, and AFAICT they only have three DROP commands and only
two of them (TABLE and DATABASE) have IF EXISTS - DROP INDEX does not
for some reason - probably because it is actually mapped to an ALTER
TABLE statement.

cheers

andrew

On Nov 17, 2005, at 11:51 , Christopher Kings-Lynne wrote:

Including objects that already have CREATE OR REPLACE?

I assume so - CREATE OR REPLACE doesn't drop things - only creates
or replaces them.

Of course. Silly me :)

Michael Glaesemann
grzm myrealbox com

If the consensus is to add this to all of them, then I propose to apply
the patch I have (with a slight fix for an oversight in the case of
domains, plus docs and tests) for the 8 cases and start working on the
remaining 13 as time permits. To be honest, I have not even looked at
those 13 cases.

I agree. I can have a crack at the others as well. It's in my area of
ability I hope ;) (Except grammar janking)

Chris