Обсуждение: strange system columns

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!

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

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'::regclass AND NOT attisdropped;

--
Michael Fuhr