Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?

2013-01-13 Thread 李海龙 
at 2013-01-12 02:21, Tom Lane wrote:

=?gb2312?B?wO66o8H6?= hailong...@qunar.commailto:hailong...@qunar.com 
writes:


This time I will give you the contents of the table route_raw, the download 
link is https://www.box.com/s/yxa4yxo6rcb3dzeaefmz or  
http://dl.dropbox.com/u/203288/route_raw_spgist.sql.tar.gz .


Thanks for the data, but I still can't reproduce the problem :-(.

Can you confirm whether loading this dump into an empty database and
running your test case (15 instances of that script) fails for you?

regards, tom lane


Yes,I do confirm that. I consider to try to do further test in order to 
reproduce it again in some way you can reproduce it simply.


Thanks again

Best Regards!

Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?

2013-01-11 Thread Tom Lane 
=?gb2312?B?wO66o8H6?= hailong...@qunar.com writes:
 This time I will give you the contents of the table route_raw, the download 
 link is https://www.box.com/s/yxa4yxo6rcb3dzeaefmz or  
 http://dl.dropbox.com/u/203288/route_raw_spgist.sql.tar.gz .

Thanks for the data, but I still can't reproduce the problem :-(.

Can you confirm whether loading this dump into an empty database and
running your test case (15 instances of that script) fails for you?

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?

2013-01-10 Thread 李海龙 
Hi,dear tom lane  pgsql-hackers

In your last e-mail, you want me to think anbout  the only  spgist-indexed 
column and give you some more further information.

This time I will give you the contents of the table route_raw, the download 
link is https://www.box.com/s/yxa4yxo6rcb3dzeaefmz or  
http://dl.dropbox.com/u/203288/route_raw_spgist.sql.tar.gz .



OS Version:
CentOS release 5.5 (Final)

PostgreSQL Version:
postgres=# select version();
version
---
 PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 
20080704 (Red Hat 4.1.2-50), 64-bit
(1 row)

I create a database named test_spgist,and there is only one table named 
route_raw which only has a id (primary key ) column and a spgist index column 
in the database.

Some details is shown as following:


test_spgist=# \dt
   List of relations
 Schema |   Name| Type  |  Owner
+---+---+--
 public | route_raw | table | postgres
(1 row)

test_spgist=# \d route_raw
   Table public.route_raw
  Column   |  Type   |   Modifiers
---+-+
 sourceurl | character varying(1000) | not null default ''::character varying
 id| integer | not null default 
nextval('route_raw_id_seq'::regclass)
Indexes:
route_raw_pkey PRIMARY KEY, btree (id)
route_raw_sourceurl_idx spgist (sourceurl)

--Before the test, this table has 997736 records.
test_spgist=# select count(1) from route_raw ;
 count

 997736
(1 row)

1. edit a shell script that function is insert data to table route_raw

$ cat /tmp/insert_spgist.sh
#/bin/bash

PSQL=/opt/pg92/bin/psql
DB=test_spgist
USR=postgres

while [ 10 -ne 9 ]
do
SQL=insert into route_raw(sourceurl) values 
('http://suz.bacts.com/group/group_33_128972.aspx , 
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6xl=12623
 , 
http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx,
 
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723xl=7096
 , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625Axl=7957 , 
http://www.ctszj.com.cn/route_group_1_134782.html , 
http://fj.bacts.com/group/group_33_113288.asp ,  
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625Axl=7957, 
http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.bacts.com/group/group_33_113288.aspx
 ,http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883ctype=1 , 
http://www.ctszj.com.cn/route_group_1_134695.html ')
sleep 1
$PSQL  -U $USR -d $DB -c $SQL
done

2. I create 15 clients and execute this shell script in each client

$ /tmp/insert_spgist.sh /dev/null
...
...
...
]$ /tmp/insert_spgist.sh /dev/null

3. only 1 connection to update the table

$ psql -U postgres
psql (9.2.2)
Type help for help.

postgres=# \c test_spgist
You are now connected to database test_spgist as user postgres.

test_spgist=# update route_raw set sourceurl = 
'http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=CGZL120701MDG05LAxl=13935'wherehttp://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=CGZL120701MDG05LAxl=13935%27where
 sourceurl like '%=CGZL120701MDG0%';


4. I execute the statement select * from pg_stat_activity; and select count(1) 
from route_raw;  to monitor the connections

test_spgist=# select count(1) from route_raw ;
 count

 997799
(1 row)

..
..

test_spgist=# select count(1) from route_raw;
  count
-
 1004066
(1 row)

..
..

test_spgist=# select count(1) from route_raw;
  count
-
 1004066
(1 row)

test_spgist=# select * from pg_stat_activity ;
 datid |   datname   |  pid  | usesysid | usename  | application_name | 
client_addr | client_hostname | client_port | backend_start |   
   xact_start   |
query_start  | state_change  | waiting | state  |


 nb sp;

 query


 
---+-+---+--+--+--+-+-+-+---+---+--

Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?

2013-01-08 Thread Tom Lane 
=?gb2312?B?wO66o8H6?= hailong...@qunar.com writes:
 I am very excited to say that I may have created a test case!

I've been running variants of this example for most of the afternoon,
and have not seen a failure :-(.  So I'm afraid there is some aspect
of your situation that you've not provided enough information to
reproduce.  Most likely, that's the initial contents of your table,
which you didn't provide.  I tried seeding the table with the five
values you did show and then running the insertion loops, but no luck,
even after many millions of insertions with various timing changes.

Please see if you can put together a self-contained test case including
necessary test data.  (Note there's no reason to think that any of the
columns other than the spgist-indexed one are interesting, if that helps
you sanitize the data to the point you can let it out.)

The control flow in spgdoinsert.c is flat enough that the stack trace
alone isn't much help in understanding the bug, I'm afraid.  We can
guess that two insertions are trying to lock the same two index pages in
opposite orders, but without looking at the data that doesn't put us
much closer to a fix.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?

2013-01-08 Thread Tom Lane 
I wrote:
 The control flow in spgdoinsert.c is flat enough that the stack trace
 alone isn't much help in understanding the bug, I'm afraid.

BTW, something that possibly *would* help, since you seem to be able to
reproduce the bug easily, is to do that and then capture the values of
the local variables in spgdoinsert() -- especially the contents of the
current and parent structs --- from each of the processes that are
stuck.  Also interesting would be to print the SpGistCache structs.
It'd go something like
frame 4
info locals
p *(SpGistCache *) index-rd_amcache

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] I s this a bug of spgist index in a heavy write condition?

2012-12-13 Thread 李海龙 

Hi,pgsql-hackers,

I'm not sure whether it is a bug of using spgist index or not .

OS Version:
CentOS release 6.2 (Final)

PostgreSQL Version:
postgres=# select version();
version
--
   PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit
(1 row)

The lock infomation, pid 17225 was granted
   # select * from pg_locks where transactionid = 45463704;

  locktype| database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction |  pid  | 
mode  | granted | f
   astpath
  
---+--+--+--+---++---+-+---+--++---+---+-+--
   
transactionid |  |  |  | ||
45463704 | |   |  | 131/103| 17225 |
ExclusiveLock | t   | f
transactionid |  |  |  | ||
45463704 | |   |  | 185/44 | 24592 |
ShareLock | f   | f
   (2 rows)


The query is come from 192.168.232.43，but we already killed the process
at that server, there is no tcp connection from that server, the
following command output nothing.
   # netstat -anp | grep 192.168.232.43

Postgres process stat which running the query, it's doing a insert.
   postgres 17225  0.0  0.0 1763812 19056 ?   Ss   21:48 0:00
postgres: searcher vacation 192.168.232.43(64757) INSERT
  


Strace info for pid 17225
   # strace -p 17225
   Process 17225 attached - interrupt to quit
   semop(877035566, {{5, -1, 0}}, 1


backtrace for pid 17225, we found there is running some function named
like spg
   Missing separate debuginfos, use: debuginfo-install
pg92-9.2.2-2.el6.x86_64
   (gdb) bt
   #0  0x003d6baea747 in semop () from /lib64/libc.so.6
   #1  0x005fef57 in PGSemaphoreLock ()
   #2  0x0063f1a4 in LWLockAcquire ()
   #3  0x004b7002 in spgdoinsert ()
   #4  0x004b25e1 in spginsert ()
   #5  0x007155a4 in FunctionCall6Coll ()
   #6  0x0047b4e0 in index_insert ()
   #7  0x005871d2 in ExecInsertIndexTuples ()
   #8  0x00594747 in ExecModifyTable ()
   #9  0x0057de18 in ExecProcNode ()
   #10 0x0057ccb1 in standard_ExecutorRun ()
   #11 0x0064e93f in ProcessQuery ()
   #12 0x0064eb6f in PortalRunMulti ()
   #13 0x0064f32e in PortalRun ()
   #14 0x0064cba2 in PostgresMain ()
   #15 0x0060a547 in ServerLoop ()
   #16 0x0060d077 in PostmasterMain ()
   #17 0x005ac3d0 in main ()

We have lots data to insert in that table which have the spgist index,
may be the spgist index have a bug on a heavy write condition?


Thanks

Best Regards!




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?

2012-12-13 Thread Tom Lane 
=?utf-8?B?5p2O5rW36b6Z?= hailong...@qunar.com writes:
 We have lots data to insert in that table which have the spgist index,
 may be the spgist index have a bug on a heavy write condition?

Perhaps, but you certainly haven't provided any information that would
help anyone to fix the bug.  Can you create a self-contained test case?

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?

2012-12-13 Thread 李海龙 
hi,

The problem is not always appear on our system, we can't find a way to 
reproduce it.
After rebuild the index with btree, the problem is disappear




at 2012-12-14 00:16, Tom Lane wrote:
 =?utf-8?B?5p2O5rW36b6Z?= hailong...@qunar.com writes:
 We have lots data to insert in that table which have the spgist index,
 may be the spgist index have a bug on a heavy write condition?
 Perhaps, but you certainly haven't provided any information that would
 help anyone to fix the bug.  Can you create a self-contained test case?

   regards, tom lane



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers