public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: Антон Глушаков <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: order of pg_dump command "create sequence"
Date: Fri, 06 Jun 2025 14:48:45 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHnOmafoGRUPrCpK1twGQYEE10VjJGW80zt9nvd1gbbT8Dk_Aw@mail.gmail.com>
References: <CAHnOmadVj0y982EqT9sEvszjcKikysWtSrX88OvknP0W0poDkQ@mail.gmail.com>
<CAKFQuwYXJ11+wMu-qrLi9yQwZYjDsgJH=h6aStyxcHti=gYC+g@mail.gmail.com>
<CAHnOmafoGRUPrCpK1twGQYEE10VjJGW80zt9nvd1gbbT8Dk_Aw@mail.gmail.com>
Some of your problem here comes from using an old-style SQL function.
The string body of such a function is opaque to pg_dump, so it has
no way to know about the dependency on my_seq. You could make it
new-style (SQL spec compliant) instead:
regression=# create sequence my_seq;
CREATE SEQUENCE
regression=# create function gen_id() returns character varying
regression-# begin atomic select 'PREFIX_'||nextval('public.my_seq'::regclass)::VARCHAR;
regression-# end;
CREATE FUNCTION
Now the dependency is known:
regression=# select pg_describe_object(classid,objid,objsubid) as obj, pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype from pg_depend where objid >= 'my_seq'::regclass;
obj | ref | deptype
-------------------+-----------------+---------
function gen_id() | schema public | n
function gen_id() | sequence my_seq | n
sequence my_seq | schema public | n
(3 rows)
and pg_dump will honor it.
But as David said, using a volatile function in a GENERATED
expression is unsupported and is not going to work well.
You would probably be better off filling the column in a
BEFORE INSERT trigger.
regards, tom lane
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 using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: order of pg_dump command "create sequence"
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox