Re: Strang behaviour SELECT ... LIMIT n FOR UPDATE - Mailing list pgsql-sql

> De : Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
> "Daniel Caune" <daniel.caune@ubisoft.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.
Thereshould 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 nullvalue     | character varying(255)         | not nullisLocked  | boolean                        | not null
defaultfalsetimestamp | 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-0312: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-2216: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
exceptmine. 

# 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-0312: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-2216: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-0312: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-2216: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
apologizeif my question is stupid because of my knowledge lack, but would it possible that for some reasons the related
SELECTstatement uses an old snapshot? 

Regards,

--
Daniel