Proper cleanup at backend exit - Mailing list pgsql-hackers

I have been looking into the problem reported by Massimo, and also seen
by me, that backends sometimes exit without cleaning up entries they
made in the pg_listener table.  I have fixed several contributing bugs
(having to do with the limited size of the on_shmem_exit table), but
I still see the problem occasionally.

I just discovered one fairly fundamental reason why there's still a
problem:

    test=> listen test;
    LISTEN
    test=> begin;
    BEGIN
    test=> \q
    $

    (start new psql)

    test=> select * from pg_listener;
    relname|listenerpid|notification
    -------+-----------+------------
    test   |      20024|           0
    (1 row)

Oops.  It would seem that async.c is *trying* to clean up, but the
changes are thrown away because they are considered part of a
never-committed transaction :-(.  More generally, I believe that
a backend killed in the middle of any individual SQL statement will
fail to remove its LISTEN entries, because it will think that any
database changes made just before exit ought to be discarded.

If we want a backend to clean out its pg_listen entries at shutdown,
it looks to me like backend exit will have to work like this:

1. Abort the current transaction, if any, and exit the current
   transaction block, if any.

2. Perform LISTEN cleanup within a new transaction.  Commit this
   transaction.

3. Continue with closing up shop.

I don't know whether there are any other things besides LISTEN cleanup
that really need to be done, not discarded, during backend exit.  If
there are, then we probably ought to restructure the exit sequence to
handle this cleanly.  But I'm worried about doing this much processing
during backend exit --- if you're trying to kill a backend that's gone
wrong, the last thing you want is for it to start doing new
transactions.

(BTW, that last thought also says that we *still* won't be able to get
rid of pg_listen entries 100% reliably.  In a panic exit scenario,
ie after one backend has crashed, none of the backends will clean up
their pg_listen entries.  We dare not tamper with that.  At least
I dare not.)

I'm half tempted to say that we should rip out the cleanup code and
not even try to remove pg_listener entries at backend exit.  They'll
go away anyway over time, because the next notifier to try to notify
that relation name will delete the entries when he discovers he can't
signal that backend PID anymore.  So cleanup-at-exit is not at all a
critical function, and it's hard to justify a bunch of new complexity
just for this.

Comments?  This depends a lot on whether you think LISTEN cleanup
is a unique case, or whether there will be other standard cleanup
activities that a backend ought to perform.  (Is there such a thing
as an ON EXIT hook in SQL?)

            regards, tom lane