Re: LISTEN/NOTIFY bug: VACUUM sets frozenxid past a xid in async queue

I came across this discussion after we had this problem at least twice
now at work.
I read through the discussion to see if this is the problem we are
experiencing.
At first, I thought this was not exactly our problem, because of the way
it was showed to be reproduced.

With a long running transaction that is listening, and then doing all
the other steps like like running through transactions until a second
pg_xact file is created, and then vacuuming etc etc...

But, I felt like we don't do this, so it cannot be the way we run into
the problem. Specifically, having a listener as a long running transaction.

So I set out to get it into the broken state, and here is how I can
reproduce it without long running queries:

1. Create another database

postgres=# CREATE DATABASE test;
CREATE DATABASE

2. Create a listener on the postgres database.

postgres=# LISTEN xx;
LISTEN

3. Create notifies for the test database.

Here I struggled to get a stuck notification in the queue (SELECT
pg_notification_queue_usage();).

It can happen with only a single notify from a  psql connection, but I
get a higher hit rate with the following:

Create a notify.sql with the only contents being:

```
NOTIFY s, 'yappers';
```
Then run this against the test database with pgbench.

pgbench -n -c 80 -j 20 -t 1000 -f notify.sql test

4. Confirm that there is now something stuck in the queue:

postgres=# SELECT pg_notification_queue_usage();
 pg_notification_queue_usage
-----------------------------
         9.5367431640625e-07
(1 row)

If this still shows 0, then run step 3 again.

5. Consume xid's. I create a file consume.sql with the only contents being:
```
SELECT txid_current();
```

Then:

pgbench -n -c 80 -j 30 -t 100000 -f consume.sql postgres

6. Verify that a new file is created in the pg_xact folder.

If not, just run the previous step again.

7. Run vacuum freeze. (Remember to allow connections to template0
beforehand with `ALTER DATABASE template0 WITH ALLOW_CONNECTIONS true;`)

$ vacuumdb --all --freeze
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template0"
vacuumdb: vacuuming database "template1"
vacuumdb: vacuuming database "test"

8. Connect to the test database and try execute a listener.
test=# LISTEN anything;
ERROR:  could not access status of transaction 81086
DETAIL:  Could not open file "pg_xact/0000": No such file or directory.
test=#

Extra weirdness:

In step 2 create a connection to the test database and LISTEN there on
any channel, even the one you are notifying to:
And you don't have to run anything on that connection, there is no
backend_xmin on that connection, you don't start a transaction nothing,
you just run `LISTEN something;`
Then after step 7, as long as you don't close this connection, you will
not get an error when you try to set up a listener on the test database,
even on a new connection to the test database.

What I'm saying is after step 7 you can have two connections. One to
postgres database with active listener, and one to test database with
active listener.
As long as the postgres one is open the queue is stuck, ie `SELECT
pg_notification_queue_usage();` returns non-zero.
As long as the test database connection is open, new listeners do not
experience the error.

If you close the test database connection, but leave the postgres
connection, from now on any listener you try to create on test database
will error.

If you close the postgres connection, the queue clears immediately and
new LISTEN's on the test database will work.
This means it is possible to get rid of the problem without restarting
the postmaster if you can close connections until you close the one that
"caused" the problem.
Don't know if this is actually useful, but to me it seemed like everyone
believed it to be impossible to recover without restarting, so it's
something at least.

TLDR:
active listener on one database causes notify on another database to get
stuck.
At no point could I get a stuck notify if I don't have a listener on at
least one other database than the one I am notifying on. See the Extra
weirdness section.
At no point do you need to have any other queries running, there is
never an idle in transaction query needed for bad timing with the vacuum.

I hope I explained everything well enough so that one of you smart
people can find and fix the problem.