
OK, we are down to one open item, related to pg_dumping on 64-bit off_t.
We had discussion today on this so it should be completed shortly.

---------------------------------------------------------------------------

                               P O S T G R E S Q L

                          7 . 3  O P E N    I T E M S


Current at ftp://momjian.postgresql.org/pub/postgresql/open_items.

Required Changes
-------------------
Schema handling - ready? interfaces? client apps?
Drop column handling - ready for all clients, apps?
Fix pg_dump to handle 64-bit off_t offsets for custom format (Philip)

Optional Changes
----------------

Documentation Changes
---------------------


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 21 Oct 2002 19:18:23 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
One 7.3 item left
> Schema handling - ready? interfaces? client apps?

What is the state of the Perl interface?

Will it work when 7.3 is released 
Will it work, but no schema support
Will it pop up later on CPAN

-- 
Kaare Rasmussen            --Linux, spil,--        Tlf:        3816 2582
Kaki Data                tshirts, merchandize      Fax:        3816 2501
Howitzvej 75               ï¿½ben 12.00-18.00        Email: kar@kakidata.dk
2000 Frederiksberg        Lï¿½rdag 12.00-16.00       Web:      www.suse.dk

Tue, 22 Oct 2002 19:01:20 +0200
Kaare Rasmussen <kar@kakidata.dk>
Re: One 7.3 item left
Kaare Rasmussen wrote:
> > Schema handling - ready? interfaces? client apps?
> 
> What is the state of the Perl interface?
> 
> Will it work when 7.3 is released 
> Will it work, but no schema support
> Will it pop up later on CPAN

We have a separate gborg project for the old perl5 in interface and
dbd-pg.  The DBD group is making improvements right now.  Not sure how
it works with 7.3 but I am sure they will get to testing it soon.  David
Wheeler is working on it, and he is involved in 7.3.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 22 Oct 2002 13:10:21 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: One 7.3 item left
Bruce Momjian writes:

> OK, we are down to one open item, related to pg_dumping on 64-bit off_t.
> We had discussion today on this so it should be completed shortly.

I hate to spoil the fun, but we have at least the Linux + Perl
5.8.1 + _GNU_SOURCE and the AIX + Large File + Flex problems to fix.

-- 
Peter Eisentraut   peter_e@gmx.net


Tue, 22 Oct 2002 22:11:18 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: One 7.3 item left
Peter Eisentraut <peter_e@gmx.net> writes:
> Bruce Momjian writes:
>> OK, we are down to one open item, related to pg_dumping on 64-bit off_t.
>> We had discussion today on this so it should be completed shortly.

> I hate to spoil the fun, but we have at least the Linux + Perl
> 5.8.1 + _GNU_SOURCE and the AIX + Large File + Flex problems to fix.

We should not, however, wait longer before pushing out a beta3 release.
Portability problems on individual platforms may hold up RC1, but we're
overdue to put out a final beta...

			regards, tom lane

Tue, 22 Oct 2002 16:15:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: One 7.3 item left 
On Tue, 22 Oct 2002, Tom Lane wrote:

> Peter Eisentraut <peter_e@gmx.net> writes:
> > Bruce Momjian writes:
> >> OK, we are down to one open item, related to pg_dumping on 64-bit off_t.
> >> We had discussion today on this so it should be completed shortly.
>
> > I hate to spoil the fun, but we have at least the Linux + Perl
> > 5.8.1 + _GNU_SOURCE and the AIX + Large File + Flex problems to fix.
>
> We should not, however, wait longer before pushing out a beta3 release.
> Portability problems on individual platforms may hold up RC1, but we're
> overdue to put out a final beta...

Was just about to ask that ... Friday sound reasonable for beta3 then?
Bruce, can you have all your files updated by then?



Tue, 22 Oct 2002 22:14:19 -0300 (ADT)
"Marc G. Fournier" <scrappy@hub.org>
Re: One 7.3 item left 
Marc G. Fournier wrote:
> On Tue, 22 Oct 2002, Tom Lane wrote:
> 
> > Peter Eisentraut <peter_e@gmx.net> writes:
> > > Bruce Momjian writes:
> > >> OK, we are down to one open item, related to pg_dumping on 64-bit off_t.
> > >> We had discussion today on this so it should be completed shortly.
> >
> > > I hate to spoil the fun, but we have at least the Linux + Perl
> > > 5.8.1 + _GNU_SOURCE and the AIX + Large File + Flex problems to fix.
> >
> > We should not, however, wait longer before pushing out a beta3 release.
> > Portability problems on individual platforms may hold up RC1, but we're
> > overdue to put out a final beta...
> 
> Was just about to ask that ... Friday sound reasonable for beta3 then?
> Bruce, can you have all your files updated by then?

I can, sure.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 22 Oct 2002 21:16:17 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: One 7.3 item left
On Tue, Oct 22, 2002 at 19:01:20 +0200,
  Kaare Rasmussen <kar@kakidata.dk> wrote:
> > Schema handling - ready? interfaces? client apps?
> 
> What is the state of the Perl interface?
> 
> Will it work when 7.3 is released 
> Will it work, but no schema support
> Will it pop up later on CPAN

I am using Pg with 7.3b1 and it works OK for what I am doing. I am not
explicitly naming schemas when referencing objects though.

Tue, 22 Oct 2002 22:24:15 -0500
Bruno Wolff III <bruno@wolff.to>
Re: One 7.3 item left
Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > OK, we are down to one open item, related to pg_dumping on 64-bit off_t.
> > We had discussion today on this so it should be completed shortly.
> 
> I hate to spoil the fun, but we have at least the Linux + Perl
> 5.8.1 + _GNU_SOURCE and the AIX + Large File + Flex problems to fix.

I was hoping those had gone away.  :-(

Open items updated.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

                               P O S T G R E S Q L

                          7 . 3  O P E N    I T E M S


Current at ftp://momjian.postgresql.org/pub/postgresql/open_items.

Required Changes
-------------------
Schema handling - ready? interfaces? client apps?
Drop column handling - ready for all clients, apps?
Add configure check for sizeof(off_t) > sizeof(long) and no fseek()
Fix Linux + Perl 5.8.1 + _GNU_SOURCE problem
Fix AIX + Large File + Flex problem

Optional Changes
----------------

Documentation Changes
---------------------
Wed, 23 Oct 2002 21:02:46 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: One 7.3 item left
Marc G. Fournier wrote:
> On Tue, 22 Oct 2002, Tom Lane wrote:
> 
> > Peter Eisentraut <peter_e@gmx.net> writes:
> > > Bruce Momjian writes:
> > >> OK, we are down to one open item, related to pg_dumping on 64-bit off_t.
> > >> We had discussion today on this so it should be completed shortly.
> >
> > > I hate to spoil the fun, but we have at least the Linux + Perl
> > > 5.8.1 + _GNU_SOURCE and the AIX + Large File + Flex problems to fix.
> >
> > We should not, however, wait longer before pushing out a beta3 release.
> > Portability problems on individual platforms may hold up RC1, but we're
> > overdue to put out a final beta...
> 
> Was just about to ask that ... Friday sound reasonable for beta3 then?
> Bruce, can you have all your files updated by then?

Done.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 23 Oct 2002 23:02:45 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: One 7.3 item left
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Marc G. Fournier wrote:
>> Was just about to ask that ... Friday sound reasonable for beta3 then?
>> Bruce, can you have all your files updated by then?

> Done.

Are we going to back-merge CVS tip into the REL7_3_STABLE branch now?
What about opening CVS tip for 7.4 development?

			regards, tom lane

Wed, 23 Oct 2002 23:17:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: One 7.3 item left 
On Wed, 23 Oct 2002, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Marc G. Fournier wrote:
> >> Was just about to ask that ... Friday sound reasonable for beta3 then?
> >> Bruce, can you have all your files updated by then?
>
> > Done.
>
> Are we going to back-merge CVS tip into the REL7_3_STABLE branch now?
> What about opening CVS tip for 7.4 development?

considering the number of changes that have lead up to beta3, I'd saw wait
... I'm almost thinking that rc1 makes more sense to do it, since once rc1
goes out, then we're at the point of "only critical changes", which means
alot less commits then what we see through beta ...



Thu, 24 Oct 2002 01:18:04 -0300 (ADT)
"Marc G. Fournier" <scrappy@hub.org>
Re: One 7.3 item left 
I came across a quite interesting issue I don't really understand but 
maybe Tom will know.
This happened rather accidentally.

I have a rather complex query which executes efficiently.

There is one interesting thing - let's have a look at the query:


SELECT t_struktur.id, t_text.code, COUNT(t_wert.wert) AS x
        FROM    t_struktur JOIN t_sportstruktur
                        ON (t_struktur.id = t_sportstruktur.strukturid),
                t_text, t_master, t_strukturtyp,
                t_master AS a JOIN t_struktur AS b
                        ON (a.slave_id = b.id) JOIN t_strukturtyp AS c
                        ON (b.typid = c.id),
                t_wert JOIN t_werttyp
                        ON (t_werttyp.id = t_wert.werttypid)
        WHERE   t_struktur.id = t_text.suchid
                AND t_text.sprache = 1
                AND t_text.texttyp IS NULL
                AND t_text.icode = 'struktur'

                AND t_master.master_id IN (11, 6, 10, 9, 5, 3, 7, 8, 13)
                AND t_master.slave_id = t_struktur.id
                AND t_struktur.typid = t_strukturtyp.id
                AND t_strukturtyp.kommentar = 'geoort'

                AND a.master_id = t_struktur.id
                AND c.sortierung = '60005'

                AND t_sportstruktur.sportid IN (1, 2, 3, 4, 5)
                AND t_struktur.id = t_wert.strukturid
                AND t_werttyp.id = t_wert.werttypid
                AND t_werttyp.anzeige IN (40550, 40555, 40525,
                        41070, 41073, 41075, 41077, 41080,
                        40745, 40750, 40775, 40735, 40780,
                        40785, 40760, 40710, 41110, 41115,
                        41090, 41120, 40265, 41085, 41030,
                        41570, 41550)
                AND (t_wert.wert > '0' OR t_wert.wert = 't')
        GROUP BY t_struktur.id, t_text.code
        ORDER BY x DESC;


On my good old P166 it takes

root@actionscouts:/tmp# time psql action < c.sql
 id  |    code    | x
-----+------------+----
 301 | Schladming | 16
 204 | Kitzbï¿½hel  |  8
(2 rows)


real    0m1.475s
user    0m0.050s
sys     0m0.010s

It takes around 5 seconds to execute the query without explicit joins 
(brief comment to the discussion we had yesterday).

As you can see the query is redundant:

t_wert JOIN t_werttyp
                        ON (t_werttyp.id = t_wert.werttypid)

I also use:

            AND t_werttyp.id = t_wert.werttypid

I have done with join twice since I have forgotten to remove the line 
below when tweaking the stuff.

However, when I remove this AND the query is logically the same but ...


root@actionscouts:/tmp# time psql action < c.sql
 id  |    code    | x
-----+------------+----
 301 | Schladming | 16
 204 | Kitzbï¿½hel  |  8
(2 rows)


real    0m2.280s
user    0m0.060s
sys     0m0.010s

It is 50% slower ...
Does anybody have an idea why?

Here are the execution plans - the first one uses the redundant query; 
the second one does not use the AND in the WHERE clause.



root@actionscouts:/tmp# time psql action < c.sql
NOTICE:  QUERY PLAN:

Sort  (cost=425.34..425.34 rows=1 width=132)
  ->  Aggregate  (cost=425.32..425.33 rows=1 width=132)
        ->  Group  (cost=425.32..425.33 rows=1 width=132)
              ->  Sort  (cost=425.32..425.32 rows=1 width=132)
                    ->  Nested Loop  (cost=240.47..425.31 rows=1 width=132)
                          ->  Nested Loop  (cost=240.47..415.76 rows=1 
width=124)
                                ->  Hash Join  (cost=240.47..399.06 
rows=1 width=101)
                                      ->  Nested Loop  
(cost=0.00..154.76 rows=765 width=29)
                                            ->  Seq Scan on t_werttyp  
(cost=0.00..14.69 rows=23 width=8)
                                            ->  Index Scan using 
idx_wert_werttypid on t_wert  (cost=0.00..5.98 rows=1 width=21)
                                      ->  Hash  (cost=240.47..240.47 
rows=1 width=72)
                                            ->  Hash Join  
(cost=114.57..240.47 rows=1 width=72)
                                                  ->  Hash Join  
(cost=22.45..148.23 rows=24 width=40)
                                                        ->  Hash Join  
(cost=18.82..128.85 rows=3091 width=32)
                                                              ->  Seq 
Scan on t_master a  (cost=0.00..55.59 rows=3159 width=16)
                                                              ->  Hash  
(cost=16.66..16.66 rows=866 width=16)
                                                                    ->  
Seq Scan on t_struktur b  (cost=0.00..16.66 rows=866 width=16)
                                                        ->  Hash  
(cost=3.62..3.62 rows=1 width=8)
                                                              ->  Seq 
Scan on t_strukturtyp c  (cost=0.00..3.62 rows=1 width=8)
                                                  ->  Hash  
(cost=92.11..92.11 rows=3 width=32)
                                                        ->  Hash Join  
(cost=41.12..92.11 rows=3 width=32)
                                                              ->  Hash 
Join  (cost=37.49..86.40 rows=273 width=24)
                                                                    ->  
Seq Scan on t_sportstruktur  (cost=0.00..44.13 rows=273 width=8)
                                                                    ->  
Hash  (cost=16.66..16.66 rows=866 width=16)
                                                                          
->  Seq Scan on t_struktur  (cost=0.00..16.66 rows=866 width=16)
                                                              ->  Hash  
(cost=3.62..3.62 rows=1 width=8)
                                                                    ->  
Seq Scan on t_strukturtyp  (cost=0.00..3.62 rows=1 width=8)
                                ->  Index Scan using idx_text_suchid on 
t_text  (cost=0.00..16.68 rows=1 width=23)
                          ->  Index Scan using idx_master_slaveid on 
t_master  (cost=0.00..9.54 rows=1 width=8)

EXPLAIN

real    0m0.616s
user    0m0.050s
sys     0m0.010s





oot@actionscouts:/tmp# time psql action < c.sql
NOTICE:  QUERY PLAN:

Sort  (cost=824.56..824.56 rows=1 width=132)
  ->  Aggregate  (cost=824.55..824.55 rows=1 width=132)
        ->  Group  (cost=824.55..824.55 rows=1 width=132)
              ->  Sort  (cost=824.55..824.55 rows=1 width=132)
                    ->  Nested Loop  (cost=255.22..824.54 rows=1 width=132)
                          ->  Nested Loop  (cost=255.22..814.98 rows=1 
width=124)
                                ->  Hash Join  (cost=255.22..798.28 
rows=1 width=101)
                                      ->  Hash Join  (cost=14.75..553.98 
rows=765 width=29)
                                            ->  Seq Scan on t_wert  
(cost=0.00..501.03 rows=5729 width=21)
                                            ->  Hash  (cost=14.69..14.69 
rows=23 width=8)
                                                  ->  Seq Scan on 
t_werttyp  (cost=0.00..14.69 rows=23 width=8)
                                      ->  Hash  (cost=240.47..240.47 
rows=1 width=72)
                                            ->  Hash Join  
(cost=114.57..240.47 rows=1 width=72)
                                                  ->  Hash Join  
(cost=22.45..148.23 rows=24 width=40)
                                                        ->  Hash Join  
(cost=18.82..128.85 rows=3091 width=32)
                                                              ->  Seq 
Scan on t_master a  (cost=0.00..55.59 rows=3159 width=16)
                                                              ->  Hash  
(cost=16.66..16.66 rows=866 width=16)
                                                                    ->  
Seq Scan on t_struktur b  (cost=0.00..16.66 rows=866 width=16)
                                                        ->  Hash  
(cost=3.62..3.62 rows=1 width=8)
                                                              ->  Seq 
Scan on t_strukturtyp c  (cost=0.00..3.62 rows=1 width=8)
                                                  ->  Hash  
(cost=92.11..92.11 rows=3 width=32)
                                                        ->  Hash Join  
(cost=41.12..92.11 rows=3 width=32)
                                                              ->  Hash 
Join  (cost=37.49..86.40 rows=273 width=24)
                                                                    ->  
Seq Scan on t_sportstruktur  (cost=0.00..44.13 rows=273 width=8)
                                                                    ->  
Hash  (cost=16.66..16.66 rows=866 width=16)
                                                                          
->  Seq Scan on t_struktur  (cost=0.00..16.66 rows=866 width=16)
                                                              ->  Hash  
(cost=3.62..3.62 rows=1 width=8)
                                                                    ->  
Seq Scan on t_strukturtyp  (cost=0.00..3.62 rows=1 width=8)
                                ->  Index Scan using idx_text_suchid on 
t_text  (cost=0.00..16.68 rows=1 width=23)
                          ->  Index Scan using idx_master_slaveid on 
t_master  (cost=0.00..9.54 rows=1 width=8)

EXPLAIN

real    0m0.659s
user    0m0.040s
sys     0m0.030s


The execution plans are slightly different.
Is it "normal"?

Also: My third PostgreSQL book is ready. It is in German - does anybody 
of those PostgreSQL hackers out there want a free issue?

    Hans

-- 
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at 
<http://cluster.postgresql.at>, www.cybertec.at 
<http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>



Thu, 24 Oct 2002 10:18:57 +0200
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at>
Using the same condition twice
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:
> I came across a quite interesting issue I don't really understand but 
> maybe Tom will know.

Interesting.  We seem to recognize the fact that the extra clause is
redundant in nearly all places ... but not in indexscan plan generation.

I tried this simplified test case:

create table t_wert(werttypid int);
create table t_werttyp(id int);
create index idx_wert_werttypid on t_wert(werttypid);

explain select * from
t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid)
where t_werttyp.id = t_wert.werttypid;

explain select * from
t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid);

I got identical merge-join plans and row count estimates both ways.
I then turned off enable_mergejoin, and got identical hash-join plans
and row counts.  But with enable_hashjoin also off:

regression=# explain select * from
regression-# t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid)
regression-# where t_werttyp.id = t_wert.werttypid;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..4858.02 rows=5000 width=8)
   ->  Seq Scan on t_werttyp  (cost=0.00..20.00 rows=1000 width=4)
   ->  Index Scan using idx_wert_werttypid on t_wert  (cost=0.00..4.83 rows=1 width=4)
         Index Cond: (("outer".id = t_wert.werttypid) AND ("outer".id = t_wert.werttypid))
(4 rows)

regression=# explain select * from
regression-# t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid);
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..17150.00 rows=5000 width=8)
   ->  Seq Scan on t_werttyp  (cost=0.00..20.00 rows=1000 width=4)
   ->  Index Scan using idx_wert_werttypid on t_wert  (cost=0.00..17.07 rows=5 width=4)
         Index Cond: ("outer".id = t_wert.werttypid)
(4 rows)

Looks like a bug is lurking someplace ...

			regards, tom lane

Thu, 24 Oct 2002 10:23:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Using the same condition twice 
I wrote:
> Interesting.  We seem to recognize the fact that the extra clause is
> redundant in nearly all places ... but not in indexscan plan generation.

> I tried this simplified test case:

> create table t_wert(werttypid int);
> create table t_werttyp(id int);
> create index idx_wert_werttypid on t_wert(werttypid);

> explain select * from
> t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid)
> where t_werttyp.id = t_wert.werttypid;

> explain select * from
> t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid);


FYI, I have committed changes that seem to fix this problem in CVS tip.

regression=# set enable_mergejoin to 0;
SET
regression=# set enable_hashjoin to 0;
SET
regression=# explain select * from
regression-# t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid)
regression-# where t_werttyp.id = t_wert.werttypid;
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..17150.00 rows=5000 width=8)
   ->  Seq Scan on t_werttyp  (cost=0.00..20.00 rows=1000 width=4)
   ->  Index Scan using idx_wert_werttypid on t_wert  (cost=0.00..17.07 rows=5 width=4)
         Index Cond: ("outer".id = t_wert.werttypid)
(4 rows)

regression=# explain select * from
regression-# t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid);
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..17150.00 rows=5000 width=8)
   ->  Seq Scan on t_werttyp  (cost=0.00..20.00 rows=1000 width=4)
   ->  Index Scan using idx_wert_werttypid on t_wert  (cost=0.00..17.07 rows=5 width=4)
         Index Cond: ("outer".id = t_wert.werttypid)
(4 rows)


			regards, tom lane

Sun, 24 Nov 2002 16:56:09 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Using the same condition twice 