Re: Support logical replication of DDLs

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

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;

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
);

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;

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;

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

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;

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);

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';

Regards,
Vignesh