agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feed[PATCH v7] Fix orderby handling in postgres_fdw
5+ messages / 3 participants
[nested] [flat]
* [PATCH v7] Fix orderby handling in postgres_fdw
@ 2021-09-06 07:54 Ronan Dunklau <ronan.dunklau@aiven.io>
0 siblings, 0 replies; 5+ messages in thread
From: Ronan Dunklau @ 2021-09-06 07:54 UTC (permalink / raw)
The logic for pushing down order bys in postgres fdw didn't take into
account the specific operator used, and as such a pushed-down order by
could return wrong results.
---
contrib/postgres_fdw/deparse.c | 156 +++++++++++++-----
.../postgres_fdw/expected/postgres_fdw.out | 23 +++
contrib/postgres_fdw/postgres_fdw.c | 28 ++--
contrib/postgres_fdw/postgres_fdw.h | 11 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 8 +
src/backend/optimizer/path/equivclass.c | 26 ++-
src/include/optimizer/paths.h | 2 +
7 files changed, 187 insertions(+), 67 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index d98bd66681..fb1b5f9d9b 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -37,9 +37,11 @@
#include "access/sysattr.h"
#include "access/table.h"
#include "catalog/pg_aggregate.h"
+#include "catalog/pg_amop.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_opfamily.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
@@ -47,6 +49,7 @@
#include "nodes/nodeFuncs.h"
#include "nodes/plannodes.h"
#include "optimizer/optimizer.h"
+#include "optimizer/paths.h"
#include "optimizer/prep.h"
#include "optimizer/tlist.h"
#include "parser/parsetree.h"
@@ -182,6 +185,8 @@ static void deparseRangeTblRef(StringInfo buf, PlannerInfo *root,
Index ignore_rel, List **ignore_conds, List **params_list);
static void deparseAggref(Aggref *node, deparse_expr_cxt *context);
static void appendGroupByClause(List *tlist, deparse_expr_cxt *context);
+static void appendOrderBySuffix(Oid sortop, Oid sortcoltype, bool nulls_first,
+ deparse_expr_cxt *context);
static void appendAggOrderBy(List *orderList, List *targetList,
deparse_expr_cxt *context);
static void appendFunctionName(Oid funcid, deparse_expr_cxt *context);
@@ -1037,6 +1042,41 @@ is_foreign_param(PlannerInfo *root,
return false;
}
+/*
+ * Returns true if it's safe to push down a sort as described by 'pathkey' to
+ * the foreign server
+ */
+bool
+is_foreign_pathkey(PlannerInfo *root,
+ RelOptInfo *baserel,
+ PathKey *pathkey)
+{
+ EquivalenceClass *pathkey_ec = pathkey->pk_eclass;
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) baserel->fdw_private;
+ Expr *em_expr;
+
+ /*
+ * is_foreign_expr would detect volatile expressions as well, but checking
+ * ec_has_volatile here saves some cycles.
+ */
+ if (pathkey_ec->ec_has_volatile)
+ return false;
+
+ /* can't push down the sort if the pathkey's opfamily is not shippable */
+ if (!is_shippable(pathkey->pk_opfamily, OperatorFamilyRelationId, fpinfo))
+ return false;
+
+ em_expr = find_em_expr_for_rel(pathkey_ec, baserel);
+ if (em_expr == NULL)
+ return false;
+
+ /*
+ * Finally, determine if it's safe to evaluate the found expr on the
+ * foreign server.
+ */
+ return is_foreign_expr(root, baserel, em_expr);
+}
+
/*
* Convert type OID + typmod info into a type name we can ship to the remote
* server. Someplace else had better have verified that this type name is
@@ -3331,6 +3371,45 @@ deparseAggref(Aggref *node, deparse_expr_cxt *context)
appendStringInfoChar(buf, ')');
}
+/*
+ * Append the ASC, DESC, USING <OPERATOR> and NULLS FIRST / NULLS LAST part
+ * of the ORDER BY clause
+ */
+static void
+appendOrderBySuffix(Oid sortop, Oid sortcoltype, bool nulls_first,
+ deparse_expr_cxt *context)
+{
+ StringInfo buf = context->buf;
+ TypeCacheEntry *typentry;
+
+ typentry = lookup_type_cache(sortcoltype, TYPECACHE_LT_OPR | TYPECACHE_GT_OPR);
+
+ if (sortop == typentry->lt_opr)
+ appendStringInfoString(buf, " ASC");
+ else if (sortop == typentry->gt_opr)
+ appendStringInfoString(buf, " DESC");
+ else
+ {
+ HeapTuple opertup;
+ Form_pg_operator operform;
+
+ appendStringInfoString(buf, " USING ");
+
+ /* Append operator name. */
+ opertup = SearchSysCache1(OPEROID, ObjectIdGetDatum(sortop));
+ if (!HeapTupleIsValid(opertup))
+ elog(ERROR, "cache lookup failed for operator %u", sortop);
+ operform = (Form_pg_operator) GETSTRUCT(opertup);
+ deparseOperatorName(buf, operform);
+ ReleaseSysCache(opertup);
+ }
+
+ if (nulls_first)
+ appendStringInfoString(buf, " NULLS FIRST");
+ else
+ appendStringInfoString(buf, " NULLS LAST");
+}
+
/*
* Append ORDER BY within aggregate function.
*/
@@ -3346,7 +3425,6 @@ appendAggOrderBy(List *orderList, List *targetList, deparse_expr_cxt *context)
SortGroupClause *srt = (SortGroupClause *) lfirst(lc);
Node *sortexpr;
Oid sortcoltype;
- TypeCacheEntry *typentry;
if (!first)
appendStringInfoString(buf, ", ");
@@ -3356,32 +3434,8 @@ appendAggOrderBy(List *orderList, List *targetList, deparse_expr_cxt *context)
false, context);
sortcoltype = exprType(sortexpr);
/* See whether operator is default < or > for datatype */
- typentry = lookup_type_cache(sortcoltype,
- TYPECACHE_LT_OPR | TYPECACHE_GT_OPR);
- if (srt->sortop == typentry->lt_opr)
- appendStringInfoString(buf, " ASC");
- else if (srt->sortop == typentry->gt_opr)
- appendStringInfoString(buf, " DESC");
- else
- {
- HeapTuple opertup;
- Form_pg_operator operform;
-
- appendStringInfoString(buf, " USING ");
-
- /* Append operator name. */
- opertup = SearchSysCache1(OPEROID, ObjectIdGetDatum(srt->sortop));
- if (!HeapTupleIsValid(opertup))
- elog(ERROR, "cache lookup failed for operator %u", srt->sortop);
- operform = (Form_pg_operator) GETSTRUCT(opertup);
- deparseOperatorName(buf, operform);
- ReleaseSysCache(opertup);
- }
-
- if (srt->nulls_first)
- appendStringInfoString(buf, " NULLS FIRST");
- else
- appendStringInfoString(buf, " NULLS LAST");
+ appendOrderBySuffix(srt->sortop, sortcoltype, srt->nulls_first,
+ context);
}
}
@@ -3486,7 +3540,11 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
foreach(lcell, pathkeys)
{
PathKey *pathkey = lfirst(lcell);
+ EquivalenceMember *em;
Expr *em_expr;
+ HeapTuple tuple;
+ Oid oprid;
+ bool isNull;
if (has_final_sort)
{
@@ -3494,26 +3552,44 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
* By construction, context->foreignrel is the input relation to
* the final sort.
*/
- em_expr = find_em_expr_for_input_target(context->root,
- pathkey->pk_eclass,
- context->foreignrel->reltarget);
+ em = find_em_for_input_target(context->root,
+ pathkey->pk_eclass,
+ context->foreignrel->reltarget);
}
else
- em_expr = find_em_expr_for_rel(pathkey->pk_eclass, baserel);
+ em = find_em_for_rel(pathkey->pk_eclass, baserel);
+
+ em_expr = em->em_expr;
+
+ /*
+ * Lookup the operator corresponding to the strategy in the opclass.
+ * The datatype used by the opfamily is not necessarily the same as
+ * the expression type (for array types for example).
+ */
+ tuple = SearchSysCache4(AMOPSTRATEGY,
+ ObjectIdGetDatum(pathkey->pk_opfamily),
+ ObjectIdGetDatum(em->em_datatype),
+ ObjectIdGetDatum(em->em_datatype),
+ Int16GetDatum(pathkey->pk_strategy));
+
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "missing operator %d(%u,%u) in opfamily %u",
+ pathkey->pk_strategy, em->em_datatype, em->em_datatype,
+ pathkey->pk_opfamily);
- Assert(em_expr != NULL);
+ oprid = DatumGetObjectId(SysCacheGetAttr(AMOPSTRATEGY, tuple,
+ Anum_pg_amop_amopopr, &isNull));
+ ReleaseSysCache(tuple);
appendStringInfoString(buf, delim);
deparseExpr(em_expr, context);
- if (pathkey->pk_strategy == BTLessStrategyNumber)
- appendStringInfoString(buf, " ASC");
- else
- appendStringInfoString(buf, " DESC");
- if (pathkey->pk_nulls_first)
- appendStringInfoString(buf, " NULLS FIRST");
- else
- appendStringInfoString(buf, " NULLS LAST");
+ /*
+ * Here we need to use the expression type to compare against the
+ * default btree sort operator.
+ */
+ appendOrderBySuffix(oprid, exprType((Node *) em_expr),
+ pathkey->pk_nulls_first, context);
delim = ", ";
}
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index e3ee30f1aa..1f44c365ef 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -3258,6 +3258,19 @@ select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6
Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE (("C 1" < 100)) AND ((c2 = 6))
(6 rows)
+-- Ensure that we don't push down an ORDER BY with a non-shippable operator
+explain (verbose, costs off)
+select * from ft2 order by c1 using operator(public.<^);
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Sort
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Sort Key: ft2.c1 USING <^
+ -> Foreign Scan on public.ft2
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
+(6 rows)
+
-- Update local stats on ft2
ANALYZE ft2;
-- Add into extension
@@ -3285,6 +3298,16 @@ select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6
{6,16,26,36,46,56,66,76,86,96}
(1 row)
+-- Ensure that the ORDER BY is pushed to the foreign server
+explain (verbose, costs off)
+select * from ft2 order by c1 using operator(public.<^);
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft2
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY "C 1" USING OPERATOR(public.<^) NULLS LAST
+(3 rows)
+
-- Remove from extension
alter extension postgres_fdw drop operator class my_op_class using btree;
alter extension postgres_fdw drop function my_op_cmp(a int, b int);
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 4bdab30a73..1dd4779185 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -917,8 +917,6 @@ get_useful_pathkeys_for_relation(PlannerInfo *root, RelOptInfo *rel)
foreach(lc, root->query_pathkeys)
{
PathKey *pathkey = (PathKey *) lfirst(lc);
- EquivalenceClass *pathkey_ec = pathkey->pk_eclass;
- Expr *em_expr;
/*
* The planner and executor don't have any clever strategy for
@@ -926,13 +924,8 @@ get_useful_pathkeys_for_relation(PlannerInfo *root, RelOptInfo *rel)
* getting it to be sorted by all of those pathkeys. We'll just
* end up resorting the entire data set. So, unless we can push
* down all of the query pathkeys, forget it.
- *
- * is_foreign_expr would detect volatile expressions as well, but
- * checking ec_has_volatile here saves some cycles.
*/
- if (pathkey_ec->ec_has_volatile ||
- !(em_expr = find_em_expr_for_rel(pathkey_ec, rel)) ||
- !is_foreign_expr(root, rel, em_expr))
+ if (!is_foreign_pathkey(root, rel, pathkey))
{
query_pathkeys_ok = false;
break;
@@ -6540,9 +6533,9 @@ add_foreign_ordered_paths(PlannerInfo *root, RelOptInfo *input_rel,
return;
/* Get the sort expression for the pathkey_ec */
- sort_expr = find_em_expr_for_input_target(root,
- pathkey_ec,
- input_rel->reltarget);
+ sort_expr = find_em_for_input_target(root,
+ pathkey_ec,
+ input_rel->reltarget)->em_expr;
/* If it's unsafe to remote, we cannot push down the final sort */
if (!is_foreign_expr(root, input_rel, sort_expr))
@@ -7332,13 +7325,12 @@ conversion_error_callback(void *arg)
}
/*
- * Find an equivalence class member expression to be computed as a sort column
- * in the given target.
+ * Find an equivalence class member to be computed as a sort column in the
+ * given target.
*/
-Expr *
-find_em_expr_for_input_target(PlannerInfo *root,
- EquivalenceClass *ec,
- PathTarget *target)
+EquivalenceMember *
+find_em_for_input_target(PlannerInfo *root, EquivalenceClass *ec,
+ PathTarget *target)
{
ListCell *lc1;
int i;
@@ -7383,7 +7375,7 @@ find_em_expr_for_input_target(PlannerInfo *root,
em_expr = ((RelabelType *) em_expr)->arg;
if (equal(em_expr, expr))
- return em->em_expr;
+ return em;
}
i++;
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index ca83306af9..9930226b6f 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -171,6 +171,9 @@ extern bool is_foreign_expr(PlannerInfo *root,
extern bool is_foreign_param(PlannerInfo *root,
RelOptInfo *baserel,
Expr *expr);
+extern bool is_foreign_pathkey(PlannerInfo *root,
+ RelOptInfo *baserel,
+ PathKey *pathkey);
extern void deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
Index rtindex, Relation rel,
List *targetAttrs, bool doNothing,
@@ -213,10 +216,10 @@ extern void deparseTruncateSql(StringInfo buf,
DropBehavior behavior,
bool restart_seqs);
extern void deparseStringLiteral(StringInfo buf, const char *val);
-extern Expr *find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel);
-extern Expr *find_em_expr_for_input_target(PlannerInfo *root,
- EquivalenceClass *ec,
- PathTarget *target);
+extern EquivalenceMember *find_em_for_rel(EquivalenceClass *ec, RelOptInfo *rel);
+extern EquivalenceMember *find_em_for_input_target(PlannerInfo *root,
+ EquivalenceClass *ec,
+ PathTarget *target);
extern List *build_tlist_to_deparse(RelOptInfo *foreignrel);
extern void deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root,
RelOptInfo *foreignrel, List *tlist,
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 30b5175da5..1bbe1212c4 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -902,6 +902,10 @@ create operator class my_op_class for type int using btree family my_op_family a
explain (verbose, costs off)
select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2;
+-- Ensure that we don't push down an ORDER BY with a non-shippable operator
+explain (verbose, costs off)
+select * from ft2 order by c1 using operator(public.<^);
+
-- Update local stats on ft2
ANALYZE ft2;
@@ -919,6 +923,10 @@ explain (verbose, costs off)
select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2;
select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2;
+-- Ensure that the ORDER BY is pushed to the foreign server
+explain (verbose, costs off)
+select * from ft2 order by c1 using operator(public.<^);
+
-- Remove from extension
alter extension postgres_fdw drop operator class my_op_class using btree;
alter extension postgres_fdw drop function my_op_cmp(a int, b int);
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 6f1abbe47d..b367f18eab 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -932,11 +932,11 @@ is_exprlist_member(Expr *node, List *exprs)
}
/*
- * Find an equivalence class member expression, all of whose Vars, come from
- * the indicated relation.
+ * Find an equivalence class member, all of whose Vars, come from the
+ * indicated relation.
*/
-Expr *
-find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
+EquivalenceMember *
+find_em_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
{
ListCell *lc_em;
@@ -952,7 +952,7 @@ find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
* taken entirely from this relation, we'll be content to choose
* any one of those.
*/
- return em->em_expr;
+ return em;
}
}
@@ -960,6 +960,22 @@ find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
return NULL;
}
+/*
+ * Find an equivalence class member expression, all of whose Vars, come from
+ * the indicated relation.
+ */
+Expr *
+find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
+{
+ EquivalenceMember *em = find_em_for_rel(ec, rel);
+
+ if (em != NULL)
+ return em->em_expr;
+
+ return NULL;
+}
+
+
/*
* relation_can_be_sorted_early
* Can this relation be sorted on this EC before the final output step?
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index f1d111063c..5a2bbc87e1 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -144,6 +144,8 @@ extern EquivalenceMember *find_computable_ec_member(PlannerInfo *root,
Relids relids,
bool require_parallel_safe);
extern Expr *find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel);
+extern EquivalenceMember *find_em_for_rel(EquivalenceClass *ec,
+ RelOptInfo *rel);
extern bool relation_can_be_sorted_early(PlannerInfo *root, RelOptInfo *rel,
EquivalenceClass *ec,
bool require_parallel_safe);
--
2.33.0
--nextPart1657077.ZGz24vUy9i--
^ permalink raw reply [nested|flat] 5+ messages in thread
* [PATCH v8] Fix orderby handling in postgres_fdw
@ 2021-09-06 07:54 Ronan Dunklau <ronan.dunklau@aiven.io>
0 siblings, 0 replies; 5+ messages in thread
From: Ronan Dunklau @ 2021-09-06 07:54 UTC (permalink / raw)
The logic for pushing down order bys in postgres fdw didn't take into
account the specific operator used, and as such a pushed-down order by
could return wrong results.
This patch looks up the original operator associated to the pathkey
opfamily, and checks that it actually exists on the foreign side.
If it does, the operator is then used to rebuild an equivalent USING
<operator> clause.
---
contrib/postgres_fdw/deparse.c | 156 +++++++++++++-----
.../postgres_fdw/expected/postgres_fdw.out | 23 +++
contrib/postgres_fdw/postgres_fdw.c | 28 ++--
contrib/postgres_fdw/postgres_fdw.h | 11 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 8 +
src/backend/optimizer/path/equivclass.c | 26 ++-
src/include/optimizer/paths.h | 2 +
7 files changed, 187 insertions(+), 67 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index d98bd66681..fb1b5f9d9b 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -37,9 +37,11 @@
#include "access/sysattr.h"
#include "access/table.h"
#include "catalog/pg_aggregate.h"
+#include "catalog/pg_amop.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_opfamily.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
@@ -47,6 +49,7 @@
#include "nodes/nodeFuncs.h"
#include "nodes/plannodes.h"
#include "optimizer/optimizer.h"
+#include "optimizer/paths.h"
#include "optimizer/prep.h"
#include "optimizer/tlist.h"
#include "parser/parsetree.h"
@@ -182,6 +185,8 @@ static void deparseRangeTblRef(StringInfo buf, PlannerInfo *root,
Index ignore_rel, List **ignore_conds, List **params_list);
static void deparseAggref(Aggref *node, deparse_expr_cxt *context);
static void appendGroupByClause(List *tlist, deparse_expr_cxt *context);
+static void appendOrderBySuffix(Oid sortop, Oid sortcoltype, bool nulls_first,
+ deparse_expr_cxt *context);
static void appendAggOrderBy(List *orderList, List *targetList,
deparse_expr_cxt *context);
static void appendFunctionName(Oid funcid, deparse_expr_cxt *context);
@@ -1037,6 +1042,41 @@ is_foreign_param(PlannerInfo *root,
return false;
}
+/*
+ * Returns true if it's safe to push down a sort as described by 'pathkey' to
+ * the foreign server
+ */
+bool
+is_foreign_pathkey(PlannerInfo *root,
+ RelOptInfo *baserel,
+ PathKey *pathkey)
+{
+ EquivalenceClass *pathkey_ec = pathkey->pk_eclass;
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) baserel->fdw_private;
+ Expr *em_expr;
+
+ /*
+ * is_foreign_expr would detect volatile expressions as well, but checking
+ * ec_has_volatile here saves some cycles.
+ */
+ if (pathkey_ec->ec_has_volatile)
+ return false;
+
+ /* can't push down the sort if the pathkey's opfamily is not shippable */
+ if (!is_shippable(pathkey->pk_opfamily, OperatorFamilyRelationId, fpinfo))
+ return false;
+
+ em_expr = find_em_expr_for_rel(pathkey_ec, baserel);
+ if (em_expr == NULL)
+ return false;
+
+ /*
+ * Finally, determine if it's safe to evaluate the found expr on the
+ * foreign server.
+ */
+ return is_foreign_expr(root, baserel, em_expr);
+}
+
/*
* Convert type OID + typmod info into a type name we can ship to the remote
* server. Someplace else had better have verified that this type name is
@@ -3331,6 +3371,45 @@ deparseAggref(Aggref *node, deparse_expr_cxt *context)
appendStringInfoChar(buf, ')');
}
+/*
+ * Append the ASC, DESC, USING <OPERATOR> and NULLS FIRST / NULLS LAST part
+ * of the ORDER BY clause
+ */
+static void
+appendOrderBySuffix(Oid sortop, Oid sortcoltype, bool nulls_first,
+ deparse_expr_cxt *context)
+{
+ StringInfo buf = context->buf;
+ TypeCacheEntry *typentry;
+
+ typentry = lookup_type_cache(sortcoltype, TYPECACHE_LT_OPR | TYPECACHE_GT_OPR);
+
+ if (sortop == typentry->lt_opr)
+ appendStringInfoString(buf, " ASC");
+ else if (sortop == typentry->gt_opr)
+ appendStringInfoString(buf, " DESC");
+ else
+ {
+ HeapTuple opertup;
+ Form_pg_operator operform;
+
+ appendStringInfoString(buf, " USING ");
+
+ /* Append operator name. */
+ opertup = SearchSysCache1(OPEROID, ObjectIdGetDatum(sortop));
+ if (!HeapTupleIsValid(opertup))
+ elog(ERROR, "cache lookup failed for operator %u", sortop);
+ operform = (Form_pg_operator) GETSTRUCT(opertup);
+ deparseOperatorName(buf, operform);
+ ReleaseSysCache(opertup);
+ }
+
+ if (nulls_first)
+ appendStringInfoString(buf, " NULLS FIRST");
+ else
+ appendStringInfoString(buf, " NULLS LAST");
+}
+
/*
* Append ORDER BY within aggregate function.
*/
@@ -3346,7 +3425,6 @@ appendAggOrderBy(List *orderList, List *targetList, deparse_expr_cxt *context)
SortGroupClause *srt = (SortGroupClause *) lfirst(lc);
Node *sortexpr;
Oid sortcoltype;
- TypeCacheEntry *typentry;
if (!first)
appendStringInfoString(buf, ", ");
@@ -3356,32 +3434,8 @@ appendAggOrderBy(List *orderList, List *targetList, deparse_expr_cxt *context)
false, context);
sortcoltype = exprType(sortexpr);
/* See whether operator is default < or > for datatype */
- typentry = lookup_type_cache(sortcoltype,
- TYPECACHE_LT_OPR | TYPECACHE_GT_OPR);
- if (srt->sortop == typentry->lt_opr)
- appendStringInfoString(buf, " ASC");
- else if (srt->sortop == typentry->gt_opr)
- appendStringInfoString(buf, " DESC");
- else
- {
- HeapTuple opertup;
- Form_pg_operator operform;
-
- appendStringInfoString(buf, " USING ");
-
- /* Append operator name. */
- opertup = SearchSysCache1(OPEROID, ObjectIdGetDatum(srt->sortop));
- if (!HeapTupleIsValid(opertup))
- elog(ERROR, "cache lookup failed for operator %u", srt->sortop);
- operform = (Form_pg_operator) GETSTRUCT(opertup);
- deparseOperatorName(buf, operform);
- ReleaseSysCache(opertup);
- }
-
- if (srt->nulls_first)
- appendStringInfoString(buf, " NULLS FIRST");
- else
- appendStringInfoString(buf, " NULLS LAST");
+ appendOrderBySuffix(srt->sortop, sortcoltype, srt->nulls_first,
+ context);
}
}
@@ -3486,7 +3540,11 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
foreach(lcell, pathkeys)
{
PathKey *pathkey = lfirst(lcell);
+ EquivalenceMember *em;
Expr *em_expr;
+ HeapTuple tuple;
+ Oid oprid;
+ bool isNull;
if (has_final_sort)
{
@@ -3494,26 +3552,44 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
* By construction, context->foreignrel is the input relation to
* the final sort.
*/
- em_expr = find_em_expr_for_input_target(context->root,
- pathkey->pk_eclass,
- context->foreignrel->reltarget);
+ em = find_em_for_input_target(context->root,
+ pathkey->pk_eclass,
+ context->foreignrel->reltarget);
}
else
- em_expr = find_em_expr_for_rel(pathkey->pk_eclass, baserel);
+ em = find_em_for_rel(pathkey->pk_eclass, baserel);
+
+ em_expr = em->em_expr;
+
+ /*
+ * Lookup the operator corresponding to the strategy in the opclass.
+ * The datatype used by the opfamily is not necessarily the same as
+ * the expression type (for array types for example).
+ */
+ tuple = SearchSysCache4(AMOPSTRATEGY,
+ ObjectIdGetDatum(pathkey->pk_opfamily),
+ ObjectIdGetDatum(em->em_datatype),
+ ObjectIdGetDatum(em->em_datatype),
+ Int16GetDatum(pathkey->pk_strategy));
+
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "missing operator %d(%u,%u) in opfamily %u",
+ pathkey->pk_strategy, em->em_datatype, em->em_datatype,
+ pathkey->pk_opfamily);
- Assert(em_expr != NULL);
+ oprid = DatumGetObjectId(SysCacheGetAttr(AMOPSTRATEGY, tuple,
+ Anum_pg_amop_amopopr, &isNull));
+ ReleaseSysCache(tuple);
appendStringInfoString(buf, delim);
deparseExpr(em_expr, context);
- if (pathkey->pk_strategy == BTLessStrategyNumber)
- appendStringInfoString(buf, " ASC");
- else
- appendStringInfoString(buf, " DESC");
- if (pathkey->pk_nulls_first)
- appendStringInfoString(buf, " NULLS FIRST");
- else
- appendStringInfoString(buf, " NULLS LAST");
+ /*
+ * Here we need to use the expression type to compare against the
+ * default btree sort operator.
+ */
+ appendOrderBySuffix(oprid, exprType((Node *) em_expr),
+ pathkey->pk_nulls_first, context);
delim = ", ";
}
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index e3ee30f1aa..1f44c365ef 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -3258,6 +3258,19 @@ select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6
Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE (("C 1" < 100)) AND ((c2 = 6))
(6 rows)
+-- Ensure that we don't push down an ORDER BY with a non-shippable operator
+explain (verbose, costs off)
+select * from ft2 order by c1 using operator(public.<^);
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Sort
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Sort Key: ft2.c1 USING <^
+ -> Foreign Scan on public.ft2
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
+(6 rows)
+
-- Update local stats on ft2
ANALYZE ft2;
-- Add into extension
@@ -3285,6 +3298,16 @@ select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6
{6,16,26,36,46,56,66,76,86,96}
(1 row)
+-- Ensure that the ORDER BY is pushed to the foreign server
+explain (verbose, costs off)
+select * from ft2 order by c1 using operator(public.<^);
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft2
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY "C 1" USING OPERATOR(public.<^) NULLS LAST
+(3 rows)
+
-- Remove from extension
alter extension postgres_fdw drop operator class my_op_class using btree;
alter extension postgres_fdw drop function my_op_cmp(a int, b int);
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 4bdab30a73..1dd4779185 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -917,8 +917,6 @@ get_useful_pathkeys_for_relation(PlannerInfo *root, RelOptInfo *rel)
foreach(lc, root->query_pathkeys)
{
PathKey *pathkey = (PathKey *) lfirst(lc);
- EquivalenceClass *pathkey_ec = pathkey->pk_eclass;
- Expr *em_expr;
/*
* The planner and executor don't have any clever strategy for
@@ -926,13 +924,8 @@ get_useful_pathkeys_for_relation(PlannerInfo *root, RelOptInfo *rel)
* getting it to be sorted by all of those pathkeys. We'll just
* end up resorting the entire data set. So, unless we can push
* down all of the query pathkeys, forget it.
- *
- * is_foreign_expr would detect volatile expressions as well, but
- * checking ec_has_volatile here saves some cycles.
*/
- if (pathkey_ec->ec_has_volatile ||
- !(em_expr = find_em_expr_for_rel(pathkey_ec, rel)) ||
- !is_foreign_expr(root, rel, em_expr))
+ if (!is_foreign_pathkey(root, rel, pathkey))
{
query_pathkeys_ok = false;
break;
@@ -6540,9 +6533,9 @@ add_foreign_ordered_paths(PlannerInfo *root, RelOptInfo *input_rel,
return;
/* Get the sort expression for the pathkey_ec */
- sort_expr = find_em_expr_for_input_target(root,
- pathkey_ec,
- input_rel->reltarget);
+ sort_expr = find_em_for_input_target(root,
+ pathkey_ec,
+ input_rel->reltarget)->em_expr;
/* If it's unsafe to remote, we cannot push down the final sort */
if (!is_foreign_expr(root, input_rel, sort_expr))
@@ -7332,13 +7325,12 @@ conversion_error_callback(void *arg)
}
/*
- * Find an equivalence class member expression to be computed as a sort column
- * in the given target.
+ * Find an equivalence class member to be computed as a sort column in the
+ * given target.
*/
-Expr *
-find_em_expr_for_input_target(PlannerInfo *root,
- EquivalenceClass *ec,
- PathTarget *target)
+EquivalenceMember *
+find_em_for_input_target(PlannerInfo *root, EquivalenceClass *ec,
+ PathTarget *target)
{
ListCell *lc1;
int i;
@@ -7383,7 +7375,7 @@ find_em_expr_for_input_target(PlannerInfo *root,
em_expr = ((RelabelType *) em_expr)->arg;
if (equal(em_expr, expr))
- return em->em_expr;
+ return em;
}
i++;
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index ca83306af9..9930226b6f 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -171,6 +171,9 @@ extern bool is_foreign_expr(PlannerInfo *root,
extern bool is_foreign_param(PlannerInfo *root,
RelOptInfo *baserel,
Expr *expr);
+extern bool is_foreign_pathkey(PlannerInfo *root,
+ RelOptInfo *baserel,
+ PathKey *pathkey);
extern void deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
Index rtindex, Relation rel,
List *targetAttrs, bool doNothing,
@@ -213,10 +216,10 @@ extern void deparseTruncateSql(StringInfo buf,
DropBehavior behavior,
bool restart_seqs);
extern void deparseStringLiteral(StringInfo buf, const char *val);
-extern Expr *find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel);
-extern Expr *find_em_expr_for_input_target(PlannerInfo *root,
- EquivalenceClass *ec,
- PathTarget *target);
+extern EquivalenceMember *find_em_for_rel(EquivalenceClass *ec, RelOptInfo *rel);
+extern EquivalenceMember *find_em_for_input_target(PlannerInfo *root,
+ EquivalenceClass *ec,
+ PathTarget *target);
extern List *build_tlist_to_deparse(RelOptInfo *foreignrel);
extern void deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root,
RelOptInfo *foreignrel, List *tlist,
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 30b5175da5..1bbe1212c4 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -902,6 +902,10 @@ create operator class my_op_class for type int using btree family my_op_family a
explain (verbose, costs off)
select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2;
+-- Ensure that we don't push down an ORDER BY with a non-shippable operator
+explain (verbose, costs off)
+select * from ft2 order by c1 using operator(public.<^);
+
-- Update local stats on ft2
ANALYZE ft2;
@@ -919,6 +923,10 @@ explain (verbose, costs off)
select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2;
select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2;
+-- Ensure that the ORDER BY is pushed to the foreign server
+explain (verbose, costs off)
+select * from ft2 order by c1 using operator(public.<^);
+
-- Remove from extension
alter extension postgres_fdw drop operator class my_op_class using btree;
alter extension postgres_fdw drop function my_op_cmp(a int, b int);
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 6f1abbe47d..b367f18eab 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -932,11 +932,11 @@ is_exprlist_member(Expr *node, List *exprs)
}
/*
- * Find an equivalence class member expression, all of whose Vars, come from
- * the indicated relation.
+ * Find an equivalence class member, all of whose Vars, come from the
+ * indicated relation.
*/
-Expr *
-find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
+EquivalenceMember *
+find_em_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
{
ListCell *lc_em;
@@ -952,7 +952,7 @@ find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
* taken entirely from this relation, we'll be content to choose
* any one of those.
*/
- return em->em_expr;
+ return em;
}
}
@@ -960,6 +960,22 @@ find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
return NULL;
}
+/*
+ * Find an equivalence class member expression, all of whose Vars, come from
+ * the indicated relation.
+ */
+Expr *
+find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
+{
+ EquivalenceMember *em = find_em_for_rel(ec, rel);
+
+ if (em != NULL)
+ return em->em_expr;
+
+ return NULL;
+}
+
+
/*
* relation_can_be_sorted_early
* Can this relation be sorted on this EC before the final output step?
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index f1d111063c..5a2bbc87e1 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -144,6 +144,8 @@ extern EquivalenceMember *find_computable_ec_member(PlannerInfo *root,
Relids relids,
bool require_parallel_safe);
extern Expr *find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel);
+extern EquivalenceMember *find_em_for_rel(EquivalenceClass *ec,
+ RelOptInfo *rel);
extern bool relation_can_be_sorted_early(PlannerInfo *root, RelOptInfo *rel,
EquivalenceClass *ec,
bool require_parallel_safe);
--
2.33.0
--nextPart4945189.lnZSD0nRIs--
^ permalink raw reply [nested|flat] 5+ messages in thread
* [PATCH 2/2] Add has_large_object_privilege function
@ 2024-07-02 06:12 Yugo Nagata <nagata@sraoss.co.jp>
0 siblings, 0 replies; 5+ messages in thread
From: Yugo Nagata @ 2024-07-02 06:12 UTC (permalink / raw)
This function is for checking whether a user has the privilege on a
large object. There are three variations whose arguments are combinations
of large object OID with user name, user OID, or implicit user (current_user).
It returns NULL if not-existing large object id is specified, and false if
non-existing user id is specified, and raises an error if non-existing user
name is specified. These behavior is similar with has_table_privilege.
---
doc/src/sgml/func.sgml | 18 +++
src/backend/utils/adt/acl.c | 140 +++++++++++++++++++
src/include/catalog/pg_proc.dat | 13 ++
src/test/regress/expected/privileges.out | 169 +++++++++++++++++++++++
src/test/regress/sql/privileges.sql | 44 ++++++
5 files changed, 384 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f1f22a1960..e06135ca9a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24975,6 +24975,24 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>has_large_object_privilege</primary>
+ </indexterm>
+ <function>has_large_object_privilege</function> (
+ <optional> <parameter>user</parameter> <type>name</type> or <type>oid</type>, </optional>
+ <parameter>largeobject</parameter> <type>oid</type>,
+ <parameter>privilege</parameter> <type>text</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Does user have privilege for large object?
+ Allowable privilege types are
+ <literal>SELECT</literal> and <literal>UPDATE</literal>.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index d7b39140b3..87f2b6c212 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -26,6 +26,7 @@
#include "catalog/pg_foreign_data_wrapper.h"
#include "catalog/pg_foreign_server.h"
#include "catalog/pg_language.h"
+#include "catalog/pg_largeobject.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_tablespace.h"
@@ -39,6 +40,7 @@
#include "lib/bloomfilter.h"
#include "lib/qunique.h"
#include "miscadmin.h"
+#include "storage/large_object.h"
#include "utils/acl.h"
#include "utils/array.h"
#include "utils/builtins.h"
@@ -46,6 +48,7 @@
#include "utils/inval.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
+#include "utils/snapmgr.h"
#include "utils/syscache.h"
#include "utils/varlena.h"
@@ -124,6 +127,7 @@ static AclMode convert_tablespace_priv_string(text *priv_type_text);
static Oid convert_type_name(text *typename);
static AclMode convert_type_priv_string(text *priv_type_text);
static AclMode convert_parameter_priv_string(text *priv_text);
+static AclMode convert_large_object_priv_string(text *priv_text);
static AclMode convert_role_priv_string(text *priv_type_text);
static AclResult pg_role_aclcheck(Oid role_oid, Oid roleid, AclMode mode);
@@ -4669,6 +4673,142 @@ convert_parameter_priv_string(text *priv_text)
return convert_any_priv_string(priv_text, parameter_priv_map);
}
+/*
+ * has_large_objec_privilege variants
+ * These are all named "has_large_object_privilege" at the SQL level.
+ * They take various combinations of large object OID with
+ * user name, user OID, or implicit user = current_user.
+ *
+ * The result is a boolean value: true if user has been granted
+ * the indicated privilege or false if not.
+ */
+
+/*
+ * has_large_object_privilege_name_id
+ * Check user privileges on a large object given
+ * name username, large object oid, and text priv name.
+ */
+Datum
+has_large_object_privilege_name_id(PG_FUNCTION_ARGS)
+{
+ Name username = PG_GETARG_NAME(0);
+ Oid roleid = get_role_oid_or_public(NameStr(*username));
+ Oid lobjId = PG_GETARG_OID(1);
+ text *priv_type_text = PG_GETARG_TEXT_PP(2);
+ AclMode mode;
+ AclResult aclresult;
+ Snapshot snapshot = NULL;
+
+ mode = convert_large_object_priv_string(priv_type_text);
+
+ if (mode & ACL_UPDATE)
+ snapshot = NULL;
+ else
+ snapshot = GetActiveSnapshot();
+
+ if (!LargeObjectExistsWithSnapshot(lobjId, snapshot))
+ PG_RETURN_NULL();
+
+ if (lo_compat_privileges)
+ PG_RETURN_BOOL(true);
+
+ aclresult = pg_largeobject_aclcheck_snapshot(lobjId,
+ roleid,
+ mode,
+ snapshot);
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+okui chiba * has_large_object_privilege_id
+ * Check user privileges on a large object given
+ * large object oid, and text priv name.
+ * current_user is assumed
+ */
+Datum
+has_large_object_privilege_id(PG_FUNCTION_ARGS)
+{
+ Oid lobjId = PG_GETARG_OID(0);
+ Oid roleid = GetUserId();
+ text *priv_type_text = PG_GETARG_TEXT_PP(1);
+ AclMode mode;
+ AclResult aclresult;
+ Snapshot snapshot = NULL;
+
+ mode = convert_large_object_priv_string(priv_type_text);
+
+ if (mode & ACL_UPDATE)
+ snapshot = NULL;
+ else
+ snapshot = GetActiveSnapshot();
+
+ if (!LargeObjectExistsWithSnapshot(lobjId, snapshot))
+ PG_RETURN_NULL();
+
+ if (lo_compat_privileges)
+ PG_RETURN_BOOL(true);
+
+ aclresult = pg_largeobject_aclcheck_snapshot(lobjId,
+ roleid,
+ mode,
+ snapshot);
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * has_large_object_privilege_id_id
+ * Check user privileges on a large object given
+ * roleid, large object oid, and text priv name.
+ */
+Datum
+has_large_object_privilege_id_id(PG_FUNCTION_ARGS)
+{
+ Oid roleid = PG_GETARG_OID(0);
+ Oid lobjId = PG_GETARG_OID(1);
+ text *priv_type_text = PG_GETARG_TEXT_PP(2);
+ AclMode mode;
+ AclResult aclresult;
+ Snapshot snapshot = NULL;
+
+ mode = convert_large_object_priv_string(priv_type_text);
+
+ if (mode & ACL_UPDATE)
+ snapshot = NULL;
+ else
+ snapshot = GetActiveSnapshot();
+
+ if (!LargeObjectExistsWithSnapshot(lobjId, snapshot))
+ PG_RETURN_NULL();
+
+ if (lo_compat_privileges)
+ PG_RETURN_BOOL(true);
+
+ aclresult = pg_largeobject_aclcheck_snapshot(lobjId,
+ roleid,
+ mode,
+ snapshot);
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * convert_large_object_priv_string
+ * Convert text string to AclMode value.
+ */
+static AclMode
+convert_large_object_priv_string(text *priv_text)
+{
+ static const priv_map parameter_priv_map[] = {
+ {"SELECT", ACL_SELECT},
+ {"UPDATE", ACL_UPDATE},
+ {NULL, 0}
+ };
+
+ return convert_any_priv_string(priv_text, parameter_priv_map);
+}
+
/*
* pg_has_role variants
* These are all named "pg_has_role" at the SQL level.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d4ac578ae6..5b0ef8df68 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5300,6 +5300,19 @@
prorettype => 'bool', proargtypes => 'oid text',
prosrc => 'has_any_column_privilege_id' },
+{ oid => '4551', descr => 'user privilege on large objct by username, large object oid',
+ proname => 'has_large_object_privilege', procost => '10', provolatile => 's',
+ prorettype => 'bool', proargtypes => 'name oid text',
+ prosrc => 'has_large_object_privilege_name_id' },
+{ oid => '4552', descr => 'current privilege on large objct by large object oid',
+ proname => 'has_large_object_privilege', procost => '10', provolatile => 's',
+ prorettype => 'bool', proargtypes => 'oid text',
+ prosrc => 'has_large_object_privilege_id' },
+{ oid => '4553', descr => 'user privilege on large objct by user oid, large object oid',
+ proname => 'has_large_object_privilege', procost => '10', provolatile => 's',
+ prorettype => 'bool', proargtypes => 'oid oid text',
+ prosrc => 'has_large_object_privilege_id_id' },
+
{ oid => '3355', descr => 'I/O',
proname => 'pg_ndistinct_in', prorettype => 'pg_ndistinct',
proargtypes => 'cstring', prosrc => 'pg_ndistinct_in' },
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index eb4b762ea1..7933601590 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -2024,10 +2024,167 @@ SELECT lo_truncate(lo_open(2001, x'20000'::int), 10);
0
(1 row)
+-- has_large_object_privilege function
+-- superuser
+\c -
+SELECT has_large_object_privilege(1001, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1003, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1004, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1001, 'UPDATE');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'UPDATE');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1003, 'UPDATE');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1004, 'UPDATE');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+-- not-existing large object
+SELECT has_large_object_privilege(9999, 'SELECT'); -- NULL
+ has_large_object_privilege
+----------------------------
+
+(1 row)
+
+-- not-existing user
+SELECT has_large_object_privilege(-99999, 1001, 'SELECT'); -- false
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+-- non-superuser
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT has_large_object_privilege(1001, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'SELECT'); -- false
+ has_large_object_privilege
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege(1003, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1004, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1001, 'UPDATE');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- false
+ has_large_object_privilege
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege(1003, 'UPDATE'); -- false
+ has_large_object_privilege
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege(1004, 'UPDATE');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege('regress_priv_user3', 1001, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege('regress_priv_user3', 1003, 'SELECT'); -- false
+ has_large_object_privilege
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege('regress_priv_user3', 1005, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege('regress_priv_user3', 1005, 'UPDATE'); -- false
+ has_large_object_privilege
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege('regress_priv_user3', 2001, 'UPDATE');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
-- compatibility mode in largeobject permission
\c -
SET lo_compat_privileges = false; -- default setting
SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT has_large_object_privilege(1002, 'SELECT'); -- false
+ has_large_object_privilege
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- false
+ has_large_object_privilege
+----------------------------
+ f
+(1 row)
+
SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied
ERROR: permission denied for large object 1002
SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied
@@ -2047,6 +2204,18 @@ ERROR: permission denied for function lo_import
\c -
SET lo_compat_privileges = true; -- compatibility mode
SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT has_large_object_privilege(1002, 'SELECT'); -- true
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- true
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
SELECT loread(lo_open(1002, x'40000'::int), 32);
loread
--------
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eeb4c00292..6b509e993a 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1318,11 +1318,52 @@ SELECT loread(lo_open(1005, x'40000'::int), 32);
SELECT lo_truncate(lo_open(1005, x'20000'::int), 10); -- to be denied
SELECT lo_truncate(lo_open(2001, x'20000'::int), 10);
+-- has_large_object_privilege function
+
+-- superuser
+\c -
+SELECT has_large_object_privilege(1001, 'SELECT');
+SELECT has_large_object_privilege(1002, 'SELECT');
+SELECT has_large_object_privilege(1003, 'SELECT');
+SELECT has_large_object_privilege(1004, 'SELECT');
+
+SELECT has_large_object_privilege(1001, 'UPDATE');
+SELECT has_large_object_privilege(1002, 'UPDATE');
+SELECT has_large_object_privilege(1003, 'UPDATE');
+SELECT has_large_object_privilege(1004, 'UPDATE');
+
+-- not-existing large object
+SELECT has_large_object_privilege(9999, 'SELECT'); -- NULL
+-- not-existing user
+SELECT has_large_object_privilege(-99999, 1001, 'SELECT'); -- false
+
+-- non-superuser
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT has_large_object_privilege(1001, 'SELECT');
+SELECT has_large_object_privilege(1002, 'SELECT'); -- false
+SELECT has_large_object_privilege(1003, 'SELECT');
+SELECT has_large_object_privilege(1004, 'SELECT');
+
+SELECT has_large_object_privilege(1001, 'UPDATE');
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- false
+SELECT has_large_object_privilege(1003, 'UPDATE'); -- false
+SELECT has_large_object_privilege(1004, 'UPDATE');
+
+SELECT has_large_object_privilege('regress_priv_user3', 1001, 'SELECT');
+SELECT has_large_object_privilege('regress_priv_user3', 1003, 'SELECT'); -- false
+SELECT has_large_object_privilege('regress_priv_user3', 1005, 'SELECT');
+
+SELECT has_large_object_privilege('regress_priv_user3', 1005, 'UPDATE'); -- false
+SELECT has_large_object_privilege('regress_priv_user3', 2001, 'UPDATE');
+
-- compatibility mode in largeobject permission
\c -
SET lo_compat_privileges = false; -- default setting
SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT has_large_object_privilege(1002, 'SELECT'); -- false
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- false
+
SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied
SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied
SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); -- to be denied
@@ -1336,6 +1377,9 @@ SELECT lo_import('/dev/null', 2003); -- to be denied
SET lo_compat_privileges = true; -- compatibility mode
SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT has_large_object_privilege(1002, 'SELECT'); -- true
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- true
+
SELECT loread(lo_open(1002, x'40000'::int), 32);
SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');
SELECT lo_truncate(lo_open(1002, x'20000'::int), 10);
--
2.25.1
--Multipart=_Tue__2_Jul_2024_16_34_44_+0900_Lpc86p3xobkG.QGS
Content-Type: text/x-diff;
name="0001-Deduplicate-codes-of-LargeObjectExists-and-mvLargeOb.patch"
Content-Disposition: attachment;
filename="0001-Deduplicate-codes-of-LargeObjectExists-and-mvLargeOb.patch"
Content-Transfer-Encoding: 7bit
^ permalink raw reply [nested|flat] 5+ messages in thread
* [PATCH 2/2] Add has_large_object_privilege function
@ 2024-07-02 06:12 Yugo Nagata <nagata@sraoss.co.jp>
0 siblings, 0 replies; 5+ messages in thread
From: Yugo Nagata @ 2024-07-02 06:12 UTC (permalink / raw)
This function is for checking whether a user has the privilege on a
large object. There are three variations whose arguments are combinations
of large object OID with user name, user OID, or implicit user (current_user).
It returns NULL if not-existing large object id is specified, and false if
non-existing user id is specified, and raises an error if non-existing user
name is specified. These behavior is similar with has_table_privilege.
---
doc/src/sgml/func.sgml | 18 +++
src/backend/utils/adt/acl.c | 140 +++++++++++++++++++
src/include/catalog/pg_proc.dat | 13 ++
src/test/regress/expected/privileges.out | 169 +++++++++++++++++++++++
src/test/regress/sql/privileges.sql | 44 ++++++
5 files changed, 384 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f1f22a1960..e06135ca9a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24975,6 +24975,24 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>has_large_object_privilege</primary>
+ </indexterm>
+ <function>has_large_object_privilege</function> (
+ <optional> <parameter>user</parameter> <type>name</type> or <type>oid</type>, </optional>
+ <parameter>largeobject</parameter> <type>oid</type>,
+ <parameter>privilege</parameter> <type>text</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Does user have privilege for large object?
+ Allowable privilege types are
+ <literal>SELECT</literal> and <literal>UPDATE</literal>.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index d7b39140b3..87f2b6c212 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -26,6 +26,7 @@
#include "catalog/pg_foreign_data_wrapper.h"
#include "catalog/pg_foreign_server.h"
#include "catalog/pg_language.h"
+#include "catalog/pg_largeobject.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_tablespace.h"
@@ -39,6 +40,7 @@
#include "lib/bloomfilter.h"
#include "lib/qunique.h"
#include "miscadmin.h"
+#include "storage/large_object.h"
#include "utils/acl.h"
#include "utils/array.h"
#include "utils/builtins.h"
@@ -46,6 +48,7 @@
#include "utils/inval.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
+#include "utils/snapmgr.h"
#include "utils/syscache.h"
#include "utils/varlena.h"
@@ -124,6 +127,7 @@ static AclMode convert_tablespace_priv_string(text *priv_type_text);
static Oid convert_type_name(text *typename);
static AclMode convert_type_priv_string(text *priv_type_text);
static AclMode convert_parameter_priv_string(text *priv_text);
+static AclMode convert_large_object_priv_string(text *priv_text);
static AclMode convert_role_priv_string(text *priv_type_text);
static AclResult pg_role_aclcheck(Oid role_oid, Oid roleid, AclMode mode);
@@ -4669,6 +4673,142 @@ convert_parameter_priv_string(text *priv_text)
return convert_any_priv_string(priv_text, parameter_priv_map);
}
+/*
+ * has_large_objec_privilege variants
+ * These are all named "has_large_object_privilege" at the SQL level.
+ * They take various combinations of large object OID with
+ * user name, user OID, or implicit user = current_user.
+ *
+ * The result is a boolean value: true if user has been granted
+ * the indicated privilege or false if not.
+ */
+
+/*
+ * has_large_object_privilege_name_id
+ * Check user privileges on a large object given
+ * name username, large object oid, and text priv name.
+ */
+Datum
+has_large_object_privilege_name_id(PG_FUNCTION_ARGS)
+{
+ Name username = PG_GETARG_NAME(0);
+ Oid roleid = get_role_oid_or_public(NameStr(*username));
+ Oid lobjId = PG_GETARG_OID(1);
+ text *priv_type_text = PG_GETARG_TEXT_PP(2);
+ AclMode mode;
+ AclResult aclresult;
+ Snapshot snapshot = NULL;
+
+ mode = convert_large_object_priv_string(priv_type_text);
+
+ if (mode & ACL_UPDATE)
+ snapshot = NULL;
+ else
+ snapshot = GetActiveSnapshot();
+
+ if (!LargeObjectExistsWithSnapshot(lobjId, snapshot))
+ PG_RETURN_NULL();
+
+ if (lo_compat_privileges)
+ PG_RETURN_BOOL(true);
+
+ aclresult = pg_largeobject_aclcheck_snapshot(lobjId,
+ roleid,
+ mode,
+ snapshot);
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+okui chiba * has_large_object_privilege_id
+ * Check user privileges on a large object given
+ * large object oid, and text priv name.
+ * current_user is assumed
+ */
+Datum
+has_large_object_privilege_id(PG_FUNCTION_ARGS)
+{
+ Oid lobjId = PG_GETARG_OID(0);
+ Oid roleid = GetUserId();
+ text *priv_type_text = PG_GETARG_TEXT_PP(1);
+ AclMode mode;
+ AclResult aclresult;
+ Snapshot snapshot = NULL;
+
+ mode = convert_large_object_priv_string(priv_type_text);
+
+ if (mode & ACL_UPDATE)
+ snapshot = NULL;
+ else
+ snapshot = GetActiveSnapshot();
+
+ if (!LargeObjectExistsWithSnapshot(lobjId, snapshot))
+ PG_RETURN_NULL();
+
+ if (lo_compat_privileges)
+ PG_RETURN_BOOL(true);
+
+ aclresult = pg_largeobject_aclcheck_snapshot(lobjId,
+ roleid,
+ mode,
+ snapshot);
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * has_large_object_privilege_id_id
+ * Check user privileges on a large object given
+ * roleid, large object oid, and text priv name.
+ */
+Datum
+has_large_object_privilege_id_id(PG_FUNCTION_ARGS)
+{
+ Oid roleid = PG_GETARG_OID(0);
+ Oid lobjId = PG_GETARG_OID(1);
+ text *priv_type_text = PG_GETARG_TEXT_PP(2);
+ AclMode mode;
+ AclResult aclresult;
+ Snapshot snapshot = NULL;
+
+ mode = convert_large_object_priv_string(priv_type_text);
+
+ if (mode & ACL_UPDATE)
+ snapshot = NULL;
+ else
+ snapshot = GetActiveSnapshot();
+
+ if (!LargeObjectExistsWithSnapshot(lobjId, snapshot))
+ PG_RETURN_NULL();
+
+ if (lo_compat_privileges)
+ PG_RETURN_BOOL(true);
+
+ aclresult = pg_largeobject_aclcheck_snapshot(lobjId,
+ roleid,
+ mode,
+ snapshot);
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * convert_large_object_priv_string
+ * Convert text string to AclMode value.
+ */
+static AclMode
+convert_large_object_priv_string(text *priv_text)
+{
+ static const priv_map parameter_priv_map[] = {
+ {"SELECT", ACL_SELECT},
+ {"UPDATE", ACL_UPDATE},
+ {NULL, 0}
+ };
+
+ return convert_any_priv_string(priv_text, parameter_priv_map);
+}
+
/*
* pg_has_role variants
* These are all named "pg_has_role" at the SQL level.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d4ac578ae6..5b0ef8df68 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5300,6 +5300,19 @@
prorettype => 'bool', proargtypes => 'oid text',
prosrc => 'has_any_column_privilege_id' },
+{ oid => '4551', descr => 'user privilege on large objct by username, large object oid',
+ proname => 'has_large_object_privilege', procost => '10', provolatile => 's',
+ prorettype => 'bool', proargtypes => 'name oid text',
+ prosrc => 'has_large_object_privilege_name_id' },
+{ oid => '4552', descr => 'current privilege on large objct by large object oid',
+ proname => 'has_large_object_privilege', procost => '10', provolatile => 's',
+ prorettype => 'bool', proargtypes => 'oid text',
+ prosrc => 'has_large_object_privilege_id' },
+{ oid => '4553', descr => 'user privilege on large objct by user oid, large object oid',
+ proname => 'has_large_object_privilege', procost => '10', provolatile => 's',
+ prorettype => 'bool', proargtypes => 'oid oid text',
+ prosrc => 'has_large_object_privilege_id_id' },
+
{ oid => '3355', descr => 'I/O',
proname => 'pg_ndistinct_in', prorettype => 'pg_ndistinct',
proargtypes => 'cstring', prosrc => 'pg_ndistinct_in' },
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index eb4b762ea1..7933601590 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -2024,10 +2024,167 @@ SELECT lo_truncate(lo_open(2001, x'20000'::int), 10);
0
(1 row)
+-- has_large_object_privilege function
+-- superuser
+\c -
+SELECT has_large_object_privilege(1001, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1003, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1004, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1001, 'UPDATE');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'UPDATE');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1003, 'UPDATE');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1004, 'UPDATE');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+-- not-existing large object
+SELECT has_large_object_privilege(9999, 'SELECT'); -- NULL
+ has_large_object_privilege
+----------------------------
+
+(1 row)
+
+-- not-existing user
+SELECT has_large_object_privilege(-99999, 1001, 'SELECT'); -- false
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+-- non-superuser
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT has_large_object_privilege(1001, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'SELECT'); -- false
+ has_large_object_privilege
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege(1003, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1004, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1001, 'UPDATE');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- false
+ has_large_object_privilege
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege(1003, 'UPDATE'); -- false
+ has_large_object_privilege
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege(1004, 'UPDATE');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege('regress_priv_user3', 1001, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege('regress_priv_user3', 1003, 'SELECT'); -- false
+ has_large_object_privilege
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege('regress_priv_user3', 1005, 'SELECT');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege('regress_priv_user3', 1005, 'UPDATE'); -- false
+ has_large_object_privilege
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege('regress_priv_user3', 2001, 'UPDATE');
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
-- compatibility mode in largeobject permission
\c -
SET lo_compat_privileges = false; -- default setting
SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT has_large_object_privilege(1002, 'SELECT'); -- false
+ has_large_object_privilege
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- false
+ has_large_object_privilege
+----------------------------
+ f
+(1 row)
+
SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied
ERROR: permission denied for large object 1002
SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied
@@ -2047,6 +2204,18 @@ ERROR: permission denied for function lo_import
\c -
SET lo_compat_privileges = true; -- compatibility mode
SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT has_large_object_privilege(1002, 'SELECT'); -- true
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- true
+ has_large_object_privilege
+----------------------------
+ t
+(1 row)
+
SELECT loread(lo_open(1002, x'40000'::int), 32);
loread
--------
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eeb4c00292..6b509e993a 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1318,11 +1318,52 @@ SELECT loread(lo_open(1005, x'40000'::int), 32);
SELECT lo_truncate(lo_open(1005, x'20000'::int), 10); -- to be denied
SELECT lo_truncate(lo_open(2001, x'20000'::int), 10);
+-- has_large_object_privilege function
+
+-- superuser
+\c -
+SELECT has_large_object_privilege(1001, 'SELECT');
+SELECT has_large_object_privilege(1002, 'SELECT');
+SELECT has_large_object_privilege(1003, 'SELECT');
+SELECT has_large_object_privilege(1004, 'SELECT');
+
+SELECT has_large_object_privilege(1001, 'UPDATE');
+SELECT has_large_object_privilege(1002, 'UPDATE');
+SELECT has_large_object_privilege(1003, 'UPDATE');
+SELECT has_large_object_privilege(1004, 'UPDATE');
+
+-- not-existing large object
+SELECT has_large_object_privilege(9999, 'SELECT'); -- NULL
+-- not-existing user
+SELECT has_large_object_privilege(-99999, 1001, 'SELECT'); -- false
+
+-- non-superuser
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT has_large_object_privilege(1001, 'SELECT');
+SELECT has_large_object_privilege(1002, 'SELECT'); -- false
+SELECT has_large_object_privilege(1003, 'SELECT');
+SELECT has_large_object_privilege(1004, 'SELECT');
+
+SELECT has_large_object_privilege(1001, 'UPDATE');
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- false
+SELECT has_large_object_privilege(1003, 'UPDATE'); -- false
+SELECT has_large_object_privilege(1004, 'UPDATE');
+
+SELECT has_large_object_privilege('regress_priv_user3', 1001, 'SELECT');
+SELECT has_large_object_privilege('regress_priv_user3', 1003, 'SELECT'); -- false
+SELECT has_large_object_privilege('regress_priv_user3', 1005, 'SELECT');
+
+SELECT has_large_object_privilege('regress_priv_user3', 1005, 'UPDATE'); -- false
+SELECT has_large_object_privilege('regress_priv_user3', 2001, 'UPDATE');
+
-- compatibility mode in largeobject permission
\c -
SET lo_compat_privileges = false; -- default setting
SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT has_large_object_privilege(1002, 'SELECT'); -- false
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- false
+
SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied
SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied
SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); -- to be denied
@@ -1336,6 +1377,9 @@ SELECT lo_import('/dev/null', 2003); -- to be denied
SET lo_compat_privileges = true; -- compatibility mode
SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT has_large_object_privilege(1002, 'SELECT'); -- true
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- true
+
SELECT loread(lo_open(1002, x'40000'::int), 32);
SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');
SELECT lo_truncate(lo_open(1002, x'20000'::int), 10);
--
2.25.1
--Multipart=_Tue__2_Jul_2024_16_34_44_+0900_Lpc86p3xobkG.QGS
Content-Type: text/x-diff;
name="0001-Deduplicate-codes-of-LargeObjectExists-and-mvLargeOb.patch"
Content-Disposition: attachment;
filename="0001-Deduplicate-codes-of-LargeObjectExists-and-mvLargeOb.patch"
Content-Transfer-Encoding: 7bit
^ permalink raw reply [nested|flat] 5+ messages in thread
* [PATCH v4 2/8] Address space reservation for shared memory
@ 2024-10-16 18:21 Dmitrii Dolgov <9erthalion6@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Dmitrii Dolgov @ 2024-10-16 18:21 UTC (permalink / raw)
Currently the kernel is responsible to chose an address, where to place each
shared memory mapping, which is the lowest possible address that do not clash
with any other mappings. This is considered to be the most portable approach,
but one of the downsides is that there is no place to resize allocated mappings
anymore. Here is how it looks like for one mapping in /proc/$PID/maps,
/dev/zero represents the anonymous shared memory we talk about:
00400000-00490000 /path/bin/postgres
...
012d9000-0133e000 [heap]
7f443a800000-7f470a800000 /dev/zero (deleted)
7f470a800000-7f471831d000 /usr/lib/locale/locale-archive
7f4718400000-7f4718401000 /usr/lib64/libicudata.so.74.2
...
7f471aef2000-7f471aef9000 /dev/shm/PostgreSQL.3859891842
7f471aef9000-7f471aefa000 /SYSV007dbf7d (deleted)
By specifying the mapping address directly it's possible to place the
mapping in a way that leaves room for resizing. The idea is:
* To reserve some address space via mmap'ing a large chunk of memory
with PROT_NONE and MAP_NORESERVE. This way we prepare a playground for
preparing shared memory layout without risking anything interfering
with that.
* To slice the reserved space up into sections, one to use for each
shared segment.
* Allocate shared memory segments out of corresponding slices and
leaving unclaimed space in between them. This is implemented via
mmap'ing memory at a specified address from the reserved space with
MAP_FIXED.
The result looks like this:
012d9000-0133e000 [heap]
7f443a800000-7f444196c000 /dev/zero (deleted)
7f444196c000-7f470a800000 # reserved space
7f470a800000-7f471831d000 /usr/lib/locale/locale-archive
7f4718400000-7f4718401000 /usr/lib64/libicudata.so.74.2
Things like address space randomization should not be a problem in this
context, since the randomization is applied to the mmap base, which is
one per process.
This approach also do not impact the actual memory usage as reported by
the kernel. Here is the output of /proc/$PID/status for the master
version with shared_buffers = 128 MB:
// Peak virtual memory size, which is described as total pages
// mapped in mm_struct. It corresponds to the mapped reserved space
// and is the only number that grows with it.
VmPeak: 2043192 kB
// Size of memory portions. It contains RssAnon + RssFile + RssShmem
VmRSS: 22908 kB
// Size of resident anonymous memory
RssAnon: 768 kB
// Size of resident file mappings
RssFile: 10364 kB
// Size of resident shmem memory (includes SysV shm, mapping of tmpfs and
// shared anonymous mappings)
RssShmem: 11776 kB
Here is the same for the patch when reserving 20GB of space:
VmPeak: 21250648 kB
VmRSS: 22948 kB
RssAnon: 768 kB
RssFile: 10404 kB
RssShmem: 11776 kB
Cgroup v2 doesn't have any problems with that as well. To verify a new cgroup
was created with the memory limit 256 MB, then PostgreSQL was launched withing
this cgroup with shared_buffers = 128 MB:
$ cd /sys/fs/cgroup
$ mkdir postgres
$ cd postres
$ echo 268435456 > memory.max
$ echo $MASTER_PID_SHELL > cgroup.procs
# postgres from the master branch has being successfully launched
# from that shell
$ cat memory.current
17465344 (~16.6 MB)
# stop postgres
$ echo $PATCH_PID_SHELL > cgroup.procs
# postgres from the patch has being successfully launched from that shell
$ cat memory.current
17637376 (~16.8 MB)
To control the amount of space reserved a new GUC max_available_memory
is introduced. Ideally it should be based on the maximum available
memory, hense the name.
---
src/backend/port/sysv_shmem.c | 284 ++++++++++++++++++++++++----
src/backend/port/win32_shmem.c | 2 +-
src/backend/storage/ipc/ipci.c | 5 +-
src/backend/utils/init/globals.c | 1 +
src/backend/utils/misc/guc_tables.c | 14 ++
src/include/storage/pg_shmem.h | 4 +-
6 files changed, 271 insertions(+), 39 deletions(-)
diff --git a/src/backend/port/sysv_shmem.c b/src/backend/port/sysv_shmem.c
index 56af0231d24..a0f03ff868f 100644
--- a/src/backend/port/sysv_shmem.c
+++ b/src/backend/port/sysv_shmem.c
@@ -108,6 +108,66 @@ static AnonymousMapping Mappings[ANON_MAPPINGS];
/* Keeps track of used mapping segments */
static int next_free_segment = 0;
+/*
+ * Anonymous mapping placing (/dev/zero (deleted) below) looks like this:
+ *
+ * 00400000-00490000 /path/bin/postgres
+ * ...
+ * 012d9000-0133e000 [heap]
+ * 7f443a800000-7f470a800000 /dev/zero (deleted)
+ * 7f470a800000-7f471831d000 /usr/lib/locale/locale-archive
+ * 7f4718400000-7f4718401000 /usr/lib64/libicudata.so.74.2
+ * ...
+ * 7f471aef2000-7f471aef9000 /dev/shm/PostgreSQL.3859891842
+ * 7f471aef9000-7f471aefa000 /SYSV007dbf7d (deleted)
+ * ...
+ *
+ * We would like to place multiple mappings in such a way, that there will be
+ * enough space between them in the address space to be able to resize up to
+ * certain size, but without counting towards the total memory consumption.
+ *
+ * To achieve that we first reserve some shared memory address space by
+ * mmap'ing a segment of MaxAvailableMemory size with PROT_NONE and
+ * MAP_NORESERVE (these flags allow to make sure this space will not be used by
+ * anything else, yet do not count against memory limits). Having the reserved
+ * space, we allocate out of it actual chunks of shared memory as usual,
+ * updating a pointer to the current available reserved space for the next
+ * allocation with the gap between segments in mind.
+ *
+ * The result would look like this:
+ *
+ * 012d9000-0133e000 [heap]
+ * 7f4426f54000-7f442e010000 /dev/zero (deleted)
+ * 7f442e010000-7f443a800000 # reserved empty space
+ * 7f443a800000-7f444196c000 /dev/zero (deleted)
+ * 7f444196c000-7f470a800000 # reserved empty space
+ * 7f470a800000-7f471831d000 /usr/lib/locale/locale-archive
+ * 7f4718400000-7f4718401000 /usr/lib64/libicudata.so.74.2
+ * [...]
+ *
+ * The reserved space pointer is calculated to slice up the total reserved
+ * space into fixed fractions of address space for each segment, as specified
+ * in the SHMEM_RESIZE_RATIO array.
+ */
+static double SHMEM_RESIZE_RATIO[1] = {
+ 1.0, /* MAIN_SHMEM_SLOT */
+};
+
+/*
+ * Offset from the beginning of the reserved space, which indicates currently
+ * available range. New shared memory segments have to be allocated at this
+ * offset related to the reserved space.
+ */
+static Size reserved_offset = 0;
+
+/*
+ * Flag telling that we have decided to use huge pages.
+ *
+ * XXX: It's possible to use GetConfigOption("huge_pages_status", false, false)
+ * instead, but it feels like an overkill.
+ */
+static bool huge_pages_on = false;
+
static void *InternalIpcMemoryCreate(IpcMemoryKey memKey, Size size);
static void IpcMemoryDetach(int status, Datum shmaddr);
static void IpcMemoryDelete(int status, Datum shmId);
@@ -626,39 +686,198 @@ check_huge_page_size(int *newval, void **extra, GucSource source)
*
* This function will modify mapping size to the actual size of the allocation,
* if it ends up allocating a segment that is larger than requested.
+ *
+ * Note that we do not switch from huge pages to regular pages in this
+ * function, this decision was already made in ReserveAnonymousMemory and we
+ * stick to it.
*/
static void
-CreateAnonymousSegment(AnonymousMapping *mapping)
+CreateAnonymousSegment(AnonymousMapping *mapping, Pointer base)
{
Size allocsize = mapping->shmem_size;
void *ptr = MAP_FAILED;
int mmap_errno = 0;
+ int mmap_flags = PG_MMAP_FLAGS;
#ifndef MAP_HUGETLB
- /* PGSharedMemoryCreate should have dealt with this case */
- Assert(huge_pages != HUGE_PAGES_ON);
+ /* ReserveAnonymousMemory should have dealt with this case */
+ Assert(huge_pages != HUGE_PAGES_ON && !huge_pages_on);
#else
- if (huge_pages == HUGE_PAGES_ON || huge_pages == HUGE_PAGES_TRY)
+ if (huge_pages_on)
{
- /*
- * Round up the request size to a suitable large value.
- */
Size hugepagesize;
- int mmap_flags;
+ /* Make sure nothing is messed up */
+ Assert(huge_pages == HUGE_PAGES_ON || huge_pages == HUGE_PAGES_TRY);
+
+ /* Round up the request size to a suitable large value */
GetHugePageSize(&hugepagesize, &mmap_flags);
if (allocsize % hugepagesize != 0)
allocsize += hugepagesize - (allocsize % hugepagesize);
+ mmap_flags = PG_MMAP_FLAGS | mmap_flags;
+ }
+#endif
+
+ elog(DEBUG1, "segment[%s]: mmap(%zu) at address %p",
+ MappingName(mapping->shmem_segment), allocsize, base + reserved_offset);
+
+ /*
+ * Try to create mapping at an address out of the reserved range, which
+ * will allow to extend it later. Use reserved_offset to allocate the
+ * segment, then update currently available reserved range.
+ *
+ * If the last step has failed, fallback to the regular mapping
+ * creation and signal that shared buffers could not be resized without
+ * a restart.
+ */
+ ptr = mmap(base + reserved_offset, allocsize, PROT_READ | PROT_WRITE,
+ mmap_flags | MAP_FIXED, -1, 0);
+ mmap_errno = errno;
+
+ if (ptr == MAP_FAILED)
+ {
+ DebugMappings();
+ elog(DEBUG1, "segment[%s]: mmap(%zu) at address %p failed: %m, "
+ "fallback to the non-resizable allocation",
+ MappingName(mapping->shmem_segment), allocsize, base + reserved_offset);
+
ptr = mmap(NULL, allocsize, PROT_READ | PROT_WRITE,
- PG_MMAP_FLAGS | mmap_flags, -1, 0);
+ PG_MMAP_FLAGS, -1, 0);
+ mmap_errno = errno;
+ }
+ else
+ {
+ Size total_reserved = (Size) MaxAvailableMemory * BLCKSZ;
+
+ reserved_offset += total_reserved * SHMEM_RESIZE_RATIO[next_free_segment];
+ }
+
+ if (ptr == MAP_FAILED)
+ {
+ errno = mmap_errno;
+ DebugMappings();
+ ereport(FATAL,
+ (errmsg("segment[%s]: could not map anonymous shared memory: %m",
+ MappingName(mapping->shmem_segment)),
+ (mmap_errno == ENOMEM) ?
+ errhint("This error usually means that PostgreSQL's request "
+ "for a shared memory segment exceeded available memory, "
+ "swap space, or huge pages. To reduce the request size "
+ "(currently %zu bytes), reduce PostgreSQL's shared "
+ "memory usage, perhaps by reducing \"shared_buffers\" or "
+ "\"max_connections\".",
+ allocsize) : 0));
+ }
+
+ mapping->shmem = ptr;
+ mapping->shmem_size = allocsize;
+}
+
+/*
+ * ReserveAnonymousMemory
+ *
+ * Reserve shared memory address space, from which shared memory segments are
+ * going to be sliced out. The goal of this exercise is to support segments
+ * resizing, for which we need a reserved space free of potential clashes with
+ * other mmap'd areas that are not under our control. Reservation is done via
+ * mmap, and will not allocate any memory until it will be actually used, and
+ * MAP_NORESERVE allows to make it not counting againt kernel reservation
+ * limits (e.g. in cgroups or for huge pages). Do not get confused because of
+ * MAP_NORESERVE -- we need to reserve some space, but not the actual memory,
+ * and that is that this flag is about.
+ *
+ * Note, that with MAP_NORESERVE a reservation with hugetlb will succeed even
+ * if there is actually not enough huge pages. Hence this function is
+ * responsible for deciding whether to use huge pages or not. To achieve that
+ * we need to probe first and try to allocate needed memory for all segments --
+ * if this succeeds, we unmap the probe segment and use hugetlb; if it fails,
+ * we proceed with the regular memory.
+ */
+void *
+ReserveAnonymousMemory(Size reserve_size)
+{
+ Size allocsize = reserve_size;
+ void *ptr = MAP_FAILED;
+ int mmap_errno = 0;
+
+ /* Complain if hugepages demanded but we can't possibly support them */
+#if !defined(MAP_HUGETLB)
+ if (huge_pages == HUGE_PAGES_ON)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("huge pages not supported on this platform")));
+#else
+ if (huge_pages == HUGE_PAGES_ON || huge_pages == HUGE_PAGES_TRY)
+ {
+ Size hugepagesize, total_size = 0;
+ int mmap_flags;
+
+ GetHugePageSize(&hugepagesize, &mmap_flags);
+
+ /*
+ * Figure out how much memory is needed for all segments, keeping in
+ * mind that for every segment this value will be rounding up by the
+ * huge page size. The resulting value will be used to probe memory and
+ * decide whether we will allocate huge pages or not.
+ *
+ * We could actually have a mix and match of segments with and without
+ * huge pages. But in that case we need to have multiple reservation
+ * spaces to use corresponding memory (hugetlb adress space reserved
+ * for hugetlb segments, regular memory for others), and it doesn't
+ * seem to worth the complexity for now.
+ */
+ for(int segment = 0; segment < ANON_MAPPINGS; segment++)
+ {
+ int numSemas;
+ Size segment_size = CalculateShmemSize(&numSemas, segment);
+
+ if (segment_size % hugepagesize != 0)
+ segment_size += hugepagesize - (segment_size % hugepagesize);
+
+ total_size += segment_size;
+ }
+
+ /* Map total amount of memory to test its availability. */
+ elog(DEBUG1, "reserving space: probe mmap(%zu) with MAP_HUGETLB",
+ total_size);
+ ptr = mmap(NULL, total_size, PROT_NONE,
+ PG_MMAP_FLAGS | MAP_ANONYMOUS | mmap_flags, -1, 0);
mmap_errno = errno;
if (huge_pages == HUGE_PAGES_TRY && ptr == MAP_FAILED)
{
- DebugMappings();
- elog(DEBUG1, "segment[%s]: mmap(%zu) with MAP_HUGETLB failed, huge pages disabled: %m",
- MappingName(mapping->shmem_segment), allocsize);
+ /* No huge pages, we will go with the regular page size */
+ elog(DEBUG1, "reserving space: probe mmap(%zu) with MAP_HUGETLB "
+ "failed, huge pages disabled: %m", total_size);
+ }
+ else
+ {
+ /*
+ * All fine, unmap the temporary segment and proceed with reserving
+ * using huge pages.
+ */
+ if (munmap(ptr, total_size) < 0)
+ elog(LOG, "reservice space: munmap(%p, %zu) failed: %m",
+ ptr, total_size);
+
+ /* Round up the requested size to a suitable large value. */
+ if (allocsize % hugepagesize != 0)
+ allocsize += hugepagesize - (allocsize % hugepagesize);
+
+ elog(DEBUG1, "reserving space: mmap(%zu) with MAP_HUGETLB",
+ allocsize);
+ ptr = mmap(NULL, allocsize, PROT_NONE,
+ PG_MMAP_FLAGS | MAP_ANONYMOUS | MAP_NORESERVE | mmap_flags,
+ -1, 0);
+ mmap_errno = errno;
+
+ /* This should not happen, but handle errors anyway */
+ if (huge_pages == HUGE_PAGES_TRY && ptr == MAP_FAILED)
+ {
+ elog(DEBUG1, "reserving space: mmap(%zu) with MAP_HUGETLB "
+ "failed, huge pages disabled: %m", allocsize);
+ }
}
}
#endif
@@ -666,10 +885,12 @@ CreateAnonymousSegment(AnonymousMapping *mapping)
/*
* Report whether huge pages are in use. This needs to be tracked before
* the second mmap() call if attempting to use huge pages failed
- * previously.
+ * previously. At this point ptr is either pointing to the probe segment,
+ * if we couldn't mmap it, or the reservation space.
*/
SetConfigOption("huge_pages_status", (ptr == MAP_FAILED) ? "off" : "on",
PGC_INTERNAL, PGC_S_DYNAMIC_DEFAULT);
+ huge_pages_on = ptr != MAP_FAILED;
if (ptr == MAP_FAILED && huge_pages != HUGE_PAGES_ON)
{
@@ -677,10 +898,11 @@ CreateAnonymousSegment(AnonymousMapping *mapping)
* Use the original size, not the rounded-up value, when falling back
* to non-huge pages.
*/
- allocsize = mapping->shmem_size;
- ptr = mmap(NULL, allocsize, PROT_READ | PROT_WRITE,
- PG_MMAP_FLAGS, -1, 0);
- mmap_errno = errno;
+ allocsize = reserve_size;
+
+ elog(DEBUG1, "reserving space: mmap(%zu)", allocsize);
+ ptr = mmap(NULL, allocsize, PROT_NONE,
+ MAP_PRIVATE | MAP_ANONYMOUS | MAP_NORESERVE, -1, 0);
}
if (ptr == MAP_FAILED)
@@ -688,20 +910,18 @@ CreateAnonymousSegment(AnonymousMapping *mapping)
errno = mmap_errno;
DebugMappings();
ereport(FATAL,
- (errmsg("segment[%s]: could not map anonymous shared memory: %m",
- MappingName(mapping->shmem_segment)),
+ (errmsg("reserving space: could not map anonymous shared "
+ "memory: %m"),
(mmap_errno == ENOMEM) ?
errhint("This error usually means that PostgreSQL's request "
- "for a shared memory segment exceeded available memory, "
- "swap space, or huge pages. To reduce the request size "
- "(currently %zu bytes), reduce PostgreSQL's shared "
- "memory usage, perhaps by reducing \"shared_buffers\" or "
- "\"max_connections\".",
+ "for a reserved shared memory address space exceeded "
+ "available memory, swap space, or huge pages. To "
+ "reduce the request reservation size (currently %zu "
+ "bytes), reduce PostgreSQL's \"maximum_shared_buffers\".",
allocsize) : 0));
}
- mapping->shmem = ptr;
- mapping->shmem_size = allocsize;
+ return ptr;
}
/*
@@ -740,7 +960,7 @@ AnonymousShmemDetach(int status, Datum arg)
*/
PGShmemHeader *
PGSharedMemoryCreate(Size size,
- PGShmemHeader **shim)
+ PGShmemHeader **shim, Pointer base)
{
IpcMemoryKey NextShmemSegID;
void *memAddress;
@@ -760,14 +980,6 @@ PGSharedMemoryCreate(Size size,
errmsg("could not stat data directory \"%s\": %m",
DataDir)));
- /* Complain if hugepages demanded but we can't possibly support them */
-#if !defined(MAP_HUGETLB)
- if (huge_pages == HUGE_PAGES_ON)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("huge pages not supported on this platform")));
-#endif
-
/* For now, we don't support huge pages in SysV memory */
if (huge_pages == HUGE_PAGES_ON && shared_memory_type != SHMEM_TYPE_MMAP)
ereport(ERROR,
@@ -782,7 +994,7 @@ PGSharedMemoryCreate(Size size,
if (shared_memory_type == SHMEM_TYPE_MMAP)
{
/* On success, mapping data will be modified. */
- CreateAnonymousSegment(mapping);
+ CreateAnonymousSegment(mapping, base);
next_free_segment++;
diff --git a/src/backend/port/win32_shmem.c b/src/backend/port/win32_shmem.c
index 4dee856d6bd..ce719f1b412 100644
--- a/src/backend/port/win32_shmem.c
+++ b/src/backend/port/win32_shmem.c
@@ -205,7 +205,7 @@ EnableLockPagesPrivilege(int elevel)
*/
PGShmemHeader *
PGSharedMemoryCreate(Size size,
- PGShmemHeader **shim)
+ PGShmemHeader **shim, Pointer base)
{
void *memAddress;
PGShmemHeader *hdr;
diff --git a/src/backend/storage/ipc/ipci.c b/src/backend/storage/ipc/ipci.c
index 8b38e985327..076888c0172 100644
--- a/src/backend/storage/ipc/ipci.c
+++ b/src/backend/storage/ipc/ipci.c
@@ -203,9 +203,12 @@ CreateSharedMemoryAndSemaphores(void)
PGShmemHeader *seghdr;
Size size;
int numSemas;
+ void *base;
Assert(!IsUnderPostmaster);
+ base = ReserveAnonymousMemory((Size) MaxAvailableMemory * BLCKSZ);
+
for(int segment = 0; segment < ANON_MAPPINGS; segment++)
{
/* Compute the size of the shared-memory block */
@@ -217,7 +220,7 @@ CreateSharedMemoryAndSemaphores(void)
*
* XXX: Do multiple shims are needed, one per segment?
*/
- seghdr = PGSharedMemoryCreate(size, &shim);
+ seghdr = PGSharedMemoryCreate(size, &shim, base);
/*
* Make sure that huge pages are never reported as "unknown" while the
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 2152aad97d9..1d42a5856c0 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -140,6 +140,7 @@ int max_parallel_maintenance_workers = 2;
* register background workers.
*/
int NBuffers = 16384;
+int MaxAvailableMemory = 131072;
int MaxConnections = 100;
int max_worker_processes = 8;
int max_parallel_workers = 8;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 4eaeca89f2c..dede37f7905 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2364,6 +2364,20 @@ struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"max_available_memory", PGC_SIGHUP, RESOURCES_MEM,
+ gettext_noop("Sets the upper limit for the shared_buffers value."),
+ gettext_noop("Shared memory could be resized at runtime, this "
+ "parameters sets the upper limit for it, beyond which "
+ "resizing would not be supported. Normally this value "
+ "would be the same as the total available memory."),
+ GUC_UNIT_BLOCKS
+ },
+ &MaxAvailableMemory,
+ 131072, 16, INT_MAX / 2,
+ NULL, NULL, NULL
+ },
+
{
{"vacuum_buffer_usage_limit", PGC_USERSET, RESOURCES_MEM,
gettext_noop("Sets the buffer pool size for VACUUM, ANALYZE, and autovacuum."),
diff --git a/src/include/storage/pg_shmem.h b/src/include/storage/pg_shmem.h
index 138078c29c5..4a83e255652 100644
--- a/src/include/storage/pg_shmem.h
+++ b/src/include/storage/pg_shmem.h
@@ -60,6 +60,7 @@ extern PGDLLIMPORT ShmemSegment Segments[ANON_MAPPINGS];
extern PGDLLIMPORT int shared_memory_type;
extern PGDLLIMPORT int huge_pages;
extern PGDLLIMPORT int huge_page_size;
+extern PGDLLIMPORT int MaxAvailableMemory;
/* Possible values for huge_pages and huge_pages_status */
typedef enum
@@ -100,10 +101,11 @@ extern void PGSharedMemoryNoReAttach(void);
#endif
extern PGShmemHeader *PGSharedMemoryCreate(Size size,
- PGShmemHeader **shim);
+ PGShmemHeader **shim, Pointer base);
extern bool PGSharedMemoryIsInUse(unsigned long id1, unsigned long id2);
extern void PGSharedMemoryDetach(void);
extern void GetHugePageSize(Size *hugepagesize, int *mmap_flags);
+void *ReserveAnonymousMemory(Size reserve_size);
/* The main segment, contains everything except buffer blocks and related data. */
#define MAIN_SHMEM_SEGMENT 0
--
2.45.1
--vninua6xybvzgrci
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
filename="v4-0003-Introduce-multiple-shmem-segments-for-shared-buff.patch"
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-10-16 18:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-06 07:54 [PATCH v7] Fix orderby handling in postgres_fdw Ronan Dunklau <ronan.dunklau@aiven.io>
2021-09-06 07:54 [PATCH v8] Fix orderby handling in postgres_fdw Ronan Dunklau <ronan.dunklau@aiven.io>
2024-07-02 06:12 [PATCH 2/2] Add has_large_object_privilege function Yugo Nagata <nagata@sraoss.co.jp>
2024-07-02 06:12 [PATCH 2/2] Add has_large_object_privilege function Yugo Nagata <nagata@sraoss.co.jp>
2024-10-16 18:21 [PATCH v4 2/8] Address space reservation for shared memory Dmitrii Dolgov <9erthalion6@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox