mORMot Open Source

rcla

Member

Registered: 2025-12-13

Posts: 10

Newbie with PostgreSQL Pipeline Mode

I had never worked with PostgreSQL's pipeline mode before.

After reading about it, For example this article.

I see that it has some advantages, and the best part is that mORMot2 has it implemented.

I implemented the following code , and it works! : the respective price values are updated in the database.

However, I'm getting the following exception when trying to release the connection, specifically with pConn.CheckPipelineSync.

TSqlDBPostgresConnection.CheckPipelineSync returned 1 instead of PGRES_PIPELINE_SYNC [] 

Any help you can provide would be greatly appreciated.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,347

Website

Re: Newbie with PostgreSQL Pipeline Mode

First idea: you are mixing class instances and interface references, which is not a good idea.
The statements are to be retrieved from the connection properties as interface references, not created directly as class instances.

And the best approach on a remote DB may be to use not pipelined mode, but array binding of INSERT/UPDATE statements, as we do in our ORM:

    /// internal pointer over field types to be used after Decode() call
    // - to create 'INSERT INTO ... SELECT UNNEST(...)' or 'UPDATE ... FROM
    // SELECT UNNEST(...)' statements for very efficient bulk writes in a
    // PostgreSQL database
    // - as set by TRestStorageExternal.JsonDecodedPrepareToSql when
    // cPostgreBulkArray flag is detected - for mormot.db.sql.postgres.pas
    DecodedFieldTypesToUnnest: PSqlDBFieldTypeArray;

What is your goal/purpose?

rcla

Member

Registered: 2025-12-13

Posts: 10

Re: Newbie with PostgreSQL Pipeline Mode

The idea is basically the same as that used in the published example techempower-bench

After several tests, he concluded that to avoid this exception, a "GetPipelineResult" is required for each SQL statements sent.
And it's consistent with the operation of Pipeline mode.

This would be the corrected code for the one initially proposed.

I ran tests with several SQL statements sent to different tables in my database and it it works like a charm.

The goal/purpose is to use Pipeline mode for better PostgreSQL performance on slow networks.

Again, thank you very much for the excellent work done with mORMot2.