Обсуждение: Transition Tables doesn´t have OID
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 ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
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.
yes, mytable has OID column select oid, ID, Name from MyTable limit 3 oid id name 279515 11000004 Carol 279516 11000006 Dalva 279517 11008354 Melissa -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 12/1/18 4: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 ? You will want to get away from using OID's as they are going away: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=578b229718e8f15fa779e20f086c4b6bb3776106 > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > -- Adrian Klaver adrian.klaver@aklaver.com
>>>>> "PegoraroF10" == PegoraroF10 <marcos@f10.com.br> writes: PegoraroF10> I would like to user OID value because we change our PegoraroF10> primary keys, sometimes, OID doesn´t. "oid" as a special system column and the WITH OIDS option when creating tables are being removed in pg12 (having been deprecated for something like 12 years now), you might want to start thinking about alternatives. PegoraroF10> So, there is a way to get OID on transition tables ? Well, arguably it is an oversight in the implementation of transition tables that they were not added to the various places in the parser that treat "oid" as a system column name. However, I not sure that you'll get any agreement to fix that in light of the demise of "oid" as mentioned above. (Not least because backpatching it could be dangerous in that it could break queries that now work, by making "oid" an ambiguous column reference.) I tried various workarounds, but they were defeated by the fact that evaluation of a whole-row Var does not copy the oid value (if any). (I'm not sure if this ever really worked, so calling it a bug may be a bit of a stretch.) -- Andrew (irc:RhodiumToad)
ok then, help me to find alternatives to it. As I sad, sometimes whe change our PK, so using OID would be a smart way to have a join between old and new transition tables and we would like to use transition tables because each statement is a lot faster than each row for auditing purposes. So, whats the alternative ? One trigger for each row just for changing PK values (will occur few times) and another trigger for each statement to do the logging ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 1/12/18 6:51 μ.μ., PegoraroF10 wrote: > ok > then, help me to find alternatives to it. > As I sad, sometimes whe change our PK, so using OID would be a smart way to > have a join between old and new transition tables and we would like to use > transition tables because each statement is a lot faster than each row for > auditing purposes. Unique key with a sequence, which you'll have to leave alone. > > So, whats the alternative ? One trigger for each row just for changing PK > values (will occur few times) and another trigger for each statement to do > the logging ? > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html >
On 12/1/18 8:51 AM, PegoraroF10 wrote: > ok > then, help me to find alternatives to it. > As I sad, sometimes whe change our PK, so using OID would be a smart way to > have a join between old and new transition tables and we would like to use > transition tables because each statement is a lot faster than each row for > auditing purposes. > > So, whats the alternative ? One trigger for each row just for changing PK > values (will occur few times) and another trigger for each statement to do > the logging ? If you don't want to change the structure of your tables that would be seem to be the way to go. It will require some thought to make sure the 'for each row' and 'for each statement' don't interfere with each other. > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sun, Dec 2, 2018 at 7:38 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 12/1/18 8:51 AM, PegoraroF10 wrote: > > ok > > then, help me to find alternatives to it. > > As I sad, sometimes whe change our PK, so using OID would be a smart way to > > have a join between old and new transition tables and we would like to use > > transition tables because each statement is a lot faster than each row for > > auditing purposes. > > > > So, whats the alternative ? One trigger for each row just for changing PK > > values (will occur few times) and another trigger for each statement to do > > the logging ? > > If you don't want to change the structure of your tables that would be > seem to be the way to go. It will require some thought to make sure the > 'for each row' and 'for each statement' don't interfere with each other. I also contemplated this question when hacking on transition tables. We know that the new and old tuplestores are ordered in the same way (as an implementation detail), but there is no way to take advantage of that knowledge in SQL, where relations are unordered sets. There is a syntax WITH ORDINALITY that SQL uses to deal with the fact that set-returning functions' results might really be be ordered, and you might not want to lose that information. Suppose we allowed WITH ORDINALITY for transition tables, so that the 'capture' order of rows could be exposed, and we promised that old and new ORDINALTITY numbers will line up, and then we made the ORDINALITY column a pathkey of the scan. Now you could join old and new tables by the ORDINALITY column, and get a merge join without any sorting. That's... pretty weird though, and the syntax would be outside the SQL spec, and the semantics might be questionable. -- Thomas Munro http://www.enterprisedb.com