From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Daniel Newman <dtnewman(at)gmail(dot)com> |
Cc: | danielnewman(at)umich(dot)edu, Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #14210: filter by "=" constraint doesn't work when hash index is present on a column |
Date: | 2016-06-23 23:08:57 |
Message-ID: | 10331.1466723337@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Daniel Newman <dtnewman(at)gmail(dot)com> writes:
> Yes. If i delete the index and recreate it, the bug is replicated.
So the answer is that this got broken by commit 9f03ca915196dfc8,
which appears to have imagined that _hash_form_tuple() is just an
alias for index_form_tuple(). But it ain't. As a result, construction
of hash indexes larger than shared_buffers is broken in 9.5 and up:
what gets entered into the index is garbage, because we are taking
raw data as if it were already hashed. (In fact, in this example,
we seem to be taking *pointers* to raw data as the hash values.)
> Interestingly, I modified the pg_dump file a bit. At the end, it says:
>> CREATE INDEX hash_issue_index ON hash_issue_table USING hash
>> (hash_issue_column);
>> DROP INDEX hash_issue_index;
>> CREATE INDEX hash_issue_index ON hash_issue_table USING hash
>> (hash_issue_column);
> This is because the issue was not replicating (for some reason) when it
> built the index the first time.
I think what's happening there is that the first CREATE INDEX
underestimates the size of the table and decides it doesn't need to
use the _h_spool() code path. The other path isn't broken.
We can either revert the aforesaid commit so far as it affects hash,
or do something to break _hash_form_tuple's encapsulation of the
hash-value-for-data substitution. I don't immediately see a non-messy
way to do the latter.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Newman | 2016-06-23 23:31:41 | Re: BUG #14210: filter by "=" constraint doesn't work when hash index is present on a column |
Previous Message | Andres Freund | 2016-06-23 22:53:01 | Re: BUG #14208: Inconsistent code modification - 3 |