Re: Behaviour of rows containg not-null domains in plpgsql

Hi

I just stumbled over the following behaviour, introduced with 8.3, and
wondered if this is by design or an oversight.

If you define a domain over some existing type, constrain it to
non-null values, and use that domain as a field type in a table
definition, it seems to be impossible to declare pl/pgsql variables
of that table's row type. The problem seems to be that upon declaration,
the row variable is filled with nulls - but since the domain is marked
not-null, that immediatly triggers an exception.

Here is an example
CREATE DOMAIN d AS varchar NOT NULL;
CREATE TABLE t (txt d);
CREATE FUNCTION f() RETURNS VOID AS $$
DECLARE
v_t t;
BEGIN
END;
$$ LANGUAGE 'plpgsql' VOLATILE;
SELECT f();

Note that the following works.
CREATE TABLE t2 (txt varchar not null);
CREATE FUNCTION f2() RETURNS VOID AS $$
DECLARE
v_t t2;
BEGIN
END;
$$ LANGUAGE 'plpgsql' VOLATILE;
SELECT f2();

If you guys agree that this is a bug, I'll try to find a fix and send a
patch.

greetings, Florian Pflug

Florian G. Pflug wrote:
> Hi
>
> I just stumbled over the following behaviour, introduced with 8.3, and
> wondered if this is by design or an oversight.
>
> If you define a domain over some existing type, constrain it to
> non-null values, and use that domain as a field type in a table
> definition, it seems to be impossible to declare pl/pgsql variables
> of that table's row type. The problem seems to be that upon declaration,
> the row variable is filled with nulls - but since the domain is marked
> not-null, that immediatly triggers an exception.
>
> Here is an example
> CREATE DOMAIN d AS varchar NOT NULL;
> CREATE TABLE t (txt d);
> CREATE FUNCTION f() RETURNS VOID AS $$
> DECLARE
> v_t t;
> BEGIN
> END;
> $$ LANGUAGE 'plpgsql' VOLATILE;
> SELECT f();
>
> Note that the following works.
> CREATE TABLE t2 (txt varchar not null);
> CREATE FUNCTION f2() RETURNS VOID AS $$
> DECLARE
> v_t t2;
> BEGIN
> END;
> $$ LANGUAGE 'plpgsql' VOLATILE;
> SELECT f2();
>
> If you guys agree that this is a bug, I'll try to find a fix and send
> a patch.
>
>

What seems worse is that it still fails even if you declare the domain
to have a default value.

cheers

andrew

Andrew Dunstan wrote:
> Florian G. Pflug wrote:
>> If you define a domain over some existing type, constrain it to
>> non-null values, and use that domain as a field type in a table
>> definition, it seems to be impossible to declare pl/pgsql variables
>> of that table's row type. The problem seems to be that upon
>> declaration, the row variable is filled with nulls - but since the
>> domain is marked not-null, that immediatly triggers an exception.
>>
>> Here is an example <snipped example>

> What seems worse is that it still fails even if you declare the
> domain to have a default value.
I didn't try that, but I *did* try was providing a default value for the
row variable - which doesn't work either, since we do not currently
support row variable defaults.

The only workaround I found was to define the variable as "record".

regards, Florian Pflug

"Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
> I just stumbled over the following behaviour, introduced with 8.3, and
> wondered if this is by design or an oversight.

No, this was in 8.2.

> If you define a domain over some existing type, constrain it to
> non-null values, and use that domain as a field type in a table
> definition, it seems to be impossible to declare pl/pgsql variables
> of that table's row type. The problem seems to be that upon declaration,
> the row variable is filled with nulls - but since the domain is marked
> not-null, that immediatly triggers an exception.

What else would you expect it to do? AFAICS any other behavior would be
contrary to spec.

regards, tom lane

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> What seems worse is that it still fails even if you declare the domain
> to have a default value.

Hmm, that seems like it could be a bug. We don't currently consider
that a rowtype includes the parent table's defaults or constraints.
But if we are going to honor a domain's constraints then maybe the
domain's default has to float along with that.

regards, tom lane

Tom Lane wrote:
> "Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
>> I just stumbled over the following behaviour, introduced with 8.3,
>> and wondered if this is by design or an oversight.
>
> No, this was in 8.2.
Ah, sorry - I'm porting an app from 8.1 straight to 8.3, and blindly
assumes that i'd have worked with 8.2...

>> If you define a domain over some existing type, constrain it to
>> non-null values, and use that domain as a field type in a table
>> definition, it seems to be impossible to declare pl/pgsql variables
>> of that table's row type. The problem seems to be that upon
>> declaration, the row variable is filled with nulls - but since the
>> domain is marked not-null, that immediatly triggers an exception.
>
> What else would you expect it to do? AFAICS any other behavior would
> be contrary to spec.
It's the inconsistency between row types (where the not-null contraint
in the table definition *doesn't* prevent a declaration like "myvar
mytable" in pl/pgsql), and domains (where the not-null constraint *does*
prevent such a declaration) that bugs me.

Plus, the fact that we don't support "default" specifications in
pl/pgsql for row types turns this inconvenience into a major PITA,
forcing you to use "record" when you know that correct type perfectly
well...

Is there some difficulty in implementing row-type defaults, or is it
just that nobody cared enough about them to do the work?

regards, Florian Pflug

"Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
> Plus, the fact that we don't support "default" specifications in
> pl/pgsql for row types turns this inconvenience into a major PITA,

You mean initialization expressions, not defaults, correct? (I would
consider the latter to mean that whatever attrdef entries were attached
to the rowtype's parent table would be used implicitly.)

> Is there some difficulty in implementing row-type defaults, or is it
> just that nobody cared enough about them to do the work?

The second statement is certainly true, I don't know about the first.
Feel free to take a shot at it.

regards, tom lane

Tom Lane wrote:
> "Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
>> Plus, the fact that we don't support "default" specifications in
>> pl/pgsql for row types turns this inconvenience into a major PITA,
>
> You mean initialization expressions, not defaults, correct? (I would
> consider the latter to mean that whatever attrdef entries were attached
> to the rowtype's parent table would be used implicitly.)
Yeah, I mean writing "declare; v_var schema.table default row(....)"

regards, Florian Pflug