mORMot Open Source

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

Trestbatch, postgresql and existing records

I am using Postgresql as external database and with TbatchRest I can not use the boInsertOrReplace option (as it is allowed with Mysql) due to different SQL syntax.
@ab recommends to use TbatchRest even in the server side, as it is more fast using multiple records inserts in one sql command.
Sometimes, a number of records in the batch could exist already in the db table and need an update. Or, we do not know if a record with the same primary key exists already and we have to replace it.
How do you deal with this situation in the server side?

With Mysql I was very satisfied as boInsertOrReplace and Mormot do the job with just a change in the first word in the query.
Now, with postgresql, I am not sure what is better....
Include delete commands in the batch for all records that they are going to be inserted after that?

How do you deal with that situation and what do you propose?

Edit: The records to be inserted have a predefined primary ID and it is not filled by mormot or the datase

Last edited by dcoun (2023-06-11 20:29:33)

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

Re: Trestbatch, postgresql and existing records

I was wondering if it is possible to have an IOrm.Replace. That will help even when not using Trestbatch in mysql/Mariadb
As I can see the function EncodeAsSqlPrepared does the real job in mormot.orm.base line 3412
For Mysql it is just a change to start with 'Replace' instead of 'INSERT' but it is better to run it as upsert (INSERT ..... ON DUPLICATE KEY UPDATE .....
For Postgres/SQLITE it is a real upsert query.
ID primary key must be excluded from the update part of the upsert, and probably a parameter in IOrm.Replace can add more fields as conflict columns.

Is is possible @ab?

Thank you in advance

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

Re: Trestbatch, postgresql and existing records

One possible solution, I was thinking is, before adding a list of records to the TrestBatch, to create a comma delimited string (CSV) with their predefined primary keys and run an sql query like the following:

SELECT RowID from table where RowID in (Csv of ID)

In such a case I can have the existing records in the db, with just one query, to decide if I am going to use INSERT or UPDATE. And I can use Trestbatch with multi-records insert.

Do you recommend something better?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: Trestbatch, postgresql and existing records

The safe pattern is indeed to use a manual transaction, then check for existing values using Retrieve, then make either Add or Update in a batch (without an automatic transaction).
Of course, since you use a manual transaction, do it on the server side, within a service - and not from the client side.

We may try to add the https://www.postgresqltutorial.com/post … ql-upsert/ PostgreSQL syntax to the ORM.
But a lot of code to write for sure, so that we could emulate properly UPSERT.