FDW wrapper for clustered app management

[Jerome Wagner]

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.

Thank you for your feedback.
Jerome

[John R Pierce]

Post by Jerome Wagner
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]

Hello John,
Thanks for your answer. I am also considering a publish/subscribe MQ based
solution (and this may be indeed needed for queuing RPCs).

the data I would like to access R/W is more like
- configuration data
- states of different state machines
- cache values for some keys (reading, invalidating)

so you could in a sense say that they are table oriented.

I agree that this becomes 'tangential' with write access.
a request like "UPDATE server.service SET start_requested = true WHERE name
= 'myService' and started = false" seems weird but it could probably work.

Thanks,
Jerome

Post by John R Pierce

Post by Jerome Wagner
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
--
http://www.postgresql.org/mailpref/pgsql-general

[Albe Laurenz]

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]

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

Post by Jerome Wagner

Post by Jerome Wagner
I am considering (postgres 9.3+) the idea of opening a R/W access into a

clustered application by

Post by Jerome Wagner
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

Post by Jerome Wagner
connections from the database and this way all the servers would become

visible with R/W access.

Post by Jerome Wagner
Is that a sound idea or does it look horrible ? Would it be reasonable

to connect in this way to a

Post by Jerome Wagner
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

Post by Jerome Wagner
inside an application ? Then I would have to implement the protocol

corresponding to this xxxx_fdw

Post by Jerome Wagner
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

[Albe Laurenz]

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.

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