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 | 51B002F3.7010104@qunar.com Whole thread Raw |
| In response to | Re: I s this a bug of spgist index in a heavy write condition? (李海龙 <hailong.li@qunar.com>) |
| List | pgsql-hackers |
<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 />
pgsql-hackers by date: