Thread: BUG #2429: Explain does not report object's schema

The following bug has been logged online:

Bug reference:      2429
Logged by:          Cristiano da Cunha Duarte
Email address:      cunha17@gmail.com
PostgreSQL version: 8.1
Operating system:   Debian GNU Linux
Description:        Explain does not report object's schema
Details:

1) PROBLEM:

Explain command does not report the schema of objects, so when using objects
having the same name but in different schemas, they will apear as being the
same object.

2) HOW TO REPRODUCE:

EXPLAIN SELECT * FROM schema1.mytable, schema2.mytable WHERE 1=0

3) WHAT IS THE CURRENT BEHAVIOR:

                              QUERY PLAN
-----------------------------------------------------------------------
 Nested Loop  (cost=10.66..500630.90 rows=24422640 width=1498)
   ->  Seq Scan on mytable  (cost=0.00..12167.44 rows=407044 width=264)
   ->  Materialize  (cost=10.66..11.26 rows=60 width=1234)
         ->  Seq Scan on mytable  (cost=0.00..10.60 rows=60 width=1234)
(4 records)

3) WHAT SHOULD BE EXPECTED:

                              QUERY PLAN
-----------------------------------------------------------------------
 Nested Loop  (cost=10.66..500630.90 rows=24422640 width=1498)
   ->  Seq Scan on schema2.mytable  (cost=0.00..12167.44 rows=407044
width=264)
   ->  Materialize  (cost=10.66..11.26 rows=60 width=1234)
         ->  Seq Scan on schema1.mytable  (cost=0.00..10.60 rows=60
width=1234)
(4 records)

4) ADDITIONAL COMMENTS:
I am developing a snapshot project(Pg::snapshots
http://cunha17.theicy.net/personal/postgresql/snapshots.en_us.php) for
postgresql. It currently has refresh (complete, force, fast), snapshot logs,
dblinks, etc.

It's 99% complete, everything works fine, except the refresh fast, since I
need to discover which objects were involved in a SELECT statement. And
that's how I got into this bug.

With the current EXPLAIN implementation, I can't tell the difference between
the two and thus I can't get the list of involved objects correctly, so I
can't get the snapshot log list, and so on.

IMHO, the schema name will add correctness to the EXPLAIN command output.

"Cristiano da Cunha Duarte" <cunha17@gmail.com> writes:
> Explain command does not report the schema of objects,

This is intentional.  Most error messages don't mention objects' schemas
either, as it would usually just be clutter.

            regards, tom lane

Hi Tom,

Tom Lane wrote:
>> Explain command does not report the schema of objects,
>
> This is intentional.  Most error messages don't mention objects' schemas
> either, as it would usually just be clutter.
Oracle's EXPLAIN PLAN generate lots of information including the operation,
search columns, schema(owner) and object name.

In PostgreSQL, the error message when you issue a select statement from an
unexistent table, reports the schema too:

SELECT * FROM public.unexistent;
ERROR:  relation "public.unexistent" does not exist

In this case the schema name is clutter, since we are dealing with only one
table, but when you have (or may have) many tables with the same exact
name, you must have a way to distinguish one to another.

This problem is much more significant with the EXPLAIN command since we are
reporting the execution plan of postgresql. It may be difficult with the
current output to distinguish between tables with the same name in order to
optimize the query.

I just think that there should be a way to uniquely identify the target
table on the EXPLAIN output, that's why I don't think that a way to fix an
ambiguous output is clutter.

Regards,

Cristiano Duarte

* cunha17@gmail.com ("Cristiano da Cunha Duarte") wrote:
|
| 1) PROBLEM:
|
| Explain command does not report the schema of objects, so when using objects
| having the same name but in different schemas, they will apear as being the
| same object.
|
| 2) HOW TO REPRODUCE:

Don't know whether this would help in your situation, but you can make the
output of EXPLAIN disambiguous by using table aliases:

EXPLAIN
 SELECT *
   FROM schema1.mytable AS mt1, schema2.mytable AS mt2
  WHERE 1=0

(The AS keyword is optional.)

The aliases will be included in the query plan output.

--
Lars Haugseth

"If anyone disagrees with anything I say, I am quite prepared not only to
 retract it, but also to deny under oath that I ever said it." -Tom Lehrer

Hi Lars,

Lars Haugseth wrote:
> * cunha17@gmail.com ("Cristiano da Cunha Duarte") wrote:
> |
> | 1) PROBLEM:
> |
> | Explain command does not report the schema of objects, so when using
> | objects having the same name but in different schemas, they will apear
> | as being the same object.
> |
> | 2) HOW TO REPRODUCE:
>
> Don't know whether this would help in your situation, but you can make the
> output of EXPLAIN disambiguous by using table aliases:
>
> EXPLAIN
>  SELECT *
>    FROM schema1.mytable AS mt1, schema2.mytable AS mt2
>   WHERE 1=0
>
> (The AS keyword is optional.)
>
> The aliases will be included in the query plan output.
>
Thanks for the notice, but to do this, I would have to raise errors on
queries having tables with the same name, which is not what I meant.

But, if there is no other way, I'll do it.