From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?B?0JDQvdGC0L7QvSDQk9C70YPRiNCw0LrQvtCy?= <a.glushakov86@gmail.com>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: Re: order of pg_dump command "create sequence"
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>
Comments: In-reply-to =?UTF-8?B?0JDQvdGC0L7QvSDQk9C70YPRiNCw0LrQvtCy?=
 <a.glushakov86@gmail.com>
	message dated "Fri, 06 Jun 2025 18:09:04 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1525848.1749235725.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 06 Jun 2025 14:48:45 -0400
Message-ID: <1525849.1749235725@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1525849.1749235725%40sss.pgh.pa.us>
Precedence: bulk

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=3D# create sequence my_seq;
CREATE SEQUENCE
regression=3D# create function gen_id() returns character varying
regression-# begin atomic select 'PREFIX_'||nextval('public.my_seq'::regcl=
ass)::VARCHAR;
regression-# end;
CREATE FUNCTION

Now the dependency is known:

regression=3D# select pg_describe_object(classid,objid,objsubid) as obj, p=
g_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype from pg=
_depend where objid >=3D '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