From d6f96ed94e73052f99a2e545ed17a8b2fdc1fb8a Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Wed, 8 Dec 2021 11:09:44 +0100 Subject: 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 Discussion: https://www.postgresql.org/message-id/flat/CACqFVBZQyMYJV=njbSMxf+rbDHpx=W=B7AEaMKn8dWn9OZJY7w@mail.gmail.com --- src/backend/parser/gram.y | 134 ++++++++++++++++++++++++++++++++++++++-------- 1 file changed, 111 insertions(+), 23 deletions(-) (limited to 'src/backend/parser') diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 86ce33bd97a..3d4dd43e47b 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -141,6 +141,19 @@ typedef struct GroupClause List *list; } GroupClause; +/* Private structs for the result of key_actions and key_action productions */ +typedef struct KeyAction +{ + char action; + List *cols; +} KeyAction; + +typedef struct KeyActions +{ + KeyAction *updateAction; + KeyAction *deleteAction; +} KeyActions; + /* ConstraintAttributeSpec yields an integer bitmask of these flags: */ #define CAS_NOT_DEFERRABLE 0x01 #define CAS_DEFERRABLE 0x02 @@ -265,6 +278,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); struct SelectLimit *selectlimit; SetQuantifier setquantifier; struct GroupClause *groupclause; + struct KeyActions *keyactions; + struct KeyAction *keyaction; } %type stmt toplevel_stmt schema_stmt routine_body_stmt @@ -570,7 +585,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type column_compression opt_column_compression %type ColQualList %type ColConstraint ColConstraintElem ConstraintAttr -%type key_actions key_delete key_match key_update key_action +%type key_match +%type key_delete key_update key_action +%type key_actions %type ConstraintAttributeSpec ConstraintAttributeElem %type ExistingIndex @@ -3690,8 +3707,9 @@ ColConstraintElem: n->fk_attrs = NIL; n->pk_attrs = $3; n->fk_matchtype = $4; - n->fk_upd_action = (char) ($5 >> 8); - n->fk_del_action = (char) ($5 & 0xFF); + n->fk_upd_action = ($5)->updateAction->action; + n->fk_del_action = ($5)->deleteAction->action; + n->fk_del_set_cols = ($5)->deleteAction->cols; n->skip_validation = false; n->initially_valid = true; $$ = (Node *)n; @@ -3901,8 +3919,9 @@ ConstraintElem: n->fk_attrs = $4; n->pk_attrs = $8; n->fk_matchtype = $9; - n->fk_upd_action = (char) ($10 >> 8); - n->fk_del_action = (char) ($10 & 0xFF); + n->fk_upd_action = ($10)->updateAction->action; + n->fk_del_action = ($10)->deleteAction->action; + n->fk_del_set_cols = ($10)->deleteAction->cols; processCASbits($11, @11, "FOREIGN KEY", &n->deferrable, &n->initdeferred, &n->skip_validation, NULL, @@ -3980,37 +3999,106 @@ OptWhereClause: | /*EMPTY*/ { $$ = NULL; } ; -/* - * We combine the update and delete actions into one value temporarily - * for simplicity of parsing, and then break them down again in the - * calling production. update is in the left 8 bits, delete in the right. - * Note that NOACTION is the default. - */ key_actions: key_update - { $$ = ($1 << 8) | (FKCONSTR_ACTION_NOACTION & 0xFF); } + { + KeyActions *n = palloc(sizeof(KeyActions)); + n->updateAction = $1; + n->deleteAction = palloc(sizeof(KeyAction)); + n->deleteAction->action = FKCONSTR_ACTION_NOACTION; + n->deleteAction->cols = NIL; + $$ = n; + } | key_delete - { $$ = (FKCONSTR_ACTION_NOACTION << 8) | ($1 & 0xFF); } + { + KeyActions *n = palloc(sizeof(KeyActions)); + n->updateAction = palloc(sizeof(KeyAction)); + n->updateAction->action = FKCONSTR_ACTION_NOACTION; + n->updateAction->cols = NIL; + n->deleteAction = $1; + $$ = n; + } | key_update key_delete - { $$ = ($1 << 8) | ($2 & 0xFF); } + { + KeyActions *n = palloc(sizeof(KeyActions)); + n->updateAction = $1; + n->deleteAction = $2; + $$ = n; + } | key_delete key_update - { $$ = ($2 << 8) | ($1 & 0xFF); } + { + KeyActions *n = palloc(sizeof(KeyActions)); + n->updateAction = $2; + n->deleteAction = $1; + $$ = n; + } | /*EMPTY*/ - { $$ = (FKCONSTR_ACTION_NOACTION << 8) | (FKCONSTR_ACTION_NOACTION & 0xFF); } + { + KeyActions *n = palloc(sizeof(KeyActions)); + n->updateAction = palloc(sizeof(KeyAction)); + n->updateAction->action = FKCONSTR_ACTION_NOACTION; + n->updateAction->cols = NIL; + n->deleteAction = palloc(sizeof(KeyAction)); + n->deleteAction->action = FKCONSTR_ACTION_NOACTION; + n->deleteAction->cols = NIL; + $$ = n; + } ; -key_update: ON UPDATE key_action { $$ = $3; } +key_update: ON UPDATE key_action + { + if (($3)->cols) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("a column list with %s is only supported for ON DELETE actions", + ($3)->action == FKCONSTR_ACTION_SETNULL ? "SET NULL" : "SET DEFAULT"), + parser_errposition(@1))); + $$ = $3; + } ; -key_delete: ON DELETE_P key_action { $$ = $3; } +key_delete: ON DELETE_P key_action + { + $$ = $3; + } ; key_action: - NO ACTION { $$ = FKCONSTR_ACTION_NOACTION; } - | RESTRICT { $$ = FKCONSTR_ACTION_RESTRICT; } - | CASCADE { $$ = FKCONSTR_ACTION_CASCADE; } - | SET NULL_P { $$ = FKCONSTR_ACTION_SETNULL; } - | SET DEFAULT { $$ = FKCONSTR_ACTION_SETDEFAULT; } + NO ACTION + { + KeyAction *n = palloc(sizeof(KeyAction)); + n->action = FKCONSTR_ACTION_NOACTION; + n->cols = NIL; + $$ = n; + } + | RESTRICT + { + KeyAction *n = palloc(sizeof(KeyAction)); + n->action = FKCONSTR_ACTION_RESTRICT; + n->cols = NIL; + $$ = n; + } + | CASCADE + { + KeyAction *n = palloc(sizeof(KeyAction)); + n->action = FKCONSTR_ACTION_CASCADE; + n->cols = NIL; + $$ = n; + } + | SET NULL_P opt_column_list + { + KeyAction *n = palloc(sizeof(KeyAction)); + n->action = FKCONSTR_ACTION_SETNULL; + n->cols = $3; + $$ = n; + } + | SET DEFAULT opt_column_list + { + KeyAction *n = palloc(sizeof(KeyAction)); + n->action = FKCONSTR_ACTION_SETDEFAULT; + n->cols = $3; + $$ = n; + } ; OptInherit: INHERITS '(' qualified_name_list ')' { $$ = $3; } -- cgit v1.2.3