Re: Plpgsql search_path issue going from 9.3 to 9.6

Hi

út 13. 11. 2018 v 14:18 odesílatel George Woodring <
george(dot)woodring(at)iglass(dot)net> napsal:

> We are having an issue with one of our plpgsql functions after migrating
> from 9.3 to 9.6. The function works fine until you change the search path.
>
> psql (9.6.10)
> Type "help" for help.
>
> woody=> select ticket_summary(8154);
> ticket_summary
> -------------------------------------------------------------------
> {58451,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
> (1 row)
>
> woody=> set search_path to "iss-hackers", public;
> SET
> woody=> select ticket_summary(8154);
> ERROR: invalid input syntax for type timestamp with time zone: "woodring"
> CONTEXT: PL/pgSQL function ticket_summary(integer) line 11 at SQL
> statement
>
> It is confused which column is which after the change.
>
> The tables used by the function are:
> public.tickets - A table with 3 child tables
> iss-hackers.tickets - A view of public.tickets with a where clause.
> public.followups - A table with 3 child tables.
>
> CREATE OR REPLACE FUNCTION ticket_summary(int4) RETURNS int8[] AS $$
> DECLARE
> tid ALIAS FOR $1;
> cstate public.followups.state%TYPE := 1;
> ticket public.tickets%ROWTYPE;
> followup public.followups%ROWTYPE;
> summary int8[] := '{0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}';
> lastdate public.followups.date%TYPE;
> BEGIN
> SELECT * INTO ticket FROM tickets WHERE ticketsid=tid;
> IF NOT FOUND THEN
> return summary;
> END IF;
> lastdate := ticket.opendate;
> FOR followup IN SELECT * FROM public.followups WHERE ticketsid=tid
> AND state IS NOT NULL ORDER BY date LOOP
> summary[cstate] := summary[cstate] + extract( EPOCH FROM
> (followup.date - lastdate))::int;
> cstate := followup.state;
> lastdate := followup.date;
> END LOOP;
> summary[cstate] := summary[cstate] + extract( EPOCH FROM
> (current_timestamp - lastdate))::int;
> RETURN summary;
> END;
> $$ LANGUAGE plpgsql;
>
> I assume I can fix this by putting the function into each of the schemas,
> but I thought I would ask opinions before doing so.
>

It looks strange. Maybe you have some garbage in iss-hackers schema created
in upgrade time.

Hard to say what is wrong without complete schema.

Regards

Pavel

> Thanks,
> George Woodring
> iGLASS Networks
> www.iglass.net
>