Lists: | pgsql-hackers |
---|
From: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
---|---|
To: | Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | DEFAULT of domain ignored in plpgsql (8.4.1) |
Date: | 2009-11-20 02:06:10 |
Message-ID: | 4B05F992.2060001@phlo.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Hi
It seems that pl/pgsql ignores the DEFAULT value of domains for local
variables. With the following definitions in place
create domain myint as int default 0;
create or replace function myint() returns myint as $body$
declare
v_result myint;
begin
return v_result;
end;
$body$ language plpgsql immutable;
issuing
select myint();
returns NULL, not 0 on postgres 8.4.1
If the line
v_result myint;
is changes to
v_result myint default 0;
than 0 is returned as expected.
I've tried to create a patch, but didn't see how I'd convert the result
from get_typedefault() (A Node*, presumeably the parsetree corresponding
to the default expression?) into a plan that I could store in a
PLpgSQL_expr. I guess I'd need something like SPI_prepare_plan that
takes a parse tree instead of a query string. Or am I on a completely
wrong track there?
While trying to cook up a patch I've also stumbled over what I perceive
as a bug relating to DOMAINS and column DEFAULTs. I'll write that up in
a second E-Mail to avoid confusion.
best regards,
Florian Pflug
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
Cc: | Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: DEFAULT of domain ignored in plpgsql (8.4.1) |
Date: | 2009-11-20 16:37:40 |
Message-ID: | 603c8f070911200837wdad0aa2s165f4e5c7a93df02@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Thu, Nov 19, 2009 at 9:06 PM, Florian G. Pflug <fgp(at)phlo(dot)org> wrote:
> Hi
>
> It seems that pl/pgsql ignores the DEFAULT value of domains for local
> variables. With the following definitions in place
>
> create domain myint as int default 0;
> create or replace function myint() returns myint as $body$
> declare
> v_result myint;
> begin
> return v_result;
> end;
> $body$ language plpgsql immutable;
>
> issuing
> select myint();
> returns NULL, not 0 on postgres 8.4.1
>
> If the line
> v_result myint;
> is changes to
> v_result myint default 0;
> than 0 is returned as expected.
>
> I've tried to create a patch, but didn't see how I'd convert the result
> from get_typedefault() (A Node*, presumeably the parsetree corresponding
> to the default expression?) into a plan that I could store in a
> PLpgSQL_expr. I guess I'd need something like SPI_prepare_plan that
> takes a parse tree instead of a query string. Or am I on a completely
> wrong track there?
>
> While trying to cook up a patch I've also stumbled over what I perceive
> as a bug relating to DOMAINS and column DEFAULTs. I'll write that up in
> a second E-Mail to avoid confusion.
I suggest adding this to the open CommitFest (2010-01) at
https://commitfest.postgresql.org/action/commitfest_view/open
...Robert
From: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: DEFAULT of domain ignored in plpgsql (8.4.1) |
Date: | 2009-11-20 17:51:50 |
Message-ID: | 4B06D736.2080306@phlo.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Robert Haas wrote:
> On Thu, Nov 19, 2009 at 9:06 PM, Florian G. Pflug <fgp(at)phlo(dot)org> wrote:
>> I've tried to create a patch, but didn't see how I'd convert the result
>> from get_typedefault() (A Node*, presumeably the parsetree corresponding
>> to the default expression?) into a plan that I could store in a
>> PLpgSQL_expr. I guess I'd need something like SPI_prepare_plan that
>> takes a parse tree instead of a query string. Or am I on a completely
>> wrong track there?
>>
>> While trying to cook up a patch I've also stumbled over what I perceive
>> as a bug relating to DOMAINS and column DEFAULTs. I'll write that up in
>> a second E-Mail to avoid confusion.
>
> I suggest adding this to the open CommitFest (2010-01) at
> https://commitfest.postgresql.org/action/commitfest_view/open
Hm, but I don't (yet) have a patch to add...
best regards,
Florian Pflug
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
Cc: | Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: DEFAULT of domain ignored in plpgsql (8.4.1) |
Date: | 2009-11-20 17:53:59 |
Message-ID: | 603c8f070911200953k287adabbie007b50727e94983@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Fri, Nov 20, 2009 at 12:51 PM, Florian G. Pflug <fgp(at)phlo(dot)org> wrote:
> Robert Haas wrote:
>>
>> On Thu, Nov 19, 2009 at 9:06 PM, Florian G. Pflug <fgp(at)phlo(dot)org> wrote:
>>>
>>> I've tried to create a patch, but didn't see how I'd convert the result
>>> from get_typedefault() (A Node*, presumeably the parsetree corresponding
>>> to the default expression?) into a plan that I could store in a
>>> PLpgSQL_expr. I guess I'd need something like SPI_prepare_plan that
>>> takes a parse tree instead of a query string. Or am I on a completely
>>> wrong track there?
>>>
>>> While trying to cook up a patch I've also stumbled over what I perceive
>>> as a bug relating to DOMAINS and column DEFAULTs. I'll write that up in
>>> a second E-Mail to avoid confusion.
>>
>> I suggest adding this to the open CommitFest (2010-01) at
>> https://commitfest.postgresql.org/action/commitfest_view/open
>
> Hm, but I don't (yet) have a patch to add...
Woops, I saw an attachment and thought there was a patch, but it was
just smime.p7s... sorry for the noise.
...Robert
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
Cc: | Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: DEFAULT of domain ignored in plpgsql (8.4.1) |
Date: | 2009-11-20 23:47:35 |
Message-ID: | 19780.1258760855@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
"Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
> It seems that pl/pgsql ignores the DEFAULT value of domains for local
> variables.
The plpgsql documentation seems entirely clear on this:
The DEFAULT clause, if given, specifies the initial value assigned to
the variable when the block is entered. If the DEFAULT clause is not
given then the variable is initialized to the SQL null value.
regards, tom lane
From: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: DEFAULT of domain ignored in plpgsql (8.4.1) |
Date: | 2009-11-21 00:08:17 |
Message-ID: | 4B072F71.8050301@phlo.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Tom Lane wrote:
> "Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
>> It seems that pl/pgsql ignores the DEFAULT value of domains for
>> local variables.
>
> The plpgsql documentation seems entirely clear on this:
>
> The DEFAULT clause, if given, specifies the initial value assigned to
> the variable when the block is entered. If the DEFAULT clause is not
> given then the variable is initialized to the SQL null value.
Hm, must have missed that paragraph :-(. Sorry for that.
Would a patch that changes that have any chance of being accepted? Or is
the gain (not having to repeat the DEFAULT clause, and being able to
maintain it at one place instead of many) considered too small compared
to the risk of breaking existing code?
best regards,
Florian Pflug
From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: DEFAULT of domain ignored in plpgsql (8.4.1) |
Date: | 2009-11-21 01:56:36 |
Message-ID: | 4B0748D4.7070601@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
> Would a patch that changes that have any chance of being accepted? Or is
> the gain (not having to repeat the DEFAULT clause, and being able to
> maintain it at one place instead of many) considered too small compared
> to the risk of breaking existing code?
I don't think there's a lot of risk of code breakage; few people use
domains, fewer use them with defaults, and you might be the only one
using them as variable types. And there are going to be more
substantial backwards compat issues with the lexer changes anyway. As
long as we remember to flag the compatibility issue in the release
notes, I don't see it as a problem.
However, there are some other issues to be resolved:
(1) what should be the interaction of DEFAULT parameters and domains
with defaults?
(2) this change, while very useful, does change what had been a simple
rule ("All variables are NULL unless specifically set otherwise") into a
conditional one ("All variables are NULL unless set otherwise OR unless
they are declared as domain types with defaults"). Do people feel that
the new behavior would be sufficiently intuitive to avoid user confusion?
(3) Last I checked, there were still several places in which domains did
not behave consistently in stored procedures. I think that Elein had
some unfinished patches in this regard -- you'll want to search the
archives and the TODO list.
--Josh Berkus
From: | Grzegorz Jaskiewicz <gj(at)pointblue(dot)com(dot)pl> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: DEFAULT of domain ignored in plpgsql (8.4.1) |
Date: | 2009-11-21 09:20:47 |
Message-ID: | BA18C835-7E5C-4EE5-B4FA-0C5D25F90448@pointblue.com.pl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On 21 Nov 2009, at 02:56, Josh Berkus wrote:
>
>> Would a patch that changes that have any chance of being accepted? Or is
>> the gain (not having to repeat the DEFAULT clause, and being able to
>> maintain it at one place instead of many) considered too small compared
>> to the risk of breaking existing code?
>
> I don't think there's a lot of risk of code breakage; few people use
> domains, fewer use them with defaults, and you might be the only one
> using them as variable types. And there are going to be more
> substantial backwards compat issues with the lexer changes anyway. As
> long as we remember to flag the compatibility issue in the release
> notes, I don't see it as a problem.
we use domains with defaults, a lot. That's one of the purposes of domains, to have certain type, constraint, and default.
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>, Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: DEFAULT of domain ignored in plpgsql (8.4.1) |
Date: | 2009-11-21 15:52:14 |
Message-ID: | 14855.1258818734@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Josh Berkus <josh(at)agliodbs(dot)com> writes:
> (2) this change, while very useful, does change what had been a simple
> rule ("All variables are NULL unless specifically set otherwise") into a
> conditional one ("All variables are NULL unless set otherwise OR unless
> they are declared as domain types with defaults"). Do people feel that
> the new behavior would be sufficiently intuitive to avoid user confusion?
I'm inclined to leave it alone. It complicates the mental model, and
frankly attaching defaults to domains was not one of the SQL committee's
better ideas anyway. It's *fundamentally* non-orthogonal.
regards, tom lane
From: | Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: DEFAULT of domain ignored in plpgsql (8.4.1) |
Date: | 2009-11-21 16:27:16 |
Message-ID: | 65937bea0911210827m61d1e781i3d6cf90e06c79e18@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Sat, Nov 21, 2009 at 7:26 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
> > Would a patch that changes that have any chance of being accepted? Or is
> > the gain (not having to repeat the DEFAULT clause, and being able to
> > maintain it at one place instead of many) considered too small compared
> > to the risk of breaking existing code?
>
> I don't think there's a lot of risk of code breakage; few people use
> domains, fewer use them with defaults, and you might be the only one
> using them as variable types. And there are going to be more
> substantial backwards compat issues with the lexer changes anyway. As
> long as we remember to flag the compatibility issue in the release
> notes, I don't see it as a problem.
>
> However, there are some other issues to be resolved:
>
> (1) what should be the interaction of DEFAULT parameters and domains
> with defaults?
>
The function's DEFAULT parameter should take precedence over the default of
the domain.
>
> (2) this change, while very useful, does change what had been a simple
> rule ("All variables are NULL unless specifically set otherwise") into a
> conditional one ("All variables are NULL unless set otherwise OR unless
> they are declared as domain types with defaults"). Do people feel that
> the new behavior would be sufficiently intuitive to avoid user confusion?
>
I see this as a straight-forward extension to what we've had till now; and I
bet some users would've definitely expected them to work this way in the
past..
>
> (3) Last I checked, there were still several places in which domains did
> not behave consistently in stored procedures. I think that Elein had
> some unfinished patches in this regard -- you'll want to search the
> archives and the TODO list.
>
One thing to remember is that, that this behavior should be supported in all
PLs that support domain types as variables.
Best regards,
--
Lets call it Postgres
EnterpriseDB http://www.enterprisedb.com
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com
Twitter: singh_gurjeet
Skype: singh_gurjeet
Mail sent from my BlackLaptop device
From: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Josh Berkus <josh(at)agliodbs(dot)com>, Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: DEFAULT of domain ignored in plpgsql (8.4.1) |
Date: | 2009-11-21 21:20:17 |
Message-ID: | 4B085991.1070209@phlo.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> (2) this change, while very useful, does change what had been a
>> simple rule ("All variables are NULL unless specifically set
>> otherwise") into a conditional one ("All variables are NULL unless
>> set otherwise OR unless they are declared as domain types with
>> defaults"). Do people feel that the new behavior would be
>> sufficiently intuitive to avoid user confusion?
>
> I'm inclined to leave it alone. It complicates the mental model, and
> frankly attaching defaults to domains was not one of the SQL
> committee's better ideas anyway. It's *fundamentally*
> non-orthogonal.
I've always though of domains as being a kind of subtype of it's base
type. In this picture, DEFAULTs for domains correspond to overriding the
default constructor of the type (thinking C++ now), and seem like a
natural thing to have. But maybe that's more a C++ programmers than a
database designers point of view...
I've just checked how rowtypes behave, and while the "set to null unless
specifically set otherwise" rule kind of holds for them, their NULL
value seems to be special-cased enough to blur the line quite a bit
create or replace function myt() returns t as $body$
declare
r t;
begin
raise notice 'r: %, r is null: %', r, (r is null);
return r;
end;
$body$ language plpgsql immutable;
select myt(),myt() is null;
gives:
NOTICE: r: (,), r is null: t
NOTICE: r: (,), r is null: t
myt | ?column?
-----+----------
(,) | f
Strange I think... And at least half of an exception to the simple
"always null unless specifically set otherwise" rule
It also seems that while domain DEFAULTs are ignored, the resulting
(null-initialized) variable is still checked against the domain's
constraints, including a potential NOT NULL constraint
create domain myint as int not null;
create or replace function myint() returns myint as $body$
declare
i myint;
begin
return i;
end;
$body$ language plpgsql immutable;
raises
ERROR: domain myint does not allow null values
CONTEXT: PL/pgSQL function "myint" line 3 during statement block local
variable initialization
This has the potential to cause some headache I think if you use domains
to prohibit NULL values because they make no semantic sense for your
application, and depend on DEFAULT to fill in some other value (like an
empty string or an empty array).
best regards,
Florian Pflug
From: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
---|---|
To: | Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> |
Cc: | Josh Berkus <josh(at)agliodbs(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: DEFAULT of domain ignored in plpgsql (8.4.1) |
Date: | 2009-11-21 21:30:41 |
Message-ID: | 4B085C01.9040009@phlo.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Gurjeet Singh wrote:
> On Sat, Nov 21, 2009 at 7:26 AM, Josh Berkus <josh(at)agliodbs(dot)com
> <mailto:josh(at)agliodbs(dot)com>> wrote: However, there are some other
> issues to be resolved:
>
> (1) what should be the interaction of DEFAULT parameters and domains
> with defaults?
>
> The function's DEFAULT parameter should take precedence over the
> default of the domain.
I think Josh was pondering whether
create domain myint as int default 0;
create function f(i myint) ...;
should behave like
create function f(i myint default 0) ...;
and hence call f(0) if you do "select f();", or instead
raise an error because no f with zero parameters is defined (as it does
now).
I'd say no, because "no default" should be treated the same as "default
null", so for consistency we'd then have to also support
create function g(i int) ...;
select g();
And of course throw an error if there was another function defined as
create function g() ...;
This way leads to madness...
If one really wanted to do that, there'd have to be an OPTIONAL clause
for function parameters that works like DEFAULT, but doesn't take a
default value and instead uses the type's default (NULL except for
domains with DEFAULT clause). But I wouldn't got that far, personally...
best regards,
Florian Pflug
From: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
---|---|
To: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Josh Berkus <josh(at)agliodbs(dot)com>, Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: DEFAULT of domain ignored in plpgsql (8.4.1) |
Date: | 2009-11-21 23:48:50 |
Message-ID: | 1258847330.30675.8.camel@vanquo.pezone.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On lör, 2009-11-21 at 22:20 +0100, Florian G. Pflug wrote:
> > I'm inclined to leave it alone. It complicates the mental model, and
> > frankly attaching defaults to domains was not one of the SQL
> > committee's better ideas anyway. It's *fundamentally*
> > non-orthogonal.
>
> I've always though of domains as being a kind of subtype of it's base
> type. In this picture, DEFAULTs for domains correspond to overriding the
> default constructor of the type (thinking C++ now), and seem like a
> natural thing to have. But maybe that's more a C++ programmers than a
> database designers point of view...
There are other things in the SQL standard to do that, like types with
inheritance and types with constructors. We have already overextended
domains enough beyond what the SQL standards says, mostly because these
other things that are the correct solution are not implemented yet.