Thread: Chicken/egg problem with range types

I'm trying to create a discrete range type and I'm having trouble with
the canonical function.

--Create shell type
CREATE TYPE dt_range;

--Create subtype diff
CREATE OR REPLACE FUNCTION dt_subtype_diff(timestamptz, timestamptz)
RETURNS float8 AS
$$
   SELECT EXTRACT(EPOCH FROM $1 - $2);
$$ LANGUAGE 'sql' IMMUTABLE STRICT;

-- Create the canonical function
CREATE OR REPLACE FUNCTION dt_range_canonical(dt_range)
RETURNS dt_range AS
$$
   SELECT dt_range(
     CASE WHEN lower_inc($1)
         THEN lower($1)::timestampTz(0)
         ELSE lower($1)::timestampTz(0) - INTERVAL '1s' END,
     CASE WHEN NOT upper_inc($1)
         THEN upper($1)::timestampTz(0)
         ELSE upper($1)::timestampTz(0) + INTERVAL '1s' END
   );
$$ LANGUAGE 'sql' IMMUTABLE STRICT;

Fails with ERROR: SQL function cannot accept shell type dt_range. So I
add the type and try to alter it later.

-- Create the type any way
CREATE TYPE dt_range AS RANGE (
   SUBTYPE = timestamptz,
   SUBTYPE_DIFF = dt_subtype_diff
   -- CANONICAL = dt_range_canonical -- can't use, fn doesn't exist
);

ALTER TYPE dt_range SET CANONICAL = dt_range_canonical;

This doesn't work either. I'm stuck.

Scott Bailey

Scott Bailey <artacus72@gmail.com> writes:
> I'm trying to create a discrete range type and I'm having trouble with
> the canonical function.

Yeah, right now you really can't write canonical functions in anything
except C, for which we don't enforce the no-shell-types restriction.

Before range types, this wasn't a big deal since it was only an issue
for I/O functions, which pretty much have to be in C anyway.  But your
example shows that at least for prototyping, a SQL or PL function could
usefully be used as a canonical function.

I wonder whether we could improve this by postponing the no-shell-types
check from creation to function runtime.  It would be annoying to have
to make an additional catalog lookup at runtime just for typisdefined,
but I think that probably we could fold it in with an existing fetch of
the pg_type row during parsing of the calling query, so that no run-time
overhead is added.

This would limit what checking could be performed on the function body
at creation time, but surely no worse than, say, a reference to a
nonexistent table, which we allow.

Another thing that would have to be thought about is security: external
PLs would very possibly not get the word about needing to check for
shell arguments/results themselves, and we'd need to make certain that
nothing too awful could happen if they didn't.  That doesn't seem
insoluble but it would take some thought.  A different security angle is
making sure that nothing too awful happens if someone tries to use a SQL
or PL function as a type I/O function, which would now not be forbidden
by the no-shell-types restriction.

            regards, tom lane

On 18 Jul 2012, at 5:08, Tom Lane wrote:

> Scott Bailey <artacus72@gmail.com> writes:
>> I'm trying to create a discrete range type and I'm having trouble with
>> the canonical function.

> I wonder whether we could improve this by postponing the no-shell-types
> check from creation to function runtime.  It would be annoying to have
> to make an additional catalog lookup at runtime just for typisdefined,
> but I think that probably we could fold it in with an existing fetch of
> the pg_type row during parsing of the calling query, so that no run-time
> overhead is added.


I don't suppose it'd be possible to treat it as a deferred constraint? Then the check would be moved to the end of the
transaction.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

Alban Hertroys <haramrae@gmail.com> writes:
> On 18 Jul 2012, at 5:08, Tom Lane wrote:
>> I wonder whether we could improve this by postponing the no-shell-types
>> check from creation to function runtime.

> I don't suppose it'd be possible to treat it as a deferred constraint? Then the check would be moved to the end of
thetransaction. 

You mean, after we've already crashed, or allowed a security breach to
happen?  Doesn't sound very helpful.  In any case, my concern is that
there not be any added overhead, not about moving it around.

            regards, tom lane

On 07/17/2012 08:08 PM, Tom Lane wrote:
> I wonder whether we could improve this by postponing the no-shell-types
> check from creation to function runtime.  It would be annoying to have
> to make an additional catalog lookup at runtime just for typisdefined,
> but I think that probably we could fold it in with an existing fetch of
> the pg_type row during parsing of the calling query, so that no run-time
> overhead is added.
>
> This would limit what checking could be performed on the function body
> at creation time, but surely no worse than, say, a reference to a
> nonexistent table, which we allow.


How about using ALTER TYPE to set it after both the function and the
type have been created?

Scott

On 18 Jul 2012, at 16:15, Tom Lane wrote:

> Alban Hertroys <haramrae@gmail.com> writes:
>> On 18 Jul 2012, at 5:08, Tom Lane wrote:
>>> I wonder whether we could improve this by postponing the no-shell-types
>>> check from creation to function runtime.
>
>> I don't suppose it'd be possible to treat it as a deferred constraint? Then the check would be moved to the end of
thetransaction. 
>
> You mean, after we've already crashed, or allowed a security breach to
> happen?  Doesn't sound very helpful.  In any case, my concern is that
> there not be any added overhead, not about moving it around.


I did mean the transaction in which the dependency got created, not some later transaction in which it gets used. I'm
fairlysure that'd be before any crashes or security breaches. Or is that not what you're implying? 

Basically:
BEGIN;
CREATE TYPE dt_range AS (..., CANONICAL = dt_range_canonical); -- check for CANONICAL function deferred
CREATE FUNCTION dt_range_canonical(dt_range) ...; -- now it exists
COMMIT; -- here the check for the CANONICAL function is performed

I did make an assumption in there that the dependency doesn't get used before the creating transaction committed. Using
thedependency in the same transaction that created it should probably not be allowed, or the check should be moved to
functionruntime if that happens (like in your original suggestion). 


Another alternative, which would probably require a major effort to implement, would be to make CREATE FUNCTION
inlinable(is that the right word?) by making the DDL statement return the function identifier of the function it just
created.

With that, the dependency of the OP would be created somewhat like:

CREATE TYPE dt_range AS (..., CANONICAL = (CREATE FUNCTION dt_range_canonical(dt_range) ...));

Some languages use similar constructs, for example to assign methods to object prototypes in the case of Javascript. I
know,SQL is not object oriented (but neither is Javascript, strictly speaking). 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.