search

FraunhoferIOSB / FROST-Server

A Complete Server implementation of the OGC SensorThings API
https://fraunhoferiosb.github.io/FROST-Server/
GNU Lesser General Public License v3.0
198 stars 74 forks source link

merge "insert" and then "select" to a single "insert … returning *" statement #2021

Closed georghas closed 1 month ago

georghas commented 1 month ago

For insertion, AbstractPersistenceManager first inserts data with "INSERT INTO … RETURNING table.ID;", returns the generated \<ID> and then queries the same row using \<ID> with "SELECT * FROM table WHERE table.ID = \<ID>;".

This requests merges these two operations into one single operation: "INSERT INTO … RETURNING table.*;" Hereby data is inserted, altered by rules or before-triggers (not after-triggers!), should there be any, and then immediately returned. It eliminates the need for another SELECT statement.

(Background: For the default implementation of FROST, this is not noticeable. However when trying to extend the database with TimescaleDB, this small detail speeds up writes significantly.)

hylkevds commented 1 month ago

Excellent idea! Thanks for the PR.

hylkevds commented 1 month ago

I fixed the problem that some EntityTypes (Datastreams) use projections in their select list, which were not in the returning list, causing all tests to fail: https://github.com/FraunhoferIOSB/FROST-Server/commit/da0fc6a678b9c3be4e3a4d5f3a29893d27c2d2ab.

hylkevds commented 1 month ago

I've merged the PR manually, so we can close this. Thanks again!

georghas commented 1 month ago

Awesome. Thank you!