dblink surprise

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