On Mon, 16 Dec 2024 at 17:21, Artur Zakirov <zaartur@gmail.com> wrote:
>
> On Mon, 16 Dec 2024 at 12:05, vignesh C <vignesh21@gmail.com> wrote:
> > I prefer the other approach to remove both the checks in
> > getPublicationTables() and getPublicationNamespaces() which also makes
> > it consistent with the other case that Amit mentioned at [1].
>
> If I understand your suggestion correctly I think this will break the
> "--exclude-schema" option of pg_dump. That change will dump all
> mappings between publications and schemas for publications which are
> dumped.
>
> That solves the issue with special schemas, but restore will fail if
> some schemas were explicitly excluded. pg_dump will include in the
> dump ALTER PUBLICATION <pub> ADD TABLES IN SCHEMA <schema> even for
> those schemas which are not created during restore.
This is already the case in the existing implementation, so users
should not be surprised by the proposed change.
This can be reproduced with the following steps:
-- Create schema and user defined function in schema sch2
create schema sch2;
CREATE FUNCTION sch2.add1(integer, integer)
RETURNS integer
LANGUAGE sql IMMUTABLE STRICT
AS $_$select $1 + $2;$_$;
-- Create a view which references user defined function of a different schema
create schema sch1;
CREATE TABLE sch1.t1 (c1 integer, c2 integer);
CREATE VIEW sch1.v1 AS SELECT c1 FROM sch1.t1 WHERE (sch2.add1(c1, c2) >= 10);
-- Exclude schema sch2 which has the user defined function while dumping
./pg_dump -d postgres -N sch2
You will notice that the schema sch2 and the user defined function in
schema sch2 will not be dumped.
Regards,
Vignesh
