SELECT for UPDATE and outer join?
Folks,
------------------------------
SQL statement<select t0.po_number, t0.po_site_id, t0.po_supp_id,
t1.pol_number,
t1.pol_po_id, t1.pol_balance, t1.pol_qty, t1.pol_p_id, t1.pol_message,
t1.pol_de
ldate from s_purchase_order t0 left outer join s_purchase_orderline
t1 on t0.
po_number = t1.pol_po_id where t0.po_number = ? for update > with
input values
:java.lang.Integer:30239.
Please examine the SQLException for more information.
NestedException: java.sql.SQLException: ERROR: SELECT FOR UPDATE cannot
be applied to the nullable side of an outer join
-----------------------------
So, my question is: why can't we apply FOR UPDATE to an outer join? Is this
defined in the SQL Spec?
--
Josh Berkus
Aglio Database Solutions
San Francisco
I find the comments in initsplan.c as following.
/*
* Presently the executor cannot support FOR UPDATE/SHARE marking of
* rels appearing on the nullable side of an outer join. (It's
* somewhat unclear what that would mean, anyway: what should we
* mark when a result row is generated from no element of the
* nullable relation?) So, complain if target rel is FOR UPDATE/SHARE.
* It's sufficient to make this check once per rel, so do it only
* if rel wasn't already known nullable.
*/
IIRC, SQL:2003 also says the nullable side is not updatable, since it is not
key-preserved. See 7.12 <query specification> of ISO/IEC 9075-2:2003.
And I also got the idea from Oracle's SQL manual.
"Josh Berkus" <josh@agliodbs.com> wrote
news:200508110843.12819.josh@agliodbs.com...
Folks,
------------------------------
SQL statement<select t0.po_number, t0.po_site_id, t0.po_supp_id,
t1.pol_number,
t1.pol_po_id, t1.pol_balance, t1.pol_qty, t1.pol_p_id, t1.pol_message,
t1.pol_de
ldate from s_purchase_order t0 left outer join s_purchase_orderline
t1 on t0.
po_number = t1.pol_po_id where t0.po_number = ? for update > with
input values
:java.lang.Integer:30239.
Please examine the SQLException for more information.
NestedException: java.sql.SQLException: ERROR: SELECT FOR UPDATE cannot
be applied to the nullable side of an outer join
-----------------------------So, my question is: why can't we apply FOR UPDATE to an outer join? Is
this
Show quoted text
defined in the SQL Spec?
--
Josh Berkus
Aglio Database Solutions
San Francisco---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly