Re: pg_dump and ON DELETE CASCADE problem - Mailing list pgsql-general

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