Message-ID: <52AA3156.20902@ultimeth.com>
Date: Thu, 12 Dec 2013 13:57:42 -0800
From: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
Organization: UltiMeth Systems
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:16.0) Gecko/20121026 Thunderbird/16.0.2
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: NULLs and composite types
References: <52A9010D.3070202@ultimeth.com>
 <1386876331115-5783187.post@n5.nabble.com>
In-Reply-To: <1386876331115-5783187.post@n5.nabble.com>
Content-Type: multipart/alternative;
 boundary="------------060409020107090008010004"
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

This is a multi-part message in MIME format.
--------------060409020107090008010004
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit


On 2013-12-12 11:25, David Johnston wrote:
> Dean Gibson (DB Administrator)-2 wrote
>> What's going on?  I can provide more detail if requested.  Of course, an
>> obvious workaround is to use in a VIEW:
>>
>> ... NULLIF( location, ROW( NULL, NULL )::"GeoPosition" ) ...
>>
>> but I'd like to know the cause.
> Cannot test right now but the core issue is that IS NULL on a record type
> evaluates both the scalar whole and the sub-components.  Try using IS [NOT]
> DISTINCT FROM with various target expressions and see if you can get
> something more sane.
>
> David J.

Yes, "SELECT ROW( NULL, NULL ) IS NULL;" produces TRUE, and "SELECT ROW( 
NULL, NULL ) IS NOT DISTINCT FROM NULL;" produces FALSE.

However, my problem is not that the comparison tests produce different 
results;  that's just a symptom.  My problem is that PostgreSQL is 
*changing* a NULL record value, to a record with NULLs for the component 
values, when I attempt to INSERT or UPDATE it into a different field.  
That means in php (for example), that retrieving what started out as a 
NULL record (and in php retrieves an empty string), becomes a record 
with NULL values (and in php retrieves a "(,)" string).  Yes, I can test 
for that in php, but problems/work-arounds need to be solved in the 
component that causes them.

However, I have found a satisfactory work-around in the TRIGGER function 
to the problem:  In my INSERT and UPDATE statements, I use:

... NULLIF( record_row.location, ROW( NULL, NULL )::"GeoPosition" ) ...

when adding or changing a value.

Note that setting "record_row.location" to NULL in PL/pgSQL just before 
the INSERT or UPDATE *does not solve the problem*, and tests of the 
value before and after setting the value in a record field (retrieved 
via a CURSOR FOR SELECT ...) shows that the value does not change to 
fully NULL.


-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.


--------------060409020107090008010004
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <br>
    <div class="moz-cite-prefix">On 2013-12-12 11:25, David Johnston
      wrote:<br>
    </div>
    <blockquote cite="mid:1386876331115-5783187.post@n5.nabble.com"
      type="cite">
      <pre wrap="">Dean Gibson (DB Administrator)-2 wrote
</pre>
      <blockquote type="cite">
        <pre wrap="">What's going on?  I can provide more detail if requested.  Of course, an 
obvious workaround is to use in a VIEW:

... NULLIF( location, ROW( NULL, NULL )::"GeoPosition" ) ...

but I'd like to know the cause.
</pre>
      </blockquote>
      <pre wrap="">
Cannot test right now but the core issue is that IS NULL on a record type
evaluates both the scalar whole and the sub-components.  Try using IS [NOT]
DISTINCT FROM with various target expressions and see if you can get
something more sane.

David J.
</pre>
    </blockquote>
    <br>
    Yes, "SELECT ROW( NULL, NULL ) IS NULL;" produces TRUE, and "SELECT
    ROW( NULL, NULL ) IS NOT DISTINCT FROM NULL;" produces FALSE.<br>
    <br>
    However, my problem is not that the comparison tests produce
    different results;  that's just a symptom.  My problem is that
    PostgreSQL is <b>changing</b> a NULL record value, to a record with
    NULLs for the component values, when I attempt to INSERT or UPDATE
    it into a different field.  That means in php (for example), that
    retrieving what started out as a NULL record (and in php retrieves
    an empty string), becomes a record with NULL values (and in php
    retrieves a "(,)" string).  Yes, I can test for that in php, but
    problems/work-arounds need to be solved in the component that causes
    them.<br>
    <br>
    However, I have found a satisfactory work-around in the TRIGGER
    function to the problem:  In my INSERT and UPDATE statements, I use:<br>
    <br>
    ... NULLIF( record_row.location, ROW( NULL, NULL )::"GeoPosition" )
    ...<br>
    <br>
    when adding or changing a value.<br>
    <br>
    Note that setting "record_row.location" to NULL in PL/pgSQL just
    before the INSERT or UPDATE <b>does not solve the problem</b>, and
    tests of the value before and after setting the value in a record
    field (retrieved via a CURSOR FOR SELECT ...) shows that the value
    does not change to fully NULL.<br>
    <br>
    <br>
    <pre class="moz-signature" cols="72">-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.</pre>
  </body>
</html>

--------------060409020107090008010004--