dblink surprise
| От | Torsten Förtsch |
|---|---|
| Тема | dblink surprise |
| Дата | |
| Msg-id | CAKkG4_=QNnScB+2z4JAS89X0TdWQCeU4OgQy=vjQ9=hnGvnvVw@mail.gmail.com обсуждение исходный текст |
| Список | pgsql-general |
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
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
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
В списке pgsql-general по дате отправления: