on delete no action deferrable

[Stefan Froehlich]

Mein erster Versuch, referentiell abhaengige Daten in der "falschen"
Reihenfolge zu loeschen, scheitert klaeglich:

| $ psql
| => create table a (a integer not null, primary key (a));
| NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
| CREATE TABLE
| => create table b(b integer references a(a) on delete no action deferrable);
| CREATE TABLE
| => insert into a values (1);
| INSERT 0 1
| => insert into b values (1);
| INSERT 0 1
| => set constraints all DEFERRED ;
| SET CONSTRAINTS
| => begin;
| BEGIN
| => delete from a;
| ERROR: update or delete on table "a" violates foreign key constraint "b_b_fkey" on table "b"
| DETAIL: Key (a)=(1) is still referenced from table "b".

Nun sagt mir das Handbuch:

| NO ACTION
|
| Produce an error indicating that the deletion or update would create
| a foreign key constraint violation. If the constraint is deferred,
| this error will be produced at constraint check time if there still
| exist any referencing rows. This is the default action.

Inzwischen habe ich zwar herausgefunden, dass der von mir gewuenschte
Effekt eintritt, wenn ich die Constraint auf "INITIALLY DEFERRED" setze.
Ich verstehe aber immer noch nicht, was ich in der obigen Sequenz falsch
mache.

Servus,
Stefan

--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike

Stefan - die Bezeichnung der feinen Leichtigkeit.
(Sloganizer)

[Bastian Blank]

Stefan Froehlich wrote:
>| => set constraints all DEFERRED ;
>| SET CONSTRAINTS
>| => begin;
>| BEGIN

> Ich verstehe aber immer noch nicht, was ich in der obigen Sequenz falsch
> mache.

Die Dokumentation nicht komplett gelesen. Dort heisst es:
| SET CONSTRAINTS -- set constraint check timing for the current
| transaction

Die Transaktion beginnt aber mit dem "BEGIN".

Bastian

[Stefan Froehlich]

On Mon, 27 Aug 2012 19:30:16 Bastian Blank wrote:
> >| => set constraints all DEFERRED ;
> >| SET CONSTRAINTS
> >| => begin;
> >| BEGIN
> > Ich verstehe aber immer noch nicht, was ich in der obigen Sequenz falsch
> > mache.

> | SET CONSTRAINTS -- set constraint check timing for the current
> | transaction

> Die Transaktion beginnt aber mit dem "BEGIN".

Ok, das ist ein echtes Argument. Allerdings ein bisserl bloed - ich haette
gerne fuer eine Applikation das default-Verhalten umgestellt, ohne das in
den Tabellen festzuschreiben und idealerweise auch ohne bei _jeder_
Transaktion "SET CONSTRAINTS..." schreiben zu muessen. Ein Defaultwert fuer
die aktuelle Sitzung halt. Geht das denn tatsaechlich nicht?

Servus,
Stefan

--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike

2012! Das Jahr des zerknallten Erfolgs von Stefan.
(Sloganizer)

[Peter Schneider]

PostgreSQL?

In Oracle wäre es so, daß wenn Du den Constraint zwar mit DEFERRABLE
deklarierst er zwar grundsätzlich DEFERRABLE ist, aber per Default INITIALLY
IMMEDIATE ist. Willst Du ihn INITIALLY DEFFERED mußt Du das explizit bei der
Definition so angeben, wie Du ja inzwischen auch herausgefunden hast.

Außerdem solltest Du AUTOCOMMIT ausschalten, da in psql ansonsten IMMEDIATE
und DEFERRED für Dich keinen Unterschied ergibt.

Im übrigen möchte ich vor deferred constraints warnen: das ist Schmarrn und
braucht kein Mensch (IMHO). Das macht nur Fehlersuche/Debugging unnötig
kompliziert. Fehler sollten meiner Erfahrung nach grundsätzlich so früh wie
möglich und pro verursachendem Datensatz geflagged werden.

Gruß
Peter

--
Climb the mountain not to plant your flag, but to embrace the challenge,
enjoy the air and behold the view. Climb it so you can see the world,
not so the world can see you. -- David McCullough Jr.

[Stefan Froehlich]

On Tue, 28 Aug 2012 14:07:58 Peter Schneider wrote:
> > | $ psql
> > [...]

> PostgreSQL?

Jupp. Sorry, wenn ich das nur da oben so klein angedeutet habe.

> In Oracle wäre es so, daß wenn Du den Constraint zwar mit
> DEFERRABLE deklarierst er zwar grundsätzlich DEFERRABLE ist, aber
> per Default INITIALLY IMMEDIATE ist. Willst Du ihn INITIALLY
> DEFFERED mußt Du das explizit bei der Definition so angeben, wie
> Du ja inzwischen auch herausgefunden hast.

Wobei es in Oracle (wenigstens habe ich das, was mir Google
versehentlich dazu ausgespuckt hat, so interpretiert) auch die
Moeglichkeit gaebe, die aktuelle Session auf DEFERRED zu stellen -
genau das, was ich suchen wuerde.

> Im übrigen möchte ich vor deferred constraints warnen: das ist
> Schmarrn und braucht kein Mensch (IMHO). Das macht nur
> Fehlersuche/Debugging unnötig kompliziert. Fehler sollten meiner
> Erfahrung nach grundsätzlich so früh wie möglich und pro
> verursachendem Datensatz geflagged werden.

Jein.

Ich habe eine nicht ganz unkomplizierte Datenstruktur (sagen wir
einmal, rund 150 Tabellen), deren Eintraege in letzter Konsequenz
von jeweils einem einzelnen Datensatz in einer der Tabellen
abhaengen. Alle Jubeljahre wird so etwas einmal geloescht - und
genau dabei sind mir die Constraints jetzt um die Ohren geflogen,
weil es darunter zirkulare Abhaengigkeiten gibt [1].

Notgedrungen ist die Datenbank jetzt auf INITIALLY DEFERRED
umgestellt, damit laeuft's. Lieber waere es mir anders gewesen,
aber Hauptsache, es geht ueberhaupt.

Servus,
Stefan

[1] beispielsweise in der Art von (unsauber hingetippt, Fehler bitte
in Gedanken korrigieren):

| M (id integer);
| A (
| id integer,
| id_m integer references M(id) on delete cascade
| );
| A_to_A (
| id_a_from integer references A(id) on delete cascade,
| id_a_to integer references A(id) on delete restrict
| );

wobei das referenzierte A nicht geloescht werden darf, solange das
referenzierende A noch am Leben ist. Ein pauschales "DELETE FROM m"
loescht die As jedoch beliebig und bricht dann mitunter wegen
Verletzung der referentiellen Integritaet ab. Finde ich irgendwie
ein wenig eigenwillig.

--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike

Rundum gelungen! Stefan und alles ist gebissen!
(Sloganizer)

[Peter Schneider]

Am 28.08.2012 17:35, schrieb Stefan Froehlich:
> On Tue, 28 Aug 2012 14:07:58 Peter Schneider wrote:
>>> | $ psql
>>> [...]
>
>> PostgreSQL?
>
> Jupp. Sorry, wenn ich das nur da oben so klein angedeutet habe.
>
>> In Oracle wäre es so, daß wenn Du den Constraint zwar mit
>> DEFERRABLE deklarierst er zwar grundsätzlich DEFERRABLE ist, aber
>> per Default INITIALLY IMMEDIATE ist. Willst Du ihn INITIALLY
>> DEFFERED mußt Du das explizit bei der Definition so angeben, wie
>> Du ja inzwischen auch herausgefunden hast.
>
> Wobei es in Oracle (wenigstens habe ich das, was mir Google
> versehentlich dazu ausgespuckt hat, so interpretiert) auch die
> Moeglichkeit gaebe, die aktuelle Session auf DEFERRED zu stellen -
> genau das, was ich suchen wuerde.

Ja, das ist richtig und möglich, aber: man sollte die Defaults so setzen, daß
sie einem taugen. Session Defaults explizit zu setzen ist Stückwerk.

>> Im übrigen möchte ich vor deferred constraints warnen: das ist
>> Schmarrn und braucht kein Mensch (IMHO). Das macht nur
>> Fehlersuche/Debugging unnötig kompliziert. Fehler sollten meiner
>> Erfahrung nach grundsätzlich so früh wie möglich und pro
>> verursachendem Datensatz geflagged werden.
>
> Jein.
>
> Ich habe eine nicht ganz unkomplizierte Datenstruktur (sagen wir
> einmal, rund 150 Tabellen), deren Eintraege in letzter Konsequenz
> von jeweils einem einzelnen Datensatz in einer der Tabellen
> abhaengen. Alle Jubeljahre wird so etwas einmal geloescht - und
> genau dabei sind mir die Constraints jetzt um die Ohren geflogen,
> weil es darunter zirkulare Abhaengigkeiten gibt [1].

Ich will hier keinen Schwanzvergleich vom Zaun brechen, aber: 150 Tabellen
sind Kinderfasching. Wirklich und ehrlich. Ich arbeite mit DBs >> 2000
Tabellen. Wenn Du ganz zentrale Tabellen hast, und FKs hast, die sich durch
die halbe DB vererben, dann kommst Du eh nicht drum herum, für Löschaktionen
auf diesen zentralen Tabellen eine explizite Löschprozedur zu schreiben, die
dreierlei leistet:

- die FKs rekursiv zu verfolgen (ob zirkulär, hierarchisch, whatever: die
Prozedur muß auf dem Data Dictionary arbeiten, und mit dem klarkommen, was sie
vorfindet)

- es müssen für die Löschaktione exklusive Table Locks gesetzt werden, und
Fehlschläge dabei mit einem multiple Retry und Error Handler korrekt
verarbeitet werden

- Du mußt in dieser Löschprozedur den Fortschritt loggen und Fehler korrekt
behandeln und protokollieren.

> Notgedrungen ist die Datenbank jetzt auf INITIALLY DEFERRED
> umgestellt, damit laeuft's. Lieber waere es mir anders gewesen,
> aber Hauptsache, es geht ueberhaupt.

Zu glauben, daß man aufgrund der Constraintdefinition oder Sessioneinstellung
für deferred constraints aus einer für die DB/Applikation ganz zentralen
Tabelle per simplem DELETE Statement löschen kann ist eine Illusion, aber
<altklug> das wirst Du noch selber erfahren </altklug> ;-)

[Stefan Froehlich]

On Tue, 28 Aug 2012 18:19:57 Peter Schneider wrote:
> > Wobei es in Oracle (wenigstens habe ich das, was mir Google
> > versehentlich dazu ausgespuckt hat, so interpretiert) auch die
> > Moeglichkeit gaebe, die aktuelle Session auf DEFERRED zu stellen -
> > genau das, was ich suchen wuerde.

> Ja, das ist richtig und möglich, aber: man sollte die Defaults so setzen,
> daß sie einem taugen. Session Defaults explizit zu setzen ist Stückwerk.

Angesichts des fuer mich leicht verblueffenden Verhaltens (ich
haette nie damit gerechnet, dass FK-Constraints _innerhalb_ eines
Statements schlagend werden koennen) stelle ich nun ohnehin das
Default-Verhalten um.

> 150 Tabellen sind Kinderfasching. Wirklich und ehrlich. Ich
> arbeite mit DBs >> 2000 Tabellen.

Und wenn man SAP hat, sind es noch mehr, klar. Dann haette ich aber
auch ein ganzes Team von Leuten, das mir solche laestigen Arbeiten
abnimmt...

Die 150 Tabellen (das sind jetzt nur die, wo sich eine hierarchische
Struktur komplett durchzieht) reichen mir jedenfalls aus, um genau
das:

> - die FKs rekursiv zu verfolgen (ob zirkulär, hierarchisch,
> whatever: die Prozedur muß auf dem Data Dictionary arbeiten, und
> mit dem klarkommen, was sie vorfindet)

> - es müssen für die Löschaktione exklusive Table Locks gesetzt
> werden, und Fehlschläge dabei mit einem multiple Retry und Error
> Handler korrekt verarbeitet werden

> - Du mußt in dieser Löschprozedur den Fortschritt loggen und
> Fehler korrekt behandeln und protokollieren.

...nicht mehr haendisch tun zu wollen. Die Datenbank selbst hat
schon ein paar Jahre mehr am Buckeln, nur systembedingt bislang ohne
foreign keys - und das Loeschen grosser Datenbloecke war immer eine
der fehleranfaelligsten Geschichten.

Genau _dafuer_ will ich "CASCADE" ja letztendlich haben.

> > Notgedrungen ist die Datenbank jetzt auf INITIALLY DEFERRED
> > umgestellt, damit laeuft's. Lieber waere es mir anders gewesen,
> > aber Hauptsache, es geht ueberhaupt.
>

> Zu glauben, daß man aufgrund der Constraintdefinition [...] aus

> einer für die DB/Applikation ganz zentralen Tabelle per simplem
> DELETE Statement löschen kann ist eine Illusion, aber <altklug>
> das wirst Du noch selber erfahren </altklug> ;-)

Hm. Klingt nicht nach guten Aussichten.

Servus,
Stefan

--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike

Stefan - welch erotischer Gedanke.
(Sloganizer)

[Peter Schneider]

Am 31.08.2012 08:49, schrieb Stefan Froehlich:
> On Tue, 28 Aug 2012 18:19:57 Peter Schneider wrote:

[...]

>> Zu glauben, daß man aufgrund der Constraintdefinition [...] aus
>> einer für die DB/Applikation ganz zentralen Tabelle per simplem
>> DELETE Statement löschen kann ist eine Illusion, aber <altklug>
>> das wirst Du noch selber erfahren </altklug> ;-)
>
> Hm. Klingt nicht nach guten Aussichten.

Du wirst unweigerlich in massive Locking-/Deadlockprobleme reinlaufen.

Bei vielen beteiligten Tabellen und vielen Usern ist es unausweichlich, daß
immer irgendeiner einen Datensatz bearbeitet und damit gelockt hat und damit
Deine umfangreiche Löschaktion blockiert. Die Datensätze aber, die Deine
Löschaktion bereits locken konnte, stehen nun für User zum Locken nicht mehr
zur Verfügung, deren Versuche zu bearbeiten hängen dann halt. Ab einer
gewissen kritischen Größe potenziert sich das dann.

[Stefan Froehlich]

On Fri, 31 Aug 2012 12:09:24 Peter Schneider wrote:
> >> Zu glauben, daß man aufgrund der Constraintdefinition [...] aus
> >> einer für die DB/Applikation ganz zentralen Tabelle per simplem
> >> DELETE Statement löschen kann ist eine Illusion, aber <altklug>
> >> das wirst Du noch selber erfahren </altklug> ;-)

> > Hm. Klingt nicht nach guten Aussichten.

> Du wirst unweigerlich in massive Locking-/Deadlockprobleme reinlaufen.

Schauen wir einmal. Das Locking existierte ja bereits zuvor, aus
naheliegenden Gruenden. Das zu uebernehmen ist das kleinere Problem... (und
gluecklicherweise ist es in meinem Fall auch so, dass das, was geloescht
werden soll, bereits durch andere Massnahmen vom Produktivbetrieb
ausgeschlossen wurde; den Usern Daten unterm Hintern wegziehen darf ohnehin
nicht vorkommen. Wenn also nicht gerade ich mich selbst auf der CLI
irgendwo bloed herumspiele, darf da nicht viel passieren. Mal sehen).

Servus,
Stefan

--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike

Stefan - die beschissenste Veralberung der gelassenen Symbiose.
(Sloganizer)