Postgres allows:
UPDATE dummy
SET customer=subquery.customer,
address=subquery.address,
partn=subquery.partn
FROM (SELECT address_id, customer, address, partn
FROM /* big hairy SQL */ ...) AS subquery
WHERE dummy.address_id=subquery.address_id;
This syntax is not standard SQL
SELECT * FROM pg_stat_activity WHERE state = 'active';
So you can identify the PID of the hanging query you want to terminate, run this:
SELECT pg_cancel_backend(PID);
Partial Indexes
A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries for only those table rows that satisfy the predicate.
CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');
This article is part of a series. You do not have to read them in order but I will be referring to topics and explanations in previous articles: Implementing Your Own Transactions with MVCCSQL Transaction Isolation Levels ExplainedImplementing Repeatable ...
P. Eisentraut. Mitp-Verlag, Bonn, (2003)das Insider-Wissen der PostgreSQL-Entwickler, mit ausführlicher SQL-Beschreibung, den PostgreSQL-Server sicher beherrschen; übers. von Peter Eisentraut.