\d is not showing global(normal) table info if we create temporary table 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
(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.
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com
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 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.
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
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 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.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