Обсуждение: I s this a bug of spgist index in a heavy write condition?

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
--------------------------------------------------------------------------------------------------------------
PostgreSQL9.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 spgxxxx > Missing separate debuginfos, use: debuginfo-install
pg92-9.2.2-2.el6.x86_64 > (gdb) bt > #0  0x0000003d6baea747 in semop () from /lib64/libc.so.6 > #1  0x00000000005fef57
inPGSemaphoreLock () > #2  0x000000000063f1a4 in LWLockAcquire () > #3  0x00000000004b7002 in spgdoinsert () > #4
0x00000000004b25e1in spginsert () > #5  0x00000000007155a4 in FunctionCall6Coll () > #6  0x000000000047b4e0 in
index_insert() > #7  0x00000000005871d2 in ExecInsertIndexTuples () > #8  0x0000000000594747 in ExecModifyTable () > #9
0x000000000057de18 in ExecProcNode () > #10 0x000000000057ccb1 in standard_ExecutorRun () > #11 0x000000000064e93f in
ProcessQuery() > #12 0x000000000064eb6f in PortalRunMulti () > #13 0x000000000064f32e in PortalRun () > #14
0x000000000064cba2in PostgresMain () > #15 0x000000000060a547 in ServerLoop () > #16 0x000000000060d077 in
PostmasterMain() > #17 0x00000000005ac3d0 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!

李海龙 <hailong.li@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

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:
> 李海龙 <hailong.li@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
>
>

<br /><pre wrap="">Hi,dear tom lane && pgsql-hackers

I am very excited to say that I may have created a test case!

<b>
</b><b>OS Version:</b>
CentOS release 5.5 (Final)

<b>PostgreSQL Version:</b>
postgres=# select version();                                                   version
                  

---------------------------------------------------------------------------------------------------------------PostgreSQL
9.2.2on 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 has a spgist index column in
thedatabase. 

Some details is shown as following:
</pre> test_spgist=# \dt<br />            List of relations<br />  Schema |   Name    | Type  |  Owner   <br />
--------+-----------+-------+----------<br/>  public | route_raw | table | postgres<br /> (1 row)<br /><br />
test_spgist=#\d route_raw<br />                                      Table "public.route_raw"<br />        
Column       |            Type             |                  Modifiers                  <br />
----------------------+-----------------------------+---------------------------------------------<br/>
 sourceurl           | character varying(1000)     | not null default ''::character varying<br />  title               
|character varying(200)      | default NULL::character varying<br />  price                |
numeric(10,0)              | default NULL::numeric<br />  routesnapshot        | character varying(4024)     | default
NULL::charactervarying<br />  routesnapshot_small  | character varying(800)      | default NULL::character varying<br
/> routesnapshot_mid    | character varying(800)      | default NULL::character varying<br />  feature              |
text                       | <br />  departure            | character varying(50)       | default NULL::character
varying<br/>  arrive               | character varying(500)      | default NULL::character varying<br />
 function            | character varying(50)       | default NULL::character varying<br />  type                 |
charactervarying(50)       | default NULL::character varying<br />  subject              | text                       
|<br />  dateofdeparture      | timestamp without time zone | <br />  dateofexpire         | timestamp without time
zone| <br />  dateofbookingexpire  | timestamp without time zone | <br />  itineraryday         |
integer                    | <br />  sightspot            | text                        | <br />  traffic             
|character varying(100)      | default NULL::character varying<br />  stargrade            | double
precision           | <br />  region               | character varying(50)       | default NULL::character varying<br
/> subregion            | character varying(50)       | default NULL::character varying<br />  currency             |
charactervarying(45)       | default NULL::character varying<br />  listinfo             | character varying(1024)    
|default NULL::character varying<br />  dateofload           | timestamp without time zone | <br />
 urlkey              | character varying(64)       | not null default ''::character varying<br />  sightspot_b         
|text                        | <br />  sightspot_c          | text                        | <br />
 wrapperid           | character varying(128)      | default NULL::character varying<br />  wrappername          |
charactervarying(50)       | default NULL::character varying<br />  toflight             | character varying(45)      
|default NULL::character varying<br />  backflight           | character varying(45)       | default NULL::character
varying<br/>  raw_date             | character varying(2048)     | default NULL::character varying<br />
 feeinfo             | text                        | <br />  others               | text                        | <br
/> feeinclude           | character varying(1000)     | default NULL::character varying<br />  feeexclude           |
charactervarying(1000)     | default NULL::character varying<br />  ownexpense           | character varying(1000)    
|default NULL::character varying<br />  bookingterms         | character varying(1000)     | default NULL::character
varying<br/>  visainfos            | character varying(1000)     | default NULL::character varying<br />
 shopinfos           | character varying(500)      | default NULL::character varying<br />  flightinfos          |
charactervarying(500)      | default NULL::character varying<br />  spotofdeparture      | character varying(20)      
|default NULL::character varying<br />  spotofback           | character varying(20)       | default NULL::character
varying<br/>  tips                 | character varying(1000)     | default NULL::character varying<br />
 contact             | character varying(100)      | default NULL::character varying<br />  raw_price            |
charactervarying(100)      | default NULL::character varying<br />  raw_bookexpiredate   | character varying(100)     
|default NULL::character varying<br />  transfer             | character varying(20)       | default NULL::character
varying<br/>  contractstyle        | character varying(500)      | default NULL::character varying<br />
 citiesofbook        | character varying(1000)     | default NULL::character varying<br />  ishotelselected      |
charactervarying(20)       | not null default 'false'::character varying<br />  freetripitineraryday |
integer                    | not null default (-1)<br />  suitcrowdsubject     | character varying(50)       | default
NULL::charactervarying<br />  newsubject           | character varying(50)       | default NULL::character varying<br
/> isflightselected     | character varying(20)       | not null default 'false'::character varying<br />
 festival            | character varying(512)      | default NULL::character varying<br />  promotion            |
charactervarying(1024)     | <br /> Indexes:<br />     "route_raw_1_idx" btree (price, departure, dateofdeparture,
itineraryday)<br/>     "route_raw_sourceurl_idx1" spgist (sourceurl)<br /><br /> test_spgist=# select sourceurl from
route_rawlimit 5;<br />                                                   
sourceurl                                                  <br />
---------------------------------------------------------------------------------------------------------------<br/>
 <aclass="moz-txt-link-freetext"
href="http://suz.bacts.com/group/group_33_128972.aspx">http://suz.bacts.com/group/group_33_128972.aspx</a><br/>  <a
class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623</a><br
/> <a class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_147707.html">http://www.ctszj.com.cn/route_group_1_147707.html</a><br/>  <a
class="moz-txt-link-freetext"
href="http://jx.bacts.com/group/group_33_118381.aspx">http://jx.bacts.com/group/group_33_118381.aspx</a><br/>  <a
class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096</a><br
/>(5 rows)<br /><br /> --<b>Before the test, this table has 997736 records.</b><br /> test_spgist=# select count(1)
fromroute_raw ;<br />  count  <br /> --------<br />  997736<br /> (1 row)<br /><br /> My some deteails the test
procedureis as follows.<br /><br /><b><big>1. edit a shell script that function is insert data to table
route_raw</big></b><br/><br /> [hailong.li@pgdb2 ~]$ <b><big>cat /tmp/insert_spgist.sh</big></b><br /> #/bin/bash<br
/><br/> export PGPASSWORD="6b4df779-dd13-4031-a00b-126b49287995"<br /> PSQL="/opt/pg92/bin/psql"<br />
DBHOST=l-pgdb2.s.cn6<br/> DB=test_spgist<br /> USR=postgres<br /><br /> while [ 10 -ne 9 ]<br /> do<br />        
SQL="insertinto route_raw(sourceurl) values ('<a class="moz-txt-link-freetext"
href="http://suz.bacts.com/group/group_33_128972.aspx">http://suz.bacts.com/group/group_33_128972.aspx</a>, <a
class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623">
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623</a>,
<aclass="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx">
http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx</a>,<a
class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096">
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096</a>, <a
class="moz-txt-link-freetext"href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957">
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957</a>, <a class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_134782.html">http://www.ctszj.com.cn/route_group_1_134782.html</a> , <a
class="moz-txt-link-freetext"href="http://fj.bacts.com/group/group_33_113288.asp">
http://fj.bacts.com/group/group_33_113288.asp</a>,  <a class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957">
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957</a>,<a class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.bacts.com/group/group_33_113288.aspx">
http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.bacts.com/group/group_33_113288.aspx</a>,<a
class="moz-txt-link-freetext"
href="http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1">http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1</a>
,<a class="moz-txt-link-freetext" href="http://www.ctszj.com.cn/route_group_1_134695.html">
http://www.ctszj.com.cn/route_group_1_134695.html</a>')"<br />         sleep 1<br />         $PSQL -h $DBHOST -U $USR
-d$DB -c "$SQL"<br /> done<br /><br /><b>2.</b> <b>I create 15 clients and execute this shell script in each client
</b><br/><br /> [<a class="moz-txt-link-abbreviated"
href="mailto:hailong.li@l-pgdb2.s.cn6">hailong.li@l-pgdb2.s.cn6</a>~]$ /tmp/insert_spgist.sh >/dev/null<br /> ...<br
/>...<br /> ...<br /> [<a class="moz-txt-link-abbreviated"
href="mailto:hailong.li@l-pgdb2.s.cn6">hailong.li@l-pgdb2.s.cn6</a>~]$ /tmp/insert_spgist.sh >/dev/null<br /><br />
3.<b>I execute the statement "<font color="#ff0000">select * from pg_stat_activity;</font> and <font
color="#ff0000">selectcount(1) from route_raw;</font> " to monitor the connections</b><br /><br /> test_spgist=# select
*from pg_stat_activity ;<br />  datid |   datname   |  pid  | usesysid | usename  | application_name |   client_addr  
|client_hostname | client_port |         backend_start         |          xact_start           |      <br />    
query_start         |         state_change          | waiting | state 
|                                                                                                                 <br
/>
                                                                                                                                                                      
;                        <br />
                                                                                                                            
query                                                            <br />
                                                                                                                                                                      
;                        <br />
                                                                                                                                                                      
;            <br />
-------+-------------+-------+----------+----------+------------------+-----------------+-----------------+-------------+-------------------------------+-------------------------------+------<br
/>
-------------------------+-------------------------------+---------+--------+------------------------------------------------------------------------------------------------------------------<br
/>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/> 16384 | test_spgist | 14959 |       10 | postgres | psql             |                 |                 |         
-1| 2013-01-08 16:43:53.175203+08 | 2013-01-08 17:16:01.997493+08 | 2013-<br /> 01-08 17:16:01.997493+08 | 2013-01-08
17:16:01.997495+08| f       | active | select * from pg_stat_activity ;<br /><br />  16384 | test_spgist | 16858
|      10 | postgres | psql             |                 |                 |          -1 | 2013-01-08
17:04:15.000472+08|                               | 2013-<br /> 01-08 17:15:01.303197+08 | 2013-01-08
17:15:02.372496+08| f       | idle   | select count(1) from route_raw ;<br /><br /> 16384 | test_spgist | 26097 |      
10| postgres | psql             | 192.168.232.122 |                 |       54637 | 2013-01-08 17:12:16.228789+08 |
2013-01-0817:12:16.231496+08 | 2013-<br /> 01-08 17:12:16.231496+08 | 2013-01-08 17:12:16.231504+08 | f       | active
|insert into route_raw(sourceurl) values ('<a class="moz-txt-link-freetext"
href="http://suz.bacts.com/group/group_33_128972.aspx">http://suz.bacts.com/group/group_33_128972.aspx</a>, <a
class="moz-txt-link-freetext"href="http://www.gzl.com.cn">http://www.gzl.com.cn</a><br />
/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623, <a
class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx">
http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx</a>,ht<br />
tp://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096, <a
class="moz-txt-link-freetext"href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957">
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957</a>, <a class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/">http://www.ctszj.com.cn/</a><br /> route_group_1_134782.html , <a
class="moz-txt-link-freetext"href="http://fj.bacts.com/group/group_33_113288.asp">
http://fj.bacts.com/group/group_33_113288.asp</a>,  <a class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957">
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957</a>,<a class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_1">http://www.ctszj.com.cn/route_group_1_1</a><br /> 34782.html,<a
class="moz-txt-link-freetext"
href="http://fj.bacts.com/group/group_33_113288.aspx">http://fj.bacts.com/group/group_33_113288.aspx</a>,<a
class="moz-txt-link-freetext"
href="http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1">http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1</a>
,<a class="moz-txt-link-freetext" href="http://www.ctszj.com.cn/route_group_1_134695.html">
http://www.ctszj.com.cn/route_group_1_134695.html</a>')<br /> ......<br /> ......<br /> (17 rows)<br /><br />
test_spgist=#select count(1) from route_raw ;<br />  count  <br /> --------<br />  997799<br /> (1 row)<br /><br />
test_spgist=#select count(1) from route_raw ;<br />  count  <br /> --------<br />  997801<br /> (1 row)<br /><br />
test_spgist=#select count(1) from route_raw ;<br />  count  <br /> --------<br />  998687<br /> (1 row)<br /><br />
test_spgist=#select count(1) from route_raw ;<br />  count  <br /> --------<br />  999647<br /> (1 row)<br /><br />
test_spgist=#select count(1) from route_raw ;<br />  count  <br /> --------<br />  999647<br /> (1 row)<br /><br />
......<br/> ......<br /><br /> test_spgist=# select count(1) from route_raw ;<br />  count  <br /> --------<br />
 999647<br/> (1 row)<br /><br /> When I found that the counts of the records of the table route_raw is is not
increased,I immediately went to  displays the  process information  using command gdb  at that time.<br /><br /><b>4.
displayprocess information using gdb</b><br /><br /> [<a class="moz-txt-link-abbreviated"
href="mailto:hailong.li@l-pgdb2.s.cn6">hailong.li@l-pgdb2.s.cn6</a>~]$ sudo gdb<br /> GNU gdb (GDB) Red Hat Enterprise
Linux(7.0.1-23.el5_5.2)<br /> Copyright (C) 2009 Free Software Foundation, Inc.<br /> License GPLv3+: GNU GPL version 3
orlater <a class="moz-txt-link-rfc2396E" href="http://gnu.org/licenses/gpl.html">
<http://gnu.org/licenses/gpl.html></a><br/> This is free software: you are free to change and redistribute it.<br
/>There is NO WARRANTY, to the extent permitted by law.  Type "show copying"<br /> and "show warranty" for details.<br
/>This GDB was configured as "x86_64-redhat-linux-gnu".<br /> For bug reporting instructions, please see:<br /><a
class="moz-txt-link-rfc2396E"
href="http://www.gnu.org/software/gdb/bugs/"><http://www.gnu.org/software/gdb/bugs/></a>.<br/> (gdb) attach
26097<br/> Attaching to process 26097<br /> Reading symbols from /opt/pg92/bin/postgres...done.<br /> Reading symbols
from/usr/lib64/libxslt.so.1...(no debugging symbols found)...done.<br /> Loaded symbols for /usr/lib64/libxslt.so.1<br
/>Reading symbols from /usr/lib64/libxml2.so.2...(no debugging symbols found)...done.<br /> Loaded symbols for
/usr/lib64/libxml2.so.2<br/> Reading symbols from /lib64/libcrypt.so.1...(no debugging symbols found)...done.<br />
Loadedsymbols for /lib64/libcrypt.so.1<br /> Reading symbols from /lib64/libdl.so.2...(no debugging symbols
found)...done.<br/> Loaded symbols for /lib64/libdl.so.2<br /> Reading symbols from /lib64/libm.so.6...(no debugging
symbolsfound)...done.<br /> Loaded symbols for /lib64/libm.so.6<br /> Reading symbols from /lib64/libc.so.6...(no
debuggingsymbols found)...done.<br /> Loaded symbols for /lib64/libc.so.6<br /> Reading symbols from
/usr/lib64/libz.so.1...(nodebugging symbols found)...done.<br /> Loaded symbols for /usr/lib64/libz.so.1<br /> Reading
symbolsfrom /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.<br /> Loaded symbols for
/lib64/ld-linux-x86-64.so.2<br/> Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols
found)...done.<br/> Loaded symbols for /lib64/libnss_files.so.2<br /> 0x00000039e4ed5337 in semop () from
/lib64/libc.so.6<br/> (gdb) bt<br /> #0  0x00000039e4ed5337 in semop () from /lib64/libc.so.6<br /> #1 
0x00000000006985f3in PGSemaphoreLock (sema=0x2b4cb1e2bb30, interruptOK=0 '\000') at pg_sema.c:415<br /> #2 
0x00000000006ea043in LWLockAcquire (lockid=355054, mode=LW_EXCLUSIVE) at lwlock.c:474<br /> #3  0x00000000006d0739 in
LockBuffer(buffer=177443, mode=2) at bufmgr.c:2533<br /> #4  0x00000000004d9ab0 in <font
color="#ff0000">spgdoinsert</font>(index=0x2b4cb26edcc0, state=0x7fffb8ce3f30, heapPtr=0xb574124, datum=190267736,
isnull=0'\000') at spgdoinsert.c:1940<br /> #5  0x00000000004d1880 in <font color="#ff0000">spginsert
</font>(fcinfo=0x7fffb8ce3fe0)at spginsert.c:220<br /> #6  0x000000000080c921 in FunctionCall6Coll (flinfo=0xb5702a0,
collation=0,arg1=47608411118784, arg2=140736293913696, arg3=140736293913664, arg4=190267684, arg5=47608411039440,
arg6=0)<br/>     at fmgr.c:1439<br /> #7  0x0000000000489526 in index_insert (indexRelation=0x2b4cb26edcc0,
values=0x7fffb8ce4460,isnull=0x7fffb8ce4440 "", heap_t_ctid=0xb574124, heapRelation=0x2b4cb26da6d0, <br />    
checkUnique=UNIQUE_CHECK_NO)at indexam.c:216<br /> #8  0x00000000005eaf3c in ExecInsertIndexTuples (slot=0xb56e520,
tupleid=0xb574124,estate=0xb56e290) at execUtils.c:1087<br /> #9  0x00000000005fcfb5 in ExecInsert (slot=0xb56e520,
planSlot=0xb56e520,estate=0xb56e290, canSetTag=1 '\001') at nodeModifyTable.c:248<br /> #10 0x00000000005fde2e in
ExecModifyTable(node=0xb56e5b0) at nodeModifyTable.c:848<br /> #11 0x00000000005ddde3 in ExecProcNode (node=0xb56e5b0)
atexecProcnode.c:376<br /> #12 0x00000000005dc24e in ExecutePlan (estate=0xb56e290, planstate=0xb56e5b0,
operation=CMD_INSERT,sendTuples=0 '\000', numberTuples=0, direction=ForwardScanDirection, dest=0xb556160)<br />     at
execMain.c:1393<br/> #13 0x00000000005daa2a in standard_ExecutorRun (queryDesc=0xb486830,
direction=ForwardScanDirection,count=0) at execMain.c:301<br /> #14 0x00000000005da922 in ExecutorRun
(queryDesc=0xb486830,direction=ForwardScanDirection, count=0) at execMain.c:249<br /> #15 0x00000000006fb344 in
ProcessQuery(plan=0xb50ce80, <br />     sourceText=0xb50b500 "insert into route_raw(sourceurl) values ('<a
class="moz-txt-link-freetext"
href="http://suz.bacts.com/group/group_33_128972.aspx">http://suz.bacts.com/group/group_33_128972.aspx</a>, <a
class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=1262">
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=1262</a>"...,
params=0x0,dest=0xb556160, completionTag=0x7fffb8ce4ae0 "") at pquery.c:185<br /> #16 0x00000000006fcaa9 in
PortalRunMulti(portal=0xb484820, isTopLevel=1 '\001', dest=0xb556160, altdest=0xb556160, completionTag=0x7fffb8ce4ae0
"")at pquery.c:1277<br /> #17 0x00000000006fc183 in PortalRun (portal=0xb484820, count=9223372036854775807,
isTopLevel=1'\001', dest=0xb556160, altdest=0xb556160, completionTag=0x7fffb8ce4ae0 "") at pquery.c:814<br /> #18
0x00000000006f66dain exec_simple_query (<br />     query_string=0xb50b500 "insert into route_raw(sourceurl) values ('<a
class="moz-txt-link-freetext"
href="http://suz.bacts.com/group/group_33_128972.aspx">http://suz.bacts.com/group/group_33_128972.aspx</a>, <a
class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=1262">
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=1262</a>"...)
atpostgres.c:1046<br /> #19 0x00000000006fa5bd in PostgresMain (argc=2, argv=0xb4664e8, username=0xb4663f0 "postgres")
atpostgres.c:3958<br /> #20 0x00000000006a9305 in BackendRun (port=0xb489290) at postmaster.c:3619<br /> #21
0x00000000006a8a02in BackendStartup (port=0xb489290) at postmaster.c:3304<br /> #22 0x00000000006a591a in ServerLoop ()
atpostmaster.c:1367<br /> #23 0x00000000006a52a8 in PostmasterMain (argc=3, argv=0xb464660) at postmaster.c:1127<br />
#240x000000000061cfcb in main (argc=3, argv=0xb464660) at main.c:199<br /><br /><br /><pre wrap="">I say my doubt once
one"May be the spgist index has a bug in a heavy write condition?" 

My colleagues and I are looking forward very much to hearing from you.


Thanks again

Best Regards!</pre><br /><br /><blockquote cite="mid:50EBED1E.4060402@qunar.com" type="cite"><div
class="moz-cite-prefix">at2012-12-14 12:00, hailong.li wrote:<br /></div><blockquote
cite="mid:50CAA452.1060602@qunar.com"type="cite"><pre wrap="">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:
</pre><blockquote type="cite"><pre wrap="">李海龙 <a class="moz-txt-link-rfc2396E" href="mailto:hailong.li@qunar.com"
moz-do-not-send="true"><hailong.li@qunar.com></a>writes: 
</pre><blockquote type="cite"><pre wrap="">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?
</pre></blockquote><pre wrap="">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


</pre></blockquote></blockquote> at 2012-12-13 23:46, hailong.li wrote:<br /><br /><pre wrap="">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
--------------------------------------------------------------------------------------------------------------
PostgreSQL9.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 spgxxxx > Missing separate debuginfos, use: debuginfo-install
pg92-9.2.2-2.el6.x86_64 > (gdb) bt > #0  0x0000003d6baea747 in semop () from /lib64/libc.so.6 > #1
0x00000000005fef57in PGSemaphoreLock () > #2  0x000000000063f1a4 in LWLockAcquire () > #3  0x00000000004b7002 in
spgdoinsert() > #4  0x00000000004b25e1 in spginsert () > #5  0x00000000007155a4 in FunctionCall6Coll () > #6
0x000000000047b4e0in index_insert () > #7  0x00000000005871d2 in ExecInsertIndexTuples () > #8
0x0000000000594747in ExecModifyTable () > #9  0x000000000057de18 in ExecProcNode () > #10 0x000000000057ccb1 in
standard_ExecutorRun() > #11 0x000000000064e93f in ProcessQuery () > #12 0x000000000064eb6f in PortalRunMulti ()
>#13 0x000000000064f32e in PortalRun () > #14 0x000000000064cba2 in PostgresMain () > #15 0x000000000060a547
inServerLoop () > #16 0x000000000060d077 in PostmasterMain () > #17 0x00000000005ac3d0 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!
</pre></blockquote><br />

李海龙 <hailong.li@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

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 likeframe 4info localsp *(SpGistCache *) index->rd_amcache
        regards, tom lane

<div class="moz-cite-prefix"><b>Hi,dear tom lane && pgsql-hackers</b><br /><br /> In your last e-mail, you want
meto think anbout  the only  spgist-indexed column and give you some more further information.<br /><br /> This time I
willgive you the contents of the table route_raw, the download link is <b><a class="moz-txt-link-freetext"
href="https://www.box.com/s/yxa4yxo6rcb3dzeaefmz">https://www.box.com/s/yxa4yxo6rcb3dzeaefmz</a></b>or <b> <a
class="moz-txt-link-freetext"href="http://dl.dropbox.com/u/203288/route_raw_spgist.sql.tar.gz">
http://dl.dropbox.com/u/203288/route_raw_spgist.sql.tar.gz</a></b>. <br /><br /><br /><pre wrap="">OS Version: 
CentOS release 5.5 (Final)

PostgreSQL Version:
postgres=# select version();                                                   version
                  

---------------------------------------------------------------------------------------------------------------PostgreSQL
9.2.2on 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 )
columnand a spgist index column in the database.</pre> <pre wrap="">Some details is shown as following:  
</pre> test_spgist=# \dt<br />            List of relations<br />  Schema |   Name    | Type  |  Owner   <br />
--------+-----------+-------+----------<br/>  public | route_raw | table | postgres<br /> (1 row)<br /><br />
test_spgist=#\d route_raw<br />                                    Table "public.route_raw"<br />   Column   |         
Type          |                       Modifiers                        <br />
-----------+-------------------------+--------------------------------------------------------<br/>  sourceurl |
charactervarying(1000) | not null default ''::character varying<br />  id        | integer                 | not null
defaultnextval('route_raw_id_seq'::regclass)<br /> Indexes:<br />     "route_raw_pkey" PRIMARY KEY, btree (id)<br />
   "route_raw_sourceurl_idx" spgist (sourceurl)<br /><br /> --<b>Before the test, this table has 997736 records.</b><br
/>test_spgist=# select count(1) from route_raw ;<br />  count  <br /> --------<br />  997736<br /> (1 row)<br /><br
/><b><big>1.edit a shell script that function is insert data to table route_raw</big></b><br /><br /><b>$ cat
/tmp/insert_spgist.sh</b><br /> #/bin/bash<br /><br /> PSQL="/opt/pg92/bin/psql"<br /> DB=test_spgist<br />
USR=postgres<br/><br /> while [ 10 -ne 9 ]<br /> do<br />         SQL="insert into route_raw(sourceurl) values ('<a
class="moz-txt-link-freetext"href="http://suz.bacts.com/group/group_33_128972.aspx"
moz-do-not-send="true">http://suz.bacts.com/group/group_33_128972.aspx</a>, <a class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623"
moz-do-not-send="true">
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623</a>,
<aclass="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx"
moz-do-not-send="true">
http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx</a>,<a
class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096"
moz-do-not-send="true">
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096</a>, <a
class="moz-txt-link-freetext"href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957"
moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957</a> , <a
class="moz-txt-link-freetext"href="http://www.ctszj.com.cn/route_group_1_134782.html" moz-do-not-send="true">
http://www.ctszj.com.cn/route_group_1_134782.html</a>, <a class="moz-txt-link-freetext"
href="http://fj.bacts.com/group/group_33_113288.asp"moz-do-not-send="true">
http://fj.bacts.com/group/group_33_113288.asp</a>,  <a class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957"moz-do-not-send="true">
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957</a>,<a class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.bacts.com/group/group_33_113288.aspx"
moz-do-not-send="true">
http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.bacts.com/group/group_33_113288.aspx</a>,<a
class="moz-txt-link-freetext"href="http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1"
moz-do-not-send="true">http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1</a>, <a
class="moz-txt-link-freetext"href="http://www.ctszj.com.cn/route_group_1_134695.html" moz-do-not-send="true">
http://www.ctszj.com.cn/route_group_1_134695.html</a>')"<br />         sleep 1<br />         $PSQL  -U $USR -d $DB -c
"$SQL"<br/> done<br /><br /><b>2.</b> <b>I create 15 clients and execute this shell script in each client </b><br /><br
/>$ /tmp/insert_spgist.sh >/dev/null<br /> ...<br /> ...<br /> ...<br /> ]$ /tmp/insert_spgist.sh >/dev/null<br
/><br/><b>3.</b> <b>only 1 connection to update the table </b><br /><br /><b>$ psql -U postgres</b><br /> psql
(9.2.2)<br/> Type "help" for help.<br /><br /> postgres=# \c test_spgist <br /> You are now connected to database
"test_spgist"as user "postgres".<br /><br /> test_spgist=# update route_raw set sourceurl = '<a
class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=CGZL120701MDG05LA&xl=13935%27where"
moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=CGZL120701MDG05LA&xl=13935'where</a>
sourceurllike '%=CGZL120701MDG0%';<br /><br /><br /> 4. <b>I execute the statement "<font color="#ff0000">select * from
pg_stat_activity;</font>and <font color="#ff0000">select count(1) from route_raw;</font> " to monitor the
connections</b><br/><br /> test_spgist=# select count(1) from route_raw ;<br />  count  <br /> --------<br />
 997799<br/> (1 row)<br /><br /> ......<br /> ......<br /><br /> test_spgist=# select count(1) from route_raw;<br />  
count <br /> ---------<br />  1004066<br /> (1 row)<br /><br /> ......<br /> ......<br /><br /> test_spgist=# select
count(1)from route_raw;<br />   count  <br /> ---------<br />  1004066<br /> (1 row)<br /><br /> test_spgist=# select *
frompg_stat_activity ;<br />  datid |   datname   |  pid  | usesysid | usename  | application_name | client_addr |
client_hostname| client_port |         backend_start         |          xact_start           |          <br />
query_start         |         state_change          | waiting | state 
|                                                                                                                     
<br/>
                                                                                                                                                                     &nb
sp;                     <br />
                                                                                                                        
query                                                                <br />                    
                                                                                                                                                                    
-------+-------------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+----------<br
/>
---------------------+-------------------------------+---------+--------+----------------------------------------------------------------------------------------------------------------------<br
/>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/> 16384 | test_spgist | 18797 |       10 | postgres | psql             |             |                 |          -1 |
2013-01-1020:27:05.171787+08 |                               | 2013-01-1<br /> 0 20:29:05.393515+08 | 2013-01-10
20:29:06.126108+08| f       | idle   | select * from route_raw where sourceurl like '%CGZL120701MDG05LA&xl%';<br />
 16384| test_spgist | 22272 |       10 | postgres | psql             |             |                 |          -1 |
2013-01-1018:23:20.035532+08 | 2013-01-10 20:31:57.898985+08 | 2013-01-1<br /> 0 20:31:57.898985+08 | 2013-01-10
20:31:57.898986+08| f       | active | select * from pg_stat_activity ; <br />  16384 | test_spgist | 24391 |       10
|postgres | psql             |             |                 |          -1 | 2013-01-10 20:29:07.113574+08 | 2013-01-10
20:29:07.1161+08  | 2013-01-1<br /> 0 20:29:07.1161+08   | 2013-01-10 20:29:07.116107+08 | f       | active | insert
intoroute_raw(sourceurl) values ('<a class="moz-txt-link-freetext"
href="http://suz.bacts.com/group/group_33_128972.aspx"
moz-do-not-send="true">http://suz.bacts.com/group/group_33_128972.aspx</a>, <a class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Tra"moz-do-not-send="true"> http://www.gzl.com.cn/Tra</a><br />
vel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623, <a
class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx"
moz-do-not-send="true">
http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx</a>,<a
class="moz-txt-link-freetext"href="http:/" moz-do-not-send="true">http:/</a><br />
/www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096, <a
class="moz-txt-link-freetext"href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957"
moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957</a> , <a
class="moz-txt-link-freetext"href="http://www.ctszj.com.cn/rout" moz-do-not-send="true">
http://www.ctszj.com.cn/rout</a><br/> e_group_1_134782.html , <a class="moz-txt-link-freetext"
href="http://fj.bacts.com/group/group_33_113288.asp"moz-do-not-send="true">
http://fj.bacts.com/group/group_33_113288.asp</a>,  <a class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957"moz-do-not-send="true">
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957</a>,<a class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_13478"moz-do-not-send="true">
http://www.ctszj.com.cn/route_group_1_13478</a><br/> 2.html,<a class="moz-txt-link-freetext"
href="http://fj.bacts.com/group/group_33_113288.aspx"
moz-do-not-send="true">http://fj.bacts.com/group/group_33_113288.aspx</a>,<a class="moz-txt-link-freetext"
href="http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1"
moz-do-not-send="true">http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1</a>, <a
class="moz-txt-link-freetext"href="http://www.ctszj.com.cn/route_group_1_134695.html" moz-do-not-send="true">
http://www.ctszj.com.cn/route_group_1_134695.html</a>')<br /><br /> ......<br /> ......<br /><br />  16384 |
test_spgist| 24437 |       10 | postgres | psql             |             |                 |          -1 | 2013-01-10
20:29:38.498181+08| 2013-01-10 20:31:17.963092+08 | 2013-01-1<br /> 0 20:31:17.963092+08 | 2013-01-10
20:31:17.963095+08| f       | active | update route_raw set sourceurl = '<a class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=CGZL120701MDG05LA&xl=13935%27whe"
moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=CGZL120701MDG05LA&xl=13935'whe</a><br/>
resourceurl like '%=CGZL120701MDG0%';<br /> (18 rows)<br /><br /> 5. <b>display process information using gdb</b><br
/><br/> [root@XXX ~]# <font color="#ff0000"><b>gdb </b></font><br /> GNU gdb (GDB) Red Hat Enterprise Linux
(7.0.1-23.el5_5.2)<br/> Copyright (C) 2009 Free Software Foundation, Inc.<br /> License GPLv3+: GNU GPL version 3 or
later<a class="moz-txt-link-rfc2396E" href="http://gnu.org/licenses/gpl.html" moz-do-not-send="true">
<http://gnu.org/licenses/gpl.html></a><br/> This is free software: you are free to change and redistribute it.<br
/>There is NO WARRANTY, to the extent permitted by law.  Type "show copying"<br /> and "show warranty" for details.<br
/>This GDB was configured as "x86_64-redhat-linux-gnu".<br /> For bug reporting instructions, please see:<br /><a
class="moz-txt-link-rfc2396E"href="http://www.gnu.org/software/gdb/bugs/"
moz-do-not-send="true"><http://www.gnu.org/software/gdb/bugs/></a>.<br/> (gdb) attach 24391<br /> Attaching to
process24391<br /> Reading symbols from /opt/pg92/bin/postgres...done.<br /> Reading symbols from
/usr/lib64/libxslt.so.1...(nodebugging symbols found)...done.<br /> Loaded symbols for /usr/lib64/libxslt.so.1<br />
Readingsymbols from /usr/lib64/libxml2.so.2...(no debugging symbols found)...done.<br /> Loaded symbols for
/usr/lib64/libxml2.so.2<br/> Reading symbols from /lib64/libcrypt.so.1...(no debugging symbols found)...done.<br />
Loadedsymbols for /lib64/libcrypt.so.1<br /> Reading symbols from /lib64/libdl.so.2...(no debugging symbols
found)...done.<br/> Loaded symbols for /lib64/libdl.so.2<br /> Reading symbols from /lib64/libm.so.6...(no debugging
symbolsfound)...done.<br /> Loaded symbols for /lib64/libm.so.6<br /> Reading symbols from /lib64/libc.so.6...(no
debuggingsymbols found)...done.<br /> Loaded symbols for /lib64/libc.so.6<br /> Reading symbols from
/usr/lib64/libz.so.1...(nodebugging symbols found)...done.<br /> Loaded symbols for /usr/lib64/libz.so.1<br /> Reading
symbolsfrom /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.<br /> Loaded symbols for
/lib64/ld-linux-x86-64.so.2<br/> Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols
found)...done.<br/> Loaded symbols for /lib64/libnss_files.so.2<br /> 0x00000039e4ed5337 in semop () from
/lib64/libc.so.6<br/><font color="#ff0000"><small><b><big>(gdb) bt</big></b></small></font><br /> #0 
0x00000039e4ed5337in semop () from /lib64/libc.so.6<br /> #1  0x00000000006985f3 in PGSemaphoreLock
(sema=0x2ac6df58b850,interruptOK=0 '\000') at pg_sema.c:415<br /> #2  0x00000000006ea043 in LWLockAcquire
(lockid=375058,mode=LW_EXCLUSIVE) at lwlock.c:474<br /> #3  0x00000000006d0739 in LockBuffer (buffer=187445, mode=2) at
bufmgr.c:2533<br/> #4  0x00000000004d9ab0 in spgdoinsert (index=0x2ac6dfe42730, state=0x7fff015dad90,
heapPtr=0x2a491c4,datum=44339704, isnull=0 '\000') at spgdoinsert.c:1940<br /> #5  0x00000000004d1880 in spginsert
(fcinfo=0x7fff015dae40)at spginsert.c:220<br /> #6  0x000000000080c921 in FunctionCall6Coll (flinfo=0x296b360,
collation=0,arg1=47033648162608, arg2=140733216305856, arg3=140733216305824, arg4=44339652, arg5=47033648129744,
arg6=0)<br/>     at fmgr.c:1439<br /> #7  0x0000000000489526 in index_insert (indexRelation=0x2ac6dfe42730,
values=0x7fff015db2c0,isnull=0x7fff015db2a0 "", heap_t_ctid=0x2a491c4, heapRelation=0x2ac6dfe3a6d0, <br />    
checkUnique=UNIQUE_CHECK_NO)at indexam.c:216<br /> #8  0x00000000005eaf3c in ExecInsertIndexTuples (slot=0x2a42420,
tupleid=0x2a491c4,estate=0x2a42190) at execUtils.c:1087<br /> #9  0x00000000005fcfb5 in ExecInsert (slot=0x2a42420,
planSlot=0x2a42420,estate=0x2a42190, canSetTag=1 '\001') at nodeModifyTable.c:248<br /> #10 0x00000000005fde2e in
ExecModifyTable(node=0x2a424b0) at nodeModifyTable.c:848<br /> #11 0x00000000005ddde3 in ExecProcNode (node=0x2a424b0)
atexecProcnode.c:376<br /> #12 0x00000000005dc24e in ExecutePlan (estate=0x2a42190, planstate=0x2a424b0,
operation=CMD_INSERT,sendTuples=0 '\000', numberTuples=0, direction=ForwardScanDirection, dest=0x2a38f98)<br />     at
execMain.c:1393<br/> #13 0x00000000005daa2a in standard_ExecutorRun (queryDesc=0x296a740,
direction=ForwardScanDirection,count=0) at execMain.c:301<br /> #14 0x00000000005da922 in ExecutorRun
(queryDesc=0x296a740,direction=ForwardScanDirection, count=0) at execMain.c:249<br /> #15 0x00000000006fb344 in
ProcessQuery(plan=0x29f0d90, <br />     sourceText=0x29ef410 "insert into route_raw(sourceurl) values ('<a
class="moz-txt-link-freetext"href="http://suz.bacts.com/group/group_33_128972.aspx"
moz-do-not-send="true">http://suz.bacts.com/group/group_33_128972.aspx</a>, <a class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=1262"
moz-do-not-send="true">
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=1262</a>"...,
params=0x0,dest=0x2a38f98, completionTag=0x7fff015db940 "") at pquery.c:185<br /> #16 0x00000000006fcaa9 in
PortalRunMulti(portal=0x2968730, isTopLevel=1 '\001', dest=0x2a38f98, altdest=0x2a38f98, completionTag=0x7fff015db940
"")at pquery.c:1277<br /> #17 0x00000000006fc183 in PortalRun (portal=0x2968730, count=9223372036854775807,
isTopLevel=1'\001', dest=0x2a38f98, altdest=0x2a38f98, completionTag=0x7fff015db940 "") at pquery.c:814<br /> #18
0x00000000006f66dain exec_simple_query (<br />     query_string=0x29ef410 "insert into route_raw(sourceurl) values ('<a
class="moz-txt-link-freetext"href="http://suz.bacts.com/group/group_33_128972.aspx"
moz-do-not-send="true">http://suz.bacts.com/group/group_33_128972.aspx</a>, <a class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=1262"
moz-do-not-send="true">
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=1262</a>"...)
atpostgres.c:1046<br /> #19 0x00000000006fa5bd in PostgresMain (argc=2, argv=0x2949640, username=0x29494d0 "postgres")
atpostgres.c:3958<br /> #20 0x00000000006a9305 in BackendRun (port=0x296cd70) at postmaster.c:3619<br /> #21
0x00000000006a8a02in BackendStartup (port=0x296cd70) at postmaster.c:3304<br /> #22 0x00000000006a591a in ServerLoop ()
atpostmaster.c:1367<br /> #23 0x00000000006a52a8 in PostmasterMain (argc=3, argv=0x2947820) at postmaster.c:1127<br />
#240x000000000061cfcb in main (argc=3, argv=0x2947820) at main.c:199<br /><br /><font color="#ff0000"><b>(gdb) frame
4</b></font><br/> #4  0x00000000004d9ab0 in spgdoinsert (index=0x2ac6dfe42730, state=0x7fff015dad90, heapPtr=0x2a491c4,
datum=44339704,isnull=0 '\000') at spgdoinsert.c:1940<br /> 1940    spgdoinsert.c: No such file or directory.<br />    
inspgdoinsert.c<br /><br /><font color="#ff0000"><b>(gdb) info locals</b></font><br /> isNew = 0 '\000'<br /> level =
0<br/> leafDatum = 44339704<br /> leafSize = 844<br /> current = {blkno = 1, buffer = 187445, page = 0x0, offnum = 1,
node= -1}<br /> parent = {blkno = 4294967295, buffer = 0, page = 0x0, offnum = 0, node = -1}<br /> __func__ =
"spgdoinsert"<br/><font color="#ff0000"><b>(gdb) p *(SpGistCache *) index->rd_amcache</b></font><br /> $1 = {config
={prefixType = 25, labelType = 18, canReturnData = 1 '\001', longValuesOK = 1 '\001'}, attType = {type = 1043, attbyval
=0 '\000', attlen = -1}, attPrefixType = {type = 25, <br />     attbyval = 0 '\000', attlen = -1}, attLabelType = {type
=18, attbyval = 1 '\001', attlen = 1}, lastUsedPages = {cachedPage = {{blkno = 6174, freeSpace = 4308}, {blkno = 6172,
<br/>         freeSpace = 2024}, {blkno = 6173, freeSpace = 5244}, {blkno = 6217, freeSpace = 6228}, {blkno =
4294967295,freeSpace = 0}, {blkno = 4294967295, freeSpace = 0}, {blkno = 4294967295, <br />         freeSpace = 0},
{blkno= 4294967295, freeSpace = 0}}}}<br /><br /><pre wrap="">Thanks again 

Best Regards!</pre><br /><br /> 于 2013年01月09日 12:28, Tom Lane 写道:<br /></div><blockquote
cite="mid:11459.1357705723@sss.pgh.pa.us"type="cite"><pre wrap="">I wrote: 
</pre><blockquote type="cite"><pre wrap="">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.
</pre></blockquote><pre wrap="">
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 likeframe 4info localsp *(SpGistCache *) index->rd_amcache
        regards, tom lane
</pre></blockquote><br />

李海龙 <hailong.li@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/yxa4yxo6rcb3dzeaefmzor  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

<blockquote cite="mid:50F37DCA.1010609@qunar.com" type="cite"><div class="moz-cite-prefix">at 2013-01-12 02:21, Tom
Lanewrote:</div><blockquote cite="mid:13220.1357928488@sss.pgh.pa.us" type="cite"><pre wrap="">李海龙 <a
class="moz-txt-link-rfc2396E"href="mailto:hailong.li@qunar.com" moz-do-not-send="true"><hailong.li@qunar.com></a>
writes:
</pre><blockquote type="cite"><pre wrap="">This time I will give you the contents of the table route_raw, the download
linkis <a class="moz-txt-link-freetext" href="https://www.box.com/s/yxa4yxo6rcb3dzeaefmz"
moz-do-not-send="true">https://www.box.com/s/yxa4yxo6rcb3dzeaefmz</a>or  <a class="moz-txt-link-freetext"
href="http://dl.dropbox.com/u/203288/route_raw_spgist.sql.tar.gz"
moz-do-not-send="true">http://dl.dropbox.com/u/203288/route_raw_spgist.sql.tar.gz</a>. 
</pre></blockquote><pre wrap="">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
</pre></blockquote></blockquote><br /><b>Yes,I do confirm that. I consider to try to do further test in order to
reproduceit again in some way you can reproduce it simply.   </b><br /><br /><pre wrap="">Thanks again 

Best Regards!</pre><br /><br /><br /><br /><br />

<div class="moz-cite-prefix"><b>My dear tom lane && pgsql-hackers, </b><b><br /></b><b><br /></b><b>  did you
readthis email, I'm look forward to hearing from you at your earliest convenience.</b><b><br /></b><b><br /></b> <pre
class="code-java"><b><spanclass="short_text" id="result_box" lang="en"><span class="hps"><font color="#ff0000"><font
color="#000000">BestRegards!</font></font></span></span></b></pre><br /><br /> at 2013-05-21 01:10, Hailong Li
wrote:<br/></div><blockquote cite="mid:519A5917.40704@qunar.com" type="cite"><div class="moz-cite-prefix"><b>Hi,dear
tomlane && pgsql-hackers<br /><br /></b>This time I also give you the contents of the test table and some
detailsabout my new procedure reproduce it, please download the attachment.<b><br /><br /><font color="#ff0000"><br />
1.test environment</font><br /><br /></b><b>1.1 OS </b><br /><pre class="code-java">[root@localhost ~]# cat /etc/issue 
CentOS release 5.5 (Final)
Kernel \r on an \m

[root@localhost ~]# uname -av
Linux localhost 2.6.18-194.26.1.el5 #1 SMP Tue Nov 9 12:54:20 EST 2010 x86_64 x86_64 x86_64 GNU/Linux


1.2 PostgreSQL

[hailong.li@localhost ~]$ psql -U postgres
psql (9.2.4)
Type <span class="code-quote">"help"</span> <span class="code-keyword">for</span> help.

postgres=# select version();                                                   version

---------------------------------------------------------------------------------------------------------------PostgreSQL
9.2.4on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-50), 64-bit 
(1 row)


[postgres@localhost ~]$ /opt/pg92/bin/pg_controldata /export/pg920_data/
pg_control version number:            922
Catalog version number:               201204301
Database system identifier:           5830987352053120670
Database cluster state:               in production
pg_control last modified:             Mon 20 May 2013 05:50:09 PM CST
Latest checkpoint location:           4/DC6228C0
Prior checkpoint location:            4/DA443A08
Latest checkpoint's REDO location:    4/DC6228C0
Latest checkpoint's TimeLineID:       1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/843738
Latest checkpoint's NextOID:          5308503
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1672
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Time of latest checkpoint:            Mon 20 May 2013 05:47:40 PM CST
Minimum recovery ending location:     0/0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            minimal
Current max_connections setting:      100
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value

[postgres@localhost ~]$ /opt/pg92/bin/pg_config
BINDIR = /opt/pg92/bin
DOCDIR = /opt/pg92/share/doc/postgresql
HTMLDIR = /opt/pg92/share/doc/postgresql
INCLUDEDIR = /opt/pg92/include
PKGINCLUDEDIR = /opt/pg92/include/postgresql
INCLUDEDIR-SERVER = /opt/pg92/include/postgresql/server
LIBDIR = /opt/pg92/lib
PKGLIBDIR = /opt/pg92/lib/postgresql
LOCALEDIR = /opt/pg92/share/locale
MANDIR = /opt/pg92/share/man
SHAREDIR = /opt/pg92/share/postgresql
SYSCONFDIR = /opt/pg92/etc/postgresql
PGXS = /opt/pg92/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/tmp/build/specs/pg92.spec/opt/pg92' '--with-perl' '--with-libxml' '--with-libxslt'
'--with-ossp-uuid''CFLAGS= -march=core2 -O2 ' 
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -march=core2 -O2  -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels
-Wmissing-format-attribute-Wformat-security -fno-strict-aliasing -fwrapv 
CFLAGS_SL = -fpic
LDFLAGS = -Wl,-rpath,'/tmp/build/specs/pg92.spec/opt/pg92/lib',--enable-<span class="code-keyword">new</span>-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgport -lxslt -lxml2 -lz -lreadline -ltermcap -lcrypt -ldl -lm
VERSION = PostgreSQL 9.2.4

1.3 the data status in the test database


[hailong.li@localhost ~]$ psql -U postgres
psql (9.2.4)
Type <span class="code-quote">"help"</span> <span class="code-keyword">for</span> help.

postgres=# \l                                  List of databases   Name     |  Owner   | Encoding |   Collate   |
Ctype   |   Access privileges 
-------------+----------+----------+-------------+-------------+-----------------------postgres    | postgres | UTF8
| en_US.UTF-8 | en_US.UTF-8 |template0   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
      |          |          |             |             | postgres=CTc/postgrestemplate1   | postgres | UTF8     |
en_US.UTF-8| en_US.UTF-8 | =c/postgres          +            |          |          |             |             |
postgres=CTc/postgrestest_spgist| postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

postgres=# \c test_spgist
You are now connected to database <span class="code-quote">"test_spgist"</span> as user <span
class="code-quote">"postgres"</span>.
test_spgist=# \d                List of relationsSchema |        Name         |   Type   |  Owner
--------+---------------------+----------+----------<span class="code-keyword">public</span> | table_spgist        |
table   | postgres<span class="code-keyword">public</span> | table_spgist_id_seq | sequence | postgres 
(2 rows)

test_spgist=# \d table_spgist                                Table <span class="code-quote">"<span
class="code-keyword">public</span>.table_spgist"</span>Column|          Type           |
Modifiers
--------+-------------------------+-----------------------------------------------------------id     | integer
      | not <span class="code-keyword">null</span> <span class="code-keyword">default</span>
nextval('table_spgist_id_seq'::regclass)col1  | character varying(128)  |col2   | character varying(1000) | 
Indexes:   <span class="code-quote">"table_spgist_tmp_pkey"</span> PRIMARY KEY, btree (id)   <span
class="code-quote">"table_spgist_col1_idx"</span>spgist (col1)   <span
class="code-quote">"table_spgist_col2_idx"</span>spgist (col2) 

test_spgist=# select count(1) from table_spgist;count
-------40577
(1 row)


test_spgist=# select count(1),col1 from table_spgist group by col1 order by count desc limit 4;count |    col1
-------+------------10780 | qwertyuiop10423 | zxcvbnm10374 | asdfghjkl 1805 | 51766
(4 rows)

test_spgist=# select count(1),col2 from table_spgist group by col2 order by count desc limit 4;
-[ RECORD 1
]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 10780
col2  | http:<span class="code-comment">//suz.bacts.com/group/group_33_128972.aspx , <a class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623"
moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623</a>
,<a class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx"
moz-do-not-send="true">http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx</a>,
<aclass="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096"
moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096</a>
,<a class="moz-txt-link-freetext" href="http://www.gzl.com.cn/Travel/TeamD 
%0Aetail.aspx?tuanhao=TUCCZ120625A&xl=7957" moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tu
anhao=TUCCZ120625A&xl=7957</a> , <a class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_134782.html"
moz-do-not-send="true">http://www.ctszj.com.cn/route_group_1_134782.html</a>, <a class="moz-txt-link-freetext"
href="http://fj.bacts.com/group/group_33_113288.asp"
moz-do-not-send="true">http://fj.bacts.com/group/group_33_113288.asp</a>,  <a class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957"
moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957</a>,<a
class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.bacts.com/group/group_33_113288.aspx"
moz-do-not-send="true">http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.bacts.com/group/group_33_113288.aspx</a>
,<aclass="moz-txt-link-freetext" href="http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1"
moz-do-not-send="true">http://www.sz

travel.com.cn/travel/detail.aspx?xlid=19883&ctype=1</a> , <a class="moz-txt-link-freetext"
href="http://www.cts%0Azj.com.cn/route_group_1_134695.html"
moz-do-not-send="true">http://www.ctszj.com.cn/route_group_1_134695.html</a>
</span>-[ RECORD 2
]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 10423
col2  | http:<span class="code-comment">//ctsho.com/tour/international/tourinfo.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175734,<a class="moz-txt-link-freetext"
href="http://www.ctsho.com/tour/international/tourinfo"
moz-do-not-send="true">http://www.ctsho.com/tour/international/tourinfo</a>.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175593,<a class="moz-txt-link-freetext"
href="http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/159304&p=1032&cmpid=mkt_01000301"
moz-do-not-send="true">http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/159304&p=1032&cmpid=mkt_01000301</a>,
<aclass="moz-txt-link-freetext" href="http://www.hithr.cn/ReserveCenter/TourDetail.aspx?TourId=934433"
moz-do-not-send="true">http://www.hithr.cn/ReserveCenter/TourDetail.aspx?TourId=934433</a>,<a
class="moz-txt-link-freetext"href="http://www.gzl.com.cn/Travel/TeamInfo/1571602.html"
moz-do-not-send="true">http://www.g

zl.com.cn/Travel/TeamInfo/1571602.html</a>, <a class="moz-txt-link-freetext"
href="http://sz%0A.bacts.com/group/group_80_160847.aspx"
moz-do-not-send="true">http://sz.bacts.com/group/group_80_160847.aspx</a>,<a class="moz-txt-link-freetext"
href="http://sz.bacts.com/group/group_80_160762.aspx"
moz-do-not-send="true">http://sz.bacts.com/group/group_80_160762.aspx</a>,<a class="moz-txt-link-freetext"
href="http://ctsho.com/tour/international/tourinfo"
moz-do-not-send="true">http://ctsho.com/tour/international/tourinfo</a>.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175034,<a class="moz-txt-link-freetext"
href="http://ctsho.com/tour/international/tourinfo"
moz-do-not-send="true">http://ctsho.com/tour/international/tourinfo</a>.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175036,<a class="moz-txt-link-freetext"
href="http://km.gayosite.com/V2001I3001E393.html"moz-do-not-send="true">http://km.gayosite.com/V2001I3001E393.html</a> 
</span>-[ RECORD 3
]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 10374
col2  | http:<span class="code-comment">//market.tuniu.com/Partner_redirect.php?url=<a class="moz-txt-link-freetext"
href="http://www.tuniu.com/tours/467170&p=1032&cmpid=mkt_01000301"
moz-do-not-send="true">http://www.tuniu.com/tours/467170&p=1032&cmpid=mkt_01000301</a>,<a
class="moz-txt-link-freetext"
href="http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2102&to=3001&Info_id=36"
moz-do-not-send="true">http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2102&to=3001&Info_id=36</a>,
<aclass="moz-txt-link-freetext" href="http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html"
moz-do-not-send="true">http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html</a>,<a
class="moz-txt-link-freetext"
href="http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855"
moz-do-not-send="true">http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_i

d=855</a>, <a class="moz-txt-link-freetext" href="http://www.668trip.com/xianlu/xhtml/detail%0A-34975.html"
moz-do-not-send="true">http://www.668trip.com/xianlu/xhtml/detail-34975.html</a>,<a class="moz-txt-link-freetext"
href="http://www.51chuyou.com/line/2013/02/11/74177.html"
moz-do-not-send="true">http://www.51chuyou.com/line/2013/02/11/74177.html</a>,<a class="moz-txt-link-freetext"
href="http://jxtrvl.com/Line/kfrn6ijcqoacxoec96652e3cz3yx1pj56eux1tjcmeexxnw6romx1gewcou56guwx3yw1owm.htm"
moz-do-not-send="true">http://jxtrvl.com/Line/kfrn6ijcqoacxoec96652e3cz3yx1pj56eux1tjcmeexxnw6romx1gewcou56guwx3yw1owm.htm</a>,
<aclass="moz-txt-link-freetext"
href="http://www.jjtravel.com/teamDetail.action?uid=E48CDBB7-4B64-4D84-9EDD-66BA59AFE6F9"
moz-do-not-send="true">http://www.jjtravel.com/teamDetail.action?uid=E48CDBB7-4B64-4D84-9EDD-66BA59AFE6F9</a>,<a
class="moz-txt-link-freetext"href="http://www.17u.cn/dujia/tours.aspx?id=29801&RefId=19386866"
moz-do-not-send="true">http://www.

17u.cn/dujia/tours.aspx?id=29801&RefId=19386866</a>, <a class="moz-txt-link-freetext"
href="http://www.17u.cn/dujia/tours.aspx?id=29803&RefId=19386866"
moz-do-not-send="true">http://www.17u.cn/dujia/tours.aspx?id=29803&Re
fId=19386866</a>
</span>-[ RECORD 4
]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 1
col2  | http:<span class="code-comment">//www.tmyou.com.cn/Route/6tianfoguoniboerzhilv.Html
</span>
test_spgist=# select count(1) , col1, col2 from table_spgist group by col1, col2 order by count desc limit 4;
-[ RECORD 1
]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 10780
col1  | qwertyuiop
col2  | http:<span class="code-comment">//suz.bacts.com/group/group_33_128972.aspx , <a class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623"
moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623</a>
,<a class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx"
moz-do-not-send="true">http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx</a>,
<aclass="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096"
moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096</a>
,<a class="moz-txt-link-freetext" href="http://www.gzl.com.cn/Travel/TeamD 
%0Aetail.aspx?tuanhao=TUCCZ120625A&xl=7957" moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tu
anhao=TUCCZ120625A&xl=7957</a> , <a class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_134782.html"
moz-do-not-send="true">http://www.ctszj.com.cn/route_group_1_134782.html</a>, <a class="moz-txt-link-freetext"
href="http://fj.bacts.com/group/group_33_113288.asp"
moz-do-not-send="true">http://fj.bacts.com/group/group_33_113288.asp</a>,  <a class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957"
moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957</a>,<a
class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.bacts.com/group/group_33_113288.aspx"
moz-do-not-send="true">http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.bacts.com/group/group_33_113288.aspx</a>
,<aclass="moz-txt-link-freetext" href="http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1"
moz-do-not-send="true">http://www.sz

travel.com.cn/travel/detail.aspx?xlid=19883&ctype=1</a> , <a class="moz-txt-link-freetext"
href="http://www.cts%0Azj.com.cn/route_group_1_134695.html"
moz-do-not-send="true">http://www.ctszj.com.cn/route_group_1_134695.html</a>
</span>-[ RECORD 2
]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 10423
col1  | zxcvbnm
col2  | http:<span class="code-comment">//ctsho.com/tour/international/tourinfo.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175734,<a class="moz-txt-link-freetext"
href="http://www.ctsho.com/tour/international/tourinfo"
moz-do-not-send="true">http://www.ctsho.com/tour/international/tourinfo</a>.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175593,<a class="moz-txt-link-freetext"
href="http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/159304&p=1032&cmpid=mkt_01000301"
moz-do-not-send="true">http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/159304&p=1032&cmpid=mkt_01000301</a>,
<aclass="moz-txt-link-freetext" href="http://www.hithr.cn/ReserveCenter/TourDetail.aspx?TourId=934433"
moz-do-not-send="true">http://www.hithr.cn/ReserveCenter/TourDetail.aspx?TourId=934433</a>,<a
class="moz-txt-link-freetext"href="http://www.gzl.com.cn/Travel/TeamInfo/1571602.html"
moz-do-not-send="true">http://www.g

zl.com.cn/Travel/TeamInfo/1571602.html</a>, <a class="moz-txt-link-freetext"
href="http://sz%0A.bacts.com/group/group_80_160847.aspx"
moz-do-not-send="true">http://sz.bacts.com/group/group_80_160847.aspx</a>,<a class="moz-txt-link-freetext"
href="http://sz.bacts.com/group/group_80_160762.aspx"
moz-do-not-send="true">http://sz.bacts.com/group/group_80_160762.aspx</a>,<a class="moz-txt-link-freetext"
href="http://ctsho.com/tour/international/tourinfo"
moz-do-not-send="true">http://ctsho.com/tour/international/tourinfo</a>.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175034,<a class="moz-txt-link-freetext"
href="http://ctsho.com/tour/international/tourinfo"
moz-do-not-send="true">http://ctsho.com/tour/international/tourinfo</a>.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175036,<a class="moz-txt-link-freetext"
href="http://km.gayosite.com/V2001I3001E393.html"moz-do-not-send="true">http://km.gayosite.com/V2001I3001E393.html</a> 
</span>-[ RECORD 3
]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 10374
col1  | asdfghjkl
col2  | http:<span class="code-comment">//market.tuniu.com/Partner_redirect.php?url=<a class="moz-txt-link-freetext"
href="http://www.tuniu.com/tours/467170&p=1032&cmpid=mkt_01000301"
moz-do-not-send="true">http://www.tuniu.com/tours/467170&p=1032&cmpid=mkt_01000301</a>,<a
class="moz-txt-link-freetext"
href="http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2102&to=3001&Info_id=36"
moz-do-not-send="true">http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2102&to=3001&Info_id=36</a>,
<aclass="moz-txt-link-freetext" href="http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html"
moz-do-not-send="true">http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html</a>,<a
class="moz-txt-link-freetext"
href="http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855"
moz-do-not-send="true">http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_i

d=855</a>, <a class="moz-txt-link-freetext" href="http://www.668trip.com/xianlu/xhtml/detail%0A-34975.html"
moz-do-not-send="true">http://www.668trip.com/xianlu/xhtml/detail-34975.html</a>,<a class="moz-txt-link-freetext"
href="http://www.51chuyou.com/line/2013/02/11/74177.html"
moz-do-not-send="true">http://www.51chuyou.com/line/2013/02/11/74177.html</a>,<a class="moz-txt-link-freetext"
href="http://jxtrvl.com/Line/kfrn6ijcqoacxoec96652e3cz3yx1pj56eux1tjcmeexxnw6romx1gewcou56guwx3yw1owm.htm"
moz-do-not-send="true">http://jxtrvl.com/Line/kfrn6ijcqoacxoec96652e3cz3yx1pj56eux1tjcmeexxnw6romx1gewcou56guwx3yw1owm.htm</a>,
<aclass="moz-txt-link-freetext"
href="http://www.jjtravel.com/teamDetail.action?uid=E48CDBB7-4B64-4D84-9EDD-66BA59AFE6F9"
moz-do-not-send="true">http://www.jjtravel.com/teamDetail.action?uid=E48CDBB7-4B64-4D84-9EDD-66BA59AFE6F9</a>,<a
class="moz-txt-link-freetext"href="http://www.17u.cn/dujia/tours.aspx?id=29801&RefId=19386866"
moz-do-not-send="true">http://www.

17u.cn/dujia/tours.aspx?id=29801&RefId=19386866</a>, <a class="moz-txt-link-freetext"
href="http://www.17u.cn/dujia/tours.aspx?id=29803&RefId=19386866"
moz-do-not-send="true">http://www.17u.cn/dujia/tours.aspx?id=29803&Re
fId=19386866</a>
</span>-[ RECORD 4
]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 1
col1  | trip668
col2  | http:<span class="code-comment">//www.668trip.com/xianlu/html/1108-1487.html
</span>
test_spgist=# select count(1) from pg_stat_activity ;count
-------    1
(1 row)

<font color="#ff0000"><b>2. test procedure</b></font>
<big><b>2.1 insert</b>

I edit 3 shell scripts that function is insert data to table table_spgist</big>.
I create 15 clients and execute the 3 shell scripts in every 5 clients.

[postgres@localhost /tmp]$ <font color="#330099">cat insert_spgist1.sh</font>
#/bin/bash

PSQL=<span class="code-quote">"/opt/pg92/bin/psql"</span>
DB=test_spgist
USR=postgres

<span class="code-keyword">while</span> [ 10 -ne 9 ]
<span class="code-keyword">do</span>       SQL=<span class="code-quote">"insert into table_spgist (col1, col2) values
('qwertyuiop','http:<span class="code-comment">//suz.bacts.com/group/group_33_128972.aspx , <a
class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623"
moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623</a>
,<a class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx"
moz-do-not-send="true">http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx</a>,
<aclass="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096"
moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096</a>
,<a class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDe%0Atail.aspx?tuanhao=TUCCZ120625A&xl=7957"
moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957</a>, <a
class="moz-txt-link-freetext"href="http://www.ctszj.com.cn/route_group_1_134782.html"
moz-do-not-send="true">http://www.ctszj.com.cn/route_group_1_134782.html</a>, <a class="moz-txt-link-freetext"
href="http://fj.bacts.com/group/group_33_113288.asp"
moz-do-not-send="true">http://fj.bacts.com/group/group_33_113288.asp</a>,  <a class="moz-txt-link-freetext"
href="http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957"
moz-do-not-send="true">http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957</a>,<a
class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.bacts.com/group/group_33_113288.aspx"
moz-do-not-send="true">http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.b

acts.com/group/group_33_113288.aspx</a> ,<a class="moz-txt-link-freetext"
href="http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1"
moz-do-not-send="true">http://www.sztravel.com.cn/travel/det
ail.aspx?xlid=19883&ctype=1</a> , <a class="moz-txt-link-freetext"
href="http://www.ctszj.com.cn/route_group_1_134695.html"
moz-do-not-send="true">http://www.ctszj.com.cn/route_group_1_134695.html</a>')"</span> 
</span>        sleep 1       $PSQL  -U $USR -d $DB -c <span class="code-quote">"$SQL"</span>
done


[postgres@localhost /tmp]$ <font color="#330099">cat insert_spgist2.sh</font>
#/bin/bash

PSQL=<span class="code-quote">"/opt/pg92/bin/psql"</span>
DB=test_spgist
USR=postgres

<span class="code-keyword">while</span> [ 10 -ne 9 ]
<span class="code-keyword">do</span>       SQL=<span class="code-quote">"insert into table_spgist(col1, col2) values
('asdfghjkl','http:<span class="code-comment">//market.tuniu.com/Partner_redirect.php?url=<a
class="moz-txt-link-freetext"href="http://www.tuniu.com/tours/467170&p=1032&cmpid=mkt_01000301"
moz-do-not-send="true">http://www.tuniu.com/tours/467170&p=1032&cmpid=mkt_01000301</a>,<a
class="moz-txt-link-freetext"
href="http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2102&to=3001&Info_id=36"
moz-do-not-send="true">http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2102&to=3001&Info_id=36</a>,
<aclass="moz-txt-link-freetext" href="http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html"
moz-do-not-send="true">http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html</a>,<a
class="moz-txt-link-freetext"
href="http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855"
moz-do-not-send="true">h

ttp://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=8
55</a>, <a class="moz-txt-link-freetext" href="http://www.668trip.com/xianlu/xhtml/detail-34975.html"
moz-do-not-send="true">http://www.668trip.com/xianlu/xhtml/detail-34975.html</a>,<a class="moz-txt-link-freetext"
href="http://www.51chuyou.com/line/2013/02/11/74177.html"
moz-do-not-send="true">http://www.51chuyou.com/line/2013/02/11/74177.html</a>,<a class="moz-txt-link-freetext"
href="http://jxtrvl.com/Line/kfrn6ijcqoacxoec96652e3cz3yx1pj56eux1tjcmeexxnw6romx1gewcou56guwx3yw1owm.htm"
moz-do-not-send="true">http://jxtrvl.com/Line/kfrn6ijcqoacxoec96652e3cz3yx1pj56eux1tjcmeexxnw6romx1gewcou56guwx3yw1owm.htm</a>,
<aclass="moz-txt-link-freetext"
href="http://www.jjtravel.com/teamDetail.action?uid=E48CDBB7-4B64-4D84-9EDD-66BA59AFE6F9"
moz-do-not-send="true">http://www.jjtravel.com/teamDetail.action?uid=E48CDBB7-4B64-4D84-9EDD-66BA59AFE6F9</a>,<a
class="moz-txt-link-freetext"href="http://www.17u.cn/dujia/tours.aspx?id=29801&RefId=19386866"
moz-do-not-send="true">http://www.17u.cn

/dujia/tours.aspx?id=29801&RefId=19386866</a>, <a class="moz-txt-link-freetext"
href="http://www.17u.cn/dujia/t%0Aours.aspx?id=29803&RefId=19386866"
moz-do-not-send="true">http://www.17u.cn/dujia/tours.aspx?id=29803&RefId=19386866</a>')"</span> 
</span>        sleep 1       $PSQL  -U $USR -d $DB -c <span class="code-quote">"$SQL"</span>
done


[postgres@localhost /tmp]$ <font color="#330099">cat insert_spgist3.sh</font>
#/bin/bash

PSQL=<span class="code-quote">"/opt/pg92/bin/psql"</span>
DB=test_spgist
USR=postgres

<span class="code-keyword">while</span> [ 10 -ne 9 ]
<span class="code-keyword">do</span>       SQL=<span class="code-quote">"insert into table_spgist(col1, col2) values
('zxcvbnm','http:<span class="code-comment">//ctsho.com/tour/international/tourinfo.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175734,<a class="moz-txt-link-freetext"
href="http://www.ctsho.com/tour/international/tourinfo"
moz-do-not-send="true">http://www.ctsho.com/tour/international/tourinfo</a>.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175593,<a class="moz-txt-link-freetext"
href="http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/159304&p=1032&cmpid=mkt_01000301"
moz-do-not-send="true">http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/159304&p=1032&cmpid=mkt_01000301</a>,
<aclass="moz-txt-link-freetext" href="http://www.hithr.cn/ReserveCenter/TourDetail.aspx?TourId=934433"
moz-do-not-send="true">http://www.hithr.cn/ReserveCenter/TourDetail.aspx?TourId=934433</a>,<a class="moz-tx 

t-link-freetext" href="http://www.gzl.com.cn/Travel/TeamInfo/1571602.html" moz-do-not-send="true">http://www.gzl.co
m.cn/Travel/TeamInfo/1571602.html</a>, <a class="moz-txt-link-freetext"
href="http://sz.bacts.com/group/group_80_160847.aspx"
moz-do-not-send="true">http://sz.bacts.com/group/group_80_160847.aspx</a>,<a class="moz-txt-link-freetext"
href="http://sz.bacts.com/group/group_80_160762.aspx"
moz-do-not-send="true">http://sz.bacts.com/group/group_80_160762.aspx</a>,<a class="moz-txt-link-freetext"
href="http://ctsho.com/tour/international/tourinfo"
moz-do-not-send="true">http://ctsho.com/tour/international/tourinfo</a>.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175034,<a class="moz-txt-link-freetext"
href="http://ctsho.com/tour/international/tourinfo"
moz-do-not-send="true">http://ctsho.com/tour/international/tourinfo</a>.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175036,<a class="moz-txt-link-freetext"
href="http://km.gayosite.com/V2001I3001E393.html"moz-do-not-send="true">http://km.gayosite.com/V2001I3001E393.html</a>'
)"</span>
</span>        sleep 1       $PSQL  -U $USR -d $DB -c <span class="code-quote">"$SQL"</span>
done

<b>2.2 update</b>

<big>I edit 6 shell scripts that function is update data to table table_spgist</big>.
In the update statement, there is a subquery and my destionation is using the spgist index when execute the update
statement.
I create 6 clients and execute every shell script in each client. 

[postgres@localhost /tmp]$ <font color="#330099">cat update_spgist1.sh</font>
#/bin/bash

PSQL=<span class="code-quote">"/opt/pg92/bin/psql"</span>
DB=test_spgist
USR=postgres

<span class="code-keyword">while</span> [ 10 -ne 9 ]
<span class="code-keyword">do</span>       SQL=" update table_spgist                 set col1 = 'qwertyuiop' , col2 =
'http:<span
class="code-comment">//km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855'
</span>              where id in                         ( select id from table_spgist  where col2 like 'http:<span
class="code-comment">//suz.bacts.com%'offset 10000 limit 5 ) 
</span>            "       sleep 2       $PSQL  -U $USR -d $DB -c <span class="code-quote">"$SQL"</span>
done


[postgres@localhost /tmp]$ <font color="#330099">cat update_spgist2.sh</font>
#/bin/bash

PSQL=<span class="code-quote">"/opt/pg92/bin/psql"</span>
DB=test_spgist
USR=postgres

<span class="code-keyword">while</span> [ 10 -ne 9 ]
<span class="code-keyword">do</span>       SQL=" update table_spgist                 set col1 = 'asdfghjkl' , col2 =
'http:<spanclass="code-comment">//www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html' 
</span>              where id in                         ( select id from table_spgist where col2 like 'http:<span
class="code-comment">//market.tuniu.com%'offset 10000 limit 5 ) 
</span>            "       sleep 2       $PSQL  -U $USR -d $DB -c <span class="code-quote">"$SQL"</span>
done


[postgres@localhost /tmp/]$ <font color="#330099">cat update_spgist3.sh</font>
#/bin/bash

PSQL=<span class="code-quote">"/opt/pg92/bin/psql"</span>
DB=test_spgist
USR=postgres

<span class="code-keyword">while</span> [ 10 -ne 9 ]
<span class="code-keyword">do</span>       SQL=" update table_spgist                 set col1 = 'zxcvbnm', col2 =
'http:<spanclass="code-comment">//ctsho.com/tour/international/tourinfo.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175036'
</span>              where id in                         ( select id from table_spgist where col2 like 'http:<span
class="code-comment">//ctsho.com/tour/international%'offset 10000 limit 5 ) 
</span>            "       sleep 2       $PSQL  -U $USR -d $DB -c <span class="code-quote">"$SQL"</span>
done


[postgres@localhost /tmp]$ <font color="#330099">cat update_spgist4.sh</font>
#/bin/bash

PSQL=<span class="code-quote">"/opt/pg92/bin/psql"</span>
DB=test_spgist
USR=postgres

<span class="code-keyword">while</span> [ 10 -ne 9 ]
<span class="code-keyword">do</span>       SQL=" update table_spgist                 set col1 = 'qwertyuiopqwertyuiop',
col2= 'http:<span
class="code-comment">//km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855,<a
class="moz-txt-link-freetext"
href="http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855"
moz-do-not-send="true">http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855</a>'
</span>              where id in                         ( select id from table_spgist  where col1 like 'qwertyuiop'
offset10000 limit 5 )           "       sleep 2       $PSQL  -U $USR -d $DB -c <span class="code-quote">"$SQL"</span> 
done


[postgres@localhost /tmp]$ <font color="#330099">cat update_spgist5.sh</font>
#/bin/bash

PSQL=<span class="code-quote">"/opt/pg92/bin/psql"</span>
DB=test_spgist
USR=postgres

<span class="code-keyword">while</span> [ 10 -ne 9 ]
<span class="code-keyword">do</span>       SQL=" update table_spgist                 set col1 = 'asdfghjklsdfghjkl' ,
col2= 'http:<span class="code-comment">//www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html,<a
class="moz-txt-link-freetext"href="http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html"
moz-do-not-send="true">http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html</a>'
</span>              where id in                         ( select id from table_spgist where col1 like 'asdfghjkl%'
offset10000 limit 5 )           "       sleep 2       $PSQL  -U $USR -d $DB -c <span class="code-quote">"$SQL"</span> 
done


[postgres@localhost /tmp]$ <font color="#330099">cat update_spgist6.sh</font>
#/bin/bash

PSQL=<span class="code-quote">"/opt/pg92/bin/psql"</span>
DB=test_spgist
USR=postgres

<span class="code-keyword">while</span> [ 10 -ne 9 ]
<span class="code-keyword">do</span>       SQL=" update table_spgist                 set col1 = 'zxcvbnmzxcvbnm', col2
='http:<span class="code-comment">//ctsho.com/tour/international/tourinfo.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175036,<aclass="moz-txt-link-freetext"
href="http://ctsho.com/tour/international/tourinfo"
moz-do-not-send="true">http://ctsho.com/tour/international/tourinfo</a>.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175036'
</span>              where id in                         ( select id from table_spgist where col1 like 'zxcvbnm%'
offset10000 limit 5 )           "       sleep 2       $PSQL  -U $USR -d $DB -c <span class="code-quote">"$SQL"</span> 
done

<b>2.3 delete</b>

<big>I edit 3 shell scripts that function is delete data to table table_spgist</big>.
In the update statement, there is also a subquery and my destination is using the spgist index when execute the delete
statement.
I create 3 clients and execute every shell script in each client. 
  
[postgres@localhost /tmp]$ <font color="#330099">cat delete_spgist1.sh</font>
#/bin/bash

PSQL=<span class="code-quote">"/opt/pg92/bin/psql"</span>
DB=test_spgist
USR=postgres

<span class="code-keyword">while</span> [ 10 -ne 9 ]
<span class="code-keyword">do</span>       SQL=" delete                 from  table_spgist             where id in
                  ( select id from table_spgist where col2 like 'http:<span
class="code-comment">//km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855'or col1
like'qwertyuiop%' offset 10000  limit 10 ) 
</span>            "       sleep 4       $PSQL  -U $USR -d $DB -c <span class="code-quote">"$SQL"</span>
done


[postgres@localhost /tmp]$ <font color="#330099">cat delete_spgist2.sh</font>
#/bin/bash

PSQL=<span class="code-quote">"/opt/pg92/bin/psql"</span>
DB=test_spgist
USR=postgres

<span class="code-keyword">while</span> [ 10 -ne 9 ]
<span class="code-keyword">do</span>       SQL=" delete                 from table_spgist             where id in
                 ( select id from table_spgist where col2 like  'http:<span
class="code-comment">//www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html'or col1 like 'asdfghjkl%' offset
10000 limit 10 ) 
</span>            "       sleep 4       $PSQL  -U $USR -d $DB -c <span class="code-quote">"$SQL"</span>
done



[postgres@localhost /tmp$ <font color="#330099">cat delete_spgist3.sh</font>
#/bin/bash

PSQL=<span class="code-quote">"/opt/pg92/bin/psql"</span>
DB=test_spgist
USR=postgres

<span class="code-keyword">while</span> [ 10 -ne 9 ]
<span class="code-keyword">do</span>       SQL=" delete                 from table_spgist             where id in
                 ( select id from table_spgist where col2 like 'http:<span
class="code-comment">//ctsho.com/tour/international/tourinfo.<span
class="code-keyword">do</span>?tourQueryDto.lineId=175036'or col1 like 'zxcvbnm%' offset 10000 limit 10 ) 
</span>            "       sleep 4       $PSQL  -U $USR -d $DB -c <span class="code-quote">"$SQL"</span>
done

<b>2.4 monitor</b>

I execute the statement '<font color="#ff0000">select * from pg_stat_activity;</font> ' and '<font
color="#ff0000">selectcount(1) from route_raw;</font> ' to monitor the data and connections. 

<b><font color="#ff0000">3. the phenonmenon when the </font></b><span class="short_text" id="result_box"
lang="en"><b><fontcolor="#ff0000"><span class="">suspicious</span> '</font></b><span class="hps"><b><font
color="#ff0000">bug'comes up<font color="#000000"> 


</font></font></b><font color="#ff0000"><font color="#000000">test_spgist=# select count(1) from table_spgist;count
-------44690
(1 row)
......
......
......
</font></font></span></span>
<span class="short_text" id="result_box" lang="en"><span class="hps"><font color="#ff0000"><font color="#000000"><span
class="short_text"id="result_box" lang="en"><span class="hps">test_spgist=# select count(1) from table_spgist;count  
-------44690
(1 row)

test_spgist=# select count(1) from pg_stat_activity ;count
-------  95
(1 row)
......
......
......
(1 row)

test_spgist=# select count(1) from pg_stat_activity ;count
-------  95
(1 row)
</span></span></font></font><b><font color="#ff0000">
</font></b><font color="#ff0000"><font color="#000000">The count of records in table </font></font></span></span><span
class="short_text"id="result_box" lang="en"><span class="hps"><font color="#ff0000"><span class="short_text"
id="result_box"lang="en"><span class="hps"><font color="#ff0000"><font color="#000000"><span class="short_text"
id="result_box"lang="en"><span class="hps">table_spgist is no longer continue to increase and the count of connections
isno longer changes. 

</span></span></font></font></span></span></font><b><font color="#ff0000"><span class="short_text" id="result_box"
lang="en"><spanclass="hps"><font color="#ff0000"><font color="#000000"><span class="short_text" id="result_box"
lang="en"><spanclass="hps"> 
</span></span></font></font></span></span><font color="#000000">3.1 kill the process in OS

</font></font></b></span></span><span class="short_text" id="result_box" lang="en"><span class="hps"><font
color="#ff0000"><fontcolor="#000000"><span class="short_text" id="result_box" lang="en"><span class="hps"><font
color="#ff0000"><fontcolor="#000000">[root@localhost /tmp]# insert_spgist1.sh >/dev/null 
Cancel request sent
Cancel request sent
Cancel request sent</font></font></span></span></font></font></span></span>
<span class="short_text" id="result_box" lang="en"><span class="hps"><font color="#ff0000"><font color="#000000"><span
class="short_text"id="result_box" lang="en"><span class="hps"><font color="#ff0000"><font
color="#000000">[root@localhost/tmp]# update_spgist1.sh >/dev/null 
Cancel request sent
Cancel request sent
Cancel request sent</font></font></span></span>
[root@localhost /tmp]# delete_spgist1.sh >/dev/null
Cancel request sent
Cancel request sent
Cancel request sent

[root@localhost ~]# ps auxww | grep postgres | grep   -i -E 'INSERT|UPDATE|DETELE'
root     12629  0.0  0.0  61196   784 pts/0    S+   00:15   0:00 grep postgres
postgres 22765  0.0  0.0 1700188 4652 ?        Ss   May20   0:00 postgres: postgres test_spgist [local] INSERT
postgres 22766  0.0  0.0 1700188 4644 ?        Ss   May20   0:00 postgres: postgres test_spgist [local] INSERT
postgres 22770  0.0  0.1 1700624 36800 ?       Ss   May20   0:00 postgres: postgres test_spgist [local] UPDATE
postgres 22772  0.0  0.0 1700188 4592 ?        Ss   May20   0:00 postgres: postgres test_spgist [local] INSERT
postgres 22775  0.0  0.1 1700476 35820 ?       Ss   May20   0:00 postgres: postgres test_spgist [local] UPDATE
postgres 22777  0.0  0.0 1700188 4584 ?        Ss   May20   0:00 postgres: postgres test_spgist [local] INSERT
postgres 22779  0.0  0.1 1700624 35828 ?       Ss   May20   0:00 postgres: postgres test_spgist [local] UPDATE
root     22780  0.0  0.0  84724  1424 pts/8    S+   May20   0:00 /opt/pg92/bin/psql -U postgres -d test_spgist -c
delete?  from  table_spgist ? where id in ? ( select id from table_spgist where col2 like '<a
class="moz-txt-link-freetext"
href="http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855"
moz-do-not-send="true">http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855</a>'
orcol1 like 'qwertyuiop%' offset 10000  limit 10 )?             
postgres 22781  0.0  0.0 1700664 6388 ?        Ss   May20   0:00 postgres: postgres test_spgist [local] DELETE
postgres 22785  0.0  0.0 1700188 4576 ?        Ss   May20   0:00 postgres: postgres test_spgist [local] INSERT
......
......
......
postgres 22806  0.0  0.0 1700192 4584 ?        Ss   May20   0:00 postgres: postgres test_spgist [local] INSERT
postgres 22810  0.0  0.1 1700476 36696 ?       Ss   May20   0:00 postgres: postgres test_spgist [local] UPDATE
postgres 22812  0.0  0.1 1700476 35464 ?       Ss   May20   0:00 postgres: postgres test_spgist [local] UPDATE
root     22813  0.0  0.0  84724  1420 pts/9    S+   May20   0:00 /opt/pg92/bin/psql -U postgres -d test_spgist -c
delete?   from table_spgist? where id in ? ( select id from table_spgist where col2 like  '<a
class="moz-txt-link-freetext"href="http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html"
moz-do-not-send="true">http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html</a>'or col1 like
'asdfghjkl%'offset 10000  limit 10 )?             
postgres 22814  0.0  0.1 1700372 36652 ?       Ss   May20   0:00 postgres: postgres test_spgist [local] DELETE waiting
root     22815  0.0  0.0  84724  1420 pts/10   S+   May20   0:00 /opt/pg92/bin/psql -U postgres -d test_spgist -c
delete?   from table_spgist? where id in ? ( select id from table_spgist where col2 like '<a
class="moz-txt-link-freetext"href="http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175036"
moz-do-not-send="true">http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175036</a>'or col1 like
'zxcvbnm%'offset 10000 limit 10 )?             
postgres 22816  0.0  0.0 1700372 6108 ?        Ss   May20   0:00 postgres: postgres test_spgist [local] DELETE
postgres 25634  0.0  0.0 1700192 4592 ?        Ss   00:09   0:00 postgres: postgres test_spgist [local] INSERT
......
......
......
postgres 26098  0.0  0.0 1700196 4616 ?        Ss   00:10   0:00 postgres: postgres test_spgist [local] INSERT



[root@localhost ~]# ps auxww | grep postgres | grep  -c -i -E 'INSERT|UPDATE|DETELE'
95
[root@localhost ~]# ps auxww | grep postgres | grep  -i -E 'INSERT|UPDATE|DETELE' | awk '{print $2}' | xargs kill
[root@localhost ~]# ps auxww | grep postgres | grep  -c -i -E 'INSERT|UPDATE|DELETE'
95</font></font><b><font color="#ff0000"><font color="#000000">
<font color="#ff0000">
We can see above,I can not kill the process.</font>

3.2 kill the process in </font></font></b></span></span><span class="short_text" id="result_box" lang="en"><span
class="hps"><b><fontcolor="#ff0000"><font color="#000000"><span class="short_text" id="result_box" lang="en"><span
class="hps"><b><spanclass="short_text" id="result_box" lang="en"><span
class="hps"><b>PostgreSQL</b></span></span></b></span></span>server 

</font></font></b><font color="#ff0000"><font color="#000000">test_spgist=# select count(1) from pg_stat_activity
;count 
-------  96
(1 row)
test_spgist=# select pg_cancel_backend(pid) from pg_stat_activity where query ~ 'insert|update|delete' and not
pg_backend_pid()=pid;pg_cancel_backend 
-------------------ttt
...
...
...
test_spgist=# select pg_terminate_backend(pid) from pg_stat_activity where query ~ 'insert|update|delete' and not
pg_backend_pid()=pid;pg_terminate_backend 
----------------------ttt
...
...
...

test_spgist=# select count(1) from pg_stat_activity ;count
-------  96
(1 row)</font></font><b><font color="#ff0000"><font color="#000000">
</font></font></b></span></span><span class="short_text" id="result_box" lang="en"><span class="hps"><b><font
color="#ff0000"><fontcolor="#000000"><span class="short_text" id="result_box" lang="en"><span class="hps"><b><font
color="#ff0000"><fontcolor="#000000"> 
<font color="#ff0000">We can see above,I also can not kill the process in the PostgreSQL server.</font>

</font></font></b></span></span>
</font></font></b><font color="#ff0000"><font color="#000000">If you need some more detailed information, please tell
meand I'll give it to you.</font></font><b><font color="#ff0000"><font color="#000000"> 


</font></font></b></span></span><span class="short_text" id="result_box" lang="en"><span class="hps"><b><font
color="#ff0000"><fontcolor="#000000">Thanks again 

Best Regards!</font>
</font></b> 
</span></span></pre></div></blockquote><br />