BUG #8633: Assigning to a variable named "current_time" gives wrong output

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.

Responses

Browse pgsql-bugs by date

  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