> De : Tom Lane [mailto:[EMAIL PROTECTED]
>
> "Daniel Caune" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
