Search code examples
postgresqltriggersrules

PostgreSQL: back-up table has different ID from parent

We are using DO ALSO RULEs in PostgreSQL to create auditing (history) tables.

What I've noticed is that when I have a SERIAL column, the values are "shared" between the parent and audit tables (hence you cannot use the value to JOIN the tables).

I've created by audit tables with the CREATE TABLE abc AS SELECT * FROM def...

How can I ensure that the SERIAL column have the same value between the two tables?

EDIT, here is the complete test case

STEP 1 - create the parent table (the one to be audit / history tracking)

CREATE TABLE audit_testing_parent (
    id SERIAL PRIMARY KEY,
    varchar_col1 VARCHAR NOT NULL,
    varchar_col2 VARCHAR NOT NULL,
    int_col INTEGER,
    created_on TIMESTAMP DEFAULT NOW()
);

STEP 2 - INSERT some data

INSERT INTO audit_testing_parent(varchar_col1, varchar_col2, int_col)
VALUES ('test01','test01',1);

STEP 3 - CREATE an auditing table

CREATE TABLE audit_testing_audit
AS SELECT 'INSERT' audit_Type, NOW() audit_time, a.*
   FROM audit_testing_parent a
;

STEP 4 - CREATE a RULE to audit the parent's data

create rule audit_testing_parent_on_insert as on insert to audit_testing_parent do also
insert into audit_testing_audit
(
    audit_type,
    audit_time,
    id,
    varchar_col1,
    varchar_col2,
    int_col,
    created_on
)
values
(
'INSERT',
now(),
    new.id,
    new.varchar_col1,
    new.varchar_col2,
    new.int_col,
    new.created_on
);

STEP 4 - Test the RULE by performing another INSERT

INSERT INTO audit_testing_parent(varchar_col1, varchar_col2, int_col)
VALUES ('test02','test02',2);

The parent table now looks like this: parent table

The audit table looks like this: audit table

Note the difference in the ID column.

In the parent table, the row where the int_col = 2 the id = 2

But the same row in the audit table (int_col = 2), the id = 3

How can I ensure that the RULE handles the SERIAL column so that both tables have the same value in the ID column?

Solution

  • That won't work, because that's not how rules work. If you insert nextval('someseq') into your main table, the rule will do the same thing for the audit table, so you will get the next sequence value.

    The solution is not to use a rule for this, but a trigger.