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
In-reply-to: <3169550.1679928579@sss.pgh.pa.us>
References: <17872-d0fbb799dc3fd85d@postgresql.org>
 <3169550.1679928579@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Mon, 27 Mar 2023 10:49:39 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3182313.1679935605.1@sss.pgh.pa.us>
Date: Mon, 27 Mar 2023 12:46:45 -0400
Message-ID: <3182314.1679935605@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3182314.1679935605%40sss.pgh.pa.us>
Precedence: bulk

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