[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] Subselect left join / not exists()
From: Desmond Coertzen <patrolliekaptein () gmail ! com>
Date: 2016-02-29 17:17:20
Message-ID: CALQ6=2AkuawZW=6BvGwj3tJn=rHdtuvgv0WA6LDAU5BRp0ssng () mail ! gmail ! com
[Download RAW message or body]
Hi Tom,
Yes I did not provide nearly enough information. I was cheating in hope for
a quick answer of something anyone may have encountered before me of the
same nature.
I have been unable to reproduce the effect in an isolated test case, only
on my live production setup. I came a bit closer to what the problem may be.
I did not see this behaviour on 8.4.22 until I started with partial
indexing on a large table. A typical index was:
create index indx_lp_contract_iscash_true on loan_Payments (ContractKey,
sp_payment_iscash(DKey)) where sp_payment_iscash(DKey) = true;
I know you would need more info, but please bare with me. When I dropped
this type of index from this table, the broken effect went away and I got
healthy results from my sub selects.
I started building a test system based on Postgres 9.3.11. I took the SQL
dumps from my 8.4.22 setup and started restoring it on the 9.3.11 setup. In
the log, I started seeing this during restore:
ERROR: could not open relation with OID 36212
CONTEXT: SQL statement "SELECT exists(select * from loan_Payments lp
left join loan_payment_detail_nupay lpdn on
lpdn.loan_payment_id = lp.DKey
left join loan_payment_detail_bank_deposit lpdbd on
lpdbd.loan_payment_id = lp.DKey
left join loan_payment_detail_mctdebit lpdmct on
lpdmct.loan_payment_id = lp.DKey
left join loan_payment_detail_cashbook lpdcb on
lpdcb.loan_payment_id = lp.DKey
where lp.DKey = apaymentid and (lp.Type = 0 or
lpdn.loan_payment_id = apaymentid or lpdbd.loan_payment_id = apaymentid or
lpdmct.loan_payment_id = apaymentid or lpdcb.loan_payment_id = apaymentid)
and lp.Payment <> 0
)"
PL/pgSQL function sp_payment_iscash(bigint) line 3 at RETURN
STATEMENT: CREATE INDEX indx_lp_iscash_true ON loan_payments USING btree
(sp_payment_iscash((dkey)::bigint)) WHERE
(sp_payment_iscash((dkey)::bigint) = true);
This log message in 9.3.11 put me on the path to drop all the partial index
referencing my boolean function sp_payment_iscash on the 8.4.22 live setup.
This returned sanity to my reports.
I am working on my test case to try to invoke the behaviour on both 8.4.22
and 9.3.11.
I have an idea the problem may be that the function accepts int8 as a
parameter while the table is of int4 primary key type, where the primary
key of the table is passed to the function during partial index. More
testing and info will follow.
Regards.
On Fri, Feb 26, 2016 at 5:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Desmond Coertzen <patrolliekaptein@gmail.com> writes:
> > On Postgres 8.4.22.
>
> You realize of course that 8.4.x has been out of support for more than
> a year ...
>
> > The first form of the query looked like:
>
> > select lots, of, stuff,
> > (select max(ls2.fiscal_ts)::date
> > from long_story ls2
> > where ls2.contract_id = ls.contract_id and ls2.tr_value > 0 and
> > sp_tr_is_cash(ls2.primary_key_id)
> > and not exists(select * from long_story ls2r where
> ls2r.reverse_of_pk_id =
> > ls2.primary_key_id)
> > ) as last_cash_tr_ts
> > from long_story ls
> > where ls.create_ts >= current_date and ls.tr_type_id = 4;
>
> > The subselect columm "last_cash_tr_ts" produces null or bogus result.
>
> You haven't provided nearly enough detail for anyone to judge whether
> this is actually a bug or just your wrong expectation of what should
> happen. If you'd like people to look into it, please provide a
> self-contained test case: not only the query but table definitions
> and sample data. (Ideally, a SQL script that reproduces the problem
> starting from an empty database would make it easy for people to test.
> We're not likely to take the time to try to reverse-engineer context
> from an incomplete bug report.)
>
> If it is a bug, it will not get fixed in 8.4.x anyway, because there
> will never be any more 8.4.x releases. However, if the bug still exists
> in newer release branches, we'd definitely endeavor to fix it there.
>
> regards, tom lane
>
[Attachment #3 (text/html)]
<div dir="ltr">Hi Tom,<div><br></div><div>Yes I did not provide nearly enough \
information. I was cheating in hope for a quick answer of something anyone may have \
encountered before me of the same nature.</div><div><br></div><div>I have been unable \
to reproduce the effect in an isolated test case, only on my live production setup. I \
came a bit closer to what the problem may be.</div><div><br></div><div>I did not see \
this behaviour on 8.4.22 until I started with partial indexing on a large table. A \
typical index was:</div><div><br></div><div>create index indx_lp_contract_iscash_true \
on loan_Payments (ContractKey, sp_payment_iscash(DKey)) where sp_payment_iscash(DKey) \
= true;<br></div><div><br></div><div>I know you would need more info, but please bare \
with me. When I dropped this type of index from this table, the broken effect went \
away and I got healthy results from my sub selects.</div><div><br></div><div>I \
started building a test system based on Postgres 9.3.11. I took the SQL dumps from my \
8.4.22 setup and started restoring it on the 9.3.11 setup. In the log, I started \
seeing this during restore:</div><div><br></div><div>ERROR: could not open relation \
with OID 36212</div><div><div>CONTEXT: SQL statement "SELECT exists(select * \
from loan_Payments lp</div><div> left join \
loan_payment_detail_nupay lpdn on lpdn.loan_payment_id = lp.DKey</div><div> \
left join loan_payment_detail_bank_deposit lpdbd on lpdbd.loan_payment_id = \
lp.DKey</div><div> left join loan_payment_detail_mctdebit \
lpdmct on lpdmct.loan_payment_id = lp.DKey</div><div> left \
join loan_payment_detail_cashbook lpdcb on lpdcb.loan_payment_id = lp.DKey</div><div> \
where lp.DKey = apaymentid and (lp.Type = 0 or lpdn.loan_payment_id = apaymentid or \
lpdbd.loan_payment_id = apaymentid or lpdmct.loan_payment_id = apaymentid or \
lpdcb.loan_payment_id = apaymentid) and lp.Payment <> 0</div><div> \
)"</div></div><div>PL/pgSQL function sp_payment_iscash(bigint) line 3 at \
RETURN<br></div><div>STATEMENT: CREATE INDEX indx_lp_iscash_true ON loan_payments \
USING btree (sp_payment_iscash((dkey)::bigint)) WHERE \
(sp_payment_iscash((dkey)::bigint) = true);<br></div><div><br></div><div>This log \
message in 9.3.11 put me on the path to drop all the partial index referencing my \
boolean function sp_payment_iscash on the 8.4.22 live setup. This returned sanity to \
my reports.</div><div><br></div><div>I am working on my test case to try to invoke \
the behaviour on both 8.4.22 and 9.3.11.</div><div><br></div><div>I have an idea the \
problem may be that the function accepts int8 as a parameter while the table is of \
int4 primary key type, where the primary key of the table is passed to the function \
during partial index. More testing and info will \
follow.</div><div><br></div><div>Regards.</div><div><br></div><div><br></div></div><div \
class="gmail_extra"><br><div class="gmail_quote">On Fri, Feb 26, 2016 at 5:00 PM, Tom \
Lane <span dir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us" \
target="_blank">tgl@sss.pgh.pa.us</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">Desmond Coertzen <<a \
href="mailto:patrolliekaptein@gmail.com">patrolliekaptein@gmail.com</a>> \
writes:<br> > On Postgres 8.4.22.<br>
<br>
You realize of course that 8.4.x has been out of support for more than<br>
a year ...<br>
<span class=""><br>
> The first form of the query looked like:<br>
<br>
> select lots, of, stuff,<br>
> (select max(ls2.fiscal_ts)::date<br>
> from long_story ls2<br>
> where ls2.contract_id = ls.contract_id and ls2.tr_value > 0 and<br>
> sp_tr_is_cash(ls2.primary_key_id)<br>
> and not exists(select * from long_story ls2r where ls2r.reverse_of_pk_id =<br>
> ls2.primary_key_id)<br>
> ) as last_cash_tr_ts<br>
> from long_story ls<br>
> where ls.create_ts >= current_date and ls.tr_type_id = 4;<br>
<br>
> The subselect columm "last_cash_tr_ts" produces null or bogus \
result.<br> <br>
</span>You haven't provided nearly enough detail for anyone to judge whether<br>
this is actually a bug or just your wrong expectation of what should<br>
happen. If you'd like people to look into it, please provide a<br>
self-contained test case: not only the query but table definitions<br>
and sample data. (Ideally, a SQL script that reproduces the problem<br>
starting from an empty database would make it easy for people to test.<br>
We're not likely to take the time to try to reverse-engineer context<br>
from an incomplete bug report.)<br>
<br>
If it is a bug, it will not get fixed in 8.4.x anyway, because there<br>
will never be any more 8.4.x releases. However, if the bug still exists<br>
in newer release branches, we'd definitely endeavor to fix it there.<br>
<br>
regards, tom lane<br>
</blockquote></div><br></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic