Thread: BUG #2027: Select on view hangs.

The following bug has been logged online:

Bug reference:      2027
Logged by:          Dmitri Fuerle
Email address:      dmitri.fuerle@gmail.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 |      |       |               |
 |       |          |       54154 | 8978 | AccessShareLock | t
 relation      |  7140087 |  7582825 |      |       |               |
 |       |          |       54152 | 8972 | AccessShareLock | t
 transactionid |          |          |      |       |         54136 |
 |       |          |       54136 | 8967 | ExclusiveLock   | t
 transactionid |          |          |      |       |         54154 |
 |       |          |       54154 | 8978 | ExclusiveLock   | t
 relation      |  7140087 |  7140308 |      |       |               |
 |       |          |       54152 | 8972 | AccessShareLock | t
 relation      |  7140087 |  7582785 |      |       |               |
 |       |          |       54152 | 8972 | AccessShareLock | t
 relation      |  7140087 |  7140407 |      |       |               |
 |       |          |       54152 | 8972 | AccessShareLock | t
 relation      |  7140087 |  7140528 |      |       |               |
 |       |          |       54152 | 8972 | AccessShareLock | t
 relation      |  7140087 |  7140393 |      |       |               |
 |       |          |       54152 | 8972 | AccessShareLock | t
 transactionid |          |          |      |       |         54152 |
 |       |          |       54152 | 8972 | ExclusiveLock   | t
(10 rows)

Dmitri Fuerle wrote:

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

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.

"Dmitri Fuerle" <dmitri.fuerle@gmail.com> writes:
> A select on the following view hangs the database

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