Re: Support logical replication of DDLs

On Tue, 7 Feb 2023 at 17:35, vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Mon, 6 Feb 2023 at 17:02, vignesh C <vignesh21(at)gmail(dot)com> wrote:
> >
> > On Mon, 6 Feb 2023 at 06:47, Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> > >
> > > Here are some comments for patch v63-0002.
> > >
> > > This is a WIP because I have not yet looked at the large file - ddl_deparse.c.
> > >
> > > ======
> > > Commit Message
> > >
> > > 1.
> > > This patch provides JSON blobs representing DDL commands, which can
> > > later be re-processed into plain strings by well-defined sprintf-like
> > > expansion. These JSON objects are intended to allow for machine-editing of
> > > the commands, by replacing certain nodes within the objects.
> > >
> > > ~
> > >
> > > "This patch provides JSON blobs" --> "This patch constructs JSON blobs"
> > >
> > > ======
> > > src/backend/commands/ddl_json.
> >
> > Modified
> >
>
> I found few issues while testing:
> Issue 1: core dump
> Steps to reproduce:
> CREATE TABLE lock_tbl1 (a BIGINT);
> CREATE TABLE lock_tbl1a (a BIGINT);
> CREATE VIEW lock_view1 AS SELECT * FROM lock_tbl1;
> CREATE VIEW lock_view2(a,b) AS SELECT * FROM lock_tbl1, lock_tbl1a;
> CREATE VIEW lock_view3 AS SELECT * from lock_view2;
> CREATE OR REPLACE VIEW lock_view2 AS SELECT * from lock_view3;
>
> Stack trace for the same:
> #5 0x00005573000128ac in ExceptionalCondition
> (conditionName=0x5573001830a3 "IsA(stmt, AlterTableStmt)",
> fileName=0x5573001821de "ddl_deparse.c", lineNumber=2840) at
> assert.c:66
> #6 0x00005572ffa8ddef in deparse_AlterRelation (cmd=0x557301038ec8)
> at ddl_deparse.c:2840
> #7 0x00005572ffaa1895 in deparse_utility_command (cmd=0x557301038ec8,
> verbose_mode=false) at ddl_deparse.c:9820
> #8 0x00005572ffd6daeb in publication_deparse_ddl_command_end
> (fcinfo=0x7fff3eba50b0) at ddltrigger.c:203
> #9 0x00005572ffaa7f87 in EventTriggerInvoke
> (fn_oid_list=0x557301033d80, trigdata=0x7fff3eba5110) at
> event_trigger.c:1047
> #10 0x00005572ffaa7769 in EventTriggerDDLCommandEnd
> (parsetree=0x557300f5b548) at event_trigger.c:719
> #11 0x00005572ffe33a22 in ProcessUtilitySlow (pstate=0x5573010458b8,
> pstmt=0x557300f5b618, queryString=0x557300f5a7c8 "CREATE OR REPLACE
> VIEW lock_view2 AS SELECT * from lock_view3;",
> context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0,
> dest=0x557300f5b8d8, qc=0x7fff3eba5910) at utility.c:1933

Fixed this

> Issue 2: unsupported object type error
> Steps to reproduce:
> create table t1(c1 int);
> ALTER TABLE t1 ADD CONSTRAINT onek_check_constraint CHECK (c1 >= 0);
> ALTER TABLE t1 RENAME CONSTRAINT onek_check_constraint TO
> onek_check_constraint_foo;

Fixed

> Issue 3: object name not found error
> Steps to reproduce:
> create type xfloat4;
> create function xfloat4in(cstring) returns xfloat4 immutable strict
> language internal as 'int4in';
> create function xfloat4out(xfloat4) returns cstring immutable strict
> language internal as 'int4out';
> CREATE TYPE xfloat4 (
> internallength = 16,
> input = xfloat4in,
> output = xfloat4out,
> element = int4,
> category = 'x', -- just to verify the system will take it
> preferred = true -- ditto
> );

Fixed

> Issue 4: unsupported alter table subtype 18
> Steps to reproduce:
> create type comptype as (r float8, i float8);
> create domain dcomptype as comptype;
> alter domain dcomptype add constraint c1 check ((value).r > 0);
> alter type comptype alter attribute r type bigint;

Fixed

> Issue 5: unsupported object type 13
> Steps to reproduce:
> create domain testdomain1 as int constraint unsigned check (value > 0);
> alter domain testdomain1 rename constraint unsigned to unsigned_foo;

Fixed

> Issue 6: invalid ObjTree element type
> Steps to reproduce:
> create extension file_fdw;
> CREATE FOREIGN DATA WRAPPER foo;
> alter foreign data wrapper foo HANDLER file_fdw_handler;
> WARNING: changing the foreign-data wrapper handler can change
> behavior of existing foreign tables
> ERROR: invalid ObjTree element type 1693984336

Fixed

> Issue 7: no owned sequence found
> Steps to reproduce:
> CREATE TABLE itest13 (a int);
> ALTER TABLE itest13 ADD COLUMN b int GENERATED BY DEFAULT AS IDENTITY;

This is slightly tricky, I will fix this in one of the later version

> Issue 8: could not find tuple for constraint 0
> Steps to reproduce:
> create table p1(f1 int);
> create table p1_c1() inherits(p1);
> alter table p1 add constraint inh_check_constraint1 check (f1 > 0);
> alter table p1_c1 add constraint inh_check_constraint1 check (f1 > 0);

Fixed

> Issue 9: unsupported object type 38
> Steps to reproduce:
> CREATE SUBSCRIPTION regress_testsub CONNECTION
> 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect =
> false);
> COMMENT ON SUBSCRIPTION regress_testsub IS 'test subscription';

Fixed

Fixes for this are available in the v68 version attached at [1].
[1] - https://www.postgresql.org/message-id/CALDaNm2ck3c-UDx5QfzJgMDu9rzVa-tj%2BUGrMDLWBJ020_5wvg%40mail.gmail.com

Regards,
Vignesh