PostgreSQL Question posted in
The official documentation can be found here.

UNLOGGED table and ROLLBACK in PostgreSQL

From the documentation, my understanding is that when a ROLLBACK command is issued in a transaction, PostgreSQL typically employs the Write-Ahead Logging (WAL) mechanism to undo the changes made within that transaction. However, because changes made to UNLOGGED tables bypass the WAL, rolling back changes made to an unlogged table should theoretically have no effect. I attempted to replicate this scenario, yet I found that the changes were indeed rolled back.

DROP TABLE IF EXISTS test;
CREATE UNLOGGED TABLE test(
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(32) NOT NULL,
  last_name VARCHAR(32) NOT NULL
);
INSERT INTO test(first_name,last_name)
VALUES ('Joe', 'Doe'), ('Sam', 'Fenjero'), ('Victoria', 'Rais');
BEGIN;
UPDATE test SET first_name = 'X';
UPDATE test SET last_name = 'Y';
ROLLBACK;

Where might the issue lie?

Tags:

2

Answers


  1. 0

    because changes made to UNLOGGED tables bypass the WAL, rolling back changes made to an unlogged table should theoretically have no effect.

    That would be the case if rollback "rewinded" the WAL, which it doesn’t. You get WAL entries only* if you commit. When you rollback, you discard changes that would go there if you committed.
    That is to say, PostgreSQL does not employ WAL to undo the changes: instead, rollback prevents those changes from taking place, so there’s nothing to undo.

    Traffic on unlogged objects doesn’t go to WAL but it still has to get committed to be applied – by rolling back, you abandoned the updates you did, before they got persisted.

    Login or Signup to reply.
  2. 0

    PostgreSQL typically employs the Write-Ahead Logging (WAL) mechanism to undo the changes made within that transaction

    It does not. The data needed to undo changes is recorded in the data blocks themselves. The only other info needed is whether a given transaction committed or rolled back is or is still open, and that is recorded in the SLRU directory "pg_xact".

    In the case of a crash, WAL is used to make sure pg_xact (along with the other data) is recovered correctly. If there is no crash (or replication), WAL is never consulted.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search