Hi Tom,
27.03.2023 19:46, Tom Lane wrote:
> 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:
>>>
>> 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.
Yes, ALTERing the attribute/column leads to much worse things. In particular,
I've seen a server crash and the "compressed pglz data is corrupt" error.
(I can present the concrete queries if they can be of interest to you
(maybe for including in regression tests).)
As to the race condition possibility, I'm not sure that this consideration
should be applicable only to this kind of DDL. (I've got a collection of
crashes caused by race conditions in the existing code (#17182 and alike).
I had plans to discuss this issue separately.)
> 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.
Yes, I think something like that can resolve the issue.
But I would also note that the problem is not with indexes only, but also
with "... partition by list(comp_type_value)", for example.
Best regards,
Alexander