Thread: Get rid of system attributes in pg_attribute?
I'm wondering how useful it is to store explicit representations of the
system attributes in pg_attribute. We could very easily hard-wire those
things instead, which would make for a large reduction in the number of
entries in pg_attribute. (In the current regression database nearly
half of the rows have attnum < 0.) I think the impact on the backend
would be pretty minimal, but I'm wondering if removing these entries
would be likely to break any client-side code. Does anyone know of
client code that actually pays attention to pg_attribute rows with
negative attnums?
regards, tom lane
-----Original Message----- From: pgsql-hackers-owner@postgresql.org on behalf of Tom Lane Sent: Fri 2/18/2005 8:48 PM To: pgsql-hackers@postgresql.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 atmthough 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@pse-consulting.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@pse-consulting.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@users.sourceforge.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@users.sourceforge.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@users.sourceforge.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
> I'm wondering how useful it is to store explicit representations of the > system attributes in pg_attribute. We could very easily hard-wire those > things instead, which would make for a large reduction in the number of > entries in pg_attribute. (In the current regression database nearly > half of the rows have attnum < 0.) I think the impact on the backend > would be pretty minimal, but I'm wondering if removing these entries > would be likely to break any client-side code. Does anyone know of > client code that actually pays attention to pg_attribute rows with > negative attnums? Well, apart from a "attnum > 0" clause in phpPgAdmin, I don't think so... Chris
-----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@turnstep.com PGP Key: 0x14964AC8 200502211318 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFCGiY0vJuQZxSWSsgRArjHAKDRsZ47E52fgJXDPPe5SUPoy7mqhACfY9eW QJXKFq0ZTIBnXtodNqXDZig= =kdBu -----END PGP SIGNATURE-----
On Monday 21 February 2005 04:23, Christopher Kings-Lynne wrote:
> > I'm wondering how useful it is to store explicit representations of the
> > system attributes in pg_attribute. We could very easily hard-wire those
> > things instead, which would make for a large reduction in the number of
> > entries in pg_attribute. (In the current regression database nearly
> > half of the rows have attnum < 0.) I think the impact on the backend
> > would be pretty minimal, but I'm wondering if removing these entries
> > would be likely to break any client-side code. Does anyone know of
> > client code that actually pays attention to pg_attribute rows with
> > negative attnums?
>
> Well, apart from a "attnum > 0" clause in phpPgAdmin, I don't think so...
>
Well, the corner case would be for those times when we use oid for updating
specific rows in a table, if a user creates there own oid column then you
could have trouble. Actually we already have a safegaurd for this in
phppgadmin so we wont cause mistakes, it's just that those updates probably
won't work... others might not have been so thorough though.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On Sunday 20 February 2005 12:30, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.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
Robert Treat <xzilla@users.sourceforge.net> writes:
>>> Does anyone know of
>>> client code that actually pays attention to pg_attribute rows with
>>> negative attnums?
> Well, the corner case would be for those times when we use oid for updating
> specific rows in a table, if a user creates there own oid column then you
> could have trouble. Actually we already have a safegaurd for this in
> phppgadmin so we wont cause mistakes, it's just that those updates probably
> won't work... others might not have been so thorough though.
Anyone who's not checking that has been at risk ever since we invented
WITHOUT OIDS:
regression=# create table foo (oid text);
ERROR: column name "oid" conflicts with a system column name
regression=# create table foo (oid text) without oids;
CREATE TABLE
Probably ctid is the more interesting case; I'm pretty sure ODBC relies
on ctid as a short-term-unique row identifier.
regards, tom lane
On Tuesday 22 February 2005 10:32, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> >>> Does anyone know of
> >>> client code that actually pays attention to pg_attribute rows with
> >>> negative attnums?
> >
> > Well, the corner case would be for those times when we use oid for
> > updating specific rows in a table, if a user creates there own oid column
> > then you could have trouble. Actually we already have a safegaurd for
> > this in phppgadmin so we wont cause mistakes, it's just that those
> > updates probably won't work... others might not have been so thorough
> > though.
>
> Anyone who's not checking that has been at risk ever since we invented
> WITHOUT OIDS:
>
> regression=# create table foo (oid text);
> ERROR: column name "oid" conflicts with a system column name
> regression=# create table foo (oid text) without oids;
> CREATE TABLE
>
Actually I was thinking more the case where someone creates their own column
names oid where they have no intention of those values being unique. If you
weren't already checking for duplicate oid's you could be in for trouble.
> Probably ctid is the more interesting case; I'm pretty sure ODBC relies
> on ctid as a short-term-unique row identifier.
>
Yeah... how many utility tools out there reference system columns explicitly?
I think we need a scheme for allowing them to keep working even with user
defined columns of the same name.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Robert Treat <xzilla@users.sourceforge.net> writes:
> On Tuesday 22 February 2005 10:32, Tom Lane wrote:
>> Probably ctid is the more interesting case; I'm pretty sure ODBC relies
>> on ctid as a short-term-unique row identifier.
> Yeah... how many utility tools out there reference system columns explicitly?
> I think we need a scheme for allowing them to keep working even with user
> defined columns of the same name.
Well, that probably knocks out my thought that we could stop reserving
the system column names (at least ctid and xmin, which are the two that
actually seem useful to ordinary clients, need to stay reserved). But
it still seems like we don't have to represent these columns explicitly
in pg_attribute.
regards, tom lane
Tom Lane wrote: > Well, that probably knocks out my thought that we could stop reserving > the system column names (at least ctid and xmin, which are the two that > actually seem useful to ordinary clients, need to stay reserved). But > it still seems like we don't have to represent these columns explicitly > in pg_attribute. Hm, technically you might be right. Still, I like pgAdmin3 to show that columns (when "show system objects" is enabled) for teaching purposes, so users/newbies browsing the objects will learn "hey, there are some reserved columns, they could have some meaning". I'd be not too excited about emulating system column pg_attribute entries... Regards, Andreas
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Tom Lane wrote:
>> it still seems like we don't have to represent these columns explicitly
>> in pg_attribute.
> Hm, technically you might be right. Still, I like pgAdmin3 to show that
> columns (when "show system objects" is enabled) for teaching purposes,
> so users/newbies browsing the objects will learn "hey, there are some
> reserved columns, they could have some meaning".
Not unreasonable, but is it worth a factor of 2 in the size of
pg_attribute?
regards, tom lane
Tom Lane wrote: > Andreas Pflug <pgadmin@pse-consulting.de> writes: > >>Tom Lane wrote: >> >>>it still seems like we don't have to represent these columns explicitly >>>in pg_attribute. > > >>Hm, technically you might be right. Still, I like pgAdmin3 to show that >>columns (when "show system objects" is enabled) for teaching purposes, >>so users/newbies browsing the objects will learn "hey, there are some >>reserved columns, they could have some meaning". > > > Not unreasonable, but is it worth a factor of 2 in the size of > pg_attribute? Do we need to save space? On a DB with quite some tables I have pg_attribute size=7.5MB, pg_class size 5.8MB (13166 pg_attribute rows total, 5865 system columns, most tables without oids). This doesn't seem unacceptable big to me. Regards, Andreas