pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pgsql: Restore the previous semantics of get_constraint_index().
2+ messages / 1 participants
[nested] [flat]

* pgsql: Restore the previous semantics of get_constraint_index().
@ 2022-03-11 18:47  Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2022-03-11 18:47 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

Restore the previous semantics of get_constraint_index().

Commit 8b069ef5d changed this function to look at pg_constraint.conindid
rather than searching pg_depend.  That was a good performance improvement,
but it failed to preserve the exact semantics.  The old code would only
return an index that was "owned by" (internally dependent on) the
specified constraint, whereas the new code will also return indexes that
are just referenced by foreign key constraints.  This confuses ALTER
TABLE, which was implicitly expecting the previous semantics, into
failing with errors like
    ERROR:  relation 146621 has multiple clustered indexes
or
    ERROR:  "pk_attbl" is not an index for table "atref"

We can fix this without reverting the performance improvement by adding
a contype check in get_constraint_index().  Another way could be to
make ALTER TABLE check it, but I'm worried that extension code could
also have subtle dependencies on the old semantics.

Tom Lane and Japin Li, per bug #17409 from Holly Roberts.
Back-patch to v14 where the error crept in.

Discussion: https://postgr.es/m/17409-52871dda8b5741cb@postgresql.org

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/641f3dffcdf1c7378cfb94c98b6642793181d6db

Modified Files
--------------
src/backend/utils/cache/lsyscache.c       | 16 +++++++++++++---
src/test/regress/expected/alter_table.out | 14 ++++++++++++++
src/test/regress/sql/alter_table.sql      | 18 ++++++++++++++++++
3 files changed, 45 insertions(+), 3 deletions(-)



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* pgsql: Restore the previous semantics of get_constraint_index().
@ 2022-03-11 18:47  Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2022-03-11 18:47 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

Restore the previous semantics of get_constraint_index().

Commit 8b069ef5d changed this function to look at pg_constraint.conindid
rather than searching pg_depend.  That was a good performance improvement,
but it failed to preserve the exact semantics.  The old code would only
return an index that was "owned by" (internally dependent on) the
specified constraint, whereas the new code will also return indexes that
are just referenced by foreign key constraints.  This confuses ALTER
TABLE, which was implicitly expecting the previous semantics, into
failing with errors like
    ERROR:  relation 146621 has multiple clustered indexes
or
    ERROR:  "pk_attbl" is not an index for table "atref"

We can fix this without reverting the performance improvement by adding
a contype check in get_constraint_index().  Another way could be to
make ALTER TABLE check it, but I'm worried that extension code could
also have subtle dependencies on the old semantics.

Tom Lane and Japin Li, per bug #17409 from Holly Roberts.
Back-patch to v14 where the error crept in.

Discussion: https://postgr.es/m/17409-52871dda8b5741cb@postgresql.org

Branch
------
REL_14_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/8dcd1c3564f04bc1f71020c150b31deea07b7a95

Modified Files
--------------
src/backend/utils/cache/lsyscache.c       | 16 +++++++++++++---
src/test/regress/expected/alter_table.out | 14 ++++++++++++++
src/test/regress/sql/alter_table.sql      | 18 ++++++++++++++++++
3 files changed, 45 insertions(+), 3 deletions(-)



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2022-03-11 18:47 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-03-11 18:47 pgsql: Restore the previous semantics of get_constraint_index(). Tom Lane <tgl@sss.pgh.pa.us>
2022-03-11 18:47 pgsql: Restore the previous semantics of get_constraint_index(). Tom Lane <tgl@sss.pgh.pa.us>

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