Thread: postgresql FDW vs dblink for DDL
Hi for remote DDL execution (such as CREATE TABLE) is dblink my only option? thanks
On 9/8/24 13:04, Achilleas Mantzios wrote: > Hi > > for remote DDL execution (such as CREATE TABLE) is dblink my only option? You will need to define in what context you are considering options. For instance you can do remote DDL operations by passing a command or script via psql. > > thanks > > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 9/8/24 23:46, Adrian Klaver wrote: > On 9/8/24 13:04, Achilleas Mantzios wrote: >> Hi >> >> for remote DDL execution (such as CREATE TABLE) is dblink my only >> option? > > You will need to define in what context you are considering options. > > For instance you can do remote DDL operations by passing a command or > script via psql. Thank you Adrian, I was thinking of wrapping the DDL around a FUNCTION that will handle both the local and the (possibly various logical subscriptions), so that my programmers don't need to write extra scripts. 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) > >> >> thanks >> >> >> >> >
On 9/9/24 03:24, Achilleas Mantzios - cloud wrote: > > On 9/8/24 23:46, Adrian Klaver wrote: >> On 9/8/24 13:04, Achilleas Mantzios wrote: >>> Hi >>> >>> for remote DDL execution (such as CREATE TABLE) is dblink my only >>> option? >> >> You will need to define in what context you are considering options. >> >> For instance you can do remote DDL operations by passing a command or >> script via psql. > > Thank you Adrian, > > I was thinking of wrapping the DDL around a FUNCTION that will handle > both the local and the (possibly various logical subscriptions), so that > my programmers don't need to write extra scripts. > > 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. > >> >>> >>> thanks >>> >>> >>> >>> >> -- Adrian Klaver adrian.klaver@aklaver.com
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.
regards, tom lane
Στις 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)