Skip to content

Commit 7054186

Browse files
author
Amit Kapila
committed
Replicate generated columns when 'publish_generated_columns' is set.
This patch builds on the work done in commit 745217a by enabling the replication of generated columns alongside regular column changes through a new publication parameter: publish_generated_columns. Example usage: CREATE PUBLICATION pub1 FOR TABLE tab_gencol WITH (publish_generated_columns = true); The column list takes precedence. If the generated columns are specified in the column list, they will be replicated even if 'publish_generated_columns' is set to false. Conversely, if generated columns are not included in the column list (assuming the user specifies a column list), they will not be replicated even if 'publish_generated_columns' is true. Author: Vignesh C, Shubham Khanna Reviewed-by: Peter Smith, Amit Kapila, Hayato Kuroda, Shlok Kyal, Ajin Cherian, Hou Zhijie, Masahiko Sawada Discussion: https://postgr.es/m/B80D17B2-2C8E-4C7D-87F2-E5B4BE3C069E@gmail.com
1 parent 70291a3 commit 7054186

File tree

20 files changed

+925
-403
lines changed

20 files changed

+925
-403
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -514,9 +514,11 @@ CREATE TABLE people (
514514
</listitem>
515515
<listitem>
516516
<para>
517-
Generated columns can be replicated during logical replication by
518-
including them in the column list of the
519-
<command>CREATE PUBLICATION</command> command.
517+
Generated columns are allowed to be replicated during logical replication
518+
according to the <command>CREATE PUBLICATION</command> parameter
519+
<link linkend="sql-createpublication-params-with-publish-generated-columns">
520+
<literal>publish_generated_columns</literal></link> or by including them
521+
in the column list of the <command>CREATE PUBLICATION</command> command.
520522
</para>
521523
</listitem>
522524
</itemizedlist>

‎doc/src/sgml/protocol.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7477,7 +7477,7 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"
74777477
</variablelist>
74787478

74797479
<para>
7480-
Next, one of the following submessages appears for each column:
7480+
Next, one of the following submessages appears for each published column:
74817481

74827482
<variablelist>
74837483
<varlistentry>

‎doc/src/sgml/ref/create_publication.sgml

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -189,6 +189,26 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
189189
</listitem>
190190
</varlistentry>
191191

192+
<varlistentry id="sql-createpublication-params-with-publish-generated-columns">
193+
<term><literal>publish_generated_columns</literal> (<type>boolean</type>)</term>
194+
<listitem>
195+
<para>
196+
Specifies whether the generated columns present in the tables
197+
associated with the publication should be replicated.
198+
The default is <literal>false</literal>.
199+
</para>
200+
201+
<note>
202+
<para>
203+
If the subscriber is from a release prior to 18, then initial table
204+
synchronization won't copy generated columns even if parameter
205+
<literal>publish_generated_columns</literal> is true in the
206+
publisher.
207+
</para>
208+
</note>
209+
</listitem>
210+
</varlistentry>
211+
192212
<varlistentry id="sql-createpublication-params-with-publish-via-partition-root">
193213
<term><literal>publish_via_partition_root</literal> (<type>boolean</type>)</term>
194214
<listitem>

‎src/backend/catalog/pg_publication.c

Lines changed: 72 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -256,6 +256,52 @@ is_schema_publication(Oid pubid)
256256
return result;
257257
}
258258

259+
/*
260+
* Returns true if the relation has column list associated with the
261+
* publication, false otherwise.
262+
*
263+
* If a column list is found, the corresponding bitmap is returned through the
264+
* cols parameter, if provided. The bitmap is constructed within the given
265+
* memory context (mcxt).
266+
*/
267+
bool
268+
check_and_fetch_column_list(Publication *pub, Oid relid, MemoryContext mcxt,
269+
Bitmapset **cols)
270+
{
271+
HeapTuple cftuple;
272+
bool found = false;
273+
274+
if (pub->alltables)
275+
return false;
276+
277+
cftuple = SearchSysCache2(PUBLICATIONRELMAP,
278+
ObjectIdGetDatum(relid),
279+
ObjectIdGetDatum(pub->oid));
280+
if (HeapTupleIsValid(cftuple))
281+
{
282+
Datum cfdatum;
283+
bool isnull;
284+
285+
/* Lookup the column list attribute. */
286+
cfdatum = SysCacheGetAttr(PUBLICATIONRELMAP, cftuple,
287+
Anum_pg_publication_rel_prattrs, &isnull);
288+
289+
/* Was a column list found? */
290+
if (!isnull)
291+
{
292+
/* Build the column list bitmap in the given memory context. */
293+
if (cols)
294+
*cols = pub_collist_to_bitmapset(*cols, cfdatum, mcxt);
295+
296+
found = true;
297+
}
298+
299+
ReleaseSysCache(cftuple);
300+
}
301+
302+
return found;
303+
}
304+
259305
/*
260306
* Gets the relations based on the publication partition option for a specified
261307
* relation.
@@ -573,6 +619,30 @@ pub_collist_to_bitmapset(Bitmapset *columns, Datum pubcols, MemoryContext mcxt)
573619
return result;
574620
}
575621

622+
/*
623+
* Returns a bitmap representing the columns of the specified table.
624+
*
625+
* Generated columns are included if include_gencols is true.
626+
*/
627+
Bitmapset *
628+
pub_form_cols_map(Relation relation, bool include_gencols)
629+
{
630+
Bitmapset *result = NULL;
631+
TupleDesc desc = RelationGetDescr(relation);
632+
633+
for (int i = 0; i < desc->natts; i++)
634+
{
635+
Form_pg_attribute att = TupleDescAttr(desc, i);
636+
637+
if (att->attisdropped || (att->attgenerated && !include_gencols))
638+
continue;
639+
640+
result = bms_add_member(result, att->attnum);
641+
}
642+
643+
return result;
644+
}
645+
576646
/*
577647
* Insert new publication / schema mapping.
578648
*/
@@ -998,6 +1068,7 @@ GetPublication(Oid pubid)
9981068
pub->pubactions.pubdelete = pubform->pubdelete;
9991069
pub->pubactions.pubtruncate = pubform->pubtruncate;
10001070
pub->pubviaroot = pubform->pubviaroot;
1071+
pub->pubgencols = pubform->pubgencols;
10011072

10021073
ReleaseSysCache(tup);
10031074

@@ -1205,7 +1276,7 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
12051276
{
12061277
Form_pg_attribute att = TupleDescAttr(desc, i);
12071278

1208-
if (att->attisdropped || att->attgenerated)
1279+
if (att->attisdropped || (att->attgenerated && !pub->pubgencols))
12091280
continue;
12101281

12111282
attnums[nattnums++] = att->attnum;

‎src/backend/commands/publicationcmds.c

Lines changed: 30 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -78,19 +78,23 @@ parse_publication_options(ParseState *pstate,
7878
bool *publish_given,
7979
PublicationActions *pubactions,
8080
bool *publish_via_partition_root_given,
81-
bool *publish_via_partition_root)
81+
bool *publish_via_partition_root,
82+
bool *publish_generated_columns_given,
83+
bool *publish_generated_columns)
8284
{
8385
ListCell *lc;
8486

8587
*publish_given = false;
8688
*publish_via_partition_root_given = false;
89+
*publish_generated_columns_given = false;
8790

8891
/* defaults */
8992
pubactions->pubinsert = true;
9093
pubactions->pubupdate = true;
9194
pubactions->pubdelete = true;
9295
pubactions->pubtruncate = true;
9396
*publish_via_partition_root = false;
97+
*publish_generated_columns = false;
9498

9599
/* Parse options */
96100
foreach(lc, options)
@@ -151,6 +155,13 @@ parse_publication_options(ParseState *pstate,
151155
*publish_via_partition_root_given = true;
152156
*publish_via_partition_root = defGetBoolean(defel);
153157
}
158+
else if (strcmp(defel->defname, "publish_generated_columns") == 0)
159+
{
160+
if (*publish_generated_columns_given)
161+
errorConflictingDefElem(defel, pstate);
162+
*publish_generated_columns_given = true;
163+
*publish_generated_columns = defGetBoolean(defel);
164+
}
154165
else
155166
ereport(ERROR,
156167
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -737,6 +748,8 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
737748
PublicationActions pubactions;
738749
bool publish_via_partition_root_given;
739750
bool publish_via_partition_root;
751+
bool publish_generated_columns_given;
752+
bool publish_generated_columns;
740753
AclResult aclresult;
741754
List *relations = NIL;
742755
List *schemaidlist = NIL;
@@ -776,7 +789,9 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
776789
stmt->options,
777790
&publish_given, &pubactions,
778791
&publish_via_partition_root_given,
779-
&publish_via_partition_root);
792+
&publish_via_partition_root,
793+
&publish_generated_columns_given,
794+
&publish_generated_columns);
780795

781796
puboid = GetNewOidWithIndex(rel, PublicationObjectIndexId,
782797
Anum_pg_publication_oid);
@@ -793,6 +808,8 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
793808
BoolGetDatum(pubactions.pubtruncate);
794809
values[Anum_pg_publication_pubviaroot - 1] =
795810
BoolGetDatum(publish_via_partition_root);
811+
values[Anum_pg_publication_pubgencols - 1] =
812+
BoolGetDatum(publish_generated_columns);
796813

797814
tup = heap_form_tuple(RelationGetDescr(rel), values, nulls);
798815

@@ -878,6 +895,8 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
878895
PublicationActions pubactions;
879896
bool publish_via_partition_root_given;
880897
bool publish_via_partition_root;
898+
bool publish_generated_columns_given;
899+
bool publish_generated_columns;
881900
ObjectAddress obj;
882901
Form_pg_publication pubform;
883902
List *root_relids = NIL;
@@ -887,7 +906,9 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
887906
stmt->options,
888907
&publish_given, &pubactions,
889908
&publish_via_partition_root_given,
890-
&publish_via_partition_root);
909+
&publish_via_partition_root,
910+
&publish_generated_columns_given,
911+
&publish_generated_columns);
891912

892913
pubform = (Form_pg_publication) GETSTRUCT(tup);
893914

@@ -997,6 +1018,12 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
9971018
replaces[Anum_pg_publication_pubviaroot - 1] = true;
9981019
}
9991020

1021+
if (publish_generated_columns_given)
1022+
{
1023+
values[Anum_pg_publication_pubgencols - 1] = BoolGetDatum(publish_generated_columns);
1024+
replaces[Anum_pg_publication_pubgencols - 1] = true;
1025+
}
1026+
10001027
tup = heap_modify_tuple(tup, RelationGetDescr(rel), values, nulls,
10011028
replaces);
10021029

0 commit comments

Comments
 (0)