Re: [v9.3] writable foreign tables - Mailing list pgsql-hackers

From Kohei KaiGai
Subject Re: [v9.3] writable foreign tables
Date
Msg-id CADyhKSVrNV_c5rrXym776wTxi8Ff4NA-MNQ9R3A3WpRekMrJiA@mail.gmail.com
Whole thread Raw
In response to Re: [v9.3] writable foreign tables  ("Albe Laurenz" <laurenz.albe@wien.gv.at>)
Responses Re: [v9.3] writable foreign tables
List pgsql-hackers
The attached patch is revised version.

One most difference from the previous version is, it constructed
PoC features on Hanada-san's latest postgres-fdw.v5.patch.
Yesh, it looks to me working fine on RDBMS backend also.

Even though the filename of this patch contains "poc" phrase,
I think it may be time to consider adoption of the core regarding
to the interface portion.
(Of course, postgres_fdw is still works in progress.)

Here is a few operation examples.

postgres=# CREATE FOREIGN TABLE tbl (a int, b text, c date) SERVER loopback;
CREATE FOREIGN TABLE
postgres=# SELECT * FROM tbl;
 a |  b  |     c
---+-----+------------
 1 | aaa | 2012-12-01
 2 | bbb | 2012-12-02
 3 | ccc | 2012-12-03
 4 | ddd | 2012-12-04
 5 | eee | 2012-12-05
 6 | fff | 2012-12-06
(6 rows)

postgres=# UPDATE tbl SET b = b || b WHERE a % 2 = 1;
UPDATE 3
postgres=# SELECT * FROM tbl ORDER BY a;
 a |   b    |     c
---+--------+------------
 1 | aaaaaa | 2012-12-01
 2 | bbb    | 2012-12-02
 3 | cccccc | 2012-12-03
 4 | ddd    | 2012-12-04
 5 | eeeeee | 2012-12-05
 6 | fff    | 2012-12-06
(6 rows)

postgres=# INSERT INTO tbl VALUES (7,'ggg'),(8,'hhh');
INSERT 0 2
postgres=# SELECT * FROM tbl ORDER BY a;
 a |   b    |     c
---+--------+------------
 1 | aaaaaa | 2012-12-01
 2 | bbb    | 2012-12-02
 3 | cccccc | 2012-12-03
 4 | ddd    | 2012-12-04
 5 | eeeeee | 2012-12-05
 6 | fff    | 2012-12-06
 7 | ggg    |
 8 | hhh    |
(8 rows)

postgres=# DELETE FROM tbl WHERE a % 2 = 0;
DELETE 4
postgres=# SELECT * FROM tbl ORDER BY a;
 a |   b    |     c
---+--------+------------
 1 | aaaaaa | 2012-12-01
 3 | cccccc | 2012-12-03
 5 | eeeeee | 2012-12-05
 7 | ggg    |
(4 rows)

Even though it still has restriction of transaction control on remote side,
I believe Hanada-san will provide more graceful implementation next to
the first read-only version getting committed.

So, let's back to the main topic of this patch.
According to the upthread discussion, I renamed the interface to inform
expected width of result set as follows:

+typedef AttrNumber (*GetForeignRelWidth_function) (PlannerInfo *root,
+                                                  RelOptInfo *baserel,
+                                                  Relation foreignrel,
+                                                  bool inhparent,
+                                                  List *targetList);

It informs the core how many slots for regular and pseudo columns shall
be acquired. If it is identical with number of attributed in foreign table
definition, it also means this scan does not use any pseudo columns.
A typical use case of pseudo column is "rowid" to move an identifier of
remote row from scan stage to modify stage. It is responsibility of FDW
driver to ensure "rowid" has uniqueness on the remote side; my
enhancement on postgres_fdw uses ctid.

get_pseudo_rowid_column() is a utility function that picks up an attribute
number of pseudo "rowid" column if query rewriter injected on previous
stage. If FDW does not support any other pseudo column features, the
value to be returned is just return-value of this function.

Other relevant APIs are as follows:

+typedef List *(*PlanForeignModify_function) (PlannerInfo *root,
+                                            ModifyTable *plan,
+                                            Index resultRelation,
+                                            Plan *subplan);
+
I newly added this handler on construction of ModifyTable structure.
Because INSERT command does not have scan stage directly connected
with table modification, FDW driver has no chance to construct its private
stuff relevant to table modification. (In case postgres_fdw, it constructs
the second query to modify remote table with/without given ctid.)
Its returned List * value is moved to BeginForeignModify handler as
third argument.

+typedef void (*BeginForeignModify_function) (ModifyTableState *mtstate,
+                                            ResultRelInfo *resultRelInfo,
+                                            List *fdw_private,
+                                            Plan *subplan,
+                                            int eflags);
I adjusted some arguments to reference fdw_private being constructed
on query plan stage. The role of this handler is not changed. FDW driver
should have all the initialization stuff on this handler, like we are doing at
BeginForeignScan.

+typedef int    (*ExecForeignInsert_function) (ResultRelInfo *resultRelInfo,
+                                          HeapTuple tuple);
+typedef int    (*ExecForeignDelete_function) (ResultRelInfo *resultRelInfo,
+                                          Datum rowid);
+typedef int    (*ExecForeignUpdate_function) (ResultRelInfo *resultRelInfo,
+                                          Datum rowid,
+                                          HeapTuple tuple);
These are not changed.

+typedef void (*EndForeignModify_function) (ResultRelInfo *resultRelInfo);

Also, it is not changed.

Thanks,
--
KaiGai Kohei <kaigai@kaigai.gr.jp>

Attachment

pgsql-hackers by date:

Previous
From: Alexander Korotkov
Date:
Subject: Re: WIP: index support for regexp search
Next
From: Andres Freund
Date:
Subject: Re: [PATCH 11/14] Introduce wal decoding via catalog timetravel