Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

listen silently fails on a read replica #24

Closed
steve-chavez opened this issue May 2, 2024 · 1 comment · Fixed by #25
Closed

listen silently fails on a read replica #24

steve-chavez opened this issue May 2, 2024 · 1 comment · Fixed by #25

Comments

@steve-chavez
Copy link

steve-chavez commented May 2, 2024

Read replicas fail on LISTEN:

postgres=# LISTEN pgrst;
ERROR:  cannot execute LISTEN during recovery

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

# Also see https://stackoverflow.com/questions/70293290/can-you-perform-listen-unlisten-on-a-postgresql-read-replica

This currently fails silently on hasql-notifications, due to listen ignoring the error (void):

execListen pqCon = void $ PQ.exec pqCon $ T.encodeUtf8 $ "LISTEN " <> fromPgIdentifier channel

Note: UNLISTEN doesn't fail on the replica, but it would still be good to not ignore the error there too.

@diogob
Copy link
Owner

diogob commented May 2, 2024

thanks for the report @steve-chavez , will fix it.

@diogob diogob linked a pull request May 2, 2024 that will close this issue
@diogob diogob closed this as completed in #25 May 5, 2024
steve-chavez added a commit to steve-chavez/postgrest that referenced this issue May 18, 2024
Update hasql-notifications to include the fix on
diogob/hasql-notifications#24.

Which now reveals the following error:

```
$ postgrest-with-postgresql-16 --replica -f test/spec/fixtures/load.sql postgrest-run

17/May/2024:18:35:38 -0500: Successfully connected to PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 13.2.0, 64-bit
17/May/2024:18:35:38 -0500: Could not listen for notifications on the "pgrst" channel. ERROR:  cannot execute LISTEN during recovery
17/May/2024:18:35:38 -0500: Retrying listening for notifications...
```

This is still not good because the LISTEN channel will be retried
forever without a backoff.
steve-chavez added a commit to steve-chavez/postgrest that referenced this issue May 18, 2024
Update hasql-notifications to include the fix on
diogob/hasql-notifications#24.

Which now reveals the following error:

```
$ postgrest-with-postgresql-16 --replica -f test/spec/fixtures/load.sql postgrest-run

17/May/2024:18:35:38 -0500: Successfully connected to PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 13.2.0, 64-bit
17/May/2024:18:35:38 -0500: Could not listen for notifications on the "pgrst" channel. ERROR:  cannot execute LISTEN during recovery
17/May/2024:18:35:38 -0500: Retrying listening for notifications...
```

This is still not good because the LISTEN channel will be retried
forever without a backoff.
steve-chavez added a commit to steve-chavez/postgrest that referenced this issue May 18, 2024
Update hasql-notifications to include the fix on
diogob/hasql-notifications#24.

Which now reveals the following error:

```
$ postgrest-with-postgresql-16 --replica -f test/spec/fixtures/load.sql postgrest-run

17/May/2024:18:35:38 -0500: Successfully connected to PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 13.2.0, 64-bit
17/May/2024:18:35:38 -0500: Could not listen for notifications on the "pgrst" channel. ERROR:  cannot execute LISTEN during recovery
17/May/2024:18:35:38 -0500: Retrying listening for notifications...
```

This is still not good because the LISTEN channel will be retried
forever without a backoff.
steve-chavez added a commit to PostgREST/postgrest that referenced this issue May 19, 2024
Update hasql-notifications to include the fix on
diogob/hasql-notifications#24.

Which now reveals the following error:

```
$ postgrest-with-postgresql-16 --replica -f test/spec/fixtures/load.sql postgrest-run

17/May/2024:18:35:38 -0500: Successfully connected to PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 13.2.0, 64-bit
17/May/2024:18:35:38 -0500: Could not listen for notifications on the "pgrst" channel. ERROR:  cannot execute LISTEN during recovery
17/May/2024:18:35:38 -0500: Retrying listening for notifications...
```

This is still not good because the LISTEN channel will be retried
forever without a backoff.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants