Achieving "sequence number", rather than dateTime, for updates; postgres specifically

Question

So a postgres database (specifically, an AmazonRDS instance fwiw).

It's easy enough to arrange things to get the last updated (or created) time for any record.

That information would be a datetime.

What I want to achieve: imagine an integer, sequenceNumber, which is atomic and global to the database.

Simply, every table would have a sequenceNumber field.

Any time any record (ie, in any table) is updated (or created), it gets the next sequenceNumber.

You know how you have programmer friends who know nothing about databases. Wave. My idiot solution would be, in the entire codebase, every time you create or modify a record, be sure to remember to update sequenceNumber on it. (Of course, just have one global system that gives the next atomic sequenceNumber.)

What's the sound solution for this?

Indeed, is it completely available already in some way I don't know, or?

Answer 1

As a_horse_with_no_name originally suggested in question comments:

--assuming we have tables: a, b, ...
CREATE TABLE a
(
  a_id integer NOT NULL PRIMARY KEY,
  a_name text
) ;

CREATE TABLE b
(
  b_id integer NOT NULL PRIMARY KEY,
  b_name text
) ;

First we create a global sequence and add a column in each table:

-- create one global sequence to be used by all tables
CREATE SEQUENCE global_sequence_number ;

-- add a column in each table, that uses the same sequence
ALTER TABLE a
  ADD COLUMN global_sequence_id INT NOT NULL 
    DEFAULT nextval('global_sequence_number'::regclass) ;

ALTER TABLE b
  ADD COLUMN global_sequence_id INT NOT NULL 
    DEFAULT nextval('global_sequence_number'::regclass) ;

Create trigger function (one) and UPDATE triggers (one in each table):

-- create a trigger function to update the column
CREATE OR REPLACE FUNCTION update_global_sequence_id()
RETURNS TRIGGER AS 
$$
BEGIN
   NEW.global_sequence_id = nextval('global_sequence_number'::regclass); 
   RETURN NEW;
END;
$$ language 'plpgsql';

-- add a trigger, in each table
CREATE TRIGGER update_a_global_sequence_id
  BEFORE UPDATE ON a FOR EACH ROW 
    EXECUTE PROCEDURE update_global_sequence_id();

CREATE TRIGGER update_b_global_sequence_id
  BEFORE UPDATE ON b FOR EACH ROW 
    EXECUTE PROCEDURE  update_global_sequence_id();

And profit:

 -- example of use
INSERT INTO a (a_id, a_name)
VALUES 
  (1, 'a'),  (2, 'b') ;

INSERT INTO b (b_id, b_name)
VALUES 
  (3, 'c'),  (4, 'd'),  (5, 'e') ;

SELECT *
FROM a FULL JOIN b ON FALSE 
ORDER BY COALESCE(a.global_sequence_id, b.global_sequence_id) ;

-- output
  1   a  1
  2   b  2 
             3   c  3
             4   d  4
             5   e  5

And after some updates, to check that the triggers work:

-- lets do some updates:
UPDATE a SET a_name = 'aa' WHERE a_id = 1 ;
UPDATE b SET b_name = 'cc' WHERE b_id = 3 ;

-- and see what happened
SELECT *
FROM a FULL JOIN b ON FALSE 
ORDER BY COALESCE(a.global_sequence_id, b.global_sequence_id) ;

-- output
  2   b  2 
             4   d  4
             5   e  5
  1  aa  6                   -- sequence updated to 6
             3  cc  7        -- sequence updated to 7

Answer 2

_{Community Wiki answer recording comments on the question by Craig Ringer}

Quick warning about this. If you intend to do this so your app can remember the "last seen sequence number" and do some kind of sync or batch processing based on sequence numbers, it will miss rows. The commit order of transactions is not necessarily the same order they called nextval in.

Specifically, if three xacts A, B, and C get values 1, 2, and 3, then they all commit at roughly the same time, C might become visible first, so you see 3 in the table but 1 and 2 aren't there yet. That sort of thing. PostgreSQL's logical decoding solves this problem by giving you a strictly commit-ordered stream of data, in case that's related to the problem you're working on.

The order you call nextval(...) to get sequence values isn't necessarily the same as the order the xacts commit, unless the app externally enforces that. Uncommitted rows aren't visible to SELECT. So you might SELECT * FROM the_table WHERE the_sequence_column > 0 because you last saw ID 0. You find that the greatest the_sequence_column value returned is 3. So next time you SELECT ... WHERE the_sequence_column > 3. But actually, 1 and 2 might've committed after you saw 3, so you didn't get them in the first SELECT and you won't get them in the latter.

You might be thinking "I'll just keep a list of IDs I haven't seen yet". But this will perform badly, and it'll also fail to cope with xacts that roll back, with the sequence values that get skipped over during a server crash and restart, etc. They'll never get re-used and are discarded. So your app will keep looking for values that'll never exist. Unless you can have your app enforce commit-ordering so that it commits in the order in which IDs were allocated (which might be impossible if one xact gets IDs 1, 4 and 7, and another xact gets IDs 2 and 3)... your solution won't really work, which is why you might want to look into logical decoding to get change-streams out of the database in a robust, commit-ordered, reliable way. There are plugins to stream json, for example.

Answer 3

Just use UUIDs if you want something globally unique to the database.

• gen_random_uuid() in pgcrypto can generate them
• To generate ITU-T Rec. X.667, ISO/IEC 9834-8:2005, and RFC 4122 UUIDs use uuid-ossp

The chances of a collision are roughly 'absolutely never going to happen', see this question on the serious chances of collision. Per the docs

Some systems refer to this data type as globally unique identifier, or GUID, instead.

Sequences have a lot of downsides when used everywhere in the database

• they're not gapless
• make sharding difficult
• make cross-db merging difficult too (think about merging two installs of same database)