Re: Legacy foreign keys

> > [ squint... ]  Which 8.3beta are you testing, exactly?  This was dealt
> > with in beta3.
> >
> > If it is beta3 or later, please send the output of "pg_dump -s -t wspolicy"
> > from the 8.2 database.
>
> *lightbulb* beta2. I haven't had much time to dedicate to testing new
> betas yet (I was just trying to get a test 8.3 server up and running),
> but I will do so. Thanks.

I finally found more time to test this more extensively. i'm still
running into an issue with this, although it's different this time.
There are no errors printed to the terminal, but neither the foreign
key nor the trigger get made.

8.2 schema for the wspolicy table:
sushi=> \d sushi.wspolicy
       Table "sushi.wspolicy"
     Column     |  Type  | Modifiers
----------------+--------+-----------
 os             | text   | not null
 dump_policy    | text   | not null
 atom_type      | text   | not null
 file_system    | text   | not null
 est_epoch_size | bigint |
Indexes:
    "su_wspolicy_pkey" PRIMARY KEY, btree (os, dump_policy, atom_type,
file_system)
Triggers:
    "RI_ConstraintTrigger_60045810" AFTER INSERT OR UPDATE ON
sushi.wspolicy FROM sushi.atom_os_policy_values NOT DEFERRABLE
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins"('<unnamed>', 'wspolicy', 'atom_os_policy_values',
'UNSPECIFIED', 'os', 'os')
    "RI_ConstraintTrigger_60045811" AFTER INSERT OR UPDATE ON
sushi.wspolicy FROM sushi.atom_dump_policy_values NOT DEFERRABLE
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins"('<unnamed>', 'wspolicy',
'atom_dump_policy_values', 'UNSPECIFIED', 'dump_policy',
'dump_policy')
    "RI_ConstraintTrigger_60045812" AFTER INSERT OR UPDATE ON
sushi.wspolicy FROM sushi.atom_type_values NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins"('<unnamed>', 'wspolicy', 'atom_type_values',
'UNSPECIFIED', 'atom_type', 'atom_type')

8.3 schema:
sushi=> \d sushi.wspolicy
       Table "sushi.wspolicy"
     Column     |  Type  | Modifiers
----------------+--------+-----------
 os             | text   | not null
 dump_policy    | text   | not null
 atom_type      | text   | not null
 file_system    | text   | not null
 est_epoch_size | bigint |
Indexes:
    "su_wspolicy_pkey" PRIMARY KEY, btree (os, dump_policy, atom_type,
file_system)

And the here's the output of pg_dump that was requested:
$ /s/postgresql/bin/pg_dump -h sensei -p 5432 sushi -s -t sushi.wspolicy
--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = sushi, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = true;

--
-- Name: wspolicy; Type: TABLE; Schema: sushi; Owner: postgres; Tablespace:
--

CREATE TABLE wspolicy (
    os text NOT NULL,
    dump_policy text NOT NULL,
    atom_type text NOT NULL,
    file_system text NOT NULL,
    est_epoch_size bigint
);


ALTER TABLE sushi.wspolicy OWNER TO postgres;

--
-- Name: su_wspolicy_pkey; Type: CONSTRAINT; Schema: sushi; Owner:
postgres; Tablespace:
--

ALTER TABLE ONLY wspolicy
    ADD CONSTRAINT su_wspolicy_pkey PRIMARY KEY (os, dump_policy,
atom_type, file_system);


--
-- Name: RI_ConstraintTrigger_60045810; Type: TRIGGER; Schema: sushi;
Owner: postgres
--

CREATE CONSTRAINT TRIGGER "<unnamed>"
    AFTER INSERT OR UPDATE ON wspolicy
    FROM atom_os_policy_values
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_check_ins"('<unnamed>', 'wspolicy',
'atom_os_policy_values', 'UNSPECIFIED', 'os', 'os');


--
-- Name: RI_ConstraintTrigger_60045811; Type: TRIGGER; Schema: sushi;
Owner: postgres
--

CREATE CONSTRAINT TRIGGER "<unnamed>"
    AFTER INSERT OR UPDATE ON wspolicy
    FROM atom_dump_policy_values
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_check_ins"('<unnamed>', 'wspolicy',
'atom_dump_policy_values', 'UNSPECIFIED', 'dump_policy',
'dump_policy');


--
-- Name: RI_ConstraintTrigger_60045812; Type: TRIGGER; Schema: sushi;
Owner: postgres
--

CREATE CONSTRAINT TRIGGER "<unnamed>"
    AFTER INSERT OR UPDATE ON wspolicy
    FROM atom_type_values
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_check_ins"('<unnamed>', 'wspolicy',
'atom_type_values', 'UNSPECIFIED', 'atom_type', 'atom_type');


--
-- Name: wspolicy; Type: ACL; Schema: sushi; Owner: postgres
--

REVOKE ALL ON TABLE wspolicy FROM PUBLIC;
REVOKE ALL ON TABLE wspolicy FROM postgres;
GRANT ALL ON TABLE wspolicy TO postgres;
GRANT SELECT ON TABLE wspolicy TO sushi;
GRANT ALL ON TABLE wspolicy TO staff;


--
-- PostgreSQL database dump complete
--

I did see these errors in the postgres syslog pertaining to wspolicy's
foreign keys. It's trying, but it's not working for some reason.

Jan 31 17:21:19 mitchell postgres[3152]: [42-1] NOTICE:  ignoring
incomplete trigger group for constraint "<unnamed>" FOREIGN KEY
wspolicy(os) REFERENCES
Jan 31 17:21:19 mitchell postgres[3152]: [42-2]  atom_os_policy_values(os)
Jan 31 17:21:19 mitchell postgres[3152]: [42-3] DETAIL:  Found
referencing table's trigger.
Jan 31 17:21:19 mitchell postgres[3152]: [43-1] NOTICE:  ignoring
incomplete trigger group for constraint "<unnamed>" FOREIGN KEY
wspolicy(dump_policy) REFERENCES
Jan 31 17:21:19 mitchell postgres[3152]: [43-2]
atom_dump_policy_values(dump_policy)
Jan 31 17:21:19 mitchell postgres[3152]: [43-3] DETAIL:  Found
referencing table's trigger.
Jan 31 17:21:19 mitchell postgres[3152]: [44-1] NOTICE:  ignoring
incomplete trigger group for constraint "<unnamed>" FOREIGN KEY
wspolicy(atom_type) REFERENCES
Jan 31 17:21:19 mitchell postgres[3152]: [44-2]  atom_type_values(atom_type)
Jan 31 17:21:19 mitchell postgres[3152]: [44-3] DETAIL:  Found
referencing table's trigger.

I'm testing this on 8.3-RC2. If it comes to it, I'm willing to
manually translate the triggers into proper foreign keys.

Peter