Re: Support logical replication of DDLs

On Tue, 29 Nov 2022 at 17:51, li jie <ggysxcq(at)gmail(dot)com> wrote:
>
> I will continue to give feedback for this patch.
>
> 1. LIKE STORAGE
> ```
> CREATE TABLE ctlt (a text, c text);
> ALTER TABLE ctlt ALTER COLUMN c SET STORAGE EXTERNAL;
> CREATE TABLE ctlt_storage (LIKE ctlt INCLUDING STORAGE);
> ```
>
> postgres=# \d+ ctlt_storage
>
> Table "public.ctlt_storage"
>
> Column | Type | Collation | Nullable | Default | Storage |
> Compression | Stats target | Description
>
> --------+------+-----------+----------+---------+----------+-------------+--------------+-------------
>
> a | text | | | | extended |
> | |
>
> c | text | | | | extended |
> | |
>
>
> It can be seen that the storage attribute in column C of table
> ctlt_storage is not replicated.
>
> After the CREATE TABLE LIKE statement is converted,
> the LIKE STORAGE attribute is lost because it is difficult to display
> it in the CREATE TABLE syntax.
> Maybe we need to add a statement to it, like 'ALTER TABLE ctlt_storage
> ALTER COLUMN c SET STORAGE EXTERNAL;'.

This is fixed with the attached patch.

> 2. Reference subcommand be dropped.
> ```
> create table another (f1 int, f2 text, f3 text);
>
> alter table another
> alter f1 type text using f2 || ' and ' || f3 || ' more',
> alter f2 type bigint using f1 * 10,
> drop column f3;
> ```
>
> The following error occurs downstream:
> ERROR: column "?dropped?column?" does not exist at character 206
> STATEMENT: ALTER TABLE public.another DROP COLUMN f3 , ALTER COLUMN
> f1 SET DATA TYPE pg_catalog.text COLLATE pg_catalog."default" USING
> (((f2 OPERATOR(pg_catalog.||) ' and '::pg_catalog.text)
> OPERATOR(pg_catalog.||) "?dropped?column?") OPERATOR(pg_catalog.||) '
> more'::pg_catalog.text), ALTER COLUMN f2 SET DATA TYPE pg_catalog.int8
> USING (f1 OPERATOR(pg_catalog.*) 10)
>
> Obviously, column f3 has been deleted and its name no longer exists.
> Maybe we need to keep it and save it in advance like a drop object.
> However, ATLER TABLE is complex, and this problem also occurs in
> other similar scenarios.

This is slightly tricky, we will fix this in the next version.

Also a couple of other issues reported are fixed in this patch:
3. ALTER TABLE SET STATISTICS
CREATE TABLE test_stat (a int);
ALTER TABLE test_stat ALTER a SET STATISTICS -1;

4. json null string coredump
CREATE OR REPLACE FUNCTION test_ddl_deparse_full()
RETURNS event_trigger LANGUAGE plpgsql AS
$$
DECLARE
r record;
deparsed_json text;
BEGIN
FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
deparsed_json = ddl_deparse_to_json(r.command);
RAISE NOTICE 'deparsed json: %', deparsed_json;
RAISE NOTICE 're-formed command: %', ddl_deparse_expand_command(deparsed_json);
END LOOP;
END;
$$;

CREATE EVENT TRIGGER test_ddl_deparse_full
ON ddl_command_end EXECUTE PROCEDURE test_ddl_deparse_full();

CREATE SCHEMA AUTHORIZATION postgres;

The attached v41 patch has the fixes for the above 3 issues.

Regards,
Vignesh