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: