Re: [GENERAL] [BUGS] pg_tables view definition incorrect??

OK, sequences removed from pg_tables.  Regression updated.  Patch
applied.

---------------------------------------------------------------------------

Mike Quinn wrote:
> The inclusion of the relkind='s' made me think that something other than tables
> were to be included as well. I don't see any problem with the original intention
> of returning only tables. The view now is operating "almost" as designed. It does
> return "pg_xactlock" which is a special relation. The relkind='s' should be removed
> in order to conform with the intention.
>
> Mike Quinn
>
> >>> Tom Lane <tgl@sss.pgh.pa.us> 7/10/03 8:24:43 AM >>>
> "Mike Quinn" <mquinn@co.merced.ca.us> writes:
> > Given that 'S' => Sequence and 's' => special shouldn't the last condition in the WHERE clause be:
> > c.relkind = 'S'::"char"
>
> Only if you suppose that pg_tables should show sequences, which was not
> the intention.  (I'm not real sure why 's' relations are listed, since
> they're not really tables either, but the view is operating as
> designed.)
>
> I'm prepared to listen to an argument that pg_tables should show
> sequences, but that's a definition change not a bug fix.  Want to
> bring it up on -general or -hackers?
>
>             regards, tom lane
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: src/bin/initdb/initdb.sh
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/initdb/initdb.sh,v
retrieving revision 1.196
diff -c -c -r1.196 initdb.sh
*** src/bin/initdb/initdb.sh    21 Jul 2003 15:20:27 -0000    1.196
--- src/bin/initdb/initdb.sh    21 Jul 2003 22:00:37 -0000
***************
*** 786,792 ****
          C.relhasrules AS hasrules, \
          (C.reltriggers > 0) AS hastriggers \
      FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
!     WHERE C.relkind IN ('r', 'S');
  
  CREATE VIEW pg_indexes AS \
      SELECT \
--- 786,792 ----
          C.relhasrules AS hasrules, \
          (C.reltriggers > 0) AS hastriggers \
      FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
!     WHERE C.relkind = 'r';
  
  CREATE VIEW pg_indexes AS \
      SELECT \
Index: src/test/regress/expected/rules.out
===================================================================
RCS file: /cvsroot/pgsql-server/src/test/regress/expected/rules.out,v
retrieving revision 1.76
diff -c -c -r1.76 rules.out
*** src/test/regress/expected/rules.out    19 Jul 2003 20:20:52 -0000    1.76
--- src/test/regress/expected/rules.out    21 Jul 2003 22:00:42 -0000
***************
*** 1292,1298 ****
   pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname,
pg_statio_all_sequences.relname,pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM
pg_statio_all_sequencesWHERE ((pg_statio_all_sequences.schemaname <> 'pg_catalog'::name) AND
(pg_statio_all_sequences.schemaname<> 'pg_toast'::name));
 
   pg_statio_user_tables    | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname,
pg_statio_all_tables.relname,pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit,
pg_statio_all_tables.idx_blks_read,pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read,
pg_statio_all_tables.toast_blks_hit,pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM
pg_statio_all_tablesWHERE ((pg_statio_all_tables.schemaname <> 'pg_catalog'::name) AND (pg_statio_all_tables.schemaname
<>'pg_toast'::name));
 
   pg_stats                 | SELECT nspname AS schemaname, relname AS tablename, attname, stanullfrac AS null_frac,
stawidthAS avg_width, stadistinct AS n_distinct, CASE WHEN (1 = stakind1) THEN stavalues1 WHEN (1 = stakind2) THEN
stavalues2WHEN (1 = stakind3) THEN stavalues3 WHEN (1 = stakind4) THEN stavalues4 ELSE NULL::anyarray END AS
most_common_vals,CASE WHEN (1 = stakind1) THEN stanumbers1 WHEN (1 = stakind2) THEN stanumbers2 WHEN (1 = stakind3)
THENstanumbers3 WHEN (1 = stakind4) THEN stanumbers4 ELSE NULL::real[] END AS most_common_freqs, CASE WHEN (2 =
stakind1)THEN stavalues1 WHEN (2 = stakind2) THEN stavalues2 WHEN (2 = stakind3) THEN stavalues3 WHEN (2 = stakind4)
THENstavalues4 ELSE NULL::anyarray END AS histogram_bounds, CASE WHEN (3 = stakind1) THEN stanumbers1[1] WHEN (3 =
stakind2)THEN stanumbers2[1] WHEN (3 = stakind3) THEN stanumbers3[1] WHEN (3 = stakind4) THEN stanumbers4[1] ELSE
NULL::realEND AS correlation FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON
(((c.oid= a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE
has_table_privilege(c.oid,'select'::text);
 
!  pg_tables                | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS
tableowner,c.relhasindex AS hasindexes, c.relhasrules AS hasrules, (c.reltriggers > 0) AS hastriggers FROM (pg_class c
LEFTJOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") OR (c.relkind = 's'::"char"));
 
   pg_user                  | SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesuper,
pg_shadow.usecatupd,'********'::text AS passwd, pg_shadow.valuntil, pg_shadow.useconfig FROM pg_shadow;
 
   pg_views                 | SELECT n.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS
viewowner,pg_get_viewdef(c.oid) AS definition FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE(c.relkind = 'v'::"char");
 
   rtest_v1                 | SELECT rtest_t1.a, rtest_t1.b FROM rtest_t1;
--- 1292,1298 ----
   pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname,
pg_statio_all_sequences.relname,pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM
pg_statio_all_sequencesWHERE ((pg_statio_all_sequences.schemaname <> 'pg_catalog'::name) AND
(pg_statio_all_sequences.schemaname<> 'pg_toast'::name));
 
   pg_statio_user_tables    | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname,
pg_statio_all_tables.relname,pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit,
pg_statio_all_tables.idx_blks_read,pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read,
pg_statio_all_tables.toast_blks_hit,pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM
pg_statio_all_tablesWHERE ((pg_statio_all_tables.schemaname <> 'pg_catalog'::name) AND (pg_statio_all_tables.schemaname
<>'pg_toast'::name));
 
   pg_stats                 | SELECT nspname AS schemaname, relname AS tablename, attname, stanullfrac AS null_frac,
stawidthAS avg_width, stadistinct AS n_distinct, CASE WHEN (1 = stakind1) THEN stavalues1 WHEN (1 = stakind2) THEN
stavalues2WHEN (1 = stakind3) THEN stavalues3 WHEN (1 = stakind4) THEN stavalues4 ELSE NULL::anyarray END AS
most_common_vals,CASE WHEN (1 = stakind1) THEN stanumbers1 WHEN (1 = stakind2) THEN stanumbers2 WHEN (1 = stakind3)
THENstanumbers3 WHEN (1 = stakind4) THEN stanumbers4 ELSE NULL::real[] END AS most_common_freqs, CASE WHEN (2 =
stakind1)THEN stavalues1 WHEN (2 = stakind2) THEN stavalues2 WHEN (2 = stakind3) THEN stavalues3 WHEN (2 = stakind4)
THENstavalues4 ELSE NULL::anyarray END AS histogram_bounds, CASE WHEN (3 = stakind1) THEN stanumbers1[1] WHEN (3 =
stakind2)THEN stanumbers2[1] WHEN (3 = stakind3) THEN stanumbers3[1] WHEN (3 = stakind4) THEN stanumbers4[1] ELSE
NULL::realEND AS correlation FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON
(((c.oid= a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE
has_table_privilege(c.oid,'select'::text);
 
!  pg_tables                | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS
tableowner,c.relhasindex AS hasindexes, c.relhasrules AS hasrules, (c.reltriggers > 0) AS hastriggers FROM (pg_class c
LEFTJOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char");
 
   pg_user                  | SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesuper,
pg_shadow.usecatupd,'********'::text AS passwd, pg_shadow.valuntil, pg_shadow.useconfig FROM pg_shadow;
 
   pg_views                 | SELECT n.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS
viewowner,pg_get_viewdef(c.oid) AS definition FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE(c.relkind = 'v'::"char");
 
   rtest_v1                 | SELECT rtest_t1.a, rtest_t1.b FROM rtest_t1;