Postgresql functions execution process

Question

I have a procedural function (written in pl/python) which queries table A, does some calculations and then returns a set. I use this function as query for my materialized view B. Everything works perfectly except that when I want to restore my dump, I get the following error:

DETAIL: spiexceptions.UndefinedTable: relation "A" does not exist.

The line which raises this error is the last line of my sql dump:

REFRESH MATERIALIZED VIEW B;

I know that I can ignore this error and refresh my materialized view after restoration process, but I want to know why this happens? Is it because this function runs in another transaction which doesn't know anything about current restoration process? And what can I do to prevent this error?

Are you sure it's called A and not a? Your view is called b yet you refer to it as B. ("I use this function as query for my materialized view B.") but REFRESH MATERIALIZED VIEW B refreshes a view called b not B. In case it's not clear, quoting an identifier makes it case sensitive, not quoting results in the identifier being all lowercase regardless of what you wrote. As you're not quoting B it will be b. Since it didn't say that b doesn't exist, the b view exists, but A doesn't. Since your view is lowercase, I suspect your table is too. — 404
– 404, Commented Nov 14, 2019 at 15:20
The other possibility is that you forgot to schema qualify the table, and search_path is different. — Laurenz Albe
– Laurenz Albe, Commented Nov 14, 2019 at 16:18
No, it isn't relevent to table names. As I said, it works when I refresh it manually. — Mehdi Pourfar
– Mehdi Pourfar, Commented Nov 14, 2019 at 16:51
My table is not schema qualified. I only have public schema and as I said, it works when I run refresh command manually. — Mehdi Pourfar
– Mehdi Pourfar, Commented Nov 14, 2019 at 16:53

jjanes · Accepted Answer · 2019-11-14 21:34:54Z

1

For security reasons, pg_dump (or pg_restore) emits a command which empties the search_path, so when you restore the process gets run with an empty search path. But it does not edit the text body of your function at all but emits it as-is, so it can't alter it to specify the fully qualified name of the table. So the function can't find the table when run inside the process doing the restore.

You can fully qualify the table name in the function, or you can define the function with SET search_path = public. Or you can edit the dump file to remove the part that clears the search_path, if you are not concerned about the security implications.

answered Nov 14, 2019 at 21:34

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mehdi Pourfar Over a year ago

Thanks a lot. That was the problem.

Collectives™ on Stack Overflow

Postgresql functions execution process

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related