In-order sequence generation

Question

Is there a way to generate some kind of in-order identifier for a table records?

Suppose that we have two threads doing queries:

Thread 1:

begin;
insert into table1(id, value) values (nextval('table1_seq'), 'hello');
commit;

Thread 2:

begin;
insert into table1(id, value) values (nextval('table1_seq'), 'world');
commit;

It's entirely possible (depending on timing) that an external observer would see the (2, 'world') record appear before the (1, 'hello').

That's fine, but I want a way to get all the records in the 'table1' that appeared since the last time the external observer checked it.

So, is there any way to get the records in the order they were inserted? Maybe OIDs can help?

Answer 1

No. Since there is no natural order of rows in a database table, all you have to work with is the values in your table.

Well, there are the Postgres specific system columns cmin and ctid you could abuse to some degree.

The tuple ID (ctid) contains the file block number and position in the block for the row. So this represents the current physical ordering on disk. Later additions will have a bigger ctid, normally. Your SELECT statement could look like this

SELECT *, ctid   -- save ctid from last row in last_ctid
FROM   tbl
WHERE  ctid > last_ctid
ORDER  BY ctid

ctid has the data type tid. Example: '(0,9)'::tid

However it is not stable as long-term identifier, since VACUUM or any concurrent UPDATE or some other operations can change the physical location of a tuple at any time. For the duration of a transaction it is stable, though. And if you are just inserting and nothing else, it should work locally for your purpose.

I would add a timestamp column with default now() in addition to the serial column ...

I would also let a column default populate your id column (a serial or IDENTITY column). That retrieves the number from the sequence at a later stage than explicitly fetching and then inserting it, thereby minimizing (but not eliminating) the window for a race condition - the chance that a lower id would be inserted at a later time. Detailed instructions:

• Auto increment table column

Answer 2

What you want is to force transactions to commit (making their inserts visible) in the same order that they did the inserts. As far as other clients are concerned the inserts haven't happened until they're committed, since they might roll back and vanish.

This is true even if you don't wrap the inserts in an explicit begin / commit. Transaction commit, even if done implicitly, still doesn't necessarily run in the same order that the row its self was inserted. It's subject to operating system CPU scheduler ordering decisions, etc.

Even if PostgreSQL supported dirty reads this would still be true. Just because you start three inserts in a given order doesn't mean they'll finish in that order.

There is no easy or reliable way to do what you seem to want that will preserve concurrency. You'll need to do your inserts in order on a single worker - or use table locking as Tometzky suggests, which has basically the same effect since only one of your insert threads can be doing anything at any given time.

You can use advisory locking, but the effect is the same.

Using a timestamp won't help, since you don't know if for any two timestamps there's a row with a timestamp between the two that hasn't yet been committed.

You can't rely on an identity column where you read rows only up to the first "gap" because gaps are normal in system-generated columns due to rollbacks.

I think you should step back and look at why you have this requirement and, given this requirement, why you're using individual concurrent inserts.

Maybe you'll be better off doing small-block batched inserts from a single session?