Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit13a185f

Browse files
author
Amit Kapila
committed
Allow publications with schema and table of the same schema.
We previously thought that allowing such cases can confuse users when theyspecify DROP TABLES IN SCHEMA but that doesn't seem to be the case basedon discussion. This helps to uplift the restriction duringALTER TABLE ... SET SCHEMA which used to ensure that we couldn't end upwith a publication having both a schema and the same schema's table.To allow this, we need to forbid having any schema on a publication ifcolumn lists on a table are specified (and vice versa). This is becauseotherwise we still need a restriction during ALTER TABLE ... SET SCHEMA toforbid cases where it could lead to a publication having both a schema andthe same schema's table with column list.Based on suggestions by Peter Eisentraut.Author: Hou Zhijie and Vignesh CReviewed-By: Peter Smith, Amit KapilaBackpatch-through: 15, where it was introducedDiscussion:https://postgr.es/m/2729c9e2-9aac-8cda-f2f4-34f2bcc18f4e@enterprisedb.com
1 parentd89755d commit13a185f

File tree

13 files changed

+251
-185
lines changed

13 files changed

+251
-185
lines changed

‎doc/src/sgml/logical-replication.sgml

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1119,6 +1119,11 @@ test_sub=# SELECT * FROM child ORDER BY a;
11191119
of columns in the list is not preserved.
11201120
</para>
11211121

1122+
<para>
1123+
Specifying a column list when the publication also publishes
1124+
<literal>FOR TABLES IN SCHEMA</literal> is not supported.
1125+
</para>
1126+
11221127
<para>
11231128
For partitioned tables, the publication parameter
11241129
<literal>publish_via_partition_root</literal> determines which column list

‎doc/src/sgml/ref/alter_publication.sgml

Lines changed: 7 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -52,9 +52,11 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
5252
remove one or more tables/schemas from the publication. Note that adding
5353
tables/schemas to a publication that is already subscribed to will require an
5454
<literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal> action on the
55-
subscribing side in order to become effective. Note also that the combination
56-
of <literal>DROP</literal> with a <literal>WHERE</literal> clause is not
57-
allowed.
55+
subscribing side in order to become effective. Note also that
56+
<literal>DROP TABLES IN SCHEMA</literal> will not drop any schema tables
57+
that were specified using <literal>FOR TABLE</literal>/
58+
<literal>ADD TABLE</literal>, and the combination of <literal>DROP</literal>
59+
with a <literal>WHERE</literal> clause is not allowed.
5860
</para>
5961

6062
<para>
@@ -82,11 +84,8 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
8284
</para>
8385

8486
<para>
85-
Adding/Setting a table that is part of schema specified in
86-
<literal>TABLES IN SCHEMA</literal>, adding/setting a schema to a
87-
publication that already has a table that is part of the specified schema or
88-
adding/setting a table to a publication that already has a table's schema as
89-
part of the specified schema is not supported.
87+
Adding/Setting any schema when the publication also publishes a table with a
88+
column list, and vice versa is not supported.
9089
</para>
9190
</refsect1>
9291

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

Lines changed: 13 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -102,18 +102,18 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
102102
materialized views, and regular views cannot be part of a publication.
103103
</para>
104104

105+
<para>
106+
Specifying a column list when the publication also publishes
107+
<literal>FOR TABLES IN SCHEMA</literal> is not supported.
108+
</para>
109+
105110
<para>
106111
When a partitioned table is added to a publication, all of its existing
107112
and future partitions are implicitly considered to be part of the
108113
publication. So, even operations that are performed directly on a
109114
partition are also published via publications that its ancestors are
110115
part of.
111116
</para>
112-
113-
<para>
114-
Specifying a table that is part of a schema specified by
115-
<literal>FOR TABLES IN SCHEMA</literal> is not supported.
116-
</para>
117117
</listitem>
118118
</varlistentry>
119119

@@ -136,8 +136,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
136136
</para>
137137

138138
<para>
139-
Specifying a schemaalong with a table which belongs to the specified
140-
schema using <literal>FOR TABLE</literal> is not supported.
139+
Specifying a schemawhen the publication also publishes a table with a
140+
column list is not supported.
141141
</para>
142142

143143
<para>
@@ -273,6 +273,12 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
273273
system columns.
274274
</para>
275275

276+
<para>
277+
The row filter on a table becomes redundant if
278+
<literal>FOR TABLES IN SCHEMA</literal> is specified and the table
279+
belongs to the referred schema.
280+
</para>
281+
276282
<para>
277283
For published partitioned tables, the row filter for each
278284
partition is taken from the published partitioned table if the

‎src/backend/catalog/pg_publication.c

Lines changed: 12 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1111,6 +1111,7 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
11111111
HeapTuplepubtuple=NULL;
11121112
HeapTuplerettuple;
11131113
Oidrelid=list_nth_oid(tables,funcctx->call_cntr);
1114+
Oidschemaid=get_rel_namespace(relid);
11141115
Datumvalues[NUM_PUBLICATION_TABLES_ELEM]= {0};
11151116
boolnulls[NUM_PUBLICATION_TABLES_ELEM]= {0};
11161117

@@ -1122,9 +1123,17 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
11221123

11231124
values[0]=ObjectIdGetDatum(relid);
11241125

1125-
pubtuple=SearchSysCacheCopy2(PUBLICATIONRELMAP,
1126-
ObjectIdGetDatum(relid),
1127-
ObjectIdGetDatum(publication->oid));
1126+
/*
1127+
* We don't consider row filters or column lists for FOR ALL TABLES or
1128+
* FOR TABLES IN SCHEMA publications.
1129+
*/
1130+
if (!publication->alltables&&
1131+
!SearchSysCacheExists2(PUBLICATIONNAMESPACEMAP,
1132+
ObjectIdGetDatum(schemaid),
1133+
ObjectIdGetDatum(publication->oid)))
1134+
pubtuple=SearchSysCacheCopy2(PUBLICATIONRELMAP,
1135+
ObjectIdGetDatum(relid),
1136+
ObjectIdGetDatum(publication->oid));
11281137

11291138
if (HeapTupleIsValid(pubtuple))
11301139
{

‎src/backend/commands/publicationcmds.c

Lines changed: 55 additions & 101 deletions
Original file line numberDiff line numberDiff line change
@@ -66,7 +66,6 @@ typedef struct rf_context
6666
Oidparentid;/* relid of the parent relation */
6767
}rf_context;
6868

69-
staticList*OpenRelIdList(List*relids);
7069
staticList*OpenTableList(List*tables);
7170
staticvoidCloseTableList(List*rels);
7271
staticvoidLockSchemaList(List*schemalist);
@@ -214,44 +213,6 @@ ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate,
214213
}
215214
}
216215

217-
/*
218-
* Check if any of the given relation's schema is a member of the given schema
219-
* list.
220-
*/
221-
staticvoid
222-
CheckObjSchemaNotAlreadyInPublication(List*rels,List*schemaidlist,
223-
PublicationObjSpecTypecheckobjtype)
224-
{
225-
ListCell*lc;
226-
227-
foreach(lc,rels)
228-
{
229-
PublicationRelInfo*pub_rel= (PublicationRelInfo*)lfirst(lc);
230-
Relationrel=pub_rel->relation;
231-
OidrelSchemaId=RelationGetNamespace(rel);
232-
233-
if (list_member_oid(schemaidlist,relSchemaId))
234-
{
235-
if (checkobjtype==PUBLICATIONOBJ_TABLES_IN_SCHEMA)
236-
ereport(ERROR,
237-
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
238-
errmsg("cannot add schema \"%s\" to publication",
239-
get_namespace_name(relSchemaId)),
240-
errdetail("Table \"%s\" in schema \"%s\" is already part of the publication, adding the same schema is not supported.",
241-
RelationGetRelationName(rel),
242-
get_namespace_name(relSchemaId)));
243-
elseif (checkobjtype==PUBLICATIONOBJ_TABLE)
244-
ereport(ERROR,
245-
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
246-
errmsg("cannot add relation \"%s.%s\" to publication",
247-
get_namespace_name(relSchemaId),
248-
RelationGetRelationName(rel)),
249-
errdetail("Table's schema \"%s\" is already part of the publication or part of the specified schema list.",
250-
get_namespace_name(relSchemaId)));
251-
}
252-
}
253-
}
254-
255216
/*
256217
* Returns true if any of the columns used in the row filter WHERE expression is
257218
* not part of REPLICA IDENTITY, false otherwise.
@@ -721,7 +682,7 @@ TransformPubWhereClauses(List *tables, const char *queryString,
721682
*/
722683
staticvoid
723684
CheckPubRelationColumnList(List*tables,constchar*queryString,
724-
boolpubviaroot)
685+
boolpublish_schema,boolpubviaroot)
725686
{
726687
ListCell*lc;
727688

@@ -732,6 +693,24 @@ CheckPubRelationColumnList(List *tables, const char *queryString,
732693
if (pri->columns==NIL)
733694
continue;
734695

696+
/*
697+
* Disallow specifying column list if any schema is in the
698+
* publication.
699+
*
700+
* XXX We could instead just forbid the case when the publication
701+
* tries to publish the table with a column list and a schema for that
702+
* table. However, if we do that then we need a restriction during
703+
* ALTER TABLE ... SET SCHEMA to prevent such a case which doesn't
704+
* seem to be a good idea.
705+
*/
706+
if (publish_schema)
707+
ereport(ERROR,
708+
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
709+
errmsg("cannot use publication column list for relation \"%s.%s\"",
710+
get_namespace_name(RelationGetNamespace(pri->relation)),
711+
RelationGetRelationName(pri->relation)),
712+
errdetail("Column list cannot be specified if any schema is part of the publication or specified in the list."));
713+
735714
/*
736715
* If the publication doesn't publish changes via the root partitioned
737716
* table, the partition's column list will be used. So disallow using
@@ -858,13 +837,11 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
858837
List*rels;
859838

860839
rels=OpenTableList(relations);
861-
CheckObjSchemaNotAlreadyInPublication(rels,schemaidlist,
862-
PUBLICATIONOBJ_TABLE);
863-
864840
TransformPubWhereClauses(rels,pstate->p_sourcetext,
865841
publish_via_partition_root);
866842

867843
CheckPubRelationColumnList(rels,pstate->p_sourcetext,
844+
schemaidlist!=NIL,
868845
publish_via_partition_root);
869846

870847
PublicationAddTables(puboid,rels, true,NULL);
@@ -1110,8 +1087,8 @@ InvalidatePublicationRels(List *relids)
11101087
*/
11111088
staticvoid
11121089
AlterPublicationTables(AlterPublicationStmt*stmt,HeapTupletup,
1113-
List*tables,List*schemaidlist,
1114-
constchar*queryString)
1090+
List*tables,constchar*queryString,
1091+
boolpublish_schema)
11151092
{
11161093
List*rels=NIL;
11171094
Form_pg_publicationpubform= (Form_pg_publication)GETSTRUCT(tup);
@@ -1129,19 +1106,12 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
11291106

11301107
if (stmt->action==AP_AddObjects)
11311108
{
1132-
List*schemas=NIL;
1133-
1134-
/*
1135-
* Check if the relation is member of the existing schema in the
1136-
* publication or member of the schema list specified.
1137-
*/
1138-
schemas=list_concat_copy(schemaidlist,GetPublicationSchemas(pubid));
1139-
CheckObjSchemaNotAlreadyInPublication(rels,schemas,
1140-
PUBLICATIONOBJ_TABLE);
1141-
11421109
TransformPubWhereClauses(rels,queryString,pubform->pubviaroot);
11431110

1144-
CheckPubRelationColumnList(rels,queryString,pubform->pubviaroot);
1111+
publish_schema |=is_schema_publication(pubid);
1112+
1113+
CheckPubRelationColumnList(rels,queryString,publish_schema,
1114+
pubform->pubviaroot);
11451115

11461116
PublicationAddTables(pubid,rels, false,stmt);
11471117
}
@@ -1154,12 +1124,10 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
11541124
List*delrels=NIL;
11551125
ListCell*oldlc;
11561126

1157-
CheckObjSchemaNotAlreadyInPublication(rels,schemaidlist,
1158-
PUBLICATIONOBJ_TABLE);
1159-
11601127
TransformPubWhereClauses(rels,queryString,pubform->pubviaroot);
11611128

1162-
CheckPubRelationColumnList(rels,queryString,pubform->pubviaroot);
1129+
CheckPubRelationColumnList(rels,queryString,publish_schema,
1130+
pubform->pubviaroot);
11631131

11641132
/*
11651133
* To recreate the relation list for the publication, look for
@@ -1308,16 +1276,35 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt,
13081276
LockSchemaList(schemaidlist);
13091277
if (stmt->action==AP_AddObjects)
13101278
{
1311-
List*rels;
1279+
ListCell*lc;
13121280
List*reloids;
13131281

13141282
reloids=GetPublicationRelations(pubform->oid,PUBLICATION_PART_ROOT);
1315-
rels=OpenRelIdList(reloids);
13161283

1317-
CheckObjSchemaNotAlreadyInPublication(rels,schemaidlist,
1318-
PUBLICATIONOBJ_TABLES_IN_SCHEMA);
1284+
foreach(lc,reloids)
1285+
{
1286+
HeapTuplecoltuple;
1287+
1288+
coltuple=SearchSysCache2(PUBLICATIONRELMAP,
1289+
ObjectIdGetDatum(lfirst_oid(lc)),
1290+
ObjectIdGetDatum(pubform->oid));
1291+
1292+
if (!HeapTupleIsValid(coltuple))
1293+
continue;
1294+
1295+
/*
1296+
* Disallow adding schema if column list is already part of the
1297+
* publication. See CheckPubRelationColumnList.
1298+
*/
1299+
if (!heap_attisnull(coltuple,Anum_pg_publication_rel_prattrs,NULL))
1300+
ereport(ERROR,
1301+
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1302+
errmsg("cannot add schema to the publication"),
1303+
errdetail("Schema cannot be added if any table that specifies column list is already part of the publication."));
1304+
1305+
ReleaseSysCache(coltuple);
1306+
}
13191307

1320-
CloseTableList(rels);
13211308
PublicationAddSchemas(pubform->oid,schemaidlist, false,stmt);
13221309
}
13231310
elseif (stmt->action==AP_DropObjects)
@@ -1429,14 +1416,7 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
14291416

14301417
heap_freetuple(tup);
14311418

1432-
/*
1433-
* Lock the publication so nobody else can do anything with it. This
1434-
* prevents concurrent alter to add table(s) that were already going
1435-
* to become part of the publication by adding corresponding schema(s)
1436-
* via this command and similarly it will prevent the concurrent
1437-
* addition of schema(s) for which there is any corresponding table
1438-
* being added by this command.
1439-
*/
1419+
/* Lock the publication so nobody else can do anything with it. */
14401420
LockDatabaseObject(PublicationRelationId,pubid,0,
14411421
AccessExclusiveLock);
14421422

@@ -1453,8 +1433,8 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
14531433
errmsg("publication \"%s\" does not exist",
14541434
stmt->pubname));
14551435

1456-
AlterPublicationTables(stmt,tup,relations,schemaidlist,
1457-
pstate->p_sourcetext);
1436+
AlterPublicationTables(stmt,tup,relations,pstate->p_sourcetext,
1437+
schemaidlist!=NIL);
14581438
AlterPublicationSchemas(stmt,tup,schemaidlist);
14591439
}
14601440

@@ -1569,32 +1549,6 @@ RemovePublicationSchemaById(Oid psoid)
15691549
table_close(rel,RowExclusiveLock);
15701550
}
15711551

1572-
/*
1573-
* Open relations specified by a relid list.
1574-
* The returned tables are locked in ShareUpdateExclusiveLock mode in order to
1575-
* add them to a publication.
1576-
*/
1577-
staticList*
1578-
OpenRelIdList(List*relids)
1579-
{
1580-
ListCell*lc;
1581-
List*rels=NIL;
1582-
1583-
foreach(lc,relids)
1584-
{
1585-
PublicationRelInfo*pub_rel;
1586-
Oidrelid=lfirst_oid(lc);
1587-
Relationrel=table_open(relid,
1588-
ShareUpdateExclusiveLock);
1589-
1590-
pub_rel=palloc(sizeof(PublicationRelInfo));
1591-
pub_rel->relation=rel;
1592-
rels=lappend(rels,pub_rel);
1593-
}
1594-
1595-
returnrels;
1596-
}
1597-
15981552
/*
15991553
* Open relations specified by a PublicationTable list.
16001554
* The returned tables are locked in ShareUpdateExclusiveLock mode in order to

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp