Re: BUG #4410: Indexes not seen right away - Mailing list pgsql-bugs

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. +
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