Обсуждение: 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