Skip site navigation (1) Skip section navigation (2)

Re: Let's talk up 6.3

From: Paul Raines <raines(at)SLAC(dot)Stanford(dot)EDU>
To: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
Cc: hackers(at)postgreSQL(dot)org, vadim(at)sable(dot)krasnoyarsk(dot)su
Subject: Re: Let's talk up 6.3
Date: 1998-03-28 23:04:25
Message-ID: raines-980328150425.A182707@flora04 (view raw or flat)
Thread:
Lists: pgsql-hackers
>> 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(at)slac(dot)stanford(dot)edu           650-926-2369
Stanford Linear Accelerator   BABAR Group                   Software Team 
http://www.slac.stanford.edu/~raines/index.html  <======== PGP public key



In response to

pgsql-hackers by date

Next:From: Gerhard ReithoferDate: 1998-03-28 23:41:24
Subject: Re: [HACKERS] pgindent on odbc
Previous:From: Bruce MomjianDate: 1998-03-28 21:30:20
Subject: Re: Let's talk up 6.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group