Thread: BUG #3040: Domain type handling change in 8.2.2 breaks declarations

The following bug has been logged online:

Bug reference:      3040
Logged by:          Dreas Nielsen
Email address:      dreas.nielsen@gmail.com
PostgreSQL version: 8.2.3
Operating system:   FreeBSD
Description:        Domain type handling change in 8.2.2 breaks declarations
Details:

If a domain type is declared NOT NULL,
and that domain type is used in a composite data type,
then declarations of that composite data type in a plpgsql function raise an
error because the custom domain value is NULL--and the composite data type
cannot be initialized so that it is non-null.

Example:
CREATE DOMAIN real_value
  AS double precision
  NOT NULL;

CREATE DOMAIN significant_digits
  AS integer
  DEFAULT 2
  NOT NULL;

CREATE TYPE measurement_result AS
   (value real_value,
    sig_figs significant_digits,
    std_dev double precision,
    undetected measurement_qualifier,
    estimated measurement_qualifier,
    rejected measurement_qualifier,
    greater_than measurement_qualifier);

CREATE OR REPLACE FUNCTION avg_mv_half(currstate measval_accum)
  RETURNS measurement_result AS
$BODY$
DECLARE
    rv measurement_result;
BEGIN
    rv := avg_mv_fact( currstate, 0.5 );
    RETURN rv;
END;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;


The declaration of the variable rv in function avg_mv_half() fails.  This
behavior appeared with version 8.2.2, and appears to be related to the item
in the release notes that says "Improve PL/pgSQL handling of domain types
(Sergiy Vyshnevetskiy, Tom)".  I suggest that restrictions on domain types
be enforced in the RETURN statement of a plpgsql function rather than in the
DECLARE statement.

The only workaround that I have found so far is to ALTER DOMAIN ... DROP NOT
NULL, but the whole point of defining those domains in the first place was
to enforce a NOT NULL constraint in the composite data type.

"Dreas Nielsen" <dreas.nielsen@gmail.com> writes:
> CREATE DOMAIN real_value
>   AS double precision
>   NOT NULL;

> CREATE DOMAIN significant_digits
>   AS integer
>   DEFAULT 2
>   NOT NULL;

> CREATE TYPE measurement_result AS
>    (value real_value,
>     sig_figs significant_digits,
>     std_dev double precision,
>     undetected measurement_qualifier,
>     estimated measurement_qualifier,
>     rejected measurement_qualifier,
>     greater_than measurement_qualifier);

> CREATE OR REPLACE FUNCTION avg_mv_half(currstate measval_accum)
>   RETURNS measurement_result AS
> $BODY$
> DECLARE
>     rv measurement_result;

> The declaration of the variable rv in function avg_mv_half() fails.

AFAICS it should do so, since you neglected to provide a non-null
initial value.

Now there is an implementation deficiency here, since if you try to
fix it:
    rv measurement_result := '(0,0 ...
you get
ERROR:  default value for row or record variable is not supported
CONTEXT:  compile of PL/pgSQL function "avg_mv_half" near line 2
I think that should be fixed, but that's not what you are claiming the
bug is.

> I suggest that restrictions on domain types
> be enforced in the RETURN statement of a plpgsql function rather than in the
> DECLARE statement.

If you don't want the domain restrictions enforced against the temporary
variable, don't declare it as being of the domain type.

Personally I think that NOT NULL domain restrictions are the stupidest
idea I've seen lately, as they break all sorts of behavior, starting with
outer joins.  But if you insist on using one, do not complain when it
gets enforced against you.

            regards, tom lane