Subject: BUG #4410: Indexes not seen right away

From: "Greg Sabino Mullane" <[email-masked]>
Date: Sun, 7 Sep 2008 21:04:03 GMT

The following bug has been logged online:

Bug reference:      4410
Logged by:          Greg Sabino Mullane
Email address:      [email-masked]
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 <[email-masked]>
Date: Sun, 07 Sep 2008 17:18:31 -0400

"Greg Sabino Mullane" <[email-masked]> writes:
> start appearing.

Does it have pg_index.indcheckxmin = true?  If so, see README.HOT.

			regards, tom lane

---

From: "Greg Sabino Mullane" <[email-masked]>
Date: Mon, 8 Sep 2008 20:39:07 -0000

> 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 [email-masked]
PGP Key: 0x14964AC8 200809081638
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 2 Oct 2008 17:07:49 -0400 (EDT)

> (other than the technical bowels of HOT)? Perhaps in the CREATE INDEX

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  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
DROP TABLE test;
CREATE TABLE test (x int);
DROP SEQUENCE seq;
CREATE SEQUENCE seq;
INSERT INTO test SELECT nextval('seq') FROM generate_series(1, 1000);
BEGIN;
-- entable line below to create old snapshot that
-- makes index invisible in 8.4
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
EXPLAIN SELECT * FROM test WHERE x=3;
\echo run HOT.index in another session
UPDATE test SET x = x + 1 WHERE x >= 1000;
SELECT ctid, * FROM test WHERE x > 1000;
UPDATE test SET x = x + 1 WHERE x >= 1000;
SELECT ctid, * FROM test WHERE x > 1000;
UPDATE test SET x = x + 1 WHERE x >= 1000;
SELECT ctid, * FROM test WHERE x > 1000;
UPDATE test SET x = x + 1 WHERE x >= 1000;
SELECT ctid, * FROM test WHERE x > 1000;

CREATE INDEX i_test ON test(x);

EXPLAIN SELECT * FROM test WHERE x=3;
END;

Index: src/backend/access/heap/README.HOT
===================================================================
RCS file: /cvsroot/pgsql/src/backend/access/heap/README.HOT,v
retrieving revision 1.3
diff -c -c -r1.3 README.HOT
*** src/backend/access/heap/README.HOT	21 Mar 2008 13:23:27 -0000	1.3
--- src/backend/access/heap/README.HOT	2 Oct 2008 20:58:25 -0000
***************
*** 301,321 ****
  a "broken" chain that can't be indexed properly.
  
  To address this issue, regular (non-concurrent) CREATE INDEX makes the
! new index usable only by transactions newer than the CREATE INDEX
! command.  This prevents transactions that can see the inconsistent HOT
! chains from trying to use the new index and getting incorrect results.  
! New transactions can only see the rows visible after the index was
! created, hence the HOT chains are consistent for them.
  
  Entries in the new index point to root tuples (tuples with current index
  pointers) so that our index uses the same index pointers as all other
  indexes on the table.  However the row we want to index is actually at
  the *end* of the chain, ie, the most recent live tuple on the HOT chain.
  That is the one we compute the index entry values for, but the TID
! we put into the index is that of the root tuple.  Since transactions that
  will be allowed to use the new index cannot see any of the older tuple
  versions in the chain, the fact that they might not match the index entry
! isn't a problem.  (Such transactions will check the tuple visibility
  information of the older versions and ignore them, without ever looking at
  their contents, so the content inconsistency is OK.)  Subsequent updates
  to the live tuple will be allowed to extend the HOT chain only if they are
--- 301,322 ----
  a "broken" chain that can't be indexed properly.
  
  To address this issue, regular (non-concurrent) CREATE INDEX makes the
! new index usable only by new transactions and transactions that don't
! have snapshots older than the the CREATE INDEX command.  This prevents
! queries that can see the inconsistent HOT chains from trying to use the
! new index and getting incorrect results.  Queries that can see the index
! can only see the rows that were visible after the index was created,
! hence the HOT chains are consistent for them.
  
  Entries in the new index point to root tuples (tuples with current index
  pointers) so that our index uses the same index pointers as all other
  indexes on the table.  However the row we want to index is actually at
  the *end* of the chain, ie, the most recent live tuple on the HOT chain.
  That is the one we compute the index entry values for, but the TID
! we put into the index is that of the root tuple.  Since queries that
  will be allowed to use the new index cannot see any of the older tuple
  versions in the chain, the fact that they might not match the index entry
! isn't a problem.  (Such queries will check the tuple visibility
  information of the older versions and ignore them, without ever looking at
  their contents, so the content inconsistency is OK.)  Subsequent updates
  to the live tuple will be allowed to extend the HOT chain only if they are
***************
*** 331,351 ****
  transaction to commit or roll back.  (We could do that for user tables
  too, but since the case is unexpected we prefer to throw an error.)
  
! Practically, we prevent old transactions from using the new index by
! setting pg_index.indcheckxmin to TRUE.  Queries are allowed to use such an
! index only after pg_index.xmin is below their TransactionXmin horizon,
! thereby ensuring that any incompatible rows in HOT chains are dead to them.
! (pg_index.xmin will be the XID of the CREATE INDEX transaction.  The reason
! for using xmin rather than a normal column is that the regular vacuum
! freezing mechanism will take care of converting xmin to FrozenTransactionId
! before it can wrap around.)
  
  This means in particular that the transaction creating the index will be
! unable to use the index.  We alleviate that problem somewhat by not setting
! indcheckxmin unless the table actually contains HOT chains with
! RECENTLY_DEAD members.  (In 8.4 we may be able to improve the situation,
! at least for non-serializable transactions, because we expect to be able to
! advance TransactionXmin intratransaction.)
  
  Another unpleasant consequence is that it is now risky to use SnapshotAny
  in an index scan: if the index was created more recently than the last
--- 332,350 ----
  transaction to commit or roll back.  (We could do that for user tables
  too, but since the case is unexpected we prefer to throw an error.)
  
! Practically, we prevent certain transactions from using the new index by
! setting pg_index.indcheckxmin to TRUE.  Transactions are allowed to use
! such an index only after pg_index.xmin is below their TransactionXmin
! horizon, thereby ensuring that any incompatible rows in HOT chains are
! dead to them. (pg_index.xmin will be the XID of the CREATE INDEX
! transaction.  The reason for using xmin rather than a normal column is
! that the regular vacuum freezing mechanism will take care of converting
! xmin to FrozenTransactionId before it can wrap around.)
  
  This means in particular that the transaction creating the index will be
! unable to use the index if the transaction has old snapshots.  We
! alleviate that problem somewhat by not setting indcheckxmin unless the
! table actually contains HOT chains with RECENTLY_DEAD members.
  
  Another unpleasant consequence is that it is now risky to use SnapshotAny
  in an index scan: if the index was created more recently than the last

---

From: "Greg Sabino Mullane" <[email-masked]>
Date: Sun, 5 Oct 2008 21:59:48 -0000

>> (other than the technical bowels of HOT)? Perhaps in the CREATE INDEX
...
> 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 [email-masked]
PGP Key: 0x14964AC8 200810051758
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

---

From: Magnus Hagander <[email-masked]>
Date: Mon, 06 Oct 2008 09:59:35 +0200

> due to the way HOT chains work. For more information, please see

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

If we want the full explanation there, that's how it should be done :-)

//Magnus