Re: dblink query interruptibility

On 11/22/23 2:29 AM, Noah Misch wrote:
> Something as simple as the following doesn't respond to cancellation. In
> v15+, any DROP DATABASE will hang as long as it's running:

Hi,

One of our customers ran into this bug when upgrading from PostgreSQL 14
to PostgreSQL 16. Your commit[1] fixed this issue in PostgreSQL 17 but
the bugfix was not backported with the explanation below.

> Code inspection identified the bug at least thirteen years ago, but
user complaints have not appeared. Hence, no back-patch for now.

But that is as far as I can tell not the case because at least for
CREATE DATABASE the bug was introduced in a commit[2] in PostgeSQL 15.
And now that we actually have a user complaint what do you think about
backporting the fix?

The patch seems small and relatively safe to backport and the functions
have had no bugfixes as far as I could see. And I did a quick git
cherry-pick myself on top of PG 16 (see attached patch) and the only
conflict was related to the introduction of custom wait events which was
easy to fix.

Here is a small snippet which reproduces the bug. It hangs reliably on
PostgreSQL 15 and 16, but not 14, 17 or HEAD.

CREATE EXTENSION dblink;

CREATE FUNCTION f(text, int, text) RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
PERFORM dblink_connect(format('host=%s port=%s user=%s
dbname=postgres', $1, $2, $3));
RAISE NOTICE 'dblink connected!';
PERFORM dblink_exec('DROP DATABASE test');
RAISE NOTICE 'Database dropped!';
PERFORM dblink_disconnect();
RAISE NOTICE 'dblink disconnected!';
END
$$;

CREATE DATABASE test;
SELECT f(:'HOST', :PORT, :'USER');

Andreas

1.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=d3c5f37dd543498cc7c678815d3921823beec9e9
2.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e2f65f42555ff531c6d7c8f151526b4ef7c016f8