[PGSQL] Ordered table
Ik heb het nu redelijk werkend met procedures.
language_get_next_ordinal(): de volgende ordinal in een table. (wellicht enigzins gevaarlijk bij hoge volume inserts, maar dit is niet aan de orde).
language_move(id, x): Verplaats record met id id naar positie x
language_reorder(): Haal gaps uit de tabel. (Bijvoorbeeld na een delete of na een update).
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
CREATE FUNCTION language_get_next_ordinal() RETURNS integer
AS $$
DECLARE
p_next_ordinal integer;
BEGIN
SELECT count(id)
INTO p_next_ordinal
FROM vdven.language;
RETURN p_next_ordinal;
END;
$$
LANGUAGE plpgsql;
CREATE FUNCTION language_move(f_id integer, f_new_pos integer) RETURNS integer
AS $$
DECLARE
p_rec record;
p_old_pos integer;
p_max_pos integer;
p_done integer := 0;
BEGIN
SELECT ordinal
INTO p_old_pos
FROM vdven.language
WHERE id = f_id;
SELECT COUNT(id)-1
INTO p_max_pos
FROM vdven.language;
IF p_old_pos = f_new_pos THEN
RETURN 0;
END IF;
IF p_max_pos < f_new_pos THEN
RETURN -1;
END IF;
UPDATE vdven.language
SET ordinal = p_max_pos+1
WHERE id = f_id;
IF p_old_pos > f_new_pos THEN
FOR p_rec IN
SELECT id,ordinal
FROM vdven.language
WHERE ordinal >= f_new_pos
AND ordinal < p_old_pos
ORDER BY ordinal DESC
LOOP
UPDATE vdven.language
SET ordinal = ordinal+1
WHERE id = p_rec.id;
p_done := p_done+1;
END LOOP;
ELSEIF p_old_pos < f_new_pos THEN
FOR p_rec IN SELECT id,ordinal FROM vdven.language WHERE ordinal <= f_new_pos AND ordinal > p_old_pos ORDER BY ordinal ASC LOOP
UPDATE vdven.language
SET ordinal = ordinal-1
WHERE id = p_rec.id;
p_done := p_done+1;
END LOOP;
ELSE
RETURN -1;
END IF;
UPDATE vdven.language
SET ordinal = f_new_pos
WHERE id = f_id;
RETURN p_done;
END;
$$
LANGUAGE plpgsql;
CREATE FUNCTION language_reorder() RETURNS integer
AS $$
DECLARE
p_rec record;
p_int integer := 0;
p_done integer := 0;
BEGIN
FOR p_rec IN SELECT id,ordinal FROM vdven.language ORDER BY ordinal ASC LOOP
IF p_int <> p_rec.ordinal THEN
UPDATE vdven.language
SET ordinal = p_int
WHERE id = p_rec.id;
p_done := p_done+1;
END IF;
p_int := p_int+1;
END LOOP;
RETURN p_done;
END;
$$
LANGUAGE plpgsql;
AS $$
DECLARE
p_next_ordinal integer;
BEGIN
SELECT count(id)
INTO p_next_ordinal
FROM vdven.language;
RETURN p_next_ordinal;
END;
$$
LANGUAGE plpgsql;
CREATE FUNCTION language_move(f_id integer, f_new_pos integer) RETURNS integer
AS $$
DECLARE
p_rec record;
p_old_pos integer;
p_max_pos integer;
p_done integer := 0;
BEGIN
SELECT ordinal
INTO p_old_pos
FROM vdven.language
WHERE id = f_id;
SELECT COUNT(id)-1
INTO p_max_pos
FROM vdven.language;
IF p_old_pos = f_new_pos THEN
RETURN 0;
END IF;
IF p_max_pos < f_new_pos THEN
RETURN -1;
END IF;
UPDATE vdven.language
SET ordinal = p_max_pos+1
WHERE id = f_id;
IF p_old_pos > f_new_pos THEN
FOR p_rec IN
SELECT id,ordinal
FROM vdven.language
WHERE ordinal >= f_new_pos
AND ordinal < p_old_pos
ORDER BY ordinal DESC
LOOP
UPDATE vdven.language
SET ordinal = ordinal+1
WHERE id = p_rec.id;
p_done := p_done+1;
END LOOP;
ELSEIF p_old_pos < f_new_pos THEN
FOR p_rec IN SELECT id,ordinal FROM vdven.language WHERE ordinal <= f_new_pos AND ordinal > p_old_pos ORDER BY ordinal ASC LOOP
UPDATE vdven.language
SET ordinal = ordinal-1
WHERE id = p_rec.id;
p_done := p_done+1;
END LOOP;
ELSE
RETURN -1;
END IF;
UPDATE vdven.language
SET ordinal = f_new_pos
WHERE id = f_id;
RETURN p_done;
END;
$$
LANGUAGE plpgsql;
CREATE FUNCTION language_reorder() RETURNS integer
AS $$
DECLARE
p_rec record;
p_int integer := 0;
p_done integer := 0;
BEGIN
FOR p_rec IN SELECT id,ordinal FROM vdven.language ORDER BY ordinal ASC LOOP
IF p_int <> p_rec.ordinal THEN
UPDATE vdven.language
SET ordinal = p_int
WHERE id = p_rec.id;
p_done := p_done+1;
END IF;
p_int := p_int+1;
END LOOP;
RETURN p_done;
END;
$$
LANGUAGE plpgsql;
Dit is een realtief complexe oplossing voor een simpel probleem, weet iemand in postgres of sql wellicht een simpelere oplossing?
Gewijzigd op 01/01/1970 01:00:00 door Arend a
Quote:
De volgorde is het gevolg van een sortering die je opgeeft in een SELECT-query. Jouw dilema kun je dus schrappen, deze bestaat niet.Ik heb een dilemma voor een tabel waarbij de volgorde uitmaakt
Quote:
Dan doe je dat toch? Niets of niemand die je tegen houdt.Je wil namelijk natuurlijk het liefst de kolom 'order' (integer), op unique zetten
Quote:
Voor een sortering maakt het niet uit of je nu 1,2,3 of 1,8, 25 hebt, 1 is altijd de kleinste waarde en 2 is altijd kleiner dan 3, net zoals 8 kleiner dan 25 is. Voor een sortering bestaan er geen gaten.en het liefst dat de order precies van 0 tot x loopt zonder gaten er tussen
Kortom, ik plaats hele grote vraagtekens bij bovenstaande "oplossingen", ik zie namelijk zelfs geen probleem. Leg eens uit wat nu het echte probleem is, dan kunnen we daar mee helpen.
Ik post dit omdat ik zelf ook mijn twijfels heb bij dit systeem maar, mijn redenering is als volgt:
1. De waarde van order moet unique zijn, want de order van de tabel is bepaald en ondubbelzinnig.
2. Je zal ook bij een systeem met gaps problemen tegenkomen, want er zullen niet altijd gaps zijn, en dan moet je alsnog gaan hernummeren in de order. Mijn conclusie hieruit is dat een systeem met gaps geen voordeel bied tov een systeem zonder gaps.
Er zijn een paar algemene problemen met een ordered structuur:
1. Product X staat op positie 10, en moet naar 14, en de waarden 10 t/m 20 zijn bezet (ook met een systeem met gaps mogelijk). Dit houd in dat alles met waarde 11 t/m 14 wordt verplaatst naar 10 t/m 13, en product x komt op 14. Echter, product x moet eerst naar een tijdelijke waarde, want waarde 14 is op dat moment bezet, en dubbele waarden zijn niet mogelijk
2. Het verwisselen van twee items:
Bij een unique column levert dit ook hersenkrakers op, immers je kan niet in een transactie order 1 naar order 2 hernoemen en order 2 naar order 1, omdat je de twee niet tegelijkertijd kan uitvoeren en er dan een unique constrain in werking treed, kortom, je moet eerst eentje naar een tijdelijke waarde hernoemen en dan pas kan de andere terug.
Resumerend:
Een tabel die een unique order colum heeft levert nogal wat problemen op bij het aanpassen van de order, is het nog de moeite waard deze integriteit in de database te willen afdwingen of ziet iemand snellere of slimmere manieren om deze problemen te ondervangen?
Gewijzigd op 01/01/1970 01:00:00 door Arend a
2) Het verwisselen van twee items wanneer je met een UNIQUE zit: Iets lastiger, maar met een work-around binnen een transactie geen enkel probleem:
1: start transaction;
2: record A geef je een oneindig grote waarde om zo ruimte te maken voor record B
3: record B krijgt de waarde van record A, die is tenslotte vrij gekomen
4: record A krijgt de waarde van record B, die is nu ook vrij.
5: commit
Stap 2 is de workaround, gebruik hier wel een random waarde voor. Dan is de kans wel heel erg klein dat er gelijktijdig iemand anders dezelfde waarde gebruikt.
Tip: Zet dit stuk in een stored procedure, dan kun je nooit de transactie vergeten. Zorg wel voor een goede rollback en test dit ook.
Edit: In het ergste geval zul een rollback moeten doen, wat in SQL de gewoonste zaak van de wereld is. Met een EXCEPTION in je stored procedure kun je dit eenvoudig afhandelen en een fraaie foutmelding richting de applicatie gooien.
Gewijzigd op 01/01/1970 01:00:00 door Frank -
Je maakt me wel nieuwsgierig naar wat je aan het doen bent. Ik kan niks voorstellen bij een tabel waarin de volgorde zo exact bepaald moet zijn
In dat voorbeeld leg ik niet alleen uit hoe je kan sorteren maar ook hoe ik daarbij de database update.
I.c.m. een transaction kan er niets mis gaan...
Over transacties, misschien doe ik iets verkeerd maar de volgende transactie werkt niet op een unique colom:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
BEGIN;
UPDATE public.test_unique
SET "unique"=2 WHERE id=1;
UPDATE public.test_unique
SET "unique"=1 WHERE id=2;
COMMIT;
UPDATE public.test_unique
SET "unique"=2 WHERE id=1;
UPDATE public.test_unique
SET "unique"=1 WHERE id=2;
COMMIT;
Je moet eerst record 1 een dummy-waarde geven, dan record 2 de gewenste waarde geven en vervolgens record 1 alsnog de juiste waarde geven.
Door dit in een transactie te doen, kun je nooit een corrupte database oplopen. Mits je de juiste beslissing neemt bij een COMMIT of ROLLBACK. Dit is dus een typisch voorbeeld van een set queries die je heel graag in een stored procedure zet. Dan hoef je de boel maar 1x te programmeren en kun je onmogelijk nog ergens in je PHP-code iets verkeerd doen.
Jouw probleem heeft niets met een transactie te maken, maar met een unique-constraint die wordt overtreden. Vandaar de dummy-waarde. Als dummy-waarde zou je bv. een sequence kunnen gebruiken die begint bij de waarde 1.000.000.000. Al heb je duizenden gebruikers per seconde, iedereen gebruikt zijn eigen dummy-waarde die door de sequence wordt uitgedeeld. De transactie is uitsluitend bedoelt om de boel veilig af te handelen. Je hebt toch wel ervaring met transacties?
De tabel:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
CREATE TABLE test
(
id serial NOT NULL,
naam character varying(29),
sort integer NOT NULL,
CONSTRAINT pk_test PRIMARY KEY (id)
);
(
id serial NOT NULL,
naam character varying(29),
sort integer NOT NULL,
CONSTRAINT pk_test PRIMARY KEY (id)
);
De UNIQUE constraint (met voorwaarde!!!):
Code (php)
1
2
3
4
5
2
3
4
5
CREATE UNIQUE INDEX u_sort
ON test
USING btree
(sort)
WHERE sort <> 0;
ON test
USING btree
(sort)
WHERE sort <> 0;
En dan de stored procedure:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CREATE OR REPLACE FUNCTION switch(IN a INT, IN b INT) RETURNS bool AS
$BODY$
DECLARE
new_a INT;
new_b INT;
ok BOOL := true;
BEGIN
-- ophalen huidige sort-waarde van records a en b
SELECT sort INTO new_b FROM test WHERE id = a;
SELECT sort INTO new_a FROM test WHERE id = b;
-- record a de sort-waarde 0 geven:
BEGIN
UPDATE test SET sort = 0 WHERE id = a;
IF NOT FOUND THEN
RAISE EXCEPTION 'record met id % niet gevonden', a;
END IF;
-- record b de oude sort-waarde van a geven:
UPDATE test SET sort = new_b WHERE id = b;
IF NOT FOUND THEN
RAISE EXCEPTION 'record met id % niet gevonden', b;
END IF;
-- record a de oude sort-waarde van b geven:
UPDATE test SET sort = new_a WHERE id = a;
IF NOT FOUND THEN
RAISE EXCEPTION 'record met id % niet gevonden', a;
END IF;
EXCEPTION
WHEN unique_violation THEN
ok := false;
WHEN not_null_violation THEN
ok := false;
END;
RETURN ok;
END;
$BODY$
language plpgsql;
$BODY$
DECLARE
new_a INT;
new_b INT;
ok BOOL := true;
BEGIN
-- ophalen huidige sort-waarde van records a en b
SELECT sort INTO new_b FROM test WHERE id = a;
SELECT sort INTO new_a FROM test WHERE id = b;
-- record a de sort-waarde 0 geven:
BEGIN
UPDATE test SET sort = 0 WHERE id = a;
IF NOT FOUND THEN
RAISE EXCEPTION 'record met id % niet gevonden', a;
END IF;
-- record b de oude sort-waarde van a geven:
UPDATE test SET sort = new_b WHERE id = b;
IF NOT FOUND THEN
RAISE EXCEPTION 'record met id % niet gevonden', b;
END IF;
-- record a de oude sort-waarde van b geven:
UPDATE test SET sort = new_a WHERE id = a;
IF NOT FOUND THEN
RAISE EXCEPTION 'record met id % niet gevonden', a;
END IF;
EXCEPTION
WHEN unique_violation THEN
ok := false;
WHEN not_null_violation THEN
ok := false;
END;
RETURN ok;
END;
$BODY$
language plpgsql;
En de query om hem aan te roepen:
Code (php)
1
SELECT switch(3,1);
Een heel verhaal, maar nu kun je records in de sortering van plaats laten wisselen. Mocht er wat fout gaan, krijg je een dikke error of een false retour, afhankelijk van wat er fout gaat. Dit mag je verder zelf uitwerken, je wilt uiteraard maar 1 soort foutmelding retour krijgen. Nu heb je alle mogelijkheden, dat laat zien wat er mogelijk is.
De index is een beetje bijzonder, voor de waarde 0 geldt geen UNIQUE, deze kun je dus gebruiken voor de noodzakelijke workaround. In voorgaande reacties zei ik nog dat je een hele hoge waarde kunt nemen, maar 0 is eigenlijk veel handiger. Dus even een UNIQUE met een uitzondering. Dat kan in PostgreSQL, dus maak je er ook gebruik van ;)
De transactie gaat nu ook goed, een sp werkt altijd transactioneel. En doordat de query bij een mislukking keihard op zijn muil gaat, wordt er door de sp een rollback uitgevoerd. Werkt dus uitstekend.
Wat kan SQL toch mooi zijn.
Bedankt voor je uigebreide antwoord!
Ik heb het nog niet getest, inderdaad slim om de waarde 0 als tijdelijke waarde te reserveren. De reden om de tabel opeenvolgend te houden is omdat het dan voorspelbaar is in programmatuur dat nummer 4 degene is na nummer 3 en positie 5 degene 6 posities na nummer 4. Dit is met name handig als je niet wil wisselen, maar wil verplaatsen, (zie de move procedure die ik eerder geschreven heb.)
Nog even een andere vraag: is het mogelijk een procedure te herschrijven zodat het simpel op meerdere tabellen te gebruiken is? (ik heb nu steeds te tabel hardcoded in de procedure, maar het zou makkelijker zijn als het op meerdere tabellen toe te passen is zonder te gehele procedure te hoeven herschrijven.)