4

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?

  • 1
    Commentary on this question has been moved to chat, and summarized in a Community Wiki answer below. – Paul White 9 Dec 19 '16 at 10:36
9
+50

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
| improve this answer | |
6

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.

-3

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

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)
| improve this answer | |
  • 2
    Hi Evan, thanks a million, but the need is for an actual sequence. – Fattie Dec 7 '16 at 18:07
  • Why? You never explain explain why you want that to be sequential? Does it matter if you get back 1, or 29894? – Evan Carroll Dec 7 '16 at 18:10
  • 7
    I've cleaned up the comments here, please keep them constructive about the answer. And @EvanCarroll leave the unnecessary commentary about other products out of answers. – Taryn Dec 7 '16 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.