Re: FDW wrapper for clustered app management

Hello Albe,

Thanks for your answer.

The application is an application that is a sort of supervisor of sub
applications, mainly web oriented. It manage access rights, update versions
of web services, launch services, ..

No the data is not sharded across the servers, but each server can have its
own set of data.

Yes I guess that the predicates (and WHERE clause) could be pushed down to
the servers.

But I am not sure how fdw works regarding joins between a remote server and
a local table. In this case it is hard for me to answer you.

For example I have yet to document myself on how fdw handles those 2 types
of requests :

-- without join
SELECT s.name FROM server1.service AS s
WHERE s.uptime > 20000;

-- with join
SELECT s.name FROM server1.service AS s
JOIN uptime_threshold u ON (u.name = s.name)
WHERE s.uptime > u.uptime;

I also have not thought about the multi-server requests. A view on all the
fdw servers, could probably enable SELECT requests communicating with all
the servers (eg: what are all the servers that have a specific property).
For the write part, a stored procedure could probably be used ; I don't
know if there is another option.

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.

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 :-)

Thanks,
Jerome

On Fri, Oct 17, 2014 at 12:00 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
wrote:

> 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
>