[postgresql] sub-supertypes en arc

[JaQ]

Ik ben bezig om een mooi databaseje op te bouwen in postgresql, maar nu loop ik
toch wel tegen een probleem op.

de situatie is als volgt:
ik sla een schedule op, met bijbehorende schedule_regels (master detail, niets aan
het handje dus). Vervolgens staat op zo'n schedule_regel een "object". Zo'n object
is of een commando, of een file of een playlist. Feitenlijk is object dus een
supertype en zijn commandos, files en playlists subtypes van het supertype object.

Als ik dit zo zou implementeren, zou ik een objecttabel krijgen van 2 kolommen
(namelijk ID) en een type-aanduiding, want verder hebben ze helemaal niets wat
overeenkomt. Door de hoeveelheid gegevens die ik wil gaan opslaan, wordt die
tabel dan wel heel erg groot (en dan is postgresql niet zo'n beste oplossing meer).
Verder zijn commando's, files en playlist dermate anders van aard, dat ik ze ook
niet wil generaliseren.

Normaalgesproken werk ik met oracle en dan kan je dus een "arc" gebruiken. Zo'n arc is dus een foreign key over meerdere tabellen. Maar volgens mij kent postgresql die niet (of heb ik dat nu verkeerd).

Ben ik nu tegen een limitatie van postgresql opgelopen, of zit ik gewoon een foute
ontwerpbeslissing te maken?

[ACM]

DrFrankenstoner schreef op 10 May 2003 @ 02:43:
Door de hoeveelheid gegevens die ik wil gaan opslaan, wordt die
tabel dan wel heel erg groot (en dan is postgresql niet zo'n beste oplossing meer).

Sais who?
Er zijn databases in postgresql van meerdere gigabytes... Sterker nog, ikzelf heb een testdatabaseje in postgres van zo'n 4GB en dat werkt prima.

Ben ik nu tegen een limitatie van postgresql opgelopen, of zit ik gewoon een foute ontwerpbeslissing te maken?

Ik heb eerlijk gezegd nog niet helemaal door wat je wilt bereiken en wat het probleem is

Je hebt drie verschillende type objecten en die hebben allemaal een id, en dan verder niks gemeen?

Je zou een supertype-tabel kunnen maken 'Object' met enkel een identifier kolom en dan verder drie subtype-tabellen kunnen maken die van Object overerven en vervolgens al je extra kolommen hebben.
Dit kan in Postgresql doordat het een Object-Relational database is. Je bespaart er een paar loze kolommen mee (type, duplicaat van het id) maar ik zou eerlijk gezegd niet weten hoe goed het werkt.
Waarbij ik bij dat laatste niet de technische correctheid bedoel, dat zit wel goed in postgres
Ik bedoel het volgende:
als je een koppeling naar Object opslaat en vervolgens wil weten of het bijbehorende object een file, commando of playlist is zou ik niet weten hoe makkelijk je dat kan achterhalen.
Als je toch al van plan was dat in drie query's op te halen (je kon het met jouw idee toch ook al niet zomaar met 1 query ophalen) dan is dat weer wat minder problematisch.

[ Voor 4% gewijzigd door ACM op 10-05-2003 10:41 ]

[JaQ]

Zodra een postgresql database wat groter wordt (500 M +) en ik doe een flinke
stresstest (meerdere users, zware queries en insert, update en delete acties) dan
schiet de load op mijn machine omhoog. (testmachine is een 1,0 Ghz PIII met 1 GB
geheugen, op Debian) Uiteraard is postgresql niet het enige wat erop staat, maar
toch. Het kan dus zijn dat ik postgresql niet goed heb geconfigureerd, het kan ook
zijn dat postgresql gewoon wat zwaarder is. Dat vind ik gewoon wat minder
relevant. Voor grote lappen data (in dit geval voornamelijk logfiles) kies ik in dit
geval voor mysql, vooral omdat ik dan toch wat minder op de integriteit hoef te
letten.

inheritence is in dit geval overigens niet de oplossing. Als ik namelijk een tabel
object met alleen een kolom ID zou creeren en vervolgens deze laat erven door de
drie overige tabellen, dan krijg ik dus lege velden in deze drie (en dat is dus niet
netjes).

Wat ik dus eigenlijk wil is een verplichte foreign key (oftewel het "object" moet
bestaan) in een van de 3 andere tabellen (ja, het id moet dan uitgedeeld worden
m.b.v. 1 sequence).

Na wat rondgeneust te hebben door de documentatie denk ik dat een check
constraint in dit geval een betere oplossing is. (een check constraint met een
functie die dus controleerd of het ID in een van deze 3 tabellen voorkomt a.h.v. een
line_type of zo). Ik ben zelf nog een aardige rookie met postgresql, maar ik heb
wel veel ervaring met Oracle. (moet zeggen dat ik verder tot op heden aardig
onder de indruk ben van Postgresql, maar goed).

Als je trouwens toch veel ervaring hebt met Postgresql, dan kan je vast wel
vertellen of dit ook waar is: klopt het dat je bij het restoren van een backup geen
commandline password mee kan geven (zoals bijvoorbeeld bij mysql), waardoor
je een recovery niet kan scripten (tenzij je OS authenticatie gebruikt, maar dat
vind ik nogal een security risk, niet zo charmant zeg maar)

[xoror]

heb je je pgsql wel goed getuned? shared mem omhoog gekrikt in zowel postgresql.conf als in je kernel ? een vacuum gedaan ? fsm settings willen ook wel helpen. ik heb zelf een aantal test db's van miljoenen records en pgsql houdt gewoon stand hoor met een beetje stress test.

als je bepaalde user automatisch pwd mee wil laten geven , kan je dat ook in je pg_hba.conf doen. daar kan je instellen dat ie bepaalde user vanaf bepaalde ip moet trusten. Normaal gesproken is de user postgres de owner van de db. als je een restore doet moet je je script laten su'en naar de user postgres. dan is dat wachtwoord ook geen probleem meer.

je kan ook restoren met psql dbnaam < dump.sql , dat gebruik ik zelf altijd. daar weet ik zeker van dat je een pwd kan setten.

[JaQ]

heb je je pgsql wel goed getuned? shared mem omhoog gekrikt in zowel postgresql.conf als in je kernel ? een vacuum gedaan ? fsm settings willen ook wel helpen.

ik heb wel wat settings veranderd in postgresql.conf, maar niet genoeg dus.
Tenminste, als ik jullie reacties zo peil, dan is de uitkomst van mijn test niet
vergelijkbaar met die van jullie. Tijd voor HOWTO's en docs dus.

als je bepaalde user automatisch pwd mee wil laten geven , kan je dat ook in je pg_hba.conf doen. daar kan je instellen dat ie bepaalde user vanaf bepaalde ip moet trusten.

Dit gebruik ik idd lokaal wel voor het ontwikkelen, maar dat vind ik eigenlijk niet zo
leuk in een productieomgeving. Het lijkt om een of andere reden onveilig. (of
minder veilig in ieder geval). Ik heb ook een aantal mails in de postgresql mailing
list gevonden waarin de desgin-desission om bij een restore altijd een password
te vragen gerechtvaardigd wordt (security), maar dat betekend natuurlijk niet
dat het voor mij handig is.

Uiteraard weet ik dat je kan restoren door gewoon een dump te voeren aan psql,
maar ik hoopte dat het ook mogelijk was met de bijgeleverd restore tools. Anyway,
dan maar een dump voeren (Arkeia kan gelukkig heel netjes met pipes omgaan)

[EfBe]

Een foreign key over meerdere tabellen? Hoe ziet dat er dan uit, want dat lijkt me semantisch wat raar in deze context, want je wilt een relatie leggen tussen entities op basis van een VALUE in een attribute, niet over attributes zelf. Dat kun je dus NOOIT in een foreign key plaatsen. Een check constraint is het enige dat rest inderdaad, alle andere database constructies zullen je niet helpen (een insert trigger zou ook nog helpen, maar dan alleen een INSTEAD OF insert trigger)

Het probleem dat jij aanroert is een van de mankos van het relationele model zoals we dat nu kennen. (en in mijn ogen is dit probleem zo levensgroot dat er wel wat aan mag gebeuren door de grote database vendors). Zie mn blog voor een (engels) artikel hierover (zie .sig))

[ Voor 14% gewijzigd door EfBe op 10-05-2003 11:42 ]

[xoror]

psql kan gewoon een dump van pg_dump restoren hoor...

[JaQ]

EfBe schreef op 10 May 2003 @ 11:41:
Een foreign key over meerdere tabellen? Hoe ziet dat er dan uit, want dat lijkt me
semantisch wat raar in deze context, want je wilt een relatie leggen tussen entities
op basis van een VALUE in een attribute, niet over attributes zelf. Dat kun je dus
NOOIT in een foreign key plaatsen. Een check constraint is het enige dat rest
inderdaad, alle andere database constructies zullen je niet helpen (een insert trigger
zou ook nog helpen, maar dan alleen een INSTEAD OF insert trigger)

Ik zal het proberen te tekenen:

code:

   [-------------------]        
   [schedule_lines]
   [-------------------]
              |
             /|\
  [--------------------]
  [      objecten     ]
  [    [P]  [F]  [C]   ]
  [--------------------]

Zo ongeveer. waar P dus voor Playlists, F voor FIles en C voor Commands staat.
(en Playlist ook nog eens recursief zijn).

Van metalink:

The arc provides a way of defining mutually exclusive columns whose values
depend on the existence of values in another table.
Validation of foreign key arcs is enforced by the Table API using pre-insert and
pre-update triggers. Checks are made to ensure that the arc is valid, that a
value is provided in at least one of the columns in the arc, and that the value
provided exists in the referenced primary key column.

[ Voor 20% gewijzigd door JaQ op 10-05-2003 11:51 ]

[ACM]

xoror schreef op 10 mei 2003 @ 11:44:
psql kan gewoon een dump van pg_dump restoren hoor...

Niet als het in (compressed) tar formaat is toch?

DrFrankenstoner schreef op 10 mei 2003 @ 11:47:
Van metalink:

Je zou dan zelf nog checkconstraints/triggers kunnen maken, wordt het natuurlijk niet sneller van, magoed

[JaQ]

Ik heb een view gemaakt waar ik alle ID's in list. Vervolgens een functie die controleerd of een
input variabele (ID dus), in deze view voorkomt, zo ja, true, zo nee, false. Deze functie weer in
een check constraint aangeroepen. Dit werkt. Het is misschien niet al te fraai, maar het werkt.

[EfBe]

DrFrankenstoner schreef op 10 May 2003 @ 11:47:
[...]
Ik zal het proberen te tekenen:

code:

   [-------------------]        
   [schedule_lines]
   [-------------------]
              |
             /|\
  [--------------------]
  [      objecten     ]
  [    [P]  [F]  [C]   ]
  [--------------------]

Zo ongeveer. waar P dus voor Playlists, F voor FIles en C voor Commands staat.
(en Playlist ook nog eens recursief zijn).

Ja ik snap het, en gezien de uitleg dus inderdaad een truukje dat onder water met triggers wordt geregeld. Ik kon in de help van Oracle 9i niet iets vinden over 'arc', maar het is wellicht ergens anders onder gefiled in die fantastische index

Nijssen/Halpin schrijven overigens voor dat je supertypes vervangt door de subtypes en dan tables genereert, dus via het principe van de type column. Er zijn meerdere oplossingen mogelijk overigens, maar geen van allen is echt fraai, alleen object oriented databases kunnen dit oplossen (door het supporten van abstract classes en polymorphism, wat je in feite aan het bouwen bent)

[JaQ]

arc komt uit Oracle Designer, in de help van de database ga je dus niets vinden. Op metalink
kan je wel veel vragen vinden van mensen die het principe niet snappen (maar goed, het
blijft metalink ;-) )

[EfBe]

ah. Vandaar. Naja, ik dacht even dat Oracle een feature zou hebben die Sqlserver bv ontbeerde, dat bleek niet zo.