Re: [HACKERS] Clarification of FDW API Documentation
--On 13. Juni 2014 13:46:38 -0400 Tom Lane t...@sss.pgh.pa.us wrote: Imagine if `BeginForeignScan` set up a remote cursor and `IterateForeignScan` just fetched _one tuple at a time_ (unlike the current behavior where they are fetched in batches). The tuple would be passed to `ExecForeignDelete` (as is required), but the remote cursor would remain pointing at that tuple. Couldn't `ExecForeignDelete` just call `DELETE FROM table WHERE CURRENT OF cursor` to then delete that tuple? No. This is not guaranteed (or even likely) to work in join cases: the tuple to be updated/deleted might no longer be the current one of the scan. You *must* arrange for the scan to return enough information to uniquely identify the tuple later, and that generally means adding some resjunk columns. Yeah, this is exactly the trap i ran into while implementing the informix_fdw driver. It used an updatable cursor to implement the modify actions as you proposed first. Consider a query like UPDATE remote SET f1 = t.id FROM local t WHERE t.id = f1 The planner might choose a hash join where the hash table is built by forwarding the cursor via the foreign scan. You'll end up with the cursor positioned at the end and you have no way to get it back in sync when the modify action is actually called. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Clarification of FDW API Documentation
(2014/06/14 2:46), Tom Lane wrote: Jason Petersen ja...@citusdata.com writes: Even if there is no guarantee that `IterateForeignScan` is called exactly once before each `ExecForeignDelete` call (which would remove the ability to have them cooperate using this single cursor), one could easily devise other storage backends that don't need junk columns to perform `DELETE` operations. Such as? I could imagine having an optimization that works like you suggest for simple scan cases, but it's not there now, and it could not be the only mode. The optimization in the following comment for postgresPlanForeignModify? /* * 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 ... */ I think this would be very useful. So, I plan to add a patch for it to 2014-08. Thanks, Best regards, Etsuro Fujita -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Clarification of FDW API Documentation
I've been deep in the FDW APIs lately and have come up with a couple of questions about the [user-facing documentation][1]. # Requirement that DELETE use junk columns The bit I'm confused by is the parenthetical in this bit at the end of the section on `AddForeignUpdateTargets`: If the AddForeignUpdateTargets pointer is set to NULL, no extra target expressions are added. (This will make it impossible to implement DELETE operations, though UPDATE may still be feasible if the FDW relies on an unchanging primary key to identify rows.) Later on, the section on `ExecForeignDelete` says (emphasis mine): The junk column(s) **must** be used to identify the tuple to be deleted. Why is this a requirement? At the moment, `postgres_fdw` asks remote machines for the `ctid` of tuples during a scan so it can use that `ctid` to create a targeted `DELETE` during `ExecForeignDelete`, but I think an alternative could avoid the use of the `ctid` junk column altogether... Imagine if `BeginForeignScan` set up a remote cursor and `IterateForeignScan` just fetched _one tuple at a time_ (unlike the current behavior where they are fetched in batches). The tuple would be passed to `ExecForeignDelete` (as is required), but the remote cursor would remain pointing at that tuple. Couldn't `ExecForeignDelete` just call `DELETE FROM table WHERE CURRENT OF cursor` to then delete that tuple? Even if there is no guarantee that `IterateForeignScan` is called exactly once before each `ExecForeignDelete` call (which would remove the ability to have them cooperate using this single cursor), one could easily devise other storage backends that don't need junk columns to perform `DELETE` operations. So why the strong language around this functionality? # Examples of `NULL` return after modification Each of the `ExecForeign`- functions needs to return a tuple representing the row inserted, deleted, or modified. But each function's documentation contains an aside similar to this: The return value is either a slot containing the data that was actually inserted (this might differ from the data supplied, for example as a result of trigger actions), or NULL if no row was actually inserted (again, typically as a result of triggers). Is this even accurate in PostgreSQL 9.3? Can triggers fire against foreign tables? If so, can someone provide an example where the foreign scan has found a tuple, passed it to `ExecForeignDelete`, and then no delete takes place (i.e. `ExecForeignDelete` returns `NULL`)? As far as I can reason, if the foreign scan has found a tuple, the update and delete actions need to do _something_ with it. Maybe I'm missing something. --Jason [1]: http://www.postgresql.org/docs/9.3/static/fdw-callbacks.html -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Clarification of FDW API Documentation
Jason Petersen ja...@citusdata.com writes: Imagine if `BeginForeignScan` set up a remote cursor and `IterateForeignScan` just fetched _one tuple at a time_ (unlike the current behavior where they are fetched in batches). The tuple would be passed to `ExecForeignDelete` (as is required), but the remote cursor would remain pointing at that tuple. Couldn't `ExecForeignDelete` just call `DELETE FROM table WHERE CURRENT OF cursor` to then delete that tuple? No. This is not guaranteed (or even likely) to work in join cases: the tuple to be updated/deleted might no longer be the current one of the scan. You *must* arrange for the scan to return enough information to uniquely identify the tuple later, and that generally means adding some resjunk columns. Even if there is no guarantee that `IterateForeignScan` is called exactly once before each `ExecForeignDelete` call (which would remove the ability to have them cooperate using this single cursor), one could easily devise other storage backends that don't need junk columns to perform `DELETE` operations. Such as? I could imagine having an optimization that works like you suggest for simple scan cases, but it's not there now, and it could not be the only mode. Each of the `ExecForeign`- functions needs to return a tuple representing the row inserted, deleted, or modified. But each function's documentation contains an aside similar to this: The return value is either a slot containing the data that was actually inserted (this might differ from the data supplied, for example as a result of trigger actions), or NULL if no row was actually inserted (again, typically as a result of triggers). Is this even accurate in PostgreSQL 9.3? Can triggers fire against foreign tables? Any local trigger execution would be handled by the core executor. What this is on about is that the remote database might have modified or suppressed the operation as a result of triggers on the remote table; and we'd like the FDW to return data that reflects what actually got inserted/updated/deleted remotely. (I guess a particular FDW might have a policy of not reporting such things accurately, but the point of the text is that if you want to tell the truth you can do so.) Perhaps it would help if these paragraphs said remote trigger not just trigger. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
