Thread: Re: Let's talk up 6.3
>
> >> 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)
>> 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