public inbox for [email protected]
help / color / mirror / Atom feedorder of pg_dump command "create sequence"
7+ messages / 5 participants
[nested] [flat]
* order of pg_dump command "create sequence"
@ 2025-06-06 09:15 Антон Глушаков <[email protected]>
2025-06-06 13:54 ` Re: order of pg_dump command "create sequence" David G. Johnston <[email protected]>
2025-06-06 14:18 ` Re: order of pg_dump command "create sequence" Keith <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Антон Глушаков @ 2025-06-06 09:15 UTC (permalink / raw)
To: [email protected]
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: order of pg_dump command "create sequence"
2025-06-06 09:15 order of pg_dump command "create sequence" Антон Глушаков <[email protected]>
@ 2025-06-06 13:54 ` David G. Johnston <[email protected]>
2025-06-06 14:07 ` Re: order of pg_dump command "create sequence" Ron Johnson <[email protected]>
2025-06-06 15:09 ` Re: order of pg_dump command "create sequence" Антон Глушаков <[email protected]>
1 sibling, 2 replies; 7+ messages in thread
From: David G. Johnston @ 2025-06-06 13:54 UTC (permalink / raw)
To: Антон Глушаков <[email protected]>; +Cc: [email protected] <[email protected]>
On Friday, June 6, 2025, Антон Глушаков <[email protected]> wrote:
>
> /* 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;
> $$;
>
>
>
Stating immutable is a lie and the breakage is on your head for violating
using only immutable functions in generated expressions.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: order of pg_dump command "create sequence"
2025-06-06 09:15 order of pg_dump command "create sequence" Антон Глушаков <[email protected]>
2025-06-06 13:54 ` Re: order of pg_dump command "create sequence" David G. Johnston <[email protected]>
@ 2025-06-06 14:07 ` Ron Johnson <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Ron Johnson @ 2025-06-06 14:07 UTC (permalink / raw)
To: [email protected] <[email protected]>
On Fri, Jun 6, 2025 at 9:54 AM David G. Johnston <[email protected]>
wrote:
> On Friday, June 6, 2025, Антон Глушаков <[email protected]> wrote:
>
>>
>> /* 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;
>> $$;
>>
>>
>>
> Stating immutable is a lie and the breakage is on your head for violating
> using only immutable functions in generated expressions.
>
It's too bad that the server, when it sees the CREATE FUNCTION DDL, can't
notice the obvious (to humans) not-immutable nature of the function and
throw a warning. It would certainly be hideously complex code, though, and
miss lots of cases while throwing some false positive messages.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: order of pg_dump command "create sequence"
2025-06-06 09:15 order of pg_dump command "create sequence" Антон Глушаков <[email protected]>
2025-06-06 13:54 ` Re: order of pg_dump command "create sequence" David G. Johnston <[email protected]>
@ 2025-06-06 15:09 ` Антон Глушаков <[email protected]>
2025-06-06 18:48 ` Re: order of pg_dump command "create sequence" Tom Lane <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Антон Глушаков @ 2025-06-06 15:09 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: [email protected] <[email protected]>
I absolutely agree.
Unfortunately, Postgres does not check the function code to see if it is
really immutable
пт, 6 июн. 2025 г. в 16:54, David G. Johnston <[email protected]>:
> On Friday, June 6, 2025, Антон Глушаков <[email protected]> wrote:
>
>>
>> /* 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;
>> $$;
>>
>>
>>
> Stating immutable is a lie and the breakage is on your head for violating
> using only immutable functions in generated expressions.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: order of pg_dump command "create sequence"
2025-06-06 09:15 order of pg_dump command "create sequence" Антон Глушаков <[email protected]>
2025-06-06 13:54 ` Re: order of pg_dump command "create sequence" David G. Johnston <[email protected]>
2025-06-06 15:09 ` Re: order of pg_dump command "create sequence" Антон Глушаков <[email protected]>
@ 2025-06-06 18:48 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2025-06-06 18:48 UTC (permalink / raw)
To: Антон Глушаков <[email protected]>; +Cc: David G. Johnston <[email protected]>; [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: order of pg_dump command "create sequence"
2025-06-06 09:15 order of pg_dump command "create sequence" Антон Глушаков <[email protected]>
@ 2025-06-06 14:18 ` Keith <[email protected]>
2025-06-06 15:07 ` Re: order of pg_dump command "create sequence" Антон Глушаков <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Keith @ 2025-06-06 14:18 UTC (permalink / raw)
To: Антон Глушаков <[email protected]>; +Cc: [email protected]
On Fri, Jun 6, 2025 at 5:16 AM Антон Глушаков <[email protected]>
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 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.
>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: order of pg_dump command "create sequence"
2025-06-06 09:15 order of pg_dump command "create sequence" Антон Глушаков <[email protected]>
2025-06-06 14:18 ` Re: order of pg_dump command "create sequence" Keith <[email protected]>
@ 2025-06-06 15:07 ` Антон Глушаков <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Антон Глушаков @ 2025-06-06 15:07 UTC (permalink / raw)
To: Keith <[email protected]>; +Cc: [email protected]
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 restore
пт, 6 июн. 2025 г. в 17:19, Keith <[email protected]>:
>
>
> On Fri, Jun 6, 2025 at 5:16 AM Антон Глушаков <[email protected]>
> 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 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.
>>
>
> 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.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-06-06 18:48 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-06 09:15 order of pg_dump command "create sequence" Антон Глушаков <[email protected]>
2025-06-06 13:54 ` David G. Johnston <[email protected]>
2025-06-06 14:07 ` Ron Johnson <[email protected]>
2025-06-06 15:09 ` Антон Глушаков <[email protected]>
2025-06-06 18:48 ` Tom Lane <[email protected]>
2025-06-06 14:18 ` Keith <[email protected]>
2025-06-06 15:07 ` Антон Глушаков <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox