Plpgsql search_path issue going from 9.3 to 9.6

Hi

út 13. 11. 2018 v 14:18 odesílatel George Woodring <
george.woodring@iglass.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

On 11/13/18 5:17 AM, George Woodring wrote:

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"

Well the above is the base of the issue, the string 'woodring' is being
used as a timestamp entry. Somewhere wires are getting crossed.

In the function below you have:

SELECT * INTO ticket FROM tickets WHERE ticketsid=tid;

This is the only place where you do not schema qualify a table.

Is there more then tickets table?

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.

 Thanks,
George Woodring
iGLASS Networks
www.iglass.net <http://www.iglass.net&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

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.stehule@gmail.com>
wrote:

On 11/13/18 6:27 AM, George Woodring wrote:

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.

That is because you have a table tickets in the public schema and a view
tickets in the iss-hackers schema.

Is that what you really want?

\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 <http://www.iglass.net&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/13/18 6:27 AM, George Woodring wrote:

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.

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.

Or change this:

SELECT * INTO ticket FROM tickets WHERE ticketsid=tid;

to:

SELECT * INTO ticket FROM public.tickets WHERE ticketsid=tid;

This will match the ROWTYPE:

ticket public.tickets%ROWTYPE;

George
iGLASS Networks
www.iglass.net <http://www.iglass.net&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

The tickets view restricts which tickets can be seen by the schema.

9.3 must have created the view in the same column order as the table (which
is the case looking at one of our 9.3 databases which we have not updated
yet), which is why we never saw the issue before.

George
iGLASS Networks
www.iglass.net

On Tue, Nov 13, 2018 at 9:46 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 11/13/18 6:54 AM, George Woodring wrote:

The tickets view restricts which tickets can be seen by the schema.

9.3 must have created the view in the same column order as the table
(which is the case looking at one of our 9.3 databases which we have not
updated yet), which is why we never saw the issue before.

What is the view definition?

George
iGLASS Networks
www.iglass.net <http://www.iglass.net&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

CREATE OR REPLACE VIEW tickets AS
SELECT *, tableoid
FROM public.tickets
WHERE ( ticketsid IN (SELECT ticketsid
FROM machtick));

iGLASS Networks
www.iglass.net

On Tue, Nov 13, 2018 at 10:32 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

I apologize for top posting, Google hid all of the other stuff.

George
iGLASS Networks
www.iglass.net

I apologize for top posting, Google hid all of the other stuff.

It is only me who thinks that when it comes to destroying email as a communication tool, no one did a better job than effing gmail.