Re: FDW system columns

I notice that there's some weird info coming out of the system columns
on any FDW:

test=# select tableoid, ctid, xmin, xmax, cmin, cmax, * from dict limit 12;
tableoid | ctid | xmin | xmax | cmin | cmax | words
----------+----------------+------+------------+-------+-------+-----------
16428 | (4294967295,0) | 104 | 4294967295 | 16430 | 16430 | A
16428 | (4294967295,0) | 104 | 4294967295 | 16430 | 16430 | a
16428 | (4294967295,0) | 108 | 4294967295 | 16430 | 16430 | aa
16428 | (4294967295,0) | 112 | 4294967295 | 16430 | 16430 | aal
16428 | (4294967295,0) | 120 | 4294967295 | 16430 | 16430 | aalii
16428 | (4294967295,0) | 112 | 4294967295 | 16430 | 16430 | aam
16428 | (4294967295,0) | 116 | 4294967295 | 16430 | 16430 | Aani
16428 | (4294967295,0) | 132 | 4294967295 | 16430 | 16430 | aardvark
16428 | (4294967295,0) | 132 | 4294967295 | 16430 | 16430 | aardwolf
16428 | (4294967295,0) | 120 | 4294967295 | 16430 | 16430 | Aaron
16428 | (4294967295,0) | 128 | 4294967295 | 16430 | 16430 | Aaronic
16428 | (4294967295,0) | 136 | 4294967295 | 16430 | 16430 | Aaronical
(12 rows)

That's file_fdw. On the not-yet-ready pgsql_fdw:

test=# select tableoid, ctid, xmin, xmax, cmin, cmax from cows limit 5;
tableoid | ctid | xmin | xmax | cmin | cmax
----------+----------------+------+------+------+------
16406 | (4294967295,0) | 0 | 0 | 0 | 0
16406 | (4294967295,0) | 0 | 0 | 0 | 0
16406 | (4294967295,0) | 0 | 0 | 0 | 0
16406 | (4294967295,0) | 0 | 0 | 0 | 0
16406 | (4294967295,0) | 0 | 0 | 0 | 0
(5 rows)

So the ctid is always 2^32-1. Bit weird, but probably explainable.
But xmin on the file_fdw result is odd. Why are these all over the
place?

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Thom Brown <thom(at)linux(dot)com> writes:
> So the ctid is always 2^32-1. Bit weird, but probably explainable.

See ItemPointerSetInvalid.

> But xmin on the file_fdw result is odd. Why are these all over the
> place?

heap_form_tuple initializes the t_choice fields as though for a tuple
Datum, and file_fdw doesn't change it.

Just a couple hours ago I was wondering why we create system columns for
foreign tables at all. Is there a reasonable prospect that they'll ever
be useful? I can see potential value in tableoid, but the others seem
pretty dubious --- even if you were fetching from a remote PG server,
the XIDs would not be meaningful within our own environment.

regards, tom lane

On 13 November 2011 00:38, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Thom Brown <thom(at)linux(dot)com> writes:
>> But xmin on the file_fdw result is odd.  Why are these all over the
>> place?
>
> heap_form_tuple initializes the t_choice fields as though for a tuple
> Datum, and file_fdw doesn't change it.
>
> Just a couple hours ago I was wondering why we create system columns for
> foreign tables at all.  Is there a reasonable prospect that they'll ever
> be useful?  I can see potential value in tableoid, but the others seem
> pretty dubious --- even if you were fetching from a remote PG server,
> the XIDs would not be meaningful within our own environment.

Yes, that's what I was thinking when curiosity led me to have a look
at what they contain. As far as I see, they serve no useful function.
I didn't bother looking at tableoid as that's generally useful.

Is there a cost to having them there? Could there be tools that might
break if the columns were no longer available?

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On sön, 2011-11-13 at 00:58 +0000, Thom Brown wrote:
> Is there a cost to having them there? Could there be tools that might
> break if the columns were no longer available?

Doubtful. Views don't have system columns either.

On Nov13, 2011, at 01:38 , Tom Lane wrote:
> Just a couple hours ago I was wondering why we create system columns for
> foreign tables at all. Is there a reasonable prospect that they'll ever
> be useful? I can see potential value in tableoid, but the others seem
> pretty dubious --- even if you were fetching from a remote PG server,
> the XIDs would not be meaningful within our own environment.

At least ctid seems useful too. I've used that in the past as a poor man's
surrogate primary key.

Also, people have used ctid and xmin in the past to re-find previously
visited rows and to check whether they've been modified. So there might be
some value in keeping xmin around also (and make the postgres fdw populate it)

best regards,
Florian Pflug

On Sun, Nov 13, 2011 at 6:57 PM, Florian Pflug <fgp(at)phlo(dot)org> wrote:
> On Nov13, 2011, at 01:38 , Tom Lane wrote:
>> Just a couple hours ago I was wondering why we create system columns for
>> foreign tables at all.  Is there a reasonable prospect that they'll ever
>> be useful?  I can see potential value in tableoid, but the others seem
>> pretty dubious --- even if you were fetching from a remote PG server,
>> the XIDs would not be meaningful within our own environment.
>
> At least ctid seems useful too. I've used that in the past as a poor man's
> surrogate primary key.
>
> Also, people have used ctid and xmin in the past to re-find previously
> visited rows and to check whether they've been modified. So there might be
> some value in keeping xmin around also (and make the postgres fdw populate it)

My vote is to nuke 'em all. :-)

I don't think that we want to encourage people to depend on the
existence of system columns any more than they do already.

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

(2011/11/14 11:25), Robert Haas wrote:
> My vote is to nuke 'em all. :-)

+1.

IIRC, main purpose of supporting tableoid for foreign tables was to be
basis of foreign table inheritance, which was not included in 9.1, and
we have not supported it yet. Other system columns are essentially
garbage, but they survived at 9.1 development because (maybe) it seemed
little odd to have system columns partially at that time.

So, IMHO removing all system columns from foreign tables seems
reasonable, unless it doesn't break any external tool seriously (Perhaps
there would be few tools which assume that foreign tables have system
columns).

If there seems to be a consensus on removing system column from foreign
tables, I'd like to work on this issue. Attached is a halfway patch,
and ISTM there is no problem so far.

Regards,
--
Shigeru Hanada

2011/11/14 Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>

> (2011/11/14 11:25), Robert Haas wrote:
> > My vote is to nuke 'em all. :-)
>
> +1.
>
> IIRC, main purpose of supporting tableoid for foreign tables was to be
> basis of foreign table inheritance, which was not included in 9.1, and
> we have not supported it yet. Other system columns are essentially
> garbage, but they survived at 9.1 development because (maybe) it seemed
> little odd to have system columns partially at that time.
>
> So, IMHO removing all system columns from foreign tables seems
> reasonable, unless it doesn't break any external tool seriously (Perhaps
> there would be few tools which assume that foreign tables have system
> columns).
>
> If there seems to be a consensus on removing system column from foreign
> tables, I'd like to work on this issue. Attached is a halfway patch,
> and ISTM there is no problem so far.
>

I can say that at least PgAdmin doesn't use these columns.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 14 November 2011 13:07, Thom Brown <thom(at)linux(dot)com> wrote:
> 2011/11/14 Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>
>>
>> (2011/11/14 11:25), Robert Haas wrote:
>> > My vote is to nuke 'em all.  :-)
>>
>> +1.
>>
>> IIRC, main purpose of supporting tableoid for foreign tables was to be
>> basis of foreign table inheritance, which was not included in 9.1, and
>> we have not supported it yet.  Other system columns are essentially
>> garbage, but they survived at 9.1 development because (maybe) it seemed
>> little odd to have system columns partially at that time.
>>
>> So, IMHO removing all system columns from foreign tables seems
>> reasonable, unless it doesn't break any external tool seriously (Perhaps
>> there would be few tools which assume that foreign tables have system
>> columns).
>>
>> If there seems to be a consensus on removing system column from foreign
>> tables, I'd like to work on this issue.  Attached is a halfway patch,
>> and ISTM there is no problem so far.
>
>
> I can say that at least PgAdmin doesn't use these columns.

So we still have all of these columns for foreign tables. I've tested
Hanada-san's patch and it removes all of the system columns. Could we
consider applying it, or has a use-case for them since been
discovered?

--
Thom

On Sat, Feb 25, 2012 at 3:56 PM, Thom Brown <thom(at)linux(dot)com> wrote:
>>> If there seems to be a consensus on removing system column from foreign
>>> tables, I'd like to work on this issue.  Attached is a halfway patch,
>>> and ISTM there is no problem so far.
>>
>>
>> I can say that at least PgAdmin doesn't use these columns.
>
> So we still have all of these columns for foreign tables.  I've tested
> Hanada-san's patch and it removes all of the system columns.  Could we
> consider applying it, or has a use-case for them since been
> discovered?

Not to my knowledge, but Hanada-san described his patch as a "halfway
patch", implying that it wasn't done.

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

(2012/02/27 12:35), Robert Haas wrote:
> On Sat, Feb 25, 2012 at 3:56 PM, Thom Brown<thom(at)linux(dot)com> wrote:
>>>> If there seems to be a consensus on removing system column from foreign
>>>> tables, I'd like to work on this issue. Attached is a halfway patch,
>>>> and ISTM there is no problem so far.
>>>
>>>
>>> I can say that at least PgAdmin doesn't use these columns.
>>
>> So we still have all of these columns for foreign tables. I've tested
>> Hanada-san's patch and it removes all of the system columns. Could we
>> consider applying it, or has a use-case for them since been
>> discovered?
>
> Not to my knowledge, but Hanada-san described his patch as a "halfway
> patch", implying that it wasn't done.

Sorry for long absence.

I've used the word "halfway" because I didn't have enough time to
examine that patch at that time. I tested the patch, and now I think
it's OK to apply. One concern is that there is no mention about
unavailable system columns in any document. ddl.sgml has main
description of system columns, but it just says:

<quote>
Every table has several system columns that are implicitly defined by
the system.
</quote>

Since this doesn't mention detailed type of relation, such as VIEW and
COMPOSITE TYPE, IMO we can leave this paragraph as is.

BTW, I still think that tableoid is useful if foreign tables can inherit
other tables. With such feature, tableoid of foreign table is necessary
to determine actual source table. Once we want to support that feature,
IMO we should revive tableoid system column for foreign tables.

--
Shigeru Hanada

2012/2/28 Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>:
> (2012/02/27 12:35), Robert Haas wrote:
>> On Sat, Feb 25, 2012 at 3:56 PM, Thom Brown<thom(at)linux(dot)com>  wrote:
>>>>> If there seems to be a consensus on removing system column from foreign
>>>>> tables, I'd like to work on this issue.  Attached is a halfway patch,
>>>>> and ISTM there is no problem so far.
>>>>
>>>>
>>>> I can say that at least PgAdmin doesn't use these columns.
>>>
>>> So we still have all of these columns for foreign tables.  I've tested
>>> Hanada-san's patch and it removes all of the system columns.  Could we
>>> consider applying it, or has a use-case for them since been
>>> discovered?
>>
>> Not to my knowledge, but Hanada-san described his patch as a "halfway
>> patch", implying that it wasn't done.
>
> Sorry for long absence.
>
> I've used the word "halfway" because I didn't have enough time to
> examine that patch at that time.  I tested the patch, and now I think
> it's OK to apply.  One concern is that there is no mention about
> unavailable system columns in any document.  ddl.sgml has main
> description of system columns, but it just says:
>
> <quote>
> Every table has several system columns that are implicitly defined by
> the system.
> </quote>
>
> Since this doesn't mention detailed type of relation, such as VIEW and
> COMPOSITE TYPE, IMO we can leave this paragraph as is.
>
> BTW, I still think that tableoid is useful if foreign tables can inherit
> other tables.  With such feature, tableoid of foreign table is necessary
> to determine actual source table.  Once we want to support that feature,
> IMO we should revive tableoid system column for foreign tables.

I'm not familiar with foreign table inheritance, or how it would work.
If that's something that will likely be introduced in future, then
surely we'd want to keep the tableoid column rather than removing it
then re-introducing it later?

--
Thom

(2012/02/28 18:08), Thom Brown wrote:
> If that's something that will likely be introduced in future, then
> surely we'd want to keep the tableoid column rather than removing it
> then re-introducing it later?

As background knowledge, currently (9.1 and 9.2dev) foreign tables have
all system columns, but they return meaningless values except tableoid.
For instance, a foreign table pgbench_branches with 3 rows will return
results like below ("bid" in the rightmost is user column):

postgres=# select ctid, xmin, cmin, xmax, cmax, tableoid,
postgres-# bid from pgbench_branches;
ctid | xmin | cmin | xmax | cmax | tableoid | bid
----------------+------+------+------+------+----------+-----
(4294967295,0) | 0 | 0 | 0 | 0 | 16400 | 2
(4294967295,0) | 0 | 0 | 0 | 0 | 16400 | 3
(4294967295,0) | 0 | 0 | 0 | 0 | 16400 | 1
(3 rows)

In this example, 16400 is correct oid of pg_class record for relation
pgbench_branches.

I don't have any idea to use system columns other than tableoid of
foreign tables, because it seems difficult to define common meaning for
various FDWs. One possible idea about ctid column is using it for
virtual tuple id (location information of remote data) for update
support, if FDW can pack location information into ItemPointerData area.

We have three options:

a) remove all system columns (posted patch)
b) remove system columns other than tableoid
c) leave all system columns as is (current 9.2dev)

Incidentally, views, which is very similar object type to foreign
tables, have no system columns.

Thoughts?

--
Shigeru Hanada

On Tue, Feb 28, 2012 at 7:00 AM, Shigeru Hanada
<shigeru(dot)hanada(at)gmail(dot)com> wrote:
> We have three options:
>
> a) remove all system columns (posted patch)
> b) remove system columns other than tableoid
> c) leave all system columns as is (current 9.2dev)
>
> Incidentally, views, which is very similar object type to foreign
> tables, have no system columns.
>
> Thoughts?

I vote against (c). I'm not sure which of (a) or (b) is better.
We've talked about allowing foreign tables to inherit from regular
tables and visca versa, and certainly, in that situation, tableoid
would be useful. But I don't think we've made a definitive decision
about that. I stripped that functionality out of the original patch
because it introduced a bunch of warts that we didn't have time to
figure out how to fix, and it's not clear to me that anyone's spent
any time thinking about that since then.

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

2012年2月28日12:00 Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>:
> (2012/02/28 18:08), Thom Brown wrote:
>> If that's something that will likely be introduced in future, then
>> surely we'd want to keep the tableoid column rather than removing it
>> then re-introducing it later?
>
> As background knowledge, currently (9.1 and 9.2dev) foreign tables have
> all system columns, but they return meaningless values except tableoid.
> For instance, a foreign table pgbench_branches with 3 rows will return
> results like below ("bid" in the rightmost is user column):
>
> postgres=# select ctid, xmin, cmin, xmax, cmax, tableoid,
> postgres-# bid from pgbench_branches;
> ctid | xmin | cmin | xmax | cmax | tableoid | bid
> ----------------+------+------+------+------+----------+-----
> (4294967295,0) | 0 | 0 | 0 | 0 | 16400 | 2
> (4294967295,0) | 0 | 0 | 0 | 0 | 16400 | 3
> (4294967295,0) | 0 | 0 | 0 | 0 | 16400 | 1
> (3 rows)
>
> In this example, 16400 is correct oid of pg_class record for relation
> pgbench_branches.
>
> I don't have any idea to use system columns other than tableoid of
> foreign tables, because it seems difficult to define common meaning for
> various FDWs. One possible idea about ctid column is using it for
> virtual tuple id (location information of remote data) for update
> support, if FDW can pack location information into ItemPointerData area.
>
> We have three options:
>
> a) remove all system columns (posted patch)
> b) remove system columns other than tableoid
> c) leave all system columns as is (current 9.2dev)
>
> Incidentally, views, which is very similar object type to foreign
> tables, have no system columns.
>
> Thoughts?
>
Which is the expected behavior in case of a foreign table
is constructed as a child table of a particular regular table?
In this case, children foreign tables don't have columns
that exist on the parent table?
(Although it is no matter when a regular table is a child of
a foreign table...)

Thanks,
--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Feb 28, 2012 at 7:00 AM, Shigeru Hanada
> <shigeru(dot)hanada(at)gmail(dot)com> wrote:
>> We have three options:
>>
>> a) remove all system columns (posted patch)
>> b) remove system columns other than tableoid
>> c) leave all system columns as is (current 9.2dev)
>>
>> Incidentally, views, which is very similar object type to foreign
>> tables, have no system columns.
>>
>> Thoughts?

> I vote against (c). I'm not sure which of (a) or (b) is better.
> We've talked about allowing foreign tables to inherit from regular
> tables and visca versa, and certainly, in that situation, tableoid
> would be useful.

I think it is a mistake to imagine that tableoid is only useful in
inheritance contexts. As one counterexample, pg_dump selects tableoid
from quite a lot of system catalogs, just as a convenient and uniform
way of remembering each object's type (of course, the fact that it needs
to match them up against pg_depend entries has something to do with
that). More generally, if we exclude tableoid from foreign tables,
that just introduces an arbitrary behavioral difference between foreign
and regular tables, thus complicating any code that has use for the
feature.

So I believe that (a) is a pretty bad choice. I would hold still for
(b) but I am not convinced that the case has been made for that either.
I think it would be wise to avoid introducing behavioral churn until
after we have designed and implemented update capabilities for foreign
tables. If we end up putting back ctid to support that, we'll look
pretty silly.

In short, (c) looks like the most reasonable choice for now, with the
expectation of revisiting the question after we have foreign update
working.

regards, tom lane

(2012/02/28 23:37), Kohei KaiGai wrote:
> 2012年2月28日12:00 Shigeru Hanada<shigeru(dot)hanada(at)gmail(dot)com>:
>> We have three options:
>>
>> a) remove all system columns (posted patch)
>> b) remove system columns other than tableoid
>> c) leave all system columns as is (current 9.2dev)
>>
>> Incidentally, views, which is very similar object type to foreign
>> tables, have no system columns.
>>
>> Thoughts?
>>
> Which is the expected behavior in case of a foreign table
> is constructed as a child table of a particular regular table?
> In this case, children foreign tables don't have columns
> that exist on the parent table?
> (Although it is no matter when a regular table is a child of
> a foreign table...)

If we support table inheritance by foreign tables, foreign tables should
return something for all system columns, because a child table MUST have
all columns held by all parent tables. I'm not sure that foreign tables
should have system columns physically, like the option c).

For now, c) seems most reasonable to me.

--
Shigeru Hanada