[BUGS] Unexpected omission of tables with duplicate names across schemas

[Chris Ross]

  When there is a table (or view, or sequence) of the same name in one 
schema as another, and both of these schemas are in the set search_path, 
only the first schema in the search path will show that name in the 
output of \d[S+].  (Also \dt, \dv, etc)


PostgreSQL versions:  8.2.x, 8.3.x, 8.4.4, 9.0.0
Operating System: Linux (Ubuntu 10 and RedHat EL5 tested)

  Running the attached file to create tables in schemas, then running 
the below commands show output as noted.  At the bottom, I have also 
simulated what I expect the output to be.


  I *think* this is a bug.  I did not expect filtration to occur with 
table/view names in the output of a simple "\d".  This also seems to 
prevent display of any tables where a table of the same name exists in 
the pg_catalog schema.


  Thank you.  Please let me know if this is a known issue, or if 
there's anything I can do to help describe the problem.


   - Chris


% \i pgsql9.bugshow-database.sql
% \d
   List of relations
 Schema | Name | Type  | Owner
+--+---+---
 test2  | test_table   | table | cross
 test2  | test_table_2 | table | cross
(2 rows)

% set search_path to test1,test2;
% \d

Output:

   List of relations
 Schema | Name | Type  | Owner
+--+---+---
 test1  | test_table   | table | cross
 test1  | test_table_1 | table | cross
 test2  | test_table_2 | table | cross
(3 rows)

Expected Output:

   List of relations
 Schema | Name | Type  | Owner
+--+---+---
 test1  | test_table   | table | cross
 test1  | test_table_1 | table | cross
 test2  | test_table   | table | cross
 test2  | test_table_2 | table | cross
(4 rows)

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: test1; Type: SCHEMA; Schema: -; Owner: cross
--

CREATE SCHEMA test1;


ALTER SCHEMA test1 OWNER TO "cross";

--
-- Name: test2; Type: SCHEMA; Schema: -; Owner: cross
--

CREATE SCHEMA test2;


ALTER SCHEMA test2 OWNER TO "cross";

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = test1, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test_table; Type: TABLE; Schema: test1; Owner: cross; Tablespace: 
--

CREATE TABLE test_table (
id integer,
test2_schema boolean DEFAULT false,
nonce integer
);


ALTER TABLE test1.test_table OWNER TO "cross";

--
-- Name: test_table_1; Type: TABLE; Schema: test1; Owner: cross; Tablespace: 
--

CREATE TABLE test_table_1 (
id integer,
test1_schema boolean DEFAULT true
);


ALTER TABLE test1.test_table_1 OWNER TO "cross";

SET search_path = test2, pg_catalog;

--
-- Name: test_table; Type: TABLE; Schema: test2; Owner: cross; Tablespace: 
--

CREATE TABLE test_table (
id integer,
test2_schema boolean DEFAULT true,
extra_field integer
);


ALTER TABLE test2.test_table OWNER TO "cross";

--
-- Name: test_table_2; Type: TABLE; Schema: test2; Owner: cross; Tablespace: 
--

CREATE TABLE test_table_2 (
id integer,
test2_schema boolean DEFAULT true
);


ALTER TABLE test2.test_table_2 OWNER TO "cross";

SET search_path = test1, pg_catalog;

--
-- Data for Name: test_table; Type: TABLE DATA; Schema: test1; Owner: cross
--

COPY test_table (id, test2_schema, nonce) FROM stdin;
\.


--
-- Data for Name: test_table_1; Type: TABLE DATA; Schema: test1; Owner: cross
--

COPY test_table_1 (id, test1_schema) FROM stdin;
\.


SET search_path = test2, pg_catalog;

--
-- Data for Name: test_table; Type: TABLE DATA; Schema: test2; Owner: cross
--

COPY test_table (id, test2_schema, extra_field) FROM stdin;
\.


--
-- Data for Name: test_table_2; Type: TABLE DATA; Schema: test2; Owner: cross
--

COPY test_table_2 (id, test2_schema) FROM stdin;
\.


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Unexpected omission of tables with duplicate names across schemas

[Chris Ross]

On 09/29/2010 02:08 PM, Chris Ross wrote:

On 09/28/2010 01:17 PM, Tom Lane wrote:

That's the intended behavior, because only the first one is actually
accessible without schema-qualifying its name. You can use a pattern
of "*.*" if you want to see objects that are hidden according to the
search path. The default behavior is equivalent to a pattern of "*",
which only shows objects reachable with unqualified names.


Is there a way to ask the database "What are all of the tables/views/etc
in my current search path?" without having it infer "that I can reach
without schema-qualifing them" ?

That's what I've always used \d for, and while it's certainly a habit
rather than anything documented explicitly to do what I think it should
do, there needs to be *a* way to do this I think...


  After thinking about this a little more, I think the problem here is 
more subtle/complex.  The problem, in some ways, is that I am (in some 
ways) misusing search_path to hide parts of the database from my view.


  We have a database with dozens of schemas, some of which have many 
dozens of tables et al.  So, I typically use search_path to modify which 
section of the database I'm looking at, and I typically schema-qualify 
everything when I code (and often when I'm just typing in psql).  So 
it's not really that I want to know what I can reach without schema 
qualifications, it's that I want a list of all things that exist, but 
only within the schemas that are in my search_path.


  Of course, that's not what search_path is *for*, so it's a confusing 
issue.  I'm using it for something slightly along-side what it is really 
designed for.  But, except for this minor issue, it works well for that.


  Does this help make more clear what problem I'm trying to solve, and 
the problem as I see it?


  Thanks again.

   - Chris

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Unexpected omission of tables with duplicate names across schemas

[Chris Ross]

On 09/28/2010 01:17 PM, Tom Lane wrote:

Chris Ross  writes:

When there is a table (or view, or sequence) of the same name in one
schema as another, and both of these schemas are in the set search_path,
only the first schema in the search path will show that name in the
output of \d[S+].  (Also \dt, \dv, etc)


That's the intended behavior, because only the first one is actually
accessible without schema-qualifying its name.  You can use a pattern
of "*.*" if you want to see objects that are hidden according to the
search path.  The default behavior is equivalent to a pattern of "*",
which only shows objects reachable with unqualified names.


  Okay.  However, that doesn't quite do what I want.  In the case of 
\d, it takes a name, not a pattern, and if a name/pattern is specified 
as * or *.*, it shows detail about the item, not just a list.
  For \dt, \dv, etc, I can supply a pattern, but *.* does not give me 
what I want either.  It gives me *all* schemas, not limited to the 
schemas that are in my search path.


  Is there a way to ask the database "What are all of the 
tables/views/etc in my current search path?" without having it infer 
"that I can reach without schema-qualifing them" ?


  That's what I've always used \d for, and while it's certainly a habit 
rather than anything documented explicitly to do what I think it should 
do, there needs to be *a* way to do this I think...


  - Chris

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs