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