Re: Get rid of system attributes in pg_attribute?

-----Original Message-----
From: pgsql-hackers-owner(at)postgresql(dot)org on behalf of Tom Lane
Sent: Fri 2/18/2005 8:48 PM
To: pgsql-hackers(at)postgresql(dot)org
Subject: [HACKERS] Get rid of system attributes in pg_attribute?

> Does anyone know of client code that actually pays attention
> to pg_attribute rows with negative attnums?

pgAdmin certainly knows about them, but I don't believe it'll break if they go. I'm a few thousand miles from my laptop atm though so I cannot look more throughly right now.

Regards, Dave

Dave Page wrote:
>
>> Does anyone know of client code that actually pays attention to
>> pg_attribute rows with negative attnums?
>
> pgAdmin certainly knows about them, but I don't believe it'll break
> if they go.

It only knows that attnum < 0 must be a system column; no specific
knowledge or interpretation of it.
Would those columns remain selectable for debugging/maintenance
purposes, despite not appearing in system catalogs?

Regards,
Andreas

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
>>> Does anyone know of client code that actually pays attention to
>>> pg_attribute rows with negative attnums?

> Would those columns remain selectable for debugging/maintenance
> purposes, despite not appearing in system catalogs?

Certainly. They just wouldn't have entries in pg_attribute.

It occurs to me that without the explicit entries, we could stop
considering the system names to be reserved column names --- that is,
we could allow users to create ordinary columns by these names.
(The procedure for looking up a column name would be to first try in
pg_attribute, and if that failed to check an internal list of system
column names.) If you did make such a column, then you'd be unable to
get at the system column you'd masked in that particular table. I'm
unsure offhand if this would be a good thing or bad. Not having
reserved column names is certainly good, but masking a system column
is something you might regret when you need to debug. I suppose you
could always rename the conflicting column if so.

Making the system column names un-reserved would be a very good thing
from the point of view of being able to add more. I've wished for
some time that there were a system column exposing the tuple flags
(t_infomask). I've not dared to propose adding it because of the
likelihood of breaking people's table definitions, but if the name
needn't be reserved then that objection goes away.

regards, tom lane

On Saturday 19 February 2005 12:17, Tom Lane wrote:
> Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> >>> Does anyone know of client code that actually pays attention to
> >>> pg_attribute rows with negative attnums?
> >
> > Would those columns remain selectable for debugging/maintenance
> > purposes, despite not appearing in system catalogs?
>
> Certainly. They just wouldn't have entries in pg_attribute.
>

If I am understanding this correctly, they could only be displayed if selected
explicitly right? So any program that attempts to show all "hidden" columns
by just doing a "where attnum < 1" is going to break, correct?

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> If I am understanding this correctly, they could only be displayed if selected
> explicitly right?

That's always been true. The behavior at the level of SQL commands
wouldn't change. The question is whether any apps out there examine
pg_attribute and expect these rows to be present. Most of the code
I've seen that examines pg_attribute explicitly disregards rows with
attnum < 0 ...

regards, tom lane

On Sunday 20 February 2005 00:25, Tom Lane wrote:
> Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> > If I am understanding this correctly, they could only be displayed if
> > selected explicitly right?
>
> That's always been true. The behavior at the level of SQL commands
> wouldn't change. The question is whether any apps out there examine
> pg_attribute and expect these rows to be present. Most of the code
> I've seen that examines pg_attribute explicitly disregards rows with
> attnum < 0 ...
>

One of us is not understanding the other :-) I'm asking if I have a piece of
code that does something like select attname from pg_attribute where attrelid
= 'stock'::regclass::oid with the intent of displaying all those attnames,
then the "system atts" will no longer show up in that list, correct? I'm
asking cause I have some code that does something like this at work so
wondering if I need to do some further investigating come Tuesday morning.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> One of us is not understanding the other :-) I'm asking if I have a piece of
> code that does something like select attname from pg_attribute where attrelid
> = 'stock'::regclass::oid with the intent of displaying all those attnames,
> then the "system atts" will no longer show up in that list, correct?

Correct. What I'm asking is whether that's a problem for anyone.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> It occurs to me that without the explicit entries, we could stop
> considering the system names to be reserved column names --- that is,
> we could allow users to create ordinary columns by these names.
> (The procedure for looking up a column name would be to first try in
> pg_attribute, and if that failed to check an internal list of system
> column names.) If you did make such a column, then you'd be unable to
> get at the system column you'd masked in that particular table. I'm
> unsure offhand if this would be a good thing or bad.

This sounds bad to me. Maybe not for things like cmin and cmax, but I
use ctid a lot, and would be quite thrown off if a table suddenly were
allowed to create it's own ctid column that did not behave as the current
one does. Perhaps if it was called "pg_ctid?" 1/2 :)

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200502211318
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFCGiY0vJuQZxSWSsgRArjHAKDRsZ47E52fgJXDPPe5SUPoy7mqhACfY9eW
QJXKFq0ZTIBnXtodNqXDZig=
=kdBu
-----END PGP SIGNATURE-----

On Sunday 20 February 2005 12:30, Tom Lane wrote:
> Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> > One of us is not understanding the other :-) I'm asking if I have a
> > piece of code that does something like select attname from pg_attribute
> > where attrelid = 'stock'::regclass::oid with the intent of displaying all
> > those attnames, then the "system atts" will no longer show up in that
> > list, correct?
>
> Correct. What I'm asking is whether that's a problem for anyone.
>

OK... I can't seem to find my theoretically problem code so I guess it is in
the clear (the code I can find references the system columns explicitly) One
thing I wonder about is will this toss driver implementors a loop? ISTR a
flag in the ODBC driver whether to include the oid column (or maybe system
columns)... could be some trouble there.

One other question, do you see a scheme for selecting system columns even
explicitly once a user has created their own column with a conflicting name.
ISTM that we wouldn't be able to select the system ctid once a user creates
thier own ctid column... somewhere in the back of my head a voice is
grumbling about sql specs and multiple columns with the same name in a table.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL