FAQ
This is the reverse of what I thought I would find.

In short my check constraint is extracting the epoch from a start timestamp,
and an end timestamp to get the number of seconds difference.
It then uses this number to check the array_upper() of an array to make sure
it's the proper size

The SQL version uses a case statement, and the plpgsql uses an IF/ELSE

In a particular insert test
The plpgsql version adds 1 second over the no constraints case.
the sql version adds 10 seconds over the no constraints case.

Why would this be?

--->

CREATE OR REPLACE FUNCTION check_end_time_foo( _start_time TIMESTAMP,
_end_time TIMESTAMP, _granularity SMALLINT, _values DOUBLE PRECISION[] )
RETURNS boolean AS $$
BEGIN
if( _granularity = 5 )
THEN
return( EXTRACT( EPOCH FROM _end_time at time zone 'utc' )::BIGINT -
EXTRACT( EPOCH FROM _start_time at time zone 'utc' )::BIGINT = 60 *
array_upper( _values,1 ) );
ELSEIF( _granularity = 7 )
THEN
return( EXTRACT( EPOCH FROM _end_time at time zone 'utc' )::BIGINT -
EXTRACT( EPOCH FROM _start_time at time zone 'utc' )::BIGINT = 900 *
array_upper( _values,1 ) );
ELSEIF( _granularity = 9 )
THEN
return( EXTRACT( EPOCH FROM _end_time at time zone 'utc' )::BIGINT -
EXTRACT( EPOCH FROM _start_time at time zone 'utc' )::BIGINT = 3600 *
array_upper( _values,1 ) );
ELSEIF( _granularity = 12 )
THEN
return( ( (EXTRACT( YEAR FROM (_end_time at time zone 'utc') at time
zone 'utc' )::INT * 12) + EXTRACT( MONTH FROM (_end_time at time zone 'utc'
) at time zone 'utc' )::INT ) - ( (EXTRACT( YEAR FROM (_start_time at time
zone 'utc') at time zone 'utc' )::INT * 12) + EXTRACT( MONTH FROM
(_start_time at time zone 'utc') at time zone 'utc' )::INT ) = array_upper(
_values,1 ) );
END IF;
END;
$$ language plpgsql IMMUTABLE;

alter table timeseries add CONSTRAINT timeseries_valid_end_time CHECK(
check_end_time_foo( series_start_time, series_end_time, granularity,
data_value ) );

-vs-


alter table timeseries add CONSTRAINT timeseries_valid_end_time CHECK( CASE
WHEN granularity = 5

THEN

EXTRACT( EPOCH FROM series_end_time at time zone 'utc' )::BIGINT - EXTRACT(
EPOCH FROM series_start_time at time zone 'utc' )::BIGINT = 60 *
array_upper( data_value,1 )

WHEN granularity = 7

THEN

EXTRACT( EPOCH FROM series_end_time at time zone 'utc' )::BIGINT - EXTRACT(
EPOCH FROM series_start_time at time zone 'utc' )::BIGINT = 900 *
array_upper( data_value,1 )

WHEN granularity = 9

THEN

EXTRACT( EPOCH FROM series_end_time at time zone 'utc' )::BIGINT - EXTRACT(
EPOCH FROM series_start_time at time zone 'utc' )::BIGINT = 3600 *
array_upper( data_value,1 )

WHEN granularity = 12

THEN

((EXTRACT( YEAR FROM ( series_end_time at time zone 'utc') at time zone
'utc' )::INT * 12) + EXTRACT( MONTH FROM ( series_end_time at time zone
'utc' ) at time zone 'utc' )::INT ) - ( (EXTRACT( YEAR FROM (
series_start_time at time zone 'utc') at time zone 'utc' )::INT * 12) +
EXTRACT( MONTH FROM ( series_start_time at time zone 'utc') at time zone
'utc' )::INT ) = array_upper( data_value,1 )

ELSE

false
END
);

Search Discussions

  • Tom Lane at Jun 17, 2011 at 11:25 pm

    bubba postgres writes:
    This is the reverse of what I thought I would find.
    In short my check constraint is extracting the epoch from a start timestamp,
    and an end timestamp to get the number of seconds difference.
    It then uses this number to check the array_upper() of an array to make sure
    it's the proper size
    The SQL version uses a case statement, and the plpgsql uses an IF/ELSE
    In a particular insert test
    The plpgsql version adds 1 second over the no constraints case.
    the sql version adds 10 seconds over the no constraints case.
    Why would this be?
    It would not likely be faster unless it can be inlined, and maybe not
    even then, because of plan caching effects (plpgsql is a lot better
    about that). In this particular case, I'm suspicious whether all the
    operations are immutable; if they aren't, the marking of the function
    as immutable will definitely prevent inlining.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJun 17, '11 at 10:45p
activeJun 17, '11 at 11:25p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Tom Lane: 1 post Bubba postgres: 1 post

People

Translate

site design / logo © 2022 Grokbase