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 atm though so I cannot look more throughly right now.
Regards, Dave
Import Notes
Resolved by subject fallback
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