Received: from send105.yahoomail.com (send105.yahoomail.com [205.180.60.128])
	by hub.org (8.8.8/8.8.8) with SMTP id SAA27852
	for <pgsql-interfaces@postgresql.org>;
	Mon, 5 Oct 1998 18:50:47 -0400 (EDT)
	(envelope-from mascarim@yahoo.com)
Message-ID: <19981005225116.24442.rocketmail@send105.yahoomail.com>
Received: from [206.246.185.100] by send105.yahoomail.com;
	Mon, 05 Oct 1998 15:51:16 PDT
Date: Mon, 5 Oct 1998 15:51:16 -0700 (PDT)
From: Marcus Mascari <mascarim@yahoo.com>
Subject: occassional postmaster DELETE wait timeout on ODBC
To: pgsql-interfaces@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


I have downloaded and installed the snapshot of
6.4 beta (Sep. 22, 1998) and am experiencing some
difficulties with several items:

1. Occassionally, using Access 97 and the new 6.4
   compatible ODBC driver, 6.30.0250, I will attempt
   to update or delete a row in a datasheet view and
   it appears that there is a deadlock between the 
   postmaster which 'ps' displays as:

   ...postmaster DELETE wait myuser

   and Access 97 which appears to be waiting for a
   response from the database.  I tried logging the 
   error by tracing the ODBC calls, but since it is 
   intermittent, I was unable to track down the
   problem.
   
   I have enabled the Use Declare/Fetch setting (I
   believe this is the new default), and enabled
   both the OID options - Show OID, Fake Indexes.

2. We have a medium sized table called "sales" with 
   137,000 rows in it.  The following query will 
   consume all RAM on the machine until swap space is
   exhausted:

   SELECT DISTINCT target, costcntr FROM sales ORDER
   BY saledate

   The table looks like this:
   
supplysource varchar() not null 16
supply       varchar() not null 16
supplyunit   varchar() not null 2
quantity     float8    not null 8
target       varchar() not null 16
costcntr     varchar() not null 8
saletype     varchar() not null 16
saledate     datetime  not null 8

Indices:  k_sales
          k_sales_saledate
          k_sales_supply
          k_sales_target  

My configuration is as follows:

Linux i686 2.0.35
PostgreSQL 6.4 beta
32M RAM
2 Gig IDE Hard Drive
96M Swap

As the query runs, the postmaster process simply
consumes up a RAM, and then consumed 70M of swap
space before I killed it to prevent it from 
taking down the machine.

3.  Also why does the backend correctly use the index
    on the sales table in the following example:

    explain select * from sales where target IN 
    ('4 EAST','2 PACU');

Index Scan using k_sales_target on sales
(cost=4.10 size=1 width=88) 

    And not use the index at all on the sales table
    in the following query:

    explain select * from sales where target IN 
    (select location from locations);

Seq Scan on sales  (cost=5738.60 size=116806 width=88)
  SubPlan
    ->  Seq Scan on locations  (cost=7.49 size=136
                                width=12)

    But, if this is rewritten as a join, it works:

    explain select sales.* from sales, locations where
    sales.target = locations.target;

Nested Loop  (cost=286.29 size=116807 width=100)
  ->  Seq Scan on locations  (cost=7.49 size=136 
      width=12)
  ->  Index Scan using k_sales_target on sales        (cost=2.05
size=116806 width=88)


4.  Finally, a SQL question - NULL values for datetime
    fields appear first when sorting in an ASCending
    order in an ORDER BY clause.  Is there a way to
    have NULL values be the last records without
    resorting to a union of two selects?

Thanks for any help on an otherwise great product, 

Marcus Mascari
(mascarim@yahoo.com)

_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com