1

I have written function that I registered in a trigger that ensure referential consistency in non-normalized data (like we store agency_id + branch_id and I ensure that a referenced branch is inside a reference agency).

The function has some selects:

SELECT b.agency_id INTO agency_id FROM branch AS b WHERE b.id = NEW.branch_id;
IF NEW.agency_id <> agency_id THEN
  RAISE EXCEPTION 'privilege.agency_id (%) mismatches privilege.branch_id => agency.id (%)', NEW.agency_id, agency_id;
END IF;

All worked fine for several years until I run pg_dump. On import by psql ... -f dump/dict-local.sql I see errors:

ERROR:  42P01: relation "branch" does not exist
LINE 1: SELECT b.agency_id                FROM branch AS b WHERE b.i...
                                               ^
QUERY:  SELECT b.agency_id                FROM branch AS b WHERE b.id = NEW.branch_id
CONTEXT:  PL/pgSQL function SCHEMA.privilege_orgunit_ck() line 8 at SQL statement

After I added schema prefix: SCHEMA.branch import started to work. I don't like to add schema prefix to tables referenced in the function. What can I do to avoid the error?

Is it related to search_path? I seee in the very beginning of pg_dump output:

SELECT pg_catalog.set_config('search_path', '', false);

UPDATE There is TG_TABLE_SCHEMA mentioned in the docs. I'm not sure how to use it, using it as prefix TG_TABLE_SCHEMA.branch produces the same error:

psql:dump/dict-local.sql:4368: ERROR:  42P01: relation "tg_table_schema.branch" does not exist
LINE 1: SELECT b.agency_id                FROM TG_TABLE_SCHEMA.branc...

If I need to rewrite select into:

EXECUTE format('SELECT ... INTO ... FROM %I.branch', TG_TABLE_SCHEMA);

it looks awful...

1
  • I don't like to add schema prefix to tables referenced in the function. Why? It's typically the cleanest, safest, fastest solution. After I added schema prefix: SCHEMA.branch import started to work. You mean the restore went through? Or did you see errors in operation? Also, please always declare your version of Postgres. Aug 5, 2021 at 1:29

1 Answer 1

2

I come up with:

PERFORM set_config('search_path', TG_TABLE_SCHEMA, true);

The last argument true ensures a change is only for the current transaction:

https://www.postgresql.org/docs/11/functions-admin.html

If is_local is true, the new value will only apply to the current transaction. If you want the new value to apply for the current session, use false instead.

I don't know the scope trigger executes, so I'm not sure in the solution.

2
  • 1
    As your quote says: this changes the search_path for the scope of the whole transaction. Since you don't know the scope trigger executes and you don't like to add schema prefix to tables, this looks like a loaded footgun to me. Changing the search_path dynamically is generally expensive as it causes a context switch and voids all saved query plans. I would rather not go there. If you must, then save the current search_path and restore it at the end of the trigger function. Aug 5, 2021 at 1:34
  • @ErwinBrandstetter I ended up with embedding schema name. It is unfortunate that pg_dump clears search_path. Actually I think I can pre-process pg_dump output removing a line that clears 'search_path'. I agree that altering search_path is expensive. It is the cleanest solution as problem only with that utility which I used once a year.
    – gavenkoa
    Aug 5, 2021 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.