On 5/11/17, Petr Jelinek <petr.jeli...@2ndquadrant.com> wrote:
> Hi,
>
> On 11/05/17 14:25, tushar wrote:
>> Hi,
>>
>> I observed that -we cannot publish "foreign table" in Publication
>>
>> but same thing is not true for Subscription
>>
>> postgres=# create foreign table t (n int) server db1_server options
>> (table_name 't');
>> CREATE FOREIGN TABLE
>> postgres=# alter subscription sub refresh publication ;
>> NOTICE: added subscription for table public.t
>> ALTER SUBSCRIPTION
>>
>> Is this an expected behavior ? if we cannot publish then how can we
>> add subscription for it.
The above foreign table subscription succeeds only if the publisher
has published a same named normal table i.e.
create table t (n int);
CREATE PUBLICATION mypub FOR TABLE t;
I think in current implemetation of LogicalRep. it is users
responsibility to match the table definition at publisher and
subscriber. Subscriber can not determine by itself what publisher has
defined. This usecase does not align with this assumption.
> However, the foreign tables indeed can't be subscribed.
I suspect that a user would want to subcribe a foreign table in real world.
> I think it does make sense to add check for this into CREATE/ALTER
> SUBSCRIBER though so that user is informed immediately about the mistake
> rather than by errors in the logs later.
Yes, system should prohibit such operation though.
I tried to write to a patch to achieve this. It disalows subscription
of relation other than RELKIND_RELATION.
Attached is the patch. Comments?
Regards,
Neha
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index b76cdc5..f6013da 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -440,9 +440,20 @@ CreateSubscription(CreateSubscriptionStmt *stmt, bool isTopLevel)
{
RangeVar *rv = (RangeVar *) lfirst(lc);
Oid relid;
+ Relation relation;
relid = RangeVarGetRelid(rv, AccessShareLock, false);
+ relation = RelationIdGetRelation(relid);
+
+ if (RelationGetForm(relation)->relkind != RELKIND_RELATION)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("\"%s\" is not a normal table",
+ RelationGetRelationName(relation)),
+ errdetail("Only normal tables can be subscribed.")));
+ RelationClose(relation);
+
SetSubscriptionRelState(subid, relid, table_state,
InvalidXLogRecPtr);
}
@@ -553,8 +564,21 @@ AlterSubscription_refresh(Subscription *sub, bool copy_data)
{
RangeVar *rv = (RangeVar *) lfirst(lc);
Oid relid;
+ Relation relation;
relid = RangeVarGetRelid(rv, AccessShareLock, false);
+
+ relation = RelationIdGetRelation(relid);
+
+ if (RelationGetForm(relation)->relkind != RELKIND_RELATION)
+ ereport(NOTICE,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("\"%s\" is not a normal table",
+ RelationGetRelationName(relation)),
+ errdetail("Only normal tables can be subscribed.")));
+
+ RelationClose(relation);
+
pubrel_local_oids[off++] = relid;
if (!bsearch(&relid, subrel_local_oids,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
