Re: Plpgsql search_path issue going from 9.3 to 9.6

I think the issue is that the function is not putting the data into the
tickets%ROWTYPE correctly. When I do \d on public.tickets and
iss-hackers.tickets, the columns are in a different order.

\d public.tickets
Column | Type |
Modifiers
--------------+--------------------------+-------------------------------------------------------------
ticketsid | integer | not null default
nextval('tickets_ticketsid_seq'::regclass)
opendate | timestamp with time zone | default now()
state | smallint | default 1
opentech | character varying(50) |
priority | smallint | default 10
severity | smallint | default 30
problem | character varying(300) |
summary | text |
parent | integer |
remed | boolean | default false
remed2 | boolean | default false
remed_hstart | timestamp with time zone |
autoclean | boolean | default false
remoteid | character varying |
remotesync | timestamp with time zone |
sla_time | interval |
sla_alarm | boolean |

\d iss-hackers.tickets
View "iss-hackers.tickets"
Column | Type | Modifiers
--------------+--------------------------+-----------
ticketsid | integer |
opentech | character varying(50) |
summary | text |
parent | integer |
opendate | timestamp with time zone |
priority | smallint |
problem | character varying(300) |
autoclean | boolean |
state | smallint |
severity | smallint |
remed | boolean |
remed2 | boolean |
remoteid | character varying |
remotesync | timestamp with time zone |
sla_time | interval |
sla_alarm | boolean |
remed_hstart | timestamp with time zone |
tableoid | oid |

The error message is saying column2 is not a timestamp, which the public
table is a timestamp for column2. If I change my SELECT in the function
from SELECT * to SELECT opendate I can fix my issue easily.

George
iGLASS Networks
www.iglass.net

On Tue, Nov 13, 2018 at 8:44 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

> 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
>>
>