Re: [HACKERS] Get rid of system attributes in pg_attribute?
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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Get rid of system attributes in pg_attribute?
On Sunday 20 February 2005 12:30, Tom Lane wrote:
Robert Treat [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Get rid of system attributes in pg_attribute?
Robert Treat [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Get rid of system attributes in pg_attribute?
On Tuesday 22 February 2005 10:32, Tom Lane wrote:
Robert Treat [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Get rid of system attributes in pg_attribute?
Robert Treat [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Get rid of system attributes in pg_attribute?
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Get rid of system attributes in pg_attribute?
Andreas Pflug [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Get rid of system attributes in pg_attribute?
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 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? Well, apart from a attnum 0 clause in phpPgAdmin, I don't think so... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Get rid of system attributes in pg_attribute?
-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 [EMAIL PROTECTED] PGP Key: 0x14964AC8 200502211318 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCGiY0vJuQZxSWSsgRArjHAKDRsZ47E52fgJXDPPe5SUPoy7mqhACfY9eW QJXKFq0ZTIBnXtodNqXDZig= =kdBu -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Get rid of system attributes in pg_attribute?
On Sunday 20 February 2005 00:25, Tom Lane wrote:
Robert Treat [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Get rid of system attributes in pg_attribute?
Robert Treat [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Get rid of system attributes in pg_attribute?
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Get rid of system attributes in pg_attribute?
Andreas Pflug [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Get rid of system attributes in pg_attribute?
On Saturday 19 February 2005 12:17, Tom Lane wrote:
Andreas Pflug [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Get rid of system attributes in pg_attribute?
Robert Treat [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Get rid of system attributes in pg_attribute?
-Original Message- From: [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
