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

Commitc1b9ec2

Browse files
committed
Add system catalog columns pg_constraint.conindid and pg_trigger.tgconstrindid.
conindid is the index supporting a constraint. We can use this not only forunique/primary-key constraints, but also foreign-key constraints, whichdepend on the unique index that constrains the referenced columns.tgconstrindid is just copied from the constraint's conindid field, or iszero for triggers not associated with constraints.This is mainly intended as infrastructure for upcoming patches, but it hassome virtue in itself, since it exposes a relationship that you formerlyhad to grovel in pg_depend to determine. I simplified one information_schemaview accordingly. (There is a pg_dump query that could also use conindid,but I left it alone because it wasn't clear it'd get any faster.)
1 parent3694250 commitc1b9ec2

File tree

15 files changed

+120
-78
lines changed

15 files changed

+120
-78
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.201 2009/07/01 23:57:33 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.202 2009/07/28 02:56:29 tgl Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -1748,6 +1748,14 @@
17481748
<entry>The domain this constraint is on; 0 if not a domain constraint</entry>
17491749
</row>
17501750

1751+
<row>
1752+
<entry><structfield>conindid</structfield></entry>
1753+
<entry><type>oid</type></entry>
1754+
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
1755+
<entry>The index supporting this constraint, if it's a unique, primary
1756+
key, or foreign key constraint; else 0</entry>
1757+
</row>
1758+
17511759
<row>
17521760
<entry><structfield>confrelid</structfield></entry>
17531761
<entry><type>oid</type></entry>
@@ -4510,6 +4518,13 @@
45104518
<entry>The table referenced by a referential integrity constraint</entry>
45114519
</row>
45124520

4521+
<row>
4522+
<entry><structfield>tgconstrindid</structfield></entry>
4523+
<entry><type>oid</type></entry>
4524+
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
4525+
<entry>The index supporting a unique, primary key, or referential integrity constraint</entry>
4526+
</row>
4527+
45134528
<row>
45144529
<entry><structfield>tgconstraint</structfield></entry>
45154530
<entry><type>oid</type></entry>
@@ -4560,6 +4575,7 @@
45604575
When <structfield>tgconstraint</> is nonzero,
45614576
<structfield>tgisconstraint</> must be true, and
45624577
<structfield>tgconstrname</>, <structfield>tgconstrrelid</>,
4578+
<structfield>tgconstrindid</>,
45634579
<structfield>tgdeferrable</>, <structfield>tginitdeferred</> are redundant
45644580
with the referenced <structname>pg_constraint</> entry. The reason we
45654581
keep these fields is that we support <quote>stand-alone</> constraint

‎doc/src/sgml/trigger.sgml

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.56 2009/05/27 01:18:06 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.57 2009/07/28 02:56:29 tgl Exp $ -->
22

33
<chapter id="triggers">
44
<title>Triggers</title>
@@ -486,6 +486,7 @@ typedef struct Trigger
486486
bool tgenabled;
487487
bool tgisconstraint;
488488
Oid tgconstrrelid;
489+
Oid tgconstrindid;
489490
Oid tgconstraint;
490491
bool tgdeferrable;
491492
bool tginitdeferred;
@@ -497,7 +498,7 @@ typedef struct Trigger
497498
</programlisting>
498499

499500
where <structfield>tgname</> is the trigger's name,
500-
<structfield>tgnargs</> is number of arguments in
501+
<structfield>tgnargs</> isthenumber of arguments in
501502
<structfield>tgargs</>, and <structfield>tgargs</> is an array of
502503
pointers to the arguments specified in the <command>CREATE
503504
TRIGGER</command> statement. The other members are for internal use

‎src/backend/catalog/heap.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.354 2009/06/11 14:48:54 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.355 2009/07/28 02:56:29 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -1659,6 +1659,7 @@ StoreRelCheck(Relation rel, char *ccname, Node *expr,
16591659
attNos,/* attrs in the constraint */
16601660
keycount,/* # attrs in the constraint */
16611661
InvalidOid,/* not a domain constraint */
1662+
InvalidOid,/* no associated index */
16621663
InvalidOid,/* Foreign key fields */
16631664
NULL,
16641665
NULL,
@@ -1668,7 +1669,6 @@ StoreRelCheck(Relation rel, char *ccname, Node *expr,
16681669
' ',
16691670
' ',
16701671
' ',
1671-
InvalidOid,/* no associated index */
16721672
expr,/* Tree form check constraint */
16731673
ccbin,/* Binary form check constraint */
16741674
ccsrc,/* Source form check constraint */

‎src/backend/catalog/index.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.318 2009/06/11 14:48:55 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.319 2009/07/28 02:56:29 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -732,6 +732,7 @@ index_create(Oid heapRelationId,
732732
indexInfo->ii_KeyAttrNumbers,
733733
indexInfo->ii_NumIndexAttrs,
734734
InvalidOid,/* no domain */
735+
indexRelationId,/* index OID */
735736
InvalidOid,/* no foreign key */
736737
NULL,
737738
NULL,
@@ -741,7 +742,6 @@ index_create(Oid heapRelationId,
741742
' ',
742743
' ',
743744
' ',
744-
InvalidOid,/* no associated index */
745745
NULL,/* no check constraint */
746746
NULL,
747747
NULL,

‎src/backend/catalog/information_schema.sql

Lines changed: 4 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
*
55
* Copyright (c) 2003-2009, PostgreSQL Global Development Group
66
*
7-
* $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.57 2009/07/13 20:25:57 petere Exp $
7+
* $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.58 2009/07/28 02:56:29 tgl Exp $
88
*/
99

1010
/*
@@ -44,17 +44,6 @@ CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
4444
LANGUAGE sql IMMUTABLE-- intentionally not STRICT, to allow inlining
4545
AS'select $1 <@ $2 and $2 <@ $1';
4646

47-
/* Get the OID of the unique index that an FK constraint depends on*/
48-
CREATEFUNCTION_pg_underlying_index(oid) RETURNSoid
49-
LANGUAGE sql STRICT STABLE
50-
AS $$
51-
SELECT refobjidFROMpg_catalog.pg_depend
52-
WHERE classid='pg_catalog.pg_constraint'::pg_catalog.regclassAND
53-
objid= $1AND
54-
refclassid='pg_catalog.pg_class'::pg_catalog.regclassAND
55-
refobjsubid=0AND deptype='n'
56-
$$;
57-
5847
/* Given an index's OID and an underlying-table column number, return the
5948
* column's position in the index (NULL if not there)*/
6049
CREATEFUNCTION_pg_index_position(oid,smallint) RETURNSint
@@ -957,15 +946,15 @@ CREATE VIEW key_column_usage AS
957946
CAST(a.attnameAS sql_identifier)AS column_name,
958947
CAST((ss.x).nAS cardinal_number)AS ordinal_position,
959948
CAST(CASE WHEN contype='f' THEN
960-
_pg_index_position(_pg_underlying_index(ss.coid),
961-
ss.confkey[(ss.x).n])
949+
_pg_index_position(ss.conindid,ss.confkey[(ss.x).n])
962950
ELSENULL
963951
ENDAS cardinal_number)
964952
AS position_in_unique_constraint
965953
FROM pg_attribute a,
966954
(SELECTr.oidAS roid,r.relname,r.relowner,
967955
nc.nspnameAS nc_nspname,nr.nspnameAS nr_nspname,
968-
c.oidAS coid,c.conname,c.contype,c.confkey,c.confrelid,
956+
c.oidAS coid,c.conname,c.contype,c.conindid,
957+
c.confkey,c.confrelid,
969958
_pg_expandarray(c.conkey)AS x
970959
FROM pg_namespace nr, pg_class r, pg_namespace nc,
971960
pg_constraint c

‎src/backend/catalog/pg_constraint.c

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/pg_constraint.c,v 1.46 2009/07/16 06:33:42 petere Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/pg_constraint.c,v 1.47 2009/07/28 02:56:29 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -49,6 +49,7 @@ CreateConstraintEntry(const char *constraintName,
4949
constint16*constraintKey,
5050
intconstraintNKeys,
5151
OiddomainId,
52+
OidindexRelId,
5253
OidforeignRelId,
5354
constint16*foreignKey,
5455
constOid*pfEqOp,
@@ -58,7 +59,6 @@ CreateConstraintEntry(const char *constraintName,
5859
charforeignUpdateType,
5960
charforeignDeleteType,
6061
charforeignMatchType,
61-
OidindexRelId,
6262
Node*conExpr,
6363
constchar*conBin,
6464
constchar*conSrc,
@@ -144,6 +144,7 @@ CreateConstraintEntry(const char *constraintName,
144144
values[Anum_pg_constraint_condeferred-1]=BoolGetDatum(isDeferred);
145145
values[Anum_pg_constraint_conrelid-1]=ObjectIdGetDatum(relId);
146146
values[Anum_pg_constraint_contypid-1]=ObjectIdGetDatum(domainId);
147+
values[Anum_pg_constraint_conindid-1]=ObjectIdGetDatum(indexRelId);
147148
values[Anum_pg_constraint_confrelid-1]=ObjectIdGetDatum(foreignRelId);
148149
values[Anum_pg_constraint_confupdtype-1]=CharGetDatum(foreignUpdateType);
149150
values[Anum_pg_constraint_confdeltype-1]=CharGetDatum(foreignDeleteType);
@@ -273,11 +274,13 @@ CreateConstraintEntry(const char *constraintName,
273274
}
274275
}
275276

276-
if (OidIsValid(indexRelId))
277+
if (OidIsValid(indexRelId)&&constraintType==CONSTRAINT_FOREIGN)
277278
{
278279
/*
279280
* Register normal dependency on the unique index that supports a
280-
* foreign-key constraint.
281+
* foreign-key constraint. (Note: for indexes associated with
282+
* unique or primary-key constraints, the dependency runs the other
283+
* way, and is not made here.)
281284
*/
282285
ObjectAddressrelobject;
283286

‎src/backend/commands/tablecmds.c

Lines changed: 18 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.291 2009/07/20 02:42:27 adunstan Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.292 2009/07/28 02:56:30 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -152,6 +152,7 @@ typedef struct NewConstraint
152152
char*name;/* Constraint name, or NULL if none */
153153
ConstrTypecontype;/* CHECK or FOREIGN */
154154
Oidrefrelid;/* PK rel, if FOREIGN */
155+
Oidrefindid;/* OID of PK's index, if FOREIGN */
155156
Oidconid;/* OID of pg_constraint entry, if FOREIGN */
156157
Node*qual;/* Check expr or FkConstraint struct */
157158
List*qualstate;/* Execution state for CHECK */
@@ -247,9 +248,10 @@ static Oid transformFkeyCheckAttrs(Relation pkrel,
247248
Oid*opclasses);
248249
staticvoidcheckFkeyPermissions(Relationrel,int16*attnums,intnatts);
249250
staticvoidvalidateForeignKeyConstraint(FkConstraint*fkconstraint,
250-
Relationrel,Relationpkrel,OidconstraintOid);
251+
Relationrel,Relationpkrel,
252+
OidpkindOid,OidconstraintOid);
251253
staticvoidcreateForeignKeyTriggers(Relationrel,FkConstraint*fkconstraint,
252-
OidconstraintOid);
254+
OidconstraintOid,OidindexOid);
253255
staticvoidATController(Relationrel,List*cmds,boolrecurse);
254256
staticvoidATPrepCmd(List**wqueue,Relationrel,AlterTableCmd*cmd,
255257
boolrecurse,boolrecursing);
@@ -2915,6 +2917,7 @@ ATRewriteTables(List **wqueue)
29152917
refrel=heap_open(con->refrelid,RowShareLock);
29162918

29172919
validateForeignKeyConstraint(fkconstraint,rel,refrel,
2920+
con->refindid,
29182921
con->conid);
29192922

29202923
heap_close(refrel,NoLock);
@@ -4819,6 +4822,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
48194822
numfks,
48204823
InvalidOid,/* not a domain
48214824
* constraint */
4825+
indexOid,
48224826
RelationGetRelid(pkrel),
48234827
pkattnum,
48244828
pfeqoperators,
@@ -4828,7 +4832,6 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
48284832
fkconstraint->fk_upd_action,
48294833
fkconstraint->fk_del_action,
48304834
fkconstraint->fk_matchtype,
4831-
indexOid,
48324835
NULL,/* no check constraint */
48334836
NULL,
48344837
NULL,
@@ -4838,7 +4841,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
48384841
/*
48394842
* Create the triggers that will enforce the constraint.
48404843
*/
4841-
createForeignKeyTriggers(rel,fkconstraint,constrOid);
4844+
createForeignKeyTriggers(rel,fkconstraint,constrOid,indexOid);
48424845

48434846
/*
48444847
* Tell Phase 3 to check that the constraint is satisfied by existing rows
@@ -4852,6 +4855,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
48524855
newcon->name=fkconstraint->constr_name;
48534856
newcon->contype=CONSTR_FOREIGN;
48544857
newcon->refrelid=RelationGetRelid(pkrel);
4858+
newcon->refindid=indexOid;
48554859
newcon->conid=constrOid;
48564860
newcon->qual= (Node*)fkconstraint;
48574861

@@ -5141,6 +5145,7 @@ static void
51415145
validateForeignKeyConstraint(FkConstraint*fkconstraint,
51425146
Relationrel,
51435147
Relationpkrel,
5148+
OidpkindOid,
51445149
OidconstraintOid)
51455150
{
51465151
HeapScanDescscan;
@@ -5156,6 +5161,7 @@ validateForeignKeyConstraint(FkConstraint *fkconstraint,
51565161
trig.tgenabled=TRIGGER_FIRES_ON_ORIGIN;
51575162
trig.tgisconstraint= TRUE;
51585163
trig.tgconstrrelid=RelationGetRelid(pkrel);
5164+
trig.tgconstrindid=pkindOid;
51595165
trig.tgconstraint=constraintOid;
51605166
trig.tgdeferrable= FALSE;
51615167
trig.tginitdeferred= FALSE;
@@ -5209,7 +5215,7 @@ validateForeignKeyConstraint(FkConstraint *fkconstraint,
52095215

52105216
staticvoid
52115217
CreateFKCheckTrigger(RangeVar*myRel,FkConstraint*fkconstraint,
5212-
OidconstraintOid,boolon_insert)
5218+
OidconstraintOid,OidindexOid,boolon_insert)
52135219
{
52145220
CreateTrigStmt*fk_trigger;
52155221

@@ -5237,7 +5243,7 @@ CreateFKCheckTrigger(RangeVar *myRel, FkConstraint *fkconstraint,
52375243
fk_trigger->constrrel=fkconstraint->pktable;
52385244
fk_trigger->args=NIL;
52395245

5240-
(void)CreateTrigger(fk_trigger,constraintOid, false);
5246+
(void)CreateTrigger(fk_trigger,constraintOid,indexOid,false);
52415247

52425248
/* Make changes-so-far visible */
52435249
CommandCounterIncrement();
@@ -5248,7 +5254,7 @@ CreateFKCheckTrigger(RangeVar *myRel, FkConstraint *fkconstraint,
52485254
*/
52495255
staticvoid
52505256
createForeignKeyTriggers(Relationrel,FkConstraint*fkconstraint,
5251-
OidconstraintOid)
5257+
OidconstraintOid,OidindexOid)
52525258
{
52535259
RangeVar*myRel;
52545260
CreateTrigStmt*fk_trigger;
@@ -5267,8 +5273,8 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
52675273
* Build and execute a CREATE CONSTRAINT TRIGGER statement for the CHECK
52685274
* action for both INSERTs and UPDATEs on the referencing table.
52695275
*/
5270-
CreateFKCheckTrigger(myRel,fkconstraint,constraintOid, true);
5271-
CreateFKCheckTrigger(myRel,fkconstraint,constraintOid, false);
5276+
CreateFKCheckTrigger(myRel,fkconstraint,constraintOid,indexOid,true);
5277+
CreateFKCheckTrigger(myRel,fkconstraint,constraintOid,indexOid,false);
52725278

52735279
/*
52745280
* Build and execute a CREATE CONSTRAINT TRIGGER statement for the ON
@@ -5316,7 +5322,7 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
53165322
}
53175323
fk_trigger->args=NIL;
53185324

5319-
(void)CreateTrigger(fk_trigger,constraintOid, false);
5325+
(void)CreateTrigger(fk_trigger,constraintOid,indexOid,false);
53205326

53215327
/* Make changes-so-far visible */
53225328
CommandCounterIncrement();
@@ -5367,7 +5373,7 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
53675373
}
53685374
fk_trigger->args=NIL;
53695375

5370-
(void)CreateTrigger(fk_trigger,constraintOid, false);
5376+
(void)CreateTrigger(fk_trigger,constraintOid,indexOid,false);
53715377
}
53725378

53735379
/*

0 commit comments

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