Lists: | pgsql-bugspgsql-docs |
---|
From: | "Greg Sabino Mullane" <greg(at)endpoint(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #4410: Indexes not seen right away |
Date: | 2008-09-07 21:04:03 |
Message-ID: | 200809072104.m87L43IA061113@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-docs |
The following bug has been logged online:
Bug reference: 4410
Logged by: Greg Sabino Mullane
Email address: greg(at)endpoint(dot)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';
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Greg Sabino Mullane" <greg(at)endpoint(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #4410: Indexes not seen right away |
Date: | 2008-09-07 21:18:31 |
Message-ID: | 16773.1220822311@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-docs |
"Greg Sabino Mullane" <greg(at)endpoint(dot)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
From: | "Greg Sabino Mullane" <greg(at)turnstep(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #4410: Indexes not seen right away |
Date: | 2008-09-08 20:39:07 |
Message-ID: | b84a76e216d8b8d064d814d82610223b@biglumber.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-docs |
-----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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200809081638
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAkjFjVwACgkQvJuQZxSWSsglCACg18kkSFnwXYgf6LXxV/UC98Us
jU8An3zMxd58t3A5NemfbHJ++uANYkUu
=rHHT
-----END PGP SIGNATURE-----
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Greg Sabino Mullane <greg(at)turnstep(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #4410: Indexes not seen right away |
Date: | 2008-10-02 21:07:49 |
Message-ID: | 200810022107.m92L7nh25041@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-docs |
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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Attachment | Content-Type | Size |
---|---|---|
/rtmp/diff | text/x-diff | 5.5 KB |
From: | "Greg Sabino Mullane" <greg(at)turnstep(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Cc: | pgsql-docs(at)postgresql(dot)org |
Subject: | Re: BUG #4410: Indexes not seen right away |
Date: | 2008-10-05 21:59:48 |
Message-ID: | 7b98830a9fbba455fa905b3ca0ef8231@biglumber.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-docs |
-----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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200810051758
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAkjpOKkACgkQvJuQZxSWSshJ/QCgzeTdyP7tEZnYc+4GZZhyTPJ2
8IQAoJDmkbBDdGYlUoF7W3pcJSNvInW6
=aStx
-----END PGP SIGNATURE-----
From: | Magnus Hagander <magnus(at)hagander(dot)net> |
---|---|
To: | Greg Sabino Mullane <greg(at)turnstep(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org, pgsql-docs(at)postgresql(dot)org |
Subject: | Re: BUG #4410: Indexes not seen right away |
Date: | 2008-10-06 07:59:35 |
Message-ID: | 48E9C567.408@hagander.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-docs |
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