Thread: BUG #12465: Materialized view dump restoration issue
The following bug has been logged on the website:
Bug reference: 12465
Logged by: Jeff Casavant
Email address: jeff.casavant@gmail.com
PostgreSQL version: 9.4.0
Operating system: Ubuntu 14.04.1
Description:
If
- two functions A and B exist in the public schema
- A calls B, and
- a materialized view calls A
then the dump produced by pg_dump or pg_dumpall will throw the following
error when passed to psql -f:
psql:dump.sql:7730: ERROR: function b() does not exist
LINE 1: SELECT B()
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
QUERY: SELECT B()
CONTEXT: SQL function "b" during inlining
Code to reproduce:
create function b() returns int as $$ select 1 $$ language sql;
create function a() returns int as $$ select b() $$ language sql;
create schema qwr;
create materialized view qwr.c as select a();
Then run pg_dump | psql .
Workaround:
Add public to all calls to 'SET search_path' in the dump.
jeff.casavant@gmail.com writes:
> Code to reproduce:
> create function b() returns int as $$ select 1 $$ language sql;
> create function a() returns int as $$ select b() $$ language sql;
> create schema qwr;
> create materialized view qwr.c as select a();
This is not a pg_dump bug, this is a broken definition of function a().
That function will fail in any context where the caller changes
search_path, not only pg_dump. You can perhaps get away without that
in a single-schema database, but not with multiple schemas.
You could fix it by schema-qualifying b in the text of a,
or by adding a "SET schema_path" clause to a.
regards, tom lane
On 2015-01-09 21:42, Tom Lane wrote: > jeff.casavant@gmail.com writes: >> Code to reproduce: > >> create function b() returns int as $$ select 1 $$ language sql; >> create function a() returns int as $$ select b() $$ language sql; >> create schema qwr; >> create materialized view qwr.c as select a(); > > This is not a pg_dump bug, this is a broken definition of function a(). > That function will fail in any context where the caller changes > search_path, not only pg_dump. You can perhaps get away without that > in a single-schema database, but not with multiple schemas. AFAIK there isn't a way to write inlineable SQL functions in relocatable extensions in that way, since you don't know which schema they end up installed in. The original test case comes from PostGIS. But I think the bigger problem is that naively thinking it shouldn't be this easy to create unrestorable databases. But perhaps I'm being overly naive. .marko
Marko Tiikkaja <marko@joh.to> writes:
> On 2015-01-09 21:42, Tom Lane wrote:
>> This is not a pg_dump bug, this is a broken definition of function a().
>> That function will fail in any context where the caller changes
>> search_path, not only pg_dump. You can perhaps get away without that
>> in a single-schema database, but not with multiple schemas.
> AFAIK there isn't a way to write inlineable SQL functions in relocatable
> extensions in that way, since you don't know which schema they end up
> installed in. The original test case comes from PostGIS.
You can do it for relocatable-at-install-time extensions, as suggested in
the manual:
CREATE FUNCTION ... SET search_path = @extschema@ ...
> But I think the bigger problem is that naively thinking it shouldn't be
> this easy to create unrestorable databases. But perhaps I'm being
> overly naive.
Well, if you know how to inform pg_dump what random assumptions about
search_path exist in the functions invoked by a matview (or expression
index, or some other cases), let me know.
regards, tom lane
On 2015-01-09 22:38, Tom Lane wrote: > Marko Tiikkaja <marko@joh.to> writes: >> On 2015-01-09 21:42, Tom Lane wrote: >>> This is not a pg_dump bug, this is a broken definition of function a(). >>> That function will fail in any context where the caller changes >>> search_path, not only pg_dump. You can perhaps get away without that >>> in a single-schema database, but not with multiple schemas. > >> AFAIK there isn't a way to write inlineable SQL functions in relocatable >> extensions in that way, since you don't know which schema they end up >> installed in. The original test case comes from PostGIS. > > You can do it for relocatable-at-install-time extensions, as suggested in > the manual: > > CREATE FUNCTION ... SET search_path = @extschema@ ... Yup, and now it's not inlineable anymore. >> But I think the bigger problem is that naively thinking it shouldn't be >> this easy to create unrestorable databases. But perhaps I'm being >> overly naive. > > Well, if you know how to inform pg_dump what random assumptions about > search_path exist in the functions invoked by a matview (or expression > index, or some other cases), let me know. Hmm.. So I guess this is pretty much impossible as long as we insist on the materialized views being initialized at restore time. (Though I notice that even WITH NO DATA does not work.) .marko
I see what you mean. I ran across this issue with ST_AsLatLonText(geometry) in PostGIS (which calls ST_AsLatLonText(geometry, text)). I'll pass this bug on to them. Jeff On Fri, Jan 9, 2015 at 3:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > jeff.casavant@gmail.com writes: > > Code to reproduce: > > > create function b() returns int as $$ select 1 $$ language sql; > > create function a() returns int as $$ select b() $$ language sql; > > create schema qwr; > > create materialized view qwr.c as select a(); > > This is not a pg_dump bug, this is a broken definition of function a(). > That function will fail in any context where the caller changes > search_path, not only pg_dump. You can perhaps get away without that > in a single-schema database, but not with multiple schemas. > > You could fix it by schema-qualifying b in the text of a, > or by adding a "SET schema_path" clause to a. > > regards, tom lane >
Hi,
I'm having a strange problem using prepared statements. I am using them
in an effort to eliminate large planning times.
I will try and break the problem down : -
1) A query in my database takes over half a second to plan, but often
only 5ms to 80ms to run depending on the parameter: -
test=# explain analyze select * from myview where site_code = 'M3374';
..
Planning time: 606.590 ms
Execution time: 83.735 ms
(203 rows)
test=# explain analyze select * from myview where site_code = 'M3373';
<=== NOTE different parameter
..
Planning time: 624.158 ms
Execution time: 5.275 ms
(213 rows)
2) To stop my application feeling sluggish, I decided to try using a
prepared statement.
test=# prepare myprep as select * from myview where site_code = $1;
PREPARE
3) Dissapointingly the first few runs still have the planning pause, but
gives the same execution result as expected:
test=# explain analyze execute myprep('M3374');
..
Execution time: 83.596 ms
(202 rows)
4) Probably the first 5 or 6 runs are like this; they have the same
planning pause of over half a second, even though I've "prepared" it...
5) Then after that I suddenly get amazing results, and even the
execution speed drops substantially for some parameters: -
test=# explain analyze execute myprep('M3374');
..
Execution time: 40.273 ms
(208 rows)
Half the execution speed!
So far so good...
6) However, if I start again but instead of executing myprep('M3374')
first, I instead execute myprep('M3373') first, the plan seems to never
get cached.
DEALLOCATE myprep;
test=# prepare myprep as select * from myview where site_code = $1;
PREPARE
test=# explain analyze select * from myview where site_code = 'M3373';
Half second pause for planning, 5ms exec time.
Repeat the query 5 times.. 10 times.. 20 times.. never changes, never
improves. Sometimes on the 5th or 6th the planning actually seems to
take over 1 second, then go back to normal.
Again, it is STILL only 5ms execution time (only returns 1 row), but
there is that annoying planning pause even though it is prepared, and
ran many times.. it is not caching the plan.
7) To re-iterate, once it is cached (using the 'M3374' parameter) it is
*very* fast with all parameters, but obviously I cannot rely on this in
a user environment.
I hope the problem makes sense.
Thanks in advance,
Mark.
--
PS please ignore the PQLIB: part of the subject line, that was a mistake. Thanks, Mark. --