2

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!

2
  • A complete function definition would be of help. A minimal example to reproduce the problem. Since a temp table created with ON COMMIT DROP is dropped at the end of the transaction, while an SQL cursor WITH HOLD is retained until the end of the session, I am not surprised you run into problems. Odd, though, that you see the same problem for a temp table without ON COMMIT DROP ... Jul 22, 2016 at 23:22
  • @ErwinBrandstetter I'll work on a script to try and reproduce the error, and update the question. Jul 23, 2016 at 1:59

1 Answer 1

3

FireDAC FDQuery.

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

1
  • 2
    Can you edit some more detail into your answer to explain how this solves the problem as stated in the question?
    – Paul White
    Aug 7, 2018 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.