On Wed, 06 Dec 2006 13:29:37 +0100, Dave Cramer <pg@fastcrypt.com> wrote:

> Unless you specifically ask for it postgresql doesn't lock any rows when
> you update data.
>
Thats not right. UPDATE will force a RowExclusiveLock to rows matching the
WHERE clause, or all if no one is specified.
@Joost Kraaijeveld: Yes, because there is no EntryExclusiveLock or
something like that. Roughly you can say, each UPDATE statement iterates
through the affected table and locks the WHERE clause matching records
(rows) exclusivly to prevent data inconsistancy during the UPDATE. After
that your rows will be updated and the lock will be repealed.
You can see this during an long lasting UPDATE by querying the pg_locks
with joined pg_stats_activity (statement must be enabled).

> Dave
> On 6-Dec-06, at 2:04 AM, Joost Kraaijeveld wrote:
>
>> Does PostgreSQL lock the entire row in a table if I update only 1
>> column?
>>
>>
>> --Groeten,
>>
>> Joost Kraaijeveld
>> Askesis B.V.
>> Molukkenstraat 14
>> 6524NB Nijmegen
>> tel: 024-3888063 / 06-51855277
>> fax: 024-3608416
>> web: www.askesis.nl
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match



CU,
Jens

--
**
Jens Schipkowski

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Hi,

Dave Cramer wrote:
> Apparently I've completely misunderstood MVCC then....

Probably not. You are both somewhat right.

Jens Schipkowski wrote:
>> Thats not right. UPDATE will force a RowExclusiveLock to rows
>> matching the WHERE clause, or all if no one is specified.

That almost right, RowExclusiveLock is a table level lock. An UPDATE
acquires that, yes. Additionally there are row-level locks, which is
what you're speaking about. An UPDATE gets an exclusive row-level lock
on rows it updates.

Please note however, that these row-level locks only block concurrent
writers, not readers (MVCC lets the readers see the old, unmodified row).

> My understanding
> is that unless you do a select ... for update then update the rows will
> not be locked.

Also almost right, depending on what you mean by 'locked'. A plain
SELECT acquires an ACCESS SHARE lock on the table, but no row-level
locks. Only a SELECT ... FOR UPDATE does row-level locking (shared ones
here...)

The very fine documentation covers that in [1].

Regards

Markus


[1]: PostgreSQL Documentation, Explicit Locking:
http://www.postgresql.org/docs/8.2/i...t-locking.html


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Wed, Dec 06, 2006 at 08:26:45AM -0500, Dave Cramer wrote:
> >>Unless you specifically ask for it postgresql doesn't lock any
> >>rows when you update data.
> >>
> >Thats not right. UPDATE will force a RowExclusiveLock to rows
> >matching the WHERE clause, or all if no one is specified.
>
> Apparently I've completely misunderstood MVCC then.... My
> understanding is that unless you do a select ... for update then
> update the rows will not be locked .

I think it comes down to what you mean by RowExclusiveLock. In MVCC,
writers don't block readers, so even if someone executes an update on a
row, readers (SELECT statements) will not be blocked.

So it's not a lock as such, more a "I've updated this row, go find the
new version if that's appropriate for your snapshot".

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFdsl3IB7bNG8LQkwRAnwWAKCDKqlHPYIVPoo5n8LcIR 1jXewWjwCfbu/8
Wt5bWPvcWOy16oESM/dGgTY=
=53TX
-----END PGP SIGNATURE-----

On 6-Dec-06, at 8:20 AM, Jens Schipkowski wrote:

> On Wed, 06 Dec 2006 13:29:37 +0100, Dave Cramer <pg@fastcrypt.com>
> wrote:
>
>> Unless you specifically ask for it postgresql doesn't lock any
>> rows when you update data.
>>
> Thats not right. UPDATE will force a RowExclusiveLock to rows
> matching the WHERE clause, or all if no one is specified.
> @Joost Kraaijeveld: Yes, because there is no EntryExclusiveLock or
> something like that. Roughly you can say, each UPDATE statement
> iterates through the affected table and locks the WHERE clause
> matching records (rows) exclusivly to prevent data inconsistancy
> during the UPDATE. After that your rows will be updated and the
> lock will be repealed.
> You can see this during an long lasting UPDATE by querying the
> pg_locks with joined pg_stats_activity (statement must be enabled).

Apparently I've completely misunderstood MVCC then.... My
understanding is that unless you do a select ... for update then
update the rows will not be locked .

Dave
>
>> Dave
>> On 6-Dec-06, at 2:04 AM, Joost Kraaijeveld wrote:
>>
>>> Does PostgreSQL lock the entire row in a table if I update only 1
>>> column?
>>>
>>>
>>> --Groeten,
>>>
>>> Joost Kraaijeveld
>>> Askesis B.V.
>>> Molukkenstraat 14
>>> 6524NB Nijmegen
>>> tel: 024-3888063 / 06-51855277
>>> fax: 024-3608416
>>> web: www.askesis.nl
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>> choose an index scan if your joining column's datatypes do
>>> not
>>> match
>>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>
>
>
> CU,
> Jens
>
> --
> **
> Jens Schipkowski
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Wed, Dec 06, 2006 at 08:26:45AM -0500, Dave Cramer wrote:
> Apparently I've completely misunderstood MVCC then.... My
> understanding is that unless you do a select ... for update then
> update the rows will not be locked .

The discussion was about updates, not selects. Selects do not in general lock
(except for ... for update, as you say).

To (partially) answer the original question: The number of columns updated
does not matter for the locking situation.

/* Steinar */
--
Homepage: http://www.sesse.net/

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings