Can the auto-increment identifier ever change from the average transaction value when committed?

Question

The possibility of this incident seems extremely unlikely to me because of the problems that this may cause, but I decided that I would still ask a question ...

Imagine a transaction in which an auto-increment identifier is involved, and a value is assigned. Prior to COMMIT, the involved code caches a copy of the assigned identifier for subsequent reference. Then the transaction is completed.

Assuming there is no direct client intervention (deleting or changing a record), is there any database or situation that would ever automatically change the identifier immediately after COMMIT, making the cache identifier invalid? Is it always safe to cache an average transaction ID?

One hypothetical case where I can imagine that this is happening is if some DBMS implementation inexplicably decided that it was necessary to have pointless and time-dependent auto-increment values ​​(since I see many examples of people who want this). In this hypothetical case, I can imagine that some magical shuffling of identifiers can be done to fill in the gaps caused by rollbacks after the ID assignment in another transaction (or a different gap in the gap). This will invalidate the cached value.

Does anyone know of such an implementation or another cache cache?

Answer 1

. , , . , . , pl/sql , , .

, id: / - . , . , , .

Answer 2

PostgreSQL DEFERRED , COMMIT.

CREATE TABLE test_autoinc (id BIGSERIAL);

CREATE TABLE test_other (id BIGSERIAL);

CREATE FUNCTION prc_update_autoinc()
RETURNS TRIGGER
AS
$$
BEGIN
        UPDATE  test_autoinc
        SET     id = id + 10;
        RETURN  NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE CONSTRAINT TRIGGER
        trg_other_ai
AFTER INSERT
ON      test_other
DEFERRABLE
INITIALLY DEFERRED
FOR EACH ROW
EXECUTE PROCEDURE prc_update_autoinc();

BEGIN TRANSACTION;

INSERT
INTO    test_autoinc
DEFAULT VALUES;

INSERT
INTO    test_other
DEFAULT VALUES;

SELECT  *
FROM    test_autoinc;

COMMIT;

SELECT  *
FROM    test_autoinc;

SELECT ( COMMIT) 1, ( COMMIT) 11.