Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uNYff-004RdB-Qx for pgsql-admin@arkaria.postgresql.org; Fri, 06 Jun 2025 15:07:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uNYfc-00C1al-4n for pgsql-admin@arkaria.postgresql.org; Fri, 06 Jun 2025 15:07:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uNYfb-00C1ab-NC for pgsql-admin@lists.postgresql.org; Fri, 06 Jun 2025 15:07:48 +0000 Received: from mail-yb1-xb2e.google.com ([2607:f8b0:4864:20::b2e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uNYfZ-000Z2O-2f for pgsql-admin@lists.postgresql.org; Fri, 06 Jun 2025 15:07:47 +0000 Received: by mail-yb1-xb2e.google.com with SMTP id 3f1490d57ef6-e7311e66a8eso2102673276.2 for ; Fri, 06 Jun 2025 08:07:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1749222463; x=1749827263; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Z4CgYBUitzZtKXMCwAXn2xn6N64pb+M22YMKnYBXNmI=; b=cp9xzBzjLnVLGayg4yyzA0aEwz4xKM5dxmpfgOAl0cJqwBDyx3p06UvXlpDVKBD8ZE AlbexqPCKfdko3l9ECbJqccObSSgyH1BK+LoQg5ybK2478qFHELfZxzv+44dpdcECPDN wAw4EKD1ou/KifVdVwvO+/9VpiJCpiDSJ+yqUA+HcTW6Ekz0Zyn2B0EtIOhQCzuy2UK8 kpQq56YmXuSFvLRux2SYtyW3ujml8LPgrG4Al1MB2If57nqgd6mKA6eTNRlLSzsxjvmP nf3NaDGfb8dC01eetwsimRlS5jjtB1lFODgbTbMvxzJ51DtwkcsCKybFaNHxf/4fWYzU /d5g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1749222463; x=1749827263; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Z4CgYBUitzZtKXMCwAXn2xn6N64pb+M22YMKnYBXNmI=; b=jkiGs5Jf91F5FIeIBD6zJF/z7f49CPLL/TgmS+yNJvCuimg9Y61uCq+HnC1pMBw/pi ZsDTeNbZvCEuAk2SAPd+RrM8qqgR5fug4MQYmfQ6D2pF4u5iN0pd6Z3WOSlAyved9U3/ YdxVqOBohhjWZOiHFWWDbkw6QfyNy66kATd0ZCxZLoIvopSZxzudwwa/HW0WZPHNkk65 6dU3AMhZmuBF5t7MCcXGndKDyGwiAOqXg9om6H4lqX0kOe+mMWxj3RkvicqFUAsvvfM5 kMgcXC+PySKB//3IIIUzSUq3GrMQpcOf7oI5eMWjOhtE3lMvw4mJAyfU74bTfOu2Or3j EtCQ== X-Gm-Message-State: AOJu0YyQjT5Zh13iBD3ChxevZgBAMWEfozl3ZeXoOIE8XwSelkWLF89P bJFgoOd8Qza4OSjKsuS7R3yVGZho13k3s0iu/0+GciQt3017HLB/mdQQ/l9GH7EfiI3biPCNLTy 4nnffg92g1OOMCXce2LB5FhYXgI5azU4= X-Gm-Gg: ASbGncss8nWmgZZc5eaFWIjgbwxRWLIxzv5HAKmG5tuRr6ItUKA/9Qrq4Kd4d1sPKVV oORAh4u6UU0pq1kTokuqlMnAd4o73/QHyiLXZaIT3IMICvD4CjmZWUQRUUh2CspPtKpp2TtLVFt b4MKo088akqdY2GzkpIVUDnKnLGBQ5sKnw X-Google-Smtp-Source: AGHT+IGRVpHBDVmbBAWMv7hXB06EuRMb+52qVAXgxyY1iuG47zB22ZsVKp+3vj0x3oXfd96Xkb4HcazCGL+fOpJWiGg= X-Received: by 2002:a05:6902:991:b0:e81:78f7:5521 with SMTP id 3f1490d57ef6-e81a2098ecamr5370308276.6.1749222462578; Fri, 06 Jun 2025 08:07:42 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: =?UTF-8?B?0JDQvdGC0L7QvSDQk9C70YPRiNCw0LrQvtCy?= Date: Fri, 6 Jun 2025 18:07:22 +0300 X-Gm-Features: AX0GCFuJRe6Pf-O3vewDqbeNFkey2ya-1d-s9RZQSdCbsSxYQu8QtPLyq5kcFSs Message-ID: Subject: Re: order of pg_dump command "create sequence" To: Keith Cc: pgsql-admin@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000083e3090636e89774" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000083e3090636e89774 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable In this case, it wasn't just a sequence that was needed, but a text prefix and a sequence. owning a sequence to the column doesn't help in this case, the DDL of the table doesn't change from this, and an error still occurs during the restor= e =D0=BF=D1=82, 6 =D0=B8=D1=8E=D0=BD. 2025=E2=80=AF=D0=B3. =D0=B2 17:19, Keit= h : > > > 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 > 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 >> 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 alphabetica= l >> 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 upgrad= e >> or logical replication, when you need to dump and restore the data schem= a. >> > > 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 automatical= ly > associated with the sequence as well. Otherwise you have to manage them > independently. > --00000000000083e3090636e89774 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
In this case, it wasn't just a sequen= ce that was needed, but a text prefix and a sequence.
owning=C2=A0 a seq= uence to the column doesn't help in this case, the DDL of the table doe= sn't change from this, and an error still occurs during the restore
=D0=BF=D1=82, 6 =D0=B8=D1=8E=D0=BD. 2025=E2=80=AF=D0=B3. = =D0=B2 17:19, Keith <keith@keithf4.= com>:


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.g= lushakov86@gmail.com> wrote:
Hi, I found a peculiarity of pg_dump&#= 39;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 du= e to the violation of the order of commands.

Example:

/* create simple sequence */
CREATE SEQUENCE public.my_seq;=
<= span style=3D"font-family:monospace">
/* create a function that will move the sequence */
CREATE FUNCTION public.gen_id() RETURNS chara= cter varying
LANGUAGE sql IMMUTABLE AS
$$
SELECT 'PREFIX_'= ||nextval('public.my_seq'::regclass)::VARCHAR;
$$;

/* tab= le, the column of which will call the function */
CREATE TABLE public.ex= p_table (id character varying(13) GENERATED ALWAYS AS (public.gen_id()) STO= RED NOT NULL);

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

1) Creating a function "C= REATE FUNCTION public.gen_id()"
2) Creating a table "CREATE TA= BLE public.exp_table"
3) Creating a sequence "CREATE SEQUENCE = public.my_seq"

And here the problems begin.
If we try to res= tore 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'::r= egclass)::VARCHAR;
CON= TEXT: SQL function "gen_id" during startup"


It turns out th= at Postgres does not know that the sequence is associated with the table an= d 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 sequenc= e so that it appears higher in the dump (according to alphabetical order, f= or 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 succe= ssful.
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 a= n upgrade or logical replication, when you need to dump and restore the dat= a schema.

If you need a sequence to b= e associated with a table, make it an IDENTITY column

<= div>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


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 sequenc= e as well. Otherwise you have to manage them independently.
--00000000000083e3090636e89774--