Thread: comparing NEW and OLD (any good this way?)
Hi, My colleage Geard Troost and I found a handy way of comparing OLD and NEW in a trigger function. Normally this does not work (if anyone can tell me why, that'd be great), but once you cast them to text, it does. Is there anything to say against this, or can i go ahead and recommend this to everyone who wants to check if anything changed before doing what their update triggers do? Cheers, WBL Here's the code: drop table test; create table test (id integer primary key, value integer); insert into test values (1,1); insert into test values (2,1); insert into test values (3,1); insert into test values (4,1); insert into test values (5,1); insert into test values (6,1); create or replace function bla() returns trigger as $$ begin IF (NEW::TEXT = OLD::TEXT) THEN raise notice 'changed'; END IF; return NEW; end $$ language plpgsql; CREATE TRIGGER test_bla BEFORE UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE public.bla(); update test set value =NULL where id= 1; update test set value =NULL where id= 1; -- "Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
Hello http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body regards Pavel Stehule 2009/7/23 Willy-Bas Loos <willybas@gmail.com>: > Hi, > > My colleage Geard Troost and I found a handy way of comparing OLD and > NEW in a trigger function. > Normally this does not work (if anyone can tell me why, that'd be > great), but once you cast them to text, it does. > > Is there anything to say against this, or can i go ahead and recommend > this to everyone who wants to check if anything changed before doing > what their update triggers do? > > Cheers, > > WBL > > Here's the code: > > drop table test; > create table test (id integer primary key, value integer); > insert into test values (1,1); > insert into test values (2,1); > insert into test values (3,1); > insert into test values (4,1); > insert into test values (5,1); > insert into test values (6,1); > > create or replace function bla() returns trigger as $$ > begin > IF (NEW::TEXT = OLD::TEXT) THEN raise notice 'changed'; > END IF; > return NEW; > end > $$ > language plpgsql; > > CREATE TRIGGER test_bla BEFORE UPDATE > ON test FOR EACH ROW > EXECUTE PROCEDURE public.bla(); > > update test set value =NULL where id= 1; > update test set value =NULL where id= 1; > > -- > "Patriotism is the conviction that your country is superior to all > others because you were born in it." -- George Bernard Shaw > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Pavel Stehule, 23.07.2009 13:45: > Hello > > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body > > regards > Pavel Stehule That collection of tips is really nice. Why isn't there a link from the Postgres Wiki to your page? Regards Thomas
Pavel Stehule schrieb: > Hello > > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body > > regards > Pavel Stehule > Pavel, this trick-list is awesome ;-) Thanks for the tip! Cheers Andy P.S.: a link to that would be nice ;-)
2009/7/23 Andreas Wenk <a.wenk@netzmeister-st-pauli.de>: > Pavel Stehule schrieb: >> >> Hello >> >> >> http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body >> >> regards >> Pavel Stehule >> > > Pavel, this trick-list is awesome ;-) Thanks for the tip! > > Cheers > > Andy > > P.S.: a link to that would be nice ;-) > look on http://wiki.postgresql.org/wiki/Category:Snippets There are link on tricks page. Pavel
Pavel Stehule, 23.07.2009 14:50: > look on http://wiki.postgresql.org/wiki/Category:Snippets > That page is not accessible from the Wiki's main page (at least I can't find an easy way to navigate there) I think there should be a prominent link right at the start page that links to that page and your excellent collection. Regards Thomas
On Thu, Jul 23, 2009 at 7:45 AM, Pavel Stehule<pavel.stehule@gmail.com> wrote: > Hello > > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body note: in PostgreSQL 8.4, you can compare record variables directly with standard boolean operators. merlin
On Thu, Jul 23, 2009 at 01:45:36PM +0200, Pavel Stehule wrote: > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks Just had a quick flick through your list and one of the early ones stuck out: http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Attention_on_IS_NULL_and_IS_NOT_NULL_operators_for_composite_types is scary; even worse is that it was changed to be like this in 8.2 because the standard says it should behave this way. What on earth were they thinking when they defined the standard this way? -- Sam http://samason.me.uk/
2009/7/23 Merlin Moncure <mmoncure@gmail.com>: > On Thu, Jul 23, 2009 at 7:45 AM, Pavel Stehule<pavel.stehule@gmail.com> wrote: >> Hello >> >> http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body > > note: in PostgreSQL 8.4, you can compare record variables directly > with standard boolean operators. > actualised Thank You Pavel > merlin >
On Thu, Jul 23, 2009 at 01:40:45PM +0200, Willy-Bas Loos wrote: > Is there anything to say against this, or can i go ahead and recommend > this to everyone who wants to check if anything changed before doing > what their update triggers do? Perhaps 8.4's suppress_redundant_updates_trigger() could be helpful in this case: http://www.postgresql.org/docs/8.4/interactive/functions-trigger.html -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com
Attachment
Thomas Kellerer wrote: > Pavel Stehule, 23.07.2009 14:50: > >look on http://wiki.postgresql.org/wiki/Category:Snippets > > That page is not accessible from the Wiki's main page (at least I > can't find an easy way to navigate there) > > I think there should be a prominent link right at the start page that > links to that page and your excellent collection. Agreed, just added one. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On 2009-07-23, Sam Mason <sam@samason.me.uk> wrote: > On Thu, Jul 23, 2009 at 01:45:36PM +0200, Pavel Stehule wrote: >> http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks > > Just had a quick flick through your list and one of the early ones stuck > out: > > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Attention_on_IS_NULL_and_IS_NOT_NULL_operators_for_composite_types > > is scary; even worse is that it was changed to be like this in 8.2 > because the standard says it should behave this way. What on earth were > they thinking when they defined the standard this way? since any comparson involving those tuples will return NULL true is the correct value for IS NULL if you are bothered by this behavior you are misusing NULL.
On Wed, Jul 29, 2009 at 01:15:27PM +0000, Jasen Betts wrote: > On 2009-07-23, Sam Mason <sam@samason.me.uk> wrote: > > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Attention_on_IS_NULL_and_IS_NOT_NULL_operators_for_composite_types > > > > is scary; even worse is that it was changed to be like this in 8.2 > > because the standard says it should behave this way. What on earth were > > they thinking when they defined the standard this way? > > since any comparson involving those tuples will return NULL true is the > correct value for IS NULL I think you missed the point: SELECT r IS NULL, r IS NOT NULL FROM (VALUES (1,NULL)) r(a,b); returns FALSE for *both* columns. How can a row be both NULL *and* non-NULL? > if you are bothered by this behavior you are misusing NULL. I understand that this is the specified behavior, and hence PG is correctly following the spec--but it still bothers me. -- Sam http://samason.me.uk/
On Wed, Jul 29, 2009 at 9:40 AM, Sam Mason<sam@samason.me.uk> wrote: > On Wed, Jul 29, 2009 at 01:15:27PM +0000, Jasen Betts wrote: >> On 2009-07-23, Sam Mason <sam@samason.me.uk> wrote: >> > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Attention_on_IS_NULL_and_IS_NOT_NULL_operators_for_composite_types >> > >> > is scary; even worse is that it was changed to be like this in 8.2 >> > because the standard says it should behave this way. What on earth were >> > they thinking when they defined the standard this way? >> >> since any comparson involving those tuples will return NULL true is the >> correct value for IS NULL > > I think you missed the point: > > SELECT r IS NULL, r IS NOT NULL > FROM (VALUES (1,NULL)) r(a,b); > > returns FALSE for *both* columns. How can a row be both NULL *and* > non-NULL? > >> if you are bothered by this behavior you are misusing NULL. > > I understand that this is the specified behavior, and hence PG is > correctly following the spec--but it still bothers me. not only that, but while pg's treats composite types with null members as null according to the 'is null' operator (in accordance with the spec), but as not null everywhere else. thus, for example, a 'null' composite type is counted in the count() aggregate function. how funky is that? merlin
> SELECT r IS NULL, r IS NOT NULL > FROM (VALUES (1,NULL)) r(a,b); > > returns FALSE for *both* columns. How can a row be both NULL *and* > non-NULL? Actually, the value is neither NULL, nor non-NULL. Part of it is NULL and part of it isn't so neither "IS NULL" is true, nor is "IS NOT NULL" cheers, WBL On Wed, Jul 29, 2009 at 3:40 PM, Sam Mason<sam@samason.me.uk> wrote: > On Wed, Jul 29, 2009 at 01:15:27PM +0000, Jasen Betts wrote: >> On 2009-07-23, Sam Mason <sam@samason.me.uk> wrote: >> > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Attention_on_IS_NULL_and_IS_NOT_NULL_operators_for_composite_types >> > >> > is scary; even worse is that it was changed to be like this in 8.2 >> > because the standard says it should behave this way. What on earth were >> > they thinking when they defined the standard this way? >> >> since any comparson involving those tuples will return NULL true is the >> correct value for IS NULL > > I think you missed the point: > > SELECT r IS NULL, r IS NOT NULL > FROM (VALUES (1,NULL)) r(a,b); > > returns FALSE for *both* columns. How can a row be both NULL *and* > non-NULL? > >> if you are bothered by this behavior you are misusing NULL. > > I understand that this is the specified behavior, and hence PG is > correctly following the spec--but it still bothers me. > > -- > Sam http://samason.me.uk/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- "Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
On Wed, Aug 12, 2009 at 10:51:04AM +0200, Willy-Bas Loos wrote: > > SELECT r IS NULL, r IS NOT NULL > > FROM (VALUES (1,NULL)) r(a,b); > > > > returns FALSE for *both* columns. How can a row be both NULL *and* > > non-NULL? > > Actually, the value is neither NULL, nor non-NULL. > Part of it is NULL and part of it isn't so neither "IS NULL" is true, > nor is "IS NOT NULL" Nope, I still don't get it. Why treat rows specially? If this was true, then what should: SELECT a IS NULL, a IS NOT NULL FROM (SELECT ARRAY [1,NULL]) x(a); evaluate to? As "part of it" is NULL and part isn't then, by your reasoning, it should return TRUE for both. PG doesn't and I think this is much more useful behavior. The value itself is not unknown, it just happens to contain some unknown values. Having a row that consists entirely of NULL values being treated as NULL is OK, but some weird halfway house is horrible. Standards' conforming, but still horrible. -- Sam http://samason.me.uk/
Sam Mason wrote: > Nope, I still don't get it. Why treat rows specially? If this was > true, then what should: > > SELECT a IS NULL, a IS NOT NULL > FROM (SELECT ARRAY [1,NULL]) x(a); > > evaluate to? As "part of it" is NULL and part isn't then, by your > reasoning, it should return TRUE for both. PG doesn't and I think this > is much more useful behavior. But IS NULL applied to an array is useless to test if there are null values inside, whereas this is apparently the whole point of IS NULL applied to rows. I mean: select a is null from (select array[null]) x(a); returns false, as well as: select a is null from (select array[1]) x(a); When applied to rows, if you consider that: - is null applied to a row means that all columns are null - is not null applied to a row means that all columns are not null which is what the standard seems to dictate, then these operators make sense and are probably useful in some situations. Now there is the unfortunate consequence that (r is null) is not equivalent to (not (r is not null)), yet it's not the standard's fault if "not all values are null" is not the same as "all values are not null", that's just set logic. Maybe they could have made this easier for us by naming the operators differently, such as "is entirely null" and "is entirely not null" Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On Wed, Aug 12, 2009 at 10:14 AM, Daniel Verite<daniel@manitou-mail.org> wrote: > Sam Mason wrote: > >> Nope, I still don't get it. Why treat rows specially? If this was >> true, then what should: >> >> SELECT a IS NULL, a IS NOT NULL >> FROM (SELECT ARRAY [1,NULL]) x(a); >> >> evaluate to? As "part of it" is NULL and part isn't then, by your >> reasoning, it should return TRUE for both. PG doesn't and I think this >> is much more useful behavior. > > But IS NULL applied to an array is useless to test if there are null values > inside, whereas this is apparently the whole point of IS NULL applied to > rows. > I mean: > select a is null from (select array[null]) x(a); > returns false, as well as: > select a is null from (select array[1]) x(a); > > When applied to rows, if you consider that: > - is null applied to a row means that all columns are null > - is not null applied to a row means that all columns are not null > which is what the standard seems to dictate, then these operators make sense > and are probably useful in some situations. > > Now there is the unfortunate consequence that (r is null) is not equivalent > to (not (r is not null)), yet it's not the standard's fault if "not all > values are null" is not the same as "all values are not null", that's just > set logic. > > Maybe they could have made this easier for us by naming the operators > differently, such as "is entirely null" and "is entirely not null" IMO, the standard really blew it. PostgreSQL's approach is ok, minimal standards compliance balanced out with practical considerations. This leads to some strange behaviors as noted upthread, but it's workable if you know the tricks. I guess it's not very well documented.... merlin
On Wed, Aug 12, 2009 at 04:14:31PM +0200, Daniel Verite wrote: > But IS NULL applied to an array is useless to test if there are null values > inside, whereas this is apparently the whole point of IS NULL applied to > rows. > I mean: > select a is null from (select array[null]) x(a); > returns false, as well as: > select a is null from (select array[1]) x(a); Yes, I know. But it seems to be a somewhat arbitrary choice to handle IS NULL for rows differently from everything else. > When applied to rows, if you consider that: > - is null applied to a row means that all columns are null > - is not null applied to a row means that all columns are not null > which is what the standard seems to dictate, then these operators make sense > and are probably useful in some situations. Yes, I understand what it's specified to do and that it's consistent with SQL spec. I just think (and Merlin seems to agree) that the spec has specified the "wrong" behavior. > Now there is the unfortunate consequence that (r is null) is not equivalent > to (not (r is not null)), yet it's not the standard's fault if "not all > values are null" is not the same as "all values are not null", that's just > set logic. Yes; but this means the user now has to be aware of exactly which type their code is using as the behavior of various things will magically change in rare circumstances. > Maybe they could have made this easier for us by naming the operators > differently, such as "is entirely null" and "is entirely not null" Yes, this would be *much* more preferable. For people aware of it this it's obviously an easy translation to make, but it's a nasty waiting for those who aren't and especially for anybody doing anything formal. I.e. when reasoning about operator semantics you suddenly have to know the type of data you're dealing with before you can say useful things about the result. There will of course be ways of avoiding the general case of an exponential increase in complexity, but it's still nasty. Anybody else think this thread is past it's bed time and should be put to rest? -- Sam http://samason.me.uk/
Sam Mason wrote: > But it seems to be a somewhat arbitrary choice to handle > IS NULL for rows differently from everything else. For scalar or array types, "is null" means that the value happens to be that special value that we call null. No conceptual problem here. But for rows, there is no such thing. You can't assign null to a row, it makes no sense and actually causes an error. Starting from that point, what consistency can we expect for the "is null" operator across row types and other types? > Yes, I understand what it's specified to do and that it's consistent > with SQL spec. I just think (and Merlin seems to agree) that the spec > has specified the "wrong" behavior. So for you guys, what would be the "right" behavior? Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On Wed, Aug 12, 2009 at 08:02:10PM +0200, Daniel Verite wrote: >Sam Mason wrote: > > But it seems to be a somewhat arbitrary choice to handle > > IS NULL for rows differently from everything else. > > For scalar or array types, "is null" means that the value happens to be that > special value that we call null. No conceptual problem here. > But for rows, there is no such thing. You can't assign null to a row, it > makes no sense and actually causes an error. What makes you say this? There's no reason I can see that would cause row values should be special in this way. Maybe if you could define what you mean by "you can't assign null to a row"? > Starting from that point, what consistency can we expect for the "is null" > operator across row types and other types? Values of row type are the only time when v IS NOT NULL and NOT v IS NULL are not synonymous. > > Yes, I understand what it's specified to do and that it's consistent > > with SQL spec. I just think (and Merlin seems to agree) that the spec > > has specified the "wrong" behavior. > > So for you guys, what would be the "right" behavior? For me anyway, that the above actually holds true. -- Sam http://samason.me.uk/
Sam Mason wrote: > > But for rows, there is no such thing. You can't assign null to a row, it > > makes no sense and actually causes an error. > > What makes you say this? There's no reason I can see that would cause > row values should be special in this way. Maybe if you could define > what you mean by "you can't assign null to a row"? It seems to me that there is something special with rows: in tables, the values of columns may be null or not, but at the level of the row, there is no information that would say: this row itself as an object is null. Anyway, let's try to assign null to a row variable (with 8.4.0): CREATE TABLE our_table(i int); CREATE FUNCTION test() returns void as $$ declare r our_table; begin r:=null; end; $$ LANGUAGE plpgsql; SELECT test() yields: ERROR: cannot assign non-composite value to a row variable CONTEXT: PL/pgSQL function "test" line 4 at assignment As a follow-up to the comparison between rows and arrays, note that if we'd make r an int[], there would be no error. However, I agree that if we consider that a row is a composite type, then there is a problem because we sure can insert NULL into a column that is of a composite type. So the "row cannot be null" line of reasoning holds only so far as you don't stuff rows into columns :) Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On Wed, Aug 12, 2009 at 10:57:54PM +0200, Daniel Verite wrote:
> It seems to me that there is something special with rows: in tables, the
> values of columns may be null or not, but at the level of the row, there is
> no information that would say: this row itself as an object is null.
Hum, there seem to be lots of different things happening here--lets try
and untangle them a bit. I would say that the following returns a null
value of type row (actually a pair of integers):
SELECT b
FROM (SELECT 1) a
LEFT JOIN (SELECT 1,2) b(b1,b2) ON FALSE;
It currently gets serialized as '\N' in the output of psql for me, but
I'd have no problem if it appeared as '(,)'. Both of these seem like
valid representations of a null row to me.
In other discussions about similar issues I've said that the expression:
ROW(NULL,NULL) IS DISTINCT FROM NULL
should evaluate to FALSE. I still think this is correct and generally
useful behavior.
> Anyway, let's try to assign null to a row variable (with 8.4.0):
>
> CREATE TABLE our_table(i int);
>
> CREATE FUNCTION test() returns void as $$
> declare
> r our_table;
> begin
> r:=null;
> end;
> $$ LANGUAGE plpgsql;
>
> SELECT test() yields:
> ERROR: cannot assign non-composite value to a row variable
> CONTEXT: PL/pgSQL function "test" line 4 at assignment
This just looks like PG missing a feature. plpgsql has much less user
and developer time spent on it, so I'd expect to find more strangeness
in darker corners like this.
> As a follow-up to the comparison between rows and arrays, note that if we'd
> make r an int[], there would be no error.
OK, maybe people just do this more often and hence there's been a reason
to make it work.
> However, I agree that if we consider that a row is a composite type, then
> there is a problem because we sure can insert NULL into a column that is of a
> composite type. So the "row cannot be null" line of reasoning holds only so
> far as you don't stuff rows into columns :)
When you say "columns", do you mean the value associated with a
particular attribute in a particular row of a particular table? Surely
this is a normal value and just because it happens to be stored in a
table it shouldn't be any different from any other value anywhere else
in PG.
--
Sam http://samason.me.uk/
> > This just looks like PG missing a feature. plpgsql has much less user > and developer time spent on it, so I'd expect to find more strangeness > in darker corners like this. > this rule should be simply removed. It's not problem. The people long time believe so row cannot be null ever. I don't know if this is from Oracle or somewhere. SQL/PSM allows it. This semantic is little bit difficult. There is rule so any object is NULL when all fields is NULL too. I thing, so it's true. There is object, that has zero information. When You thinking about it, you have to forgot any your knowledges from languages that's knows pointers. Maybe some people has problem, because they put in equality NULL from SQL and NULL pointer. regards Pavel Stehule
Sam Mason wrote: > Hum, there seem to be lots of different things happening here--lets try > and untangle them a bit. I would say that the following returns a null > value of type row (actually a pair of integers): > > SELECT b > FROM (SELECT 1) a > LEFT JOIN (SELECT 1,2) b(b1,b2) ON FALSE; > > It currently gets serialized as '\N' in the output of psql for me, but > I'd have no problem if it appeared as '(,)'. Both of these seem like > valid representations of a null row to me. If we query that result with libpq functions, it appears that the result is a row that contains a field named "b" of type record (oid=2249), and that field is null. So if we consider that this field is a row, then yeah it's a null row. > In other discussions about similar issues I've said that the expression: > > ROW(NULL,NULL) IS DISTINCT FROM NULL > > should evaluate to FALSE. I still think this is correct and generally > useful behavior. I see no reason to disagree with this. Besides, the fact that ROW(NULL,NULL) IS DISTINCT FROM NULL evaluates to true while ROW(NULL,NULL) IS NULL also evaluates to true looks quite puzzling to me. > > However, I agree that if we consider that a row is a composite type, then > > there is a problem because we sure can insert NULL into a column that is of a > > composite type. So the "row cannot be null" line of reasoning holds only so > > far as you don't stuff rows into columns :) > > When you say "columns", do you mean the value associated with a > particular attribute in a particular row of a particular table? That's what I meant, yes. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On Thu, Aug 13, 2009 at 11:44 PM, Daniel Verite<daniel@manitou-mail.org> wrote: >> In other discussions about similar issues I've said that the expression: >> >> ROW(NULL,NULL) IS DISTINCT FROM NULL >> >> should evaluate to FALSE. I still think this is correct and generally >> useful behavior. > > I see no reason to disagree with this. Besides, the fact that > ROW(NULL,NULL) IS DISTINCT FROM NULL evaluates to true > while ROW(NULL,NULL) IS NULL also evaluates to true > looks quite puzzling to me. Why is this thread still going on? What does the spec say we should be doing and are we violating it in any of these cases? -- greg http://mit.edu/~gsstark/resume.pdf
On Thu, Aug 13, 2009 at 11:53:49PM +0100, Greg Stark wrote: > On Thu, Aug 13, 2009 at 11:44 PM, Daniel Verite<daniel@manitou-mail.org> wrote: > >> In other discussions about similar issues I've said that the expression: > >> > >> ROW(NULL,NULL) IS DISTINCT FROM NULL > >> > >> should evaluate to FALSE. I still think this is correct and generally > >> useful behavior. > > > > I see no reason to disagree with this. Besides, the fact that > > ROW(NULL,NULL) IS DISTINCT FROM NULL evaluates to true > > while ROW(NULL,NULL) IS NULL also evaluates to true > > looks quite puzzling to me. > > Why is this thread still going on? Because I'm a stickler for details and people keep replying! > What does the spec say we should be > doing and are we violating it in any of these cases? Whenever I've looked through I've not found anything definite either way. I think my interests here are more pedagogical that anything else, but PG's behavior is somewhat inconsistent and it could be nice to figure out what the "best" way of fixing these inconsistencies are. -- Sam http://samason.me.uk/
Greg Stark wrote: > Why is this thread still going on? Sorry, it's still going on. Call me a slow learner if you want :) > What does the spec say we should be > doing and are we violating it in any of these cases? After a bit more reading, I believe the bottom line is: while the spec says that if X is the null value, then "X is null" evaluates to true, it doesn't say that if "X is null" is true, then X is the null value. And that's the catch. Once digested the (counter-intuitive) rule that "X is null" should never be confused with "X is the null value", then PG's behavior suddenly feels consistant. For example, when evaluating "X is distinct FROM null", the fact that "X is null" returns true is irrelevant and is not considered. What is tested is whether X evaluates to null or not. The spec says "A null value and a non-null value are distinct". Since "A null value" is NOT equivalent to "an expression on which IS NULL returns true", the fact that "ROW(null,null) is distinct FROM null" evaluates to true doesn't violate the spec. I believe the implication of this weirdness for SQL programmers is that when we feel like using "IS NULL" and rowtypes are involved, we should think hard about what we really want to test and possibly use "IS DISTINCT FROM NULL" rather than "IS NULL". Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
Sam Mason wrote: > > CREATE FUNCTION test() returns void as $$ > > declare > > r our_table; > > begin > > r:=null; > > end; > > $$ LANGUAGE plpgsql; > > > > SELECT test() yields: > > ERROR: cannot assign non-composite value to a row variable > > CONTEXT: PL/pgSQL function "test" line 4 at assignment > > This just looks like PG missing a feature. plpgsql has much less user > and developer time spent on it, so I'd expect to find more strangeness > in darker corners like this. Actually if I had written r:=null::our_table then this would have worked, which negates the point I was trying to make. I'm not sure if it's an oversight of plpgsql that null by itself is not accepted in this context, but anyway my idea that it had something to do with a conceptual problem was wrong. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On Mon, Aug 17, 2009 at 03:45:02PM +0200, Daniel Verite wrote: > while the spec says that if X is the null value, then "X is null" > evaluates to true, it doesn't say that if "X is null" is true, then X > is the null value. And that's the catch. But you've had to introduce a whole new abstraction (that there is this difference) just to make sense of the spec and PGs implementation of it. Most people struggle with NULL enough, it's madness to introduce another layer to say that when something says it's NULL it's not actually NULL. > Once digested the (counter-intuitive) rule that "X is null" should never be > confused with "X is the null value", then PG's behavior suddenly feels > consistant. Because there's a hack in there to make RECORDs special. Apart from them IS NULL is completely polymorphic with respect to the datatype it's operating over. Internally there's a nice structure to track what's *really* NULL and what's not, this is reported on for *everything* except RECORDs. I've just realized another case where it's not consistent; why does the following return true: SELECT row(null) IS NULL; and yet the following false: SELECT row(row(null)) IS NULL; > I believe the implication of this weirdness for SQL programmers is that when > we feel like using "IS NULL" and rowtypes are involved, we should think hard > about what we really want to test and possibly use "IS DISTINCT FROM NULL" > rather than "IS NULL". So when is IS NULL ever to be used then? I don't think I've ever written code that uses IS NULL the way that the spec defines it. I've wanted "v IS NULL" to mean the same as "v IS NOT DISTINCT FROM NULL", this being the same as "NOT (v IS DISTINCT FROM NULL) lots of times, but if I'm interested in knowing if a member of a RECORD is NULL then I want to know specifically which attribute it is. I think I'm saying that PG should be deliberately breaking specified behavior and go back to pre-8.2 behavior in this regard. -- Sam http://samason.me.uk/
Sam Mason wrote: > I've just realized another case where it's not consistent; why does the > following return true: > > SELECT row(null) IS NULL; > > and yet the following false: > > SELECT row(row(null)) IS NULL; You're intentionally assuming that row(null) IS NULL evaluating to true implies that row(null) can be replaced by NULL. As discussed upthread, this is not the case. > I think I'm saying that PG should be deliberately breaking specified > behavior and go back to pre-8.2 behavior in this regard. But let's run your example with 8.1: # SELECT row(null) IS NULL; ?column? ---------- t # SELECT row(row(null)) IS NULL; ?column? ---------- f These are the same results that you say are inconsistant, so pre-8.2 behavior doesn't help here... Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On Tue, Aug 18, 2009 at 10:32:35AM +0200, Daniel Verite wrote: > Sam Mason wrote: > > I've just realized another case where it's not consistent; why does the > > following return true: > > > > SELECT row(null) IS NULL; > > > > and yet the following false: > > > > SELECT row(row(null)) IS NULL; > > You're intentionally assuming that row(null) IS NULL evaluating to true > implies that row(null) can be replaced by NULL. As discussed upthread, this > is not the case. But you've still not said how is this useful! I can reformulate maths so that 1+0 <> 1+(0), but this is not useful behavior. Programmers need logical abstractions upon which to build and without them you end up with even more bugs. > > I think I'm saying that PG should be deliberately breaking specified > > behavior and go back to pre-8.2 behavior in this regard. > > But let's run your example with 8.1: > > # SELECT row(null) IS NULL; > ?column? > ---------- > t > > # SELECT row(row(null)) IS NULL; > ?column? > ---------- > f > > These are the same results that you say are inconsistant, so pre-8.2 behavior > doesn't help here... Doh, that'll learn me--I never actually tried older versions. I was just repeating what the docs said about the behavior changing in 8.2. http://www.postgresql.org/docs/current/static/functions-comparison.html#AEN7444 and was mis-interpreting what it was saying. -- Sam http://samason.me.uk/
Sam Mason wrote: > > You're intentionally assuming that row(null) IS NULL evaluating to true > > implies that row(null) can be replaced by NULL. As discussed upthread, this > > is not the case. > > But you've still not said how is this useful! To me, IS NULL applied to rows, as a test of combined-nullnesss of the columns inside the row, doesn't indeed look like something I'd use on a regular basis, if at all. But I'll use IS DISTINCT FROM NULL on records. I sympathize with the opinion that the standard "hijacks" the IS NULL operator for rows in a way that is problematic (though not unworkable). But who cares if it's not useful to some, or even to the majority? The standard opted for that definition years ago, and also PG opted to implement it. It's too late. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org