Re: If subscription to foreign table valid ?

Hi,

On 11/05/17 14:25, tushar wrote:
> Hi,
>
> I observed that -we cannot publish "foreign table" in Publication
>
> postgres=# create foreign table t (n int) server db1_server options
> (table_name 't1');
> CREATE FOREIGN TABLE
>
> postgres=# create publication pub for table t;
> ERROR: "t" is not a table
> DETAIL: Only tables can be added to publications.
> postgres=#
>
> 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.
>

Thanks for report. What you can publish and what you can subscribe is
not necessarily same (we can write to relations which we can't capture
from wal, for example unlogged table can't be published but can be
subscribed).

However, the foreign tables indeed can't be subscribed. I originally
planned to have foreign tables allowed on subscriber but it turned out
to be more complex to implement than I had anticipated do I ripped the
code for that from the original patch.

We do check for this, but only during replication which we have to do
because the fact that relation 't' was foreign table during ALTER
SUBSCRIPTION does not mean that it won't be something else half hour later.

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.

I'll look into writing patch for this. I don't think it's beta blocker
though.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services