Re: [LIKELY_SPAM]Thoughts about bug #3883

I suggest make a distinction between DDL and DML locks.  A DDL lock would be
required for a TRUNCATE, CREATE, ALTER, DROP, REPLACE, etc while DML is just
insert, update, and delete.

A TRUNCATE (or any DDL activity) should wait until all DML activity is
committed before it can acquire an exclusive lock, perform the task, and
then release the lock.   

This would ensure a consistent view of the database structure.  

Of course, I'm speaking in terms of how I would like to see it and not
knowing the guts of postgres so feel free to bash my ideas.


Jon
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Monday, January 21, 2008 5:01 PM
> To: pgsql-hackers@postgreSQL.org
> Subject: [LIKELY_SPAM][HACKERS] 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster