Networking feature for postgresql...

Katsaros Kwn/nos wrote:

Hi,

I'm trying to add a -project specific- networking feature to my postgres
build (or database as function). What I want to do is to send a Query
instance (as a String-retrieved through an SPI function) to other
machines and (after they have executed it) to receive result tuples.
It's about a mediator-wrapper project. My first thought was to write 2
SPI functions (one for the server (concurrent) and the other for client)
but I'm not sure if this is going to work. I'm worried about setting up
the server process running on the background while other SPI calls are
made.

Have you looked at the dblink code?

--
Richard Huxton
Archonet Ltd

Well, actually no :) ! Thanks for the hint!

But just from curiosity, would the scenario I described work?
I mean is it possible for an SPI process to run in the background while
other SPI calls are made?

Ntinos Katsaros

Katsaros Kwn/nos wrote:

Well, actually no :) ! Thanks for the hint!

But just from curiosity, would the scenario I described work?
I mean is it possible for an SPI process to run in the background while
other SPI calls are made?

I don't think so, you're running in a backend process, so you'd need to
fork the backend itself.

On Thu, 2004-10-14 at 11:15, Richard Huxton wrote:

Katsaros Kwn/nos wrote:

Hi,

I'm trying to add a -project specific- networking feature to my postgres
build (or database as function). What I want to do is to send a Query
instance (as a String-retrieved through an SPI function) to other
machines and (after they have executed it) to receive result tuples.
It's about a mediator-wrapper project. My first thought was to write 2
SPI functions (one for the server (concurrent) and the other for client)
but I'm not sure if this is going to work. I'm worried about setting up
the server process running on the background while other SPI calls are
made.

Have you looked at the dblink code?

--
Richard Huxton
Archonet Ltd

I saw your message on the postgresql mailing lists. The TelegraphCQ
project at Berkeley is implemented using the Postgres code base as a
starting point. TelegraphCQ has a generalized mechanism for receiving
data from remote data sources, and also for on demand request-response
style queries to remote data sources. You can get more information
about the project at: http://telegraph.cs.berkeley.edu. A quick
overview of the remote data source features can be found here:
http://telegraph.cs.berkeley.edu/telegraphcq/v0.2/Data_Acquisition.html
I am currently doing preliminary investigations to see how difficult
it would be to integrate access to remote data sources into the
postgres code base.

Thank you very much for this information! I'll definitely take a look at
it.

There is a project in the contrib directory called dblink which may
fit your needs.

Well, I saw it but although it can serve the communication between
db-nodes, it seems it may not be appropriate for what I want to do. Even
though it can be used to send queries and receive results, it blocks
until a transaction is finished (at least I think so). This may be no
problem for other applications but for a mediator-wrapper model with
adaptive/dynamic query processing techniques it is. For example, if it
really blocks (and thats why I asked about concurrent SPI calls & fork)
it is not possible to have multiple queries on wire or monitor the
connection (e.g. data rate etc.).

Excuse me if I make a mistake in what I say above, I'm not the best
programmer :-) !!

Best Regards,
Ntinos Katsaros

PS: Sorry if this mail comes to you for the second time Owen. I have
problems with my SMTP server.

Hi again,

Having taken a look at the dblink code I have some questions:

Having a user defined function, is it possible -with no serious memory
overheads- to fork it (outside SPI calls code) in order to make
concurrent dblink calls? What I'm thinking of doing is to create a
function which opens an SPI session, creates some objects (Query nodes),
closes it and then forks as many times as the number of required dblink
calls (having available the appropriate Query node, something like
for(all nodes) fork();...).

My guess is that it is possible cause the backend calls for dblink are
done on the other side (when speaking for Select statements
only).However the returned tuples can only be merged (to produce the
final result) in main memory cause storing them first (e.g. temp table)
would need concurrent SPI calls.Am I right? If so is there any
mechanism that can multiplex this storing procedures?

On the other side, I suppose a server can serve multiple incoming
queries.

Regards,
Ntinos Katsaros

Katsaros Kwn/nos wrote:

Having taken a look at the dblink code I have some questions:

ISTM that you might start with dblink_record() and modify it to suit
using SPI and asynchronous libpq calls. See:
http://www.postgresql.org/docs/current/static/libpq-async.html

Joe