[v9.3] writable foreign tables

On Thu, Aug 23, 2012 at 1:10 AM, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:

It is a responsibility of FDW extension (and DBA) to ensure each
foreign-row has a unique identifier that has 48-bits width integer
data type in maximum.

It strikes me as incredibly short-sighted to decide that the row
identifier has to have the same format as what our existing heap AM
happens to have. I think we need to allow the row identifier to be of
any data type, and even compound. For example, the foreign side might
have no equivalent of CTID, and thus use primary key. And the primary
key might consist of an integer and a string, or some such.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2012/8/25 Robert Haas <robertmhaas@gmail.com>:

On Thu, Aug 23, 2012 at 1:10 AM, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:

It is a responsibility of FDW extension (and DBA) to ensure each
foreign-row has a unique identifier that has 48-bits width integer
data type in maximum.

It strikes me as incredibly short-sighted to decide that the row
identifier has to have the same format as what our existing heap AM
happens to have. I think we need to allow the row identifier to be of
any data type, and even compound. For example, the foreign side might
have no equivalent of CTID, and thus use primary key. And the primary
key might consist of an integer and a string, or some such.

I assume it is a task of FDW extension to translate between the pseudo
ctid and the primary key in remote side.

For example, if primary key of the remote table is Text data type, an idea
is to use a hash table to track the text-formed primary being associated
with a particular 48-bits integer. The pseudo ctid shall be utilized to track
the tuple to be modified on the scan-stage, then FDW can reference the
hash table to pull-out the primary key to be provided on the prepared
statement.

Do we have some other reasonable ideas?

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

Kohei KaiGai wrote:

2012/8/25 Robert Haas <robertmhaas@gmail.com>:

On Thu, Aug 23, 2012 at 1:10 AM, Kohei KaiGai <kaigai@kaigai.gr.jp>

wrote:

It is a responsibility of FDW extension (and DBA) to ensure each
foreign-row has a unique identifier that has 48-bits width integer
data type in maximum.

It strikes me as incredibly short-sighted to decide that the row
identifier has to have the same format as what our existing heap AM
happens to have. I think we need to allow the row identifier to be

of

any data type, and even compound. For example, the foreign side

might

have no equivalent of CTID, and thus use primary key. And the

primary

key might consist of an integer and a string, or some such.

I assume it is a task of FDW extension to translate between the pseudo
ctid and the primary key in remote side.

For example, if primary key of the remote table is Text data type, an

idea

is to use a hash table to track the text-formed primary being

associated

with a particular 48-bits integer. The pseudo ctid shall be utilized

to track

the tuple to be modified on the scan-stage, then FDW can reference the
hash table to pull-out the primary key to be provided on the prepared
statement.

And what if there is a hash collision? Then you would not be able to
determine which row is meant.

I agree with Robert that this should be flexible enough to cater for
all kinds of row identifiers. Oracle, for example, uses ten byte
identifiers which would give me a headache with your suggested design.

Do we have some other reasonable ideas?

Would it be too invasive to introduce a new pointer in TupleTableSlot
that is NULL for anything but virtual tuples from foreign tables?

Yours,
Laurenz Albe

Kaigai-san,

On Thu, Aug 23, 2012 at 2:10 PM, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:

The patched portion at contrib/file_fdw.c does not make sense
actually. It just prints messages for each invocation.
It is just a proof-of-concept to show possibility of implementation
based on real RDBMS.

Attached is a tar ball of pgsql_fdw. It's WIP and contains no
document, but it would be enough for your PoC purpose. Usage and
features are same as the last version posted for 9.2 cycle.
# I'll post finished patch in the CF-Sep.

Here are random comments for your PoC patch:

+ As Robert says, using CTID as virtual tuple identifier doesn't seem
nice when considering various FDWs for NoSQL or RDBMS. Having abstract
layer between FDWs and tuple sounds better, but implementing it by each
FDW seems useless effort. Do yo have any idea of generic mechanism for
tuple mapping?

+ Do you have any plan about deparsing local qualifiers into remote
query to avoid repeated query submission? This would improve
performance of big UPDATE, but its use case might be limited to
statements which consist of one foreign table. For this case, we can
consider pass-through mode as second way.

+ I have not read your patch closely yet, but I wonder how we can know
which column is actually updated. If we have only updated image of
tuple, we have to update all remote columns by "new" values?

--
Shigeru Hanada

2012/8/27 Albe Laurenz <laurenz.albe@wien.gv.at>:

Kohei KaiGai wrote:

2012/8/25 Robert Haas <robertmhaas@gmail.com>:

On Thu, Aug 23, 2012 at 1:10 AM, Kohei KaiGai <kaigai@kaigai.gr.jp>

wrote:

It is a responsibility of FDW extension (and DBA) to ensure each
foreign-row has a unique identifier that has 48-bits width integer
data type in maximum.

It strikes me as incredibly short-sighted to decide that the row
identifier has to have the same format as what our existing heap AM
happens to have. I think we need to allow the row identifier to be

of

any data type, and even compound. For example, the foreign side

might

have no equivalent of CTID, and thus use primary key. And the

primary

key might consist of an integer and a string, or some such.

I assume it is a task of FDW extension to translate between the pseudo
ctid and the primary key in remote side.

For example, if primary key of the remote table is Text data type, an

idea

is to use a hash table to track the text-formed primary being

associated

with a particular 48-bits integer. The pseudo ctid shall be utilized

to track

the tuple to be modified on the scan-stage, then FDW can reference the
hash table to pull-out the primary key to be provided on the prepared
statement.

And what if there is a hash collision? Then you would not be able to
determine which row is meant.

Even if we had a hash collision, each hash entry can have the original
key itself to be compared. But anyway, I love the idea to support
an opaque pointer to track particular remote-row rather.

I agree with Robert that this should be flexible enough to cater for
all kinds of row identifiers. Oracle, for example, uses ten byte
identifiers which would give me a headache with your suggested design.

Do we have some other reasonable ideas?

Would it be too invasive to introduce a new pointer in TupleTableSlot
that is NULL for anything but virtual tuples from foreign tables?

I'm not certain whether the duration of TupleTableSlot is enough to
carry a private datum between scan and modify stage.
For example, the TupleTableSlot shall be cleared at ExecNestLoop
prior to the slot being delivered to ExecModifyTuple.

postgres=# EXPLAIN UPDATE t1 SET b = 'abcd' WHERE a IN (SELECT x FROM
t2 WHERE x % 2 = 0);
QUERY PLAN
-------------------------------------------------------------------------------
Update on t1 (cost=0.00..54.13 rows=6 width=16)
-> Nested Loop (cost=0.00..54.13 rows=6 width=16)
-> Seq Scan on t2 (cost=0.00..28.45 rows=6 width=10)
Filter: ((x % 2) = 0)
-> Index Scan using t1_pkey on t1 (cost=0.00..4.27 rows=1 width=10)
Index Cond: (a = t2.x)
(6 rows)

Is it possible to utilize ctid field to move a private pointer?
TID data type is internally represented as a pointer to ItemPointerData,
so it has enough width to track an opaque formed remote-row identifier;
including string, int64 or others.

One disadvantage is "ctid" system column shows a nonsense value
when user explicitly references this system column. But it does not
seems to me a fundamental problem, because we didn't give any
special meaning on the "ctid" field of foreign table.

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

2012/8/27 Shigeru HANADA <shigeru.hanada@gmail.com>:

Kaigai-san,

On Thu, Aug 23, 2012 at 2:10 PM, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:

The patched portion at contrib/file_fdw.c does not make sense
actually. It just prints messages for each invocation.
It is just a proof-of-concept to show possibility of implementation
based on real RDBMS.

Attached is a tar ball of pgsql_fdw. It's WIP and contains no
document, but it would be enough for your PoC purpose. Usage and
features are same as the last version posted for 9.2 cycle.
# I'll post finished patch in the CF-Sep.

Thanks, it is helpful to work on.

Here are random comments for your PoC patch:

+ As Robert says, using CTID as virtual tuple identifier doesn't seem
nice when considering various FDWs for NoSQL or RDBMS. Having abstract
layer between FDWs and tuple sounds better, but implementing it by each
FDW seems useless effort. Do yo have any idea of generic mechanism for
tuple mapping?

As I wrote in the previous message, isn't it a reasonable idea to move
a private datum (instead of alternate key) on the "ctid" field which has
been internally represented as a pointer to indicate ItemPointerData?

+ Do you have any plan about deparsing local qualifiers into remote
query to avoid repeated query submission? This would improve
performance of big UPDATE, but its use case might be limited to
statements which consist of one foreign table. For this case, we can
consider pass-through mode as second way.

I think, FDW should run UPDATE or DELETE statement at the scan
stage on remote-side, then return a pseudo result to scanner, in case
of the statement is "enough simple", like no qualifier, no returning, etc...
The callback on ExecUpdate/ExecDelete will perform just a stub; that
does not actually work except for increment of affected rows.

+ I have not read your patch closely yet, but I wonder how we can know
which column is actually updated. If we have only updated image of
tuple, we have to update all remote columns by "new" values?

It seems to me TargetEntry of the parse tree can inform us which column
should be modified on UPDATE or INSERT. If it has just a Var element
that reference original table as-is, it means here is no change.

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

Kohei KaiGai wrote:

It is a responsibility of FDW extension (and DBA) to ensure each
foreign-row has a unique identifier that has 48-bits width integer
data type in maximum.

For example, if primary key of the remote table is Text data type,
an idea is to use a hash table to track the text-formed primary
being associated with a particular 48-bits integer.

Even if we had a hash collision, each hash entry can have the original
key itself to be compared. But anyway, I love the idea to support
an opaque pointer to track particular remote-row rather.

Me too.

Do we have some other reasonable ideas?

I'm not certain whether the duration of TupleTableSlot is enough to
carry a private datum between scan and modify stage.

Is it possible to utilize ctid field to move a private pointer?
TID data type is internally represented as a pointer to

ItemPointerData,

so it has enough width to track an opaque formed remote-row

identifier;

including string, int64 or others.

One disadvantage is "ctid" system column shows a nonsense value
when user explicitly references this system column. But it does not
seems to me a fundamental problem, because we didn't give any
special meaning on the "ctid" field of foreign table.

I can't say if (ab)using the field that way would cause other
problems, but I don't think that "nonsense values" are a problem.
The pointer would stay the same for the duration of the foreign
scan, which I think is as good a ctid for a foreign table as
anybody should reasonably ask.

BTW, I see the following comment in htup.h:

* t_self and t_tableOid should be valid if the HeapTupleData points to
* a disk buffer, or if it represents a copy of a tuple on disk. They
* should be explicitly set invalid in manufactured tuples.

I don't know if "invalid" means "zero" in that case.

Yours,
Laurenz Albe

2012/8/28 Albe Laurenz <laurenz.albe@wien.gv.at>:

Kohei KaiGai wrote:

It is a responsibility of FDW extension (and DBA) to ensure each
foreign-row has a unique identifier that has 48-bits width integer
data type in maximum.

For example, if primary key of the remote table is Text data type,
an idea is to use a hash table to track the text-formed primary
being associated with a particular 48-bits integer.

Even if we had a hash collision, each hash entry can have the original
key itself to be compared. But anyway, I love the idea to support
an opaque pointer to track particular remote-row rather.

Me too.

Do we have some other reasonable ideas?

I'm not certain whether the duration of TupleTableSlot is enough to
carry a private datum between scan and modify stage.

Is it possible to utilize ctid field to move a private pointer?
TID data type is internally represented as a pointer to

ItemPointerData,

so it has enough width to track an opaque formed remote-row

identifier;

including string, int64 or others.

One disadvantage is "ctid" system column shows a nonsense value
when user explicitly references this system column. But it does not
seems to me a fundamental problem, because we didn't give any
special meaning on the "ctid" field of foreign table.

I can't say if (ab)using the field that way would cause other
problems, but I don't think that "nonsense values" are a problem.
The pointer would stay the same for the duration of the foreign
scan, which I think is as good a ctid for a foreign table as
anybody should reasonably ask.

BTW, I see the following comment in htup.h:

* t_self and t_tableOid should be valid if the HeapTupleData points to
* a disk buffer, or if it represents a copy of a tuple on disk. They
* should be explicitly set invalid in manufactured tuples.

I don't know if "invalid" means "zero" in that case.

ItemPointerSetInvalid is declared as follows:

/*
* ItemPointerSetInvalid
* Sets a disk item pointer to be invalid.
*/
#define ItemPointerSetInvalid(pointer) \
( \
AssertMacro(PointerIsValid(pointer)), \
BlockIdSet(&((pointer)->ip_blkid), InvalidBlockNumber), \
(pointer)->ip_posid = InvalidOffsetNumber \
)

Since ItemPointerGetBlockNumber() and ItemPointerGetOffsetNumber()
checks whether the given ItemPointer is valid, FDWs may have to put
a dummy ItemPointerData on head of their private datum to avoid
the first 6-bytes having zero.

For example, the following data structure is safe to carry an opaque
datum without false-positive of invalid ctid.

typedef struct {
ItemPointerData dumm
char *pk_of_remote_table;
} my_pseudo_rowid;

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

Kohei KaiGai <kaigai@kaigai.gr.jp> writes:

Would it be too invasive to introduce a new pointer in TupleTableSlot
that is NULL for anything but virtual tuples from foreign tables?

I'm not certain whether the duration of TupleTableSlot is enough to
carry a private datum between scan and modify stage.

It's not.

Is it possible to utilize ctid field to move a private pointer?

UPDATEs and DELETEs do not rely on the ctid field of tuples to carry the
TID from scan to modify --- in fact, most of the time what the modify
step is going to get is a "virtual" TupleTableSlot that hasn't even
*got* a physical CTID field.

Instead, the planner arranges for the TID to be carried up as an
explicit resjunk column named ctid. (Currently this is done in
rewriteTargetListUD(), but see also preptlist.c which does some related
things for SELECT FOR UPDATE.)

I'm inclined to think that what we need here is for FDWs to be able to
modify the details of that behavior, at least to the extent of being
able to specify a different data type than TID for the row
identification column.

regards, tom lane

Kohei KaiGai <kaigai@kaigai.gr.jp> writes:

It seems to me TargetEntry of the parse tree can inform us which column
should be modified on UPDATE or INSERT. If it has just a Var element
that reference original table as-is, it means here is no change.

Only if you're not going to support BEFORE triggers modifying the row...

regards, tom lane

On Tue, Aug 28, 2012 at 10:58:25AM -0400, Tom Lane wrote:

Kohei KaiGai <kaigai@kaigai.gr.jp> writes:

It seems to me TargetEntry of the parse tree can inform us which column
should be modified on UPDATE or INSERT. If it has just a Var element
that reference original table as-is, it means here is no change.

Only if you're not going to support BEFORE triggers modifying the row...

+1 for supporting these.

Speaking of triggers on foreign tables, what's needed to support them
independent of support at the FDW level for writing on foreign tables,
or does that even make sense?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

2012/8/28 David Fetter <david@fetter.org>:

On Tue, Aug 28, 2012 at 10:58:25AM -0400, Tom Lane wrote:

Kohei KaiGai <kaigai@kaigai.gr.jp> writes:

It seems to me TargetEntry of the parse tree can inform us which column
should be modified on UPDATE or INSERT. If it has just a Var element
that reference original table as-is, it means here is no change.

Only if you're not going to support BEFORE triggers modifying the row...

+1 for supporting these.

Speaking of triggers on foreign tables, what's needed to support them
independent of support at the FDW level for writing on foreign tables,
or does that even make sense?

I agree with trigger support on foreign tables is definitely useful feature,
even though it does not have capability to replace the writable foreign
table functionality.

In case when foreign-table definition does not contain a column mapped
with primary-key column in remote-side, the trigger function cannot
determine which row should be updated / deleted.
It is a situation that FDW driver should track a particular remote-row using
its identifier.

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

On Tue, Aug 28, 2012 at 05:18:34PM +0200, Kohei KaiGai wrote:

2012/8/28 David Fetter <david@fetter.org>:

On Tue, Aug 28, 2012 at 10:58:25AM -0400, Tom Lane wrote:

Kohei KaiGai <kaigai@kaigai.gr.jp> writes:

It seems to me TargetEntry of the parse tree can inform us
which column should be modified on UPDATE or INSERT. If it has
just a Var element that reference original table as-is, it
means here is no change.

Only if you're not going to support BEFORE triggers modifying the
row...

+1 for supporting these.

Speaking of triggers on foreign tables, what's needed to support
them independent of support at the FDW level for writing on
foreign tables, or does that even make sense?

I agree with trigger support on foreign tables is definitely useful
feature, even though it does not have capability to replace the
writable foreign table functionality.

With utmost respect, trigger support does make it possible to write to
foreign tables using a whole-row comparison with the effect that all
whole-row matches would be affected. This is how DBI-Link does it
currently.

In case when foreign-table definition does not contain a column
mapped with primary-key column in remote-side, the trigger function
cannot determine which row should be updated / deleted. It is a
situation that FDW driver should track a particular remote-row using
its identifier.

Generated identifiers and whole-row matching are two ways to approach
this. There are likely others, especially in cases where people have
special knowledge of the remote source.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

2012/8/28 Tom Lane <tgl@sss.pgh.pa.us>:

Kohei KaiGai <kaigai@kaigai.gr.jp> writes:

Would it be too invasive to introduce a new pointer in TupleTableSlot
that is NULL for anything but virtual tuples from foreign tables?

I'm not certain whether the duration of TupleTableSlot is enough to
carry a private datum between scan and modify stage.

It's not.

Is it possible to utilize ctid field to move a private pointer?

UPDATEs and DELETEs do not rely on the ctid field of tuples to carry the
TID from scan to modify --- in fact, most of the time what the modify
step is going to get is a "virtual" TupleTableSlot that hasn't even
*got* a physical CTID field.

Instead, the planner arranges for the TID to be carried up as an
explicit resjunk column named ctid. (Currently this is done in
rewriteTargetListUD(), but see also preptlist.c which does some related
things for SELECT FOR UPDATE.)

I'm inclined to think that what we need here is for FDWs to be able to
modify the details of that behavior, at least to the extent of being
able to specify a different data type than TID for the row
identification column.

Hmm. It seems to me a straight-forward solution rather than ab-use
of ctid system column. Probably, cstring data type is more suitable
to carry a private datum between scan and modify stage.

One problem I noticed is how FDW driver returns an extra field that
is in neither system nor regular column.
Number of columns and its data type are defined with TupleDesc of
the target foreign-table, so we also need a feature to extend it on
run-time. For example, FDW driver may have to be able to extend
a "virtual" column with cstring data type, even though the target
foreign table does not have such a column.

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

2012/8/28 David Fetter <david@fetter.org>:

On Tue, Aug 28, 2012 at 05:18:34PM +0200, Kohei KaiGai wrote:

2012/8/28 David Fetter <david@fetter.org>:

On Tue, Aug 28, 2012 at 10:58:25AM -0400, Tom Lane wrote:

Kohei KaiGai <kaigai@kaigai.gr.jp> writes:

It seems to me TargetEntry of the parse tree can inform us
which column should be modified on UPDATE or INSERT. If it has
just a Var element that reference original table as-is, it
means here is no change.

Only if you're not going to support BEFORE triggers modifying the
row...

+1 for supporting these.

Speaking of triggers on foreign tables, what's needed to support
them independent of support at the FDW level for writing on
foreign tables, or does that even make sense?

I agree with trigger support on foreign tables is definitely useful
feature, even though it does not have capability to replace the
writable foreign table functionality.

With utmost respect, trigger support does make it possible to write to
foreign tables using a whole-row comparison with the effect that all
whole-row matches would be affected. This is how DBI-Link does it
currently.

In case when foreign-table definition does not contain a column
mapped with primary-key column in remote-side, the trigger function
cannot determine which row should be updated / deleted. It is a
situation that FDW driver should track a particular remote-row using
its identifier.

Generated identifiers and whole-row matching are two ways to approach
this. There are likely others, especially in cases where people have
special knowledge of the remote source.

One major problem is how to carry the generated identifiers on run-time,
even though we have no slot except for system and regular columns
defined in TupleDesc of the target foreign tables.
It may need a feature to expand TupleDesc on demand.

Of course, I don't deny the benefit of trigger support on foreign-tables.
Both writable-feature and trigger-support can be supported simultaneously.

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

On Tue, Aug 28, 2012 at 06:08:59PM +0200, Kohei KaiGai wrote:

2012/8/28 David Fetter <david@fetter.org>:

On Tue, Aug 28, 2012 at 05:18:34PM +0200, Kohei KaiGai wrote:

2012/8/28 David Fetter <david@fetter.org>:

On Tue, Aug 28, 2012 at 10:58:25AM -0400, Tom Lane wrote:

Kohei KaiGai <kaigai@kaigai.gr.jp> writes:

It seems to me TargetEntry of the parse tree can inform us
which column should be modified on UPDATE or INSERT. If it has
just a Var element that reference original table as-is, it
means here is no change.

Only if you're not going to support BEFORE triggers modifying the
row...

+1 for supporting these.

Generated identifiers and whole-row matching are two ways to approach
this. There are likely others, especially in cases where people have
special knowledge of the remote source.

One major problem is how to carry the generated identifiers on run-time,
even though we have no slot except for system and regular columns
defined in TupleDesc of the target foreign tables.
It may need a feature to expand TupleDesc on demand.

Could be. You know a lot more about the implementation details than I do.

Of course, I don't deny the benefit of trigger support on foreign-tables.
Both writable-feature and trigger-support can be supported simultaneously.

Do you see these as independent features, or is there some essential
overlap?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

2012/8/28 Kohei KaiGai <kaigai@kaigai.gr.jp>:

2012/8/28 Tom Lane <tgl@sss.pgh.pa.us>:

Kohei KaiGai <kaigai@kaigai.gr.jp> writes:

Would it be too invasive to introduce a new pointer in TupleTableSlot
that is NULL for anything but virtual tuples from foreign tables?

I'm not certain whether the duration of TupleTableSlot is enough to
carry a private datum between scan and modify stage.

It's not.

Is it possible to utilize ctid field to move a private pointer?

UPDATEs and DELETEs do not rely on the ctid field of tuples to carry the
TID from scan to modify --- in fact, most of the time what the modify
step is going to get is a "virtual" TupleTableSlot that hasn't even
*got* a physical CTID field.

Instead, the planner arranges for the TID to be carried up as an
explicit resjunk column named ctid. (Currently this is done in
rewriteTargetListUD(), but see also preptlist.c which does some related
things for SELECT FOR UPDATE.)

I'm inclined to think that what we need here is for FDWs to be able to
modify the details of that behavior, at least to the extent of being
able to specify a different data type than TID for the row
identification column.

Hmm. It seems to me a straight-forward solution rather than ab-use
of ctid system column. Probably, cstring data type is more suitable
to carry a private datum between scan and modify stage.

One problem I noticed is how FDW driver returns an extra field that
is in neither system nor regular column.
Number of columns and its data type are defined with TupleDesc of
the target foreign-table, so we also need a feature to extend it on
run-time. For example, FDW driver may have to be able to extend
a "virtual" column with cstring data type, even though the target
foreign table does not have such a column.

I tried to investigate the related routines.

TupleDesc of TupleTableSlot associated with ForeignScanState
is initialized at ExecInitForeignScan as literal.
ExecAssignScanType assigns TupleDesc of the target foreign-
table on tts_tupleDescriptor, "as-is".
It is the reason why IterateForeignScan cannot return a private
datum except for the columns being declared as regular ones.

Confrontation between ForeignScan and SubqueryScan tell us
the point to be extended. It assigns TupleDesc of the subplan
generated at run-time.
It seems to me ForeignScan will be able to adopt similar idea;
that allows to append "pseudo-column" onto TupleDesc to
carry identifier of remote-rows to be updated / deleted, if
FDW driver can control TupleDesc being set, instead of the
one come from relation's definition as-is.

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

2012/8/28 David Fetter <david@fetter.org>:

On Tue, Aug 28, 2012 at 06:08:59PM +0200, Kohei KaiGai wrote:

2012/8/28 David Fetter <david@fetter.org>:

On Tue, Aug 28, 2012 at 05:18:34PM +0200, Kohei KaiGai wrote:

2012/8/28 David Fetter <david@fetter.org>:

On Tue, Aug 28, 2012 at 10:58:25AM -0400, Tom Lane wrote:

Kohei KaiGai <kaigai@kaigai.gr.jp> writes:

It seems to me TargetEntry of the parse tree can inform us
which column should be modified on UPDATE or INSERT. If it has
just a Var element that reference original table as-is, it
means here is no change.

Only if you're not going to support BEFORE triggers modifying the
row...

+1 for supporting these.

Generated identifiers and whole-row matching are two ways to approach
this. There are likely others, especially in cases where people have
special knowledge of the remote source.

One major problem is how to carry the generated identifiers on run-time,
even though we have no slot except for system and regular columns
defined in TupleDesc of the target foreign tables.
It may need a feature to expand TupleDesc on demand.

Could be. You know a lot more about the implementation details than I do.

Of course, I don't deny the benefit of trigger support on foreign-tables.
Both writable-feature and trigger-support can be supported simultaneously.

Do you see these as independent features, or is there some essential
overlap?

If we stand on the viewpoint that foreign-tables should perform as if regular
tables, I don't think its writer feature should depend on trigger stuff.
They can work independently.

On the other hand, trigger feature gives users flexibility to control the data
to be written, as if regular tables. We shouldn't miss the point.
At least, I don't think we have some technical differences to support row-level
triggers on foreign tables.

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

Tom Lane wrote:

Kohei KaiGai <kaigai@kaigai.gr.jp> writes:

Laurenz Albe wrote:

Would it be too invasive to introduce a new pointer in

TupleTableSlot

that is NULL for anything but virtual tuples from foreign tables?

I'm not certain whether the duration of TupleTableSlot is enough to
carry a private datum between scan and modify stage.

It's not.

Is it possible to utilize ctid field to move a private pointer?

UPDATEs and DELETEs do not rely on the ctid field of tuples to carry

the

TID from scan to modify --- in fact, most of the time what the modify
step is going to get is a "virtual" TupleTableSlot that hasn't even
*got* a physical CTID field.

Instead, the planner arranges for the TID to be carried up as an
explicit resjunk column named ctid. (Currently this is done in
rewriteTargetListUD(), but see also preptlist.c which does some

related

things for SELECT FOR UPDATE.)

I'm inclined to think that what we need here is for FDWs to be able to
modify the details of that behavior, at least to the extent of being
able to specify a different data type than TID for the row
identification column.

Would that imply inventing a new system attribute for
"foreign tid"?

Yours,
Laurenz Albe