
> >> I am working on eliminating the "relation NNN modified while in use"
> >> misfeature by instead grabbing a lock on each relation at first use
> >> in a statement, and holding that lock till end of transaction. 
> 
> > As anticipated, I object :-) 
> 
> Your objection is founded on two misunderstandings.  In the first place,
> we are *always* inside a transaction when executing a query. It may be
> an implicit one-statement transaction, but it's still a transaction.

Ok, but I thought there was some optimization for readonly statements.

> In the second place, we already grab locks that we do not release till
> end of xact for all user-level queries.  The problem is that we grab
> them too late, ie, in the executor.  I'm just planning to move up the
> grab till first use.

For a "select  colname from tablename" we do not currently hold any lock 
until end of tx. This is the situation you described, and I am worried about.

Andreas

Tue, 7 Nov 2000 18:29:05 +0100 
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
AW: AW: Issue NOTICE for attempt to raise lock level? 
Tom Lane wrote:
> I am not nearly as worried about long-running transactions that delay
> admin actions as I am about admin actions that crash other transactions.
> I do not believe it is safe to drop read locks intra-transaction, and
> I am unwilling to take a chance on it being safe so close to 7.1 beta.

Will we still have readers-dont-block-writers behaviour?
i.e. will the read lock lock only rows visible to current transaction ?
Or am i talking about something completely different ?

-------------
Hannu

Tue, 07 Nov 2000 19:41:02 +0200
Hannu Krosing <hannu@tm.ee>
Re: AW: AW: Issue NOTICE for attempt to raise lock level?
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
> Ok, but I thought there was some optimization for readonly statements.

Doesn't have anything to do with locking, only with avoiding disk
writes.

>> In the second place, we already grab locks that we do not release till
>> end of xact for all user-level queries.  The problem is that we grab
>> them too late, ie, in the executor.  I'm just planning to move up the
>> grab till first use.

> For a "select  colname from tablename" we do not currently hold any lock 
> until end of tx. This is the situation you described, and I am worried about.

That's a bug in itself, because the executor's read lock is grabbed by
heap_beginscan and released by heap_endscan, which means it may be
grabbed and released multiple times during a single query (think
nested-loop join).  There is nothing to stop someone from, say, dropping
the entire table between scans.  I intend to fix that.

I am not nearly as worried about long-running transactions that delay
admin actions as I am about admin actions that crash other transactions.
I do not believe it is safe to drop read locks intra-transaction, and
I am unwilling to take a chance on it being safe so close to 7.1 beta.
We can argue about it when 7.2 development cycle starts, if you like.

			regards, tom lane

Tue, 07 Nov 2000 12:56:46 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: AW: Issue NOTICE for attempt to raise lock level? 
Hannu Krosing <hannu@tm.ee> writes:
> Tom Lane wrote:
>> I do not believe it is safe to drop read locks intra-transaction, and
>> I am unwilling to take a chance on it being safe so close to 7.1 beta.

> Will we still have readers-dont-block-writers behaviour?

Sure.  The only thing this really affects is VACUUM and schema-altering
commands, which will now have to wait until reader transactions commit.
In other words

	Session 1			Session 2

	BEGIN;
	SELECT * FROM foo;

					ALTER TABLE foo ...

	...

	COMMIT;

Session 2 will have to wait for session 1 to commit; before it didn't.
An example of why this is a good idea is

	Session 1			Session 2

	BEGIN;
	DECLARE c CURSOR FOR
	    SELECT * FROM foo;

					ALTER TABLE foo ...

	FETCH FROM c;

	COMMIT;

Without a held read lock on foo, session 1 is in deep trouble,
because its cursor is no longer correctly planned.

			regards, tom lane

Tue, 07 Nov 2000 14:35:45 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: AW: Issue NOTICE for attempt to raise lock level? 