Transforming IN (...) to ORs, volatility
We sometimes transform IN-clauses to a list of ORs:
postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
QUERY PLAN
------------------------------------------------------
Seq Scan on foo (cost=0.00..39.10 rows=19 width=12)
Filter: ((a = b) OR (a = c))
(2 rows)
But what if you replace "a" with a volatile function? It doesn't seem
legal to do that transformation in that case, but we do it:
postgres=# explain SELECT * FROM foo WHERE (random()*2)::integer IN (b, c);
QUERY PLAN
-------------------------------------------------------------------------------------------------
-------------------
Seq Scan on foo (cost=0.00..68.20 rows=19 width=12)
Filter: ((((random() * 2::double precision))::integer = b) OR
(((random() * 2::double precision))::integer = c))
(2 rows)
I tried to read the SQL spec to see if it has anything to say about
that, but I couldn't find anything. My common sense says that that
transformation is not legal.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Fri, Apr 01, 2011 at 02:24:53PM +0300, Heikki Linnakangas wrote:
I tried to read the SQL spec to see if it has anything to say about
that, but I couldn't find anything. My common sense says that that
transformation is not legal.
Your feeling is correct; I would motivate it as follows.
random() IN (b,c)
is not equivalent to
(random() = b) OR (random() = c)
because the two random() will evaluate to two different numbers. So,
for instance, if you define random_boolean() as either true or false
randomly (and VOLATILEly), then
random_boolean() IN (true, false)
is always true, while
(random_boolean() = true) OR (random_boolean() = false)
is not (has probability 75%). For instance, the first random_boolean()
might return false while the second one returns true.
Best regards,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gianni.ciolli@2ndquadrant.it | www.2ndquadrant.it
On Fri, Apr 1, 2011 at 7:24 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
My common sense says that that transformation
is not legal.
+1.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
We sometimes transform IN-clauses to a list of ORs:
postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
QUERY PLAN
------------------------------------------------------
Seq Scan on foo (cost=0.00..39.10 rows=19 width=12)
Filter: ((a = b) OR (a = c))
(2 rows)
But what if you replace "a" with a volatile function? It doesn't seem
legal to do that transformation in that case, but we do it:
This is the fault of transformAExprIn(). But please let's *not* fix
this by adding volatility to the set of heuristics used there. Looking
at this again, it seems to me that most of the problem with this code
is that we're trying to make optimization decisions in the parser.
I think what we ought to do is have the parser emit a full-fledged
InExpr node type (with semantics rather like CaseExpr) and then teach
the planner to optimize that to something else when it seems
safe/prudent to do so. One nontrivial advantage of that is that
rules/views containing IN constructs would start to reverse-parse
in the same fashion, instead of introducing weird substitute
expressions.
regards, tom lane
On 02.04.2011 20:48, Tom Lane wrote:
Heikki Linnakangas<heikki.linnakangas@enterprisedb.com> writes:
We sometimes transform IN-clauses to a list of ORs:
postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
QUERY PLAN
------------------------------------------------------
Seq Scan on foo (cost=0.00..39.10 rows=19 width=12)
Filter: ((a = b) OR (a = c))
(2 rows)But what if you replace "a" with a volatile function? It doesn't seem
legal to do that transformation in that case, but we do it:This is the fault of transformAExprIn(). But please let's *not* fix
this by adding volatility to the set of heuristics used there. Looking
at this again, it seems to me that most of the problem with this code
is that we're trying to make optimization decisions in the parser.
Agreed. The history of this is that before 8.2 all IN clauses were
transformed to OR clauses straight in the grammar. 8.2 added the code to
represent IN clause as a ScalarArrayOpExpr, but it was changed in 8.2.10
to use the OR-form again for Vars
(http://archives.postgresql.org/pgsql-hackers/2008-10/msg01269.php)
I think what we ought to do is have the parser emit a full-fledged
InExpr node type (with semantics rather like CaseExpr) and then teach
the planner to optimize that to something else when it seems
safe/prudent to do so. One nontrivial advantage of that is that
rules/views containing IN constructs would start to reverse-parse
in the same fashion, instead of introducing weird substitute
expressions.
Here's my first cut at that. The lefthand expression is now evaluated
only once, and stored in econtext->caseValue. Parse analysis turns the
righthand expressions into a list of comparison expressions like
"CaseTestExpr = value1". It's perhaps time that we rename CaseTestExpr
into something more generic, now that it's used not only in CASE
expressions, but also in IN and in UPDATE targets, but I didn't do that
in this patch.
eval_const_expressions checks the lefthand expression for volatile
functions. If there aren't any, it transform the InExprs to a list of ORs.
This isn't finished, because it doesn't yet do the transformation to
ScalarArrayOpExpr. The OR form is much slower to plan, which is why the
ScalarArrayOpExpr transformation was introduced in 8.2. I'll continue
hacking on that, but please let me know if you have a better idea on how
to handle that. One alternative is to teach the machinery that matches
restrictinfos to usable indexes to handle InExpr like it does
ScalarArrayOpExprs, but I don't know that code very well.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Attachments:
fix-in-clause-2.patchtext/x-diff; name=fix-in-clause-2.patchDownload
*** a/src/backend/executor/execQual.c
--- b/src/backend/executor/execQual.c
***************
*** 125,130 **** static Datum ExecEvalCase(CaseExprState *caseExpr, ExprContext *econtext,
--- 125,133 ----
static Datum ExecEvalCaseTestExpr(ExprState *exprstate,
ExprContext *econtext,
bool *isNull, ExprDoneCond *isDone);
+ static Datum ExecEvalInExpr(InExprState *exprstate,
+ ExprContext *econtext,
+ bool *isNull, ExprDoneCond *isDone);
static Datum ExecEvalArray(ArrayExprState *astate,
ExprContext *econtext,
bool *isNull, ExprDoneCond *isDone);
***************
*** 2854,2859 **** ExecEvalCaseTestExpr(ExprState *exprstate,
--- 2857,2933 ----
}
/* ----------------------------------------------------------------
+ * ExecEvalInExpr
+ *
+ * Evaluate an IN clause.
+ * ----------------------------------------------------------------
+ */
+ static Datum
+ ExecEvalInExpr(InExprState *inExpr, ExprContext *econtext,
+ bool *isNull, ExprDoneCond *isDone)
+ {
+ ListCell *clause;
+ Datum save_datum;
+ bool save_isNull;
+ bool anyNull = false;
+
+ if (isDone)
+ *isDone = ExprSingleResult;
+
+ /*
+ * First we evaluate the lefthand side of the IN clause, and save the
+ * value where the CaseTestExpr placeholders can find it. We must save and
+ * restore prior setting of econtext's caseValue fields, in case this node
+ * is itself within a larger CASE or IN.
+ */
+ save_datum = econtext->caseValue_datum;
+ save_isNull = econtext->caseValue_isNull;
+
+ econtext->caseValue_datum = ExecEvalExpr(inExpr->lexpr,
+ econtext,
+ &econtext->caseValue_isNull,
+ NULL);
+
+ /*
+ * Evaluate each comparison expression in turn, until we find a match or
+ * run out of expressions. NULLs are handled the same as in a list of
+ * OR expressions.
+ */
+ anyNull = false;
+ foreach(clause, inExpr->comparisons)
+ {
+ ExprState *expr = lfirst(clause);
+ Datum clause_value;
+
+ clause_value = ExecEvalExpr(expr, econtext, isNull, NULL);
+
+ if (*isNull)
+ anyNull = true; /* remember we got a null */
+ else
+ {
+ /* return true as soon as one comparison returns true. */
+ if (DatumGetBool(clause_value))
+ {
+ econtext->caseValue_datum = save_datum;
+ econtext->caseValue_isNull = save_isNull;
+ *isNull = false;
+ return BoolGetDatum(true);
+ }
+ }
+ }
+
+ econtext->caseValue_datum = save_datum;
+ econtext->caseValue_isNull = save_isNull;
+
+ /*
+ * If no match, return false, or NULL if any of the comparisons returned
+ * NULL.
+ */
+ *isNull = anyNull;
+ return BoolGetDatum(false);
+ }
+
+ /* ----------------------------------------------------------------
* ExecEvalArray - ARRAY[] expressions
* ----------------------------------------------------------------
*/
***************
*** 4253,4258 **** ExecInitExpr(Expr *node, PlanState *parent)
--- 4327,4342 ----
state = (ExprState *) makeNode(ExprState);
state->evalfunc = ExecEvalCaseTestExpr;
break;
+ case T_InExpr:
+ {
+ InExpr *inexpr = (InExpr *) node;
+ InExprState *istate = (InExprState *) makeNode(InExprState);
+ istate->lexpr = ExecInitExpr((Expr *) inexpr->lexpr, parent);
+ istate->comparisons = (List *) ExecInitExpr((Expr *) inexpr->comparisons, parent);
+ istate->xprstate.evalfunc = (ExprStateEvalFunc) ExecEvalInExpr;
+ state = (ExprState *) istate;
+ }
+ break;
case T_Aggref:
{
Aggref *aggref = (Aggref *) node;
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 1526,1531 **** _copyCaseTestExpr(CaseTestExpr *from)
--- 1526,1546 ----
}
/*
+ * _copyInExpr
+ */
+ static InExpr *
+ _copyInExpr(InExpr *from)
+ {
+ InExpr *newnode = makeNode(InExpr);
+
+ COPY_NODE_FIELD(lexpr);
+ COPY_NODE_FIELD(comparisons);
+ COPY_LOCATION_FIELD(location);
+
+ return newnode;
+ }
+
+ /*
* _copyArrayExpr
*/
static ArrayExpr *
***************
*** 4044,4049 **** copyObject(void *from)
--- 4059,4067 ----
case T_CaseTestExpr:
retval = _copyCaseTestExpr(from);
break;
+ case T_InExpr:
+ retval = _copyInExpr(from);
+ break;
case T_ArrayExpr:
retval = _copyArrayExpr(from);
break;
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 571,576 **** _equalCaseTestExpr(CaseTestExpr *a, CaseTestExpr *b)
--- 571,586 ----
}
static bool
+ _equalInExpr(InExpr *a, InExpr *b)
+ {
+ COMPARE_NODE_FIELD(lexpr);
+ COMPARE_NODE_FIELD(comparisons);
+ COMPARE_LOCATION_FIELD(location);
+
+ return true;
+ }
+
+ static bool
_equalArrayExpr(ArrayExpr *a, ArrayExpr *b)
{
COMPARE_SCALAR_FIELD(array_typeid);
***************
*** 2610,2615 **** equal(void *a, void *b)
--- 2620,2628 ----
case T_CaseTestExpr:
retval = _equalCaseTestExpr(a, b);
break;
+ case T_InExpr:
+ retval = _equalInExpr(a, b);
+ break;
case T_ArrayExpr:
retval = _equalArrayExpr(a, b);
break;
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
***************
*** 88,93 **** exprType(Node *expr)
--- 88,96 ----
case T_BoolExpr:
type = BOOLOID;
break;
+ case T_InExpr:
+ type = BOOLOID;
+ break;
case T_SubLink:
{
SubLink *sublink = (SubLink *) expr;
***************
*** 680,685 **** exprCollation(Node *expr)
--- 683,691 ----
case T_BoolExpr:
coll = InvalidOid; /* result is always boolean */
break;
+ case T_InExpr:
+ coll = InvalidOid; /* result is always boolean */
+ break;
case T_SubLink:
{
SubLink *sublink = (SubLink *) expr;
***************
*** 912,917 **** exprSetCollation(Node *expr, Oid collation)
--- 918,926 ----
case T_BoolExpr:
Assert(!OidIsValid(collation)); /* result is always boolean */
break;
+ case T_InExpr:
+ Assert(!OidIsValid(collation)); /* result is always boolean */
+ break;
case T_SubLink:
#ifdef USE_ASSERT_CHECKING
{
***************
*** 1687,1692 **** expression_tree_walker(Node *node,
--- 1696,1711 ----
return true;
}
break;
+ case T_InExpr:
+ {
+ InExpr *inexpr = (InExpr *) node;
+
+ if (walker(inexpr->lexpr, context))
+ return true;
+ if (walker(inexpr->comparisons, context))
+ return true;
+ }
+ break;
case T_ArrayExpr:
return walker(((ArrayExpr *) node)->elements, context);
case T_RowExpr:
***************
*** 2289,2294 **** expression_tree_mutator(Node *node,
--- 2308,2324 ----
return (Node *) newnode;
}
break;
+ case T_InExpr:
+ {
+ InExpr *inexpr = (InExpr *) node;
+ InExpr *newnode;
+
+ FLATCOPY(newnode, inexpr, InExpr);
+ MUTATE(newnode->lexpr, inexpr->lexpr, Expr *);
+ MUTATE(newnode->comparisons, inexpr->comparisons, List *);
+ return (Node *) newnode;
+ }
+ break;
case T_ArrayExpr:
{
ArrayExpr *arrayexpr = (ArrayExpr *) node;
***************
*** 2758,2763 **** bool
--- 2788,2803 ----
return true;
}
break;
+ case T_InExpr:
+ {
+ InExpr *inexpr = (InExpr *) node;
+
+ if (walker(inexpr->lexpr, context))
+ return true;
+ if (walker(inexpr->comparisons, context))
+ return true;
+ }
+ break;
case T_RowExpr:
/* Assume colnames isn't interesting */
return walker(((RowExpr *) node)->args, context);
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 1262,1267 **** _outCaseTestExpr(StringInfo str, CaseTestExpr *node)
--- 1262,1277 ----
}
static void
+ _outInExpr(StringInfo str, InExpr *node)
+ {
+ WRITE_NODE_TYPE("INEXPR");
+
+ WRITE_NODE_FIELD(lexpr);
+ WRITE_NODE_FIELD(comparisons);
+ WRITE_LOCATION_FIELD(location);
+ }
+
+ static void
_outArrayExpr(StringInfo str, ArrayExpr *node)
{
WRITE_NODE_TYPE("ARRAY");
***************
*** 2880,2885 **** _outNode(StringInfo str, void *obj)
--- 2890,2898 ----
case T_CaseTestExpr:
_outCaseTestExpr(str, obj);
break;
+ case T_InExpr:
+ _outInExpr(str, obj);
+ break;
case T_ArrayExpr:
_outArrayExpr(str, obj);
break;
*** a/src/backend/nodes/readfuncs.c
--- b/src/backend/nodes/readfuncs.c
***************
*** 897,902 **** _readCaseTestExpr(void)
--- 897,917 ----
}
/*
+ * _readInExpr
+ */
+ static InExpr *
+ _readInExpr(void)
+ {
+ READ_LOCALS(InExpr);
+
+ READ_NODE_FIELD(lexpr);
+ READ_NODE_FIELD(comparisons);
+ READ_LOCATION_FIELD(location);
+
+ READ_DONE();
+ }
+
+ /*
* _readArrayExpr
*/
static ArrayExpr *
***************
*** 1311,1316 **** parseNodeString(void)
--- 1326,1333 ----
return_value = _readCollateExpr();
else if (MATCH("CASE", 4))
return_value = _readCaseExpr();
+ else if (MATCH("INEXPR", 6))
+ return_value = _readInExpr();
else if (MATCH("WHEN", 4))
return_value = _readCaseWhen();
else if (MATCH("CASETESTEXPR", 12))
*** a/src/backend/optimizer/util/clauses.c
--- b/src/backend/optimizer/util/clauses.c
***************
*** 2801,2806 **** eval_const_expressions_mutator(Node *node,
--- 2801,2860 ----
else
return copyObject(node);
}
+ if (IsA(node, InExpr))
+ {
+ InExpr *inexpr = (InExpr *) node;
+ Node *newlexpr;
+
+ /* Simplify the left expression */
+ newlexpr = eval_const_expressions_mutator((Node *) inexpr->lexpr,
+ context);
+
+ /*
+ * If the left expression is not volatile, transform the expression
+ * into a list of ORs. For example:
+ * foo IN (bar, 123)
+ * is transformed to
+ * foo = bar OR foo = 123
+ *
+ * The OR clauses give the planner more room for optimization, like
+ * choosing a bitmap-OR path. But we can't do the transformation if
+ * the lefthand expression is volatile, because it is evaluated
+ * multiple times in the OR form.
+ */
+ if (!contain_volatile_functions(newlexpr))
+ {
+ Node *orclause;
+ Node *save_case_val;
+
+ save_case_val = context->case_val;
+ context->case_val = newlexpr;
+ orclause = (Node *) make_orclause(inexpr->comparisons);
+ orclause = eval_const_expressions_mutator(orclause,
+ context);
+ context->case_val = save_case_val;
+ return orclause;
+ }
+ else
+ {
+ /* can't transform to OR clauses. */
+ InExpr *newinexpr;
+ ListCell *lc;
+
+ newinexpr = makeNode(InExpr);
+ newinexpr->lexpr = (Expr *) newlexpr;
+ foreach(lc, inexpr->comparisons)
+ {
+ Node *cmp;
+
+ cmp = eval_const_expressions_mutator((Node *) lfirst(lc),
+ context);
+ newinexpr->comparisons = lappend(newinexpr->comparisons, cmp);
+ }
+
+ return (Node *) newinexpr;
+ }
+ }
if (IsA(node, ArrayExpr))
{
ArrayExpr *arrayexpr = (ArrayExpr *) node;
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 10055,10061 **** a_expr: c_expr { $$ = $1; }
else
{
/* generate scalar NOT IN expression */
! $$ = (Node *) makeSimpleA_Expr(AEXPR_IN, "<>", $1, $4, @2);
}
}
| a_expr subquery_Op sub_type select_with_parens %prec Op
--- 10055,10063 ----
else
{
/* generate scalar NOT IN expression */
! $$ = (Node *) makeA_Expr(AEXPR_NOT, NIL, NULL,
! (Node *) makeSimpleA_Expr(AEXPR_IN, "=", $1, $4, @2),
! @2);
}
}
| a_expr subquery_Op sub_type select_with_parens %prec Op
*** a/src/backend/parser/parse_expr.c
--- b/src/backend/parser/parse_expr.c
***************
*** 313,318 **** transformExpr(ParseState *pstate, Node *expr)
--- 313,319 ----
case T_NullIfExpr:
case T_ScalarArrayOpExpr:
case T_BoolExpr:
+ case T_InExpr:
case T_FieldSelect:
case T_FieldStore:
case T_RelabelType:
***************
*** 1071,1187 **** transformAExprOf(ParseState *pstate, A_Expr *a)
static Node *
transformAExprIn(ParseState *pstate, A_Expr *a)
{
! Node *result = NULL;
Node *lexpr;
List *rexprs;
- List *rvars;
- List *rnonvars;
- bool useOr;
bool haveRowExpr;
ListCell *l;
! /*
! * If the operator is <>, combine with AND not OR.
! */
! if (strcmp(strVal(linitial(a->name)), "<>") == 0)
! useOr = false;
! else
! useOr = true;
/*
- * We try to generate a ScalarArrayOpExpr from IN/NOT IN, but this is only
- * possible if the inputs are all scalars (no RowExprs) and there is a
- * suitable array type available. If not, we fall back to a boolean
- * condition tree with multiple copies of the lefthand expression. Also,
- * any IN-list items that contain Vars are handled as separate boolean
- * conditions, because that gives the planner more scope for optimization
- * on such clauses.
- *
* First step: transform all the inputs, and detect whether any are
! * RowExprs or contain Vars.
*/
lexpr = transformExpr(pstate, a->lexpr);
haveRowExpr = (lexpr && IsA(lexpr, RowExpr));
! rexprs = rvars = rnonvars = NIL;
foreach(l, (List *) a->rexpr)
{
Node *rexpr = transformExpr(pstate, lfirst(l));
haveRowExpr |= (rexpr && IsA(rexpr, RowExpr));
rexprs = lappend(rexprs, rexpr);
- if (contain_vars_of_level(rexpr, 0))
- rvars = lappend(rvars, rexpr);
- else
- rnonvars = lappend(rnonvars, rexpr);
}
/*
! * ScalarArrayOpExpr is only going to be useful if there's more than one
! * non-Var righthand item. Also, it won't work for RowExprs.
*/
! if (!haveRowExpr && list_length(rnonvars) > 1)
! {
! List *allexprs;
! Oid scalar_type;
! Oid array_type;
! /*
! * Try to select a common type for the array elements. Note that
! * since the LHS' type is first in the list, it will be preferred when
! * there is doubt (eg, when all the RHS items are unknown literals).
! *
! * Note: use list_concat here not lcons, to avoid damaging rnonvars.
! */
! allexprs = list_concat(list_make1(lexpr), rnonvars);
! scalar_type = select_common_type(pstate, allexprs, NULL, NULL);
! /* Do we have an array type to use? */
! if (OidIsValid(scalar_type))
! array_type = get_array_type(scalar_type);
! else
! array_type = InvalidOid;
! if (array_type != InvalidOid)
! {
! /*
! * OK: coerce all the right-hand non-Var inputs to the common type
! * and build an ArrayExpr for them.
! */
! List *aexprs;
! ArrayExpr *newa;
! aexprs = NIL;
! foreach(l, rnonvars)
! {
! Node *rexpr = (Node *) lfirst(l);
! rexpr = coerce_to_common_type(pstate, rexpr,
! scalar_type,
! "IN");
! aexprs = lappend(aexprs, rexpr);
! }
! newa = makeNode(ArrayExpr);
! newa->array_typeid = array_type;
! /* array_collid will be set by parse_collate.c */
! newa->element_typeid = scalar_type;
! newa->elements = aexprs;
! newa->multidims = false;
! newa->location = -1;
!
! result = (Node *) make_scalar_array_op(pstate,
! a->name,
! useOr,
! lexpr,
! (Node *) newa,
! a->location);
!
! /* Consider only the Vars (if any) in the loop below */
! rexprs = rvars;
}
}
! /*
! * Must do it the hard way, ie, with a boolean expression tree.
! */
foreach(l, rexprs)
{
Node *rexpr = (Node *) lfirst(l);
--- 1072,1156 ----
static Node *
transformAExprIn(ParseState *pstate, A_Expr *a)
{
! InExpr *result;
Node *lexpr;
List *rexprs;
bool haveRowExpr;
ListCell *l;
+ List *newrexprs;
+ List *comparisons;
+ Node *placeholder;
+ List *fieldPlaceholders = NIL;
! Assert(strcmp(strVal(linitial(a->name)), "=") == 0);
/*
* First step: transform all the inputs, and detect whether any are
! * RowExprs.
*/
lexpr = transformExpr(pstate, a->lexpr);
haveRowExpr = (lexpr && IsA(lexpr, RowExpr));
! rexprs = NIL;
foreach(l, (List *) a->rexpr)
{
Node *rexpr = transformExpr(pstate, lfirst(l));
haveRowExpr |= (rexpr && IsA(rexpr, RowExpr));
rexprs = lappend(rexprs, rexpr);
}
+ lexpr = (Node *) transformExpr(pstate, a->lexpr);
+
/*
! * Run collation assignment on the test expression so that we know
! * what collation to mark the placeholder with. In principle we
! * could leave it to parse_collate.c to do that later, but propagating
! * the result to the CaseTestExpr would be unnecessarily complicated.
*/
! assign_expr_collations(pstate, lexpr);
! /* generate placeholder for test expression */
! if (IsA(lexpr, Const))
! {
! placeholder = lexpr;
! }
! else
! {
! CaseTestExpr *c = makeNode(CaseTestExpr);
! c->typeId = exprType(lexpr);
! c->typeMod = exprTypmod(lexpr);
! c->collation = exprCollation(lexpr);
! placeholder = (Node *) c;
! }
! /*
! * If this is a row IN clause, generate placeholders for each field in
! * the test expression.
! */
! if (IsA(lexpr, RowExpr))
! {
! AttrNumber fieldnum = 1;
! fieldPlaceholders = NIL;
! foreach(l, ((RowExpr *) lexpr)->args)
! {
! Node *arg = lfirst(l);
! FieldSelect *f = makeNode(FieldSelect);
!
! f->arg = (Expr *) placeholder;
! f->fieldnum = fieldnum++;
! f->resulttype = exprType(arg);
! f->resulttypmod = exprTypmod(arg);
! f->resultcollid = exprCollation(arg);
! fieldPlaceholders = lappend(fieldPlaceholders, f);
}
}
! /* Construct comparison expressions */
! newrexprs = NIL;
! comparisons = NIL;
! haveRowExpr = IsA(lexpr, RowExpr);
foreach(l, rexprs)
{
Node *rexpr = (Node *) lfirst(l);
***************
*** 1195,1223 **** transformAExprIn(ParseState *pstate, A_Expr *a)
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("arguments of row IN must all be row expressions"),
parser_errposition(pstate, a->location)));
cmp = make_row_comparison_op(pstate,
a->name,
! (List *) copyObject(((RowExpr *) lexpr)->args),
((RowExpr *) rexpr)->args,
a->location);
}
else
cmp = (Node *) make_op(pstate,
a->name,
! copyObject(lexpr),
rexpr,
a->location);
cmp = coerce_to_boolean(pstate, cmp, "IN");
! if (result == NULL)
! result = cmp;
! else
! result = (Node *) makeBoolExpr(useOr ? OR_EXPR : AND_EXPR,
! list_make2(result, cmp),
! a->location);
}
! return result;
}
static Node *
--- 1164,1200 ----
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("arguments of row IN must all be row expressions"),
parser_errposition(pstate, a->location)));
+
cmp = make_row_comparison_op(pstate,
a->name,
! fieldPlaceholders,
((RowExpr *) rexpr)->args,
a->location);
}
else
+ {
+ Node *arg;
+
+ arg = (Node *) placeholder;
cmp = (Node *) make_op(pstate,
a->name,
! arg,
rexpr,
a->location);
+ }
cmp = coerce_to_boolean(pstate, cmp, "IN");
!
! comparisons = lappend(comparisons, cmp);
}
! result = makeNode(InExpr);
! result->lexpr = (Expr *) lexpr;
! result->comparisons = comparisons;
! /* Make the result have the original input's parse location */
! result->location = exprLocation((Node *) a);
!
! return (Node *) result;
}
static Node *
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 5245,5250 **** get_rule_expr(Node *node, deparse_context *context,
--- 5245,5335 ----
}
break;
+ case T_InExpr:
+ {
+ InExpr *inexpr = (InExpr *) node;
+ ListCell *temp;
+ const char *sep;
+
+ if (!PRETTY_PAREN(context))
+ appendStringInfoChar(buf, '(');
+
+ get_rule_expr((Node *) inexpr->lexpr, context, showimplicit);
+ appendStringInfoString(buf, " IN (");
+ sep = "";
+ foreach(temp, inexpr->comparisons)
+ {
+ Node *cmp = (Node *) lfirst(temp);
+
+ appendStringInfoString(buf, sep);
+
+ cmp = strip_implicit_coercions(cmp);
+ /*
+ * Scalar IN clause. The comparison expressions are of
+ * form:
+ * CaseTestExpr = arg
+ * Print the 'arg'
+ */
+ if (IsA(cmp, OpExpr))
+ {
+ OpExpr *eq = (OpExpr *) cmp;
+
+ Assert(list_length(eq->args) == 2);
+ Assert(IsA(strip_implicit_coercions(linitial(eq->args)),
+ CaseTestExpr) ||
+ IsA(strip_implicit_coercions(linitial(eq->args)),
+ Const));
+
+ get_rule_expr(lsecond(eq->args), context, false);
+ }
+ /*
+ * Row IN clause. The comparison expressions are of form:
+ * CaseTestExpr.field1 = arg1 AND CaseTestExpr.field2 = arg2
+ * Print '(arg1, arg2)'
+ */
+ else if (IsA(cmp, BoolExpr))
+ {
+ BoolExpr *b = (BoolExpr *) cmp;
+ ListCell *temp2;
+ const char *sep2 = "";
+
+ if (b->boolop != AND_EXPR)
+ elog(ERROR, "unexpected boolean expression in IN clause: %d",
+ b->boolop);
+ appendStringInfoChar(buf, '(');
+ foreach (temp2, b->args)
+ {
+ Node *a = (Node *) lfirst(temp2);
+ OpExpr *eq;
+
+ if (!IsA(a, OpExpr))
+ elog(ERROR, "unexpected IN clause: %d",
+ (int) nodeTag(a));
+ eq = (OpExpr *) a;
+
+ Assert(list_length(eq->args) == 2);
+ Assert(IsA(strip_implicit_coercions(linitial(eq->args)),
+ FieldSelect));
+ /* Assert the arg of FieldSelect is CaseTestExpr or Const */
+
+ appendStringInfoString(buf, sep2);
+ get_rule_expr(lsecond(eq->args), context, false);
+ sep2 = ", ";
+ }
+ appendStringInfoChar(buf, ')');
+ }
+ else
+ elog(ERROR, "unexpected IN clause: %d",
+ (int) nodeTag(cmp));
+ sep = ", ";
+ }
+ appendStringInfoChar(buf, ')');
+
+ if (!PRETTY_PAREN(context))
+ appendStringInfoChar(buf, ')');
+ }
+ break;
+
case T_ArrayExpr:
{
ArrayExpr *arrayexpr = (ArrayExpr *) node;
*** a/src/include/nodes/execnodes.h
--- b/src/include/nodes/execnodes.h
***************
*** 828,833 **** typedef struct CaseWhenState
--- 828,844 ----
} CaseWhenState;
/* ----------------
+ * InExprState node
+ * ----------------
+ */
+ typedef struct InExprState
+ {
+ ExprState xprstate;
+ ExprState *lexpr; /* lefthand expression */
+ List *comparisons; /* comparison expressions */
+ } InExprState;
+
+ /* ----------------
* ArrayExprState node
*
* Note: ARRAY[] expressions always produce varlena arrays, never fixed-length
*** a/src/include/nodes/nodes.h
--- b/src/include/nodes/nodes.h
***************
*** 170,175 **** typedef enum NodeTag
--- 170,176 ----
T_JoinExpr,
T_FromExpr,
T_IntoClause,
+ T_InExpr,
/*
* TAGS FOR EXPRESSION STATE NODES (execnodes.h)
***************
*** 203,208 **** typedef enum NodeTag
--- 204,210 ----
T_NullTestState,
T_CoerceToDomainState,
T_DomainConstraintState,
+ T_InExprState,
/*
* TAGS FOR PLANNER NODES (relation.h)
*** a/src/include/nodes/primnodes.h
--- b/src/include/nodes/primnodes.h
***************
*** 797,804 **** typedef struct CaseWhen
* This is effectively like a Param, but can be implemented more simply
* since we need only one replacement value at a time.
*
! * We also use this in nested UPDATE expressions.
! * See transformAssignmentIndirection().
*/
typedef struct CaseTestExpr
{
--- 797,804 ----
* This is effectively like a Param, but can be implemented more simply
* since we need only one replacement value at a time.
*
! * We also use this in nested UPDATE expressions and IN expressions.
! * See transformAssignmentIndirection() and transformInExpr().
*/
typedef struct CaseTestExpr
{
***************
*** 808,813 **** typedef struct CaseTestExpr
--- 808,830 ----
Oid collation; /* collation for the substituted value */
} CaseTestExpr;
+
+ /*
+ * Parse analysis converts IN clause to a list of valid boolean expressions
+ * of the form
+ * CaseTestExpr '=' rexpr
+ * where the CaseTestExpr node is a placeholder that emits the correct
+ * value of the lefthand expression at runtime. This structure is used so
+ * that the lefthand expression need be evaluated only once.
+ */
+ typedef struct InExpr
+ {
+ Expr xpr;
+ Expr *lexpr; /* lefthand expression */
+ List *comparisons; /* comparison expressions */
+ int location; /* token location, or -1 if unknown */
+ } InExpr;
+
/*
* ArrayExpr - an ARRAY[] expression
*
*** /dev/null
--- b/src/test/regress/expected/inclause.out
***************
*** 0 ****
--- 1,156 ----
+ --
+ -- Test the IN clause
+ --
+ CREATE TABLE IN_TBL (
+ i integer,
+ j integer
+ );
+ INSERT INTO IN_TBL VALUES (1, 2);
+ INSERT INTO IN_TBL VALUES (2, 3);
+ INSERT INTO IN_TBL VALUES (3, 4);
+ INSERT INTO IN_TBL VALUES (4, NULL);
+ CREATE INDEX i_in_tbl ON IN_TBL (i);
+ -- Simple scalar IN, including untyped literals
+ SELECT 1 IN (1, 2);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ SELECT 50 IN (1, 2, 3, 4);
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ SELECT 'foo' IN ('foo', 'bar');
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ SELECT 'notfound' IN ('foo', 'bar');
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ SELECT 'foo' IN ('foo');
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ SELECT 'foo' IN ('bar');
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ SELECT '123' IN ('bar', 123);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ SELECT 'foo' IN ('bar', 123);
+ ERROR: invalid input syntax for integer: "foo"
+ LINE 1: SELECT 'foo' IN ('bar', 123);
+ ^
+ -- Simple row IN
+ SELECT (1, 2) IN ((1, 2), (2, 3));
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ SELECT (1, 3) IN ((1, 2), (2, 3));
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ SELECT (1, 3) IN ((1, 2));
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ -- Scalar and row IN using table
+ SELECT * FROM in_tbl WHERE i IN (0, 1);
+ i | j
+ ---+---
+ 1 | 2
+ (1 row)
+
+ SELECT * FROM in_tbl WHERE i IN (5, 6, 7, 8, 9, 10);
+ i | j
+ ---+---
+ (0 rows)
+
+ SELECT * FROM in_tbl WHERE (i, j) IN ((1, 2), (2, 4), (2, 3), (10, 11));
+ i | j
+ ---+---
+ 1 | 2
+ 2 | 3
+ (2 rows)
+
+ SELECT * FROM in_tbl WHERE (i, j) IN ((1, 3));
+ i | j
+ ---+---
+ (0 rows)
+
+ -- Scalar with NULLs
+ SELECT 1 IN (0, NULL, 2);
+ ?column?
+ ----------
+
+ (1 row)
+
+ SELECT 1 IN (1, NULL, 2);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ SELECT 3 IN (1, 2, NULL);
+ ?column?
+ ----------
+
+ (1 row)
+
+ SELECT NULL IN (1, 2, NULL);
+ ?column?
+ ----------
+
+ (1 row)
+
+ --
+ -- Check that the lefthand expression is only executed once. Before 9.1
+ -- we used to transform IN to a list of OR expressions, which lead to
+ -- multiple evaluation.
+ --
+ CREATE FUNCTION volatilefunc() RETURNS int4 AS $$
+ begin
+ RAISE NOTICE 'volatilefunc executed';
+ RETURN 123;
+ end;
+ $$ LANGUAGE plpgsql VOLATILE;
+ SELECT volatilefunc() IN (1, 2, 3, 123);
+ NOTICE: volatilefunc executed
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ -- Subselect IN
+ SELECT 1 IN (SELECT i FROM in_tbl);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ --
+ -- Clean up
+ --
+ DROP TABLE IN_TBL;
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
***************
*** 1293,1323 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline);
pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolreplication AS userepl, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin;
pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, s.xact_start, s.query_start, s.waiting, s.current_query FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_hostname, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
! pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
! pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, pg_stat_get_db_conflict_all(d.oid) AS conflicts, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d;
pg_stat_database_conflicts | SELECT d.oid AS datid, d.datname, pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock FROM pg_database d;
pg_stat_replication | SELECT s.procpid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location, w.replay_location, w.sync_priority, w.sync_state FROM pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_hostname, client_port), pg_authid u, pg_stat_get_wal_senders() w(procpid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid = u.oid) AND (s.procpid = w.procpid));
! pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text));
! pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count, pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text));
pg_stat_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_function_calls(p.oid) AS calls, (pg_stat_get_function_time(p.oid) / 1000) AS total_time, (pg_stat_get_function_self_time(p.oid) / 1000) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL));
! pg_stat_user_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_indexes.schemaname !~ '^pg_toast'::text));
! pg_stat_user_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count, pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
! pg_stat_xact_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_xact_numscans(c.oid) AS seq_scan, pg_stat_get_xact_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_xact_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_xact_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_xact_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
! pg_stat_xact_sys_tables | SELECT pg_stat_xact_all_tables.relid, pg_stat_xact_all_tables.schemaname, pg_stat_xact_all_tables.relname, pg_stat_xact_all_tables.seq_scan, pg_stat_xact_all_tables.seq_tup_read, pg_stat_xact_all_tables.idx_scan, pg_stat_xact_all_tables.idx_tup_fetch, pg_stat_xact_all_tables.n_tup_ins, pg_stat_xact_all_tables.n_tup_upd, pg_stat_xact_all_tables.n_tup_del, pg_stat_xact_all_tables.n_tup_hot_upd FROM pg_stat_xact_all_tables WHERE ((pg_stat_xact_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_xact_all_tables.schemaname ~ '^pg_toast'::text));
pg_stat_xact_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_xact_function_calls(p.oid) AS calls, (pg_stat_get_xact_function_time(p.oid) / 1000) AS total_time, (pg_stat_get_xact_function_self_time(p.oid) / 1000) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL));
! pg_stat_xact_user_tables | SELECT pg_stat_xact_all_tables.relid, pg_stat_xact_all_tables.schemaname, pg_stat_xact_all_tables.relname, pg_stat_xact_all_tables.seq_scan, pg_stat_xact_all_tables.seq_tup_read, pg_stat_xact_all_tables.idx_scan, pg_stat_xact_all_tables.idx_tup_fetch, pg_stat_xact_all_tables.n_tup_ins, pg_stat_xact_all_tables.n_tup_upd, pg_stat_xact_all_tables.n_tup_del, pg_stat_xact_all_tables.n_tup_hot_upd FROM pg_stat_xact_all_tables WHERE ((pg_stat_xact_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_xact_all_tables.schemaname !~ '^pg_toast'::text));
! pg_statio_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
pg_statio_all_sequences | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, pg_stat_get_blocks_hit(c.oid) AS blks_hit FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'S'::"char");
! pg_statio_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read, (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit, (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, (pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid)) AS tidx_blks_read, pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit FROM ((((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) LEFT JOIN pg_class x ON ((t.reltoastidxid = x.oid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname, t.oid, x.oid;
! pg_statio_sys_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_indexes.schemaname ~ '^pg_toast'::text));
! pg_statio_sys_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_sequences.schemaname ~ '^pg_toast'::text));
! pg_statio_sys_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_tables.schemaname ~ '^pg_toast'::text));
! pg_statio_user_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text));
! pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text));
! pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text));
! pg_stats | SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stainherit AS inherited, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, CASE WHEN (s.stakind1 = ANY (ARRAY[1, 4])) THEN s.stavalues1 WHEN (s.stakind2 = ANY (ARRAY[1, 4])) THEN s.stavalues2 WHEN (s.stakind3 = ANY (ARRAY[1, 4])) THEN s.stavalues3 WHEN (s.stakind4 = ANY (ARRAY[1, 4])) THEN s.stavalues4 ELSE NULL::anyarray END AS most_common_vals, CASE WHEN (s.stakind1 = ANY (ARRAY[1, 4])) THEN s.stanumbers1 WHEN (s.stakind2 = ANY (ARRAY[1, 4])) THEN s.stanumbers2 WHEN (s.stakind3 = ANY (ARRAY[1, 4])) THEN s.stanumbers3 WHEN (s.stakind4 = ANY (ARRAY[1, 4])) THEN s.stanumbers4 ELSE NULL::real[] END AS most_common_freqs, CASE WHEN (s.stakind1 = 2) THEN s.stavalues1 WHEN (s.stakind2 = 2) THEN s.stavalues2 WHEN (s.stakind3 = 2) THEN s.stavalues3 WHEN (s.stakind4 = 2) THEN s.stavalues4 ELSE NULL::anyarray END AS histogram_bounds, CASE WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] ELSE NULL::real END AS correlation FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text));
pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char");
pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, pg_timezone_abbrevs.utc_offset, pg_timezone_abbrevs.is_dst FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst);
pg_timezone_names | SELECT pg_timezone_names.name, pg_timezone_names.abbrev, pg_timezone_names.utc_offset, pg_timezone_names.is_dst FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst);
--- 1293,1323 ----
pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline);
pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolreplication AS userepl, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin;
pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, s.xact_start, s.query_start, s.waiting, s.current_query FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_hostname, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
! pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind IN ('r'::"char", 't'::"char"));
! pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind IN ('r'::"char", 't'::"char")) GROUP BY c.oid, n.nspname, c.relname;
pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, pg_stat_get_db_conflict_all(d.oid) AS conflicts, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d;
pg_stat_database_conflicts | SELECT d.oid AS datid, d.datname, pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock FROM pg_database d;
pg_stat_replication | SELECT s.procpid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location, w.replay_location, w.sync_priority, w.sync_state FROM pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_hostname, client_port), pg_authid u, pg_stat_get_wal_senders() w(procpid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid = u.oid) AND (s.procpid = w.procpid));
! pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname IN ('pg_catalog'::name, 'information_schema'::name)) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text));
! pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count, pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname IN ('pg_catalog'::name, 'information_schema'::name)) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text));
pg_stat_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_function_calls(p.oid) AS calls, (pg_stat_get_function_time(p.oid) / 1000) AS total_time, (pg_stat_get_function_self_time(p.oid) / 1000) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL));
! pg_stat_user_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((NOT (pg_stat_all_indexes.schemaname IN ('pg_catalog'::name, 'information_schema'::name))) AND (pg_stat_all_indexes.schemaname !~ '^pg_toast'::text));
! pg_stat_user_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count, pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count FROM pg_stat_all_tables WHERE ((NOT (pg_stat_all_tables.schemaname IN ('pg_catalog'::name, 'information_schema'::name))) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
! pg_stat_xact_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_xact_numscans(c.oid) AS seq_scan, pg_stat_get_xact_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_xact_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_xact_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_xact_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind IN ('r'::"char", 't'::"char")) GROUP BY c.oid, n.nspname, c.relname;
! pg_stat_xact_sys_tables | SELECT pg_stat_xact_all_tables.relid, pg_stat_xact_all_tables.schemaname, pg_stat_xact_all_tables.relname, pg_stat_xact_all_tables.seq_scan, pg_stat_xact_all_tables.seq_tup_read, pg_stat_xact_all_tables.idx_scan, pg_stat_xact_all_tables.idx_tup_fetch, pg_stat_xact_all_tables.n_tup_ins, pg_stat_xact_all_tables.n_tup_upd, pg_stat_xact_all_tables.n_tup_del, pg_stat_xact_all_tables.n_tup_hot_upd FROM pg_stat_xact_all_tables WHERE ((pg_stat_xact_all_tables.schemaname IN ('pg_catalog'::name, 'information_schema'::name)) OR (pg_stat_xact_all_tables.schemaname ~ '^pg_toast'::text));
pg_stat_xact_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_xact_function_calls(p.oid) AS calls, (pg_stat_get_xact_function_time(p.oid) / 1000) AS total_time, (pg_stat_get_xact_function_self_time(p.oid) / 1000) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL));
! pg_stat_xact_user_tables | SELECT pg_stat_xact_all_tables.relid, pg_stat_xact_all_tables.schemaname, pg_stat_xact_all_tables.relname, pg_stat_xact_all_tables.seq_scan, pg_stat_xact_all_tables.seq_tup_read, pg_stat_xact_all_tables.idx_scan, pg_stat_xact_all_tables.idx_tup_fetch, pg_stat_xact_all_tables.n_tup_ins, pg_stat_xact_all_tables.n_tup_upd, pg_stat_xact_all_tables.n_tup_del, pg_stat_xact_all_tables.n_tup_hot_upd FROM pg_stat_xact_all_tables WHERE ((NOT (pg_stat_xact_all_tables.schemaname IN ('pg_catalog'::name, 'information_schema'::name))) AND (pg_stat_xact_all_tables.schemaname !~ '^pg_toast'::text));
! pg_statio_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind IN ('r'::"char", 't'::"char"));
pg_statio_all_sequences | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, pg_stat_get_blocks_hit(c.oid) AS blks_hit FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'S'::"char");
! pg_statio_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read, (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit, (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, (pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid)) AS tidx_blks_read, pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit FROM ((((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) LEFT JOIN pg_class x ON ((t.reltoastidxid = x.oid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind IN ('r'::"char", 't'::"char")) GROUP BY c.oid, n.nspname, c.relname, t.oid, x.oid;
! pg_statio_sys_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname IN ('pg_catalog'::name, 'information_schema'::name)) OR (pg_statio_all_indexes.schemaname ~ '^pg_toast'::text));
! pg_statio_sys_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname IN ('pg_catalog'::name, 'information_schema'::name)) OR (pg_statio_all_sequences.schemaname ~ '^pg_toast'::text));
! pg_statio_sys_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname IN ('pg_catalog'::name, 'information_schema'::name)) OR (pg_statio_all_tables.schemaname ~ '^pg_toast'::text));
! pg_statio_user_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((NOT (pg_statio_all_indexes.schemaname IN ('pg_catalog'::name, 'information_schema'::name))) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text));
! pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((NOT (pg_statio_all_sequences.schemaname IN ('pg_catalog'::name, 'information_schema'::name))) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text));
! pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((NOT (pg_statio_all_tables.schemaname IN ('pg_catalog'::name, 'information_schema'::name))) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text));
! pg_stats | SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stainherit AS inherited, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, CASE WHEN (s.stakind1 IN (1, 4)) THEN s.stavalues1 WHEN (s.stakind2 IN (1, 4)) THEN s.stavalues2 WHEN (s.stakind3 IN (1, 4)) THEN s.stavalues3 WHEN (s.stakind4 IN (1, 4)) THEN s.stavalues4 ELSE NULL::anyarray END AS most_common_vals, CASE WHEN (s.stakind1 IN (1, 4)) THEN s.stanumbers1 WHEN (s.stakind2 IN (1, 4)) THEN s.stanumbers2 WHEN (s.stakind3 IN (1, 4)) THEN s.stanumbers3 WHEN (s.stakind4 IN (1, 4)) THEN s.stanumbers4 ELSE NULL::real[] END AS most_common_freqs, CASE WHEN (s.stakind1 = 2) THEN s.stavalues1 WHEN (s.stakind2 = 2) THEN s.stavalues2 WHEN (s.stakind3 = 2) THEN s.stavalues3 WHEN (s.stakind4 = 2) THEN s.stavalues4 ELSE NULL::anyarray END AS histogram_bounds, CASE WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] ELSE NULL::real END AS correlation FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text));
pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char");
pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, pg_timezone_abbrevs.utc_offset, pg_timezone_abbrevs.is_dst FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst);
pg_timezone_names | SELECT pg_timezone_names.name, pg_timezone_names.abbrev, pg_timezone_names.utc_offset, pg_timezone_names.is_dst FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst);
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
***************
*** 79,85 **** test: select_into select_distinct select_distinct_on select_implicit select_havi
# ----------
# Another group of parallel tests
# ----------
! test: privileges security_label collate
test: misc
# rules cannot run concurrently with any test that creates a view
--- 79,85 ----
# ----------
# Another group of parallel tests
# ----------
! test: privileges security_label collate inclause
test: misc
# rules cannot run concurrently with any test that creates a view
*** a/src/test/regress/serial_schedule
--- b/src/test/regress/serial_schedule
***************
*** 90,95 **** test: prepared_xacts
--- 90,96 ----
test: privileges
test: security_label
test: collate
+ test: inclause
test: misc
test: rules
test: select_views
*** /dev/null
--- b/src/test/regress/sql/inclause.sql
***************
*** 0 ****
--- 1,65 ----
+ --
+ -- Test the IN clause
+ --
+
+ CREATE TABLE IN_TBL (
+ i integer,
+ j integer
+ );
+
+ INSERT INTO IN_TBL VALUES (1, 2);
+ INSERT INTO IN_TBL VALUES (2, 3);
+ INSERT INTO IN_TBL VALUES (3, 4);
+ INSERT INTO IN_TBL VALUES (4, NULL);
+
+ CREATE INDEX i_in_tbl ON IN_TBL (i);
+
+ -- Simple scalar IN, including untyped literals
+ SELECT 1 IN (1, 2);
+ SELECT 50 IN (1, 2, 3, 4);
+ SELECT 'foo' IN ('foo', 'bar');
+ SELECT 'notfound' IN ('foo', 'bar');
+ SELECT 'foo' IN ('foo');
+ SELECT 'foo' IN ('bar');
+ SELECT '123' IN ('bar', 123);
+ SELECT 'foo' IN ('bar', 123);
+
+ -- Simple row IN
+ SELECT (1, 2) IN ((1, 2), (2, 3));
+ SELECT (1, 3) IN ((1, 2), (2, 3));
+ SELECT (1, 3) IN ((1, 2));
+
+ -- Scalar and row IN using table
+ SELECT * FROM in_tbl WHERE i IN (0, 1);
+ SELECT * FROM in_tbl WHERE i IN (5, 6, 7, 8, 9, 10);
+ SELECT * FROM in_tbl WHERE (i, j) IN ((1, 2), (2, 4), (2, 3), (10, 11));
+ SELECT * FROM in_tbl WHERE (i, j) IN ((1, 3));
+
+ -- Scalar with NULLs
+ SELECT 1 IN (0, NULL, 2);
+ SELECT 1 IN (1, NULL, 2);
+ SELECT 3 IN (1, 2, NULL);
+ SELECT NULL IN (1, 2, NULL);
+
+ --
+ -- Check that the lefthand expression is only executed once. Before 9.1
+ -- we used to transform IN to a list of OR expressions, which lead to
+ -- multiple evaluation.
+ --
+ CREATE FUNCTION volatilefunc() RETURNS int4 AS $$
+ begin
+ RAISE NOTICE 'volatilefunc executed';
+ RETURN 123;
+ end;
+ $$ LANGUAGE plpgsql VOLATILE;
+
+ SELECT volatilefunc() IN (1, 2, 3, 123);
+
+ -- Subselect IN
+ SELECT 1 IN (SELECT i FROM in_tbl);
+
+ --
+ -- Clean up
+ --
+
+ DROP TABLE IN_TBL;
On Fri, Apr 1, 2011 at 14:24, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
We sometimes transform IN-clauses to a list of ORs:
postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
QUERY PLAN
Seq Scan on foo (cost=0.00..39.10 rows=19 width=12)
Filter: ((a = b) OR (a = c))But what if you replace "a" with a volatile function? It doesn't seem legal
to do that transformation in that case, but we do it:postgres=# explain SELECT * FROM foo WHERE (random()*2)::integer IN (b, c);
QUERY PLANSeq Scan on foo (cost=0.00..68.20 rows=19 width=12)
Filter: ((((random() * 2::double precision))::integer = b) OR (((random()
* 2::double precision))::integer = c))
Is there a similar problem with the BETWEEN clause transformation into
AND expressions?
marti=> explain verbose select random() between 0.25 and 0.75;
Result (cost=0.00..0.02 rows=1 width=0)
Output: ((random() >= 0.25::double precision) AND (random() <=
0.75::double precision))
As expected, I get a statistical skew of 0.4375 / 0.5625, whereas the
"correct" would be 50/50:
marti=> select random() between 0.25 and 0.75 as result, count(*) from
generate_series(1,1000000) i group by 1;
result | count
--------+--------
f | 437262
t | 562738
I also always noticed that BETWEEN with subqueries produces two
subplan nodes, this seems suboptimal.
marti=> explain verbose select (select random()) between 0.25 and 0.75;
Result (cost=0.03..0.04 rows=1 width=0)
Output: (($0 >= 0.25::double precision) AND ($1 <= 0.75::double precision))
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=0)
Output: random()
InitPlan 2 (returns $1)
-> Result (cost=0.00..0.01 rows=1 width=0)
Output: random()
Regards,
Marti
On 05.04.2011 13:19, Marti Raudsepp wrote:
On Fri, Apr 1, 2011 at 14:24, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:We sometimes transform IN-clauses to a list of ORs:
postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
QUERY PLAN
Seq Scan on foo (cost=0.00..39.10 rows=19 width=12)
Filter: ((a = b) OR (a = c))But what if you replace "a" with a volatile function? It doesn't seem legal
to do that transformation in that case, but we do it:postgres=# explain SELECT * FROM foo WHERE (random()*2)::integer IN (b, c);
QUERY PLANSeq Scan on foo (cost=0.00..68.20 rows=19 width=12)
Filter: ((((random() * 2::double precision))::integer = b) OR (((random()
* 2::double precision))::integer = c))Is there a similar problem with the BETWEEN clause transformation into
AND expressions?marti=> explain verbose select random() between 0.25 and 0.75;
Result (cost=0.00..0.02 rows=1 width=0)
Output: ((random()>= 0.25::double precision) AND (random()<=
0.75::double precision))
Yes, good point.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On 05.04.2011 18:42, Heikki Linnakangas wrote:
On 05.04.2011 13:19, Marti Raudsepp wrote:
On Fri, Apr 1, 2011 at 14:24, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:We sometimes transform IN-clauses to a list of ORs:
postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
QUERY PLAN
Seq Scan on foo (cost=0.00..39.10 rows=19 width=12)
Filter: ((a = b) OR (a = c))But what if you replace "a" with a volatile function? It doesn't seem
legal
to do that transformation in that case, but we do it:postgres=# explain SELECT * FROM foo WHERE (random()*2)::integer IN
(b, c);
QUERY PLANSeq Scan on foo (cost=0.00..68.20 rows=19 width=12)
Filter: ((((random() * 2::double precision))::integer = b) OR
(((random()
* 2::double precision))::integer = c))Is there a similar problem with the BETWEEN clause transformation into
AND expressions?marti=> explain verbose select random() between 0.25 and 0.75;
Result (cost=0.00..0.02 rows=1 width=0)
Output: ((random()>= 0.25::double precision) AND (random()<=
0.75::double precision))Yes, good point.
Hmm, the SQL specification explicitly says that
X BETWEEN Y AND Z
is equal to
X >= Y AND X <= Z
It doesn't say anything about side-effects of X. Seems like an oversight
in the specification. I would not expect X to be evaluated twice, and I
think we should change BETWEEN to not do that.
Does anyone object to making BETWEEN and IN more strict about the data
types? At the moment, you can do this:
postgres=# SELECT '1234' BETWEEN '10001'::text AND 10002::int4;
?column?
----------
t
(1 row)
I'm thinking that it should throw an error. Same with IN, if the values
in the IN-list can't be coerced to a common type. That will probably
simplify the code a lot, and is what the SQL standard assumes anyway AFAICS.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:
On 05.04.2011 18:42, Heikki Linnakangas wrote:
On 05.04.2011 13:19, Marti Raudsepp wrote:
On Fri, Apr 1, 2011 at 14:24, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:We sometimes transform IN-clauses to a list of ORs:
postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
QUERY PLAN
Seq Scan on foo (cost=0.00..39.10 rows=19 width=12)
Filter: ((a = b) OR (a = c))But what if you replace "a" with a volatile function? It
doesn't seem legal to do that transformation in that case, but
we do it:postgres=# explain SELECT * FROM foo WHERE
(random()*2)::integer IN (b, c);
QUERY PLANSeq Scan on foo (cost=0.00..68.20 rows=19 width=12)
Filter: ((((random() * 2::double precision))::integer = b) OR
(((random()
* 2::double precision))::integer = c))Is there a similar problem with the BETWEEN clause
transformation into AND expressions?marti=> explain verbose select random() between 0.25 and 0.75;
Result (cost=0.00..0.02 rows=1 width=0)
Output: ((random()>= 0.25::double precision) AND (random()<=
0.75::double precision))Yes, good point.
Hmm, the SQL specification explicitly says that
X BETWEEN Y AND Z
is equal to
X >= Y AND X <= Z
It doesn't say anything about side-effects of X. Seems like an
oversight in the specification. I would not expect X to be
evaluated twice, and I think we should change BETWEEN to not do
that.
Does the SQL spec explicitly say anything about how many times X
should be evaluated if you were to code it as?:
X >= Y AND X <= Z
If it does, evaluating it a different number of times for BETWEEN
would seem to be a deviation from standard. Evaluating it once seem
less surprising, but if we're going to deviate from the standard in
doing that, it at least deserves a clear note to that effect in the
docs.
Evaluating X once for BETWEEN seems better from a POLA perspective,
unless you happen to be massaging a query to another form and
trusting that the equivalence defined in the standard will always
hold.
Does anyone object to making BETWEEN and IN more strict about the
data types? At the moment, you can do this:postgres=# SELECT '1234' BETWEEN '10001'::text AND 10002::int4;
?column?
----------
t
(1 row)I'm thinking that it should throw an error. Same with IN, if the
values in the IN-list can't be coerced to a common type. That will
probably simplify the code a lot, and is what the SQL standard
assumes anyway AFAICS.
+1 for more strict.
-Kevin
On 11.04.2011 19:06, Kevin Grittner wrote:
Heikki Linnakangas<heikki.linnakangas@enterprisedb.com> wrote:
On 05.04.2011 18:42, Heikki Linnakangas wrote:
On 05.04.2011 13:19, Marti Raudsepp wrote:
On Fri, Apr 1, 2011 at 14:24, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:We sometimes transform IN-clauses to a list of ORs:
postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
QUERY PLAN
Seq Scan on foo (cost=0.00..39.10 rows=19 width=12)
Filter: ((a = b) OR (a = c))But what if you replace "a" with a volatile function? It
doesn't seem legal to do that transformation in that case, but
we do it:postgres=# explain SELECT * FROM foo WHERE
(random()*2)::integer IN (b, c);
QUERY PLANSeq Scan on foo (cost=0.00..68.20 rows=19 width=12)
Filter: ((((random() * 2::double precision))::integer = b) OR
(((random()
* 2::double precision))::integer = c))Is there a similar problem with the BETWEEN clause
transformation into AND expressions?marti=> explain verbose select random() between 0.25 and 0.75;
Result (cost=0.00..0.02 rows=1 width=0)
Output: ((random()>= 0.25::double precision) AND (random()<=
0.75::double precision))Yes, good point.
Hmm, the SQL specification explicitly says that
X BETWEEN Y AND Z
is equal to
X>= Y AND X<= Z
It doesn't say anything about side-effects of X. Seems like an
oversight in the specification. I would not expect X to be
evaluated twice, and I think we should change BETWEEN to not do
that.Does the SQL spec explicitly say anything about how many times X
should be evaluated if you were to code it as?:X>= Y AND X<= Z
Not explicitly. However, it does say that:
"
NOTE 258 � Since <between predicate> is an ordering operation, the
Conformance Rules of Subclause 9.12, �Ordering
operations�, also apply.
"
If I'm reading those ordering operation conformance rules correctly, it
only allows the operand to be a simple column or an expression that's
specified in the ORDER BY or similar, not an arbitrary expression. Which
seems quite restrictive, but it would dodge the whole issue..
The spec also has that:
�X BETWEEN SYMMETRIC Y AND Z� is equivalent to �((X BETWEEN ASYMMETRIC Y
AND Z)
OR (X BETWEEN ASYMMETRIC Z AND Y))�.
So if you take that into account too, X is evaluated four times. The SQL
standard can be funny sometimes, but I can't believe that they intended
that.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
Does anyone object to making BETWEEN and IN more strict about the data
types? At the moment, you can do this:
postgres=# SELECT '1234' BETWEEN '10001'::text AND 10002::int4;
?column?
----------
t
(1 row)
I'm thinking that it should throw an error. Same with IN, if the values
in the IN-list can't be coerced to a common type.
You *will* get push-back on that ... maybe from people with badly coded
applications, but I guarantee there will be complaints.
regards, tom lane
On 11.04.2011 19:33, Heikki Linnakangas wrote:
On 11.04.2011 19:06, Kevin Grittner wrote:
Heikki Linnakangas<heikki.linnakangas@enterprisedb.com> wrote:
Hmm, the SQL specification explicitly says that
X BETWEEN Y AND Z
is equal to
X>= Y AND X<= Z
It doesn't say anything about side-effects of X. Seems like an
oversight in the specification. I would not expect X to be
evaluated twice, and I think we should change BETWEEN to not do
that.Does the SQL spec explicitly say anything about how many times X
should be evaluated if you were to code it as?:X>= Y AND X<= Z
Not explicitly. However, it does say that:
"
NOTE 258 � Since <between predicate> is an ordering operation, the
Conformance Rules of Subclause 9.12, �Ordering
operations�, also apply.
"If I'm reading those ordering operation conformance rules correctly, it
only allows the operand to be a simple column or an expression that's
specified in the ORDER BY or similar, not an arbitrary expression. Which
seems quite restrictive, but it would dodge the whole issue..
Another data point on this: DB2 disallow volatile left-operand to BETWEEN
db2 => SELECT * FROM atable WHERE smallint(rand()*10) BETWEEN 4 AND 5
SQL0583N The use of routine or expression "SYSFUN.RAND" is invalid
because it
is not deterministic or has an external action. SQLSTATE=42845
I'd like us to still fix this so that there's no multiple evaluation -
that would actually make BETWEEN more useful than it is today. I'm
working on a patch to handle both BETWEEN and IN.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
I'd like us to still fix this so that there's no multiple evaluation -
that would actually make BETWEEN more useful than it is today. I'm
working on a patch to handle both BETWEEN and IN.
One other issue here is that the parser has traditionally handled
BETWEEN by multiply linking the same expression tree. I've wanted to
get rid of that behavior for a long time, but never got round to it.
It causes a lot of headaches for later processing, because you have to
be wary of multiply processing the same tree. If we switch BETWEEN
to something with a dedicated representation we could probably get rid
of all multiple-linking in the parser's output, allowing ensuing
simplifications downstream.
regards, tom lane
Uh, have we addressed this? I don't think so.
---------------------------------------------------------------------------
Heikki Linnakangas wrote:
On 02.04.2011 20:48, Tom Lane wrote:
Heikki Linnakangas<heikki.linnakangas@enterprisedb.com> writes:
We sometimes transform IN-clauses to a list of ORs:
postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
QUERY PLAN
------------------------------------------------------
Seq Scan on foo (cost=0.00..39.10 rows=19 width=12)
Filter: ((a = b) OR (a = c))
(2 rows)But what if you replace "a" with a volatile function? It doesn't seem
legal to do that transformation in that case, but we do it:This is the fault of transformAExprIn(). But please let's *not* fix
this by adding volatility to the set of heuristics used there. Looking
at this again, it seems to me that most of the problem with this code
is that we're trying to make optimization decisions in the parser.Agreed. The history of this is that before 8.2 all IN clauses were
transformed to OR clauses straight in the grammar. 8.2 added the code to
represent IN clause as a ScalarArrayOpExpr, but it was changed in 8.2.10
to use the OR-form again for Vars
(http://archives.postgresql.org/pgsql-hackers/2008-10/msg01269.php)I think what we ought to do is have the parser emit a full-fledged
InExpr node type (with semantics rather like CaseExpr) and then teach
the planner to optimize that to something else when it seems
safe/prudent to do so. One nontrivial advantage of that is that
rules/views containing IN constructs would start to reverse-parse
in the same fashion, instead of introducing weird substitute
expressions.Here's my first cut at that. The lefthand expression is now evaluated
only once, and stored in econtext->caseValue. Parse analysis turns the
righthand expressions into a list of comparison expressions like
"CaseTestExpr = value1". It's perhaps time that we rename CaseTestExpr
into something more generic, now that it's used not only in CASE
expressions, but also in IN and in UPDATE targets, but I didn't do that
in this patch.eval_const_expressions checks the lefthand expression for volatile
functions. If there aren't any, it transform the InExprs to a list of ORs.This isn't finished, because it doesn't yet do the transformation to
ScalarArrayOpExpr. The OR form is much slower to plan, which is why the
ScalarArrayOpExpr transformation was introduced in 8.2. I'll continue
hacking on that, but please let me know if you have a better idea on how
to handle that. One alternative is to teach the machinery that matches
restrictinfos to usable indexes to handle InExpr like it does
ScalarArrayOpExprs, but I don't know that code very well.--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
[ Attachment, skipping... ]
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Nope, this hasn't been addressed. FWIW, I put it on the todo list when I
stopped working on it.
On 06.09.2011 20:48, Bruce Momjian wrote:
Uh, have we addressed this? I don't think so.
---------------------------------------------------------------------------
Heikki Linnakangas wrote:
On 02.04.2011 20:48, Tom Lane wrote:
Heikki Linnakangas<heikki.linnakangas@enterprisedb.com> writes:
We sometimes transform IN-clauses to a list of ORs:
postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
QUERY PLAN
------------------------------------------------------
Seq Scan on foo (cost=0.00..39.10 rows=19 width=12)
Filter: ((a = b) OR (a = c))
(2 rows)But what if you replace "a" with a volatile function? It doesn't seem
legal to do that transformation in that case, but we do it:This is the fault of transformAExprIn(). But please let's *not* fix
this by adding volatility to the set of heuristics used there. Looking
at this again, it seems to me that most of the problem with this code
is that we're trying to make optimization decisions in the parser.Agreed. The history of this is that before 8.2 all IN clauses were
transformed to OR clauses straight in the grammar. 8.2 added the code to
represent IN clause as a ScalarArrayOpExpr, but it was changed in 8.2.10
to use the OR-form again for Vars
(http://archives.postgresql.org/pgsql-hackers/2008-10/msg01269.php)I think what we ought to do is have the parser emit a full-fledged
InExpr node type (with semantics rather like CaseExpr) and then teach
the planner to optimize that to something else when it seems
safe/prudent to do so. One nontrivial advantage of that is that
rules/views containing IN constructs would start to reverse-parse
in the same fashion, instead of introducing weird substitute
expressions.Here's my first cut at that. The lefthand expression is now evaluated
only once, and stored in econtext->caseValue. Parse analysis turns the
righthand expressions into a list of comparison expressions like
"CaseTestExpr = value1". It's perhaps time that we rename CaseTestExpr
into something more generic, now that it's used not only in CASE
expressions, but also in IN and in UPDATE targets, but I didn't do that
in this patch.eval_const_expressions checks the lefthand expression for volatile
functions. If there aren't any, it transform the InExprs to a list of ORs.This isn't finished, because it doesn't yet do the transformation to
ScalarArrayOpExpr. The OR form is much slower to plan, which is why the
ScalarArrayOpExpr transformation was introduced in 8.2. I'll continue
hacking on that, but please let me know if you have a better idea on how
to handle that. One alternative is to teach the machinery that matches
restrictinfos to usable indexes to handle InExpr like it does
ScalarArrayOpExprs, but I don't know that code very well.--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com[ Attachment, skipping... ]
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote:
Nope, this hasn't been addressed. FWIW, I put it on the todo list when I
stopped working on it.
Oh, I see it now. Thanks.
---------------------------------------------------------------------------
On 06.09.2011 20:48, Bruce Momjian wrote:
Uh, have we addressed this? I don't think so.
---------------------------------------------------------------------------
Heikki Linnakangas wrote:
On 02.04.2011 20:48, Tom Lane wrote:
Heikki Linnakangas<heikki.linnakangas@enterprisedb.com> writes:
We sometimes transform IN-clauses to a list of ORs:
postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
QUERY PLAN
------------------------------------------------------
Seq Scan on foo (cost=0.00..39.10 rows=19 width=12)
Filter: ((a = b) OR (a = c))
(2 rows)But what if you replace "a" with a volatile function? It doesn't seem
legal to do that transformation in that case, but we do it:This is the fault of transformAExprIn(). But please let's *not* fix
this by adding volatility to the set of heuristics used there. Looking
at this again, it seems to me that most of the problem with this code
is that we're trying to make optimization decisions in the parser.Agreed. The history of this is that before 8.2 all IN clauses were
transformed to OR clauses straight in the grammar. 8.2 added the code to
represent IN clause as a ScalarArrayOpExpr, but it was changed in 8.2.10
to use the OR-form again for Vars
(http://archives.postgresql.org/pgsql-hackers/2008-10/msg01269.php)I think what we ought to do is have the parser emit a full-fledged
InExpr node type (with semantics rather like CaseExpr) and then teach
the planner to optimize that to something else when it seems
safe/prudent to do so. One nontrivial advantage of that is that
rules/views containing IN constructs would start to reverse-parse
in the same fashion, instead of introducing weird substitute
expressions.Here's my first cut at that. The lefthand expression is now evaluated
only once, and stored in econtext->caseValue. Parse analysis turns the
righthand expressions into a list of comparison expressions like
"CaseTestExpr = value1". It's perhaps time that we rename CaseTestExpr
into something more generic, now that it's used not only in CASE
expressions, but also in IN and in UPDATE targets, but I didn't do that
in this patch.eval_const_expressions checks the lefthand expression for volatile
functions. If there aren't any, it transform the InExprs to a list of ORs.This isn't finished, because it doesn't yet do the transformation to
ScalarArrayOpExpr. The OR form is much slower to plan, which is why the
ScalarArrayOpExpr transformation was introduced in 8.2. I'll continue
hacking on that, but please let me know if you have a better idea on how
to handle that. One alternative is to teach the machinery that matches
restrictinfos to usable indexes to handle InExpr like it does
ScalarArrayOpExprs, but I don't know that code very well.--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com[ Attachment, skipping... ]
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes:
Uh, have we addressed this? I don't think so.
No. IIRC, I didn't like Heikki's proposed patch, so it's on my head
to come up with something better.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
Uh, have we addressed this? I don't think so.
No. IIRC, I didn't like Heikki's proposed patch, so it's on my head
to come up with something better.
You can blame me for getting it into the parser. It used to be in
gram.y!
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On 06.09.2011 20:53, Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian<bruce@momjian.us> writes:
Uh, have we addressed this? I don't think so.
No. IIRC, I didn't like Heikki's proposed patch, so it's on my head
to come up with something better.You can blame me for getting it into the parser. It used to be in
gram.y!
Huh? Isn't "the parser" and "gram.y" more or less the same thing?
Anyway, it needs to be somewhere else..
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote:
On 06.09.2011 20:53, Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian<bruce@momjian.us> writes:
Uh, have we addressed this? I don't think so.
No. IIRC, I didn't like Heikki's proposed patch, so it's on my head
to come up with something better.You can blame me for getting it into the parser. It used to be in
gram.y!Huh? Isn't "the parser" and "gram.y" more or less the same thing?
Anyway, it needs to be somewhere else..
I meant the '/parser' directory. It actually created AND nodes in gram.y
so the rest of the parser didn't even know a BETWEEN was used.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +