Thread: [HACKERS] alter server for foreign table

[HACKERS] alter server for foreign table

From
Konstantin Knizhnik
Date:
According to Postgresql documentation it is not possible to alter server for foreign table:

https://www.postgresql.org/docs/10/static/sql-alterforeigntable.html

But stackoverflow suggests the following hack directly updating pg_foreign_table:
https://stackoverflow.com/questions/37388756/may-i-alter-server-for-foreign-table

I wonder how safe it is and if it is so simple, why it is not support in ALTER FOREIGN TABLE statement?

Thanks in advance,
-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 

Re: [HACKERS] alter server for foreign table

From
Tom Lane
Date:
Konstantin Knizhnik <k.knizhnik@postgrespro.ru> writes:
> According to Postgresql documentation it is not possible to alter server 
> for foreign table:
> https://www.postgresql.org/docs/10/static/sql-alterforeigntable.html

Hmm, we'd have to check if the table's options were legal for the
new FDW, but in principle we could support this, I suppose.
Not sure if it's useful enough to be worth the trouble.
        regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] alter server for foreign table

From
David Fetter
Date:
On Fri, Sep 29, 2017 at 01:47:59PM -0400, Tom Lane wrote:
> Konstantin Knizhnik <k.knizhnik@postgrespro.ru> writes:
> > According to Postgresql documentation it is not possible to alter server 
> > for foreign table:
> > https://www.postgresql.org/docs/10/static/sql-alterforeigntable.html
> 
> Hmm, we'd have to check if the table's options were legal for the
> new FDW, but in principle we could support this, I suppose.
> Not sure if it's useful enough to be worth the trouble.

It would definitely be useful if it were available.  Nodes are a good
bit more fungible than they were even 5 years back.

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] alter server for foreign table

From
Nico Williams
Date:
On Fri, Sep 29, 2017 at 10:19:03PM +0200, David Fetter wrote:
> On Fri, Sep 29, 2017 at 01:47:59PM -0400, Tom Lane wrote:
> > Konstantin Knizhnik <k.knizhnik@postgrespro.ru> writes:
> > > According to Postgresql documentation it is not possible to alter server 
> > > for foreign table:
> > > https://www.postgresql.org/docs/10/static/sql-alterforeigntable.html
> > 
> > Hmm, we'd have to check if the table's options were legal for the
> > new FDW, but in principle we could support this, I suppose.
> > Not sure if it's useful enough to be worth the trouble.
> 
> It would definitely be useful if it were available.  Nodes are a good
> bit more fungible than they were even 5 years back.

It would be nice to have DDLs for everything ALTERation that one could
make that is trivial to support.

It would also be nice to have a rule that every DDL (and every ADD/DROP
in ALTER statements) support IF EXISTS / IF NOT EXISTS, and, where
meaningful, OR REPLACE.  I work around a lot of missing IE/INE/OR by
using techniques like: conditioning on a schema query, using DROP IF
EXISTS then CREATE in a transaction (when the DROP has IE but the CREATE
does not have INE), catching exceptions, and so on.

These are little things -- quality of life sorts of things :)

I've also grown accustomed to writing complex pg_catalog queries.  It'd
be nice to have some built-in views on the pg_catalog -- something
better than the information_schema.  I have written some that we use for
code generation based on PG schemas; we'd be glad to contribute them.

Nico
-- 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] alter server for foreign table

From
Derry Hamilton
Date:
Just to say, yes, this would be handy. I've been using a variant of that hack on reporting servers, while migrating systems from proprietary databases.  It behaves quite gracefully when there are incompatible options, and it fixes up properly with DROPs as the first options.

Derry

Re: [HACKERS] alter server for foreign table

From
Andrew Dunstan
Date:

On 09/30/2017 05:14 AM, Derry Hamilton wrote:
> Just to say, yes, this would be handy. I've been using a variant of
> that hack on reporting servers, while migrating systems from
> proprietary databases.  It behaves quite gracefully when there are
> incompatible options, and it fixes up properly with DROPs as the first
> options.
>
>


I assume the proposal is to allow changing to a different server using
the same FDW. I can see all sorts of odd things happening if we allow
changing to a server of a different FDW.


cheers

andrew

-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] alter server for foreign table

From
Nico Williams
Date:
On Sat, Sep 30, 2017 at 03:58:04PM -0400, Andrew Dunstan wrote:
> On 09/30/2017 05:14 AM, Derry Hamilton wrote:
> > Just to say, yes, this would be handy. I've been using a variant of
> > that hack on reporting servers, while migrating systems from
> > proprietary databases.  It behaves quite gracefully when there are
> > incompatible options, and it fixes up properly with DROPs as the first
> > options.
>
> I assume the proposal is to allow changing to a different server using
> the same FDW. I can see all sorts of odd things happening if we allow
> changing to a server of a different FDW.

Like what that could not happen without this feature anyways?

Suppose the foreign server becomes unreachable, changes its schema
completely, and becomes reachable again?  How would that be different
from changing the server name to one with a totally different schema?

Naturally one should shoot one's feet off, but the proposed feature
wouldn't exactly be a footgun.  To believe otherwise would be like
arguing that DROP TABLE (especially CASCASDE!) is a footgun, so better
not have it.

Nico
--


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] alter server for foreign table

From
konstantin knizhnik
Date:
On Sep 30, 2017, at 10:58 PM, Andrew Dunstan wrote:

>
>
> On 09/30/2017 05:14 AM, Derry Hamilton wrote:
>> Just to say, yes, this would be handy. I've been using a variant of
>> that hack on reporting servers, while migrating systems from
>> proprietary databases.  It behaves quite gracefully when there are
>> incompatible options, and it fixes up properly with DROPs as the first
>> options.
>>
>>
>
>
> I assume the proposal is to allow changing to a different server using
> the same FDW. I can see all sorts of odd things happening if we allow
> changing to a server of a different FDW.

Actually what I need is to handle a move of a shard (partition) to other node.
I can not use "alter server" to change connection string, because this server is used for many shards located at this
node.
And I do not want to drop and recreate foreign table because it seems to be very complicated.

So I need to change server of the same FDW.
But in theory I can imagine situation when partition is moved to another database (from MySQL to Postgres for example).
Inthis case we need to change FDW. 
What can be wrong with changing FDW? All checks that FDW is consistent with foreign table can be redone...




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] alter server for foreign table

From
Tom Lane
Date:
Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> I assume the proposal is to allow changing to a different server using
> the same FDW. I can see all sorts of odd things happening if we allow
> changing to a server of a different FDW.

As long as we check that the table's FDW options are acceptable to the
new FDW, where's the problem?  I could see there being an issue if we
provided any hook whereby FDW could have a say during CREATE FOREIGN
TABLE (or DROP FOREIGN TABLE), but we don't.  So as far as the FDWs
are concerned, this should be little different from dropping the foreign
table and then remaking it attached to the new server.
        regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers