Transition Tables doesn´t have OID

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Transition Tables doesn´t have OID

PegoraroF10
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

Reply | Threaded
Open this post in threaded view
|

Re: Transition Tables doesn´t have OID

Ron-2
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.

Reply | Threaded
Open this post in threaded view
|

Re: Transition Tables doesn´t have OID

PegoraroF10
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

Reply | Threaded
Open this post in threaded view
|

Re: Transition Tables doesn´t have OID

Adrian Klaver-4
In reply to this post by PegoraroF10
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Transition Tables doesn´t have OID

Andrew Gierth
In reply to this post by PegoraroF10
>>>>> "PegoraroF10" == PegoraroF10  <[hidden email]> 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)

Reply | Threaded
Open this post in threaded view
|

Re: Transition Tables doesn´t have OID

PegoraroF10
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

Reply | Threaded
Open this post in threaded view
|

Re: Transition Tables doesn´t have OID

Achilleas Mantzios

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
>

Reply | Threaded
Open this post in threaded view
|

Re: Transition Tables doesn´t have OID

Adrian Klaver-4
In reply to this post by PegoraroF10
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Transition Tables doesn´t have OID

Thomas Munro-3
On Sun, Dec 2, 2018 at 7:38 AM Adrian Klaver <[hidden email]> 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