Thoughts about bug #3883

Steven Flatt's report in this thread:
http://archives.postgresql.org/pgsql-bugs/2008-01/msg00138.php
exposes two more-or-less-independent flaws.

One problem is that we allow operations like TRUNCATE on tables that are
open in the current backend. This poses a risk of strange behavior,
such as

regression=# create table foo as select x from generate_series(1,1000) x;
SELECT
regression=# begin;
BEGIN
regression=# declare c cursor for select * from foo;
DECLARE CURSOR
regression=# fetch 10 from c;
x
----
1
2
3
4
5
6
7
8
9
10
(10 rows)

regression=# truncate foo;
TRUNCATE TABLE
regression=# fetch 10 from c;
x
----
11
12
13
14
15
16
17
18
19
20
(10 rows)

regression=# fetch all from c;
ERROR: could not read block 1 of relation 1663/133283/156727: read only 0 of 8192 bytes

It's not too consistent that we could still read rows from c until we
needed to fetch the next page of the table. For more complex queries
involving indexscans, I'm afraid the behavior could be even more
bizarre.

What I propose we do about this is put the same check into TRUNCATE,
CLUSTER, and REINDEX that is already in ALTER TABLE, namely that we
reject the command if the current transaction is already holding
the table open.

The issue Steven directly complained of is a potential for undetected
deadlock via LockBufferForCleanup. Ordinarily, buffer-level locks don't
pose a deadlock risk because we don't hold one while trying to acquire
another (except in UPDATE, which uses an ordering rule to avoid the
risk). The problem with LockBufferForCleanup is that it can be blocked
by a mere pin, which another backend could well hold while trying to
acquire a lock that will be blocked by VACUUM.

There are a couple of migitating factors: first, patching TRUNCATE et al
as suggested above will prevent the immediate case, and second, as of
8.3 this isn't a problem for autovacuum because of the facility for
kicking autovacuum off a table if it's blocking someone else's lock
request. Still, undetected deadlocks are unpleasant, so it'd be nice
to have some way to recognize the situation if we do get into it.
I have no idea about a reasonable way to do that though. Getting the
heavyweight lock manager involved in buffer accesses seems right out on
performance grounds.

Comments, ideas?

regards, tom lane