TABLESAMPLE, SQL Standard and extensible

Add a TABLESAMPLE clause to SELECT statements that allows
user to specify random BERNOULLI sampling or block level
SYSTEM sampling. Implementation allows for extensible
sampling functions to be written, using a standard API.
Basic version follows SQLStandard exactly. Usable
concrete use cases for the sampling API follow in later
commits.

Petr Jelinek

Reviewed by Michael Paquier and Simon Riggs

1 files changed, 143 insertions, 0 deletions

diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index f7affebf84e..fa50f92d8dd 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -18,6 +18,7 @@
 #include "catalog/pg_aggregate.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_type.h"
+#include "catalog/pg_tablesample_method.h"
 #include "funcapi.h"
 #include "lib/stringinfo.h"
 #include "nodes/makefuncs.h"
@@ -26,6 +27,7 @@
 #include "parser/parse_clause.h"
 #include "parser/parse_coerce.h"
 #include "parser/parse_func.h"
+#include "parser/parse_expr.h"
 #include "parser/parse_relation.h"
 #include "parser/parse_target.h"
 #include "parser/parse_type.h"
@@ -767,6 +769,147 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
 }
 
 
+/*
+ * ParseTableSample
+ *
+ * Parse TABLESAMPLE clause and process the arguments
+ */
+TableSampleClause *
+ParseTableSample(ParseState *pstate, char *samplemethod, Node *repeatable,
+				 List *sampleargs, int location)
+{
+	HeapTuple		tuple;
+	Form_pg_tablesample_method tsm;
+	Form_pg_proc procform;
+	TableSampleClause *tablesample;
+	List		   *fargs;
+	ListCell	   *larg;
+	int				nargs, initnargs;
+	Oid				init_arg_types[FUNC_MAX_ARGS];
+
+	/* Load the tablesample method */
+	tuple = SearchSysCache1(TABLESAMPLEMETHODNAME, PointerGetDatum(samplemethod));
+	if (!HeapTupleIsValid(tuple))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("tablesample method \"%s\" does not exist",
+						samplemethod),
+				 parser_errposition(pstate, location)));
+
+	tablesample = makeNode(TableSampleClause);
+	tablesample->tsmid = HeapTupleGetOid(tuple);
+
+	tsm = (Form_pg_tablesample_method) GETSTRUCT(tuple);
+
+	tablesample->tsmseqscan = tsm->tsmseqscan;
+	tablesample->tsmpagemode = tsm->tsmpagemode;
+	tablesample->tsminit = tsm->tsminit;
+	tablesample->tsmnextblock = tsm->tsmnextblock;
+	tablesample->tsmnexttuple = tsm->tsmnexttuple;
+	tablesample->tsmexaminetuple = tsm->tsmexaminetuple;
+	tablesample->tsmend = tsm->tsmend;
+	tablesample->tsmreset = tsm->tsmreset;
+	tablesample->tsmcost = tsm->tsmcost;
+
+	ReleaseSysCache(tuple);
+
+	/* Validate the parameters against init function definition. */
+	tuple = SearchSysCache1(PROCOID,
+							ObjectIdGetDatum(tablesample->tsminit));
+
+	if (!HeapTupleIsValid(tuple))	/* should not happen */
+		elog(ERROR, "cache lookup failed for function %u",
+			 tablesample->tsminit);
+
+	procform = (Form_pg_proc) GETSTRUCT(tuple);
+	initnargs = procform->pronargs;
+	Assert(initnargs >= 3);
+
+	/*
+	 * First parameter is used to pass the SampleScanState, second is
+	 * seed (REPEATABLE), skip the processing for them here, just assert
+	 * that the types are correct.
+	 */
+	Assert(procform->proargtypes.values[0] == INTERNALOID);
+	Assert(procform->proargtypes.values[1] == INT4OID);
+	initnargs -= 2;
+	memcpy(init_arg_types, procform->proargtypes.values + 2,
+				   initnargs * sizeof(Oid));
+
+	/* Now we are done with the catalog */
+	ReleaseSysCache(tuple);
+
+	/* Process repeatable (seed) */
+	if (repeatable != NULL)
+	{
+		Node   *arg = repeatable;
+
+		if (arg && IsA(arg, A_Const))
+		{
+			A_Const    *con = (A_Const *) arg;
+
+			if (con->val.type == T_Null)
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+						 errmsg("REPEATABLE clause must be NOT NULL numeric value"),
+						 parser_errposition(pstate, con->location)));
+
+		}
+
+		arg = transformExpr(pstate, arg, EXPR_KIND_FROM_FUNCTION);
+		arg = coerce_to_specific_type(pstate, arg, INT4OID, "REPEATABLE");
+		tablesample->repeatable = arg;
+	}
+	else
+		tablesample->repeatable = NULL;
+
+	/* Check user provided expected number of arguments. */
+	if (list_length(sampleargs) != initnargs)
+		ereport(ERROR,
+				(errcode(ERRCODE_TOO_MANY_ARGUMENTS),
+		 errmsg_plural("tablesample method \"%s\" expects %d argument got %d",
+					   "tablesample method \"%s\" expects %d arguments got %d",
+					   initnargs,
+					   samplemethod,
+					   initnargs, list_length(sampleargs)),
+		 parser_errposition(pstate, location)));
+
+	/* Transform the arguments, typecasting them as needed. */
+	fargs = NIL;
+	nargs = 0;
+	foreach(larg, sampleargs)
+	{
+		Node   *inarg = (Node *) lfirst(larg);
+		Node   *arg = transformExpr(pstate, inarg, EXPR_KIND_FROM_FUNCTION);
+		Oid		argtype = exprType(arg);
+
+		if (argtype != init_arg_types[nargs])
+		{
+			if (!can_coerce_type(1, &argtype, &init_arg_types[nargs],
+								 COERCION_IMPLICIT))
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("wrong parameter %d for tablesample method \"%s\"",
+							nargs + 1, samplemethod),
+					 errdetail("Expected type %s got %s.",
+							   format_type_be(init_arg_types[nargs]),
+							   format_type_be(argtype)),
+					 parser_errposition(pstate, exprLocation(inarg))));
+
+			arg = coerce_type(pstate, arg, argtype, init_arg_types[nargs], -1,
+							  COERCION_IMPLICIT, COERCE_IMPLICIT_CAST, -1);
+		}
+
+		fargs = lappend(fargs, arg);
+		nargs++;
+	}
+
+	/* Pass the arguments down */
+	tablesample->args = fargs;
+
+	return tablesample;
+}
+
 /* func_match_argtypes()
  *
  * Given a list of candidate functions (having the right name and number