Re: Transition Tables doesn´t have OID - Mailing list pgsql-general
From | Ron |
---|---|
Subject | Re: Transition Tables doesn´t have OID |
Date | |
Msg-id | 2121577b-0c08-4ab9-28a8-797a12f05d0f@gmail.com Whole thread Raw |
In response to | Transition Tables doesn´t have OID (PegoraroF10 <marcos@f10.com.br>) |
Responses |
Re: Transition Tables doesn´t have OID
(PegoraroF10 <marcos@f10.com.br>)
|
List | 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.
pgsql-general by date: