Re: BUG #13444: psql can't recover a pg_dump.

HI!

I discovered (and solve) the problem.

Materialized View schema1.MV calls function public.F and function public.F
uses table public.T

Inside the function we made something like:
.....
SELECT * FROM T;
.....

Then when the pgsql is recreating the schema can not acces T because is on
schema1.

The solution: change public.F code using complete name:
SELECT * FROM public.T;

Sergi Casbas
*Scrum Master*

*Email: sergi(dot)casbas(at)iris(dot)cat <sergi(dot)casbas(at)iris(dot)cat>Phone Direct : +34 93
554 25 05 <%2B34-93-554-25-05>*

2015-06-17 0:08 GMT+02:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Marko Tiikkaja <marko(at)joh(dot)to> writes:
> > This sounds like it might be a duplicate of bug #12465:
> >
> http://www.postgresql.org/message-id/20150108212429.11502.18220@wrigleys.postgresql.org
>
> It's hard to tell on the basis of the supplied info what exactly is the
> OP's specific problem. However, although I rejected #12465 as not-a-bug,
> there definitely are issues with functions in materialized views, because
> pg_dump lacks enough information to understand what dependencies might be
> implied by the bodies of the functions. We've also seen reports of cases
> where it nominally worked, but took forever, because execution of the
> matview queries was too slow for lack of not-yet-restored indexes or for
> lack of planner statistics.
>
> A simple response would be to delay all the REFRESH MATVIEW commands to
> the end of the dump script, but (1) that doesn't fix the lack-of-ANALYZE
> problem, and (2) it plays hob with the notion of pre-data/data/post-data
> section boundaries, unless you're willing to reclassify the REFRESH
> commands as not being "data".
>
> regards, tom lane
>

--

Web: www.iris.cat; Twitter: www.
<http://t.signauxcinq.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XZsdDZcWW4WJ24g63RS0FW4XyMQH56dPj7dpZnKn02?t=http%3A%2F%2Fwww.%2F&si=5061214055432192&pi=3b9b88cc-a5b6-420c-818b-b7d44bc26dc7>
twitter.com/iris_rd
<http://t.signauxcinq.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XZsdDZcWW4WJ24g63RS0FW4XyMQH56dPj7dpZnKn02?t=http%3A%2F%2Ftwitter.com%2Firis_rd&si=5061214055432192&pi=3b9b88cc-a5b6-420c-818b-b7d44bc26dc7>

LinkedIn: www.linkedin.com/company/iris-s.l.

--

--

Parc Mediterrani de la Tecnologia, Avda. Carl Friedrich Gauss 11, 08860
Castelldefels, Barcelona (Spain)

Phone office: +34 93 554 25 00;

NexusUCD, University College Dublin, Blocks 9 & 10 Belfield Office Park Belfield,
Dublin (Ireland)

Phone office: +353 (0)1 716 5791;

Privacy Policy:
http://www.iris.cat/wp-content/uploads/2014/01/PoliticaDePrivacidad.pdf
<http://t.signauxcinq.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XZsdDZcWW4WJ24g63RS0FW4XyMQH56dPj7dpZnKn02?t=http%3A%2F%2Fwww.iris.cat%2Fwp-content%2Fuploads%2F2014%2F01%2FPoliticaDePrivacidad.pdf&si=5061214055432192&pi=3b9b88cc-a5b6-420c-818b-b7d44bc26dc7>