Thread: view on system tables upgrade?

I've moved from 7.1.2 to 7.2.1 and now 
a view does no longer work as intended 

create view pgsql_usertables as 
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_attrdefd WHERE d.adrelid = a.attrelid and d.adnum = a.attnum) THEN 1ELSE0 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 ;

CREATE TABLE catsource(sid        INTEGER        NOT NULL, name        VARCHAR(64)     NOT NULL, entrancetime
DATETIME   NOT NULL
 
) ; 

select col_type,col_length,col_null,col_seq,col_name,col_default from pgsql_usertables where tbl_name='CATSOURCE' order
bycol_seq ; 
 

retrieves col_type | col_length | col_null | col_seq | col_name | col_default
----------+------------+----------+---------+----------+-------------      23 |          4 |        0 |       1 | SID
  |           0    1043 |         64 |        0 |       2 | NAME     |           0
 
(2 rows)

All columns of type timestamp have disappeared. 
Can somebody please give me a hint what happened. 

Regards, Christoph 

Christoph Haller <ch@rodos.fzk.de> writes:
> All columns of type timestamp have disappeared. 
> Can somebody please give me a hint what happened. 

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.)

If you need timestamp arrays I'd suggest

UPDATE pg_type SET typelem = 1114 WHERE oid = 1115;

Unfortunately we cannot fix this in the 7.2.* series since we have no
way to fix it in the distribution short of initdb.  It'll be fixed
in the 7.3 release though.
        regards, tom lane

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_attrdefd WHERE d.adrelid = a.attrelid and d.adnum = a.attnum) THEN 1ELSE0 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 

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