Re: order of pg_dump command "create sequence"

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 <keith(at)keithf4(dot)com>:

>
>
> On Fri, Jun 6, 2025 at 5:16 AM Антон Глушаков <a(dot)glushakov86(at)gmail(dot)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
>> 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.
>