From: Tom Lane <tgl@sss.pgh.pa.us>
To: exclusion@gmail.com
Cc: pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Subject: Re: BUG #17872: Dropping an attribute of a composite type breaks indexes over the type silently
Date: Mon, 27 Mar 2023 12:46:45 -0400
Message-ID: <3182314.1679935605@sss.pgh.pa.us> (raw)
In-Reply-To: <3169550.1679928579@sss.pgh.pa.us>
References: <17872-d0fbb799dc3fd85d@postgresql.org>
<3169550.1679928579@sss.pgh.pa.us>
I wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
>> The following script:
>> CREATE TYPE ctype AS (i int, j int);
>> CREATE TABLE ctbl(a int, cf ctype);
>> CREATE UNIQUE INDEX ctbl_idx ON ctbl(cf);
>> INSERT INTO ctbl VALUES (1, '(1, 2)'::ctype), (2, '(1, 1)'::ctype);
>> ALTER TYPE ctype DROP ATTRIBUTE j;
>> Results in the UNIQUE constraint broken:
>> SELECT ctid, * FROM ctbl;
>> ctid | a | cf
>> -------+---+-----
>> (0,1) | 1 | (1)
>> (0,2) | 2 | (1)
> Meh. I'm happy to classify this as "so don't do that".
On the other hand, this seems considerably more troubling:
regression=# CREATE TYPE ctype AS (i int, j int);
CREATE TYPE
regression=# CREATE TABLE ctbl(a int, b int);
CREATE TABLE
regression=# CREATE UNIQUE INDEX ctbl_idx ON ctbl((row(a,b)::ctype));
CREATE INDEX
regression=# ALTER TYPE ctype ALTER ATTRIBUTE j type numeric;
ALTER TYPE
If we had any data in the index, it'd now be completely broken.
This should be forbidden, but find_composite_type_dependencies
has no idea whatever that indexes might contain expression
columns of the target datatype. It does find a pg_depend
entry showing the index as dependent on the composite type,
but it ignores it because objsubid = 0 --- and would ignore it
also because of the relkind.
I think what we should do, instead of just ignoring objsubid = 0,
is to look through the index's columns and see if any have
atttypid equal to the target type. If not, then the composite
type is used in an expression but not stored on disk, so it's
just as safe (or not) as a reference in a view.
regards, tom lane
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-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, exclusion@gmail.com, peter.eisentraut@enterprisedb.com
Subject: Re: BUG #17872: Dropping an attribute of a composite type breaks indexes over the type silently
In-Reply-To: <3182314.1679935605@sss.pgh.pa.us>
* 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