PostgreSQL ddl extenstion does not change sequence type

[PSkarzynski-SkyCash]

Description: We have encountered an issue where newly created sequences in PostgreSQL database are defaulting to the integer type, which could lead to potential overflow issues with large datasets. This behavior requires manual intervention to alter the sequence type to bigint after creation.

Proposed Solution: To automate this process and ensure that all newly created sequences are of type bigint, we propose adding a following event trigger.

CREATE OR REPLACE FUNCTION update_sequence_to_bigint_oncreate()
    RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
    r record;
    matches text[];
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        SELECT regexp_matches(current_query(), E'CREATE\s+SEQUENCE\s+(\w+)\s+.*AS\s+integer') INTO matches;
        IF r.object_type = 'sequence' AND array_length(matches, 1) > 0 THEN
            RAISE NOTICE 'Altering sequence % to bigint', matches[1];
            EXECUTE 'ALTER SEQUENCE ' || matches[1] || ' AS bigint';
        END IF;
    END LOOP;
END
$$;

CREATE EVENT TRIGGER update_sequence_on_create_trigger
    ON ddl_command_end
    WHEN TAG IN ('CREATE SEQUENCE')
    EXECUTE FUNCTION update_sequence_to_bigint_oncreate();

[pierre]

Which sequences would this match in your environment?

FWIW serial is already handled via https://github.com/killbill/killbill/blob/master/util/src/main/resources/org/killbill/billing/util/ddl-postgresql.sql#L55