Index trouble with 8.3b4

Hannes Dorbath <light@theendofthetunnel.de> writes:

hannes=> CREATE INDEX CONCURRENTLY "ts_fts_tsv" ON "public"."fts" USING gin ("tsv");
ERROR: item pointer (0,1) alreadt exists

I was able to reproduce that error a few times, but not always. It seems
it only happens with CONCURRENTLY.

After creating a GIST index instead of GIN I tried to cluster on that:

hannes=> CLUSTER fts USING ts_fts_tsv;
ERROR: could not create unique index "pk_fts"
DETAIL: Table contains duplicated values.
So duplicate values in my PK column?

I didn't have any luck reproducing either of these behaviors --- maybe
it's data-dependent. Can you extract a test case?

regards, tom lane

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

Hannes Dorbath <light@theendofthetunnel.de> writes:

hannes=> CREATE INDEX CONCURRENTLY "ts_fts_tsv" ON "public"."fts" USING gin ("tsv");
ERROR: item pointer (0,1) alreadt exists

I was able to reproduce that error a few times, but not always. It seems
it only happens with CONCURRENTLY.

...
I didn't have any luck reproducing either of these behaviors --- maybe
it's data-dependent. Can you extract a test case?

I the GIN problem kicks in with the "posting tree" representation. That would
require more than about 85 records with the same matching lexeme-key.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

I didn't have any luck reproducing either of these behaviors --- maybe
it's data-dependent. Can you extract a test case?

I haven't been able to reproduce this either but I produced an entirely
different problem:

postgres=# create index concurrently dg5 on doc using gin (to_tsvector('english',d));
ERROR: deadlock detected
DETAIL: Process 7076 waits for ShareLock on unrecognized locktag type 5; blocked by process 10497.
Process 10497 waits for ShareUpdateExclusiveLock on relation 24656 of database 11511; blocked by process 7076.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!

"Gregory Stark" <stark@enterprisedb.com> writes:

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

I didn't have any luck reproducing either of these behaviors --- maybe
it's data-dependent. Can you extract a test case?

I haven't been able to reproduce this either but I produced an entirely
different problem:

postgres=# create index concurrently dg5 on doc using gin (to_tsvector('english',d));
ERROR: deadlock detected
DETAIL: Process 7076 waits for ShareLock on unrecognized locktag type 5; blocked by process 10497.
Process 10497 waits for ShareUpdateExclusiveLock on relation 24656 of database 11511; blocked by process 7076.

Further poking around shows that the "unrecognized locktag" is because
lmgr.c:DescribeLockTag was never taught about virtual xids. Ie something like
this (untested):

--- lmgr.c	04 Jan 2008 15:12:37 +0000	1.95
+++ lmgr.c	07 Jan 2008 15:54:49 +0000	
@@ -739,6 +739,12 @@
 							 tag->locktag_field2,
 							 tag->locktag_field1);
 			break;
+		case LOCKTAG_VIRTUALTRANSACTION:
+			appendStringInfo(buf,
+							 _("virtual transaction %d/%u"),
+							 tag->locktag_field1,
+							 tag->locktag_field2);
+			break;
 		case LOCKTAG_TRANSACTION:
 			appendStringInfo(buf,
 							 _("transaction %u"),

The pid it's waiting on is long since gone but looks like it was probably an
autovacuum process. I have a vague recollection that you had rigged CREATE
INDEX CONCURRENTLY to ignore vacuum processes when checking for conflicting
processes. Since any such process will be blocked on our session-level
ShareUpdateExclusiveLock it will always cause a deadlock and we would rather
it just hang out and wait until our index build is finished.

On the other hand we can't just ignore all vacuums because someone could issue
a manual vacuum inside a transaction (I think?). But this is a general problem
with all the places where we check if another transaction is just running
vacuum, such as checking for globalxmin. We should only be ignoring
transactions which were started just to execute a vacuum.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

"Gregory Stark" <stark@enterprisedb.com> writes:

On the other hand we can't just ignore all vacuums because someone could issue
a manual vacuum inside a transaction (I think?).

Doh, ignore this. sigh.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

Tom Lane wrote:

I didn't have any luck reproducing either of these behaviors --- maybe
it's data-dependent. Can you extract a test case?

I will try to come up with a test case, but it might take until next
weekend, sorry.

The test data was an IRC log file, containing lots of color and
formating codes as well as other garbage. So anything else than a well
formed text document. I cleaned it a bit with -f UTF-8 -t UTF-8 -c to
make it import at all.

--
Best regards,
Hannes Dorbath

Gregory Stark <stark@enterprisedb.com> writes:

Further poking around shows that the "unrecognized locktag" is because
lmgr.c:DescribeLockTag was never taught about virtual xids.

That's fixed, thanks for the patch.

The pid it's waiting on is long since gone but looks like it was probably an
autovacuum process. I have a vague recollection that you had rigged CREATE
INDEX CONCURRENTLY to ignore vacuum processes when checking for conflicting
processes.

I'm still not too clear on the underlying bug though.

regards, tom lane

Gregory Stark <stark@enterprisedb.com> writes:

The pid it's waiting on is long since gone but looks like it was probably an
autovacuum process. I have a vague recollection that you had rigged CREATE
INDEX CONCURRENTLY to ignore vacuum processes when checking for conflicting
processes. Since any such process will be blocked on our session-level
ShareUpdateExclusiveLock it will always cause a deadlock and we would rather
it just hang out and wait until our index build is finished.

OK, after reading the code some more I think I've got the point. The
scenario is that autovacuum is waiting to get ShareUpdateExclusiveLock
(it can't already have it, because the CREATE INDEX CONCURRENTLY does)
and then one of C.I.C's three wait steps decides it has to wait for the
autovacuum. It cannot be one of the first two, because those only block
for xacts that *already have* a conflicting lock. The problem must be
at the third wait step, which waits out all xacts that might conceivably
be interested in recently-dead tuples that are not in the index.

Now an unindexed dead tuple is not a problem from vacuum's point of
view, nor does ANALYZE care, so AFAICS there is no need for this step
to wait for autovacuum processes --- nor indeed for manual vacuums.
So we can avoid the deadlock if we just exclude those processes from
the list of ones to wait for.

I suggest we extend GetCurrentVirtualXIDs() with an additional
parameter includeVacuums, and have it skip vacuum procs if that's
set. (Hmm, maybe a more flexible approach is to make the parameter
a bitmask, and ignore any procs for which param & vacuumFlags is
not zero.)

Comments?

regards, tom lane

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

It cannot be one of the first two, because those only block
for xacts that *already have* a conflicting lock. The problem must be
at the third wait step, which waits out all xacts that might conceivably
be interested in recently-dead tuples that are not in the index.

Ah, I had missed that point.

Now an unindexed dead tuple is not a problem from vacuum's point of
view, nor does ANALYZE care, so AFAICS there is no need for this step
to wait for autovacuum processes --- nor indeed for manual vacuums.
So we can avoid the deadlock if we just exclude those processes from
the list of ones to wait for.

That's what I had in mind.

I suggest we extend GetCurrentVirtualXIDs() with an additional
parameter includeVacuums, and have it skip vacuum procs if that's
set. (Hmm, maybe a more flexible approach is to make the parameter
a bitmask, and ignore any procs for which param & vacuumFlags is
not zero.)

Comments?

Only that the restrictions on what VACUUM is allowed to do seem the piling up.
We may have to write up a separate document explaining what specialized set of
rules VACUUM operates under.

Also, ANALYZE was included in the latest security changes. Is there some way
that ANALYZE could trigger some user-defined function being invoked which
could in turn run some SQL using this index? I suppose a very strange
expression index where the expression involved a recursive SQL query back to
the same table (presumably being careful to avoid an infinite loop) could be
possible.

I am hoping our other things which ignore VACUUM such as the globalxmin
calculation are careful not to ignore VACUUM ANALYZE processes?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

Gregory Stark <stark@enterprisedb.com> writes:

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

Now an unindexed dead tuple is not a problem from vacuum's point of
view, nor does ANALYZE care, so AFAICS there is no need for this step
to wait for autovacuum processes --- nor indeed for manual vacuums.

Also, ANALYZE was included in the latest security changes. Is there some way
that ANALYZE could trigger some user-defined function being invoked which
could in turn run some SQL using this index?

Hmm. ANALYZE itself doesn't look into the indexes, but it does invoke
user-defined functions that could nominally run queries. However, a
function in an index that runs a query that examines its own table seems
implausible, and very unlikely to work right anyway. You could hardly
expect such a function to be really immutable -- consider for example
that it would be unlikely to deliver the same results during CREATE
INDEX on an already-filled table that it would if the rows were being
inserted with the index already existing. So I'm not really worried
about that scenario.

regards, tom lane

Gregory Stark wrote:

I am hoping our other things which ignore VACUUM such as the globalxmin
calculation are careful not to ignore VACUUM ANALYZE processes?

It doesn't matter -- the ANALYZE is done in a separate transaction (so
the VACUUM part is ignored, the ANALYZE part is not).

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Gregory Stark wrote:

"Gregory Stark" <stark@enterprisedb.com> writes:

On the other hand we can't just ignore all vacuums because someone could issue
a manual vacuum inside a transaction (I think?).

Doh, ignore this. sigh.

I started from scratch to put up a test case. I cannot trigger "ERROR:
item pointer (0,1) already exists" again as the deadlock issue reported
by Gregory Stark hit me every time. Is this fixed in RC1? Can I retry
with that?

Only good news is that I think I found the CLUSTER issue:

It was no GIST index I created, I accidentally created a BTREE index:
http://theendofthetunnel.de/cluster.txt

If it does help anything, the data and setup:

8.3-b4 build from source

./configure --prefix=/usr/local --enable-thread-safety --with-perl
--with-openssl --with-libxml --with-libxslt

initdb line:
initdb -D /data/pgsql --locale='de_DE.utf8' --lc-collate='C'

Only listen_address and pg_hba.conf was touched.

Please get the -Fc dump (37MB) from:

http://theendofthetunnel.de/dump.bin
http://theendofthetunnel.de/glob.sql

--
Best regards,
Hannes Dorbath

On Jan 13, 2008 7:50 PM, Hannes Dorbath <light@theendofthetunnel.de> wrote:

I started from scratch to put up a test case. I cannot trigger "ERROR:
item pointer (0,1) already exists" again as the deadlock issue reported
by Gregory Stark hit me every time. Is this fixed in RC1? Can I retry
with that?

No, it's not fixed in RC1. You have to compile CVS HEAD to have it fixed.

--
Guillaume

Guillaume Smet wrote:

On Jan 13, 2008 7:50 PM, Hannes Dorbath <light@theendofthetunnel.de> wrote:

I started from scratch to put up a test case. I cannot trigger "ERROR:
item pointer (0,1) already exists" again as the deadlock issue reported
by Gregory Stark hit me every time. Is this fixed in RC1? Can I retry
with that?

No, it's not fixed in RC1. You have to compile CVS HEAD to have it fixed.

OK, the deadlock is gone. I can only provoke it when issuing the create
index statement from 2 terminals at the same time. But I think this is
intended. I keep trying to catch the gin error though.

--
Best regards,
Hannes Dorbath

Hannes Dorbath wrote:

Guillaume Smet wrote:

On Jan 13, 2008 7:50 PM, Hannes Dorbath <light@theendofthetunnel.de>
wrote:

I started from scratch to put up a test case. I cannot trigger "ERROR:
item pointer (0,1) already exists" again as the deadlock issue reported
by Gregory Stark hit me every time. Is this fixed in RC1? Can I retry
with that?

No, it's not fixed in RC1. You have to compile CVS HEAD to have it fixed.

OK, the deadlock is gone. I can only provoke it when issuing the create
index statement from 2 terminals at the same time. But I think this is
intended. I keep trying to catch the gin error though.

Well, or maybe not really intended that way. Both terminals error out with:

ERROR: relation "ts_test_tsv" already exists

But the index was created.

ERROR: relation "ts_test_tsv" already exists
test=# drop INDEX ts_test_tsv ;
DROP INDEX

--
Best regards,
Hannes Dorbath

Hannes Dorbath wrote:

ERROR: relation "ts_test_tsv" already exists
test=# drop INDEX ts_test_tsv ;
DROP INDEX

This is a general thing I'd like to ask. If the creation of an index
fails, why is it nevertheless there? No matter if deadlock or my GIN
error, why isn't the whole operation "rolled back"? And what state is it
it leaves me on? Do I end up with a corrupt index on my table?

--
Best regards,
Hannes Dorbath

Hannes Dorbath <light@theendofthetunnel.de> writes:

This is a general thing I'd like to ask. If the creation of an index
fails, why is it nevertheless there?

It's a rather ugly consequence of the fact that CREATE INDEX
CONCURRENTLY requires more than one transaction. If the later ones
fail, the invalid index is still there.

It'd be nice to clean that up sometime, but don't hold your breath.

regards, tom lane

Tom Lane wrote:

Hannes Dorbath <light@theendofthetunnel.de> writes:

This is a general thing I'd like to ask. If the creation of an index
fails, why is it nevertheless there?

It's a rather ugly consequence of the fact that CREATE INDEX
CONCURRENTLY requires more than one transaction. If the later ones
fail, the invalid index is still there.

It'd be nice to clean that up sometime, but don't hold your breath.

OK, I have my GIN failure back with CSV-HEAD:

test=# UPDATE test SET tsv = to_tsvector(text);
UPDATE 753100
test=# CREATE INDEX CONCURRENTLY "ts_test_tsv" ON "public"."test" USING
gin ("tsv");
ERROR: item pointer (8,23) already exists
test=# drop INDEX ts_test_tsv ;
DROP INDEX
test=# CREATE INDEX CONCURRENTLY "ts_test_tsv" ON "public"."test" USING
gin ("tsv");
CREATE INDEX
test=#

I have a hard time to pin it down. Currently all I can say is: It
happens the first time after I bulk load data into that table.

I cannot catch it with pg_dump -- after a restore it works. I can
reproduce it here more or less reliable. Maybe I should just bzip
$PGDATA and send it.

--
Best regards,
Hannes Dorbath

Hannes Dorbath wrote:

Currently all I can say is: It happens the first time after I bulk load data into that table.

I have the bad feeling that I need to correct this into "It happens when
autovacuum is active on the table".

Is it by any chance possible that CREATE INDEX CONCURRENTLY might read
dirt while autovacuum is busy with the table?

--
Best regards,
Hannes Dorbath