Re: drop if exists - Mailing list pgsql-patches
| From | Andrew Dunstan |
|---|---|
| Subject | Re: drop if exists |
| Date | |
| Msg-id | 4378C416.5060704@dunslane.net Whole thread Raw |
| In response to | Re: drop if exists (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: drop if exists
|
| List | pgsql-patches |
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
>
>
>
Index: src/backend/commands/conversioncmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/conversioncmds.c,v
retrieving revision 1.23
diff -c -r1.23 conversioncmds.c
*** src/backend/commands/conversioncmds.c 15 Oct 2005 02:49:15 -0000 1.23
--- src/backend/commands/conversioncmds.c 14 Nov 2005 17:00:05 -0000
***************
*** 98,113 ****
* DROP CONVERSION
*/
void
! DropConversionCommand(List *name, DropBehavior behavior)
{
Oid conversionOid;
conversionOid = FindConversionByName(name);
if (!OidIsValid(conversionOid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("conversion \"%s\" does not exist",
! NameListToString(name))));
ConversionDrop(conversionOid, behavior);
}
--- 98,128 ----
* DROP CONVERSION
*/
void
! DropConversionCommand(List *name, DropBehavior behavior, bool missing_ok)
{
Oid conversionOid;
conversionOid = FindConversionByName(name);
if (!OidIsValid(conversionOid))
! {
! if (! missing_ok)
! {
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("conversion \"%s\" does not exist",
! NameListToString(name))));
! }
! else
! {
! ereport(NOTICE,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("conversion \"%s\" does not exist, skipping",
! NameListToString(name))));
! }
!
! Assert(missing_ok);
! return;
! }
ConversionDrop(conversionOid, behavior);
}
Index: src/backend/commands/schemacmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/schemacmds.c,v
retrieving revision 1.35
diff -c -r1.35 schemacmds.c
*** src/backend/commands/schemacmds.c 15 Oct 2005 02:49:15 -0000 1.35
--- src/backend/commands/schemacmds.c 14 Nov 2005 17:00:05 -0000
***************
*** 147,153 ****
* Removes a schema.
*/
void
! RemoveSchema(List *names, DropBehavior behavior)
{
char *namespaceName;
Oid namespaceId;
--- 147,153 ----
* Removes a schema.
*/
void
! RemoveSchema(List *names, DropBehavior behavior, bool missing_ok)
{
char *namespaceName;
Oid namespaceId;
***************
*** 163,171 ****
CStringGetDatum(namespaceName),
0, 0, 0);
if (!OidIsValid(namespaceId))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_SCHEMA),
! errmsg("schema \"%s\" does not exist", namespaceName)));
/* Permission check */
if (!pg_namespace_ownercheck(namespaceId, GetUserId()))
--- 163,186 ----
CStringGetDatum(namespaceName),
0, 0, 0);
if (!OidIsValid(namespaceId))
! {
! if (!missing_ok)
! {
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_SCHEMA),
! errmsg("schema \"%s\" does not exist", namespaceName)));
! }
! else
! {
! ereport(NOTICE,
! (errcode(ERRCODE_UNDEFINED_SCHEMA),
! errmsg("schema \"%s\" does not exist, skipping",
! namespaceName)));
! }
!
! Assert(missing_ok);
! return;
! }
/* Permission check */
if (!pg_namespace_ownercheck(namespaceId, GetUserId()))
Index: src/backend/commands/typecmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/typecmds.c,v
retrieving revision 1.82
diff -c -r1.82 typecmds.c
*** src/backend/commands/typecmds.c 18 Oct 2005 01:06:24 -0000 1.82
--- src/backend/commands/typecmds.c 14 Nov 2005 17:00:05 -0000
***************
*** 398,404 ****
* Removes a datatype.
*/
void
! RemoveType(List *names, DropBehavior behavior)
{
TypeName *typename;
Oid typeoid;
--- 398,404 ----
* Removes a datatype.
*/
void
! RemoveType(List *names, DropBehavior behavior, bool missing_ok)
{
TypeName *typename;
Oid typeoid;
***************
*** 414,423 ****
/* Use LookupTypeName here so that shell types can be removed. */
typeoid = LookupTypeName(typename);
if (!OidIsValid(typeoid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("type \"%s\" does not exist",
! TypeNameToString(typename))));
tup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(typeoid),
--- 414,438 ----
/* Use LookupTypeName here so that shell types can be removed. */
typeoid = LookupTypeName(typename);
if (!OidIsValid(typeoid))
! {
! if (!missing_ok)
! {
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("type \"%s\" does not exist",
! TypeNameToString(typename))));
! }
! else
! {
! ereport(NOTICE,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("type \"%s\" does not exist, skipping",
! TypeNameToString(typename))));
! }
!
! Assert(missing_ok);
! return;
! }
tup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(typeoid),
***************
*** 779,785 ****
* This is identical to RemoveType except we insist it be a domain.
*/
void
! RemoveDomain(List *names, DropBehavior behavior)
{
TypeName *typename;
Oid typeoid;
--- 794,800 ----
* This is identical to RemoveType except we insist it be a domain.
*/
void
! RemoveDomain(List *names, DropBehavior behavior, bool missing_ok)
{
TypeName *typename;
Oid typeoid;
***************
*** 796,805 ****
/* Use LookupTypeName here so that shell types can be removed. */
typeoid = LookupTypeName(typename);
if (!OidIsValid(typeoid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("type \"%s\" does not exist",
! TypeNameToString(typename))));
tup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(typeoid),
--- 811,825 ----
/* Use LookupTypeName here so that shell types can be removed. */
typeoid = LookupTypeName(typename);
if (!OidIsValid(typeoid))
! {
! if (missing_ok)
! return;
! else
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("type \"%s\" does not exist",
! TypeNameToString(typename))));
! }
tup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(typeoid),
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.511
diff -c -r2.511 gram.y
*** src/backend/parser/gram.y 23 Sep 2005 22:25:25 -0000 2.511
--- src/backend/parser/gram.y 14 Nov 2005 17:00:13 -0000
***************
*** 173,179 ****
%type <ival> opt_lock lock_type cast_context
%type <boolean> opt_force opt_or_replace
opt_grant_grant_option opt_grant_admin_option
! opt_nowait
%type <boolean> like_including_defaults
--- 173,179 ----
%type <ival> opt_lock lock_type cast_context
%type <boolean> opt_force opt_or_replace
opt_grant_grant_option opt_grant_admin_option
! opt_nowait
%type <boolean> like_including_defaults
***************
*** 362,368 ****
HANDLER HAVING HEADER HOLD HOUR_P
! ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT
INDEX INHERIT INHERITS INITIALLY INNER_P INOUT INPUT_P
INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT
INTERVAL INTO INVOKER IS ISNULL ISOLATION
--- 362,368 ----
HANDLER HAVING HEADER HOLD HOUR_P
! IF ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT
INDEX INHERIT INHERITS INITIALLY INNER_P INOUT INPUT_P
INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT
INTERVAL INTO INVOKER IS ISNULL ISOLATION
***************
*** 2822,2837 ****
*
*****************************************************************************/
! DropStmt: DROP drop_type any_name_list opt_drop_behavior
{
DropStmt *n = makeNode(DropStmt);
n->removeType = $2;
n->objects = $3;
n->behavior = $4;
$$ = (Node *)n;
}
;
drop_type: TABLE { $$ = OBJECT_TABLE; }
| SEQUENCE { $$ = OBJECT_SEQUENCE; }
| VIEW { $$ = OBJECT_VIEW; }
--- 2822,2848 ----
*
*****************************************************************************/
! DropStmt: DROP drop_type IF EXISTS any_name_list opt_drop_behavior
{
DropStmt *n = makeNode(DropStmt);
n->removeType = $2;
+ n->missing_ok = TRUE;
+ n->objects = $5;
+ n->behavior = $6;
+ $$ = (Node *)n;
+ }
+ | DROP drop_type any_name_list opt_drop_behavior
+ {
+ DropStmt *n = makeNode(DropStmt);
+ n->removeType = $2;
+ n->missing_ok = FALSE;
n->objects = $3;
n->behavior = $4;
$$ = (Node *)n;
}
;
+
drop_type: TABLE { $$ = OBJECT_TABLE; }
| SEQUENCE { $$ = OBJECT_SEQUENCE; }
| VIEW { $$ = OBJECT_VIEW; }
***************
*** 8149,8154 ****
--- 8160,8166 ----
| HEADER
| HOLD
| HOUR_P
+ | IF
| IMMEDIATE
| IMMUTABLE
| IMPLICIT_P
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.166
diff -c -r1.166 keywords.c
*** src/backend/parser/keywords.c 15 Oct 2005 02:49:22 -0000 1.166
--- src/backend/parser/keywords.c 14 Nov 2005 17:00:13 -0000
***************
*** 160,165 ****
--- 160,166 ----
{"header", HEADER},
{"hold", HOLD},
{"hour", HOUR_P},
+ {"if",IF},
{"ilike", ILIKE},
{"immediate", IMMEDIATE},
{"immutable", IMMUTABLE},
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.245
diff -c -r1.245 utility.c
*** src/backend/tcop/utility.c 15 Oct 2005 02:49:27 -0000 1.245
--- src/backend/tcop/utility.c 14 Nov 2005 17:00:15 -0000
***************
*** 67,72 ****
--- 67,73 ----
char kind;
int nonexistent_code;
const char *nonexistent_msg;
+ const char *skipping_msg;
const char *nota_msg;
const char *drophint_msg;
};
***************
*** 75,100 ****
--- 76,106 ----
{RELKIND_RELATION,
ERRCODE_UNDEFINED_TABLE,
gettext_noop("table \"%s\" does not exist"),
+ gettext_noop("table \"%s\" does not exist, skipping"),
gettext_noop("\"%s\" is not a table"),
gettext_noop("Use DROP TABLE to remove a table.")},
{RELKIND_SEQUENCE,
ERRCODE_UNDEFINED_TABLE,
gettext_noop("sequence \"%s\" does not exist"),
+ gettext_noop("sequence \"%s\" does not exist, skipping"),
gettext_noop("\"%s\" is not a sequence"),
gettext_noop("Use DROP SEQUENCE to remove a sequence.")},
{RELKIND_VIEW,
ERRCODE_UNDEFINED_TABLE,
gettext_noop("view \"%s\" does not exist"),
+ gettext_noop("view \"%s\" does not exist, skipping"),
gettext_noop("\"%s\" is not a view"),
gettext_noop("Use DROP VIEW to remove a view.")},
{RELKIND_INDEX,
ERRCODE_UNDEFINED_OBJECT,
gettext_noop("index \"%s\" does not exist"),
+ gettext_noop("index \"%s\" does not exist, skipping"),
gettext_noop("\"%s\" is not an index"),
gettext_noop("Use DROP INDEX to remove an index.")},
{RELKIND_COMPOSITE_TYPE,
ERRCODE_UNDEFINED_OBJECT,
gettext_noop("type \"%s\" does not exist"),
+ gettext_noop("type \"%s\" does not exist, skipping"),
gettext_noop("\"%s\" is not a type"),
gettext_noop("Use DROP TYPE to remove a type.")},
{'\0', 0, NULL, NULL, NULL}
***************
*** 132,154 ****
* non-existent relation
*/
static void
! DropErrorMsgNonExistent(RangeVar *rel, char rightkind)
{
const struct msgstrings *rentry;
for (rentry = msgstringarray; rentry->kind != '\0'; rentry++)
{
if (rentry->kind == rightkind)
! ereport(ERROR,
! (errcode(rentry->nonexistent_code),
! errmsg(rentry->nonexistent_msg, rel->relname)));
}
! Assert(false); /* Should be impossible */
}
! static void
! CheckDropPermissions(RangeVar *rel, char rightkind)
{
Oid relOid;
HeapTuple tuple;
--- 138,179 ----
* non-existent relation
*/
static void
! DropErrorMsgNonExistent(RangeVar *rel, char rightkind, bool missing_ok)
{
const struct msgstrings *rentry;
for (rentry = msgstringarray; rentry->kind != '\0'; rentry++)
{
if (rentry->kind == rightkind)
! {
! if (! missing_ok)
! {
! ereport(ERROR,
! (errcode(rentry->nonexistent_code),
! errmsg(rentry->nonexistent_msg, rel->relname)));
! }
! else
! {
! ereport(NOTICE,
! (errcode(rentry->nonexistent_code),
! errmsg(rentry->skipping_msg, rel->relname)));
! break;
! }
! }
}
! Assert(missing_ok); /* Should be impossible to get here otherwise */
}
! /*
! * returns false if missing_ok is true and the object does not exist,
! * true if object exists and permissions are OK,
! * errors otherwise
! *
! */
!
! static bool
! CheckDropPermissions(RangeVar *rel, char rightkind, bool missing_ok)
{
Oid relOid;
HeapTuple tuple;
***************
*** 156,162 ****
relOid = RangeVarGetRelid(rel, true);
if (!OidIsValid(relOid))
! DropErrorMsgNonExistent(rel, rightkind);
tuple = SearchSysCache(RELOID,
ObjectIdGetDatum(relOid),
--- 181,192 ----
relOid = RangeVarGetRelid(rel, true);
if (!OidIsValid(relOid))
! {
! DropErrorMsgNonExistent(rel, rightkind, missing_ok);
!
! Assert(missing_ok);
! return false;
! }
tuple = SearchSysCache(RELOID,
ObjectIdGetDatum(relOid),
***************
*** 183,188 ****
--- 213,220 ----
rel->relname)));
ReleaseSysCache(tuple);
+
+ return true;
}
/*
***************
*** 528,558 ****
{
case OBJECT_TABLE:
rel = makeRangeVarFromNameList(names);
! CheckDropPermissions(rel, RELKIND_RELATION);
! RemoveRelation(rel, stmt->behavior);
break;
case OBJECT_SEQUENCE:
rel = makeRangeVarFromNameList(names);
! CheckDropPermissions(rel, RELKIND_SEQUENCE);
! RemoveRelation(rel, stmt->behavior);
break;
case OBJECT_VIEW:
rel = makeRangeVarFromNameList(names);
! CheckDropPermissions(rel, RELKIND_VIEW);
! RemoveView(rel, stmt->behavior);
break;
case OBJECT_INDEX:
rel = makeRangeVarFromNameList(names);
! CheckDropPermissions(rel, RELKIND_INDEX);
! RemoveIndex(rel, stmt->behavior);
break;
case OBJECT_TYPE:
/* RemoveType does its own permissions checks */
! RemoveType(names, stmt->behavior);
break;
case OBJECT_DOMAIN:
--- 560,595 ----
{
case OBJECT_TABLE:
rel = makeRangeVarFromNameList(names);
! if (CheckDropPermissions(rel, RELKIND_RELATION,
! stmt->missing_ok))
! RemoveRelation(rel, stmt->behavior);
break;
case OBJECT_SEQUENCE:
rel = makeRangeVarFromNameList(names);
! if (CheckDropPermissions(rel, RELKIND_SEQUENCE,
! stmt->missing_ok))
! RemoveRelation(rel, stmt->behavior);
break;
case OBJECT_VIEW:
rel = makeRangeVarFromNameList(names);
! if (CheckDropPermissions(rel, RELKIND_VIEW,
! stmt->missing_ok))
! RemoveView(rel, stmt->behavior);
break;
case OBJECT_INDEX:
rel = makeRangeVarFromNameList(names);
! if (CheckDropPermissions(rel, RELKIND_INDEX,
! stmt->missing_ok))
! RemoveIndex(rel, stmt->behavior);
break;
case OBJECT_TYPE:
/* RemoveType does its own permissions checks */
! RemoveType(names, stmt->behavior,
! stmt->missing_ok);
break;
case OBJECT_DOMAIN:
***************
*** 560,570 ****
/*
* RemoveDomain does its own permissions checks
*/
! RemoveDomain(names, stmt->behavior);
break;
case OBJECT_CONVERSION:
! DropConversionCommand(names, stmt->behavior);
break;
case OBJECT_SCHEMA:
--- 597,609 ----
/*
* RemoveDomain does its own permissions checks
*/
! RemoveDomain(names, stmt->behavior,
! stmt->missing_ok);
break;
case OBJECT_CONVERSION:
! DropConversionCommand(names, stmt->behavior,
! stmt->missing_ok);
break;
case OBJECT_SCHEMA:
***************
*** 572,578 ****
/*
* RemoveSchema does its own permissions checks
*/
! RemoveSchema(names, stmt->behavior);
break;
default:
--- 611,618 ----
/*
* RemoveSchema does its own permissions checks
*/
! RemoveSchema(names, stmt->behavior,
! stmt->missing_ok);
break;
default:
Index: src/include/commands/conversioncmds.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/conversioncmds.h,v
retrieving revision 1.10
diff -c -r1.10 conversioncmds.h
*** src/include/commands/conversioncmds.h 28 Jun 2005 05:09:12 -0000 1.10
--- src/include/commands/conversioncmds.h 14 Nov 2005 17:00:18 -0000
***************
*** 18,24 ****
#include "nodes/parsenodes.h"
extern void CreateConversionCommand(CreateConversionStmt *parsetree);
! extern void DropConversionCommand(List *conversion_name, DropBehavior behavior);
extern void RenameConversion(List *name, const char *newname);
extern void AlterConversionOwner(List *name, Oid newOwnerId);
--- 18,25 ----
#include "nodes/parsenodes.h"
extern void CreateConversionCommand(CreateConversionStmt *parsetree);
! extern void DropConversionCommand(List *conversion_name,
! DropBehavior behavior, bool missing_ok);
extern void RenameConversion(List *name, const char *newname);
extern void AlterConversionOwner(List *name, Oid newOwnerId);
Index: src/include/commands/schemacmds.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/schemacmds.h,v
retrieving revision 1.10
diff -c -r1.10 schemacmds.h
*** src/include/commands/schemacmds.h 28 Jun 2005 05:09:12 -0000 1.10
--- src/include/commands/schemacmds.h 14 Nov 2005 17:00:18 -0000
***************
*** 19,25 ****
extern void CreateSchemaCommand(CreateSchemaStmt *parsetree);
! extern void RemoveSchema(List *names, DropBehavior behavior);
extern void RemoveSchemaById(Oid schemaOid);
extern void RenameSchema(const char *oldname, const char *newname);
--- 19,25 ----
extern void CreateSchemaCommand(CreateSchemaStmt *parsetree);
! extern void RemoveSchema(List *names, DropBehavior behavior, bool missing_ok);
extern void RemoveSchemaById(Oid schemaOid);
extern void RenameSchema(const char *oldname, const char *newname);
Index: src/include/commands/typecmds.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/typecmds.h,v
retrieving revision 1.14
diff -c -r1.14 typecmds.h
*** src/include/commands/typecmds.h 15 Oct 2005 02:49:44 -0000 1.14
--- src/include/commands/typecmds.h 14 Nov 2005 17:00:18 -0000
***************
*** 20,29 ****
#define DEFAULT_TYPDELIM ','
extern void DefineType(List *names, List *parameters);
! extern void RemoveType(List *names, DropBehavior behavior);
extern void RemoveTypeById(Oid typeOid);
extern void DefineDomain(CreateDomainStmt *stmt);
! extern void RemoveDomain(List *names, DropBehavior behavior);
extern Oid DefineCompositeType(const RangeVar *typevar, List *coldeflist);
extern void AlterDomainDefault(List *names, Node *defaultRaw);
--- 20,29 ----
#define DEFAULT_TYPDELIM ','
extern void DefineType(List *names, List *parameters);
! extern void RemoveType(List *names, DropBehavior behavior, bool missing_ok);
extern void RemoveTypeById(Oid typeOid);
extern void DefineDomain(CreateDomainStmt *stmt);
! extern void RemoveDomain(List *names, DropBehavior behavior, bool missing_ok);
extern Oid DefineCompositeType(const RangeVar *typevar, List *coldeflist);
extern void AlterDomainDefault(List *names, Node *defaultRaw);
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.292
diff -c -r1.292 parsenodes.h
*** src/include/nodes/parsenodes.h 26 Oct 2005 19:21:55 -0000 1.292
--- src/include/nodes/parsenodes.h 14 Nov 2005 17:00:20 -0000
***************
*** 1278,1283 ****
--- 1278,1284 ----
List *objects; /* list of sublists of names (as Values) */
ObjectType removeType; /* object type */
DropBehavior behavior; /* RESTRICT or CASCADE behavior */
+ bool missing_ok; /* skip error if object is missing? */
} DropStmt;
/* ----------------------
pgsql-patches by date: