Re: Select max(id) causes AccessExclusiveLock?

Em 28/04/2014 12:01, Tom Lane escreveu:
> Edson Richter <edsonrichter@hotmail.com> 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