occassional postmaster DELETE wait timeout on ODBC - Mailing list pgsql-interfaces

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