Thread: [BUGS] Can't restore view with pg_restore
Hello,
I'm writing here, however I'm not sure if it's a bug or if there's something I don't understand correctly.ERROR: could not convert table "bmv_visits_list" to a view because it has triggers
STATEMENT: 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('
FROM (((ehealth.visit v
JOIN ehealth.company ON ((v.company_id = company.id)))
JOIN ehealth.m_rd_opa_pcl pcl ON ((((v.patientclass)::text = (pcl.code)::text) AND (v.company_id = pcl.company_id))))
JOIN ehealth.patient ON ((patient.id = v.patient_id)))
WHERE (((v.patientclass)::text <> 'DEVE'::text) AND ((pcl.hl7code)::text = 'I'::text) AND ((v.admitstatus)::text = 'admitted'::text) AND ((v.dischargedt IS NULL) OR (v.dischargedt > ('now'::text)::timestamp without time zone)))
GROUP BY patient.id, company.code;
Florian Gossin <fluancefg@gmail.com> writes:
> I'm doing a dump in directory format of database with several schemas using
> pg_dump and when I try to restore it using pg_restore, I get the following
> error:
> ERROR: could not convert table "bmv_visits_list" to a view because it has
> triggers
> STATEMENT: CREATE RULE "_RETURN" AS
> ON SELECT TO bmv_visits_list DO INSTEAD SELECT patient.firstname,
Hm, seems like pg_dump is trying to dump the various bits of the view
in the wrong order. But since we haven't seen reports of this before,
there must be something odd about your schema to trigger it. Can you do
a plain "pg_dump -s" and show us the parts relating to this view and its
underlying tables?
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Wed, May 24, 2017 at 11:39 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Florian Gossin <fluancefg@gmail.com> writes: >> I'm doing a dump in directory format of database with several schemas using >> pg_dump and when I try to restore it using pg_restore, I get the following >> error: It may help to now the version of pg_dump you are using and what's the server version you are working on. -- Michael -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[ 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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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
Flo <fluancefg@gmail.com> writes:
> I was not able to find any explicit trigger creation related to
> bmv_visits_list, but I have found where the issue comes from.
> ...
> So basicaly, the extension pglogical adds the table to its list of known
> tables and set a trigger to it.
Hm, I don't know much about pglogical. Does it automatically add a
trigger to every new table without user intervention? If so, that's
kinda broken.
> What I don't know and don't understand is why it doesn't happen with the
> other views.
Probably because they don't have to be split into two steps like this one
is. I'm guessing that pglogical has a hook installed that sees the CREATE
TABLE, thinks this is going to be a regular table, and gloms onto it
before it's converted to a view.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Hm, I don't know much about pglogical. Does it automatically add a
trigger to every new table without user intervention? If so, that's
kinda broken.
Honestly, I'm very surprised about that behaviour. I made few tests, and yes, it seems that as soon as a new table is created using CREATE TABLE or CREATE TABLE AS, there's a trigger automatically added.
I'm going to open a bug report on their side.
Regards,
Florian