56.2. Foreign Data Wrapper Callback Routines
The FDW handler function returns a palloc'd FdwRoutine
struct containing pointers to the callback functions described below. The scan-related functions are required, the rest are optional.
The FdwRoutine
struct type is declared in src/include/foreign/fdwapi.h
, which see for additional details.
56.2.1. FDW Routines for Scanning Foreign Tables
Obtain relation size estimates for a foreign table. This is called at the beginning of planning for a query that scans a foreign table. root
is the planner's global information about the query; baserel
is the planner's information about this table; and foreigntableid
is the pg_class
OID of the foreign table. (foreigntableid
could be obtained from the planner data structures, but it's passed explicitly to save effort.)
This function should update baserel->rows
to be the expected number of rows returned by the table scan, after accounting for the filtering done by the restriction quals. The initial value of baserel->rows
is just a constant default estimate, which should be replaced if at all possible. The function may also choose to update baserel->width
if it can compute a better estimate of the average result row width.
See Section 56.4 for additional information.
Create possible access paths for a scan on a foreign table. This is called during query planning. The parameters are the same as for GetForeignRelSize
, which has already been called.
This function must generate at least one access path (ForeignPath
node) for a scan on the foreign table and must call add_path
to add each such path to baserel->pathlist
. It's recommended to use create_foreignscan_path
to build the ForeignPath
nodes. The function can generate multiple access paths, e.g., a path which has valid pathkeys
to represent a pre-sorted result. Each access path must contain cost estimates, and can contain any FDW-private information that is needed to identify the specific scan method intended.
See Section 56.4 for additional information.
Create a ForeignScan
plan node from the selected foreign access path. This is called at the end of query planning. The parameters are as for GetForeignRelSize
, plus the selected ForeignPath
(previously produced by GetForeignPaths
, GetForeignJoinPaths
, or GetForeignUpperPaths
), the target list to be emitted by the plan node, the restriction clauses to be enforced by the plan node, and the outer subplan of the ForeignScan
, which is used for rechecks performed by RecheckForeignScan
. (If the path is for a join rather than a base relation, foreigntableid
is InvalidOid
.)
This function must create and return a ForeignScan
plan node; it's recommended to use make_foreignscan
to build the ForeignScan
node.
See Section 56.4 for additional information.
Begin executing a foreign scan. This is called during executor startup. It should perform any initialization needed before the scan can start, but not start executing the actual scan (that should be done upon the first call to IterateForeignScan
). The ForeignScanState
node has already been created, but its fdw_state
field is still NULL. Information about the table to scan is accessible through the ForeignScanState
node (in particular, from the underlying ForeignScan
plan node, which contains any FDW-private information provided by GetForeignPlan
). eflags
contains flag bits describing the executor's operating mode for this plan node.
Note that when (eflags & EXEC_FLAG_EXPLAIN_ONLY)
is true, this function should not perform any externally-visible actions; it should only do the minimum required to make the node state valid for ExplainForeignScan
and EndForeignScan
.
Fetch one row from the foreign source, returning it in a tuple table slot (the node's ScanTupleSlot
should be used for this purpose). Return NULL if no more rows are available. The tuple table slot infrastructure allows either a physical or virtual tuple to be returned; in most cases the latter choice is preferable from a performance standpoint. Note that this is called in a short-lived memory context that will be reset between invocations. Create a memory context in BeginForeignScan
if you need longer-lived storage, or use the es_query_cxt
of the node's EState
.
The rows returned must match the fdw_scan_tlist
target list if one was supplied, otherwise they must match the row type of the foreign table being scanned. If you choose to optimize away fetching columns that are not needed, you should insert nulls in those column positions, or else generate a fdw_scan_tlist
list with those columns omitted.
Note that PostgreSQL's executor doesn't care whether the rows returned violate any constraints that were defined on the foreign table — but the planner does care, and may optimize queries incorrectly if there are rows visible in the foreign table that do not satisfy a declared constraint. If a constraint is violated when the user has declared that the constraint should hold true, it may be appropriate to raise an error (just as you would need to do in the case of a data type mismatch).
Restart the scan from the beginning. Note that any parameters the scan depends on may have changed value, so the new scan does not necessarily return exactly the same rows.
End the scan and release resources. It is normally not important to release palloc'd memory, but for example open files and connections to remote servers should be cleaned up.
56.2.2. FDW Routines for Scanning Foreign Joins
If an FDW supports performing foreign joins remotely (rather than by fetching both tables' data and doing the join locally), it should provide this callback function:
Create possible access paths for a join of two (or more) foreign tables that all belong to the same foreign server. This optional function is called during query planning. As with GetForeignPaths
, this function should generate ForeignPath
path(s) for the supplied joinrel
(use create_foreign_join_path
to build them), and call add_path
to add these paths to the set of paths considered for the join. But unlike GetForeignPaths
, it is not necessary that this function succeed in creating at least one path, since paths involving local joining are always possible.
Note that this function will be invoked repeatedly for the same join relation, with different combinations of inner and outer relations; it is the responsibility of the FDW to minimize duplicated work.
If a ForeignPath
path is chosen for the join, it will represent the entire join process; paths generated for the component tables and subsidiary joins will not be used. Subsequent processing of the join path proceeds much as it does for a path scanning a single foreign table. One difference is that the scanrelid
of the resulting ForeignScan
plan node should be set to zero, since there is no single relation that it represents; instead, the fs_relids
field of the ForeignScan
node represents the set of relations that were joined. (The latter field is set up automatically by the core planner code, and need not be filled by the FDW.) Another difference is that, because the column list for a remote join cannot be found from the system catalogs, the FDW must fill fdw_scan_tlist
with an appropriate list of TargetEntry
nodes, representing the set of columns it will supply at run time in the tuples it returns.
See Section 56.4 for additional information.
56.2.3. FDW Routines for Planning Post-Scan/Join Processing
If an FDW supports performing remote post-scan/join processing, such as remote aggregation, it should provide this callback function:
Create possible access paths for upper relation processing, which is the planner's term for all post-scan/join query processing, such as aggregation, window functions, sorting, and table updates. This optional function is called during query planning. Currently, it is called only if all base relation(s) involved in the query belong to the same FDW. This function should generate ForeignPath
path(s) for any post-scan/join processing that the FDW knows how to perform remotely (use create_foreign_upper_path
to build them), and call add_path
to add these paths to the indicated upper relation. As with GetForeignJoinPaths
, it is not necessary that this function succeed in creating any paths, since paths involving local processing are always possible.
The stage
parameter identifies which post-scan/join step is currently being considered. output_rel
is the upper relation that should receive paths representing computation of this step, and input_rel
is the relation representing the input to this step. The extra
parameter provides additional details, currently, it is set only for UPPERREL_PARTIAL_GROUP_AGG
or UPPERREL_GROUP_AGG
, in which case it points to a GroupPathExtraData
structure; or for UPPERREL_FINAL
, in which case it points to a FinalPathExtraData
structure. (Note that ForeignPath
paths added to output_rel
would typically not have any direct dependency on paths of the input_rel
, since their processing is expected to be done externally. However, examining paths previously generated for the previous processing step can be useful to avoid redundant planning work.)
See Section 56.4 for additional information.
56.2.4. FDW Routines for Updating Foreign Tables
If an FDW supports writable foreign tables, it should provide some or all of the following callback functions depending on the needs and capabilities of the FDW:
UPDATE
and DELETE
operations are performed against rows previously fetched by the table-scanning functions. The FDW may need extra information, such as a row ID or the values of primary-key columns, to ensure that it can identify the exact row to update or delete. To support that, this function can add extra hidden, or “junk”, target columns to the list of columns that are to be retrieved from the foreign table during an UPDATE
or DELETE
.
To do that, add TargetEntry
items to parsetree->targetList
, containing expressions for the extra values to be fetched. Each such entry must be marked resjunk
= true
, and must have a distinct resname
that will identify it at execution time. Avoid using names matching ctid
N
, wholerow
, or wholerow
N
, as the core system can generate junk columns of these names. If the extra expressions are more complex than simple Vars, they must be run through eval_const_expressions
before adding them to the targetlist.
Although this function is called during planning, the information provided is a bit different from that available to other planning routines. parsetree
is the parse tree for the UPDATE
or DELETE
command, while target_rte
and target_relation
describe the target foreign table.
If the AddForeignUpdateTargets
pointer is set to NULL
, no extra target expressions are added. (This will make it impossible to implement DELETE
operations, though UPDATE
may still be feasible if the FDW relies on an unchanging primary key to identify rows.)
Perform any additional planning actions needed for an insert, update, or delete on a foreign table. This function generates the FDW-private information that will be attached to the ModifyTable
plan node that performs the update action. This private information must have the form of a List
, and will be delivered to BeginForeignModify
during the execution stage.
root
is the planner's global information about the query. plan
is the ModifyTable
plan node, which is complete except for the fdwPrivLists
field. resultRelation
identifies the target foreign table by its range table index. subplan_index
identifies which target of the ModifyTable
plan node this is, counting from zero; use this if you want to index into plan->plans
or other substructure of the plan
node.
See Section 56.4 for additional information.
If the PlanForeignModify
pointer is set to NULL
, no additional plan-time actions are taken, and the fdw_private
list delivered to BeginForeignModify
will be NIL.
Begin executing a foreign table modification operation. This routine is called during executor startup. It should perform any initialization needed prior to the actual table modifications. Subsequently, ExecForeignInsert
, ExecForeignUpdate
or ExecForeignDelete
will be called for each tuple to be inserted, updated, or deleted.
mtstate
is the overall state of the ModifyTable
plan node being executed; global data about the plan and execution state is available via this structure. rinfo
is the ResultRelInfo
struct describing the target foreign table. (The ri_FdwState
field of ResultRelInfo
is available for the FDW to store any private state it needs for this operation.) fdw_private
contains the private data generated by PlanForeignModify
, if any. subplan_index
identifies which target of the ModifyTable
plan node this is. eflags
contains flag bits describing the executor's operating mode for this plan node.
Note that when (eflags & EXEC_FLAG_EXPLAIN_ONLY)
is true, this function should not perform any externally-visible actions; it should only do the minimum required to make the node state valid for ExplainForeignModify
and EndForeignModify
.
If the BeginForeignModify
pointer is set to NULL
, no action is taken during executor startup.
Insert one tuple into the foreign table. estate
is global execution state for the query. rinfo
is the ResultRelInfo
struct describing the target foreign table. slot
contains the tuple to be inserted; it will match the row-type definition of the foreign table. planSlot
contains the tuple that was generated by the ModifyTable
plan node's subplan; it differs from slot
in possibly containing additional “junk” columns. (The planSlot
is typically of little interest for INSERT
cases, but is provided for completeness.)
The return value is either a slot containing the data that was actually inserted (this might differ from the data supplied, for example as a result of trigger actions), or NULL if no row was actually inserted (again, typically as a result of triggers). The passed-in slot
can be re-used for this purpose.
The data in the returned slot is used only if the INSERT
statement has a RETURNING
clause or involves a view WITH CHECK OPTION
; or if the foreign table has an AFTER ROW
trigger. Triggers require all columns, but the FDW could choose to optimize away returning some or all columns depending on the contents of the RETURNING
clause or WITH CHECK OPTION
constraints. Regardless, some slot must be returned to indicate success, or the query's reported row count will be wrong.
If the ExecForeignInsert
pointer is set to NULL
, attempts to insert into the foreign table will fail with an error message.
Note that this function is also called when inserting routed tuples into a foreign-table partition or executing COPY FROM
on a foreign table, in which case it is called in a different way than it is in the INSERT
case. See the callback functions described below that allow the FDW to support that.
Update one tuple in the foreign table. estate
is global execution state for the query. rinfo
is the ResultRelInfo
struct describing the target foreign table. slot
contains the new data for the tuple; it will match the row-type definition of the foreign table. planSlot
contains the tuple that was generated by the ModifyTable
plan node's subplan; it differs from slot
in possibly containing additional “junk” columns. In particular, any junk columns that were requested by AddForeignUpdateTargets
will be available from this slot.
The return value is either a slot containing the row as it was actually updated (this might differ from the data supplied, for example as a result of trigger actions), or NULL if no row was actually updated (again, typically as a result of triggers). The passed-in slot
can be re-used for this purpose.
The data in the returned slot is used only if the UPDATE
statement has a RETURNING
clause or involves a view WITH CHECK OPTION
; or if the foreign table has an AFTER ROW
trigger. Triggers require all columns, but the FDW could choose to optimize away returning some or all columns depending on the contents of the RETURNING
clause or WITH CHECK OPTION
constraints. Regardless, some slot must be returned to indicate success, or the query's reported row count will be wrong.
If the ExecForeignUpdate
pointer is set to NULL
, attempts to update the foreign table will fail with an error message.
Delete one tuple from the foreign table. estate
is global execution state for the query. rinfo
is the ResultRelInfo
struct describing the target foreign table. slot
contains nothing useful upon call, but can be used to hold the returned tuple. planSlot
contains the tuple that was generated by the ModifyTable
plan node's subplan; in particular, it will carry any junk columns that were requested by AddForeignUpdateTargets
. The junk column(s) must be used to identify the tuple to be deleted.
The return value is either a slot containing the row that was deleted, or NULL if no row was deleted (typically as a result of triggers). The passed-in slot
can be used to hold the tuple to be returned.
The data in the returned slot is used only if the DELETE
query has a RETURNING
clause or the foreign table has an AFTER ROW
trigger. Triggers require all columns, but the FDW could choose to optimize away returning some or all columns depending on the contents of the RETURNING
clause. Regardless, some slot must be returned to indicate success, or the query's reported row count will be wrong.
If the ExecForeignDelete
pointer is set to NULL
, attempts to delete from the foreign table will fail with an error message.