pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Nunya Business <nb3425586@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@postgresql.org
Cc: pgsql-bugs@postgresql.org
Subject: Re[2]: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
Date: Tue, 06 Dec 2022 18:19:36 +0000
Message-ID: <emd5c018bd-ee54-4ca6-b61d-741ad84aa451@81da44b0.com> (raw)
In-Reply-To: <3635234.1670340237@sss.pgh.pa.us>
References: <emad8fa821-2230-4b8b-893b-cb2b8b376e9b@81da44b0.com>
	<3635234.1670340237@sss.pgh.pa.us>

>"Nunya Business" <nb3425586@gmail.com> writes:
>>Within my schema there is a table that has a GENERATED ALWAYS column that calls a plpgsql function.  The called function has a "row type" variable declared that  references a view.  While the schema itself functions properly day to day, and pg_dumpall works as expected, the generated SQL fails to successfully execute.  The table in question is restored with no rows, and an error is generated during the COPY stating that the type does not exist.
>
>Hmm, do you have actually circular dependencies in that?  pg_dump has
>some heuristics for dealing with such cases, but maybe it needs more.
>Please create a self-contained example and submit it to pgsql-bugs.
>
>			regards, tom lane

Thanks Tom.  There are indeed circular references in the schema and the 
whole thing sort of doesn't pass the smell test, but this is my first 
look at it.  The generated column on the table calls a function which 
selects from a view that references the table.  The production schema 
where I ran into this is pretty large and complex, so the contrived 
example that follows may not be the minimum working example but it's 
pretty small and has the same behavior regarding the SQL generated by 
pg_dumpall.

It seems that the schema is probably invalid according to the GENERATED 
rules and that pg_dumpall is operating as intended, but somehow the 
check in the ALTER TABLE isn't deep enough to prevent the issue, but 
maybe I'm mistaken.  Once this is created, if you insert a few rows and 
execute pg_dumpall, the resulting SQL cannot be loaded and will fail 
during the COPY, complaining that the view referenced by the function 
doesn't exist.

Here is the schema.  CCing pgsql-bugs as requested.

--------------------------CUT
CREATE TABLE tblA (
   id serial unique not null,
   dt timestamp with time zone not null default now(),
   data text
);

CREATE OR REPLACE VIEW viewA as (
   SELECT sum(id) FROM tblA
);

CREATE OR REPLACE FUNCTION fnA(askid INTEGER) RETURNS integer
   LANGUAGE 'plpgsql' IMMUTABLE
   AS $$

   declare
     varA viewA;
     ret integer;

   begin
     SELECT viewA.*
     INTO varA
     FROM viewA;

     ret = varA.sum;

     return ret;
   end;
$$;

ALTER TABLE tblA ADD COLUMN idsum INTEGER GENERATED ALWAYS AS (fnA(id)) 
STORED;








view thread (2+ messages)  latest in thread

Message-ID: <emd5c018bd-ee54-4ca6-b61d-741ad84aa451@81da44b0.com>
Permalink:  ../emd5c018bd-ee54-4ca6-b61d-741ad84aa451@81da44b0.com/
Also on:    postgresql.org/message-id/emd5c018bd-ee54-4ca6-b61d-741ad84aa451@81da44b0.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: nb3425586@gmail.com, tgl@sss.pgh.pa.us, pgsql-general@postgresql.org
  Subject: Re: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
  In-Reply-To: <emd5c018bd-ee54-4ca6-b61d-741ad84aa451@81da44b0.com>
  References: <emad8fa821-2230-4b8b-893b-cb2b8b376e9b@81da44b0.com> <3635234.1670340237@sss.pgh.pa.us> <emd5c018bd-ee54-4ca6-b61d-741ad84aa451@81da44b0.com>

* 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