From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Transition Tables doesn´t have OID |
Date: | 2018-12-01 12:37:05 |
Message-ID: | 2121577b-0c08-4ab9-28a8-797a12f05d0f@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 12/01/2018 06:22 AM, PegoraroF10 wrote:
> I´m trying to use transition tables for auditing purposes.
>
> create trigger MyTableAudit_UPD after update on MyTable referencing old
> table as Transition_old new table as Transition_new for each statement
> execute procedure AuditTable();
>
> create or replace function AuditTable() returns trigger language plpgsql as
> $$
> if (TG_OP = 'UPDATE') then
> insert into audittable(table_name, oid, audit_action, user_id,
> table_schema, values_old, values_new)
> select TG_TABLE_NAME, Transition_new.oid, TG_OP, CURRENT_USER,
> TG_TABLE_SCHEMA, row_to_json(Transition_old.*)::jsonb,
> row_to_json(Transition_new.*)::jsonb from Transition_new inner join
> Transition_old on Transition_new.OID = Transition_old.OID;
> elsif (TG_OP = 'DELETE') then
> insert into audittable(table_name, oid, audit_action, user_id,
> table_schema, values_old)
> select TG_TABLE_NAME, Transition_old.oid, TG_OP, CURRENT_USER,
> TG_TABLE_SCHEMA, row_to_json(Transition_old.*)::jsonb from Transition_old;
> end if;
>
> [42703] ERROR: column transition_new.oid does not exist Where: função
> PL/pgSQL audittable() linha 14 em comando SQL
>
> I would like to user OID value because we change our primary keys,
> sometimes, OID doesn´t.
>
> So, there is a way to get OID on transition tables ?
Did you create MyTable WITH OIDS (or set default_with_oids on in
postgresql.conf)? https://www.postgresql.org/docs/9.6/datatype-oid.html
Also,
https://www.postgresql.org/docs/9.6/runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS
"The use of OIDs in user tables is considered deprecated, so most
installations should leave this variable disabled. Applications that require
OIDs for a particular table should specify WITH OIDS when creating the table."
More importantly, https://wiki.postgresql.org/wiki/FAQ#What_is_an_OID.3F
"OIDs are sequentially assigned 4-byte integers. Initially they are unique
across the entire installation. However, the OID counter wraps around at 4
billion, and after that OIDs may be duplicated.
It is possible to prevent duplication of OIDs within a single table by
creating a unique index on the OID column (but note that the WITH OIDS
clause doesn't by itself create such an index). The system checks the index
to see if a newly generated OID is already present, and if so generates a
new OID and repeats. This works well so long as no OID-containing table has
more than a small fraction of 4 billion rows."
--
Angular momentum makes the world go 'round.
From | Date | Subject | |
---|---|---|---|
Next Message | Rene Romero Benavides | 2018-12-01 12:42:02 | Re: Unused indexes |
Previous Message | PegoraroF10 | 2018-12-01 12:22:18 | Transition Tables doesn´t have OID |