Re: Can't restore view with pg_restore

I was not able to find any explicit trigger creation related to
bmv_visits_list, but I have found where the issue comes from.

SELECT tgname, tgfoid FROM pg_trigger WHERE tgrelid =
'wsikentoo.bmv_visits_list'::regclass;

tgname | tgfoid
---------------------------------+----------
queue_truncate_trigger_11078295 | 11077522

SELECT pg_get_functiondef(11077522);

pg_get_functiondef
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION pglogical.queue_truncate()
RETURNS trigger
LANGUAGE c
AS '$libdir/pglogical', $function$pglogical_queue_truncate$function$

SELECT * from pglogical.tables;

wsikentoo | bmv_visits_list |

So basicaly, the extension pglogical adds the table to its list of known
tables and set a trigger to it.
What I don't know and don't understand is why it doesn't happen with the
other views.
Anyway, there's a problem with the extension because this view is not part
of the replication set before the dump, so removing the pglogical schema
from the dump solves the problem.

Thank you for your explanations and your support.

2017-05-29 20:53 GMT+02:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> [ please keep the list cc'd for the archives' sake ]
>
> Flo <fluancefg(at)gmail(dot)com> writes:
> > Very interesting point. I have done a pg_dump -s and I've found in the
> text
> > file that all the views are dumped with a CREATE VIEW command, but not
> that
> > one.
>
> This doesn't seem like it could be all of the commands affecting
> bmv_visits_list, because nothing you've shown here could result in
> creation of a trigger on bmv_visits_list before the rule is created.
>
> > CREATE RULE "_RETURN" AS
> > ON SELECT TO bmv_visits_list DO INSTEAD SELECT patient.firstname,
> > patient.lastname,
> > patient.birthdate,
> > company.code,
> > jsonb_agg(jsonb_build_object('nb', v.nb, 'admissiondt', v.admitdt,
> > 'patientroom', v.patientroom)) AS visits
> > ...
> > GROUP BY patient.id, company.code;
>
> > Does it have anything to do with the jsonb data type, because it is the
> > only view that uses that ?
>
> No. I think the reason the view is getting dumped like this is the
> incomplete GROUP BY spec. To make that SELECT legal, patient.id must
> be a primary key (or at least unique), which means that the pkey or
> unique index must be created before we can issue the command. pg_dump
> doesn't want to build indexes until after the data is loaded, so it
> has to split creation of this view into creation of a shell view (before
> data load) and replacement of the shell with the real view definition
> (after data load and index creation).
>
> It might help to think of the CREATE TABLE as being CREATE VIEW with
> a dummy view definition and then the CREATE RULE as being like CREATE
> OR REPLACE VIEW with the real definition. The fact that these commands
> are spelled the way they are is a historical accident, which we might
> or might not get around to fixing someday.
>
> This gets us no closer to understanding your failure though. Please
> take a closer look for other commands mentioning bmv_visits_list before
> the CREATE RULE.
>
> regards, tom lane
>