PGSQL Phriday #004: Sequence Survey

by gorthx

Hello! This month’s PGSQL Phriday prompts included a question about scripts. I have a vault of SQL queries, if that counts as scripts…they are all queries I’ve worked up when I had to find a specific piece of information, and the examples on StackOverflow weren’t quite right. I love to share them, and see what other folks come up with – here’s one I’ve been using a lot over the past year.

For background, maybe you’ve seen this error message before:

gabrielle=# INSERT INTO my_stuff (stuff) values ('hello');
ERROR:  nextval: reached maximum value of sequence "my_stuff_id_seq" (2147483647)

Yikes! You need a bigint sequence, now.

There are ways to fix this when it happens; Robert Treat’s mildy-named “Advanced Int->Bigint Conversions” from the PgConfNYC 2021 (video here) has some options.

I personally don’t like that much excitement in my day job. I prefer to catch these situations long before they happen, so I can correct them at my leisure.

Here’s a quick and easy check for “integer sequence that is roughly halfway to overflow”, courtesy Xof:

SELECT * FROM pg_sequences WHERE data_type = 'integer'::regtype AND last_value > (1<<30);

Halfway is usually enough time to implement a fix.

But it misses a specific situation that I’ve seen a fair bit of lately: bigint sequences on integer columns. Robert mentioned in his talk that Django had a bug that caused this; which gave me the idea for this next check. Over the past year, I’ve seen this with at least one other ORM and a manually-managed schema.

For reference, this is the error you get when you’re using a bigint sequence and overflow your integer column:

gabrielle=# INSERT INTO my_stuff (stuff) values ('and goodbye');
ERROR: integer out of range

(If you landed here by searching for this error, note that a bigint sequence isn’t the only cause of this error – it could just be your application trying to insert a stupidly large value into the column. That’s usually simpler to fix.)

Here’s a query that collects both the column datatype and the sequence datatype for all of your sequences, plus the last value used:

SELECT ts.nspname                  AS table_schema
       , tbl.relname               AS table_name
       , c.attname                 AS column_name
       , pg_type.typname::regtype  AS column_datatype
       , sequences.schemaname      AS sequence_schema
       , s.relname                 AS sequence_name
       , sequences.data_type       AS sequence_datatype
       , sequences.last_value
  FROM pg_class s
  JOIN pg_namespace ns  ON ns.oid = s.relnamespace
  JOIN pg_depend d      ON d.refobjid   = s.oid      AND d.refclassid='pg_class'::regclass
  JOIN pg_attrdef ad    ON ad.oid       = d.objid    AND d.classid = 'pg_attrdef'::regclass
  JOIN pg_attribute c   ON c.attrelid = ad.adrelid AND c.attnum = ad.adnum
  JOIN pg_type          ON pg_type.oid = c.atttypid
  JOIN pg_class tbl     ON tbl.oid = ad.adrelid
  JOIN pg_namespace ts  ON ts.oid = tbl.relnamespace
  JOIN pg_sequences sequences ON sequences.sequencename = s.relname
 WHERE s.relkind = 'S'
  AND d.deptype IN ('a', 'n');

I run both of these about once a week and track the rate of change on all integer sequences, and on integer columns that have bigint sequences. Is it perfect? No, but it reduces the likelihood of an outage from this problem. There’s good info in Robert’s talk about other places you can get tripped up, and the fixes would be excellent fire drill type exercises if your team is into that sort of thing. (If you’re getting the feeling you should just go watch that talk… you’re right.)

Have a great weekend!

Addendum: Before you spring into action, check how fast the sequence is incrementing by collecting a few data points, then determine how much time you have:

(2,100,000,000 – [current_value]) / [rate of increase] = time until doom

For example, if the current sequence value is 1,300,000,000 and your sequence is incrementing by 7,000/day:

(2,100,000,000 – 1,300,000,000) / (7,000/day) = about 300 years.

So this particular example isn’t worth the trouble of changing the sequence or column data type, as long as this rate stays the same. It’s not a bad idea to verify that periodically.

Advertisement

5 Responses to “PGSQL Phriday #004: Sequence Survey”

  1. Here’s production freshports.org:

    freshports.org=> SELECT ts.nspname                  AS table_schema
           , tbl.relname               AS table_name
           , c.attname                 AS column_name
           , pg_type.typname::regtype  AS column_datatype
           , sequences.schemaname      AS sequence_schema
           , s.relname                 AS sequence_name
           , sequences.data_type       AS sequence_datatype
           , sequences.last_value
      FROM pg_class s
      JOIN pg_namespace ns  ON ns.oid = s.relnamespace
      JOIN pg_depend d      ON d.refobjid   = s.oid      AND d.refclassid='pg_class'::regclass
      JOIN pg_attrdef ad    ON ad.oid       = d.objid    AND d.classid = 'pg_attrdef'::regclass
      JOIN pg_attribute c   ON c.attrelid = ad.adrelid AND c.attnum = ad.adnum
      JOIN pg_type          ON pg_type.oid = c.atttypid
      JOIN pg_class tbl     ON tbl.oid = ad.adrelid
      JOIN pg_namespace ts  ON ts.oid = tbl.relnamespace
      JOIN pg_sequences sequences ON sequences.sequencename = s.relname
     WHERE s.relkind = 'S'
      AND d.deptype IN ('a', 'n');
     table_schema |       table_name       | column_name | column_datatype | sequence_schema |         sequence_name         | sequence_datatype | last_value 
    --------------+------------------------+-------------+-----------------+-----------------+-------------------------------+-------------------+------------
     public       | flavors                | id          | bigint          | public          | flavors_id_seq                | bigint            |     113863
     public       | generate_plist         | id          | bigint          | public          | generate_plist_id_seq         | bigint            |  142920882
     public       | package_flavors        | id          | bigint          | public          | package_flavors_id_seq        | bigint            |     113863
     public       | announcements          | id          | integer         | public          | announcements_id_seq          | bigint            |        120
     public       | cache_clearing_dates   | id          | integer         | public          | cache_clearing_dates_id_seq   | bigint            |     329901
     public       | cache_clearing_ports   | id          | integer         | public          | cache_clearing_ports_id_seq   | bigint            |   41407900
     public       | categories             | id          | integer         | public          | categories_id_seq             | bigint            |        135
     public       | commit_log             | id          | integer         | public          | commit_log_id_seq             | bigint            |     941104
     public       | commit_log_elements    | id          | integer         | public          | commit_log_elements_id_seq    | bigint            |    4832664
     public       | commit_log_ports_vuxml | id          | integer         | public          | commit_log_ports_vuxml_id_seq | bigint            |  158582407
     public       | design_results         | id          | integer         | public          | design_results_id_seq         | bigint            |         96
     public       | element                | id          | integer         | public          | element_id_seq                | bigint            |    1266386
     public       | graphs                 | id          | integer         | public          | graphs_id_seq                 | bigint            |         32
     public       | listen_for             | id          | integer         | public          | listen_for_id_seq             | bigint            |         14
     public       | page_load_detail       | id          | integer         | public          | page_load_detail_id_seq       | bigint            |  571128120
     public       | page_load_summary      | id          | integer         | public          | page_load_summary_id_seq      | bigint            |  165163806
     public       | ports                  | id          | integer         | public          | ports_id_seq                  | bigint            |      80063
     public       | ports_check            | id          | integer         | public          | ports_check_id_seq            | bigint            |      42963
     public       | ports_conflicts        | id          | bigint          | public          | ports_conflicts_id_seq        | bigint            |     271335
     public       | ports_moved            | id          | integer         | public          | ports_moved_id_seq            | bigint            |   47740977
     public       | ports_updating         | id          | integer         | public          | ports_updating_id_seq         | bigint            |    1315575
     public       | repo                   | id          | integer         | public          | repo_id_seq                   | bigint            |          9
     public       | report_frequency       | id          | integer         | public          | report_frequency_id_seq       | bigint            |          5
     public       | report_log             | id          | integer         | public          | report_log_id_seq             | bigint            |      25586
     public       | reports                | id          | integer         | public          | reports_id_seq                | bigint            |          6
     public       | sanity_test_failures   | id          | integer         | public          | sanity_test_failures_id_seq   | bigint            |       1368
     public       | security_notice        | id          | integer         | public          | security_notice_id_seq        | bigint            |         89
     public       | security_notice_audit  | id          | integer         | public          | security_notice_audit_id_seq  | bigint            |          3
     public       | system                 | id          | integer         | public          | system_id_seq                 | bigint            |           
     public       | system_branch          | id          | integer         | public          | system_branch_id_seq          | bigint            |        164
     public       | tasks                  | id          | integer         | public          | tasks_id_seq                  | bigint            |          3
     public       | users                  | id          | integer         | public          | users_id_seq                  | bigint            |      19086
     public       | vuxml                  | id          | integer         | public          | vuxml_id_seq                  | bigint            |    2296489
     public       | vuxml_affected         | id          | integer         | public          | vuxml_affected_id_seq         | bigint            |    3485370
     public       | vuxml_names            | id          | integer         | public          | vuxml_names_id_seq            | bigint            |    5927184
     public       | vuxml_ranges           | id          | integer         | public          | vuxml_ranges_id_seq           | bigint            |    3956031
     public       | vuxml_references       | id          | integer         | public          | vuxml_references_id_seq       | bigint            |    8426044
     public       | watch_list             | id          | integer         | public          | watch_list_id_seq             | bigint            |      20524
     public       | watch_list_staging     | id          | integer         | public          | watch_list_staging_id_seq     | bigint            |    2220458
     public       | watch_list_staging_log | id          | integer         | public          | watch_list_staging_log_id_seq | bigint            |      39492
     public       | watch_notice           | id          | integer         | public          | watch_notice_id_seq           | bigint            |           
     public       | watch_notice_log       | id          | integer         | public          | watch_notice_log_id_seq       | bigint            |        228
    (42 rows)
    
  2. Thank you so much for contributing to #pgsqlpriday! Yes, “scripts” in this context are “SQL scripts” :). Question: do you have a GitHub or other version control repo for your “vault of scripts”?

Trackbacks

whattaya say?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: