Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit36546c9

Browse files
committed
Attached is my patch that adds DROP CONSTRAINT support to PostgreSQL. I
basically want your guys feedback. I have sprinkled some of my q's thruthe text delimited with the @@ symbol. It seems to work perfectly.[ Removed @@ comments because patch was reviewed. ]At the moment it does CHECK constraints only, with inheritance. However,due to the problem mentioned before with the mismatching between inheritedconstraints it may be wise to disable the inheritance feature for a while.it is written in an extensible fashion to support future dropping of othertypes of constraint, and is well documented.Please send me your comments, check my use of locking, updating ofindices, use of ERROR and NOTICE, etc. and I will rework the patch basedon feedback until everyoneis happy with it...Christopher Kings
1 parent7d4854f commit36546c9

File tree

3 files changed

+215
-6
lines changed

3 files changed

+215
-6
lines changed

‎src/backend/catalog/heap.c

Lines changed: 146 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.165 2001/05/14 20:30:19 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.166 2001/05/30 12:57:36 momjian Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -48,6 +48,7 @@
4848
#include"miscadmin.h"
4949
#include"optimizer/clauses.h"
5050
#include"optimizer/planmain.h"
51+
#include"optimizer/prep.h"
5152
#include"optimizer/var.h"
5253
#include"nodes/makefuncs.h"
5354
#include"parser/parse_clause.h"
@@ -1975,6 +1976,150 @@ RemoveRelCheck(Relation rel)
19751976

19761977
heap_endscan(rcscan);
19771978
heap_close(rcrel,RowExclusiveLock);
1979+
1980+
}
1981+
1982+
/*
1983+
* Removes all CHECK constraints on a relation that match the given name.
1984+
* It is the responsibility of the calling function to acquire a lock on
1985+
* the relation.
1986+
* Returns: The number of CHECK constraints removed.
1987+
*/
1988+
int
1989+
RemoveCheckConstraint(Relationrel,constchar*constrName,boolinh)
1990+
{
1991+
Oidrelid;
1992+
Relationrcrel;
1993+
Relationrelrel;
1994+
Relationinhrel;
1995+
Relationrelidescs[Num_pg_class_indices];
1996+
TupleDesctupleDesc;
1997+
TupleConstr*oldconstr;
1998+
intnumoldchecks;
1999+
intnumchecks;
2000+
HeapScanDescrcscan;
2001+
ScanKeyDatakey[2];
2002+
HeapTuplerctup;
2003+
HeapTuplereltup;
2004+
Form_pg_classrelStruct;
2005+
intrel_deleted=0;
2006+
intall_deleted=0;
2007+
2008+
/* Find id of the relation */
2009+
relid=RelationGetRelid(rel);
2010+
2011+
/* Process child tables and remove constraints of the
2012+
same name. */
2013+
if (inh)
2014+
{
2015+
List*child,
2016+
*children;
2017+
2018+
/* This routine is actually in the planner */
2019+
children=find_all_inheritors(relid);
2020+
2021+
/*
2022+
* find_all_inheritors does the recursive search of the
2023+
* inheritance hierarchy, so all we have to do is process all
2024+
* of the relids in the list that it returns.
2025+
*/
2026+
foreach(child,children)
2027+
{
2028+
Oidchildrelid=lfirsti(child);
2029+
2030+
if (childrelid==relid)
2031+
continue;
2032+
inhrel=heap_open(childrelid,AccessExclusiveLock);
2033+
all_deleted+=RemoveCheckConstraint(inhrel,constrName, false);
2034+
heap_close(inhrel,NoLock);
2035+
}
2036+
}
2037+
2038+
/* Grab an exclusive lock on the pg_relcheck relation */
2039+
rcrel=heap_openr(RelCheckRelationName,RowExclusiveLock);
2040+
2041+
/*
2042+
* Create two scan keys. We need to match on the oid of the table
2043+
* the CHECK is in and also we need to match the name of the CHECK
2044+
* constraint.
2045+
*/
2046+
ScanKeyEntryInitialize(&key[0],0,Anum_pg_relcheck_rcrelid,
2047+
F_OIDEQ,RelationGetRelid(rel));
2048+
2049+
ScanKeyEntryInitialize(&key[1],0,Anum_pg_relcheck_rcname,
2050+
F_NAMEEQ,PointerGetDatum(constrName));
2051+
2052+
/* Begin scanning the heap */
2053+
rcscan=heap_beginscan(rcrel,0,SnapshotNow,2,key);
2054+
2055+
/*
2056+
* Scan over the result set, removing any matching entries. Note
2057+
* that this has the side-effect of removing ALL CHECK constraints
2058+
* that share the specified constraint name.
2059+
*/
2060+
while (HeapTupleIsValid(rctup=heap_getnext(rcscan,0))) {
2061+
simple_heap_delete(rcrel,&rctup->t_self);
2062+
++rel_deleted;
2063+
++all_deleted;
2064+
}
2065+
2066+
/* Clean up after the scan */
2067+
heap_endscan(rcscan);
2068+
2069+
/*
2070+
* Update the count of constraints in the relation's pg_class tuple.
2071+
* We do this even if there was no change, in order to ensure that an
2072+
* SI update message is sent out for the pg_class tuple, which will
2073+
* force other backends to rebuild their relcache entries for the rel.
2074+
* (Of course, for a newly created rel there is no need for an SI
2075+
* message, but for ALTER TABLE ADD ATTRIBUTE this'd be important.)
2076+
*/
2077+
2078+
/*
2079+
* Get number of existing constraints.
2080+
*/
2081+
2082+
tupleDesc=RelationGetDescr(rel);
2083+
oldconstr=tupleDesc->constr;
2084+
if (oldconstr)
2085+
numoldchecks=oldconstr->num_check;
2086+
else
2087+
numoldchecks=0;
2088+
2089+
/* Calculate the new number of checks in the table, fail if negative */
2090+
numchecks=numoldchecks-rel_deleted;
2091+
2092+
if (numchecks<0)
2093+
elog(ERROR,"check count became negative");
2094+
2095+
relrel=heap_openr(RelationRelationName,RowExclusiveLock);
2096+
reltup=SearchSysCacheCopy(RELOID,
2097+
ObjectIdGetDatum(RelationGetRelid(rel)),0,0,0);
2098+
2099+
if (!HeapTupleIsValid(reltup))
2100+
elog(ERROR,"cache lookup of relation %u failed",
2101+
RelationGetRelid(rel));
2102+
relStruct= (Form_pg_class)GETSTRUCT(reltup);
2103+
2104+
relStruct->relchecks=numchecks;
2105+
2106+
simple_heap_update(relrel,&reltup->t_self,reltup);
2107+
2108+
/* Keep catalog indices current */
2109+
CatalogOpenIndices(Num_pg_class_indices,Name_pg_class_indices,
2110+
relidescs);
2111+
CatalogIndexInsert(relidescs,Num_pg_class_indices,relrel,reltup);
2112+
CatalogCloseIndices(Num_pg_class_indices,relidescs);
2113+
2114+
/* Clean up after the scan */
2115+
heap_freetuple(reltup);
2116+
heap_close(relrel,RowExclusiveLock);
2117+
2118+
/* Close the heap relation */
2119+
heap_close(rcrel,RowExclusiveLock);
2120+
2121+
/* Return the number of tuples deleted */
2122+
returnall_deleted;
19782123
}
19792124

19802125
staticvoid

‎src/backend/commands/command.c

Lines changed: 65 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.129 2001/05/27 09:59:28 petere Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.130 2001/05/30 12:57:36 momjian Exp $
1212
*
1313
* NOTES
1414
* The PerformAddAttribute() code, like most of the relation
@@ -51,9 +51,7 @@
5151
#include"catalog/pg_shadow.h"
5252
#include"utils/relcache.h"
5353

54-
#ifdef_DROP_COLUMN_HACK__
5554
#include"parser/parse.h"
56-
#endif/* _DROP_COLUMN_HACK__ */
5755
#include"access/genam.h"
5856

5957

@@ -1322,6 +1320,11 @@ AlterTableAddConstraint(char *relationName,
13221320
heap_close(rel,NoLock);
13231321
pfree(constlist);
13241322

1323+
break;
1324+
}
1325+
caseCONSTR_PRIMARY:
1326+
{
1327+
13251328
break;
13261329
}
13271330
default:
@@ -1585,13 +1588,71 @@ AlterTableAddConstraint(char *relationName,
15851588

15861589
/*
15871590
* ALTER TABLE DROP CONSTRAINT
1591+
* Note: It is legal to remove a constraint with name "" as it is possible
1592+
* to add a constraint with name "".
1593+
* Christopher Kings-Lynne
15881594
*/
15891595
void
15901596
AlterTableDropConstraint(constchar*relationName,
15911597
boolinh,constchar*constrName,
15921598
intbehavior)
15931599
{
1594-
elog(ERROR,"ALTER TABLE / DROP CONSTRAINT is not implemented");
1600+
Relationrel;
1601+
intdeleted;
1602+
1603+
#ifndefNO_SECURITY
1604+
if (!pg_ownercheck(GetUserId(),relationName,RELNAME))
1605+
elog(ERROR,"ALTER TABLE: permission denied");
1606+
#endif
1607+
1608+
/* We don't support CASCADE yet - in fact, RESTRICT
1609+
* doesn't work to the spec either! */
1610+
if (behavior==CASCADE)
1611+
elog(ERROR,"ALTER TABLE / DROP CONSTRAINT does not support the CASCADE keyword");
1612+
1613+
/*
1614+
* Acquire an exclusive lock on the target relation for
1615+
* the duration of the operation.
1616+
*/
1617+
1618+
rel=heap_openr(relationName,AccessExclusiveLock);
1619+
1620+
/* Disallow DROP CONSTRAINT on views, indexes, sequences, etc */
1621+
if (rel->rd_rel->relkind!=RELKIND_RELATION)
1622+
elog(ERROR,"ALTER TABLE / DROP CONSTRAINT: %s is not a table",
1623+
relationName);
1624+
1625+
/*
1626+
* Since all we have is the name of the constraint, we have to look through
1627+
* all catalogs that could possibly contain a constraint for this relation.
1628+
* We also keep a count of the number of constraints removed.
1629+
*/
1630+
1631+
deleted=0;
1632+
1633+
/*
1634+
* First, we remove all CHECK constraints with the given name
1635+
*/
1636+
1637+
deleted+=RemoveCheckConstraint(rel,constrName,inh);
1638+
1639+
/*
1640+
* Now we remove NULL, UNIQUE, PRIMARY KEY and FOREIGN KEY constraints.
1641+
*
1642+
* Unimplemented.
1643+
*/
1644+
1645+
/* Close the target relation */
1646+
heap_close(rel,NoLock);
1647+
1648+
/* If zero constraints deleted, complain */
1649+
if (deleted==0)
1650+
elog(ERROR,"ALTER TABLE / DROP CONSTRAINT: %s does not exist",
1651+
constrName);
1652+
/* Otherwise if more than one constraint deleted, notify */
1653+
elseif (deleted>1)
1654+
elog(NOTICE,"Multiple constraints dropped");
1655+
15951656
}
15961657

15971658

‎src/include/catalog/heap.h

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $Id: heap.h,v 1.35 2001/05/07 00:43:24 tgl Exp $
10+
* $Id: heap.h,v 1.36 2001/05/30 12:57:36 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -45,6 +45,9 @@ extern void AddRelationRawConstraints(Relation rel,
4545
List*rawColDefaults,
4646
List*rawConstraints);
4747

48+
externintRemoveCheckConstraint(Relationrel,constchar*constrName,boolinh);
49+
50+
4851
externForm_pg_attributeSystemAttributeDefinition(AttrNumberattno);
4952

5053
#endif/* HEAP_H */

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp