Problem mit pg_restore und check constraint

Hi @ all,
eine meiner Tabellen enthält zwei Check-constraints, die je eine Funktion aufrufen:

ALTER TABLE table ADD CONSTRAINT verify_homa_ir CHECK (verify_homa_ir(homa_ir, id));

und

ALTER TABLE table ADD CONSTRAINT verify_homa_proz_b CHECK (verify_homa_proz_b(homa_proz_b, id));

Die zugehörige Funktion des ersten Check:

CREATE OR REPLACE FUNCTION verify_homa_ir(numeric, integer)
  RETURNS boolean AS
$BODY$
declare
    homa_given alias for $1;
    given_checkup_id alias for $2;
    homa_calc table.homa_ir%TYPE;
begin
    homa_calc := (blablabla);
    
    if
        homa_calc = homa_given or homa_given is null
    then
        return true;
    else

        return false;
    end if;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION verify_homa_ir(numeric, integer) OWNER TO owner_one;

Die zweite Funktion sieht quasi genauso aus.
Ich habe einen Dump erstellt mittels

/usr/bin/pg_dump -i -h localhost -p 5432 -U owner_one -F c -b -f my_backup my_db

und dies ist der anschließende Restorebefehl (Datenbank vorher gelöscht):

pg_restore -C -d postgres Desktop/my_db

Die Datenbank wird zwar wiederhergestellt, ist aber leer. Ich bekomme eine Reihe von Fehlern aufgrund fehlender Fremdschlüssel, die alle auf diesen Fehler folgen:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1861; 0 17032 TABLE DATA table owner_one
pg_restore: [archiver (db)] COPY failed: ERROR:  new row for relation "table" violates check constraint "verify_homa_ir"
CONTEXT:  COPY table, line 20: "<Zeileninhalt>"

Ich kann beide Checks löschen, neu dumpen und alles ist gut. Die Zeile, die den Fehler ausgelöst hat, ist ebenfalls ok, widerspricht dem Constraint also nicht.
Jemand ne Idee, wie ich auch mit constraints dumpen und wiederherstellen kann?
Postgresversion 8.2.4
Thx.

ich vermute das geheimnis in homa_calc - bzw. in blablabla

dass du einen fehler in dem check_constraint hast … der berechnung, die nun wegen anderer situation in einen fehler laeuft …

was macht denn blabla? es sollte ja nen key oder zahl (numeric?) berechnen …

ausserdem … wenn homa_calc null ist … dann ist null = null > false

begin
    [color="Red"]homa_calc := (blablabla);[/color]
    
    if
        [color="#ff0000"]homa_calc =[/color] homa_given or homa_given is null
    then
        return true;
    else

        return [color="#ff0000"]false[/color];
    end if;
end;

uebrigends kannst du den code (wenn er denn funzt) auch abkuerzen

begin
    homa_calc := (blablabla);
    
    return homa_calc = homa_given or homa_given is null;
end;

Genau, blabla holt sich per select aus anderen Tabellen ein paar Datensätze mit derselben ID und gibt einfach ne daraus berechnete Dezimalzahl zurück.
Ok, sofern der einzelne Datensatz noch nicht komplett ist, wird das Check nicht funktionieren, das klappt erst danach. Andererseits können ja auch Einträge mit Fremdschlüsseln erst geschrieben werden, wenn der zugehörige Primärschlüssel schon existiert und das klappt ja auch. Ist mir nicht ganz klar, ebenso ein Workaround…
Und daß mit null = null > false umgehe ich dann durch vertauschen der Reihenfolge:

homa_given is null or [color=black]homa_calc =[/color] homa_given

Thx!

Edit: create constraint trigger statt des check müsste funzen als workaround

nunja … bei dem restore wird wohl zuerst das modell erzeugt … alle constrains und alle trigger …

da spielt es auch keine rolle, dass die tables alle leer sind - ist sogar von vorteil …

nur bloederweise wird dann wohl die db in der falschen reihenfolge befuellt? da muss ich mal nachlesen ob das moeglich ist

es gibt ja auch zirkulare referenzen … etc. … das mit den abhaengigkeiten ist nicht einfach … ich deke mit dem constraint-trigger bist auf dem richtigen weg

Naja, das Problem scheint ja geklärt: dein CHECK erwartet, daß da zuerst eine andere Tabelle gefüllt ist, erst dann kann der funktionieren. PG kann manches, aber nicht die Logik in Deiner Check-Funktion erkennen.

Eine Lösung wäre, beim Restore zu sagen, daß zuerst diese eine Tabelle zu befüllen ist. Also erst einmal mit pg_restore -l ein Listfile aller Objekte erzeugen, dieses mit $EDITOR so bearbeiten, daß die Reihenfolge stimmt, und dann pg_restore -L laufen lassen. Ungetestet.

Andreas

Ungetestet, aber genau das ist der dafür vorgeschlagene Weg.

muss aber auch nicht immer funktionieren

die wege von check_constraints sind manchmal unergruendlich …

so kann es z.B. vorkommen, dass row A eine referenz auf B erwartet, was wiederum eine referenz auf ein frueheres A erwartet … so chronologische modelle …

dann ist ein constraint_trigger immernoch die bessere wahl …

vielleicht sollte man aber beides in betracht ziehen

Im übrigen mal wieder ein schönes Beispiel von mal wieder was geschrieben, von dem Du keine Ahnung hast.

test=# create schema symbiont;
CREATE SCHEMA
test=*# set search_path=symbiont;
SET
test=*# commit;
COMMIT
test=# create table a (aid int primary key, atext text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
CREATE TABLE
test=*# create table b (bid int primary key, btext text, akey int references a deferrable);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey" for table "b"
CREATE TABLE
test=*# alter table a add column bkey int references b deferrable;
ALTER TABLE
test=*# set constraints all deferred;
SET CONSTRAINTS
test=*# insert into a values (1, 'text a', 3);
INSERT 0 1
test=*# insert into b values (3, 'text b', 1);
INSERT 0 1
test=*# commit;
COMMIT
test=# \q

Das Backup dazu sieht so aus (gekürzt):

pg_dump -n symbiont test

--
-- Name: a; Type: TABLE; Schema: symbiont; Owner: kretschmer; Tablespace: 
--                                                                        

CREATE TABLE a (
    aid integer NOT NULL,
    atext text,          
    bkey integer         
);                       


ALTER TABLE symbiont.a OWNER TO kretschmer;

--
-- Name: b; Type: TABLE; Schema: symbiont; Owner: kretschmer; Tablespace: 
--                                                                        

CREATE TABLE b (
    bid integer NOT NULL,
    btext text,          
    akey integer         
);                       


ALTER TABLE symbiont.b OWNER TO kretschmer;

--
-- Data for Name: a; Type: TABLE DATA; Schema: symbiont; Owner: kretschmer
--                                                                        

COPY a (aid, atext, bkey) FROM stdin;
1       text a  3                    
\.                                   


--
-- Data for Name: b; Type: TABLE DATA; Schema: symbiont; Owner: kretschmer
--                                                                        

COPY b (bid, btext, akey) FROM stdin;
3       text b  1
\.


--
-- Name: a_pkey; Type: CONSTRAINT; Schema: symbiont; Owner: kretschmer; Tablespace:
--

ALTER TABLE ONLY a
    ADD CONSTRAINT a_pkey PRIMARY KEY (aid);


--
-- Name: b_pkey; Type: CONSTRAINT; Schema: symbiont; Owner: kretschmer; Tablespace:
--

ALTER TABLE ONLY b
    ADD CONSTRAINT b_pkey PRIMARY KEY (bid);


--
-- Name: a_bkey_fkey; Type: FK CONSTRAINT; Schema: symbiont; Owner: kretschmer
--

ALTER TABLE ONLY a
    ADD CONSTRAINT a_bkey_fkey FOREIGN KEY (bkey) REFERENCES b(bid) DEFERRABLE;


--
-- Name: b_akey_fkey; Type: FK CONSTRAINT; Schema: symbiont; Owner: kretschmer
--

ALTER TABLE ONLY b
    ADD CONSTRAINT b_akey_fkey FOREIGN KEY (akey) REFERENCES a(aid) DEFERRABLE;


--
-- PostgreSQL database dump complete
--

PG erzeugt also Constraints etc. erst zum Schluß. Ich kann Dir auch erklären, warum: es geht schneller und PG kann davon ausgehen, daß ein Dump in sich konsistent ist. Und falls nicht, würde es zum Ende knallen.


Andreas

eher ein schoenes beispiel fuer dein ueberhebliches rumgetoene . du musst wirklich schlimme probleme haben

wenn dem so waere, dürfte der OP das problem nicht haben …

offenbar erzeugt er nicht alle constraints “zum schluss” - weshalb es nahe des endes eben genau “knallt”

Sorry, Check-Constraints werden mit der Tabelle erstellt, hast Recht.

Hm, blöd.


Andreas

und ja - gelegentlich irre ich mich auch mal

ich kann es nicht leiden, wenn man mich auf diese weise daran erinnert :wink:

“alle constraints” - wie ich sagte - war ja auch nicht so ganz richtig

das mag wohl daran liegen, dass fuer check_constraints keine indizes aufgebaut werden muessen? das also beim daten laden zumindest kein soo großes performance problem darstellt … naja kommt auf den check an

naja vielleicht wird das ja in einer zukuenftigen version nochmal ueberarbeitet

Also, vielen Dank für die Antworten und die wiedergewonnene Sachlichkeit.
Fazit: ich mach’s mit constraint trigger und habe gelernt, daß ansonsten auch das Editieren der Reihenfolge der “Tabellenbefüllung” wie von Andreas beschrieben Abhilfe schaffen kann.
Thx @ all!

Wobei ich sagen muß, daß ich das Verhalten von PG da für suboptimal halte, die Check-Contraints könnten auch erst zum Schluß angelegt werden (ich war eigentlich fest davon überzeugt, daß dies so ist).

Mal sehen, was andere dazu meinen …

Andreas

Whow, Tom Lane ist mal wieder schnell:

> test=# create function check_b() returns bool as $$ declare s int; begin select into s sum(i) from b; if s > 3 then return true; else return false; end if; end;$$ language
+plpgsql;

> test=*# create table a (i int check(check_b()));

This is unsupported, and will fail in *many* situations not only
pg_dump.  A check constraint is only expected to examine the current
row of its table.

                        regards, tom lane

Andreas

Ist es also so, das man beim pg_restore die table contraints nicht deaktivieren kann (–disable-triggers greift NIHCT bei Table Constraints) ?

habe nämlich atm das gleiche Problem. muss constraint auf der table löschen, daaten importeiren danach alter table add constraint