Re: t_self as system column

Is there a reason we don't have t_self as one of the system columns that
you can examine from SQL? I'd propose its addition otherwise.

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> Is there a reason we don't have t_self as one of the system columns that
> you can examine from SQL? I'd propose its addition otherwise.

pg_attribute bloat? I'm a bit hesitant to add a row per table for
something we've gotten along without for so long, especially something
with as bizarre a definition as "t_self" has got.

At one time I was hoping to get rid of explicit entries in pg_attribute
for system columns, which would negate this concern. I think we're
stuck with them now, though, because of per-column permissions.

regards, tom lane

On Mon, Jul 5, 2010 at 2:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
>> Is there a reason we don't have t_self as one of the system columns that
>> you can examine from SQL?  I'd propose its addition otherwise.
>
> pg_attribute bloat?  I'm a bit hesitant to add a row per table for
> something we've gotten along without for so long, especially something
> with as bizarre a definition as "t_self" has got.
>
> At one time I was hoping to get rid of explicit entries in pg_attribute
> for system columns, which would negate this concern.  I think we're
> stuck with them now, though, because of per-column permissions.

Because someone might want to grant per-column permissions on those
columns? That seems like an awfully thin reason to keep all that
bloat around. I bet the number of people who have granted per-column
permissions on, say, cmax can be counted on one hand - possibly with
five fingers left over.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Jul 5, 2010 at 2:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> At one time I was hoping to get rid of explicit entries in pg_attribute
>> for system columns, which would negate this concern. I think we're
>> stuck with them now, though, because of per-column permissions.

> Because someone might want to grant per-column permissions on those
> columns? That seems like an awfully thin reason to keep all that
> bloat around. I bet the number of people who have granted per-column
> permissions on, say, cmax can be counted on one hand - possibly with
> five fingers left over.

I'd agree with that argument for the most part, but I'm not entirely
sure about oid, which has some characteristics of a user-data column.

(OTOH, maybe we could allow just oid to retain an explicit pg_attribute
entry... could be messy though.)

regards, tom lane

On Mon, Jul 5, 2010 at 3:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, Jul 5, 2010 at 2:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> At one time I was hoping to get rid of explicit entries in pg_attribute
>>> for system columns, which would negate this concern.  I think we're
>>> stuck with them now, though, because of per-column permissions.
>
>> Because someone might want to grant per-column permissions on those
>> columns?  That seems like an awfully thin reason to keep all that
>> bloat around.  I bet the number of people who have granted per-column
>> permissions on, say, cmax can be counted on one hand - possibly with
>> five fingers left over.
>
> I'd agree with that argument for the most part, but I'm not entirely
> sure about oid, which has some characteristics of a user-data column.
>
> (OTOH, maybe we could allow just oid to retain an explicit pg_attribute
> entry... could be messy though.)

[woops, forgot to reply on-list]

Treating OID as a user-defined column seems reasonable, and probably
not even that messy if we put some appropriate macros in place. I'm
guessing the messy part would be finding all the places that expect to
be consulting a real pg_attribute row and supplying them with a
faked-up one in its place.

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

Excerpts from Robert Haas's message of mar jul 06 10:08:21 -0400 2010:

> Treating OID as a user-defined column seems reasonable, and probably
> not even that messy if we put some appropriate macros in place. I'm
> guessing the messy part would be finding all the places that expect to
> be consulting a real pg_attribute row and supplying them with a
> faked-up one in its place.

Agreed.

I'm intending to work on logical column identifiers for 9.1. Perhaps I
could try to have a look at this, too, while at it.

On Tue, Jul 6, 2010 at 2:49 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Robert Haas's message of mar jul 06 10:08:21 -0400 2010:
>
>> Treating OID as a user-defined column seems reasonable, and probably
>> not even that messy if we put some appropriate macros in place.  I'm
>> guessing the messy part would be finding all the places that expect to
>> be consulting a real pg_attribute row and supplying them with a
>> faked-up one in its place.
>
> Agreed.
>
> I'm intending to work on logical column identifiers for 9.1.  Perhaps I
> could try to have a look at this, too, while at it.

I have a strong suspicion that's going to be a, ahem, challenging
project. But it would be great to have. Getting rid of the system
column entries from pg_attribute is probably easy by comparison.

When we discussed this previously, Tom suggested that we might want to
have a three-tiered structure: (1) permanent identifier (never
changes, used by other system catalogs to reference the attribute in
question), (2) display position, and (3) physical storage position.
I'm not sure if it's feasible to think about splitting out (2) and (3)
in a single patch, but either one would be useful by itself. Which
are you planning to work on?

One other thought for you to mull over. Currently, we can never
really totally get rid of an attribute because it would leave us at a
loss as to how to interpret the tuples already on disk - we can't cope
with HeapTupleHeaderGetNatts ever decreasing. But maybe instead of
storing natts in the tuple header, we could store a "tuple version
number". Whenever a column is added or dropped, physical storage
layout is changed, etc., we bump the tuple version number but retain
the information necessary to interpret old tuple versions. After
CLUSTER or VACUUM FULL, we can forget about all the old tuple
versions. A regular VACUUM can, if it visits the entire table, forget
about all tuple versions other than the latest which are observed not
to be in use. It's a bit awkward if you go to make a change and
discover that all 2047 possible tuple versions are in use, because now
you have to force a table rewrite for an operation that doesn't
normally require one. But in the immortal words of Bill Gates, 640K
ought to be enough for anybody.

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

Robert Haas wrote:
> On Tue, Jul 6, 2010 at 2:49 PM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
>
>>
>> I'm intending to work on logical column identifiers for 9.1. Perhaps I
>> could try to have a look at this, too, while at it.
>>
>
> I have a strong suspicion that's going to be a, ahem, challenging
> project. But it would be great to have. Getting rid of the system
> column entries from pg_attribute is probably easy by comparison.
>

It will be a bit invasive, but I'm not so sure that it's difficult, just
a mass of details to take care of. Like you I'd be very glad to see it done.

> When we discussed this previously, Tom suggested that we might want to
> have a three-tiered structure: (1) permanent identifier (never
> changes, used by other system catalogs to reference the attribute in
> question), (2) display position, and (3) physical storage position.
> I'm not sure if it's feasible to think about splitting out (2) and (3)
> in a single patch, but either one would be useful by itself. Which
> are you planning to work on?
>

Why wouldn't it be feasible? In any case, having a mutable logical
column position is the feature that's been most requested.

cheers

andrew

On Tue, Jul 6, 2010 at 5:18 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> I have a strong suspicion that's going to be a, ahem, challenging
>> project.  But it would be great to have.  Getting rid of the system
>> column entries from pg_attribute is probably easy by comparison.
>
> It will be a bit invasive, but I'm not so sure that it's difficult, just a
> mass of details to take care of. Like you I'd be very glad to see it done.

I guess we'll find out...!

>> When we discussed this previously, Tom suggested that we might want to
>> have a three-tiered structure: (1) permanent identifier (never
>> changes, used by other system catalogs to reference the attribute in
>> question), (2) display position, and (3) physical storage position.
>> I'm not sure if it's feasible to think about splitting out (2) and (3)
>> in a single patch, but either one would be useful by itself.  Which
>> are you planning to work on?
>
> Why wouldn't it be feasible?

Just because it might be too much to do all at once.

> In any case, having a mutable logical column
> position is the feature that's been most requested.

I think that's true. But the physical storage position would give us
a performance benefit, by allowing us to try to avoid useless
alignment padding.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Jul 6, 2010 at 5:18 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> Why wouldn't it be feasible?

> Just because it might be too much to do all at once.

My thought is that the hardest part of this is going to be making sure
that every "column index" usage in the code is properly categorized as
to whether it's physical, logical, or identifier index. If we try to
divide the problem into sub-patches, that will probably just increase
the amount of effort because all that code will have to be looked at
twice.

Think of it as Polya's paradox in action.

regards, tom lane

Excerpts from Robert Haas's message of mar jul 06 17:24:21 -0400 2010:
> On Tue, Jul 6, 2010 at 5:18 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

> > In any case, having a mutable logical column
> > position is the feature that's been most requested.
>
> I think that's true. But the physical storage position would give us
> a performance benefit, by allowing us to try to avoid useless
> alignment padding.

That's true too. I intend to look at both problems simultaneously, i.e.
decoupling the current attnum in three columns as previously discussed;
as Tom says, I think it'll end up being less work than attacking them
separately. However, I will not attempt to include optimizations such
as avoiding padding, in the first patch, just the possibility that it is
added later.

On Wed, Jul 7, 2010 at 1:29 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Robert Haas's message of mar jul 06 17:24:21 -0400 2010:
>> On Tue, Jul 6, 2010 at 5:18 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>> > In any case, having a mutable logical column
>> > position is the feature that's been most requested.
>>
>> I think that's true.  But the physical storage position would give us
>> a performance benefit, by allowing us to try to avoid useless
>> alignment padding.
>
> That's true too.  I intend to look at both problems simultaneously, i.e.
> decoupling the current attnum in three columns as previously discussed;
> as Tom says, I think it'll end up being less work than attacking them
> separately.  However, I will not attempt to include optimizations such
> as avoiding padding, in the first patch, just the possibility that it is
> added later.

Sounds great.

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