Skip to content

Commit d6f96ed

Browse files
peterepetere
committed
Allow specifying column list for foreign key ON DELETE SET actions
Extend the foreign key ON DELETE actions SET NULL and SET DEFAULT by allowing the specification of a column list, like CREATE TABLE posts ( ... FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL (author_id) ); If a column list is specified, only those columns are set to null/default, instead of all the columns in the foreign-key constraint. This is useful for multitenant or sharded schemas, where the tenant or shard ID is included in the primary key of all tables but shouldn't be set to null. Author: Paul Martinez <paulmtz@google.com> Discussion: https://www.postgresql.org/message-id/flat/CACqFVBZQyMYJV=njbSMxf+rbDHpx=W=B7AEaMKn8dWn9OZJY7w@mail.gmail.com
1 parent e464cb7 commit d6f96ed

22 files changed

+557
-72
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2708,6 +2708,18 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
27082708
</para></entry>
27092709
</row>
27102710

2711+
<row>
2712+
<entry role="catalog_table_entry"><para role="column_definition">
2713+
<structfield>confdelsetcols</structfield> <type>int2[]</type>
2714+
(references <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attnum</structfield>)
2715+
</para>
2716+
<para>
2717+
If a foreign key with a <literal>SET NULL</literal> or <literal>SET
2718+
DEFAULT</literal> delete action, the columns that will be updated.
2719+
If null, all of the referencing columns will be updated.
2720+
</para></entry>
2721+
</row>
2722+
27112723
<row>
27122724
<entry role="catalog_table_entry"><para role="column_definition">
27132725
<structfield>conexclop</structfield> <type>oid[]</type>

‎doc/src/sgml/ddl.sgml

Lines changed: 32 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1083,10 +1083,41 @@ CREATE TABLE order_items (
10831083
manager to null or a default might be useful.
10841084
</para>
10851085

1086+
<para>
1087+
The actions <literal>SET NULL</literal> and <literal>SET DEFAULT</literal>
1088+
can take a column list to specify which columns to set. Normally, all
1089+
columns of the foreign-key constraint are set; setting only a subset is
1090+
useful in some special cases. Consider the following example:
1091+
<programlisting>
1092+
CREATE TABLE tenants (
1093+
tenant_id integer PRIMARY KEY
1094+
);
1095+
1096+
CREATE TABLE users (
1097+
tenant_id integer REFERENCES tenants ON DELETE CASCADE,
1098+
user_id integer NOT NULL,
1099+
PRIMARY KEY (tenant_id, user_id)
1100+
);
1101+
1102+
CREATE TABLE posts (
1103+
tenant_id integer REFERENCES tenants ON DELETE CASCADE,
1104+
post_id integer NOT NULL,
1105+
author_id integer,
1106+
PRIMARY KEY (tenant_id, post_id),
1107+
FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL <emphasis>(author_id)</emphasis>
1108+
);
1109+
</programlisting>
1110+
Without the specification of the column, the foreign key would also set
1111+
the column <literal>tenant_id</literal> to null, but that column is still
1112+
required as part of the primary key.
1113+
</para>
1114+
10861115
<para>
10871116
Analogous to <literal>ON DELETE</literal> there is also
10881117
<literal>ON UPDATE</literal> which is invoked when a referenced
1089-
column is changed (updated). The possible actions are the same.
1118+
column is changed (updated). The possible actions are the same,
1119+
except that column lists cannot be specified for <literal>SET
1120+
NULL</literal> and <literal>SET DEFAULT</literal>.
10901121
In this case, <literal>CASCADE</literal> means that the updated values of the
10911122
referenced column(s) should be copied into the referencing row(s).
10921123
</para>

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -138,7 +138,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
138138

139139
<phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase>
140140

141-
{ NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT }
141+
{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] | SET DEFAULT [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] }
142142
</synopsis>
143143
</refsynopsisdiv>
144144

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 19 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -108,7 +108,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
108108

109109
<phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase>
110110

111-
{ NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT }
111+
{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] | SET DEFAULT [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] }
112112
</synopsis>
113113

114114
</refsynopsisdiv>
@@ -1169,19 +1169,23 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
11691169
</varlistentry>
11701170

11711171
<varlistentry>
1172-
<term><literal>SET NULL</literal></term>
1172+
<term><literal>SET NULL [ ( <replaceable>column_name</replaceable> [, ... ] ) ]</literal></term>
11731173
<listitem>
11741174
<para>
1175-
Set the referencing column(s) to null.
1175+
Set all of the referencing columns, or a specified subset of the
1176+
referencing columns, to null. A subset of columns can only be
1177+
specified for <literal>ON DELETE</literal> actions.
11761178
</para>
11771179
</listitem>
11781180
</varlistentry>
11791181

11801182
<varlistentry>
1181-
<term><literal>SET DEFAULT</literal></term>
1183+
<term><literal>SET DEFAULT [ ( <replaceable>column_name</replaceable> [, ... ] ) ]</literal></term>
11821184
<listitem>
11831185
<para>
1184-
Set the referencing column(s) to their default values.
1186+
Set all of the referencing columns, or a specified subset of the
1187+
referencing columns, to their default values. A subset of columns
1188+
can only be specified for <literal>ON DELETE</literal> actions.
11851189
(There must be a row in the referenced table matching the default
11861190
values, if they are not null, or the operation will fail.)
11871191
</para>
@@ -2223,6 +2227,16 @@ CREATE TABLE cities_partdef
22232227
</para>
22242228
</refsect2>
22252229

2230+
<refsect2>
2231+
<title>Foreign-Key Constraint Actions</title>
2232+
2233+
<para>
2234+
The ability to specify column lists in the foreign-key actions
2235+
<literal>SET DEFAULT</literal> and <literal>SET NULL</literal> is a
2236+
<productname>PostgreSQL</productname> extension.
2237+
</para>
2238+
</refsect2>
2239+
22262240
<refsect2>
22272241
<title><literal>NULL</literal> <quote>Constraint</quote></title>
22282242

‎src/backend/catalog/heap.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2441,6 +2441,8 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
24412441
0,
24422442
' ',
24432443
' ',
2444+
NULL,
2445+
0,
24442446
' ',
24452447
NULL, /* not an exclusion constraint */
24462448
expr, /* Tree form of check constraint */

‎src/backend/catalog/index.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1969,6 +1969,8 @@ index_constraint_create(Relation heapRelation,
19691969
0,
19701970
' ',
19711971
' ',
1972+
NULL,
1973+
0,
19721974
' ',
19731975
indexInfo->ii_ExclusionOps,
19741976
NULL, /* no check constraint */

‎src/backend/catalog/pg_constraint.c

Lines changed: 50 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -68,6 +68,8 @@ CreateConstraintEntry(const char *constraintName,
6868
int foreignNKeys,
6969
char foreignUpdateType,
7070
char foreignDeleteType,
71+
const int16 *fkDeleteSetCols,
72+
int numFkDeleteSetCols,
7173
char foreignMatchType,
7274
const Oid *exclOp,
7375
Node *conExpr,
@@ -88,6 +90,7 @@ CreateConstraintEntry(const char *constraintName,
8890
ArrayType *conppeqopArray;
8991
ArrayType *conffeqopArray;
9092
ArrayType *conexclopArray;
93+
ArrayType *confdelsetcolsArray;
9194
NameData cname;
9295
int i;
9396
ObjectAddress conobject;
@@ -136,13 +139,24 @@ CreateConstraintEntry(const char *constraintName,
136139
fkdatums[i] = ObjectIdGetDatum(ffEqOp[i]);
137140
conffeqopArray = construct_array(fkdatums, foreignNKeys,
138141
OIDOID, sizeof(Oid), true, TYPALIGN_INT);
142+
143+
if (numFkDeleteSetCols > 0)
144+
{
145+
for (i = 0; i < numFkDeleteSetCols; i++)
146+
fkdatums[i] = Int16GetDatum(fkDeleteSetCols[i]);
147+
confdelsetcolsArray = construct_array(fkdatums, numFkDeleteSetCols,
148+
INT2OID, 2, true, TYPALIGN_SHORT);
149+
}
150+
else
151+
confdelsetcolsArray = NULL;
139152
}
140153
else
141154
{
142155
confkeyArray = NULL;
143156
conpfeqopArray = NULL;
144157
conppeqopArray = NULL;
145158
conffeqopArray = NULL;
159+
confdelsetcolsArray = NULL;
146160
}
147161

148162
if (exclOp != NULL)
@@ -211,6 +225,11 @@ CreateConstraintEntry(const char *constraintName,
211225
else
212226
nulls[Anum_pg_constraint_conffeqop - 1] = true;
213227

228+
if (confdelsetcolsArray)
229+
values[Anum_pg_constraint_confdelsetcols - 1] = PointerGetDatum(confdelsetcolsArray);
230+
else
231+
nulls[Anum_pg_constraint_confdelsetcols - 1] = true;
232+
214233
if (conexclopArray)
215234
values[Anum_pg_constraint_conexclop - 1] = PointerGetDatum(conexclopArray);
216235
else
@@ -1157,13 +1176,15 @@ get_primary_key_attnos(Oid relid, bool deferrableOk, Oid *constraintOid)
11571176
/*
11581177
* Extract data from the pg_constraint tuple of a foreign-key constraint.
11591178
*
1160-
* All arguments save the first are output arguments; the last three of them
1161-
* can be passed as NULL if caller doesn't need them.
1179+
* All arguments save the first are output arguments. All output arguments
1180+
* other than numfks, conkey and confkey can be passed as NULL if caller
1181+
* doesn't need them.
11621182
*/
11631183
void
11641184
DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
11651185
AttrNumber *conkey, AttrNumber *confkey,
1166-
Oid *pf_eq_oprs, Oid *pp_eq_oprs, Oid *ff_eq_oprs)
1186+
Oid *pf_eq_oprs, Oid *pp_eq_oprs, Oid *ff_eq_oprs,
1187+
int *num_fk_del_set_cols, AttrNumber *fk_del_set_cols)
11671188
{
11681189
Oid constrId;
11691190
Datum adatum;
@@ -1260,6 +1281,32 @@ DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
12601281
pfree(arr); /* free de-toasted copy, if any */
12611282
}
12621283

1284+
if (fk_del_set_cols)
1285+
{
1286+
adatum = SysCacheGetAttr(CONSTROID, tuple,
1287+
Anum_pg_constraint_confdelsetcols, &isNull);
1288+
if (isNull)
1289+
{
1290+
*num_fk_del_set_cols = 0;
1291+
}
1292+
else
1293+
{
1294+
int num_delete_cols;
1295+
1296+
arr = DatumGetArrayTypeP(adatum); /* ensure not toasted */
1297+
if (ARR_NDIM(arr) != 1 ||
1298+
ARR_HASNULL(arr) ||
1299+
ARR_ELEMTYPE(arr) != INT2OID)
1300+
elog(ERROR, "confdelsetcols is not a 1-D smallint array");
1301+
num_delete_cols = ARR_DIMS(arr)[0];
1302+
memcpy(fk_del_set_cols, ARR_DATA_PTR(arr), num_delete_cols * sizeof(int16));
1303+
if ((Pointer) arr != DatumGetPointer(adatum))
1304+
pfree(arr); /* free de-toasted copy, if any */
1305+
1306+
*num_fk_del_set_cols = num_delete_cols;
1307+
}
1308+
}
1309+
12631310
*numfks = numkeys;
12641311
}
12651312

0 commit comments

Comments
 (0)