From: Tom Lane <tgl@sss.pgh.pa.us>
To: Дмитрий Иванов <firstdismay@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: pg_restore depending on user functions
Date: Mon, 15 Nov 2021 17:07:45 -0500
Message-ID: <2157331.1637014065@sss.pgh.pa.us> (raw)
In-Reply-To: <CAPL5KHo46S6kRRiaTfNdm63eR3u72gcnS9zMa2DYOjqZyPrFsw@mail.gmail.com>
References: <CAPL5KHoGa3uvyKp6z6m48LwCnTsK+LRQ_mcA4uKGfqAVSEjV_A@mail.gmail.com>
<2011525.1636911069@sss.pgh.pa.us>
<CAPL5KHrkVKVp22XE4S0ByA94-3OvH+9HC19WFFMePK2_c1VF0g@mail.gmail.com>
<2016240.1636915579@sss.pgh.pa.us>
<CAPL5KHoiNP8Z4Jq=EP0WBKr+07bfwk4sdH9k-+RQSJvYFVsC1A@mail.gmail.com>
<2048100.1636934771@sss.pgh.pa.us>
<CAPL5KHpN=pz97X+7vThsSn1V0ag8dSBrhMxjpWr1Z=wwU2BPow@mail.gmail.com>
<2124696.1636991365@sss.pgh.pa.us>
<CAPL5KHo46S6kRRiaTfNdm63eR3u72gcnS9zMa2DYOjqZyPrFsw@mail.gmail.com>
[ redirecting to -bugs ]
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.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
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, firstdismay@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: pg_restore depending on user functions
In-Reply-To: <2157331.1637014065@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