3

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?

4
  • I cannot reproduce this error with Postgres 15.0 or 15.1. Can you add the output of select version() to your question? Nov 12, 2022 at 16:53
  • "Because of the particular design of the COPY facility, I must first save the program's output to a file," Why? What part of the design of COPY requires this?
    – jjanes
    Nov 12, 2022 at 19:29
  • @DanielVérité I can reproduce this as long as the output is long enough that it gets toasted.
    – jjanes
    Nov 12, 2022 at 19:29
  • @jjanes, the COPY facility can either write to an external program or file (COPY ... TO PROGRAM 'myprog') or read from an external program or file (COPY ... FROM 'myfile'). In my particular situation, I need to pipe data to the external program and then read its output, which can be performed using COPY only in two separate steps, as far as I can tell. Or show me otherwise; I'd be happy to improve and make this code less of a hack.
    – mesr
    Nov 13, 2022 at 16:54

2 Answers 2

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.

3
  • I wasn't aware this existed. I went with the ...ON COMMIT DELETE ROWS version instead, so as to not repeatedly consume OIDs. Worked like a charm. Thanks! Still puzzled though over how can PostgreSQL get its optimization so obviously wrong...
    – mesr
    Nov 12, 2022 at 16:15
  • Worth filing a bug on it.
    – dwhitemv
    Nov 12, 2022 at 20:09
  • I will. Indeed, this looks to me like faulty optimization.
    – mesr
    Nov 13, 2022 at 17:03
1

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.

2
  • 1
    Materializing the data is an interesting approach, but in my particular situation, I believe that dwitemv's answer is better for being more straightforward, and also more correct, as it leverages (pg)SQL's build-in capability as opposed to ad-hoc coding.
    – mesr
    Nov 13, 2022 at 17:02
  • Also, how does your answer explain why the table gets dropped before reading from it despite my coding otherwise?
    – mesr
    Nov 13, 2022 at 17:09

Your Answer

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

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