From 57e42f0a32be39eea541808979a41ab6feac6b73 Mon Sep 17 00:00:00 2001 From: Laurenz Albe Date: Mon, 19 Feb 2018 12:17:59 +0100 Subject: [PATCH] Add support for NEXT VALUE FOR NEXT VALUE FOR is the standard SQL expression for getting the next value from a sequence, which in PostgreSQL traditionally has been the task of the "nextval" function. This completes the implementation of SQL standard feature T176. --- doc/src/sgml/func.sgml | 10 ++++++++ doc/src/sgml/ref/create_sequence.sgml | 3 ++- doc/src/sgml/syntax.sgml | 18 ++++++++++++++ src/backend/commands/tablecmds.c | 2 ++ src/backend/executor/execExpr.c | 1 + src/backend/executor/execExprInterp.c | 3 ++- src/backend/nodes/copyfuncs.c | 2 ++ src/backend/nodes/equalfuncs.c | 2 ++ src/backend/nodes/outfuncs.c | 2 ++ src/backend/nodes/readfuncs.c | 2 ++ src/backend/parser/gram.y | 6 +++++ src/backend/parser/parse_expr.c | 43 ++++++++++++++++++++++++++++++++++ src/backend/rewrite/rewriteHandler.c | 2 ++ src/backend/utils/adt/ruleutils.c | 12 +++------- src/include/executor/execExpr.h | 1 + src/include/nodes/primnodes.h | 7 ++++-- src/test/regress/expected/sequence.out | 32 ++++++++++++++++++++++++- src/test/regress/sql/sequence.sql | 12 +++++++++- 18 files changed, 145 insertions(+), 15 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 487c7ff750..e135a05314 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -12095,6 +12095,16 @@ nextval('foo'::text) foo is looked up at This function requires USAGE or UPDATE privilege on the sequence. + + + + PostgreSQL also offers the SQL standard compliant expression + NEXT VALUE FOR sequence_name + which is the same as + nextval('sequence_name'). + See for details. + + diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml index 3e0d339c85..2671e80f12 100644 --- a/doc/src/sgml/ref/create_sequence.sgml +++ b/doc/src/sgml/ref/create_sequence.sgml @@ -51,7 +51,8 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] + <literal>NEXT VALUE FOR</literal> + + + NEXT VALUE FOR + + + + This expression is used to obtain the next value for a sequence. + +NEXT VALUE FOR sequence_name + + + It is equivalent to calling the nextval function. + + + + Expression Evaluation Rules diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 89454d8e80..34a1b0684d 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -5496,6 +5496,8 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, nve->seqid = RangeVarGetRelid(colDef->identitySequence, NoLock, false); nve->typeId = typeOid; + /* no need to check permissions on the implicit sequence */ + nve->checkperms = false; defval = (Expr *) nve; } diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index c6eb3ebacf..2f6b536d90 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -2104,6 +2104,7 @@ ExecInitExprRec(Expr *node, ExprState *state, scratch.opcode = EEOP_NEXTVALUEEXPR; scratch.d.nextvalueexpr.seqid = nve->seqid; scratch.d.nextvalueexpr.seqtypid = nve->typeId; + scratch.d.nextvalueexpr.checkperms = nve->checkperms; ExprEvalPushStep(state, &scratch); break; diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index f646fd9c51..1b067eb491 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -2355,7 +2355,8 @@ ExecEvalCurrentOfExpr(ExprState *state, ExprEvalStep *op) void ExecEvalNextValueExpr(ExprState *state, ExprEvalStep *op) { - int64 newval = nextval_internal(op->d.nextvalueexpr.seqid, false); + int64 newval = nextval_internal(op->d.nextvalueexpr.seqid, + op->d.nextvalueexpr.checkperms); switch (op->d.nextvalueexpr.seqtypid) { diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index bafe0d1071..aaadc8b54b 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2022,6 +2022,8 @@ _copyNextValueExpr(const NextValueExpr *from) COPY_SCALAR_FIELD(seqid); COPY_SCALAR_FIELD(typeId); + COPY_NODE_FIELD(relation); + COPY_SCALAR_FIELD(checkperms); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 02ca7d588c..31b3f7ca76 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -737,6 +737,8 @@ _equalNextValueExpr(const NextValueExpr *a, const NextValueExpr *b) { COMPARE_SCALAR_FIELD(seqid); COMPARE_SCALAR_FIELD(typeId); + COMPARE_NODE_FIELD(relation); + COMPARE_SCALAR_FIELD(checkperms); return true; } diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index e6ba096257..7eb73c31f9 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -1629,6 +1629,8 @@ _outNextValueExpr(StringInfo str, const NextValueExpr *node) WRITE_OID_FIELD(seqid); WRITE_OID_FIELD(typeId); + WRITE_NODE_FIELD(relation); + WRITE_BOOL_FIELD(checkperms); } static void diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index 22d8b9d0d5..0e88cc7df7 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -1218,6 +1218,8 @@ _readNextValueExpr(void) READ_OID_FIELD(seqid); READ_OID_FIELD(typeId); + READ_NODE_FIELD(relation); + READ_BOOL_FIELD(checkperms); READ_DONE(); } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 5329432f25..6db50c294e 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -13422,6 +13422,12 @@ c_expr: columnref { $$ = $1; } { $$ = $1; } | func_expr { $$ = $1; } + | NEXT VALUE_P FOR qualified_name + { + NextValueExpr *n = makeNode(NextValueExpr); + n->relation = $4; + $$ = (Node *)n; + } | select_with_parens %prec UMINUS { SubLink *n = makeNode(SubLink); diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index b2f5e46e3b..1d5c5d79db 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -15,8 +15,12 @@ #include "postgres.h" +#include "access/htup_details.h" +#include "catalog/pg_class.h" +#include "catalog/pg_sequence.h" #include "catalog/pg_type.h" #include "commands/dbcommands.h" +#include "commands/sequence.h" #include "miscadmin.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" @@ -36,6 +40,7 @@ #include "utils/builtins.h" #include "utils/date.h" #include "utils/lsyscache.h" +#include "utils/syscache.h" #include "utils/timestamp.h" #include "utils/xml.h" @@ -109,6 +114,7 @@ static Node *transformArrayExpr(ParseState *pstate, A_ArrayExpr *a, static Node *transformRowExpr(ParseState *pstate, RowExpr *r, bool allowDefault); static Node *transformCoalesceExpr(ParseState *pstate, CoalesceExpr *c); static Node *transformMinMaxExpr(ParseState *pstate, MinMaxExpr *m); +static Node *transformNextValueExpr(ParseState *pstate, NextValueExpr *nv); static Node *transformSQLValueFunction(ParseState *pstate, SQLValueFunction *svf); static Node *transformXmlExpr(ParseState *pstate, XmlExpr *x); @@ -303,6 +309,10 @@ transformExprRecurse(ParseState *pstate, Node *expr) result = transformMinMaxExpr(pstate, (MinMaxExpr *) expr); break; + case T_NextValueExpr: + result = transformNextValueExpr(pstate, (NextValueExpr *) expr); + break; + case T_SQLValueFunction: result = transformSQLValueFunction(pstate, (SQLValueFunction *) expr); @@ -2288,6 +2298,39 @@ transformMinMaxExpr(ParseState *pstate, MinMaxExpr *m) return (Node *) newm; } +static Node * +transformNextValueExpr(ParseState *pstate, NextValueExpr *nv) +{ + HeapTuple tuple; + Form_pg_sequence pg_seq_tuple; + + nv->seqid = RangeVarGetRelid(nv->relation, AccessShareLock, false); + nv->checkperms = true; + + if (get_rel_relkind(nv->seqid) != RELKIND_SEQUENCE) + { + if (nv->relation->schemaname) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\".\"%s\" is not a sequence", nv->relation->schemaname, nv->relation->relname))); + else + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a sequence", nv->relation->relname))); + } + + tuple = SearchSysCache1(SEQRELID, ObjectIdGetDatum(nv->seqid)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for sequence %u", nv->seqid); + + pg_seq_tuple = (Form_pg_sequence) GETSTRUCT(tuple); + nv->typeId = pg_seq_tuple->seqtypid; + + ReleaseSysCache(tuple); + + return (Node *) nv; +} + static Node * transformSQLValueFunction(ParseState *pstate, SQLValueFunction *svf) { diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 66253fc3d3..7b25c20794 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -1119,6 +1119,8 @@ build_column_default(Relation rel, int attrno) nve->seqid = getOwnedSequence(RelationGetRelid(rel), attrno); nve->typeId = att_tup->atttypid; + /* no need to check permissions on the implicit sequence */ + nve->checkperms = false; return (Node *) nve; } diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index c5f5a1ca3f..2e1b2f1b49 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8651,15 +8651,9 @@ get_rule_expr(Node *node, deparse_context *context, { NextValueExpr *nvexpr = (NextValueExpr *) node; - /* - * This isn't exactly nextval(), but that seems close enough - * for EXPLAIN's purposes. - */ - appendStringInfoString(buf, "nextval("); - simple_quote_literal(buf, - generate_relation_name(nvexpr->seqid, - NIL)); - appendStringInfoChar(buf, ')'); + appendStringInfo(buf, "NEXT VALUE FOR %s", + generate_relation_name(nvexpr->seqid, + NIL)); } break; diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index 117fc892f4..04ffae2285 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -394,6 +394,7 @@ typedef struct ExprEvalStep { Oid seqid; Oid seqtypid; + bool checkperms; } nextvalueexpr; /* for EEOP_ARRAYEXPR */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 1b4b0d75af..cc8a163b02 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1289,8 +1289,8 @@ typedef struct CurrentOfExpr /* * NextValueExpr - get next value from sequence * - * This has the same effect as calling the nextval() function, but it does not - * check permissions on the sequence. This is used for identity columns, + * This has the same effect as calling the nextval() function. + * It is used for NEXT VALUE FOR expressions and identity columns. * where the sequence is an implicit dependency without its own permissions. */ typedef struct NextValueExpr @@ -1298,6 +1298,9 @@ typedef struct NextValueExpr Expr xpr; Oid seqid; Oid typeId; + RangeVar *relation; + bool checkperms; /* false for identity columns, since the + sequence is an implicit dependency */ } NextValueExpr; /* diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out index ca5ea063fa..345f10fca8 100644 --- a/src/test/regress/expected/sequence.out +++ b/src/test/regress/expected/sequence.out @@ -237,7 +237,37 @@ SELECT nextval('sequence_test'::text); DISCARD SEQUENCES; SELECT currval('sequence_test'::regclass); ERROR: currval of sequence "sequence_test" is not yet defined in this session -DROP SEQUENCE sequence_test; +-- get sequence value with NEXT VALUE FOR +SELECT NEXT VALUE FOR sequence_test; + ?column? +---------- + 100 +(1 row) + +CREATE VIEW seq_v(s) AS SELECT NEXT VALUE FOR sequence_test; +SELECT s FROM seq_v; + s +----- + 101 +(1 row) + +SELECT pg_get_viewdef('seq_v'::regclass); + pg_get_viewdef +-------------------------------------------- + SELECT NEXT VALUE FOR sequence_test AS s; +(1 row) + +BEGIN; +ALTER SEQUENCE sequence_test RENAME TO "bogus seq"; +SELECT pg_get_viewdef('seq_v'::regclass); + pg_get_viewdef +------------------------------------------ + SELECT NEXT VALUE FOR "bogus seq" AS s; +(1 row) + +ROLLBACK; +DROP SEQUENCE sequence_test CASCADE; +NOTICE: drop cascades to view seq_v -- renaming sequences CREATE SEQUENCE foo_seq; ALTER TABLE foo_seq RENAME TO foo_seq_new; diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql index a7b9e63372..cdadac5c0b 100644 --- a/src/test/regress/sql/sequence.sql +++ b/src/test/regress/sql/sequence.sql @@ -128,7 +128,17 @@ SELECT nextval('sequence_test'::text); DISCARD SEQUENCES; SELECT currval('sequence_test'::regclass); -DROP SEQUENCE sequence_test; +-- get sequence value with NEXT VALUE FOR +SELECT NEXT VALUE FOR sequence_test; +CREATE VIEW seq_v(s) AS SELECT NEXT VALUE FOR sequence_test; +SELECT s FROM seq_v; +SELECT pg_get_viewdef('seq_v'::regclass); +BEGIN; +ALTER SEQUENCE sequence_test RENAME TO "bogus seq"; +SELECT pg_get_viewdef('seq_v'::regclass); +ROLLBACK; + +DROP SEQUENCE sequence_test CASCADE; -- renaming sequences CREATE SEQUENCE foo_seq; -- 2.14.3