pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with FOR UPDATE
3+ messages / 3 participants
[nested] [flat]
* Problem with FOR UPDATE
@ 2006-09-07 09:00 Kaloyan Iliev <news1@faith.digsys.bg>
0 siblings, 2 replies; 3+ messages in thread
From: Kaloyan Iliev @ 2006-09-07 09:00 UTC (permalink / raw)
To: pgsql-sql
Hi All,
I have a query in which I want to SELECT FOR UPDATE same rows but only
from one table.
Firs I try just with SELECT FOR UPDATE but I receive an error
because of the LEFT JOIN - "ERROR: SELECT FOR UPDATE/SHARE cannot be
applied to the nullable side of an outer join".
So I decide to use SELECT FOR UPDATE OF <table name> but I then receive
the error you can see.
Can anyone help me with this query?
Thanks in advance.
Regards,
Kaloyan Iliev
rsr=# SELECT
rsr-# DD.*
rsr-# ( SELECT sum(-amount *
saldo_sign(credit))
rsr(# FROM acc_debts ACD1
rsr(# WHERE
ACD1.debtid = DD.debtid ) AS saldo,
rsr-# C.custid,
rsr-# S.descr_bg
rsr-# FROM debts_desc DD LEFT JOIN config
C ON (DD.conf_id = C.id),
rsr-# acc_debts AD,
rsr-# acc_clients AC,
rsr-# services S
rsr-# WHERE DD.debtid = AD.debtid
rsr-# AND DD.closed AND NOT
DD.paid AND DD.has_invoice AND DD.active AND DD.has_proform AND NOT
DD.storned
rsr-# AND AD.transact_no =
AC.transact_no
rsr-#
AND AC.ino = 45
rsr-# FOR UPDATE OF
debts_desc;
ERROR: relation "debts_desc" in FOR UPDATE/SHARE clause not found in
FROM clause
rsr=# select version();
version
------------------------------------------------------------------------------------------------
PostgreSQL 8.1.4 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC)
3.4.4 [FreeBSD] 20050518
(1 row)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with FOR UPDATE
@ 2006-09-07 14:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kaloyan Iliev <news1@faith.digsys.bg>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-09-07 14:15 UTC (permalink / raw)
To: Kaloyan Iliev <news1@faith.digsys.bg>; +Cc: pgsql-sql
Kaloyan Iliev <news1@faith.digsys.bg> writes:
> rsr=# SELECT
> ...
> rsr-# FROM debts_desc DD LEFT JOIN config
> C ON (DD.conf_id = C.id),
> ...
> rsr-# FOR UPDATE OF
> debts_desc;
> ERROR: relation "debts_desc" in FOR UPDATE/SHARE clause not found in
> FROM clause
Use the alias, ie, DD. Remember that an alias hides the real name of
that table for all purposes in the current query.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with FOR UPDATE
@ 2006-09-07 14:20 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Kaloyan Iliev <news1@faith.digsys.bg>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2006-09-07 14:20 UTC (permalink / raw)
To: Kaloyan Iliev <news1@faith.digsys.bg>; +Cc: pgsql-sql
On Thu, 7 Sep 2006, Kaloyan Iliev wrote:
> Hi All,
> I have a query in which I want to SELECT FOR UPDATE same rows but only
> from one table.
> Firs I try just with SELECT FOR UPDATE but I receive an error
> because of the LEFT JOIN - "ERROR: SELECT FOR UPDATE/SHARE cannot be
> applied to the nullable side of an outer join".
> So I decide to use SELECT FOR UPDATE OF <table name> but I then receive
> the error you can see.
I think you'd want to use DD not debts_desc as you've renamed the from
list entry.
> Can anyone help me with this query?
>
> Thanks in advance.
>
> Regards,
> Kaloyan Iliev
>
> rsr=# SELECT
> rsr-# DD.*
> rsr-# ( SELECT sum(-amount *
> saldo_sign(credit))
> rsr(# FROM acc_debts ACD1
> rsr(# WHERE
> ACD1.debtid = DD.debtid ) AS saldo,
> rsr-# C.custid,
> rsr-# S.descr_bg
> rsr-# FROM debts_desc DD LEFT JOIN config
> C ON (DD.conf_id = C.id),
> rsr-# acc_debts AD,
> rsr-# acc_clients AC,
> rsr-# services S
> rsr-# WHERE DD.debtid = AD.debtid
> rsr-# AND DD.closed AND NOT
> DD.paid AND DD.has_invoice AND DD.active AND DD.has_proform AND NOT
> DD.storned
> rsr-# AND AD.transact_no =
> AC.transact_no
> rsr-#
> AND AC.ino = 45
> rsr-# FOR UPDATE OF
> debts_desc;
> ERROR: relation "debts_desc" in FOR UPDATE/SHARE clause not found in
> FROM clause
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-09-07 14:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-07 09:00 Problem with FOR UPDATE Kaloyan Iliev <news1@faith.digsys.bg>
2006-09-07 14:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-07 14:20 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox