SEO

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 majordomo@postgresql.org

> 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

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

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

---------------------------(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

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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

---------------------------(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