From: | Avi Weinberg <AviW(at)gilat(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Postgres Incompatibility |
Date: | 2023-05-17 06:05:52 |
Message-ID: | DB9PR07MB71804E6D1CA3449412DBA09FCB7E9@DB9PR07MB7180.eurprd07.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Experts
I noticed this problem when creating a backup on Postgres 13 and restoring it on Postgres 15.
CREATE FUNCTION sync.show_pg_subscription1() RETURNS SETOF pg_subscription
LANGUAGE sql
AS $$
SELECT * from pg_subscription;
$$;
The Postgres backup creates the view syntax with " FROM sync.show_pg_subscription1() show_pg_subscription1(oid, subdbid, subname, subowner, subenabled, subconninfo, subslotname, subsynccommit, subpublications);" The original syntax just had FROM sync.show_pg_subscription1() .
CREATE OR REPLACE VIEW sync.pg_subscription_view1
AS
SELECT show_pg_subscription1.oid,
show_pg_subscription1.subdbid,
show_pg_subscription1.subname,
show_pg_subscription1.subowner,
show_pg_subscription1.subenabled,
show_pg_subscription1.subconninfo,
show_pg_subscription1.subslotname,
show_pg_subscription1.subsynccommit,
show_pg_subscription1.subpublications
FROM sync.show_pg_subscription1() show_pg_subscription1(oid, subdbid, subname, subowner, subenabled, subconninfo, subslotname, subsynccommit, subpublications);
We get an error:
ERROR: column reference "subconninfo" is ambiguous LINE 8: show_pg_subscription1.subconninfo,
If we remove the part generated by Postgres backup " show_pg_subscription1(oid, subdbid, subname, subowner, subenabled, subconninfo, subslotname, subsynccommit, subpublications);" it works.
What can be done so backups will be able to restore correctly? I have a not so elegant solution, but I hope to hear a better solution than creating additional view:
create view abc as
SELECT
oid,
subdbid,
subname,
subowner,
subenabled,
subconninfo,
subslotname,
subsynccommit,
subpublications
from pg_subscription p;
CREATE FUNCTION sync.show_pg_subscription1() RETURNS SETOF abc
LANGUAGE sql
AS $$
SELECT * from abc;
$$;
SELECT show_pg_subscription1.oid,
show_pg_subscription1.subdbid,
show_pg_subscription1.subname,
show_pg_subscription1.subowner,
show_pg_subscription1.subenabled,
show_pg_subscription1.subconninfo,
show_pg_subscription1.subslotname,
show_pg_subscription1.subsynccommit,
show_pg_subscription1.subpublications
FROM sync.show_pg_subscription1() show_pg_subscription1(oid, subdbid, subname, subowner, subenabled, subconninfo, subslotname, subsynccommit, subpublications);
This works also with the additional part generated by Postgres backup
Thanks!
IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
From | Date | Subject | |
---|---|---|---|
Next Message | Shubham Dhama | 2023-05-17 06:06:37 | Earliest streamed message from replication slot has LSN less than the `consistent_point` |
Previous Message | Andres Freund | 2023-05-16 23:39:50 | Re: "PANIC: could not open critical system index 2662" - twice |