pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
strange system columns
3+ messages / 3 participants
[nested] [flat]

* strange system columns
@ 2006-01-05 20:17 Colton A Smith <smith@cs.utk.edu>
  2006-01-12 05:11 ` Re: strange system columns Tom Lane <tgl@sss.pgh.pa.us>
  2006-01-12 05:16 ` Re: strange system columns Michael Fuhr <mike@fuhr.org>
  0 siblings, 2 replies; 3+ messages in thread

From: Colton A Smith @ 2006-01-05 20:17 UTC (permalink / raw)
  To: 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!







^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: strange system columns
  2006-01-05 20:17 strange system columns Colton A Smith <smith@cs.utk.edu>
@ 2006-01-12 05:11 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2006-01-12 05:11 UTC (permalink / raw)
  To: Colton A Smith <smith@cs.utk.edu>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: strange system columns
  2006-01-05 20:17 strange system columns Colton A Smith <smith@cs.utk.edu>
@ 2006-01-12 05:16 ` Michael Fuhr <mike@fuhr.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Michael Fuhr @ 2006-01-12 05:16 UTC (permalink / raw)
  To: Colton A Smith <smith@cs.utk.edu>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-01-12 05:16 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-05 20:17 strange system columns Colton A Smith <smith@cs.utk.edu>
2006-01-12 05:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-12 05:16 ` Michael Fuhr <mike@fuhr.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox