Heureka -> The RETURNING of GENERATED_KEYS

Für alle die auch schlau werden wollen - hier mal eine Lösung

Das Problem ist das gleiche wie im benachbarten Thread …

Statement.RETURN_GENERATED_KEYS wird vom Postgres Jdbc Treiber nit unterstuetzt.

Hier eine Lösung - Ein Tip mal nach einer RETURNING Anweisung zu schauen, die man an ein INSERT statment haengen kann brachte schließlich den gewünschten Erfolg.

Und zwar lässt liefert die Methode Statement.execute(String sql) einen boolean, welcher darüber Auskunft gibt, ob hernach ein Resultset zur Verfügung steht. Anders als bei der üblichen Verwendung von Statement.RETURN_GENERATED_KEYS stellt der PG Jdbc-Treiber ein solches ResultSet doch tatsächlich an statement.getResultSet() bereit (statt statement.getGeneratedKeys() == Sackgasse). Nun - Auch gut. Über dieses kann man wunderbar, den (oder die) generierten Keys auslesen - sogar über den Namen der jeweiligen Columns. Es geht sogar noch mehr - dazu komm ich nach dem kleinen Beispiel:


            // my convention ..
            private static final int UNKNOWN_ID = 0;
.....
            String qry = "INSERT INTO foo (bla, blub ... ) "+
            " VALUES (bli, blo, ... ) [B]RETURNING [color=RoyalBlue]myPKcolumn[/color][/B][color=RoyalBlue]";[/color]

            if (statement.execute(qry)) {
                
                // ResultSet available ..
                ResultSet resultSet = [color=Red]statement.getResultSet();[/color]
                try {
                    int pkColumnIndex; 
                    if (resultSet.first() && 
                   [color=DimGray] // I know - thats not necessary .. but I'm paranoid :) [/color]
                        (pkColumnIndex = resultSet.findColumn([color=RoyalBlue]"myPKcolumn"[/color])) > 0) {
                        
                        lastID = resultSet.getInt(pkColumnIndex);
                    } else {
                        
                        lastID = UNKNOWN_ID;
                    }
                } finally {

                    resultSet.close();
                }
            } else {
                
                // no ResultSet available .. 
                lastID = UNKNOWN_ID;
            }

Alles akzeptabel … keine überflüssigen TX … kein Race-Condition-Problem … auch wenn man ein bissel was PG-Spezifisches coden muss.

In diversen Foren od. Mailinglisten habe ich z.B. gelesen, dass sich die Leute den Kopf darueber zerbrechen welchen Index eine Autowert-PK Column in ihrer Table hat, um dem RETURNING eben diesen Index anzugeben (z.B. … RETURNING 4 … funktioniert ja auch) - denn ein PK kann ja an beliebiger Stelle stehen. (PK’s über mehrere Felder lassen wir erstmal aussen vor)

Dabei koennte man für eine Unterstützung von Statement.RETURN_GENERATED_KEYS doch auch einfach RETURNING * (ja das geht auch - liefert alle Felder - nicht nur jene die im INSERT verwendet wurden) anhaengen, um über die PG-Metadaten (API) im Nachhinein den PK zu finden (mehr oder weniger effizient), je nachdem ob die API ueberhaupt verrät, ob eine Column als “PRIMARY_KEY” deklariert wurde. Zumindest hat man die OID der Column - mit der man sicher weiter kommt.

Wie dem auch sei - selbst das sollte für eine Unterstuetzung von Statement.RETURN_GENERATED_KEYS obsolete sein, da Statement.getGeneratedKeys() keine Vorgaben macht, wie das ResultSet auszuschauen hat. Da wäre es sicherlich auch nicht gänzlich falsch, das gesamte Tupel zu fetchen (wenn man drauf zugreift). Man bekaeme auch gleich noch alle sonstigen erzeugten DEFAULT-Werte und weiss der Geier was generiert würde. Ob die NICHT-generierten Werte in diesem Resultset jemanden wirklich stören - sei dahingestellt - mich würde das entzücken.

Es waren auch Bedenken zu lesen, dass man ja dieses RETURNING auch gern mal selbst Anwendungsbedingt an ein INSERT hängen möchte. Aber auch das ist kein Problem, denn RETURNING myAPPGenKey, mySecondAPPGenKey, * akzeptiert PG ebenso wie RETURNING *, *. Da muss der Jdbc-Treiber noch nichtmal viel denken, und könnte sowohl ein “Custom RETURNING” als auch ein “BuildIn RETURNING” gleichzeitig bedienen.

Viel Spass
Gruß
Steffen

p.s. kann momentan leider nicht auf das PG-CVS zugreifen … sonst wuerde ich mich selbst dran machen einen Patch fuer zu bauen.

Nette Idee, hatte Dir ja auch schon im anderen Thread geantwortet und
Dir Metadaten Methoden vorgeschlagen, weil sie seit JDBC API 2.0
eigentlich jeder Treiber unterstützen sollte.


ps:
Warum verwendest Du eigentlich überall CREATE STATEMENT
anstatt PREPARED STATEMENTS wenn Du doch ein solcher Performance-
fanatiker bist??

Auch Normalisierung und Executionplan Optimierung scheinen keine
Freunde von Dir zu sein, dabei ist dass das erste was man im
Postgres Performanceguide zu lesen bekommt.

Dir ist aber schon aufgefallen, dass es sich hier um ein “Beispiel” handelt, welches demonstrieren soll, wie man an eine generierte ID kommen könnte? Es also keinen echten Anwendungsfall darstellt? Warum soll ich ne PreparedStatements-Orgie demonstrieren wenn es einzig und allein um die generierte ID geht? Und was soll all das mit Normalisierungen oder “Executionplan-Optimierung” zu tun haben?

“Metadaten Methoden” - welche auch immer du meinst - scheinen leider auch nicht weiter zu helfen - vielleicht liegt das daran, dass eine generierte ID nix mit Metadaten zu tun haben :slight_smile: ?

[quote=“symbiont”]


Das ergibt sich eher aus deiner andauernden Betonung nach mehr
Performance. Das wahr lediglich ein Hinweis darauf, wo man die meiste
Performance als erstes versuchen sollte zu erzielen. Wahr Dir offenbar
mal wieder nicht klar, habs Dir daher gerne erklärt, kleiner :wink:

Du weisst also wo ich Performance-Engpässe versuche entgegen zu wirken?

@Prepared Statements …

Die machen in vielen Dynamic SQL Szenarien (bei Performance-Betrachtungen) wenig Sinn. Z.B. genau dann, wenn die Query eh nur wenige male bis nur einmal benötigt wird - denn das Prepared Statement selbst “kostet” auch … Es gibt dann aber noch andere Gründe Prepared Statements zu verwenden die aber nix mit Performance zu tun haben.

Und immernoch hat es ueberhaupt nichts mit dem Thema dieses Threads zu tun …

Ich glaube Du weißt nicht wie die großen, gängingen RDBMS mit
Prepared Statements im Detail und PosgreSQL im speziellen umgehen,
sonst würdest Du ganz sicher nicht zu dieser, etwas naiv gestrickten
Aussage kommen.

Prepared Statements, also Queries die sich in Ihrer Struktur nicht mehr
verändern und wo sich nur die Parameter verändern, werden durch den
Optimizer ganz genau einmal analysiert, angepasst und lassen sich
mit den verschiedensten Methoden die der DBMS Herrsteller dafür
vorgibt im Detail verbessern.

Sobald die Query 1 mal abgefeuert wurde, wird sie via Hashoffsett
von der Queryexecution Engine des DB-Servers als ein fix und fertiges
Konstrukt betrachtet, das nicht mehr der Analyse und Dynamischer
Anpassung unterliegt. Alleine durch diesen Mechanismus, den Java
sehr gut unterstützt kann man erheblich Performanceverbesserungen
erzielen.

Was willst Du eigentlich mit nur einmalieger Ausführung aussagen?
Deinen vorheriegen Formulierungen folgend meinst Du damit das nur
1 (ein) Datensatz ausgegeben werden soll. LOL. Glaubst Du deswegen
muss die Query nicht trotzdem immer wieder ausgeführt werden?

So langsam schließe ich mich der allgemeinen Tendenz an, das Du einfach
ein unverbesserlicher Besserwisser bist, der viel theoretisiert aber leider
über nur sehr wenig Praxiskenntnisse verfügt. Daher werde ich auf deine
Posts in Zukunft auch nicht mehr eingehen, weil mir die Zeit dazu zu schade ist.

Korrekt.

Sobald die Query 1 mal abgefeuert wurde, wird sie via Hashoffsett
von der Queryexecution Engine des DB-Servers als ein fix und fertiges
Konstrukt betrachtet, das nicht mehr der Analyse und Dynamischer
Anpassung unterliegt. Alleine durch diesen Mechanismus, den Java
sehr gut unterstützt kann man erheblich Performanceverbesserungen
erzielen.

Vorsicht: wenn z.B. partielle Indexe existieren, kann der Planner bei der Vorbereitung nicht wissen, ob diese nützlich sind oder nicht - er verwendet sie daher nicht. (er kennt ja die aktuellen Werte der Parameter nicht). Das kann u.U. auch Schmerzen verursachen.

Also: prepared Statements sind nett, aber kein Allheilmittel. Sie können Performance verbessern, wenn z.B. sehr, sehr viele Tabellen verjoint werden (denn dann ist die Planungszeit schon signifikant höher als bei Trivialabfragen. Ich hab in einer Mailingliste da schon Beispiele gesehen, wo die Planungszeit im Bereich von Sekunden und die Ausführung bei Millisekunden lag) UND die Abfrage mit ähnlichen Parametern mehrfach in dieser Session kommt. Den potentiellen Nachteil nannte ich schon, ein anderer Vorteil ist, daß prepared Statements robust gegen SQL-Injection sind und daher ein Mittel sind, dies zu verhindern.

Was willst Du eigentlich mit nur einmalieger Ausführung aussagen?

Das dieses Select in dieser Session nur einmal ausgeführt wird.

Deinen vorheriegen Formulierungen folgend meinst Du damit das nur
1 (ein) Datensatz ausgegeben werden soll. LOL. Glaubst Du deswegen
muss die Query nicht trotzdem immer wieder ausgeführt werden?

Vorsicht: prepared Statements sind nach Sessionende futsch, zumindest in PG.


Andreas

und ich meine Ruhe …

auweia …

bingo

bingo … und nun basteln wir dazu noch ein bisschen connection-pooling das ab und zu aufgeraeumt wird …

Nützlich sind sie auch aus ganz praktischen Gründen, z.B. kann man sich OFT bei der jdbc-schnittstelle das rumgemache mit datentypen sparen, da man z.B. nem date attribut auch ein java.sql.Date uebergibt und nicht erst das passende Literal zusammenfrickeln muss