From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Repair misbehavior with duplicate entries in FK SET column lists
Date: Sat, 05 Apr 2025 00:11:59 +0000
Message-ID: <E1u0r8h-002iTy-0O@gemulon.postgresql.org> (raw)
Repair misbehavior with duplicate entries in FK SET column lists.
Since v15 we've had an option to apply a foreign key constraint's
ON DELETE SET DEFAULT or SET NULL action to just some of the
referencing columns. There was not a check for duplicate entries in
the list of columns-to-set, though. That caused a potential memory
stomp in CreateConstraintEntry(), which incautiously assumed that
the list of columns-to-set couldn't be longer than the number of key
columns. Even after fixing that, the case doesn't work because you
get an error like "multiple assignments to same column" from the SQL
command that is generated to do the update.
We could either raise an error for duplicate columns or silently
suppress the dups, and after a bit of thought I chose to do the
latter. This is motivated by the fact that duplicates in the FK
column list are legal, so it's not real clear why duplicates
in the columns-to-set list shouldn't be. Of course there's no
need to actually set the column more than once.
I left in the fix in CreateConstraintEntry() too, just because
it didn't seem like such low-level code ought to be making
assumptions about what it's handed.
Bug: #18879
Reported-by: Yu Liang <luy70@psu.edu>
Author: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/18879-259fc59d072bd4d7@postgresql.org
Backpatch-through: 15
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/43b8e6c4abfb632bbff5b3a26dc39bf3f188afff
Modified Files
--------------
src/backend/catalog/pg_constraint.c | 3 ++-
src/backend/commands/tablecmds.c | 35 ++++++++++++++++++++++++-------
src/test/regress/expected/foreign_key.out | 3 ++-
src/test/regress/sql/foreign_key.sql | 3 ++-
4 files changed, 34 insertions(+), 10 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: tgl@sss.pgh.pa.us, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Repair misbehavior with duplicate entries in FK SET column lists
In-Reply-To: <E1u0r8h-002iTy-0O@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox