Postgres Pro
Downloads
Home   >   mailing lists

Thread: Select max(id) causes AccessExclusiveLock?

Select max(id) causes AccessExclusiveLock?

From
Edson Richter
Date:
On table

create table MyTableName (
   id integer not null primary key,
   seqYear char(4),
   seqCount integer,
   ...)

Where id is primary key and (seqYear, seqCount) is a unique index.
Table facts: about 40 fields, 1 million records.

When I issue a

select max(id) from MyTableName


Postgres 9.2.7 is scaling a AccessExclusiveLock and causing large delays.

Is that expected? Is there a way to avoid the AccessExclusiveLock?

Thanks,

Edson

Re: Select max(id) causes AccessExclusiveLock?

From
Tom Lane
Date:
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

Re: Select max(id) causes AccessExclusiveLock?

From
Edson Richter
Date:
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

Re: Select max(id) causes AccessExclusiveLock?

From
Stephen Frost
Date:
Edson,

* Edson Richter (edsonrichter@hotmail.com) wrote:
> The programmer added a
>
> LOCK TABLE MyTableName
>
> just before issuing the select max(id) from MyTableName.
>
> I do suspect this is the case, right?

Yup, that'll do it.

http://www.postgresql.org/docs/9.3/static/sql-lock.html

    Thanks,

        Stephen
Attachment