|
Home > Archive > PostgreSQL Administration > January 2006 > strange system columns
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
strange system columns
|
|
| Colton A Smith 2006-01-12, 3:24 am |
| Hi:
I use v8.1.0, and have a table named pressure:
Table "public.pressure"
Column | Type |
Modifiers
------------------+-----------------------------+---------------------------------------------------------------
pressure_id | integer | not null default
nextval(('pressure_i
d_seq'::text)::regcl
XXX)
row_entry_date | timestamp with time zone |
sensor_id | integer | not null
measurement_date | timestamp without time zone |
pressure | double precision |
Indexes:
"pressure_pkey" PRIMARY KEY, btree (pressure_id)
Foreign-key constraints:
"sensor_id_exists" FOREIGN KEY (sensor_id) REFERENCES
sensor(sensor_id) ON DELETE RESTRICT
Tablespace: "diskvol2"
When I query the system tables to get a more complete listing, I get:
attname | typname | attnum
------------------+--------------+--------
oid | oidvector | -2
tableoid | oidvector | -7
pressure | point | 5
pressure | line | 5
sensor_id | _int4 | 3
pressure_id | _int4 | 1
oid | _oid | -2
tableoid | _oid | -7
ctid | _tid | -1
xmin | _xid | -3
xmax | _xid | -5
cmin | _cid | -4
cmax | _cid | -6
pressure | _float8 | 5
measurement_date | _timestamp | 4
row_entry_date | _timestamptz | 2
(16 rows)
My question: where did entries 3 and 4 come from?
I certainly didn't add them.
Looking at the rest of my tables, wherever I have a column datatyped as
float, I get the same result, i.e., two 'shadow' columns typed point and
line. Take my organic_matter table, for example:
Table "public.organic_matter"
Column | Type |
Modifiers
-------------------+-----------------------------+---------------------------------------------------------------------
organic_matter_id | integer | not null default
nextval(('organic_ma
tter_id_seq'::text):
:regclass)
row_entry_date | timestamp with time zone |
sensor_id | integer | not null
measurement_date | timestamp without time zone |
dom | double precision |
year | integer |
month | integer |
day | integer |
Indexes:
"organic_matter_pkey" PRIMARY KEY, btree (organic_matter_id)
" organic_matter_measu
rement_date_index" btree (measurement_date)
Foreign-key constraints:
"sensor_id_exists" FOREIGN KEY (sensor_id) REFERENCES
sensor(sensor_id) ON DELETE RESTRICT
Triggers:
converter AFTER INSERT ON organic_matter FOR EACH ROW EXECUTE
PROCEDURE organic_time_convert
er()
And then:
attname | typname | attnum
-------------------+--------------+--------
oid | oidvector | -2
tableoid | oidvector | -7
dom | point | 5
dom | line | 5
day | _int4 | 8
month | _int4 | 7
year | _int4 | 6
sensor_id | _int4 | 3
organic_matter_id | _int4 | 1
oid | _oid | -2
tableoid | _oid | -7
ctid | _tid | -1
xmin | _xid | -3
xmax | _xid | -5
cmin | _cid | -4
cmax | _cid | -6
dom | _float8 | 5
measurement_date | _timestamp | 4
row_entry_date | _timestamptz | 2
(19 rows)
The latter was derived using a series
of two queries:
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog. pg_table_is_visible(
c.oid)
AND c.relname = '$table_name'
ORDER BY 2, 3;
SELECT attname, typname, attnum
FROM pg_attribute INNER JOIN pg_type
ON pg_attribute.atttypid = pg_type.typelem
WHERE pg_attribute.attrelid = $oid (derived above)
AND NOT attisdropped;
Can anyone give me a clue? For what it's worth, I've recently upgraded
from 8.0.4 to 8.1.0.
Thanks!
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Tom Lane 2006-01-12, 3:24 am |
| Colton A Smith <smith@cs.utk.edu> writes:
> SELECT attname, typname, attnum
> FROM pg_attribute INNER JOIN pg_type
> ON pg_attribute.atttypid = pg_type.typelem
> WHERE pg_attribute.attrelid = $oid (derived above)
> AND NOT attisdropped;
I think you meant to join to pg_type.oid, not pg_type.typelem.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Michael Fuhr 2006-01-12, 3:24 am |
| On Thu, Jan 05, 2006 at 03:17:02PM -0500, Colton A Smith wrote:
> SELECT attname, typname, attnum
> FROM pg_attribute INNER JOIN pg_type
> ON pg_attribute.atttypid = pg_type.typelem
> WHERE pg_attribute.attrelid = $oid (derived above)
> AND NOT attisdropped;
The join condition for pg_type should be against pg_type.oid, not
pg_type.typelem. A simplified version of your two queries is:
SELECT attname, atttypid::regtype, attnum
FROM pg_attribute
WHERE attrelid = 'organic_matter'::re
gclass AND NOT attisdropped;
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
|
|
|
|