diff options
29 files changed, 3671 insertions, 346 deletions
diff --git a/contrib/file_fdw/input/file_fdw.source b/contrib/file_fdw/input/file_fdw.source index 8e3d553f904..f7fd28d44d7 100644 --- a/contrib/file_fdw/input/file_fdw.source +++ b/contrib/file_fdw/input/file_fdw.source @@ -118,7 +118,6 @@ SELECT tableoid::regclass, b FROM agg_csv; INSERT INTO agg_csv VALUES(1,2.0); UPDATE agg_csv SET a = 1; DELETE FROM agg_csv WHERE a = 100; -SELECT * FROM agg_csv FOR UPDATE OF agg_csv; -- but this should be ignored SELECT * FROM agg_csv FOR UPDATE; diff --git a/contrib/file_fdw/output/file_fdw.source b/contrib/file_fdw/output/file_fdw.source index ece72429ba5..4f90baebd6b 100644 --- a/contrib/file_fdw/output/file_fdw.source +++ b/contrib/file_fdw/output/file_fdw.source @@ -185,15 +185,11 @@ SELECT tableoid::regclass, b FROM agg_csv; -- updates aren't supported INSERT INTO agg_csv VALUES(1,2.0); -ERROR: cannot change foreign table "agg_csv" +ERROR: cannot insert into foreign table "agg_csv" UPDATE agg_csv SET a = 1; -ERROR: cannot change foreign table "agg_csv" +ERROR: cannot update foreign table "agg_csv" DELETE FROM agg_csv WHERE a = 100; -ERROR: cannot change foreign table "agg_csv" -SELECT * FROM agg_csv FOR UPDATE OF agg_csv; -ERROR: row-level locks cannot be used with foreign table "agg_csv" -LINE 1: SELECT * FROM agg_csv FOR UPDATE OF agg_csv; - ^ +ERROR: cannot delete from foreign table "agg_csv" -- but this should be ignored SELECT * FROM agg_csv FOR UPDATE; a | b diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c index 32a3138ce0f..22ac50e6f9f 100644 --- a/contrib/postgres_fdw/connection.c +++ b/contrib/postgres_fdw/connection.c @@ -47,6 +47,8 @@ typedef struct ConnCacheEntry PGconn *conn; /* connection to foreign server, or NULL */ int xact_depth; /* 0 = no xact open, 1 = main xact open, 2 = * one level of subxact open, etc */ + bool have_prep_stmt; /* have we prepared any stmts in this xact? */ + bool have_error; /* have any subxacts aborted in this xact? */ } ConnCacheEntry; /* @@ -54,8 +56,9 @@ typedef struct ConnCacheEntry */ static HTAB *ConnectionHash = NULL; -/* for assigning cursor numbers */ +/* for assigning cursor numbers and prepared statement numbers */ static unsigned int cursor_number = 0; +static unsigned int prep_stmt_number = 0; /* tracks whether any work is needed in callback functions */ static bool xact_got_connection = false; @@ -78,6 +81,10 @@ static void pgfdw_subxact_callback(SubXactEvent event, * if we don't already have a suitable one, and a transaction is opened at * the right subtransaction nesting depth if we didn't do that already. * + * will_prep_stmt must be true if caller intends to create any prepared + * statements. Since those don't go away automatically at transaction end + * (not even on error), we need this flag to cue manual cleanup. + * * XXX Note that caching connections theoretically requires a mechanism to * detect change of FDW objects to invalidate already established connections. * We could manage that by watching for invalidation events on the relevant @@ -86,7 +93,8 @@ static void pgfdw_subxact_callback(SubXactEvent event, * mid-transaction anyway. */ PGconn * -GetConnection(ForeignServer *server, UserMapping *user) +GetConnection(ForeignServer *server, UserMapping *user, + bool will_prep_stmt) { bool found; ConnCacheEntry *entry; @@ -131,6 +139,8 @@ GetConnection(ForeignServer *server, UserMapping *user) /* initialize new hashtable entry (key is already filled in) */ entry->conn = NULL; entry->xact_depth = 0; + entry->have_prep_stmt = false; + entry->have_error = false; } /* @@ -147,6 +157,8 @@ GetConnection(ForeignServer *server, UserMapping *user) if (entry->conn == NULL) { entry->xact_depth = 0; /* just to be sure */ + entry->have_prep_stmt = false; + entry->have_error = false; entry->conn = connect_pg_server(server, user); elog(DEBUG3, "new postgres_fdw connection %p for server \"%s\"", entry->conn, server->servername); @@ -157,6 +169,9 @@ GetConnection(ForeignServer *server, UserMapping *user) */ begin_remote_xact(entry); + /* Remember if caller will prepare statements */ + entry->have_prep_stmt |= will_prep_stmt; + return entry->conn; } @@ -394,12 +409,30 @@ GetCursorNumber(PGconn *conn) } /* + * Assign a "unique" number for a prepared statement. + * + * This works much like GetCursorNumber, except that we never reset the counter + * within a session. That's because we can't be 100% sure we've gotten rid + * of all prepared statements on all connections, and it's not really worth + * increasing the risk of prepared-statement name collisions by resetting. + */ +unsigned int +GetPrepStmtNumber(PGconn *conn) +{ + return ++prep_stmt_number; +} + +/* * Report an error we got from the remote server. * * elevel: error level to use (typically ERROR, but might be less) * res: PGresult containing the error * clear: if true, PQclear the result (otherwise caller will handle it) * sql: NULL, or text of remote command we tried to execute + * + * Note: callers that choose not to throw ERROR for a remote error are + * responsible for making sure that the associated ConnCacheEntry gets + * marked with have_error = true. */ void pgfdw_report_error(int elevel, PGresult *res, bool clear, const char *sql) @@ -480,6 +513,22 @@ pgfdw_xact_callback(XactEvent event, void *arg) if (PQresultStatus(res) != PGRES_COMMAND_OK) pgfdw_report_error(ERROR, res, true, "COMMIT TRANSACTION"); PQclear(res); + + /* + * If there were any errors in subtransactions, and we made + * prepared statements, do a DEALLOCATE ALL to make sure we + * get rid of all prepared statements. This is annoying and + * not terribly bulletproof, but it's probably not worth + * trying harder. We intentionally ignore any errors in the + * DEALLOCATE. + */ + if (entry->have_prep_stmt && entry->have_error) + { + res = PQexec(entry->conn, "DEALLOCATE ALL"); + PQclear(res); + } + entry->have_prep_stmt = false; + entry->have_error = false; break; case XACT_EVENT_PRE_PREPARE: @@ -502,6 +551,8 @@ pgfdw_xact_callback(XactEvent event, void *arg) elog(ERROR, "missed cleaning up connection during pre-commit"); break; case XACT_EVENT_ABORT: + /* Assume we might have lost track of prepared statements */ + entry->have_error = true; /* If we're aborting, abort all remote transactions too */ res = PQexec(entry->conn, "ABORT TRANSACTION"); /* Note: can't throw ERROR, it would be infinite loop */ @@ -509,7 +560,17 @@ pgfdw_xact_callback(XactEvent event, void *arg) pgfdw_report_error(WARNING, res, true, "ABORT TRANSACTION"); else + { PQclear(res); + /* As above, make sure we've cleared any prepared stmts */ + if (entry->have_prep_stmt && entry->have_error) + { + res = PQexec(entry->conn, "DEALLOCATE ALL"); + PQclear(res); + } + entry->have_prep_stmt = false; + entry->have_error = false; + } break; } @@ -593,6 +654,8 @@ pgfdw_subxact_callback(SubXactEvent event, SubTransactionId mySubid, } else { + /* Assume we might have lost track of prepared statements */ + entry->have_error = true; /* Rollback all remote subtransactions during abort */ snprintf(sql, sizeof(sql), "ROLLBACK TO SAVEPOINT s%d; RELEASE SAVEPOINT s%d", diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index 9816f550ca5..d667c997609 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -25,6 +25,7 @@ #include "postgres_fdw.h" +#include "access/heapam.h" #include "access/htup_details.h" #include "access/sysattr.h" #include "access/transam.h" @@ -66,6 +67,14 @@ static bool is_builtin(Oid procid); /* * Functions to construct string representation of a node tree. */ +static void deparseTargetList(StringInfo buf, + PlannerInfo *root, + Index rtindex, + Relation rel, + Bitmapset *attrs_used); +static void deparseReturningList(StringInfo buf, PlannerInfo *root, + Index rtindex, Relation rel, + List *returningList); static void deparseColumnRef(StringInfo buf, int varno, int varattno, PlannerInfo *root); static void deparseRelation(StringInfo buf, Oid relid); @@ -349,80 +358,104 @@ is_builtin(Oid oid) /* - * Construct a simple SELECT statement that retrieves interesting columns + * Construct a simple SELECT statement that retrieves desired columns * of the specified foreign table, and append it to "buf". The output * contains just "SELECT ... FROM tablename". - * - * "Interesting" columns are those appearing in the rel's targetlist or - * in local_conds (conditions which can't be executed remotely). */ void -deparseSimpleSql(StringInfo buf, +deparseSelectSql(StringInfo buf, PlannerInfo *root, RelOptInfo *baserel, - List *local_conds) + Bitmapset *attrs_used) { - RangeTblEntry *rte = root->simple_rte_array[baserel->relid]; - Bitmapset *attrs_used = NULL; - bool have_wholerow; - bool first; - AttrNumber attr; - ListCell *lc; + RangeTblEntry *rte = planner_rt_fetch(baserel->relid, root); + Relation rel; - /* Collect all the attributes needed for joins or final output. */ - pull_varattnos((Node *) baserel->reltargetlist, baserel->relid, - &attrs_used); + /* + * Core code already has some lock on each rel being planned, so we can + * use NoLock here. + */ + rel = heap_open(rte->relid, NoLock); - /* Add all the attributes used by local_conds. */ - foreach(lc, local_conds) - { - RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc); + /* + * Construct SELECT list + */ + appendStringInfoString(buf, "SELECT "); + deparseTargetList(buf, root, baserel->relid, rel, attrs_used); - pull_varattnos((Node *) rinfo->clause, baserel->relid, - &attrs_used); - } + /* + * Construct FROM clause + */ + appendStringInfoString(buf, " FROM "); + deparseRelation(buf, RelationGetRelid(rel)); + + heap_close(rel, NoLock); +} + +/* + * Emit a target list that retrieves the columns specified in attrs_used. + * This is used for both SELECT and RETURNING targetlists. + * + * We list attributes in order of the foreign table's columns, but replace + * any attributes that need not be fetched with NULL constants. (We can't + * just omit such attributes, or we'll lose track of which columns are + * which at runtime.) Note however that any dropped columns are ignored. + * Also, if ctid needs to be retrieved, it's added at the end. + */ +static void +deparseTargetList(StringInfo buf, + PlannerInfo *root, + Index rtindex, + Relation rel, + Bitmapset *attrs_used) +{ + TupleDesc tupdesc = RelationGetDescr(rel); + bool have_wholerow; + bool first; + int i; /* If there's a whole-row reference, we'll need all the columns. */ have_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, attrs_used); - /* - * Construct SELECT list - * - * We list attributes in order of the foreign table's columns, but replace - * any attributes that need not be fetched with NULL constants. (We can't - * just omit such attributes, or we'll lose track of which columns are - * which at runtime.) Note however that any dropped columns are ignored. - */ - appendStringInfo(buf, "SELECT "); first = true; - for (attr = 1; attr <= baserel->max_attr; attr++) + for (i = 1; i <= tupdesc->natts; i++) { + Form_pg_attribute attr = tupdesc->attrs[i - 1]; + /* Ignore dropped attributes. */ - if (get_rte_attribute_is_dropped(rte, attr)) + if (attr->attisdropped) continue; if (!first) - appendStringInfo(buf, ", "); + appendStringInfoString(buf, ", "); first = false; if (have_wholerow || - bms_is_member(attr - FirstLowInvalidHeapAttributeNumber, + bms_is_member(i - FirstLowInvalidHeapAttributeNumber, attrs_used)) - deparseColumnRef(buf, baserel->relid, attr, root); + deparseColumnRef(buf, rtindex, i, root); else - appendStringInfo(buf, "NULL"); + appendStringInfoString(buf, "NULL"); } - /* Don't generate bad syntax if no undropped columns */ - if (first) - appendStringInfo(buf, "NULL"); - /* - * Construct FROM clause + * Add ctid if needed. We currently don't support retrieving any other + * system columns. */ - appendStringInfo(buf, " FROM "); - deparseRelation(buf, rte->relid); + if (bms_is_member(SelfItemPointerAttributeNumber - FirstLowInvalidHeapAttributeNumber, + attrs_used)) + { + if (!first) + appendStringInfoString(buf, ", "); + first = false; + + appendStringInfoString(buf, "ctid"); + } + + /* Don't generate bad syntax if no undropped columns */ + if (first) + appendStringInfoString(buf, "NULL"); } /* @@ -432,9 +465,9 @@ deparseSimpleSql(StringInfo buf, */ void appendWhereClause(StringInfo buf, - bool is_first, + PlannerInfo *root, List *exprs, - PlannerInfo *root) + bool is_first) { ListCell *lc; @@ -444,9 +477,9 @@ appendWhereClause(StringInfo buf, /* Connect expressions with "AND" and parenthesize each condition. */ if (is_first) - appendStringInfo(buf, " WHERE "); + appendStringInfoString(buf, " WHERE "); else - appendStringInfo(buf, " AND "); + appendStringInfoString(buf, " AND "); appendStringInfoChar(buf, '('); deparseExpr(buf, ri->clause, root); @@ -457,6 +490,147 @@ appendWhereClause(StringInfo buf, } /* + * deparse remote INSERT statement + */ +void +deparseInsertSql(StringInfo buf, PlannerInfo *root, Index rtindex, + List *targetAttrs, List *returningList) +{ + RangeTblEntry *rte = planner_rt_fetch(rtindex, root); + Relation rel = heap_open(rte->relid, NoLock); + TupleDesc tupdesc = RelationGetDescr(rel); + AttrNumber pindex; + bool first; + ListCell *lc; + + appendStringInfoString(buf, "INSERT INTO "); + deparseRelation(buf, rte->relid); + appendStringInfoString(buf, "("); + + first = true; + foreach(lc, targetAttrs) + { + int attnum = lfirst_int(lc); + Form_pg_attribute attr = tupdesc->attrs[attnum - 1]; + + Assert(!attr->attisdropped); + + if (!first) + appendStringInfoString(buf, ", "); + first = false; + + deparseColumnRef(buf, rtindex, attnum, root); + } + + appendStringInfoString(buf, ") VALUES ("); + + pindex = 1; + first = true; + foreach(lc, targetAttrs) + { + if (!first) + appendStringInfoString(buf, ", "); + first = false; + + appendStringInfo(buf, "$%d", pindex); + pindex++; + } + + appendStringInfoString(buf, ")"); + + if (returningList) + deparseReturningList(buf, root, rtindex, rel, returningList); + + heap_close(rel, NoLock); +} + +/* + * deparse remote UPDATE statement + */ +void +deparseUpdateSql(StringInfo buf, PlannerInfo *root, Index rtindex, + List *targetAttrs, List *returningList) +{ + RangeTblEntry *rte = planner_rt_fetch(rtindex, root); + Relation rel = heap_open(rte->relid, NoLock); + TupleDesc tupdesc = RelationGetDescr(rel); + AttrNumber pindex; + bool first; + ListCell *lc; + + appendStringInfoString(buf, "UPDATE "); + deparseRelation(buf, rte->relid); + appendStringInfoString(buf, " SET "); + + pindex = 2; /* ctid is always the first param */ + first = true; + foreach(lc, targetAttrs) + { + int attnum = lfirst_int(lc); + Form_pg_attribute attr = tupdesc->attrs[attnum - 1]; + + Assert(!attr->attisdropped); + + if (!first) + appendStringInfoString(buf, ", "); + first = false; + + deparseColumnRef(buf, rtindex, attnum, root); + appendStringInfo(buf, " = $%d", pindex); + pindex++; + } + appendStringInfoString(buf, " WHERE ctid = $1"); + + if (returningList) + deparseReturningList(buf, root, rtindex, rel, returningList); + + heap_close(rel, NoLock); +} + +/* + * deparse remote DELETE statement + */ +void +deparseDeleteSql(StringInfo buf, PlannerInfo *root, Index rtindex, + List *returningList) +{ + RangeTblEntry *rte = planner_rt_fetch(rtindex, root); + + appendStringInfoString(buf, "DELETE FROM "); + deparseRelation(buf, rte->relid); + appendStringInfoString(buf, " WHERE ctid = $1"); + + if (returningList) + { + Relation rel = heap_open(rte->relid, NoLock); + + deparseReturningList(buf, root, rtindex, rel, returningList); + heap_close(rel, NoLock); + } +} + +/* + * deparse RETURNING clause of INSERT/UPDATE/DELETE + */ +static void +deparseReturningList(StringInfo buf, PlannerInfo *root, + Index rtindex, Relation rel, + List *returningList) +{ + Bitmapset *attrs_used; + + /* + * We need the attrs mentioned in the query's RETURNING list. + */ + attrs_used = NULL; + pull_varattnos((Node *) returningList, rtindex, + &attrs_used); + + appendStringInfoString(buf, " RETURNING "); + deparseTargetList(buf, root, rtindex, rel, attrs_used); +} + +/* * Construct SELECT statement to acquire size in blocks of given relation. * * Note: we use local definition of block size, not remote definition. @@ -495,13 +669,17 @@ deparseAnalyzeSql(StringInfo buf, Relation rel) ListCell *lc; bool first = true; - appendStringInfo(buf, "SELECT "); + appendStringInfoString(buf, "SELECT "); for (i = 0; i < tupdesc->natts; i++) { /* Ignore dropped columns. */ if (tupdesc->attrs[i]->attisdropped) continue; + if (!first) + appendStringInfoString(buf, ", "); + first = false; + /* Use attribute name or column_name option. */ colname = NameStr(tupdesc->attrs[i]->attname); options = GetForeignColumnOptions(relid, i + 1); @@ -517,20 +695,17 @@ deparseAnalyzeSql(StringInfo buf, Relation rel) } } - if (!first) - appendStringInfo(buf, ", "); appendStringInfoString(buf, quote_identifier(colname)); - first = false; } /* Don't generate bad syntax for zero-column relation. */ if (first) - appendStringInfo(buf, "NULL"); + appendStringInfoString(buf, "NULL"); /* * Construct FROM clause */ - appendStringInfo(buf, " FROM "); + appendStringInfoString(buf, " FROM "); deparseRelation(buf, relid); } @@ -547,10 +722,10 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, PlannerInfo *root) ListCell *lc; /* varno must not be any of OUTER_VAR, INNER_VAR and INDEX_VAR. */ - Assert(varno >= 1 && varno <= root->simple_rel_array_size); + Assert(!IS_SPECIAL_VARNO(varno)); /* Get RangeTblEntry from array in PlannerInfo. */ - rte = root->simple_rte_array[varno]; + rte = planner_rt_fetch(varno, root); /* * If it's a column of a foreign table, and it has the column_name FDW @@ -608,8 +783,8 @@ deparseRelation(StringInfo buf, Oid relid) } /* - * Note: we could skip printing the schema name if it's pg_catalog, - * but that doesn't seem worth the trouble. + * Note: we could skip printing the schema name if it's pg_catalog, but + * that doesn't seem worth the trouble. */ if (nspname == NULL) nspname = get_namespace_name(get_rel_namespace(relid)); @@ -1059,7 +1234,7 @@ deparseDistinctExpr(StringInfo buf, DistinctExpr *node, PlannerInfo *root) appendStringInfoChar(buf, '('); deparseExpr(buf, linitial(node->args), root); - appendStringInfo(buf, " IS DISTINCT FROM "); + appendStringInfoString(buf, " IS DISTINCT FROM "); deparseExpr(buf, lsecond(node->args), root); appendStringInfoChar(buf, ')'); } @@ -1146,7 +1321,7 @@ deparseBoolExpr(StringInfo buf, BoolExpr *node, PlannerInfo *root) op = "OR"; break; case NOT_EXPR: - appendStringInfo(buf, "(NOT "); + appendStringInfoString(buf, "(NOT "); deparseExpr(buf, linitial(node->args), root); appendStringInfoChar(buf, ')'); return; @@ -1173,9 +1348,9 @@ deparseNullTest(StringInfo buf, NullTest *node, PlannerInfo *root) appendStringInfoChar(buf, '('); deparseExpr(buf, node->arg, root); if (node->nulltesttype == IS_NULL) - appendStringInfo(buf, " IS NULL)"); + appendStringInfoString(buf, " IS NULL)"); else - appendStringInfo(buf, " IS NOT NULL)"); + appendStringInfoString(buf, " IS NOT NULL)"); } /* @@ -1187,11 +1362,11 @@ deparseArrayExpr(StringInfo buf, ArrayExpr *node, PlannerInfo *root) bool first = true; ListCell *lc; - appendStringInfo(buf, "ARRAY["); + appendStringInfoString(buf, "ARRAY["); foreach(lc, node->elements) { if (!first) - appendStringInfo(buf, ", "); + appendStringInfoString(buf, ", "); deparseExpr(buf, lfirst(lc), root); first = false; } diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 07c7970dc7b..21429ca1661 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -731,3 +731,1512 @@ SELECT * FROM ft1 ORDER BY c1 LIMIT 1; (1 row) COMMIT; +-- =================================================================== +-- test writable foreign table stuff +-- =================================================================== +EXPLAIN (verbose, costs off) +INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Insert on public.ft2 + Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3) VALUES ($1, $2, $3) + -> Subquery Scan on "*SELECT*" + Output: NULL::integer, "*SELECT*"."?column?", "*SELECT*"."?column?_1", "*SELECT*"."?column?_2", NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying, NULL::bpchar, NULL::user_enum + -> Limit + Output: ((ft2_1.c1 + 1000)), ((ft2_1.c2 + 100)), ((ft2_1.c3 || ft2_1.c3)) + -> Foreign Scan on public.ft2 ft2_1 + Output: (ft2_1.c1 + 1000), (ft2_1.c2 + 100), (ft2_1.c3 || ft2_1.c3) + Remote SQL: SELECT "C 1", c2, c3, NULL, NULL, NULL, NULL, NULL FROM "S 1"."T 1" +(9 rows) + +INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; +INSERT INTO ft2 (c1,c2,c3) + VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING *; + c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 +------+-----+-----+----+----+----+----+---- + 1101 | 201 | aaa | | | | | + 1102 | 202 | bbb | | | | | + 1103 | 203 | ccc | | | | | +(3 rows) + +INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee'); +UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3; +UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *; + c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 +------+-----+--------------------+------------------------------+--------------------------+----+------------+----- + 7 | 407 | 00007_update7 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo + 17 | 407 | 00017_update7 | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7 | 7 | foo + 27 | 407 | 00027_update7 | Wed Jan 28 00:00:00 1970 PST | Wed Jan 28 00:00:00 1970 | 7 | 7 | foo + 37 | 407 | 00037_update7 | Sat Feb 07 00:00:00 1970 PST | Sat Feb 07 00:00:00 1970 | 7 | 7 | foo + 47 | 407 | 00047_update7 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo + 57 | 407 | 00057_update7 | Fri Feb 27 00:00:00 1970 PST | Fri Feb 27 00:00:00 1970 | 7 | 7 | foo + 67 | 407 | 00067_update7 | Mon Mar 09 00:00:00 1970 PST | Mon Mar 09 00:00:00 1970 | 7 | 7 | foo + 77 | 407 | 00077_update7 | Thu Mar 19 00:00:00 1970 PST | Thu Mar 19 00:00:00 1970 | 7 | 7 | foo + 87 | 407 | 00087_update7 | Sun Mar 29 00:00:00 1970 PST | Sun Mar 29 00:00:00 1970 | 7 | 7 | foo + 97 | 407 | 00097_update7 | Wed Apr 08 00:00:00 1970 PST | Wed Apr 08 00:00:00 1970 | 7 | 7 | foo + 107 | 407 | 00107_update7 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo + 117 | 407 | 00117_update7 | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7 | 7 | foo + 127 | 407 | 00127_update7 | Wed Jan 28 00:00:00 1970 PST | Wed Jan 28 00:00:00 1970 | 7 | 7 | foo + 137 | 407 | 00137_update7 | Sat Feb 07 00:00:00 1970 PST | Sat Feb 07 00:00:00 1970 | 7 | 7 | foo + 147 | 407 | 00147_update7 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo + 157 | 407 | 00157_update7 | Fri Feb 27 00:00:00 1970 PST | Fri Feb 27 00:00:00 1970 | 7 | 7 | foo + 167 | 407 | 00167_update7 | Mon Mar 09 00:00:00 1970 PST | Mon Mar 09 00:00:00 1970 | 7 | 7 | foo + 177 | 407 | 00177_update7 | Thu Mar 19 00:00:00 1970 PST | Thu Mar 19 00:00:00 1970 | 7 | 7 | foo + 187 | 407 | 00187_update7 | Sun Mar 29 00:00:00 1970 PST | Sun Mar 29 00:00:00 1970 | 7 | 7 | foo + 197 | 407 | 00197_update7 | Wed Apr 08 00:00:00 1970 PST | Wed Apr 08 00:00:00 1970 | 7 | 7 | foo + 207 | 407 | 00207_update7 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo + 217 | 407 | 00217_update7 | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7 | 7 | foo + 227 | 407 | 00227_update7 | Wed Jan 28 00:00:00 1970 PST | Wed Jan 28 00:00:00 1970 | 7 | 7 | foo + 237 | 407 | 00237_update7 | Sat Feb 07 00:00:00 1970 PST | Sat Feb 07 00:00:00 1970 | 7 | 7 | foo + 247 | 407 | 00247_update7 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo + 257 | 407 | 00257_update7 | Fri Feb 27 00:00:00 1970 PST | Fri Feb 27 00:00:00 1970 | 7 | 7 | foo + 267 | 407 | 00267_update7 | Mon Mar 09 00:00:00 1970 PST | Mon Mar 09 00:00:00 1970 | 7 | 7 | foo + 277 | 407 | 00277_update7 | Thu Mar 19 00:00:00 1970 PST | Thu Mar 19 00:00:00 1970 | 7 | 7 | foo + 287 | 407 | 00287_update7 | Sun Mar 29 00:00:00 1970 PST | Sun Mar 29 00:00:00 1970 | 7 | 7 | foo + 297 | 407 | 00297_update7 | Wed Apr 08 00:00:00 1970 PST | Wed Apr 08 00:00:00 1970 | 7 | 7 | foo + 307 | 407 | 00307_update7 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo + 317 | 407 | 00317_update7 | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7 | 7 | foo + 327 | 407 | 00327_update7 | Wed Jan 28 00:00:00 1970 PST | Wed Jan 28 00:00:00 1970 | 7 | 7 | foo + 337 | 407 | 00337_update7 | Sat Feb 07 00:00:00 1970 PST | Sat Feb 07 00:00:00 1970 | 7 | 7 | foo + 347 | 407 | 00347_update7 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo + 357 | 407 | 00357_update7 | Fri Feb 27 00:00:00 1970 PST | Fri Feb 27 00:00:00 1970 | 7 | 7 | foo + 367 | 407 | 00367_update7 | Mon Mar 09 00:00:00 1970 PST | Mon Mar 09 00:00:00 1970 | 7 | 7 | foo + 377 | 407 | 00377_update7 | Thu Mar 19 00:00:00 1970 PST | Thu Mar 19 00:00:00 1970 | 7 | 7 | foo + 387 | 407 | 00387_update7 | Sun Mar 29 00:00:00 1970 PST | Sun Mar 29 00:00:00 1970 | 7 | 7 | foo + 397 | 407 | 00397_update7 | Wed Apr 08 00:00:00 1970 PST | Wed Apr 08 00:00:00 1970 | 7 | 7 | foo + 407 | 407 | 00407_update7 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo + 417 | 407 | 00417_update7 | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7 | 7 | foo + 427 | 407 | 00427_update7 | Wed Jan 28 00:00:00 1970 PST | Wed Jan 28 00:00:00 1970 | 7 | 7 | foo + 437 | 407 | 00437_update7 | Sat Feb 07 00:00:00 1970 PST | Sat Feb 07 00:00:00 1970 | 7 | 7 | foo + 447 | 407 | 00447_update7 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo + 457 | 407 | 00457_update7 | Fri Feb 27 00:00:00 1970 PST | Fri Feb 27 00:00:00 1970 | 7 | 7 | foo + 467 | 407 | 00467_update7 | Mon Mar 09 00:00:00 1970 PST | Mon Mar 09 00:00:00 1970 | 7 | 7 | foo + 477 | 407 | 00477_update7 | Thu Mar 19 00:00:00 1970 PST | Thu Mar 19 00:00:00 1970 | 7 | 7 | foo + 487 | 407 | 00487_update7 | Sun Mar 29 00:00:00 1970 PST | Sun Mar 29 00:00:00 1970 | 7 | 7 | foo + 497 | 407 | 00497_update7 | Wed Apr 08 00:00:00 1970 PST | Wed Apr 08 00:00:00 1970 | 7 | 7 | foo + 507 | 407 | 00507_update7 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo + 517 | 407 | 00517_update7 | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7 | 7 | foo + 527 | 407 | 00527_update7 | Wed Jan 28 00:00:00 1970 PST | Wed Jan 28 00:00:00 1970 | 7 | 7 | foo + 537 | 407 | 00537_update7 | Sat Feb 07 00:00:00 1970 PST | Sat Feb 07 00:00:00 1970 | 7 | 7 | foo + 547 | 407 | 00547_update7 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo + 557 | 407 | 00557_update7 | Fri Feb 27 00:00:00 1970 PST | Fri Feb 27 00:00:00 1970 | 7 | 7 | foo + 567 | 407 | 00567_update7 | Mon Mar 09 00:00:00 1970 PST | Mon Mar 09 00:00:00 1970 | 7 | 7 | foo + 577 | 407 | 00577_update7 | Thu Mar 19 00:00:00 1970 PST | Thu Mar 19 00:00:00 1970 | 7 | 7 | foo + 587 | 407 | 00587_update7 | Sun Mar 29 00:00:00 1970 PST | Sun Mar 29 00:00:00 1970 | 7 | 7 | foo + 597 | 407 | 00597_update7 | Wed Apr 08 00:00:00 1970 PST | Wed Apr 08 00:00:00 1970 | 7 | 7 | foo + 607 | 407 | 00607_update7 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo + 617 | 407 | 00617_update7 | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7 | 7 | foo + 627 | 407 | 00627_update7 | Wed Jan 28 00:00:00 1970 PST | Wed Jan 28 00:00:00 1970 | 7 | 7 | foo + 637 | 407 | 00637_update7 | Sat Feb 07 00:00:00 1970 PST | Sat Feb 07 00:00:00 1970 | 7 | 7 | foo + 647 | 407 | 00647_update7 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo + 657 | 407 | 00657_update7 | Fri Feb 27 00:00:00 1970 PST | Fri Feb 27 00:00:00 1970 | 7 | 7 | foo + 667 | 407 | 00667_update7 | Mon Mar 09 00:00:00 1970 PST | Mon Mar 09 00:00:00 1970 | 7 | 7 | foo + 677 | 407 | 00677_update7 | Thu Mar 19 00:00:00 1970 PST | Thu Mar 19 00:00:00 1970 | 7 | 7 | foo + 687 | 407 | 00687_update7 | Sun Mar 29 00:00:00 1970 PST | Sun Mar 29 00:00:00 1970 | 7 | 7 | foo + 697 | 407 | 00697_update7 | Wed Apr 08 00:00:00 1970 PST | Wed Apr 08 00:00:00 1970 | 7 | 7 | foo + 707 | 407 | 00707_update7 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo + 717 | 407 | 00717_update7 | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7 | 7 | foo + 727 | 407 | 00727_update7 | Wed Jan 28 00:00:00 1970 PST | Wed Jan 28 00:00:00 1970 | 7 | 7 | foo + 737 | 407 | 00737_update7 | Sat Feb 07 00:00:00 1970 PST | Sat Feb 07 00:00:00 1970 | 7 | 7 | foo + 747 | 407 | 00747_update7 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo + 757 | 407 | 00757_update7 | Fri Feb 27 00:00:00 1970 PST | Fri Feb 27 00:00:00 1970 | 7 | 7 | foo + 767 | 407 | 00767_update7 | Mon Mar 09 00:00:00 1970 PST | Mon Mar 09 00:00:00 1970 | 7 | 7 | foo + 777 | 407 | 00777_update7 | Thu Mar 19 00:00:00 1970 PST | Thu Mar 19 00:00:00 1970 | 7 | 7 | foo + 787 | 407 | 00787_update7 | Sun Mar 29 00:00:00 1970 PST | Sun Mar 29 00:00:00 1970 | 7 | 7 | foo + 797 | 407 | 00797_update7 | Wed Apr 08 00:00:00 1970 PST | Wed Apr 08 00:00:00 1970 | 7 | 7 | foo + 807 | 407 | 00807_update7 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo + 817 | 407 | 00817_update7 | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7 | 7 | foo + 827 | 407 | 00827_update7 | Wed Jan 28 00:00:00 1970 PST | Wed Jan 28 00:00:00 1970 | 7 | 7 | foo + 837 | 407 | 00837_update7 | Sat Feb 07 00:00:00 1970 PST | Sat Feb 07 00:00:00 1970 | 7 | 7 | foo + 847 | 407 | 00847_update7 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo + 857 | 407 | 00857_update7 | Fri Feb 27 00:00:00 1970 PST | Fri Feb 27 00:00:00 1970 | 7 | 7 | foo + 867 | 407 | 00867_update7 | Mon Mar 09 00:00:00 1970 PST | Mon Mar 09 00:00:00 1970 | 7 | 7 | foo + 877 | 407 | 00877_update7 | Thu Mar 19 00:00:00 1970 PST | Thu Mar 19 00:00:00 1970 | 7 | 7 | foo + 887 | 407 | 00887_update7 | Sun Mar 29 00:00:00 1970 PST | Sun Mar 29 00:00:00 1970 | 7 | 7 | foo + 897 | 407 | 00897_update7 | Wed Apr 08 00:00:00 1970 PST | Wed Apr 08 00:00:00 1970 | 7 | 7 | foo + 907 | 407 | 00907_update7 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo + 917 | 407 | 00917_update7 | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7 | 7 | foo + 927 | 407 | 00927_update7 | Wed Jan 28 00:00:00 1970 PST | Wed Jan 28 00:00:00 1970 | 7 | 7 | foo + 937 | 407 | 00937_update7 | Sat Feb 07 00:00:00 1970 PST | Sat Feb 07 00:00:00 1970 | 7 | 7 | foo + 947 | 407 | 00947_update7 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo + 957 | 407 | 00957_update7 | Fri Feb 27 00:00:00 1970 PST | Fri Feb 27 00:00:00 1970 | 7 | 7 | foo + 967 | 407 | 00967_update7 | Mon Mar 09 00:00:00 1970 PST | Mon Mar 09 00:00:00 1970 | 7 | 7 | foo + 977 | 407 | 00977_update7 | Thu Mar 19 00:00:00 1970 PST | Thu Mar 19 00:00:00 1970 | 7 | 7 | foo + 987 | 407 | 00987_update7 | Sun Mar 29 00:00:00 1970 PST | Sun Mar 29 00:00:00 1970 | 7 | 7 | foo + 997 | 407 | 00997_update7 | Wed Apr 08 00:00:00 1970 PST | Wed Apr 08 00:00:00 1970 | 7 | 7 | foo + 1007 | 507 | 0000700007_update7 | | | | | + 1017 | 507 | 0001700017_update7 | | | | | +(102 rows) + +EXPLAIN (verbose, costs off) +UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9' + FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------- + Update on public.ft2 + Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c3 = $3 WHERE ctid = $1 + -> Hash Join + Output: NULL::integer, ft2.c1, (ft2.c2 + 500), (ft2.c3 || '_update9'::text), ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft1.* + Hash Cond: (ft2.c2 = ft1.c1) + -> Foreign Scan on public.ft2 + Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" FOR UPDATE + -> Hash + Output: ft1.*, ft1.c1 + -> Foreign Scan on public.ft1 + Output: ft1.*, ft1.c1 + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 9)) +(13 rows) + +UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9' + FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; +DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING *; + c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 +------+-----+------------+------------------------------+--------------------------+----+------------+----- + 5 | 5 | 00005 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo + 15 | 5 | 00015 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo + 25 | 5 | 00025 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo + 35 | 5 | 00035 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo + 45 | 5 | 00045 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo + 55 | 5 | 00055 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo + 65 | 5 | 00065 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo + 75 | 5 | 00075 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo + 85 | 5 | 00085 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo + 95 | 5 | 00095 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo + 105 | 5 | 00105 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo + 115 | 5 | 00115 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo + 125 | 5 | 00125 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo + 135 | 5 | 00135 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo + 145 | 5 | 00145 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo + 155 | 5 | 00155 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo + 165 | 5 | 00165 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo + 175 | 5 | 00175 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo + 185 | 5 | 00185 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo + 195 | 5 | 00195 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo + 205 | 5 | 00205 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo + 215 | 5 | 00215 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo + 225 | 5 | 00225 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo + 235 | 5 | 00235 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo + 245 | 5 | 00245 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo + 255 | 5 | 00255 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo + 265 | 5 | 00265 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo + 275 | 5 | 00275 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo + 285 | 5 | 00285 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo + 295 | 5 | 00295 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo + 305 | 5 | 00305 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo + 315 | 5 | 00315 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo + 325 | 5 | 00325 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo + 335 | 5 | 00335 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo + 345 | 5 | 00345 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo + 355 | 5 | 00355 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo + 365 | 5 | 00365 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo + 375 | 5 | 00375 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo + 385 | 5 | 00385 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo + 395 | 5 | 00395 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo + 405 | 5 | 00405 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo + 415 | 5 | 00415 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo + 425 | 5 | 00425 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo + 435 | 5 | 00435 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo + 445 | 5 | 00445 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo + 455 | 5 | 00455 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo + 465 | 5 | 00465 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo + 475 | 5 | 00475 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo + 485 | 5 | 00485 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo + 495 | 5 | 00495 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo + 505 | 5 | 00505 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo + 515 | 5 | 00515 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo + 525 | 5 | 00525 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo + 535 | 5 | 00535 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo + 545 | 5 | 00545 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo + 555 | 5 | 00555 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo + 565 | 5 | 00565 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo + 575 | 5 | 00575 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo + 585 | 5 | 00585 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo + 595 | 5 | 00595 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo + 605 | 5 | 00605 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo + 615 | 5 | 00615 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo + 625 | 5 | 00625 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo + 635 | 5 | 00635 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo + 645 | 5 | 00645 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo + 655 | 5 | 00655 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo + 665 | 5 | 00665 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo + 675 | 5 | 00675 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo + 685 | 5 | 00685 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo + 695 | 5 | 00695 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo + 705 | 5 | 00705 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo + 715 | 5 | 00715 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo + 725 | 5 | 00725 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo + 735 | 5 | 00735 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo + 745 | 5 | 00745 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo + 755 | 5 | 00755 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo + 765 | 5 | 00765 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo + 775 | 5 | 00775 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo + 785 | 5 | 00785 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo + 795 | 5 | 00795 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo + 805 | 5 | 00805 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo + 815 | 5 | 00815 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo + 825 | 5 | 00825 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo + 835 | 5 | 00835 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo + 845 | 5 | 00845 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo + 855 | 5 | 00855 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo + 865 | 5 | 00865 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo + 875 | 5 | 00875 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo + 885 | 5 | 00885 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo + 895 | 5 | 00895 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo + 905 | 5 | 00905 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo + 915 | 5 | 00915 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo + 925 | 5 | 00925 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo + 935 | 5 | 00935 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo + 945 | 5 | 00945 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo + 955 | 5 | 00955 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo + 965 | 5 | 00965 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo + 975 | 5 | 00975 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo + 985 | 5 | 00985 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo + 995 | 5 | 00995 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo + 1005 | 105 | 0000500005 | | | | | + 1015 | 105 | 0001500015 | | | | | + 1105 | 205 | eee | | | | | +(103 rows) + +EXPLAIN (verbose, costs off) +DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------- + Delete on public.ft2 + Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1 + -> Hash Join + Output: ft2.ctid, ft1.* + Hash Cond: (ft2.c2 = ft1.c1) + -> Foreign Scan on public.ft2 + Output: ft2.ctid, ft2.c2 + Remote SQL: SELECT NULL, c2, NULL, NULL, NULL, NULL, NULL, NULL, ctid FROM "S 1"."T 1" FOR UPDATE + -> Hash + Output: ft1.*, ft1.c1 + -> Foreign Scan on public.ft1 + Output: ft1.*, ft1.c1 + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 2)) +(13 rows) + +DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; +SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1; + c1 | c2 | c3 | c4 +------+-----+--------------------+------------------------------ + 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST + 3 | 303 | 00003_update3 | Sun Jan 04 00:00:00 1970 PST + 4 | 4 | 00004 | Mon Jan 05 00:00:00 1970 PST + 6 | 6 | 00006 | Wed Jan 07 00:00:00 1970 PST + 7 | 407 | 00007_update7 | Thu Jan 08 00:00:00 1970 PST + 8 | 8 | 00008 | Fri Jan 09 00:00:00 1970 PST + 9 | 509 | 00009_update9 | Sat Jan 10 00:00:00 1970 PST + 10 | 0 | 00010 | Sun Jan 11 00:00:00 1970 PST + 11 | 1 | 00011 | Mon Jan 12 00:00:00 1970 PST + 13 | 303 | 00013_update3 | Wed Jan 14 00:00:00 1970 PST + 14 | 4 | 00014 | Thu Jan 15 00:00:00 1970 PST + 16 | 6 | 00016 | Sat Jan 17 00:00:00 1970 PST + 17 | 407 | 00017_update7 | Sun Jan 18 00:00:00 1970 PST + 18 | 8 | 00018 | Mon Jan 19 00:00:00 1970 PST + 19 | 509 | 00019_update9 | Tue Jan 20 00:00:00 1970 PST + 20 | 0 | 00020 | Wed Jan 21 00:00:00 1970 PST + 21 | 1 | 00021 | Thu Jan 22 00:00:00 1970 PST + 23 | 303 | 00023_update3 | Sat Jan 24 00:00:00 1970 PST + 24 | 4 | 00024 | Sun Jan 25 00:00:00 1970 PST + 26 | 6 | 00026 | Tue Jan 27 00:00:00 1970 PST + 27 | 407 | 00027_update7 | Wed Jan 28 00:00:00 1970 PST + 28 | 8 | 00028 | Thu Jan 29 00:00:00 1970 PST + 29 | 509 | 00029_update9 | Fri Jan 30 00:00:00 1970 PST + 30 | 0 | 00030 | Sat Jan 31 00:00:00 1970 PST + 31 | 1 | 00031 | Sun Feb 01 00:00:00 1970 PST + 33 | 303 | 00033_update3 | Tue Feb 03 00:00:00 1970 PST + 34 | 4 | 00034 | Wed Feb 04 00:00:00 1970 PST + 36 | 6 | 00036 | Fri Feb 06 00:00:00 1970 PST + 37 | 407 | 00037_update7 | Sat Feb 07 00:00:00 1970 PST + 38 | 8 | 00038 | Sun Feb 08 00:00:00 1970 PST + 39 | 509 | 00039_update9 | Mon Feb 09 00:00:00 1970 PST + 40 | 0 | 00040 | Tue Feb 10 00:00:00 1970 PST + 41 | 1 | 00041 | Wed Feb 11 00:00:00 1970 PST + 43 | 303 | 00043_update3 | Fri Feb 13 00:00:00 1970 PST + 44 | 4 | 00044 | Sat Feb 14 00:00:00 1970 PST + 46 | 6 | 00046 | Mon Feb 16 00:00:00 1970 PST + 47 | 407 | 00047_update7 | Tue Feb 17 00:00:00 1970 PST + 48 | 8 | 00048 | Wed Feb 18 00:00:00 1970 PST + 49 | 509 | 00049_update9 | Thu Feb 19 00:00:00 1970 PST + 50 | 0 | 00050 | Fri Feb 20 00:00:00 1970 PST + 51 | 1 | 00051 | Sat Feb 21 00:00:00 1970 PST + 53 | 303 | 00053_update3 | Mon Feb 23 00:00:00 1970 PST + 54 | 4 | 00054 | Tue Feb 24 00:00:00 1970 PST + 56 | 6 | 00056 | Thu Feb 26 00:00:00 1970 PST + 57 | 407 | 00057_update7 | Fri Feb 27 00:00:00 1970 PST + 58 | 8 | 00058 | Sat Feb 28 00:00:00 1970 PST + 59 | 509 | 00059_update9 | Sun Mar 01 00:00:00 1970 PST + 60 | 0 | 00060 | Mon Mar 02 00:00:00 1970 PST + 61 | 1 | 00061 | Tue Mar 03 00:00:00 1970 PST + 63 | 303 | 00063_update3 | Thu Mar 05 00:00:00 1970 PST + 64 | 4 | 00064 | Fri Mar 06 00:00:00 1970 PST + 66 | 6 | 00066 | Sun Mar 08 00:00:00 1970 PST + 67 | 407 | 00067_update7 | Mon Mar 09 00:00:00 1970 PST + 68 | 8 | 00068 | Tue Mar 10 00:00:00 1970 PST + 69 | 509 | 00069_update9 | Wed Mar 11 00:00:00 1970 PST + 70 | 0 | 00070 | Thu Mar 12 00:00:00 1970 PST + 71 | 1 | 00071 | Fri Mar 13 00:00:00 1970 PST + 73 | 303 | 00073_update3 | Sun Mar 15 00:00:00 1970 PST + 74 | 4 | 00074 | Mon Mar 16 00:00:00 1970 PST + 76 | 6 | 00076 | Wed Mar 18 00:00:00 1970 PST + 77 | 407 | 00077_update7 | Thu Mar 19 00:00:00 1970 PST + 78 | 8 | 00078 | Fri Mar 20 00:00:00 1970 PST + 79 | 509 | 00079_update9 | Sat Mar 21 00:00:00 1970 PST + 80 | 0 | 00080 | Sun Mar 22 00:00:00 1970 PST + 81 | 1 | 00081 | Mon Mar 23 00:00:00 1970 PST + 83 | 303 | 00083_update3 | Wed Mar 25 00:00:00 1970 PST + 84 | 4 | 00084 | Thu Mar 26 00:00:00 1970 PST + 86 | 6 | 00086 | Sat Mar 28 00:00:00 1970 PST + 87 | 407 | 00087_update7 | Sun Mar 29 00:00:00 1970 PST + 88 | 8 | 00088 | Mon Mar 30 00:00:00 1970 PST + 89 | 509 | 00089_update9 | Tue Mar 31 00:00:00 1970 PST + 90 | 0 | 00090 | Wed Apr 01 00:00:00 1970 PST + 91 | 1 | 00091 | Thu Apr 02 00:00:00 1970 PST + 93 | 303 | 00093_update3 | Sat Apr 04 00:00:00 1970 PST + 94 | 4 | 00094 | Sun Apr 05 00:00:00 1970 PST + 96 | 6 | 00096 | Tue Apr 07 00:00:00 1970 PST + 97 | 407 | 00097_update7 | Wed Apr 08 00:00:00 1970 PST + 98 | 8 | 00098 | Thu Apr 09 00:00:00 1970 PST + 99 | 509 | 00099_update9 | Fri Apr 10 00:00:00 1970 PST + 100 | 0 | 00100 | Thu Jan 01 00:00:00 1970 PST + 101 | 1 | 00101 | Fri Jan 02 00:00:00 1970 PST + 103 | 303 | 00103_update3 | Sun Jan 04 00:00:00 1970 PST + 104 | 4 | 00104 | Mon Jan 05 00:00:00 1970 PST + 106 | 6 | 00106 | Wed Jan 07 00:00:00 1970 PST + 107 | 407 | 00107_update7 | Thu Jan 08 00:00:00 1970 PST + 108 | 8 | 00108 | Fri Jan 09 00:00:00 1970 PST + 109 | 509 | 00109_update9 | Sat Jan 10 00:00:00 1970 PST + 110 | 0 | 00110 | Sun Jan 11 00:00:00 1970 PST + 111 | 1 | 00111 | Mon Jan 12 00:00:00 1970 PST + 113 | 303 | 00113_update3 | Wed Jan 14 00:00:00 1970 PST + 114 | 4 | 00114 | Thu Jan 15 00:00:00 1970 PST + 116 | 6 | 00116 | Sat Jan 17 00:00:00 1970 PST + 117 | 407 | 00117_update7 | Sun Jan 18 00:00:00 1970 PST + 118 | 8 | 00118 | Mon Jan 19 00:00:00 1970 PST + 119 | 509 | 00119_update9 | Tue Jan 20 00:00:00 1970 PST + 120 | 0 | 00120 | Wed Jan 21 00:00:00 1970 PST + 121 | 1 | 00121 | Thu Jan 22 00:00:00 1970 PST + 123 | 303 | 00123_update3 | Sat Jan 24 00:00:00 1970 PST + 124 | 4 | 00124 | Sun Jan 25 00:00:00 1970 PST + 126 | 6 | 00126 | Tue Jan 27 00:00:00 1970 PST + 127 | 407 | 00127_update7 | Wed Jan 28 00:00:00 1970 PST + 128 | 8 | 00128 | Thu Jan 29 00:00:00 1970 PST + 129 | 509 | 00129_update9 | Fri Jan 30 00:00:00 1970 PST + 130 | 0 | 00130 | Sat Jan 31 00:00:00 1970 PST + 131 | 1 | 00131 | Sun Feb 01 00:00:00 1970 PST + 133 | 303 | 00133_update3 | Tue Feb 03 00:00:00 1970 PST + 134 | 4 | 00134 | Wed Feb 04 00:00:00 1970 PST + 136 | 6 | 00136 | Fri Feb 06 00:00:00 1970 PST + 137 | 407 | 00137_update7 | Sat Feb 07 00:00:00 1970 PST + 138 | 8 | 00138 | Sun Feb 08 00:00:00 1970 PST + 139 | 509 | 00139_update9 | Mon Feb 09 00:00:00 1970 PST + 140 | 0 | 00140 | Tue Feb 10 00:00:00 1970 PST + 141 | 1 | 00141 | Wed Feb 11 00:00:00 1970 PST + 143 | 303 | 00143_update3 | Fri Feb 13 00:00:00 1970 PST + 144 | 4 | 00144 | Sat Feb 14 00:00:00 1970 PST + 146 | 6 | 00146 | Mon Feb 16 00:00:00 1970 PST + 147 | 407 | 00147_update7 | Tue Feb 17 00:00:00 1970 PST + 148 | 8 | 00148 | Wed Feb 18 00:00:00 1970 PST + 149 | 509 | 00149_update9 | Thu Feb 19 00:00:00 1970 PST + 150 | 0 | 00150 | Fri Feb 20 00:00:00 1970 PST + 151 | 1 | 00151 | Sat Feb 21 00:00:00 1970 PST + 153 | 303 | 00153_update3 | Mon Feb 23 00:00:00 1970 PST + 154 | 4 | 00154 | Tue Feb 24 00:00:00 1970 PST + 156 | 6 | 00156 | Thu Feb 26 00:00:00 1970 PST + 157 | 407 | 00157_update7 | Fri Feb 27 00:00:00 1970 PST + 158 | 8 | 00158 | Sat Feb 28 00:00:00 1970 PST + 159 | 509 | 00159_update9 | Sun Mar 01 00:00:00 1970 PST + 160 | 0 | 00160 | Mon Mar 02 00:00:00 1970 PST + 161 | 1 | 00161 | Tue Mar 03 00:00:00 1970 PST + 163 | 303 | 00163_update3 | Thu Mar 05 00:00:00 1970 PST + 164 | 4 | 00164 | Fri Mar 06 00:00:00 1970 PST + 166 | 6 | 00166 | Sun Mar 08 00:00:00 1970 PST + 167 | 407 | 00167_update7 | Mon Mar 09 00:00:00 1970 PST + 168 | 8 | 00168 | Tue Mar 10 00:00:00 1970 PST + 169 | 509 | 00169_update9 | Wed Mar 11 00:00:00 1970 PST + 170 | 0 | 00170 | Thu Mar 12 00:00:00 1970 PST + 171 | 1 | 00171 | Fri Mar 13 00:00:00 1970 PST + 173 | 303 | 00173_update3 | Sun Mar 15 00:00:00 1970 PST + 174 | 4 | 00174 | Mon Mar 16 00:00:00 1970 PST + 176 | 6 | 00176 | Wed Mar 18 00:00:00 1970 PST + 177 | 407 | 00177_update7 | Thu Mar 19 00:00:00 1970 PST + 178 | 8 | 00178 | Fri Mar 20 00:00:00 1970 PST + 179 | 509 | 00179_update9 | Sat Mar 21 00:00:00 1970 PST + 180 | 0 | 00180 | Sun Mar 22 00:00:00 1970 PST + 181 | 1 | 00181 | Mon Mar 23 00:00:00 1970 PST + 183 | 303 | 00183_update3 | Wed Mar 25 00:00:00 1970 PST + 184 | 4 | 00184 | Thu Mar 26 00:00:00 1970 PST + 186 | 6 | 00186 | Sat Mar 28 00:00:00 1970 PST + 187 | 407 | 00187_update7 | Sun Mar 29 00:00:00 1970 PST + 188 | 8 | 00188 | Mon Mar 30 00:00:00 1970 PST + 189 | 509 | 00189_update9 | Tue Mar 31 00:00:00 1970 PST + 190 | 0 | 00190 | Wed Apr 01 00:00:00 1970 PST + 191 | 1 | 00191 | Thu Apr 02 00:00:00 1970 PST + 193 | 303 | 00193_update3 | Sat Apr 04 00:00:00 1970 PST + 194 | 4 | 00194 | Sun Apr 05 00:00:00 1970 PST + 196 | 6 | 00196 | Tue Apr 07 00:00:00 1970 PST + 197 | 407 | 00197_update7 | Wed Apr 08 00:00:00 1970 PST + 198 | 8 | 00198 | Thu Apr 09 00:00:00 1970 PST + 199 | 509 | 00199_update9 | Fri Apr 10 00:00:00 1970 PST + 200 | 0 | 00200 | Thu Jan 01 00:00:00 1970 PST + 201 | 1 | 00201 | Fri Jan 02 00:00:00 1970 PST + 203 | 303 | 00203_update3 | Sun Jan 04 00:00:00 1970 PST + 204 | 4 | 00204 | Mon Jan 05 00:00:00 1970 PST + 206 | 6 | 00206 | Wed Jan 07 00:00:00 1970 PST + 207 | 407 | 00207_update7 | Thu Jan 08 00:00:00 1970 PST + 208 | 8 | 00208 | Fri Jan 09 00:00:00 1970 PST + 209 | 509 | 00209_update9 | Sat Jan 10 00:00:00 1970 PST + 210 | 0 | 00210 | Sun Jan 11 00:00:00 1970 PST + 211 | 1 | 00211 | Mon Jan 12 00:00:00 1970 PST + 213 | 303 | 00213_update3 | Wed Jan 14 00:00:00 1970 PST + 214 | 4 | 00214 | Thu Jan 15 00:00:00 1970 PST + 216 | 6 | 00216 | Sat Jan 17 00:00:00 1970 PST + 217 | 407 | 00217_update7 | Sun Jan 18 00:00:00 1970 PST + 218 | 8 | 00218 | Mon Jan 19 00:00:00 1970 PST + 219 | 509 | 00219_update9 | Tue Jan 20 00:00:00 1970 PST + 220 | 0 | 00220 | Wed Jan 21 00:00:00 1970 PST + 221 | 1 | 00221 | Thu Jan 22 00:00:00 1970 PST + 223 | 303 | 00223_update3 | Sat Jan 24 00:00:00 1970 PST + 224 | 4 | 00224 | Sun Jan 25 00:00:00 1970 PST + 226 | 6 | 00226 | Tue Jan 27 00:00:00 1970 PST + 227 | 407 | 00227_update7 | Wed Jan 28 00:00:00 1970 PST + 228 | 8 | 00228 | Thu Jan 29 00:00:00 1970 PST + 229 | 509 | 00229_update9 | Fri Jan 30 00:00:00 1970 PST + 230 | 0 | 00230 | Sat Jan 31 00:00:00 1970 PST + 231 | 1 | 00231 | Sun Feb 01 00:00:00 1970 PST + 233 | 303 | 00233_update3 | Tue Feb 03 00:00:00 1970 PST + 234 | 4 | 00234 | Wed Feb 04 00:00:00 1970 PST + 236 | 6 | 00236 | Fri Feb 06 00:00:00 1970 PST + 237 | 407 | 00237_update7 | Sat Feb 07 00:00:00 1970 PST + 238 | 8 | 00238 | Sun Feb 08 00:00:00 1970 PST + 239 | 509 | 00239_update9 | Mon Feb 09 00:00:00 1970 PST + 240 | 0 | 00240 | Tue Feb 10 00:00:00 1970 PST + 241 | 1 | 00241 | Wed Feb 11 00:00:00 1970 PST + 243 | 303 | 00243_update3 | Fri Feb 13 00:00:00 1970 PST + 244 | 4 | 00244 | Sat Feb 14 00:00:00 1970 PST + 246 | 6 | 00246 | Mon Feb 16 00:00:00 1970 PST + 247 | 407 | 00247_update7 | Tue Feb 17 00:00:00 1970 PST + 248 | 8 | 00248 | Wed Feb 18 00:00:00 1970 PST + 249 | 509 | 00249_update9 | Thu Feb 19 00:00:00 1970 PST + 250 | 0 | 00250 | Fri Feb 20 00:00:00 1970 PST + 251 | 1 | 00251 | Sat Feb 21 00:00:00 1970 PST + 253 | 303 | 00253_update3 | Mon Feb 23 00:00:00 1970 PST + 254 | 4 | 00254 | Tue Feb 24 00:00:00 1970 PST + 256 | 6 | 00256 | Thu Feb 26 00:00:00 1970 PST + 257 | 407 | 00257_update7 | Fri Feb 27 00:00:00 1970 PST + 258 | 8 | 00258 | Sat Feb 28 00:00:00 1970 PST + 259 | 509 | 00259_update9 | Sun Mar 01 00:00:00 1970 PST + 260 | 0 | 00260 | Mon Mar 02 00:00:00 1970 PST + 261 | 1 | 00261 | Tue Mar 03 00:00:00 1970 PST + 263 | 303 | 00263_update3 | Thu Mar 05 00:00:00 1970 PST + 264 | 4 | 00264 | Fri Mar 06 00:00:00 1970 PST + 266 | 6 | 00266 | Sun Mar 08 00:00:00 1970 PST + 267 | 407 | 00267_update7 | Mon Mar 09 00:00:00 1970 PST + 268 | 8 | 00268 | Tue Mar 10 00:00:00 1970 PST + 269 | 509 | 00269_update9 | Wed Mar 11 00:00:00 1970 PST + 270 | 0 | 00270 | Thu Mar 12 00:00:00 1970 PST + 271 | 1 | 00271 | Fri Mar 13 00:00:00 1970 PST + 273 | 303 | 00273_update3 | Sun Mar 15 00:00:00 1970 PST + 274 | 4 | 00274 | Mon Mar 16 00:00:00 1970 PST + 276 | 6 | 00276 | Wed Mar 18 00:00:00 1970 PST + 277 | 407 | 00277_update7 | Thu Mar 19 00:00:00 1970 PST + 278 | 8 | 00278 | Fri Mar 20 00:00:00 1970 PST + 279 | 509 | 00279_update9 | Sat Mar 21 00:00:00 1970 PST + 280 | 0 | 00280 | Sun Mar 22 00:00:00 1970 PST + 281 | 1 | 00281 | Mon Mar 23 00:00:00 1970 PST + 283 | 303 | 00283_update3 | Wed Mar 25 00:00:00 1970 PST + 284 | 4 | 00284 | Thu Mar 26 00:00:00 1970 PST + 286 | 6 | 00286 | Sat Mar 28 00:00:00 1970 PST + 287 | 407 | 00287_update7 | Sun Mar 29 00:00:00 1970 PST + 288 | 8 | 00288 | Mon Mar 30 00:00:00 1970 PST + 289 | 509 | 00289_update9 | Tue Mar 31 00:00:00 1970 PST + 290 | 0 | 00290 | Wed Apr 01 00:00:00 1970 PST + 291 | 1 | 00291 | Thu Apr 02 00:00:00 1970 PST + 293 | 303 | 00293_update3 | Sat Apr 04 00:00:00 1970 PST + 294 | 4 | 00294 | Sun Apr 05 00:00:00 1970 PST + 296 | 6 | 00296 | Tue Apr 07 00:00:00 1970 PST + 297 | 407 | 00297_update7 | Wed Apr 08 00:00:00 1970 PST + 298 | 8 | 00298 | Thu Apr 09 00:00:00 1970 PST + 299 | 509 | 00299_update9 | Fri Apr 10 00:00:00 1970 PST + 300 | 0 | 00300 | Thu Jan 01 00:00:00 1970 PST + 301 | 1 | 00301 | Fri Jan 02 00:00:00 1970 PST + 303 | 303 | 00303_update3 | Sun Jan 04 00:00:00 1970 PST + 304 | 4 | 00304 | Mon Jan 05 00:00:00 1970 PST + 306 | 6 | 00306 | Wed Jan 07 00:00:00 1970 PST + 307 | 407 | 00307_update7 | Thu Jan 08 00:00:00 1970 PST + 308 | 8 | 00308 | Fri Jan 09 00:00:00 1970 PST + 309 | 509 | 00309_update9 | Sat Jan 10 00:00:00 1970 PST + 310 | 0 | 00310 | Sun Jan 11 00:00:00 1970 PST + 311 | 1 | 00311 | Mon Jan 12 00:00:00 1970 PST + 313 | 303 | 00313_update3 | Wed Jan 14 00:00:00 1970 PST + 314 | 4 | 00314 | Thu Jan 15 00:00:00 1970 PST + 316 | 6 | 00316 | Sat Jan 17 00:00:00 1970 PST + 317 | 407 | 00317_update7 | Sun Jan 18 00:00:00 1970 PST + 318 | 8 | 00318 | Mon Jan 19 00:00:00 1970 PST + 319 | 509 | 00319_update9 | Tue Jan 20 00:00:00 1970 PST + 320 | 0 | 00320 | Wed Jan 21 00:00:00 1970 PST + 321 | 1 | 00321 | Thu Jan 22 00:00:00 1970 PST + 323 | 303 | 00323_update3 | Sat Jan 24 00:00:00 1970 PST + 324 | 4 | 00324 | Sun Jan 25 00:00:00 1970 PST + 326 | 6 | 00326 | Tue Jan 27 00:00:00 1970 PST + 327 | 407 | 00327_update7 | Wed Jan 28 00:00:00 1970 PST + 328 | 8 | 00328 | Thu Jan 29 00:00:00 1970 PST + 329 | 509 | 00329_update9 | Fri Jan 30 00:00:00 1970 PST + 330 | 0 | 00330 | Sat Jan 31 00:00:00 1970 PST + 331 | 1 | 00331 | Sun Feb 01 00:00:00 1970 PST + 333 | 303 | 00333_update3 | Tue Feb 03 00:00:00 1970 PST + 334 | 4 | 00334 | Wed Feb 04 00:00:00 1970 PST + 336 | 6 | 00336 | Fri Feb 06 00:00:00 1970 PST + 337 | 407 | 00337_update7 | Sat Feb 07 00:00:00 1970 PST + 338 | 8 | 00338 | Sun Feb 08 00:00:00 1970 PST + 339 | 509 | 00339_update9 | Mon Feb 09 00:00:00 1970 PST + 340 | 0 | 00340 | Tue Feb 10 00:00:00 1970 PST + 341 | 1 | 00341 | Wed Feb 11 00:00:00 1970 PST + 343 | 303 | 00343_update3 | Fri Feb 13 00:00:00 1970 PST + 344 | 4 | 00344 | Sat Feb 14 00:00:00 1970 PST + 346 | 6 | 00346 | Mon Feb 16 00:00:00 1970 PST + 347 | 407 | 00347_update7 | Tue Feb 17 00:00:00 1970 PST + 348 | 8 | 00348 | Wed Feb 18 00:00:00 1970 PST + 349 | 509 | 00349_update9 | Thu Feb 19 00:00:00 1970 PST + 350 | 0 | 00350 | Fri Feb 20 00:00:00 1970 PST + 351 | 1 | 00351 | Sat Feb 21 00:00:00 1970 PST + 353 | 303 | 00353_update3 | Mon Feb 23 00:00:00 1970 PST + 354 | 4 | 00354 | Tue Feb 24 00:00:00 1970 PST + 356 | 6 | 00356 | Thu Feb 26 00:00:00 1970 PST + 357 | 407 | 00357_update7 | Fri Feb 27 00:00:00 1970 PST + 358 | 8 | 00358 | Sat Feb 28 00:00:00 1970 PST + 359 | 509 | 00359_update9 | Sun Mar 01 00:00:00 1970 PST + 360 | 0 | 00360 | Mon Mar 02 00:00:00 1970 PST + 361 | 1 | 00361 | Tue Mar 03 00:00:00 1970 PST + 363 | 303 | 00363_update3 | Thu Mar 05 00:00:00 1970 PST + 364 | 4 | 00364 | Fri Mar 06 00:00:00 1970 PST + 366 | 6 | 00366 | Sun Mar 08 00:00:00 1970 PST + 367 | 407 | 00367_update7 | Mon Mar 09 00:00:00 1970 PST + 368 | 8 | 00368 | Tue Mar 10 00:00:00 1970 PST + 369 | 509 | 00369_update9 | Wed Mar 11 00:00:00 1970 PST + 370 | 0 | 00370 | Thu Mar 12 00:00:00 1970 PST + 371 | 1 | 00371 | Fri Mar 13 00:00:00 1970 PST + 373 | 303 | 00373_update3 | Sun Mar 15 00:00:00 1970 PST + 374 | 4 | 00374 | Mon Mar 16 00:00:00 1970 PST + 376 | 6 | 00376 | Wed Mar 18 00:00:00 1970 PST + 377 | 407 | 00377_update7 | Thu Mar 19 00:00:00 1970 PST + 378 | 8 | 00378 | Fri Mar 20 00:00:00 1970 PST + 379 | 509 | 00379_update9 | Sat Mar 21 00:00:00 1970 PST + 380 | 0 | 00380 | Sun Mar 22 00:00:00 1970 PST + 381 | 1 | 00381 | Mon Mar 23 00:00:00 1970 PST + 383 | 303 | 00383_update3 | Wed Mar 25 00:00:00 1970 PST + 384 | 4 | 00384 | Thu Mar 26 00:00:00 1970 PST + 386 | 6 | 00386 | Sat Mar 28 00:00:00 1970 PST + 387 | 407 | 00387_update7 | Sun Mar 29 00:00:00 1970 PST + 388 | 8 | 00388 | Mon Mar 30 00:00:00 1970 PST + 389 | 509 | 00389_update9 | Tue Mar 31 00:00:00 1970 PST + 390 | 0 | 00390 | Wed Apr 01 00:00:00 1970 PST + 391 | 1 | 00391 | Thu Apr 02 00:00:00 1970 PST + 393 | 303 | 00393_update3 | Sat Apr 04 00:00:00 1970 PST + 394 | 4 | 00394 | Sun Apr 05 00:00:00 1970 PST + 396 | 6 | 00396 | Tue Apr 07 00:00:00 1970 PST + 397 | 407 | 00397_update7 | Wed Apr 08 00:00:00 1970 PST + 398 | 8 | 00398 | Thu Apr 09 00:00:00 1970 PST + 399 | 509 | 00399_update9 | Fri Apr 10 00:00:00 1970 PST + 400 | 0 | 00400 | Thu Jan 01 00:00:00 1970 PST + 401 | 1 | 00401 | Fri Jan 02 00:00:00 1970 PST + 403 | 303 | 00403_update3 | Sun Jan 04 00:00:00 1970 PST + 404 | 4 | 00404 | Mon Jan 05 00:00:00 1970 PST + 406 | 6 | 00406 | Wed Jan 07 00:00:00 1970 PST + 407 | 407 | 00407_update7 | Thu Jan 08 00:00:00 1970 PST + 408 | 8 | 00408 | Fri Jan 09 00:00:00 1970 PST + 409 | 509 | 00409_update9 | Sat Jan 10 00:00:00 1970 PST + 410 | 0 | 00410 | Sun Jan 11 00:00:00 1970 PST + 411 | 1 | 00411 | Mon Jan 12 00:00:00 1970 PST + 413 | 303 | 00413_update3 | Wed Jan 14 00:00:00 1970 PST + 414 | 4 | 00414 | Thu Jan 15 00:00:00 1970 PST + 416 | 6 | 00416 | Sat Jan 17 00:00:00 1970 PST + 417 | 407 | 00417_update7 | Sun Jan 18 00:00:00 1970 PST + 418 | 8 | 00418 | Mon Jan 19 00:00:00 1970 PST + 419 | 509 | 00419_update9 | Tue Jan 20 00:00:00 1970 PST + 420 | 0 | 00420 | Wed Jan 21 00:00:00 1970 PST + 421 | 1 | 00421 | Thu Jan 22 00:00:00 1970 PST + 423 | 303 | 00423_update3 | Sat Jan 24 00:00:00 1970 PST + 424 | 4 | 00424 | Sun Jan 25 00:00:00 1970 PST + 426 | 6 | 00426 | Tue Jan 27 00:00:00 1970 PST + 427 | 407 | 00427_update7 | Wed Jan 28 00:00:00 1970 PST + 428 | 8 | 00428 | Thu Jan 29 00:00:00 1970 PST + 429 | 509 | 00429_update9 | Fri Jan 30 00:00:00 1970 PST + 430 | 0 | 00430 | Sat Jan 31 00:00:00 1970 PST + 431 | 1 | 00431 | Sun Feb 01 00:00:00 1970 PST + 433 | 303 | 00433_update3 | Tue Feb 03 00:00:00 1970 PST + 434 | 4 | 00434 | Wed Feb 04 00:00:00 1970 PST + 436 | 6 | 00436 | Fri Feb 06 00:00:00 1970 PST + 437 | 407 | 00437_update7 | Sat Feb 07 00:00:00 1970 PST + 438 | 8 | 00438 | Sun Feb 08 00:00:00 1970 PST + 439 | 509 | 00439_update9 | Mon Feb 09 00:00:00 1970 PST + 440 | 0 | 00440 | Tue Feb 10 00:00:00 1970 PST + 441 | 1 | 00441 | Wed Feb 11 00:00:00 1970 PST + 443 | 303 | 00443_update3 | Fri Feb 13 00:00:00 1970 PST + 444 | 4 | 00444 | Sat Feb 14 00:00:00 1970 PST + 446 | 6 | 00446 | Mon Feb 16 00:00:00 1970 PST + 447 | 407 | 00447_update7 | Tue Feb 17 00:00:00 1970 PST + 448 | 8 | 00448 | Wed Feb 18 00:00:00 1970 PST + 449 | 509 | 00449_update9 | Thu Feb 19 00:00:00 1970 PST + 450 | 0 | 00450 | Fri Feb 20 00:00:00 1970 PST + 451 | 1 | 00451 | Sat Feb 21 00:00:00 1970 PST + 453 | 303 | 00453_update3 | Mon Feb 23 00:00:00 1970 PST + 454 | 4 | 00454 | Tue Feb 24 00:00:00 1970 PST + 456 | 6 | 00456 | Thu Feb 26 00:00:00 1970 PST + 457 | 407 | 00457_update7 | Fri Feb 27 00:00:00 1970 PST + 458 | 8 | 00458 | Sat Feb 28 00:00:00 1970 PST + 459 | 509 | 00459_update9 | Sun Mar 01 00:00:00 1970 PST + 460 | 0 | 00460 | Mon Mar 02 00:00:00 1970 PST + 461 | 1 | 00461 | Tue Mar 03 00:00:00 1970 PST + 463 | 303 | 00463_update3 | Thu Mar 05 00:00:00 1970 PST + 464 | 4 | 00464 | Fri Mar 06 00:00:00 1970 PST + 466 | 6 | 00466 | Sun Mar 08 00:00:00 1970 PST + 467 | 407 | 00467_update7 | Mon Mar 09 00:00:00 1970 PST + 468 | 8 | 00468 | Tue Mar 10 00:00:00 1970 PST + 469 | 509 | 00469_update9 | Wed Mar 11 00:00:00 1970 PST + 470 | 0 | 00470 | Thu Mar 12 00:00:00 1970 PST + 471 | 1 | 00471 | Fri Mar 13 00:00:00 1970 PST + 473 | 303 | 00473_update3 | Sun Mar 15 00:00:00 1970 PST + 474 | 4 | 00474 | Mon Mar 16 00:00:00 1970 PST + 476 | 6 | 00476 | Wed Mar 18 00:00:00 1970 PST + 477 | 407 | 00477_update7 | Thu Mar 19 00:00:00 1970 PST + 478 | 8 | 00478 | Fri Mar 20 00:00:00 1970 PST + 479 | 509 | 00479_update9 | Sat Mar 21 00:00:00 1970 PST + 480 | 0 | 00480 | Sun Mar 22 00:00:00 1970 PST + 481 | 1 | 00481 | Mon Mar 23 00:00:00 1970 PST + 483 | 303 | 00483_update3 | Wed Mar 25 00:00:00 1970 PST + 484 | 4 | 00484 | Thu Mar 26 00:00:00 1970 PST + 486 | 6 | 00486 | Sat Mar 28 00:00:00 1970 PST + 487 | 407 | 00487_update7 | Sun Mar 29 00:00:00 1970 PST + 488 | 8 | 00488 | Mon Mar 30 00:00:00 1970 PST + 489 | 509 | 00489_update9 | Tue Mar 31 00:00:00 1970 PST + 490 | 0 | 00490 | Wed Apr 01 00:00:00 1970 PST + 491 | 1 | 00491 | Thu Apr 02 00:00:00 1970 PST + 493 | 303 | 00493_update3 | Sat Apr 04 00:00:00 1970 PST + 494 | 4 | 00494 | Sun Apr 05 00:00:00 1970 PST + 496 | 6 | 00496 | Tue Apr 07 00:00:00 1970 PST + 497 | 407 | 00497_update7 | Wed Apr 08 00:00:00 1970 PST + 498 | 8 | 00498 | Thu Apr 09 00:00:00 1970 PST + 499 | 509 | 00499_update9 | Fri Apr 10 00:00:00 1970 PST + 500 | 0 | 00500 | Thu Jan 01 00:00:00 1970 PST + 501 | 1 | 00501 | Fri Jan 02 00:00:00 1970 PST + 503 | 303 | 00503_update3 | Sun Jan 04 00:00:00 1970 PST + 504 | 4 | 00504 | Mon Jan 05 00:00:00 1970 PST + 506 | 6 | 00506 | Wed Jan 07 00:00:00 1970 PST + 507 | 407 | 00507_update7 | Thu Jan 08 00:00:00 1970 PST + 508 | 8 | 00508 | Fri Jan 09 00:00:00 1970 PST + 509 | 509 | 00509_update9 | Sat Jan 10 00:00:00 1970 PST + 510 | 0 | 00510 | Sun Jan 11 00:00:00 1970 PST + 511 | 1 | 00511 | Mon Jan 12 00:00:00 1970 PST + 513 | 303 | 00513_update3 | Wed Jan 14 00:00:00 1970 PST + 514 | 4 | 00514 | Thu Jan 15 00:00:00 1970 PST + 516 | 6 | 00516 | Sat Jan 17 00:00:00 1970 PST + 517 | 407 | 00517_update7 | Sun Jan 18 00:00:00 1970 PST + 518 | 8 | 00518 | Mon Jan 19 00:00:00 1970 PST + 519 | 509 | 00519_update9 | Tue Jan 20 00:00:00 1970 PST + 520 | 0 | 00520 | Wed Jan 21 00:00:00 1970 PST + 521 | 1 | 00521 | Thu Jan 22 00:00:00 1970 PST + 523 | 303 | 00523_update3 | Sat Jan 24 00:00:00 1970 PST + 524 | 4 | 00524 | Sun Jan 25 00:00:00 1970 PST + 526 | 6 | 00526 | Tue Jan 27 00:00:00 1970 PST + 527 | 407 | 00527_update7 | Wed Jan 28 00:00:00 1970 PST + 528 | 8 | 00528 | Thu Jan 29 00:00:00 1970 PST + 529 | 509 | 00529_update9 | Fri Jan 30 00:00:00 1970 PST + 530 | 0 | 00530 | Sat Jan 31 00:00:00 1970 PST + 531 | 1 | 00531 | Sun Feb 01 00:00:00 1970 PST + 533 | 303 | 00533_update3 | Tue Feb 03 00:00:00 1970 PST + 534 | 4 | 00534 | Wed Feb 04 00:00:00 1970 PST + 536 | 6 | 00536 | Fri Feb 06 00:00:00 1970 PST + 537 | 407 | 00537_update7 | Sat Feb 07 00:00:00 1970 PST + 538 | 8 | 00538 | Sun Feb 08 00:00:00 1970 PST + 539 | 509 | 00539_update9 | Mon Feb 09 00:00:00 1970 PST + 540 | 0 | 00540 | Tue Feb 10 00:00:00 1970 PST + 541 | 1 | 00541 | Wed Feb 11 00:00:00 1970 PST + 543 | 303 | 00543_update3 | Fri Feb 13 00:00:00 1970 PST + 544 | 4 | 00544 | Sat Feb 14 00:00:00 1970 PST + 546 | 6 | 00546 | Mon Feb 16 00:00:00 1970 PST + 547 | 407 | 00547_update7 | Tue Feb 17 00:00:00 1970 PST + 548 | 8 | 00548 | Wed Feb 18 00:00:00 1970 PST + 549 | 509 | 00549_update9 | Thu Feb 19 00:00:00 1970 PST + 550 | 0 | 00550 | Fri Feb 20 00:00:00 1970 PST + 551 | 1 | 00551 | Sat Feb 21 00:00:00 1970 PST + 553 | 303 | 00553_update3 | Mon Feb 23 00:00:00 1970 PST + 554 | 4 | 00554 | Tue Feb 24 00:00:00 1970 PST + 556 | 6 | 00556 | Thu Feb 26 00:00:00 1970 PST + 557 | 407 | 00557_update7 | Fri Feb 27 00:00:00 1970 PST + 558 | 8 | 00558 | Sat Feb 28 00:00:00 1970 PST + 559 | 509 | 00559_update9 | Sun Mar 01 00:00:00 1970 PST + 560 | 0 | 00560 | Mon Mar 02 00:00:00 1970 PST + 561 | 1 | 00561 | Tue Mar 03 00:00:00 1970 PST + 563 | 303 | 00563_update3 | Thu Mar 05 00:00:00 1970 PST + 564 | 4 | 00564 | Fri Mar 06 00:00:00 1970 PST + 566 | 6 | 00566 | Sun Mar 08 00:00:00 1970 PST + 567 | 407 | 00567_update7 | Mon Mar 09 00:00:00 1970 PST + 568 | 8 | 00568 | Tue Mar 10 00:00:00 1970 PST + 569 | 509 | 00569_update9 | Wed Mar 11 00:00:00 1970 PST + 570 | 0 | 00570 | Thu Mar 12 00:00:00 1970 PST + 571 | 1 | 00571 | Fri Mar 13 00:00:00 1970 PST + 573 | 303 | 00573_update3 | Sun Mar 15 00:00:00 1970 PST + 574 | 4 | 00574 | Mon Mar 16 00:00:00 1970 PST + 576 | 6 | 00576 | Wed Mar 18 00:00:00 1970 PST + 577 | 407 | 00577_update7 | Thu Mar 19 00:00:00 1970 PST + 578 | 8 | 00578 | Fri Mar 20 00:00:00 1970 PST + 579 | 509 | 00579_update9 | Sat Mar 21 00:00:00 1970 PST + 580 | 0 | 00580 | Sun Mar 22 00:00:00 1970 PST + 581 | 1 | 00581 | Mon Mar 23 00:00:00 1970 PST + 583 | 303 | 00583_update3 | Wed Mar 25 00:00:00 1970 PST + 584 | 4 | 00584 | Thu Mar 26 00:00:00 1970 PST + 586 | 6 | 00586 | Sat Mar 28 00:00:00 1970 PST + 587 | 407 | 00587_update7 | Sun Mar 29 00:00:00 1970 PST + 588 | 8 | 00588 | Mon Mar 30 00:00:00 1970 PST + 589 | 509 | 00589_update9 | Tue Mar 31 00:00:00 1970 PST + 590 | 0 | 00590 | Wed Apr 01 00:00:00 1970 PST + 591 | 1 | 00591 | Thu Apr 02 00:00:00 1970 PST + 593 | 303 | 00593_update3 | Sat Apr 04 00:00:00 1970 PST + 594 | 4 | 00594 | Sun Apr 05 00:00:00 1970 PST + 596 | 6 | 00596 | Tue Apr 07 00:00:00 1970 PST + 597 | 407 | 00597_update7 | Wed Apr 08 00:00:00 1970 PST + 598 | 8 | 00598 | Thu Apr 09 00:00:00 1970 PST + 599 | 509 | 00599_update9 | Fri Apr 10 00:00:00 1970 PST + 600 | 0 | 00600 | Thu Jan 01 00:00:00 1970 PST + 601 | 1 | 00601 | Fri Jan 02 00:00:00 1970 PST + 603 | 303 | 00603_update3 | Sun Jan 04 00:00:00 1970 PST + 604 | 4 | 00604 | Mon Jan 05 00:00:00 1970 PST + 606 | 6 | 00606 | Wed Jan 07 00:00:00 1970 PST + 607 | 407 | 00607_update7 | Thu Jan 08 00:00:00 1970 PST + 608 | 8 | 00608 | Fri Jan 09 00:00:00 1970 PST + 609 | 509 | 00609_update9 | Sat Jan 10 00:00:00 1970 PST + 610 | 0 | 00610 | Sun Jan 11 00:00:00 1970 PST + 611 | 1 | 00611 | Mon Jan 12 00:00:00 1970 PST + 613 | 303 | 00613_update3 | Wed Jan 14 00:00:00 1970 PST + 614 | 4 | 00614 | Thu Jan 15 00:00:00 1970 PST + 616 | 6 | 00616 | Sat Jan 17 00:00:00 1970 PST + 617 | 407 | 00617_update7 | Sun Jan 18 00:00:00 1970 PST + 618 | 8 | 00618 | Mon Jan 19 00:00:00 1970 PST + 619 | 509 | 00619_update9 | Tue Jan 20 00:00:00 1970 PST + 620 | 0 | 00620 | Wed Jan 21 00:00:00 1970 PST + 621 | 1 | 00621 | Thu Jan 22 00:00:00 1970 PST + 623 | 303 | 00623_update3 | Sat Jan 24 00:00:00 1970 PST + 624 | 4 | 00624 | Sun Jan 25 00:00:00 1970 PST + 626 | 6 | 00626 | Tue Jan 27 00:00:00 1970 PST + 627 | 407 | 00627_update7 | Wed Jan 28 00:00:00 1970 PST + 628 | 8 | 00628 | Thu Jan 29 00:00:00 1970 PST + 629 | 509 | 00629_update9 | Fri Jan 30 00:00:00 1970 PST + 630 | 0 | 00630 | Sat Jan 31 00:00:00 1970 PST + 631 | 1 | 00631 | Sun Feb 01 00:00:00 1970 PST + 633 | 303 | 00633_update3 | Tue Feb 03 00:00:00 1970 PST + 634 | 4 | 00634 | Wed Feb 04 00:00:00 1970 PST + 636 | 6 | 00636 | Fri Feb 06 00:00:00 1970 PST + 637 | 407 | 00637_update7 | Sat Feb 07 00:00:00 1970 PST + 638 | 8 | 00638 | Sun Feb 08 00:00:00 1970 PST + 639 | 509 | 00639_update9 | Mon Feb 09 00:00:00 1970 PST + 640 | 0 | 00640 | Tue Feb 10 00:00:00 1970 PST + 641 | 1 | 00641 | Wed Feb 11 00:00:00 1970 PST + 643 | 303 | 00643_update3 | Fri Feb 13 00:00:00 1970 PST + 644 | 4 | 00644 | Sat Feb 14 00:00:00 1970 PST + 646 | 6 | 00646 | Mon Feb 16 00:00:00 1970 PST + 647 | 407 | 00647_update7 | Tue Feb 17 00:00:00 1970 PST + 648 | 8 | 00648 | Wed Feb 18 00:00:00 1970 PST + 649 | 509 | 00649_update9 | Thu Feb 19 00:00:00 1970 PST + 650 | 0 | 00650 | Fri Feb 20 00:00:00 1970 PST + 651 | 1 | 00651 | Sat Feb 21 00:00:00 1970 PST + 653 | 303 | 00653_update3 | Mon Feb 23 00:00:00 1970 PST + 654 | 4 | 00654 | Tue Feb 24 00:00:00 1970 PST + 656 | 6 | 00656 | Thu Feb 26 00:00:00 1970 PST + 657 | 407 | 00657_update7 | Fri Feb 27 00:00:00 1970 PST + 658 | 8 | 00658 | Sat Feb 28 00:00:00 1970 PST + 659 | 509 | 00659_update9 | Sun Mar 01 00:00:00 1970 PST + 660 | 0 | 00660 | Mon Mar 02 00:00:00 1970 PST + 661 | 1 | 00661 | Tue Mar 03 00:00:00 1970 PST + 663 | 303 | 00663_update3 | Thu Mar 05 00:00:00 1970 PST + 664 | 4 | 00664 | Fri Mar 06 00:00:00 1970 PST + 666 | 6 | 00666 | Sun Mar 08 00:00:00 1970 PST + 667 | 407 | 00667_update7 | Mon Mar 09 00:00:00 1970 PST + 668 | 8 | 00668 | Tue Mar 10 00:00:00 1970 PST + 669 | 509 | 00669_update9 | Wed Mar 11 00:00:00 1970 PST + 670 | 0 | 00670 | Thu Mar 12 00:00:00 1970 PST + 671 | 1 | 00671 | Fri Mar 13 00:00:00 1970 PST + 673 | 303 | 00673_update3 | Sun Mar 15 00:00:00 1970 PST + 674 | 4 | 00674 | Mon Mar 16 00:00:00 1970 PST + 676 | 6 | 00676 | Wed Mar 18 00:00:00 1970 PST + 677 | 407 | 00677_update7 | Thu Mar 19 00:00:00 1970 PST + 678 | 8 | 00678 | Fri Mar 20 00:00:00 1970 PST + 679 | 509 | 00679_update9 | Sat Mar 21 00:00:00 1970 PST + 680 | 0 | 00680 | Sun Mar 22 00:00:00 1970 PST + 681 | 1 | 00681 | Mon Mar 23 00:00:00 1970 PST + 683 | 303 | 00683_update3 | Wed Mar 25 00:00:00 1970 PST + 684 | 4 | 00684 | Thu Mar 26 00:00:00 1970 PST + 686 | 6 | 00686 | Sat Mar 28 00:00:00 1970 PST + 687 | 407 | 00687_update7 | Sun Mar 29 00:00:00 1970 PST + 688 | 8 | 00688 | Mon Mar 30 00:00:00 1970 PST + 689 | 509 | 00689_update9 | Tue Mar 31 00:00:00 1970 PST + 690 | 0 | 00690 | Wed Apr 01 00:00:00 1970 PST + 691 | 1 | 00691 | Thu Apr 02 00:00:00 1970 PST + 693 | 303 | 00693_update3 | Sat Apr 04 00:00:00 1970 PST + 694 | 4 | 00694 | Sun Apr 05 00:00:00 1970 PST + 696 | 6 | 00696 | Tue Apr 07 00:00:00 1970 PST + 697 | 407 | 00697_update7 | Wed Apr 08 00:00:00 1970 PST + 698 | 8 | 00698 | Thu Apr 09 00:00:00 1970 PST + 699 | 509 | 00699_update9 | Fri Apr 10 00:00:00 1970 PST + 700 | 0 | 00700 | Thu Jan 01 00:00:00 1970 PST + 701 | 1 | 00701 | Fri Jan 02 00:00:00 1970 PST + 703 | 303 | 00703_update3 | Sun Jan 04 00:00:00 1970 PST + 704 | 4 | 00704 | Mon Jan 05 00:00:00 1970 PST + 706 | 6 | 00706 | Wed Jan 07 00:00:00 1970 PST + 707 | 407 | 00707_update7 | Thu Jan 08 00:00:00 1970 PST + 708 | 8 | 00708 | Fri Jan 09 00:00:00 1970 PST + 709 | 509 | 00709_update9 | Sat Jan 10 00:00:00 1970 PST + 710 | 0 | 00710 | Sun Jan 11 00:00:00 1970 PST + 711 | 1 | 00711 | Mon Jan 12 00:00:00 1970 PST + 713 | 303 | 00713_update3 | Wed Jan 14 00:00:00 1970 PST + 714 | 4 | 00714 | Thu Jan 15 00:00:00 1970 PST + 716 | 6 | 00716 | Sat Jan 17 00:00:00 1970 PST + 717 | 407 | 00717_update7 | Sun Jan 18 00:00:00 1970 PST + 718 | 8 | 00718 | Mon Jan 19 00:00:00 1970 PST + 719 | 509 | 00719_update9 | Tue Jan 20 00:00:00 1970 PST + 720 | 0 | 00720 | Wed Jan 21 00:00:00 1970 PST + 721 | 1 | 00721 | Thu Jan 22 00:00:00 1970 PST + 723 | 303 | 00723_update3 | Sat Jan 24 00:00:00 1970 PST + 724 | 4 | 00724 | Sun Jan 25 00:00:00 1970 PST + 726 | 6 | 00726 | Tue Jan 27 00:00:00 1970 PST + 727 | 407 | 00727_update7 | Wed Jan 28 00:00:00 1970 PST + 728 | 8 | 00728 | Thu Jan 29 00:00:00 1970 PST + 729 | 509 | 00729_update9 | Fri Jan 30 00:00:00 1970 PST + 730 | 0 | 00730 | Sat Jan 31 00:00:00 1970 PST + 731 | 1 | 00731 | Sun Feb 01 00:00:00 1970 PST + 733 | 303 | 00733_update3 | Tue Feb 03 00:00:00 1970 PST + 734 | 4 | 00734 | Wed Feb 04 00:00:00 1970 PST + 736 | 6 | 00736 | Fri Feb 06 00:00:00 1970 PST + 737 | 407 | 00737_update7 | Sat Feb 07 00:00:00 1970 PST + 738 | 8 | 00738 | Sun Feb 08 00:00:00 1970 PST + 739 | 509 | 00739_update9 | Mon Feb 09 00:00:00 1970 PST + 740 | 0 | 00740 | Tue Feb 10 00:00:00 1970 PST + 741 | 1 | 00741 | Wed Feb 11 00:00:00 1970 PST + 743 | 303 | 00743_update3 | Fri Feb 13 00:00:00 1970 PST + 744 | 4 | 00744 | Sat Feb 14 00:00:00 1970 PST + 746 | 6 | 00746 | Mon Feb 16 00:00:00 1970 PST + 747 | 407 | 00747_update7 | Tue Feb 17 00:00:00 1970 PST + 748 | 8 | 00748 | Wed Feb 18 00:00:00 1970 PST + 749 | 509 | 00749_update9 | Thu Feb 19 00:00:00 1970 PST + 750 | 0 | 00750 | Fri Feb 20 00:00:00 1970 PST + 751 | 1 | 00751 | Sat Feb 21 00:00:00 1970 PST + 753 | 303 | 00753_update3 | Mon Feb 23 00:00:00 1970 PST + 754 | 4 | 00754 | Tue Feb 24 00:00:00 1970 PST + 756 | 6 | 00756 | Thu Feb 26 00:00:00 1970 PST + 757 | 407 | 00757_update7 | Fri Feb 27 00:00:00 1970 PST + 758 | 8 | 00758 | Sat Feb 28 00:00:00 1970 PST + 759 | 509 | 00759_update9 | Sun Mar 01 00:00:00 1970 PST + 760 | 0 | 00760 | Mon Mar 02 00:00:00 1970 PST + 761 | 1 | 00761 | Tue Mar 03 00:00:00 1970 PST + 763 | 303 | 00763_update3 | Thu Mar 05 00:00:00 1970 PST + 764 | 4 | 00764 | Fri Mar 06 00:00:00 1970 PST + 766 | 6 | 00766 | Sun Mar 08 00:00:00 1970 PST + 767 | 407 | 00767_update7 | Mon Mar 09 00:00:00 1970 PST + 768 | 8 | 00768 | Tue Mar 10 00:00:00 1970 PST + 769 | 509 | 00769_update9 | Wed Mar 11 00:00:00 1970 PST + 770 | 0 | 00770 | Thu Mar 12 00:00:00 1970 PST + 771 | 1 | 00771 | Fri Mar 13 00:00:00 1970 PST + 773 | 303 | 00773_update3 | Sun Mar 15 00:00:00 1970 PST + 774 | 4 | 00774 | Mon Mar 16 00:00:00 1970 PST + 776 | 6 | 00776 | Wed Mar 18 00:00:00 1970 PST + 777 | 407 | 00777_update7 | Thu Mar 19 00:00:00 1970 PST + 778 | 8 | 00778 | Fri Mar 20 00:00:00 1970 PST + 779 | 509 | 00779_update9 | Sat Mar 21 00:00:00 1970 PST + 780 | 0 | 00780 | Sun Mar 22 00:00:00 1970 PST + 781 | 1 | 00781 | Mon Mar 23 00:00:00 1970 PST + 783 | 303 | 00783_update3 | Wed Mar 25 00:00:00 1970 PST + 784 | 4 | 00784 | Thu Mar 26 00:00:00 1970 PST + 786 | 6 | 00786 | Sat Mar 28 00:00:00 1970 PST + 787 | 407 | 00787_update7 | Sun Mar 29 00:00:00 1970 PST + 788 | 8 | 00788 | Mon Mar 30 00:00:00 1970 PST + 789 | 509 | 00789_update9 | Tue Mar 31 00:00:00 1970 PST + 790 | 0 | 00790 | Wed Apr 01 00:00:00 1970 PST + 791 | 1 | 00791 | Thu Apr 02 00:00:00 1970 PST + 793 | 303 | 00793_update3 | Sat Apr 04 00:00:00 1970 PST + 794 | 4 | 00794 | Sun Apr 05 00:00:00 1970 PST + 796 | 6 | 00796 | Tue Apr 07 00:00:00 1970 PST + 797 | 407 | 00797_update7 | Wed Apr 08 00:00:00 1970 PST + 798 | 8 | 00798 | Thu Apr 09 00:00:00 1970 PST + 799 | 509 | 00799_update9 | Fri Apr 10 00:00:00 1970 PST + 800 | 0 | 00800 | Thu Jan 01 00:00:00 1970 PST + 801 | 1 | 00801 | Fri Jan 02 00:00:00 1970 PST + 803 | 303 | 00803_update3 | Sun Jan 04 00:00:00 1970 PST + 804 | 4 | 00804 | Mon Jan 05 00:00:00 1970 PST + 806 | 6 | 00806 | Wed Jan 07 00:00:00 1970 PST + 807 | 407 | 00807_update7 | Thu Jan 08 00:00:00 1970 PST + 808 | 8 | 00808 | Fri Jan 09 00:00:00 1970 PST + 809 | 509 | 00809_update9 | Sat Jan 10 00:00:00 1970 PST + 810 | 0 | 00810 | Sun Jan 11 00:00:00 1970 PST + 811 | 1 | 00811 | Mon Jan 12 00:00:00 1970 PST + 813 | 303 | 00813_update3 | Wed Jan 14 00:00:00 1970 PST + 814 | 4 | 00814 | Thu Jan 15 00:00:00 1970 PST + 816 | 6 | 00816 | Sat Jan 17 00:00:00 1970 PST + 817 | 407 | 00817_update7 | Sun Jan 18 00:00:00 1970 PST + 818 | 8 | 00818 | Mon Jan 19 00:00:00 1970 PST + 819 | 509 | 00819_update9 | Tue Jan 20 00:00:00 1970 PST + 820 | 0 | 00820 | Wed Jan 21 00:00:00 1970 PST + 821 | 1 | 00821 | Thu Jan 22 00:00:00 1970 PST + 823 | 303 | 00823_update3 | Sat Jan 24 00:00:00 1970 PST + 824 | 4 | 00824 | Sun Jan 25 00:00:00 1970 PST + 826 | 6 | 00826 | Tue Jan 27 00:00:00 1970 PST + 827 | 407 | 00827_update7 | Wed Jan 28 00:00:00 1970 PST + 828 | 8 | 00828 | Thu Jan 29 00:00:00 1970 PST + 829 | 509 | 00829_update9 | Fri Jan 30 00:00:00 1970 PST + 830 | 0 | 00830 | Sat Jan 31 00:00:00 1970 PST + 831 | 1 | 00831 | Sun Feb 01 00:00:00 1970 PST + 833 | 303 | 00833_update3 | Tue Feb 03 00:00:00 1970 PST + 834 | 4 | 00834 | Wed Feb 04 00:00:00 1970 PST + 836 | 6 | 00836 | Fri Feb 06 00:00:00 1970 PST + 837 | 407 | 00837_update7 | Sat Feb 07 00:00:00 1970 PST + 838 | 8 | 00838 | Sun Feb 08 00:00:00 1970 PST + 839 | 509 | 00839_update9 | Mon Feb 09 00:00:00 1970 PST + 840 | 0 | 00840 | Tue Feb 10 00:00:00 1970 PST + 841 | 1 | 00841 | Wed Feb 11 00:00:00 1970 PST + 843 | 303 | 00843_update3 | Fri Feb 13 00:00:00 1970 PST + 844 | 4 | 00844 | Sat Feb 14 00:00:00 1970 PST + 846 | 6 | 00846 | Mon Feb 16 00:00:00 1970 PST + 847 | 407 | 00847_update7 | Tue Feb 17 00:00:00 1970 PST + 848 | 8 | 00848 | Wed Feb 18 00:00:00 1970 PST + 849 | 509 | 00849_update9 | Thu Feb 19 00:00:00 1970 PST + 850 | 0 | 00850 | Fri Feb 20 00:00:00 1970 PST + 851 | 1 | 00851 | Sat Feb 21 00:00:00 1970 PST + 853 | 303 | 00853_update3 | Mon Feb 23 00:00:00 1970 PST + 854 | 4 | 00854 | Tue Feb 24 00:00:00 1970 PST + 856 | 6 | 00856 | Thu Feb 26 00:00:00 1970 PST + 857 | 407 | 00857_update7 | Fri Feb 27 00:00:00 1970 PST + 858 | 8 | 00858 | Sat Feb 28 00:00:00 1970 PST + 859 | 509 | 00859_update9 | Sun Mar 01 00:00:00 1970 PST + 860 | 0 | 00860 | Mon Mar 02 00:00:00 1970 PST + 861 | 1 | 00861 | Tue Mar 03 00:00:00 1970 PST + 863 | 303 | 00863_update3 | Thu Mar 05 00:00:00 1970 PST + 864 | 4 | 00864 | Fri Mar 06 00:00:00 1970 PST + 866 | 6 | 00866 | Sun Mar 08 00:00:00 1970 PST + 867 | 407 | 00867_update7 | Mon Mar 09 00:00:00 1970 PST + 868 | 8 | 00868 | Tue Mar 10 00:00:00 1970 PST + 869 | 509 | 00869_update9 | Wed Mar 11 00:00:00 1970 PST + 870 | 0 | 00870 | Thu Mar 12 00:00:00 1970 PST + 871 | 1 | 00871 | Fri Mar 13 00:00:00 1970 PST + 873 | 303 | 00873_update3 | Sun Mar 15 00:00:00 1970 PST + 874 | 4 | 00874 | Mon Mar 16 00:00:00 1970 PST + 876 | 6 | 00876 | Wed Mar 18 00:00:00 1970 PST + 877 | 407 | 00877_update7 | Thu Mar 19 00:00:00 1970 PST + 878 | 8 | 00878 | Fri Mar 20 00:00:00 1970 PST + 879 | 509 | 00879_update9 | Sat Mar 21 00:00:00 1970 PST + 880 | 0 | 00880 | Sun Mar 22 00:00:00 1970 PST + 881 | 1 | 00881 | Mon Mar 23 00:00:00 1970 PST + 883 | 303 | 00883_update3 | Wed Mar 25 00:00:00 1970 PST + 884 | 4 | 00884 | Thu Mar 26 00:00:00 1970 PST + 886 | 6 | 00886 | Sat Mar 28 00:00:00 1970 PST + 887 | 407 | 00887_update7 | Sun Mar 29 00:00:00 1970 PST + 888 | 8 | 00888 | Mon Mar 30 00:00:00 1970 PST + 889 | 509 | 00889_update9 | Tue Mar 31 00:00:00 1970 PST + 890 | 0 | 00890 | Wed Apr 01 00:00:00 1970 PST + 891 | 1 | 00891 | Thu Apr 02 00:00:00 1970 PST + 893 | 303 | 00893_update3 | Sat Apr 04 00:00:00 1970 PST + 894 | 4 | 00894 | Sun Apr 05 00:00:00 1970 PST + 896 | 6 | 00896 | Tue Apr 07 00:00:00 1970 PST + 897 | 407 | 00897_update7 | Wed Apr 08 00:00:00 1970 PST + 898 | 8 | 00898 | Thu Apr 09 00:00:00 1970 PST + 899 | 509 | 00899_update9 | Fri Apr 10 00:00:00 1970 PST + 900 | 0 | 00900 | Thu Jan 01 00:00:00 1970 PST + 901 | 1 | 00901 | Fri Jan 02 00:00:00 1970 PST + 903 | 303 | 00903_update3 | Sun Jan 04 00:00:00 1970 PST + 904 | 4 | 00904 | Mon Jan 05 00:00:00 1970 PST + 906 | 6 | 00906 | Wed Jan 07 00:00:00 1970 PST + 907 | 407 | 00907_update7 | Thu Jan 08 00:00:00 1970 PST + 908 | 8 | 00908 | Fri Jan 09 00:00:00 1970 PST + 909 | 509 | 00909_update9 | Sat Jan 10 00:00:00 1970 PST + 910 | 0 | 00910 | Sun Jan 11 00:00:00 1970 PST + 911 | 1 | 00911 | Mon Jan 12 00:00:00 1970 PST + 913 | 303 | 00913_update3 | Wed Jan 14 00:00:00 1970 PST + 914 | 4 | 00914 | Thu Jan 15 00:00:00 1970 PST + 916 | 6 | 00916 | Sat Jan 17 00:00:00 1970 PST + 917 | 407 | 00917_update7 | Sun Jan 18 00:00:00 1970 PST + 918 | 8 | 00918 | Mon Jan 19 00:00:00 1970 PST + 919 | 509 | 00919_update9 | Tue Jan 20 00:00:00 1970 PST + 920 | 0 | 00920 | Wed Jan 21 00:00:00 1970 PST + 921 | 1 | 00921 | Thu Jan 22 00:00:00 1970 PST + 923 | 303 | 00923_update3 | Sat Jan 24 00:00:00 1970 PST + 924 | 4 | 00924 | Sun Jan 25 00:00:00 1970 PST + 926 | 6 | 00926 | Tue Jan 27 00:00:00 1970 PST + 927 | 407 | 00927_update7 | Wed Jan 28 00:00:00 1970 PST + 928 | 8 | 00928 | Thu Jan 29 00:00:00 1970 PST + 929 | 509 | 00929_update9 | Fri Jan 30 00:00:00 1970 PST + 930 | 0 | 00930 | Sat Jan 31 00:00:00 1970 PST + 931 | 1 | 00931 | Sun Feb 01 00:00:00 1970 PST + 933 | 303 | 00933_update3 | Tue Feb 03 00:00:00 1970 PST + 934 | 4 | 00934 | Wed Feb 04 00:00:00 1970 PST + 936 | 6 | 00936 | Fri Feb 06 00:00:00 1970 PST + 937 | 407 | 00937_update7 | Sat Feb 07 00:00:00 1970 PST + 938 | 8 | 00938 | Sun Feb 08 00:00:00 1970 PST + 939 | 509 | 00939_update9 | Mon Feb 09 00:00:00 1970 PST + 940 | 0 | 00940 | Tue Feb 10 00:00:00 1970 PST + 941 | 1 | 00941 | Wed Feb 11 00:00:00 1970 PST + 943 | 303 | 00943_update3 | Fri Feb 13 00:00:00 1970 PST + 944 | 4 | 00944 | Sat Feb 14 00:00:00 1970 PST + 946 | 6 | 00946 | Mon Feb 16 00:00:00 1970 PST + 947 | 407 | 00947_update7 | Tue Feb 17 00:00:00 1970 PST + 948 | 8 | 00948 | Wed Feb 18 00:00:00 1970 PST + 949 | 509 | 00949_update9 | Thu Feb 19 00:00:00 1970 PST + 950 | 0 | 00950 | Fri Feb 20 00:00:00 1970 PST + 951 | 1 | 00951 | Sat Feb 21 00:00:00 1970 PST + 953 | 303 | 00953_update3 | Mon Feb 23 00:00:00 1970 PST + 954 | 4 | 00954 | Tue Feb 24 00:00:00 1970 PST + 956 | 6 | 00956 | Thu Feb 26 00:00:00 1970 PST + 957 | 407 | 00957_update7 | Fri Feb 27 00:00:00 1970 PST + 958 | 8 | 00958 | Sat Feb 28 00:00:00 1970 PST + 959 | 509 | 00959_update9 | Sun Mar 01 00:00:00 1970 PST + 960 | 0 | 00960 | Mon Mar 02 00:00:00 1970 PST + 961 | 1 | 00961 | Tue Mar 03 00:00:00 1970 PST + 963 | 303 | 00963_update3 | Thu Mar 05 00:00:00 1970 PST + 964 | 4 | 00964 | Fri Mar 06 00:00:00 1970 PST + 966 | 6 | 00966 | Sun Mar 08 00:00:00 1970 PST + 967 | 407 | 00967_update7 | Mon Mar 09 00:00:00 1970 PST + 968 | 8 | 00968 | Tue Mar 10 00:00:00 1970 PST + 969 | 509 | 00969_update9 | Wed Mar 11 00:00:00 1970 PST + 970 | 0 | 00970 | Thu Mar 12 00:00:00 1970 PST + 971 | 1 | 00971 | Fri Mar 13 00:00:00 1970 PST + 973 | 303 | 00973_update3 | Sun Mar 15 00:00:00 1970 PST + 974 | 4 | 00974 | Mon Mar 16 00:00:00 1970 PST + 976 | 6 | 00976 | Wed Mar 18 00:00:00 1970 PST + 977 | 407 | 00977_update7 | Thu Mar 19 00:00:00 1970 PST + 978 | 8 | 00978 | Fri Mar 20 00:00:00 1970 PST + 979 | 509 | 00979_update9 | Sat Mar 21 00:00:00 1970 PST + 980 | 0 | 00980 | Sun Mar 22 00:00:00 1970 PST + 981 | 1 | 00981 | Mon Mar 23 00:00:00 1970 PST + 983 | 303 | 00983_update3 | Wed Mar 25 00:00:00 1970 PST + 984 | 4 | 00984 | Thu Mar 26 00:00:00 1970 PST + 986 | 6 | 00986 | Sat Mar 28 00:00:00 1970 PST + 987 | 407 | 00987_update7 | Sun Mar 29 00:00:00 1970 PST + 988 | 8 | 00988 | Mon Mar 30 00:00:00 1970 PST + 989 | 509 | 00989_update9 | Tue Mar 31 00:00:00 1970 PST + 990 | 0 | 00990 | Wed Apr 01 00:00:00 1970 PST + 991 | 1 | 00991 | Thu Apr 02 00:00:00 1970 PST + 993 | 303 | 00993_update3 | Sat Apr 04 00:00:00 1970 PST + 994 | 4 | 00994 | Sun Apr 05 00:00:00 1970 PST + 996 | 6 | 00996 | Tue Apr 07 00:00:00 1970 PST + 997 | 407 | 00997_update7 | Wed Apr 08 00:00:00 1970 PST + 998 | 8 | 00998 | Thu Apr 09 00:00:00 1970 PST + 999 | 509 | 00999_update9 | Fri Apr 10 00:00:00 1970 PST + 1000 | 0 | 01000 | Thu Jan 01 00:00:00 1970 PST + 1001 | 101 | 0000100001 | + 1003 | 403 | 0000300003_update3 | + 1004 | 104 | 0000400004 | + 1006 | 106 | 0000600006 | + 1007 | 507 | 0000700007_update7 | + 1008 | 108 | 0000800008 | + 1009 | 609 | 0000900009_update9 | + 1010 | 100 | 0001000010 | + 1011 | 101 | 0001100011 | + 1013 | 403 | 0001300013_update3 | + 1014 | 104 | 0001400014 | + 1016 | 106 | 0001600016 | + 1017 | 507 | 0001700017_update7 | + 1018 | 108 | 0001800018 | + 1019 | 609 | 0001900019_update9 | + 1020 | 100 | 0002000020 | + 1101 | 201 | aaa | + 1103 | 503 | ccc_update3 | + 1104 | 204 | ddd | +(819 rows) + +-- Test that defaults and triggers on remote table work as expected +ALTER TABLE "S 1"."T 1" ALTER c6 SET DEFAULT '(^-^;)'; +CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$ +BEGIN + NEW.c3 = NEW.c3 || '_trig_update'; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; +CREATE TRIGGER t1_br_insert BEFORE INSERT OR UPDATE + ON "S 1"."T 1" FOR EACH ROW EXECUTE PROCEDURE "S 1".F_BRTRIG(); +INSERT INTO ft2 (c1,c2,c3) VALUES (1208, 218, 'fff') RETURNING *; + c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 +------+-----+-----------------+----+----+--------+----+---- + 1208 | 218 | fff_trig_update | | | (^-^;) | | +(1 row) + +INSERT INTO ft2 (c1,c2,c3,c6) VALUES (1218, 218, 'ggg', '(--;') RETURNING *; + c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 +------+-----+-----------------+----+----+------+----+---- + 1218 | 218 | ggg_trig_update | | | (--; | | +(1 row) + +UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 RETURNING *; + c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 +------+-----+-----------------------------+------------------------------+--------------------------+--------+------------+----- + 8 | 608 | 00008_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo + 18 | 608 | 00018_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo + 28 | 608 | 00028_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo + 38 | 608 | 00038_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo + 48 | 608 | 00048_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo + 58 | 608 | 00058_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo + 68 | 608 | 00068_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo + 78 | 608 | 00078_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo + 88 | 608 | 00088_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo + 98 | 608 | 00098_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo + 108 | 608 | 00108_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo + 118 | 608 | 00118_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo + 128 | 608 | 00128_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo + 138 | 608 | 00138_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo + 148 | 608 | 00148_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo + 158 | 608 | 00158_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo + 168 | 608 | 00168_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo + 178 | 608 | 00178_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo + 188 | 608 | 00188_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo + 198 | 608 | 00198_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo + 208 | 608 | 00208_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo + 218 | 608 | 00218_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo + 228 | 608 | 00228_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo + 238 | 608 | 00238_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo + 248 | 608 | 00248_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo + 258 | 608 | 00258_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo + 268 | 608 | 00268_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo + 278 | 608 | 00278_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo + 288 | 608 | 00288_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo + 298 | 608 | 00298_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo + 308 | 608 | 00308_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo + 318 | 608 | 00318_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo + 328 | 608 | 00328_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo + 338 | 608 | 00338_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo + 348 | 608 | 00348_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo + 358 | 608 | 00358_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo + 368 | 608 | 00368_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo + 378 | 608 | 00378_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo + 388 | 608 | 00388_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo + 398 | 608 | 00398_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo + 408 | 608 | 00408_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo + 418 | 608 | 00418_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo + 428 | 608 | 00428_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo + 438 | 608 | 00438_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo + 448 | 608 | 00448_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo + 458 | 608 | 00458_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo + 468 | 608 | 00468_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo + 478 | 608 | 00478_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo + 488 | 608 | 00488_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo + 498 | 608 | 00498_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo + 508 | 608 | 00508_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo + 518 | 608 | 00518_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo + 528 | 608 | 00528_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo + 538 | 608 | 00538_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo + 548 | 608 | 00548_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo + 558 | 608 | 00558_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo + 568 | 608 | 00568_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo + 578 | 608 | 00578_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo + 588 | 608 | 00588_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo + 598 | 608 | 00598_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo + 608 | 608 | 00608_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo + 618 | 608 | 00618_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo + 628 | 608 | 00628_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo + 638 | 608 | 00638_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo + 648 | 608 | 00648_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo + 658 | 608 | 00658_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo + 668 | 608 | 00668_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo + 678 | 608 | 00678_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo + 688 | 608 | 00688_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo + 698 | 608 | 00698_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo + 708 | 608 | 00708_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo + 718 | 608 | 00718_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo + 728 | 608 | 00728_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo + 738 | 608 | 00738_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo + 748 | 608 | 00748_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo + 758 | 608 | 00758_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo + 768 | 608 | 00768_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo + 778 | 608 | 00778_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo + 788 | 608 | 00788_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo + 798 | 608 | 00798_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo + 808 | 608 | 00808_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo + 818 | 608 | 00818_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo + 828 | 608 | 00828_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo + 838 | 608 | 00838_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo + 848 | 608 | 00848_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo + 858 | 608 | 00858_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo + 868 | 608 | 00868_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo + 878 | 608 | 00878_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo + 888 | 608 | 00888_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo + 898 | 608 | 00898_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo + 908 | 608 | 00908_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo + 918 | 608 | 00918_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo + 928 | 608 | 00928_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo + 938 | 608 | 00938_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo + 948 | 608 | 00948_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo + 958 | 608 | 00958_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo + 968 | 608 | 00968_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo + 978 | 608 | 00978_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo + 988 | 608 | 00988_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo + 998 | 608 | 00998_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo + 1008 | 708 | 0000800008_trig_update | | | | | + 1018 | 708 | 0001800018_trig_update | | | | | + 1208 | 818 | fff_trig_update_trig_update | | | (^-^;) | | + 1218 | 818 | ggg_trig_update_trig_update | | | (--; | | +(104 rows) + +-- Test errors thrown on remote side during update +ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0); +INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key +ERROR: duplicate key value violates unique constraint "t1_pkey" +DETAIL: Key ("C 1")=(11) already exists. +CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2) VALUES ($1, $2) +INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive +ERROR: new row for relation "T 1" violates check constraint "c2positive" +DETAIL: Failing row contains (1111, -2, null, null, null, (^-^;), null, null). +CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2) VALUES ($1, $2) +UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive +ERROR: new row for relation "T 1" violates check constraint "c2positive" +DETAIL: Failing row contains (1, -1, 00001_trig_update, 1970-01-02 03:00:00-05, 1970-01-02 00:00:00, 1, 1 , foo). +CONTEXT: Remote SQL command: UPDATE "S 1"."T 1" SET c2 = $2 WHERE ctid = $1 +-- Test savepoint/rollback behavior +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; + c2 | count +-----+------- + 0 | 100 + 1 | 100 + 4 | 100 + 6 | 100 + 100 | 2 + 101 | 2 + 104 | 2 + 106 | 2 + 201 | 1 + 204 | 1 + 303 | 100 + 403 | 2 + 407 | 100 +(13 rows) + +select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; + c2 | count +-----+------- + 0 | 100 + 1 | 100 + 4 | 100 + 6 | 100 + 100 | 2 + 101 | 2 + 104 | 2 + 106 | 2 + 201 | 1 + 204 | 1 + 303 | 100 + 403 | 2 + 407 | 100 +(13 rows) + +begin; +update ft2 set c2 = 42 where c2 = 0; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; + c2 | count +-----+------- + 1 | 100 + 4 | 100 + 6 | 100 + 42 | 100 + 100 | 2 + 101 | 2 + 104 | 2 + 106 | 2 + 201 | 1 + 204 | 1 + 303 | 100 + 403 | 2 + 407 | 100 +(13 rows) + +savepoint s1; +update ft2 set c2 = 44 where c2 = 4; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; + c2 | count +-----+------- + 1 | 100 + 6 | 100 + 42 | 100 + 44 | 100 + 100 | 2 + 101 | 2 + 104 | 2 + 106 | 2 + 201 | 1 + 204 | 1 + 303 | 100 + 403 | 2 + 407 | 100 +(13 rows) + +release savepoint s1; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; + c2 | count +-----+------- + 1 | 100 + 6 | 100 + 42 | 100 + 44 | 100 + 100 | 2 + 101 | 2 + 104 | 2 + 106 | 2 + 201 | 1 + 204 | 1 + 303 | 100 + 403 | 2 + 407 | 100 +(13 rows) + +savepoint s2; +update ft2 set c2 = 46 where c2 = 6; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; + c2 | count +-----+------- + 1 | 100 + 42 | 100 + 44 | 100 + 46 | 100 + 100 | 2 + 101 | 2 + 104 | 2 + 106 | 2 + 201 | 1 + 204 | 1 + 303 | 100 + 403 | 2 + 407 | 100 +(13 rows) + +rollback to savepoint s2; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; + c2 | count +-----+------- + 1 | 100 + 6 | 100 + 42 | 100 + 44 | 100 + 100 | 2 + 101 | 2 + 104 | 2 + 106 | 2 + 201 | 1 + 204 | 1 + 303 | 100 + 403 | 2 + 407 | 100 +(13 rows) + +release savepoint s2; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; + c2 | count +-----+------- + 1 | 100 + 6 | 100 + 42 | 100 + 44 | 100 + 100 | 2 + 101 | 2 + 104 | 2 + 106 | 2 + 201 | 1 + 204 | 1 + 303 | 100 + 403 | 2 + 407 | 100 +(13 rows) + +savepoint s3; +update ft2 set c2 = -2 where c2 = 42; -- fail on remote side +ERROR: new row for relation "T 1" violates check constraint "c2positive" +DETAIL: Failing row contains (10, -2, 00010_trig_update_trig_update, 1970-01-11 03:00:00-05, 1970-01-11 00:00:00, 0, 0 , foo). +CONTEXT: Remote SQL command: UPDATE "S 1"."T 1" SET c2 = $2 WHERE ctid = $1 +rollback to savepoint s3; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; + c2 | count +-----+------- + 1 | 100 + 6 | 100 + 42 | 100 + 44 | 100 + 100 | 2 + 101 | 2 + 104 | 2 + 106 | 2 + 201 | 1 + 204 | 1 + 303 | 100 + 403 | 2 + 407 | 100 +(13 rows) + +release savepoint s3; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; + c2 | count +-----+------- + 1 | 100 + 6 | 100 + 42 | 100 + 44 | 100 + 100 | 2 + 101 | 2 + 104 | 2 + 106 | 2 + 201 | 1 + 204 | 1 + 303 | 100 + 403 | 2 + 407 | 100 +(13 rows) + +-- none of the above is committed yet remotely +select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; + c2 | count +-----+------- + 0 | 100 + 1 | 100 + 4 | 100 + 6 | 100 + 100 | 2 + 101 | 2 + 104 | 2 + 106 | 2 + 201 | 1 + 204 | 1 + 303 | 100 + 403 | 2 + 407 | 100 +(13 rows) + +commit; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; + c2 | count +-----+------- + 1 | 100 + 6 | 100 + 42 | 100 + 44 | 100 + 100 | 2 + 101 | 2 + 104 | 2 + 106 | 2 + 201 | 1 + 204 | 1 + 303 | 100 + 403 | 2 + 407 | 100 +(13 rows) + +select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; + c2 | count +-----+------- + 1 | 100 + 6 | 100 + 42 | 100 + 44 | 100 + 100 | 2 + 101 | 2 + 104 | 2 + 106 | 2 + 201 | 1 + 204 | 1 + 303 | 100 + 403 | 2 + 407 | 100 +(13 rows) + diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index a46597f02ea..a6db061d603 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -15,16 +15,21 @@ #include "postgres_fdw.h" #include "access/htup_details.h" +#include "access/sysattr.h" #include "commands/defrem.h" #include "commands/explain.h" #include "commands/vacuum.h" #include "foreign/fdwapi.h" #include "funcapi.h" #include "miscadmin.h" +#include "nodes/makefuncs.h" #include "optimizer/cost.h" #include "optimizer/pathnode.h" #include "optimizer/planmain.h" +#include "optimizer/prep.h" +#include "optimizer/var.h" #include "parser/parsetree.h" +#include "utils/builtins.h" #include "utils/lsyscache.h" #include "utils/memutils.h" @@ -58,7 +63,7 @@ typedef struct PgFdwRelationInfo } PgFdwRelationInfo; /* - * Indexes of FDW-private information stored in fdw_private list. + * Indexes of FDW-private information stored in fdw_private lists. * * We store various information in ForeignScan.fdw_private to pass it from * planner to executor. Specifically there is: @@ -66,26 +71,41 @@ typedef struct PgFdwRelationInfo * 1) SELECT statement text to be sent to the remote server * 2) IDs of PARAM_EXEC Params used in the SELECT statement * - * These items are indexed with the enum FdwPrivateIndex, so an item can be - * fetched with list_nth(). For example, to get the SELECT statement: - * sql = strVal(list_nth(fdw_private, FdwPrivateSelectSql)); + * These items are indexed with the enum FdwScanPrivateIndex, so an item + * can be fetched with list_nth(). For example, to get the SELECT statement: + * sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql)); */ -enum FdwPrivateIndex +enum FdwScanPrivateIndex { /* SQL statement to execute remotely (as a String node) */ - FdwPrivateSelectSql, - + FdwScanPrivateSelectSql, /* Integer list of param IDs of PARAM_EXEC Params used in SQL stmt */ - FdwPrivateExternParamIds, + FdwScanPrivateExternParamIds +}; - /* # of elements stored in the list fdw_private */ - FdwPrivateNum +/* + * Similarly, this enum describes what's kept in the fdw_private list for + * a ModifyTable node referencing a postgres_fdw foreign table. We store: + * + * 1) INSERT/UPDATE/DELETE statement text to be sent to the remote server + * 2) Integer list of target attribute numbers for INSERT/UPDATE + * (NIL for a DELETE) + * 3) Boolean flag showing if there's a RETURNING clause + */ +enum FdwModifyPrivateIndex +{ + /* SQL statement to execute remotely (as a String node) */ + FdwModifyPrivateUpdateSql, + /* Integer list of target attribute numbers for INSERT/UPDATE */ + FdwModifyPrivateTargetAttnums, + /* has-returning flag (as an integer Value node) */ + FdwModifyPrivateHasReturning }; /* * Execution state of a foreign scan using postgres_fdw. */ -typedef struct PgFdwExecutionState +typedef struct PgFdwScanState { Relation rel; /* relcache entry for the foreign table */ AttInMetadata *attinmeta; /* attribute datatype conversion metadata */ @@ -113,7 +133,33 @@ typedef struct PgFdwExecutionState /* working memory contexts */ MemoryContext batch_cxt; /* context holding current batch of tuples */ MemoryContext temp_cxt; /* context for per-tuple temporary data */ -} PgFdwExecutionState; +} PgFdwScanState; + +/* + * Execution state of a foreign insert/update/delete operation. + */ +typedef struct PgFdwModifyState +{ + Relation rel; /* relcache entry for the foreign table */ + AttInMetadata *attinmeta; /* attribute datatype conversion metadata */ + + /* for remote query execution */ + PGconn *conn; /* connection for the scan */ + char *p_name; /* name of prepared statement, if created */ + + /* extracted fdw_private data */ + char *query; /* text of INSERT/UPDATE/DELETE command */ + List *target_attrs; /* list of target attribute numbers */ + bool has_returning; /* is there a RETURNING clause? */ + + /* info about parameters for prepared statement */ + AttrNumber ctidAttno; /* attnum of input resjunk ctid column */ + int p_nums; /* number of parameters to transmit */ + FmgrInfo *p_flinfo; /* output conversion functions for them */ + + /* working memory context */ + MemoryContext temp_cxt; /* context for per-tuple temporary data */ +} PgFdwModifyState; /* * Workspace for analyzing a foreign table. @@ -169,12 +215,43 @@ static ForeignScan *postgresGetForeignPlan(PlannerInfo *root, ForeignPath *best_path, List *tlist, List *scan_clauses); -static void postgresExplainForeignScan(ForeignScanState *node, - ExplainState *es); static void postgresBeginForeignScan(ForeignScanState *node, int eflags); static TupleTableSlot *postgresIterateForeignScan(ForeignScanState *node); static void postgresReScanForeignScan(ForeignScanState *node); static void postgresEndForeignScan(ForeignScanState *node); +static void postgresAddForeignUpdateTargets(Query *parsetree, + RangeTblEntry *target_rte, + Relation target_relation); +static List *postgresPlanForeignModify(PlannerInfo *root, + ModifyTable *plan, + Index resultRelation, + int subplan_index); +static void postgresBeginForeignModify(ModifyTableState *mtstate, + ResultRelInfo *resultRelInfo, + List *fdw_private, + int subplan_index, + int eflags); +static TupleTableSlot *postgresExecForeignInsert(EState *estate, + ResultRelInfo *resultRelInfo, + TupleTableSlot *slot, + TupleTableSlot *planSlot); +static TupleTableSlot *postgresExecForeignUpdate(EState *estate, + ResultRelInfo *resultRelInfo, + TupleTableSlot *slot, + TupleTableSlot *planSlot); +static TupleTableSlot *postgresExecForeignDelete(EState *estate, + ResultRelInfo *resultRelInfo, + TupleTableSlot *slot, + TupleTableSlot *planSlot); +static void postgresEndForeignModify(EState *estate, + ResultRelInfo *resultRelInfo); +static void postgresExplainForeignScan(ForeignScanState *node, + ExplainState *es); +static void postgresExplainForeignModify(ModifyTableState *mtstate, + ResultRelInfo *rinfo, + List *fdw_private, + int subplan_index, + ExplainState *es); static bool postgresAnalyzeForeignTable(Relation relation, AcquireSampleRowsFunc *func, BlockNumber *totalpages); @@ -191,6 +268,12 @@ static void get_remote_estimate(const char *sql, static void create_cursor(ForeignScanState *node); static void fetch_more_data(ForeignScanState *node); static void close_cursor(PGconn *conn, unsigned int cursor_number); +static void prepare_foreign_modify(PgFdwModifyState *fmstate); +static const char **convert_prep_stmt_params(PgFdwModifyState *fmstate, + ItemPointer tupleid, + TupleTableSlot *slot); +static void store_returning_result(PgFdwModifyState *fmstate, + TupleTableSlot *slot, PGresult *res); static int postgresAcquireSampleRowsFunc(Relation relation, int elevel, HeapTuple *rows, int targrows, double *totalrows, @@ -214,17 +297,29 @@ postgres_fdw_handler(PG_FUNCTION_ARGS) { FdwRoutine *routine = makeNode(FdwRoutine); - /* Required handler functions. */ + /* Functions for scanning foreign tables */ routine->GetForeignRelSize = postgresGetForeignRelSize; routine->GetForeignPaths = postgresGetForeignPaths; routine->GetForeignPlan = postgresGetForeignPlan; - routine->ExplainForeignScan = postgresExplainForeignScan; routine->BeginForeignScan = postgresBeginForeignScan; routine->IterateForeignScan = postgresIterateForeignScan; routine->ReScanForeignScan = postgresReScanForeignScan; routine->EndForeignScan = postgresEndForeignScan; - /* Optional handler functions. */ + /* Functions for updating foreign tables */ + routine->AddForeignUpdateTargets = postgresAddForeignUpdateTargets; + routine->PlanForeignModify = postgresPlanForeignModify; + routine->BeginForeignModify = postgresBeginForeignModify; + routine->ExecForeignInsert = postgresExecForeignInsert; + routine->ExecForeignUpdate = postgresExecForeignUpdate; + routine->ExecForeignDelete = postgresExecForeignDelete; + routine->EndForeignModify = postgresEndForeignModify; + + /* Support functions for EXPLAIN */ + routine->ExplainForeignScan = postgresExplainForeignScan; + routine->ExplainForeignModify = postgresExplainForeignModify; + + /* Support functions for ANALYZE */ routine->AnalyzeForeignTable = postgresAnalyzeForeignTable; PG_RETURN_POINTER(routine); @@ -249,7 +344,6 @@ postgresGetForeignRelSize(PlannerInfo *root, Oid foreigntableid) { bool use_remote_estimate = false; - ListCell *lc; PgFdwRelationInfo *fpinfo; StringInfo sql; ForeignTable *table; @@ -266,12 +360,14 @@ postgresGetForeignRelSize(PlannerInfo *root, List *param_conds; List *local_conds; List *param_numbers; + Bitmapset *attrs_used; + ListCell *lc; /* * We use PgFdwRelationInfo to pass various information to subsequent * functions. */ - fpinfo = palloc0(sizeof(PgFdwRelationInfo)); + fpinfo = (PgFdwRelationInfo *) palloc0(sizeof(PgFdwRelationInfo)); initStringInfo(&fpinfo->sql); sql = &fpinfo->sql; @@ -303,16 +399,37 @@ postgresGetForeignRelSize(PlannerInfo *root, } /* - * Construct remote query which consists of SELECT, FROM, and WHERE - * clauses. Conditions which contain any Param node are excluded because - * placeholder can't be used in EXPLAIN statement. Such conditions are - * appended later. + * Identify which restriction clauses can be sent to the remote server and + * which can't. Conditions that are remotely executable but contain + * PARAM_EXTERN Params have to be treated separately because we can't use + * placeholders in remote EXPLAIN. */ classifyConditions(root, baserel, &remote_conds, ¶m_conds, &local_conds, ¶m_numbers); - deparseSimpleSql(sql, root, baserel, local_conds); - if (list_length(remote_conds) > 0) - appendWhereClause(sql, true, remote_conds, root); + + /* + * Identify which attributes will need to be retrieved from the remote + * server. These include all attrs needed for joins or final output, plus + * all attrs used in the local_conds. + */ + attrs_used = NULL; + pull_varattnos((Node *) baserel->reltargetlist, baserel->relid, + &attrs_used); + foreach(lc, local_conds) + { + RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc); + + pull_varattnos((Node *) rinfo->clause, baserel->relid, + &attrs_used); + } + + /* + * Construct remote query which consists of SELECT, FROM, and WHERE + * clauses. For now, leave out the param_conds. + */ + deparseSelectSql(sql, root, baserel, attrs_used); + if (remote_conds) + appendWhereClause(sql, root, remote_conds, true); /* * If the table or the server is configured to use remote estimates, @@ -336,7 +453,7 @@ postgresGetForeignRelSize(PlannerInfo *root, userid = rte->checkAsUser ? rte->checkAsUser : GetUserId(); user = GetUserMapping(userid, server->serverid); - conn = GetConnection(server, user); + conn = GetConnection(server, user, false); get_remote_estimate(sql->data, conn, &rows, &width, &startup_cost, &total_cost); ReleaseConnection(conn); @@ -403,11 +520,53 @@ postgresGetForeignRelSize(PlannerInfo *root, /* * Finish deparsing remote query by adding conditions which were unusable - * in remote EXPLAIN since they contain Param nodes. + * in remote EXPLAIN because they contain Param nodes. */ - if (list_length(param_conds) > 0) - appendWhereClause(sql, !(list_length(remote_conds) > 0), param_conds, - root); + if (param_conds) + appendWhereClause(sql, root, param_conds, (remote_conds == NIL)); + + /* + * Add FOR UPDATE/SHARE if appropriate. We apply locking during the + * initial row fetch, rather than later on as is done for local tables. + * The extra roundtrips involved in trying to duplicate the local + * semantics exactly don't seem worthwhile (see also comments for + * RowMarkType). + */ + if (baserel->relid == root->parse->resultRelation && + (root->parse->commandType == CMD_UPDATE || + root->parse->commandType == CMD_DELETE)) + { + /* Relation is UPDATE/DELETE target, so use FOR UPDATE */ + appendStringInfo(sql, " FOR UPDATE"); + } + else + { + RowMarkClause *rc = get_parse_rowmark(root->parse, baserel->relid); + + if (rc) + { + /* + * Relation is specified as a FOR UPDATE/SHARE target, so handle + * that. + * + * For now, just ignore any [NO] KEY specification, since (a) it's + * not clear what that means for a remote table that we don't have + * complete information about, and (b) it wouldn't work anyway on + * older remote servers. Likewise, we don't worry about NOWAIT. + */ + switch (rc->strength) + { + case LCS_FORKEYSHARE: + case LCS_FORSHARE: + appendStringInfo(sql, " FOR SHARE"); + break; + case LCS_FORNOKEYUPDATE: + case LCS_FORUPDATE: + appendStringInfo(sql, " FOR UPDATE"); + break; + } + } + } /* * Store obtained information into FDW-private area of RelOptInfo so it's @@ -477,7 +636,7 @@ postgresGetForeignPaths(PlannerInfo *root, /* * Build the fdw_private list that will be available to the executor. - * Items in the list must match enum FdwPrivateIndex, above. + * Items in the list must match enum FdwScanPrivateIndex, above. */ fdw_private = list_make2(makeString(fpinfo->sql.data), fpinfo->param_numbers); @@ -574,24 +733,6 @@ postgresGetForeignPlan(PlannerInfo *root, } /* - * postgresExplainForeignScan - * Produce extra output for EXPLAIN - */ -static void -postgresExplainForeignScan(ForeignScanState *node, ExplainState *es) -{ - List *fdw_private; - char *sql; - - if (es->verbose) - { - fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private; - sql = strVal(list_nth(fdw_private, FdwPrivateSelectSql)); - ExplainPropertyText("Remote SQL", sql, es); - } -} - -/* * postgresBeginForeignScan * Initiate an executor scan of a foreign PostgreSQL table. */ @@ -600,7 +741,7 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags) { ForeignScan *fsplan = (ForeignScan *) node->ss.ps.plan; EState *estate = node->ss.ps.state; - PgFdwExecutionState *festate; + PgFdwScanState *fsstate; RangeTblEntry *rte; Oid userid; ForeignTable *table; @@ -619,8 +760,8 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags) /* * We'll save private state in node->fdw_state. */ - festate = (PgFdwExecutionState *) palloc0(sizeof(PgFdwExecutionState)); - node->fdw_state = (void *) festate; + fsstate = (PgFdwScanState *) palloc0(sizeof(PgFdwScanState)); + node->fdw_state = (void *) fsstate; /* * Identify which user to do the remote access as. This should match what @@ -630,8 +771,8 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags) userid = rte->checkAsUser ? rte->checkAsUser : GetUserId(); /* Get info about foreign table. */ - festate->rel = node->ss.ss_currentRelation; - table = GetForeignTable(RelationGetRelid(festate->rel)); + fsstate->rel = node->ss.ss_currentRelation; + table = GetForeignTable(RelationGetRelid(fsstate->rel)); server = GetForeignServer(table->serverid); user = GetUserMapping(userid, server->serverid); @@ -639,29 +780,29 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags) * Get connection to the foreign server. Connection manager will * establish new connection if necessary. */ - festate->conn = GetConnection(server, user); + fsstate->conn = GetConnection(server, user, false); /* Assign a unique ID for my cursor */ - festate->cursor_number = GetCursorNumber(festate->conn); - festate->cursor_exists = false; + fsstate->cursor_number = GetCursorNumber(fsstate->conn); + fsstate->cursor_exists = false; /* Get private info created by planner functions. */ - festate->fdw_private = fsplan->fdw_private; + fsstate->fdw_private = fsplan->fdw_private; /* Create contexts for batches of tuples and per-tuple temp workspace. */ - festate->batch_cxt = AllocSetContextCreate(estate->es_query_cxt, + fsstate->batch_cxt = AllocSetContextCreate(estate->es_query_cxt, "postgres_fdw tuple data", ALLOCSET_DEFAULT_MINSIZE, ALLOCSET_DEFAULT_INITSIZE, ALLOCSET_DEFAULT_MAXSIZE); - festate->temp_cxt = AllocSetContextCreate(estate->es_query_cxt, + fsstate->temp_cxt = AllocSetContextCreate(estate->es_query_cxt, "postgres_fdw temporary data", ALLOCSET_SMALL_MINSIZE, ALLOCSET_SMALL_INITSIZE, ALLOCSET_SMALL_MAXSIZE); /* Get info we'll need for data conversion. */ - festate->attinmeta = TupleDescGetAttInMetadata(RelationGetDescr(festate->rel)); + fsstate->attinmeta = TupleDescGetAttInMetadata(RelationGetDescr(fsstate->rel)); /* * Allocate buffer for query parameters, if the remote conditions use any. @@ -673,7 +814,7 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags) * null values that are arbitrarily marked as being of type int4. */ param_numbers = (List *) - list_nth(festate->fdw_private, FdwPrivateExternParamIds); + list_nth(fsstate->fdw_private, FdwScanPrivateExternParamIds); if (param_numbers != NIL) { ParamListInfo params = estate->es_param_list_info; @@ -682,21 +823,21 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags) } else numParams = 0; - festate->numParams = numParams; + fsstate->numParams = numParams; if (numParams > 0) { /* we initially fill all slots with value = NULL, type = int4 */ - festate->param_types = (Oid *) palloc(numParams * sizeof(Oid)); - festate->param_values = (const char **) palloc0(numParams * sizeof(char *)); + fsstate->param_types = (Oid *) palloc(numParams * sizeof(Oid)); + fsstate->param_values = (const char **) palloc0(numParams * sizeof(char *)); for (i = 0; i < numParams; i++) - festate->param_types[i] = INT4OID; + fsstate->param_types[i] = INT4OID; } else { - festate->param_types = NULL; - festate->param_values = NULL; + fsstate->param_types = NULL; + fsstate->param_values = NULL; } - festate->extparams_done = false; + fsstate->extparams_done = false; } /* @@ -707,33 +848,33 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags) static TupleTableSlot * postgresIterateForeignScan(ForeignScanState *node) { - PgFdwExecutionState *festate = (PgFdwExecutionState *) node->fdw_state; + PgFdwScanState *fsstate = (PgFdwScanState *) node->fdw_state; TupleTableSlot *slot = node->ss.ss_ScanTupleSlot; /* * If this is the first call after Begin or ReScan, we need to create the * cursor on the remote side. */ - if (!festate->cursor_exists) + if (!fsstate->cursor_exists) create_cursor(node); /* * Get some more tuples, if we've run out. */ - if (festate->next_tuple >= festate->num_tuples) + if (fsstate->next_tuple >= fsstate->num_tuples) { /* No point in another fetch if we already detected EOF, though. */ - if (!festate->eof_reached) + if (!fsstate->eof_reached) fetch_more_data(node); /* If we didn't get any tuples, must be end of data. */ - if (festate->next_tuple >= festate->num_tuples) + if (fsstate->next_tuple >= fsstate->num_tuples) return ExecClearTuple(slot); } /* * Return the next tuple. */ - ExecStoreTuple(festate->tuples[festate->next_tuple++], + ExecStoreTuple(fsstate->tuples[fsstate->next_tuple++], slot, InvalidBuffer, false); @@ -748,7 +889,7 @@ postgresIterateForeignScan(ForeignScanState *node) static void postgresReScanForeignScan(ForeignScanState *node) { - PgFdwExecutionState *festate = (PgFdwExecutionState *) node->fdw_state; + PgFdwScanState *fsstate = (PgFdwScanState *) node->fdw_state; char sql[64]; PGresult *res; @@ -758,7 +899,7 @@ postgresReScanForeignScan(ForeignScanState *node) */ /* If we haven't created the cursor yet, nothing to do. */ - if (!festate->cursor_exists) + if (!fsstate->cursor_exists) return; /* @@ -769,19 +910,19 @@ postgresReScanForeignScan(ForeignScanState *node) */ if (node->ss.ps.chgParam != NULL) { - festate->cursor_exists = false; + fsstate->cursor_exists = false; snprintf(sql, sizeof(sql), "CLOSE c%u", - festate->cursor_number); + fsstate->cursor_number); } - else if (festate->fetch_ct_2 > 1) + else if (fsstate->fetch_ct_2 > 1) { snprintf(sql, sizeof(sql), "MOVE BACKWARD ALL IN c%u", - festate->cursor_number); + fsstate->cursor_number); } else { /* Easy: just rescan what we already have in memory, if anything */ - festate->next_tuple = 0; + fsstate->next_tuple = 0; return; } @@ -789,17 +930,17 @@ postgresReScanForeignScan(ForeignScanState *node) * We don't use a PG_TRY block here, so be careful not to throw error * without releasing the PGresult. */ - res = PQexec(festate->conn, sql); + res = PQexec(fsstate->conn, sql); if (PQresultStatus(res) != PGRES_COMMAND_OK) pgfdw_report_error(ERROR, res, true, sql); PQclear(res); /* Now force a fresh FETCH. */ - festate->tuples = NULL; - festate->num_tuples = 0; - festate->next_tuple = 0; - festate->fetch_ct_2 = 0; - festate->eof_reached = false; + fsstate->tuples = NULL; + fsstate->num_tuples = 0; + fsstate->next_tuple = 0; + fsstate->fetch_ct_2 = 0; + fsstate->eof_reached = false; } /* @@ -809,24 +950,530 @@ postgresReScanForeignScan(ForeignScanState *node) static void postgresEndForeignScan(ForeignScanState *node) { - PgFdwExecutionState *festate = (PgFdwExecutionState *) node->fdw_state; + PgFdwScanState *fsstate = (PgFdwScanState *) node->fdw_state; - /* if festate is NULL, we are in EXPLAIN; nothing to do */ - if (festate == NULL) + /* if fsstate is NULL, we are in EXPLAIN; nothing to do */ + if (fsstate == NULL) return; /* Close the cursor if open, to prevent accumulation of cursors */ - if (festate->cursor_exists) - close_cursor(festate->conn, festate->cursor_number); + if (fsstate->cursor_exists) + close_cursor(fsstate->conn, fsstate->cursor_number); /* Release remote connection */ - ReleaseConnection(festate->conn); - festate->conn = NULL; + ReleaseConnection(fsstate->conn); + fsstate->conn = NULL; /* MemoryContexts will be deleted automatically. */ } /* + * postgresAddForeignUpdateTargets + * Add resjunk column(s) needed for update/delete on a foreign table + */ +static void +postgresAddForeignUpdateTargets(Query *parsetree, + RangeTblEntry *target_rte, + Relation target_relation) +{ + Var *var; + const char *attrname; + TargetEntry *tle; + + /* + * In postgres_fdw, what we need is the ctid, same as for a regular table. + */ + + /* Make a Var representing the desired value */ + var = makeVar(parsetree->resultRelation, + SelfItemPointerAttributeNumber, + TIDOID, + -1, + InvalidOid, + 0); + + /* Wrap it in a resjunk TLE with the right name ... */ + attrname = "ctid"; + + tle = makeTargetEntry((Expr *) var, + list_length(parsetree->targetList) + 1, + pstrdup(attrname), + true); + + /* ... and add it to the query's targetlist */ + parsetree->targetList = lappend(parsetree->targetList, tle); +} + +/* + * postgresPlanForeignModify + * Plan an insert/update/delete operation on a foreign table + * + * Note: currently, the plan tree generated for UPDATE/DELETE will always + * include a ForeignScan that retrieves ctids (using SELECT FOR UPDATE) + * and then the ModifyTable node will have to execute individual remote + * UPDATE/DELETE commands. If there are no local conditions or joins + * needed, it'd be better to let the scan node do UPDATE/DELETE RETURNING + * and then do nothing at ModifyTable. Room for future optimization ... + */ +static List * +postgresPlanForeignModify(PlannerInfo *root, + ModifyTable *plan, + Index resultRelation, + int subplan_index) +{ + CmdType operation = plan->operation; + StringInfoData sql; + List *targetAttrs = NIL; + List *returningList = NIL; + + initStringInfo(&sql); + + /* + * Construct a list of the columns that are to be assigned during INSERT + * or UPDATE. We should transmit only these columns, for performance and + * to respect any DEFAULT values the remote side may have for other + * columns. (XXX this will need some re-thinking when we support default + * expressions for foreign tables.) + */ + if (operation == CMD_INSERT || operation == CMD_UPDATE) + { + RangeTblEntry *rte = planner_rt_fetch(resultRelation, root); + Bitmapset *tmpset = bms_copy(rte->modifiedCols); + AttrNumber col; + + while ((col = bms_first_member(tmpset)) >= 0) + { + col += FirstLowInvalidHeapAttributeNumber; + if (col <= InvalidAttrNumber) /* shouldn't happen */ + elog(ERROR, "system-column update is not supported"); + targetAttrs = lappend_int(targetAttrs, col); + } + } + + /* + * Extract the relevant RETURNING list if any. + */ + if (plan->returningLists) + returningList = (List *) list_nth(plan->returningLists, subplan_index); + + /* + * Construct the SQL command string. + */ + switch (operation) + { + case CMD_INSERT: + deparseInsertSql(&sql, root, resultRelation, + targetAttrs, returningList); + break; + case CMD_UPDATE: + deparseUpdateSql(&sql, root, resultRelation, + targetAttrs, returningList); + break; + case CMD_DELETE: + deparseDeleteSql(&sql, root, resultRelation, returningList); + break; + default: + elog(ERROR, "unexpected operation: %d", (int) operation); + break; + } + + /* + * Build the fdw_private list that will be available to the executor. + * Items in the list must match enum FdwModifyPrivateIndex, above. + */ + return list_make3(makeString(sql.data), + targetAttrs, + makeInteger((returningList != NIL))); +} + +/* + * postgresBeginForeignModify + * Begin an insert/update/delete operation on a foreign table + */ +static void +postgresBeginForeignModify(ModifyTableState *mtstate, + ResultRelInfo *resultRelInfo, + List *fdw_private, + int subplan_index, + int eflags) +{ + PgFdwModifyState *fmstate; + EState *estate = mtstate->ps.state; + CmdType operation = mtstate->operation; + Relation rel = resultRelInfo->ri_RelationDesc; + RangeTblEntry *rte; + Oid userid; + ForeignTable *table; + ForeignServer *server; + UserMapping *user; + AttrNumber n_params; + Oid typefnoid; + bool isvarlena; + ListCell *lc; + + /* + * Do nothing in EXPLAIN (no ANALYZE) case. resultRelInfo->ri_FdwState + * stays NULL. + */ + if (eflags & EXEC_FLAG_EXPLAIN_ONLY) + return; + + /* Begin constructing PgFdwModifyState. */ + fmstate = (PgFdwModifyState *) palloc0(sizeof(PgFdwModifyState)); + fmstate->rel = rel; + + /* + * Identify which user to do the remote access as. This should match what + * ExecCheckRTEPerms() does. + */ + rte = rt_fetch(resultRelInfo->ri_RangeTableIndex, estate->es_range_table); + userid = rte->checkAsUser ? rte->checkAsUser : GetUserId(); + + /* Get info about foreign table. */ + table = GetForeignTable(RelationGetRelid(rel)); + server = GetForeignServer(table->serverid); + user = GetUserMapping(userid, server->serverid); + + /* Open connection; report that we'll create a prepared statement. */ + fmstate->conn = GetConnection(server, user, true); + fmstate->p_name = NULL; /* prepared statement not made yet */ + + /* Deconstruct fdw_private data. */ + fmstate->query = strVal(list_nth(fdw_private, + FdwModifyPrivateUpdateSql)); + fmstate->target_attrs = (List *) list_nth(fdw_private, + FdwModifyPrivateTargetAttnums); + fmstate->has_returning = intVal(list_nth(fdw_private, + FdwModifyPrivateHasReturning)); + + /* Create context for per-tuple temp workspace. */ + fmstate->temp_cxt = AllocSetContextCreate(estate->es_query_cxt, + "postgres_fdw temporary data", + ALLOCSET_SMALL_MINSIZE, + ALLOCSET_SMALL_INITSIZE, + ALLOCSET_SMALL_MAXSIZE); + + /* Prepare for input conversion of RETURNING results. */ + if (fmstate->has_returning) + fmstate->attinmeta = TupleDescGetAttInMetadata(RelationGetDescr(rel)); + + /* Prepare for output conversion of parameters used in prepared stmt. */ + n_params = list_length(fmstate->target_attrs) + 1; + fmstate->p_flinfo = (FmgrInfo *) palloc0(sizeof(FmgrInfo) * n_params); + fmstate->p_nums = 0; + + if (operation == CMD_UPDATE || operation == CMD_DELETE) + { + /* Find the ctid resjunk column in the subplan's result */ + Plan *subplan = mtstate->mt_plans[subplan_index]->plan; + + fmstate->ctidAttno = ExecFindJunkAttributeInTlist(subplan->targetlist, + "ctid"); + if (!AttributeNumberIsValid(fmstate->ctidAttno)) + elog(ERROR, "could not find junk ctid column"); + + /* First transmittable parameter will be ctid */ + getTypeOutputInfo(TIDOID, &typefnoid, &isvarlena); + fmgr_info(typefnoid, &fmstate->p_flinfo[fmstate->p_nums]); + fmstate->p_nums++; + } + + if (operation == CMD_INSERT || operation == CMD_UPDATE) + { + /* Set up for remaining transmittable parameters */ + foreach(lc, fmstate->target_attrs) + { + int attnum = lfirst_int(lc); + Form_pg_attribute attr = RelationGetDescr(rel)->attrs[attnum - 1]; + + Assert(!attr->attisdropped); + + getTypeOutputInfo(attr->atttypid, &typefnoid, &isvarlena); + fmgr_info(typefnoid, &fmstate->p_flinfo[fmstate->p_nums]); + fmstate->p_nums++; + } + } + + Assert(fmstate->p_nums <= n_params); + + resultRelInfo->ri_FdwState = fmstate; +} + +/* + * postgresExecForeignInsert + * Insert one row into a foreign table + */ +static TupleTableSlot * +postgresExecForeignInsert(EState *estate, + ResultRelInfo *resultRelInfo, + TupleTableSlot *slot, + TupleTableSlot *planSlot) +{ + PgFdwModifyState *fmstate = (PgFdwModifyState *) resultRelInfo->ri_FdwState; + const char **p_values; + PGresult *res; + int n_rows; + + /* Set up the prepared statement on the remote server, if we didn't yet */ + if (!fmstate->p_name) + prepare_foreign_modify(fmstate); + + /* Convert parameters needed by prepared statement to text form */ + p_values = convert_prep_stmt_params(fmstate, NULL, slot); + + /* + * Execute the prepared statement, and check for success. + * + * We don't use a PG_TRY block here, so be careful not to throw error + * without releasing the PGresult. + */ + res = PQexecPrepared(fmstate->conn, + fmstate->p_name, + fmstate->p_nums, + p_values, + NULL, + NULL, + 0); + if (PQresultStatus(res) != + (fmstate->has_returning ? PGRES_TUPLES_OK : PGRES_COMMAND_OK)) + pgfdw_report_error(ERROR, res, true, fmstate->query); + + /* Check number of rows affected, and fetch RETURNING tuple if any */ + if (fmstate->has_returning) + { + n_rows = PQntuples(res); + if (n_rows > 0) + store_returning_result(fmstate, slot, res); + } + else + n_rows = atoi(PQcmdTuples(res)); + + /* And clean up */ + PQclear(res); + + MemoryContextReset(fmstate->temp_cxt); + + /* Return NULL if nothing was inserted on the remote end */ + return (n_rows > 0) ? slot : NULL; +} + +/* + * postgresExecForeignUpdate + * Update one row in a foreign table + */ +static TupleTableSlot * +postgresExecForeignUpdate(EState *estate, + ResultRelInfo *resultRelInfo, + TupleTableSlot *slot, + TupleTableSlot *planSlot) +{ + PgFdwModifyState *fmstate = (PgFdwModifyState *) resultRelInfo->ri_FdwState; + Datum datum; + bool isNull; + const char **p_values; + PGresult *res; + int n_rows; + + /* Set up the prepared statement on the remote server, if we didn't yet */ + if (!fmstate->p_name) + prepare_foreign_modify(fmstate); + + /* Get the ctid that was passed up as a resjunk column */ + datum = ExecGetJunkAttribute(planSlot, + fmstate->ctidAttno, + &isNull); + /* shouldn't ever get a null result... */ + if (isNull) + elog(ERROR, "ctid is NULL"); + + /* Convert parameters needed by prepared statement to text form */ + p_values = convert_prep_stmt_params(fmstate, + (ItemPointer) DatumGetPointer(datum), + slot); + + /* + * Execute the prepared statement, and check for success. + * + * We don't use a PG_TRY block here, so be careful not to throw error + * without releasing the PGresult. + */ + res = PQexecPrepared(fmstate->conn, + fmstate->p_name, + fmstate->p_nums, + p_values, + NULL, + NULL, + 0); + if (PQresultStatus(res) != + (fmstate->has_returning ? PGRES_TUPLES_OK : PGRES_COMMAND_OK)) + pgfdw_report_error(ERROR, res, true, fmstate->query); + + /* Check number of rows affected, and fetch RETURNING tuple if any */ + if (fmstate->has_returning) + { + n_rows = PQntuples(res); + if (n_rows > 0) + store_returning_result(fmstate, slot, res); + } + else + n_rows = atoi(PQcmdTuples(res)); + + /* And clean up */ + PQclear(res); + + MemoryContextReset(fmstate->temp_cxt); + + /* Return NULL if nothing was updated on the remote end */ + return (n_rows > 0) ? slot : NULL; +} + +/* + * postgresExecForeignDelete + * Delete one row from a foreign table + */ +static TupleTableSlot * +postgresExecForeignDelete(EState *estate, + ResultRelInfo *resultRelInfo, + TupleTableSlot *slot, + TupleTableSlot *planSlot) +{ + PgFdwModifyState *fmstate = (PgFdwModifyState *) resultRelInfo->ri_FdwState; + Datum datum; + bool isNull; + const char **p_values; + PGresult *res; + int n_rows; + + /* Set up the prepared statement on the remote server, if we didn't yet */ + if (!fmstate->p_name) + prepare_foreign_modify(fmstate); + + /* Get the ctid that was passed up as a resjunk column */ + datum = ExecGetJunkAttribute(planSlot, + fmstate->ctidAttno, + &isNull); + /* shouldn't ever get a null result... */ + if (isNull) + elog(ERROR, "ctid is NULL"); + + /* Convert parameters needed by prepared statement to text form */ + p_values = convert_prep_stmt_params(fmstate, + (ItemPointer) DatumGetPointer(datum), + NULL); + + /* + * Execute the prepared statement, and check for success. + * + * We don't use a PG_TRY block here, so be careful not to throw error + * without releasing the PGresult. + */ + res = PQexecPrepared(fmstate->conn, + fmstate->p_name, + fmstate->p_nums, + p_values, + NULL, + NULL, + 0); + if (PQresultStatus(res) != + (fmstate->has_returning ? PGRES_TUPLES_OK : PGRES_COMMAND_OK)) + pgfdw_report_error(ERROR, res, true, fmstate->query); + + /* Check number of rows affected, and fetch RETURNING tuple if any */ + if (fmstate->has_returning) + { + n_rows = PQntuples(res); + if (n_rows > 0) + store_returning_result(fmstate, slot, res); + } + else + n_rows = atoi(PQcmdTuples(res)); + + /* And clean up */ + PQclear(res); + + MemoryContextReset(fmstate->temp_cxt); + + /* Return NULL if nothing was deleted on the remote end */ + return (n_rows > 0) ? slot : NULL; +} + +/* + * postgresEndForeignModify + * Finish an insert/update/delete operation on a foreign table + */ +static void +postgresEndForeignModify(EState *estate, + ResultRelInfo *resultRelInfo) +{ + PgFdwModifyState *fmstate = (PgFdwModifyState *) resultRelInfo->ri_FdwState; + + /* If fmstate is NULL, we are in EXPLAIN; nothing to do */ + if (fmstate == NULL) + return; + + /* If we created a prepared statement, destroy it */ + if (fmstate->p_name) + { + char sql[64]; + PGresult *res; + + snprintf(sql, sizeof(sql), "DEALLOCATE %s", fmstate->p_name); + + /* + * We don't use a PG_TRY block here, so be careful not to throw error + * without releasing the PGresult. + */ + res = PQexec(fmstate->conn, sql); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + pgfdw_report_error(ERROR, res, true, sql); + PQclear(res); + fmstate->p_name = NULL; + } + + /* Release remote connection */ + ReleaseConnection(fmstate->conn); + fmstate->conn = NULL; +} + +/* + * postgresExplainForeignScan + * Produce extra output for EXPLAIN of a ForeignScan on a foreign table + */ +static void +postgresExplainForeignScan(ForeignScanState *node, ExplainState *es) +{ + List *fdw_private; + char *sql; + + if (es->verbose) + { + fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private; + sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql)); + ExplainPropertyText("Remote SQL", sql, es); + } +} + +/* + * postgresExplainForeignModify + * Produce extra output for EXPLAIN of a ModifyTable on a foreign table + */ +static void +postgresExplainForeignModify(ModifyTableState *mtstate, + ResultRelInfo *rinfo, + List *fdw_private, + int subplan_index, + ExplainState *es) +{ + if (es->verbose) + { + char *sql = strVal(list_nth(fdw_private, + FdwModifyPrivateUpdateSql)); + + ExplainPropertyText("Remote SQL", sql, es); + } +} + +/* * Estimate costs of executing given SQL statement. */ static void @@ -885,11 +1532,11 @@ get_remote_estimate(const char *sql, PGconn *conn, static void create_cursor(ForeignScanState *node) { - PgFdwExecutionState *festate = (PgFdwExecutionState *) node->fdw_state; - int numParams = festate->numParams; - Oid *types = festate->param_types; - const char **values = festate->param_values; - PGconn *conn = festate->conn; + PgFdwScanState *fsstate = (PgFdwScanState *) node->fdw_state; + int numParams = fsstate->numParams; + Oid *types = fsstate->param_types; + const char **values = fsstate->param_values; + PGconn *conn = fsstate->conn; char *sql; StringInfoData buf; PGresult *res; @@ -904,14 +1551,14 @@ create_cursor(ForeignScanState *node) * recreate the cursor after a rescan, so we could need to re-use the * values anyway. */ - if (numParams > 0 && !festate->extparams_done) + if (numParams > 0 && !fsstate->extparams_done) { ParamListInfo params = node->ss.ps.state->es_param_list_info; List *param_numbers; ListCell *lc; param_numbers = (List *) - list_nth(festate->fdw_private, FdwPrivateExternParamIds); + list_nth(fsstate->fdw_private, FdwScanPrivateExternParamIds); foreach(lc, param_numbers) { int paramno = lfirst_int(lc); @@ -929,8 +1576,8 @@ create_cursor(ForeignScanState *node) * same OIDs we do for the parameters' types. * * We'd not need to pass a type array to PQexecParams at all, - * except that there may be unused holes in the array, which - * will have to be filled with something or the remote server will + * except that there may be unused holes in the array, which will + * have to be filled with something or the remote server will * complain. We arbitrarily set them to INT4OID earlier. */ types[paramno - 1] = InvalidOid; @@ -951,14 +1598,14 @@ create_cursor(ForeignScanState *node) prm->value); } } - festate->extparams_done = true; + fsstate->extparams_done = true; } /* Construct the DECLARE CURSOR command */ - sql = strVal(list_nth(festate->fdw_private, FdwPrivateSelectSql)); + sql = strVal(list_nth(fsstate->fdw_private, FdwScanPrivateSelectSql)); initStringInfo(&buf); appendStringInfo(&buf, "DECLARE c%u CURSOR FOR\n%s", - festate->cursor_number, sql); + fsstate->cursor_number, sql); /* * We don't use a PG_TRY block here, so be careful not to throw error @@ -971,12 +1618,12 @@ create_cursor(ForeignScanState *node) PQclear(res); /* Mark the cursor as created, and show no tuples have been retrieved */ - festate->cursor_exists = true; - festate->tuples = NULL; - festate->num_tuples = 0; - festate->next_tuple = 0; - festate->fetch_ct_2 = 0; - festate->eof_reached = false; + fsstate->cursor_exists = true; + fsstate->tuples = NULL; + fsstate->num_tuples = 0; + fsstate->next_tuple = 0; + fsstate->fetch_ct_2 = 0; + fsstate->eof_reached = false; /* Clean up */ pfree(buf.data); @@ -988,7 +1635,7 @@ create_cursor(ForeignScanState *node) static void fetch_more_data(ForeignScanState *node) { - PgFdwExecutionState *festate = (PgFdwExecutionState *) node->fdw_state; + PgFdwScanState *fsstate = (PgFdwScanState *) node->fdw_state; PGresult *volatile res = NULL; MemoryContext oldcontext; @@ -996,14 +1643,14 @@ fetch_more_data(ForeignScanState *node) * We'll store the tuples in the batch_cxt. First, flush the previous * batch. */ - festate->tuples = NULL; - MemoryContextReset(festate->batch_cxt); - oldcontext = MemoryContextSwitchTo(festate->batch_cxt); + fsstate->tuples = NULL; + MemoryContextReset(fsstate->batch_cxt); + oldcontext = MemoryContextSwitchTo(fsstate->batch_cxt); /* PGresult must be released before leaving this function. */ PG_TRY(); { - PGconn *conn = festate->conn; + PGconn *conn = fsstate->conn; char sql[64]; int fetch_size; int numrows; @@ -1013,36 +1660,36 @@ fetch_more_data(ForeignScanState *node) fetch_size = 100; snprintf(sql, sizeof(sql), "FETCH %d FROM c%u", - fetch_size, festate->cursor_number); + fetch_size, fsstate->cursor_number); res = PQexec(conn, sql); /* On error, report the original query, not the FETCH. */ if (PQresultStatus(res) != PGRES_TUPLES_OK) pgfdw_report_error(ERROR, res, false, - strVal(list_nth(festate->fdw_private, - FdwPrivateSelectSql))); + strVal(list_nth(fsstate->fdw_private, + FdwScanPrivateSelectSql))); /* Convert the data into HeapTuples */ numrows = PQntuples(res); - festate->tuples = (HeapTuple *) palloc0(numrows * sizeof(HeapTuple)); - festate->num_tuples = numrows; - festate->next_tuple = 0; + fsstate->tuples = (HeapTuple *) palloc0(numrows * sizeof(HeapTuple)); + fsstate->num_tuples = numrows; + fsstate->next_tuple = 0; for (i = 0; i < numrows; i++) { - festate->tuples[i] = + fsstate->tuples[i] = make_tuple_from_result_row(res, i, - festate->rel, - festate->attinmeta, - festate->temp_cxt); + fsstate->rel, + fsstate->attinmeta, + fsstate->temp_cxt); } /* Update fetch_ct_2 */ - if (festate->fetch_ct_2 < 2) - festate->fetch_ct_2++; + if (fsstate->fetch_ct_2 < 2) + fsstate->fetch_ct_2++; /* Must be EOF if we didn't get as many tuples as we asked for. */ - festate->eof_reached = (numrows < fetch_size); + fsstate->eof_reached = (numrows < fetch_size); PQclear(res); res = NULL; @@ -1080,6 +1727,136 @@ close_cursor(PGconn *conn, unsigned int cursor_number) } /* + * prepare_foreign_modify + * Establish a prepared statement for execution of INSERT/UPDATE/DELETE + */ +static void +prepare_foreign_modify(PgFdwModifyState *fmstate) +{ + char prep_name[NAMEDATALEN]; + char *p_name; + PGresult *res; + + /* Construct name we'll use for the prepared statement. */ + snprintf(prep_name, sizeof(prep_name), "pgsql_fdw_prep_%u", + GetPrepStmtNumber(fmstate->conn)); + p_name = pstrdup(prep_name); + + /* + * We intentionally do not specify parameter types here, but leave the + * remote server to derive them by default. This avoids possible problems + * with the remote server using different type OIDs than we do. All of + * the prepared statements we use in this module are simple enough that + * the remote server will make the right choices. + * + * We don't use a PG_TRY block here, so be careful not to throw error + * without releasing the PGresult. + */ + res = PQprepare(fmstate->conn, + p_name, + fmstate->query, + 0, + NULL); + + if (PQresultStatus(res) != PGRES_COMMAND_OK) + pgfdw_report_error(ERROR, res, true, fmstate->query); + PQclear(res); + + /* This action shows that the prepare has been done. */ + fmstate->p_name = p_name; +} + +/* + * convert_prep_stmt_params + * Create array of text strings representing parameter values + * + * tupleid is ctid to send, or NULL if none + * slot is slot to get remaining parameters from, or NULL if none + * + * Data is constructed in temp_cxt; caller should reset that after use. + */ +static const char ** +convert_prep_stmt_params(PgFdwModifyState *fmstate, + ItemPointer tupleid, + TupleTableSlot *slot) +{ + const char **p_values; + int pindex = 0; + MemoryContext oldcontext; + + oldcontext = MemoryContextSwitchTo(fmstate->temp_cxt); + + p_values = (const char **) palloc(sizeof(char *) * fmstate->p_nums); + + /* 1st parameter should be ctid, if it's in use */ + if (tupleid != NULL) + { + p_values[pindex] = OutputFunctionCall(&fmstate->p_flinfo[pindex], + PointerGetDatum(tupleid)); + pindex++; + } + + /* get following parameters from slot */ + if (slot != NULL) + { + ListCell *lc; + + foreach(lc, fmstate->target_attrs) + { + int attnum = lfirst_int(lc); + Datum value; + bool isnull; + + value = slot_getattr(slot, attnum, &isnull); + if (isnull) + p_values[pindex] = NULL; + else + p_values[pindex] = OutputFunctionCall(&fmstate->p_flinfo[pindex], + value); + pindex++; + } + } + + Assert(pindex == fmstate->p_nums); + + MemoryContextSwitchTo(oldcontext); + + return p_values; +} + +/* + * store_returning_result + * Store the result of a RETURNING clause + * + * On error, be sure to release the PGresult on the way out. Callers do not + * have PG_TRY blocks to ensure this happens. + */ +static void +store_returning_result(PgFdwModifyState *fmstate, + TupleTableSlot *slot, PGresult *res) +{ + /* PGresult must be released before leaving this function. */ + PG_TRY(); + { + HeapTuple newtup; + + newtup = make_tuple_from_result_row(res, 0, + fmstate->rel, + fmstate->attinmeta, + fmstate->temp_cxt); + /* tuple will be deleted when it is cleared from the slot */ + ExecStoreTuple(newtup, slot, InvalidBuffer, true); + } + PG_CATCH(); + { + if (res) + PQclear(res); + PG_RE_THROW(); + } + PG_END_TRY(); +} + +/* * postgresAnalyzeForeignTable * Test whether analyzing this foreign table is supported */ @@ -1099,7 +1876,7 @@ postgresAnalyzeForeignTable(Relation relation, *func = postgresAcquireSampleRowsFunc; /* - * Now we have to get the number of pages. It's annoying that the ANALYZE + * Now we have to get the number of pages. It's annoying that the ANALYZE * API requires us to return that now, because it forces some duplication * of effort between this routine and postgresAcquireSampleRowsFunc. But * it's probably not worth redefining that API at this point. @@ -1112,7 +1889,7 @@ postgresAnalyzeForeignTable(Relation relation, table = GetForeignTable(RelationGetRelid(relation)); server = GetForeignServer(table->serverid); user = GetUserMapping(relation->rd_rel->relowner, server->serverid); - conn = GetConnection(server, user); + conn = GetConnection(server, user, false); /* * Construct command to get page count for relation. @@ -1204,7 +1981,7 @@ postgresAcquireSampleRowsFunc(Relation relation, int elevel, table = GetForeignTable(RelationGetRelid(relation)); server = GetForeignServer(table->serverid); user = GetUserMapping(relation->rd_rel->relowner, server->serverid); - conn = GetConnection(server, user); + conn = GetConnection(server, user, false); /* * Construct cursor that retrieves whole rows from remote. @@ -1382,6 +2159,7 @@ make_tuple_from_result_row(PGresult *res, Form_pg_attribute *attrs = tupdesc->attrs; Datum *values; bool *nulls; + ItemPointer ctid = NULL; ConversionLocation errpos; ErrorContextCallback errcallback; MemoryContext oldcontext; @@ -1449,6 +2227,21 @@ make_tuple_from_result_row(PGresult *res, j++; } + /* + * Convert ctid if present. XXX we could stand to have a cleaner way of + * detecting whether ctid is included in the result. + */ + if (j < PQnfields(res)) + { + char *valstr; + Datum datum; + + valstr = PQgetvalue(res, row, j); + datum = DirectFunctionCall1(tidin, CStringGetDatum(valstr)); + ctid = (ItemPointer) DatumGetPointer(datum); + j++; + } + /* Uninstall error context callback. */ error_context_stack = errcallback.previous; @@ -1463,6 +2256,9 @@ make_tuple_from_result_row(PGresult *res, tuple = heap_form_tuple(tupdesc, values, nulls); + if (ctid) + tuple->t_self = *ctid; + /* Clean up */ MemoryContextReset(temp_context); diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h index 940b81a7398..236a60b4898 100644 --- a/contrib/postgres_fdw/postgres_fdw.h +++ b/contrib/postgres_fdw/postgres_fdw.h @@ -21,9 +21,11 @@ #include "libpq-fe.h" /* in connection.c */ -extern PGconn *GetConnection(ForeignServer *server, UserMapping *user); +extern PGconn *GetConnection(ForeignServer *server, UserMapping *user, + bool will_prep_stmt); extern void ReleaseConnection(PGconn *conn); extern unsigned int GetCursorNumber(PGconn *conn); +extern unsigned int GetPrepStmtNumber(PGconn *conn); extern void pgfdw_report_error(int elevel, PGresult *res, bool clear, const char *sql); @@ -39,14 +41,20 @@ extern void classifyConditions(PlannerInfo *root, List **param_conds, List **local_conds, List **param_numbers); -extern void deparseSimpleSql(StringInfo buf, +extern void deparseSelectSql(StringInfo buf, PlannerInfo *root, RelOptInfo *baserel, - List *local_conds); + Bitmapset *attrs_used); extern void appendWhereClause(StringInfo buf, - bool has_where, + PlannerInfo *root, List *exprs, - PlannerInfo *root); + bool is_first); +extern void deparseInsertSql(StringInfo buf, PlannerInfo *root, Index rtindex, + List *targetAttrs, List *returningList); +extern void deparseUpdateSql(StringInfo buf, PlannerInfo *root, Index rtindex, + List *targetAttrs, List *returningList); +extern void deparseDeleteSql(StringInfo buf, PlannerInfo *root, Index rtindex, + List *returningList); extern void deparseAnalyzeSizeSql(StringInfo buf, Relation rel); extern void deparseAnalyzeSql(StringInfo buf, Relation rel); diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 0b0231bc306..007109c7c76 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -273,3 +273,77 @@ ROLLBACK TO s; FETCH c; SELECT * FROM ft1 ORDER BY c1 LIMIT 1; COMMIT; + +-- =================================================================== +-- test writable foreign table stuff +-- =================================================================== +EXPLAIN (verbose, costs off) +INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; +INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; +INSERT INTO ft2 (c1,c2,c3) + VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING *; +INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee'); +UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3; +UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *; +EXPLAIN (verbose, costs off) +UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9' + FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; +UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9' + FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; +DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING *; +EXPLAIN (verbose, costs off) +DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; +DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; +SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1; + +-- Test that defaults and triggers on remote table work as expected +ALTER TABLE "S 1"."T 1" ALTER c6 SET DEFAULT '(^-^;)'; +CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$ +BEGIN + NEW.c3 = NEW.c3 || '_trig_update'; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; +CREATE TRIGGER t1_br_insert BEFORE INSERT OR UPDATE + ON "S 1"."T 1" FOR EACH ROW EXECUTE PROCEDURE "S 1".F_BRTRIG(); + +INSERT INTO ft2 (c1,c2,c3) VALUES (1208, 218, 'fff') RETURNING *; +INSERT INTO ft2 (c1,c2,c3,c6) VALUES (1218, 218, 'ggg', '(--;') RETURNING *; +UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 RETURNING *; + +-- Test errors thrown on remote side during update +ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0); + +INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key +INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive +UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive + +-- Test savepoint/rollback behavior +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; +select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; +begin; +update ft2 set c2 = 42 where c2 = 0; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; +savepoint s1; +update ft2 set c2 = 44 where c2 = 4; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; +release savepoint s1; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; +savepoint s2; +update ft2 set c2 = 46 where c2 = 6; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; +rollback to savepoint s2; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; +release savepoint s2; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; +savepoint s3; +update ft2 set c2 = -2 where c2 = 42; -- fail on remote side +rollback to savepoint s3; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; +release savepoint s3; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; +-- none of the above is committed yet remotely +select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; +commit; +select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; +select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 207de9b1259..e9135bffaa5 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3040,36 +3040,41 @@ ANALYZE measurement; Foreign data is accessed with help from a <firstterm>foreign data wrapper</firstterm>. A foreign data wrapper is a library that can communicate with an external data source, hiding the - details of connecting to the data source and fetching data from it. There - is a foreign data wrapper available as a <filename>contrib</> module, - which can read plain data files residing on the server. Other kind of - foreign data wrappers might be found as third party products. If none of - the existing foreign data wrappers suit your needs, you can write your - own; see <xref linkend="fdwhandler">. + details of connecting to the data source and obtaining data from it. + There are some foreign data wrappers available as <filename>contrib</> + modules; see <xref linkend="contrib">. Other kinds of foreign data + wrappers might be found as third party products. If none of the existing + foreign data wrappers suit your needs, you can write your own; see <xref + linkend="fdwhandler">. </para> <para> To access foreign data, you need to create a <firstterm>foreign server</> - object, which defines how to connect to a particular external data source, - according to the set of options used by a particular foreign data + object, which defines how to connect to a particular external data source + according to the set of options used by its supporting foreign data wrapper. Then you need to create one or more <firstterm>foreign tables</firstterm>, which define the structure of the remote data. A foreign table can be used in queries just like a normal table, but a foreign table has no storage in the PostgreSQL server. Whenever it is used, <productname>PostgreSQL</productname> asks the foreign data wrapper - to fetch the data from the external source. + to fetch data from the external source, or transmit data to the external + source in the case of update commands. </para> <para> - Accessing remote data may require authentication at the external + Accessing remote data may require authenticating to the external data source. This information can be provided by a - <firstterm>user mapping</>, which can provide additional options based + <firstterm>user mapping</>, which can provide additional data + such as user names and passwords based on the current <productname>PostgreSQL</productname> role. </para> <para> - Currently, foreign tables are read-only. This limitation may be fixed - in a future release. + For additional information, see + <xref linkend="sql-createforeigndatawrapper">, + <xref linkend="sql-createserver">, + <xref linkend="sql-createusermapping">, and + <xref linkend="sql-createforeigntable">. </para> </sect1> diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml index 638b6ab9ce8..e6bce195e63 100644 --- a/doc/src/sgml/fdwhandler.sgml +++ b/doc/src/sgml/fdwhandler.sgml @@ -13,14 +13,15 @@ wrapper, which consists of a set of functions that the core server calls. The foreign data wrapper is responsible for fetching data from the remote data source and returning it to the - <productname>PostgreSQL</productname> executor. This chapter outlines how - to write a new foreign data wrapper. + <productname>PostgreSQL</productname> executor. If updating foreign + tables is to be supported, the wrapper must handle that, too. + This chapter outlines how to write a new foreign data wrapper. </para> <para> The foreign data wrappers included in the standard distribution are good references when trying to write your own. Look into the - <filename>contrib/file_fdw</> subdirectory of the source tree. + <filename>contrib</> subdirectory of the source tree. The <xref linkend="sql-createforeigndatawrapper"> reference page also has some useful details. </para> @@ -84,10 +85,20 @@ <para> The FDW handler function returns a palloc'd <structname>FdwRoutine</> - struct containing pointers to the following callback functions: + struct containing pointers to the callback functions described below. + The scan-related functions are required, the rest are optional. </para> <para> + The <structname>FdwRoutine</> struct type is declared in + <filename>src/include/foreign/fdwapi.h</>, which see for additional + details. + </para> + + <sect2 id="fdw-callbacks-scan"> + <title>FDW Routines For Scanning Foreign Tables</title> + + <para> <programlisting> void GetForeignRelSize (PlannerInfo *root, @@ -96,7 +107,7 @@ GetForeignRelSize (PlannerInfo *root, </programlisting> Obtain relation size estimates for a foreign table. This is called - at the beginning of planning for a query involving a foreign table. + at the beginning of planning for a query that scans a foreign table. <literal>root</> is the planner's global information about the query; <literal>baserel</> is the planner's information about this table; and <literal>foreigntableid</> is the <structname>pg_class</> OID of the @@ -181,23 +192,6 @@ GetForeignPlan (PlannerInfo *root, <para> <programlisting> void -ExplainForeignScan (ForeignScanState *node, - ExplainState *es); -</programlisting> - - Print additional <command>EXPLAIN</> output for a foreign table scan. - This can just return if there is no need to print anything. - Otherwise, it should call <function>ExplainPropertyText</> and - related functions to add fields to the <command>EXPLAIN</> output. - The flag fields in <literal>es</> can be used to determine what to - print, and the state of the <structname>ForeignScanState</> node - can be inspected to provide run-time statistics in the <command>EXPLAIN - ANALYZE</> case. - </para> - - <para> -<programlisting> -void BeginForeignScan (ForeignScanState *node, int eflags); </programlisting> @@ -212,6 +206,8 @@ BeginForeignScan (ForeignScanState *node, <structname>ForeignScanState</> node (in particular, from the underlying <structname>ForeignScan</> plan node, which contains any FDW-private information provided by <function>GetForeignPlan</>). + <literal>eflags</> contains flag bits describing the executor's + operating mode for this plan node. </para> <para> @@ -246,9 +242,9 @@ IterateForeignScan (ForeignScanState *node); <para> Note that <productname>PostgreSQL</productname>'s executor doesn't care - whether the rows returned violate the <literal>NOT NULL</literal> - constraints which were defined on the foreign table columns - but the - planner does care, and may optimize queries incorrectly if + whether the rows returned violate any <literal>NOT NULL</literal> + constraints that were defined on the foreign table columns — but + the planner does care, and may optimize queries incorrectly if <literal>NULL</> values are present in a column declared not to contain them. If a <literal>NULL</> value is encountered when the user has declared that none should be present, it may be appropriate to raise an @@ -277,6 +273,356 @@ EndForeignScan (ForeignScanState *node); to remote servers should be cleaned up. </para> + </sect2> + + <sect2 id="fdw-callbacks-update"> + <title>FDW Routines For Updating Foreign Tables</title> + + <para> + 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: + </para> + + <para> +<programlisting> +void +AddForeignUpdateTargets (Query *parsetree, + RangeTblEntry *target_rte, + Relation target_relation); +</programlisting> + + <command>UPDATE</> and <command>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 <quote>junk</>, target columns to the list of columns that are to be + retrieved from the foreign table during an <command>UPDATE</> or + <command>DELETE</>. + </para> + + <para> + To do that, add <structname>TargetEntry</> items to + <literal>parsetree->targetList</>, containing expressions for the + extra values to be fetched. Each such entry must be marked + <structfield>resjunk</> = <literal>true</>, and must have a distinct + <structfield>resname</> that will identify it at execution time. + Avoid using names matching <literal>ctid<replaceable>N</></literal> or + <literal>wholerow<replaceable>N</></literal>, as the core system can + generate junk columns of these names. + </para> + + <para> + This function is called in the rewriter, not the planner, so the + information available is a bit different from that available to the + planning routines. + <literal>parsetree</> is the parse tree for the <command>UPDATE</> or + <command>DELETE</> command, while <literal>target_rte</> and + <literal>target_relation</> describe the target foreign table. + </para> + + <para> + If the <function>AddForeignUpdateTargets</> pointer is set to + <literal>NULL</>, no extra target expressions are added. + (This will make it impossible to implement <command>DELETE</> + operations, though <command>UPDATE</> may still be feasible if the FDW + relies on an unchanging primary key to identify rows.) + </para> + + <para> +<programlisting> +List * +PlanForeignModify (PlannerInfo *root, + ModifyTable *plan, + Index resultRelation, + int subplan_index); +</programlisting> + + 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 <structname>ModifyTable</> plan + node that performs the update action. This private information must + have the form of a <literal>List</>, and will be delivered to + <function>BeginForeignModify</> during the execution stage. + </para> + + <para> + <literal>root</> is the planner's global information about the query. + <literal>plan</> is the <structname>ModifyTable</> plan node, which is + complete except for the <structfield>fdwPrivLists</> field. + <literal>resultRelation</> identifies the target foreign table by its + rangetable index. <literal>subplan_index</> identifies which target of + the <structname>ModifyTable</> plan node this is, counting from zero; + use this if you want to index into <literal>node->plans</> or other + substructure of the <literal>plan</> node. + </para> + + <para> + See <xref linkend="fdw-planning"> for additional information. + </para> + + <para> + If the <function>PlanForeignModify</> pointer is set to + <literal>NULL</>, no additional plan-time actions are taken, and the + <literal>fdw_private</> list delivered to + <function>BeginForeignModify</> will be NIL. + </para> + + <para> +<programlisting> +void +BeginForeignModify (ModifyTableState *mtstate, + ResultRelInfo *rinfo, + List *fdw_private, + int subplan_index, + int eflags); +</programlisting> + + 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, + <function>ExecForeignInsert</>, <function>ExecForeignUpdate</> or + <function>ExecForeignDelete</> will be called for each tuple to be + inserted, updated, or deleted. + </para> + + <para> + <literal>mtstate</> is the overall state of the + <structname>ModifyTable</> plan node being executed; global data about + the plan and execution state is available via this structure. + <literal>rinfo</> is the <structname>ResultRelInfo</> struct describing + the target foreign table. (The <structfield>ri_FdwState</> field of + <structname>ResultRelInfo</> is available for the FDW to store any + private state it needs for this operation.) + <literal>fdw_private</> contains the private data generated by + <function>PlanForeignModify</>, if any. + <literal>subplan_index</> identifies which target of + the <structname>ModifyTable</> plan node this is. + <literal>eflags</> contains flag bits describing the executor's + operating mode for this plan node. + </para> + + <para> + Note that when <literal>(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 <function>ExplainForeignModify</> and <function>EndForeignModify</>. + </para> + + <para> + If the <function>BeginForeignModify</> pointer is set to + <literal>NULL</>, no action is taken during executor startup. + </para> + + <para> +<programlisting> +TupleTableSlot * +ExecForeignInsert (EState *estate, + ResultRelInfo *rinfo, + TupleTableSlot *slot, + TupleTableSlot *planSlot); +</programlisting> + + Insert one tuple into the foreign table. + <literal>estate</> is global execution state for the query. + <literal>rinfo</> is the <structname>ResultRelInfo</> struct describing + the target foreign table. + <literal>slot</> contains the tuple to be inserted; it will match the + rowtype definition of the foreign table. + <literal>planSlot</> contains the tuple that was generated by the + <structname>ModifyTable</> plan node's subplan; it differs from + <literal>slot</> in possibly containing additional <quote>junk</> + columns. (The <literal>planSlot</> is typically of little interest + for <command>INSERT</> cases, but is provided for completeness.) + </para> + + <para> + 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 + <literal>slot</> can be re-used for this purpose. + </para> + + <para> + The data in the returned slot is used only if the <command>INSERT</> + query has a <literal>RETURNING</> clause. Hence, the FDW could choose + to optimize away returning some or all columns depending on the contents + of the <literal>RETURNING</> clause. However, some slot must be + returned to indicate success, or the query's reported rowcount will be + wrong. + </para> + + <para> + If the <function>ExecForeignInsert</> pointer is set to + <literal>NULL</>, attempts to insert into the foreign table will fail + with an error message. + </para> + + <para> +<programlisting> +TupleTableSlot * +ExecForeignUpdate (EState *estate, + ResultRelInfo *rinfo, + TupleTableSlot *slot, + TupleTableSlot *planSlot); +</programlisting> + + Update one tuple in the foreign table. + <literal>estate</> is global execution state for the query. + <literal>rinfo</> is the <structname>ResultRelInfo</> struct describing + the target foreign table. + <literal>slot</> contains the new data for the tuple; it will match the + rowtype definition of the foreign table. + <literal>planSlot</> contains the tuple that was generated by the + <structname>ModifyTable</> plan node's subplan; it differs from + <literal>slot</> in possibly containing additional <quote>junk</> + columns. In particular, any junk columns that were requested by + <function>AddForeignUpdateTargets</> will be available from this slot. + </para> + + <para> + 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 + <literal>slot</> can be re-used for this purpose. + </para> + + <para> + The data in the returned slot is used only if the <command>UPDATE</> + query has a <literal>RETURNING</> clause. Hence, the FDW could choose + to optimize away returning some or all columns depending on the contents + of the <literal>RETURNING</> clause. However, some slot must be + returned to indicate success, or the query's reported rowcount will be + wrong. + </para> + + <para> + If the <function>ExecForeignUpdate</> pointer is set to + <literal>NULL</>, attempts to update the foreign table will fail + with an error message. + </para> + + <para> +<programlisting> +TupleTableSlot * +ExecForeignDelete (EState *estate, + ResultRelInfo *rinfo, + TupleTableSlot *slot, + TupleTableSlot *planSlot); +</programlisting> + + Delete one tuple from the foreign table. + <literal>estate</> is global execution state for the query. + <literal>rinfo</> is the <structname>ResultRelInfo</> struct describing + the target foreign table. + <literal>slot</> contains nothing useful upon call, but can be used to + hold the returned tuple. + <literal>planSlot</> contains the tuple that was generated by the + <structname>ModifyTable</> plan node's subplan; in particular, it will + carry any junk columns that were requested by + <function>AddForeignUpdateTargets</>. The junk column(s) must be used + to identify the tuple to be deleted. + </para> + + <para> + 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 <literal>slot</> can be used to hold the tuple to be returned. + </para> + + <para> + The data in the returned slot is used only if the <command>DELETE</> + query has a <literal>RETURNING</> clause. Hence, the FDW could choose + to optimize away returning some or all columns depending on the contents + of the <literal>RETURNING</> clause. However, some slot must be + returned to indicate success, or the query's reported rowcount will be + wrong. + </para> + + <para> + If the <function>ExecForeignDelete</> pointer is set to + <literal>NULL</>, attempts to delete from the foreign table will fail + with an error message. + </para> + + <para> +<programlisting> +void +EndForeignModify (EState *estate, + ResultRelInfo *rinfo); +</programlisting> + + End the table update 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. + </para> + + <para> + If the <function>EndForeignModify</> pointer is set to + <literal>NULL</>, no action is taken during executor shutdown. + </para> + + </sect2> + + <sect2 id="fdw-callbacks-explain"> + <title>FDW Routines for <command>EXPLAIN</></title> + + <para> +<programlisting> +void +ExplainForeignScan (ForeignScanState *node, + ExplainState *es); +</programlisting> + + Print additional <command>EXPLAIN</> output for a foreign table scan. + This function can call <function>ExplainPropertyText</> and + related functions to add fields to the <command>EXPLAIN</> output. + The flag fields in <literal>es</> can be used to determine what to + print, and the state of the <structname>ForeignScanState</> node + can be inspected to provide run-time statistics in the <command>EXPLAIN + ANALYZE</> case. + </para> + + <para> + If the <function>ExplainForeignScan</> pointer is set to + <literal>NULL</>, no additional information is printed during + <command>EXPLAIN</>. + </para> + + <para> +<programlisting> +void +ExplainForeignModify (ModifyTableState *mtstate, + ResultRelInfo *rinfo, + List *fdw_private, + int subplan_index, + struct ExplainState *es); +</programlisting> + + Print additional <command>EXPLAIN</> output for a foreign table update. + This function can call <function>ExplainPropertyText</> and + related functions to add fields to the <command>EXPLAIN</> output. + The flag fields in <literal>es</> can be used to determine what to + print, and the state of the <structname>ModifyTableState</> node + can be inspected to provide run-time statistics in the <command>EXPLAIN + ANALYZE</> case. The first four arguments are the same as for + <function>BeginForeignModify</>. + </para> + + <para> + If the <function>ExplainForeignModify</> pointer is set to + <literal>NULL</>, no additional information is printed during + <command>EXPLAIN</>. + </para> + + </sect2> + + <sect2 id="fdw-callbacks-analyze"> + <title>FDW Routines for <command>ANALYZE</></title> + <para> <programlisting> bool @@ -291,6 +637,9 @@ AnalyzeForeignTable (Relation relation, to a function that will collect sample rows from the table in <parameter>func</>, plus the estimated size of the table in pages in <parameter>totalpages</>. Otherwise, return <literal>false</>. + </para> + + <para> If the FDW does not support collecting statistics for any tables, the <function>AnalyzeForeignTable</> pointer can be set to <literal>NULL</>. </para> @@ -314,11 +663,7 @@ AcquireSampleRowsFunc (Relation relation, int elevel, if the FDW does not have any concept of dead rows.) </para> - <para> - The <structname>FdwRoutine</> struct type is declared in - <filename>src/include/foreign/fdwapi.h</>, which see for additional - details. - </para> + </sect2> </sect1> @@ -432,9 +777,10 @@ GetForeignServerByName(const char *name, bool missing_ok); <para> The FDW callback functions <function>GetForeignRelSize</>, - <function>GetForeignPaths</>, and <function>GetForeignPlan</> must fit - into the workings of the <productname>PostgreSQL</> planner. Here are - some notes about what they must do. + <function>GetForeignPaths</>, <function>GetForeignPlan</>, and + <function>PlanForeignModify</> must fit into the workings of the + <productname>PostgreSQL</> planner. Here are some notes about what + they must do. </para> <para> @@ -546,6 +892,33 @@ GetForeignServerByName(const char *name, bool missing_ok); same as for an ordinary restriction clause. </para> + <para> + When planning an <command>UPDATE</> or <command>DELETE</>, + <function>PlanForeignModify</> can look up the <structname>RelOptInfo</> + struct for the foreign table and make use of the + <literal>baserel->fdw_private</> data previously created by the + scan-planning functions. However, in <command>INSERT</> the target + table is not scanned so there is no <structname>RelOptInfo</> for it. + </para> + + <para> + For an <command>UPDATE</> or <command>DELETE</> against an external data + source that supports concurrent updates, it is recommended that the + <literal>ForeignScan</> operation lock the rows that it fetches, perhaps + via the equivalent of <command>SELECT FOR UPDATE</>. The FDW may also + choose to lock rows at fetch time when the foreign table is referenced + in a <command>SELECT FOR UPDATE/SHARE</>; if it does not, the + <literal>FOR UPDATE</> or <literal>FOR SHARE</> option is essentially a + no-op so far as the foreign table is concerned. This behavior may yield + semantics slightly different from operations on local tables, where row + locking is customarily delayed as long as possible: remote rows may get + locked even though they subsequently fail locally-applied restriction or + join conditions. However, matching the local semantics exactly would + require an additional remote access for every row, and might be + impossible anyway depending on what locking semantics the external data + source provides. + </para> + </sect1> </chapter> diff --git a/doc/src/sgml/file-fdw.sgml b/doc/src/sgml/file-fdw.sgml index 4acb8264a45..8c527612aef 100644 --- a/doc/src/sgml/file-fdw.sgml +++ b/doc/src/sgml/file-fdw.sgml @@ -13,6 +13,7 @@ files in the server's file system. Data files must be in a format that can be read by <command>COPY FROM</command>; see <xref linkend="sql-copy"> for details. + Access to such data files is currently read-only. </para> <para> @@ -160,7 +161,7 @@ <example> <title id="csvlog-fdw">Create a Foreign Table for PostgreSQL CSV Logs</title> - + <para> One of the obvious uses for the <literal>file_fdw</> is to make the PostgreSQL activity log available as a table for querying. To @@ -217,8 +218,8 @@ OPTIONS ( filename '/home/josh/9.1/data/pg_log/pglog.csv', format 'csv' ); </para> <para> - That's it — now you can query your log directly. In production, of course, - you would need to define some way to adjust to log rotation. + That's it — now you can query your log directly. In production, of + course, you would need to define some way to deal with log rotation. </para> </example> diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml index 61b77774aee..61cc2aafc24 100644 --- a/doc/src/sgml/postgres-fdw.sgml +++ b/doc/src/sgml/postgres-fdw.sgml @@ -61,7 +61,10 @@ <para> Now you need only <command>SELECT</> from a foreign table to access - the data stored in its underlying remote table. + the data stored in its underlying remote table. You can also modify + the remote table using <command>INSERT</>, <command>UPDATE</>, or + <command>DELETE</>. (Of course, the remote user you have specified + in your user mapping must have privileges to do these things.) </para> <para> diff --git a/doc/src/sgml/ref/create_foreign_data_wrapper.sgml b/doc/src/sgml/ref/create_foreign_data_wrapper.sgml index d9936e81659..e2d897fb214 100644 --- a/doc/src/sgml/ref/create_foreign_data_wrapper.sgml +++ b/doc/src/sgml/ref/create_foreign_data_wrapper.sgml @@ -117,9 +117,10 @@ CREATE FOREIGN DATA WRAPPER <replaceable class="parameter">name</replaceable> <title>Notes</title> <para> - At the moment, the foreign-data wrapper functionality is rudimentary. - There is no support for updating a foreign table, and optimization of - queries is primitive (and mostly left to the wrapper, too). + <productname>PostgreSQL</>'s foreign-data functionality is still under + active development. Optimization of queries is primitive (and mostly left + to the wrapper, too). Thus, there is considerable room for future + performance improvements. </para> </refsect1> @@ -158,7 +159,7 @@ CREATE FOREIGN DATA WRAPPER mywrapper 9075-9 (SQL/MED), with the exception that the <literal>HANDLER</literal> and <literal>VALIDATOR</literal> clauses are extensions and the standard clauses <literal>LIBRARY</literal> and <literal>LANGUAGE</literal> - are not implemented in PostgreSQL. + are not implemented in <productname>PostgreSQL</>. </para> <para> @@ -175,6 +176,7 @@ CREATE FOREIGN DATA WRAPPER mywrapper <member><xref linkend="sql-dropforeigndatawrapper"></member> <member><xref linkend="sql-createserver"></member> <member><xref linkend="sql-createusermapping"></member> + <member><xref linkend="sql-createforeigntable"></member> </simplelist> </refsect1> diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 4825bca363f..4eb94a43add 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -2121,17 +2121,10 @@ CopyFrom(CopyState cstate) * here that basically duplicated execUtils.c ...) */ resultRelInfo = makeNode(ResultRelInfo); - resultRelInfo->ri_RangeTableIndex = 1; /* dummy */ - resultRelInfo->ri_RelationDesc = cstate->rel; - resultRelInfo->ri_TrigDesc = CopyTriggerDesc(cstate->rel->trigdesc); - if (resultRelInfo->ri_TrigDesc) - { - resultRelInfo->ri_TrigFunctions = (FmgrInfo *) - palloc0(resultRelInfo->ri_TrigDesc->numtriggers * sizeof(FmgrInfo)); - resultRelInfo->ri_TrigWhenExprs = (List **) - palloc0(resultRelInfo->ri_TrigDesc->numtriggers * sizeof(List *)); - } - resultRelInfo->ri_TrigInstrument = NULL; + InitResultRelInfo(resultRelInfo, + cstate->rel, + 1, /* dummy rangetable index */ + 0); ExecOpenIndices(resultRelInfo); diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 989b52da9d4..9799e9ecb41 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -90,6 +90,7 @@ static void ExplainIndexScanDetails(Oid indexid, ScanDirection indexorderdir, static void ExplainScanTarget(Scan *plan, ExplainState *es); static void ExplainModifyTarget(ModifyTable *plan, ExplainState *es); static void ExplainTargetRel(Plan *plan, Index rti, ExplainState *es); +static void show_modifytable_info(ModifyTableState *mtstate, ExplainState *es); static void ExplainMemberNodes(List *plans, PlanState **planstates, List *ancestors, ExplainState *es); static void ExplainSubPlans(List *plans, List *ancestors, @@ -1341,6 +1342,9 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; + case T_ModifyTable: + show_modifytable_info((ModifyTableState *) planstate, es); + break; case T_Hash: show_hash_info((HashState *) planstate, es); break; @@ -1840,7 +1844,8 @@ show_foreignscan_info(ForeignScanState *fsstate, ExplainState *es) FdwRoutine *fdwroutine = fsstate->fdwroutine; /* Let the FDW emit whatever fields it wants */ - fdwroutine->ExplainForeignScan(fsstate, es); + if (fdwroutine->ExplainForeignScan != NULL) + fdwroutine->ExplainForeignScan(fsstate, es); } /* @@ -2037,6 +2042,34 @@ ExplainTargetRel(Plan *plan, Index rti, ExplainState *es) } /* + * Show extra information for a ModifyTable node + */ +static void +show_modifytable_info(ModifyTableState *mtstate, ExplainState *es) +{ + FdwRoutine *fdwroutine = mtstate->resultRelInfo->ri_FdwRoutine; + + /* + * If the first target relation is a foreign table, call its FDW to + * display whatever additional fields it wants to. For now, we ignore the + * possibility of other targets being foreign tables, although the API for + * ExplainForeignModify is designed to allow them to be processed. + */ + if (fdwroutine != NULL && + fdwroutine->ExplainForeignModify != NULL) + { + ModifyTable *node = (ModifyTable *) mtstate->ps.plan; + List *fdw_private = (List *) linitial(node->fdwPrivLists); + + fdwroutine->ExplainForeignModify(mtstate, + mtstate->resultRelInfo, + fdw_private, + 0, + es); + } +} + +/* * Explain the constituent plans of a ModifyTable, Append, MergeAppend, * BitmapAnd, or BitmapOr node. * diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 288b29e44a9..1f2a23bcdd6 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -44,6 +44,7 @@ #include "catalog/namespace.h" #include "commands/trigger.h" #include "executor/execdebug.h" +#include "foreign/fdwapi.h" #include "mb/pg_wchar.h" #include "miscadmin.h" #include "optimizer/clauses.h" @@ -1005,6 +1006,7 @@ void CheckValidResultRel(Relation resultRel, CmdType operation) { TriggerDesc *trigDesc = resultRel->trigdesc; + FdwRoutine *fdwroutine; switch (resultRel->rd_rel->relkind) { @@ -1069,10 +1071,35 @@ CheckValidResultRel(Relation resultRel, CmdType operation) RelationGetRelationName(resultRel)))); break; case RELKIND_FOREIGN_TABLE: - ereport(ERROR, - (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("cannot change foreign table \"%s\"", - RelationGetRelationName(resultRel)))); + /* Okay only if the FDW supports it */ + fdwroutine = GetFdwRoutineForRelation(resultRel, false); + switch (operation) + { + case CMD_INSERT: + if (fdwroutine->ExecForeignInsert == NULL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot insert into foreign table \"%s\"", + RelationGetRelationName(resultRel)))); + break; + case CMD_UPDATE: + if (fdwroutine->ExecForeignUpdate == NULL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot update foreign table \"%s\"", + RelationGetRelationName(resultRel)))); + break; + case CMD_DELETE: + if (fdwroutine->ExecForeignDelete == NULL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot delete from foreign table \"%s\"", + RelationGetRelationName(resultRel)))); + break; + default: + elog(ERROR, "unrecognized CmdType: %d", (int) operation); + break; + } break; default: ereport(ERROR, @@ -1126,7 +1153,7 @@ CheckValidRowMarkRel(Relation rel, RowMarkType markType) RelationGetRelationName(rel)))); break; case RELKIND_FOREIGN_TABLE: - /* Perhaps we can support this someday, but not today */ + /* Should not get here */ ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("cannot lock rows in foreign table \"%s\"", @@ -1180,6 +1207,11 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, resultRelInfo->ri_TrigWhenExprs = NULL; resultRelInfo->ri_TrigInstrument = NULL; } + if (resultRelationDesc->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + resultRelInfo->ri_FdwRoutine = GetFdwRoutineForRelation(resultRelationDesc, true); + else + resultRelInfo->ri_FdwRoutine = NULL; + resultRelInfo->ri_FdwState = NULL; resultRelInfo->ri_ConstraintExprs = NULL; resultRelInfo->ri_junkFilter = NULL; resultRelInfo->ri_projectReturning = NULL; diff --git a/src/backend/executor/nodeForeignscan.c b/src/backend/executor/nodeForeignscan.c index 63478cd12ad..448fd6a912f 100644 --- a/src/backend/executor/nodeForeignscan.c +++ b/src/backend/executor/nodeForeignscan.c @@ -147,7 +147,8 @@ ExecInitForeignScan(ForeignScan *node, EState *estate, int eflags) scanstate->ss.ss_currentRelation = currentRelation; /* - * get the scan type from the relation descriptor. + * get the scan type from the relation descriptor. (XXX at some point we + * might want to let the FDW editorialize on the scan tupdesc.) */ ExecAssignScanType(&scanstate->ss, RelationGetDescr(currentRelation)); diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index cb084d03d47..a6f247e1bc3 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -42,6 +42,7 @@ #include "commands/trigger.h" #include "executor/executor.h" #include "executor/nodeModifyTable.h" +#include "foreign/fdwapi.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" #include "storage/bufmgr.h" @@ -225,6 +226,24 @@ ExecInsert(TupleTableSlot *slot, newId = InvalidOid; } + else if (resultRelInfo->ri_FdwRoutine) + { + /* + * insert into foreign table: let the FDW do it + */ + slot = resultRelInfo->ri_FdwRoutine->ExecForeignInsert(estate, + resultRelInfo, + slot, + planSlot); + + if (slot == NULL) /* "do nothing" */ + return NULL; + + /* FDW might have changed tuple */ + tuple = ExecMaterializeSlot(slot); + + newId = InvalidOid; + } else { /* @@ -279,7 +298,9 @@ ExecInsert(TupleTableSlot *slot, * When deleting from a table, tupleid identifies the tuple to * delete and oldtuple is NULL. When deleting from a view, * oldtuple is passed to the INSTEAD OF triggers and identifies - * what to delete, and tupleid is invalid. + * what to delete, and tupleid is invalid. When deleting from a + * foreign table, both tupleid and oldtuple are NULL; the FDW has + * to figure out which row to delete using data from the planSlot. * * Returns RETURNING result if any, otherwise NULL. * ---------------------------------------------------------------- @@ -296,6 +317,7 @@ ExecDelete(ItemPointer tupleid, Relation resultRelationDesc; HTSU_Result result; HeapUpdateFailureData hufd; + TupleTableSlot *slot = NULL; /* * get information on the (current) result relation @@ -334,6 +356,27 @@ ExecDelete(ItemPointer tupleid, if (!dodelete) /* "do nothing" */ return NULL; } + else if (resultRelInfo->ri_FdwRoutine) + { + /* + * delete from foreign table: let the FDW do it + * + * We offer the trigger tuple slot as a place to store RETURNING data, + * although the FDW can return some other slot if it wants. Set up + * the slot's tupdesc so the FDW doesn't need to do that for itself. + */ + slot = estate->es_trig_tuple_slot; + if (slot->tts_tupleDescriptor != RelationGetDescr(resultRelationDesc)) + ExecSetSlotDescriptor(slot, RelationGetDescr(resultRelationDesc)); + + slot = resultRelInfo->ri_FdwRoutine->ExecForeignDelete(estate, + resultRelInfo, + slot, + planSlot); + + if (slot == NULL) /* "do nothing" */ + return NULL; + } else { /* @@ -443,34 +486,49 @@ ldelete:; * We have to put the target tuple into a slot, which means first we * gotta fetch it. We can use the trigger tuple slot. */ - TupleTableSlot *slot = estate->es_trig_tuple_slot; TupleTableSlot *rslot; HeapTupleData deltuple; Buffer delbuffer; - if (oldtuple != NULL) + if (resultRelInfo->ri_FdwRoutine) { - deltuple.t_data = oldtuple; - deltuple.t_len = HeapTupleHeaderGetDatumLength(oldtuple); - ItemPointerSetInvalid(&(deltuple.t_self)); - deltuple.t_tableOid = InvalidOid; + /* FDW must have provided a slot containing the deleted row */ + Assert(!TupIsNull(slot)); delbuffer = InvalidBuffer; } else { - deltuple.t_self = *tupleid; - if (!heap_fetch(resultRelationDesc, SnapshotAny, - &deltuple, &delbuffer, false, NULL)) - elog(ERROR, "failed to fetch deleted tuple for DELETE RETURNING"); - } + slot = estate->es_trig_tuple_slot; + if (oldtuple != NULL) + { + deltuple.t_data = oldtuple; + deltuple.t_len = HeapTupleHeaderGetDatumLength(oldtuple); + ItemPointerSetInvalid(&(deltuple.t_self)); + deltuple.t_tableOid = InvalidOid; + delbuffer = InvalidBuffer; + } + else + { + deltuple.t_self = *tupleid; + if (!heap_fetch(resultRelationDesc, SnapshotAny, + &deltuple, &delbuffer, false, NULL)) + elog(ERROR, "failed to fetch deleted tuple for DELETE RETURNING"); + } - if (slot->tts_tupleDescriptor != RelationGetDescr(resultRelationDesc)) - ExecSetSlotDescriptor(slot, RelationGetDescr(resultRelationDesc)); - ExecStoreTuple(&deltuple, slot, InvalidBuffer, false); + if (slot->tts_tupleDescriptor != RelationGetDescr(resultRelationDesc)) + ExecSetSlotDescriptor(slot, RelationGetDescr(resultRelationDesc)); + ExecStoreTuple(&deltuple, slot, InvalidBuffer, false); + } rslot = ExecProcessReturning(resultRelInfo->ri_projectReturning, slot, planSlot); + /* + * Before releasing the target tuple again, make sure rslot has a + * local copy of any pass-by-reference values. + */ + ExecMaterializeSlot(rslot); + ExecClearTuple(slot); if (BufferIsValid(delbuffer)) ReleaseBuffer(delbuffer); @@ -494,7 +552,9 @@ ldelete:; * When updating a table, tupleid identifies the tuple to * update and oldtuple is NULL. When updating a view, oldtuple * is passed to the INSTEAD OF triggers and identifies what to - * update, and tupleid is invalid. + * update, and tupleid is invalid. When updating a foreign table, + * both tupleid and oldtuple are NULL; the FDW has to figure out + * which row to update using data from the planSlot. * * Returns RETURNING result if any, otherwise NULL. * ---------------------------------------------------------------- @@ -568,6 +628,22 @@ ExecUpdate(ItemPointer tupleid, /* trigger might have changed tuple */ tuple = ExecMaterializeSlot(slot); } + else if (resultRelInfo->ri_FdwRoutine) + { + /* + * update in foreign table: let the FDW do it + */ + slot = resultRelInfo->ri_FdwRoutine->ExecForeignUpdate(estate, + resultRelInfo, + slot, + planSlot); + + if (slot == NULL) /* "do nothing" */ + return NULL; + + /* FDW might have changed tuple */ + tuple = ExecMaterializeSlot(slot); + } else { LockTupleMode lockmode; @@ -867,10 +943,12 @@ ExecModifyTable(ModifyTableState *node) */ if (operation == CMD_UPDATE || operation == CMD_DELETE) { + char relkind; Datum datum; bool isNull; - if (resultRelInfo->ri_RelationDesc->rd_rel->relkind == RELKIND_RELATION) + relkind = resultRelInfo->ri_RelationDesc->rd_rel->relkind; + if (relkind == RELKIND_RELATION) { datum = ExecGetJunkAttribute(slot, junkfilter->jf_junkAttNo, @@ -884,6 +962,10 @@ ExecModifyTable(ModifyTableState *node) * ctid!! */ tupleid = &tuple_ctid; } + else if (relkind == RELKIND_FOREIGN_TABLE) + { + /* do nothing; FDW must fetch any junk attrs it wants */ + } else { datum = ExecGetJunkAttribute(slot, @@ -1026,6 +1108,19 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) estate->es_result_relation_info = resultRelInfo; mtstate->mt_plans[i] = ExecInitNode(subplan, estate, eflags); + /* Also let FDWs init themselves for foreign-table result rels */ + if (resultRelInfo->ri_FdwRoutine != NULL && + resultRelInfo->ri_FdwRoutine->BeginForeignModify != NULL) + { + List *fdw_private = (List *) list_nth(node->fdwPrivLists, i); + + resultRelInfo->ri_FdwRoutine->BeginForeignModify(mtstate, + resultRelInfo, + fdw_private, + i, + eflags); + } + resultRelInfo++; i++; } @@ -1180,12 +1275,19 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) if (operation == CMD_UPDATE || operation == CMD_DELETE) { /* For UPDATE/DELETE, find the appropriate junk attr now */ - if (resultRelInfo->ri_RelationDesc->rd_rel->relkind == RELKIND_RELATION) + char relkind; + + relkind = resultRelInfo->ri_RelationDesc->rd_rel->relkind; + if (relkind == RELKIND_RELATION) { j->jf_junkAttNo = ExecFindJunkAttribute(j, "ctid"); if (!AttributeNumberIsValid(j->jf_junkAttNo)) elog(ERROR, "could not find junk ctid column"); } + else if (relkind == RELKIND_FOREIGN_TABLE) + { + /* FDW must fetch any junk attrs it wants */ + } else { j->jf_junkAttNo = ExecFindJunkAttribute(j, "wholerow"); @@ -1244,6 +1346,19 @@ ExecEndModifyTable(ModifyTableState *node) int i; /* + * Allow any FDWs to shut down + */ + for (i = 0; i < node->mt_nplans; i++) + { + ResultRelInfo *resultRelInfo = node->resultRelInfo + i; + + if (resultRelInfo->ri_FdwRoutine != NULL && + resultRelInfo->ri_FdwRoutine->EndForeignModify != NULL) + resultRelInfo->ri_FdwRoutine->EndForeignModify(node->ps.state, + resultRelInfo); + } + + /* * Free the exprcontext */ ExecFreeExprContext(&node->ps); diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 867b0c09d90..fd3823a36ee 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -179,6 +179,7 @@ _copyModifyTable(const ModifyTable *from) COPY_SCALAR_FIELD(resultRelIndex); COPY_NODE_FIELD(plans); COPY_NODE_FIELD(returningLists); + COPY_NODE_FIELD(fdwPrivLists); COPY_NODE_FIELD(rowMarks); COPY_SCALAR_FIELD(epqParam); diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index be4e5482816..d8ce5753a4c 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -333,6 +333,7 @@ _outModifyTable(StringInfo str, const ModifyTable *node) WRITE_INT_FIELD(resultRelIndex); WRITE_NODE_FIELD(plans); WRITE_NODE_FIELD(returningLists); + WRITE_NODE_FIELD(fdwPrivLists); WRITE_NODE_FIELD(rowMarks); WRITE_INT_FIELD(epqParam); } diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 8a51b56dde2..d668128ccb1 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -20,6 +20,7 @@ #include <math.h> #include "access/skey.h" +#include "catalog/pg_class.h" #include "foreign/fdwapi.h" #include "miscadmin.h" #include "nodes/makefuncs.h" @@ -4695,7 +4696,8 @@ make_result(PlannerInfo *root, * to make it look better sometime. */ ModifyTable * -make_modifytable(CmdType operation, bool canSetTag, +make_modifytable(PlannerInfo *root, + CmdType operation, bool canSetTag, List *resultRelations, List *subplans, List *returningLists, List *rowMarks, int epqParam) @@ -4703,7 +4705,10 @@ make_modifytable(CmdType operation, bool canSetTag, ModifyTable *node = makeNode(ModifyTable); Plan *plan = &node->plan; double total_size; + List *fdw_private_list; ListCell *subnode; + ListCell *lc; + int i; Assert(list_length(resultRelations) == list_length(subplans)); Assert(returningLists == NIL || @@ -4746,6 +4751,53 @@ make_modifytable(CmdType operation, bool canSetTag, node->rowMarks = rowMarks; node->epqParam = epqParam; + /* + * For each result relation that is a foreign table, allow the FDW to + * construct private plan data, and accumulate it all into a list. + */ + fdw_private_list = NIL; + i = 0; + foreach(lc, resultRelations) + { + Index rti = lfirst_int(lc); + FdwRoutine *fdwroutine; + List *fdw_private; + + /* + * If possible, we want to get the FdwRoutine from our RelOptInfo for + * the table. But sometimes we don't have a RelOptInfo and must get + * it the hard way. (In INSERT, the target relation is not scanned, + * so it's not a baserel; and there are also corner cases for + * updatable views where the target rel isn't a baserel.) + */ + if (rti < root->simple_rel_array_size && + root->simple_rel_array[rti] != NULL) + { + RelOptInfo *resultRel = root->simple_rel_array[rti]; + + fdwroutine = resultRel->fdwroutine; + } + else + { + RangeTblEntry *rte = planner_rt_fetch(rti, root); + + Assert(rte->rtekind == RTE_RELATION); + if (rte->relkind == RELKIND_FOREIGN_TABLE) + fdwroutine = GetFdwRoutineByRelId(rte->relid); + else + fdwroutine = NULL; + } + + if (fdwroutine != NULL && + fdwroutine->PlanForeignModify != NULL) + fdw_private = fdwroutine->PlanForeignModify(root, node, rti, i); + else + fdw_private = NIL; + fdw_private_list = lappend(fdw_private_list, fdw_private); + i++; + } + node->fdwPrivLists = fdw_private_list; + return node; } diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index db3d5c50182..0847e787c39 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -571,7 +571,8 @@ subquery_planner(PlannerGlobal *glob, Query *parse, else rowMarks = root->rowMarks; - plan = (Plan *) make_modifytable(parse->commandType, + plan = (Plan *) make_modifytable(root, + parse->commandType, parse->canSetTag, list_make1_int(parse->resultRelation), list_make1(plan), @@ -964,7 +965,8 @@ inheritance_planner(PlannerInfo *root) rowMarks = root->rowMarks; /* And last, tack on a ModifyTable node to do the UPDATE/DELETE work */ - return (Plan *) make_modifytable(parse->commandType, + return (Plan *) make_modifytable(root, + parse->commandType, parse->canSetTag, resultRelations, subplans, @@ -2035,6 +2037,15 @@ preprocess_rowmarks(PlannerInfo *root) if (rte->rtekind != RTE_RELATION) continue; + /* + * Similarly, ignore RowMarkClauses for foreign tables; foreign tables + * will instead get ROW_MARK_COPY items in the next loop. (FDWs might + * choose to do something special while fetching their rows, but that + * is of no concern here.) + */ + if (rte->relkind == RELKIND_FOREIGN_TABLE) + continue; + rels = bms_del_member(rels, rc->rti); newrc = makeNode(PlanRowMark); diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c index 7b28b5584b9..fb67f9e4447 100644 --- a/src/backend/optimizer/prep/preptlist.c +++ b/src/backend/optimizer/prep/preptlist.c @@ -6,7 +6,8 @@ * For INSERT and UPDATE queries, the targetlist must contain an entry for * each attribute of the target relation in the correct order. For all query * types, we may need to add junk tlist entries for Vars used in the RETURNING - * list and row ID information needed for EvalPlanQual checking. + * list and row ID information needed for SELECT FOR UPDATE locking and/or + * EvalPlanQual checking. * * NOTE: the rewriter's rewriteTargetListIU and rewriteTargetListUD * routines also do preprocessing of the targetlist. The division of labor diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index d34fca54666..2a943f9c6a5 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -2164,7 +2164,7 @@ transformCreateTableAsStmt(ParseState *pstate, CreateTableAsStmt *stmt) /* - * Check for features that are not supported together with FOR [KEY] UPDATE/SHARE. + * Check for features that are not supported with FOR [KEY] UPDATE/SHARE. * * exported so planner can check again after rewriting, query pullup, etc */ @@ -2239,9 +2239,6 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc, switch (rte->rtekind) { case RTE_RELATION: - /* ignore foreign tables */ - if (rte->relkind == RELKIND_FOREIGN_TABLE) - break; applyLockingClause(qry, i, lc->strength, lc->noWait, pushedDown); rte->requiredPerms |= ACL_SELECT_FOR_UPDATE; @@ -2251,7 +2248,7 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc, lc->strength, lc->noWait, pushedDown); /* - * FOR [KEY] UPDATE/SHARE of subquery is propagated to all of + * FOR UPDATE/SHARE of subquery is propagated to all of * subquery's rels, too. We could do this later (based on * the marking of the subquery RTE) but it is convenient * to have local knowledge in each query level about which @@ -2291,12 +2288,6 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc, switch (rte->rtekind) { case RTE_RELATION: - if (rte->relkind == RELKIND_FOREIGN_TABLE) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("row-level locks cannot be used with foreign table \"%s\"", - rte->eref->aliasname), - parser_errposition(pstate, thisrel->location))); applyLockingClause(qry, i, lc->strength, lc->noWait, pushedDown); diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 83c83a6a8a4..b99b9930d6a 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -16,6 +16,7 @@ #include "access/sysattr.h" #include "catalog/pg_type.h" #include "commands/trigger.h" +#include "foreign/fdwapi.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" #include "parser/analyze.h" @@ -1156,6 +1157,7 @@ rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos) * is a regular table, the junk TLE emits the ctid attribute of the original * row. When the target relation is a view, there is no ctid, so we instead * emit a whole-row Var that will contain the "old" values of the view row. + * If it's a foreign table, we let the FDW decide what to add. * * For UPDATE queries, this is applied after rewriteTargetListIU. The * ordering isn't actually critical at the moment. @@ -1183,6 +1185,21 @@ rewriteTargetListUD(Query *parsetree, RangeTblEntry *target_rte, attrname = "ctid"; } + else if (target_relation->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + { + /* + * Let the foreign table's FDW add whatever junk TLEs it wants. + */ + FdwRoutine *fdwroutine; + + fdwroutine = GetFdwRoutineForRelation(target_relation, false); + + if (fdwroutine->AddForeignUpdateTargets != NULL) + fdwroutine->AddForeignUpdateTargets(parsetree, target_rte, + target_relation); + + return; + } else { /* @@ -1444,17 +1461,13 @@ markQueryForLocking(Query *qry, Node *jtnode, if (rte->rtekind == RTE_RELATION) { - /* ignore foreign tables */ - if (rte->relkind != RELKIND_FOREIGN_TABLE) - { - applyLockingClause(qry, rti, strength, noWait, pushedDown); - rte->requiredPerms |= ACL_SELECT_FOR_UPDATE; - } + applyLockingClause(qry, rti, strength, noWait, pushedDown); + rte->requiredPerms |= ACL_SELECT_FOR_UPDATE; } else if (rte->rtekind == RTE_SUBQUERY) { applyLockingClause(qry, rti, strength, noWait, pushedDown); - /* FOR [KEY] UPDATE/SHARE of subquery is propagated to subquery's rels */ + /* FOR UPDATE/SHARE of subquery is propagated to subquery's rels */ markQueryForLocking(rte->subquery, (Node *) rte->subquery->jointree, strength, noWait, true); } diff --git a/src/include/foreign/fdwapi.h b/src/include/foreign/fdwapi.h index 562d5412df7..485eee320f8 100644 --- a/src/include/foreign/fdwapi.h +++ b/src/include/foreign/fdwapi.h @@ -38,9 +38,6 @@ typedef ForeignScan *(*GetForeignPlan_function) (PlannerInfo *root, List *tlist, List *scan_clauses); -typedef void (*ExplainForeignScan_function) (ForeignScanState *node, - struct ExplainState *es); - typedef void (*BeginForeignScan_function) (ForeignScanState *node, int eflags); @@ -50,6 +47,48 @@ typedef void (*ReScanForeignScan_function) (ForeignScanState *node); typedef void (*EndForeignScan_function) (ForeignScanState *node); +typedef void (*AddForeignUpdateTargets_function) (Query *parsetree, + RangeTblEntry *target_rte, + Relation target_relation); + +typedef List *(*PlanForeignModify_function) (PlannerInfo *root, + ModifyTable *plan, + Index resultRelation, + int subplan_index); + +typedef void (*BeginForeignModify_function) (ModifyTableState *mtstate, + ResultRelInfo *rinfo, + List *fdw_private, + int subplan_index, + int eflags); + +typedef TupleTableSlot *(*ExecForeignInsert_function) (EState *estate, + ResultRelInfo *rinfo, + TupleTableSlot *slot, + TupleTableSlot *planSlot); + +typedef TupleTableSlot *(*ExecForeignUpdate_function) (EState *estate, + ResultRelInfo *rinfo, + TupleTableSlot *slot, + TupleTableSlot *planSlot); + +typedef TupleTableSlot *(*ExecForeignDelete_function) (EState *estate, + ResultRelInfo *rinfo, + TupleTableSlot *slot, + TupleTableSlot *planSlot); + +typedef void (*EndForeignModify_function) (EState *estate, + ResultRelInfo *rinfo); + +typedef void (*ExplainForeignScan_function) (ForeignScanState *node, + struct ExplainState *es); + +typedef void (*ExplainForeignModify_function) (ModifyTableState *mtstate, + ResultRelInfo *rinfo, + List *fdw_private, + int subplan_index, + struct ExplainState *es); + typedef int (*AcquireSampleRowsFunc) (Relation relation, int elevel, HeapTuple *rows, int targrows, double *totalrows, @@ -73,22 +112,34 @@ typedef struct FdwRoutine { NodeTag type; - /* - * These functions are required. - */ + /* Functions for scanning foreign tables */ GetForeignRelSize_function GetForeignRelSize; GetForeignPaths_function GetForeignPaths; GetForeignPlan_function GetForeignPlan; - ExplainForeignScan_function ExplainForeignScan; BeginForeignScan_function BeginForeignScan; IterateForeignScan_function IterateForeignScan; ReScanForeignScan_function ReScanForeignScan; EndForeignScan_function EndForeignScan; /* - * These functions are optional. Set the pointer to NULL for any that are - * not provided. + * Remaining functions are optional. Set the pointer to NULL for any that + * are not provided. */ + + /* Functions for updating foreign tables */ + AddForeignUpdateTargets_function AddForeignUpdateTargets; + PlanForeignModify_function PlanForeignModify; + BeginForeignModify_function BeginForeignModify; + ExecForeignInsert_function ExecForeignInsert; + ExecForeignUpdate_function ExecForeignUpdate; + ExecForeignDelete_function ExecForeignDelete; + EndForeignModify_function EndForeignModify; + + /* Support functions for EXPLAIN */ + ExplainForeignScan_function ExplainForeignScan; + ExplainForeignModify_function ExplainForeignModify; + + /* Support functions for ANALYZE */ AnalyzeForeignTable_function AnalyzeForeignTable; } FdwRoutine; diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 76e8cdb1ad8..4f77016652d 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -270,7 +270,8 @@ typedef struct ProjectionInfo * resultSlot: tuple slot used to hold cleaned tuple. * junkAttNo: not used by junkfilter code. Can be used by caller * to remember the attno of a specific junk attribute - * (execMain.c stores the "ctid" attno here). + * (nodeModifyTable.c keeps the "ctid" or "wholerow" + * attno here). * ---------------- */ typedef struct JunkFilter @@ -300,6 +301,8 @@ typedef struct JunkFilter * TrigFunctions cached lookup info for trigger functions * TrigWhenExprs array of trigger WHEN expr states * TrigInstrument optional runtime measurements for triggers + * FdwRoutine FDW callback functions, if foreign table + * FdwState available to save private state of FDW * ConstraintExprs array of constraint-checking expr states * junkFilter for removing junk attributes from tuples * projectReturning for computing a RETURNING list @@ -317,6 +320,8 @@ typedef struct ResultRelInfo FmgrInfo *ri_TrigFunctions; List **ri_TrigWhenExprs; Instrumentation *ri_TrigInstrument; + struct FdwRoutine *ri_FdwRoutine; + void *ri_FdwState; List **ri_ConstraintExprs; JunkFilter *ri_junkFilter; ProjectionInfo *ri_projectReturning; diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 0b8b1076bbf..841701ed98a 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -173,6 +173,7 @@ typedef struct ModifyTable int resultRelIndex; /* index of first resultRel in plan's list */ List *plans; /* plan(s) producing source data */ List *returningLists; /* per-target-table RETURNING tlists */ + List *fdwPrivLists; /* per-target-table FDW private data lists */ List *rowMarks; /* PlanRowMarks (non-locking only) */ int epqParam; /* ID of Param for EvalPlanQual re-eval */ } ModifyTable; @@ -752,13 +753,32 @@ typedef struct Limit * RowMarkType - * enums for types of row-marking operations * - * When doing UPDATE, DELETE, or SELECT FOR [KEY] UPDATE/SHARE, we have to uniquely + * The first four of these values represent different lock strengths that + * we can take on tuples according to SELECT FOR [KEY] UPDATE/SHARE requests. + * We only support these on regular tables. For foreign tables, any locking + * that might be done for these requests must happen during the initial row + * fetch; there is no mechanism for going back to lock a row later (and thus + * no need for EvalPlanQual machinery during updates of foreign tables). + * This means that the semantics will be a bit different than for a local + * table; in particular we are likely to lock more rows than would be locked + * locally, since remote rows will be locked even if they then fail + * locally-checked restriction or join quals. However, the alternative of + * doing a separate remote query to lock each selected row is extremely + * unappealing, so let's do it like this for now. + * + * When doing UPDATE, DELETE, or SELECT FOR UPDATE/SHARE, we have to uniquely * identify all the source rows, not only those from the target relations, so * that we can perform EvalPlanQual rechecking at need. For plain tables we - * can just fetch the TID, the same as for a target relation. Otherwise (for - * example for VALUES or FUNCTION scans) we have to copy the whole row value. - * The latter is pretty inefficient but fortunately the case is not - * performance-critical in practice. + * can just fetch the TID, much as for a target relation; this case is + * represented by ROW_MARK_REFERENCE. Otherwise (for example for VALUES or + * FUNCTION scans) we have to copy the whole row value. ROW_MARK_COPY is + * pretty inefficient, since most of the time we'll never need the data; but + * fortunately the case is not performance-critical in practice. Note that + * we use ROW_MARK_COPY for non-target foreign tables, even if the FDW has a + * concept of rowid and so could theoretically support some form of + * ROW_MARK_REFERENCE. Although copying the whole row value is inefficient, + * it's probably still faster than doing a second remote fetch, so it doesn't + * seem worth the extra complexity to permit ROW_MARK_REFERENCE. */ typedef enum RowMarkType { @@ -776,10 +796,10 @@ typedef enum RowMarkType * PlanRowMark - * plan-time representation of FOR [KEY] UPDATE/SHARE clauses * - * When doing UPDATE, DELETE, or SELECT FOR [KEY] UPDATE/SHARE, we create a separate + * When doing UPDATE, DELETE, or SELECT FOR UPDATE/SHARE, we create a separate * PlanRowMark node for each non-target relation in the query. Relations that - * are not specified as FOR [KEY] UPDATE/SHARE are marked ROW_MARK_REFERENCE (if - * real tables) or ROW_MARK_COPY (if not). + * are not specified as FOR UPDATE/SHARE are marked ROW_MARK_REFERENCE (if + * regular tables) or ROW_MARK_COPY (if not). * * Initially all PlanRowMarks have rti == prti and isParent == false. * When the planner discovers that a relation is the root of an inheritance @@ -791,7 +811,7 @@ typedef enum RowMarkType * * The planner also adds resjunk output columns to the plan that carry * information sufficient to identify the locked or fetched rows. For - * tables (markType != ROW_MARK_COPY), these columns are named + * regular tables (markType != ROW_MARK_COPY), these columns are named * tableoid%u OID of table * ctid%u TID of row * The tableoid column is only present for an inheritance hierarchy. diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h index 2f9fcd575a6..16d685846e8 100644 --- a/src/include/optimizer/planmain.h +++ b/src/include/optimizer/planmain.h @@ -79,7 +79,8 @@ extern SetOp *make_setop(SetOpCmd cmd, SetOpStrategy strategy, Plan *lefttree, long numGroups, double outputRows); extern Result *make_result(PlannerInfo *root, List *tlist, Node *resconstantqual, Plan *subplan); -extern ModifyTable *make_modifytable(CmdType operation, bool canSetTag, +extern ModifyTable *make_modifytable(PlannerInfo *root, + CmdType operation, bool canSetTag, List *resultRelations, List *subplans, List *returningLists, List *rowMarks, int epqParam); extern bool is_projection_capable_plan(Plan *plan); |