pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix bogus dependency handling for GENERATED expressions.
Date: Mon, 21 Mar 2022 18:59:13 +0000
Message-ID: <E1nWNFI-000kv4-L7@gemulon.postgresql.org> (raw)

Fix bogus dependency handling for GENERATED expressions.

For GENERATED columns, we record all dependencies of the generation
expression as AUTO dependencies of the column itself.  This means
that the generated column is silently dropped if any dependency
is removed, even if CASCADE wasn't specified.  This is at least
a POLA violation, but I think it's actually based on a misreading
of the standard.  The standard does say that you can't drop a
dependent GENERATED column in RESTRICT mode; but that's buried down
in a subparagraph, on a different page from some pseudocode that
makes it look like an AUTO drop is being suggested.

Change this to be more like the way that we handle regular default
expressions, ie record the dependencies as NORMAL dependencies of
the pg_attrdef entry.  Also, make the pg_attrdef entry's dependency
on the column itself be INTERNAL not AUTO.  That has two effects:

* the column will go away, not just lose its default, if any
dependency of the expression is dropped with CASCADE.  So we
don't need any special mechanism to make that happen.

* it provides an additional cross-check preventing someone from
dropping the default expression without dropping the column.

catversion bump because of change in the contents of pg_depend
(which also requires a change in one information_schema view).

Per bug #17439 from Kevin Humphreys.  Although this is a longstanding
bug, it seems impractical to back-patch because of the need for
catalog contents changes.

Discussion: https://postgr.es/m/17439-7df4421197e928f0@postgresql.org

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/cb02fcb4c95bae08adaca1202c2081cfc81a28b5

Modified Files
--------------
src/backend/catalog/information_schema.sql |  12 ++-
src/backend/catalog/pg_attrdef.c           |  30 ++----
src/backend/commands/tablecmds.c           | 163 ++++++++++++++---------------
src/bin/pg_dump/pg_dump_sort.c             |   8 +-
src/include/catalog/catversion.h           |   2 +-
src/test/regress/expected/generated.out    |  11 +-
src/test/regress/sql/generated.sql         |   4 +-
7 files changed, 113 insertions(+), 117 deletions(-)



Message-ID: <E1nWNFI-000kv4-L7@gemulon.postgresql.org>
Permalink:  ../E1nWNFI-000kv4-L7@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1nWNFI-000kv4-L7@gemulon.postgresql.org

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-committers@postgresql.org
  Cc: tgl@sss.pgh.pa.us
  Subject: Re: pgsql: Fix bogus dependency handling for GENERATED expressions.
  In-Reply-To: <E1nWNFI-000kv4-L7@gemulon.postgresql.org>
  References: <E1nWNFI-000kv4-L7@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