Re: [BUGS] Can't restore view with pg_restore - Mailing list pgsql-bugs
| From | Flo |
|---|---|
| Subject | Re: [BUGS] Can't restore view with pg_restore |
| Date | |
| Msg-id | CAPW7RPkQLb_4VmwQYqd6oVVJNYzxqw0wwWzdGP8yqhEUe_91Bg@mail.gmail.com Whole thread Raw |
| In response to | Re: [BUGS] Can't restore view with pg_restore (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: [BUGS] Can't restore view with pg_restore
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-bugs |
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;
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 |
---------------------------------+----------
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@sss.pgh.pa.us>:
[ please keep the list cc'd for the archives' sake ]
Flo <fluancefg@gmail.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
pgsql-bugs by date: