Re: statement_timeout and crosstab

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