Re: pg_dump and ON DELETE CASCADE problem

--- 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 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);