Hi, I have a problem with partition tables and schemas Postgres: 8.4.2 on redhat and debian I have three schemas public (the default one), live and test live and test are identical copies in table layout, just the tables are created for each one sperated. in those two schemas I have two tables that do logging for me CREATE TABLE session ( session_id SERIAL, session_string VARCHAR, previous_session_string VARCHAR, identified_agent VARCHAR, session_updated TIMESTAMP WITHOUT TIME ZONE, session_created TIMESTAMP WITHOUT TIME ZONE, PRIMARY KEY (session_id) ) INHERITS (public.generic) WITHOUT OIDS; CREATE TABLE visit ( visit_id SERIAL, session_id INT NOT NULL, path VARCHAR, menu_code VARCHAR, page_code VARCHAR, idkey VARCHAR, referer VARCHAR, redirect_url VARCHAR, date_visited TIMESTAMP WITHOUT TIME ZONE, PRIMARY KEY (visit_id), FOREIGN KEY (idkey) REFERENCES page (idkey) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (session_id) REFERENCES session (session_id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE ) INHERITS (public.generic) WITHOUT OIDS; and then I create tables for each month CREATE TABLE session_201002 ( CHECK ( date_created >= DATE '2010-02-01' AND date_created < DATE '2010-03-01' ) ) INHERITS (session); CREATE TABLE visit_201002 ( CHECK ( date_created >= DATE '2010-02-01' AND date_created < DATE '2010-03-01' ) ) INHERITS (visit); I add the primary key ALTER TABLE session_201002 ADD PRIMARY KEY (session_id); ALTER TABLE visit_201002 ADD PRIMARY KEY (visit_id); and I add several indexes (not shown here) and then FK constraints ALTER TABLE visit_201002 ADD CONSTRAINT visit_201002_session_id_fkey FOREIGN KEY (session_id) REFERENCES session (session_id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE visit_201002 ADD CONSTRAINT visit_201002_idkey_fkey FOREIGN KEY (idkey) REFERENCES page (idkey) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE; and my triggers for some internal date setting CREATE TRIGGER trg_session_201002 BEFORE INSERT OR UPDATE ON session_201002 FOR EACH ROW EXECUTE PROCEDURE public.set_generic(); CREATE TRIGGER trg_visit_201002 BEFORE INSERT OR UPDATE ON visit_201002 FOR EACH ROW EXECUTE PROCEDURE public.set_generic(); Finally I add the main triggers for the partition: -- session CREATE OR REPLACE FUNCTION session_insert_trigger () RETURNS TRIGGER AS $$ BEGIN IF ( NEW.date_created >= DATE '2010-02-01' AND NEW.date_created < DATE '2010-03-01') THEN INSERT INTO session_201002 VALUES (NEW.*); ELSE INSERT INTO session_overflow VALUES (NEW.*); END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; -- and attach to session table CREATE TRIGGER trg_session_insert BEFORE INSERT OR UPDATE ON session FOR EACH ROW EXECUTE PROCEDURE session_insert_trigger(); -- visit CREATE OR REPLACE FUNCTION visit_insert_trigger () RETURNS TRIGGER AS $$ BEGIN IF ( NEW.date_created >= DATE '2010-02-01' AND NEW.date_created < DATE '2010-03-01') THEN INSERT INTO visit_201002 VALUES (NEW.*); ELSE INSERT INTO visit_overflow VALUES (NEW.*); END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; -- and attach to visit table CREATE TRIGGER trg_visit_insert BEFORE INSERT OR UPDATE ON visit FOR EACH ROW EXECUTE PROCEDURE visit_insert_trigger(); Everything is done for each schema separately when I am in the schema itself (via SET search_path TO test/live) my problem is, when I insert data into the visit table it tries to find the session data in the live schema. I have no idea why, because no schema was copied or inherited from the other side. Is there any explanation for this? Creating FK on the main (dummy) tables makes no sense, because there is no data stored in them anyway. I tried to create everything and every command where each table or function is prefixed with the schema name, but with the same result. Anyone can give me some tips what I am doing wrong? -- ★ Clemens 呉 Schwaighofer ★ IT Engineer/Web Producer/Planning ★ E-Graphics Communications SP Digital ★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ★ Tel: +81-(0)3-3545-7706 ★ Fax: +81-(0)3-3545-7343 ★ http://www.e-gra.co.jp This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. If you received this e-mail in error, any review, use, dissemination, distribution or copying of this e-mail is strictly prohibited. Please notify us immediately of the error via e-mail to disclaimer@xxxxxxxxxxxxx and please delete the e-mail from your system, retaining no copies in any media. We appreciate your cooperation. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
