pg_dump and ON DELETE CASCADE problem

On 10/12/2009 3:31 AM, CG wrote:

Hi all,
We're using PostgreSQL 8.4 ... We do our nightly database backups with
pg_dump. I was doing a test restore and I encountered some data during
the reload that was in a table against the conditions of a foreign key
constraint. I run my restores with the "-e" option to halt on errors, so
this data halted the restore... I went to check the running database and
the row in question had been deleted.

I had defined the foreign key to cascade on delete, and I imagine that
during the dump the delete occurred on the master table. Perhaps the
keyed table had already been dumped so when it came time to dump the
master table, the referencing row was not there to be dumped.

pg_dump does all its work in a single serializable transaction to avoid
this sort of problem. It doesn't see any changes made to the database
after it starts. So, assuming you used pg_dump to dump the database as a
whole rather than invoking it separately for a bunch of separate tables,
that should not be your problem.

How do you run pg_dump? Can you supply the script or command line?

One would
imagine that PostgreSQL would have protections for that sort of thing...

It does, which is what makes the issue you've encountered somewhat strange.

--
Craig Ringer

The command's nothing out-of-the-ordinary:
 
#!/bin/bash

export LD_LIBRARY_PATH=/usr/local/pgsql/lib
 
#####################################################################
# Set Variables
#####################################################################
DAY_NUM=`/bin/date +"%d"`
MON_NUM=`/bin/date +"%m"`
YEAR_NUM=`/bin/date +"%Y"`

/usr/local/pgsql/bin/pg_dump -h 192.168.1.5 -Upostgres -f backup.$YEAR_NUM$MON_NUM$DAY_NUM.pga -Fc -b data
 
#END
 
Curiouser and curiouser... Last night's dump failed to restore in the same way:
 
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 7545; 2606 311883439 FK CONSTRAINT packet_search_trigram_puuid_fkey postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  insert or update on table "packet_search_trigram" violates foreign key constraint "packet_search_trigram_puuid_fkey"
DETAIL:  Key (packet_uuid)=(0ab44da9-544d-413a-9ab1-a1b442310b24) is not present in table "packet".
    Command was:
ALTER TABLE ONLY packet_search_trigram
    ADD CONSTRAINT packet_search_trigram_puuid_fkey FOREIGN KEY (packet_uuid) REFERE...
pg_restore: *** aborted because of error
pg_restore: finished item 7545 FK CONSTRAINT packet_search_trigram_puuid_fkey
pg_restore: [archiver] worker process failed: exit code 1
pg_restore: *** aborted because of error

That was the same failure I got the previous night. I go to the live database and rows with that key are /not/ in either one of those tables. They /were/ in the tables at one point. I have an ON DELETE trigger that copies deleted rows into another table, so I can see that a row with that key once existed in those tables.
 
This may not be a pg_dump problem, but some sort of MVCC irregularity where pg_dump is able to dump rows that it shouldn't. I bet a VACUUM FULL would clean this up, but I have a live problem here. If I eradicate it, who knows when we'll see it again...
 

--- On Wed, 12/9/09, Craig Ringer <craig@postnewspapers.com.au> wrote:

From: Craig Ringer <craig@postnewspapers.com.au>
Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
To: cgg007@yahoo.com
Cc: "postgresql listserv" <pgsql-general@postgresql.org>
Date: Wednesday, December 9, 2009, 9:02 PM

On 10/12/2009 3:31 AM, CG wrote:

Hi all,
We're using PostgreSQL 8.4 ... We do our nightly database backups with
pg_dump. I was doing a test restore and I encountered some data during
the reload that was in a table against the conditions of a foreign key
constraint. I run my restores with the "-e" option to halt on errors, so
this data halted the restore... I went to check the running database and
the row in question had been deleted.

I had defined the foreign key to cascade on delete, and I imagine that
during the dump the delete occurred on the master table. Perhaps the
keyed table had already been dumped so when it came time to dump the
master table, the referencing row was not there to be dumped.

pg_dump does all its work in a single serializable transaction to avoid this sort of problem. It doesn't see any changes made to the database after it starts. So, assuming you used pg_dump to dump the database as a whole rather than invoking it separately for a bunch of separate tables, that should not be your problem.

How do you run pg_dump? Can you supply the script or command line?

One would
imagine that PostgreSQL would have protections for that sort of thing...

It does, which is what makes the issue you've encountered somewhat strange.

--
Craig Ringer

On Thursday 10 December 2009 7:27:54 am CG wrote:

The command's nothing out-of-the-ordinary:
 
#!/bin/bash

export LD_LIBRARY_PATH=/usr/local/pgsql/lib
 
#####################################################################
# Set Variables
#####################################################################
DAY_NUM=`/bin/date +"%d"`
MON_NUM=`/bin/date +"%m"`
YEAR_NUM=`/bin/date +"%Y"`

/usr/local/pgsql/bin/pg_dump -h 192.168.1.5 -Upostgres -f
backup.$YEAR_NUM$MON_NUM$DAY_NUM.pga -Fc -b data
#END
 
Curiouser and curiouser... Last night's dump failed to restore in the same
way:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 7545; 2606 311883439 FK
CONSTRAINT packet_search_trigram_puuid_fkey postgres pg_restore: [archiver
(db)] could not execute query: ERROR:  insert or update on table
"packet_search_trigram" violates foreign key constraint
"packet_search_trigram_puuid_fkey" DETAIL:  Key
(packet_uuid)=(0ab44da9-544d-413a-9ab1-a1b442310b24) is not present in
table "packet". Command was:
ALTER TABLE ONLY packet_search_trigram
    ADD CONSTRAINT packet_search_trigram_puuid_fkey FOREIGN KEY
(packet_uuid) REFERE... pg_restore: *** aborted because of error
pg_restore: finished item 7545 FK CONSTRAINT
packet_search_trigram_puuid_fkey pg_restore: [archiver] worker process
failed: exit code 1
pg_restore: *** aborted because of error

That was the same failure I got the previous night. I go to the live
database and rows with that key are /not/ in either one of those tables.
They /were/ in the tables at one point. I have an ON DELETE trigger that
copies deleted rows into another table, so I can see that a row with that
key once existed in those tables.
This may not be a pg_dump problem, but some sort of MVCC irregularity where
pg_dump is able to dump rows that it shouldn't. I bet a VACUUM FULL would
clean this up, but I have a live problem here. If I eradicate it, who knows
when we'll see it again...

--- On Wed, 12/9/09, Craig Ringer <craig@postnewspapers.com.au> wrote:

One thing that comes to mind is to restore the dump file to a file instead of a
database and see what is being dumped from the live database.

--
Adrian Klaver
aklaver@comcast.net

Thanks for the suggestion. I'm not sure what you mean when you say I should restore to a file. Do you mean I should dump the database to an SQL file instead of the "compressed" format?

What do you think I will find?

In the database dump, it is including a row that should be marked as deleted. I can select on that key in the production database and get zero rows, and I can select on that key in the restored database and find the row. When I ignore errors the data is restored, but the foreign key can't be created (and that is the only error I encounter). The presence of the data in the dump can not be contested... :)

--- On Thu, 12/10/09, Adrian Klaver <aklaver@comcast.net> wrote:

CG wrote:

Thanks for the suggestion. I'm not sure what you mean when you say I should restore to a file. Do you mean I should dump the database to an SQL file instead of the "compressed" format?

he meant...

pg_restore -f outputfile.sql yourdumpfile

this will convert the dumpfile to SQL...

----- "CG" <cgg007@yahoo.com> wrote:

Thanks for the suggestion. I'm not sure what you mean when you say I
should restore to a file. Do you mean I should dump the database to an
SQL file instead of the "compressed" format?

See Johns answer.

What do you think I will find?

In the database dump, it is including a row that should be marked as
deleted. I can select on that key in the production database and get
zero rows, and I can select on that key in the restored database and
find the row. When I ignore errors the data is restored, but the
foreign key can't be created (and that is the only error I encounter).
The presence of the data in the dump can not be contested... :)

Well I often find what I 'know' and what is are not the same:) Basically restoring to the file replicates the database restore, with out the error hopefully. Looking at the data restored in the file might give you a clue to what is going on. Just one step in the process of resolving the problem.

Adrian Klaver
aklaver@comcast.net

That's really nifty! I didn't know you could do that!

So I expanded it, and I grepped for that UUID through the 46 gig file, and I found the row in the dump that shouldn't be there... It defies explanation.

--- On Thu, 12/10/09, John R Pierce <pierce@hogranch.com> wrote:

On Friday 11 December 2009 5:59:31 am CG wrote:

That's really nifty! I didn't know you could do that!

So I expanded it, and I grepped for that UUID through the 46 gig file, and
I found the row in the dump that shouldn't be there... It defies
explanation.

Just so I am clear it always exactly the same UUID that shows up in the dump
file? Upstream in this thread you mentioned a DELETE trigger that copied
deleted items to another table. Is it possible that there is a stuck query
related to this trigger that is keeping this row visible to pg_dump? A query
against pg_stat_activity might be useful.

--
Adrian Klaver
aklaver@comcast.net

On Thu, Dec 10, 2009 at 1:21 PM, CG <cgg007@yahoo.com> wrote:

Thanks for the suggestion. I'm not sure what you mean when you say I should restore to a file. Do you mean I should dump the database to an SQL file instead of the "compressed" format?

What do you think I will find?

In the database dump, it is including a row that should be marked as deleted. I can select on that key in the production database and get zero rows, and I can select on that key in the restored database and find the row. When I ignore errors the data is restored, but the foreign key can't be created (and that is the only error I encounter). The presence of the data in the dump can not be contested... :)

This could be a corrupted index problem maybe? If you do this:

set enable_indexscan=off;
select * from table where key=value;

does it still not show up?

--- On Fri, 12/11/09, Scott Marlowe <scott.marlowe@gmail.com> wrote:

From: Scott Marlowe <scott.marlowe@gmail.com>
Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
To: cgg007@yahoo.com
Cc: pgsql-general@postgresql.org, "Adrian Klaver" <aklaver@comcast.net>, "Craig Ringer" <craig@postnewspapers.com.au>
Date: Friday, December 11, 2009, 1:17 PM
On Thu, Dec 10, 2009 at 1:21 PM, CG
<cgg007@yahoo.com>
wrote:

Thanks for the suggestion. I'm not sure what you mean

when you say I should restore to a file. Do you mean I
should dump the database to an SQL file instead of the
"compressed" format?

What do you think I will find?

In the database dump, it is including a row that

should be marked as deleted. I can select on that key in the
production database and get zero rows, and I can select on
that key in the restored database and find the row. When I
ignore errors the data is restored, but the foreign key
can't be created (and that is the only error I encounter).
The presence of the data in the dump can not be contested...
:)

This could be a corrupted index problem maybe?  If you
do this:

set enable_indexscan=off;
select * from table where key=value;

does it still not show up?

Bingo. Showed right up. I did a reindex, and now it shows up searching via sequential scan or index scan.

So that's pretty scary to have a corrupted index. Once I reindexed, I'm able to see /a lot/ of data I couldn't before. This is the first time in 9 years that I've been bitten by PostgreSQL, and this one HURT.

PostgreSQL didn't crash, so there was no indication of failure until the demp-reload. To quote from the masters: Although in theory this should never happen, in practice indexes may become corrupted due to software bugs or hardware failures. I'm reasonably certain that the hardware for the server is sound. No crashes, no alarms... That leaves sofware bugs.

We're running PostgreSQL 8.4.1. I don't see any smoking gun bugfixes in 8.4.2, but we'll upgrade ASAP anyway...

What are your suggestions for how to proceed?

On Tuesday 15 December 2009 2:33:39 pm CG wrote:

Bingo. Showed right up. I did a reindex, and now it shows up searching via
sequential scan or index scan.

So that's pretty scary to have a corrupted index. Once I reindexed, I'm
able to see /a lot/ of data I couldn't before. This is the first time in 9
years that I've been bitten by PostgreSQL, and this one HURT.

PostgreSQL didn't crash, so there was no indication of failure until the
demp-reload. To quote from the masters: Although in theory this should
never happen, in practice indexes may become corrupted due to software bugs
or hardware failures. I'm reasonably certain that the hardware for the
server is sound. No crashes, no alarms... That leaves sofware bugs.

We're running PostgreSQL 8.4.1. I don't see any smoking gun bugfixes in
8.4.2, but we'll upgrade ASAP anyway...

What are your suggestions for how to proceed?

Interesting, though something is still bothering me. To quote from one of your
posts upstream;

"That was the same failure I got the previous night. I go to the live database
and rows with that key are /not/ in either one of those tables. They /were/ in
the tables at one point. I have an ON DELETE trigger that copies deleted rows
into another table, so I can see that a row with that key once existed in those
tables."

Would seem that the rows where deleted and should not be there when the table
was reindexed. Are the 'new' rows you are seeing also in the delete table?

--
Adrian Klaver
aklaver@comcast.net

--- On Tue, 12/15/09, Adrian Klaver <aklaver@comcast.net> wrote:

From: Adrian Klaver <aklaver@comcast.net>
Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
To: cgg007@yahoo.com
Cc: "postgresql listserv" <pgsql-general@postgresql.org>, "Craig Ringer" <craig@postnewspapers.com.au>, "Scott Marlowe" <scott.marlowe@gmail.com>
Date: Tuesday, December 15, 2009, 6:53 PM
On Tuesday 15 December 2009 2:33:39
pm CG wrote:

Bingo. Showed right up. I did a reindex, and now it

shows up searching via

sequential scan or index scan.

So that's pretty scary to have a corrupted index. Once

I reindexed, I'm

able to see /a lot/ of data I couldn't before. This is

the first time in 9

years that I've been bitten by PostgreSQL, and this

one HURT.

PostgreSQL didn't crash, so there was no indication of

failure until the

demp-reload. To quote from the masters: Although in

theory this should

never happen, in practice indexes may become corrupted

due to software bugs

or hardware failures. I'm reasonably certain that the

hardware for the

server is sound. No crashes, no alarms... That leaves

sofware bugs.

We're running PostgreSQL 8.4.1. I don't see any

smoking gun bugfixes in

8.4.2, but we'll upgrade ASAP anyway...

What are your suggestions for how to proceed?

Interesting, though something is still bothering me. To
quote from one of your
posts upstream;

"That was the same failure I got the previous night. I go
to the live database
and rows with that key are /not/ in either one of those
tables. They /were/ in
the tables at one point. I have an ON DELETE trigger that
copies deleted rows
into another table, so I can see that a row with that key
once existed in those
tables."

Would seem that the rows where deleted and should not be
there when the table
was reindexed. Are the 'new' rows you are seeing also in
the delete table?

select foo from bar where baz = 'key';

I was mistaken when I said that the row was not in the table. If I had an index on baz, and the index was corrupted, that SQL would return 0 rows leading me to believe that there were no rows in the table with that key.

And, the reason for that row remaining in the database after its foreign keyed parent row was deleted was because the delete operation was depending on the index to find the rows to delete, and that index was corrupt.

Of course, I had no idea that the index was corrupt when I made my first post.

On the table that has the "martian" row, there is no delete storage. Since the data in the table is trigger-generated for FTI searches, there's no reason to keep that data around.

I'm still faced with the unpleasant and unresolved issue of why the index was corrupted in the first place.

----- "CG" <cgg007@yahoo.com> wrote:

--- On Tue, 12/15/09, Adrian Klaver <aklaver@comcast.net> wrote:

From: Adrian Klaver <aklaver@comcast.net>
Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
To: cgg007@yahoo.com
Cc: "postgresql listserv" <pgsql-general@postgresql.org>, "Craig

Ringer" <craig@postnewspapers.com.au>, "Scott Marlowe"
<scott.marlowe@gmail.com>

Date: Tuesday, December 15, 2009, 6:53 PM
On Tuesday 15 December 2009 2:33:39
pm CG wrote:

Bingo. Showed right up. I did a reindex, and now it

shows up searching via

sequential scan or index scan.

So that's pretty scary to have a corrupted index. Once

I reindexed, I'm

able to see /a lot/ of data I couldn't before. This is

the first time in 9

years that I've been bitten by PostgreSQL, and this

one HURT.

PostgreSQL didn't crash, so there was no indication of

failure until the

demp-reload. To quote from the masters: Although in

theory this should

never happen, in practice indexes may become corrupted

due to software bugs

or hardware failures. I'm reasonably certain that the

hardware for the

server is sound. No crashes, no alarms... That leaves

sofware bugs.

We're running PostgreSQL 8.4.1. I don't see any

smoking gun bugfixes in

8.4.2, but we'll upgrade ASAP anyway...

What are your suggestions for how to proceed?

Interesting, though something is still bothering me. To
quote from one of your
posts upstream;

"That was the same failure I got the previous night. I go
to the live database
and rows with that key are /not/ in either one of those
tables. They /were/ in
the tables at one point. I have an ON DELETE trigger that
copies deleted rows
into another table, so I can see that a row with that key
once existed in those
tables."

Would seem that the rows where deleted and should not be
there when the table
was reindexed. Are the 'new' rows you are seeing also in
the delete table?

select foo from bar where baz = 'key';

I was mistaken when I said that the row was not in the table. If I had
an index on baz, and the index was corrupted, that SQL would return 0
rows leading me to believe that there were no rows in the table with
that key.

And, the reason for that row remaining in the database after its
foreign keyed parent row was deleted was because the delete operation
was depending on the index to find the rows to delete, and that index
was corrupt.

Of course, I had no idea that the index was corrupt when I made my
first post.

On the table that has the "martian" row, there is no delete storage.
Since the data in the table is trigger-generated for FTI searches,
there's no reason to keep that data around.

Would it be possible to see the table schemas and indices ?

I'm still faced with the unpleasant and unresolved issue of why the
index was corrupted in the first place.

Adrian Klaver
aklaver@comcast.net

--- On Thu, 12/17/09, Adrian Klaver <aklaver@comcast.net> wrote:

Would it be possible to see the table schemas and indices
?

Sure (you asked for it!!) :

CREATE TABLE packet
(
id integer NOT NULL DEFAULT nextval('packet_id_seq'::regclass),
packet_uuid uniqueidentifier NOT NULL DEFAULT newid(),
username character varying(50) NOT NULL DEFAULT ''::character varying,
pgroup_uuid uniqueidentifier DEFAULT newid(),
orig_trans_uuid uniqueidentifier,
user_reference_id character varying(50) DEFAULT ''::character varying,
trans_data character varying(100) NOT NULL DEFAULT ''::character varying,
trans_type character varying(50) NOT NULL DEFAULT 'unknown'::character varying,
trans_date timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone,
processor character varying(10),
service character varying(10),
CONSTRAINT packet_pkey PRIMARY KEY (id)
)
WITH (
OIDS=TRUE
);
ALTER TABLE packet OWNER TO postgres;
GRANT ALL ON TABLE packet TO postgres;
GRANT ALL ON TABLE packet TO adduser;

CREATE INDEX packet_otuuid_idx
ON packet
USING btree
(orig_trans_uuid);

CREATE INDEX packet_pgroup_uuid_idx
ON packet
USING btree
(pgroup_uuid);

CREATE INDEX packet_puuid_hash_uniq
ON packet
USING hash
(packet_uuid);

CREATE UNIQUE INDEX packet_puuid_idx
ON packet
USING btree
(packet_uuid);

CREATE INDEX packet_trans_date_idx
ON packet
USING btree
(trans_date);

CREATE INDEX packet_user_idx
ON packet
USING btree
(username);

CREATE INDEX packet_user_puuid_idx
ON packet
USING btree
(username, packet_uuid);

CREATE OR REPLACE RULE packet_delete_rule AS
ON DELETE TO packet DO INSERT INTO removed_packet (id, packet_uuid, username, pgroup_uuid, orig_trans_uuid, user_reference_id, trans_data, trans_type, trans_date, processor, service) SELECT packet.id, packet.packet_uuid, packet.username, packet.pgroup_uuid, packet.orig_trans_uuid, packet.user_reference_id, packet.trans_data, packet.trans_type, packet.trans_date, packet.processor, packet.service
FROM packet
WHERE packet.id = old.id;

CREATE TRIGGER packet_count_delete_trig
BEFORE DELETE
ON packet
FOR EACH ROW
EXECUTE PROCEDURE letter_count_trig();

CREATE TRIGGER packet_count_insert_trig
AFTER INSERT
ON packet
FOR EACH ROW
EXECUTE PROCEDURE letter_count_trig();

CREATE TRIGGER packet_delete_trig
BEFORE DELETE
ON packet
FOR EACH ROW
EXECUTE PROCEDURE packet_datalink_status_trig();

CREATE TRIGGER packet_insert_trig
AFTER INSERT
ON packet
FOR EACH ROW
EXECUTE PROCEDURE packet_ins_trig();

CREATE TABLE dpo.packet_search_trigram
(
id integer NOT NULL DEFAULT nextval('packet_search_trigram_id_seq'::regclass),
packet_uuid uniqueidentifier NOT NULL,
trigram_vector tsvector NOT NULL,
CONSTRAINT packet_search_trigram_id_pkey PRIMARY KEY (id),
CONSTRAINT packet_search_trigram_puuid_fkey FOREIGN KEY (packet_uuid)
REFERENCES dpo.packet (packet_uuid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
ALTER TABLE dpo.packet_search_trigram OWNER TO postgres;
GRANT ALL ON TABLE dpo.packet_search_trigram TO postgres WITH GRANT OPTION;
GRANT ALL ON TABLE dpo.packet_search_trigram TO addgroup;

CREATE INDEX packet_search_trigram_packet_uuid_idx
ON dpo.packet_search_trigram
USING hash
(packet_uuid);

CREATE INDEX packet_search_trigram_trigram_vector_idx
ON dpo.packet_search_trigram
USING gin
(trigram_vector);

On Thursday 17 December 2009 6:39:45 pm CG wrote:

--- On Thu, 12/17/09, Adrian Klaver <aklaver@comcast.net> wrote:

Would it be possible to see the table schemas and indices
?

Sure (you asked for it!!) :

CREATE TABLE dpo.packet_search_trigram
(
id integer NOT NULL DEFAULT
nextval('packet_search_trigram_id_seq'::regclass), packet_uuid
uniqueidentifier NOT NULL,
trigram_vector tsvector NOT NULL,
CONSTRAINT packet_search_trigram_id_pkey PRIMARY KEY (id),
CONSTRAINT packet_search_trigram_puuid_fkey FOREIGN KEY (packet_uuid)
REFERENCES dpo.packet (packet_uuid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
ALTER TABLE dpo.packet_search_trigram OWNER TO postgres;
GRANT ALL ON TABLE dpo.packet_search_trigram TO postgres WITH GRANT OPTION;
GRANT ALL ON TABLE dpo.packet_search_trigram TO addgroup;

CREATE INDEX packet_search_trigram_packet_uuid_idx
ON dpo.packet_search_trigram
USING hash
(packet_uuid);

CREATE INDEX packet_search_trigram_trigram_vector_idx
ON dpo.packet_search_trigram
USING gin
(trigram_vector);

You might want to take a look at upgrading to 8.4.2 per this from the release
notes:

"Fix hash index corruption (Tom)

The 8.4 change that made hash indexes keep entries sorted by hash value failed
to update the bucket splitting and compaction routines to preserve the
ordering. So application of either of those operations could lead to permanent
corruption of an index, in the sense that searches might fail to find entries
that are present. To deal with this, it is recommended to REINDEX any hash
indexes you may have after installing this update. "

See also:
http://archives.postgresql.org/pgsql-committers/2009-11/msg00002.php

--
Adrian Klaver
aklaver@comcast.net

Adrian Klaver <aklaver@comcast.net> writes:

On Thursday 17 December 2009 6:39:45 pm CG wrote:

CREATE INDEX packet_search_trigram_packet_uuid_idx
ON dpo.packet_search_trigram
USING hash
(packet_uuid);

You might want to take a look at upgrading to 8.4.2 per this from the release
notes:

Actually, what I'd recommend is dropping that hash index and replacing
it with regular btree. And the same for any other hash indexes you
have. Hash indexes simply are not production grade (yet).

regards, tom lane