FAQ
I'm relatively new to postgres and SQL in general and need some assistance.




We have a database split into multiple schemas, and a program that runs an
SQL command (abbreviated for brevity) like:

INSERT INTO xxx.parts_purchasing (DEALER_ID,DATE_CHANGED)
VALUES ('xxx', '28-Apr-2008')



Where xxx is one of a number of possible schemas, and parts_purchasing is a
table. We appear to have a trigger on parts_purchasing like so:



CREATE OR REPLACE function fn_update_so_tran() RETURNS TRIGGER AS
$trg_update_so_tran$

DECLARE

BEGIN

IF (NEW.tran_status = 'U') OR (NEW.tran_status = 'D') THEN

UPDATE parts_purchasing SET

qty_received=qty_received + NEW.qty_received,

qty_invoiced = qty_invoiced + NEW.qty_invoiced,

amt_invoiced = amt_invoiced + NEW.amt_invoiced,

amt_received = amt_received + NEW.amt_received

WHERE

dealer_id = NEW.dealer_id AND

so_tran_address = NEW.so_tran_address AND

this_tran_address = so_tran_address;

END IF;

RETURN NULL;

END;

$trg_update_so_tran$ LANGUAGE plpgsql;

ALTER FUNCTION fn_update_so_tran() OWNER TO "AutoDRS";

CREATE TRIGGER trg_update_so_tran AFTER INSERT OR UPDATE on parts_purchasing
FOR EACH ROW EXECUTE PROCEDURE fn_update_so_tran();



I've pasted the whole thing because I don't know how much is important. The
problem is that it seems whenever we do the INSERT, the trigger causes an
error because it says parts_purchasing table doesn't exist. Naturally... it
does exist!



I wonder if triggers aren't schema specific, and so it's getting the schema
wrong? But if that's the case, I'm not sure how to fix it. I've tried
changing the first part of it to:



UPDATE dealer_id.parts_purchasing SET

UPDATE NEW.dealer_id..parts_purchasing SET

UPDATE OPERATOR(dealer_id.+)parts_purchasing SET

UPDATE OPERATOR(NEW.dealer_id.+)parts_purchasing SET



But none of those seem valid.



I also thought of doing a workaround where set SET SEARCH_PATH = xxx before
each INSERT. But the problem then is ... there appears to be no way to do
an UNSET SEARCH_PATH, so if a SET SEARCH_PATH failed for some reason then
we'd start clobbering the wrong schema's data.



Some advice would be greatly appreciated!



Thanks

Search Discussions

  • Tom Lane at May 5, 2008 at 2:18 pm

    "Cody Konior" <cody.konior@reynolds.com.au> writes:
    I've pasted the whole thing because I don't know how much is important. The
    problem is that it seems whenever we do the INSERT, the trigger causes an
    error because it says parts_purchasing table doesn't exist. Naturally... it
    does exist!
    The three standard answers for this type of problem are:

    1. case-folding mismatch (you quoted a mixed-case name when creating the
    table and tried to reference it without quotes, or vice versa);

    2. wrong schema search path;

    3. obsolete cached plan.

    It sounds like you already eliminated #2, and if the entire example is
    shown exactly then it's not #1 either. #3 could be eliminated by
    starting a fresh database session.

    The real question in my mind is how this code could've ever worked at
    all, though. The trigger creates a fresh update event (maybe more than
    one) on its own table every time through, which will fire the same
    trigger again, which means that this absolutely *should* be an infinite
    loop. The only way it isn't is if the "parts_purchasing" table affected
    by its UPDATE isn't the same one the trigger itself is attached to.
    So I'm thinking there probably is a schema search path issue hidden
    in here somewhere, but you've not given us enough information to
    understand what is supposed to be happening.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMay 5, '08 at 6:26a
activeMay 5, '08 at 2:18p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Cody Konior: 1 post Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase