1

Given the following table in PostgreSQL 11.1, what is the order of logical decoding output rows?

CREATE TABLE t1 (
  t1_id INTEGER PRIMARY KEY
  ,name TEXT
)
;
CREATE TABLE t2 (
  t2_id INTEGER PRIMARY KEY
  ,t1_id INTEGER NOT NULL
  ,name TEXT
  ,CONSTRAINT fkt2_t1id FOREIGN KEY (t1_id)
    REFERENCES t1 (t1_id) ON DELETE NO ACTION ON UPDATE CASCADE
    DEFERRABLE INITIALLY DEFERRED
)
;
CREATE TABLE t3 (
  t3_id INTEGER PRIMARY KEY
  ,t2_id INTEGER NOT NULL
  ,name TEXT
  ,CONSTRAINT fkt3_t2id FOREIGN KEY (t2_id)
    REFERENCES t2 (t2_id) ON DELETE NO ACTION ON UPDATE CASCADE
    DEFERRABLE INITIALLY DEFERRED
)
;

Because of DEFERRABLE foreign key, in one transaction, a referencing row (t3, t2) can be inserted before the referenced rows (t2, t1).

BEGIN
;
INSERT INTO t3 VALUES
(1, 1, '3')
;
INSERT INTO t2 VALUES
(1, 1, '2')
;
INSERT INTO t1 VALUES
(1, '1')
;
COMMIT;

Question

What is the order of logical decoding output ?

  1. Referenced rows first, then referencing rows. t1 → t2 → t3

  2. Same order as statements. t3 → t2 → t1

  3. Non-deterministic order

I guess No.1 is the answer. But I want to confirm whether postgresql guarantees such order or not, with/without DEFERRABLE constraint condition.

  • Should be 2, nothing else would make sense – a_horse_with_no_name Feb 14 at 17:03
  • Ummm... logical decoding depends on WAL. You may be right. – takaomag Feb 14 at 17:31

Your Answer

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

Browse other questions tagged or ask your own question.