Re: Extract numeric filed in JSONB more effectively - Mailing list pgsql-hackers
From | Chapman Flack |
---|---|
Subject | Re: Extract numeric filed in JSONB more effectively |
Date | |
Msg-id | 111272f2dc112c7becdd35ad89f6b935@anastigmatix.net Whole thread Raw |
In response to | Re: Extract numeric filed in JSONB more effectively (Andy Fan <zhihui.fan1213@gmail.com>) |
Responses |
Re: Extract numeric filed in JSONB more effectively
|
List | pgsql-hackers |
On 2023-08-18 03:41, Andy Fan wrote: > I just have > a quick hack on this, and crash happens at the simplest case. If I build from this patch, this test: SELECT (test_json -> 0)::int4, test_json -> 0 FROM test_jsonb WHERE json_type = 'scalarint'; fails like this: Program received signal SIGSEGV, Segmentation fault. convert_saop_to_hashed_saop_walker (node=0x17, context=0x0) at /var/tmp/nohome/pgbuildh/../postgresql/src/backend/optimizer/util/clauses.c:2215 2215 if (IsA(node, ScalarArrayOpExpr)) (gdb) p node $1 = (Node *) 0x17 So the optimizer is looking at some node to see if it is a ScalarArrayOpExpr, but the node has some rather weird address. Or maybe it's not that weird. 0x17 is 23, and so is: select 'int4'::regtype::oid; oid ----- 23 See what happened? + int64 target_typ = fexpr->funcresulttype; ... + fexpr->args = list_insert_nth(fexpr->args, 0, (void *) target_typ); This is inserting the desired result type oid directly as the first thing in the list of fexpr's args. But at the time your support function is called, nothing is being evaluated yet. You are just manipulating a tree of expressions to be evaluated later, and you want fexpr's first arg to be an expression that will produce this type oid later, when it is evaluated. A constant would do nicely: + Const *target = makeConst( INTERNALOID, -1, InvalidOid, SIZEOF_DATUM, ObjectIdGetDatum(fexpr->funcresulttype), false, true); + fexpr->args = list_insert_nth(fexpr->args, 0, target); With that change, it doesn't segfault, but it does do this: ERROR: cast jsonb to type 0 is not allowed and that's because of this: + Oid targetOid = DatumGetObjectId(0); The DatumGetFoo(x) macros are for when you already have the Datum (it's x) and you know it's a Foo. So this is just setting targetOid to zero. When you want to get something from function argument 0 and you know that's a Foo, you use a PG_GETARG_FOO(argno) macro (which amounts to PG_GETARG_DATUM(argno) followed by DatumGetFoo. So, with + Oid targetOid = PG_GETARG_OID(0); SELECT (test_json -> 0)::int4, test_json -> 0 FROM test_jsonb WHERE json_type = 'scalarint'; int4 | ?column? ------+---------- 2 | 2 However, EXPLAIN is sad: ERROR: cannot display a value of type internal and that may be where this idea runs aground. Now, I was expecting something to complain about the result of jsonb_array_element_type, and that didn't happen. We rewrote a function that was supposed to be a cast to int4, and replaced it with a function returning internal, and evaluation happily just took that as the int4 that the next node expected. If something had complained about that, it might have been necessary to insert some new node above the internal-returning function to say the result was really int4. Notice there is a makeRelabelType() for that. (I had figured there probably was, but didn't know its exact name.) So it doesn't seem strictly necessary to do that, but it might make the EXPLAIN result look better (if EXPLAIN were made to work, of course). Now, my guess is EXPLAIN is complaining when it sees the Const of type internal, and doesn't know how to show that value. Perhaps makeRelabelType is the answer there, too: what if the Const has Oid type, so EXPLAIN can show it, and what's inserted as the function argument is a relabel node saying it's internal? Haven't tried that yet. Regards, -Chap
pgsql-hackers by date: