Обсуждение: FDW wrapper for clustered app management

On 10/17/2014 2:35 AM, Jerome Wagner wrote:
> Hello,
>
> I am considering (postgres 9.3+) the idea of opening a R/W access into
> a clustered application by creating one fdw server from a central
> database to each server a cluster.
>
> That would imply opening a port on each server inside the application,
> listening for incoming connections from the database and this way all
> the servers would become visible with R/W access.
>
> Is that a sound idea or does it look horrible ? Would it be reasonable
> to connect in this way to a cluster of 1, 10, 100, 1000 or more servers ?
>
> is there an existing xxxx_fdw wrapper that would look like a good
> candidate for such a direct access inside an application ? Then I
> would have to implement the protocol corresponding to this xxxx_fdw
> inside my application.
>

is the application running on these 10, 100, 1000 nodes something
resembling a table oriented relational database?

I would suggest instead you look at using a MQ style message queueing
system, with publish-subscribe semantics for your distributed remote
procedure calls. and not from within a database, rather, from your
central control application to your distributed application workers...




--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Jerome Wagner wrote:
> I am considering (postgres 9.3+) the idea of opening a R/W access into a clustered application by
> creating one fdw server from a central database to each server a cluster.
> 
> That would imply opening a port on each server inside the application, listening for incoming
> connections from the database and this way all the servers would become visible with R/W access.
> 
> Is that a sound idea or does it look horrible ? Would it be reasonable to connect in this way to a
> cluster of 1, 10, 100, 1000 or more servers ?
> 
> is there an existing xxxx_fdw wrapper that would look like a good candidate for such a direct access
> inside an application ? Then I would have to implement the protocol corresponding to this xxxx_fdw
> inside my application.

What is the application you want to access this way?

Does "cluster" mean that data is distributed (sharded) across these servers?

Can you reasonably translate SQL access predicates so that you can push down
WHERE conditions to the remote servers?  If not, every access would have to pull
the entire foreign table into the PostgreSQL server before applying the conditions.

Under the right conditions, such a setup could work, e.g. to join data from
different data sources.  How many servers make sense probably depends a lot on how
you want to use them.

Yours,
Laurenz Albe

Jerome Wagner wrote:
[wants to access an application server farm with FDW]
> Clearly, I am trying to see how I could twist the fdw wrappers into a sort of manhole inside the
> application, without resorting to a classic event based mechanism.

That's too metaphorical for me to understand.

> I could also imagine very risky things (security wise) like :
> 
> SELECT line FROM server1.execute WHERE command = 'wc -l /my/file' ORDER by lineno;
> 
> and use pgadmin as general control mechanism for the cluster : the cluster and the data would be on
> the same SQL data plane :-)

Such things are conceivable, but that would subvert the concept of a FDW,
which is to translate SQL queries into a foreign API and convert the result
to PostgreSQL data.
I found that it is often a bad idea to use things in ways that are not intended
(it can be good fun though).

While you can certainly draw inspiration from existing FDW (where is a www_fdw for example),
what you want to do would mean writing a PostgreSQL module in C (or Python, if you use
Multicorn) for your application.

Yours,
Laurenz Albe