From: | michael(dot)lancaster(at)exa-networks(dot)co(dot)uk |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #8633: Assigning to a variable named "current_time" gives wrong output |
Date: | 2013-11-27 15:49:49 |
Message-ID: | E1VlhN7-0006I7-1M@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 8633
Logged by: Michael Lancaster
Email address: michael(dot)lancaster(at)exa-networks(dot)co(dot)uk
PostgreSQL version: 9.1.10
Operating system: Linux ubuntu 3.5.0-42-generic #65~precise1-Ubuntu
Description:
Reading the value from a variable named 'current_time' that has a value
assigned to it then assigns the value of the function 'current_time' rather
than reading the value that was previously assigned.
Changing the variable name to anything else - current_time_value for example
- fixes the issue.
Table schema is not included as the function works without issue in psql
version 8.4.7.
------------------------------------------------------------
create type period_call_count_type as (
start_time timestamp,
end_time timestamp,
max_count integer
);
------------------------------------------------------------
create or replace function call_count (text, date, date=null) returns setof
period_call_count_type as $$
DECLARE
customer_value alias for $1;
start_date alias for $2;
end_date alias for $3;
start_time timestamp;
end_time timestamp;
current_time timestamp;
threshold_time timestamp;
resolution_value interval;
max_count_value integer;
current_count_value integer;
edge_value period_edge_type;
result period_call_count_type;
BEGIN
raise warning 'start_date: %',start_date;
resolution_value := '10 minutes'::interval;
raise warning 'resolution_value: %',resolution_value;
current_time := start_date::timestamp;
raise warning 'current_time: %',current_time;
start_time := start_date::timestamp;
raise warning 'start_time: %',start_time;
threshold_time := current_time + resolution_value;
if end_date is not null then
end_time := end_date + '1 day'::interval;
else
end_time := start_date + '1 day'::interval;
end if;
max_count_value := 0;
current_count_value := 0;
for edge_value in
select event, event_time
from detect_call_edges(customer_value, start_time, end_time)
order by event_time nulls first, event
loop
while edge_value.event_time >= threshold_time loop
result.start_time := current_time;
result.end_time := threshold_time;
result.max_count := max_count_value;
current_time = threshold_time;
threshold_time = threshold_time + resolution_value;
max_count_value := current_count_value;
return next result;
end loop;
if edge_value.event = 'start'::text then
current_count_value := current_count_value + 1;
if current_count_value > max_count_value then
max_count_value = current_count_value;
end if;
elsif edge_value.event = 'stop'::text then
current_count_value := current_count_value - 1;
if current_count_value < 0 then
raise exception 'concurrent call count drops below 0';
end if;
end if;
end loop;
result.start_time := current_time;
result.end_time := current_time + resolution_value;
result.max_count := max_count_value;
return next result;
result.max_count := 0;
while result.end_time < end_time loop
result.start_time := result.start_time + resolution_value;
result.end_time := result.end_time + resolution_value;
return next result;
end loop;
return;
END
$$ language plpgsql security definer;
COMMIT;
-------------------------------------------------------------------------
PSQL QUERY:
select * from call_count('exa','2013-10-01','2013-10-31');
CONSOLE OUTPUT:
select * from call_count('exa','2013-10-01','2013-10-31');
WARNING: 01000: start_date: 2013-10-01
LOCATION: exec_stmt_raise, pl_exec.c:2799
WARNING: 01000: resolution_value: 00:10:00
LOCATION: exec_stmt_raise, pl_exec.c:2799
WARNING: 01000: current_time: 15:06:43.601688+00
LOCATION: exec_stmt_raise, pl_exec.c:2799
WARNING: 01000: start_time: 2013-10-01 00:00:00
LOCATION: exec_stmt_raise, pl_exec.c:2799
ERROR: 22007: invalid input syntax for type timestamp:
"15:16:43.601688+00"
CONTEXT: PL/pgSQL function "call_count" line 30 at assignment
LOCATION: DateTimeParseError, datetime.c:3565
EXPECTED OUTPUT:
The assignment for current_time and start_time should both be the same, a
timestamp of start_date, however current_time is getting the current time
rather than its assigned value.
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2013-11-27 16:42:27 | Re: BUG #8434: Why does dead lock occur many times ? |
Previous Message | Pavel Stehule | 2013-11-27 12:32:45 | Re: Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist |