Thread: statement_timeout and crosstab

statement_timeout and crosstab

From
"Tiago D. Jacobs - iMDT"
Date:
Hi, i think that there is a bug
with the crosstab function,
the query is not stopped even with a too little value for <font
 face="Arial">statement_timeout, like this:

  set statement_timeout = 1;
  create temp table resultset_26243
TABLESPACE temp_space as
  SELECT *
  FROM crosstab(
  '', '' );

I implemented at application level, but i think that if is a bug, it's
good to report you.

Any ideas?

regards,
tiago jacobs

Re: statement_timeout and crosstab

From
Tom Lane
Date:
"Tiago D. Jacobs - iMDT" <tiago@imdt.com.br> writes:
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <blockquote><small><font face="Arial">Hi, i think that there is a bug
> with the </font></small><small><font face="Arial">crosstab function,
> the query is not stopped even with a too little value for </font></small><small><font
>  face="Arial">statement_timeout, like this:</font></small><br>
>   <br>
>   <small><font face="Arial">set statement_timeout = 1;</font></small><br>
>   <small><font face="Arial">create temp table resultset_26243
> TABLESPACE temp_space as</font></small><br>
>   <small><font face="Arial">SELECT *</font></small><br>
>   <small><font face="Arial">FROM crosstab(</font></small><br>
>   <small><font face="Arial">'', '' );<br>
>   <br>
> I implemented at application level, but i think that if is a bug, it's
> good to report you.<br>
>   <br>
> Any ideas?<br>
>   <br>
> regards,<br>
> tiago jacobs<br>
>   <br>
>   <br>
>   </font></small></blockquote>
> </body>
> </html>

(1) Please do not post HTML.

(2) Your example fails instantly with
ERROR:  a column definition list is required for functions returning "record"
I'm prepared to believe that there's a loop in crosstab() that fails to
check for interrupts reasonably often, but it's not obvious where.  You
need to provide a complete working test case if you want the problem
investigated.

            regards, tom lane

Re: statement_timeout and crosstab

From
"Tiago D. Jacobs - iMDT"
Date:
(1) Sorry, automatic by e-mail client, disabled.

(2) I`m just trying to help to make postgresql better, my problem was
solved at app level with asyncronous query and controlling the timeout
by itself. I believe that this is a way of rewarding the dedication and
commitment of all of you.

(3) I got a sample usage of crosstab at this site:

http://www.postgresonline.com/journal/index.php?/archives/14-CrossTab-Queries-in-PostgreSQL-using-tablefunc-contrib.html.

(4) Create tables and data:

CREATE TABLE inventory
(
  item_id serial NOT NULL,
  item_name varchar(100) NOT NULL,
  CONSTRAINT pk_inventory PRIMARY KEY (item_id),
  CONSTRAINT inventory_item_name_idx UNIQUE (item_name)
)
WITH (OIDS=FALSE);

CREATE TABLE inventory_flow
(
  inventory_flow_id serial NOT NULL,
  item_id integer NOT NULL,
  project varchar(100),
  num_used integer,
  num_ordered integer,
  action_date timestamp without time zone
      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT pk_inventory_flow PRIMARY KEY (inventory_flow_id),
  CONSTRAINT fk_item_id FOREIGN KEY (item_id)
      REFERENCES inventory (item_id)
      ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH (OIDS=FALSE);

CREATE INDEX inventory_flow_action_date_idx
  ON inventory_flow
  USING btree
  (action_date)
  WITH (FILLFACTOR=95);

INSERT INTO inventory(item_name) VALUES('CSCL (g)');
INSERT INTO inventory(item_name) VALUES('DNA Ligase (ul)');
INSERT INTO inventory(item_name) VALUES('Phenol (ul)');
INSERT INTO inventory(item_name) VALUES('Pippette Tip 10ul');


INSERT INTO inventory_flow(item_id, project, num_ordered, action_date)
    SELECT i.item_id, 'Initial Order', 10000, '2007-01-01'
        FROM inventory i;

--Similulate usage
INSERT INTO inventory_flow(item_id, project, num_used, action_date)
    SELECT i.item_id, 'MS', n*2,
        '2007-03-01'::timestamp + (n || ' day')::interval + ((n + 1) ||
' hour')::interval
        FROM inventory As i CROSS JOIN generate_series(1, 250) As n
        WHERE mod(n + 42, i.item_id) = 0;

INSERT INTO inventory_flow(item_id, project, num_used, action_date)
    SELECT i.item_id, 'Alzheimer''s', n*1,
        '2007-02-26'::timestamp + (n || ' day')::interval + ((n + 1) ||
' hour')::interval
        FROM inventory as i CROSS JOIN generate_series(50, 100) As n
        WHERE mod(n + 50, i.item_id) = 0;

INSERT INTO inventory_flow(item_id, project, num_used, action_date)
    SELECT i.item_id, 'Mad Cow', n*i.item_id,
        '2007-02-26'::timestamp + (n || ' day')::interval + ((n + 1) ||
' hour')::interval
        FROM inventory as i CROSS JOIN generate_series(50, 200) As n
        WHERE mod(n + 7, i.item_id) = 0 AND i.item_name IN('Pippette Tip
10ul', 'CSCL (g)');


( 5 ) Here's the problem (it returns without breaking by timeout):

set statement_timeout = 1;

SELECT mthreport.*
    FROM
    crosstab('SELECT i.item_name::text As row_name,
to_char(if.action_date, ''mon'')::text As bucket,
        SUM(if.num_used)::integer As bucketvalue
    FROM inventory As i INNER JOIN inventory_flow As if
        ON i.item_id = if.item_id
      AND action_date BETWEEN date ''2007-01-01'' and date ''2007-12-31
23:59''
    GROUP BY i.item_name, to_char(if.action_date, ''mon''),
date_part(''month'', if.action_date)
    ORDER BY i.item_name',
    'SELECT to_char(date ''2007-01-01'' + (n || '' month'')::interval,
''mon'') As short_mname
        FROM generate_series(0,11) n')
        As mthreport(item_name text, jan integer, feb integer, mar integer,
            apr integer, may integer, jun integer, jul integer,
            aug integer, sep integer, oct integer, nov integer,
            dec integer);

( 6 ) A big THANK YOU, for all postgresql team.

regards,
tiago jacobs

Tom Lane escreveu:
> "Tiago D. Jacobs - iMDT" <tiago@imdt.com.br> writes:
>
> (1) Please do not post HTML.
>
> (2) Your example fails instantly with
> ERROR:  a column definition list is required for functions returning "record"
> I'm prepared to believe that there's a loop in crosstab() that fails to
> check for interrupts reasonably often, but it's not obvious where.  You
> need to provide a complete working test case if you want the problem
> investigated.
>
>             regards, tom lane
>

Re: statement_timeout and crosstab

From
Tom Lane
Date:
"Tiago D. Jacobs - iMDT" <tiago@imdt.com.br> writes:
> ( 5 ) Here's the problem (it returns without breaking by timeout):

Hm, works fine here:

ERROR:  canceling statement due to statement timeout
CONTEXT:  SQL statement "SELECT i.item_name::text As row_name,
to_char(if.action_date, 'mon')::text As bucket,
        SUM(if.num_used)::integer As bucketvalue
    FROM inventory As i INNER JOIN inventory_flow As if
        ON i.item_id = if.item_id
      AND action_date BETWEEN date '2007-01-01' and date '2007-12-31
23:59'
    GROUP BY i.item_name, to_char(if.action_date, 'mon'),
date_part('month', if.action_date)
    ORDER BY i.item_name"

How long does the query run on your machine?  If it's less than 10ms,
maybe what you're seeing is just that the resolution of
statement_timeout isn't necessarily less than 10ms, depending on
platform.

What is the platform, anyway?  And what Postgres version?

            regards, tom lane