Message-ID: <b056baa5-aace-4e83-928d-dc669914c16b@cloud.gatewaynet.com>
Date: Mon, 9 Sep 2024 19:56:00 +0300
MIME-Version: 1.0
Subject: Re: postgresql FDW vs dblink for DDL
To: Tom Lane <tgl@sss.pgh.pa.us>, Adrian Klaver <adrian.klaver@aklaver.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <c150aec1-10e1-4004-b71f-6a87efcf8b37@cloud.gatewaynet.com>
 <f54de1ef-7c11-4289-9075-560d899d1295@aklaver.com>
 <1a884667-8474-dd01-d55f-1135724912f5@cloud.gatewaynet.com>
 <4efd9c6b-4021-4230-8463-d22a3d23f06c@aklaver.com>
 <3152670.1725896400@sss.pgh.pa.us>
Content-Language: en-US
From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
In-Reply-To: <3152670.1725896400@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/b056baa5-aace-4e83-928d-dc669914c16b%40cloud.gatewaynet.com>
Precedence: bulk

Στις 9/9/24 18:40, ο/η Tom Lane έγραψε:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 9/9/24 03:24, Achilleas Mantzios - cloud wrote:
>>> And the thing is that this creation via DDL is inside our design.
>>> Certain users create some backup tables of the public data in their own
>>> schema (via our app), then do some manipulations on the public data,
>>> then restore to the public or merge with the backups. When done, those
>>> backup tables are dropped. So the DDL is inside the app. And the
>>> question was if dblink is my only option, in the sense of doing this in
>>> a somewhat elegant manner. (and not resort to scripts, etc)
>> My sense is yes, if you want to encapsulate all of this within the
>> database/app you will need to use dblink.
> postgres_fdw certainly can't do it, nor any other FDW -- the FDW APIs
> simply don't cover issuance of DDL.  If you don't like dblink, you
> could consider writing code within plperlu or plpythonu or another
> "untrusted" PL, making use of whatever Postgres client library exists
> within that PL's ecosystem to connect to the remote server.  It's also
> possible that there's some third-party extension that overlaps
> dblink's functionality.  dblink sure seems like the path of least
> resistance, though.
Thank you Tom and Adrian.
>
> 			regards, tom lane

-- 
Achilleas Mantzios
  IT DEV - HEAD
  IT DEPT
  Dynacom Tankers Mgmt (as agents only)