From news1@faith.digsys.bg Thu Sep 7 09:00:47 2006 X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 707BD9FA474 for ; Thu, 7 Sep 2006 06:00:47 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 91640-02-6 for ; Thu, 7 Sep 2006 09:00:40 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from danbo.digsys.bg (danbo.digsys.bg [192.92.129.13]) by postgresql.org (Postfix) with ESMTP id 86FD29FA2F5 for ; Thu, 7 Sep 2006 06:00:37 -0300 (ADT) Received: from [193.68.6.22] (kaloyan@luke.digsys.bg [193.68.6.22]) by danbo.digsys.bg (8.11.6/8.10.1) with ESMTP id k8790Yp21618 for ; Thu, 7 Sep 2006 12:00:34 +0300 (EEST) Message-ID: <44FFDFA6.4080600@faith.digsys.bg> Date: Thu, 07 Sep 2006 12:00:22 +0300 From: Kaloyan Iliev User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.7.12) Gecko/20060117 X-Accept-Language: bg, en, en-us MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Problem with FOR UPDATE Content-Type: text/plain; charset=windows-1251; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200609/44 X-Sequence-Number: 26255 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 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) From tgl@sss.pgh.pa.us Thu Sep 7 14:16:05 2006 X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id C88649FA2A1 for ; Thu, 7 Sep 2006 11:16:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 61422-04 for ; Thu, 7 Sep 2006 11:15:49 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 187E99F9F0E for ; Thu, 7 Sep 2006 11:15:43 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k87EFdgk005032; Thu, 7 Sep 2006 10:15:39 -0400 (EDT) To: Kaloyan Iliev cc: pgsql-sql@postgresql.org Subject: Re: Problem with FOR UPDATE In-reply-to: <44FFDFA6.4080600@faith.digsys.bg> References: <44FFDFA6.4080600@faith.digsys.bg> Comments: In-reply-to Kaloyan Iliev message dated "Thu, 07 Sep 2006 12:00:22 +0300" Date: Thu, 07 Sep 2006 10:15:39 -0400 Message-ID: <5031.1157638539@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.114 tagged_above=0 required=5 tests=AWL, SPF_HELO_PASS, SPF_PASS, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200609/50 X-Sequence-Number: 26261 Kaloyan Iliev 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 From sszabo@megazone.bigpanda.com Thu Sep 7 14:22:01 2006 X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 379E69FB202 for ; Thu, 7 Sep 2006 11:22:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 00192-05 for ; Thu, 7 Sep 2006 14:21:52 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by postgresql.org (Postfix) with ESMTP id C7B859FA2F5 for ; Thu, 7 Sep 2006 11:20:29 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id E028A35318; Thu, 7 Sep 2006 07:20:27 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id DE21A35317; Thu, 7 Sep 2006 07:20:27 -0700 (PDT) Date: Thu, 7 Sep 2006 07:20:27 -0700 (PDT) From: Stephan Szabo To: Kaloyan Iliev Cc: pgsql-sql@postgresql.org Subject: Re: Problem with FOR UPDATE In-Reply-To: <44FFDFA6.4080600@faith.digsys.bg> Message-ID: <20060907071820.P60412@megazone.bigpanda.com> References: <44FFDFA6.4080600@faith.digsys.bg> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200609/51 X-Sequence-Number: 26262 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
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