Re: Subselect left join / not exists()

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(at)gmail(dot)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 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Desmond Coertzen <patrolliekaptein(at)gmail(dot)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
>>
>
>