Re: I s this a bug of spgist index in a heavy write condition? - Mailing list pgsql-hackers

From 李海龙
Subject Re: I s this a bug of spgist index in a heavy write condition?
Date
Msg-id 50EBF992.2000704@qunar.com
Whole thread Raw
In response to I s this a bug of spgist index in a heavy write condition?  (李海龙 <hailong.li@qunar.com>)
Responses Re: I s this a bug of spgist index in a heavy write condition?
List pgsql-hackers
<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 />

pgsql-hackers by date:

Previous
From: Magnus Hagander
Date:
Subject: Re: PL/Python result object str handler
Next
From: Hannu Krosing
Date:
Subject: Re: Improve compression speeds in pg_lzcompress.c