diff options
Diffstat (limited to 'contrib/postgres_fdw')
| -rw-r--r-- | contrib/postgres_fdw/connection.c | 67 | ||||
| -rw-r--r-- | contrib/postgres_fdw/deparse.c | 303 | ||||
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 1509 | ||||
| -rw-r--r-- | contrib/postgres_fdw/postgres_fdw.c | 1066 | ||||
| -rw-r--r-- | contrib/postgres_fdw/postgres_fdw.h | 18 | ||||
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 74 |
6 files changed, 2831 insertions, 206 deletions
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; |