Thread: [HACKERS] alter server for foreign table
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
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,
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
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
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
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
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
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
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
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
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