Thread: Re: Let's talk up 6.3

Re: Let's talk up 6.3

From
Bruce Momjian
Date:
>
> >> 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)

Re: Let's talk up 6.3

From
Paul Raines
Date:
>> 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