Обсуждение: BUG #13444: psql can't recover a pg_dump.
The following bug has been logged on the website:
Bug reference: 13444
Logged by: Sergi Casbas
Email address: sergi.casbas@iris.cat
PostgreSQL version: 9.4.4
Operating system: Debian 8
Description:
When I try to recover a dump made with pg_dump, psql can't recovery some
materialized views because the views use a function that is not created yet
in the dump sql.
Example:
SET check_function_bodies = false;
SET client_min_messages = warning;
SET default_tablespace = '';
SET search_path = public, pg_catalog;
CREATE MATERIALIZED VIEW testmview AS
SELECT publicz.testfunc() AS testfunc
WITH NO DATA;
CREATE SCHEMA publicz;
SET search_path = publicz, pg_catalog;
CREATE FUNCTION testfunc() RETURNS integer
LANGUAGE sql
AS $$SELECT 1;$$;
If we swap the order between public an publicz creation it works.
On Mon, Jun 15, 2015 at 3:55 AM, <sergi.casbas@iris.cat> wrote: > The following bug has been logged on the website: > > Bug reference: 13444 > Logged by: Sergi Casbas > Email address: sergi.casbas@iris.cat > PostgreSQL version: 9.4.4 > Operating system: Debian 8 > Description: > > When I try to recover a dump made with pg_dump, psql can't recovery some > materialized views because the views use a function that is not created yet > in the dump sql. > > Example: > SET check_function_bodies = false; > SET client_min_messages = warning; > SET default_tablespace = ''; > > SET search_path = public, pg_catalog; > CREATE MATERIALIZED VIEW testmview AS > SELECT publicz.testfunc() AS testfunc > WITH NO DATA; > > CREATE SCHEMA publicz; > SET search_path = publicz, pg_catalog; > > CREATE FUNCTION testfunc() RETURNS integer > LANGUAGE sql > AS $$SELECT 1;$$; > > If we swap the order between public an publicz creation it works. > I can't reproduce this. When I create this schema in 9.4.4 and then run 9.4.4's pg_dump on it, it gives them to me in the proper order. Was the original dump created from 9.4.4 or some earlier version? Does there need to be some other objects present to trigger this bad order? Cheers, Jeff
Hi Jeff, This example is made by hand, but reproduces a situation made with pg_dump 9.4.4. on a 9.4.1 server The dump belongs to a project with more than 800 objects, but is a private project and i'm not authorized to send it to you, this is why I create i dummy dump that recreates the situation. 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-15 19:36 GMT+02:00 Jeff Janes <jeff.janes@gmail.com>: > On Mon, Jun 15, 2015 at 3:55 AM, <sergi.casbas@iris.cat> wrote: > >> The following bug has been logged on the website: >> >> Bug reference: 13444 >> Logged by: Sergi Casbas >> Email address: sergi.casbas@iris.cat >> PostgreSQL version: 9.4.4 >> Operating system: Debian 8 >> Description: >> >> When I try to recover a dump made with pg_dump, psql can't recovery some >> materialized views because the views use a function that is not created >> yet >> in the dump sql. >> >> Example: >> SET check_function_bodies =3D false; >> SET client_min_messages =3D warning; >> SET default_tablespace =3D ''; >> >> SET search_path =3D public, pg_catalog; >> CREATE MATERIALIZED VIEW testmview AS >> SELECT publicz.testfunc() AS testfunc >> WITH NO DATA; >> >> CREATE SCHEMA publicz; >> SET search_path =3D publicz, pg_catalog; >> >> CREATE FUNCTION testfunc() RETURNS integer >> LANGUAGE sql >> AS $$SELECT 1;$$; >> >> If we swap the order between public an publicz creation it works. >> > > I can't reproduce this. When I create this schema in 9.4.4 and then run > 9.4.4's pg_dump on it, it gives them to me > in the proper order. Was the original dump created from 9.4.4 or some > earlier version? Does there need to be some other objects present to > trigger this bad order? > > Cheers, > > Jeff > --=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>
On Tue, Jun 16, 2015 at 1:40 AM, Sergi Casbas <sergi.casbas@iris.cat> wrote: > Hi Jeff, > > This example is made by hand, but reproduces a situation made with pg_dump > 9.4.4. on a 9.4.1 server > > The dump belongs to a project with more than 800 objects, but is a private > project and i'm not authorized to send it to you, this is why I create i > dummy dump that recreates the situation. > Hi Sergi, It is understandable that you can't share the whole schema, but unfortunately that doesn't give us very much to work with. The thing that needs to be fixed is how the original dump gets made, not how it replays. So a reproducible test case would have to be something that reproduces a broken dump, rather than something which is itself a broken dump. What happens if you dump the whole schema (without data), manually fix the dump so that it can be restored, restore it to a test instance, and then dump from that instance? Is that dump still broken? If it is still broken, that suggests a bug in pg_dump. Next thing I would try is to delete seemingly-irrelevant objects in a controlled manner, either until the you have something small enough you can share it, or until the problem goes away. The last object deleted before the problem went away must be triggering the bug. If it not still broken after a dump-manual repair-reload cycle, then that sounds more like some form of corruption in your existing database, rather than a bug in the software. Cheers, Jeff
This sounds like it might be a duplicate of bug #12465: http://www.postgresql.org/message-id/20150108212429.11502.18220@wrigleys.postgresql.org .m
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
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>