Postgres : Executing SELECT within a transaction does not return most recent rows

Question

Given a table layout that looks like this

inv_items
=========
* id bigserial
sku character varying(22)
name character varying(32)
...

inv_items_stock
===============
item_id bigint (inv_items)
qty int
...

inv_items_group
===============
group_id bigint (inv_items)
item_id bigint (inv_items)

where an item may be a "stock item" or a "group item" (with compound items...) I have a trigger that checks before insert / update on each inv_items_stock and inv_items_group to make sure that some conditions are met; for example, a stock item cannot be a group item.

The problem is that, in my program, I run a batch of inserts inside a transaction; first I insert into inv_items, then into one of the other table. Unfortunately, the SELECT inside my trigger function does not find any item that I just inserted within the transaction, thus always fails.

For example, here is the group item check trigger

CREATE FUNCTION inv_check_set_group_items() RETURNS trigger AS $INV_GRP_SET$
DECLARE found_item bigint;
BEGIN
   SELECT id INTO found_item FROM inv_groups_vw WHERE id = NEW.group_id;
   IF found_item IS NULL THEN
       RAISE EXCEPTION 'Invalid group item';
   END IF;
   RETURN NEW;
END;
$INV_GRP_SET$ LANGUAGE plpgsql;

CREATE TRIGGER inv_check_set_group_items BEFORE INSERT OR UPDATE ON inv_items_group
FOR EACH ROW EXECUTE PROCEDURE inv_check_set_group_items();

Where inv_groups_vw is a VIEW with the predefined SELECT to return all the item groups inside inv_items.

Is the problem with the VIEW or the FUNCTION? What can I do to fix this and be able to select rows of newly inserted items within the transaction? ... am I missing anything else?

Could it be that my condition IF found_item IS NULL THEN is flawed?

Answer 1

I think your problem is simply having a BEFORE trigger - it fires before the row is inserted which cannot appear in the view yet. Change it to AFTER and (after considering Craig's suggestion) you are done.