Re: Support logical replication of DDLs

On Fri, Mar 10, 2023 at 5:41 PM Runqi Tian <runqidev(at)gmail(dot)com> wrote:
>
> Hi,
>
> I'm working on test coverage for the DDL deparser using the
> test_ddl_deparse_regress test module. We’re focusing on table commands
> as the first step in providing test coverage as proposed in [1], I
> have constructed unit test cases by testing each clause in the syntax
> and complex test cases by combining multiple clauses together. I found
> about 20 test failures and they are marked TOFIX in the test script,

Thanks for working on the test coverage for CREATE and ALTER TABLE.
I've made fixes for some of the failures in the v79 patch set (0002,
0003 and 0004 are updated). The changes includes:
1. Fixed a syntax error caused by ON COMMIT clause placement in
deparse_CreateStmt.
2. Fixed deparse_Seq_As and start using it in deparse_CreateSeqStmt,
this issue is also reported in [1].
3. Fixed a bug in append_not_present: the 'present: false' element
can't be omitted even in non-verbose mode. It will cause syntax error
on reformed command if 'present: false' element is missing but the fmt
string indicates the corresponding object must be present.
4. Replaced if_not_exists with if_exists in deparse of
AT_DropConstraint and AT_DropColumn.
5. Added missing CASCADE clause for AT_DropConstraint deparse.
6. Enabled the fixed test cases.

There are still the following failing tests in create_table.sql and
alter_table.sql, I'll continue to fix these:

1. Deparse of NOT NULL isn't working in deparse_ColumnDef_typed
CREATE TABLE part9_partition_with_options_constraints
PARTITION OF part9_parent_table_range (
id PRIMARY KEY,
name WITH OPTIONS NOT NULL,
CHECK (height > 0)
)
FOR VALUES FROM (MINVALUE) TO (2);

2. ERROR: cannot drop index test_drop_column_name_key because
constraint test_drop_column_name_key on table test_drop_column
requires it
CREATE TABLE test_drop_column(
LIKE orders,
UNIQUE (id),
UNIQUE (name)
);
CREATE TABLE foreign_table(
id int REFERENCES test_drop_column (id),
name varchar REFERENCES test_drop_column (name)
);
ALTER TABLE test_drop_column DROP IF EXISTS name CASCADE;

3. Partitioned table deparsed but not replicated:
CREATE TABLE test_detach_partition(
LIKE orders
) PARTITION BY RANGE (id);

4. The old index name has already been replaced with the new one for
deparse of ALTER TABLE ... ADD CONSTRAINT ... USING INDEX, need to
save the original index name when collecting the command in the event
trigger.
CREATE TABLE test_add_constraint_using_index(
id2 int,
);
CREATE UNIQUE INDEX test_add_constraint_used_index2 ON
test_add_constraint_using_index (id2);
ALTER TABLE test_add_constraint_using_index ADD CONSTRAINT
primary_constraint_using_index
PRIMARY KEY USING INDEX test_add_constraint_used_index2;

Regards,
Zane
[1] https://www.postgresql.org/message-id/OS3PR01MB6275FE40496DA47C0A3369289EB69%40OS3PR01MB6275.jpnprd01.prod.outlook.com