Since I'm not observing any movement on the key question of redesigning
the tablesample method API, and I think that's something that's absolutely
necessary to fix for 9.5, attached is an attempt to respecify the API.
I haven't actually written any code, but I've written a tsmapi.h file
modeled on fdwapi.h, and rewritten tablesample-method.sgml to match;
those two files are attached. Some notes:
* This is predicated on the assumption that we'll get rid of the
pg_tablesample_method catalog and instead have a single FDW-style handler
function for each sample method. That fixes the problem with the contrib
modules being broken in DROP/pg_upgrade cases.
* I got rid of the TableSampleDesc struct altogether in favor of giving
the execution functions access to the whole SampleScanState executor
state node. If we add support for filtering at the join level, filtering
in indexscan nodes, etc, those would become separate sets of API functions
in my view; there is no need to pretend that this set of API functions
works for anything except the SampleScan case. This way is more parallel
to the FDW precedent, too. In particular it lets tablesample code get at
the executor's EState, which the old API did not, but which might be
necessary for some scenarios.
* You might have expected me to move the tsmseqscan and tsmpagemode flags
into the TsmRoutine struct, but instead this API puts equivalent flags
into the SampleScanState struct. The reason for that is that it lets
the settings be determined at runtime after inspecting the TABLESAMPLE
parameters, which I think would be useful. For example, whether to use
the bulkread strategy should probably depend on what the sampling
percentage is.
* I got rid of the ExamineTuple function altogether. As I said before,
I think what that is mostly doing is encouraging people to do unsound
things. But in any case, there is no need for it because NextSampleTuple
*can* look at the HeapScanDesc state if it really wants to: that lets it
identify visible tuples, or even inspect the tuple contents. In the
attached, I documented the visible-tuples aspect but did not suggest
examining tuple contents.
* As written, this still allows TABLESAMPLE parameters to have null
values, but I'm pretty strongly tempted to get rid of that: remove
the paramisnull[] argument and make the core code reject null values.
I can't see any benefit in allowing null values that would justify the
extra code and risks-of-omission involved in making every tablesample
method check this for itself.
* I specified that omitting NextSampleBlock is allowed and causes the
core code to do a standard seqscan, including syncscan support, which
is a behavior that's impossible with the current API. If we fix
the bernoulli code to have history-independent sampling behavior,
I see no reason that syncscan shouldn't be enabled for it.
Barring objections, I'll press forward with turning this into code
over the next few days.
regards, tom lane
/*-------------------------------------------------------------------------
*
* tsmapi.h
* API for tablesample methods
*
* Copyright (c) 2015, PostgreSQL Global Development Group
*
* src/include/access/tsmapi.h
*
*-------------------------------------------------------------------------
*/
#ifndef TSMAPI_H
#define TSMAPI_H
#include "nodes/execnodes.h"
#include "nodes/relation.h"
/*
* Callback function signatures --- see tablesample-method.sgml for more info.
*/
typedef void (*SampleScanCost_function) (PlannerInfo *root,
RelOptInfo *baserel,
List *paramexprs,
BlockNumber *pages,
double *tuples);
typedef void (*BeginSampleScan_function) (SampleScanState *node,
int eflags,
uint32 seed,
Datum *params,
bool *paramisnull,
int nparams);
typedef BlockNumber (*NextSampleBlock_function) (SampleScanState *node);
typedef OffsetNumber (*NextSampleTuple_function) (SampleScanState *node,
BlockNumber blockno,
OffsetNumber maxoffset);
typedef void (*ReScanSampleScan_function) (SampleScanState *node);
typedef void (*EndSampleScan_function) (SampleScanState *node);
/*
* TsmRoutine is the struct returned by a tablesample method's handler
* function. It provides pointers to the callback functions needed by the
* planner and executor, as well as additional information about the method.
*
* More function pointers are likely to be added in the future.
* Therefore it's recommended that the handler initialize the struct with
* makeNode(TsmRoutine) so that all fields are set to NULL. This will
* ensure that no fields are accidentally left undefined.
*/
typedef struct TsmRoutine
{
NodeTag type;
/* List of datatype OIDs for the arguments of the TABLESAMPLE clause */
List *parameterTypes;
/* Can method produce repeatable samples across, or even within, queries? */
bool repeatable_across_queries;
bool repeatable_across_scans;
/* Functions for planning a SampleScan on a physical table */
SampleScanCost_function SampleScanCost;
/* Functions for executing a SampleScan on a physical table */
BeginSampleScan_function BeginSampleScan;
NextSampleBlock_function NextSampleBlock; /* can be NULL */
NextSampleTuple_function NextSampleTuple;
ReScanSampleScan_function ReScanSampleScan;
EndSampleScan_function EndSampleScan; /* can be NULL */
} TsmRoutine;
#endif /* TSMAPI_H */
Title: Writing A TABLESAMPLE Sampling Method
tablesample method
PostgreSQL's implementation of the TABLESAMPLE
clause supports custom sampling methods, in addition to
the BERNOULLI and SYSTEM methods that are required
by the SQL standard. The sampling method determines which rows of the
table will be selected when the TABLESAMPLE clause is used.
At the SQL level, a tablesample method is represented by a single SQL
function, typically implemented in C, having the signature
method_name(internal) RETURNS tsm_handler
The name of the function is the same method name appearing in the
TABLESAMPLE clause. The internal argument is a dummy
(always having value zero) that simply serves to prevent this function from
being called directly from a SQL command.
The result of the function must be a palloc'd struct of
type TsmRoutine, which contains pointers to support functions for
the tablesample method. These support functions are plain C functions and
are not visible or callable at the SQL level. The support functions are
described in .
In addition to function pointers, the TsmRoutine struct must
include these additional fields:
List *parameterTypes
This is an OID list containing the data type OIDs of the parameter(s)
that will be accepted by the TABLESAMPLE clause when this
sampling method is used. For example, for the built-in methods, this
list contains a single item with value FLOAT4OID, which
represents the sampling percentage. Custom sampling methods can have
more or different parameters.
bool repeatable_across_queries
If true, the sampling method can deliver identical samples
across successive queries, if the same REPEATABLE seed
value is supplied each time and the table contents have not changed.
When this is false, the REPEATABLE clause is
not accepted for use with the sampling method.
bool repeatable_across_scans
If true, the sampling method can deliver identical samples
across successive scans in the same query (with an unchanging snapshot).
When this is false, the planner will not select plans that
would require scanning the sampled table more than once, since that
might result in inconsistent query output.
The TsmRoutine struct type is declared
in src/include/access/tsmapi.h, which see for additional
details.
Tablesample Support Functions
The TSM handler function returns a palloc'd TsmRoutine struct
containing pointers to the support functions described below. Most of
the functions are required, but some are optional, and those pointers can
be NULL.
void
SampleScanCost (PlannerInfo *root,
RelOptInfo *baserel,
List *paramexprs,
BlockNumber *pages,
double *tuples);
This function is called during planning. It must estimate the number of
relation pages that will be read during a sample scan, and the number of
tuples that will be selected by the scan. (For example, these might be
determined by estimating the sampling fraction, and then multiplying
the baserel->pages and baserel->tuples
numbers by that, being sure to round the results to integral values.)
The paramexprs list holds the _expression_(s) that are
parameters to the TABLESAMPLE clause. It is recommended to
use estimate_expression_value() to try to reduce these
expressions to constants, if their values are needed for estimation
purposes; but the function must provide cost estimates even if they cannot
be reduced, and it should not fail even if the values appear invalid
(remember that they're only estimates of what the run-time values will
be).
The pages and tuples parameters are outputs.
void
BeginSampleScan (SampleScanState *node,
int eflags,
uint32 seed,
Datum *params,
bool *paramisnull,
int nparams);
Initialize for execution of a SampleScan plan node.
This is called during executor startup.
It should perform any initialization needed before the scan can start.
The SampleScanState node has already been created, but
its tsm_state field is NULL.
The BeginSampleScan function can palloc whatever internal
state data is needed by the tablesample method, and store a pointer to
it in node->tsm_state.
Information about the table to scan is accessible through other fields
of the SampleScanState node.
eflags contains flag bits describing the executor's
operating mode for this plan node.
seed contains a seed to use for any random numbers generated
within the sampling method; it is either a hash derived from the
REPEATABLE value if one was given, or the result
of random() if not.
The params and paramisnull arrays, each of length
nparams, contain the values of the parameters supplied in
the TABLESAMPLE clause. These will have the number and types
specified in the tablesample method's parameterTypes
list; but note that the core code does not reject null values.
This function can also adjust the fields node->use_bulkread
and node->use_pagemode.
If node->use_bulkread is true, which it is by
default, the scan will use a buffer access strategy that encourages
recycling buffers after use. It might be reasonable to set this
to false if the scan will visit only a small fraction of the
table's pages.
If node->use_pagemode is true, which it is by
default, the scan will perform visibility checking in a single pass for
all tuples on each visited page. It might be reasonable to set this
to false if the scan will select only a small fraction of the
tuples on each visited page. That will result in fewer tuple visibility
checks being performed, though each one will be more expensive because it
will require more locking.
Note that when (eflags & EXEC_FLAG_EXPLAIN_ONLY) is true,
this function should only do the minimum required to make the node state
valid for EXPLAIN and EndSampleScan.
BlockNumber
NextSampleBlock (SampleScanState *node);
Returns the block number of the next page to be scanned, or
InvalidBlockNumber if no pages remain to be scanned.
This function can be omitted (set the pointer to NULL), in which case
the core code will perform a sequential scan of the entire relation.
Such a scan can use synchronized scanning, so that the tablesample method
cannot assume that the relation pages are visited in the same order on
each scan.
OffsetNumber
NextSampleTuple (SampleScanState *node,
BlockNumber blockno,
OffsetNumber maxoffset);
Returns the offset number of the next tuple to be sampled on the
specified page, or InvalidOffsetNumber if no tuples remain to
be sampled. maxoffset is the largest offset number in use
on the page.
NextSampleTuple is not explicitly told which of the offset
numbers in the range 1 .. maxoffset actually contain valid
tuples. This is not normally a problem since the core code ignores
requests to sample missing or invisible tuples; that should not result in
any bias in the sample. However, if necessary, the function can
examine node->ss.ss_currentScanDesc->rs_vistuples[]
to identify which tuples are valid and visible. (This
requires node->use_pagemode to be true.)
NextSampleTuple must not assume
that blockno is the same page number returned by the most
recent NextSampleBlock call. It was returned by some
previous NextSampleBlock call, but the core code is allowed
to call NextSampleBlock in advance of actually scanning
pages, so as to support prefetching. It is OK to assume that once
sampling of a given page begins, successive NextSampleTuple
calls all refer to the same page until InvalidOffsetNumber is
returned.
void
ReScanSampleScan (SampleScanState *node);
Restart the scan from the beginning. If the tablesample method is
marked repeatable_across_scans, it must be able
to select the same set of tuples during a rescan.
void
EndSampleScan (SampleScanState *node);
End the scan and release resources. It is normally not important
to release palloc'd memory, but any externally-visible resources
should be cleaned up.
This function can be omitted (set the pointer to NULL) in the common
case where no such resources exist.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
