The following bug has been logged online:

Bug reference: 2027
Logged by: Dmitri Fuerle
Email address: dmitri.fuerle (AT) gmail (DOT) com
PostgreSQL version: 8.1
Operating system: Mac OS X
Description: Select on view hangs.
Details:

A select on the following view hangs the database
and produces the lock table at the bottom of the message


CREATE OR REPLACE VIEW "public"."report_dci_efficiency" (
invoice_id,
bill_to_name,
ship_to_name,
po_num,
ordered_date,
promised_date,
ship_date,
number_days,
late_report_note,
ignore_late)
AS
SELECT i.invoice_id, billa.comp_name AS bill_to_name, shipa.comp_name AS
ship_to_name, i.po_num, i.accepted AS ordered_date, holi.due_date AS
promised_date, (i.invoiced_date)::date AS ship_date,
((i.invoiced_date)::date - holi.due_date) AS number_days,
i.late_report_note, i.ignore_late
FROM (((invoice i JOIN history_order_line_item holi ON ((i.order_id =
holi.order_id))) JOIN address billa ON ((i.bill_to_address_id =
billa.address_id))) JOIN address shipa ON ((i.ship_to_address_id =
shipa.address_id)))
WHERE ((holi.history_order_line_item_id = (
SELECT max(tmpholi.history_order_line_item_id) AS max
FROM history_order_line_item tmpholi
WHERE (tmpholi.order_id = i.order_id)
)) AND (i.bill_to_address_id <> 12373))
ORDER BY i.invoiced_date DESC;


locktype | database | relation | page | tuple | transactionid |
classid | objid | objsubid | transaction | pid | mode | granted

---------------+----------+----------+------+-------+---------------+-------
--+-------+----------+-------------+------+-----------------+---------
relation | 7140087 | 10342 | | | |

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Dmitri Fuerle wrote:

It doesn't look like it's actually waiting on a lock (unless you managed
to paste only a portion of pg_locks). Are you running on NFS or some
such?


--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Dmitri Fuerle" <dmitri.fuerle (AT) gmail (DOT) com> writes:
Are you sure it's actually hung, and not (say) computing like mad?
Perhaps the planner chose a bad plan that will take a long time.
Have you ANALYZEd all the tables underlying the view?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend