Re: Support logical replication of DDLs

From: li jie <ggysxcq(at)gmail(dot)com>
To: Ajin Cherian <itsajin(at)gmail(dot)com>
Cc: rajesh singarapu <rajesh(dot)rs0541(at)gmail(dot)com>, Zheng Li <zhengli10(at)gmail(dot)com>, vignesh C <vignesh21(at)gmail(dot)com>, Peter Smith <smithpb2250(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, "houzj(dot)fnst(at)fujitsu(dot)com" <houzj(dot)fnst(at)fujitsu(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Japin Li <japinli(at)hotmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Support logical replication of DDLs
Date: 2022-11-29 12:21:07
Message-ID: CAGfChW4zpk9LaMav+nLazgjTLTBfD-XXr-M1H2C0bE0pQgR_fQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

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.

Thoughts? Adger.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message shashidhar Reddy 2022-11-29 12:48:35 plpgsql_check_function issue after upgrade
Previous Message klaus.mailinglists 2022-11-29 10:45:03 Re: PANIC: could not flush dirty data: Cannot allocate memory

Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2022-11-29 12:32:38 Re: Reducing power consumption on idle servers
Previous Message gkokolatos 2022-11-29 12:10:46 Re: Add LZ4 compression in pg_dump