MIME-Version: 1.0
References: 
 <CAHnOmadVj0y982EqT9sEvszjcKikysWtSrX88OvknP0W0poDkQ@mail.gmail.com>
In-Reply-To: 
 <CAHnOmadVj0y982EqT9sEvszjcKikysWtSrX88OvknP0W0poDkQ@mail.gmail.com>
From: Keith <keith@keithf4.com>
Date: Fri, 6 Jun 2025 10:18:40 -0400
Message-ID: 
 <CAHw75vsXZcvFpYhoVy=p-yhhf2u2q7yN+GP6j0guifz7sCbKZw@mail.gmail.com>
Subject: Re: order of pg_dump command "create sequence"
To: =?UTF-8?B?0JDQvdGC0L7QvSDQk9C70YPRiNCw0LrQvtCy?= <a.glushakov86@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000004f7aa40636e7eab4"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHw75vsXZcvFpYhoVy%3Dp-yhhf2u2q7yN%2BGP6j0guifz7sCbKZw%40mail.gmail.com>
Precedence: bulk

--0000000000004f7aa40636e7eab4
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Jun 6, 2025 at 5:16=E2=80=AFAM =D0=90=D0=BD=D1=82=D0=BE=D0=BD =D0=
=93=D0=BB=D1=83=D1=88=D0=B0=D0=BA=D0=BE=D0=B2 <a.glushakov86@gmail.com>
wrote:

> 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 Postgre=
s
> does not prohibit it) in which restoring from a dump becomes impossible d=
ue
> 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 t=
he
> 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=
.
>

If you need a sequence to be associated with a table, make it an IDENTITY
column

https://www.postgresql.org/docs/17/ddl-identity-columns.html

Or make sure that the sequence is owned by the table column that it is
associated with. See the OWNED BY clause

https://www.postgresql.org/docs/17/sql-createsequence.html

The IDENTITY method is far preferred, though, since it allows use of the
GENERATED features and makes all the permissions of the table automatically
associated with the sequence as well. Otherwise you have to manage them
independently.

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Jun 6, =
2025 at 5:16=E2=80=AFAM =D0=90=D0=BD=D1=82=D0=BE=D0=BD =D0=93=D0=BB=D1=83=
=D1=88=D0=B0=D0=BA=D0=BE=D0=B2 &lt;<a href=3D"mailto:a.glushakov86@gmail.co=
m">a.glushakov86@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex"><div dir=3D"ltr">Hi, I found a peculiarity of pg_=
dump&#39;s work with sequences when they are not explicitly linked to a tab=
le.<br>I encountered a situation (clearly abnormal use of sequences, but Po=
stgres does not prohibit it) in which restoring from a dump becomes impossi=
ble due to the violation of the order of commands.<br><br>Example:<br><br><=
div><span style=3D"font-family:monospace">/* create simple sequence */</spa=
n></div><div><span style=3D"font-family:monospace">CREATE SEQUENCE public.m=
y_seq;</span></div><div><span style=3D"font-family:monospace"></span></div>=
<div><span style=3D"font-family:monospace"><br></span></div><div><span styl=
e=3D"font-family:monospace">/* create a function that will move the sequenc=
e */</span></div><div><span style=3D"font-family:monospace"></span></div><s=
pan style=3D"font-family:monospace">CREATE FUNCTION public.gen_id() RETURNS=
 character varying<br>LANGUAGE sql IMMUTABLE AS<br>$$<br>SELECT &#39;PREFIX=
_&#39;||nextval(&#39;public.my_seq&#39;::regclass)::VARCHAR;<br>$$;<br><br>=
/* table, the column of which will call the function */<br>CREATE TABLE pub=
lic.exp_table (id character varying(13) GENERATED ALWAYS AS (public.gen_id(=
)) STORED NOT NULL);<br><br>If you make a pg_dump of the created , the sequ=
ence in dump of actions will be as follows:<br><br>1) Creating a function &=
quot;CREATE FUNCTION public.gen_id()&quot;<br>2) Creating a table &quot;CRE=
ATE TABLE public.exp_table&quot;<br>3) Creating a sequence &quot;CREATE SEQ=
UENCE public.my_seq&quot;<br><br>And here the problems begin.<br>If we try =
to restore the table structure from the dump, we get the expected error<br>=
&quot;ERROR: relation &quot;public.my_seq&quot; does not exist<br>LINE 2: S=
ELECT &#39;PREFIX_&#39;||nextval(&#39;public.my_seq&#39;::regclass)::VARCHA=
R;<br>^<br>QUERY:<br>SELECT &#39;PREFIX_&#39;||nextval(&#39;public.my_seq&#=
39;::regclass)::VARCHAR;<br></span><div><span style=3D"font-family:monospac=
e">CONTEXT: SQL function &quot;gen_id&quot; during startup&quot;</span></di=
v><div><span style=3D"font-family:monospace"><br></span></div><br>It turns =
out that Postgres does not know that the sequence is associated with the ta=
ble and places the code for creating it after the code for creating the tab=
le.<br>A workaround for this particular case is to change the name of the s=
equence so that it appears higher in the dump (according to alphabetical or=
der, for example rename it to &quot;a_my_seq&quot;) and then pg_dump will p=
lace 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 ab=
out, I can&#39;t say, but such problems can cause, for example, an error du=
ring an upgrade or logical replication, when you need to dump and restore t=
he data schema.</div></blockquote><div><br></div><div>If you need a sequenc=
e to be associated with a table, make it an IDENTITY column</div><div><br><=
/div><div><a href=3D"https://www.postgresql.org/docs/17/ddl-identity-column=
s.html">https://www.postgresql.org/docs/17/ddl-identity-columns.html</a></d=
iv><div><br></div><div>Or make sure that the sequence is owned by the table=
 column that it is associated with. See the OWNED BY clause</div><div><br><=
/div><div><a href=3D"https://www.postgresql.org/docs/17/sql-createsequence.=
html">https://www.postgresql.org/docs/17/sql-createsequence.html</a></div><=
div><br></div><div>The IDENTITY method is far preferred, though, since it a=
llows use of the GENERATED features and makes all the permissions of the ta=
ble automatically associated with the sequence as well. Otherwise you have =
to manage them independently.</div></div></div>

--0000000000004f7aa40636e7eab4--