2

I have a stored procedure that returns a record type. If I ALTER the table this stored procedure returns (let's say I add a column), I have to either disconnect my database session or re-run the CREATE OR REPLACE. When I don't I will get the error wrong record type supplied in RETURN NEXT.

Here's a script that fails when I run it in a single psql session:

CREATE TABLE p1(a INT, b TEXT);

CREATE OR REPLACE FUNCTION authenticate() RETURNS SETOF p1 as '
DECLARE
    player_row p1;
BEGIN

    -- query is more complicated but always returns zero or one rows
    SELECT p.* INTO player_row
    FROM p1 p;

    IF FOUND THEN
        RETURN NEXT player_row;

        -- more code in here..
    END IF;

    RETURN;
END;' LANGUAGE plpgsql ROWS 1;


ALTER TABLE p1 ADD COLUMN c VARCHAR(2);
INSERT INTO p1 VALUES(1,'a', 'c');
SELECT * FROM AUTHENTICATE();

Is there a Postgres command or setting with which I could automate the re-compilation of stored procedures? I tried @vao-tsun's suggestion of running DISCARD PLANS after the migrations, but unfortunately it did not help.

Gregor Petrin
  • 2,891
  • 1
  • 20
  • 24
  • Wow, that's interesting.. the line that fails is the `RETURN NEXT` line, but it works in your example. Thank you very much, I will try to replicate my problem the DB Fiddle you created (didn't know about DB Fiddle, thank you for that as well!) – Gregor Petrin Apr 05 '18 at 09:09
  • ..currently it seems the problem is if I run the whole thing inside a single connection and/or transaction (which is what happens in our testing setup). I don't know what DB Fiddle does behind the scenes, I have to investigate further. – Gregor Petrin Apr 05 '18 at 10:16
  • I will, and thank you, I would like to test a something first though - I've had something urgent fill my time today, thank you for your help so far! – Gregor Petrin Apr 05 '18 at 13:24
  • I think this could be my problem: 'Functions are evaluated the first time that they are called in a session. At that point the plan for any database operations is done and it is left available for future executions within the same session.' - https://www.postgresql.org/message-id/1192554889.10677.176.camel@linda.lfix.co.uk (I've updated my code sample so it is directly runnable) – Gregor Petrin Apr 06 '18 at 13:15
  • No, in the fiddle it works - I suspect it may be splitting various commands across different connections. If I take my block of code and paste it into `psql` all at once it fails. – Gregor Petrin Apr 06 '18 at 13:33
  • yes - I C now. of course `DISCARD PLANS` before next call does not help?.. https://www.postgresql.org/docs/current/static/sql-discard.html I thought plpgsql functions plans are not cached though :/ – Vao Tsun Apr 06 '18 at 13:46
  • No, unfortunately. Thank you for all your help, I modified the question now that I (hopefully :) ) understand the problem better. I see the programmatic solution now (drop connection between migrations) but I would prefer a pure PSQL resolution. – Gregor Petrin Apr 06 '18 at 14:17
  • In `psql` you could put a `\c` into the script to establish a new connection –  Apr 06 '18 at 14:23
  • Thank you, didn't know that either, but my main problem is when running migrations via Flyway in a JVM application. I can program around it now that I know a reconnect solves the problem, still, if I knew how to purge the function cache (or something to that affect :) ), I wouldn't have to :) – Gregor Petrin Apr 06 '18 at 14:37
  • `\c` would be purely `psql` solution, not SQL though – Vao Tsun Apr 06 '18 at 15:42
  • I just realised, that I worked for me all the time, because I tested behind the `pgpool` with two RO slaves, so load balancer was splitting statements, and as soon as I tried to run it in transaction it failed :) tough week it was... Also I give up finding the reasonable neat solution here (like I was hoping discard would help) and answered with re-recreating function - as you suggest in title – Vao Tsun Apr 06 '18 at 16:00

1 Answers1

2

you were looking for this DO statement:

CREATE TABLE p1(a INT, b TEXT);

CREATE OR REPLACE FUNCTION authenticate() RETURNS SETOF p1 as '
DECLARE
    player_row p1;
BEGIN

    -- query is more complicated but always returns zero or one rows
    SELECT p.* INTO player_row
    FROM p1 p;

    IF FOUND THEN
        RETURN NEXT player_row;

        -- more code in here..
    END IF;

    RETURN;
END;' LANGUAGE plpgsql ROWS 1;

ALTER TABLE p1 ADD COLUMN c VARCHAR(2);
INSERT INTO p1 VALUES(1,'a', 'c');

do $$ begin execute pg_get_functiondef('authenticate'::regproc); end; $$;

SELECT * FROM AUTHENTICATE();

but as a_horse_with_no_name suggests, you can just \c to reconnect with psql

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132