Horton

Thread

Commits

  1. Skip system attributes when applying mvdistinct stats

  1. BUG #16111: Unexpected query compilation error “negative bitmapset member not allowed”

    The Post Office <noreply@postgresql.org> — 2019-11-13T10:42:32Z

    The following bug has been logged on the website:

Bug reference:      16111
Logged by:          Tuomas Leikola
Email address:      tuomas.leikola@gmail.com
PostgreSQL version: 12.0
Operating system:   windows, linux
Description:        

I’ve been trying to cook up some kind of online reorganizer, which requires
looking at rows’ physical locations and rearranging those that are
suboptimal. In this endeavor, I stumbled on what appears to be a compiler
bug introduced with multivariate statistics.

When running reduced script:

create table tuuba (id bigint, fun bigint, nofun float);

insert into tuuba(id,fun,nofun)
select gs,gs2,random()
from generate_series(1,1000) gs
cross join generate_series(1,1000) gs2;

create statistics tubestat on id,fun from tuuba;

analyze tuuba;

select (ctid::text::point)[0] blkno, count(*) cn
from tuuba
group by blkno
order by cn desc
limit 100;

The last select gives out an error

ERROR:  negative bitmapset member not allowed

This is not very specific, so i traced it with gdb (traces from real db
where i originally encountered this):

(gdb) bt
#0  errfinish (dummy=dummy@entry=0) at
./build/../src/backend/utils/error/elog.c:414
#1  0x00005627ce82e149 in elog_finish (elevel=elevel@entry=20,
fmt=fmt@entry=0x5627ce97daf0 "negative bitmapset member not allowed") at
./build/../src/backend/utils/error/elog.c:1376
#2  0x00005627ce61319a in bms_add_member (a=<optimized out>, x=<optimized
out>) at ./build/../src/backend/nodes/bitmapset.c:770
#3  0x00005627ce7dade8 in estimate_multivariate_ndistinct
(root=0x5627d0570458, rel=0x5627d0528fb8, ndistinct=<synthetic pointer>,
varinfos=<synthetic pointer>) at
./build/../src/backend/utils/adt/selfuncs.c:3939
#4  estimate_num_groups (root=root@entry=0x5627d0567778,
groupExprs=<optimized out>, input_rows=input_rows@entry=116378227,
pgset=pgset@entry=0x0) at ./build/../src/backend/utils/adt/selfuncs.c:3623
#5  0x00005627ce65bbd5 in get_number_of_groups
(root=root@entry=0x5627d0567778, path_rows=116378227, gd=gd@entry=0x0,
target_list=0x5627d0567e58) at
./build/../src/backend/optimizer/plan/planner.c:3665
#6  0x00005627ce65db1c in create_partial_grouping_paths
(force_rel_creation=<optimized out>, extra=0x7fff9309be60, gd=0x0,
input_rel=0x5627d0528fb8, grouped_rel=0x5627d056f128, root=0x562700000001)
    at ./build/../src/backend/optimizer/plan/planner.c:6552
#7  create_ordinary_grouping_paths (root=root@entry=0x5627d0567778,
input_rel=input_rel@entry=0x5627d0528fb8,
grouped_rel=grouped_rel@entry=0x5627d056f128,
agg_costs=agg_costs@entry=0x7fff9309c040, gd=gd@entry=0x0,
    extra=extra@entry=0x7fff9309c070,
partially_grouped_rel_p=0x7fff9309c018) at
./build/../src/backend/optimizer/plan/planner.c:4045
#8  0x00005627ce66077d in create_grouping_paths (gd=0x0,
agg_costs=0x7fff9309c040, target_parallel_safe=true, target=0x5627d056ee88,
input_rel=0x5627d0528fb8, root=0x5627d0567778)
    at ./build/../src/backend/optimizer/plan/planner.c:3833
#9  grouping_planner (root=root@entry=0x5627d0567778,
inheritance_update=inheritance_update@entry=false, tuple_fraction=<optimized
out>, tuple_fraction@entry=0) at
./build/../src/backend/optimizer/plan/planner.c:2082
#10 0x00005627ce661c1e in subquery_planner (glob=glob@entry=0x5627d0567548,
parse=parse@entry=0x5627d05291c8, parent_root=parent_root@entry=0x0,
hasRecursion=hasRecursion@entry=false,
tuple_fraction=tuple_fraction@entry=0)
    at ./build/../src/backend/optimizer/plan/planner.c:966
#11 0x00005627ce662c15 in standard_planner (parse=0x5627d05291c8,
cursorOptions=256, boundParams=<optimized out>) at
./build/../src/backend/optimizer/plan/planner.c:405
#12 0x00005627ce70d181 in pg_plan_query
(querytree=querytree@entry=0x5627d05291c8, cursorOptions=<optimized out>,
boundParams=boundParams@entry=0x0) at
./build/../src/backend/tcop/postgres.c:832
#13 0x00005627ce55ae18 in ExplainOneQuery (query=0x5627d05291c8,
cursorOptions=<optimized out>, into=0x0, es=0x5627d0528f28,
    queryString=0x5627d045c5e0 "explain select (ctid::text::point)[0] blkno,
count(*) cn\nfrom big_table\ngroup by blkno\norder by cn desc\nlimit 100;",
params=0x0, queryEnv=0x0)
    at ./build/../src/backend/commands/explain.c:365
#14 0x00005627ce55b3af in ExplainQuery (pstate=pstate@entry=0x5627d052eaf8,
stmt=stmt@entry=0x5627d045da98,
    queryString=queryString@entry=0x5627d045c5e0 "explain select
(ctid::text::point)[0] blkno, count(*) cn\nfrom big_table\ngroup by
blkno\norder by cn desc\nlimit 100;", params=params@entry=0x0,
    queryEnv=queryEnv@entry=0x0, dest=dest@entry=0x5627d052ea68) at
./build/../src/backend/commands/explain.c:254
#15 0x00005627ce7133bb in standard_ProcessUtility
(pstmt=pstmt@entry=0x5627d045db48,
    queryString=queryString@entry=0x5627d045c5e0 "explain select
(ctid::text::point)[0] blkno, count(*) cn\nfrom big_table\ngroup by
blkno\norder by cn desc\nlimit 100;",
context=context@entry=PROCESS_UTILITY_TOPLEVEL,
    params=params@entry=0x0, queryEnv=queryEnv@entry=0x0,
dest=dest@entry=0x5627d052ea68, completionTag=0x7fff9309c720 "") at
./build/../src/backend/tcop/utility.c:675
#16 0x00007fee66bf9377 in pgss_ProcessUtility (pstmt=0x5627d045db48,
queryString=0x5627d045c5e0 "explain select (ctid::text::point)[0] blkno,
count(*) cn\nfrom big_table\ngroup by blkno\norder by cn desc\nlimit
100;",
    context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0,
dest=0x5627d052ea68, completionTag=0x7fff9309c720 "") at
./build/../contrib/pg_stat_statements/pg_stat_statements.c:1005
#17 0x00005627ce710049 in PortalRunUtility (portal=0x5627d04eb740,
pstmt=0x5627d045db48, isTopLevel=<optimized out>, setHoldSnapshot=<optimized
out>, dest=<optimized out>, completionTag=0x7fff9309c720 "")
    at ./build/../src/backend/tcop/pquery.c:1178
#18 0x00005627ce710e57 in FillPortalStore
(portal=portal@entry=0x5627d04eb740, isTopLevel=isTopLevel@entry=true) at
./build/../src/backend/tcop/pquery.c:1038
#19 0x00005627ce711a67 in PortalRun (portal=portal@entry=0x5627d04eb740,
count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true,
run_once=run_once@entry=true, dest=dest@entry=0x5627d045e410,
    altdest=altdest@entry=0x5627d045e410, completionTag=0x7fff9309c960 "")
at ./build/../src/backend/tcop/pquery.c:768
#20 0x00005627ce70d49f in exec_simple_query (query_string=0x5627d045c5e0
"explain select (ctid::text::point)[0] blkno, count(*) cn\nfrom
big_table\ngroup by blkno\norder by cn desc\nlimit 100;")
    at ./build/../src/backend/tcop/postgres.c:1145
#21 0x00005627ce70f49e in PostgresMain (argc=<optimized out>,
argv=argv@entry=0x5627d04afd58, dbname=<optimized out>, username=<optimized
out>) at ./build/../src/backend/tcop/postgres.c:4182
#22 0x00005627ce699bcd in BackendRun (port=0x5627d04a6df0) at
./build/../src/backend/postmaster/postmaster.c:4358
#23 BackendStartup (port=0x5627d04a6df0) at
./build/../src/backend/postmaster/postmaster.c:4030
#24 ServerLoop () at ./build/../src/backend/postmaster/postmaster.c:1707
#25 0x00005627ce69ac0d in PostmasterMain (argc=5, argv=0x5627d0457090) at
./build/../src/backend/postmaster/postmaster.c:1380
#26 0x00005627ce426662 in main (argc=5, argv=0x5627d0457090) at
./build/../src/backend/main/main.c:228

As multivariate ndistinct is right there in the call stack, I tried dropping
all custom statistics from the table, and that indeed solved the issue as a
workaround, and now i get an access plan like explain is supposed to:

Limit  (cost=5782607.20..5782607.45 rows=100 width=16)
  ->  Sort  (cost=5782607.20..5836448.29 rows=21536436 width=16)
        Sort Key: (count(*)) DESC
        ->  Finalize GroupAggregate  (cost=1707829.06..4959500.11
rows=21536436 width=16)
              Group Key: ((((ctid)::text)::point)[0])
              ->  Gather Merge  (cost=1707829.06..4421089.21 rows=21536436
width=16)
                    Workers Planned: 4
                    ->  Partial GroupAggregate  (cost=1706829.00..1854892.00
rows=5384109 width=16)
                          Group Key: ((((ctid)::text)::point)[0])
                          ->  Sort  (cost=1706829.00..1720289.27
rows=5384109 width=8)
                                Sort Key: ((((ctid)::text)::point)[0])
                                ->  Parallel Seq Scan on big_table 
(cost=0.00..957654.18 rows=5384109 width=8)


This occurred a while ago to me, but the trace was captured with 
 PostgreSQL 11.5 (Ubuntu 11.5-3.pgdg18.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
The server was since upgraded in-place to 12.0:
 PostgreSQL 12.0 (Ubuntu 12.0-2.pgdg18.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
And the same error is still repeatable, apparently this part has not
changed.
This also reproduces on windows 64.

  2. Re: BUG #16111: Unexpected query compilation error “negative bitmapset member not allowed”

    Tomas Vondra <tomas.vondra@2ndquadrant.com> — 2019-11-13T13:41:14Z

    On Wed, Nov 13, 2019 at 10:42:32AM +0000, PG Bug reporting form wrote:
>The following bug has been logged on the website:
>
>Bug reference:      16111
>Logged by:          Tuomas Leikola
>Email address:      tuomas.leikola@gmail.com
>PostgreSQL version: 12.0
>Operating system:   windows, linux
>Description:
>
>I’ve been trying to cook up some kind of online reorganizer, which requires
>looking at rows’ physical locations and rearranging those that are
>suboptimal. In this endeavor, I stumbled on what appears to be a compiler
>bug introduced with multivariate statistics.
>

Yeah, this is a bug in estimate_multivariate_ndistinct, which fails to
ignore system attributes :-(

The attached patch fixes it, but unfortunatly we've missed this round of
minor releases :-( I'll get it pushed in a couple of days.

Thanks for the report and the reproducer, very helpful!

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  3. Re: BUG #16111: Unexpected query compilation error “negative bitmapset member not allowed”

    Tomas Vondra <tomas.vondra@2ndquadrant.com> — 2019-11-16T01:36:42Z

    On Wed, Nov 13, 2019 at 02:41:14PM +0100, Tomas Vondra wrote:
>On Wed, Nov 13, 2019 at 10:42:32AM +0000, PG Bug reporting form wrote:
>>The following bug has been logged on the website:
>>
>>Bug reference:      16111
>>Logged by:          Tuomas Leikola
>>Email address:      tuomas.leikola@gmail.com
>>PostgreSQL version: 12.0
>>Operating system:   windows, linux
>>Description:
>>
>>I’ve been trying to cook up some kind of online reorganizer, which requires
>>looking at rows’ physical locations and rearranging those that are
>>suboptimal. In this endeavor, I stumbled on what appears to be a compiler
>>bug introduced with multivariate statistics.
>>
>
>Yeah, this is a bug in estimate_multivariate_ndistinct, which fails to
>ignore system attributes :-(
>
>The attached patch fixes it, but unfortunatly we've missed this round of
>minor releases :-( I'll get it pushed in a couple of days.
>
>Thanks for the report and the reproducer, very helpful!
>

I've pushed the fix, after a minor improvement (it wasn't quite right,
because the bitmap was updated again a bit later). Backpatched to 10+.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  4. Re: BUG #16111: Unexpected query compilation error “negative bitmapset member not allowed”

    Justin Pryzby <pryzby@telsasoft.com> — 2019-12-05T00:38:23Z

    On Sat, Nov 16, 2019 at 02:36:42AM +0100, Tomas Vondra wrote:
> >Yeah, this is a bug in estimate_multivariate_ndistinct, which fails to
> >ignore system attributes :-(
> >
> >The attached patch fixes it, but unfortunatly we've missed this round of
> >minor releases :-( I'll get it pushed in a couple of days.
> >
> >Thanks for the report and the reproducer, very helpful!
> 
> I've pushed the fix, after a minor improvement (it wasn't quite right,
> because the bitmap was updated again a bit later). Backpatched to 10+.

I thought you'd be interested to know I just ran into this myself, so I
would've reported it now, if it hadn't already been fixed - thanks.

-- Note, that's actually a table named after a view..
ts=# explain SELECT tableoid::regclass FROM daily_enodeb_baseband_view GROUP BY 1;
ERROR:  negative bitmapset member not allowed

-- 
Justin Pryzby
System Administrator
Telsasoft
+1-952-707-8581