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
