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.casbas@iris.cat <sergi.casbas@iris.cat>Phone Direct : +34 93
554 25 05 <%2B34-93-554-25-05>*

2015-06-17 0:08 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:

> Marko Tiikkaja <marko@joh.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
>

--=20


Web: www.iris.cat; Twitter: www.=20
<http://t.signauxcinq.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XZsdDZ=
cWW4WJ24g63RS0FW4XyMQH56dPj7dpZnKn02?t=3Dhttp%3A%2F%2Fwww.%2F&si=3D50612140=
55432192&pi=3D3b9b88cc-a5b6-420c-818b-b7d44bc26dc7>
twitter.com/iris_rd=20
<http://t.signauxcinq.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XZsdDZ=
cWW4WJ24g63RS0FW4XyMQH56dPj7dpZnKn02?t=3Dhttp%3A%2F%2Ftwitter.com%2Firis_rd=
&si=3D5061214055432192&pi=3D3b9b88cc-a5b6-420c-818b-b7d44bc26dc7>
=20

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

--=20


--=20


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

Phone office: +34 93 554 25 00;=20


NexusUCD, University College Dublin, Blocks 9 & 10 Belfield Office Park Bel=
field,=20
Dublin (Ireland)

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


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