Обсуждение: Missing Column names with multi-insert
Hello all,
I'm working on a foreign data wrapper that uses INSERT, and I noticed some odd behaviour. If I insert just one row, the TupleDesc->attr[0]->attname.data has the column names in it. However, in a multi-row string, all those are empty strings:
I added this debugging code to BeginForeignInsert in https://bitbucket.org/adunstan/blackhole_fdw on postgres 10.
int i;
FormData_pg_attribute *attr;
TupleDesc tupleDesc;
tupleDesc = slot->tts_tupleDescriptor;
for (i = 0; i < tupleDesc -> natts; i++) {
attr = tupleDesc->attrs[i];
elog(WARNING, "found column '%s'", attr->attname.data);
}
Now with a single row insert, this works as you'd expect:
liz=# INSERT INTO bhtable (key, value) VALUES ('hello', 'world');
WARNING: found column 'key'
WARNING: found column 'value'
INSERT 0 1
But with a multi-row, all the column names are empty:
liz=# INSERT INTO bhtable (key, value) VALUES ('hello', 'world'), ('goodmorning', 'world');
WARNING: found column ''
WARNING: found column ''
WARNING: found column ''
WARNING: found column ''
INSERT 0 2
It doesn't seem unreasonable to me that this data wouldn't be duplicated, but there's no mention of how I would go about retriving these column names for my individual rows, and most foreign data wrappers I can find are write-only.
Am I missing something obvious? Is this a bug?
Thanks,
Liz
Hi, On 2019-02-18 14:34:43 -0500, Liz Frost wrote: > I'm working on a foreign data wrapper that uses INSERT, and I noticed some > odd behaviour. If I insert just one row, the > TupleDesc->attr[0]->attname.data has the column names in it. However, in a > multi-row string, all those are empty strings: > > I added this debugging code to BeginForeignInsert in > https://bitbucket.org/adunstan/blackhole_fdw on postgres 10. > > int i; > FormData_pg_attribute *attr; > TupleDesc tupleDesc; > > tupleDesc = slot->tts_tupleDescriptor; > > for (i = 0; i < tupleDesc -> natts; i++) { > attr = tupleDesc->attrs[i]; > elog(WARNING, "found column '%s'", attr->attname.data); > } > > Now with a single row insert, this works as you'd expect: > > liz=# INSERT INTO bhtable (key, value) VALUES ('hello', 'world'); > WARNING: found column 'key' > WARNING: found column 'value' > INSERT 0 1 > > But with a multi-row, all the column names are empty: > liz=# INSERT INTO bhtable (key, value) VALUES ('hello', 'world'), > ('goodmorning', 'world'); > WARNING: found column '' > WARNING: found column '' > WARNING: found column '' > WARNING: found column '' > INSERT 0 2 > > It doesn't seem unreasonable to me that this data wouldn't be duplicated, > but there's no mention of how I would go about retriving these column names > for my individual rows I think you might be looking at the wrong tuple descriptor. You ought to look at the tuple descriptor from the target relation, not the one from the input slot. It's more or less an accident / efficiency hack that the slot in the first case actually carries the column names. The callback should have a ResultRelInfo as a paramter, I think something like Relation rel = resultRelInfo->ri_RelationDesc; TupleDesc tupdesc = RelationGetDescr(rel); ought to give you the tuple descriptor you want. > , and most foreign data wrappers I can find are write-only. Did you mean read-only? If not, I'm unfortunately not following... Greetings, Andres Freund
Liz Frost <web@stillinbeta.com> writes:
> I'm working on a foreign data wrapper that uses INSERT, and I noticed some
> odd behaviour. If I insert just one row, the
> TupleDesc->attr[0]->attname.data has the column names in it. However, in a
> multi-row string, all those are empty strings:
There's not really any expectation that those be valid info during
planning. The parser has a substantially different code path for
single-row INSERT than multi-row (mostly to minimize overhead for the
single-row case), and probably somewhere in there is the reason why
these happen to show up as valid in that case.
If you want column names, the eref field of RTEs is usually the right
place to look.
> Am I missing something obvious? Is this a bug?
I don't think it's a bug.
regards, tom lane
On 2/18/19 2:34 PM, Liz Frost wrote: > Hello all, > > I'm working on a foreign data wrapper that uses INSERT, and I noticed > some odd behaviour. If I insert just one row, the > TupleDesc->attr[0]->attname.data has the column names in it. However, > in a multi-row string, all those are empty strings: > > I added this debugging code to BeginForeignInsert > in https://bitbucket.org/adunstan/blackhole_fdw on postgres 10. > > int i; > FormData_pg_attribute *attr; > TupleDesc tupleDesc; > > tupleDesc = slot->tts_tupleDescriptor; > > for (i = 0; i < tupleDesc -> natts; i++) { > attr = tupleDesc->attrs[i]; > elog(WARNING, "found column '%s'", attr->attname.data); > } > > Now with a single row insert, this works as you'd expect: > > liz=# INSERT INTO bhtable (key, value) VALUES ('hello', 'world'); > WARNING: found column 'key' > WARNING: found column 'value' > INSERT 0 1 > > But with a multi-row, all the column names are empty: > liz=# INSERT INTO bhtable (key, value) VALUES ('hello', 'world'), > ('goodmorning', 'world'); > WARNING: found column '' > WARNING: found column '' > WARNING: found column '' > WARNING: found column '' > INSERT 0 2 > > It doesn't seem unreasonable to me that this data wouldn't be > duplicated, but there's no mention of how I would go about retriving > these column names for my individual rows, and most foreign data > wrappers I can find are write-only. > > There are numerous writable FDWs, including postgres_fdw in contrib, and a whole lot more listed at <https://wiki.postgresql.org/wiki/Fdw> That should supply you with plenty of example code. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services