RE: Pgoutput not capturing the generated columns
Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
Dear Shubham, More comments for v43-0001. 01. publication.out and publication.sql I think your fix is not sufficient, even if it pass tests. ``` -- error: system attributes "ctid" not allowed in column list ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, ctid); -ERROR: cannot use system column "ctid" in publication column list +ERROR: relation "testpub_tbl5" is already member of publication "testpub_fortable" ALTER PUBLICATION testpub_fortable SET TABLE testpub_tbl1 (id, ctid); ERROR: cannot use system column "ctid" in publication column list -- error: duplicates not allowed in column list ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, a); -ERROR: duplicate column "a" in publication column list +ERROR: relation "testpub_tbl5" is already member of publication "testpub_fortable ``` The error message is not match with the comment. The comment said that the table has already been added in the publication. I think the first line [1] succeeded by your change and testpub_tbl5 became a member at that time then upcoming ALTER statements failed by the duplicate registration. ``` -- ok ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, c); +ERROR: relation "testpub_tbl5" is already member of publication "testpub_fortable" ``` You said OK but same error happened. ``` ALTER TABLE testpub_tbl5 DROP COLUMN c; -- no dice -ERROR: cannot drop column c of table testpub_tbl5 because other objects depend on it -DETAIL: publication of table testpub_tbl5 in publication testpub_fortable depends on column c of table testpub_tbl5 -HINT: Use DROP ... CASCADE to drop the dependent objects too. ``` This statement should be failed because c was included in the column. However, it succeeded because previous ALTER PUBLICATION was failed. Upcoming SQLs wrongly thawed ERRORs because of this. Please look at all of differences before doing copy-and-paste. 02. 031_column_list.pl ``` -# TEST: Generated and dropped columns are not considered for the column list. +# TEST: Dropped columns are not considered for the column list. # So, the publication having a column list except for those columns and a # publication without any column (aka all columns as part of the columns # list) are considered to have the same column list. ``` Based on the comment, this case does not test the behavior of generated columns anymore. So, I felt column 'd' could be removed from the case. 03. 031_column_list.pl Can we test that generated columns won't be replaced if it does not included in the column list? [1]: ``` ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, d); ``` Best regards, Hayato Kuroda FUJITSU LIMITED
Commits
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Doc: Generated column replication.
- 6252b1eaf82b 18.0 landed
-
Rename pubgencols_type to pubgencols in pg_publication.
- 75eb9766ec20 18.0 landed
-
Doc: Fix column name in pg_publication catalog.
- 117f9f328e0f 18.0 landed
-
Fix buildfarm failure introduced by commit e65dbc9927.
- b35434b134b1 18.0 landed
-
Change publication's publish_generated_columns option type to enum.
- e65dbc9927fb 18.0 landed
-
Fix \dRp+ output when describing publications with a lower server version.
- 991974bb4888 18.0 landed
-
Replicate generated columns when 'publish_generated_columns' is set.
- 7054186c4ebe 18.0 landed
-
Doc: Update the behavior of generated columns in Logical Replication.
- 5b0c46ea0932 18.0 landed
-
Replicate generated columns when specified in the column list.
- 745217a051a9 18.0 landed
-
Doc: Generated columns are skipped for logical replication.
- 7a089f6e6a14 17.0 cited