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

Hi,pgsql-hackers,

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

OS Version:
CentOS release 6.2 (Final)

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

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

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

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

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

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

backtrace for pid 17225, we found there is running some function named
like spgxxxx
> Missing separate debuginfos, use: debuginfo-install
pg92-9.2.2-2.el6.x86_64
> (gdb) bt
> #0 0x0000003d6baea747 in semop () from /lib64/libc.so.6
> #1 0x00000000005fef57 in PGSemaphoreLock ()
> #2 0x000000000063f1a4 in LWLockAcquire ()
> #3 0x00000000004b7002 in spgdoinsert ()
> #4 0x00000000004b25e1 in spginsert ()
> #5 0x00000000007155a4 in FunctionCall6Coll ()
> #6 0x000000000047b4e0 in index_insert ()
> #7 0x00000000005871d2 in ExecInsertIndexTuples ()
> #8 0x0000000000594747 in ExecModifyTable ()
> #9 0x000000000057de18 in ExecProcNode ()
> #10 0x000000000057ccb1 in standard_ExecutorRun ()
> #11 0x000000000064e93f in ProcessQuery ()
> #12 0x000000000064eb6f in PortalRunMulti ()
> #13 0x000000000064f32e in PortalRun ()
> #14 0x000000000064cba2 in PostgresMain ()
> #15 0x000000000060a547 in ServerLoop ()
> #16 0x000000000060d077 in PostmasterMain ()
> #17 0x00000000005ac3d0 in main ()

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

Thanks

Best Regards!

=?utf-8?B?5p2O5rW36b6Z?= <hailong(dot)li(at)qunar(dot)com> writes:
> We have lots data to insert in that table which have the spgist index,
> may be the spgist index have a bug on a heavy write condition?

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

regards, tom lane

hi,

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

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

Hi,dear tom lane && pgsql-hackers

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

OS Version:
CentOS release 5.5 (Final)

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

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

Some details is shown as following:

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

test_spgist=# \d route_raw
Table "public.route_raw"
Column | Type | Modifiers
----------------------+-----------------------------+---------------------------------------------
sourceurl | character varying(1000) | not null default ''::character varying
title | character varying(200) | default NULL::character varying
price | numeric(10,0) | default NULL::numeric
routesnapshot | character varying(4024) | default NULL::character varying
routesnapshot_small | character varying(800) | default NULL::character varying
routesnapshot_mid | character varying(800) | default NULL::character varying
feature | text |
departure | character varying(50) | default NULL::character varying
arrive | character varying(500) | default NULL::character varying
function | character varying(50) | default NULL::character varying
type | character varying(50) | default NULL::character varying
subject | text |
dateofdeparture | timestamp without time zone |
dateofexpire | timestamp without time zone |
dateofbookingexpire | timestamp without time zone |
itineraryday | integer |
sightspot | text |
traffic | character varying(100) | default NULL::character varying
stargrade | double precision |
region | character varying(50) | default NULL::character varying
subregion | character varying(50) | default NULL::character varying
currency | character varying(45) | default NULL::character varying
listinfo | character varying(1024) | default NULL::character varying
dateofload | timestamp without time zone |
urlkey | character varying(64) | not null default ''::character varying
sightspot_b | text |
sightspot_c | text |
wrapperid | character varying(128) | default NULL::character varying
wrappername | character varying(50) | default NULL::character varying
toflight | character varying(45) | default NULL::character varying
backflight | character varying(45) | default NULL::character varying
raw_date | character varying(2048) | default NULL::character varying
feeinfo | text |
others | text |
feeinclude | character varying(1000) | default NULL::character varying
feeexclude | character varying(1000) | default NULL::character varying
ownexpense | character varying(1000) | default NULL::character varying
bookingterms | character varying(1000) | default NULL::character varying
visainfos | character varying(1000) | default NULL::character varying
shopinfos | character varying(500) | default NULL::character varying
flightinfos | character varying(500) | default NULL::character varying
spotofdeparture | character varying(20) | default NULL::character varying
spotofback | character varying(20) | default NULL::character varying
tips | character varying(1000) | default NULL::character varying
contact | character varying(100) | default NULL::character varying
raw_price | character varying(100) | default NULL::character varying
raw_bookexpiredate | character varying(100) | default NULL::character varying
transfer | character varying(20) | default NULL::character varying
contractstyle | character varying(500) | default NULL::character varying
citiesofbook | character varying(1000) | default NULL::character varying
ishotelselected | character varying(20) | not null default 'false'::character varying
freetripitineraryday | integer | not null default (-1)
suitcrowdsubject | character varying(50) | default NULL::character varying
newsubject | character varying(50) | default NULL::character varying
isflightselected | character varying(20) | not null default 'false'::character varying
festival | character varying(512) | default NULL::character varying
promotion | character varying(1024) |
Indexes:
"route_raw_1_idx" btree (price, departure, dateofdeparture, itineraryday)
"route_raw_sourceurl_idx1" spgist (sourceurl)

test_spgist=# select sourceurl from route_raw limit 5;
sourceurl
---------------------------------------------------------------------------------------------------------------
http://suz.bacts.com/group/group_33_128972.aspx
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623
http://www.ctszj.com.cn/route_group_1_147707.html
http://jx.bacts.com/group/group_33_118381.aspx
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096
(5 rows)

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

My some deteails the test procedure is as follows.

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

[hailong(dot)li(at)pgdb2 ~]$ cat /tmp/insert_spgist.sh
#/bin/bash

export PGPASSWORD="6b4df779-dd13-4031-a00b-126b49287995"
PSQL="/opt/pg92/bin/psql"
DBHOST=l-pgdb2.s.cn6
DB=test_spgist
USR=postgres

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

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

[hailong(dot)li(at)l-pgdb2(dot)s(dot)cn6<mailto:hailong(dot)li(at)l-pgdb2(dot)s(dot)cn6> ~]$ /tmp/insert_spgist.sh >/dev/null
...
...
...
[hailong(dot)li(at)l-pgdb2(dot)s(dot)cn6<mailto:hailong(dot)li(at)l-pgdb2(dot)s(dot)cn6> ~]$ /tmp/insert_spgist.sh >/dev/null

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

test_spgist=# select * from pg_stat_activity ;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start |
query_start | state_change | waiting | state |
;
query
;
;
-------+-------------+-------+----------+----------+------------------+-----------------+-----------------+-------------+-------------------------------+-------------------------------+------
-------------------------+-------------------------------+---------+--------+------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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-
01-08 17:16:01.997493+08 | 2013-01-08 17:16:01.997495+08 | f | active | select * from pg_stat_activity ;

16384 | test_spgist | 16858 | 10 | postgres | psql | | | -1 | 2013-01-08 17:04:15.000472+08 | | 2013-
01-08 17:15:01.303197+08 | 2013-01-08 17:15:02.372496+08 | f | idle | select count(1) from route_raw ;

16384 | test_spgist | 26097 | 10 | postgres | psql | 192.168.232.122 | | 54637 | 2013-01-08 17:12:16.228789+08 | 2013-01-08 17:12:16.231496+08 | 2013-
01-08 17:12:16.231496+08 | 2013-01-08 17:12:16.231504+08 | f | active | insert into route_raw(sourceurl) values ('http://suz.bacts.com/group/group_33_128972.aspx , http://www.gzl.com.cn
/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623 , http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx, ht
tp://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=TUCCZ120625A&xl=7957 , http://www.ctszj.com.cn/
route_group_1_134782.html , http://fj.bacts.com/group/group_33_113288.asp , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957, http://www.ctszj.com.cn/route_group_1_1
34782.html,http://fj.bacts.com/group/group_33_113288.aspx ,http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1 , http://www.ctszj.com.cn/route_group_1_134695.html ')
......
......
(17 rows)

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

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

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

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

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

......
......

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

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.

4. display process information using gdb

[hailong(dot)li(at)l-pgdb2(dot)s(dot)cn6<mailto:hailong(dot)li(at)l-pgdb2(dot)s(dot)cn6> ~]$ sudo gdb
GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-23.el5_5.2)
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html><http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/><http://www.gnu.org/software/gdb/bugs/>.
(gdb) attach 26097
Attaching to process 26097
Reading symbols from /opt/pg92/bin/postgres...done.
Reading symbols from /usr/lib64/libxslt.so.1...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libxslt.so.1
Reading symbols from /usr/lib64/libxml2.so.2...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libxml2.so.2
Reading symbols from /lib64/libcrypt.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libcrypt.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /usr/lib64/libz.so.1...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libz.so.1
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnss_files.so.2
0x00000039e4ed5337 in semop () from /lib64/libc.so.6
(gdb) bt
#0 0x00000039e4ed5337 in semop () from /lib64/libc.so.6
#1 0x00000000006985f3 in PGSemaphoreLock (sema=0x2b4cb1e2bb30, interruptOK=0 '\000') at pg_sema.c:415
#2 0x00000000006ea043 in LWLockAcquire (lockid=355054, mode=LW_EXCLUSIVE) at lwlock.c:474
#3 0x00000000006d0739 in LockBuffer (buffer=177443, mode=2) at bufmgr.c:2533
#4 0x00000000004d9ab0 in spgdoinsert (index=0x2b4cb26edcc0, state=0x7fffb8ce3f30, heapPtr=0xb574124, datum=190267736, isnull=0 '\000') at spgdoinsert.c:1940
#5 0x00000000004d1880 in spginsert (fcinfo=0x7fffb8ce3fe0) at spginsert.c:220
#6 0x000000000080c921 in FunctionCall6Coll (flinfo=0xb5702a0, collation=0, arg1=47608411118784, arg2=140736293913696, arg3=140736293913664, arg4=190267684, arg5=47608411039440, arg6=0)
at fmgr.c:1439
#7 0x0000000000489526 in index_insert (indexRelation=0x2b4cb26edcc0, values=0x7fffb8ce4460, isnull=0x7fffb8ce4440 "", heap_t_ctid=0xb574124, heapRelation=0x2b4cb26da6d0,
checkUnique=UNIQUE_CHECK_NO) at indexam.c:216
#8 0x00000000005eaf3c in ExecInsertIndexTuples (slot=0xb56e520, tupleid=0xb574124, estate=0xb56e290) at execUtils.c:1087
#9 0x00000000005fcfb5 in ExecInsert (slot=0xb56e520, planSlot=0xb56e520, estate=0xb56e290, canSetTag=1 '\001') at nodeModifyTable.c:248
#10 0x00000000005fde2e in ExecModifyTable (node=0xb56e5b0) at nodeModifyTable.c:848
#11 0x00000000005ddde3 in ExecProcNode (node=0xb56e5b0) at execProcnode.c:376
#12 0x00000000005dc24e in ExecutePlan (estate=0xb56e290, planstate=0xb56e5b0, operation=CMD_INSERT, sendTuples=0 '\000', numberTuples=0, direction=ForwardScanDirection, dest=0xb556160)
at execMain.c:1393
#13 0x00000000005daa2a in standard_ExecutorRun (queryDesc=0xb486830, direction=ForwardScanDirection, count=0) at execMain.c:301
#14 0x00000000005da922 in ExecutorRun (queryDesc=0xb486830, direction=ForwardScanDirection, count=0) at execMain.c:249
#15 0x00000000006fb344 in ProcessQuery (plan=0xb50ce80,
sourceText=0xb50b500 "insert into route_raw(sourceurl) values ('http://suz.bacts.com/group/group_33_128972.aspx , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=1262"..., params=0x0, dest=0xb556160, completionTag=0x7fffb8ce4ae0 "") at pquery.c:185
#16 0x00000000006fcaa9 in PortalRunMulti (portal=0xb484820, isTopLevel=1 '\001', dest=0xb556160, altdest=0xb556160, completionTag=0x7fffb8ce4ae0 "") at pquery.c:1277
#17 0x00000000006fc183 in PortalRun (portal=0xb484820, count=9223372036854775807, isTopLevel=1 '\001', dest=0xb556160, altdest=0xb556160, completionTag=0x7fffb8ce4ae0 "") at pquery.c:814
#18 0x00000000006f66da in exec_simple_query (
query_string=0xb50b500 "insert into route_raw(sourceurl) values ('http://suz.bacts.com/group/group_33_128972.aspx , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=1262"...) at postgres.c:1046
#19 0x00000000006fa5bd in PostgresMain (argc=2, argv=0xb4664e8, username=0xb4663f0 "postgres") at postgres.c:3958
#20 0x00000000006a9305 in BackendRun (port=0xb489290) at postmaster.c:3619
#21 0x00000000006a8a02 in BackendStartup (port=0xb489290) at postmaster.c:3304
#22 0x00000000006a591a in ServerLoop () at postmaster.c:1367
#23 0x00000000006a52a8 in PostmasterMain (argc=3, argv=0xb464660) at postmaster.c:1127
#24 0x000000000061cfcb in main (argc=3, argv=0xb464660) at main.c:199

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!

at 2012-12-14 12:00, hailong.li wrote:

hi,

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

at 2012-12-14 00:16, Tom Lane wrote:

=?utf-8?B?5p2O5rW36b6Z?= <hailong(dot)li(at)qunar(dot)com><mailto:hailong(dot)li(at)qunar(dot)com> writes:

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

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

regards, tom lane

at 2012-12-13 23:46, hailong.li wrote:

Hi,pgsql-hackers,

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

OS Version:
CentOS release 6.2 (Final)

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

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

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

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

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

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

backtrace for pid 17225, we found there is running some function named
like spgxxxx
> Missing separate debuginfos, use: debuginfo-install
pg92-9.2.2-2.el6.x86_64
> (gdb) bt
> #0 0x0000003d6baea747 in semop () from /lib64/libc.so.6
> #1 0x00000000005fef57 in PGSemaphoreLock ()
> #2 0x000000000063f1a4 in LWLockAcquire ()
> #3 0x00000000004b7002 in spgdoinsert ()
> #4 0x00000000004b25e1 in spginsert ()
> #5 0x00000000007155a4 in FunctionCall6Coll ()
> #6 0x000000000047b4e0 in index_insert ()
> #7 0x00000000005871d2 in ExecInsertIndexTuples ()
> #8 0x0000000000594747 in ExecModifyTable ()
> #9 0x000000000057de18 in ExecProcNode ()
> #10 0x000000000057ccb1 in standard_ExecutorRun ()
> #11 0x000000000064e93f in ProcessQuery ()
> #12 0x000000000064eb6f in PortalRunMulti ()
> #13 0x000000000064f32e in PortalRun ()
> #14 0x000000000064cba2 in PostgresMain ()
> #15 0x000000000060a547 in ServerLoop ()
> #16 0x000000000060d077 in PostmasterMain ()
> #17 0x00000000005ac3d0 in main ()

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

Thanks

Best Regards!

=?gb2312?B?wO66o8H6?= <hailong(dot)li(at)qunar(dot)com> writes:
> I am very excited to say that I may have created a test case!

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

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

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

regards, tom lane

I wrote:
> The control flow in spgdoinsert.c is flat enough that the stack trace
> alone isn't much help in understanding the bug, I'm afraid.

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

regards, tom lane

Hi,dear tom lane && pgsql-hackers

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

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

OS Version:
CentOS release 5.5 (Final)

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

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

Some details is shown as following:

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

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

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

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

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

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

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

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

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

3. only 1 connection to update the table

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

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

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

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

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

......
......

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

......
......

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

test_spgist=# select * from pg_stat_activity ;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start |
query_start | state_change | waiting | state |
&nb sp;
query
-------+-------------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+----------
---------------------+-------------------------------+---------+--------+----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16384 | test_spgist | 18797 | 10 | postgres | psql | | | -1 | 2013-01-10 20:27:05.171787+08 | | 2013-01-1
0 20:29:05.393515+08 | 2013-01-10 20:29:06.126108+08 | f | idle | select * from route_raw where sourceurl like '%CGZL120701MDG05LA&xl%';
16384 | test_spgist | 22272 | 10 | postgres | psql | | | -1 | 2013-01-10 18:23:20.035532+08 | 2013-01-10 20:31:57.898985+08 | 2013-01-1
0 20:31:57.898985+08 | 2013-01-10 20:31:57.898986+08 | f | active | select * from pg_stat_activity ;
16384 | test_spgist | 24391 | 10 | postgres | psql | | | -1 | 2013-01-10 20:29:07.113574+08 | 2013-01-10 20:29:07.1161+08 | 2013-01-1
0 20:29:07.1161+08 | 2013-01-10 20:29:07.116107+08 | f | active | insert into route_raw(sourceurl) values ('http://suz.bacts.com/group/group_33_128972.aspx , http://www.gzl.com.cn/Tra
vel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623 , http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx, http:/
/www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096 , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957 , http://www.ctszj.com.cn/rout
e_group_1_134782.html , http://fj.bacts.com/group/group_33_113288.asp , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957, http://www.ctszj.com.cn/route_group_1_13478
2.html,http://fj.bacts.com/group/group_33_113288.aspx ,http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1 , http://www.ctszj.com.cn/route_group_1_134695.html ')

......
......

16384 | test_spgist | 24437 | 10 | postgres | psql | | | -1 | 2013-01-10 20:29:38.498181+08 | 2013-01-10 20:31:17.963092+08 | 2013-01-1
0 20:31:17.963092+08 | 2013-01-10 20:31:17.963095+08 | f | active | update route_raw set sourceurl = 'http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=CGZL120701MDG05LA&xl=13935'whe<http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=CGZL120701MDG05LA&xl=13935%27whe>
re sourceurl like '%=CGZL120701MDG0%';
(18 rows)

5. display process information using gdb

[root(at)XXX ~]# gdb
GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-23.el5_5.2)
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html><http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/><http://www.gnu.org/software/gdb/bugs/>.
(gdb) attach 24391
Attaching to process 24391
Reading symbols from /opt/pg92/bin/postgres...done.
Reading symbols from /usr/lib64/libxslt.so.1...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libxslt.so.1
Reading symbols from /usr/lib64/libxml2.so.2...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libxml2.so.2
Reading symbols from /lib64/libcrypt.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libcrypt.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /usr/lib64/libz.so.1...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libz.so.1
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnss_files.so.2
0x00000039e4ed5337 in semop () from /lib64/libc.so.6
(gdb) bt
#0 0x00000039e4ed5337 in semop () from /lib64/libc.so.6
#1 0x00000000006985f3 in PGSemaphoreLock (sema=0x2ac6df58b850, interruptOK=0 '\000') at pg_sema.c:415
#2 0x00000000006ea043 in LWLockAcquire (lockid=375058, mode=LW_EXCLUSIVE) at lwlock.c:474
#3 0x00000000006d0739 in LockBuffer (buffer=187445, mode=2) at bufmgr.c:2533
#4 0x00000000004d9ab0 in spgdoinsert (index=0x2ac6dfe42730, state=0x7fff015dad90, heapPtr=0x2a491c4, datum=44339704, isnull=0 '\000') at spgdoinsert.c:1940
#5 0x00000000004d1880 in spginsert (fcinfo=0x7fff015dae40) at spginsert.c:220
#6 0x000000000080c921 in FunctionCall6Coll (flinfo=0x296b360, collation=0, arg1=47033648162608, arg2=140733216305856, arg3=140733216305824, arg4=44339652, arg5=47033648129744, arg6=0)
at fmgr.c:1439
#7 0x0000000000489526 in index_insert (indexRelation=0x2ac6dfe42730, values=0x7fff015db2c0, isnull=0x7fff015db2a0 "", heap_t_ctid=0x2a491c4, heapRelation=0x2ac6dfe3a6d0,
checkUnique=UNIQUE_CHECK_NO) at indexam.c:216
#8 0x00000000005eaf3c in ExecInsertIndexTuples (slot=0x2a42420, tupleid=0x2a491c4, estate=0x2a42190) at execUtils.c:1087
#9 0x00000000005fcfb5 in ExecInsert (slot=0x2a42420, planSlot=0x2a42420, estate=0x2a42190, canSetTag=1 '\001') at nodeModifyTable.c:248
#10 0x00000000005fde2e in ExecModifyTable (node=0x2a424b0) at nodeModifyTable.c:848
#11 0x00000000005ddde3 in ExecProcNode (node=0x2a424b0) at execProcnode.c:376
#12 0x00000000005dc24e in ExecutePlan (estate=0x2a42190, planstate=0x2a424b0, operation=CMD_INSERT, sendTuples=0 '\000', numberTuples=0, direction=ForwardScanDirection, dest=0x2a38f98)
at execMain.c:1393
#13 0x00000000005daa2a in standard_ExecutorRun (queryDesc=0x296a740, direction=ForwardScanDirection, count=0) at execMain.c:301
#14 0x00000000005da922 in ExecutorRun (queryDesc=0x296a740, direction=ForwardScanDirection, count=0) at execMain.c:249
#15 0x00000000006fb344 in ProcessQuery (plan=0x29f0d90,
sourceText=0x29ef410 "insert into route_raw(sourceurl) values ('http://suz.bacts.com/group/group_33_128972.aspx , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=1262"..., params=0x0, dest=0x2a38f98, completionTag=0x7fff015db940 "") at pquery.c:185
#16 0x00000000006fcaa9 in PortalRunMulti (portal=0x2968730, isTopLevel=1 '\001', dest=0x2a38f98, altdest=0x2a38f98, completionTag=0x7fff015db940 "") at pquery.c:1277
#17 0x00000000006fc183 in PortalRun (portal=0x2968730, count=9223372036854775807, isTopLevel=1 '\001', dest=0x2a38f98, altdest=0x2a38f98, completionTag=0x7fff015db940 "") at pquery.c:814
#18 0x00000000006f66da in exec_simple_query (
query_string=0x29ef410 "insert into route_raw(sourceurl) values ('http://suz.bacts.com/group/group_33_128972.aspx , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=1262"...) at postgres.c:1046
#19 0x00000000006fa5bd in PostgresMain (argc=2, argv=0x2949640, username=0x29494d0 "postgres") at postgres.c:3958
#20 0x00000000006a9305 in BackendRun (port=0x296cd70) at postmaster.c:3619
#21 0x00000000006a8a02 in BackendStartup (port=0x296cd70) at postmaster.c:3304
#22 0x00000000006a591a in ServerLoop () at postmaster.c:1367
#23 0x00000000006a52a8 in PostmasterMain (argc=3, argv=0x2947820) at postmaster.c:1127
#24 0x000000000061cfcb in main (argc=3, argv=0x2947820) at main.c:199

(gdb) frame 4
#4 0x00000000004d9ab0 in spgdoinsert (index=0x2ac6dfe42730, state=0x7fff015dad90, heapPtr=0x2a491c4, datum=44339704, isnull=0 '\000') at spgdoinsert.c:1940
1940 spgdoinsert.c: No such file or directory.
in spgdoinsert.c

(gdb) info locals
isNew = 0 '\000'
level = 0
leafDatum = 44339704
leafSize = 844
current = {blkno = 1, buffer = 187445, page = 0x0, offnum = 1, node = -1}
parent = {blkno = 4294967295, buffer = 0, page = 0x0, offnum = 0, node = -1}
__func__ = "spgdoinsert"
(gdb) p *(SpGistCache *) index->rd_amcache
$1 = {config = {prefixType = 25, labelType = 18, canReturnData = 1 '\001', longValuesOK = 1 '\001'}, attType = {type = 1043, attbyval = 0 '\000', attlen = -1}, attPrefixType = {type = 25,
attbyval = 0 '\000', attlen = -1}, attLabelType = {type = 18, attbyval = 1 '\001', attlen = 1}, lastUsedPages = {cachedPage = {{blkno = 6174, freeSpace = 4308}, {blkno = 6172,
freeSpace = 2024}, {blkno = 6173, freeSpace = 5244}, {blkno = 6217, freeSpace = 6228}, {blkno = 4294967295, freeSpace = 0}, {blkno = 4294967295, freeSpace = 0}, {blkno = 4294967295,
freeSpace = 0}, {blkno = 4294967295, freeSpace = 0}}}}

Thanks again

Best Regards!

于 2013年01月09日 12:28, Tom Lane 写道:

I wrote:

The control flow in spgdoinsert.c is flat enough that the stack trace
alone isn't much help in understanding the bug, I'm afraid.

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

regards, tom lane

=?gb2312?B?wO66o8H6?= <hailong(dot)li(at)qunar(dot)com> writes:
> This time I will give you the contents of the table route_raw, the download link is https://www.box.com/s/yxa4yxo6rcb3dzeaefmz or http://dl.dropbox.com/u/203288/route_raw_spgist.sql.tar.gz .

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

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

regards, tom lane

at 2013-01-12 02:21, Tom Lane wrote:

=?gb2312?B?wO66o8H6?= <hailong(dot)li(at)qunar(dot)com><mailto:hailong(dot)li(at)qunar(dot)com> writes:

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

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

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

regards, tom lane

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

Thanks again

Best Regards!

Hi,dear tom lane && pgsql-hackers

This time I also give you the contents of the test table and some details about my new procedure reproduce it, please download the attachment.

1. test environment

1.1 OS

[root(at)localhost ~]# cat /etc/issue
CentOS release 5.5 (Final)
Kernel \r on an \m

[root(at)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(dot)li(at)localhost ~]$ psql -U postgres
psql (9.2.4)
Type "help" for help.

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

[postgres(at)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(at)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-new-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(dot)li(at)localhost ~]$ psql -U postgres
psql (9.2.4)
Type "help" for 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/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test_spgist | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

postgres=# \c test_spgist
You are now connected to database "test_spgist" as user "postgres".
test_spgist=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------------+----------+----------
public | table_spgist | table | postgres
public | table_spgist_id_seq | sequence | postgres
(2 rows)

test_spgist=# \d table_spgist
Table "public.table_spgist"
Column | Type | Modifiers
--------+-------------------------+-----------------------------------------------------------
id | integer | not null default nextval('table_spgist_id_seq'::regclass)
col1 | character varying(128) |
col2 | character varying(1000) |
Indexes:
"table_spgist_tmp_pkey" PRIMARY KEY, btree (id)
"table_spgist_col1_idx" spgist (col1)
"table_spgist_col2_idx" 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 | qwertyuiop
10423 | zxcvbnm
10374 | 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://suz.bacts.com/group/group_33_128972.aspx , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623 , http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx, http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096 , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tu
anhao=TUCCZ120625A&xl=7957<http://www.gzl.com.cn/Travel/TeamD
etail.aspx?tuanhao=TUCCZ120625A&xl=7957> , http://www.ctszj.com.cn/route_group_1_134782.html , http://fj.bacts.com/group/group_33_113288.asp , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957, http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.bacts.com/group/group_33_113288.aspx ,http://www.sz
travel.com.cn/travel/detail.aspx?xlid=19883&ctype=1<http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1> , http://www.ctszj.com.cn/route_group_1_134695.html<http://www.cts%0Azj.com.cn/route_group_1_134695.html>
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 10423
col2 | http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175734, http://www.ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175593, http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/159304&p=1032&cmpid=mkt_01000301, http://www.hithr.cn/ReserveCenter/TourDetail.aspx?TourId=934433, http://www.g
zl.com.cn/Travel/TeamInfo/1571602.html<http://www.gzl.com.cn/Travel/TeamInfo/1571602.html>, http://sz.bacts.com/group/group_80_160847.aspx<http://sz%0A.bacts.com/group/group_80_160847.aspx>, http://sz.bacts.com/group/group_80_160762.aspx, http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175034, http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175036, http://km.gayosite.com/V2001I3001E393.html
-[ RECORD 3 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 10374
col2 | http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/467170&p=1032&cmpid=mkt_01000301, http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2102&to=3001&Info_id=36, http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html, http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_i
d=855<http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855>, http://www.668trip.com/xianlu/xhtml/detail-34975.html<http://www.668trip.com/xianlu/xhtml/detail%0A-34975.html>, http://www.51chuyou.com/line/2013/02/11/74177.html, http://jxtrvl.com/Line/kfrn6ijcqoacxoec96652e3cz3yx1pj56eux1tjcmeexxnw6romx1gewcou56guwx3yw1owm.htm, http://www.jjtravel.com/teamDetail.action?uid=E48CDBB7-4B64-4D84-9EDD-66BA59AFE6F9, http://www.
17u.cn/dujia/tours.aspx?id=29801&RefId=19386866<http://www.17u.cn/dujia/tours.aspx?id=29801&RefId=19386866>, http://www.17u.cn/dujia/tours.aspx?id=29803&Re
fId=19386866<http://www.17u.cn/dujia/tours.aspx?id=29803&RefId=19386866>
-[ RECORD 4 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 1
col2 | http://www.tmyou.com.cn/Route/6tianfoguoniboerzhilv.Html

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://suz.bacts.com/group/group_33_128972.aspx , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623 , http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx, http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096 , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tu
anhao=TUCCZ120625A&xl=7957<http://www.gzl.com.cn/Travel/TeamD
etail.aspx?tuanhao=TUCCZ120625A&xl=7957> , http://www.ctszj.com.cn/route_group_1_134782.html , http://fj.bacts.com/group/group_33_113288.asp , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957, http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.bacts.com/group/group_33_113288.aspx ,http://www.sz
travel.com.cn/travel/detail.aspx?xlid=19883&ctype=1<http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1> , http://www.ctszj.com.cn/route_group_1_134695.html<http://www.cts%0Azj.com.cn/route_group_1_134695.html>
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 10423
col1 | zxcvbnm
col2 | http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175734, http://www.ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175593, http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/159304&p=1032&cmpid=mkt_01000301, http://www.hithr.cn/ReserveCenter/TourDetail.aspx?TourId=934433, http://www.g
zl.com.cn/Travel/TeamInfo/1571602.html<http://www.gzl.com.cn/Travel/TeamInfo/1571602.html>, http://sz.bacts.com/group/group_80_160847.aspx<http://sz%0A.bacts.com/group/group_80_160847.aspx>, http://sz.bacts.com/group/group_80_160762.aspx, http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175034, http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175036, http://km.gayosite.com/V2001I3001E393.html
-[ RECORD 3 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 10374
col1 | asdfghjkl
col2 | http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/467170&p=1032&cmpid=mkt_01000301, http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2102&to=3001&Info_id=36, http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html, http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_i
d=855<http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855>, http://www.668trip.com/xianlu/xhtml/detail-34975.html<http://www.668trip.com/xianlu/xhtml/detail%0A-34975.html>, http://www.51chuyou.com/line/2013/02/11/74177.html, http://jxtrvl.com/Line/kfrn6ijcqoacxoec96652e3cz3yx1pj56eux1tjcmeexxnw6romx1gewcou56guwx3yw1owm.htm, http://www.jjtravel.com/teamDetail.action?uid=E48CDBB7-4B64-4D84-9EDD-66BA59AFE6F9, http://www.
17u.cn/dujia/tours.aspx?id=29801&RefId=19386866<http://www.17u.cn/dujia/tours.aspx?id=29801&RefId=19386866>, http://www.17u.cn/dujia/tours.aspx?id=29803&Re
fId=19386866<http://www.17u.cn/dujia/tours.aspx?id=29803&RefId=19386866>
-[ RECORD 4 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 1
col1 | trip668
col2 | http://www.668trip.com/xianlu/html/1108-1487.html

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

2. test procedure
2.1 insert

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

[postgres(at)localhost /tmp]$ cat insert_spgist1.sh
#/bin/bash

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

while [ 10 -ne 9 ]
do
SQL="insert into table_spgist (col1, col2) values ('qwertyuiop' ,'http://suz.bacts.com/group/group_33_128972.aspx , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623 , http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx, http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096 , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957<http://www.gzl.com.cn/Travel/TeamDe%0Atail.aspx?tuanhao=TUCCZ120625A&xl=7957> , http://www.ctszj.com.cn/route_group_1_134782.html , http://fj.bacts.com/group/group_33_113288.asp , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957, http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.b
acts.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> ,http://www.sztravel.com.cn/travel/det
ail.aspx?xlid=19883&ctype=1<http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1> , http://www.ctszj.com.cn/route_group_1_134695.html ')"
sleep 1
$PSQL -U $USR -d $DB -c "$SQL"
done

[postgres(at)localhost /tmp]$ cat insert_spgist2.sh
#/bin/bash

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

while [ 10 -ne 9 ]
do
SQL="insert into table_spgist(col1, col2) values ('asdfghjkl', 'http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/467170&p=1032&cmpid=mkt_01000301, http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2102&to=3001&Info_id=36, http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html, h
ttp://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=8
55<http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855>, http://www.668trip.com/xianlu/xhtml/detail-34975.html, http://www.51chuyou.com/line/2013/02/11/74177.html, http://jxtrvl.com/Line/kfrn6ijcqoacxoec96652e3cz3yx1pj56eux1tjcmeexxnw6romx1gewcou56guwx3yw1owm.htm, http://www.jjtravel.com/teamDetail.action?uid=E48CDBB7-4B64-4D84-9EDD-66BA59AFE6F9, http://www.17u.cn
/dujia/tours.aspx?id=29801&RefId=19386866<http://www.17u.cn/dujia/tours.aspx?id=29801&RefId=19386866>, http://www.17u.cn/dujia/tours.aspx?id=29803&RefId=19386866<http://www.17u.cn/dujia/t%0Aours.aspx?id=29803&RefId=19386866>' )"
sleep 1
$PSQL -U $USR -d $DB -c "$SQL"
done

[postgres(at)localhost /tmp]$ cat insert_spgist3.sh
#/bin/bash

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

while [ 10 -ne 9 ]
do
SQL="insert into table_spgist(col1, col2) values ('zxcvbnm', 'http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175734, http://www.ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175593, http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/159304&p=1032&cmpid=mkt_01000301, http://www.hithr.cn/ReserveCenter/TourDetail.aspx?TourId=934433, http://www.gzl.co
m.cn/Travel/TeamInfo/1571602.html<http://www.gzl.com.cn/Travel/TeamInfo/1571602.html>, http://sz.bacts.com/group/group_80_160847.aspx, http://sz.bacts.com/group/group_80_160762.aspx, http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175034, http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175036, http://km.gayosite.com/V2001I3001E393.html' )"
sleep 1
$PSQL -U $USR -d $DB -c "$SQL"
done

2.2 update

I edit 6 shell scripts that function is update data to table table_spgist.
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(at)localhost /tmp]$ cat update_spgist1.sh
#/bin/bash

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

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

[postgres(at)localhost /tmp]$ cat update_spgist2.sh
#/bin/bash

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

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

[postgres(at)localhost /tmp/]$ cat update_spgist3.sh
#/bin/bash

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

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

[postgres(at)localhost /tmp]$ cat update_spgist4.sh
#/bin/bash

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

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

[postgres(at)localhost /tmp]$ cat update_spgist5.sh
#/bin/bash

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

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

[postgres(at)localhost /tmp]$ cat update_spgist6.sh
#/bin/bash

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

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

2.3 delete

I edit 3 shell scripts that function is delete data to table table_spgist.
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(at)localhost /tmp]$ cat delete_spgist1.sh
#/bin/bash

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

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

[postgres(at)localhost /tmp]$ cat delete_spgist2.sh
#/bin/bash

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

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

[postgres(at)localhost /tmp$ cat delete_spgist3.sh
#/bin/bash

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

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

2.4 monitor

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

3. the phenonmenon when the suspicious 'bug' comes up

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

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)

The count of records in table table_spgist is no longer continue to increase and the count of connections is no longer changes.

3.1 kill the process in OS

[root(at)localhost /tmp]# insert_spgist1.sh >/dev/null
Cancel request sent
Cancel request sent
Cancel request sent
[root(at)localhost /tmp]# update_spgist1.sh >/dev/null
Cancel request sent
Cancel request sent
Cancel request sent
[root(at)localhost /tmp]# delete_spgist1.sh >/dev/null
Cancel request sent
Cancel request sent
Cancel request sent

[root(at)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 'http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855' or col1 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 'http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html' 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 'http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175036' 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(at)localhost ~]# ps auxww | grep postgres | grep -c -i -E 'INSERT|UPDATE|DETELE'
95
[root(at)localhost ~]# ps auxww | grep postgres | grep -i -E 'INSERT|UPDATE|DETELE' | awk '{print $2}' | xargs kill
[root(at)localhost ~]# ps auxww | grep postgres | grep -c -i -E 'INSERT|UPDATE|DELETE'
95

We can see above,I can not kill the process.

3.2 kill the process in PostgreSQL server

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
-------------------
t
t
t
...
...
...

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
----------------------
t
t
t
...
...
...

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

We can see above,I also can not kill the process in the PostgreSQL server.

If you need some more detailed information, please tell me and I'll give it to you.

Thanks again

Best Regards!

My dear tom lane && pgsql-hackers,

did you read this email, I'm look forward to hearing from you at your earliest convenience.

Best Regards!

at 2013-05-21 01:10, Hailong Li wrote:
Hi,dear tom lane && pgsql-hackers

This time I also give you the contents of the test table and some details about my new procedure reproduce it, please download the attachment.

1. test environment

1.1 OS

[root(at)localhost ~]# cat /etc/issue
CentOS release 5.5 (Final)
Kernel \r on an \m

[root(at)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(dot)li(at)localhost ~]$ psql -U postgres
psql (9.2.4)
Type "help" for help.

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

[postgres(at)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(at)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-new-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(dot)li(at)localhost ~]$ psql -U postgres
psql (9.2.4)
Type "help" for 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/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test_spgist | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

postgres=# \c test_spgist
You are now connected to database "test_spgist" as user "postgres".
test_spgist=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------------+----------+----------
public | table_spgist | table | postgres
public | table_spgist_id_seq | sequence | postgres
(2 rows)

test_spgist=# \d table_spgist
Table "public.table_spgist"
Column | Type | Modifiers
--------+-------------------------+-----------------------------------------------------------
id | integer | not null default nextval('table_spgist_id_seq'::regclass)
col1 | character varying(128) |
col2 | character varying(1000) |
Indexes:
"table_spgist_tmp_pkey" PRIMARY KEY, btree (id)
"table_spgist_col1_idx" spgist (col1)
"table_spgist_col2_idx" 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 | qwertyuiop
10423 | zxcvbnm
10374 | 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://suz.bacts.com/group/group_33_128972.aspx , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623 , http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx, http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096 , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tu
anhao=TUCCZ120625A&xl=7957<http://www.gzl.com.cn/Travel/TeamD
%0Aetail.aspx?tuanhao=TUCCZ120625A&xl=7957> , http://www.ctszj.com.cn/route_group_1_134782.html , http://fj.bacts.com/group/group_33_113288.asp , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957, http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.bacts.com/group/group_33_113288.aspx ,http://www.sz

travel.com.cn/travel/detail.aspx?xlid=19883&ctype=1<http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1> , http://www.ctszj.com.cn/route_group_1_134695.html<http://www.cts%0Azj.com.cn/route_group_1_134695.html>
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 10423
col2 | http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175734, http://www.ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175593, http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/159304&p=1032&cmpid=mkt_01000301, http://www.hithr.cn/ReserveCenter/TourDetail.aspx?TourId=934433, http://www.g

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

d=855<http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855>, http://www.668trip.com/xianlu/xhtml/detail-34975.html<http://www.668trip.com/xianlu/xhtml/detail%0A-34975.html>, http://www.51chuyou.com/line/2013/02/11/74177.html, http://jxtrvl.com/Line/kfrn6ijcqoacxoec96652e3cz3yx1pj56eux1tjcmeexxnw6romx1gewcou56guwx3yw1owm.htm, http://www.jjtravel.com/teamDetail.action?uid=E48CDBB7-4B64-4D84-9EDD-66BA59AFE6F9, http://www.

17u.cn/dujia/tours.aspx?id=29801&RefId=19386866<http://www.17u.cn/dujia/tours.aspx?id=29801&RefId=19386866>, http://www.17u.cn/dujia/tours.aspx?id=29803&Re
fId=19386866<http://www.17u.cn/dujia/tours.aspx?id=29803&RefId=19386866>
-[ RECORD 4 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 1
col2 | http://www.tmyou.com.cn/Route/6tianfoguoniboerzhilv.Html

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://suz.bacts.com/group/group_33_128972.aspx , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6&xl=12623 , http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx, http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723&xl=7096 , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tu
anhao=TUCCZ120625A&xl=7957<http://www.gzl.com.cn/Travel/TeamD
%0Aetail.aspx?tuanhao=TUCCZ120625A&xl=7957> , http://www.ctszj.com.cn/route_group_1_134782.html , http://fj.bacts.com/group/group_33_113288.asp , http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625A&xl=7957, http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.bacts.com/group/group_33_113288.aspx ,http://www.sz

travel.com.cn/travel/detail.aspx?xlid=19883&ctype=1<http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1> , http://www.ctszj.com.cn/route_group_1_134695.html<http://www.cts%0Azj.com.cn/route_group_1_134695.html>
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 10423
col1 | zxcvbnm
col2 | http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175734, http://www.ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175593, http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/159304&p=1032&cmpid=mkt_01000301, http://www.hithr.cn/ReserveCenter/TourDetail.aspx?TourId=934433, http://www.g

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

d=855<http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855>, http://www.668trip.com/xianlu/xhtml/detail-34975.html<http://www.668trip.com/xianlu/xhtml/detail%0A-34975.html>, http://www.51chuyou.com/line/2013/02/11/74177.html, http://jxtrvl.com/Line/kfrn6ijcqoacxoec96652e3cz3yx1pj56eux1tjcmeexxnw6romx1gewcou56guwx3yw1owm.htm, http://www.jjtravel.com/teamDetail.action?uid=E48CDBB7-4B64-4D84-9EDD-66BA59AFE6F9, http://www.

17u.cn/dujia/tours.aspx?id=29801&RefId=19386866<http://www.17u.cn/dujia/tours.aspx?id=29801&RefId=19386866>, http://www.17u.cn/dujia/tours.aspx?id=29803&Re
fId=19386866<http://www.17u.cn/dujia/tours.aspx?id=29803&RefId=19386866>
-[ RECORD 4 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count | 1
col1 | trip668
col2 | http://www.668trip.com/xianlu/html/1108-1487.html

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

2. test procedure
2.1 insert

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

[postgres(at)localhost /tmp]$ cat insert_spgist1.sh
#/bin/bash

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

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

acts.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> ,http://www.sztravel.com.cn/travel/det
ail.aspx?xlid=19883&ctype=1<http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883&ctype=1> , http://www.ctszj.com.cn/route_group_1_134695.html ')"
sleep 1
$PSQL -U $USR -d $DB -c "$SQL"
done

[postgres(at)localhost /tmp]$ cat insert_spgist2.sh
#/bin/bash

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

while [ 10 -ne 9 ]
do
SQL="insert into table_spgist(col1, col2) values ('asdfghjkl', 'http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/467170&p=1032&cmpid=mkt_01000301, http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2102&to=3001&Info_id=36, http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html, h

ttp://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=8
55<http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855>, http://www.668trip.com/xianlu/xhtml/detail-34975.html, http://www.51chuyou.com/line/2013/02/11/74177.html, http://jxtrvl.com/Line/kfrn6ijcqoacxoec96652e3cz3yx1pj56eux1tjcmeexxnw6romx1gewcou56guwx3yw1owm.htm, http://www.jjtravel.com/teamDetail.action?uid=E48CDBB7-4B64-4D84-9EDD-66BA59AFE6F9, http://www.17u.cn

/dujia/tours.aspx?id=29801&RefId=19386866<http://www.17u.cn/dujia/tours.aspx?id=29801&RefId=19386866>, http://www.17u.cn/dujia/tours.aspx?id=29803&RefId=19386866<http://www.17u.cn/dujia/t%0Aours.aspx?id=29803&RefId=19386866>' )"
sleep 1
$PSQL -U $USR -d $DB -c "$SQL"
done

[postgres(at)localhost /tmp]$ cat insert_spgist3.sh
#/bin/bash

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

while [ 10 -ne 9 ]
do
SQL="insert into table_spgist(col1, col2) values ('zxcvbnm', 'http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175734, http://www.ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175593, http://market.tuniu.com/Partner_redirect.php?url=http://www.tuniu.com/tours/159304&p=1032&cmpid=mkt_01000301, http://www.hithr.cn/ReserveCenter/TourDetail.aspx?TourId=934433, http://www.gzl.co
m.cn/Travel/TeamInfo/1571602.html<http://www.gzl.com.cn/Travel/TeamInfo/1571602.html>, http://sz.bacts.com/group/group_80_160847.aspx, http://sz.bacts.com/group/group_80_160762.aspx, http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175034, http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175036, http://km.gayosite.com/V2001I3001E393.html' )"
sleep 1
$PSQL -U $USR -d $DB -c "$SQL"
done

2.2 update

I edit 6 shell scripts that function is update data to table table_spgist.
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(at)localhost /tmp]$ cat update_spgist1.sh
#/bin/bash

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

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

[postgres(at)localhost /tmp]$ cat update_spgist2.sh
#/bin/bash

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

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

[postgres(at)localhost /tmp/]$ cat update_spgist3.sh
#/bin/bash

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

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

[postgres(at)localhost /tmp]$ cat update_spgist4.sh
#/bin/bash

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

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

[postgres(at)localhost /tmp]$ cat update_spgist5.sh
#/bin/bash

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

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

[postgres(at)localhost /tmp]$ cat update_spgist6.sh
#/bin/bash

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

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

2.3 delete

I edit 3 shell scripts that function is delete data to table table_spgist.
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(at)localhost /tmp]$ cat delete_spgist1.sh
#/bin/bash

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

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

[postgres(at)localhost /tmp]$ cat delete_spgist2.sh
#/bin/bash

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

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

[postgres(at)localhost /tmp$ cat delete_spgist3.sh
#/bin/bash

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

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

2.4 monitor

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

3. the phenonmenon when the suspicious 'bug' comes up

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

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)

The count of records in table table_spgist is no longer continue to increase and the count of connections is no longer changes.

3.1 kill the process in OS

[root(at)localhost /tmp]# insert_spgist1.sh >/dev/null
Cancel request sent
Cancel request sent
Cancel request sent
[root(at)localhost /tmp]# update_spgist1.sh >/dev/null
Cancel request sent
Cancel request sent
Cancel request sent
[root(at)localhost /tmp]# delete_spgist1.sh >/dev/null
Cancel request sent
Cancel request sent
Cancel request sent

[root(at)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 'http://km.gayosite.com/line_xl_view.asp?SubStation=km&form=2201&to=3001&Info_id=855' or col1 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 'http://www.tengda.cc/line/showb20bb95ab626d93fd976af958fbc61ba.html' 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 'http://ctsho.com/tour/international/tourinfo.do?tourQueryDto.lineId=175036' 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(at)localhost ~]# ps auxww | grep postgres | grep -c -i -E 'INSERT|UPDATE|DETELE'
95
[root(at)localhost ~]# ps auxww | grep postgres | grep -i -E 'INSERT|UPDATE|DETELE' | awk '{print $2}' | xargs kill
[root(at)localhost ~]# ps auxww | grep postgres | grep -c -i -E 'INSERT|UPDATE|DELETE'
95

We can see above,I can not kill the process.

3.2 kill the process in PostgreSQL server

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
-------------------
t
t
t
...
...
...

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
----------------------
t
t
t
...
...
...

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

We can see above,I also can not kill the process in the PostgreSQL server.

If you need some more detailed information, please tell me and I'll give it to you.

Thanks again

Best Regards!