MIME-Version: 1.0
From: =?UTF-8?B?0JDQvdGC0L7QvSDQk9C70YPRiNCw0LrQvtCy?=
 <a.glushakov86@gmail.com>
Date: Fri, 6 Jun 2025 12:15:45 +0300
Message-ID: 
 <CAHnOmadVj0y982EqT9sEvszjcKikysWtSrX88OvknP0W0poDkQ@mail.gmail.com>
Subject: order of pg_dump command "create sequence"
To: pgsql-admin@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000006cf6f0636e3ae0c"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHnOmadVj0y982EqT9sEvszjcKikysWtSrX88OvknP0W0poDkQ%40mail.gmail.com>
Precedence: bulk

--00000000000006cf6f0636e3ae0c
Content-Type: text/plain; charset="UTF-8"

Hi, I found a peculiarity of pg_dump's work with sequences when they are
not explicitly linked to a table.
I encountered a situation (clearly abnormal use of sequences, but Postgres
does not prohibit it) in which restoring from a dump becomes impossible due
to the violation of the order of commands.

Example:

/* create simple sequence */
CREATE SEQUENCE public.my_seq;

/* create a function that will move the sequence */
CREATE FUNCTION public.gen_id() RETURNS character varying
LANGUAGE sql IMMUTABLE AS
$$
SELECT 'PREFIX_'||nextval('public.my_seq'::regclass)::VARCHAR;
$$;

/* table, the column of which will call the function */
CREATE TABLE public.exp_table (id character varying(13) GENERATED ALWAYS AS
(public.gen_id()) STORED NOT NULL);

If you make a pg_dump of the created , the sequence in dump of actions will
be as follows:

1) Creating a function "CREATE FUNCTION public.gen_id()"
2) Creating a table "CREATE TABLE public.exp_table"
3) Creating a sequence "CREATE SEQUENCE public.my_seq"

And here the problems begin.
If we try to restore the table structure from the dump, we get the expected
error
"ERROR: relation "public.my_seq" does not exist
LINE 2: SELECT 'PREFIX_'||nextval('public.my_seq'::regclass)::VARCHAR;
^
QUERY:
SELECT 'PREFIX_'||nextval('public.my_seq'::regclass)::VARCHAR;
CONTEXT: SQL function "gen_id" during startup"


It turns out that Postgres does not know that the sequence is associated
with the table and places the code for creating it after the code for
creating the table.
A workaround for this particular case is to change the name of the sequence
so that it appears higher in the dump (according to alphabetical order, for
example rename it to "a_my_seq") and then pg_dump will place the creation
of the sequence before the table, and the restore will be successful.
Whether this is a bug or a feature that you need to know about, I can't
say, but such problems can cause, for example, an error during an upgrade
or logical replication, when you need to dump and restore the data schema.

--00000000000006cf6f0636e3ae0c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi, I found a peculiarity of pg_dump&#39;s work with seque=
nces when they are not explicitly linked to a table.<br>I encountered a sit=
uation (clearly abnormal use of sequences, but Postgres does not prohibit i=
t) in which restoring from a dump becomes impossible due to the violation o=
f the order of commands.<br><br>Example:<br><br><div><span style=3D"font-fa=
mily:monospace">/* create simple sequence */</span></div><div><span style=
=3D"font-family:monospace">CREATE SEQUENCE public.my_seq;</span></div><div>=
<span style=3D"font-family:monospace"></span></div><div><span style=3D"font=
-family:monospace"><br></span></div><div><span style=3D"font-family:monospa=
ce">/* create a function that will move the sequence */</span></div><div><s=
pan style=3D"font-family:monospace"></span></div><span style=3D"font-family=
:monospace">CREATE FUNCTION public.gen_id() RETURNS character varying<br>LA=
NGUAGE sql IMMUTABLE AS<br>$$<br>SELECT &#39;PREFIX_&#39;||nextval(&#39;pub=
lic.my_seq&#39;::regclass)::VARCHAR;<br>$$;<br><br>/* table, the column of =
which will call the function */<br>CREATE TABLE public.exp_table (id charac=
ter varying(13) GENERATED ALWAYS AS (public.gen_id()) STORED NOT NULL);<br>=
<br>If you make a pg_dump of the created , the sequence in dump of actions =
will be as follows:<br><br>1) Creating a function &quot;CREATE FUNCTION pub=
lic.gen_id()&quot;<br>2) Creating a table &quot;CREATE TABLE public.exp_tab=
le&quot;<br>3) Creating a sequence &quot;CREATE SEQUENCE public.my_seq&quot=
;<br><br>And here the problems begin.<br>If we try to restore the table str=
ucture from the dump, we get the expected error<br>&quot;ERROR: relation &q=
uot;public.my_seq&quot; does not exist<br>LINE 2: SELECT &#39;PREFIX_&#39;|=
|nextval(&#39;public.my_seq&#39;::regclass)::VARCHAR;<br>^<br>QUERY:<br>SEL=
ECT &#39;PREFIX_&#39;||nextval(&#39;public.my_seq&#39;::regclass)::VARCHAR;=
<br></span><div><span style=3D"font-family:monospace">CONTEXT: SQL function=
 &quot;gen_id&quot; during startup&quot;</span></div><div><span style=3D"fo=
nt-family:monospace"><br></span></div><br>It turns out that Postgres does n=
ot know that the sequence is associated with the table and places the code =
for creating it after the code for creating the table.<br>A workaround for =
this particular case is to change the name of the sequence so that it appea=
rs higher in the dump (according to alphabetical order, for example rename =
it to &quot;a_my_seq&quot;) and then pg_dump will place the creation of the=
 sequence before the table, and the restore will be successful.<br>Whether =
this is a bug or a feature that you need to know about, I can&#39;t say, bu=
t such problems can cause, for example, an error during an upgrade or logic=
al replication, when you need to dump and restore the data schema.</div>

--00000000000006cf6f0636e3ae0c--