Обсуждение: Triggers failing from 7.0.2 to 7.1.3

I have a postgresql application that relies on triggers to function.
I have migrated from 7.0.2 to 7.1.3 and the triggers are failing.
I have also tried 7.1.2 and they also fail there.
The application fails on updates to the package table. If I remove the
triggers, the application runs, but of course I do not get the
updates that should come from the trigger functions.

Below I have included the pg_dump for the table that was working.

Danny Aldham
------------------

-- $Revision: 1.1.1.1 $
-- $Author: falstaff $
-- $Date: 2001/07/28 19:22:02 $
-- $Id: crpackage.sql,v 1.1.1.1 2001/07/28 19:22:02 falstaff Exp $

create table package (
    pack_code    varchar,
    status    varchar,
    config_id    int4,
    pack_id    varchar,
    asset_tag    varchar,
    mac    varchar,
    ipaddr    varchar,
    date1    date,
    date2    date,
    num_prod    int4,
    remarks    varchar,
    p_order    varchar,
    batch    varchar,
    cap_lease_num    varchar,
    cap_cost    float,
    exp_cost    float,
    tot_cost    float,
    rev    int4,
    revdate    timestamp,
    revby    int4
);
create index pack_indx on package using btree (config_id);
create index pmac_indx on package using btree (mac);

create table mupack (
    xfop    varchar,
    xfstat    varchar,
    xftm    timestamp
) inherits (package);

create function inspack() returns opaque as '
begin
    NEW.revdate := ''now'';
    NEW.rev := 1;
    insert into mupack values (NEW.pack_code, NEW.status, NEW.config_id,
    NEW.pack_id, NEW.asset_tag, NEW.mac, NEW.ipaddr, NEW.date1, NEW.date2,
    NEW.num_prod, NEW.remarks, NEW.p_order, NEW.batch, NEW.cap_lease_num,
    NEW.cap_cost, NEW.exp_cost, NEW.tot_cost, NEW.rev, NEW.revdate,
    NEW.revby, ''INS'', ''CRE'', ''now'' );
    return NEW;
end;
' LANGUAGE 'plpgsql';
create function updpack() returns opaque as '
begin
    NEW.revdate := ''now'';
    NEW.rev := OLD.rev + 1;
    insert into mupack values (OLD.pack_code, OLD.status, OLD.config_id,
    OLD.pack_id, OLD.asset_tag, OLD.mac, OLD.ipaddr, OLD.date1, OLD.date2,
    OLD.num_prod, OLD.remarks, OLD.p_order, OLD.batch, OLD.cap_lease_num,
    OLD.cap_cost, OLD.exp_cost, OLD.tot_cost, OLD.rev, OLD.revdate,
    OLD.revby, ''UPD'', ''CRE'', ''now'' );
    return NEW;
end;
' LANGUAGE 'plpgsql';
create function delpack() returns opaque as '
begin
    insert into mupack values (OLD.pack_code, OLD.status, OLD.config_id,
    OLD.pack_id, OLD.asset_tag, OLD.mac, OLD.ipaddr, OLD.date1, OLD.date2,
    OLD.num_prod, OLD.remarks, OLD.p_order, OLD.batch, OLD.cap_lease_num,
    OLD.cap_cost, OLD.exp_cost, OLD.tot_cost, OLD.rev, OLD.revdate,
    OLD.revby, ''DEL'', ''CRE'', ''now'' );
    return OLD;
end;
' LANGUAGE 'plpgsql';

--create trigger ins_pck before insert on package for each row execute
--procedure inspack();
create trigger upd_pck before update on package for each row execute
procedure updpack();
create trigger del_pck before delete on package for each row execute
procedure delpack();

Danny Aldham <danny@lennon.postino.com> writes:
> I have migrated from 7.0.2 to 7.1.3 and the triggers are failing.

Please define "fail".  You didn't actually say what's going wrong.

            regards, tom lane

When an update is done to the package table, it appears that it
has actually done an insert of the new record but not deleted the
old record. So I end up with an extra record. If I define the tables
without the trigger, the updates execute correctly.

Danny Aldham

>
> Danny Aldham <danny@lennon.postino.com> writes:
> > I have migrated from 7.0.2 to 7.1.3 and the triggers are failing.
>
> Please define "fail".  You didn't actually say what's going wrong.
>
>             regards, tom lane
>

Danny Aldham <danny@lennon.postino.com> writes:
> When an update is done to the package table, it appears that it
> has actually done an insert of the new record but not deleted the
> old record. So I end up with an extra record. If I define the tables
> without the trigger, the updates execute correctly.

I think what's really going on is not that the triggers have changed
behavior, but that SELECT has changed behavior.  You defined your log
table as a child of the data table:

create table mupack (
    xfop    varchar,
    xfstat    varchar,
    xftm    timestamp
) inherits (package);

Beginning in 7.1, "SELECT FROM package" really means "SELECT FROM package*"
... that is, it selects over child tables too, so you see the log
records along with the package records.  You need to write "SELECT FROM
ONLY package" to get the SELECT to look only at the parent table.

I believe there is a configuration variable you can set to get the old
behavior by default, but in the long run the best bet is probably not to
use inheritance simply as a way to save typing duplicate column
definitions.  The new SELECT behavior really enforces the worldview that
says a parent/child table relationship is like a superclass/subclass
relationship.  We haven't yet extended that to other behaviors (say,
making unique indexes extend over both tables), but I foresee that sort
of thing happening eventually.

Alternatively, you could make package and mupack inherit separately from
a common ancestor table.  That'd allow you to avoid duplicating the
column definitions without getting the unwanted SELECT behavior when you
query the package table.

            regards, tom lane