From: | Marcus Mascari <mascarim(at)yahoo(dot)com> |
---|---|
To: | pgsql-interfaces(at)postgresql(dot)org |
Subject: | occassional postmaster DELETE wait timeout on ODBC |
Date: | 1998-10-05 22:51:16 |
Message-ID: | 19981005225116.24442.rocketmail@send105.yahoomail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)yahoo(dot)com)
_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com
From | Date | Subject | |
---|---|---|---|
Next Message | Gerald Gryschuk | 1998-10-06 08:25:09 | Re: Sorry 'bout that mild outburst, and here's another change. |
Previous Message | Gerald Gryschuk | 1998-10-05 20:39:02 | So sorry I didn't realize what I was doing. |