Re: Strang behaviour SELECT ... LIMIT n FOR UPDATE

> De : Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>
> "Daniel Caune" <daniel(dot)caune(at)ubisoft(dot)com> writes:
> > I did the following test, removing all the where-clause from the SELECT
> statement. Every statement completes immediately, i.e. it doesn't block.
>
> I think you left out some critical information, like who else was doing
> what to the table.
>
> What it looks like to me is that the third and fourth rows in this view
> were live according to your transaction snapshot, but were committed
> dead as of current time, and so FOR UPDATE wouldn't return them.
>
> > agoratokens=> select id from "Tokens" where id IN (47, 104, 44, 42)
> limit 3 for update;
> > This time, the statement returns the row where id equals to 44.
>
> No, it returns *some* row where id equals 44. Not necessarily the same
> one seen in the seqscan. (I imagine this query is using an index, and
> so would visit rows in a different physical order.) Printing the ctid
> of the rows would confirm or disprove that theory.
>
> regards, tom lane

Thanks Tom. I think this time you will point me out the problem. The column id has a primary key constraint on. There should not be more than one row with id equals to 44.

agoratokens=> \d "Tokens"
Table "public.Tokens"
Column | Type | Modifiers
-----------+--------------------------------+-------------------------------------------------------
id | integer | not null default nextval('"Tokens_id_seq"'::regclass)
type | integer | not null
value | character varying(255) | not null
isLocked | boolean | not null default false
timestamp | timestamp(6) without time zone |
Indexes:
"Tokens_pkey" PRIMARY KEY, btree (id)
(...)

agoratokens=> select ctid, * from "Tokens" where "isLocked" = true limit 3 for update;
ctid | id | type | value | isLocked | timestamp
------+----+------+-------+----------+-----------
(0 rows)

agoratokens=> select ctid, * from "Tokens" where "isLocked" = true limit 3;
ctid | id | type | value | isLocked | timestamp
-----------+----+------+-------+----------+---------------------------
(199,84) | 44 | 3 | 3 | t | 2007-04-03 12:12:02.46944
(199,114) | 42 | 3 | 1 | t | 2007-04-03 13:00:44.877
(2 rows)

agoratokens=> select ctid, * from "Tokens" where id = 44;
ctid | id | type | value | isLocked | timestamp
-----------+----+------+-------+----------+----------------------------
(3702,85) | 44 | 3 | 3 | f | 2007-11-22 16:41:33.494371
(1 row)

agoratokens=> select count(*) from "Tokens" where id = 44;
count
-------
1
(1 row)

It seems that, in certain condition, row (199,84) is shadowing row (3702,85); my feeling from a "customer" high level. Indeed, as a PostgreSQL core developer, that assertion could make you laugh... :-)

I took into account your point about the concurrent context. Therefore I isolated the database from any connection except mine.

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all all trust
host all all 127.0.0.1 255.255.255.0 password
#host all all 10.3.41.0 255.255.254.0 password

sudo /etc/init.d/postgresql-8.1 restart
* Restarting PostgreSQL 8.1 database server [ ok ]

No other client than my psql was connected to PostgreSQL. You can trust me. The result is exactly the same:

agoratokens=> select ctid, * from "Tokens" where "isLocked" = true limit 3;
ctid | id | type | value | isLocked | timestamp
-----------+----+------+-------+----------+---------------------------
(199,84) | 44 | 3 | 3 | t | 2007-04-03 12:12:02.46944
(199,114) | 42 | 3 | 1 | t | 2007-04-03 13:00:44.877
(2 rows)

agoratokens=> select ctid, * from "Tokens" where id = 44;
ctid | id | type | value | isLocked | timestamp
-----------+----+------+-------+----------+----------------------------
(3702,85) | 44 | 3 | 3 | f | 2007-11-22 16:41:33.494371
(1 row)

agoratokens=> select ctid, * from "Tokens" where "isLocked" = true limit 3;
ctid | id | type | value | isLocked | timestamp
-----------+----+------+-------+----------+---------------------------
(199,84) | 44 | 3 | 3 | t | 2007-04-03 12:12:02.46944
(199,114) | 42 | 3 | 1 | t | 2007-04-03 13:00:44.877
(2 rows)

agoratokens=> select ctid, * from "Tokens" where id = 44;
ctid | id | type | value | isLocked | timestamp
-----------+----+------+-------+----------+----------------------------
(3702,85) | 44 | 3 | 3 | f | 2007-11-22 16:41:33.494371
(1 row)

agoratokens=> select count(*) from "Tokens" where id = 44;
count
-------
1
(1 row)

By the way, according to the "business logic", the timestamp "2007-04-03 12:12:02.46944" is weird, because too old. I apologize if my question is stupid because of my knowledge lack, but would it possible that for some reasons the related SELECT statement uses an old snapshot?

Regards,

--
Daniel