Re: pg_restore depending on user functions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Дмитрий Иванов <firstdismay(at)gmail(dot)com>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: pg_restore depending on user functions
Date: 2021-11-15 22:07:45
Message-ID: 2157331.1637014065@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

[ redirecting to -bugs ]

=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay(at)gmail(dot)com> writes:
> I continue to extract data as promised, but I think I see some pattern.
> "chicken or egg"
> To work with the NPGSQL library, I created a cast. They are created after
> the views in which I use them.

Hmm. I do see a potential issue there, though it seems like it should
result in failing to create the views, not the functions. I experimented
with

create function topoint(float8) returns point
as 'select point($1,$1)' language sql;

create cast (float8 as point) with function topoint;

create view vv as select f1, f1::point from float8_tbl;

That results in these pg_depend entries:

regression=# select pg_describe_object(classid,objid,objsubid) as obj,
pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype from
pg_depend where ...

obj | ref | deptype
-------------------------------------+------------------------------------+---------
function topoint(double precision) | schema public | n
cast from double precision to point | function topoint(double precision) | n
type vv | view vv | i
type vv[] | type vv | i
view vv | schema public | n
rule _RETURN on view vv | view vv | i
rule _RETURN on view vv | view vv | n
rule _RETURN on view vv | function topoint(double precision) | n
rule _RETURN on view vv | column f1 of table float8_tbl | n
(9 rows)

That is, we made the view depend directly on the function, not on the
cast, which would license pg_dump to dump things in the order function,
view, cast --- which'd fail, since the view is going to be printed with
cast syntax.

So that seems bad, but just because pg_dump could theoretically do
that doesn't mean it will. The object type priority rules built into
pg_dump_sort should normally cause the dump order to be function, cast,
view. It's conceivable that some circular dependency exists in this DB
and pg_dump chooses to break the circularity in a way that causes the
view to be moved ahead of the cast. I'd like to see the details though.

Fixing this "properly" seems like it'd require recording the cast OID in
FuncExpr, RelabelType, and several other node types that can be generated
from cast syntax. Not only would that be invasive and non-back-patchable,
but it'd be really ugly semantically, since at least for optimization
purposes you'd want the cast field to be ignored when deciding if two
expressions are equal(). So I don't think I want to go there. I wonder
if we can fix this by twiddling pg_dump's circularity-breaking rules, or
by forcing it to emit casts immediately after their underlying functions.

Or maybe this has nothing to do with the actual problem. I still want
to see an example before embarking on fixing it.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andres Freund 2021-11-16 02:39:18 Re: BUG #17255: Server crashes in index_delete_sort_cmp() due to race condition with vacuum
Previous Message Дмитрий Иванов 2021-11-15 21:24:31 Re: pg_restore depending on user functions

Browse pgsql-general by date

  From Date Subject
Next Message Saul Perdomo 2021-11-16 01:48:14 Re: Managing major PostgreSQL upgrades
Previous Message Дмитрий Иванов 2021-11-15 21:24:31 Re: pg_restore depending on user functions