Re: [HACKERS] [PATCH] Deferrable unique constraints vs join removal -- bug?
On Sun, Oct 23, 2011 at 21:39, Tom Lane t...@sss.pgh.pa.us wrote: I will try to come up with a new patch in a few days (haven't had too much time lately). Oh, I did it already. Cool. I noticed now that you didn't add a regression test for this fix. Perhaps you could reuse the test from my patch, which also tests for plan invalidation. Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Deferrable unique constraints vs join removal -- bug?
On Sun, Oct 23, 2011 at 06:44, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I think maybe what we should do is add an immediate field to IndexOptInfo, mirroring the existing unique flag, and have get_relation_info() populate it from indimmediate, and then make relation_has_unique_index() disqualify any non-immediate index. Yeah, this seems like the right fix. Oh, that sounds pretty obvious now that you mention it. :) I will try to come up with a new patch in a few days (haven't had too much time lately). Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Deferrable unique constraints vs join removal -- bug?
Marti Raudsepp ma...@juffo.org writes: On Sun, Oct 23, 2011 at 06:44, Tom Lane t...@sss.pgh.pa.us wrote: Yeah, this seems like the right fix. Oh, that sounds pretty obvious now that you mention it. :) I will try to come up with a new patch in a few days (haven't had too much time lately). Oh, I did it already. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Deferrable unique constraints vs join removal -- bug?
Robert Haas robertmh...@gmail.com writes: Yuck. Well, that's certainly a bug. What's weird is that I thought we had put logic into the join removal code to ignore deferrable constraints. Apparently not. I poked around a bit more and could not find any evidence that we'd ever done that. Ah well. I think maybe what we should do is add an immediate field to IndexOptInfo, mirroring the existing unique flag, and have get_relation_info() populate it from indimmediate, and then make relation_has_unique_index() disqualify any non-immediate index. Yeah, this seems like the right fix. I considered redefining the unique flag to mean indisunique indimmediate, but that's wrong because of: has_unique_index() arguably needs a similar fix, although at present that appears to be used for only statistic purposes, so maybe it's OK. Yes, since this is meant for statistical purposes, I think it's appropriate for it to disregard indimmediate. A comment update might be a good idea, though. Or we could add a parameter to have the caller indicate which behavior is wanted. But for now I think a comment is enough. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Deferrable unique constraints vs join removal -- bug?
On Wed, Oct 19, 2011 at 7:35 AM, Marti Raudsepp ma...@juffo.org wrote: This probably doesn't affect many real-world applications, but it seems wrong that a performance feature can affect results returned by a query. Test case: create table uniq (i int unique deferrable initially deferred); begin; insert into uniq values(1),(1); select count(*) from uniq a left join uniq b using (i); count --- 2 Yuck. Well, that's certainly a bug. What's weird is that I thought we had put logic into the join removal code to ignore deferrable constraints. Apparently not. I think maybe what we should do is add an immediate field to IndexOptInfo, mirroring the existing unique flag, and have get_relation_info() populate it from indimmediate, and then make relation_has_unique_index() disqualify any non-immediate index. has_unique_index() arguably needs a similar fix, although at present that appears to be used for only statistic purposes, so maybe it's OK. A comment update might be a good idea, though. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Deferrable unique constraints vs join removal -- bug?
Robert Haas robertmh...@gmail.com writes: Yuck. Well, that's certainly a bug. What's weird is that I thought we had put logic into the join removal code to ignore deferrable constraints. Yeah, I thought we had too. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
