On 2014-06-03 15:08:15 -0400, Tom Lane wrote:
> Andres Freund <and...@2ndquadrant.com> writes:
> > In 9.4. COSTS OFF for EXPLAIN prevents 'Planning time' to be
> > printed. Should we perhaps do the same for 'Execution time'? That'd make
> > it possible to use EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) in
> > regression tests.
>
> > Currently the output for that is:
> > postgres=# EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT 1;
> > QUERY PLAN
> > --------------------------------
> > Result (actual rows=1 loops=1)
> > Total runtime: 0.035 ms
> > (2 rows)
>
> > Leaving off the total runtime doesn't seem bad to me.
>
> It seems a little weird to call it a "cost" ... but maybe that
> ship has sailed given how we're treating the planning-time item.
>
> I'm unconvinced that this'd add much to our regression testing capability,
> though. The standard thing is to do an EXPLAIN to check the plan shape
> and then run the query to see if it gets the right answer. Checking row
> counts is pretty well subsumed by the latter, and is certainly not an
> adequate substitute for it.
>
> So on the whole, -1 ... this is an unintuitive and
> non-backwards-compatible change that doesn't look like it buys much.
I've added the regression test I want this for.
0001 is the bugfix making me look into it
0002 is COSTS OFF removing the display of execution time
0003 is the regression test
Note that 0003 will require a kill -9 without 0001.
I am not sure myself if the test is really worth it. On one hand it's an
area that had seen several hard to find bugs over the years and is
likely to see further changes (e.g. CSN stuff) in the near future, on
the other hand the tests are tricky and require specific ordering.
Opinions?
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
>From 621a99a666ba1a27b852dc5ddc0e1b224c388f53 Mon Sep 17 00:00:00 2001
From: Andres Freund <and...@anarazel.de>
Date: Wed, 4 Jun 2014 21:36:19 +0200
Subject: [PATCH 1/3] Fix longstanding bug in HeapTupleSatisfiesVacuum().
HeapTupleSatisfiesVacuum() didn't properly discern between
DELETE_IN_PROGRESS and INSERT_IN_PROGRESS for rows that have been
inserted in the current transaction and deleted in a aborted
subtransaction of the current backend. At the very least that caused
problems for CLUSTER and CREATE INDEX in transactions that had
aborting subtransactions producing rows, leading to warnings like:
WARNING: concurrent delete in progress within table "..."
possibly in an endless, uninterruptible, loop.
Instead of treating *InProgress xmins the same as *IsCurrent ones,
treat them as being distinct like the other visibility routines. As
implemented this separatation can cause a behaviour change for rows
that have been inserted and deleted in another, still running,
transaction. HTSV will now return INSERT_IN_PROGRESS instead of
DELETE_IN_PROGRESS for those. That's both, more in line with the other
visibility routines and arguably more correct. The latter because a
INSERT_IN_PROGRESS will make callers look at/wait for xmin, instead of
xmax.
The only current caller where that's possibly worse than the old
behaviour is heap_prune_chain() which now won't mark the page as
prunable if a row has concurrently been inserted and deleted. That's
harmless enough.
As a cautionary measure also insert a interrupt check before the gotos
in IndexBuildHeapScan() that lead to the uninterruptible loop. There
are other possible causes, like a row that several sessions try to
update and all fail, for repeated loops and the cost of doing so in
the retry case is low.
As this bug goes back all the way to the introduction of
subtransactions in 573a71a5da backpatch to all supported releases.
Reported-By: Sandro Santilli
---
src/backend/catalog/index.c | 2 ++
src/backend/utils/time/tqual.c | 19 +++++++++++++++++--
2 files changed, 19 insertions(+), 2 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 80acc0e..a5a204e 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2298,6 +2298,7 @@ IndexBuildHeapScan(Relation heapRelation,
XactLockTableWait(xwait, heapRelation,
&heapTuple->t_data->t_ctid,
XLTW_InsertIndexUnique);
+ CHECK_FOR_INTERRUPTS();
goto recheck;
}
}
@@ -2346,6 +2347,7 @@ IndexBuildHeapScan(Relation heapRelation,
XactLockTableWait(xwait, heapRelation,
&heapTuple->t_data->t_ctid,
XLTW_InsertIndexUnique);
+ CHECK_FOR_INTERRUPTS();
goto recheck;
}
diff --git a/src/backend/utils/time/tqual.c b/src/backend/utils/time/tqual.c
index 75cd53e..96874ab 100644
--- a/src/backend/utils/time/tqual.c
+++ b/src/backend/utils/time/tqual.c
@@ -1166,7 +1166,7 @@ HeapTupleSatisfiesVacuum(HeapTuple htup, TransactionId OldestXmin,
return HEAPTUPLE_DEAD;
}
}
- else if (TransactionIdIsInProgress(HeapTupleHeaderGetRawXmin(tuple)))
+ else if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetRawXmin(tuple)))
{
if (tuple->t_infomask & HEAP_XMAX_INVALID) /* xid invalid */
return HEAPTUPLE_INSERT_IN_PROGRESS;
@@ -1175,7 +1175,22 @@ HeapTupleSatisfiesVacuum(HeapTuple htup, TransactionId OldestXmin,
HeapTupleHeaderIsOnlyLocked(tuple))
return HEAPTUPLE_INSERT_IN_PROGRESS;
/* inserted and then deleted by same xact */
- return HEAPTUPLE_DELETE_IN_PROGRESS;
+ if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetUpdateXid(tuple)))
+ return HEAPTUPLE_DELETE_IN_PROGRESS;
+ /* deleting subtransaction must have aborted */
+ return HEAPTUPLE_INSERT_IN_PROGRESS;
+ }
+ else if (TransactionIdIsInProgress(HeapTupleHeaderGetRawXmin(tuple)))
+ {
+ /*
+ * It'd be possible to discern between INSERT/DELETE in progress
+ * here by looking at xmax - but that doesn't seem beneficial for
+ * the majority of callers and even detrimental for some. We'd
+ * rather have callers look at/wait for xmin than xmax. It's
+ * always correct to return INSERT_IN_PROGRESS because that's
+ * what's happening from the view of other backends.
+ */
+ return HEAPTUPLE_INSERT_IN_PROGRESS;
}
else if (TransactionIdDidCommit(HeapTupleHeaderGetRawXmin(tuple)))
SetHintBits(tuple, buffer, HEAP_XMIN_COMMITTED,
--
2.0.0.rc2.4.g1dc51c6.dirty
>From 093af92ea74dc3a8c3a3eb1f323b0c9508816a71 Mon Sep 17 00:00:00 2001
From: Andres Freund <and...@anarazel.de>
Date: Wed, 4 Jun 2014 21:36:19 +0200
Subject: [PATCH 2/3] Don't print the execution time for EXPLAIN (ANALYZE,
COSTS OFF).
The primary reason to use COSTS OFF for EXPLAIN is to get reproducable
output. Until now EXPLAIN with ANALYZE didn't generate reproducable
output, because it printed the execution time even with TIMING
OFF. Strangely sounding as it may be that actually makes sense because
TIMING OFF is there to reduce the timing overhead, not to produce
reproducable output.
---
src/backend/commands/explain.c | 4 +++-
1 file changed, 3 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 0d9663c..b2c39f5 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -492,6 +492,7 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
/* Create textual dump of plan tree */
ExplainPrintPlan(es, queryDesc);
+ /* COSTS OFF is used for regression tests - don't print the plan time */
if (es->costs && planduration)
{
double plantime = INSTR_TIME_GET_DOUBLE(*planduration);
@@ -525,7 +526,8 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
totaltime += elapsed_time(&starttime);
- if (es->analyze)
+ /* COSTS OFF is used for regression tests - don't print the execution time */
+ if (es->costs && es->analyze)
{
if (es->format == EXPLAIN_FORMAT_TEXT)
appendStringInfo(es->str, "Execution time: %.3f ms\n",
--
2.0.0.rc2.4.g1dc51c6.dirty
>From 7f04ac6255360f21dcba0323630fbb4b85bb6cab Mon Sep 17 00:00:00 2001
From: Andres Freund <and...@anarazel.de>
Date: Wed, 4 Jun 2014 21:36:19 +0200
Subject: [PATCH 3/3] Add tests for interaction between visibility and CREATE
INDEX's heap scan.
There have been several bugs over the years in the interaction between
HeapTupleSatisfiesVacuum() and CREATE INDEX. It's also looking like
there will be some changes to the visiblity routines expected during
the 9.5 cycle.
Those facts together seem to warrant a couple of tests, even if they
aren't exactly pretty.
---
src/test/regress/expected/create_index.out | 198 +++++++++++++++++++++++++++++
src/test/regress/sql/create_index.sql | 65 ++++++++++
2 files changed, 263 insertions(+)
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index f6f5516..b9cd3d8 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2782,3 +2782,201 @@ explain (costs off)
Index Cond: ((thousand = 1) AND (tenthous = 1001))
(2 rows)
+----
+-- Check interactions of index creation in a xact that also has done
+-- insertions, updates and deletions. There've been several bugs
+-- around that.
+----
+CREATE TABLE sametrans1(a text, b int default 1);
+INSERT INTO sametrans1(a) VALUES ('ins-before-xact');
+INSERT INTO sametrans1(a) VALUES ('ins-before-xact-del');
+INSERT INTO sametrans1(a) VALUES ('ins-before-xact-del-after-index');
+INSERT INTO sametrans1(a) VALUES ('ins-before-xact-upd-main');
+INSERT INTO sametrans1(a) VALUES ('ins-before-xact-upd-sub-abort');
+INSERT INTO sametrans1(a) VALUES ('ins-before-xact-upd-sub');
+BEGIN;
+INSERT INTO sametrans1(a) VALUES ('ins-mainxact');
+INSERT INTO sametrans1(a) VALUES ('ins-mainxact-subdel-abort');
+INSERT INTO sametrans1(a) VALUES ('ins-mainxact-subdel');
+INSERT INTO sametrans1(a) VALUES ('ins-mainxact-upd-sub-abort');
+INSERT INTO sametrans1(a) VALUES ('ins-mainxact-upd-sub');
+DELETE FROM sametrans1 WHERE a = 'ins-before-xact-del';
+UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-before-xact-upd-main';
+SAVEPOINT a;
+INSERT INTO sametrans1(a) VALUES ('ins-sub');
+INSERT INTO sametrans1(a) VALUES ('ins-sub-del-mainxact');
+DELETE FROM sametrans1 WHERE a = 'ins-mainxact-subdel';
+UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-before-xact-upd-sub';
+UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-mainxact-upd-sub';
+RELEASE SAVEPOINT a;
+SAVEPOINT b;
+INSERT INTO sametrans1(a) VALUES ('ins-sub-abort');
+DELETE FROM sametrans1 WHERE a = 'ins-mainxact-subdel-abort';
+UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-before-xact-upd-sub-abort';
+UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-mainxact-upd-sub-abort';
+ROLLBACK TO b;
+-- don't add anything here, page pruning might otherwise remove rows
+CREATE UNIQUE INDEX ON sametrans1 (a);
+CREATE INDEX ON sametrans1 (b);
+DELETE FROM sametrans1 WHERE a = 'ins-sub-del-mainxact';
+DELETE FROM sametrans1 WHERE a = 'ins-before-xact-del-after-index';
+COMMIT;
+INSERT INTO sametrans1(a) VALUES ('ins-after-xact');
+-- force bitmapscans, they show the indexscans separately from the heap scan
+SET enable_seqscan = off;
+SET enable_indexscan = off;
+SET enable_indexonlyscan = off;
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1)
+ Recheck Cond: (a = 'ins-before-xact'::text)
+ Heap Blocks: exact=1
+ -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1)
+ Index Cond: (a = 'ins-before-xact'::text)
+(5 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-del';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Bitmap Heap Scan on sametrans1 (actual rows=0 loops=1)
+ Recheck Cond: (a = 'ins-before-xact-del'::text)
+ Heap Blocks: exact=1
+ -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1)
+ Index Cond: (a = 'ins-before-xact-del'::text)
+(5 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-del-after-index';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Bitmap Heap Scan on sametrans1 (actual rows=0 loops=1)
+ Recheck Cond: (a = 'ins-before-xact-del-after-index'::text)
+ Heap Blocks: exact=1
+ -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1)
+ Index Cond: (a = 'ins-before-xact-del-after-index'::text)
+(5 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-upd-main';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1)
+ Recheck Cond: (a = 'ins-before-xact-upd-main'::text)
+ Heap Blocks: exact=1
+ -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1)
+ Index Cond: (a = 'ins-before-xact-upd-main'::text)
+(5 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-upd-sub-abort';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1)
+ Recheck Cond: (a = 'ins-before-xact-upd-sub-abort'::text)
+ Heap Blocks: exact=1
+ -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1)
+ Index Cond: (a = 'ins-before-xact-upd-sub-abort'::text)
+(5 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-upd-sub';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1)
+ Recheck Cond: (a = 'ins-before-xact-upd-sub'::text)
+ Heap Blocks: exact=1
+ -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1)
+ Index Cond: (a = 'ins-before-xact-upd-sub'::text)
+(5 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1)
+ Recheck Cond: (a = 'ins-mainxact'::text)
+ Heap Blocks: exact=1
+ -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1)
+ Index Cond: (a = 'ins-mainxact'::text)
+(5 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-subdel-abort';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1)
+ Recheck Cond: (a = 'ins-mainxact-subdel-abort'::text)
+ Heap Blocks: exact=1
+ -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1)
+ Index Cond: (a = 'ins-mainxact-subdel-abort'::text)
+(5 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-subdel';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Bitmap Heap Scan on sametrans1 (actual rows=0 loops=1)
+ Recheck Cond: (a = 'ins-mainxact-subdel'::text)
+ Heap Blocks: exact=1
+ -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1)
+ Index Cond: (a = 'ins-mainxact-subdel'::text)
+(5 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-upd-sub-abort';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1)
+ Recheck Cond: (a = 'ins-mainxact-upd-sub-abort'::text)
+ Heap Blocks: exact=1
+ -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1)
+ Index Cond: (a = 'ins-mainxact-upd-sub-abort'::text)
+(5 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-upd-sub';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1)
+ Recheck Cond: (a = 'ins-mainxact-upd-sub'::text)
+ Heap Blocks: exact=1
+ -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1)
+ Index Cond: (a = 'ins-mainxact-upd-sub'::text)
+(5 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-sub';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1)
+ Recheck Cond: (a = 'ins-sub'::text)
+ Heap Blocks: exact=1
+ -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1)
+ Index Cond: (a = 'ins-sub'::text)
+(5 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-sub-del-mainxact';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Bitmap Heap Scan on sametrans1 (actual rows=0 loops=1)
+ Recheck Cond: (a = 'ins-sub-del-mainxact'::text)
+ Heap Blocks: exact=1
+ -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1)
+ Index Cond: (a = 'ins-sub-del-mainxact'::text)
+(5 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-sub-abort';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Bitmap Heap Scan on sametrans1 (actual rows=0 loops=1)
+ Recheck Cond: (a = 'ins-sub-abort'::text)
+ Heap Blocks:
+ -> Bitmap Index Scan on sametrans1_a_idx (actual rows=0 loops=1)
+ Index Cond: (a = 'ins-sub-abort'::text)
+(5 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-after-xact';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1)
+ Recheck Cond: (a = 'ins-after-xact'::text)
+ Heap Blocks: exact=1
+ -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1)
+ Index Cond: (a = 'ins-after-xact'::text)
+(5 rows)
+
+SET enable_seqscan = on;
+SET enable_indexscan = on;
+SET enable_indexonlyscan = on;
+DROP TABLE sametrans1;
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index d4d24ef..1fae3e9 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -938,3 +938,68 @@ ORDER BY thousand;
explain (costs off)
select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null));
+
+----
+-- Check interactions of index creation in a xact that also has done
+-- insertions, updates and deletions. There've been several bugs
+-- around that.
+----
+CREATE TABLE sametrans1(a text, b int default 1);
+
+INSERT INTO sametrans1(a) VALUES ('ins-before-xact');
+INSERT INTO sametrans1(a) VALUES ('ins-before-xact-del');
+INSERT INTO sametrans1(a) VALUES ('ins-before-xact-del-after-index');
+INSERT INTO sametrans1(a) VALUES ('ins-before-xact-upd-main');
+INSERT INTO sametrans1(a) VALUES ('ins-before-xact-upd-sub-abort');
+INSERT INTO sametrans1(a) VALUES ('ins-before-xact-upd-sub');
+BEGIN;
+INSERT INTO sametrans1(a) VALUES ('ins-mainxact');
+INSERT INTO sametrans1(a) VALUES ('ins-mainxact-subdel-abort');
+INSERT INTO sametrans1(a) VALUES ('ins-mainxact-subdel');
+INSERT INTO sametrans1(a) VALUES ('ins-mainxact-upd-sub-abort');
+INSERT INTO sametrans1(a) VALUES ('ins-mainxact-upd-sub');
+DELETE FROM sametrans1 WHERE a = 'ins-before-xact-del';
+UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-before-xact-upd-main';
+SAVEPOINT a;
+INSERT INTO sametrans1(a) VALUES ('ins-sub');
+INSERT INTO sametrans1(a) VALUES ('ins-sub-del-mainxact');
+DELETE FROM sametrans1 WHERE a = 'ins-mainxact-subdel';
+UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-before-xact-upd-sub';
+UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-mainxact-upd-sub';
+RELEASE SAVEPOINT a;
+SAVEPOINT b;
+INSERT INTO sametrans1(a) VALUES ('ins-sub-abort');
+DELETE FROM sametrans1 WHERE a = 'ins-mainxact-subdel-abort';
+UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-before-xact-upd-sub-abort';
+UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-mainxact-upd-sub-abort';
+ROLLBACK TO b;
+-- don't add anything here, page pruning might otherwise remove rows
+CREATE UNIQUE INDEX ON sametrans1 (a);
+CREATE INDEX ON sametrans1 (b);
+DELETE FROM sametrans1 WHERE a = 'ins-sub-del-mainxact';
+DELETE FROM sametrans1 WHERE a = 'ins-before-xact-del-after-index';
+COMMIT;
+INSERT INTO sametrans1(a) VALUES ('ins-after-xact');
+-- force bitmapscans, they show the indexscans separately from the heap scan
+SET enable_seqscan = off;
+SET enable_indexscan = off;
+SET enable_indexonlyscan = off;
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact';
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-del';
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-del-after-index';
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-upd-main';
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-upd-sub-abort';
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-upd-sub';
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact';
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-subdel-abort';
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-subdel';
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-upd-sub-abort';
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-upd-sub';
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-sub';
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-sub-del-mainxact';
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-sub-abort';
+EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-after-xact';
+SET enable_seqscan = on;
+SET enable_indexscan = on;
+SET enable_indexonlyscan = on;
+DROP TABLE sametrans1;
--
2.0.0.rc2.4.g1dc51c6.dirty
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
