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