Re: Select max(id) causes AccessExclusiveLock?

Em 28/04/2014 12:01, Tom Lane escreveu:
> Edson Richter  writes:
>> When I issue a
>> select max(id) from MyTableName
>> Postgres 9.2.7 is scaling a AccessExclusiveLock and causing large delays.
> Really?
>
> regression=# create table MyTableName (
> regression(#    id integer not null primary key,
> regression(#    seqYear char(4),
> regression(#    seqCount integer);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mytablename_pkey" for table "mytablename"
> CREATE TABLE
> regression=# begin;
> BEGIN
> regression=# select max(id) from MyTableName;
>   max
> -----
>      
> (1 row)
>
> regression=# select * from pg_locks;
>    locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |      mode       | granted | fastpath
> ------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------
>   relation   |    16384 |    11069 |      |       |            |               |         |       |          | 2/120853           | 12432 | AccessShareLock | t       | t
>   relation   |    16384 |    42142 |      |       |            |               |         |       |          | 2/120853           | 12432 | AccessShareLock | t       | t
>   relation   |    16384 |    42139 |      |       |            |               |         |       |          | 2/120853           | 12432 | AccessShareLock | t       | t
>   virtualxid |          |          |      |       | 2/120853   |               |         |       |          | 2/120853           | 12432 | ExclusiveLock   | t       | t
> (4 rows)
>
> I see nothing higher than AccessShareLock on the table.
>
> 			regards, tom lane
>
>

Really big sorry!!!

The programmer added a


LOCK TABLE MyTableName


just before issuing the select max(id) from MyTableName.


I do suspect this is the case, right?

Really sorry, I should have look into the code before asking!

Thanks for your (always) fast support.

Regards,


Edson