Thread: Controling Rule's Firing Order

Hello!

I have seen in TODO saying that "Allow user to
control trigger firing order" will be added to
version 7.3. This is an extremely good news to me.

I would like to ask two question related to this new
feature:

(Q1) Are we going to be able to control the firing
order for rule too? That is, controling the rule to
be fired before or after some specific triggers?

(Q2) Is there any existing document for version 7.3
explaining how to control the trigger firing order?

My best regards to pgsql developers,

CN

--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com

<cnliou@eurosport.com> writes:
> (Q2) Is there any existing document for version 7.3
> explaining how to control the trigger firing order?

Rules and triggers both fire in alphabetical order in 7.3.

            regards, tom lane

Thank you very much! Tom,

I forgot to raise another question:

Does foreign key constraints also fire in
alphabetical order in 7.3?

Regards,

CN


> Rules and triggers both fire in alphabetical order
in 7.3.

--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com

Please pardon me for having not asked the questions
systematically!

Suppose I have master table (table1) and detail table
(table2), and both table1 have their own triggers and
rules.

In what order do these foreign key constraint,
triggers, and rules be fired?  Please do not forget
to explain whether or not table1 and table2 are
treated in the following manner:

(1) When table1 is deleted, table2 is deleted first.
(2) When primary key value of table1 is updated, the
primary key value of table1 is updated first.

Regards,

CN

--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com

<cnliou@eurosport.com> writes:
> Does foreign key constraints also fire in
> alphabetical order in 7.3?

Foreign key triggers will fire in alphabetical order along with other
triggers.  This means they're firing based on a textual sort of the
OIDs assigned to the triggers, which will often but not always
correspond to creation order.

I'm having a hard time visualizing a situation where it's reasonable
to depend on FK trigger firing order, actually.  Convince me why I
should care about this?

            regards, tom lane

Hello! Tom,

Thank you very much for the patience! Probably you are my last hope.

> Foreign key triggers will fire in alphabetical order along with other triggers.
> This means they're firing based on a textual sort of the OIDs assigned to the
> triggers, which will often but not always correspond to creation order.

I don't fully understand the meaning of the term "firing in alphabetic
order" you mentioned earlier and "they're firing based on a textual sort
of the OIDs assigned to the triggers". OIDs are integers. Why terms
"textual" and "alphabetic" are used?  Did you actually referring them to
the _names_ of triggers, rules, and FK triggers, instead of their OIDs?

Hopefully the answer is not "their oids". Triggers/FK triggers/rules are
frequently dropped and recreated during database development stage.
Please imagine I have rule1, FK trigger2, trigger3, and trigger4 to be
fired in this listed order. If I change the logic of rule1, then I will
also have to remember to drop FK trigger2, trigger3, and trigger4 and
recreate them after rule1 is dropped and recreated. Seeing this, I feel
using OIDs as the control for firing order makes database design work
very complicate and the design result is prone to errors.

Now back to the topic of your concern about the value of controling FK
trigger's firing order. I have prepared this example trying to prove
that they are worthy being added to the already very powerful
postgresql. I want the system to work in this way:

- When exchange rate in SaleMaster is changed or when price in
SaleDetail is inserted/updated/deleted, VoucherDetail reflects the
change immediately.
- VoucherMaster is not created until SaleDetail is inserted.
- VocherMaster is also deleted when SaleMaster is deleted.

Problems arise if I have no control over the firing order of FK trigger
as the following 2 scenarios.

Scenario 1: When SaleNumber in SaleMaster is updated.
If trigger SaleMasterU is fired before FK trigger fkSale, then statement

FOR rec IN SELECT * FROM SaleDetail WHERE SaleNumber=varSaleNo LOOP

in UpdateVoucher(), called by trigger SaleMusterU, will do nothing. This
is because column SaleNumber in SaleDetail now still keeps old value
while varSaleNo contains new one. The net result is that the data is
incorrect.

Scenario 2: When SaleMaster is deleted.
VoucherMaster will be deleted by trigger SaleMasterD. However, if
trigger SaleMasterD is fired before FK trigger fkSale, VoucherMaster
will be re-inserted by trigger SaleDetailD which is an orphan master
row.

Regards,

CN
==============
CREATE TABLE SaleMaster(
SaleNumber INTEGER PRIMARY KEY,
ExchangeRate NUMERIC
);

CREATE TABLE SaleDetail(
CONSTRAINT fkSale FOREIGN KEY (SaleNumber) REFERENCES SaleMaster
(SaleNumber) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (SaleNumber,ItemSold),
SaleNumber INTEGER,
ItemSold TEXT,
price NUMERIC
);

CREATE TABLE VoucherMaster(
VoucherNumber SERIAL PRIMARY KEY,
SaleNumber INTEGER
);

CREATE TABLE VoucherDetail(
CONSTRAINT fkVoucher FOREIGN KEY (VoucherNumber) REFERENCES
VoucherMaster (VoucherNumber) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (VoucherNumber,item),
VoucherNumber SERIAL PRIMARY KEY,
item TEXT,
amount NUMERIC
);

CREATE FUNCTION UpdateVoucher(TEXT) RETURNS BOOLEAN AS '
DECLARE
  varSaleNo ALIAS FOR $1;
  varExchangeRate NUMERIC;
  varVoucherNo TEXT;
  varItem TEXT;
  rec RECORD;
  amount NUMERIC;
BEGIN
  SELECT ExchangeRate INTO varExchangeRate FROM SaleMaster WHERE
SaleNumber=varSaleNo;

  SELECT VoucherNo INTO varVoucherNo FROM VoucherMaster WHERE
SaleNo=varSaleNo;
  IF NOT FOUND THEN
    INSERT INTO VoucherMaster(SaleNo) VALUES (varSaleNo);
  END IF;

  --Delete all VoucherDetail and regenerate.
  DELETE FROM VoucherDetail WHERE VoucherNo=varVoucherNo;

  FOR rec IN SELECT * FROM SaleDetail WHERE SaleNumber=varSaleNo LOOP
    --The logic for the real function is more complicate than this
example.
    --That is this function exists for.
    varItem:=rec.ItemSold;
    amount:=rec.price*varExchangeRate;
    INSERT INTO VoucherDetail VALUES (VoucherNumber,varItem,amount);
  END LOOP;

  RETURN TRUE;
END;' LANGUAGE 'plpgsql';
-----------------------
CREATE FUNCTION FuncSaleMasterU() RETURNS OPAQUE AS '
BEGIN
  IF NEW.SaleNumber <> OLD.SaleNumber THEN
    UPDATE VoucherMaster SET SaleNo=NEW.SaleNumber WHERE
SaleNo=OLD.SaleNumber;
  END IF;

  IF NEW.ExchangeRate <> OLD.ExchangeRate THEN
    PERFORM UpdateVoucher(NEW.SaleNumber);
  END IF;

  RETURN NEW;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER SaleMasterU AFTER UPDATE ON SaleMaster FOR EACH ROW
EXECUTE PROCEDURE FuncSaleMasterU();
-----------------------
CREATE FUNCTION FuncSaleMasterD() RETURNS OPAQUE AS '
BEGIN
  --If sale master is gone, delete the corresponding voucher master,
too.
  DELETE FROM VoucherMaster WHERE SaleNo=OLD.SaleNumber;
  RETURN OLD;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER SaleMasterD BEFORE DELETE ON SaleMaster FOR EACH ROW
EXECUTE PROCEDURE FuncSaleMasterD();
-----------------------
CREATE FUNCTION FuncSaleDetailI() RETURNS OPAQUE AS '
BEGIN
  PERFORM UpdateVoucher(NEW.SaleNumber);
  RETURN NEW;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER SaleDetailI AFTER INSERT ON SaleDetail FOR EACH ROW
EXECUTE PROCEDURE FuncSaleDetailI();
-----------------------
CREATE FUNCTION FuncSaleDetailU() RETURNS OPAQUE AS '
BEGIN
  IF NEW.ItemSold <> OLD.ItemSold THEN
    UPDATE VoucherDetail SET item=NEW.ItemSold WHERE
VoucherNumber=(SELECT VoucherNumber FROM VoucherMaster WHERE
SaleNo=NEW.SaleNumber) AND item=OLD.ItemSold;
  END IF;

  IF NEW.price <> OLD.price THEN
    PERFORM UpdateVoucher(NEW.SaleNumber);
  END IF;

  RETURN NEW;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER SaleDetailU AFTER UPDATE ON SaleDetail FOR EACH ROW
EXECUTE PROCEDURE FuncSaleDetailU();
--------------------------
CREATE FUNCTION FuncSaleDetailD() RETURNS OPAQUE AS '
BEGIN
  PERFORM UpdateVoucher(OLD.SaleNumber);
  RETURN OLD;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER SaleDetailD AFTER DELETE ON SaleDetail FOR EACH ROW
EXECUTE PROCEDURE FuncSaleDetailD();

On Sat, 24 Aug 2002, cn wrote:

> Hello! Tom,
>
> Thank you very much for the patience! Probably you are my last hope.
>
> > Foreign key triggers will fire in alphabetical order along with other triggers.
> > This means they're firing based on a textual sort of the OIDs assigned to the
> > triggers, which will often but not always correspond to creation order.
>
> I don't fully understand the meaning of the term "firing in alphabetic
> order" you mentioned earlier and "they're firing based on a textual sort
> of the OIDs assigned to the triggers". OIDs are integers. Why terms
> "textual" and "alphabetic" are used?  Did you actually referring them to
> the _names_ of triggers, rules, and FK triggers, instead of their OIDs?

The name of a foreign key trigger is a constant beginning followed by an
oid.

> Now back to the topic of your concern about the value of controling FK
> trigger's firing order. I have prepared this example trying to prove
> that they are worthy being added to the already very powerful
> postgresql. I want the system to work in this way:

You can't easily control which foreign key trigger runs first, but you
should be able to make triggers run before/after the foreign
key trigger by picking names before/after the constant part of the
fk trigger name.  I'm not sure how triggers fired from statements
within trigger functions would play into that however.

cn dijo:


> > Foreign key triggers will fire in alphabetical order along with other triggers.
> > This means they're firing based on a textual sort of the OIDs assigned to the
> > triggers, which will often but not always correspond to creation order.
>
> I don't fully understand the meaning of the term "firing in alphabetic
> order" you mentioned earlier and "they're firing based on a textual sort
> of the OIDs assigned to the triggers". OIDs are integers. Why terms
> "textual" and "alphabetic" are used?  Did you actually referring them to
> the _names_ of triggers, rules, and FK triggers, instead of their OIDs?

Foreign key constraints are implemented internally with triggers.  Those
triggers are named after the foreign key that they serve, and that name
contains the OID that is apparently the only means of distinguishing
from others FK triggers (on the same table?).  The firing order of
foreign key is thus the firing order of the trigger, which is by OID (by
the OID their name contains).

I unfortunatly just broke my copy of Postgres so cannot show you an
example, but try looking at the names of the triggers inside your
database (yes, system catalog poking is a very interesting exercise).

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Las cosas son buenas o malas segun las hace nuestra opinion" (Lisias)

Thank you! Alvaro and Stephan,

> The name of a foreign key trigger is a constant
> beginning followed by an oid.

Now I think I have found that FK trigger names look
like this:

RI_ConstraintTrigger_<OID>

> but you should be able to make triggers run
> before/after the foreign key trigger by picking
> names before/after the constant part of the
> fk trigger name.

Yes, this is the way for me to go. The last missing
better-have feature is the firing order among FK
triggers for master and detail tables:

- When the primary key of master record is to be
updated, the primary key of this master record is
updated before those of its child records.

- When the master record is to be deleted, its detail
records are first deleted.

Best Regards,

CN

--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com

On Mon, 26 Aug 2002 cnliou@eurosport.com wrote:

> Thank you! Alvaro and Stephan,
>
> > The name of a foreign key trigger is a constant
> > beginning followed by an oid.
>
> Now I think I have found that FK trigger names look
> like this:
>
> RI_ConstraintTrigger_<OID>
>
> > but you should be able to make triggers run
> > before/after the foreign key trigger by picking
> > names before/after the constant part of the
> > fk trigger name.
>
> Yes, this is the way for me to go. The last missing
> better-have feature is the firing order among FK
> triggers for master and detail tables:
>
> - When the primary key of master record is to be
> updated, the primary key of this master record is
> updated before those of its child records.

If you mean, upon an update of master record, the update occurs
before the referential action, this should already be true
since it's an after trigger.

> - When the master record is to be deleted, its detail
> records are first deleted.

Unless we've misread the spec (which is possible) the above
does not comply with the foreign key specification.  There
are disagreements about when the referential action should
occur, but the earliest anyone's found justification for
is upon marking a row for deletion the detail rows are marked
for deletion (we currently actually do it after this point
because we don't have an agreement as to what some of the phrases
mean).