foreign key referenzen auf row-ebene herausfinden

Hallo Leute,

ich habe eine Benutzertabelle. Darauf referenzieren mit einem foreign key ca. 20 Tabellen. Unter anderen wer eine bestimmte Row angelegt hat u.ä.
Nun wollte ich mal in die Runde Fragen wie ich mittels der System-kataloge wie pg_depend herausfinden kann welche row in welchen tabellen auf eine bestimmte Zeile in der Benutzertabelle referenziert.

Hintergrund: Ich habe quasi eine mandantenfähige Datenbank, die die Daten der einzelnen Mandanten in separate Schemas packt. Die zentrale Verwaltung des Systems und damit auch die der der Benutzer erfolgt aber in dem Schema “public”. Damit ich nun nicht alle Schemas einzeln mit den mir ja eigentlich bekannten Foreign-Key-Feldern durchsuchen muss, war meine Idee das komfortabel über die Systemkataloge zu machen. Erschwerend kommt nämlich dazu, dass die Anzahl der Mandanten flexibel ist und in naher Zukunft auch einige Hundert betragen kann. So würde der “Durchsuchungsvorgang”, der bei jedem Aufruf des PHP-Scriptes aufgerufen werden muss, unweigerlich zum Kollaps führen.

Nach einigen Tagen rumexperimentieren bin ich mit meinem Latein am Ende und hoffe dass Ihr mir Hinweise geben könnt.

Danke!
Patrick.

Das wird nicht gehen, zumindest ist mir kein Weg bekannt. IIRC sind solche Constraints intern als TRIGGER realisiert, die das prüfen.

Hintergrund: Ich habe quasi eine mandantenfähige Datenbank, die die Daten der einzelnen Mandanten in separate Schemas packt. Die zentrale Verwaltung des Systems und damit auch die der der Benutzer erfolgt aber in dem Schema “public”. Damit ich nun nicht alle Schemas einzeln mit den mir ja eigentlich bekannten Foreign-Key-Feldern durchsuchen muss, war meine Idee das komfortabel über die Systemkataloge zu machen. Erschwerend kommt nämlich dazu, dass die Anzahl der Mandanten flexibel ist und in naher Zukunft auch einige Hundert betragen kann. So würde der “Durchsuchungsvorgang”, der bei jedem Aufruf des PHP-Scriptes aufgerufen werden muss, unweigerlich zum Kollaps führen.

Warum mußt Du das mit PHP überhaupt prüfen?

Andreas

Vielen Dank für Deine schnelle Antwort.

Ok, verstehe. Diese Trigger müssten aber doch auch irgendwo stehen, oder?

Gut mit PHP hat das natürlich nicht direkt was zutun, aber da es sich um eine Webanwendnug handelt ist es PHP :slight_smile: Aber um die Antwort zu vervollständigen. Viele Referenzen bedeuten bestimmte Rechte auf den entsprechenden Eintrag. Konkret handelt es sich meistens um Freigaben, die fast jedesmal von dem PHP-Script angezeigt werden.

ciao!

naja … es gibt ja den guten alten katalog information_schema …

da gibt es ein paar interessante views:

  • constraint_table_usage … da findet man welche constraints in welchen tabellen verwendet werden
  • constraint column_usage … da findet man die felder die in constraints (auch FK) verwendet werden …

in weiteren views wird man auch finden, welche der verwendeten felder in constraint_column_usage PKs der tabellen sind … so weiss man von wo nach wo die constraints referenzieren

diese infos kann man dann hernehmen um sich queries zu generieren die nachschauen “welche rows von table a nach table b” referenzieren …

einfach mal reinschauen … die views sind sehr simpel …

Na sicher doch.

test=# \df+ pg_catalog."RI_FKey_cascade_del"
List of functions
-[ RECORD 1 ]-------+----------------------------------------
Schema              | pg_catalog
Name                | RI_FKey_cascade_del
Result data type    | "trigger"
Argument data types |
Owner               | postgres
Language            | internal
Source code         | RI_FKey_cascade_del
Description         | referential integrity ON DELETE CASCADE

Mal so als Beispiel.

Gut mit PHP hat das natürlich nicht direkt was zutun, aber da es sich um eine Webanwendnug handelt ist es PHP :slight_smile: Aber um die Antwort zu vervollständigen. Viele Referenzen bedeuten bestimmte Rechte auf den entsprechenden Eintrag. Konkret handelt es sich meistens um Freigaben, die fast jedesmal von dem PHP-Script angezeigt werden.

ciao!

Ich sehe aber noch immer nicht das konkrete Problem. Du willst für einen bestimmten User anzeigen, in welchen Tabellen auf ihn referenziert wird? Wenn Du einerseits viele Mandanten da in vielen Schemas hast, andererseits aber die User alle in einer Tabelle sind, ist das natürlich blöd. Entweder merkst Du Dir gleich in der User-Tabelle, für welche Schemas er gilt, oder Du verzichtest auf die Schemas und speicherst neben User auch noch den Mandanten in den Tabellen. Falls ich Dich korrekt verstanden habe…

Andreas

Ich zitiere hier noch mal den Fragesteller:

herausfinden kann welche row in welchen tabellen auf eine bestimmte Zeile in der Benutzertabelle referenziert.

Nun zeigst Du uns bitte, wie das geht.

Andreas

indem man sich die besagten views anschaut

und mal kurz drueber nachdenkt ?

schau doch einfach mal selbst was man alles so findet …

select * from information_schema.constraint_column_usage
hier sind die FK’s
also: table_name, column_name -> constraint_name (im prinzip die struktur des FK)

select * from information_schema.key_column_usage
hier die PK’s auf die FKs referenzieren
also: constraint_name -> column_name (meint hier den PK der Zieltabelle - beim Frager eben die user-table)

wenn man also die constraints und deren aufbau kennt - sollte es doch moeglich sein entsprechende queries zu generieren - die wie klassische lookups funktionieren ?

er kann ja sogar in diese queries die er generiert als constante column noch meta-daten unterbringen, damit er weiss welchen FK die aktuelle row meint …

Vorher sollte man sich die Frage aber auch genau anschauen.

und mal kurz drueber nachdenkt ?

Und vielleicht nicht nur kurz drüber nachdenken.



Andreas

Das, was symbiont meint, und was auch geht, wäre, mittels der Systemkataloge eine ganze Lawine von SQL-Befehlen zu generieren und diese auszuführen. Aber das sind dann Anzahl der Mandanten mal Anzahl der betroffenen Tabellen je Mandant. In diesen Abfragen wird dann jeweils geprüft, ob in den Tabellen eine bestimmte User-ID vorkommt. Schön ist was anderes.


Können User Mitglied mehrerer Mandanten sein?


Andreas

Die Frage war:

  • welche Rows
  • aus welchen Tables (source-table)
    -> auf user-table (bestimmte Row/User) referenzieren

also - nochmal fuer dich:
man ermittle all die constraints ueber die von mir genannten views, die auf user-table referenzieren …

man bastle sich z.B. eine schleife, die ein
union-selekt erzeugt über:
selekt source-table-name, … from source-table(n)
where … schleife (constraint-columns = user-table.columns)

generiert …

wenns nur ein bestimmter user sein soll, und man sowieso von nur einem PK-feld ausgehen darf wird die ganze geschichte absolut trivial

und nun klaer mich bitte auf, was ich “nicht verstanden” haben soll

haha … lawine :slight_smile:

wieder hilft nachdenken …

je nach fragestellung:

  1. möglichkeit -> unionselekt …

  2. möglichkeit -> join

  3. möglichkeit -> subselects

  4. möglichkeit -> lawine …

  5. abwarten, was der frager eigentlich erreichen will

fakt ist - information_schema hilft weiter
fakt ist - constraints bedingen indizes … das sollte also selbst bei einem union (auch eine billige operation) absolut kein thema fuer die DB sein

die komplexitaet dynamisch erzuegter SQLs war noch nie ein kriterium weils eh nicht dafuer gedacht ist dass es sich nochmal jemand anschaut, wenns funktionier *Smile

diese problem - vom frager beschrieben - laesst sich mit einem einzigen statement erledigen … er sollte selbst abschaetzen koennen wie gross das resultset wird … und was man damit noch so alles anstellen will

sortieren koennte dann schon sehr kostspielig werden - das war bisher aber auch garnicht thema …

vielleicht will er auch nur wissen WO welche referenziert werden … da helfen subselects mit exists
z.B. so (schematisch natuerlich)

String qry = null
for (source_table_name:sources) {
if (qry != null) qry += union
qry += select ‘source_table_name’ … from user_table u
where exists (select true from source_table_name s where s.id_user = u.id_user)
}

Das man das Problem eleganter lösen kann.

Andreas

das kann man immer :slight_smile:

mach doch mal einen vorschlag …

also halten wir mal fest

  • es gibt eine einzige user-table

  • das php-script whatever zeigt gerade infos füer einen mandante an ?!

ich sehe an dieser stelle nicht wo du all das brauchst … wenn es nur eine user-table gibt, dann sollten die user-ids doch eindeutig fuer alle mandanten sein

das script selektiert ja bereits in eine tablle die es kennt - also die des mandanten ? und die kann doch wiederum nur in diese eine user-table referenzieren …

also irgendwas verheimlichst du uns noch :slight_smile:

Wer lesen kann, ist klar im Vorteil.

Andreas

haha …

ja wo ist denn nun eine loesung ?

bisher kam nur “geht nicht” …

ach ja … 5€ bitte

Hallo Ihr beiden!

also erstmal danke für Eure rege Anteilnahme.

symbiont: Vielen Dank für Deinen Hinweis auf das information_schema… Ja ja, der Wald mit seinen Bäumen. Aber ich verheimliche nichts :wink:

Ich konstruiere mal ein ähnliches, weltliches Beispiel für meine Problemsituation.

Nehmen wir an wir haben eine Kalenderapplication wie Google. Für jeden Teilnehmer macht die DB-App ein eigenes Schema auf. Die gesamte Benutzerverwaltung liegt in public.
Jetzt füge ich einen andere Benutzer in meinem Schema zu einem Termin hinzu.
Und mir geht es darum alle Schemas schnell und unkompliziert danach abzufragen ob irgendwo eben durch dieses Hinzufügen eine Referenz auf meine Benutzertabelle entstanden ist. Damit könnte das Script eben darstellen bei welchen Terminen der aktuelle Benutzer überall noch dabei ist.

Ich denke die Lösung ist irgendwie mit einer Mischung aus information_schema und pg_catalog zu regeln. Da bin ich allerdings noch nicht so schrittfest.

Ich freue mich auf Eure weiteren Antworten. Wenn ich was rausfinde wirds natürlich auch gleich hier gepostet.

ciao!

Eben das würde ich überdenken, die Alternative nannte ich bereits.

Andreas

ohne dich necken zu wollen … es waere schoen wenn du uns einfach nochmal mit der nase drauf stoesst …

warum gibts dann kein gemeinsames kallender modell ?

also EINEN kallender fuer alle ?

hmm