Skip site navigation (1) Skip section navigation (2)

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

From: "Cristiano da Cunha Duarte" <cunha17(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2429: Explain does not report object's schema
Date: 2006-05-09 20:38:03
Message-ID: 200605092038.k49Kc3J5049127@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
The following bug has been logged online:

Bug reference:      2429
Logged by:          Cristiano da Cunha Duarte
Email address:      cunha17(at)gmail(dot)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.

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-05-09 20:40:17
Subject: standard_conforming_strings vs escape_string_warning
Previous:From: Martijn van OosterhoutDate: 2006-05-09 20:37:04
Subject: [PATCH] Improve EXPLAIN ANALYZE overhead by sampling

pgsql-bugs by date

Next:From: Tom LaneDate: 2006-05-09 20:43:45
Subject: Re: BUG #2419: could not reattach to shared memory
Previous:From: Casey DuncanDate: 2006-05-09 17:13:29
Subject: BUG #2428: ERROR: out of memory, running INSERT SELECT statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group