Problem with partition tables and schemas - Mailing list pgsql-general

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@tbwaworld.com and please delete the e-mail from your system, retaining no copies in any media.
We appreciate your cooperation.