Postgres Incompatibility

[Avi Weinberg <AviW@xxxxxxxxx>]

 

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.