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

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!