Ah ha ha , this is great, I am very ashamed of my English expression, did not let you clearly understand my mail.
now, I am very glad that you can understand this. I sincerely hope that I can help you. I am also a postgres fan, a freshly graduated student. We have all confirmed that this bug will only appear on the standby and will not appear on the master.But it does affect the use of standby. For this bug, I proposed two options, one is to disable this feature (drop index concurrently), the other is to wait for the standby select like on the master, but it may affect the application delay of the log. Because this bug appears on the standby, I think both methods have advantages and disadvantages. So I hope that you can discuss it so much that it will help you. Sincerely adger ------------------------------------------------------------------ 发件人:Tomas Vondra <tomas.von...@2ndquadrant.com> 发送时间:2019年10月23日(星期三) 06:49 收件人:李杰(慎追) <adger...@alibaba-inc.com> 抄 送:pgsql-hackers <pgsql-hackers@lists.postgresql.org> 主 题:Re: Bug about drop index concurrently On Fri, Oct 18, 2019 at 05:00:54PM +0200, Tomas Vondra wrote: >Hi, > >I can trivially reproduce this - it's enough to create a master-standby >setup, and then do this on the master > > CREATE TABLE t (a int, b int); > INSERT INTO t SELECT i, i FROM generate_series(1,10000) s(i); > >and run pgbench with this script > > DROP INDEX CONCURRENTLY IF EXISTS t_a_idx; > CREATE INDEX t_a_idx ON t(a); > >while on the standby there's another pgbench running this script > > EXPLAIN ANALYZE SELECT * FROM t WHERE a = 10000; > >and it fails pretty fast for me. With an extra assert(false) added to >src/backend/access/common/relation.c I get a backtrace like this: > > Program terminated with signal SIGABRT, Aborted. > #0 0x00007c32e458fe35 in raise () from /lib64/libc.so.6 > Missing separate debuginfos, use: dnf debuginfo-install > glibc-2.29-22.fc30.x86_64 > (gdb) bt > #0 0x00007c32e458fe35 in raise () from /lib64/libc.so.6 > #1 0x00007c32e457a895 in abort () from /lib64/libc.so.6 > #2 0x0000000000a58579 in ExceptionalCondition (conditionName=0xacd9bc > "!(0)", errorType=0xacd95b "FailedAssertion", fileName=0xacd950 "relation.c", > lineNumber=64) at assert.c:54 > #3 0x000000000048d1bd in relation_open (relationId=38216, lockmode=1) at > relation.c:64 > #4 0x00000000005082e4 in index_open (relationId=38216, lockmode=1) at > indexam.c:130 > #5 0x000000000080ac3f in get_relation_info (root=0x21698b8, > relationObjectId=16385, inhparent=false, rel=0x220ce60) at plancat.c:196 > #6 0x00000000008118c6 in build_simple_rel (root=0x21698b8, relid=1, > parent=0x0) at relnode.c:292 > #7 0x00000000007d485d in add_base_rels_to_query (root=0x21698b8, > jtnode=0x2169478) at initsplan.c:114 > #8 0x00000000007d48a3 in add_base_rels_to_query (root=0x21698b8, > jtnode=0x21693e0) at initsplan.c:122 > #9 0x00000000007d8fad in query_planner (root=0x21698b8, > qp_callback=0x7ded97 <standard_qp_callback>, qp_extra=0x7fffa4834f10) at > planmain.c:168 > #10 0x00000000007dc316 in grouping_planner (root=0x21698b8, > inheritance_update=false, tuple_fraction=0) at planner.c:2048 > #11 0x00000000007da7ca in subquery_planner (glob=0x220d078, > parse=0x2168f78, parent_root=0x0, hasRecursion=false, tuple_fraction=0) at > planner.c:1012 > #12 0x00000000007d942c in standard_planner (parse=0x2168f78, > cursorOptions=256, boundParams=0x0) at planner.c:406 > #13 0x00000000007d91e8 in planner (parse=0x2168f78, cursorOptions=256, > boundParams=0x0) at planner.c:275 > #14 0x00000000008e1b0d in pg_plan_query (querytree=0x2168f78, > cursorOptions=256, boundParams=0x0) at postgres.c:878 > #15 0x0000000000658683 in ExplainOneQuery (query=0x2168f78, > cursorOptions=256, into=0x0, es=0x220cd90, queryString=0x21407b8 "explain > analyze select * from t where a = 10000;", params=0x0, queryEnv=0x0) at > explain.c:367 > #16 0x0000000000658386 in ExplainQuery (pstate=0x220cc28, stmt=0x2141728, > queryString=0x21407b8 "explain analyze select * from t where a = 10000;", > params=0x0, queryEnv=0x0, dest=0x220cb90) at explain.c:255 > #17 0x00000000008ea218 in standard_ProcessUtility (pstmt=0x21425c0, > queryString=0x21407b8 "explain analyze select * from t where a = 10000;", > context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x220cb90, > completionTag=0x7fffa48355c0 "") at utility.c:675 > #18 0x00000000008e9a45 in ProcessUtility (pstmt=0x21425c0, > queryString=0x21407b8 "explain analyze select * from t where a = 10000;", > context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x220cb90, > completionTag=0x7fffa48355c0 "") at utility.c:360 > #19 0x00000000008e8a0c in PortalRunUtility (portal=0x219c278, > pstmt=0x21425c0, isTopLevel=true, setHoldSnapshot=true, dest=0x220cb90, > completionTag=0x7fffa48355c0 "") at pquery.c:1175 > #20 0x00000000008e871a in FillPortalStore (portal=0x219c278, > isTopLevel=true) at pquery.c:1035 > #21 0x00000000008e8075 in PortalRun (portal=0x219c278, > count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x21efb90, > altdest=0x21efb90, completionTag=0x7fffa48357b0 "") at pquery.c:765 > #22 0x00000000008e207c in exec_simple_query (query_string=0x21407b8 > "explain analyze select * from t where a = 10000;") at postgres.c:1215 > #23 0x00000000008e636e in PostgresMain (argc=1, argv=0x216c600, > dbname=0x216c4e0 "test", username=0x213c3f8 "user") at postgres.c:4236 > #24 0x000000000083c71e in BackendRun (port=0x2165850) at postmaster.c:4437 > #25 0x000000000083beef in BackendStartup (port=0x2165850) at > postmaster.c:4128 > #26 0x0000000000838313 in ServerLoop () at postmaster.c:1704 > #27 0x0000000000837bbf in PostmasterMain (argc=3, argv=0x213a360) at > postmaster.c:1377 > #28 0x0000000000759643 in main (argc=3, argv=0x213a360) at main.c:228 > >So my guess is the root cause is pretty simple - we close/unlock the >indexes after completing the query, but then EXPLAIN tries to open it >again when producing the explain plan. > D'oh! I've just looked at this issue more carefully, and I realize this suggestion (that it's due to releasing a lock too early) is just bogus. Sorry about the confusion :-( In fact, I think you've been 100% correct in your analysis regarding the root cause, which is that we don't realize there's a query on a standby, using the index that we're dropping (and REINDEX CONCURRENTLY seems to have exactly the same issue). I've reproduced this on all releases back to 10, I suppose it affects all releases with DROP INDEX CONCURRENTLY (I haven't tried, but I don't see why it wouldn't). I still think it's a bug, and we'll need to fix it somehow. Not sure how, though. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services