Thread: FDW: possible resjunk columns in AddForeignUpdateTargets
I have a question concerning the Foreign Data Wrapper API: I find no mention of this in the documentation, but I remember that you can only add a resjunk column that matches an existing attribute of the foreign table and not one with an arbitrary name or definition. Ist that right? Yours, Laurenz Albe
2013/11/6 Albe Laurenz <laurenz.albe@wien.gv.at>: > I have a question concerning the Foreign Data Wrapper API: > > I find no mention of this in the documentation, but I remember that > you can only add a resjunk column that matches an existing attribute > of the foreign table and not one with an arbitrary name or > definition. > > Ist that right? My understanding (having recently had a crack at getting an FDW working) is that the name can be arbitrary within reason - at least that's what I get from this bit of the documentation: > To do that, add TargetEntry items to parsetree->targetList, containing > expressions for the extra values to be fetched. Each such entry must > be marked resjunk = true, and > must have a distinct resname that will > identify it at execution time. Avoid using names matching ctidN or > wholerowN, as the core system can generate junk columns of these names. http://www.postgresql.org/docs/9.3/interactive/fdw-callbacks.html#FDW-CALLBACKS-UPDATE Someone more knowledgeable than myself will know better, I hope. Regards Ian Barwick
Albe Laurenz <laurenz.albe@wien.gv.at> writes:
> I have a question concerning the Foreign Data Wrapper API:
> I find no mention of this in the documentation, but I remember that
> you can only add a resjunk column that matches an existing attribute
> of the foreign table and not one with an arbitrary name or
> definition.
> Ist that right?
I don't recall such a limitation offhand. It's probably true that
you can't create Vars that don't match some declared column of the
table, but that doesn't restrict what you put into resjunk columns
AFAIR.
regards, tom lane
Tom Lane wrote: > Albe Laurenz <laurenz.albe@wien.gv.at> writes: >> I have a question concerning the Foreign Data Wrapper API: >> I find no mention of this in the documentation, but I remember that >> you can only add a resjunk column that matches an existing attribute >> of the foreign table and not one with an arbitrary name or >> definition. > >> Ist that right? > > I don't recall such a limitation offhand. It's probably true that > you can't create Vars that don't match some declared column of the > table, but that doesn't restrict what you put into resjunk columns > AFAIR. I am confused, probably due to lack of knowledge. What I would like to do is add a custom resjunk column (e.g. a bytea) in AddForeignUpdateTargets that carries a row identifier from the scan state to the modify state. Would that be possible? Can I have anything else than a Var in a resjunk column? Yours, Laurenz Albe
Albe Laurenz <laurenz.albe@wien.gv.at> writes:
> What I would like to do is add a custom resjunk column
> (e.g. a bytea) in AddForeignUpdateTargets that carries a row identifier
> from the scan state to the modify state.
> Would that be possible? Can I have anything else than a Var
> in a resjunk column?
[ thinks for awhile... ] Hm. In principle you can put any expression
you want into the tlist during AddForeignUpdateTargets. However, if it's
not a Var then the planner won't understand that it's something that needs
to be supplied by the table scan, so things won't work right in any but
the most trivial cases (maybe not even then :-().
What I'd try is creating a Var that has the attno of ctid
(ie, SelfItemPointerAttributeNumber) but the datatype you want, ie bytea.
This won't match what the catalogs say your table's ctid is, but I think
that nothing will care much about that.
It's definitely an area that could use more work. IIRC we'd discussed
providing some way for an FDW to specify the type of the ctid column
for its tables, but we didn't do anything about it in 9.3.
regards, tom lane
Tom Lane wrote: > Albe Laurenz <laurenz.albe@wien.gv.at> writes: >> What I would like to do is add a custom resjunk column >> (e.g. a bytea) in AddForeignUpdateTargets that carries a row identifier >> from the scan state to the modify state. >> Would that be possible? Can I have anything else than a Var >> in a resjunk column? > > [ thinks for awhile... ] Hm. In principle you can put any expression > you want into the tlist during AddForeignUpdateTargets. However, if it's > not a Var then the planner won't understand that it's something that needs > to be supplied by the table scan, so things won't work right in any but > the most trivial cases (maybe not even then :-(). > > What I'd try is creating a Var that has the attno of ctid > (ie, SelfItemPointerAttributeNumber) but the datatype you want, ie bytea. > This won't match what the catalogs say your table's ctid is, but I think > that nothing will care much about that. > > It's definitely an area that could use more work. IIRC we'd discussed > providing some way for an FDW to specify the type of the ctid column > for its tables, but we didn't do anything about it in 9.3. Thanks a lot, I will try that. Yours, Laurenz Albe
On 8.11.2013 16:13, Albe Laurenz wrote: > Tom Lane wrote: >> Albe Laurenz <laurenz.albe@wien.gv.at> writes: >>> What I would like to do is add a custom resjunk column (e.g. a >>> bytea) in AddForeignUpdateTargets that carries a row identifier >>> from the scan state to the modify state. Would that be possible? >>> Can I have anything else than a Var in a resjunk column? >> >> [ thinks for awhile... ] Hm. In principle you can put any >> expression you want into the tlist during AddForeignUpdateTargets. >> However, if it's not a Var then the planner won't understand that >> it's something that needs to be supplied by the table scan, so >> things won't work right in any but the most trivial cases (maybe >> not even then :-(). >> >> What I'd try is creating a Var that has the attno of ctid (ie, >> SelfItemPointerAttributeNumber) but the datatype you want, ie >> bytea. This won't match what the catalogs say your table's ctid is, >> but I think that nothing will care much about that. >> >> It's definitely an area that could use more work. IIRC we'd >> discussed providing some way for an FDW to specify the type of the >> ctid column for its tables, but we didn't do anything about it in >> 9.3. > > Thanks a lot, I will try that. Hi Laurenz, have you found a way to pass data types other than TID as a resjunk column? I'm trying to solve almost the same thing (pass INT8 instead of TID), but I got stuck. Adding a custom Var with INT8OID instead of TIDOID seems to work fine, but I've found no way to populate this in IterateForeignScan :-( regards Tomas
Tomas Vondra wrote: > have you found a way to pass data types other than TID as a resjunk > column? I'm trying to solve almost the same thing (pass INT8 instead of > TID), but I got stuck. > > Adding a custom Var with INT8OID instead of TIDOID seems to work fine, > but I've found no way to populate this in IterateForeignScan :-( I didn't get to try it yet, but I'll keep you updated. Yours, Laurenz Albe
2013/11/8 Tom Lane <tgl@sss.pgh.pa.us>: > Albe Laurenz <laurenz.albe@wien.gv.at> writes: >> What I would like to do is add a custom resjunk column >> (e.g. a bytea) in AddForeignUpdateTargets that carries a row identifier >> from the scan state to the modify state. >> Would that be possible? Can I have anything else than a Var >> in a resjunk column? > > [ thinks for awhile... ] Hm. In principle you can put any expression > you want into the tlist during AddForeignUpdateTargets. However, if it's > not a Var then the planner won't understand that it's something that needs > to be supplied by the table scan, so things won't work right in any but > the most trivial cases (maybe not even then :-(). > > What I'd try is creating a Var that has the attno of ctid > (ie, SelfItemPointerAttributeNumber) but the datatype you want, ie bytea. > This won't match what the catalogs say your table's ctid is, but I think > that nothing will care much about that. Apologies for reinvigorating this thread, but I'm running into a similar wall myself and would like to clarify if this approach will work at all. My foreign data source is returning a fixed-length string as a unique row identifier; in AddForeignUpdateTargets() I can create a Var like this: var = makeVar(parsetree->resultRelation, SelfItemPointerAttributeNumber, BPCHAROID, 32, InvalidOid, 0); but is it possible to store something other than a TIDOID here, and if so how? Regards Ian Barwick
Ian Lawrence Barwick wrote: > 2013/11/8 Tom Lane <tgl@sss.pgh.pa.us>: >> [ thinks for awhile... ] Hm. In principle you can put any expression >> you want into the tlist during AddForeignUpdateTargets. However, if it's >> not a Var then the planner won't understand that it's something that needs >> to be supplied by the table scan, so things won't work right in any but >> the most trivial cases (maybe not even then :-(). >> >> What I'd try is creating a Var that has the attno of ctid >> (ie, SelfItemPointerAttributeNumber) but the datatype you want, ie bytea. >> This won't match what the catalogs say your table's ctid is, but I think >> that nothing will care much about that. > > Apologies for reinvigorating this thread, but I'm running into a similar wall > myself and would like to clarify if this approach will work at all. > > My foreign data source is returning a fixed-length string as a unique row > identifier; in AddForeignUpdateTargets() I can create a Var like this: > > var = makeVar(parsetree->resultRelation, > SelfItemPointerAttributeNumber, > BPCHAROID, > 32, > InvalidOid, > 0); > > but is it possible to store something other than a TIDOID here, and if so how? Subsequent analysis showed that this won't work as you have no way to populate such a resjunk column. resjunk columns seem to get filled with the values from the column of the same name, so currently there is no way to invent your own column, fill it and pass it on. See thread 8b848b463a71b7a905bc5ef18b95528e.squirrel@sq.gransy.com What I ended up doing is introduce a column option that identifies a primary key column. I add a resjunk entry for each of those and use them to identify the correct row during an UPDATE or DELETE. That only works for foreign data sources that have a concept of a primary key, but maybe you can do something similar. Yours, Laurenz Albe
2013/12/5 Albe Laurenz <laurenz.albe@wien.gv.at>: > Ian Lawrence Barwick wrote: >> 2013/11/8 Tom Lane <tgl@sss.pgh.pa.us>: >>> [ thinks for awhile... ] Hm. In principle you can put any expression >>> you want into the tlist during AddForeignUpdateTargets. However, if it's >>> not a Var then the planner won't understand that it's something that needs >>> to be supplied by the table scan, so things won't work right in any but >>> the most trivial cases (maybe not even then :-(). >>> >>> What I'd try is creating a Var that has the attno of ctid >>> (ie, SelfItemPointerAttributeNumber) but the datatype you want, ie bytea. >>> This won't match what the catalogs say your table's ctid is, but I think >>> that nothing will care much about that. >> >> Apologies for reinvigorating this thread, but I'm running into a similar wall >> myself and would like to clarify if this approach will work at all. >> >> My foreign data source is returning a fixed-length string as a unique row >> identifier; in AddForeignUpdateTargets() I can create a Var like this: >> >> var = makeVar(parsetree->resultRelation, >> SelfItemPointerAttributeNumber, >> BPCHAROID, >> 32, >> InvalidOid, >> 0); >> >> but is it possible to store something other than a TIDOID here, and if so how? > > Subsequent analysis showed that this won't work as you have > no way to populate such a resjunk column. > resjunk columns seem to get filled with the values from the > column of the same name, so currently there is no way to invent > your own column, fill it and pass it on. > > See thread 8b848b463a71b7a905bc5ef18b95528e.squirrel@sq.gransy.com > > What I ended up doing is introduce a column option that identifies > a primary key column. I add a resjunk entry for each of those and > use them to identify the correct row during an UPDATE or DELETE. > > That only works for foreign data sources that have a concept of > a primary key, but maybe you can do something similar. Thanks for confirming that, I suspected that might be the case. I'll have to go for Plan B (or C or D). Regards Ian Barwick