On Wed, Jun 29, 2011 at 12:37 PM, Marinos Yannikos <m...@geizhals.at> wrote:
> On Wed, 29 Jun 2011 13:55:58 +0200, Svetlin Manavski < > svetlin.manav...@gmail.com> wrote: > > Question: Is there a way to get the same result from within a PL/pgSQL >> function but running all the sub-queries in parallel? In case it is not >> directly available, which one would be the simplest way to implement it in >> my application? (I am very keen to avoid the obvious solution of an >> additional multi-threaded layer which would do it out of the RDBMS) >> > > Have you tried dblink_send_query() + dblink_get_results() yet? > > http://www.postgresql.org/**docs/current/static/contrib-** > dblink-send-query.html<http://www.postgresql.org/docs/current/static/contrib-dblink-send-query.html> > > You'd have to do something like this to your queries [untested]: > > select dblink_send_query('remote1','**select * from > > appqosfe.F_total_utilization(**1306918800000000000::INT8, NULL, > 60000000000::INT8, NULL)'); > > (select * from appqosfe.F_total_utilization(**1306918800000000000::INT8, > NULL, > 60000000000::INT8, NULL)) > UNION ALL > (SELECT * from dblink_get_result('remote1') as T1(detectroid numeric, > timegroup numeric, > numbytes numeric, numpackets numeric)) > order by timegroup asc; > > i.e. start your remote query/-ies asynchronously, then collect the results > in the UNION query. At least in theory it should work... > > This does work however you'll need to add a little more to it to ensure your UNION succeeds. In pseudo... connection #1: CREATE TABLE target_1 ... BEGIN; LOCK TABLE target_1 IN ACCESS EXCLUSIVE MODE; INSERT INTO target_1 SELECT ... COMMIT; connection #2: CREATE TABLE target_2 ... BEGIN; LOCK TABLE target_2 IN ACCESS EXCLUSIVE MODE; INSERT INTO target_2 SELECT ... COMMIT; connection #3: SELECT * FROM target_1 UNION SELECT * FROM target_2; Connections 1 and 2 can be done in simultaneously and after both have reached the LOCK statement then the SELECT on connection 3 can be executed. Same fundamentals if all three connections are to different databases and connection 3 uses dblink to pull the data. Another alternative is to use GridSQL. I haven't used it myself but seen it in action on a large install with 4 backend databases. Pretty slick. Greg
