Can other users break into my sql sequence of operations?

Question

I do insert operations into the database (Postgres) and then immediately take the last serial id. If 10 people do the same thing, can they break the sequence of my database operations?

INSERT INTO table VALUES (DEFAULT,...);
SELECT currval(pg_get_serial_sequence('table','column')) as inserted_id;

I'm afraid that may happen situation like:

INSERT INTO table VALUES (DEFAULT,...);
Meanwhile, another user was doing insertions and I end up getting the wrong id;
SELECT currval(pg_get_serial_sequence('table','column')) as inserted_id;

No, the last insert ID is per client - each user will always get the result of the last insert query **they** performed. — The Blue Dog, Feb 14 '15 at 20:56

Robert Bain · Accepted Answer · 2015-02-15T19:10:45.000

pg_get_serial_sequence(table_name, column_name) gets the name of the sequence that a serial, smallserial or bigserial column uses.

From the PostgreSQL documentation currval will:

Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.

Based on the above, noting the emphases, the answer to your question "If 10 people do the same thing, can they break the sequence of my database operations?" is no.

It's an easy one to test too. Take two instances of of pgAdmin or whatever client you prefer.

On client 1:

CREATE TABLE foo (id BIGSERIAL PRIMARY KEY, some_column TEXT);
INSERT INTO foo(some_column) VALUES('a');

On client 2:

INSERT INTO foo(some_column) VALUES('b');

On client 1:

SELECT CURRVAL(PG_GET_SERIAL_SEQUENCE('foo','id')) AS inserted_id; -- it will return 1

On client 2:

SELECT CURRVAL(PG_GET_SERIAL_SEQUENCE('foo','id')) AS inserted_id; -- it will return 2

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

It's safe even with concurrent write access, like @Robert explained very well.

Unless you have multiple column defaults obtaining numbers from sequences (typically just serial type columns), it would even be safe with the more generic lastval() - and cheaper since you don't need the extra function call with pg_get_serial_sequence(). Per documentation:

Return value most recently obtained with nextval for any sequence

You also can't have triggers causing additional sequence operations - but that could make the use of currval() unreliable as well.

Better alternative

INSERT INTO table(col1, col2, ...)  -- target list!statements
VALUES ('val1', 'val2', ...)        -- don't mention serial columns at all
RETURNING table_id;                 -- name(s) of auto-generated column(s)

This form is shorter, cheaper and even safer than using lastval() or currval(). It's even safe against multiple calls of nextval() on the same sequence (unlikely, but possibly caused by triggers, rules or side effects of functions) and changes to the search_path that might affect how identifiers are resolved. You get the actual value inserted for the column, in a single call to the server.
It's almost always better to add a target list for persisted INSERT statements. If you later change the table layout, such a statement is much less likely to break (in dangerous ways).
Completely omit columns from the INSERT that should be filled with column defaults.

More details:

If your intention is to use the new ID in another INSERT (which is the typical use case), I suggest a data-modifying CTE:

PostgreSQL multi INSERT...RETURNING with multiple columns

Can other users break into my sql sequence of operations?

2 Answers2

Better alternative