public inbox for [email protected]
help / color / mirror / Atom feed[PATCH] Remove self-joins.
18+ messages / 8 participants
[nested] [flat]
* [PATCH] Remove self-joins.
@ 2020-10-30 05:24 Andrey Lepikhov <[email protected]>
0 siblings, 0 replies; 18+ messages in thread
From: Andrey Lepikhov @ 2020-10-30 05:24 UTC (permalink / raw)
Remove inner joins of a relation to itself if can be proven that such
join can be replaced with a scan. We can build the required proofs of
uniqueness using the existing innerrel_is_unique machinery.
We can remove a self-join when for each outer row, if:
1. At most one inner row matches the join clauses.
2. If the join target list contains any inner vars then the inner row
is (physically) same row as the outer one.
In this patch we use Rowley's [1] approach to identify a self-join:
1. Collect all mergejoinable join quals looks like a.x = b.x
2. Collect all another join quals.
3. Check innerrel_is_unique() for the qual list from (1). If it
returns true, then outer row matches only the same row from the inner
relation. Proved, that this join is self-join and can be replaced by
a scan.
Some regression tests changed due to self-join removal logic.
[1] https://www.postgresql.org/message-id/raw/CAApHDvpggnFMC4yP-jUO7PKN%3DfXeErW5bOxisvJ0HvkHQEY%3DWw%40...
---
src/backend/optimizer/plan/analyzejoins.c | 1186 +++++++++++++++++++++
src/backend/optimizer/plan/planmain.c | 5 +
src/backend/optimizer/util/relnode.c | 26 +-
src/backend/utils/misc/guc.c | 10 +
src/include/optimizer/pathnode.h | 4 +
src/include/optimizer/planmain.h | 2 +
src/test/regress/expected/equivclass.out | 32 +
src/test/regress/expected/join.out | 331 ++++++
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/sql/equivclass.sql | 16 +
src/test/regress/sql/join.sql | 174 +++
11 files changed, 1773 insertions(+), 16 deletions(-)
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 806629fff2..0e92245116 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -22,6 +22,7 @@
*/
#include "postgres.h"
+#include "catalog/pg_class.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/joininfo.h"
@@ -29,8 +30,12 @@
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
#include "optimizer/planmain.h"
+#include "optimizer/restrictinfo.h"
#include "optimizer/tlist.h"
#include "utils/lsyscache.h"
+#include "utils/memutils.h"
+
+bool enable_self_join_removal;
/* local functions */
static bool join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo);
@@ -47,6 +52,7 @@ static bool is_innerrel_unique_for(PlannerInfo *root,
RelOptInfo *innerrel,
JoinType jointype,
List *restrictlist);
+static void change_rinfo(RestrictInfo* rinfo, Index from, Index to);
/*
@@ -1118,3 +1124,1183 @@ is_innerrel_unique_for(PlannerInfo *root,
/* Let rel_is_distinct_for() do the hard work */
return rel_is_distinct_for(root, innerrel, clause_list);
}
+
+typedef struct
+{
+ Index oldRelid;
+ Index newRelid;
+} ChangeVarnoContext;
+
+
+static bool
+change_varno_walker(Node *node, ChangeVarnoContext *context)
+{
+ if (node == NULL)
+ return false;
+
+ if (IsA(node, Var))
+ {
+ Var* var = (Var*)node;
+ if (var->varno == context->oldRelid)
+ {
+ var->varno = context->newRelid;
+ var->varnosyn = context->newRelid;
+ var->location = -1;
+ }
+ else if (var->varno == context->newRelid)
+ var->location = -1;
+
+ return false;
+ }
+ if (IsA(node, RestrictInfo))
+ {
+ change_rinfo((RestrictInfo*)node, context->oldRelid, context->newRelid);
+ return false;
+ }
+ return expression_tree_walker(node, change_varno_walker, context);
+}
+
+/*
+ * For all Vars in the expression that have varno = oldRelid, set
+ * varno = newRelid.
+ */
+static void
+change_varno(Expr *expr, Index oldRelid, Index newRelid)
+{
+ ChangeVarnoContext context;
+
+ context.oldRelid = oldRelid;
+ context.newRelid = newRelid;
+ change_varno_walker((Node *) expr, &context);
+}
+
+/*
+ * Substitute newId for oldId in relids.
+ */
+static void
+change_relid(Relids *relids, Index oldId, Index newId)
+{
+ if (bms_is_member(oldId, *relids))
+ *relids = bms_add_member(bms_del_member(bms_copy(*relids), oldId), newId);
+}
+
+static void
+change_rinfo(RestrictInfo* rinfo, Index from, Index to)
+{
+ bool is_req_equal =
+ (rinfo->required_relids == rinfo->clause_relids) ? true : false;
+
+ change_varno(rinfo->clause, from, to);
+ change_varno(rinfo->orclause, from, to);
+ change_relid(&rinfo->clause_relids, from, to);
+ if (is_req_equal)
+ rinfo->required_relids = rinfo->clause_relids;
+ else
+ change_relid(&rinfo->required_relids, from, to);
+ change_relid(&rinfo->left_relids, from, to);
+ change_relid(&rinfo->right_relids, from, to);
+ change_relid(&rinfo->outer_relids, from, to);
+ change_relid(&rinfo->nullable_relids, from, to);
+}
+
+/*
+ * Update EC members to point to the remaining relation instead of the removed
+ * one, removing duplicates.
+ */
+static void
+update_ec_members(EquivalenceClass *ec, Index toRemove, Index toKeep)
+{
+ int counter;
+
+ for (counter = 0; counter < list_length(ec->ec_members); )
+ {
+ ListCell *cell = list_nth_cell(ec->ec_members, counter);
+ EquivalenceMember *em = lfirst(cell);
+ int counter1;
+
+ if (!bms_is_member(toRemove, em->em_relids))
+ {
+ counter++;
+ continue;
+ }
+
+ change_relid(&em->em_relids, toRemove, toKeep);
+ /* We only process inner joins */
+ change_varno(em->em_expr, toRemove, toKeep);
+
+ /*
+ * After we switched the equivalence member to the remaining relation,
+ * check that it is not the same as the existing member, and if it
+ * is, delete it.
+ */
+ for (counter1 = 0; counter1 < list_length(ec->ec_members); counter1++)
+ {
+ EquivalenceMember *other;
+
+ if (counter1 == counter)
+ continue;
+
+ other = castNode(EquivalenceMember, list_nth(ec->ec_members, counter1));
+
+ if (equal(other->em_expr, em->em_expr))
+ break;
+ }
+
+ if (counter1 < list_length(ec->ec_members))
+ ec->ec_members = list_delete_cell(ec->ec_members, cell);
+ else
+ counter++;
+ }
+}
+
+/*
+ * Update EC sources to point to the remaining relation instead of the
+ * removed one.
+ */
+static void
+update_ec_sources(List **sources, Index toRemove, Index toKeep)
+{
+ int counter;
+ int cc=0;
+
+ for (counter = 0; counter < list_length(*sources); )
+ {
+ ListCell *cell = list_nth_cell(*sources, counter);
+ RestrictInfo *rinfo = castNode(RestrictInfo, lfirst(cell));
+ int counter1;
+
+ if (!bms_is_member(toRemove, rinfo->required_relids))
+ {
+ counter++;
+ continue;
+ }
+
+ change_varno(rinfo->clause, toRemove, toKeep);
+
+ /*
+ * After switching the clause to the remaining relation, check it for
+ * redundancy with existing ones. We don't have to check for
+ * redundancy with derived clauses, because we've just deleted them.
+ */
+ for (counter1 = 0; counter1 < list_length(*sources); counter1++)
+ {
+ RestrictInfo *other;
+
+ if (counter1 == counter)
+ continue;
+
+ other = castNode(RestrictInfo, list_nth(*sources, counter1));
+ if (equal(rinfo->clause, other->clause))
+ break;
+ }
+
+ if (counter1 < list_length(*sources))
+ {
+ *sources = list_delete_cell(*sources, cell);
+ cc++;
+ }
+ else
+ {
+ counter++;
+
+ /* We will keep this RestrictInfo, correct its relids. */
+ change_rinfo(rinfo, toRemove, toKeep);
+ }
+ }
+}
+
+/*
+ * Scratch space for the unique self join removal code.
+ */
+typedef struct
+{
+ PlannerInfo *root;
+
+ /* Temporary array for relation ids. */
+ Index *relids;
+
+ /* Array of row marks indexed by relid. */
+ PlanRowMark **row_marks;
+
+ /* Bitmapset for join relids that is used to avoid reallocation. */
+ Relids joinrelids;
+} UsjScratch;
+
+/*
+ * Remove a relation after we have proven that it participates only in an
+ * unneeded unique self join.
+ *
+ * The joinclauses list is destructively changed.
+ */
+static void
+remove_self_join_rel(UsjScratch *scratch, Relids joinrelids, List *joinclauses,
+ RelOptInfo *toKeep, RelOptInfo *toRemove)
+{
+ PlannerInfo *root = scratch->root;
+ ListCell *cell;
+ int i;
+ List *joininfos = NIL;
+
+ /*
+ * Include all eclass mentions of removed relation into the eclass mentions
+ * of kept relation.
+ */
+ toKeep->eclass_indexes = bms_add_members(toRemove->eclass_indexes,
+ toKeep->eclass_indexes);
+
+ /*
+ * Transfer join and restriction clauses from the removed relation to the
+ * remaining one. We change the Vars of the clause to point to the
+ * remaining relation instead of the removed one. The clauses that require
+ * a subset of joinrelids become restriction clauses of the remaining
+ * relation, and others remain join clauses. We append them to
+ * baserestrictinfo and joininfo respectively, trying not to introduce
+ * duplicates.
+ *
+ * We also have to process the 'joinclauses' list here, because it
+ * contains EC-derived join clauses which must become filter clauses. It
+ * is not enough to just correct the ECs, because the EC-derived
+ * restrictions are generated before join removal (see
+ * generate_base_implied_equalities).
+ */
+
+ /* Replace removed relation in joininfo */
+ foreach(cell, toKeep->joininfo)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, cell);
+
+ change_rinfo(rinfo, toRemove->relid, toKeep->relid);
+
+ /*
+ * If this clause is a mergejoinable equality clause that compares a
+ * variable to itself, i.e., has the form of "X=X", replace it with
+ * null test.
+ */
+ if (rinfo->mergeopfamilies && IsA(rinfo->clause, OpExpr))
+ {
+ Expr *leftOp;
+ Expr *rightOp;
+
+ leftOp = (Expr *) get_leftop(rinfo->clause);
+ rightOp = (Expr *) get_rightop(rinfo->clause);
+
+ if (leftOp != NULL && equal(leftOp, rightOp))
+ {
+ NullTest *nullTest = makeNode(NullTest);
+ nullTest->arg = leftOp;
+ nullTest->nulltesttype = IS_NOT_NULL;
+ nullTest->argisrow = false;
+ nullTest->location = -1;
+ rinfo->clause = (Expr *) nullTest;
+ }
+ }
+ }
+
+ /* Replace removed relation in joinclauses.
+ * It is similar with code above but also adds substituted null-tests to
+ * baserestrict list of kept relation.
+ * joinclauses contains all restrictions, include general expressions like
+ * f(a'.x1..a'.xN, a''.x1..a''.xN) op g(a'.x1..a'.xN, a''.x1..a''.xN)
+ * that couldn't fall into the EC.
+ */
+ foreach(cell, joinclauses)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, cell);
+
+ change_rinfo(rinfo, toRemove->relid, toKeep->relid);
+
+ /*
+ * If this clause is a mergejoinable equality clause that compares a
+ * variable to itself, i.e., has the form of "X=X", replace it with
+ * null test.
+ */
+ if (rinfo->mergeopfamilies && IsA(rinfo->clause, OpExpr))
+ {
+ Expr *leftOp;
+ Expr *rightOp;
+ ListCell *otherCell;
+ List **target = &toKeep->baserestrictinfo;
+
+ leftOp = (Expr *) get_leftop(rinfo->clause);
+ rightOp = (Expr *) get_rightop(rinfo->clause);
+
+ if (leftOp != NULL && equal(leftOp, rightOp))
+ {
+ NullTest *nullTest = makeNode(NullTest);
+ nullTest->arg = leftOp;
+ nullTest->nulltesttype = IS_NOT_NULL;
+ nullTest->argisrow = false;
+ nullTest->location = -1;
+ rinfo->clause = (Expr *) nullTest;
+ }
+
+ foreach(otherCell, *target)
+ {
+ RestrictInfo *other = lfirst_node(RestrictInfo, otherCell);
+
+ if (other == rinfo ||
+ (rinfo->parent_ec != NULL
+ && other->parent_ec == rinfo->parent_ec)
+ || equal(rinfo->clause, other->clause))
+ {
+ break;
+ }
+ }
+
+ if (otherCell != NULL)
+ continue;
+
+ /*
+ * XXX: In the case of clauses:
+ * toKeep.x = toRemove.y and toKeep.y = toRemove.x
+ * we pass into baserestrictinfo both as:
+ * toKeep.x = toKeep.y and toKeep.y = toKeep.x
+ * that is may be not fine, but correct.
+ */
+ toKeep->baserestrictinfo = lappend(toKeep->baserestrictinfo, rinfo);
+ }
+ }
+
+ /* Tranfer removed relation baserestrictinfo clauses to kept relation */
+ foreach(cell, toRemove->baserestrictinfo)
+ {
+ ListCell *otherCell;
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, cell);
+ List **target = &toKeep->baserestrictinfo;
+
+ /*
+ * Replace the references to the removed relation with references to
+ * the remaining one. We won't necessarily add this clause, because
+ * it may be already present in the joininfo or baserestrictinfo.
+ * Still, we have to switch it to point to the remaining relation.
+ * This is important for join clauses that reference both relations,
+ * because they are included in both joininfos.
+ */
+ change_rinfo(rinfo, toRemove->relid, toKeep->relid);
+
+ /*
+ * Don't add the clause if it is already present in the list, or
+ * derived from the same equivalence class, or is the same as another
+ * clause.
+ */
+ foreach (otherCell, *target)
+ {
+ RestrictInfo *other = lfirst_node(RestrictInfo, otherCell);
+
+ if (other == rinfo ||
+ (rinfo->parent_ec != NULL
+ && other->parent_ec == rinfo->parent_ec)
+ || equal(rinfo->clause, other->clause))
+ {
+ break;
+ }
+ }
+
+ /* We can't have an EC-derived clause that joins to some third relation */
+ if (otherCell != NULL)
+ continue;
+
+ /*
+ * During replacing relids in joininfo some restrictions could change
+ * semantic from join to baserestrict info, for example:
+ * a1.x = a2.y => a1.x = a1.y
+ * If we already have restriction in the baserestrictinfo list:
+ * a1.x = a1.y, here we will remove duplicates.
+ */
+ target = &toKeep->joininfo;
+ foreach (otherCell, *target)
+ {
+ RestrictInfo *other = lfirst_node(RestrictInfo, otherCell);
+
+ if (equal(rinfo->clause, other->clause))
+ break;
+ }
+
+ if (otherCell != NULL)
+ /* Duplicate found */
+ continue;
+
+ *target = lappend(*target, rinfo);
+ }
+
+ /* Transfer removed relation join-clauses to kept relation.
+ * It is similar with code above but also substituted redundant quals with
+ * null-tests.
+ */
+ foreach(cell, toRemove->joininfo)
+ {
+ ListCell *otherCell;
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, cell);
+ List **target = &toKeep->joininfo;
+
+ /*
+ * Replace the references to the removed relation with references to
+ * the remaining one. We won't necessarily add this clause, because
+ * it may be already present in the joininfo or baserestrictinfo.
+ * Still, we have to switch it to point to the remaining relation.
+ * This is important for join clauses that reference both relations,
+ * because they are included in both joininfos.
+ */
+ change_rinfo(rinfo, toRemove->relid, toKeep->relid);
+
+ /*
+ * Don't add the clause if it is already present in the list, or
+ * derived from the same equivalence class, or is the same as another
+ * clause.
+ */
+ foreach (otherCell, *target)
+ {
+ RestrictInfo *other = lfirst_node(RestrictInfo, otherCell);
+
+ if (other == rinfo ||
+ (rinfo->parent_ec != NULL
+ && other->parent_ec == rinfo->parent_ec)
+ || equal(rinfo->clause, other->clause))
+ {
+ break;
+ }
+ }
+
+ if (otherCell != NULL)
+ continue;
+
+ /*
+ * If this clause is a mergejoinable equality clause that compares a
+ * variable to itself, i.e., has the form of "X=X", replace it with
+ * null test.
+ */
+ if (rinfo->mergeopfamilies && IsA(rinfo->clause, OpExpr))
+ {
+ Expr *leftOp, *rightOp;
+
+ Assert(IsA(rinfo->clause, OpExpr));
+
+ leftOp = (Expr *) get_leftop(rinfo->clause);
+ rightOp = (Expr *) get_rightop(rinfo->clause);
+
+ if (leftOp != NULL && equal(leftOp, rightOp))
+ {
+ NullTest *nullTest = makeNode(NullTest);
+ nullTest->arg = leftOp;
+ nullTest->nulltesttype = IS_NOT_NULL;
+ nullTest->argisrow = false;
+ nullTest->location = -1;
+ rinfo->clause = (Expr*)nullTest;
+ }
+ }
+
+ *target = lappend(*target, rinfo);
+ toKeep->baserestrict_min_security = Min(toKeep->baserestrict_min_security, rinfo->security_level);
+ }
+
+ /*
+ * Now the state of the joininfo list of the toKeep relation is changed:
+ * some of clauses can be removed, some need to move into baserestrictinfo.
+ * To do this use the same technique as the remove_rel_from_query() routine.
+ */
+ joininfos = list_copy(toKeep->joininfo);
+ foreach(cell, joininfos)
+ {
+ RestrictInfo *rinfo = (RestrictInfo *) lfirst(cell);
+
+ remove_join_clause_from_rels(root, rinfo, rinfo->required_relids);
+
+ if (RINFO_IS_PUSHED_DOWN(rinfo, joinrelids))
+ {
+ /* Recheck that qual doesn't actually reference the target rel */
+ Assert(!bms_is_member(toRemove->relid, rinfo->clause_relids));
+
+ /*
+ * The required_relids probably aren't shared with anything else,
+ * but let's copy them just to be sure.
+ */
+ rinfo->required_relids = bms_copy(rinfo->required_relids);
+ rinfo->required_relids = bms_del_member(rinfo->required_relids,
+ toRemove->relid);
+ distribute_restrictinfo_to_rels(root, rinfo);
+ }
+ }
+
+ /*
+ * Transfer the targetlist and attr_needed flags.
+ */
+ Assert(toRemove->reltarget->sortgrouprefs == 0);
+
+ foreach (cell, toRemove->reltarget->exprs)
+ {
+ Expr *node = lfirst(cell);
+ change_varno(node, toRemove->relid, toKeep->relid);
+ if (!list_member(toKeep->reltarget->exprs, node))
+ toKeep->reltarget->exprs = lappend(toKeep->reltarget->exprs, node);
+ }
+
+ for (i = toKeep->min_attr; i <= toKeep->max_attr; i++)
+ {
+ int attno = i - toKeep->min_attr;
+ toKeep->attr_needed[attno] = bms_add_members(toKeep->attr_needed[attno],
+ toRemove->attr_needed[attno]);
+ }
+
+ /*
+ * If the removed relation has a row mark, transfer it to the remaining
+ * one.
+ *
+ * If both rels have row marks, just keep the one corresponding to the
+ * remaining relation, because we verified earlier that they have the same
+ * strength.
+ *
+ * Also make sure that the scratch->row_marks cache is up to date, because
+ * we are going to use it for further join removals.
+ */
+ if (scratch->row_marks[toRemove->relid])
+ {
+ PlanRowMark **markToRemove = &scratch->row_marks[toRemove->relid];
+ PlanRowMark **markToKeep = &scratch->row_marks[toKeep->relid];
+
+ if (*markToKeep)
+ {
+ Assert((*markToKeep)->markType == (*markToRemove)->markType);
+
+ root->rowMarks = list_delete_ptr(root->rowMarks, *markToKeep);
+ *markToKeep = NULL;
+ }
+ else
+ {
+ *markToKeep = *markToRemove;
+ *markToRemove = NULL;
+
+ /* Shouldn't have inheritance children here. */
+ Assert((*markToKeep)->rti == (*markToKeep)->prti);
+
+ (*markToKeep)->rti = toKeep->relid;
+ (*markToKeep)->prti = toKeep->relid;
+ }
+ }
+
+ /*
+ * Likewise replace references in SpecialJoinInfo data structures.
+ *
+ * This is relevant in case the join we're deleting is nested inside some
+ * special joins: the upper joins' relid sets have to be adjusted.
+ */
+ foreach (cell, root->join_info_list)
+ {
+ SpecialJoinInfo *sjinfo = (SpecialJoinInfo *) lfirst(cell);
+
+ change_relid(&sjinfo->min_lefthand, toRemove->relid, toKeep->relid);
+ change_relid(&sjinfo->min_righthand, toRemove->relid, toKeep->relid);
+ change_relid(&sjinfo->syn_lefthand, toRemove->relid, toKeep->relid);
+ change_relid(&sjinfo->syn_righthand, toRemove->relid, toKeep->relid);
+ change_varno((Expr*)sjinfo->semi_rhs_exprs, toRemove->relid, toKeep->relid);
+ }
+
+ /*
+ * Likewise update references in PlaceHolderVar data structures.
+ */
+ foreach(cell, root->placeholder_list)
+ {
+ PlaceHolderInfo *phinfo = (PlaceHolderInfo *) lfirst(cell);
+
+ /*
+ * We are at an inner join of two base relations. A placeholder can't
+ * be needed here or evaluated here.
+ */
+ Assert(!bms_is_subset(phinfo->ph_eval_at, joinrelids));
+ Assert(!bms_is_subset(phinfo->ph_needed, joinrelids));
+
+ change_relid(&phinfo->ph_eval_at, toRemove->relid, toKeep->relid);
+ change_relid(&phinfo->ph_needed, toRemove->relid, toKeep->relid);
+ change_relid(&phinfo->ph_lateral, toRemove->relid, toKeep->relid);
+ change_relid(&phinfo->ph_var->phrels, toRemove->relid, toKeep->relid);
+ }
+
+ /*
+ * Update the equivalence classes that reference the removed relations.
+ */
+ foreach(cell, root->eq_classes)
+ {
+ EquivalenceClass *ec = lfirst(cell);
+
+ if (!bms_is_member(toRemove->relid, ec->ec_relids))
+ {
+ /*
+ * This EC doesn't reference the removed relation, nothing to be
+ * done for it.
+ */
+ continue;
+ }
+
+ /*
+ * Update the EC members to reference the remaining relation instead
+ * of the removed one.
+ */
+ update_ec_members(ec, toRemove->relid, toKeep->relid);
+ change_relid(&ec->ec_relids, toRemove->relid, toKeep->relid);
+
+ /*
+ * We will now update source and derived clauses of the EC.
+ *
+ * Restriction clauses for base relations are already distributed to
+ * the respective baserestrictinfo lists (see
+ * generate_implied_equalities). The above code has already processed
+ * this list, and updated these clauses to reference the remaining
+ * relation, so we can skip them here based on their relids.
+ *
+ * Likewise, we have already processed the join clauses that join the
+ * removed relation to the remaining one.
+ *
+ * Finally, there are join clauses that join the removed relation to
+ * some third relation. We can't just delete the source clauses and
+ * regenerate them from the EC, because the corresponding equality
+ * operators might be missing (see the handling of ec_broken).
+ * Therefore, we will update the references in the source clauses.
+ *
+ * Derived clauses can be generated again, so it is simpler to just
+ * delete them.
+ */
+ list_free(ec->ec_derives);
+ ec->ec_derives = NULL;
+ update_ec_sources(&ec->ec_sources, toRemove->relid, toKeep->relid);
+ }
+
+ /*
+ * Mark the rel as "dead" to show it is no longer part of the join tree.
+ * (Removing it from the baserel array altogether seems too risky.)
+ */
+ toRemove->reloptkind = RELOPT_DEADREL;
+
+ /*
+ * Update references to the removed relation from other baserels.
+ */
+ for (i = 1; i < root->simple_rel_array_size; i++)
+ {
+ RelOptInfo *otherrel = root->simple_rel_array[i];
+ int attroff;
+
+ /* no point in processing target rel itself */
+ if (i == toRemove->relid)
+ continue;
+
+ /* there may be empty slots corresponding to non-baserel RTEs */
+ if (otherrel == NULL)
+ continue;
+
+ Assert(otherrel->relid == i); /* sanity check on array */
+
+ /* Update attr_needed arrays. */
+ for (attroff = otherrel->max_attr - otherrel->min_attr;
+ attroff >= 0; attroff--)
+ {
+ change_relid(&otherrel->attr_needed[attroff], toRemove->relid,
+ toKeep->relid);
+ }
+
+ /* Update lateral references. */
+ change_varno((Expr*)otherrel->lateral_vars, toRemove->relid, toKeep->relid);
+ }
+
+ /*
+ * Change varno in some special cases with non-trivial RangeTblEntry
+ */
+ foreach(cell, root->parse->rtable)
+ {
+ RangeTblEntry *rte = lfirst(cell);
+
+ switch(rte->rtekind)
+ {
+ case RTE_FUNCTION:
+ change_varno((Expr*)rte->functions, toRemove->relid, toKeep->relid);
+ break;
+ case RTE_TABLEFUNC:
+ change_varno((Expr*)rte->tablefunc, toRemove->relid, toKeep->relid);
+ break;
+ case RTE_VALUES:
+ change_varno((Expr*)rte->values_lists, toRemove->relid, toKeep->relid);
+ break;
+ default:
+ /* no op */
+ break;
+ }
+ }
+}
+
+/*
+ * split_selfjoin_quals
+ * Processes 'joinquals' building two lists, one with a list of quals
+ * where the columns/exprs on either side of the join match and another
+ * list containing the remaining quals.
+ *
+ * 'joinquals' must only contain quals for a RTE_RELATION being joined to
+ * itself.
+ */
+static void
+split_selfjoin_quals(PlannerInfo *root, List *joinquals, List **selfjoinquals,
+ List **otherjoinquals)
+{
+ ListCell *lc;
+ List *sjoinquals = NIL;
+ List *rjoinquals = NIL;
+
+ foreach(lc, joinquals)
+ {
+ RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
+ OpExpr *expr;
+ Expr *leftexpr;
+ Expr *rightexpr;
+
+ if (bms_num_members(rinfo->clause_relids) != 2 ||
+ bms_num_members(rinfo->left_relids) != 1 ||
+ bms_num_members(rinfo->right_relids) != 1)
+ {
+ rjoinquals = lappend(rjoinquals, rinfo);
+ continue;
+ }
+
+ expr = (OpExpr *) rinfo->clause;
+
+ if (!IsA(expr, OpExpr) || list_length(expr->args) != 2)
+ {
+ rjoinquals = lappend(rjoinquals, rinfo);
+ continue;
+ }
+
+ leftexpr = (Expr *) copyObject(get_leftop(rinfo->clause));
+ rightexpr = (Expr *) copyObject(get_rightop(rinfo->clause));
+
+ /* Can't match of the exprs are not of the same type */
+ if (leftexpr->type != rightexpr->type)
+ {
+ rjoinquals = lappend(rjoinquals, rinfo);
+ continue;
+ }
+
+ change_varno(rightexpr,
+ bms_next_member(rinfo->right_relids, -1),
+ bms_next_member(rinfo->left_relids, -1));
+
+ if (equal(leftexpr, rightexpr))
+ sjoinquals = lappend(sjoinquals, rinfo);
+ else
+ rjoinquals = lappend(rjoinquals, rinfo);
+ }
+
+ *selfjoinquals = sjoinquals;
+ *otherjoinquals = rjoinquals;
+}
+
+static bool
+tlist_contains_rel_exprs(PlannerInfo *root, Relids relids, RelOptInfo *rel)
+{
+ ListCell *vars;
+
+ foreach(vars, rel->reltarget->exprs)
+ {
+ Var *var = (Var *) lfirst(vars);
+ RelOptInfo *baserel;
+ int ndx;
+
+ /*
+ * Ignore PlaceHolderVars in the input tlists; we'll make our own
+ * decisions about whether to copy them.
+ */
+ if (IsA(var, PlaceHolderVar))
+ return true;
+
+ /*
+ * Otherwise, anything in a baserel or joinrel targetlist ought to be
+ * a Var. (More general cases can only appear in appendrel child
+ * rels, which will never be seen here.)
+ */
+ if (!IsA(var, Var))
+ elog(ERROR, "unexpected node type in rel targetlist: %d",
+ (int) nodeTag(var));
+
+ /* Get the Var's original base rel */
+ baserel = find_base_rel(root, var->varno);
+
+ /* Is it still needed above this joinrel? */
+ ndx = var->varattno - baserel->min_attr;
+ if (bms_nonempty_difference(baserel->attr_needed[ndx], relids))
+ return true;
+ }
+ return false;
+}
+
+/*
+ * Find and remove unique self joins in a group of base relations that have
+ * the same Oid.
+ *
+ * Returns a set of relids that were removed.
+ */
+static Relids
+remove_self_joins_one_group(UsjScratch *scratch, Index *relids, int n)
+{
+ PlannerInfo *root = scratch->root;
+ Relids joinrelids = scratch->joinrelids;
+ Relids result = NULL;
+ int i, o;
+
+ if (n < 2)
+ return NULL;
+
+ for (o = 0; o < n; o++)
+ {
+ RelOptInfo *outer = root->simple_rel_array[relids[o]];
+
+ for (i = o + 1; i < n; i++)
+ {
+ RelOptInfo *inner = root->simple_rel_array[relids[i]];
+ List *restrictlist;
+ List *selfjoinquals;
+ List *otherjoinquals;
+ ListCell *lc;
+ bool jinfo_check = true;
+
+ /* A sanity check: the relations have the same Oid. */
+ Assert(root->simple_rte_array[relids[i]]->relid
+ == root->simple_rte_array[relids[o]]->relid);
+
+ /*
+ * It is impossible to optimize two relations if they belong to
+ * different rules of order restriction. Otherwise planner can't
+ * be able to find any variants of correct query plan.
+ */
+ foreach(lc, root->join_info_list)
+ {
+ SpecialJoinInfo *info = (SpecialJoinInfo *) lfirst(lc);
+
+ if (bms_is_member(relids[i], info->syn_lefthand) &&
+ !bms_is_member(relids[o], info->syn_lefthand))
+ jinfo_check = false;
+
+ if (bms_is_member(relids[i], info->syn_righthand) &&
+ !bms_is_member(relids[o], info->syn_righthand))
+ jinfo_check = false;
+
+ if (bms_is_member(relids[o], info->syn_lefthand) &&
+ !bms_is_member(relids[i], info->syn_lefthand))
+ jinfo_check = false;
+
+ if (bms_is_member(relids[o], info->syn_righthand) &&
+ !bms_is_member(relids[i], info->syn_righthand))
+ jinfo_check = false;
+
+ if (!jinfo_check)
+ break;
+ }
+
+ if (!jinfo_check)
+ continue;
+
+ /* Reuse joinrelids bitset to avoid reallocation. */
+ joinrelids = bms_del_members(joinrelids, joinrelids);
+
+ /*
+ * We only deal with base rels here, so their relids bitset
+ * contains only one member -- their relid.
+ */
+ joinrelids = bms_add_member(joinrelids, relids[o]);
+ joinrelids = bms_add_member(joinrelids, relids[i]);
+
+ /* Is it a unique self join? */
+ restrictlist = build_joinrel_restrictlist(root, joinrelids, outer,
+ inner);
+
+ /*
+ * Process restrictlist to seperate out the self join quals from
+ * the other quals. e.g x = x goes to selfjoinquals and a = b to
+ * otherjoinquals.
+ */
+ split_selfjoin_quals(root, restrictlist, &selfjoinquals,
+ &otherjoinquals);
+
+ if (list_length(selfjoinquals) == 0)
+ {
+ /*
+ * Have a chance to remove join if target list contains vars from
+ * the only one relation.
+ */
+ if (list_length(otherjoinquals) == 0)
+ {
+ /* Can't determine uniqueness without any quals. */
+ continue;
+
+ }
+ else if (!tlist_contains_rel_exprs(root, joinrelids, inner))
+ {
+ /*
+ * TODO:
+ * In this case, we only have a chance in the case of a
+ * foreign key reference.
+ */
+ continue;
+ }
+ else
+ /*
+ * The target list contains vars from both inner and outer
+ * relations.
+ */
+ continue;
+ }
+ /*
+ * Determine if the inner table can duplicate outer rows. We must
+ * bypass the unique rel cache here since we're possibly using a
+ * subset of join quals. We can use 'force_cache' = true when all
+ * join quals are selfjoin quals. Otherwise we could end up
+ * putting false negatives in the cache.
+ */
+ else if (!innerrel_is_unique(root, joinrelids, outer->relids,
+ inner, JOIN_INNER, selfjoinquals,
+ list_length(otherjoinquals) == 0))
+ continue;
+
+ /*
+ * We can't remove the join if the relations have row marks of
+ * different strength (e.g. one is locked FOR UPDATE and another
+ * just has ROW_MARK_REFERENCE for EvalPlanQual rechecking).
+ */
+ if (scratch->row_marks[relids[i]] && scratch->row_marks[relids[o]]
+ && scratch->row_marks[relids[i]]->markType
+ != scratch->row_marks[relids[o]]->markType)
+ {
+ continue;
+ }
+
+ /*
+ * Be safe to do not remove table participated in complicated PH
+ */
+ foreach(lc, root->placeholder_list)
+ {
+ PlaceHolderInfo *phinfo = (PlaceHolderInfo *) lfirst(lc);
+
+ /* there isn't any other place to eval PHV */
+ if (bms_is_subset(phinfo->ph_eval_at, joinrelids) ||
+ bms_is_subset(phinfo->ph_needed, joinrelids))
+ break;
+ }
+
+ if (lc)
+ continue;
+
+ /*
+ * We can remove either relation, so remove the outer one, to
+ * simplify this loop.
+ */
+ remove_self_join_rel(scratch, joinrelids, restrictlist, inner, outer);
+ result = bms_add_member(result, relids[o]);
+
+ /*
+ * Replace varno in root targetlist and HAVING clause.
+ */
+ change_varno((Expr *) root->processed_tlist, relids[o], relids[i]);
+ change_varno((Expr *) root->parse->havingQual, relids[o], relids[i]);
+
+ /* We removed the outer relation, try the next one. */
+ break;
+ }
+ }
+
+ scratch->joinrelids = joinrelids;
+ return result;
+}
+
+/*
+ * A qsort comparator to sort the relids by the relation Oid.
+ */
+static int
+compare_rte(const Index *left, const Index *right, PlannerInfo *root)
+{
+ Oid l = root->simple_rte_array[*left]->relid;
+ Oid r = root->simple_rte_array[*right]->relid;
+
+ return l < r ? 1 : (l == r ? 0 : -1);
+}
+
+/*
+ * Find and remove unique self joins on a particular level of the join tree.
+ *
+ * We sort the relations by Oid and then examine each group with the same Oid.
+ * If we removed any relation, remove it from joinlist as well.
+ */
+static Relids
+remove_self_joins_one_level(UsjScratch *scratch, List *joinlist,
+ Relids relidsToRemove)
+{
+ ListCell *lc;
+ Oid groupOid;
+ int groupStart;
+ int i;
+ int n = 0;
+ Index *relid_ascending = scratch->relids;
+ PlannerInfo *root = scratch->root;
+
+ /*
+ * Collect the ids of base relations at this level of the join tree.
+ */
+ foreach (lc, joinlist)
+ {
+ RangeTblEntry *rte;
+ RelOptInfo *rel;
+ RangeTblRef *ref = (RangeTblRef *) lfirst(lc);
+ if (!IsA(ref, RangeTblRef))
+ continue;
+
+ rte = root->simple_rte_array[ref->rtindex];
+ rel = root->simple_rel_array[ref->rtindex];
+
+ /* We only care about base relations from which we select something. */
+ if (rte->rtekind != RTE_RELATION || rte->relkind != RELKIND_RELATION
+ || rel == NULL)
+ {
+ continue;
+ }
+
+ relid_ascending[n++] = ref->rtindex;
+
+ /*
+ * Limit the number of joins we process to control the quadratic
+ * behavior.
+ */
+ if (n > join_collapse_limit)
+ break;
+ }
+
+ if (n < 2)
+ return relidsToRemove;
+
+ /*
+ * Find and process the groups of relations that have same Oid.
+ */
+ qsort_arg(relid_ascending, n, sizeof(*relid_ascending),
+ (qsort_arg_comparator) compare_rte, root);
+ groupOid = root->simple_rte_array[relid_ascending[0]]->relid;
+ groupStart = 0;
+ for (i = 1; i < n; i++)
+ {
+ RangeTblEntry *rte = root->simple_rte_array[relid_ascending[i]];
+ Assert(rte->relid != InvalidOid);
+ if (rte->relid != groupOid)
+ {
+ relidsToRemove = bms_add_members(relidsToRemove,
+ remove_self_joins_one_group(scratch, &relid_ascending[groupStart],
+ i - groupStart));
+ groupOid = rte->relid;
+ groupStart = i;
+ }
+ }
+ Assert(groupOid != InvalidOid);
+ Assert(groupStart < n);
+ relidsToRemove = bms_add_members(relidsToRemove,
+ remove_self_joins_one_group(scratch, &relid_ascending[groupStart],
+ n - groupStart));
+
+ return relidsToRemove;
+}
+
+/*
+ * Find and remove unique self joins on a single level of a join tree, and
+ * recurse to handle deeper levels.
+ */
+static Relids
+remove_self_joins_recurse(UsjScratch *scratch, List *joinlist,
+ Relids relidsToRemove)
+{
+ ListCell *lc;
+
+ foreach (lc, joinlist)
+ {
+ switch (((Node *) lfirst(lc))->type)
+ {
+ case T_List:
+ relidsToRemove = remove_self_joins_recurse(
+ scratch,
+ (List *) lfirst(lc),
+ relidsToRemove);
+ break;
+ case T_RangeTblRef:
+ break;
+ default:
+ Assert(false);
+ }
+ }
+ return remove_self_joins_one_level(scratch, joinlist, relidsToRemove);
+}
+
+/*
+ * Find and remove useless self joins.
+ *
+ * We search for joins where the same relation is joined to itself on all
+ * columns of some unique index. If this condition holds, then, for
+ * each outer row, only one inner row matches, and it is the same row
+ * of the same relation. This allows us to remove the join and replace
+ * it with a scan that combines WHERE clauses from both sides. The join
+ * clauses themselves assume the form of X = X and can be replaced with
+ * NOT NULL clauses.
+ *
+ * For the sake of simplicity, we don't apply this optimization to special
+ * joins. Here is a list of what we could do in some particular cases:
+ * 'a a1 semi join a a2': is reduced to inner by reduce_unique_semijoins,
+ * and then removed normally.
+ * 'a a1 anti join a a2': could simplify to a scan with 'outer quals AND
+ * (IS NULL on join columns OR NOT inner quals)'.
+ * 'a a1 left join a a2': could simplify to a scan like inner, but without
+ * NOT NULL conditions on join columns.
+ * 'a a1 left join (a a2 join b)': can't simplify this, because join to b
+ * can both remove rows and introduce duplicates.
+ *
+ * To search for removable joins, we order all the relations on their Oid,
+ * go over each set with the same Oid, and consider each pair of relations
+ * in this set. We check that both relation are made unique by the same
+ * unique index with the same clauses.
+ *
+ * To remove the join, we mark one of the participating relation as
+ * dead, and rewrite all references to it to point to the remaining
+ * relation. This includes modifying RestrictInfos, EquivalenceClasses and
+ * EquivalenceMembers. We also have to modify the row marks. The join clauses
+ * of the removed relation become either restriction or join clauses, based on
+ * whether they reference any relations not participating in the removed join.
+ *
+ * 'targetlist' is the top-level targetlist of query. If it has any references
+ * to the removed relations, we update them to point to the remaining ones.
+ */
+List *
+remove_useless_self_joins(PlannerInfo *root, List *joinlist)
+{
+ ListCell *lc;
+ UsjScratch scratch;
+ Relids relidsToRemove = NULL;
+ int relid = -1;
+
+ if (!enable_self_join_removal)
+ return joinlist;
+
+ scratch.root = root;
+ scratch.relids = palloc(root->simple_rel_array_size * sizeof(Index));
+ scratch.row_marks = palloc0(root->simple_rel_array_size * sizeof(PlanRowMark *));
+ scratch.joinrelids = NULL;
+
+ /* Collect row marks. */
+ foreach (lc, root->rowMarks)
+ {
+ PlanRowMark *rowMark = (PlanRowMark *) lfirst(lc);
+
+ /* Can't have more than one row mark for a relation. */
+ Assert(scratch.row_marks[rowMark->rti] == NULL);
+
+ scratch.row_marks[rowMark->rti] = rowMark;
+ }
+
+ /* Finally, remove the joins. */
+ relidsToRemove = remove_self_joins_recurse(&scratch,
+ joinlist,
+ relidsToRemove);
+ while ((relid = bms_next_member(relidsToRemove, relid)) >= 0)
+ {
+ int nremoved = 0;
+ joinlist = remove_rel_from_joinlist(joinlist, relid, &nremoved);
+ }
+
+ pfree(scratch.relids);
+ pfree(scratch.row_marks);
+ return joinlist;
+}
diff --git a/src/backend/optimizer/plan/planmain.c b/src/backend/optimizer/plan/planmain.c
index 62dfc6d44a..3dd3269fe3 100644
--- a/src/backend/optimizer/plan/planmain.c
+++ b/src/backend/optimizer/plan/planmain.c
@@ -226,6 +226,11 @@ query_planner(PlannerInfo *root,
*/
reduce_unique_semijoins(root);
+ /*
+ * Remove self joins on a unique column.
+ */
+ joinlist = remove_useless_self_joins(root, joinlist);
+
/*
* Now distribute "placeholders" to base rels as needed. This has to be
* done after join removal because removal could change whether a
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 76245c1ff3..a7dc7bed1b 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -40,14 +40,10 @@ typedef struct JoinHashEntry
static void build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
RelOptInfo *input_rel);
-static List *build_joinrel_restrictlist(PlannerInfo *root,
- RelOptInfo *joinrel,
- RelOptInfo *outer_rel,
- RelOptInfo *inner_rel);
static void build_joinrel_joinlist(RelOptInfo *joinrel,
RelOptInfo *outer_rel,
RelOptInfo *inner_rel);
-static List *subbuild_joinrel_restrictlist(RelOptInfo *joinrel,
+static List *subbuild_joinrel_restrictlist(Relids joinrelids,
List *joininfo_list,
List *new_restrictlist);
static List *subbuild_joinrel_joinlist(RelOptInfo *joinrel,
@@ -599,7 +595,7 @@ build_join_rel(PlannerInfo *root,
*/
if (restrictlist_ptr)
*restrictlist_ptr = build_joinrel_restrictlist(root,
- joinrel,
+ joinrel->relids,
outer_rel,
inner_rel);
return joinrel;
@@ -704,7 +700,7 @@ build_join_rel(PlannerInfo *root,
* caller might or might not need the restrictlist, but I need it anyway
* for set_joinrel_size_estimates().)
*/
- restrictlist = build_joinrel_restrictlist(root, joinrel,
+ restrictlist = build_joinrel_restrictlist(root, joinrel->relids,
outer_rel, inner_rel);
if (restrictlist_ptr)
*restrictlist_ptr = restrictlist;
@@ -1038,7 +1034,7 @@ build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
* the various joinlist entries ultimately refer to RestrictInfos
* pushed into them by distribute_restrictinfo_to_rels().
*
- * 'joinrel' is a join relation node
+ * 'joinrelids' is a join relation id set
* 'outer_rel' and 'inner_rel' are a pair of relations that can be joined
* to form joinrel.
*
@@ -1051,9 +1047,9 @@ build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
* RestrictInfo nodes are no longer context-dependent. Instead, just include
* the original nodes in the lists made for the join relation.
*/
-static List *
+List *
build_joinrel_restrictlist(PlannerInfo *root,
- RelOptInfo *joinrel,
+ Relids joinrelids,
RelOptInfo *outer_rel,
RelOptInfo *inner_rel)
{
@@ -1064,8 +1060,8 @@ build_joinrel_restrictlist(PlannerInfo *root,
* eliminating any duplicates (important since we will see many of the
* same clauses arriving from both input relations).
*/
- result = subbuild_joinrel_restrictlist(joinrel, outer_rel->joininfo, NIL);
- result = subbuild_joinrel_restrictlist(joinrel, inner_rel->joininfo, result);
+ result = subbuild_joinrel_restrictlist(joinrelids, outer_rel->joininfo, NIL);
+ result = subbuild_joinrel_restrictlist(joinrelids, inner_rel->joininfo, result);
/*
* Add on any clauses derived from EquivalenceClasses. These cannot be
@@ -1074,7 +1070,7 @@ build_joinrel_restrictlist(PlannerInfo *root,
*/
result = list_concat(result,
generate_join_implied_equalities(root,
- joinrel->relids,
+ joinrelids,
outer_rel->relids,
inner_rel));
@@ -1100,7 +1096,7 @@ build_joinrel_joinlist(RelOptInfo *joinrel,
}
static List *
-subbuild_joinrel_restrictlist(RelOptInfo *joinrel,
+subbuild_joinrel_restrictlist(Relids joinrelids,
List *joininfo_list,
List *new_restrictlist)
{
@@ -1110,7 +1106,7 @@ subbuild_joinrel_restrictlist(RelOptInfo *joinrel,
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(l);
- if (bms_is_subset(rinfo->required_relids, joinrel->relids))
+ if (bms_is_subset(rinfo->required_relids, joinrelids))
{
/*
* This clause becomes a restriction clause for the joinrel, since
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 245a3472bc..71b43ed17a 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -1108,6 +1108,16 @@ static struct config_bool ConfigureNamesBool[] =
true,
NULL, NULL, NULL
},
+ {
+ {"enable_self_join_removal", PGC_USERSET, QUERY_TUNING_METHOD,
+ gettext_noop("Enable removal of unique self-joins."),
+ NULL,
+ GUC_EXPLAIN
+ },
+ &enable_self_join_removal,
+ true,
+ NULL, NULL, NULL
+ },
{
{"geqo", PGC_USERSET, QUERY_TUNING_GEQO,
gettext_noop("Enables genetic query optimization."),
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 715a24ad29..d7bcfcd7ec 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -295,6 +295,10 @@ extern RelOptInfo *build_join_rel(PlannerInfo *root,
RelOptInfo *inner_rel,
SpecialJoinInfo *sjinfo,
List **restrictlist_ptr);
+extern List *build_joinrel_restrictlist(PlannerInfo *root,
+ Relids joinrelids,
+ RelOptInfo *outer_rel,
+ RelOptInfo *inner_rel);
extern Relids min_join_parameterization(PlannerInfo *root,
Relids joinrelids,
RelOptInfo *outer_rel,
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index 81c4a7e560..16cfd3f7a4 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -20,6 +20,7 @@
/* GUC parameters */
#define DEFAULT_CURSOR_TUPLE_FRACTION 0.1
extern double cursor_tuple_fraction;
+extern bool enable_self_join_removal;
/* query_planner callback to compute query_pathkeys */
typedef void (*query_pathkeys_callback) (PlannerInfo *root, void *extra);
@@ -106,6 +107,7 @@ extern bool query_is_distinct_for(Query *query, List *colnos, List *opids);
extern bool innerrel_is_unique(PlannerInfo *root,
Relids joinrelids, Relids outerrelids, RelOptInfo *innerrel,
JoinType jointype, List *restrictlist, bool force_cache);
+extern List * remove_useless_self_joins(PlannerInfo *root, List *jointree);
/*
* prototypes for plan/setrefs.c
diff --git a/src/test/regress/expected/equivclass.out b/src/test/regress/expected/equivclass.out
index 126f7047fe..de71441052 100644
--- a/src/test/regress/expected/equivclass.out
+++ b/src/test/regress/expected/equivclass.out
@@ -430,6 +430,38 @@ explain (costs off)
Filter: ((unique1 IS NOT NULL) AND (unique2 IS NOT NULL))
(2 rows)
+-- Test that broken ECs are processed correctly during self join removal.
+-- Disable merge joins so that we don't get an error about missing commutator.
+-- Test both orientations of the join clause, because only one of them breaks
+-- the EC.
+set enable_mergejoin to off;
+explain (costs off)
+ select * from ec0 m join ec0 n on m.ff = n.ff
+ join ec1 p on m.ff + n.ff = p.f1;
+ QUERY PLAN
+----------------------------------------
+ Nested Loop
+ Join Filter: ((n.ff + n.ff) = p.f1)
+ -> Seq Scan on ec1 p
+ -> Materialize
+ -> Seq Scan on ec0 n
+ Filter: (ff IS NOT NULL)
+(6 rows)
+
+explain (costs off)
+ select * from ec0 m join ec0 n on m.ff = n.ff
+ join ec1 p on p.f1::int8 = (m.ff + n.ff)::int8alias1;
+ QUERY PLAN
+---------------------------------------------------------------
+ Nested Loop
+ Join Filter: ((p.f1)::bigint = ((n.ff + n.ff))::int8alias1)
+ -> Seq Scan on ec1 p
+ -> Materialize
+ -> Seq Scan on ec0 n
+ Filter: (ff IS NOT NULL)
+(6 rows)
+
+reset enable_mergejoin;
-- this could be converted, but isn't at present
explain (costs off)
select * from tenk1 where unique1 = unique1 or unique2 = unique2;
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 60b621b651..2683f93c62 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4674,6 +4674,337 @@ select * from
----+----+----+----
(0 rows)
+--
+-- test that semi- or inner self-joins on a unique column are removed
+--
+-- enable only nestloop to get more predictable plans
+set enable_hashjoin to off;
+set enable_mergejoin to off;
+create table sj (a int unique, b int);
+insert into sj values (1, null), (null, 2), (2, 1);
+analyze sj;
+select p.* from sj p, sj q where q.a = p.a and q.b = q.a - 1;
+ a | b
+---+---
+ 2 | 1
+(1 row)
+
+explain (costs off)
+select p.* from sj p, sj q where q.a = p.a and q.b = q.a - 1;
+ QUERY PLAN
+-----------------------------------------------
+ Seq Scan on sj q
+ Filter: ((a IS NOT NULL) AND (b = (a - 1)))
+(2 rows)
+
+explain (costs off)
+select * from sj p
+where exists (select * from sj q
+ where q.a = p.a and q.b < 10);
+ QUERY PLAN
+------------------------------------------
+ Seq Scan on sj q
+ Filter: ((a IS NOT NULL) AND (b < 10))
+(2 rows)
+
+-- Double self-join removal.
+-- Use a condition on "b + 1", not on "b", for the second join, so that
+-- the equivalence class is different from the first one, and we can
+-- test the non-ec code path.
+explain (costs off)
+select * from sj t1 join sj t2 on t1.a = t2.a and t1.b = t2.b
+ join sj t3 on t2.a = t3.a and t2.b + 1 = t3.b + 1;
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Seq Scan on sj t3
+ Filter: ((a IS NOT NULL) AND (b IS NOT NULL) AND ((b + 1) IS NOT NULL))
+(2 rows)
+
+-- subselect that references the removed relation
+explain (costs off)
+select t1.a, (select a from sj where a = t2.a and a = t1.a)
+from sj t1, sj t2
+where t1.a = t2.a;
+ QUERY PLAN
+------------------------------------------
+ Seq Scan on sj t2
+ Filter: (a IS NOT NULL)
+ SubPlan 1
+ -> Result
+ One-Time Filter: (t2.a = t2.a)
+ -> Seq Scan on sj
+ Filter: (a = t2.a)
+(7 rows)
+
+-- self-join under outer join
+explain (costs off)
+select * from sj x join sj y on x.a = y.a
+left join int8_tbl z on x.a = z.q1;
+ QUERY PLAN
+------------------------------------
+ Nested Loop Left Join
+ Join Filter: (y.a = z.q1)
+ -> Seq Scan on sj y
+ Filter: (a IS NOT NULL)
+ -> Materialize
+ -> Seq Scan on int8_tbl z
+(6 rows)
+
+explain (costs off)
+select * from sj x join sj y on x.a = y.a
+left join int8_tbl z on y.a = z.q1;
+ QUERY PLAN
+------------------------------------
+ Nested Loop Left Join
+ Join Filter: (y.a = z.q1)
+ -> Seq Scan on sj y
+ Filter: (a IS NOT NULL)
+ -> Materialize
+ -> Seq Scan on int8_tbl z
+(6 rows)
+
+-- Test that placeholders are updated correctly after join removal
+explain (costs off)
+select * from (values (1)) x
+left join (select coalesce(y.q1, 1) from int8_tbl y
+ right join sj j1 inner join sj j2 on j1.a = j2.a
+ on true) z
+on true;
+ QUERY PLAN
+------------------------------------------
+ Nested Loop Left Join
+ -> Result
+ -> Nested Loop Left Join
+ -> Seq Scan on sj j2
+ Filter: (a IS NOT NULL)
+ -> Materialize
+ -> Seq Scan on int8_tbl y
+(7 rows)
+
+-- Test that OR predicated are updated correctly after join removal
+CREATE TABLE tab_with_flag ( id INT PRIMARY KEY, is_flag SMALLINT);
+CREATE INDEX idx_test_is_flag ON tab_with_flag (is_flag);
+explain (costs off)
+SELECT COUNT(*) FROM tab_with_flag WHERE (is_flag IS NULL OR is_flag = 0) AND id IN (SELECT id FROM tab_with_flag WHERE id IN (2, 3));
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tab_with_flag
+ Recheck Cond: ((id = ANY ('{2,3}'::integer[])) AND (id IS NOT NULL))
+ Filter: ((is_flag IS NULL) OR (is_flag = 0))
+ -> Bitmap Index Scan on tab_with_flag_pkey
+ Index Cond: ((id = ANY ('{2,3}'::integer[])) AND (id IS NOT NULL))
+(6 rows)
+
+DROP TABLE tab_with_flag;
+-- HAVING clause
+explain (costs off)
+select p.b from sj p join sj q on p.a = q.a group by p.b having sum(p.a) = 1;
+ QUERY PLAN
+---------------------------------
+ HashAggregate
+ Group Key: q.b
+ Filter: (sum(q.a) = 1)
+ -> Seq Scan on sj q
+ Filter: (a IS NOT NULL)
+(5 rows)
+
+-- update lateral references and range table entry reference
+explain (verbose, costs off)
+select 1 from (select x.* from sj x, sj y where x.a = y.a) q,
+ lateral generate_series(1, q.a) gs(i);
+ QUERY PLAN
+------------------------------------------------------
+ Nested Loop
+ Output: 1
+ -> Seq Scan on public.sj y
+ Output: y.a, y.b
+ Filter: (y.a IS NOT NULL)
+ -> Function Scan on pg_catalog.generate_series gs
+ Output: gs.i
+ Function Call: generate_series(1, y.a)
+(8 rows)
+
+explain (verbose, costs off)
+select 1 from (select y.* from sj x, sj y where x.a = y.a) q,
+ lateral generate_series(1, q.a) gs(i);
+ QUERY PLAN
+------------------------------------------------------
+ Nested Loop
+ Output: 1
+ -> Seq Scan on public.sj y
+ Output: y.a, y.b
+ Filter: (y.a IS NOT NULL)
+ -> Function Scan on pg_catalog.generate_series gs
+ Output: gs.i
+ Function Call: generate_series(1, y.a)
+(8 rows)
+
+-- Test that a non-EC-derived join clause is processed correctly. Use an
+-- outer join so that we can't form an EC.
+explain (costs off) select * from sj p join sj q on p.a = q.a
+ left join sj r on p.a + q.a = r.a;
+ QUERY PLAN
+------------------------------------
+ Nested Loop Left Join
+ Join Filter: ((q.a + q.a) = r.a)
+ -> Seq Scan on sj q
+ Filter: (a IS NOT NULL)
+ -> Materialize
+ -> Seq Scan on sj r
+(6 rows)
+
+-- FIXME this constant false filter doesn't look good. Should we merge
+-- equivalence classes?
+explain (costs off)
+select * from sj p, sj q where p.a = q.a and p.b = 1 and q.b = 2;
+ QUERY PLAN
+-----------------------------------------------------
+ Seq Scan on sj q
+ Filter: ((a IS NOT NULL) AND (b = 2) AND (b = 1))
+(2 rows)
+
+-- Check that attr_needed is updated correctly after self-join removal. In this
+-- test, the join of j1 with j2 is removed. k1.b is required at either j1 or j2.
+-- If this info is lost, join targetlist for (k1, k2) will not contain k1.b.
+-- Use index scan for k1 so that we don't get 'b' from physical tlist used for
+-- seqscan. Also disable reordering of joins because this test depends on a
+-- particular join tree.
+create table sk (a int, b int);
+create index on sk(a);
+set join_collapse_limit to 1;
+set enable_seqscan to off;
+explain (costs off) select 1 from
+ (sk k1 join sk k2 on k1.a = k2.a)
+ join (sj j1 join sj j2 on j1.a = j2.a) on j1.b = k1.b;
+ QUERY PLAN
+-----------------------------------------------------
+ Nested Loop
+ Join Filter: (k1.b = j2.b)
+ -> Nested Loop
+ -> Index Scan using sk_a_idx on sk k1
+ -> Index Only Scan using sk_a_idx on sk k2
+ Index Cond: (a = k1.a)
+ -> Materialize
+ -> Index Scan using sj_a_key on sj j2
+ Index Cond: (a IS NOT NULL)
+(9 rows)
+
+explain (costs off) select 1 from
+ (sk k1 join sk k2 on k1.a = k2.a)
+ join (sj j1 join sj j2 on j1.a = j2.a) on j2.b = k1.b;
+ QUERY PLAN
+-----------------------------------------------------
+ Nested Loop
+ Join Filter: (k1.b = j2.b)
+ -> Nested Loop
+ -> Index Scan using sk_a_idx on sk k1
+ -> Index Only Scan using sk_a_idx on sk k2
+ Index Cond: (a = k1.a)
+ -> Materialize
+ -> Index Scan using sj_a_key on sj j2
+ Index Cond: (a IS NOT NULL)
+(9 rows)
+
+reset join_collapse_limit;
+reset enable_seqscan;
+-- Check that clauses from the join filter list is not lost on the self-join removal
+CREATE TABLE emp1 ( id SERIAL PRIMARY KEY NOT NULL, code int);
+explain (verbose, costs off)
+SELECT * FROM emp1 e1, emp1 e2 WHERE e1.id = e2.id AND e2.code <> e1.code;
+ QUERY PLAN
+----------------------------------------------------------
+ Seq Scan on public.emp1 e2
+ Output: e2.id, e2.code, e2.id, e2.code
+ Filter: ((e2.id IS NOT NULL) AND (e2.code <> e2.code))
+(3 rows)
+
+-- We can remove the join even if we find the join can't duplicate rows and
+-- the base quals of each side are different. In the following case we end up
+-- moving quals over to s1 to make it so it can't match any rows.
+create table sl(a int, b int);
+create unique index on sl(a, b);
+vacuum analyze sl;
+-- Both sides are unique, but base quals are different
+explain (costs off)
+select * from sl t1, sl t2 where t1.a = t2.a and t1.b = 1 and t2.b = 2;
+ QUERY PLAN
+-----------------------------------------------------
+ Seq Scan on sl t2
+ Filter: ((a IS NOT NULL) AND (b = 2) AND (b = 1))
+(2 rows)
+
+--
+---- Only one side is unqiue
+--select * from sl t1, sl t2 where t1.a = t2.a and t1.b = 1;
+--select * from sl t1, sl t2 where t1.a = t2.a and t2.b = 1;
+--
+---- Several uniques indexes match, and we select a different one
+---- for each side, so the join is not removed
+--create table sm(a int unique, b int unique, c int unique);
+--explain (costs off)
+--select * from sm m, sm n where m.a = n.b and m.c = n.c;
+--explain (costs off)
+--select * from sm m, sm n where m.a = n.c and m.b = n.b;
+--explain (costs off)
+--select * from sm m, sm n where m.c = n.b and m.a = n.a;
+-- Check optimization disabling if it will violate special join conditions.
+-- Two identical joined relations satisfies self join removal conditions but
+-- stay in different special join infos.
+CREATE TABLE sj_t1 (id serial, a int);
+CREATE TABLE sj_t2 (id serial, a int);
+CREATE TABLE sj_t3 (id serial, a int);
+CREATE TABLE sj_t4 (id serial, a int);
+CREATE UNIQUE INDEX ON sj_t3 USING btree (a,id);
+CREATE UNIQUE INDEX ON sj_t2 USING btree (id);
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj_t1
+JOIN (
+ SELECT sj_t2.id AS id FROM sj_t2
+ WHERE EXISTS
+ (
+ SELECT TRUE FROM sj_t3,sj_t4 WHERE sj_t3.a = 1 AND sj_t3.id = sj_t2.id
+ )
+ ) t2t3t4
+ON sj_t1.id = t2t3t4.id
+JOIN (
+ SELECT sj_t2.id AS id FROM sj_t2
+ WHERE EXISTS
+ (
+ SELECT TRUE FROM sj_t3,sj_t4 WHERE sj_t3.a = 1 AND sj_t3.id = sj_t2.id
+ )
+ ) _t2t3t4
+ON sj_t1.id = _t2t3t4.id;
+ QUERY PLAN
+-------------------------------------------------------------------------------------
+ Nested Loop
+ Join Filter: (sj_t3.id = sj_t1.id)
+ -> Nested Loop
+ Join Filter: (sj_t3.id = sj_t2.id)
+ -> Nested Loop Semi Join
+ -> Nested Loop
+ -> HashAggregate
+ Group Key: sj_t3.id
+ -> Nested Loop
+ -> Seq Scan on sj_t4
+ -> Materialize
+ -> Bitmap Heap Scan on sj_t3
+ Recheck Cond: (a = 1)
+ -> Bitmap Index Scan on sj_t3_a_id_idx
+ Index Cond: (a = 1)
+ -> Index Only Scan using sj_t2_id_idx on sj_t2 sj_t2_1
+ Index Cond: (id = sj_t3.id)
+ -> Nested Loop
+ -> Index Only Scan using sj_t3_a_id_idx on sj_t3 sj_t3_1
+ Index Cond: ((a = 1) AND (id = sj_t3.id))
+ -> Seq Scan on sj_t4 sj_t4_1
+ -> Index Only Scan using sj_t2_id_idx on sj_t2
+ Index Cond: (id = sj_t2_1.id)
+ -> Seq Scan on sj_t1
+(24 rows)
+
+reset enable_hashjoin;
+reset enable_mergejoin;
--
-- Test hints given on incorrect column references are useful
--
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 81bdacf59d..9847097700 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -103,10 +103,11 @@ select name, setting from pg_settings where name like 'enable%';
enable_partition_pruning | on
enable_partitionwise_aggregate | off
enable_partitionwise_join | off
+ enable_self_join_removal | on
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(18 rows)
+(19 rows)
-- Test that the pg_timezone_names and pg_timezone_abbrevs views are
-- more-or-less working. We can't test their contents in any great detail
diff --git a/src/test/regress/sql/equivclass.sql b/src/test/regress/sql/equivclass.sql
index 247b0a3105..77dd964ebf 100644
--- a/src/test/regress/sql/equivclass.sql
+++ b/src/test/regress/sql/equivclass.sql
@@ -259,6 +259,22 @@ drop user regress_user_ectest;
explain (costs off)
select * from tenk1 where unique1 = unique1 and unique2 = unique2;
+-- Test that broken ECs are processed correctly during self join removal.
+-- Disable merge joins so that we don't get an error about missing commutator.
+-- Test both orientations of the join clause, because only one of them breaks
+-- the EC.
+set enable_mergejoin to off;
+
+explain (costs off)
+ select * from ec0 m join ec0 n on m.ff = n.ff
+ join ec1 p on m.ff + n.ff = p.f1;
+
+explain (costs off)
+ select * from ec0 m join ec0 n on m.ff = n.ff
+ join ec1 p on p.f1::int8 = (m.ff + n.ff)::int8alias1;
+
+reset enable_mergejoin;
+
-- this could be converted, but isn't at present
explain (costs off)
select * from tenk1 where unique1 = unique1 or unique2 = unique2;
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index d687216618..79c7a4b6ed 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1656,6 +1656,180 @@ select * from
select * from
int8_tbl x join (int4_tbl x cross join int4_tbl y(ff)) j on q1 = f1; -- ok
+--
+-- test that semi- or inner self-joins on a unique column are removed
+--
+
+-- enable only nestloop to get more predictable plans
+set enable_hashjoin to off;
+set enable_mergejoin to off;
+
+create table sj (a int unique, b int);
+insert into sj values (1, null), (null, 2), (2, 1);
+analyze sj;
+
+select p.* from sj p, sj q where q.a = p.a and q.b = q.a - 1;
+
+explain (costs off)
+select p.* from sj p, sj q where q.a = p.a and q.b = q.a - 1;
+
+explain (costs off)
+select * from sj p
+where exists (select * from sj q
+ where q.a = p.a and q.b < 10);
+
+-- Double self-join removal.
+-- Use a condition on "b + 1", not on "b", for the second join, so that
+-- the equivalence class is different from the first one, and we can
+-- test the non-ec code path.
+explain (costs off)
+select * from sj t1 join sj t2 on t1.a = t2.a and t1.b = t2.b
+ join sj t3 on t2.a = t3.a and t2.b + 1 = t3.b + 1;
+
+-- subselect that references the removed relation
+explain (costs off)
+select t1.a, (select a from sj where a = t2.a and a = t1.a)
+from sj t1, sj t2
+where t1.a = t2.a;
+
+-- self-join under outer join
+explain (costs off)
+select * from sj x join sj y on x.a = y.a
+left join int8_tbl z on x.a = z.q1;
+
+explain (costs off)
+select * from sj x join sj y on x.a = y.a
+left join int8_tbl z on y.a = z.q1;
+
+-- Test that placeholders are updated correctly after join removal
+explain (costs off)
+select * from (values (1)) x
+left join (select coalesce(y.q1, 1) from int8_tbl y
+ right join sj j1 inner join sj j2 on j1.a = j2.a
+ on true) z
+on true;
+
+-- Test that OR predicated are updated correctly after join removal
+CREATE TABLE tab_with_flag ( id INT PRIMARY KEY, is_flag SMALLINT);
+CREATE INDEX idx_test_is_flag ON tab_with_flag (is_flag);
+explain (costs off)
+SELECT COUNT(*) FROM tab_with_flag WHERE (is_flag IS NULL OR is_flag = 0) AND id IN (SELECT id FROM tab_with_flag WHERE id IN (2, 3));
+DROP TABLE tab_with_flag;
+
+-- HAVING clause
+explain (costs off)
+select p.b from sj p join sj q on p.a = q.a group by p.b having sum(p.a) = 1;
+
+-- update lateral references and range table entry reference
+explain (verbose, costs off)
+select 1 from (select x.* from sj x, sj y where x.a = y.a) q,
+ lateral generate_series(1, q.a) gs(i);
+
+explain (verbose, costs off)
+select 1 from (select y.* from sj x, sj y where x.a = y.a) q,
+ lateral generate_series(1, q.a) gs(i);
+
+-- Test that a non-EC-derived join clause is processed correctly. Use an
+-- outer join so that we can't form an EC.
+explain (costs off) select * from sj p join sj q on p.a = q.a
+ left join sj r on p.a + q.a = r.a;
+
+-- FIXME this constant false filter doesn't look good. Should we merge
+-- equivalence classes?
+explain (costs off)
+select * from sj p, sj q where p.a = q.a and p.b = 1 and q.b = 2;
+
+-- Check that attr_needed is updated correctly after self-join removal. In this
+-- test, the join of j1 with j2 is removed. k1.b is required at either j1 or j2.
+-- If this info is lost, join targetlist for (k1, k2) will not contain k1.b.
+-- Use index scan for k1 so that we don't get 'b' from physical tlist used for
+-- seqscan. Also disable reordering of joins because this test depends on a
+-- particular join tree.
+create table sk (a int, b int);
+create index on sk(a);
+set join_collapse_limit to 1;
+set enable_seqscan to off;
+explain (costs off) select 1 from
+ (sk k1 join sk k2 on k1.a = k2.a)
+ join (sj j1 join sj j2 on j1.a = j2.a) on j1.b = k1.b;
+explain (costs off) select 1 from
+ (sk k1 join sk k2 on k1.a = k2.a)
+ join (sj j1 join sj j2 on j1.a = j2.a) on j2.b = k1.b;
+reset join_collapse_limit;
+reset enable_seqscan;
+
+-- Check that clauses from the join filter list is not lost on the self-join removal
+CREATE TABLE emp1 ( id SERIAL PRIMARY KEY NOT NULL, code int);
+explain (verbose, costs off)
+SELECT * FROM emp1 e1, emp1 e2 WHERE e1.id = e2.id AND e2.code <> e1.code;
+
+-- We can remove the join even if we find the join can't duplicate rows and
+-- the base quals of each side are different. In the following case we end up
+-- moving quals over to s1 to make it so it can't match any rows.
+create table sl(a int, b int);
+create unique index sl_idx on sl(a, b);
+vacuum analyze sl;
+
+-- Both sides are unique, but base quals are different
+explain (costs off)
+select * from sl t1, sl t2 where t1.a = t2.a and t1.b = 1 and t2.b = 2;
+
+DROP INDEX sl_idx;
+CREATE UNIQUE INDEX ON sl(a);
+CREATE UNIQUE INDEX ON sl(b);
+explain (COSTS OFF)
+SELECT a1.* FROM sl a1, sl a2 WHERE a1.a = a2.a; -- self-join
+explain (COSTS OFF)
+SELECT a1.* FROM sl a1, sl a2 WHERE a1.a = a2.b; -- not self-join
+
+--
+---- Only one side is unqiue
+--select * from sl t1, sl t2 where t1.a = t2.a and t1.b = 1;
+--select * from sl t1, sl t2 where t1.a = t2.a and t2.b = 1;
+--
+---- Several uniques indexes match, and we select a different one
+---- for each side, so the join is not removed
+--create table sm(a int unique, b int unique, c int unique);
+--explain (costs off)
+--select * from sm m, sm n where m.a = n.b and m.c = n.c;
+--explain (costs off)
+--select * from sm m, sm n where m.a = n.c and m.b = n.b;
+--explain (costs off)
+--select * from sm m, sm n where m.c = n.b and m.a = n.a;
+
+-- Check optimization disabling if it will violate special join conditions.
+-- Two identical joined relations satisfies self join removal conditions but
+-- stay in different special join infos.
+CREATE TABLE sj_t1 (id serial, a int);
+CREATE TABLE sj_t2 (id serial, a int);
+CREATE TABLE sj_t3 (id serial, a int);
+CREATE TABLE sj_t4 (id serial, a int);
+
+CREATE UNIQUE INDEX ON sj_t3 USING btree (a,id);
+CREATE UNIQUE INDEX ON sj_t2 USING btree (id);
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj_t1
+JOIN (
+ SELECT sj_t2.id AS id FROM sj_t2
+ WHERE EXISTS
+ (
+ SELECT TRUE FROM sj_t3,sj_t4 WHERE sj_t3.a = 1 AND sj_t3.id = sj_t2.id
+ )
+ ) t2t3t4
+ON sj_t1.id = t2t3t4.id
+JOIN (
+ SELECT sj_t2.id AS id FROM sj_t2
+ WHERE EXISTS
+ (
+ SELECT TRUE FROM sj_t3,sj_t4 WHERE sj_t3.a = 1 AND sj_t3.id = sj_t2.id
+ )
+ ) _t2t3t4
+ON sj_t1.id = _t2t3t4.id;
+
+reset enable_hashjoin;
+reset enable_mergejoin;
+
--
-- Test hints given on incorrect column references are useful
--
--
2.25.1
--------------CD2D5514AD6DD9934CE89D5F--
^ permalink raw reply [nested|flat] 18+ messages in thread
* generating function default settings from pg_proc.dat
@ 2026-02-16 17:31 Andrew Dunstan <[email protected]>
2026-02-16 18:07 ` Re: generating function default settings from pg_proc.dat Daniel Gustafsson <[email protected]>
2026-02-16 18:08 ` Re: generating function default settings from pg_proc.dat Corey Huinker <[email protected]>
2026-02-16 18:54 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
0 siblings, 3 replies; 18+ messages in thread
From: Andrew Dunstan @ 2026-02-16 17:31 UTC (permalink / raw)
To: pgsql-hackers
Motivated by Bug 19409 [1] I decided to do something about a wart that
has bugged me for a while, namely the requirement to write stuff in
system_views.sql if you need to specify default values for function
arguments. Here's my attempt. The first patch here sets up the required
infrastructure. genbki.pl creates a file called function_defaults.sql
which is run by initdb at the appropriate time. There are two new fields
in pg_proc.dat entries: proargdflts,and provariadicdflt. These are
parsed and the appropriate CREATE OR REPLACE statement is generated and
placed in function_defaults.sql. The second patch applies this treatment
to 37 function definitions and removes the corresponding statements from
system_views.sql. This gets us closer to having pg_proc.dat as a single
source of truth.
cheers
andrew
[1]
https://www.postgresql.org/message-id/19409-e16cd2605e59a4af%40postgresql.org
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Attachments:
[text/x-patch] 0001-Add-infrastructure-for-proargdflts-in-pg_proc.dat.patch (11.9K, ../../[email protected]/2-0001-Add-infrastructure-for-proargdflts-in-pg_proc.dat.patch)
download | inline diff:
From 7e80ad51623b87dff4ad810124dd485684b6ef3f Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email protected]>
Date: Mon, 16 Feb 2026 11:57:27 -0500
Subject: [PATCH 1/2] Add infrastructure for proargdflts in pg_proc.dat
Add support for specifying function argument defaults directly in
pg_proc.dat using a new proargdflts field for IN arguments and
provariadicdflt for VARIADIC arguments. The genbki.pl script
generates function_defaults.sql which is processed by initdb after
postgres.bki.
---
src/backend/catalog/genbki.pl | 218 +++++++++++++++++++++++++++++++-
src/bin/initdb/initdb.c | 5 +
src/include/catalog/Makefile | 4 +-
src/include/catalog/meson.build | 2 +
4 files changed, 225 insertions(+), 4 deletions(-)
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index b2c1b1c5733..113cb0e4996 100644
--- a/src/backend/catalog/genbki.pl
+++ b/src/backend/catalog/genbki.pl
@@ -2,9 +2,12 @@
#----------------------------------------------------------------------
#
# genbki.pl
-# Perl script that generates postgres.bki and symbol definition
-# headers from specially formatted header files and data files.
-# postgres.bki is used to initialize the postgres template database.
+# Perl script that generates postgres.bki, symbol definition headers,
+# and function_defaults.sql from specially formatted header files and
+# data files. postgres.bki is used to initialize the postgres template
+# database. function_defaults.sql provides default argument values for
+# built-in functions specified via proargdflts (for IN arguments)
+# and provariadicdflt (for VARIADIC arguments) in pg_proc.dat.
#
# Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
# Portions Copyright (c) 1994, Regents of the University of California
@@ -452,6 +455,9 @@ open my $syscache_ids_fh, '>', $syscache_ids_file . $tmpext
my $syscache_info_file = $output_path . 'syscache_info.h';
open my $syscache_info_fh, '>', $syscache_info_file . $tmpext
or die "can't open $syscache_info_file$tmpext: $!";
+my $fmgr_defaults_file = $output_path . 'function_defaults.sql';
+open my $fmgr_defaults, '>', $fmgr_defaults_file . $tmpext
+ or die "can't open $fmgr_defaults_file$tmpext: $!";
# Generate postgres.bki and pg_*_d.h headers.
@@ -597,6 +603,8 @@ EOM
if $key eq "oid_symbol"
|| $key eq "array_type_oid"
|| $key eq "descr"
+ || $key eq "proargdflts"
+ || $key eq "provariadicdflt"
|| $key eq "autogenerated"
|| $key eq "line_number";
die sprintf "unrecognized field name \"%s\" in %s.dat line %s\n",
@@ -726,6 +734,171 @@ foreach my $c (@system_constraints)
print $constraints $c, "\n\n";
}
+# Now generate function_defaults.sql
+# This file contains CREATE OR REPLACE FUNCTION statements to set default argument values
+# for functions that have proargdflts specified in pg_proc.dat.
+
+print $fmgr_defaults <<EOM;
+--
+-- PostgreSQL System Function Defaults
+--
+-- Auto-generated from pg_proc.dat proargdflts entries.
+-- Do not edit manually.
+--
+-- This file sets default argument values for built-in functions.
+-- It is processed after postgres.bki during initdb.
+--
+
+EOM
+
+# Maps for converting catalog codes to SQL keywords
+my %volatility_map = ('i' => 'IMMUTABLE', 's' => 'STABLE', 'v' => 'VOLATILE');
+my %parallel_map = ('s' => 'PARALLEL SAFE', 'r' => 'PARALLEL RESTRICTED', 'u' => 'PARALLEL UNSAFE');
+
+foreach my $row (@{ $catalog_data{pg_proc} })
+{
+ next unless defined $row->{proargdflts} || defined $row->{provariadicdflt};
+
+ my $proname = $row->{proname};
+
+ # Get all argument types (use proallargtypes if present and not NULL, else proargtypes)
+ my @allargtypes;
+ if (defined $row->{proallargtypes} && $row->{proallargtypes} ne '' && $row->{proallargtypes} ne '_null_')
+ {
+ my $alltypes = $row->{proallargtypes};
+ $alltypes =~ s/^\{|\}$//g;
+ @allargtypes = split /,/, $alltypes;
+ }
+ else
+ {
+ @allargtypes = split /\s+/, $row->{proargtypes};
+ }
+
+ # Get argument modes (i=IN, o=OUT, b=INOUT, v=VARIADIC, t=TABLE)
+ my @argmodes;
+ if (defined $row->{proargmodes} && $row->{proargmodes} ne '' && $row->{proargmodes} ne '_null_')
+ {
+ my $modes = $row->{proargmodes};
+ $modes =~ s/^\{|\}$//g;
+ @argmodes = split /,/, $modes;
+ }
+ else
+ {
+ # All arguments are IN if no modes specified
+ @argmodes = ('i') x scalar(@allargtypes);
+ }
+
+ # Parse proargnames: '{name1,name2}' -> ['name1', 'name2']
+ my @argnames;
+ if (defined $row->{proargnames})
+ {
+ my $names = $row->{proargnames};
+ $names =~ s/^\{|\}$//g;
+ @argnames = split /,/, $names;
+ }
+
+ # Count IN arguments (these are the ones that can have defaults)
+ my $n_in_args = 0;
+ for my $mode (@argmodes)
+ {
+ $n_in_args++ if $mode eq 'i';
+ }
+
+ # Parse defaults: '{val1,val2}' or 'val1,val2' -> ['val1', 'val2']
+ my @defaults;
+ my $ndefaults = 0;
+ if (defined $row->{proargdflts})
+ {
+ my $defaults_str = $row->{proargdflts};
+ $defaults_str =~ s/^\{|\}$//g;
+ # Split on comma, but respect parentheses for function calls like foo(1,2)
+ @defaults = parse_defaults_list($defaults_str);
+ $ndefaults = scalar @defaults;
+ }
+
+ if ($ndefaults > $n_in_args)
+ {
+ die sprintf "too many defaults (%d) for function %s with %d IN args at line %s\n",
+ $ndefaults, $proname, $n_in_args, $row->{line_number};
+ }
+
+ # Build the argument list with names, types, modes, and defaults
+ my $first_default_in_arg = $n_in_args - $ndefaults;
+ my @arg_defs;
+ my $in_arg_idx = 0;
+ for (my $i = 0; $i < scalar(@allargtypes); $i++)
+ {
+ my $argname = $argnames[$i] // '';
+ my $argtype = $allargtypes[$i];
+ my $argmode = $argmodes[$i];
+ my $arg_def;
+
+ if ($argmode eq 'o')
+ {
+ $arg_def = "OUT $argname $argtype";
+ }
+ elsif ($argmode eq 'b')
+ {
+ $arg_def = "INOUT $argname $argtype";
+ }
+ elsif ($argmode eq 'v')
+ {
+ $arg_def = "VARIADIC $argname $argtype";
+ if (defined $row->{provariadicdflt})
+ {
+ my $vardefault = $row->{provariadicdflt};
+ $vardefault =~ s/^\s+|\s+$//g; # trim whitespace
+ $arg_def .= " DEFAULT $vardefault";
+ }
+ }
+ else
+ {
+ # IN argument (mode 'i' or default)
+ $arg_def = "$argname $argtype";
+
+ # Add default if this IN arg has one
+ if ($in_arg_idx >= $first_default_in_arg)
+ {
+ my $default_idx = $in_arg_idx - $first_default_in_arg;
+ my $default_val = $defaults[$default_idx];
+ $default_val =~ s/^\s+|\s+$//g; # trim whitespace
+ $arg_def .= " DEFAULT $default_val";
+ }
+ $in_arg_idx++;
+ }
+ push @arg_defs, $arg_def;
+ }
+
+ my $volatility = $volatility_map{$row->{provolatile} // 'v'} // 'VOLATILE';
+ my $parallel = $parallel_map{$row->{proparallel} // 'u'} // 'PARALLEL UNSAFE';
+
+ # Check if function is strict
+ my $strict = ($row->{proisstrict} // 't') eq 't' ? 'STRICT' : '';
+
+ # Get cost (default is 1 for internal functions)
+ my $cost = "COST " . ($row->{procost} // 1);
+
+ # Get return type and prosrc
+ my $rettype = $row->{prorettype};
+ # Add SETOF if proretset is true
+ if (($row->{proretset} // 'f') eq 't')
+ {
+ $rettype = "SETOF $rettype";
+ }
+ my $prosrc = $row->{prosrc};
+
+ # Build the CREATE OR REPLACE FUNCTION statement
+ my $args_str = join(",\n ", @arg_defs);
+ my @options = grep { $_ ne '' } ($volatility, $parallel, $strict, $cost);
+ my $options_str = join(' ', @options);
+
+ print $fmgr_defaults "CREATE OR REPLACE FUNCTION \"$proname\"(\n $args_str)\n" .
+ " RETURNS $rettype\n" .
+ " LANGUAGE internal\n" .
+ " $options_str\n" .
+ "AS '$prosrc';\n\n";
+}
+
# Now generate schemapg.h
print_boilerplate($schemapg, "schemapg.h",
@@ -837,6 +1010,7 @@ close $fk_info;
close $constraints;
close $syscache_ids_fh;
close $syscache_info_fh;
+close $fmgr_defaults;
# Finally, rename the completed files into place.
Catalog::RenameTempFile($bkifile, $tmpext);
@@ -845,6 +1019,7 @@ Catalog::RenameTempFile($fk_info_file, $tmpext);
Catalog::RenameTempFile($constraints_file, $tmpext);
Catalog::RenameTempFile($syscache_ids_file, $tmpext);
Catalog::RenameTempFile($syscache_info_file, $tmpext);
+Catalog::RenameTempFile($fmgr_defaults_file, $tmpext);
exit($num_errors != 0 ? 1 : 0);
@@ -1176,6 +1351,43 @@ sub print_boilerplate
EOM
}
+# Parse a comma-separated list of default values, respecting parentheses.
+# This handles expressions like "foo(1,2), bar" correctly.
+sub parse_defaults_list
+{
+ my $str = shift;
+ my @result;
+ my $current = '';
+ my $depth = 0;
+
+ for my $char (split //, $str)
+ {
+ if ($char eq '(' || $char eq '[')
+ {
+ $depth++;
+ $current .= $char;
+ }
+ elsif ($char eq ')' || $char eq ']')
+ {
+ $depth--;
+ $current .= $char;
+ }
+ elsif ($char eq ',' && $depth == 0)
+ {
+ push @result, $current;
+ $current = '';
+ }
+ else
+ {
+ $current .= $char;
+ }
+ }
+ # Don't forget the last element
+ push @result, $current if $current ne '' || @result > 0;
+
+ return @result;
+}
+
sub usage
{
die <<EOM;
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index a3980e5535f..811988bb590 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -183,6 +183,7 @@ static char *info_schema_file;
static char *features_file;
static char *system_constraints_file;
static char *system_functions_file;
+static char *function_defaults_file;
static char *system_views_file;
static bool success = false;
static bool made_new_pgdata = false;
@@ -2799,6 +2800,7 @@ setup_data_file_paths(void)
set_input(&features_file, "sql_features.txt");
set_input(&system_constraints_file, "system_constraints.sql");
set_input(&system_functions_file, "system_functions.sql");
+ set_input(&function_defaults_file, "function_defaults.sql");
set_input(&system_views_file, "system_views.sql");
if (show_setting || debug)
@@ -2827,6 +2829,7 @@ setup_data_file_paths(void)
check_input(features_file);
check_input(system_constraints_file);
check_input(system_functions_file);
+ check_input(function_defaults_file);
check_input(system_views_file);
}
@@ -3119,6 +3122,8 @@ initialize_data_directory(void)
setup_run_file(cmdfd, system_functions_file);
+ setup_run_file(cmdfd, function_defaults_file);
+
setup_depend(cmdfd);
/*
diff --git a/src/include/catalog/Makefile b/src/include/catalog/Makefile
index c90022f7c57..dbf67eb44dc 100644
--- a/src/include/catalog/Makefile
+++ b/src/include/catalog/Makefile
@@ -118,6 +118,7 @@ GENBKI_OUTPUT_FILES = \
$(GENERATED_HEADERS) \
postgres.bki \
system_constraints.sql \
+ function_defaults.sql \
schemapg.h \
syscache_ids.h \
syscache_info.h \
@@ -145,6 +146,7 @@ bki-stamp: $(top_srcdir)/src/backend/catalog/genbki.pl $(top_srcdir)/src/backend
install: all installdirs
$(INSTALL_DATA) postgres.bki '$(DESTDIR)$(datadir)/postgres.bki'
$(INSTALL_DATA) system_constraints.sql '$(DESTDIR)$(datadir)/system_constraints.sql'
+ $(INSTALL_DATA) function_defaults.sql '$(DESTDIR)$(datadir)/function_defaults.sql'
# In non-vpath builds, src/include/Makefile already installs all headers.
ifeq ($(vpath_build),yes)
$(INSTALL_DATA) schemapg.h '$(DESTDIR)$(includedir_server)'/catalog/schemapg.h
@@ -159,7 +161,7 @@ installdirs:
$(MKDIR_P) '$(DESTDIR)$(datadir)' '$(DESTDIR)$(includedir_server)'
uninstall:
- rm -f $(addprefix '$(DESTDIR)$(datadir)'/, postgres.bki system_constraints.sql)
+ rm -f $(addprefix '$(DESTDIR)$(datadir)'/, postgres.bki system_constraints.sql function_defaults.sql)
rm -f $(addprefix '$(DESTDIR)$(includedir_server)'/catalog/, schemapg.h syscache_ids.h system_fk_info.h $(GENERATED_HEADERS))
clean:
diff --git a/src/include/catalog/meson.build b/src/include/catalog/meson.build
index b63cd584068..e9968438a0d 100644
--- a/src/include/catalog/meson.build
+++ b/src/include/catalog/meson.build
@@ -105,12 +105,14 @@ input = []
output_files = [
'postgres.bki',
'system_constraints.sql',
+ 'function_defaults.sql',
'schemapg.h',
'syscache_ids.h',
'syscache_info.h',
'system_fk_info.h',
]
output_install = [
+ dir_data,
dir_data,
dir_data,
dir_include_server / 'catalog',
--
2.43.0
[text/x-patch] 0002-Move-function-defaults-from-system_functions.sql-to-.patch (25.6K, ../../[email protected]/3-0002-Move-function-defaults-from-system_functions.sql-to-.patch)
download | inline diff:
From 78a83084e7c18414bb7d4e5b1f44e960a2519ea9 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email protected]>
Date: Mon, 16 Feb 2026 11:58:22 -0500
Subject: [PATCH 2/2] Move function defaults from system_functions.sql to
pg_proc.dat
Functions using proargdflts (33 entries, 32 unique):
is_normalized, json_populate_record, json_populate_recordset,
json_strip_nulls, jsonb_insert, jsonb_path_exists, jsonb_path_exists_tz,
jsonb_path_match, jsonb_path_match_tz, jsonb_path_query,
jsonb_path_query_array, jsonb_path_query_array_tz, jsonb_path_query_first,
jsonb_path_query_first_tz, jsonb_path_query_tz, jsonb_set, jsonb_set_lax,
jsonb_strip_nulls, make_interval, normalize, parse_ident, pg_backup_start,
pg_backup_stop, pg_create_logical_replication_slot,
pg_create_physical_replication_slot, pg_logical_emit_message, pg_promote,
pg_replication_origin_session_setup, pg_stat_reset_shared,
pg_stat_reset_slru, pg_terminate_backend, random_normal
Functions using provariadicdflt (4 entries):
pg_logical_slot_get_binary_changes, pg_logical_slot_get_changes,
pg_logical_slot_peek_binary_changes, pg_logical_slot_peek_changes
---
src/backend/catalog/system_functions.sql | 282 -----------------------
src/include/catalog/pg_proc.dat | 86 ++++++-
2 files changed, 75 insertions(+), 293 deletions(-)
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index eb9e31ae1bf..8fa025be17b 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -66,13 +66,6 @@ CREATE OR REPLACE FUNCTION bit_length(text)
IMMUTABLE PARALLEL SAFE STRICT COST 1
RETURN octet_length($1) * 8;
-CREATE OR REPLACE FUNCTION
- random_normal(mean float8 DEFAULT 0, stddev float8 DEFAULT 1)
- RETURNS float8
- LANGUAGE internal
- VOLATILE PARALLEL RESTRICTED STRICT COST 1
-AS 'drandom_normal';
-
CREATE OR REPLACE FUNCTION log(numeric)
RETURNS numeric
LANGUAGE sql
@@ -382,281 +375,6 @@ CREATE OR REPLACE FUNCTION ts_debug(document text,
BEGIN ATOMIC
SELECT * FROM ts_debug(get_current_ts_config(), $1);
END;
-
-CREATE OR REPLACE FUNCTION
- pg_backup_start(label text, fast boolean DEFAULT false)
- RETURNS pg_lsn STRICT VOLATILE LANGUAGE internal AS 'pg_backup_start'
- PARALLEL RESTRICTED;
-
-CREATE OR REPLACE FUNCTION pg_backup_stop (
- wait_for_archive boolean DEFAULT true, OUT lsn pg_lsn,
- OUT labelfile text, OUT spcmapfile text)
- RETURNS record STRICT VOLATILE LANGUAGE internal as 'pg_backup_stop'
- PARALLEL RESTRICTED;
-
-CREATE OR REPLACE FUNCTION
- pg_promote(wait boolean DEFAULT true, wait_seconds integer DEFAULT 60)
- RETURNS boolean STRICT VOLATILE LANGUAGE INTERNAL AS 'pg_promote'
- PARALLEL SAFE;
-
-CREATE OR REPLACE FUNCTION
- pg_terminate_backend(pid integer, timeout int8 DEFAULT 0)
- RETURNS boolean STRICT VOLATILE LANGUAGE INTERNAL AS 'pg_terminate_backend'
- PARALLEL SAFE;
-
--- legacy definition for compatibility with 9.3
-CREATE OR REPLACE FUNCTION
- json_populate_record(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
- RETURNS anyelement LANGUAGE internal STABLE AS 'json_populate_record' PARALLEL SAFE;
-
--- legacy definition for compatibility with 9.3
-CREATE OR REPLACE FUNCTION
- json_populate_recordset(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
- RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'json_populate_recordset' PARALLEL SAFE;
-
-CREATE OR REPLACE FUNCTION pg_logical_slot_get_changes(
- IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
- OUT lsn pg_lsn, OUT xid xid, OUT data text)
-RETURNS SETOF RECORD
-LANGUAGE INTERNAL
-VOLATILE ROWS 1000 COST 1000
-AS 'pg_logical_slot_get_changes';
-
-CREATE OR REPLACE FUNCTION pg_logical_slot_peek_changes(
- IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
- OUT lsn pg_lsn, OUT xid xid, OUT data text)
-RETURNS SETOF RECORD
-LANGUAGE INTERNAL
-VOLATILE ROWS 1000 COST 1000
-AS 'pg_logical_slot_peek_changes';
-
-CREATE OR REPLACE FUNCTION pg_logical_slot_get_binary_changes(
- IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
- OUT lsn pg_lsn, OUT xid xid, OUT data bytea)
-RETURNS SETOF RECORD
-LANGUAGE INTERNAL
-VOLATILE ROWS 1000 COST 1000
-AS 'pg_logical_slot_get_binary_changes';
-
-CREATE OR REPLACE FUNCTION pg_logical_slot_peek_binary_changes(
- IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
- OUT lsn pg_lsn, OUT xid xid, OUT data bytea)
-RETURNS SETOF RECORD
-LANGUAGE INTERNAL
-VOLATILE ROWS 1000 COST 1000
-AS 'pg_logical_slot_peek_binary_changes';
-
-CREATE OR REPLACE FUNCTION pg_logical_emit_message(
- transactional boolean,
- prefix text,
- message text,
- flush boolean DEFAULT false)
-RETURNS pg_lsn
-LANGUAGE INTERNAL
-STRICT VOLATILE
-AS 'pg_logical_emit_message_text';
-
-CREATE OR REPLACE FUNCTION pg_logical_emit_message(
- transactional boolean,
- prefix text,
- message bytea,
- flush boolean DEFAULT false)
-RETURNS pg_lsn
-LANGUAGE INTERNAL
-STRICT VOLATILE
-AS 'pg_logical_emit_message_bytea';
-
-CREATE OR REPLACE FUNCTION pg_create_physical_replication_slot(
- IN slot_name name, IN immediately_reserve boolean DEFAULT false,
- IN temporary boolean DEFAULT false,
- OUT slot_name name, OUT lsn pg_lsn)
-RETURNS RECORD
-LANGUAGE INTERNAL
-STRICT VOLATILE
-AS 'pg_create_physical_replication_slot';
-
-CREATE OR REPLACE FUNCTION pg_create_logical_replication_slot(
- IN slot_name name, IN plugin name,
- IN temporary boolean DEFAULT false,
- IN twophase boolean DEFAULT false,
- IN failover boolean DEFAULT false,
- OUT slot_name name, OUT lsn pg_lsn)
-RETURNS RECORD
-LANGUAGE INTERNAL
-STRICT VOLATILE
-AS 'pg_create_logical_replication_slot';
-
-CREATE OR REPLACE FUNCTION
- make_interval(years int4 DEFAULT 0, months int4 DEFAULT 0, weeks int4 DEFAULT 0,
- days int4 DEFAULT 0, hours int4 DEFAULT 0, mins int4 DEFAULT 0,
- secs double precision DEFAULT 0.0)
-RETURNS interval
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'make_interval';
-
-CREATE OR REPLACE FUNCTION
- jsonb_set(jsonb_in jsonb, path text[] , replacement jsonb,
- create_if_missing boolean DEFAULT true)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_set';
-
-CREATE OR REPLACE FUNCTION
- jsonb_set_lax(jsonb_in jsonb, path text[] , replacement jsonb,
- create_if_missing boolean DEFAULT true,
- null_value_treatment text DEFAULT 'use_json_null')
-RETURNS jsonb
-LANGUAGE INTERNAL
-CALLED ON NULL INPUT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_set_lax';
-
-CREATE OR REPLACE FUNCTION
- parse_ident(str text, strict boolean DEFAULT true)
-RETURNS text[]
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'parse_ident';
-
-CREATE OR REPLACE FUNCTION
- jsonb_insert(jsonb_in jsonb, path text[] , replacement jsonb,
- insert_after boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_insert';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_exists(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS boolean
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_path_exists';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_match(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS boolean
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_path_match';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS SETOF jsonb
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_path_query';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query_array(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_path_query_array';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query_first(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_path_query_first';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_exists_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS boolean
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_path_exists_tz';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_match_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS boolean
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_path_match_tz';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS SETOF jsonb
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_path_query_tz';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query_array_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_path_query_array_tz';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query_first_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_path_query_first_tz';
-
-CREATE OR REPLACE FUNCTION
- jsonb_strip_nulls(target jsonb, strip_in_arrays boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_strip_nulls';
-
-CREATE OR REPLACE FUNCTION
- json_strip_nulls(target json, strip_in_arrays boolean DEFAULT false)
-RETURNS json
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'json_strip_nulls';
-
--- default normalization form is NFC, per SQL standard
-CREATE OR REPLACE FUNCTION
- "normalize"(text, text DEFAULT 'NFC')
-RETURNS text
-LANGUAGE internal
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'unicode_normalize_func';
-
-CREATE OR REPLACE FUNCTION
- is_normalized(text, text DEFAULT 'NFC')
-RETURNS boolean
-LANGUAGE internal
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'unicode_is_normalized';
-
-CREATE OR REPLACE FUNCTION
- pg_stat_reset_shared(target text DEFAULT NULL)
-RETURNS void
-LANGUAGE INTERNAL
-CALLED ON NULL INPUT VOLATILE PARALLEL SAFE
-AS 'pg_stat_reset_shared';
-
-CREATE OR REPLACE FUNCTION
- pg_stat_reset_slru(target text DEFAULT NULL)
-RETURNS void
-LANGUAGE INTERNAL
-CALLED ON NULL INPUT VOLATILE PARALLEL SAFE
-AS 'pg_stat_reset_slru';
-
-CREATE OR REPLACE FUNCTION
- pg_replication_origin_session_setup(node_name text, pid integer DEFAULT 0)
-RETURNS void
-LANGUAGE INTERNAL
-STRICT VOLATILE PARALLEL UNSAFE
-AS 'pg_replication_origin_session_setup';
-
--
-- The default permissions for functions mean that anyone can execute them.
-- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 83f6501df38..d59d61f4511 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3499,6 +3499,7 @@
{ oid => '6212', descr => 'random value from normal distribution',
proname => 'random_normal', provolatile => 'v', proparallel => 'r',
prorettype => 'float8', proargtypes => 'float8 float8',
+ proargnames => '{mean,stddev}', proargdflts => '{0,1}',
prosrc => 'drandom_normal' },
{ oid => '6339', descr => 'random integer in range',
proname => 'random', provolatile => 'v', proparallel => 'r',
@@ -6174,6 +6175,7 @@
descr => 'statistics: reset collected statistics shared across the cluster',
proname => 'pg_stat_reset_shared', proisstrict => 'f', provolatile => 'v',
prorettype => 'void', proargtypes => 'text',
+ proargnames => '{target}', proargdflts => '{NULL}',
prosrc => 'pg_stat_reset_shared' },
{ oid => '3776',
descr => 'statistics: reset collected statistics for a single table or index in the current database or shared across all databases in the cluster',
@@ -6193,6 +6195,7 @@
descr => 'statistics: reset collected statistics for a single SLRU',
proname => 'pg_stat_reset_slru', proisstrict => 'f', provolatile => 'v',
prorettype => 'void', proargtypes => 'text', proargnames => '{target}',
+ proargdflts => '{NULL}',
prosrc => 'pg_stat_reset_slru' },
{ oid => '6170',
descr => 'statistics: reset collected statistics for a single replication slot',
@@ -6728,20 +6731,24 @@
{ oid => '2096', descr => 'terminate a server process',
proname => 'pg_terminate_backend', provolatile => 'v', prorettype => 'bool',
proargtypes => 'int4 int8', proargnames => '{pid,timeout}',
+ proargdflts => '{0}',
prosrc => 'pg_terminate_backend' },
{ oid => '2172', descr => 'prepare for taking an online backup',
proname => 'pg_backup_start', provolatile => 'v', proparallel => 'r',
prorettype => 'pg_lsn', proargtypes => 'text bool',
+ proargnames => '{label,fast}', proargdflts => '{false}',
prosrc => 'pg_backup_start' },
{ oid => '2739', descr => 'finish taking an online backup',
proname => 'pg_backup_stop', provolatile => 'v', proparallel => 'r',
prorettype => 'record', proargtypes => 'bool',
proallargtypes => '{bool,pg_lsn,text,text}', proargmodes => '{i,o,o,o}',
proargnames => '{wait_for_archive,lsn,labelfile,spcmapfile}',
+ proargdflts => '{true}',
prosrc => 'pg_backup_stop' },
{ oid => '3436', descr => 'promote standby server',
proname => 'pg_promote', provolatile => 'v', prorettype => 'bool',
proargtypes => 'bool int4', proargnames => '{wait,wait_seconds}',
+ proargdflts => '{true,60}',
prosrc => 'pg_promote' },
{ oid => '2848', descr => 'switch to new wal file',
proname => 'pg_switch_wal', provolatile => 'v', prorettype => 'pg_lsn',
@@ -7517,7 +7524,8 @@
{ oid => '1268',
descr => 'parse qualified identifier to array of identifiers',
proname => 'parse_ident', prorettype => '_text', proargtypes => 'text bool',
- proargnames => '{str,strict}', prosrc => 'parse_ident' },
+ proargnames => '{str,strict}', proargdflts => '{true}',
+ prosrc => 'parse_ident' },
{ oid => '2246', descr => '(internal)',
proname => 'fmgr_internal_validator', provolatile => 's',
@@ -9423,7 +9431,9 @@
proargtypes => 'anyelement', prosrc => 'to_json' },
{ oid => '3261', descr => 'remove object fields with null values from json',
proname => 'json_strip_nulls', prorettype => 'json',
- proargtypes => 'json bool', prosrc => 'json_strip_nulls' },
+ proargtypes => 'json bool',
+ proargnames => '{target,strip_in_arrays}', proargdflts => '{false}',
+ prosrc => 'json_strip_nulls' },
{ oid => '3947',
proname => 'json_object_field', prorettype => 'json',
@@ -9480,12 +9490,17 @@
{ oid => '3960', descr => 'get record fields from a json object',
proname => 'json_populate_record', proisstrict => 'f', provolatile => 's',
prorettype => 'anyelement', proargtypes => 'anyelement json bool',
+ proargnames => '{base,from_json,use_json_as_text}',
+ proargdflts => '{false}',
prosrc => 'json_populate_record' },
{ oid => '3961',
descr => 'get set of records with fields from a json array of objects',
proname => 'json_populate_recordset', prorows => '100', proisstrict => 'f',
proretset => 't', provolatile => 's', prorettype => 'anyelement',
- proargtypes => 'anyelement json bool', prosrc => 'json_populate_recordset' },
+ proargtypes => 'anyelement json bool',
+ proargnames => '{base,from_json,use_json_as_text}',
+ proargdflts => '{false}',
+ prosrc => 'json_populate_recordset' },
{ oid => '3204', descr => 'get record fields from a json object',
proname => 'json_to_record', provolatile => 's', prorettype => 'record',
proargtypes => 'json', prosrc => 'json_to_record' },
@@ -10364,7 +10379,9 @@
prosrc => 'jsonb_build_object_noargs' },
{ oid => '3262', descr => 'remove object fields with null values from jsonb',
proname => 'jsonb_strip_nulls', prorettype => 'jsonb',
- proargtypes => 'jsonb bool', prosrc => 'jsonb_strip_nulls' },
+ proargtypes => 'jsonb bool',
+ proargnames => '{target,strip_in_arrays}', proargdflts => '{false}',
+ prosrc => 'jsonb_strip_nulls' },
{ oid => '3478',
proname => 'jsonb_object_field', prorettype => 'jsonb',
@@ -10538,16 +10555,25 @@
proargtypes => 'jsonb _text', prosrc => 'jsonb_delete_path' },
{ oid => '5054', descr => 'Set part of a jsonb, handle NULL value',
proname => 'jsonb_set_lax', proisstrict => 'f', prorettype => 'jsonb',
- proargtypes => 'jsonb _text jsonb bool text', prosrc => 'jsonb_set_lax' },
+ proargtypes => 'jsonb _text jsonb bool text',
+ proargnames => '{jsonb_in,path,replacement,create_if_missing,null_value_treatment}',
+ proargdflts => "{true,'use_json_null'}",
+ prosrc => 'jsonb_set_lax' },
{ oid => '3305', descr => 'Set part of a jsonb',
proname => 'jsonb_set', prorettype => 'jsonb',
- proargtypes => 'jsonb _text jsonb bool', prosrc => 'jsonb_set' },
+ proargtypes => 'jsonb _text jsonb bool',
+ proargnames => '{jsonb_in,path,replacement,create_if_missing}',
+ proargdflts => '{true}',
+ prosrc => 'jsonb_set' },
{ oid => '3306', descr => 'Indented text from jsonb',
proname => 'jsonb_pretty', prorettype => 'text', proargtypes => 'jsonb',
prosrc => 'jsonb_pretty' },
{ oid => '3579', descr => 'Insert value into a jsonb',
proname => 'jsonb_insert', prorettype => 'jsonb',
- proargtypes => 'jsonb _text jsonb bool', prosrc => 'jsonb_insert' },
+ proargtypes => 'jsonb _text jsonb bool',
+ proargnames => '{jsonb_in,path,replacement,insert_after}',
+ proargdflts => '{false}',
+ prosrc => 'jsonb_insert' },
# jsonpath
{ oid => '4001', descr => 'I/O',
@@ -10565,42 +10591,66 @@
{ oid => '4005', descr => 'jsonpath exists test',
proname => 'jsonb_path_exists', prorettype => 'bool',
- proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_exists' },
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdflts => "{'{}',false}",
+ prosrc => 'jsonb_path_exists' },
{ oid => '4006', descr => 'jsonpath query',
proname => 'jsonb_path_query', prorows => '1000', proretset => 't',
prorettype => 'jsonb', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdflts => "{'{}',false}",
prosrc => 'jsonb_path_query' },
{ oid => '4007', descr => 'jsonpath query wrapped into array',
proname => 'jsonb_path_query_array', prorettype => 'jsonb',
proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdflts => "{'{}',false}",
prosrc => 'jsonb_path_query_array' },
{ oid => '4008', descr => 'jsonpath query first item',
proname => 'jsonb_path_query_first', prorettype => 'jsonb',
proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdflts => "{'{}',false}",
prosrc => 'jsonb_path_query_first' },
{ oid => '4009', descr => 'jsonpath match',
proname => 'jsonb_path_match', prorettype => 'bool',
- proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_match' },
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdflts => "{'{}',false}",
+ prosrc => 'jsonb_path_match' },
{ oid => '1177', descr => 'jsonpath exists test with timezone',
proname => 'jsonb_path_exists_tz', provolatile => 's', prorettype => 'bool',
proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdflts => "{'{}',false}",
prosrc => 'jsonb_path_exists_tz' },
{ oid => '1179', descr => 'jsonpath query with timezone',
proname => 'jsonb_path_query_tz', prorows => '1000', proretset => 't',
provolatile => 's', prorettype => 'jsonb',
- proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_query_tz' },
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdflts => "{'{}',false}",
+ prosrc => 'jsonb_path_query_tz' },
{ oid => '1180', descr => 'jsonpath query wrapped into array with timezone',
proname => 'jsonb_path_query_array_tz', provolatile => 's',
prorettype => 'jsonb', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdflts => "{'{}',false}",
prosrc => 'jsonb_path_query_array_tz' },
{ oid => '2023', descr => 'jsonpath query first item with timezone',
proname => 'jsonb_path_query_first_tz', provolatile => 's',
prorettype => 'jsonb', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdflts => "{'{}',false}",
prosrc => 'jsonb_path_query_first_tz' },
{ oid => '2030', descr => 'jsonpath match with timezone',
proname => 'jsonb_path_match_tz', provolatile => 's', prorettype => 'bool',
- proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_match_tz' },
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdflts => "{'{}',false}",
+ prosrc => 'jsonb_path_match_tz' },
{ oid => '4010', descr => 'implementation of @? operator',
proname => 'jsonb_path_exists_opr', prorettype => 'bool',
@@ -11411,6 +11461,7 @@
proname => 'make_interval', prorettype => 'interval',
proargtypes => 'int4 int4 int4 int4 int4 int4 float8',
proargnames => '{years,months,weeks,days,hours,mins,secs}',
+ proargdflts => '{0,0,0,0,0,0,0.0}',
prosrc => 'make_interval' },
# spgist opclasses
@@ -11511,6 +11562,7 @@
proallargtypes => '{name,bool,bool,name,pg_lsn}',
proargmodes => '{i,i,i,o,o}',
proargnames => '{slot_name,immediately_reserve,temporary,slot_name,lsn}',
+ proargdflts => '{false,false}',
prosrc => 'pg_create_physical_replication_slot' },
{ oid => '4220',
descr => 'copy a physical replication slot, changing temporality',
@@ -11546,6 +11598,7 @@
proallargtypes => '{name,name,bool,bool,bool,name,pg_lsn}',
proargmodes => '{i,i,i,i,i,o,o}',
proargnames => '{slot_name,plugin,temporary,twophase,failover,slot_name,lsn}',
+ proargdflts => '{false,false,false}',
prosrc => 'pg_create_logical_replication_slot' },
{ oid => '4222',
descr => 'copy a logical replication slot, changing temporality and plugin',
@@ -11578,6 +11631,7 @@
proallargtypes => '{name,pg_lsn,int4,_text,pg_lsn,xid,text}',
proargmodes => '{i,i,i,v,o,o,o}',
proargnames => '{slot_name,upto_lsn,upto_nchanges,options,lsn,xid,data}',
+ provariadicdflt => "'{}'",
prosrc => 'pg_logical_slot_get_changes' },
{ oid => '3783', descr => 'get binary changes from replication slot',
proname => 'pg_logical_slot_get_binary_changes', procost => '1000',
@@ -11587,6 +11641,7 @@
proallargtypes => '{name,pg_lsn,int4,_text,pg_lsn,xid,bytea}',
proargmodes => '{i,i,i,v,o,o,o}',
proargnames => '{slot_name,upto_lsn,upto_nchanges,options,lsn,xid,data}',
+ provariadicdflt => "'{}'",
prosrc => 'pg_logical_slot_get_binary_changes' },
{ oid => '3784', descr => 'peek at changes from replication slot',
proname => 'pg_logical_slot_peek_changes', procost => '1000',
@@ -11596,6 +11651,7 @@
proallargtypes => '{name,pg_lsn,int4,_text,pg_lsn,xid,text}',
proargmodes => '{i,i,i,v,o,o,o}',
proargnames => '{slot_name,upto_lsn,upto_nchanges,options,lsn,xid,data}',
+ provariadicdflt => "'{}'",
prosrc => 'pg_logical_slot_peek_changes' },
{ oid => '3785', descr => 'peek at binary changes from replication slot',
proname => 'pg_logical_slot_peek_binary_changes', procost => '1000',
@@ -11605,6 +11661,7 @@
proallargtypes => '{name,pg_lsn,int4,_text,pg_lsn,xid,bytea}',
proargmodes => '{i,i,i,v,o,o,o}',
proargnames => '{slot_name,upto_lsn,upto_nchanges,options,lsn,xid,data}',
+ provariadicdflt => "'{}'",
prosrc => 'pg_logical_slot_peek_binary_changes' },
{ oid => '3878', descr => 'advance logical replication slot',
proname => 'pg_replication_slot_advance', provolatile => 'v',
@@ -11615,10 +11672,14 @@
{ oid => '3577', descr => 'emit a textual logical decoding message',
proname => 'pg_logical_emit_message', provolatile => 'v', proparallel => 'u',
prorettype => 'pg_lsn', proargtypes => 'bool text text bool',
+ proargnames => '{transactional,prefix,message,flush}',
+ proargdflts => '{false}',
prosrc => 'pg_logical_emit_message_text' },
{ oid => '3578', descr => 'emit a binary logical decoding message',
proname => 'pg_logical_emit_message', provolatile => 'v', proparallel => 'u',
prorettype => 'pg_lsn', proargtypes => 'bool text bytea bool',
+ proargnames => '{transactional,prefix,message,flush}',
+ proargdflts => '{false}',
prosrc => 'pg_logical_emit_message_bytea' },
{ oid => '6344',
descr => 'sync replication slots from the primary to the standby',
@@ -12268,6 +12329,7 @@
descr => 'configure session to maintain replication progress tracking for the passed in origin',
proname => 'pg_replication_origin_session_setup', provolatile => 'v',
proparallel => 'u', prorettype => 'void', proargtypes => 'text int4',
+ proargnames => '{node_name,pid}', proargdflts => '{0}',
prosrc => 'pg_replication_origin_session_setup' },
{ oid => '6007', descr => 'teardown configured replication progress tracking',
@@ -12518,10 +12580,12 @@
{ oid => '4350', descr => 'Unicode normalization',
proname => 'normalize', prorettype => 'text', proargtypes => 'text text',
+ proargnames => '{str,form}', proargdflts => "{'NFC'}",
prosrc => 'unicode_normalize_func' },
{ oid => '4351', descr => 'check Unicode normalization',
proname => 'is_normalized', prorettype => 'bool', proargtypes => 'text text',
+ proargnames => '{str,form}', proargdflts => "{'NFC'}",
prosrc => 'unicode_is_normalized' },
{ oid => '6198', descr => 'unescape Unicode characters',
--
2.43.0
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
@ 2026-02-16 18:07 ` Daniel Gustafsson <[email protected]>
2 siblings, 0 replies; 18+ messages in thread
From: Daniel Gustafsson @ 2026-02-16 18:07 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: pgsql-hackers
> On 16 Feb 2026, at 18:31, Andrew Dunstan <[email protected]> wrote:
> Motivated by Bug 19409 [1] I decided to do something about a wart that has bugged me for a while, namely the requirement to write stuff in system_views.sql if you need to specify default values for function arguments.
I haven't studied the patch yet, but a big +many on the idea. This has bugged
me many times.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
@ 2026-02-16 18:08 ` Corey Huinker <[email protected]>
2 siblings, 0 replies; 18+ messages in thread
From: Corey Huinker @ 2026-02-16 18:08 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: pgsql-hackers
On Mon, Feb 16, 2026 at 12:31 PM Andrew Dunstan <[email protected]> wrote:
>
> Motivated by Bug 19409 [1] I decided to do something about a wart that
> has bugged me for a while, namely the requirement to write stuff in
> system_views.sql if you need to specify default values for function
> arguments. Here's my attempt. The first patch here sets up the required
> infrastructure. genbki.pl creates a file called function_defaults.sql
> which is run by initdb at the appropriate time. There are two new fields
> in pg_proc.dat entries: proargdflts,and provariadicdflt. These are
> parsed and the appropriate CREATE OR REPLACE statement is generated and
> placed in function_defaults.sql. The second patch applies this treatment
> to 37 function definitions and removes the corresponding statements from
> system_views.sql. This gets us closer to having pg_proc.dat as a single
> source of truth.
+1 for the attempt. My preference would be for allowing CREATE OR REPLACE
to specify an oid, but that's a non-starter for bootstrapping purposes, so
this is the next best option.
The defaults read a little funny in that a human reader must line up the
defaults right-to-left to then determine a given parameter's default, if
any. For example:
proname => 'json_strip_nulls', prorettype => 'json',
- proargtypes => 'json bool', prosrc => 'json_strip_nulls' },
+ proargtypes => 'json bool',
+ proargnames => '{target,strip_in_arrays}', proargdflts => '{false}',
+ prosrc => 'json_strip_nulls' },
Perhaps we could require proargdflts to pre-pad with empty values
proargdflts => '{,false}'
or even be a hash
proargdflts => { strip_in_arrays => 'false' }
which I will grant you is wordy, but its definitely clearer.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
@ 2026-02-16 18:54 ` Andres Freund <[email protected]>
2026-02-16 19:13 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 23:19 ` Re: generating function default settings from pg_proc.dat Michael Paquier <[email protected]>
2 siblings, 2 replies; 18+ messages in thread
From: Andres Freund @ 2026-02-16 18:54 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: pgsql-hackers
Hi,
On 2026-02-16 12:31:37 -0500, Andrew Dunstan wrote:
> Motivated by Bug 19409 [1] I decided to do something about a wart that has
> bugged me for a while, namely the requirement to write stuff in
> system_views.sql if you need to specify default values for function
> arguments.
I'm one more person this has been bugging.
A related issue is that we have grants and revokes as part of
system_functions.sql. Perhaps it's worth tackling that at the same time?
> Here's my attempt. The first patch here sets up the required
> infrastructure. genbki.pl creates a file called function_defaults.sql which
> is run by initdb at the appropriate time.
Hm. I don't love that we first insert something into pg_proc, then generate a
full blown CREATE OR REPLACE FUNCTION with all the details to edit it. It
wouldn't take too much to somehow end up, down the line, with a silent
mismatch between what pg_proc.h contains and what CREATE OR REPLACE FUNCTION
ends up generating. I'm not sure we'd be likely to notice such a mismatch
immediately.
In fact, I think the current translation may have such an issue, the generated
CREATE OR REPLACE doesn't seem include ROWS, which seems to lead to resetting
prorows to the default 1000, regardless of what it was before.
DROP FUNCTION IF EXISTS foo();
CREATE FUNCTION foo() RETURNS SETOF int LANGUAGE SQL VOLATILE ROWS 10 AS $$SELECT generate_series(1, 10)$$;
SELECT prorows FROM pg_proc WHERE proname = 'foo';
┌─────────┐
│ prorows │
├─────────┤
│ 10 │
└─────────┘
CREATE OR REPLACE FUNCTION foo() RETURNS SETOF int LANGUAGE SQL VOLATILE AS $$SELECT generate_series(1, 10)$$;
SELECT prorows FROM pg_proc WHERE proname = 'foo';
┌─────────┐
│ prorows │
├─────────┤
│ 1000 │
└─────────┘
I wish we could just generate the parse-analyzed representation for default
values during bootstrap, but that's probably not realistic.
Although, I guess we don't really need the full machinery. Afaict we just need
a list of simple CONST nodes. There are two current functions (random_normal()
and pg_terminate_backend()), where the default expressions contains casts
implemented via FUNCEXPR, but that's just sloppiness in the DEFAULT
specification we probably should fix independently of everything else.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 18:54 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
@ 2026-02-16 19:13 ` Tom Lane <[email protected]>
2026-02-16 19:47 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
1 sibling, 1 reply; 18+ messages in thread
From: Tom Lane @ 2026-02-16 19:13 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; pgsql-hackers
Andres Freund <[email protected]> writes:
> I wish we could just generate the parse-analyzed representation for default
> values during bootstrap, but that's probably not realistic.
> Although, I guess we don't really need the full machinery. Afaict we just need
> a list of simple CONST nodes.
Const is enough to be problematic. In particular, the bytes of the
stored Datum are shown in physical order so that the results are
endian-dependent. We can't have machine dependencies in postgres.bki.
I suppose it might be possible to rethink the printed representation
of Const nodes to dodge that problem, but that's starting to make the
project seem rather complex. Even without that, hand-maintained
byte-level representations of jsonb, float8, text[] seem like a pretty
bad idea.
I think what we'd really want here is some smarts in backend bootstrap
mode to be able to invoke the correct datatype input function to
convert the type's standard string representation into a Datum.
I wonder how complicated that'd be.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 18:54 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 19:13 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
@ 2026-02-16 19:47 ` Andres Freund <[email protected]>
2026-02-16 20:02 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 23:39 ` Re: generating function default settings from pg_proc.dat Álvaro Herrera <[email protected]>
0 siblings, 2 replies; 18+ messages in thread
From: Andres Freund @ 2026-02-16 19:47 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; pgsql-hackers
Hi,
On 2026-02-16 14:13:45 -0500, Tom Lane wrote:
> Andres Freund <[email protected]> writes:
> > I wish we could just generate the parse-analyzed representation for default
> > values during bootstrap, but that's probably not realistic.
>
> > Although, I guess we don't really need the full machinery. Afaict we just need
> > a list of simple CONST nodes.
>
> Const is enough to be problematic. In particular, the bytes of the
> stored Datum are shown in physical order so that the results are
> endian-dependent. We can't have machine dependencies in postgres.bki.
I was more thinking we would teach bootstrap.c/bootparse.y to generate the
List(Const+) from a simpler representation that would be included in
postgres.bki, rather than include the node tree soup in postgres.bki.
> I think what we'd really want here is some smarts in backend bootstrap
> mode to be able to invoke the correct datatype input function to
> convert the type's standard string representation into a Datum.
> I wonder how complicated that'd be.
Seems we were thinking something roughly similar...
Looks like the slightly difficult bit is that we haven't assembled the pg_proc
row by the time we'd do the OidInputFunctionCall() in InsertOneValue(), so
we'd not trivially know the type of the corresponding column.
But if we made the input something like {'some'::type1, 'value'::type2}, we
wouldn't need to know the corresponding column's type, and genbki could build
it.
I'm starting to wonder if we shouldn't do something more bespoke for all
functions during bootstrap in general, rather than just for the argument
defaults.
Particularly for SRFs, I find it rather painful to keep proargtypes,
proallargtypes, proargmodes, proargnames in sync. Not helped by proargtypes
and proallargtypes/proargmodes/... having a different input syntax. I've
spent too much time trying to keep the arguments of stats functions in sync.
{ oid => '3786', descr => 'set up a logical replication slot',
proname => 'pg_create_logical_replication_slot', provolatile => 'v',
proparallel => 'u', prorettype => 'record',
proargtypes => 'name name bool bool bool',
proallargtypes => '{name,name,bool,bool,bool,name,pg_lsn}',
proargmodes => '{i,i,i,i,i,o,o}',
proargnames => '{slot_name,plugin,temporary,twophase,failover,slot_name,lsn}',
prosrc => 'pg_create_logical_replication_slot' },
+
CREATE OR REPLACE FUNCTION pg_create_logical_replication_slot(
IN slot_name name, IN plugin name,
IN temporary boolean DEFAULT false,
IN twophase boolean DEFAULT false,
IN failover boolean DEFAULT false,
OUT slot_name name, OUT lsn pg_lsn)
RETURNS RECORD
LANGUAGE INTERNAL
STRICT VOLATILE
AS 'pg_create_logical_replication_slot';
could be
{ oid => '3786', descr => 'set up a logical replication slot',
proname => 'pg_create_logical_replication_slot', provolatile => 'v',
proparallel => 'u',
proargs => [
{type => 'name', name => 'slot_name'},
{type => 'name', name => 'plugin'},
{type => 'bool', name => 'temporary', default => 'false'},
{type => 'bool', name => 'twophase', default => 'false'},
{type => 'bool', name => 'failover', default => 'false'},
],
prorettype => [
{type => 'name', name => 'slot_name'},
{type => 'pg_lsn', name => 'lsn'},
]
}
and then either turn that into something like the current representation, or
perhaps better, into a new top-level 'proc' bootstrap command, which then
would know how to interpret the default literals into the node soup.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 18:54 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 19:13 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 19:47 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
@ 2026-02-16 20:02 ` Tom Lane <[email protected]>
2026-02-16 20:31 ` Re: generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
1 sibling, 1 reply; 18+ messages in thread
From: Tom Lane @ 2026-02-16 20:02 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; pgsql-hackers
Andres Freund <[email protected]> writes:
> On 2026-02-16 14:13:45 -0500, Tom Lane wrote:
>> Const is enough to be problematic. In particular, the bytes of the
>> stored Datum are shown in physical order so that the results are
>> endian-dependent. We can't have machine dependencies in postgres.bki.
> I was more thinking we would teach bootstrap.c/bootparse.y to generate the
> List(Const+) from a simpler representation that would be included in
> postgres.bki, rather than include the node tree soup in postgres.bki.
Right, maintaining pg_node_tree strings is exactly what we don't want
to do.
> Looks like the slightly difficult bit is that we haven't assembled the pg_proc
> row by the time we'd do the OidInputFunctionCall() in InsertOneValue(), so
> we'd not trivially know the type of the corresponding column.
Ah, I'd not got far enough to notice that.
> But if we made the input something like {'some'::type1, 'value'::type2}, we
> wouldn't need to know the corresponding column's type, and genbki could build
> it.
Hmm. Your idea of a bespoke 'proc' command would avoid the need for
duplication, I think, although I'm not sure how to write that without
it becoming its own source of maintenance pain.
> Particularly for SRFs, I find it rather painful to keep proargtypes,
> proallargtypes, proargmodes, proargnames in sync. Not helped by proargtypes
> and proallargtypes/proargmodes/... having a different input syntax. I've
> spent too much time trying to keep the arguments of stats functions in sync.
Agreed, we could stand to do that better.
> proargs => [
> {type => 'name', name => 'slot_name'},
> {type => 'name', name => 'plugin'},
> {type => 'bool', name => 'temporary', default => 'false'},
> {type => 'bool', name => 'twophase', default => 'false'},
> {type => 'bool', name => 'failover', default => 'false'},
> ],
> prorettype => [
> {type => 'name', name => 'slot_name'},
> {type => 'pg_lsn', name => 'lsn'},
> ]
> }
I'd be inclined to keep prorettype separate from the output
arguments, but otherwise something like this seems attractive.
Who's going to work on this? I'm happy to take a swing at it,
but don't want to duplicate someone else's effort.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 18:54 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 19:13 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 19:47 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 20:02 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
@ 2026-02-16 20:31 ` Andrew Dunstan <[email protected]>
2026-02-16 21:46 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Andrew Dunstan @ 2026-02-16 20:31 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Andres Freund <[email protected]>; +Cc: pgsql-hackers
On 2026-02-16 Mo 3:02 PM, Tom Lane wrote:
> Andres Freund <[email protected]> writes:
>> On 2026-02-16 14:13:45 -0500, Tom Lane wrote:
>>> Const is enough to be problematic. In particular, the bytes of the
>>> stored Datum are shown in physical order so that the results are
>>> endian-dependent. We can't have machine dependencies in postgres.bki.
>> I was more thinking we would teach bootstrap.c/bootparse.y to generate the
>> List(Const+) from a simpler representation that would be included in
>> postgres.bki, rather than include the node tree soup in postgres.bki.
> Right, maintaining pg_node_tree strings is exactly what we don't want
> to do.
>
>> Looks like the slightly difficult bit is that we haven't assembled the pg_proc
>> row by the time we'd do the OidInputFunctionCall() in InsertOneValue(), so
>> we'd not trivially know the type of the corresponding column.
> Ah, I'd not got far enough to notice that.
>
>> But if we made the input something like {'some'::type1, 'value'::type2}, we
>> wouldn't need to know the corresponding column's type, and genbki could build
>> it.
> Hmm. Your idea of a bespoke 'proc' command would avoid the need for
> duplication, I think, although I'm not sure how to write that without
> it becoming its own source of maintenance pain.
>
>> Particularly for SRFs, I find it rather painful to keep proargtypes,
>> proallargtypes, proargmodes, proargnames in sync. Not helped by proargtypes
>> and proallargtypes/proargmodes/... having a different input syntax. I've
>> spent too much time trying to keep the arguments of stats functions in sync.
> Agreed, we could stand to do that better.
>
>> proargs => [
>> {type => 'name', name => 'slot_name'},
>> {type => 'name', name => 'plugin'},
>> {type => 'bool', name => 'temporary', default => 'false'},
>> {type => 'bool', name => 'twophase', default => 'false'},
>> {type => 'bool', name => 'failover', default => 'false'},
>> ],
>> prorettype => [
>> {type => 'name', name => 'slot_name'},
>> {type => 'pg_lsn', name => 'lsn'},
>> ]
>> }
> I'd be inclined to keep prorettype separate from the output
> arguments, but otherwise something like this seems attractive.
>
> Who's going to work on this? I'm happy to take a swing at it,
> but don't want to duplicate someone else's effort.
Go for it. I'm happy to review.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 18:54 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 19:13 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 19:47 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 20:02 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 20:31 ` Re: generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
@ 2026-02-16 21:46 ` Tom Lane <[email protected]>
2026-02-17 01:36 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2026-02-16 21:46 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: Andres Freund <[email protected]>; pgsql-hackers
Andrew Dunstan <[email protected]> writes:
> On 2026-02-16 Mo 3:02 PM, Tom Lane wrote:
>> Who's going to work on this? I'm happy to take a swing at it,
>> but don't want to duplicate someone else's effort.
> Go for it. I'm happy to review.
After a little bit of thought, I propose the following sketch
for what to do in the bootstrap backend:
In InsertOneValue(), add a special case for typoid == PG_NODE_TREEOID.
pg_node_tree_in() would just fail anyway so this isn't removing
any useful functionality. The special-case code would check which
column we are entering a value for and dispatch to appropriate code:
if (typoid == PG_NODE_TREEOID)
{
if (RelationGetRelid(boot_reldesc) == ProcedureRelationId &&
i == Anum_pg_proc_proargdefaults - 1)
values[i] = ConvertOneProargdefaultsValue(value);
else
... maybe other cases later
else
elog(ERROR, "can't handle pg_node_tree input");
return;
}
This framework would permit special-casing other catalog columns
in future, if we feel a need for that.
Andres was concerned about not having access to the other columns of
pg_proc, but I think that's mistaken: bootstrap.c's values[] array
should hold the already-parsed values of all earlier columns for the
current entry. So ISTM that we should have enough data to interpret
an input that just looks like an array of input-value strings and
construct a List of Const nodes from that, then flatten it to a
nodetree string.
So with that we'd have enough infrastructure to allow writing
something like
proargdefaults => '{1,2,true}'
This seems orthogonal to Andres' suggestion about refactoring
the pg_proc.dat representation of arguments to not be parallel
arrays but a list of hashes. I think that's likely a good
idea, but I don't want to implement it because I'm not a great
Perl programmer. Do you want to pick up that part?
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 18:54 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 19:13 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 19:47 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 20:02 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 20:31 ` Re: generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 21:46 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
@ 2026-02-17 01:36 ` Tom Lane <[email protected]>
2026-02-17 15:13 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2026-02-17 01:36 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: Andres Freund <[email protected]>; pgsql-hackers
I wrote:
> After a little bit of thought, I propose the following sketch
> for what to do in the bootstrap backend:
Here is a draft patch along those lines. I've verified that
the initial contents of pg_proc are exactly as before,
except that json[b]_strip_nulls gain the correct provolatile
value, and a few proargdefaults entries no longer involve
cast functions.
The changes in system_functions.sql and pg_proc.dat are nearly
verbatim from your 0002 patch, except that I had to adjust the
quoting conventions in some places because array_in does it
differently.
regards, tom lane
Attachments:
[text/x-diff] 0001-fill-proargdefaults-during-bootstrap.patch (35.4K, ../../[email protected]/2-0001-fill-proargdefaults-during-bootstrap.patch)
download | inline diff:
diff --git a/doc/src/sgml/bki.sgml b/doc/src/sgml/bki.sgml
index 53a982bf60d..0747db98fc0 100644
--- a/doc/src/sgml/bki.sgml
+++ b/doc/src/sgml/bki.sgml
@@ -271,6 +271,21 @@
</para>
</listitem>
+ <listitem>
+ <para>
+ There is a special case for values of the
+ <structname>pg_proc</structname>.<structfield>proargdefaults</structfield>
+ field, which is of type <type>pg_node_tree</type>. The real
+ contents of that type are too complex for hand-written entries,
+ but what we need for <structfield>proargdefaults</structfield> is
+ typically just a list of Const nodes. Therefore, the bootstrap
+ backend will interpret a value given for that field according to
+ text array syntax, and then feed the array element values to the
+ datatype input routines for the corresponding input parameters' data
+ types, and finally build Const nodes from the datums.
+ </para>
+ </listitem>
+
<listitem>
<para>
Since hashes are unordered data structures, field order and line
@@ -817,8 +832,10 @@ $ perl rewrite_dat_with_prokind.pl pg_proc.dat
The following column types are supported directly by
<filename>bootstrap.c</filename>: <type>bool</type>,
<type>bytea</type>, <type>char</type> (1 byte),
- <type>name</type>, <type>int2</type>,
- <type>int4</type>, <type>regproc</type>, <type>regclass</type>,
+ <type>int2</type>, <type>int4</type>, <type>int8</type>,
+ <type>float4</type>, <type>float8</type>,
+ <type>name</type>,
+ <type>regproc</type>, <type>regclass</type>,
<type>regtype</type>, <type>text</type>,
<type>oid</type>, <type>tid</type>, <type>xid</type>,
<type>cid</type>, <type>int2vector</type>, <type>oidvector</type>,
@@ -884,7 +901,7 @@ $ perl rewrite_dat_with_prokind.pl pg_proc.dat
<varlistentry>
<term>
- <literal>insert</literal> <literal>(</literal> <optional><replaceable class="parameter">oid_value</replaceable></optional> <replaceable class="parameter">value1</replaceable> <replaceable class="parameter">value2</replaceable> ... <literal>)</literal>
+ <literal>insert</literal> <literal>(</literal> <replaceable class="parameter">value1</replaceable> <replaceable class="parameter">value2</replaceable> ... <literal>)</literal>
</term>
<listitem>
@@ -902,6 +919,13 @@ $ perl rewrite_dat_with_prokind.pl pg_proc.dat
(To include a single quote in a value, write it twice.
Escape-string-style backslash escapes are allowed in the string, too.)
</para>
+
+ <para>
+ In most cases a <replaceable class="parameter">value</replaceable>
+ string is simply fed to the datatype input routine for the column's
+ data type, after de-quoting if needed. However there are exceptions
+ for certain fields, as detailed previously.
+ </para>
</listitem>
</varlistentry>
diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c
index 7d32cd0e159..36a3a9f774c 100644
--- a/src/backend/bootstrap/bootstrap.c
+++ b/src/backend/bootstrap/bootstrap.c
@@ -26,6 +26,7 @@
#include "bootstrap/bootstrap.h"
#include "catalog/index.h"
#include "catalog/pg_collation.h"
+#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "common/link-canary.h"
#include "miscadmin.h"
@@ -45,7 +46,9 @@
static void CheckerModeMain(void);
static void bootstrap_signals(void);
+static Oid boot_get_typcollation(Oid typid);
static Form_pg_attribute AllocateAttribute(void);
+static Datum ConvertOneProargdefaultsValue(char *value);
static void populate_typ_list(void);
static Oid gettype(char *type);
static void cleanup(void);
@@ -95,8 +98,12 @@ static const struct typinfo TypInfo[] = {
F_INT2IN, F_INT2OUT},
{"int4", INT4OID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
F_INT4IN, F_INT4OUT},
+ {"int8", INT8OID, 0, 8, true, TYPALIGN_DOUBLE, TYPSTORAGE_PLAIN, InvalidOid,
+ F_INT8IN, F_INT8OUT},
{"float4", FLOAT4OID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
F_FLOAT4IN, F_FLOAT4OUT},
+ {"float8", FLOAT8OID, 0, 8, true, TYPALIGN_DOUBLE, TYPSTORAGE_PLAIN, InvalidOid,
+ F_FLOAT8IN, F_FLOAT8OUT},
{"name", NAMEOID, CHAROID, NAMEDATALEN, false, TYPALIGN_CHAR, TYPSTORAGE_PLAIN, C_COLLATION_OID,
F_NAMEIN, F_NAMEOUT},
{"regclass", REGCLASSOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
@@ -123,6 +130,8 @@ static const struct typinfo TypInfo[] = {
F_XIDIN, F_XIDOUT},
{"cid", CIDOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
F_CIDIN, F_CIDOUT},
+ {"jsonb", JSONBOID, 0, -1, false, TYPALIGN_INT, TYPSTORAGE_EXTENDED, InvalidOid,
+ F_JSONB_IN, F_JSONB_OUT},
{"pg_node_tree", PG_NODE_TREEOID, 0, -1, false, TYPALIGN_INT, TYPSTORAGE_EXTENDED, DEFAULT_COLLATION_OID,
F_PG_NODE_TREE_IN, F_PG_NODE_TREE_OUT},
{"int2vector", INT2VECTOROID, INT2OID, -1, false, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
@@ -673,14 +682,32 @@ InsertOneValue(char *value, int i)
elog(DEBUG4, "inserting column %d value \"%s\"", i, value);
- typoid = TupleDescAttr(boot_reldesc->rd_att, i)->atttypid;
+ typoid = TupleDescAttr(RelationGetDescr(boot_reldesc), i)->atttypid;
boot_get_type_io_data(typoid,
&typlen, &typbyval, &typalign,
&typdelim, &typioparam,
&typinput, &typoutput);
- values[i] = OidInputFunctionCall(typinput, value, typioparam, -1);
+ /*
+ * pg_node_tree values can't be inserted normally (pg_node_tree_in would
+ * just error out), so provide special cases for such columns that we
+ * would like to fill during bootstrap.
+ */
+ if (typoid == PG_NODE_TREEOID)
+ {
+ /* pg_proc.proargdefaults */
+ if (RelationGetRelid(boot_reldesc) == ProcedureRelationId &&
+ i == Anum_pg_proc_proargdefaults - 1)
+ values[i] = ConvertOneProargdefaultsValue(value);
+ else /* maybe other cases later */
+ elog(ERROR, "can't handle pg_node_tree input");
+ }
+ else
+ {
+ /* Normal case */
+ values[i] = OidInputFunctionCall(typinput, value, typioparam, -1);
+ }
/*
* We use ereport not elog here so that parameters aren't evaluated unless
@@ -691,6 +718,110 @@ InsertOneValue(char *value, int i)
OidOutputFunctionCall(typoutput, values[i]))));
}
+/* ----------------
+ * ConvertOneProargdefaultsValue
+ *
+ * In general, proargdefaults can be a list of any expressions, but
+ * for bootstrap we only support a list of Const nodes. The input
+ * has the form of a text array, and we feed non-null elements to the
+ * typinput functions for the appropriate parameters.
+ * ----------------
+ */
+static Datum
+ConvertOneProargdefaultsValue(char *value)
+{
+ int pronargs;
+ oidvector *proargtypes;
+ Datum arrayval;
+ Datum *array_datums;
+ bool *array_nulls;
+ int array_count;
+ List *proargdefaults;
+
+ /* The pg_proc columns we need to use must have been filled already */
+ StaticAssertDecl(Anum_pg_proc_pronargs < Anum_pg_proc_proargdefaults,
+ "pronargs must come before proargdefaults");
+ StaticAssertDecl(Anum_pg_proc_pronargdefaults < Anum_pg_proc_proargdefaults,
+ "pronargdefaults must come before proargdefaults");
+ StaticAssertDecl(Anum_pg_proc_proargtypes < Anum_pg_proc_proargdefaults,
+ "proargtypes must come before proargdefaults");
+ if (Nulls[Anum_pg_proc_pronargs - 1])
+ elog(ERROR, "pronargs must not be null");
+ if (Nulls[Anum_pg_proc_proargtypes - 1])
+ elog(ERROR, "proargtypes must not be null");
+ pronargs = DatumGetInt16(values[Anum_pg_proc_pronargs - 1]);
+ proargtypes = DatumGetPointer(values[Anum_pg_proc_proargtypes - 1]);
+ Assert(pronargs == proargtypes->dim1);
+
+ /* Parse the input string as a text[] value, then deconstruct to Datums */
+ arrayval = OidFunctionCall3(F_ARRAY_IN,
+ CStringGetDatum(value),
+ ObjectIdGetDatum(TEXTOID),
+ Int32GetDatum(-1));
+ deconstruct_array_builtin(DatumGetArrayTypeP(arrayval), TEXTOID,
+ &array_datums, &array_nulls, &array_count);
+
+ /* The values should correspond to the last N argtypes */
+ if (array_count > pronargs)
+ elog(ERROR, "too many proargdefaults entries");
+
+ /* Build the List of Const nodes */
+ proargdefaults = NIL;
+ for (int i = 0; i < array_count; i++)
+ {
+ Oid argtype = proargtypes->values[pronargs - array_count + i];
+ int16 typlen;
+ bool typbyval;
+ char typalign;
+ char typdelim;
+ Oid typioparam;
+ Oid typinput;
+ Oid typoutput;
+ Oid typcollation;
+ Datum defval;
+ bool defnull;
+ Const *defConst;
+
+ boot_get_type_io_data(argtype,
+ &typlen, &typbyval, &typalign,
+ &typdelim, &typioparam,
+ &typinput, &typoutput);
+ typcollation = boot_get_typcollation(argtype);
+
+ defnull = array_nulls[i];
+ if (defnull)
+ defval = (Datum) 0;
+ else
+ {
+ char *defstr = text_to_cstring(DatumGetTextPP(array_datums[i]));
+
+ defval = OidInputFunctionCall(typinput, defstr, typioparam, -1);
+ }
+
+ defConst = makeConst(argtype,
+ -1, /* never any typmod */
+ typcollation,
+ typlen,
+ defval,
+ defnull,
+ typbyval);
+ proargdefaults = lappend(proargdefaults, defConst);
+ }
+
+ /*
+ * Hack: fill in pronargdefaults with the right value. This is surely
+ * ugly, but it beats making the programmer do it.
+ */
+ values[Anum_pg_proc_pronargdefaults - 1] = Int16GetDatum(array_count);
+ Nulls[Anum_pg_proc_pronargdefaults - 1] = false;
+
+ /*
+ * Flatten the List to a node-tree string, then convert to a text datum,
+ * which is the storage representation of pg_node_tree.
+ */
+ return CStringGetTextDatum(nodeToString(proargdefaults));
+}
+
/* ----------------
* InsertOneNull
* ----------------
@@ -907,6 +1038,53 @@ boot_get_type_io_data(Oid typid,
}
}
+/* ----------------
+ * boot_get_typcollation
+ *
+ * Obtain type's collation at bootstrap time. This intentionally has
+ * the same API as lsyscache.c's get_typcollation.
+ *
+ * XXX would it be better to add another output to boot_get_type_io_data?
+ * ----------------
+ */
+static Oid
+boot_get_typcollation(Oid typid)
+{
+ if (Typ != NIL)
+ {
+ /* We have the boot-time contents of pg_type, so use it */
+ struct typmap *ap = NULL;
+ ListCell *lc;
+
+ foreach(lc, Typ)
+ {
+ ap = lfirst(lc);
+ if (ap->am_oid == typid)
+ break;
+ }
+
+ if (!ap || ap->am_oid != typid)
+ elog(ERROR, "type OID %u not found in Typ list", typid);
+
+ return ap->am_typ.typcollation;
+ }
+ else
+ {
+ /* We don't have pg_type yet, so use the hard-wired TypInfo array */
+ int typeindex;
+
+ for (typeindex = 0; typeindex < n_types; typeindex++)
+ {
+ if (TypInfo[typeindex].oid == typid)
+ break;
+ }
+ if (typeindex >= n_types)
+ elog(ERROR, "type OID %u not found in TypInfo", typid);
+
+ return TypInfo[typeindex].collation;
+ }
+}
+
/* ----------------
* AllocateAttribute
*
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index eb9e31ae1bf..4c20bb380a4 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -7,7 +7,7 @@
*
* This file redefines certain built-in functions that are impractical
* to fully define in pg_proc.dat. In most cases that's because they use
- * SQL-standard function bodies and/or default expressions. The node
+ * SQL-standard function bodies. The node
* tree representations of those are too unreadable, platform-dependent,
* and changeable to want to deal with them manually. Hence, we put stub
* definitions of such functions into pg_proc.dat and then replace them
@@ -66,13 +66,6 @@ CREATE OR REPLACE FUNCTION bit_length(text)
IMMUTABLE PARALLEL SAFE STRICT COST 1
RETURN octet_length($1) * 8;
-CREATE OR REPLACE FUNCTION
- random_normal(mean float8 DEFAULT 0, stddev float8 DEFAULT 1)
- RETURNS float8
- LANGUAGE internal
- VOLATILE PARALLEL RESTRICTED STRICT COST 1
-AS 'drandom_normal';
-
CREATE OR REPLACE FUNCTION log(numeric)
RETURNS numeric
LANGUAGE sql
@@ -382,281 +375,6 @@ CREATE OR REPLACE FUNCTION ts_debug(document text,
BEGIN ATOMIC
SELECT * FROM ts_debug(get_current_ts_config(), $1);
END;
-
-CREATE OR REPLACE FUNCTION
- pg_backup_start(label text, fast boolean DEFAULT false)
- RETURNS pg_lsn STRICT VOLATILE LANGUAGE internal AS 'pg_backup_start'
- PARALLEL RESTRICTED;
-
-CREATE OR REPLACE FUNCTION pg_backup_stop (
- wait_for_archive boolean DEFAULT true, OUT lsn pg_lsn,
- OUT labelfile text, OUT spcmapfile text)
- RETURNS record STRICT VOLATILE LANGUAGE internal as 'pg_backup_stop'
- PARALLEL RESTRICTED;
-
-CREATE OR REPLACE FUNCTION
- pg_promote(wait boolean DEFAULT true, wait_seconds integer DEFAULT 60)
- RETURNS boolean STRICT VOLATILE LANGUAGE INTERNAL AS 'pg_promote'
- PARALLEL SAFE;
-
-CREATE OR REPLACE FUNCTION
- pg_terminate_backend(pid integer, timeout int8 DEFAULT 0)
- RETURNS boolean STRICT VOLATILE LANGUAGE INTERNAL AS 'pg_terminate_backend'
- PARALLEL SAFE;
-
--- legacy definition for compatibility with 9.3
-CREATE OR REPLACE FUNCTION
- json_populate_record(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
- RETURNS anyelement LANGUAGE internal STABLE AS 'json_populate_record' PARALLEL SAFE;
-
--- legacy definition for compatibility with 9.3
-CREATE OR REPLACE FUNCTION
- json_populate_recordset(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
- RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'json_populate_recordset' PARALLEL SAFE;
-
-CREATE OR REPLACE FUNCTION pg_logical_slot_get_changes(
- IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
- OUT lsn pg_lsn, OUT xid xid, OUT data text)
-RETURNS SETOF RECORD
-LANGUAGE INTERNAL
-VOLATILE ROWS 1000 COST 1000
-AS 'pg_logical_slot_get_changes';
-
-CREATE OR REPLACE FUNCTION pg_logical_slot_peek_changes(
- IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
- OUT lsn pg_lsn, OUT xid xid, OUT data text)
-RETURNS SETOF RECORD
-LANGUAGE INTERNAL
-VOLATILE ROWS 1000 COST 1000
-AS 'pg_logical_slot_peek_changes';
-
-CREATE OR REPLACE FUNCTION pg_logical_slot_get_binary_changes(
- IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
- OUT lsn pg_lsn, OUT xid xid, OUT data bytea)
-RETURNS SETOF RECORD
-LANGUAGE INTERNAL
-VOLATILE ROWS 1000 COST 1000
-AS 'pg_logical_slot_get_binary_changes';
-
-CREATE OR REPLACE FUNCTION pg_logical_slot_peek_binary_changes(
- IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
- OUT lsn pg_lsn, OUT xid xid, OUT data bytea)
-RETURNS SETOF RECORD
-LANGUAGE INTERNAL
-VOLATILE ROWS 1000 COST 1000
-AS 'pg_logical_slot_peek_binary_changes';
-
-CREATE OR REPLACE FUNCTION pg_logical_emit_message(
- transactional boolean,
- prefix text,
- message text,
- flush boolean DEFAULT false)
-RETURNS pg_lsn
-LANGUAGE INTERNAL
-STRICT VOLATILE
-AS 'pg_logical_emit_message_text';
-
-CREATE OR REPLACE FUNCTION pg_logical_emit_message(
- transactional boolean,
- prefix text,
- message bytea,
- flush boolean DEFAULT false)
-RETURNS pg_lsn
-LANGUAGE INTERNAL
-STRICT VOLATILE
-AS 'pg_logical_emit_message_bytea';
-
-CREATE OR REPLACE FUNCTION pg_create_physical_replication_slot(
- IN slot_name name, IN immediately_reserve boolean DEFAULT false,
- IN temporary boolean DEFAULT false,
- OUT slot_name name, OUT lsn pg_lsn)
-RETURNS RECORD
-LANGUAGE INTERNAL
-STRICT VOLATILE
-AS 'pg_create_physical_replication_slot';
-
-CREATE OR REPLACE FUNCTION pg_create_logical_replication_slot(
- IN slot_name name, IN plugin name,
- IN temporary boolean DEFAULT false,
- IN twophase boolean DEFAULT false,
- IN failover boolean DEFAULT false,
- OUT slot_name name, OUT lsn pg_lsn)
-RETURNS RECORD
-LANGUAGE INTERNAL
-STRICT VOLATILE
-AS 'pg_create_logical_replication_slot';
-
-CREATE OR REPLACE FUNCTION
- make_interval(years int4 DEFAULT 0, months int4 DEFAULT 0, weeks int4 DEFAULT 0,
- days int4 DEFAULT 0, hours int4 DEFAULT 0, mins int4 DEFAULT 0,
- secs double precision DEFAULT 0.0)
-RETURNS interval
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'make_interval';
-
-CREATE OR REPLACE FUNCTION
- jsonb_set(jsonb_in jsonb, path text[] , replacement jsonb,
- create_if_missing boolean DEFAULT true)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_set';
-
-CREATE OR REPLACE FUNCTION
- jsonb_set_lax(jsonb_in jsonb, path text[] , replacement jsonb,
- create_if_missing boolean DEFAULT true,
- null_value_treatment text DEFAULT 'use_json_null')
-RETURNS jsonb
-LANGUAGE INTERNAL
-CALLED ON NULL INPUT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_set_lax';
-
-CREATE OR REPLACE FUNCTION
- parse_ident(str text, strict boolean DEFAULT true)
-RETURNS text[]
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'parse_ident';
-
-CREATE OR REPLACE FUNCTION
- jsonb_insert(jsonb_in jsonb, path text[] , replacement jsonb,
- insert_after boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_insert';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_exists(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS boolean
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_path_exists';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_match(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS boolean
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_path_match';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS SETOF jsonb
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_path_query';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query_array(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_path_query_array';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query_first(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_path_query_first';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_exists_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS boolean
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_path_exists_tz';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_match_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS boolean
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_path_match_tz';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS SETOF jsonb
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_path_query_tz';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query_array_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_path_query_array_tz';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query_first_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_path_query_first_tz';
-
-CREATE OR REPLACE FUNCTION
- jsonb_strip_nulls(target jsonb, strip_in_arrays boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_strip_nulls';
-
-CREATE OR REPLACE FUNCTION
- json_strip_nulls(target json, strip_in_arrays boolean DEFAULT false)
-RETURNS json
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'json_strip_nulls';
-
--- default normalization form is NFC, per SQL standard
-CREATE OR REPLACE FUNCTION
- "normalize"(text, text DEFAULT 'NFC')
-RETURNS text
-LANGUAGE internal
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'unicode_normalize_func';
-
-CREATE OR REPLACE FUNCTION
- is_normalized(text, text DEFAULT 'NFC')
-RETURNS boolean
-LANGUAGE internal
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'unicode_is_normalized';
-
-CREATE OR REPLACE FUNCTION
- pg_stat_reset_shared(target text DEFAULT NULL)
-RETURNS void
-LANGUAGE INTERNAL
-CALLED ON NULL INPUT VOLATILE PARALLEL SAFE
-AS 'pg_stat_reset_shared';
-
-CREATE OR REPLACE FUNCTION
- pg_stat_reset_slru(target text DEFAULT NULL)
-RETURNS void
-LANGUAGE INTERNAL
-CALLED ON NULL INPUT VOLATILE PARALLEL SAFE
-AS 'pg_stat_reset_slru';
-
-CREATE OR REPLACE FUNCTION
- pg_replication_origin_session_setup(node_name text, pid integer DEFAULT 0)
-RETURNS void
-LANGUAGE INTERNAL
-STRICT VOLATILE PARALLEL UNSAFE
-AS 'pg_replication_origin_session_setup';
-
--
-- The default permissions for functions mean that anyone can execute them.
-- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 83f6501df38..dac40992cbc 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3499,6 +3499,7 @@
{ oid => '6212', descr => 'random value from normal distribution',
proname => 'random_normal', provolatile => 'v', proparallel => 'r',
prorettype => 'float8', proargtypes => 'float8 float8',
+ proargnames => '{mean,stddev}', proargdefaults => '{0,1}',
prosrc => 'drandom_normal' },
{ oid => '6339', descr => 'random integer in range',
proname => 'random', provolatile => 'v', proparallel => 'r',
@@ -6174,6 +6175,7 @@
descr => 'statistics: reset collected statistics shared across the cluster',
proname => 'pg_stat_reset_shared', proisstrict => 'f', provolatile => 'v',
prorettype => 'void', proargtypes => 'text',
+ proargnames => '{target}', proargdefaults => '{NULL}',
prosrc => 'pg_stat_reset_shared' },
{ oid => '3776',
descr => 'statistics: reset collected statistics for a single table or index in the current database or shared across all databases in the cluster',
@@ -6193,6 +6195,7 @@
descr => 'statistics: reset collected statistics for a single SLRU',
proname => 'pg_stat_reset_slru', proisstrict => 'f', provolatile => 'v',
prorettype => 'void', proargtypes => 'text', proargnames => '{target}',
+ proargdefaults => '{NULL}',
prosrc => 'pg_stat_reset_slru' },
{ oid => '6170',
descr => 'statistics: reset collected statistics for a single replication slot',
@@ -6728,20 +6731,24 @@
{ oid => '2096', descr => 'terminate a server process',
proname => 'pg_terminate_backend', provolatile => 'v', prorettype => 'bool',
proargtypes => 'int4 int8', proargnames => '{pid,timeout}',
+ proargdefaults => '{0}',
prosrc => 'pg_terminate_backend' },
{ oid => '2172', descr => 'prepare for taking an online backup',
proname => 'pg_backup_start', provolatile => 'v', proparallel => 'r',
prorettype => 'pg_lsn', proargtypes => 'text bool',
+ proargnames => '{label,fast}', proargdefaults => '{false}',
prosrc => 'pg_backup_start' },
{ oid => '2739', descr => 'finish taking an online backup',
proname => 'pg_backup_stop', provolatile => 'v', proparallel => 'r',
prorettype => 'record', proargtypes => 'bool',
proallargtypes => '{bool,pg_lsn,text,text}', proargmodes => '{i,o,o,o}',
proargnames => '{wait_for_archive,lsn,labelfile,spcmapfile}',
+ proargdefaults => '{true}',
prosrc => 'pg_backup_stop' },
{ oid => '3436', descr => 'promote standby server',
proname => 'pg_promote', provolatile => 'v', prorettype => 'bool',
proargtypes => 'bool int4', proargnames => '{wait,wait_seconds}',
+ proargdefaults => '{true,60}',
prosrc => 'pg_promote' },
{ oid => '2848', descr => 'switch to new wal file',
proname => 'pg_switch_wal', provolatile => 'v', prorettype => 'pg_lsn',
@@ -7517,7 +7524,8 @@
{ oid => '1268',
descr => 'parse qualified identifier to array of identifiers',
proname => 'parse_ident', prorettype => '_text', proargtypes => 'text bool',
- proargnames => '{str,strict}', prosrc => 'parse_ident' },
+ proargnames => '{str,strict}', proargdefaults => '{true}',
+ prosrc => 'parse_ident' },
{ oid => '2246', descr => '(internal)',
proname => 'fmgr_internal_validator', provolatile => 's',
@@ -9423,7 +9431,9 @@
proargtypes => 'anyelement', prosrc => 'to_json' },
{ oid => '3261', descr => 'remove object fields with null values from json',
proname => 'json_strip_nulls', prorettype => 'json',
- proargtypes => 'json bool', prosrc => 'json_strip_nulls' },
+ proargtypes => 'json bool',
+ proargnames => '{target,strip_in_arrays}', proargdefaults => '{false}',
+ prosrc => 'json_strip_nulls' },
{ oid => '3947',
proname => 'json_object_field', prorettype => 'json',
@@ -9480,12 +9490,17 @@
{ oid => '3960', descr => 'get record fields from a json object',
proname => 'json_populate_record', proisstrict => 'f', provolatile => 's',
prorettype => 'anyelement', proargtypes => 'anyelement json bool',
+ proargnames => '{base,from_json,use_json_as_text}',
+ proargdefaults => '{false}',
prosrc => 'json_populate_record' },
{ oid => '3961',
descr => 'get set of records with fields from a json array of objects',
proname => 'json_populate_recordset', prorows => '100', proisstrict => 'f',
proretset => 't', provolatile => 's', prorettype => 'anyelement',
- proargtypes => 'anyelement json bool', prosrc => 'json_populate_recordset' },
+ proargtypes => 'anyelement json bool',
+ proargnames => '{base,from_json,use_json_as_text}',
+ proargdefaults => '{false}',
+ prosrc => 'json_populate_recordset' },
{ oid => '3204', descr => 'get record fields from a json object',
proname => 'json_to_record', provolatile => 's', prorettype => 'record',
proargtypes => 'json', prosrc => 'json_to_record' },
@@ -10364,7 +10379,9 @@
prosrc => 'jsonb_build_object_noargs' },
{ oid => '3262', descr => 'remove object fields with null values from jsonb',
proname => 'jsonb_strip_nulls', prorettype => 'jsonb',
- proargtypes => 'jsonb bool', prosrc => 'jsonb_strip_nulls' },
+ proargtypes => 'jsonb bool',
+ proargnames => '{target,strip_in_arrays}', proargdefaults => '{false}',
+ prosrc => 'jsonb_strip_nulls' },
{ oid => '3478',
proname => 'jsonb_object_field', prorettype => 'jsonb',
@@ -10538,16 +10555,25 @@
proargtypes => 'jsonb _text', prosrc => 'jsonb_delete_path' },
{ oid => '5054', descr => 'Set part of a jsonb, handle NULL value',
proname => 'jsonb_set_lax', proisstrict => 'f', prorettype => 'jsonb',
- proargtypes => 'jsonb _text jsonb bool text', prosrc => 'jsonb_set_lax' },
+ proargtypes => 'jsonb _text jsonb bool text',
+ proargnames => '{jsonb_in,path,replacement,create_if_missing,null_value_treatment}',
+ proargdefaults => '{true,use_json_null}',
+ prosrc => 'jsonb_set_lax' },
{ oid => '3305', descr => 'Set part of a jsonb',
proname => 'jsonb_set', prorettype => 'jsonb',
- proargtypes => 'jsonb _text jsonb bool', prosrc => 'jsonb_set' },
+ proargtypes => 'jsonb _text jsonb bool',
+ proargnames => '{jsonb_in,path,replacement,create_if_missing}',
+ proargdefaults => '{true}',
+ prosrc => 'jsonb_set' },
{ oid => '3306', descr => 'Indented text from jsonb',
proname => 'jsonb_pretty', prorettype => 'text', proargtypes => 'jsonb',
prosrc => 'jsonb_pretty' },
{ oid => '3579', descr => 'Insert value into a jsonb',
proname => 'jsonb_insert', prorettype => 'jsonb',
- proargtypes => 'jsonb _text jsonb bool', prosrc => 'jsonb_insert' },
+ proargtypes => 'jsonb _text jsonb bool',
+ proargnames => '{jsonb_in,path,replacement,insert_after}',
+ proargdefaults => '{false}',
+ prosrc => 'jsonb_insert' },
# jsonpath
{ oid => '4001', descr => 'I/O',
@@ -10565,42 +10591,66 @@
{ oid => '4005', descr => 'jsonpath exists test',
proname => 'jsonb_path_exists', prorettype => 'bool',
- proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_exists' },
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
+ prosrc => 'jsonb_path_exists' },
{ oid => '4006', descr => 'jsonpath query',
proname => 'jsonb_path_query', prorows => '1000', proretset => 't',
prorettype => 'jsonb', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
prosrc => 'jsonb_path_query' },
{ oid => '4007', descr => 'jsonpath query wrapped into array',
proname => 'jsonb_path_query_array', prorettype => 'jsonb',
proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
prosrc => 'jsonb_path_query_array' },
{ oid => '4008', descr => 'jsonpath query first item',
proname => 'jsonb_path_query_first', prorettype => 'jsonb',
proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
prosrc => 'jsonb_path_query_first' },
{ oid => '4009', descr => 'jsonpath match',
proname => 'jsonb_path_match', prorettype => 'bool',
- proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_match' },
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
+ prosrc => 'jsonb_path_match' },
{ oid => '1177', descr => 'jsonpath exists test with timezone',
proname => 'jsonb_path_exists_tz', provolatile => 's', prorettype => 'bool',
proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
prosrc => 'jsonb_path_exists_tz' },
{ oid => '1179', descr => 'jsonpath query with timezone',
proname => 'jsonb_path_query_tz', prorows => '1000', proretset => 't',
provolatile => 's', prorettype => 'jsonb',
- proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_query_tz' },
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
+ prosrc => 'jsonb_path_query_tz' },
{ oid => '1180', descr => 'jsonpath query wrapped into array with timezone',
proname => 'jsonb_path_query_array_tz', provolatile => 's',
prorettype => 'jsonb', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
prosrc => 'jsonb_path_query_array_tz' },
{ oid => '2023', descr => 'jsonpath query first item with timezone',
proname => 'jsonb_path_query_first_tz', provolatile => 's',
prorettype => 'jsonb', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
prosrc => 'jsonb_path_query_first_tz' },
{ oid => '2030', descr => 'jsonpath match with timezone',
proname => 'jsonb_path_match_tz', provolatile => 's', prorettype => 'bool',
- proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_match_tz' },
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
+ prosrc => 'jsonb_path_match_tz' },
{ oid => '4010', descr => 'implementation of @? operator',
proname => 'jsonb_path_exists_opr', prorettype => 'bool',
@@ -11411,6 +11461,7 @@
proname => 'make_interval', prorettype => 'interval',
proargtypes => 'int4 int4 int4 int4 int4 int4 float8',
proargnames => '{years,months,weeks,days,hours,mins,secs}',
+ proargdefaults => '{0,0,0,0,0,0,0.0}',
prosrc => 'make_interval' },
# spgist opclasses
@@ -11511,6 +11562,7 @@
proallargtypes => '{name,bool,bool,name,pg_lsn}',
proargmodes => '{i,i,i,o,o}',
proargnames => '{slot_name,immediately_reserve,temporary,slot_name,lsn}',
+ proargdefaults => '{false,false}',
prosrc => 'pg_create_physical_replication_slot' },
{ oid => '4220',
descr => 'copy a physical replication slot, changing temporality',
@@ -11546,6 +11598,7 @@
proallargtypes => '{name,name,bool,bool,bool,name,pg_lsn}',
proargmodes => '{i,i,i,i,i,o,o}',
proargnames => '{slot_name,plugin,temporary,twophase,failover,slot_name,lsn}',
+ proargdefaults => '{false,false,false}',
prosrc => 'pg_create_logical_replication_slot' },
{ oid => '4222',
descr => 'copy a logical replication slot, changing temporality and plugin',
@@ -11578,6 +11631,7 @@
proallargtypes => '{name,pg_lsn,int4,_text,pg_lsn,xid,text}',
proargmodes => '{i,i,i,v,o,o,o}',
proargnames => '{slot_name,upto_lsn,upto_nchanges,options,lsn,xid,data}',
+ proargdefaults => '{"{}"}',
prosrc => 'pg_logical_slot_get_changes' },
{ oid => '3783', descr => 'get binary changes from replication slot',
proname => 'pg_logical_slot_get_binary_changes', procost => '1000',
@@ -11587,6 +11641,7 @@
proallargtypes => '{name,pg_lsn,int4,_text,pg_lsn,xid,bytea}',
proargmodes => '{i,i,i,v,o,o,o}',
proargnames => '{slot_name,upto_lsn,upto_nchanges,options,lsn,xid,data}',
+ proargdefaults => '{"{}"}',
prosrc => 'pg_logical_slot_get_binary_changes' },
{ oid => '3784', descr => 'peek at changes from replication slot',
proname => 'pg_logical_slot_peek_changes', procost => '1000',
@@ -11596,6 +11651,7 @@
proallargtypes => '{name,pg_lsn,int4,_text,pg_lsn,xid,text}',
proargmodes => '{i,i,i,v,o,o,o}',
proargnames => '{slot_name,upto_lsn,upto_nchanges,options,lsn,xid,data}',
+ proargdefaults => '{"{}"}',
prosrc => 'pg_logical_slot_peek_changes' },
{ oid => '3785', descr => 'peek at binary changes from replication slot',
proname => 'pg_logical_slot_peek_binary_changes', procost => '1000',
@@ -11605,6 +11661,7 @@
proallargtypes => '{name,pg_lsn,int4,_text,pg_lsn,xid,bytea}',
proargmodes => '{i,i,i,v,o,o,o}',
proargnames => '{slot_name,upto_lsn,upto_nchanges,options,lsn,xid,data}',
+ proargdefaults => '{"{}"}',
prosrc => 'pg_logical_slot_peek_binary_changes' },
{ oid => '3878', descr => 'advance logical replication slot',
proname => 'pg_replication_slot_advance', provolatile => 'v',
@@ -11615,10 +11672,14 @@
{ oid => '3577', descr => 'emit a textual logical decoding message',
proname => 'pg_logical_emit_message', provolatile => 'v', proparallel => 'u',
prorettype => 'pg_lsn', proargtypes => 'bool text text bool',
+ proargnames => '{transactional,prefix,message,flush}',
+ proargdefaults => '{false}',
prosrc => 'pg_logical_emit_message_text' },
{ oid => '3578', descr => 'emit a binary logical decoding message',
proname => 'pg_logical_emit_message', provolatile => 'v', proparallel => 'u',
prorettype => 'pg_lsn', proargtypes => 'bool text bytea bool',
+ proargnames => '{transactional,prefix,message,flush}',
+ proargdefaults => '{false}',
prosrc => 'pg_logical_emit_message_bytea' },
{ oid => '6344',
descr => 'sync replication slots from the primary to the standby',
@@ -12268,6 +12329,7 @@
descr => 'configure session to maintain replication progress tracking for the passed in origin',
proname => 'pg_replication_origin_session_setup', provolatile => 'v',
proparallel => 'u', prorettype => 'void', proargtypes => 'text int4',
+ proargnames => '{node_name,pid}', proargdefaults => '{0}',
prosrc => 'pg_replication_origin_session_setup' },
{ oid => '6007', descr => 'teardown configured replication progress tracking',
@@ -12518,10 +12580,12 @@
{ oid => '4350', descr => 'Unicode normalization',
proname => 'normalize', prorettype => 'text', proargtypes => 'text text',
+ proargdefaults => '{NFC}',
prosrc => 'unicode_normalize_func' },
{ oid => '4351', descr => 'check Unicode normalization',
proname => 'is_normalized', prorettype => 'bool', proargtypes => 'text text',
+ proargdefaults => '{NFC}',
prosrc => 'unicode_is_normalized' },
{ oid => '6198', descr => 'unescape Unicode characters',
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 18:54 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 19:13 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 19:47 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 20:02 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 20:31 ` Re: generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 21:46 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-17 01:36 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
@ 2026-02-17 15:13 ` Andres Freund <[email protected]>
2026-02-17 15:24 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Andres Freund @ 2026-02-17 15:13 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; pgsql-hackers
Hi,
On 2026-02-16 20:36:25 -0500, Tom Lane wrote:
> Here is a draft patch along those lines. I've verified that
> the initial contents of pg_proc are exactly as before,
> except that json[b]_strip_nulls gain the correct provolatile
> value, and a few proargdefaults entries no longer involve
> cast functions.
Nice.
> @@ -817,8 +832,10 @@ $ perl rewrite_dat_with_prokind.pl pg_proc.dat
> The following column types are supported directly by
> <filename>bootstrap.c</filename>: <type>bool</type>,
> <type>bytea</type>, <type>char</type> (1 byte),
> - <type>name</type>, <type>int2</type>,
> - <type>int4</type>, <type>regproc</type>, <type>regclass</type>,
> + <type>int2</type>, <type>int4</type>, <type>int8</type>,
> + <type>float4</type>, <type>float8</type>,
> + <type>name</type>,
> + <type>regproc</type>, <type>regclass</type>,
> <type>regtype</type>, <type>text</type>,
> <type>oid</type>, <type>tid</type>, <type>xid</type>,
> <type>cid</type>, <type>int2vector</type>, <type>oidvector</type>,
Don't you also add jsonb support below?
> + /*
> + * pg_node_tree values can't be inserted normally (pg_node_tree_in would
> + * just error out), so provide special cases for such columns that we
> + * would like to fill during bootstrap.
> + */
> + if (typoid == PG_NODE_TREEOID)
> + {
> + /* pg_proc.proargdefaults */
> + if (RelationGetRelid(boot_reldesc) == ProcedureRelationId &&
> + i == Anum_pg_proc_proargdefaults - 1)
> + values[i] = ConvertOneProargdefaultsValue(value);
> + else /* maybe other cases later */
> + elog(ERROR, "can't handle pg_node_tree input");
Perhaps add the relid to the ERROR?
> +/* ----------------
> + * ConvertOneProargdefaultsValue
> + *
> + * In general, proargdefaults can be a list of any expressions, but
> + * for bootstrap we only support a list of Const nodes. The input
> + * has the form of a text array, and we feed non-null elements to the
> + * typinput functions for the appropriate parameters.
> + * ----------------
> + */
> +static Datum
> +ConvertOneProargdefaultsValue(char *value)
> +{
> + int pronargs;
> + oidvector *proargtypes;
> + Datum arrayval;
> + Datum *array_datums;
> + bool *array_nulls;
> + int array_count;
> + List *proargdefaults;
> +
> + /* The pg_proc columns we need to use must have been filled already */
> + StaticAssertDecl(Anum_pg_proc_pronargs < Anum_pg_proc_proargdefaults,
> + "pronargs must come before proargdefaults");
> + StaticAssertDecl(Anum_pg_proc_pronargdefaults < Anum_pg_proc_proargdefaults,
> + "pronargdefaults must come before proargdefaults");
> + StaticAssertDecl(Anum_pg_proc_proargtypes < Anum_pg_proc_proargdefaults,
> + "proargtypes must come before proargdefaults");
> + if (Nulls[Anum_pg_proc_pronargs - 1])
> + elog(ERROR, "pronargs must not be null");
> + if (Nulls[Anum_pg_proc_proargtypes - 1])
> + elog(ERROR, "proargtypes must not be null");
> + pronargs = DatumGetInt16(values[Anum_pg_proc_pronargs - 1]);
> + proargtypes = DatumGetPointer(values[Anum_pg_proc_proargtypes - 1]);
> + Assert(pronargs == proargtypes->dim1);
> +
> + /* Parse the input string as a text[] value, then deconstruct to Datums */
> + arrayval = OidFunctionCall3(F_ARRAY_IN,
> + CStringGetDatum(value),
> + ObjectIdGetDatum(TEXTOID),
> + Int32GetDatum(-1));
>
> + deconstruct_array_builtin(DatumGetArrayTypeP(arrayval), TEXTOID,
> + &array_datums, &array_nulls, &array_count);
If we convert to cstring below anyway, why not make it a cstring array?
> + /* The values should correspond to the last N argtypes */
> + if (array_count > pronargs)
> + elog(ERROR, "too many proargdefaults entries");
> +
> + /* Build the List of Const nodes */
> + proargdefaults = NIL;
> + for (int i = 0; i < array_count; i++)
> + {
> + Oid argtype = proargtypes->values[pronargs - array_count + i];
> + int16 typlen;
> + bool typbyval;
> + char typalign;
> + char typdelim;
> + Oid typioparam;
> + Oid typinput;
> + Oid typoutput;
> + Oid typcollation;
> + Datum defval;
> + bool defnull;
> + Const *defConst;
> +
> + boot_get_type_io_data(argtype,
> + &typlen, &typbyval, &typalign,
> + &typdelim, &typioparam,
> + &typinput, &typoutput);
> + typcollation = boot_get_typcollation(argtype);
> + defnull = array_nulls[i];
> + if (defnull)
> + defval = (Datum) 0;
> + else
> + {
> + char *defstr = text_to_cstring(DatumGetTextPP(array_datums[i]));
> +
> + defval = OidInputFunctionCall(typinput, defstr, typioparam, -1);
> + }
> +
> + defConst = makeConst(argtype,
> + -1, /* never any typmod */
> + typcollation,
> + typlen,
> + defval,
> + defnull,
> + typbyval);
> + proargdefaults = lappend(proargdefaults, defConst);
> + }
> +
> + /*
> + * Hack: fill in pronargdefaults with the right value. This is surely
> + * ugly, but it beats making the programmer do it.
> + */
> + values[Anum_pg_proc_pronargdefaults - 1] = Int16GetDatum(array_count);
> + Nulls[Anum_pg_proc_pronargdefaults - 1] = false;
I don't mind the hack, but I wonder about it location. It's odd that the
caller puts the return value of ConvertOneProargdefaultsValue() into the
values array, but then ConvertOneProargdefaultsValue() also sets
pronargdefaults?
> +/* ----------------
> + * boot_get_typcollation
> + *
> + * Obtain type's collation at bootstrap time. This intentionally has
> + * the same API as lsyscache.c's get_typcollation.
> + *
> + * XXX would it be better to add another output to boot_get_type_io_data?
Yes, it seems like it would? There aren't many callers for it...
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 18:54 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 19:13 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 19:47 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 20:02 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 20:31 ` Re: generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 21:46 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-17 01:36 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-17 15:13 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
@ 2026-02-17 15:24 ` Tom Lane <[email protected]>
2026-02-17 18:41 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2026-02-17 15:24 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; pgsql-hackers
Andres Freund <[email protected]> writes:
> If we convert to cstring below anyway, why not make it a cstring array?
Ha, I'd forgotten that cstring[] is a thing. Yup, that'd save one
step.
> I don't mind the hack, but I wonder about it location. It's odd that the
> caller puts the return value of ConvertOneProargdefaultsValue() into the
> values array, but then ConvertOneProargdefaultsValue() also sets
> pronargdefaults?
Yeah, I'd gone back and forth about whether this function ought to
return the converted datum or just shove it into values[] directly.
Given that it's also filling the pronargdefaults entry, it probably
should take the latter approach.
I'll post a v2 in a bit. Thanks for reviewing!
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 18:54 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 19:13 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 19:47 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 20:02 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 20:31 ` Re: generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 21:46 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-17 01:36 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-17 15:13 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-17 15:24 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
@ 2026-02-17 18:41 ` Tom Lane <[email protected]>
2026-02-17 19:07 ` Re: generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2026-02-17 18:41 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; pgsql-hackers
Here's the promised v2, which addresses all your review comments.
With respect to the list of supported types in bki.sgml: I wonder if
we should just drop that, because it evidently hasn't been maintained
well. It wasn't at all in sync with the actual contents of TypInfo[].
I made it be so, but ...
Poking further at that, I found that there were a lot of TypInfo[]
entries that were not actually used and seem to have just been
cargo-culted in. So this patch removes all the ones that aren't
demonstrably necessary to get through initdb. Maybe that's too
aggressive, but in view of the potential for maintenance errors
(cf 7cdb633c8) I don't think we should be carrying unused entries
there.
regards, tom lane
Attachments:
[text/x-diff] v2-0001-Simplify-creation-of-built-in-functions-with-defa.patch (41.2K, ../../[email protected]/2-v2-0001-Simplify-creation-of-built-in-functions-with-defa.patch)
download | inline diff:
From fa818e3fa7c74019c2b6ffc3a0050db719581852 Mon Sep 17 00:00:00 2001
From: Tom Lane <[email protected]>
Date: Tue, 17 Feb 2026 13:31:56 -0500
Subject: [PATCH v2] Simplify creation of built-in functions with default
arguments.
Up to now, to create such a function, one had to make a pg_proc.dat
entry and then overwrite it with a CREATE OR REPLACE command in
system_functions.sql. That's error-prone (cf. bug #19409) and
results in leaving dead rows in the initial contents of pg_proc.
Manual maintenance of pg_node_tree strings seems entirely impractical,
and parsing expressions during bootstrap would be extremely difficult
as well. But Andres Freund observed that all the current use-cases
are simple constants, and building a Const node is well within the
capabilities of bootstrap mode. So this patch invents a special case:
if bootstrap mode is asked to ingest a non-null value for
pg_proc.proargdefaults (which would otherwise fail in
pg_node_tree_in), it parses the value as an array literal and then
feeds the element strings to the input functions for the corresponding
parameter types. Then we can build a suitable pg_node_tree string
with just a few more lines of code.
This allows removing all the system_functions.sql entries that are
just there to set up default arguments, replacing them with
proargdefaults fields in pg_proc.dat entries. The old technique
remains available in case someone needs a non-constant default.
The initial contents of pg_proc are demonstrably the same after
this patch, except that (1) json_strip_nulls and jsonb_strip_nulls
now have the correct provolatile setting, as per bug #19409;
(2) pg_terminate_backend, make_interval, and drandom_normal
now have defaults that don't include a type coercion, which is
how they should have been all along.
Author: Tom Lane <[email protected]>
Author: Andrew Dunstan <[email protected]>
Reviewed-by: Andres Freund <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
doc/src/sgml/bki.sgml | 34 ++-
src/backend/bootstrap/bootstrap.c | 177 ++++++++++++--
src/backend/catalog/system_functions.sql | 285 +----------------------
src/backend/utils/cache/lsyscache.c | 4 +-
src/include/bootstrap/bootstrap.h | 3 +-
src/include/catalog/pg_proc.dat | 86 ++++++-
6 files changed, 262 insertions(+), 327 deletions(-)
diff --git a/doc/src/sgml/bki.sgml b/doc/src/sgml/bki.sgml
index 53a982bf60d..087a6827b00 100644
--- a/doc/src/sgml/bki.sgml
+++ b/doc/src/sgml/bki.sgml
@@ -271,6 +271,21 @@
</para>
</listitem>
+ <listitem>
+ <para>
+ There is a special case for values of the
+ <structname>pg_proc</structname>.<structfield>proargdefaults</structfield>
+ field, which is of type <type>pg_node_tree</type>. The real
+ contents of that type are too complex for hand-written entries,
+ but what we need for <structfield>proargdefaults</structfield> is
+ typically just a list of Const nodes. Therefore, the bootstrap
+ backend will interpret a value given for that field according to
+ text array syntax, and then feed the array element values to the
+ datatype input routines for the corresponding input parameters' data
+ types, and finally build Const nodes from the datums.
+ </para>
+ </listitem>
+
<listitem>
<para>
Since hashes are unordered data structures, field order and line
@@ -817,11 +832,11 @@ $ perl rewrite_dat_with_prokind.pl pg_proc.dat
The following column types are supported directly by
<filename>bootstrap.c</filename>: <type>bool</type>,
<type>bytea</type>, <type>char</type> (1 byte),
- <type>name</type>, <type>int2</type>,
- <type>int4</type>, <type>regproc</type>, <type>regclass</type>,
- <type>regtype</type>, <type>text</type>,
- <type>oid</type>, <type>tid</type>, <type>xid</type>,
- <type>cid</type>, <type>int2vector</type>, <type>oidvector</type>,
+ <type>int2</type>, <type>int4</type>, <type>int8</type>,
+ <type>float4</type>, <type>float8</type>,
+ <type>name</type>, <type>regproc</type>, <type>text</type>,
+ <type>jsonb</type>, <type>oid</type>, <type>pg_node_tree</type>,
+ <type>int2vector</type>, <type>oidvector</type>,
<type>_int4</type> (array), <type>_text</type> (array),
<type>_oid</type> (array), <type>_char</type> (array),
<type>_aclitem</type> (array). Although it is possible to create
@@ -884,7 +899,7 @@ $ perl rewrite_dat_with_prokind.pl pg_proc.dat
<varlistentry>
<term>
- <literal>insert</literal> <literal>(</literal> <optional><replaceable class="parameter">oid_value</replaceable></optional> <replaceable class="parameter">value1</replaceable> <replaceable class="parameter">value2</replaceable> ... <literal>)</literal>
+ <literal>insert</literal> <literal>(</literal> <replaceable class="parameter">value1</replaceable> <replaceable class="parameter">value2</replaceable> ... <literal>)</literal>
</term>
<listitem>
@@ -902,6 +917,13 @@ $ perl rewrite_dat_with_prokind.pl pg_proc.dat
(To include a single quote in a value, write it twice.
Escape-string-style backslash escapes are allowed in the string, too.)
</para>
+
+ <para>
+ In most cases a <replaceable class="parameter">value</replaceable>
+ string is simply fed to the datatype input routine for the column's
+ data type, after de-quoting if needed. However there are exceptions
+ for certain fields, as detailed previously.
+ </para>
</listitem>
</varlistentry>
diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c
index 7d32cd0e159..8d601c363b4 100644
--- a/src/backend/bootstrap/bootstrap.c
+++ b/src/backend/bootstrap/bootstrap.c
@@ -26,6 +26,7 @@
#include "bootstrap/bootstrap.h"
#include "catalog/index.h"
#include "catalog/pg_collation.h"
+#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "common/link-canary.h"
#include "miscadmin.h"
@@ -46,6 +47,7 @@
static void CheckerModeMain(void);
static void bootstrap_signals(void);
static Form_pg_attribute AllocateAttribute(void);
+static void InsertOneProargdefaultsValue(char *value);
static void populate_typ_list(void);
static Oid gettype(char *type);
static void cleanup(void);
@@ -91,38 +93,28 @@ static const struct typinfo TypInfo[] = {
F_BYTEAIN, F_BYTEAOUT},
{"char", CHAROID, 0, 1, true, TYPALIGN_CHAR, TYPSTORAGE_PLAIN, InvalidOid,
F_CHARIN, F_CHAROUT},
+ {"cstring", CSTRINGOID, 0, -2, false, TYPALIGN_CHAR, TYPSTORAGE_PLAIN, InvalidOid,
+ F_CSTRING_IN, F_CSTRING_OUT},
{"int2", INT2OID, 0, 2, true, TYPALIGN_SHORT, TYPSTORAGE_PLAIN, InvalidOid,
F_INT2IN, F_INT2OUT},
{"int4", INT4OID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
F_INT4IN, F_INT4OUT},
+ {"int8", INT8OID, 0, 8, true, TYPALIGN_DOUBLE, TYPSTORAGE_PLAIN, InvalidOid,
+ F_INT8IN, F_INT8OUT},
{"float4", FLOAT4OID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
F_FLOAT4IN, F_FLOAT4OUT},
+ {"float8", FLOAT8OID, 0, 8, true, TYPALIGN_DOUBLE, TYPSTORAGE_PLAIN, InvalidOid,
+ F_FLOAT8IN, F_FLOAT8OUT},
{"name", NAMEOID, CHAROID, NAMEDATALEN, false, TYPALIGN_CHAR, TYPSTORAGE_PLAIN, C_COLLATION_OID,
F_NAMEIN, F_NAMEOUT},
- {"regclass", REGCLASSOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
- F_REGCLASSIN, F_REGCLASSOUT},
{"regproc", REGPROCOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
F_REGPROCIN, F_REGPROCOUT},
- {"regtype", REGTYPEOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
- F_REGTYPEIN, F_REGTYPEOUT},
- {"regrole", REGROLEOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
- F_REGROLEIN, F_REGROLEOUT},
- {"regnamespace", REGNAMESPACEOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
- F_REGNAMESPACEIN, F_REGNAMESPACEOUT},
- {"regdatabase", REGDATABASEOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
- F_REGDATABASEIN, F_REGDATABASEOUT},
{"text", TEXTOID, 0, -1, false, TYPALIGN_INT, TYPSTORAGE_EXTENDED, DEFAULT_COLLATION_OID,
F_TEXTIN, F_TEXTOUT},
+ {"jsonb", JSONBOID, 0, -1, false, TYPALIGN_INT, TYPSTORAGE_EXTENDED, InvalidOid,
+ F_JSONB_IN, F_JSONB_OUT},
{"oid", OIDOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
F_OIDIN, F_OIDOUT},
- {"oid8", OID8OID, 0, 8, true, TYPALIGN_DOUBLE, TYPSTORAGE_PLAIN, InvalidOid,
- F_OID8IN, F_OID8OUT},
- {"tid", TIDOID, 0, 6, false, TYPALIGN_SHORT, TYPSTORAGE_PLAIN, InvalidOid,
- F_TIDIN, F_TIDOUT},
- {"xid", XIDOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
- F_XIDIN, F_XIDOUT},
- {"cid", CIDOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
- F_CIDIN, F_CIDOUT},
{"pg_node_tree", PG_NODE_TREEOID, 0, -1, false, TYPALIGN_INT, TYPSTORAGE_EXTENDED, DEFAULT_COLLATION_OID,
F_PG_NODE_TREE_IN, F_PG_NODE_TREE_OUT},
{"int2vector", INT2VECTOROID, INT2OID, -1, false, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
@@ -660,6 +652,7 @@ InsertOneTuple(void)
void
InsertOneValue(char *value, int i)
{
+ Form_pg_attribute attr;
Oid typoid;
int16 typlen;
bool typbyval;
@@ -668,19 +661,42 @@ InsertOneValue(char *value, int i)
Oid typioparam;
Oid typinput;
Oid typoutput;
+ Oid typcollation;
Assert(i >= 0 && i < MAXATTR);
elog(DEBUG4, "inserting column %d value \"%s\"", i, value);
- typoid = TupleDescAttr(boot_reldesc->rd_att, i)->atttypid;
+ attr = TupleDescAttr(RelationGetDescr(boot_reldesc), i);
+ typoid = attr->atttypid;
boot_get_type_io_data(typoid,
&typlen, &typbyval, &typalign,
&typdelim, &typioparam,
- &typinput, &typoutput);
+ &typinput, &typoutput,
+ &typcollation);
- values[i] = OidInputFunctionCall(typinput, value, typioparam, -1);
+ /*
+ * pg_node_tree values can't be inserted normally (pg_node_tree_in would
+ * just error out), so provide special cases for such columns that we
+ * would like to fill during bootstrap.
+ */
+ if (typoid == PG_NODE_TREEOID)
+ {
+ /* pg_proc.proargdefaults */
+ if (RelationGetRelid(boot_reldesc) == ProcedureRelationId &&
+ i == Anum_pg_proc_proargdefaults - 1)
+ InsertOneProargdefaultsValue(value);
+ else /* maybe other cases later */
+ elog(ERROR, "can't handle pg_node_tree input for %s.%s",
+ RelationGetRelationName(boot_reldesc),
+ NameStr(attr->attname));
+ }
+ else
+ {
+ /* Normal case */
+ values[i] = OidInputFunctionCall(typinput, value, typioparam, -1);
+ }
/*
* We use ereport not elog here so that parameters aren't evaluated unless
@@ -691,6 +707,111 @@ InsertOneValue(char *value, int i)
OidOutputFunctionCall(typoutput, values[i]))));
}
+/* ----------------
+ * InsertOneProargdefaultsValue
+ *
+ * In general, proargdefaults can be a list of any expressions, but
+ * for bootstrap we only support a list of Const nodes. The input
+ * has the form of a text array, and we feed non-null elements to the
+ * typinput functions for the appropriate parameters.
+ * ----------------
+ */
+static void
+InsertOneProargdefaultsValue(char *value)
+{
+ int pronargs;
+ oidvector *proargtypes;
+ Datum arrayval;
+ Datum *array_datums;
+ bool *array_nulls;
+ int array_count;
+ List *proargdefaults;
+ char *nodestring;
+
+ /* The pg_proc columns we need to use must have been filled already */
+ StaticAssertDecl(Anum_pg_proc_pronargs < Anum_pg_proc_proargdefaults,
+ "pronargs must come before proargdefaults");
+ StaticAssertDecl(Anum_pg_proc_pronargdefaults < Anum_pg_proc_proargdefaults,
+ "pronargdefaults must come before proargdefaults");
+ StaticAssertDecl(Anum_pg_proc_proargtypes < Anum_pg_proc_proargdefaults,
+ "proargtypes must come before proargdefaults");
+ if (Nulls[Anum_pg_proc_pronargs - 1])
+ elog(ERROR, "pronargs must not be null");
+ if (Nulls[Anum_pg_proc_proargtypes - 1])
+ elog(ERROR, "proargtypes must not be null");
+ pronargs = DatumGetInt16(values[Anum_pg_proc_pronargs - 1]);
+ proargtypes = DatumGetPointer(values[Anum_pg_proc_proargtypes - 1]);
+ Assert(pronargs == proargtypes->dim1);
+
+ /* Parse the input string as an array value, then deconstruct to Datums */
+ arrayval = OidFunctionCall3(F_ARRAY_IN,
+ CStringGetDatum(value),
+ ObjectIdGetDatum(CSTRINGOID),
+ Int32GetDatum(-1));
+ deconstruct_array_builtin(DatumGetArrayTypeP(arrayval), CSTRINGOID,
+ &array_datums, &array_nulls, &array_count);
+
+ /* The values should correspond to the last N argtypes */
+ if (array_count > pronargs)
+ elog(ERROR, "too many proargdefaults entries");
+
+ /* Build the List of Const nodes */
+ proargdefaults = NIL;
+ for (int i = 0; i < array_count; i++)
+ {
+ Oid argtype = proargtypes->values[pronargs - array_count + i];
+ int16 typlen;
+ bool typbyval;
+ char typalign;
+ char typdelim;
+ Oid typioparam;
+ Oid typinput;
+ Oid typoutput;
+ Oid typcollation;
+ Datum defval;
+ bool defnull;
+ Const *defConst;
+
+ boot_get_type_io_data(argtype,
+ &typlen, &typbyval, &typalign,
+ &typdelim, &typioparam,
+ &typinput, &typoutput,
+ &typcollation);
+
+ defnull = array_nulls[i];
+ if (defnull)
+ defval = (Datum) 0;
+ else
+ defval = OidInputFunctionCall(typinput,
+ DatumGetCString(array_datums[i]),
+ typioparam, -1);
+
+ defConst = makeConst(argtype,
+ -1, /* never any typmod */
+ typcollation,
+ typlen,
+ defval,
+ defnull,
+ typbyval);
+ proargdefaults = lappend(proargdefaults, defConst);
+ }
+
+ /*
+ * Flatten the List to a node-tree string, then convert to a text datum,
+ * which is the storage representation of pg_node_tree.
+ */
+ nodestring = nodeToString(proargdefaults);
+ values[Anum_pg_proc_proargdefaults - 1] = CStringGetTextDatum(nodestring);
+ Nulls[Anum_pg_proc_proargdefaults - 1] = false;
+
+ /*
+ * Hack: fill in pronargdefaults with the right value. This is surely
+ * ugly, but it beats making the programmer do it.
+ */
+ values[Anum_pg_proc_pronargdefaults - 1] = Int16GetDatum(array_count);
+ Nulls[Anum_pg_proc_pronargdefaults - 1] = false;
+}
+
/* ----------------
* InsertOneNull
* ----------------
@@ -831,10 +952,11 @@ gettype(char *type)
* boot_get_type_io_data
*
* Obtain type I/O information at bootstrap time. This intentionally has
- * almost the same API as lsyscache.c's get_type_io_data, except that
+ * an API very close to that of lsyscache.c's get_type_io_data, except that
* we only support obtaining the typinput and typoutput routines, not
- * the binary I/O routines. It is exported so that array_in and array_out
- * can be made to work during early bootstrap.
+ * the binary I/O routines, and we also return the type's collation.
+ * This is exported so that array_in and array_out can be made to work
+ * during early bootstrap.
* ----------------
*/
void
@@ -845,7 +967,8 @@ boot_get_type_io_data(Oid typid,
char *typdelim,
Oid *typioparam,
Oid *typinput,
- Oid *typoutput)
+ Oid *typoutput,
+ Oid *typcollation)
{
if (Typ != NIL)
{
@@ -876,6 +999,8 @@ boot_get_type_io_data(Oid typid,
*typinput = ap->am_typ.typinput;
*typoutput = ap->am_typ.typoutput;
+
+ *typcollation = ap->am_typ.typcollation;
}
else
{
@@ -904,6 +1029,8 @@ boot_get_type_io_data(Oid typid,
*typinput = TypInfo[typeindex].inproc;
*typoutput = TypInfo[typeindex].outproc;
+
+ *typcollation = TypInfo[typeindex].collation;
}
}
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index eb9e31ae1bf..4e24d6b55fa 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -7,7 +7,8 @@
*
* This file redefines certain built-in functions that are impractical
* to fully define in pg_proc.dat. In most cases that's because they use
- * SQL-standard function bodies and/or default expressions. The node
+ * SQL-standard function bodies and/or default expressions. (But defaults
+ * that are just constants can be entered in pg_proc.dat.) The node
* tree representations of those are too unreadable, platform-dependent,
* and changeable to want to deal with them manually. Hence, we put stub
* definitions of such functions into pg_proc.dat and then replace them
@@ -66,13 +67,6 @@ CREATE OR REPLACE FUNCTION bit_length(text)
IMMUTABLE PARALLEL SAFE STRICT COST 1
RETURN octet_length($1) * 8;
-CREATE OR REPLACE FUNCTION
- random_normal(mean float8 DEFAULT 0, stddev float8 DEFAULT 1)
- RETURNS float8
- LANGUAGE internal
- VOLATILE PARALLEL RESTRICTED STRICT COST 1
-AS 'drandom_normal';
-
CREATE OR REPLACE FUNCTION log(numeric)
RETURNS numeric
LANGUAGE sql
@@ -382,281 +376,6 @@ CREATE OR REPLACE FUNCTION ts_debug(document text,
BEGIN ATOMIC
SELECT * FROM ts_debug(get_current_ts_config(), $1);
END;
-
-CREATE OR REPLACE FUNCTION
- pg_backup_start(label text, fast boolean DEFAULT false)
- RETURNS pg_lsn STRICT VOLATILE LANGUAGE internal AS 'pg_backup_start'
- PARALLEL RESTRICTED;
-
-CREATE OR REPLACE FUNCTION pg_backup_stop (
- wait_for_archive boolean DEFAULT true, OUT lsn pg_lsn,
- OUT labelfile text, OUT spcmapfile text)
- RETURNS record STRICT VOLATILE LANGUAGE internal as 'pg_backup_stop'
- PARALLEL RESTRICTED;
-
-CREATE OR REPLACE FUNCTION
- pg_promote(wait boolean DEFAULT true, wait_seconds integer DEFAULT 60)
- RETURNS boolean STRICT VOLATILE LANGUAGE INTERNAL AS 'pg_promote'
- PARALLEL SAFE;
-
-CREATE OR REPLACE FUNCTION
- pg_terminate_backend(pid integer, timeout int8 DEFAULT 0)
- RETURNS boolean STRICT VOLATILE LANGUAGE INTERNAL AS 'pg_terminate_backend'
- PARALLEL SAFE;
-
--- legacy definition for compatibility with 9.3
-CREATE OR REPLACE FUNCTION
- json_populate_record(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
- RETURNS anyelement LANGUAGE internal STABLE AS 'json_populate_record' PARALLEL SAFE;
-
--- legacy definition for compatibility with 9.3
-CREATE OR REPLACE FUNCTION
- json_populate_recordset(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
- RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'json_populate_recordset' PARALLEL SAFE;
-
-CREATE OR REPLACE FUNCTION pg_logical_slot_get_changes(
- IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
- OUT lsn pg_lsn, OUT xid xid, OUT data text)
-RETURNS SETOF RECORD
-LANGUAGE INTERNAL
-VOLATILE ROWS 1000 COST 1000
-AS 'pg_logical_slot_get_changes';
-
-CREATE OR REPLACE FUNCTION pg_logical_slot_peek_changes(
- IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
- OUT lsn pg_lsn, OUT xid xid, OUT data text)
-RETURNS SETOF RECORD
-LANGUAGE INTERNAL
-VOLATILE ROWS 1000 COST 1000
-AS 'pg_logical_slot_peek_changes';
-
-CREATE OR REPLACE FUNCTION pg_logical_slot_get_binary_changes(
- IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
- OUT lsn pg_lsn, OUT xid xid, OUT data bytea)
-RETURNS SETOF RECORD
-LANGUAGE INTERNAL
-VOLATILE ROWS 1000 COST 1000
-AS 'pg_logical_slot_get_binary_changes';
-
-CREATE OR REPLACE FUNCTION pg_logical_slot_peek_binary_changes(
- IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
- OUT lsn pg_lsn, OUT xid xid, OUT data bytea)
-RETURNS SETOF RECORD
-LANGUAGE INTERNAL
-VOLATILE ROWS 1000 COST 1000
-AS 'pg_logical_slot_peek_binary_changes';
-
-CREATE OR REPLACE FUNCTION pg_logical_emit_message(
- transactional boolean,
- prefix text,
- message text,
- flush boolean DEFAULT false)
-RETURNS pg_lsn
-LANGUAGE INTERNAL
-STRICT VOLATILE
-AS 'pg_logical_emit_message_text';
-
-CREATE OR REPLACE FUNCTION pg_logical_emit_message(
- transactional boolean,
- prefix text,
- message bytea,
- flush boolean DEFAULT false)
-RETURNS pg_lsn
-LANGUAGE INTERNAL
-STRICT VOLATILE
-AS 'pg_logical_emit_message_bytea';
-
-CREATE OR REPLACE FUNCTION pg_create_physical_replication_slot(
- IN slot_name name, IN immediately_reserve boolean DEFAULT false,
- IN temporary boolean DEFAULT false,
- OUT slot_name name, OUT lsn pg_lsn)
-RETURNS RECORD
-LANGUAGE INTERNAL
-STRICT VOLATILE
-AS 'pg_create_physical_replication_slot';
-
-CREATE OR REPLACE FUNCTION pg_create_logical_replication_slot(
- IN slot_name name, IN plugin name,
- IN temporary boolean DEFAULT false,
- IN twophase boolean DEFAULT false,
- IN failover boolean DEFAULT false,
- OUT slot_name name, OUT lsn pg_lsn)
-RETURNS RECORD
-LANGUAGE INTERNAL
-STRICT VOLATILE
-AS 'pg_create_logical_replication_slot';
-
-CREATE OR REPLACE FUNCTION
- make_interval(years int4 DEFAULT 0, months int4 DEFAULT 0, weeks int4 DEFAULT 0,
- days int4 DEFAULT 0, hours int4 DEFAULT 0, mins int4 DEFAULT 0,
- secs double precision DEFAULT 0.0)
-RETURNS interval
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'make_interval';
-
-CREATE OR REPLACE FUNCTION
- jsonb_set(jsonb_in jsonb, path text[] , replacement jsonb,
- create_if_missing boolean DEFAULT true)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_set';
-
-CREATE OR REPLACE FUNCTION
- jsonb_set_lax(jsonb_in jsonb, path text[] , replacement jsonb,
- create_if_missing boolean DEFAULT true,
- null_value_treatment text DEFAULT 'use_json_null')
-RETURNS jsonb
-LANGUAGE INTERNAL
-CALLED ON NULL INPUT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_set_lax';
-
-CREATE OR REPLACE FUNCTION
- parse_ident(str text, strict boolean DEFAULT true)
-RETURNS text[]
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'parse_ident';
-
-CREATE OR REPLACE FUNCTION
- jsonb_insert(jsonb_in jsonb, path text[] , replacement jsonb,
- insert_after boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_insert';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_exists(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS boolean
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_path_exists';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_match(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS boolean
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_path_match';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS SETOF jsonb
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_path_query';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query_array(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_path_query_array';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query_first(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'jsonb_path_query_first';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_exists_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS boolean
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_path_exists_tz';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_match_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS boolean
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_path_match_tz';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS SETOF jsonb
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_path_query_tz';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query_array_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_path_query_array_tz';
-
-CREATE OR REPLACE FUNCTION
- jsonb_path_query_first_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
- silent boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_path_query_first_tz';
-
-CREATE OR REPLACE FUNCTION
- jsonb_strip_nulls(target jsonb, strip_in_arrays boolean DEFAULT false)
-RETURNS jsonb
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'jsonb_strip_nulls';
-
-CREATE OR REPLACE FUNCTION
- json_strip_nulls(target json, strip_in_arrays boolean DEFAULT false)
-RETURNS json
-LANGUAGE INTERNAL
-STRICT STABLE PARALLEL SAFE
-AS 'json_strip_nulls';
-
--- default normalization form is NFC, per SQL standard
-CREATE OR REPLACE FUNCTION
- "normalize"(text, text DEFAULT 'NFC')
-RETURNS text
-LANGUAGE internal
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'unicode_normalize_func';
-
-CREATE OR REPLACE FUNCTION
- is_normalized(text, text DEFAULT 'NFC')
-RETURNS boolean
-LANGUAGE internal
-STRICT IMMUTABLE PARALLEL SAFE
-AS 'unicode_is_normalized';
-
-CREATE OR REPLACE FUNCTION
- pg_stat_reset_shared(target text DEFAULT NULL)
-RETURNS void
-LANGUAGE INTERNAL
-CALLED ON NULL INPUT VOLATILE PARALLEL SAFE
-AS 'pg_stat_reset_shared';
-
-CREATE OR REPLACE FUNCTION
- pg_stat_reset_slru(target text DEFAULT NULL)
-RETURNS void
-LANGUAGE INTERNAL
-CALLED ON NULL INPUT VOLATILE PARALLEL SAFE
-AS 'pg_stat_reset_slru';
-
-CREATE OR REPLACE FUNCTION
- pg_replication_origin_session_setup(node_name text, pid integer DEFAULT 0)
-RETURNS void
-LANGUAGE INTERNAL
-STRICT VOLATILE PARALLEL UNSAFE
-AS 'pg_replication_origin_session_setup';
-
--
-- The default permissions for functions mean that anyone can execute them.
-- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index b924a2d900b..1913b009d40 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -2492,6 +2492,7 @@ get_type_io_data(Oid typid,
{
Oid typinput;
Oid typoutput;
+ Oid typcollation;
boot_get_type_io_data(typid,
typlen,
@@ -2500,7 +2501,8 @@ get_type_io_data(Oid typid,
typdelim,
typioparam,
&typinput,
- &typoutput);
+ &typoutput,
+ &typcollation);
switch (which_func)
{
case IOFunc_input:
diff --git a/src/include/bootstrap/bootstrap.h b/src/include/bootstrap/bootstrap.h
index 51680522afc..21447a3d661 100644
--- a/src/include/bootstrap/bootstrap.h
+++ b/src/include/bootstrap/bootstrap.h
@@ -53,7 +53,8 @@ extern void boot_get_type_io_data(Oid typid,
char *typdelim,
Oid *typioparam,
Oid *typinput,
- Oid *typoutput);
+ Oid *typoutput,
+ Oid *typcollation);
union YYSTYPE;
typedef void *yyscan_t;
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 83f6501df38..dac40992cbc 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3499,6 +3499,7 @@
{ oid => '6212', descr => 'random value from normal distribution',
proname => 'random_normal', provolatile => 'v', proparallel => 'r',
prorettype => 'float8', proargtypes => 'float8 float8',
+ proargnames => '{mean,stddev}', proargdefaults => '{0,1}',
prosrc => 'drandom_normal' },
{ oid => '6339', descr => 'random integer in range',
proname => 'random', provolatile => 'v', proparallel => 'r',
@@ -6174,6 +6175,7 @@
descr => 'statistics: reset collected statistics shared across the cluster',
proname => 'pg_stat_reset_shared', proisstrict => 'f', provolatile => 'v',
prorettype => 'void', proargtypes => 'text',
+ proargnames => '{target}', proargdefaults => '{NULL}',
prosrc => 'pg_stat_reset_shared' },
{ oid => '3776',
descr => 'statistics: reset collected statistics for a single table or index in the current database or shared across all databases in the cluster',
@@ -6193,6 +6195,7 @@
descr => 'statistics: reset collected statistics for a single SLRU',
proname => 'pg_stat_reset_slru', proisstrict => 'f', provolatile => 'v',
prorettype => 'void', proargtypes => 'text', proargnames => '{target}',
+ proargdefaults => '{NULL}',
prosrc => 'pg_stat_reset_slru' },
{ oid => '6170',
descr => 'statistics: reset collected statistics for a single replication slot',
@@ -6728,20 +6731,24 @@
{ oid => '2096', descr => 'terminate a server process',
proname => 'pg_terminate_backend', provolatile => 'v', prorettype => 'bool',
proargtypes => 'int4 int8', proargnames => '{pid,timeout}',
+ proargdefaults => '{0}',
prosrc => 'pg_terminate_backend' },
{ oid => '2172', descr => 'prepare for taking an online backup',
proname => 'pg_backup_start', provolatile => 'v', proparallel => 'r',
prorettype => 'pg_lsn', proargtypes => 'text bool',
+ proargnames => '{label,fast}', proargdefaults => '{false}',
prosrc => 'pg_backup_start' },
{ oid => '2739', descr => 'finish taking an online backup',
proname => 'pg_backup_stop', provolatile => 'v', proparallel => 'r',
prorettype => 'record', proargtypes => 'bool',
proallargtypes => '{bool,pg_lsn,text,text}', proargmodes => '{i,o,o,o}',
proargnames => '{wait_for_archive,lsn,labelfile,spcmapfile}',
+ proargdefaults => '{true}',
prosrc => 'pg_backup_stop' },
{ oid => '3436', descr => 'promote standby server',
proname => 'pg_promote', provolatile => 'v', prorettype => 'bool',
proargtypes => 'bool int4', proargnames => '{wait,wait_seconds}',
+ proargdefaults => '{true,60}',
prosrc => 'pg_promote' },
{ oid => '2848', descr => 'switch to new wal file',
proname => 'pg_switch_wal', provolatile => 'v', prorettype => 'pg_lsn',
@@ -7517,7 +7524,8 @@
{ oid => '1268',
descr => 'parse qualified identifier to array of identifiers',
proname => 'parse_ident', prorettype => '_text', proargtypes => 'text bool',
- proargnames => '{str,strict}', prosrc => 'parse_ident' },
+ proargnames => '{str,strict}', proargdefaults => '{true}',
+ prosrc => 'parse_ident' },
{ oid => '2246', descr => '(internal)',
proname => 'fmgr_internal_validator', provolatile => 's',
@@ -9423,7 +9431,9 @@
proargtypes => 'anyelement', prosrc => 'to_json' },
{ oid => '3261', descr => 'remove object fields with null values from json',
proname => 'json_strip_nulls', prorettype => 'json',
- proargtypes => 'json bool', prosrc => 'json_strip_nulls' },
+ proargtypes => 'json bool',
+ proargnames => '{target,strip_in_arrays}', proargdefaults => '{false}',
+ prosrc => 'json_strip_nulls' },
{ oid => '3947',
proname => 'json_object_field', prorettype => 'json',
@@ -9480,12 +9490,17 @@
{ oid => '3960', descr => 'get record fields from a json object',
proname => 'json_populate_record', proisstrict => 'f', provolatile => 's',
prorettype => 'anyelement', proargtypes => 'anyelement json bool',
+ proargnames => '{base,from_json,use_json_as_text}',
+ proargdefaults => '{false}',
prosrc => 'json_populate_record' },
{ oid => '3961',
descr => 'get set of records with fields from a json array of objects',
proname => 'json_populate_recordset', prorows => '100', proisstrict => 'f',
proretset => 't', provolatile => 's', prorettype => 'anyelement',
- proargtypes => 'anyelement json bool', prosrc => 'json_populate_recordset' },
+ proargtypes => 'anyelement json bool',
+ proargnames => '{base,from_json,use_json_as_text}',
+ proargdefaults => '{false}',
+ prosrc => 'json_populate_recordset' },
{ oid => '3204', descr => 'get record fields from a json object',
proname => 'json_to_record', provolatile => 's', prorettype => 'record',
proargtypes => 'json', prosrc => 'json_to_record' },
@@ -10364,7 +10379,9 @@
prosrc => 'jsonb_build_object_noargs' },
{ oid => '3262', descr => 'remove object fields with null values from jsonb',
proname => 'jsonb_strip_nulls', prorettype => 'jsonb',
- proargtypes => 'jsonb bool', prosrc => 'jsonb_strip_nulls' },
+ proargtypes => 'jsonb bool',
+ proargnames => '{target,strip_in_arrays}', proargdefaults => '{false}',
+ prosrc => 'jsonb_strip_nulls' },
{ oid => '3478',
proname => 'jsonb_object_field', prorettype => 'jsonb',
@@ -10538,16 +10555,25 @@
proargtypes => 'jsonb _text', prosrc => 'jsonb_delete_path' },
{ oid => '5054', descr => 'Set part of a jsonb, handle NULL value',
proname => 'jsonb_set_lax', proisstrict => 'f', prorettype => 'jsonb',
- proargtypes => 'jsonb _text jsonb bool text', prosrc => 'jsonb_set_lax' },
+ proargtypes => 'jsonb _text jsonb bool text',
+ proargnames => '{jsonb_in,path,replacement,create_if_missing,null_value_treatment}',
+ proargdefaults => '{true,use_json_null}',
+ prosrc => 'jsonb_set_lax' },
{ oid => '3305', descr => 'Set part of a jsonb',
proname => 'jsonb_set', prorettype => 'jsonb',
- proargtypes => 'jsonb _text jsonb bool', prosrc => 'jsonb_set' },
+ proargtypes => 'jsonb _text jsonb bool',
+ proargnames => '{jsonb_in,path,replacement,create_if_missing}',
+ proargdefaults => '{true}',
+ prosrc => 'jsonb_set' },
{ oid => '3306', descr => 'Indented text from jsonb',
proname => 'jsonb_pretty', prorettype => 'text', proargtypes => 'jsonb',
prosrc => 'jsonb_pretty' },
{ oid => '3579', descr => 'Insert value into a jsonb',
proname => 'jsonb_insert', prorettype => 'jsonb',
- proargtypes => 'jsonb _text jsonb bool', prosrc => 'jsonb_insert' },
+ proargtypes => 'jsonb _text jsonb bool',
+ proargnames => '{jsonb_in,path,replacement,insert_after}',
+ proargdefaults => '{false}',
+ prosrc => 'jsonb_insert' },
# jsonpath
{ oid => '4001', descr => 'I/O',
@@ -10565,42 +10591,66 @@
{ oid => '4005', descr => 'jsonpath exists test',
proname => 'jsonb_path_exists', prorettype => 'bool',
- proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_exists' },
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
+ prosrc => 'jsonb_path_exists' },
{ oid => '4006', descr => 'jsonpath query',
proname => 'jsonb_path_query', prorows => '1000', proretset => 't',
prorettype => 'jsonb', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
prosrc => 'jsonb_path_query' },
{ oid => '4007', descr => 'jsonpath query wrapped into array',
proname => 'jsonb_path_query_array', prorettype => 'jsonb',
proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
prosrc => 'jsonb_path_query_array' },
{ oid => '4008', descr => 'jsonpath query first item',
proname => 'jsonb_path_query_first', prorettype => 'jsonb',
proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
prosrc => 'jsonb_path_query_first' },
{ oid => '4009', descr => 'jsonpath match',
proname => 'jsonb_path_match', prorettype => 'bool',
- proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_match' },
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
+ prosrc => 'jsonb_path_match' },
{ oid => '1177', descr => 'jsonpath exists test with timezone',
proname => 'jsonb_path_exists_tz', provolatile => 's', prorettype => 'bool',
proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
prosrc => 'jsonb_path_exists_tz' },
{ oid => '1179', descr => 'jsonpath query with timezone',
proname => 'jsonb_path_query_tz', prorows => '1000', proretset => 't',
provolatile => 's', prorettype => 'jsonb',
- proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_query_tz' },
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
+ prosrc => 'jsonb_path_query_tz' },
{ oid => '1180', descr => 'jsonpath query wrapped into array with timezone',
proname => 'jsonb_path_query_array_tz', provolatile => 's',
prorettype => 'jsonb', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
prosrc => 'jsonb_path_query_array_tz' },
{ oid => '2023', descr => 'jsonpath query first item with timezone',
proname => 'jsonb_path_query_first_tz', provolatile => 's',
prorettype => 'jsonb', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
prosrc => 'jsonb_path_query_first_tz' },
{ oid => '2030', descr => 'jsonpath match with timezone',
proname => 'jsonb_path_match_tz', provolatile => 's', prorettype => 'bool',
- proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_match_tz' },
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ proargdefaults => '{"{}",false}',
+ prosrc => 'jsonb_path_match_tz' },
{ oid => '4010', descr => 'implementation of @? operator',
proname => 'jsonb_path_exists_opr', prorettype => 'bool',
@@ -11411,6 +11461,7 @@
proname => 'make_interval', prorettype => 'interval',
proargtypes => 'int4 int4 int4 int4 int4 int4 float8',
proargnames => '{years,months,weeks,days,hours,mins,secs}',
+ proargdefaults => '{0,0,0,0,0,0,0.0}',
prosrc => 'make_interval' },
# spgist opclasses
@@ -11511,6 +11562,7 @@
proallargtypes => '{name,bool,bool,name,pg_lsn}',
proargmodes => '{i,i,i,o,o}',
proargnames => '{slot_name,immediately_reserve,temporary,slot_name,lsn}',
+ proargdefaults => '{false,false}',
prosrc => 'pg_create_physical_replication_slot' },
{ oid => '4220',
descr => 'copy a physical replication slot, changing temporality',
@@ -11546,6 +11598,7 @@
proallargtypes => '{name,name,bool,bool,bool,name,pg_lsn}',
proargmodes => '{i,i,i,i,i,o,o}',
proargnames => '{slot_name,plugin,temporary,twophase,failover,slot_name,lsn}',
+ proargdefaults => '{false,false,false}',
prosrc => 'pg_create_logical_replication_slot' },
{ oid => '4222',
descr => 'copy a logical replication slot, changing temporality and plugin',
@@ -11578,6 +11631,7 @@
proallargtypes => '{name,pg_lsn,int4,_text,pg_lsn,xid,text}',
proargmodes => '{i,i,i,v,o,o,o}',
proargnames => '{slot_name,upto_lsn,upto_nchanges,options,lsn,xid,data}',
+ proargdefaults => '{"{}"}',
prosrc => 'pg_logical_slot_get_changes' },
{ oid => '3783', descr => 'get binary changes from replication slot',
proname => 'pg_logical_slot_get_binary_changes', procost => '1000',
@@ -11587,6 +11641,7 @@
proallargtypes => '{name,pg_lsn,int4,_text,pg_lsn,xid,bytea}',
proargmodes => '{i,i,i,v,o,o,o}',
proargnames => '{slot_name,upto_lsn,upto_nchanges,options,lsn,xid,data}',
+ proargdefaults => '{"{}"}',
prosrc => 'pg_logical_slot_get_binary_changes' },
{ oid => '3784', descr => 'peek at changes from replication slot',
proname => 'pg_logical_slot_peek_changes', procost => '1000',
@@ -11596,6 +11651,7 @@
proallargtypes => '{name,pg_lsn,int4,_text,pg_lsn,xid,text}',
proargmodes => '{i,i,i,v,o,o,o}',
proargnames => '{slot_name,upto_lsn,upto_nchanges,options,lsn,xid,data}',
+ proargdefaults => '{"{}"}',
prosrc => 'pg_logical_slot_peek_changes' },
{ oid => '3785', descr => 'peek at binary changes from replication slot',
proname => 'pg_logical_slot_peek_binary_changes', procost => '1000',
@@ -11605,6 +11661,7 @@
proallargtypes => '{name,pg_lsn,int4,_text,pg_lsn,xid,bytea}',
proargmodes => '{i,i,i,v,o,o,o}',
proargnames => '{slot_name,upto_lsn,upto_nchanges,options,lsn,xid,data}',
+ proargdefaults => '{"{}"}',
prosrc => 'pg_logical_slot_peek_binary_changes' },
{ oid => '3878', descr => 'advance logical replication slot',
proname => 'pg_replication_slot_advance', provolatile => 'v',
@@ -11615,10 +11672,14 @@
{ oid => '3577', descr => 'emit a textual logical decoding message',
proname => 'pg_logical_emit_message', provolatile => 'v', proparallel => 'u',
prorettype => 'pg_lsn', proargtypes => 'bool text text bool',
+ proargnames => '{transactional,prefix,message,flush}',
+ proargdefaults => '{false}',
prosrc => 'pg_logical_emit_message_text' },
{ oid => '3578', descr => 'emit a binary logical decoding message',
proname => 'pg_logical_emit_message', provolatile => 'v', proparallel => 'u',
prorettype => 'pg_lsn', proargtypes => 'bool text bytea bool',
+ proargnames => '{transactional,prefix,message,flush}',
+ proargdefaults => '{false}',
prosrc => 'pg_logical_emit_message_bytea' },
{ oid => '6344',
descr => 'sync replication slots from the primary to the standby',
@@ -12268,6 +12329,7 @@
descr => 'configure session to maintain replication progress tracking for the passed in origin',
proname => 'pg_replication_origin_session_setup', provolatile => 'v',
proparallel => 'u', prorettype => 'void', proargtypes => 'text int4',
+ proargnames => '{node_name,pid}', proargdefaults => '{0}',
prosrc => 'pg_replication_origin_session_setup' },
{ oid => '6007', descr => 'teardown configured replication progress tracking',
@@ -12518,10 +12580,12 @@
{ oid => '4350', descr => 'Unicode normalization',
proname => 'normalize', prorettype => 'text', proargtypes => 'text text',
+ proargdefaults => '{NFC}',
prosrc => 'unicode_normalize_func' },
{ oid => '4351', descr => 'check Unicode normalization',
proname => 'is_normalized', prorettype => 'bool', proargtypes => 'text text',
+ proargdefaults => '{NFC}',
prosrc => 'unicode_is_normalized' },
{ oid => '6198', descr => 'unescape Unicode characters',
--
2.43.7
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 18:54 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 19:13 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 19:47 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 20:02 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 20:31 ` Re: generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 21:46 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-17 01:36 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-17 15:13 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-17 15:24 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-17 18:41 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
@ 2026-02-17 19:07 ` Andrew Dunstan <[email protected]>
0 siblings, 0 replies; 18+ messages in thread
From: Andrew Dunstan @ 2026-02-17 19:07 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Andres Freund <[email protected]>; +Cc: pgsql-hackers
On 2026-02-17 Tu 1:41 PM, Tom Lane wrote:
> Here's the promised v2, which addresses all your review comments.
>
> With respect to the list of supported types in bki.sgml: I wonder if
> we should just drop that, because it evidently hasn't been maintained
> well. It wasn't at all in sync with the actual contents of TypInfo[].
> I made it be so, but ...
>
> Poking further at that, I found that there were a lot of TypInfo[]
> entries that were not actually used and seem to have just been
> cargo-culted in. So this patch removes all the ones that aren't
> demonstrably necessary to get through initdb. Maybe that's too
> aggressive, but in view of the potential for maintenance errors
> (cf 7cdb633c8) I don't think we should be carrying unused entries
> there.
>
>
That makes sense. I haven't tested it, but the code looks pretty sane.
Nice job. I guess if we ever want to specify a default that's not a
Const for some reason we'd have to fall back on the system_views
mechanism. But that's not very likely.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 18:54 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 19:13 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 19:47 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
@ 2026-02-16 23:39 ` Álvaro Herrera <[email protected]>
2026-02-17 08:49 ` Re: generating function default settings from pg_proc.dat Corey Huinker <[email protected]>
1 sibling, 1 reply; 18+ messages in thread
From: Álvaro Herrera @ 2026-02-16 23:39 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Tom Lane <[email protected]>; Andrew Dunstan <[email protected]>; pgsql-hackers
On 2026-Feb-16, Andres Freund wrote:
> could be
>
> { oid => '3786', descr => 'set up a logical replication slot',
> proname => 'pg_create_logical_replication_slot', provolatile => 'v',
> proparallel => 'u',
> proargs => [
> {type => 'name', name => 'slot_name'},
> {type => 'name', name => 'plugin'},
> {type => 'bool', name => 'temporary', default => 'false'},
> {type => 'bool', name => 'twophase', default => 'false'},
> {type => 'bool', name => 'failover', default => 'false'},
> ],
> prorettype => [
> {type => 'name', name => 'slot_name'},
> {type => 'pg_lsn', name => 'lsn'},
> ]
> }
This is pretty much the sort of thing I was imagining when I read
Corey's argumentation. +1 for something along these lines.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Pero la cosa no es muy grave ..." (le petit Nicolas -- René Goscinny)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 18:54 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 19:13 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 19:47 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 23:39 ` Re: generating function default settings from pg_proc.dat Álvaro Herrera <[email protected]>
@ 2026-02-17 08:49 ` Corey Huinker <[email protected]>
0 siblings, 0 replies; 18+ messages in thread
From: Corey Huinker @ 2026-02-17 08:49 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: Andres Freund <[email protected]>; Tom Lane <[email protected]>; Andrew Dunstan <[email protected]>; pgsql-hackers
On Tue, Feb 17, 2026 at 1:37 AM Álvaro Herrera <[email protected]> wrote:
> On 2026-Feb-16, Andres Freund wrote:
>
> > could be
> >
> > { oid => '3786', descr => 'set up a logical replication slot',
> > proname => 'pg_create_logical_replication_slot', provolatile => 'v',
> > proparallel => 'u',
> > proargs => [
> > {type => 'name', name => 'slot_name'},
> > {type => 'name', name => 'plugin'},
> > {type => 'bool', name => 'temporary', default => 'false'},
> > {type => 'bool', name => 'twophase', default => 'false'},
> > {type => 'bool', name => 'failover', default => 'false'},
> > ],
> > prorettype => [
> > {type => 'name', name => 'slot_name'},
> > {type => 'pg_lsn', name => 'lsn'},
> > ]
> > }
>
> This is pretty much the sort of thing I was imagining when I read
> Corey's argumentation. +1 for something along these lines.
I like this a lot too, but I'm noticing that with each iteration we're
getting closer to re-inventing SQL. Would it make sense in the long run to
have a mode on the CREATE FUNCTION command that cues initdb to create the
minimal function skeleton with prescribed oid on the first pass, but then
stores the defer-able parts (if any) for a later pass, perhaps in parallel?
Then we wouldn't have to worry about how to model all future additions to
CREATE FUNCTION, and instead focus on what parts of creating the function
need to be in the bootstrap pass.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: generating function default settings from pg_proc.dat
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 18:54 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
@ 2026-02-16 23:19 ` Michael Paquier <[email protected]>
1 sibling, 0 replies; 18+ messages in thread
From: Michael Paquier @ 2026-02-16 23:19 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; pgsql-hackers
On Mon, Feb 16, 2026 at 01:54:49PM -0500, Andres Freund wrote:
> On 2026-02-16 12:31:37 -0500, Andrew Dunstan wrote:
>> Motivated by Bug 19409 [1] I decided to do something about a wart that has
>> bugged me for a while, namely the requirement to write stuff in
>> system_views.sql if you need to specify default values for function
>> arguments.
>
> I'm one more person this has been bugging.
Please add me on the stack of people who have been annoyed by that
largely more than once. Glad to see that this could be enforced in
the bki scripts.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2026-02-17 19:07 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-10-30 05:24 [PATCH] Remove self-joins. Andrey Lepikhov <[email protected]>
2026-02-16 17:31 generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 18:07 ` Re: generating function default settings from pg_proc.dat Daniel Gustafsson <[email protected]>
2026-02-16 18:08 ` Re: generating function default settings from pg_proc.dat Corey Huinker <[email protected]>
2026-02-16 18:54 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 19:13 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 19:47 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-16 20:02 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-16 20:31 ` Re: generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 21:46 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-17 01:36 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-17 15:13 ` Re: generating function default settings from pg_proc.dat Andres Freund <[email protected]>
2026-02-17 15:24 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-17 18:41 ` Re: generating function default settings from pg_proc.dat Tom Lane <[email protected]>
2026-02-17 19:07 ` Re: generating function default settings from pg_proc.dat Andrew Dunstan <[email protected]>
2026-02-16 23:39 ` Re: generating function default settings from pg_proc.dat Álvaro Herrera <[email protected]>
2026-02-17 08:49 ` Re: generating function default settings from pg_proc.dat Corey Huinker <[email protected]>
2026-02-16 23:19 ` Re: generating function default settings from pg_proc.dat Michael Paquier <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox