Re: pg_dump and ON DELETE CASCADE problem

On Thursday 17 December 2009 6:39:45 pm CG wrote:
> --- On Thu, 12/17/09, Adrian Klaver <aklaver(at)comcast(dot)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(at)comcast(dot)net