agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedSubselect left join / not exists()
10+ messages / 4 participants
[nested] [flat]
* Subselect left join / not exists()
@ 2016-02-26 11:17 Desmond Coertzen <patrolliekaptein@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Desmond Coertzen @ 2016-02-26 11:17 UTC (permalink / raw)
To: pgsql-sql
Hi there,
I refer to this post:
http://www.postgresql.org/message-id/25301.1291044420@sss.pgh.pa.us
I'm not convinced the problem is fully exposed.
I also followed Tom's advice by adding a is-not-null check to my clause in
the not exists() section of my query. It did not fully solve my problem of
returning no rows or bogus rows. I had to remove an aggregate max() and
replace it with an order by desc limit 1, and I also had to relocate a date
cast.
On Postgres 8.4.22.
The query references the same table 3 times:
In the main "from",
In a subselect from the column perspective of the above "from",
And in a subselect again of the above subselect in a not-exists test.
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.
I tried wrapping the subselect in a stored function but came up with the
same broken result. I tried a left join on the reversal self-referencing PK
with a null test as a substitute for not exists() but that also produced
weird results. The only way I could produce the correct results was by
rewriting the subselect like this:
select lots, of, stuff,
(select ls2.fiscal_ts
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
is not null and ls2r.reverse_of_pk_id = ls2.primary_key_id)
order by 1 desc
limit 1
)::date as last_cash_tr_ts
from long_story ls
where ls.create_ts >= current_date and ls.tr_type_id = 4;
Including the not-null test in the subselect of the first query that
produced weird results did not solve the problem.
Notice the ::date cast outside of the subselect. If I include within the
subselect, I also get the same weird results.
Note that long_story.reverse_of_pk_id is a foreign key to itself,
long_story.primary_key_id and the FK column is nullable.
It's not the best DB design but the query without the null test and the max
aggregate should have worked. I am convinced there must be a bug exposed
when doing nested sub queries on the same table and the bug may show itself
the deeper you stack - stack meaning nested subselect on the same table. I
am also convinced that I am completely insane and may be missing something
very obvious like a noob.
Any help/comment highly appreciated in advance.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselect left join / not exists()
@ 2016-02-26 14:53 David G. Johnston <david.g.johnston@gmail.com>
parent: Desmond Coertzen <patrolliekaptein@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: David G. Johnston @ 2016-02-26 14:53 UTC (permalink / raw)
To: Desmond Coertzen <patrolliekaptein@gmail.com>; +Cc: pgsql-sql
On Fri, Feb 26, 2016 at 4:17 AM, Desmond Coertzen <
patrolliekaptein@gmail.com> wrote:
>
> It's not the best DB design but the query without the null test and the
> max aggregate should have worked. I am convinced there must be a bug
> exposed when doing nested sub queries on the same table and the bug may
> show itself the deeper you stack - stack meaning nested subselect on the
> same table. I am also convinced that I am completely insane and may be
> missing something very obvious like a noob.
>
> Any help/comment highly appreciated in advance.
>
If you deign to provide a self-contained test case showing where the
non-aggregated query provides bogus results while the aggregated and
limited one does not we would be most greatful since we could then test
whether what you are seeing exists in a release of PostgreSQL that is
currently supported. And if the behavior is correct we would have concrete
values that could be used the in the explanation of said behavior.
Don't expect us to be able to upgrade the quality of the discussion: If the
best you can give us is phrases like "weird" and "bogus" to describe what
you are seeing, and no explicit schema definitions, then they best I can
say is that while this looks odd it is likely explainable and a direct
function of the fact that "it's not the best DB design" and that because of
such there are data anomalies that are potentially coming into play here.
Or its a bug - potentially one that has been fixed.
David J.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselect left join / not exists()
@ 2016-02-26 15:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Desmond Coertzen <patrolliekaptein@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2016-02-26 15:00 UTC (permalink / raw)
To: Desmond Coertzen <patrolliekaptein@gmail.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselect left join / not exists()
@ 2016-02-26 19:54 Desmond Coertzen <patrolliekaptein@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Desmond Coertzen @ 2016-02-26 19:54 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
I don't use the terms "bogus" and "weird" lightly.
A self contained test case is difficult to produce. I already built a
script that creates a DB, three tables and test data, and then exercised
the two forms of the the sub select. As expected, the test case does not
provoke the behaviour whitnessed. Other than providing the entire DB dump
to recreate the exact conditions that provoke this behaviour, I don't see
how I can provide a self contained test case. The real table representing
"long_story" in my report contains over 10.5 million rows and the behaviour
in the sub select was not there before today. Possibly as my data
collection grew, I may have stumbled over a problem.
I will try anyway by inserting more rows to try and provoke the behaviour.
I will continue my answer on Tom's reply.
On Fri, Feb 26, 2016 at 4:53 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Fri, Feb 26, 2016 at 4:17 AM, Desmond Coertzen <
> patrolliekaptein@gmail.com> wrote:
>
>>
>> It's not the best DB design but the query without the null test and the
>> max aggregate should have worked. I am convinced there must be a bug
>> exposed when doing nested sub queries on the same table and the bug may
>> show itself the deeper you stack - stack meaning nested subselect on the
>> same table. I am also convinced that I am completely insane and may be
>> missing something very obvious like a noob.
>>
>> Any help/comment highly appreciated in advance.
>>
>
> If you deign to provide a self-contained test case showing where the
> non-aggregated query provides bogus results while the aggregated and
> limited one does not we would be most greatful since we could then test
> whether what you are seeing exists in a release of PostgreSQL that is
> currently supported. And if the behavior is correct we would have concrete
> values that could be used the in the explanation of said behavior.
>
> Don't expect us to be able to upgrade the quality of the discussion: If
> the best you can give us is phrases like "weird" and "bogus" to describe
> what you are seeing, and no explicit schema definitions, then they best I
> can say is that while this looks odd it is likely explainable and a direct
> function of the fact that "it's not the best DB design" and that because of
> such there are data anomalies that are potentially coming into play here.
>
> Or its a bug - potentially one that has been fixed.
>
> David J.
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselect left join / not exists()
@ 2016-02-26 20:35 Rob Sargent <robjsargent@gmail.com>
parent: Desmond Coertzen <patrolliekaptein@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Rob Sargent @ 2016-02-26 20:35 UTC (permalink / raw)
To: pgsql-sql
On 02/26/2016 12:54 PM, Desmond Coertzen wrote:
> I don't use the terms "bogus" and "weird" lightly.
>
> A self contained test case is difficult to produce. I already built a
> script that creates a DB, three tables and test data, and then
> exercised the two forms of the the sub select. As expected, the test
> case does not provoke the behaviour whitnessed. Other than providing
> the entire DB dump to recreate the exact conditions that provoke this
> behaviour, I don't see how I can provide a self contained test case.
> The real table representing "long_story" in my report contains over
> 10.5 million rows and the behaviour in the sub select was not there
> before today. Possibly as my data collection grew, I may have stumbled
> over a problem.
>
> I will try anyway by inserting more rows to try and provoke the
> behaviour. I will continue my answer on Tom's reply.
>
>
> On Fri, Feb 26, 2016 at 4:53 PM, David G. Johnston
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
> On Fri, Feb 26, 2016 at 4:17 AM, Desmond Coertzen
> <patrolliekaptein@gmail.com <mailto:patrolliekaptein@gmail.com>>wrote:
>
>
> It's not the best DB design but the query without the null
> test and the max aggregate should have worked. I am convinced
> there must be a bug exposed when doing nested sub queries on
> the same table and the bug may show itself the deeper you
> stack - stack meaning nested subselect on the same table. I am
> also convinced that I am completely insane and may be missing
> something very obvious like a noob.
>
> Any help/comment highly appreciated in advance.
>
>
> If you deign to provide a self-contained test case showing where
> the non-aggregated query provides bogus results while the
> aggregated and limited one does not we would be most greatful
> since we could then test whether what you are seeing exists in a
> release of PostgreSQL that is currently supported. And if the
> behavior is correct we would have concrete values that could be
> used the in the explanation of said behavior.
>
> Don't expect us to be able to upgrade the quality of the
> discussion: If the best you can give us is phrases like "weird"
> and "bogus" to describe what you are seeing, and no explicit
> schema definitions, then they best I can say is that while this
> looks odd it is likely explainable and a direct function of the
> fact that "it's not the best DB design" and that because of such
> there are data anomalies that are potentially coming into play here.
>
> Or its a bug - potentially one that has been fixed.
>
> David J.
>
>
>
The real schema and sql used might get people started
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselect left join / not exists()
@ 2016-02-29 17:17 Desmond Coertzen <patrolliekaptein@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Desmond Coertzen @ 2016-02-29 17:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselect left join / not exists()
@ 2016-03-01 10:24 Desmond Coertzen <patrolliekaptein@gmail.com>
parent: Desmond Coertzen <patrolliekaptein@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Desmond Coertzen @ 2016-03-01 10:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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 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
>>
>
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Attachments:
[text/x-sql] sp_payment_iscash.pg.sql (844B, ../../CALQ6=2Cu_pROq6AAw5HZ8Mkon1B6oNPRsap6khpNS2AUAxV7wQ@mail.gmail.com/3-sp_payment_iscash.pg.sql)
download | inline:
set search_path = bbrk;
create or replace function sp_payment_iscash(apaymentid bigint) returns boolean as
$BODY$
begin
return 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
);
end;
$BODY$
immutable
language plpgsql;
[text/x-sql] loan_Payments.ddl.sql (7.6K, ../../CALQ6=2Cu_pROq6AAw5HZ8Mkon1B6oNPRsap6khpNS2AUAxV7wQ@mail.gmail.com/4-loan_Payments.ddl.sql)
download | inline:
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = bbrk, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: loan_payments; Type: TABLE; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE TABLE loan_payments (
dkey integer DEFAULT nextval('seq_loan_payments_id'::regclass) NOT NULL,
paydate character varying(21) DEFAULT NULL::character varying NOT NULL,
contractkey integer DEFAULT 0 NOT NULL,
pmstaffkey integer DEFAULT 0 NOT NULL,
payment numeric DEFAULT 0.00 NOT NULL,
notes character varying(255) DEFAULT NULL::character varying,
type integer DEFAULT 0,
usern character varying(50) DEFAULT '0'::character varying,
tdate character varying(21) DEFAULT '0'::character varying,
metpay integer DEFAULT 0,
reference character varying(50) DEFAULT NULL::character varying,
eft integer DEFAULT 0 NOT NULL,
connection_string_oninsert character varying(100) DEFAULT NULL::character varying,
tdate_native timestamp without time zone,
paydate_native timestamp without time zone NOT NULL,
connection_string_onupdate character varying(100) DEFAULT NULL::character varying,
create_ts timestamp without time zone DEFAULT now() NOT NULL,
update_ts timestamp without time zone,
exported_to_excalibur integer DEFAULT 0 NOT NULL,
reverse_of_payment_id integer,
debit_payable_on_ts timestamp without time zone,
created_by_bbrk_user_id integer,
statement_sensor_enable integer DEFAULT 0 NOT NULL,
reference_int bigint,
statement_presentation_ts timestamp without time zone
);
ALTER TABLE bbrk.loan_payments OWNER TO mysqltest;
--
-- Name: loan_payments_pkey; Type: CONSTRAINT; Schema: bbrk; Owner: mysqltest; Tablespace:
--
ALTER TABLE ONLY loan_payments
ADD CONSTRAINT loan_payments_pkey PRIMARY KEY (dkey);
--
-- Name: contractkey; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX contractkey ON loan_payments USING btree (contractkey);
--
-- Name: eft; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX eft ON loan_payments USING btree (eft);
--
-- Name: fk_lp_createuser; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX fk_lp_createuser ON loan_payments USING btree (created_by_bbrk_user_id);
--
-- Name: fk_lp_reverseoforiginal; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX fk_lp_reverseoforiginal ON loan_payments USING btree (reverse_of_payment_id);
--
-- Name: indx_lp_combo_contracttype; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lp_combo_contracttype ON loan_payments USING btree (contractkey, type);
--
-- Name: indx_lp_contractdatenat; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lp_contractdatenat ON loan_payments USING btree (contractkey, tdate_native);
--
-- Name: indx_lp_contractdebtpayablets; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lp_contractdebtpayablets ON loan_payments USING btree (contractkey, debit_payable_on_ts);
--
-- Name: indx_lp_contractpaydatenat; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lp_contractpaydatenat ON loan_payments USING btree (contractkey, paydate_native);
--
-- Name: indx_lp_contracttypepaytdate; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lp_contracttypepaytdate ON loan_payments USING btree (contractkey, type, payment, tdate_native);
--
-- Name: indx_lp_cts; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lp_cts ON loan_payments USING btree (create_ts);
--
-- Name: indx_lp_debtpayablets; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lp_debtpayablets ON loan_payments USING btree (debit_payable_on_ts);
--
-- Name: indx_lp_paydatenat; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lp_paydatenat ON loan_payments USING btree (paydate_native);
--
-- Name: indx_lp_reference; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lp_reference ON loan_payments USING btree (reference);
--
-- Name: indx_lp_spts; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lp_spts ON loan_payments USING btree (statement_presentation_ts);
--
-- Name: indx_lp_tdatenat; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lp_tdatenat ON loan_payments USING btree (tdate_native);
--
-- Name: indx_lp_typereference; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lp_typereference ON loan_payments USING btree (type, reference);
--
-- Name: indx_pymt_contractexportexcal; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_pymt_contractexportexcal ON loan_payments USING btree (contractkey, exported_to_excalibur);
--
-- Name: indx_pymt_exportexcal; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_pymt_exportexcal ON loan_payments USING btree (exported_to_excalibur);
--
-- Name: paydate; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX paydate ON loan_payments USING btree (paydate);
--
-- Name: payment; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX payment ON loan_payments USING btree (payment);
--
-- Name: type; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX type ON loan_payments USING btree (type);
--
-- Name: tgr_loan_payments_after_delete; Type: TRIGGER; Schema: bbrk; Owner: mysqltest
--
CREATE TRIGGER tgr_loan_payments_after_delete
AFTER DELETE ON loan_payments
FOR EACH ROW
EXECUTE PROCEDURE tgrf_loan_payments_after_delete();
--
-- Name: tgr_loan_payments_after_insert; Type: TRIGGER; Schema: bbrk; Owner: mysqltest
--
CREATE TRIGGER tgr_loan_payments_after_insert
AFTER INSERT ON loan_payments
FOR EACH ROW
EXECUTE PROCEDURE tgrf_loan_payments_after_insert();
--
-- Name: tgr_loan_payments_before_insert; Type: TRIGGER; Schema: bbrk; Owner: mysqltest
--
CREATE TRIGGER tgr_loan_payments_before_insert
BEFORE INSERT ON loan_payments
FOR EACH ROW
EXECUTE PROCEDURE tgrf_loan_payments_before_insert();
--
-- Name: tgr_loan_payments_before_update; Type: TRIGGER; Schema: bbrk; Owner: mysqltest
--
CREATE TRIGGER tgr_loan_payments_before_update
BEFORE UPDATE ON loan_payments
FOR EACH ROW
EXECUTE PROCEDURE tgrf_loan_payments_before_update();
--
-- Name: fk_lp_contract; Type: FK CONSTRAINT; Schema: bbrk; Owner: mysqltest
--
ALTER TABLE ONLY loan_payments
ADD CONSTRAINT fk_lp_contract FOREIGN KEY (contractkey) REFERENCES loan_contractn(loankey);
--
-- Name: fk_lp_createuser; Type: FK CONSTRAINT; Schema: bbrk; Owner: mysqltest
--
ALTER TABLE ONLY loan_payments
ADD CONSTRAINT fk_lp_createuser FOREIGN KEY (created_by_bbrk_user_id) REFERENCES bbrk_user(bbrk_user_id);
--
-- Name: fk_lp_reverseoforiginal; Type: FK CONSTRAINT; Schema: bbrk; Owner: mysqltest
--
ALTER TABLE ONLY loan_payments
ADD CONSTRAINT fk_lp_reverseoforiginal FOREIGN KEY (reverse_of_payment_id) REFERENCES loan_payments(dkey);
--
-- Name: loan_payments; Type: ACL; Schema: bbrk; Owner: mysqltest
--
REVOKE ALL ON TABLE loan_payments FROM PUBLIC;
REVOKE ALL ON TABLE loan_payments FROM mysqltest;
GRANT ALL ON TABLE loan_payments TO mysqltest;
GRANT SELECT ON TABLE loan_payments TO wfgsoftware;
--
-- PostgreSQL database dump complete
--
[text/x-sql] loan_payment_detail_bank_deposit.ddl.sql (2.2K, ../../CALQ6=2Cu_pROq6AAw5HZ8Mkon1B6oNPRsap6khpNS2AUAxV7wQ@mail.gmail.com/5-loan_payment_detail_bank_deposit.ddl.sql)
download | inline:
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = bbrk, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: loan_payment_detail_bank_deposit; Type: TABLE; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE TABLE loan_payment_detail_bank_deposit (
loan_payment_id integer NOT NULL,
bank_deposit_id bigint NOT NULL
);
ALTER TABLE bbrk.loan_payment_detail_bank_deposit OWNER TO mysqltest;
--
-- Name: loan_payment_detail_bank_deposit_pkey; Type: CONSTRAINT; Schema: bbrk; Owner: mysqltest; Tablespace:
--
ALTER TABLE ONLY loan_payment_detail_bank_deposit
ADD CONSTRAINT loan_payment_detail_bank_deposit_pkey PRIMARY KEY (loan_payment_id);
--
-- Name: fk_lpdbd_deposit; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX fk_lpdbd_deposit ON loan_payment_detail_bank_deposit USING btree (bank_deposit_id);
--
-- Name: tgr_loan_payment_detail_bank_deposit_before_insert; Type: TRIGGER; Schema: bbrk; Owner: mysqltest
--
CREATE TRIGGER tgr_loan_payment_detail_bank_deposit_before_insert
BEFORE INSERT ON loan_payment_detail_bank_deposit
FOR EACH ROW
EXECUTE PROCEDURE tgrf_loan_payment_detail_bank_deposit_before_insert();
--
-- Name: tgr_loan_payment_detail_bank_deposit_before_update; Type: TRIGGER; Schema: bbrk; Owner: mysqltest
--
CREATE TRIGGER tgr_loan_payment_detail_bank_deposit_before_update
BEFORE UPDATE ON loan_payment_detail_bank_deposit
FOR EACH ROW
EXECUTE PROCEDURE tgrf_loan_payment_detail_bank_deposit_before_update();
--
-- Name: fk_lpdbd_deposit; Type: FK CONSTRAINT; Schema: bbrk; Owner: mysqltest
--
ALTER TABLE ONLY loan_payment_detail_bank_deposit
ADD CONSTRAINT fk_lpdbd_deposit FOREIGN KEY (bank_deposit_id) REFERENCES bank_deposit(bank_deposit_id);
--
-- Name: fk_lpdbd_payment; Type: FK CONSTRAINT; Schema: bbrk; Owner: mysqltest
--
ALTER TABLE ONLY loan_payment_detail_bank_deposit
ADD CONSTRAINT fk_lpdbd_payment FOREIGN KEY (loan_payment_id) REFERENCES loan_payments(dkey);
--
-- PostgreSQL database dump complete
--
[text/x-sql] loan_payment_detail_cashbook.ddl.sql (1.6K, ../../CALQ6=2Cu_pROq6AAw5HZ8Mkon1B6oNPRsap6khpNS2AUAxV7wQ@mail.gmail.com/6-loan_payment_detail_cashbook.ddl.sql)
download | inline:
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = bbrk, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: loan_payment_detail_cashbook; Type: TABLE; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE TABLE loan_payment_detail_cashbook (
loan_payment_id integer NOT NULL,
source_account_reference character varying(80) NOT NULL,
source_contract_id integer NOT NULL
);
ALTER TABLE bbrk.loan_payment_detail_cashbook OWNER TO mysqltest;
--
-- Name: loan_payment_detail_cashbook_pkey; Type: CONSTRAINT; Schema: bbrk; Owner: mysqltest; Tablespace:
--
ALTER TABLE ONLY loan_payment_detail_cashbook
ADD CONSTRAINT loan_payment_detail_cashbook_pkey PRIMARY KEY (loan_payment_id);
--
-- Name: fk_lpdcb_srccontract; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX fk_lpdcb_srccontract ON loan_payment_detail_cashbook USING btree (source_contract_id);
--
-- Name: fk_lpdcb_srccontract; Type: FK CONSTRAINT; Schema: bbrk; Owner: mysqltest
--
ALTER TABLE ONLY loan_payment_detail_cashbook
ADD CONSTRAINT fk_lpdcb_srccontract FOREIGN KEY (source_contract_id) REFERENCES loan_contractn(loankey);
--
-- Name: fk_lpdcb_transaction; Type: FK CONSTRAINT; Schema: bbrk; Owner: mysqltest
--
ALTER TABLE ONLY loan_payment_detail_cashbook
ADD CONSTRAINT fk_lpdcb_transaction FOREIGN KEY (loan_payment_id) REFERENCES loan_payments(dkey);
--
-- PostgreSQL database dump complete
--
[text/x-sql] loan_payment_detail_mctdebit.ddl.sql (2.4K, ../../CALQ6=2Cu_pROq6AAw5HZ8Mkon1B6oNPRsap6khpNS2AUAxV7wQ@mail.gmail.com/7-loan_payment_detail_mctdebit.ddl.sql)
download | inline:
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = bbrk, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: loan_payment_detail_mctdebit; Type: TABLE; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE TABLE loan_payment_detail_mctdebit (
loan_payment_id integer NOT NULL,
cycle_date timestamp without time zone NOT NULL,
reference character varying(14) NOT NULL,
sequence_number bigint NOT NULL,
response_code character varying(2) NOT NULL,
client_bank_account_id bigint,
effective_date timestamp without time zone,
tracking_period_id integer,
original_action_date timestamp without time zone NOT NULL
);
ALTER TABLE bbrk.loan_payment_detail_mctdebit OWNER TO mysqltest;
--
-- Name: loan_payment_detail_mctdebit_pkey; Type: CONSTRAINT; Schema: bbrk; Owner: mysqltest; Tablespace:
--
ALTER TABLE ONLY loan_payment_detail_mctdebit
ADD CONSTRAINT loan_payment_detail_mctdebit_pkey PRIMARY KEY (loan_payment_id);
--
-- Name: unq_lpdmctd_unqref; Type: CONSTRAINT; Schema: bbrk; Owner: mysqltest; Tablespace:
--
ALTER TABLE ONLY loan_payment_detail_mctdebit
ADD CONSTRAINT unq_lpdmctd_unqref UNIQUE (cycle_date, reference, response_code, original_action_date);
--
-- Name: indx_lpdmct_cba; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lpdmct_cba ON loan_payment_detail_mctdebit USING btree (client_bank_account_id);
--
-- Name: tgr_loan_payment_detail_mctdebit_after_insert; Type: TRIGGER; Schema: bbrk; Owner: mysqltest
--
CREATE TRIGGER tgr_loan_payment_detail_mctdebit_after_insert
AFTER INSERT ON loan_payment_detail_mctdebit
FOR EACH ROW
EXECUTE PROCEDURE tgrf_loan_payment_detail_mctdebit_after_insert();
--
-- Name: fk_lpdmct_cba; Type: FK CONSTRAINT; Schema: bbrk; Owner: mysqltest
--
ALTER TABLE ONLY loan_payment_detail_mctdebit
ADD CONSTRAINT fk_lpdmct_cba FOREIGN KEY (client_bank_account_id) REFERENCES client_bank_account(client_bank_account_id);
--
-- Name: fk_lpdmctd_payment; Type: FK CONSTRAINT; Schema: bbrk; Owner: mysqltest
--
ALTER TABLE ONLY loan_payment_detail_mctdebit
ADD CONSTRAINT fk_lpdmctd_payment FOREIGN KEY (loan_payment_id) REFERENCES loan_payments(dkey);
--
-- PostgreSQL database dump complete
--
[text/x-sql] loan_payment_detail_nupay.ddl.sql (2.4K, ../../CALQ6=2Cu_pROq6AAw5HZ8Mkon1B6oNPRsap6khpNS2AUAxV7wQ@mail.gmail.com/8-loan_payment_detail_nupay.ddl.sql)
download | inline:
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = bbrk, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: loan_payment_detail_nupay; Type: TABLE; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE TABLE loan_payment_detail_nupay (
loan_payment_id integer NOT NULL,
nupay_transaction_id bigint NOT NULL,
nupay_result_code integer NOT NULL,
nupay_reply_ts timestamp without time zone NOT NULL
);
ALTER TABLE bbrk.loan_payment_detail_nupay OWNER TO mysqltest;
--
-- Name: loan_payment_detail_nupay_pkey; Type: CONSTRAINT; Schema: bbrk; Owner: mysqltest; Tablespace:
--
ALTER TABLE ONLY loan_payment_detail_nupay
ADD CONSTRAINT loan_payment_detail_nupay_pkey PRIMARY KEY (loan_payment_id);
--
-- Name: unq_lpdn_nupaytransactionreplystatus; Type: CONSTRAINT; Schema: bbrk; Owner: mysqltest; Tablespace:
--
ALTER TABLE ONLY loan_payment_detail_nupay
ADD CONSTRAINT unq_lpdn_nupaytransactionreplystatus UNIQUE (nupay_transaction_id, nupay_reply_ts, nupay_result_code);
--
-- Name: indx_lpdn_nupayresult; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lpdn_nupayresult ON loan_payment_detail_nupay USING btree (nupay_result_code);
--
-- Name: indx_lpdn_nupaytransactionreply; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lpdn_nupaytransactionreply ON loan_payment_detail_nupay USING btree (nupay_transaction_id, nupay_reply_ts);
--
-- Name: indx_lpdnupay_replyts; Type: INDEX; Schema: bbrk; Owner: mysqltest; Tablespace:
--
CREATE INDEX indx_lpdnupay_replyts ON loan_payment_detail_nupay USING btree (nupay_reply_ts);
--
-- Name: tgr_loan_payment_detail_nupay_after_insert; Type: TRIGGER; Schema: bbrk; Owner: mysqltest
--
CREATE TRIGGER tgr_loan_payment_detail_nupay_after_insert
AFTER INSERT ON loan_payment_detail_nupay
FOR EACH ROW
EXECUTE PROCEDURE tgrf_loan_payment_detail_nupay_after_insert();
--
-- Name: fk_lpdn_payment; Type: FK CONSTRAINT; Schema: bbrk; Owner: mysqltest
--
ALTER TABLE ONLY loan_payment_detail_nupay
ADD CONSTRAINT fk_lpdn_payment FOREIGN KEY (loan_payment_id) REFERENCES loan_payments(dkey);
--
-- PostgreSQL database dump complete
--
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselect left join / not exists()
@ 2016-03-13 23:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Desmond Coertzen <patrolliekaptein@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2016-03-13 23:13 UTC (permalink / raw)
To: Desmond Coertzen <patrolliekaptein@gmail.com>; +Cc: pgsql-sql
[ sorry for slow response ]
Desmond Coertzen <patrolliekaptein@gmail.com> writes:
> 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 think you are running into the same issue discussed in this thread:
http://www.postgresql.org/message-id/flat/87tx0dc80x.fsf@news-spur.riddles.org.uk
namely that you are trying to create an index on an allegedly immutable
function which, far from being immutable, actually attempts to consult the
table that the index is on. That's never been considered supported, which
is why not a lot of enthusiasm has been mustered for suppressing this
weird error message. The error message is indeed annoying and confusing,
but it's not like such an index could be expected to work usefully if we
prevented the error during index build. In the example you've got here,
not only is the function consulting the underlying table, but four other
tables as well. Updates on any one of those could invalidate the result,
but there's no mechanism to cause the index entries to be recomputed
when some other table changes.
So in short, you really need to reconsider trying to use an index this
way.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselect left join / not exists()
@ 2016-03-14 09:40 Desmond Coertzen <patrolliekaptein@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Desmond Coertzen @ 2016-03-14 09:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
I get it.
I was under the impression that postgres will not allow me to declare a
function immutable if it falls outside of the bounds of the definition. In
example function sp_payment_iscash, the result would have been the same for
every row with the same arguments, although that is because I willed it
like that by design and not because it could not change through user action
by manipulating the other tables references by the function.
I have other indexed immutable functions that references the table in the
index and one more table. I will be taking a hard look at those as well.
The ability of postgres to index a function of this nature will be very
much kick-ass if it could be supported in the future.
Thanks for your valuable input.
Regards.
On Mon, Mar 14, 2016 at 1:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> [ sorry for slow response ]
>
> Desmond Coertzen <patrolliekaptein@gmail.com> writes:
> > 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 think you are running into the same issue discussed in this thread:
>
>
> http://www.postgresql.org/message-id/flat/87tx0dc80x.fsf@news-spur.riddles.org.uk
>
> namely that you are trying to create an index on an allegedly immutable
> function which, far from being immutable, actually attempts to consult the
> table that the index is on. That's never been considered supported, which
> is why not a lot of enthusiasm has been mustered for suppressing this
> weird error message. The error message is indeed annoying and confusing,
> but it's not like such an index could be expected to work usefully if we
> prevented the error during index build. In the example you've got here,
> not only is the function consulting the underlying table, but four other
> tables as well. Updates on any one of those could invalidate the result,
> but there's no mechanism to cause the index entries to be recomputed
> when some other table changes.
>
> So in short, you really need to reconsider trying to use an index this
> way.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselect left join / not exists()
@ 2016-03-14 13:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Desmond Coertzen <patrolliekaptein@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2016-03-14 13:42 UTC (permalink / raw)
To: Desmond Coertzen <patrolliekaptein@gmail.com>; +Cc: pgsql-sql
Desmond Coertzen <patrolliekaptein@gmail.com> writes:
> I was under the impression that postgres will not allow me to declare a
> function immutable if it falls outside of the bounds of the definition.
IIRC, there was an explicit decision not to throw errors for "immutable
function reads a table", because we have no way to be sure that such a
thing might not be safe in practice in a particular usage. (That is,
you actually don't ever change the table's contents after first use of
the function.) But yeah, it's a gray area.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2016-03-14 13:42 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-02-26 11:17 Subselect left join / not exists() Desmond Coertzen <patrolliekaptein@gmail.com>
2016-02-26 14:53 ` David G. Johnston <david.g.johnston@gmail.com>
2016-02-26 19:54 ` Desmond Coertzen <patrolliekaptein@gmail.com>
2016-02-26 20:35 ` Rob Sargent <robjsargent@gmail.com>
2016-02-26 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-02-29 17:17 ` Desmond Coertzen <patrolliekaptein@gmail.com>
2016-03-01 10:24 ` Desmond Coertzen <patrolliekaptein@gmail.com>
2016-03-13 23:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-03-14 09:40 ` Desmond Coertzen <patrolliekaptein@gmail.com>
2016-03-14 13:42 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox