dblink surprise

[Torsten Förtsch]

Hi,

according to the documentation, dblink_send_query sends the query to be
executed asynchronously. I tried this out in the following function and it
works as expected:

CREATE OR REPLACE FUNCTION t_par () RETURNS TABLE (
    tx_time TIMESTAMP,
    end_time TIMESTAMP
) AS $def$
DECLARE
    v_q RECORD;
BEGIN
    FOR v_q IN
        WITH jobs(cn) AS (
            VALUES ('c1'), ('c2')
        )
        , conn AS (
            SELECT *, 1/(dblink_connect(cn, 'dbname=postgres
port=5440')='OK')::INT AS connstatus
              FROM jobs
        )
        SELECT conn.*, 1/q.status AS sendstatus
          FROM conn
         CROSS JOIN LATERAL dblink_send_query(conn.cn,
               $$
                   select now(), pg_sleep(3), clock_timestamp()
               $$) q(status)
    LOOP
        RETURN QUERY
        SELECT tb.tx_time, tb.end_time
          FROM dblink_get_result(v_q.cn) tb(
                   tx_time TIMESTAMP,
                   dummy TEXT,
                   end_time TIMESTAMP
               );
        PERFORM dblink_disconnect(v_q.cn);
    END LOOP;
END
$def$ LANGUAGE plpgsql;

# select * from t_par();
          tx_time           |          end_time
----------------------------+----------------------------
 2017-11-21 21:14:35.593741 | 2017-11-21 21:14:38.597384
 2017-11-21 21:14:35.595805 | 2017-11-21 21:14:38.599272

Both remote queries start at the same time and finish 3 seconds later.

Then I thought I can take it one step further and collect the results in
the same statement:

CREATE OR REPLACE FUNCTION t_seq () RETURNS TABLE (
    tx_time TIMESTAMP,
    end_time TIMESTAMP
) AS $def$
BEGIN
    RETURN QUERY
    WITH v_q AS (
        WITH jobs(cn) AS (
            VALUES ('c1'), ('c2')
        )
        , conn AS (
            SELECT *, 1/(dblink_connect(cn, 'dbname=postgres
port=5440')='OK')::INT AS connstatus
              FROM jobs
        )
        SELECT conn.*, 1/q.status AS sendstatus
          FROM conn
         CROSS JOIN LATERAL dblink_send_query(conn.cn,
               $$
                   select now(), pg_sleep(3), clock_timestamp()
               $$) q(status)
    )
    SELECT tb.tx_time, tb.end_time
      FROM v_q
     CROSS JOIN LATERAL dblink_get_result(v_q.cn) tb(
               tx_time TIMESTAMP,
               dummy TEXT,
               end_time TIMESTAMP
           );
    PERFORM dblink_disconnect(jobs.cn)
       FROM (VALUES ('c1'), ('c2')) jobs(cn);
END
$def$ LANGUAGE plpgsql;

regentmarkets=# select * from t_seq();
          tx_time           |          end_time
----------------------------+----------------------------
 2017-11-21 21:25:07.764467 | 2017-11-21 21:25:10.768032
 2017-11-21 21:25:10.770409 | 2017-11-21 21:25:13.773907

Unfortunately, that does not work. The remote queries are started one after
the other.

Why?

This is PG 9.6.

BTW, is it somehow possible in plpgsql to catch a query cancellation or
backend termination request? That would be useful to propagate such
requests to remote queries.

Thanks,
Torsten