Обсуждение: 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