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.
the gabriellephant 