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!
ON COMMIT DROP
is dropped at the end of the transaction, while an SQL cursorWITH 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 withoutON COMMIT DROP
...