BUG #4410: Indexes not seen right away
The following bug has been logged online:
Bug reference: 4410
Logged by: Greg Sabino Mullane
Email address: greg@endpoint.com
PostgreSQL version: 8.3.3
Operating system: Linux
Description: Indexes not seen right away
Details:
I cannot reproduce, as this was on a production system and not seen again,
but I created a simple index on a TEXT field, which was not chosen by the
planner, even when seqscan was turned off. I analyzed the table, checked all
the settings, etc. pg_index looked as it should. Eventually (~ 10 minutes
later) the index as chosen - I don't know what might have triggered it to
start appearing. Creating a second table based on the first worked as it
should during the 'noindex' time period: CREATE TABLE foo AS SELECT * FROM
bar; CREATE INDEX foo_idx1 ON foo(textcol); EXPLAIN SELECT 1 FROM foo WHERE
textcol1 = 'baz';
"Greg Sabino Mullane" <greg@endpoint.com> writes:
I cannot reproduce, as this was on a production system and not seen again,
but I created a simple index on a TEXT field, which was not chosen by the
planner, even when seqscan was turned off. I analyzed the table, checked all
the settings, etc. pg_index looked as it should. Eventually (~ 10 minutes
later) the index as chosen - I don't know what might have triggered it to
start appearing.
Does it have pg_index.indcheckxmin = true? If so, see README.HOT.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
Does it have pg_index.indcheckxmin = true? If so, see README.HOT.
Yes, that was probably it. Is this worth noting in the documentation somewhere
(other than the technical bowels of HOT)? Perhaps in the CREATE INDEX
docs?
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200809081638
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAkjFjVwACgkQvJuQZxSWSsglCACg18kkSFnwXYgf6LXxV/UC98Us
jU8An3zMxd58t3A5NemfbHJ++uANYkUu
=rHHT
-----END PGP SIGNATURE-----
Greg Sabino Mullane wrote:
Does it have pg_index.indcheckxmin = true? If so, see README.HOT.
Yes, that was probably it. Is this worth noting in the documentation somewhere
(other than the technical bowels of HOT)? Perhaps in the CREATE INDEX
docs?
I have done some research on this. Postgres 8.3 didn't allow
in-progress transactions to see the CREATE INDEX if the index had broken
HOT chains. However, the 8.4 code has more sophisticated tracking of
snapshots so this should be less of a problem. The only way I could get
the CREATE INDEX to be invisible in 8.4 was to use a serialized
isolation level.
I have attached the scripts I used for testing. I don't think it is
worth documenting this until we have 8.4 released and people start using
it. I believe a mention in the manual would require quite complex
wording. I have also updated README.HOT, patch attached.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
Does it have pg_index.indcheckxmin = true? If so, see README.HOT.
Yes, that was probably it. Is this worth noting in the documentation somewhere
(other than the technical bowels of HOT)? Perhaps in the CREATE INDEX
docs?
...
I have attached the scripts I used for testing. I don't think it is
worth documenting this until we have 8.4 released and people start using
it.
I'm not following this, Bruce. HOT came out in 8.3, not 8.4, so why would we
wait on documenting the problem?
I believe a mention in the manual would require quite complex wording.
Seems something would be better than nothing. Example:
"Note: In versions 8.3 and higher, indexes may not be immediately visible
due to the way HOT chains work. For more information, please see
<link to README.HOT>."
It might be nice to upgrade README.HOT to a real documentation page
as well.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200810051758
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAkjpOKkACgkQvJuQZxSWSshJ/QCgzeTdyP7tEZnYc+4GZZhyTPJ2
8IQAoJDmkbBDdGYlUoF7W3pcJSNvInW6
=aStx
-----END PGP SIGNATURE-----
Greg Sabino Mullane wrote:
Does it have pg_index.indcheckxmin = true? If so, see README.HOT.
Yes, that was probably it. Is this worth noting in the documentation somewhere
(other than the technical bowels of HOT)? Perhaps in the CREATE INDEX
docs?...
I have attached the scripts I used for testing. I don't think it is
worth documenting this until we have 8.4 released and people start using
it.I'm not following this, Bruce. HOT came out in 8.3, not 8.4, so why would we
wait on documenting the problem?I believe a mention in the manual would require quite complex wording.
Seems something would be better than nothing. Example:
"Note: In versions 8.3 and higher, indexes may not be immediately visible
due to the way HOT chains work. For more information, please see
<link to README.HOT>."
I think putting links in the user documentation directly to development
README files that require you to look in the source code is a bad thing...
It might be nice to upgrade README.HOT to a real documentation page
as well.
If we want the full explanation there, that's how it should be done :-)
//Magnus