PostgreSQL 9.5, getting "cached plan must not change result type" error

Question

I had a really odd and unexpected behaviour in PostgreSQL. Debugging I found that the problem only occurred while including CREATE TEMP TABLE ... inside a function, and by using an alternative (in this case, I resorted to an array) the error would be gone. Perhaps any one experienced a similar error or knows what this error relates to? I might need to create temporary tables in functions sooner or later.

I'm porting an old Delphi XE6 program from a legacy proprietary database to PostgreSQL, changing the TxxTable components (proprietary) to TFDTable ones (FireDAC) pointing to a copy of the database in PostgreSQL 9.5.

Things were working peachy until I created this plpgsql function that inserts and updates some data here and there. I ran the function via PGConnection.ExecSQLScalar('SELECT do_stuff(:id)', FID);, where PGConnection is my TFDConnection to PostgreSQL (same where I have many other tables open and working well), do_stuff is the function in question, and FID is an ID I send as a parameter. The function runs well, as it does when running it from PgAdmin, but the connection becomes unstable as any already open table afterwards trying to do anything (insert/edit/move) will raise the following error:

ERROR: cached plan must not change result type
STATEMENT: FETCH FORWARD 50 FROM "357STM"`

FireDAC normally opens tables and queries using cursors:

LOG: execute 358STM: DECLARE "357STM" CURSOR WITH HOLD FOR SELECT *
FROM table

If I execute do_stuff(:id) via an TFDQuery component, the function itself fails (FireDAC executes it as a cursor), raising the same error stated above.

After tearing my hear out debugging, I realised that the only way to avoid the error was avoid creating any temporary tables inside the function. Whereas creating the function as CREATE TEMP TABLE tbl ON COMMIT DROP AS SELECT * FROM some_table WHERE .... or as CREATE TEMP TABLE tbl (id int, value text); the error was raised. Without CREATE TEMP TABLE, no errors raised.

Any clues will be highly appreciated!

Answer 1

FireDAC FDQuery.

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