Re: pg_restore --clean failing due to dependancies

Le 16/11/2016 à 20:05, Tom Lane a écrit :
> Arnaud Lesauvage <arnaud.lesauvage@codata.eu> writes:
>> [ dump from problematic database ]
>
> OK, thanks for the test case.  The problem here is that pg_dump is setting
> up a circular dependency that it doesn't know how to break correctly.
> You've got a couple of views that are implicitly dependent on the primary
> keys of their underlying tables, because they use a GROUP BY the primary
> key without also grouping by other columns they use post-grouping.  That
> means that pg_dump has to dump the view definition after the creation of
> the primary key, but it also needs to put the view out sooner than that
> for other reasons.  It manages to deal with that okay in the default mode,
> but when you have --clean in there, it ends up generating an illegal DROP
> RULE command.

All right, at least I'm glad that I did not miss something obvious.


> This is something we ought to fix, but it's not exactly trivial to do.
> In the meantime I'd suggest changing the view definitions to not assume
> that the underlying tables have primary keys.  It looks like in
> view_temp_export_geo_recherche_extra_sites_projets you need to add
> c.official_language_id to the GROUP BY, and similarly in
> view_temp_export_geo_recherche_offtrad_sites.

Thanks for the tip ! I'll try this ASAP.
I never "GROUP BY" primary keys only, so I can consider this as an error
that needs fixing. I did not even know that this was valid SQL to be honest.

Thanks a lot for your help !

Regards
--
Arnaud