Обсуждение: Unexpected omission of tables with duplicate names across schemas
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)
Вложения
Chris Ross <cross@markmonitor.com> 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. regards, tom lane
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
On 09/28/2010 01:17 PM, Tom Lane wrote: > Chris Ross<cross@markmonitor.com> 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
On Wed, Sep 29, 2010 at 2:19 PM, Chris Ross <cross@markmonitor.com> wrote: > 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... > > =A0After thinking about this a little more, I think the problem here is m= ore > subtle/complex. =A0The problem, in some ways, is that I am (in some ways) > misusing search_path to hide parts of the database from my view. > > =A0We have a database with dozens of schemas, some of which have many doz= ens > of tables et al. =A0So, I typically use search_path to modify which secti= on of > the database I'm looking at, and I typically schema-qualify everything wh= en > I code (and often when I'm just typing in psql). =A0So it's not really th= at 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. > > =A0Of course, that's not what search_path is *for*, so it's a confusing i= ssue. > =A0I'm using it for something slightly along-side what it is really desig= ned > for. =A0But, except for this minor issue, it works well for that. > > =A0Does this help make more clear what problem I'm trying to solve, and t= he > problem as I see it? I think what you're trying to do is totally reasonable, but psql doesn't really support it. You might be able to rig something up using macros (see \set). There's a pretty significant difference between the way that graphical admin tools like pgAdmin view schemas and the way they look from psql. In pgAdmin, you get a tree (which is how you seem to be thinking about it), whereas in psql it tends to feel more like a flat namespace that's constructed by smashing several namespaces together, a la UNIX $PATH. The underlying reality is that it's some of both. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company