SEO

#1 (**permalink**) 05-10-2008, 02:04 PM

Hi, i think that there is a bug with the crosstab function, the query is not stopped even with a too little value for 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

#2 (**permalink**) 05-10-2008, 02:04 PM

"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>Hi, i think that there is a bug
> with the crosstab function,
> the query is not stopped even with a too little value for 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 
> 
> 
> </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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#3 (**permalink**) 05-11-2008, 08:42 PM

(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/journa...c-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
>

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#4 (**permalink**) 05-11-2008, 08:42 PM

"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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

statement_timeout and crosstab