pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
view on system tables upgrade II
2+ messages / 2 participants
[nested] [flat]

* view on system tables upgrade II
@ 1970-01-01 00:00  Christoph Haller <ch@rodos.fzk.de>
  0 siblings, 1 reply; 2+ messages in thread

From: Christoph Haller @ 1970-01-01 00:00 UTC (permalink / raw)
  To: tgl@sss.pgh.pa.us; +Cc: pgsql-sql

Tom Lane wrote:
>
> There's a bug in the 7.2.* pg_type table, which I found just a
> couple days ago: _timestamp has the wrong typelem.  Since your join
> assumes every datatype has an array type, it fails to find a join
> for timestamp columns.  (Should probably use an outer join there,
> rather than assuming that.) 
Sorry, but I cannot figure out where in the join 
SELECT 
upper(u.usename) AS TBL_OWNER, upper(t.typname) AS TBL_NAME, 
upper(a.attname) AS COL_NAME, a.atttypid AS COL_TYPE, 
int4larger(a.attlen, a.atttypmod - 4) AS COL_LENGTH, 
CASE WHEN a.attnotnull=true THEN 0 ELSE 1 END AS COL_NULL, a.attnum AS COL_SEQ, 
 CASE WHEN EXISTS(SELECT adsrc FROM pg_attrdef d 
 WHERE d.adrelid = a.attrelid and d.adnum = a.attnum) THEN 
 1
 ELSE
 0 
 END AS COL_DEFAULT 
from pg_user u, pg_type t, pg_attribute a, pg_type n 
where u.usesysid = t.typowner 
and t.typrelid = a.attrelid and t.typtype = 'c' and not (t.typname ~* 'pg_') 
and n.typelem = a.atttypid 
and substr(n.typname, 1, 1) = '_' 
and a.attnum > 0 ;
I am assuming every datatype has an array type. 
Would an outer join solve the current bug? 
> 
> If you need timestamp arrays I'd suggest
> 
> UPDATE pg_type SET typelem = 1114 WHERE oid = 1115;
> 
Did the pg_attribute.atttypid resp. pg_type.typelem of timestamp change from 
1184 to 1114? 
If this bug is fixed, what would it become, 1184 or 1114? 

Regards, Christoph 



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

* Re: view on system tables upgrade II
@ 2002-04-29 14:02  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Christoph Haller <ch@rodos.fzk.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2002-04-29 14:02 UTC (permalink / raw)
  To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql

Christoph Haller <ch@rodos.fzk.de> writes:
> Sorry, but I cannot figure out where in the join 
> ...
> from pg_user u, pg_type t, pg_attribute a, pg_type n 
> where u.usesysid = t.typowner 
> and t.typrelid = a.attrelid and t.typtype = 'c' and not (t.typname ~* 'pg_') 
> and n.typelem = a.atttypid 
      ^^^^^^^^^^^^^^^^^^^^^^
> and substr(n.typname, 1, 1) = '_' 
> and a.attnum > 0 ;
> I am assuming every datatype has an array type. 

The row "pg_type n" is the array type corresponding to the datatype of
the "pg_attribute a" row.  If there is no such array type, no join.
Since the select isn't actually doing anything with the "n" row, I'm
not sure why it's there at all.

Another thing that's slightly bizarre about this code is that it joins
attrelid to pg_type.typrelid, rather than pg_class.oid.  I'd be inclined
to write the join as

from pg_attribute a,
     pg_class c left join pg_user u on (u.usesysid = c.relowner)
where c.oid = a.attrelid and not (c.relname ~* 'pg_') and a.attnum > 0 ;

The outer join against pg_user guarantees that you won't miss tables
that have no owning user.

>> If you need timestamp arrays I'd suggest
>> 
>> UPDATE pg_type SET typelem = 1114 WHERE oid = 1115;
>> 
> Did the pg_attribute.atttypid resp. pg_type.typelem of timestamp change from 
> 1184 to 1114? 

1184 is timestamptz, 1114 is timestamp.  But both _timestamptz and
_timestamp are pointing at 1184 as their typelem.  I imagine this was
a cut-and-paste error...

			regards, tom lane



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


end of thread, other threads:[~2002-04-29 14:02 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1970-01-01 00:00 view on system tables upgrade II Christoph Haller <ch@rodos.fzk.de>
2002-04-29 14:02 ` Tom Lane <tgl@sss.pgh.pa.us>

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