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

From: 李海龙 <hailong(dot)li(at)qunar(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Cc: 何伟平 <weiping(dot)he(at)qunar(dot)com>, 王冬 <dong(dot)wang(at)qunar(dot)com>, 于超超 <chaochao(dot)yu(at)qunar(dot)com>
Subject: I s this a bug of spgist index in a heavy write condition?
Date: 2012-12-13 15:46:15
Message-ID: 50C9F845.202@qunar.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


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!

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Steve Singer 2012-12-13 16:02:06 Re: Logical decoding & exported base snapshot
Previous Message Alvaro Herrera 2012-12-13 15:44:43 Re: Re: [PATCH 02/14] Add support for a generic wal reading facility dubbed XLogReader