Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Vqtyw-0003Oc-Rd for pgsql-sql@arkaria.postgresql.org; Thu, 12 Dec 2013 00:18:23 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Vqtyw-00077o-6z for pgsql-sql@arkaria.postgresql.org; Thu, 12 Dec 2013 00:18:22 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Vqtyv-00077h-1L for pgsql-sql@postgresql.org; Thu, 12 Dec 2013 00:18:21 +0000 Received: from mail.mailpen.net ([209.59.217.159]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Vqtyq-00033m-KW for pgsql-sql@postgresql.org; Thu, 12 Dec 2013 00:18:20 +0000 Received: from [172.20.4.200] (50-46-168-128.evrt.wa.frontiernet.net [50.46.168.128]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.mailpen.net (Postfix) with ESMTP id 77C741140CC for ; Wed, 11 Dec 2013 16:18:14 -0800 (PST) Message-ID: <52A9010D.3070202@ultimeth.com> Date: Wed, 11 Dec 2013 16:19:25 -0800 From: "Dean Gibson (DB Administrator)" 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: NULLs and composite types Content-Type: multipart/alternative; boundary="------------010505080001090309010203" X-Pg-Spam-Score: -0.0 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------010505080001090309010203 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit PostgreSQL 9.0.2 (CentOS 4.4): I think the crux of my problem is: SELECT ROW( NULL, NULL) IS NULL; -- returns TRUE SELECT COALESCE( ROW( NULL, NULL), ROW( 1,2 )); -- returns "(,)" Manifestation: I have a composite type: CREATE TYPE "BaseTypes"."GeoPosition" AS( latitude FLOAT, longitude FLOAT ); For the problem at hand, I have two tables (say named A and B) which each declare a field thusly: ... location" "BaseTypes"."GeoPosition", ... I also have a PL/pqSQL TRIGGER (BEFORE INSERT) that intercepts INSERTs to table A generated elsewhere, and depending on a bunch of stuff, may: 1. Change values in NEW fields. 2. Using a CURSOR, go find a related record in table A and update that instead (and RETURN NULL from the TRIGGER procedure), or just RETURN NEW. 3. Before RETURNing, the TRIGGER procedure may also INSERT or UPDATE a related record in table B. This has all worked beautifully for three years, until I added the above "location" variable to tables A and B. At the beginning of the TRIGGER procedure, I have: IF (NEW.location).latitude IS NULL OR (NEW.location).longitude IS NULL THEN NEW.location := NULL; END IF; My intent is to make sure that "location" never has the value "ROW( NULL, NULL)", mainly for subsequent rendering in a web page. This works in making sure that table A never has the above value. However, when I INSERT or UPDATE the related record in table B, somehow the fully "NULL" value for "location" gets "corrupted" into "ROW( NULL, NULL)". I've spent the better part of a day trying to figure this out, with statements like ("record_row" comes from a row captured in a CURSOR SELECT statement from table A): IF (record_row.location).latitude IS NULL OR (record_row.location).longitude IS NULL THEN record_row.location := NULL; END IF; IF record_row.location = ROW( NULL, NULL )::"GeoPosition" THEN RAISE LOG 'Debug 1'; END IF; These are six successive lines, and yet the RAISE statement is frequently executed. Right now I get rid of the problem by manually (and frequently) executing the following statement: UPDATE "A" SET location = NULL WHERE location = ROW( NULL, NULL )::"GeoPosition"; The above changes the "corrupted" lines correctly, and DOESN'T change lines where "location" is already fully NULL. 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. ps: I know the word "location" is "non-reserved", and if that's the problem, I can change it; it just means changing a bunch of other stuff, which I'd rather not do unless necessary. -- Dean -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce. --------------010505080001090309010203 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit PostgreSQL 9.0.2 (CentOS 4.4):

I think the crux of my problem is:

SELECT ROW( NULL, NULL) IS NULL;  -- returns TRUE

SELECT COALESCE( ROW( NULL, NULL), ROW( 1,2 ));  -- returns "(,)"

Manifestation:

I have a composite type:

CREATE          TYPE    "BaseTypes"."GeoPosition"  AS(
        latitude        FLOAT,
        longitude       FLOAT
);

For the problem at hand, I have two tables (say named A and B) which each declare a field thusly:

   ...
   location"  "BaseTypes"."GeoPosition",
   ...

I also have a PL/pqSQL TRIGGER (BEFORE INSERT) that intercepts INSERTs to table A generated elsewhere, and depending on a bunch of stuff, may:

  1. Change values in NEW fields.
  2. Using a CURSOR, go find a related record in table A and update that instead (and RETURN NULL from the TRIGGER procedure), or just RETURN NEW.
  3. Before RETURNing, the TRIGGER procedure may also INSERT or UPDATE a related record in table B.

This has all worked beautifully for three years, until I added the above "location" variable to tables A and B.  At the beginning of the TRIGGER procedure, I have:

                IF  (NEW.location).latitude  IS NULL OR  (NEW.location).longitude IS NULL    THEN
                        NEW.location    := NULL;
                END IF;

My intent is to make sure that "location" never has the value "ROW( NULL, NULL)", mainly for subsequent rendering in a web page.

This works in making sure that table A never has the above value.  However, when I INSERT or UPDATE the related record in table B, somehow the fully "NULL" value for "location" gets "corrupted" into "ROW( NULL, NULL)".  I've spent the better part of a day trying to figure this out, with statements like ("record_row" comes from a row captured in a CURSOR SELECT statement from table A):

                        IF  (record_row.location).latitude  IS NULL OR  (record_row.location).longitude IS NULL  THEN
                                record_row.location     := NULL;
                        END IF;
                        IF  record_row.location = ROW( NULL, NULL )::"GeoPosition"      THEN
                                RAISE   LOG     'Debug 1';
                        END IF;

These are six successive lines, and yet the RAISE statement is frequently executed.

Right now I get rid of the problem by manually (and frequently) executing the following statement:

UPDATE "A" SET location = NULL WHERE location = ROW( NULL, NULL )::"GeoPosition";

The above changes the "corrupted" lines correctly, and DOESN'T change lines where "location" is already fully NULL.

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.

ps: I know the word "location" is "non-reserved", and if that's the problem, I can change it;  it just means changing a bunch of other stuff, which I'd rather not do unless necessary.

-- Dean

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