Partitionierung - Dynamische Constraint-Check's

Okay hab mal einen neuen Thread eröffnet, da es eig. ein eig. Thema ist. Und zwar hab ich gerade meine Datenbank in eine Partitionierte Datenbank um strukturiert.

Die große Frage: Funktionen in Constraint-Checks?


Ein Trigger regelt bei mir sämtliche Insert-Befehle, und sortiert diese ordentlich in die Partitionen ein. Erstellt ggf. auch eine neue Partition.

Jetzt möchte ich natürlich sinnvollerweise mit Hilfe der Constraint-Check’s die Select Anfragen gezielt steuern.

Dafür gäbe es nun zwei Möglichkeiten. Einmal Statisch oder Dynamisch.


Zu den Statischen:
Sie werden einmalig beim erstellen der PartitionsTabellen definiert.

ALTER TABLE userstats_part1
  ADD CONSTRAINT userid_intb CHECK ( id_user >= 0 AND id_user < 1000 );
ALTER TABLE userstats_part2
  ADD CONSTRAINT userid_intb CHECK ( id_user >= 1000 AND id_user < 2000 );
ALTER TABLE userstats_part3
  ADD CONSTRAINT userid_intb CHECK ( id_user >= 2000 AND id_user < 3000 );

Okay. Jetzt habe ich eindeutig festgelegt, in welcher Tabelle welcher User zu finden ist. Nach ein paar Tests hab ich festgestellt funktioniert auch einwandfrei.

Probleme hab ich dann bei folgendem bekommen.


Der Ansatz - Dynamischen Constraint-Check’s

Nun ja, das reicht mir aber nicht. Gehe ich mal von aus, dass eine partitionstabelle wächst und wächst möchte ich die Query-Anfragen wieder erneut umleiten in eine neue (aktuellere) PartitionsTabelle und sozusagen die anderen Ignorieren.


Daraus folgt also. Meine user-tabelle erhält ein neues Feld, das genau auf die aktuelle PartitionsTabelle zeigt. zb. partid 1

Daraus folgt wiederum, dass ich in meinem Constraint-Checks mit einer Funktion arbeiten muss. Diese Prüft dann, ob die aktuelle Tabelle die richtige dafür ist.
So wie es aussieht werden Funktionen generell in Constraint-Checks Irgnoriert… :angry: Entweder ich hab hier einen Denkfehler oder es gibt hier Sicherheitproblematische Aspekte.

Hier mal ein wenig code.

ALTER TABLE userstats_part1
  ADD CONSTRAINT userid_fortb CHECK (check_select_for_plr(id_user, 1));

ALTER TABLE userstats_part2
  ADD CONSTRAINT userid_fortb CHECK (check_select_for_plr(id_user, 2));

ALTER TABLE userstats_part3
  ADD CONSTRAINT userid_fortb CHECK (check_select_for_plr(id_user, 3));

Okay und natürlich die Funktion.

CREATE OR REPLACE FUNCTION check_select_for_plr(userid integer, partid integer)
  RETURNS BOOLEAN AS
$BODY$
BEGIN
    RETURN (SELECT id_part FROM users WHERE id_user=$1 AND id_part=$2);
END;
$BODY$
  LANGUAGE 'plpgsql';

Okay… hoffe man kann mir ein Tipp geben.

SO hab mal in ein paar Büchern nachgeschlagen und wie folgt was gefunden:

Gemäß dem SQL-Standard sind auch komplexere Integritätsbedingunge, die sich auf mehreren Relationen beziehen, möglich. In gewisser Weise stellen natürlich die foreign key-klauseln schon scholche Integritätsbedingen dar, da sie sich immer auf zwei Relationen beziehen. Ein allgemeines Beispiel einer Integritätsbedingung über mehrere Relationen ist nachfolgend für die Relation prufen formuliert:


crfeate table prüfen(
   MatrNr..
   VorlNr...
  PersNr...
  Note ...
  primary key (MatrNr,VorlNr)
constraint VorherHören
        check ( exists (select * from hören h where ... ))
);

Okay hab sowas nun auch in postgresql versucht - und ich erhalte die fehlermeldug:

ERROR: cannot use subquery in check constraint

********** Fehler **********

ERROR: cannot use subquery in check constraint
SQL Status:0A000

D.h. PostgreSQL verwendet kein SQL-Standard?? :confused:

Sagen wir es mal so: es ist eine dokumentierte Einschränkung.

Andreas

Okay…sagen wir mal es gibt keine query’s in constraint checks. Soweit gut. Funktionen kann ich ohne probleme hier einbauen. Nur hab ich das problem, egal was die funktion zurückgibt, es ist immer true!!

egal ob ich meine oder andere FUnktionen nutze.

ALTER TABLE userstats_part3
  ADD CONSTRAINT userid_in CHECK ( id_player > 100 AND round(3.2) = 1);
ALTER TABLE userstats_part3
  ADD CONSTRAINT userid_in CHECK ( round(3.2) = 1);

“round(3.2) = 1” > ist hier imm wahr! Da kann doch was nicht stimmen.?

Oder kann man generell auch keine funktionen hier verwenden.

Ja, round(3.2) ist ‘wahr’ im Sinne eines boolschen Wertes. Eine Check-Condition gibt einen Boolschen Wert. Du suchst etwas wie:

test=*# create table c  (i real, check (case when round(i) = 1 then 't'::bool else 'f'::bool end));
CREATE TABLE
test=*# insert into c values (1.4);
INSERT 0 1
test=*# insert into c values (1.6);
ERROR:  new row for relation "c" violates check constraint "c_i_check"
test=!#

Andreas