> 
> >> Subselects are a BIG item for 6.3, and this is a serious feature that we
> >> should be telling people about.  In the past, I am sure certain people
> >> did not consider using PostgreSQL because of this missing feature.
> >> 
> 
> Yes, they are a big reason I want to use PostgreSQL, but as far as
> I can tell, they do not work.  Is there a patch I am missing?
> I have 6.3.1 on RedHat Linux 5.0.

Nope, this is the first problem I have heard about with subselects.

> 
> Here is what I tried:
> ======================================================================
> bbrmdc=> select runnum from mdc1_simu where version = '4.3.7g';
> runnum
> ------
> 048930
> 048931
> 048932
> 048933
> 048934
> (5 rows)
>  
> bbrmdc=> select distinct runtype from mdc1_runs where runnum in
> bbrmdc-> ('048930','048931','048932','048933','048934');
> runtype             
> --------------------
> tau+ -> X, tau- -> X
> (1 row)
>  
> bbrmdc=> select distinct runtype from mdc1_runs where runnum in
> bbrmdc-> (select runnum from mdc1_simu where version = '4.3.7g');
> FATAL:  unrecognized data from the backend.  It probably dumped core.
> FATAL:  unrecognized data from the backend.  It probably dumped core.
> bbrmdc=> \q
> 
> ======================================================================
> 
> Each of the single selects took < 1 sec. The fatals are that after 15 
> minutes, I killed the postgres process on my server.  BTW, is there 
> clean way to kill a query from the psql side? Doing a Ctrl-C just 
> kills the psql process and leaves the postgres process eating up my 
> CPU on the server. 

No way to cancel them, but it is on the TODO list.

I am CC'ing Vadim on this.  Looks strange.  Any way we can reproduce
this?  Does the removal of the DISTINCT help?  Are there a lot of values
without the DISTINCT?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Sat, 28 Mar 1998 16:30:20 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: Let's talk up 6.3
>> I am CC'ing Vadim on this.  Looks strange.  Any way we can reproduce
>> this?  Does the removal of the DISTINCT help?

No, removing DISTINCT did not help.

I currently have the data in Oracle and am using Perl and DBI to 
transfer data between the two.  I did the following additional tests. 
I dropped both tables, did a vacuum, and recreated the tables. Run the 
subselect with them empty returned no rows as expected. I transfered 
over about 20 rows into each table.  The subselect ran fine (and fast) 
returning the expected result.  

I did another drop, vacuum, create and then transfered over the entire 
~5500 rows for each table. The subselect now hangs as before.  Maybe 
it is working if the time is an expotential function of the number of 
rows.  I killed it after 15 minutes. I fail to see why it should be 
much longer than doing the subselect by hand as in my previous email.  
Oracle takes a couple of seconds to do the same subselect command.  

After killing the postgres process, I reconnected to the database
and tried a vacuum.  This also appeared to hang. I killed it after
one minute (it normal took about 5 seconds).  I killed the postmaster, then
restarted, reconnected and a vacuum worked fine.

>> Are there a lot of values
>> without the DISTINCT?

There are just as many values as there are values returned by the
subselect.  For my example it was just five, but it can certainly
be a lot more for other choices and the DISTINCT is important.

Here are the tables:

bbrmdc=> \d mdc1_runs
 
Table    = mdc1_runs
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| runnum                           | char() not null                  |     6 |
| runtype                          | text                             |   var |
| nevents                          | int4                             |     4 |
| who                              | text                             |   var |
| note                             | text                             |   var |
+----------------------------------+----------------------------------+-------+
bbrmdc=> \d mdc1_simu
 
Table    = mdc1_simu
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| runnum                           | char() not null                  |     6 |
| version                          | varchar() not null               |    10 |
| jobgrp                           | varchar() not null               |     8 |
| bldrnum                          | int4 not null                    |     4 |
| status                           | text                             |   var |
| cpusecs                          | int4                             |     4 |
| outsize                          | int4                             |     4 |
| machine                          | text                             |   var |
| location                         | text                             |   var |
| jobdate                          | abstime                          |     4 |
| who                              | text                             |   var |
| note                             | text                             |   var |
+----------------------------------+----------------------------------+-------+

I can make the entire database available to you if that would be helpful.
It is about 5MB uncompressed.

pr

--
_________________________________________________________________________
Paul Raines               raines@slac.stanford.edu           650-926-2369
Stanford Linear Accelerator   BABAR Group                   Software Team 
http://www.slac.stanford.edu/~raines/index.html  <======== PGP public key



Sat, 28 Mar 1998 15:04:25 -0800 (PST)
Paul Raines <raines@SLAC.Stanford.EDU>
Re: Let's talk up 6.3