From: Marcus Mascari <mascarim@yahoo.com>
To: pgsql-interfaces@postgresql.org
Subject: occassional postmaster DELETE wait timeout on ODBC
Date: Mon, 5 Oct 1998 15:51:16 -0700 (PDT)
Message-ID: <19981005225116.24442.rocketmail@send105.yahoomail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: mascarim@yahoo.com
Subject: Re: occassional postmaster DELETE wait timeout on ODBC
In-Reply-To: <19981005225116.24442.rocketmail@send105.yahoomail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox