How to prevent what looks like PostgreSQL faulty optimization inside a function?

Question

I have defined a function that uses PostgreSQL's COPY facility for reading the output of a shell program which reads its input from stdin and returns a string through stdout. Because of the particular design of the COPY facility, I must first save the program's output to a file, then read that file into a temporary table, and finally query that table into a variable that the function can return (actually, I could return the result of the query directly, but I want to delete the data from the temporary table before exiting the function):

CREATE FUNCTION exec_prog(cmd_stdin text, cmd_file text)
    RETURNS text
    LANGUAGE 'plpgsql'
    PARALLEL UNSAFE
AS $BODY$
DECLARE
    cmd_line text;
    cmd_result text;
BEGIN
    cmd_line := format($$/bin/bash -c 'myprog arg1 arg2 > "%s"'$$, cmd_file);
CREATE TEMPORARY TABLE _result_ (result text);

EXECUTE format($$COPY ( SELECT %L ) TO PROGRAM %L$$, cmd_stdin, cmd_line);
EXECUTE format($$COPY _result_ FROM %L$$, cmd_file);

SELECT result FROM _result_ INTO cmd_result;

DROP TABLE _result_;

RETURN cmd_result;

END
$BODY$;

This seems like a simple and logical course of action. However, when I test it with psql, I get the following error (with a different OID every time):

ERROR:  could not open relation with OID 376472

Now, if I move the DROP statement just before the CREATE TEMPORARY TABLE statement, then everything works fine, but it's not very elegant:

    ...
    DROP TABLE IF EXISTS _result_;
    CREATE TEMPORARY TABLE _result_ (result text);
    ...

This seems to me like PostgreSQL is "optimizing" the flow of execution by re-ordering or parallelizing statements, and that the DROP statement ends up being executed before the SELECT statement, even though the SELECT statement clearly refers to the table being dropped. Or am I mistaken? The actual function and shell command are significantly more complex than the distilled example shown above, so I guess the problem could also be due to some race condition. But the question remains.

I've also thought that, since table creation can be an expensive operation that also consumes an OID, I could CREATE IF NOT EXISTS the temporary table and then TRUNCATE it when I'm done instead of dropping it, but I'm still running into the same kind of issue:

ERROR:  missing chunk number 0 for toast value 376493 in pg_toast_376488

And regardless of what quirk I end up with to make it work, I'd like to understand what's going on here and, if possible, an elegant way to mitigate it. Any idea?

score 2 · Accepted Answer · answered Nov 12 '22 at 16:02

2

Instead of manually dropping the temp table, declare it with ON COMMIT DROP so it is discarded automatically at the end of the transaction block.

answered Nov 12 '22 at 16:02

score 1 · Answer 2 · answered Nov 12 '22 at 19:33

The data is stored on disk via TOAST, and the variable just has a pointer to that TOAST data. But then once the table is dropped, that pointer no longer works. You can force it to materialize the data in memory like so:

SELECT result||'' FROM _result_ INTO cmd_result;

But I would probably use plperlu or plpythonu instead.

How to prevent what looks like PostgreSQL faulty optimization inside a function?

2 Answers2