On Sat, Nov 13, 2010 at 10:41 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Jon Nelson <jnelson+pgsql@xxxxxxxxxxx> writes: >> OK. This is a highly distilled example that shows the behavior. > >> BEGIN; >> CREATE TEMPORARY TABLE foo AS SELECT x AS A, chr(x % 75 + 32) AS b, >> ''::text AS c from generate_series(1,500) AS x; >> UPDATE foo SET c = 'foo' WHERE b = 'A' ; >> CREATE INDEX foo_b_idx on foo (b); >> [ and the rest of the transaction can't use that index ] > > OK, this is an artifact of the "HOT update" optimization. Before > creating the index, you did updates on the table that would have been > executed differently if the index had existed. When the index does get > created, its entries for those updates are incomplete, so the index > can't be used in transactions that could in principle see the unmodified > rows. Is the "in principle" here because there might be an open snapshot other than the one under which CREATE INDEX is running, like a cursor? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
