MariaDB: Nextval als Select-Kriterium

OldGrowler · 27 Juni 2024

Hallo, ich bin ziemlich neu in der DB-Anwendung und MariaDB im speziellen.

Nun habe ich folgendes Problem: Ich habe eine Sequence namens seq01 eingerichtet. Die Abfrage "select nextVAL(memdb1.seq01)" liefert auch erwartungsgemäß stets den nächsten Wert.
Aber die Abfrage "select * FROM memdb1.treadings02 WHERE treadings02.RID=nextval(memdb1.seq01)" liefert mir keinen Treffer, obwohl durchaus ein Datensatz in der Tabelle memdb1.treadings02 mit dem zutreffenden Feldwert in RID vorhanden ist.
Was mache ich hier grundlegend falsch?

Danke und Grüße
OldGrowler

akretschmer · 27 Juni 2024

Code:

postgres=# create table old_growler(id serial);
CREATE TABLE
postgres=# \d old_growler
                            Table "public.old_growler"
 Column |  Type   | Collation | Nullable |                 Default                 
--------+---------+-----------+----------+-----------------------------------------
 id     | integer |           | not null | nextval('old_growler_id_seq'::regclass)

postgres=# select nextval('old_growler_id_seq'::regclass);
 nextval 
---------
       1
(1 row)

postgres=# select nextval('old_growler_id_seq'::regclass);
 nextval 
---------
       2
(1 row)

postgres=# select * from old_growler where id = nextval('old_growler_id_seq'::regclass);
 id 
----
(0 rows)

postgres=# select * from old_growler where id = nextval('old_growler_id_seq'::regclass);
 id 
----
(0 rows)

postgres=# select * from old_growler;
 id 
----
(0 rows)

postgres=#

Dein nextval erhöht die Sequence, was aber nicht bedeutet, daß der Wert in der Tabelle vorhanden ist.

dabadepdu · 27 Juni 2024

OldGrowler schrieb:
WHERE treadings02.RID=nextval(memdb1.seq01)

Das ergibt keinen Sinn! (vielleicht bin ich auch zu naiv)
1. Das nextval Ergebnis möchtest du meistens in eine (primär-Schlüssel-) Spalte eintragen, per Insert.
2. Zur Weiterbearbeitung möchtest Du den zuletzt erzeugten Wert vielleicht wissen. Dafür gibt es currval()
3. Die where condition oben würde mit currval u. U. Sinn machen

OldGrowler · 28 Juni 2024

SQL:

CREATE OR REPLACE SEQUENCE `seq01` start with 1 minvalue 1 maxvalue 500 increment by 1 cache 500 cycle ENGINE=InnoDB

-- memdb1.treadings02 definition
CREATE TABLE `treadings02` (
  `RID` int(11) DEFAULT NULL,
  `TESTID` int(11) DEFAULT NULL,
  `TS` double DEFAULT NULL,
  `D01` double DEFAULT NULL,
  `D02` double DEFAULT NULL,
  `D03` double DEFAULT NULL,
  `D04` double DEFAULT NULL,
  `D05` double DEFAULT NULL,
  `D06` double DEFAULT NULL,
  `D07` double DEFAULT NULL,
  `D08` double DEFAULT NULL,
  `D09` double DEFAULT NULL,
  `D10` double DEFAULT NULL,
  `DTN` datetime DEFAULT NULL
) ENGINE=MEMORY;

Das initiale Füllen der Tabelle funktioniert mit NEXTVAL wunderbar.
Die eigentliche Idee dahinter: Die Tabelle als Ringpuffer für ziemlich kurzlebige Werte zu verwenden. Die Tabelle beinhaltet genau n Datensätze, und NEXTVAL zeigt als Schreibzeiger auf den neuesten aktualisierten Datensatz. Ist Datensatz n erreicht, wird als nächstes Datensatz 1 aktualisiert. Alle lesenden Clients können mit LASTVAL auf den aktuellen Datensatz, oder bei Bedarf auf m zurückliegende Datensätze zugreifen.

Ich fand die Idee gut, wäre mit superwenig Code ausgekommen. Aber scheinbar kommt das Problem hinzu, dass je Sitzung ein LASTVAL nur dann liefert, wenn zuvor einmalig NEXTVAL ausgeführt wurde.

dabadepdu · 28 Juni 2024

OldGrowler schrieb:
Ich fand die Idee gut, wäre mit superwenig Code ausgekommen.

Ja, auf den Anwendungsfall bin ich nach Deiner ersten Beschreibung nicht gekommen.

Was da passiert, habe ich auch zuerst nicht verstanden. Aber am Ende leuchtet es ein.
Nextval() kommt als harmlose Funktion daher, verhält sich aber nicht so. Sie produziert pro Datensatz einen neuen/anderen Wert.
Damit ist es ein seltener Glücksfall, dass bei einem Aufruf die verglichene ID dem Ausgabewert von Nextval() entspricht. In den meisten Fällen "begegnen" sich Vergleichswert und Nextval nie. Wie beim Esel, dem man die Möhre an der Angelrute vors Maul bindet...
Also was Du brauchst ist ein statischer Wert von Nextval, das bekommst Du hin, indem Du nextval() in die From Clause pakst.

So ungefähr:

Code:

select t.*
  from testnext t, 
      (select nextval(seq01) as nv) sq
 where sq.nv = t.id;

Hier ist ein fiddle dazu. Das vorletzte Resultat ist die funktionierende Version. Das letzte eine Darstellung, die zeigt, warum der Vergleich meist kein Match ergibt. Um es zeigen zu können ist der Vergleich selbst entfernt und das Nextval einfach in das Ergebnis genommen.

MariaDB 10.6

| version() | |:----------| | 10.6.9-MariaDB-1:10.6.9+maria~deb11 |

dbfiddle.uk

BerndB · 1 Juli 2024

@OldGrowler -
Welche MariaDB Version setzt du ein ?
Und hast du volle Rechte auf dem DB-Server ?

Gruß

Bernd

OldGrowler · 2 Juli 2024

Vielen Dank für den Support. Die Lösung mit der Sequence birgt leider immer noch das oben geschilderte Problem mit der Sitzung.

Ich habe es jetzt gelöst mit einer Kombination aus

Zusatztabelle, welche in einer einzigen Zeile den Schreibzeiger speichert:

Code:

CREATE TABLE `tr02merker` (
  `schreibzeiger` int(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_bin COMMENT='Merker für Schreibzeiger u. ä.';
INSERT INTO tr02merker (schreibzeiger) VALUES (1);

Einer Funktion, welche den aktuellen Schreibzeiger liefert:

Code:

CREATE or replace FUNCTION memdb1.fngetschreibzeiger(IN aa INT) RETURNS int
begin
  set aa = (select schreibzeiger from memdb1.tr02merker limit 1);
 return aa;
END

Einem Trigger, der nach Update des aktuellen Messwert-Datensatzes den Schreibzeiger erhöht:

Code:

CREATE TRIGGER setzer02schreibzeiger 
AFTER update
ON treadings02 FOR EACH ROW
ende: begin
   IF old.RID < (SELECT COUNT(*) FROM treadings02) THEN
    UPDATE tr02merker SET tr02merker.schreibzeiger = tr02merker.schreibzeiger + 1;
   ELSE 
    UPDATE tr02merker SET tr02merker.schreibzeiger = 1;
   END IF;
  leave ende;
end

Der Messwert-Tabelle selbst (initial füllen mit z. B. 100 Datensätzen):

Code:

CREATE TABLE `treadings02` (
`RID` int(11) DEFAULT NULL,
`D01` double DEFAULT NULL,
`D02` double DEFAULT NULL,
`DTN` datetime DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_bin COMMENT='Messwertpuffer 02 zu Testzwecken';

Ein beispielhafter neuer Messwert würde wie folgt eingetragen:

Code:

UPDATE memdb1.treadings02 SET D01=1.137, D02=8.3221 WHERE treadings02.RID=memdb1.test002(0) limit 1

Hier ist es wichtig, das Update mit LIMIT 1 auf einen Datensatz zu beschränken. Ansonsten erfährt nämlich die komplette Messwert-Tabelle -also jeder Datensatz- das gleiche Update, was für mich komplett rätselhaft ist, denn nur ein einziger Datensatz erfüllt die WHERE-Klausel zunächst. Erst mit hochzählendem Schreibzeiger erfüllen auch die nächsten Datensätze die WHERE-Klausel.

OldGrowler · 2 Juli 2024

Der Update-Befehl beinhaltet noch einen veralteten Funktionsaufruf und muss natürlich heißen:

Code:

UPDATE memdb1.treadings02 SET D01=1.137, D02=8.3221 WHERE treadings02.RID=memdb1.fngetschreibzeiger(0) limit 1

akretschmer · 2 Juli 2024

UPDATE mit LIMIT?

Nun denn ...

BerndB · 2 Juli 2024

akretschmer schrieb:
UPDATE mit LIMIT?

Nun denn ...

Warum nicht ??

Code:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
  [PARTITION (partition_list)]
  [FOR PORTION OF period FROM expr1 TO expr2]
  SET col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] ...
  [WHERE where_condition]
  [ORDER BY ...]
  [LIMIT row_count]

In MariaDB geht so was

OldGrowler · 2 Juli 2024

akretschmer schrieb:
UPDATE mit LIMIT? ...

Ja, wirkt auf mich auch seltsam. Ich bin schon am zweifeln, ob ich für das Projekt die optimale Datenbank gewählt habe. Meistens sitzt ja das größte Problem vor dem Computer, aber nur meistens, nicht immer...

BerndB · 2 Juli 2024

OldGrowler schrieb:
Ja, wirkt auf mich auch seltsam. Ich bin schon am zweifeln, ob ich für das Projekt die optimale Datenbank gewählt habe. Meistens sitzt ja das größte Problem vor dem Computer, aber nur meistens, nicht immer...

MAch da mal KEINE Sorgen. Du bist mit MySQL / MariaDB schon sehr gut aufgehoben

dabadepdu · 2 Juli 2024

OldGrowler schrieb:
Die Lösung mit der Sequence birgt leider immer noch das oben geschilderte Problem mit der Sitzung.

Ja, darauf hab ich gar nicht mehr geachtet (vergessen). Meine "Lösung" ermöglich nur den Join über NextVal, ist also nur die halbe Miete.
Diese Funktionen sind Session spezifisch (was sie auch sein müssen).

Man müsste den Nextval Abrufwert in eine Tabelle schreiben (update), die immer nur einen Datensatz hält. Von dort können alle dann den CurrentVal lesen (oder sich weiter zurückliegende Werte errechnen, was m.E. reichen sollte)

Wenn man NextVal in eine Funktion einpakt, die auch das Update durchführt, könnte das klappen. Das würde auch ohne Trigger auskommen.
Ich vermute, im Fehlerfall (Rollback) entsteht dabei aber Chaos.

OldGrowler · 9 Juli 2024

Um den Ungereimtheiten auf den Grund zu gehen habe ich kurzerhand PostgreSQL installiert und den ursprünglichen Ansatz getestet: Hier hat er einwandfrei funktioniert. Ein anschließender Speed-Test mit Tausenden von Datensatz-Updates hat überdies fast eine Halbierung der Ausführungszeit gegenüber MariaDB ergeben, obwohl dort die Tabellen im Memory eingerichtet waren.
Mir ist klar, das dies kein umfänglicher Test mit Allgemeingültigkeit war, aber nach Rücksprache mit Projektbeteiligten, die beide Systeme kennen, ist nun die Wahl auf PostgreSQL gefallen.

Dank an alle für die Unterstützung

akretschmer · 9 Juli 2024

eine sehr gute Entscheidung!

MariaDB: Nextval als Select-Kriterium

OldGrowler

Benutzer

akretschmer

Datenbank-Guru

dabadepdu

Datenbank-Guru

OldGrowler

Benutzer

dabadepdu

Datenbank-Guru

MariaDB 10.6

BerndB

Datenbank-Guru

OldGrowler

Benutzer

OldGrowler

Benutzer

akretschmer

Datenbank-Guru

BerndB

Datenbank-Guru

OldGrowler

Benutzer

BerndB

Datenbank-Guru

dabadepdu

Datenbank-Guru

OldGrowler

Benutzer

akretschmer

Datenbank-Guru