Thread: Weird Trigger behaviour

Hi Jeff,

Here is the letter I sent to bugs.

------------------------

Version 7.2.1

I have a small table with a unique primary key and a second history
table that inherits the first and add a modification tstamp.

If I attempt to insert a duplicate it is not permitted by psql as
expected.

I have a trigger on the table to write the OLD record to the history
table. I have tried both before and after, it doesn't change the
behaviour.

When I update a row in the table, I get a duplicate created. i.e. the
update does an insert which allows the duplicate key. I also get the
correct record in the history table.

drop table "vlan_hist";
drop table "vlan";
create table "vlan" (
       "id" integer,
       "name" text,
       "site" text,
       "network" inet,
       "gateway" inet,
       primary key( "id" ) );

create table "vlan_hist" (
       "mod_tstamp" timestamp )
       inherits( "vlan" );

drop function "vlan_mod" ();
create function "vlan_mod" ( ) returns opaque as '
begin
  insert into "vlan_hist" values (
     OLD.id, OLD.name, OLD.site, OLD.network, OLD.gateway,
     ''now()'' );
  return NULL;
end;
' language 'plpgsql';

drop trigger "vlan_mod_trigger" on "vlan";
create trigger "vlan_mod_trigger"
   after UPDATE on "vlan"
   for each row execute procedure "vlan_mod" ();

insert into vlan values ( 1,
'Here','TUR','130.123.104.0/22','130.123.104.200' );

netdbase=> update vlan set name='No Ok' where id=1;
UPDATE 1
netdbase=> select * from vlan;
 id | name  | site |     network      |     gateway
----+-------+------+------------------+-----------------
  1 | No Ok | TUR  | 130.123.104.0/22 | 130.123.104.200
  1 | Here  | TUR  | 130.123.104.0/22 | 130.123.104.200
(2 rows)

--

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 5301
Ph/Fax: +64 6 357 8168, Mob: +64 21 424 015

Glen Eustace dijo:

> I have a small table with a unique primary key and a second history
> table that inherits the first and add a modification tstamp.

> When I update a row in the table, I get a duplicate created. i.e. the
> update does an insert which allows the duplicate key. I also get the
> correct record in the history table.

This is the expected behavior.  When you do this

> netdbase=> select * from vlan;
>  id | name  | site |     network      |     gateway
> ----+-------+------+------------------+-----------------
>   1 | No Ok | TUR  | 130.123.104.0/22 | 130.123.104.200
>   1 | Here  | TUR  | 130.123.104.0/22 | 130.123.104.200
> (2 rows)

the rows from both tables are returned.  If you want only the parent
table, you have to use

SELECT * from ONLY vlan;

This behavior changed in 7.1 or so, where you had to indicate that you
wanted to recurse to child tables; in 7.1 and later, you have to
indicate that you do _not_ want to recurse.

There is a known bug in that the inheritance allows you to bypass the
uniqueness check.  This issue and others are being discussed, but won't
be fixed in 7.3 (maybe 7.4 or later)

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Some men are heterosexual, and some are bisexual, and some
men don't think about sex at all... they become lawyers" (Woody Allen)