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
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.albe@xxxxxxxxxx> wrote:
What is the application you want to access this way?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.
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
