Re: Can't restore view with pg_restore

[ 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