FireDAC precedes query with DECLARE CURSOR

Question

When I execute following query in PG Admin III, it runs fine:

WITH new_values (updated_at,talker_id,sentence_id,v0,v1,v2,v3,v4,v5) AS (
    VALUES (current_timestamp,'2','3','4','5','6','7','8','9')
),
UPSERT AS (
    UPDATE utel u
    SET
        updated_at=nv.updated_at,
        v0=nv.v0,v1=nv.v1,v2=nv.v2,v3=nv.v3,v4=nv.v4,v5=nv.v5
    FROM
        new_values nv
    WHERE
        u.talker_id=nv.talker_id AND u.sentence_id=nv.sentence_id RETURNING u.*
)
INSERT into utel (updated_at,talker_id,sentence_id,v0,v1,v2,v3,v4,v5)
SELECT updated_at,talker_id,sentence_id,v0,v1,v2,v3,v4,v5
FROM new_values WHERE NOT EXISTS(
    SELECT 1 FROM upsert up
    WHERE
        up.talker_id=new_values.talker_id
        AND up.sentence_id=new_values.sentence_id
)

but when I try to execute it through TFDQuery, PostgreSQL complains:

syntax error at or near "INSERT" at character 355

I turned on PostgreSQL logging to Windows error log and saw following:

DECLARE "10STM" CURSOR WITH HOLD FOR
WITH new_values (updated_at,talker_id,sentence_id,v0,v1,v2,v3,v4,v5) AS (
    VALUES (current_timestamp,'2','3','4','5','6','7','8','9')
),
UPSERT AS (
    UPDATE utel u
    SET
        updated_at=nv.updated_at,
        v0=nv.v0,v1=nv.v1,v2=nv.v2,v3=nv.v3,v4=nv.v4,v5=nv.v5
    FROM
        new_values nv
    WHERE
        u.talker_id=nv.talker_id AND u.sentence_id=nv.sentence_id RETURNING u.*
)
INSERT into utel (updated_at,talker_id,sentence_id,v0,v1,v2,v3,v4,v5)
SELECT updated_at,talker_id,sentence_id,v0,v1,v2,v3,v4,v5
FROM new_values WHERE NOT EXISTS(
    SELECT 1 FROM upsert up
    WHERE
        up.talker_id=new_values.talker_id
        AND up.sentence_id=new_values.sentence_id
)

When I execute the same, with DECLARE "10STM" CURSOR WITH HOLD FOR in PG Admin III, I get exact the same error.

Why FireDAC precedes PG query with a cursor and how to avoid this?

Do you use ckStatic cursor kind (in your fetch options)? Do you use transactions? Which FireDAC component? Which version of Delphi? Have you considered executing this in a block? I can see what happens in Tokyo, just want to review what led to that decision. — Victoria
@Victoria: Thank you very much. It was ckAutomatic. I changed it to ckDefault and it works now. Please, get your points. — Paul

Paul Paul · Accepted Answer · 2017-11-01T12:28:20

Victoria doesn't want to get her points, so here is the answer:

Cursor kind in fetch options was set to ckAutomatic. I changed it to ckDefault and it works now.

FireDAC precedes query with DECLARE CURSOR

1 Answers