Re: Subselect left join / not exists() - Mailing list pgsql-sql
From | Desmond Coertzen |
---|---|
Subject | Re: Subselect left join / not exists() |
Date | |
Msg-id | CALQ6=2Cu_pROq6AAw5HZ8Mkon1B6oNPRsap6khpNS2AUAxV7wQ@mail.gmail.com Whole thread Raw |
In response to | Re: Subselect left join / not exists() (Desmond Coertzen <patrolliekaptein@gmail.com>) |
Responses |
Re: Subselect left join / not exists()
|
List | pgsql-sql |
Recreating the stored function with an int4 parameter instead of int8 and trying to create the partial index does not change anything on 9.3.11
I cannot create this index on 9.3.11. I tried to recreate the index on 9.3.11 after my restore of my live setup from 8.4.22.
New detail in the output this time:
ERROR: could not read block 0 in file "base/28654/39611": read only 0 of 8192 bytes
I checked my file system and disk space. No problems. The file in pg_data dir referenced in the error message does not exist. I ran vacuum analyze verboze on the 5 tables referenced by the stored function. I did not see any errors. I tried the index creation again. It failed with the same message.
Worse, even though the attempt to create the index failed and the command returned with a failed result immediately, the back end process running index creation is still running. I can see it on pg_stat_activity. It is not really running though. ps -ef shows "postgres: desmondc micro 10.0.3.169(37339) idle" and no disk io etc on the test system.
Lets forget what I see on 8.4.22. I'm failing on 9.3.11 on a test system to create a partial index and it seems the behaviour I'm getting is close to sigsegv that propagates corruption as far as pg_stat_activity. A bad exit on a routine or something.
I'm attaching DDL for 5 tables involved and the actual DDL of the stored function I'm trying to index. The index I'm trying to create is:
create index indx_lp_iscash on loan_Payments (sp_payment_iscash(DKey));
Full output of the attempt:
ERROR: could not read block 0 in file "base/28654/39618": read only 0 of 8192 bytes
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(integer) line 3 at RETURN
What else can I provide to narrow this down?
Regards.
On Mon, Feb 29, 2016 at 7:17 PM, Desmond Coertzen <patrolliekaptein@gmail.com> wrote:
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 36212CONTEXT: SQL statement "SELECT exists(select * from loan_Payments lpleft join loan_payment_detail_nupay lpdn on lpdn.loan_payment_id = lp.DKeyleft join loan_payment_detail_bank_deposit lpdbd on lpdbd.loan_payment_id = lp.DKeyleft join loan_payment_detail_mctdebit lpdmct on lpdmct.loan_payment_id = lp.DKeyleft join loan_payment_detail_cashbook lpdcb on lpdcb.loan_payment_id = lp.DKeywhere 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 RETURNSTATEMENT: 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