View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000339My infrastructureGeneralpublic2025-11-09 12:162025-11-09 12:59
Reporterdvl Assigned Todvl  
PrioritynormalSeverityminorReproducibilityhave not tried
Status assignedResolutionopen 
Summary0000339: drop PostgreSQL sequences which are no longer used
Descriptione.g.

freshports.dvl=# \d page_load_detail
                                     Table "public.page_load_detail"
     Column     |          Type          | Collation | Nullable |                Default                 
----------------+------------------------+-----------+----------+----------------------------------------
 id             | bigint                 |           | not null | generated always as identity
 date           | date                   |           | not null | 'now'::text::date
 time           | time without time zone |           | not null | 'now'::text::time(6) without time zone
 page_name      | text                   |           | not null | 
 user_id        | integer                |           |          | 
 ip_address     | inet                   |           | not null | 
 full_url       | text                   |           | not null | 
 rendering_time | interval               |           | not null | 
Indexes:
    "page_load_detail_test_pkey" PRIMARY KEY, btree (id)
    "page_load_detail_date" btree (date)
    "page_load_ip_address" btree (ip_address)

freshports.dvl=#


id is now "generated always as identity"

Which means this sequence can go:

freshports.dvl=# \dS
...
 public     | page_load_detail                        | table    | postgres
 public     | page_load_detail_id_seq                 | sequence | postgres
...


And we have:

freshports.dvl=# select last_value from page_load_detail_id_seq;
 last_value 
------------
  483014319
(1 row)
TagsNo tags attached.

Activities

dvl

2025-11-09 12:19

manager   ~0000481

The initial premise is proven wrong:

freshports.dvl=# begin;
BEGIN
freshports.dvl=*# drop sequence page_load_detail_id_seq;
ERROR:  cannot drop sequence page_load_detail_id_seq because column id of table page_load_detail requires it
HINT:  You can drop column id of table page_load_detail instead.
freshports.dvl=!#

dvl

2025-11-09 12:26

manager   ~0000482

As found in the [plain text] dump of that database:

[12:25 dbclone dvl ~/tmp] % grep page_load_detail_id_seq ~/tmp/freshports.dvl.sql       
-- Name: page_load_detail_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
    SEQUENCE NAME public.page_load_detail_id_seq
-- Name: page_load_detail_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
SELECT pg_catalog.setval('public.page_load_detail_id_seq', 0, false);
[12:25 dbclone dvl ~/tmp] %

dvl

2025-11-09 12:34

manager   ~0000483

In the dump, it is the only sequence with a SELECT pg_catalog.setval() assigning zero.

It is not the only IDENTITY column:

[12:33 dbclone dvl ~/tmp] % grep 'ADD GENERATED ALWAYS AS IDENTITY' ~/tmp/freshports.dvl.sql
            'ALTER TABLE %I ALTER COLUMN %I ADD GENERATED ALWAYS AS IDENTITY (START WITH %s)',
ALTER TABLE public.abi ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
ALTER TABLE public.package_imports ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
ALTER TABLE public.packages ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
ALTER TABLE public.packages_raw ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
ALTER TABLE public.page_load_detail ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
ALTER TABLE public.report_log_package_notifications ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
ALTER TABLE public.supported_releases ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
[12:34 dbclone dvl ~/tmp] %

dvl

2025-11-09 12:36

manager   ~0000484

I have discovered a forgotten function:

--
-- Name: upgrade_serial_to_identity_bigint(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.upgrade_serial_to_identity_bigint(table_name text, column_name text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
    -- will hold the max value found in the sequence
    max_id integer;
     
    -- sequence name
    seqence_name text := table_name || '_' || column_name || '_seq';
BEGIN
   
    EXECUTE format('LOCK %I;', table_name);
     
    -- get current maximum id
    EXECUTE format (
            'SELECT COALESCE(MAX(%I), 0) FROM %I;',
             column_name,
             table_name
    ) INTO max_id;
     
    -- drop the sequence
    EXECUTE format('DROP SEQUENCE IF EXISTS %I CASCADE;', seqence_name);
     
    -- alter table so it now uses an identity, instead of the previously seqence
    EXECUTE format (
            'ALTER TABLE %I ALTER COLUMN %I ADD GENERATED ALWAYS AS IDENTITY (START WITH %s)',
            table_name,
            column_name,
            max_id + 1
    );
 
    EXECUTE format (
            'ALTER TABLE %I ALTER COLUMN %I type bigint',
            table_name,
            column_name
    );
     
END;
$$;


ALTER FUNCTION public.upgrade_serial_to_identity_bigint(table_name text, column_name text) OWNER TO postgres;

dvl

2025-11-09 12:46

manager   ~0000485

Looking closer at a grep from above, we find:

...
-- Name: page_load_detail_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

ALTER TABLE public.page_load_detail ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.page_load_detail_id_seq
    START WITH 482967636
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);
...
--
-- Name: page_load_detail_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.page_load_detail_id_seq', 0, false);


The question now is: why?

dvl

2025-11-09 12:55

manager   ~0000486

NOTE: this ticket arose from this output from pg_restore:

pg_restore: error: could not execute query: ERROR:  setval: value 0 is out of bounds for sequence "page_load_detail_id_seq" (1..9223372036854775807)
Command was: SELECT pg_catalog.setval('public.page_load_detail_id_seq', 0, false);


pg_restore: warning: errors ignored on restore: 2

dvl

2025-11-09 12:59

manager   ~0000487

Could this be because the source database is:

[12:59 pg01 dvl ~/src/freshports-database-schema] % psql -U postgres freshports.dvl
psql (18beta1)
Type "help" for help.

freshports.dvl=# SELECT version();
                                          version                                          
-------------------------------------------------------------------------------------------
 PostgreSQL 18beta1 on amd64-portbld-freebsd14.2, compiled by clang version 19.1.7, 64-bit
(1 row)

freshports.dvl=#


and the destination database is:
[12:58 dbclone dvl ~/tmp] % psql -U postgres template1                  
psql (18.0)
Type "help" for help.

template1=# select version();
                                        version                                         
----------------------------------------------------------------------------------------
 PostgreSQL 18.0 on amd64-portbld-freebsd14.3, compiled by clang version 19.1.7, 64-bit
(1 row)

template1=#

Issue History

Date Modified Username Field Change
2025-11-09 12:16 dvl New Issue
2025-11-09 12:16 dvl Status new => assigned
2025-11-09 12:16 dvl Assigned To => dvl
2025-11-09 12:19 dvl Note Added: 0000481
2025-11-09 12:26 dvl Note Added: 0000482
2025-11-09 12:34 dvl Note Added: 0000483
2025-11-09 12:36 dvl Note Added: 0000484
2025-11-09 12:46 dvl Note Added: 0000485
2025-11-09 12:53 dvl Description Updated
2025-11-09 12:55 dvl Note Added: 0000486
2025-11-09 12:59 dvl Note Added: 0000487