Обсуждение: [BUGS] BUG #14867: Cascade drop type error

The following bug has been logged on the website:

Bug reference:      14867
Logged by:          Mansur Galiev
Email address:      gomer94@yandex.ru
PostgreSQL version: 10.0
Operating system:   Ubuntu 16.04.3 LTS x64
Description:

CREATE TYPE my_type AS (f1 integer);
CREATE TYPE my_type_2 AS (f2 my_type);
CREATE TABLE my_table (c1 my_type_2);
CREATE VIEW my_view AS SELECT ((C1).f2).f1 FROM my_table;
DROP TYPE my_type CASCADE;

after then my_view still has a column and a type queries of this:

SELECT * FROM pg_views;

shows:

ERROR:  could not open relation with OID 33070


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

gomer94@yandex.ru writes:
> CREATE TYPE my_type AS (f1 integer);
> CREATE TYPE my_type_2 AS (f2 my_type);
> CREATE TABLE my_table (c1 my_type_2);
> CREATE VIEW my_view AS SELECT ((C1).f2).f1 FROM my_table;
> DROP TYPE my_type CASCADE;

Cute.  Type my_type isn't exposed as a dependency of the view,
because it's only referenced internally in the expression tree
not as a result column type.  We can fix that easily enough by
teaching dependency.c to log the result type of a FieldSelect
as a dependency.  That results in dropping the whole view, not
just one column:

regression=# DROP TYPE my_type CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to composite type my_type_2 column f2
drop cascades to view my_view
DROP TYPE

which is a bit annoying but I think there's no help for it.
We don't have logic that could rip apart the view query and
reconstruct it without the expression for that one column.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 2017-10-23 12:09:24 -0400, Tom Lane wrote:
> gomer94@yandex.ru writes:
> > CREATE TYPE my_type AS (f1 integer);
> > CREATE TYPE my_type_2 AS (f2 my_type);
> > CREATE TABLE my_table (c1 my_type_2);
> > CREATE VIEW my_view AS SELECT ((C1).f2).f1 FROM my_table;
> > DROP TYPE my_type CASCADE;
> 
> Cute.  Type my_type isn't exposed as a dependency of the view,
> because it's only referenced internally in the expression tree
> not as a result column type.  We can fix that easily enough by
> teaching dependency.c to log the result type of a FieldSelect
> as a dependency.

That does remind me of patch 0001 in
http://archives.postgresql.org/message-id/20170314224706.nxvtapenky6eom3z%40alap3.anarazel.de

- Andres


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Andres Freund <andres@anarazel.de> writes:
> On 2017-10-23 12:09:24 -0400, Tom Lane wrote:
>> Cute.  Type my_type isn't exposed as a dependency of the view,
>> because it's only referenced internally in the expression tree
>> not as a result column type.  We can fix that easily enough by
>> teaching dependency.c to log the result type of a FieldSelect
>> as a dependency.

> That does remind me of patch 0001 in
> http://archives.postgresql.org/message-id/20170314224706.nxvtapenky6eom3z%40alap3.anarazel.de

It is in the same part of the code, but seems directed at a different
goal (which I disapproved of, IIRC).  But we definitely need the
principle that any type OID appearing in an expression tree needs to
be logged, except in cases where there's necessarily an indirect
dependency (eg, a function or operator's result type doesn't need to
be logged separately).
        regards, tom lane


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs