Thread: Function parameter type precision modifiers ignored.

Hi,
I don't know if this is a bug as such, but the behaviour certainly
confused me for a while : -

Given the following PostgreSQL functions:

CREATE OR REPLACE FUNCTION fn_dtm (
     dtm timestamptz(0))
RETURNS void AS $$
BEGIN
     RAISE NOTICE 'fn: %, %', dtm, dtm::timestamptz(0);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION fn_num (
     num numeric(5, 2))
RETURNS void AS $$
BEGIN
     RAISE NOTICE 'num: %, %', num, num::numeric(5, 2);
END;
$$ LANGUAGE plpgsql;

Would you expect the output of these functions to show the result as per
the type declared in the function parameter?

I was very surprised to find that the precision was retained and shown
in the "NOTICE" despite the parameter type limiting the precision.

Is this a bug or am I just thinking about it the wrong way?

Here is the output:

db=> SELECT fn_dtm(now());
NOTICE:  fn: 2015-02-05 10:25:44.184+00, 2015-02-05 10:25:44+00

db=> SELECT fn_num(1.23456789);
NOTICE:  num: 1.23456789, 1.23

I am using PostgreSQL 9.3.

CREATE OR REPLACE FUNCTION fn_dtm (
     dtm timestamptz(0))
RETURNS void AS $$
BEGIN
     dtm = dtm::timestamptz(0); ----- CHANGE PRECISION
     RAISE NOTICE 'fn: %', dtm;
END;
$$ LANGUAGE plpgsql;

Gives:

db=> SELECT fn_dtm(now());
NOTICE:  fn: 2015-02-05 10:38:38+00

I don't know if the behaviour is documented anywhere (sorry if I've
missed it), but not knowing this really confused me for a good couple of
hours.  For fun here was the scenario : -

1) For my monitoring system I have a readings table.  In my readings
table I store the timestamp only to a 1 second precision (timestamptz(0)).

2) When a new "live" reading comes in, I use "now()" to get the
timestamp.  I then pass this to another function with the reading
parameters, including a time argument (of type timestamptz(0)) which
gets passed now()).  Lets say now() is 2015-02-05 16:35:38.923.

3) The new reading gets stored in the database, and since the field type
is timestamptz(0) it gets rounded up to (2015-02-05 16:35:39).

4) If the new reading indicates an alarm state, I do a pg_notify to send
an event to my app which includes the passed in timestamp.  Now I
assumed because of the parameter type this would not include
milliseconds, so naturally I ignore anything after the seconds when I
parse the notification event.. So I end up with 2015-02-05 16:35:38.
Spot the difference to the stored timestamp?

5) The application then periodically loads the readings in alarm from
the database based on the events received.  So I do SELECT ..... WHERE
reading_dtm = '2015-02-05 16:35:38' etc.  Obviously nothing is returned
as the timestamp is out by one second (except where the original reading
timestamp was not rounded up).

I've fixed it by just doing p_reading_dtm =
p_reading_dtm::timestamptz(0) at the start of my function.  Seems odd
though when the parameter type is already timestamptz(0).

Regards,
Mark.
--

Mark Simonetti <marks@opalsoftware.co.uk> writes:
> I don't know if this is a bug as such, but the behaviour certainly
> confused me for a while : -

> Given the following PostgreSQL functions:

> CREATE OR REPLACE FUNCTION fn_dtm (
>      dtm timestamptz(0))
> RETURNS void AS $$
> BEGIN
>      RAISE NOTICE 'fn: %, %', dtm, dtm::timestamptz(0);
> END;
> $$ LANGUAGE plpgsql;

> CREATE OR REPLACE FUNCTION fn_num (
>      num numeric(5, 2))
> RETURNS void AS $$
> BEGIN
>      RAISE NOTICE 'num: %, %', num, num::numeric(5, 2);
> END;
> $$ LANGUAGE plpgsql;

> Would you expect the output of these functions to show the result as per
> the type declared in the function parameter?

Attributes applied to function parameter types --- or result types for
that matter --- are entirely ignored by Postgres; only the base type
matters.  This is documented.

There's been some talk of rejecting syntax like the above, because we
get bug reports like this once or twice a year, so obviously a lot of
people are confused about what happens.

The odds of actually enforcing such typmods anytime in the near future
are not distinguishable from zero, but we could make CREATE FUNCTION
throw an error.

            regards, tom lane

Tom Lane-2 wrote
> Mark Simonetti <

> marks@.co

> > writes:
>> I don't know if this is a bug as such, but the behaviour certainly
>> confused me for a while : -
>
>> Given the following PostgreSQL functions:
>
>> CREATE OR REPLACE FUNCTION fn_dtm (
>>      dtm timestamptz(0))
>> RETURNS void AS $$
>> BEGIN
>>      RAISE NOTICE 'fn: %, %', dtm, dtm::timestamptz(0);
>> END;
>> $$ LANGUAGE plpgsql;
>
>> CREATE OR REPLACE FUNCTION fn_num (
>>      num numeric(5, 2))
>> RETURNS void AS $$
>> BEGIN
>>      RAISE NOTICE 'num: %, %', num, num::numeric(5, 2);
>> END;
>> $$ LANGUAGE plpgsql;
>
>> Would you expect the output of these functions to show the result as per
>> the type declared in the function parameter?
>
> Attributes applied to function parameter types --- or result types for
> that matter --- are entirely ignored by Postgres; only the base type
> matters.  This is documented.

http://www.postgresql.org/docs/9.4/interactive/sql-createfunction.html
@ Notes
First Paragraph

Maybe it would be more obvious in the section detailing "argtype"...but its
not like its buried deep in the documentation.  The bigger problem is
understanding exactly what that notes means in reality.  Often that means
getting bit by the behavior first and then reading about what just bit you.
Happens to all of us.

I'm for the idea of issuing a syntax error upon seeing "type()" in general
but not strongly since perfectly valid code (often back-stopped by table
constraints) currently works and would otherwise have no reason to change
other than to conform to this decision.

David J.



--
View this message in context:
http://postgresql.nabble.com/Function-parameter-type-precision-modifiers-ignored-tp5836988p5837000.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

David G Johnston <david.g.johnston@gmail.com> writes:
> Tom Lane-2 wrote
>> Attributes applied to function parameter types --- or result types for
>> that matter --- are entirely ignored by Postgres; only the base type
>> matters.  This is documented.

> Maybe it would be more obvious in the section detailing "argtype"...but its
> not like its buried deep in the documentation.  The bigger problem is
> understanding exactly what that notes means in reality.  Often that means
> getting bit by the behavior first and then reading about what just bit you.
> Happens to all of us.

> I'm for the idea of issuing a syntax error upon seeing "type()" in general
> but not strongly since perfectly valid code (often back-stopped by table
> constraints) currently works and would otherwise have no reason to change
> other than to conform to this decision.

Another possibility is to issue a WARNING or NOTICE rather than hard
error.

            regards, tom lane