pg_net background worker never starts

[psteinroe]

Bug report

It seems like the pg_net worker never starts.

create extension pg_net;
select
    net.http_post(
            url:='https://httpbin.org/post',
            body:='{"hello": "world"}'::jsonb
        ) as request_id; 

returns the pg_net background worker must be up when doing requests on a fresh supabase/postgres:14.1.0.

To Reproduce

• supabase init
• supabase start
• go to sql editor in studio and run the code provided above.

Expected behavior

HTTP request should be executed successfully.

System information

• OS: macOs
• supabase cli version 0.15.15
• supabase db image 14.1.0

[soedirgo]

If you run it directly like:

docker run --rm -it -e POSTGRES_PASSWORD=postgres -p 5432:5432 supabase/postgres:14.1.0 postgres -c config_file=/etc/postgresql/postgresql.conf

the problem doesn't surface. So this looks like a CLI issue.

[psteinroe]

Thanks for looking into it! For now, simply restarting the db container after supabase start works.

[soedirgo]

Ah, interesting - I couldn't figure out what the issue was. I guess we could just do a restart in the supabase start code for now.

[psteinroe]

That should fix the issue for now. The same problem arises with supabase db reset, so a restart should be put there, too.

I am still wondering what the root cause is, it seems very odd that this only happens if the container is started with the CLI. Maybe some problem with the docker client?

[plmwd]

I've run into this issue too, but running supabase db reset and the docker command above doesn't work. However, it works on the remote db.

This is the error that I'm receiving.

[2022-04-05 17:13:18] Detail: the pg_net background worker is down due to an internal error and cannot process requests
[2022-04-05 17:13:18] Hint: make sure that you didn't modify any of pg_net internal tables or used them for foreign key references
[2022-04-05 17:13:18] Where: PL/pgSQL function _check_worker_is_up() line 4 at RAISE
[2022-04-05 17:13:18] SQL statement "insert into net.http_request_queue(method, url, headers, body, timeout_milliseconds)
[2022-04-05 17:13:18] values (
[2022-04-05 17:13:18] 'POST',
[2022-04-05 17:13:18] net._encode_url_with_params_array(url, params_array),
[2022-04-05 17:13:18] headers,
[2022-04-05 17:13:18] convert_to(body::text, 'UTF8'),
[2022-04-05 17:13:18] timeout_milliseconds
[2022-04-05 17:13:18] )
[2022-04-05 17:13:18] returning id"
[2022-04-05 17:13:18] PL/pgSQL function http_post(text,jsonb,jsonb,jsonb,integer) line 42 at SQL statement```

[sweatybridge]

This is fixed since v1.4.7 as we restart the db container after initialisation. The underlying issue was that we rely on pg_terminate_backend when switching/restoring databases and that closes all background worker connections. Other extensions like pg_cron had similar issues.

https://github.com/supabase/cli/pull/465