strange system columns

From: Colton A Smith <smith(at)cs(dot)utk(dot)edu>
To: pgsql-admin(at)postgresql(dot)org
Subject: strange system columns
Date: 2006-01-05 20:17:02
Message-ID: Pine.LNX.4.62.0601051447080.7355@cetus5.cs.utk.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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_id_seq'::text)::regclass)
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_matter_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_measurement_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_converter()

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!

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Jim C. Nasby 2006-01-05 21:02:10 Re: preventing deadlocks
Previous Message Guido Barosio 2006-01-05 17:30:13 Re: Vacuum is needed or not?