Обсуждение: \d is not showing global(normal) table info if we create temporarytable with same name as global table
Hi hackers,
While reading code and doing some testing, I found that if we create a temporary table with same name as we created a normal(global) table, then \d is showing only temporary table info. I think, ideally we should display info of both the tables. Below is the example:
postgres=# create table t (a int);
CREATE TABLE
postgres=# create temporary table t (a int);
CREATE TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
-----------+------+-------+----------
pg_temp_2 | t | table | mahendra
(1 row)
Expected behavior:
postgres=# \d
List of relations
Schema | Name | Type | Owner
-----------+------+-------+----------
pg_temp_2 | t | table | mahendra
public | t | table | mahendra
(2 rows)
For me, It looks like a bug.
I debugged and found that due to below code, we are showing only temp table information.
/*
* If it is in the path, it might still not be visible; it could be
* hidden by another relation of the same name earlier in the path. So
* we must do a slow check for conflicting relations.
*/
char *relname = NameStr(relform->relname);
ListCell *l;
visible = false;
foreach(l, activeSearchPath)
{
Oid namespaceId = lfirst_oid(l);
if (namespaceId == relnamespace)
{
/* Found it first in path */
visible = true;
break;
}
if (OidIsValid(get_relname_relid(relname, namespaceId)))
{
/* Found something else first in path */
break;
}
postgres=# select oid, relname , relnamespace , reltype from pg_class where relname = 't';
oid | relname | relnamespace | reltype
-------+---------+--------------+---------
16384 | t | 2200 | 16386
16389 | t | 16387 | 16391
While reading code and doing some testing, I found that if we create a temporary table with same name as we created a normal(global) table, then \d is showing only temporary table info. I think, ideally we should display info of both the tables. Below is the example:
postgres=# create table t (a int);
CREATE TABLE
postgres=# create temporary table t (a int);
CREATE TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
-----------+------+-------+----------
pg_temp_2 | t | table | mahendra
(1 row)
Expected behavior:
postgres=# \d
List of relations
Schema | Name | Type | Owner
-----------+------+-------+----------
pg_temp_2 | t | table | mahendra
public | t | table | mahendra
(2 rows)
For me, It looks like a bug.
I debugged and found that due to below code, we are showing only temp table information.
/*
* If it is in the path, it might still not be visible; it could be
* hidden by another relation of the same name earlier in the path. So
* we must do a slow check for conflicting relations.
*/
char *relname = NameStr(relform->relname);
ListCell *l;
visible = false;
foreach(l, activeSearchPath)
{
Oid namespaceId = lfirst_oid(l);
if (namespaceId == relnamespace)
{
/* Found it first in path */
visible = true;
break;
}
if (OidIsValid(get_relname_relid(relname, namespaceId)))
{
/* Found something else first in path */
break;
}
}
oid | relname | relnamespace | reltype
-------+---------+--------------+---------
16384 | t | 2200 | 16386
16389 | t | 16387 | 16391
(2 rows)
For able example, we have 3 namespaceId in the activeSearchPath list (16387->temporary_table, 11, 2200->noraml-table). As I can see that 16387 is the 1st oid in list, that is corresponds to temp table, we are displaying info of temp table but when we are checking visibility of normal table, then we exiting from list after comparing with 1st oid because 16387 is the 1st in list and that oid is valid.
If this is a bug, then please let me know. I will be happy to fix this.
On Thu, Jan 2, 2020 at 12:59 PM Mahendra Singh <mahi6run@gmail.com> wrote: > While reading code and doing some testing, I found that if we create a temporary table with same name as we created a normal(global)table, then \d is showing only temporary table info. That's because the query that \d issues to the backend includes: AND pg_catalog.pg_table_is_visible(c.oid) So I'd say it's not a bug, because that bit of SQL didn't get included in the query by accident. Whether it is the behavior that everybody wants is debatable, but I think it's been this way since 2002. See commit 039cb479884abc28ee494f6cf6c5e7ec26b88fc8. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Jan 2, 2020 at 12:59 PM Mahendra Singh <mahi6run@gmail.com> wrote:
>> While reading code and doing some testing, I found that if we create a temporary table with same name as we created
anormal(global) table, then \d is showing only temporary table info.
> That's because the query that \d issues to the backend includes:
> AND pg_catalog.pg_table_is_visible(c.oid)
> So I'd say it's not a bug, because that bit of SQL didn't get included
> in the query by accident.
It's also documented:
Whenever the pattern parameter is omitted completely, the \d commands
display all objects that are visible in the current schema search path
— this is equivalent to using * as the pattern. (An object is said to
be visible if its containing schema is in the search path and no
object of the same kind and name appears earlier in the search
path. This is equivalent to the statement that the object can be
referenced by name without explicit schema qualification.) To see all
objects in the database regardless of visibility, use *.* as the
pattern.
Perhaps that's not clear enough, but the behavior is certainly as-intended.
regards, tom lane
Re: \d is not showing global(normal) table info if we createtemporary table with same name as global table
От
Mahendra Singh Thalor
Дата:
On Fri, 3 Jan 2020 at 00:40, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Robert Haas <robertmhaas@gmail.com> writes: > > On Thu, Jan 2, 2020 at 12:59 PM Mahendra Singh <mahi6run@gmail.com> wrote: > >> While reading code and doing some testing, I found that if we create a temporary table with same name as we createda normal(global) table, then \d is showing only temporary table info. > > > That's because the query that \d issues to the backend includes: > > AND pg_catalog.pg_table_is_visible(c.oid) > > So I'd say it's not a bug, because that bit of SQL didn't get included > > in the query by accident. > > It's also documented: > > Whenever the pattern parameter is omitted completely, the \d commands > display all objects that are visible in the current schema search path > — this is equivalent to using * as the pattern. (An object is said to > be visible if its containing schema is in the search path and no > object of the same kind and name appears earlier in the search > path. This is equivalent to the statement that the object can be > referenced by name without explicit schema qualification.) To see all > objects in the database regardless of visibility, use *.* as the > pattern. > > Perhaps that's not clear enough, but the behavior is certainly as-intended. > > regards, tom lane Thanks Robert and Tom for quick detailed response. -- Thanks and Regards Mahendra Singh Thalor EnterpriseDB: http://www.enterprisedb.com