pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / 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> 2002-04-29 14:02 ` Re: view on system tables upgrade II Tom Lane <tgl@sss.pgh.pa.us> 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 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> 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