NULLs and composite types

From: "Dean Gibson (DB Administrator)" <postgresql(at)ultimeth(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: NULLs and composite types
Date: 2013-12-12 00:19:25
Message-ID: 52A9010D.3070202@ultimeth.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Vick Khera 2013-12-12 16:29:07 Re: [GENERAL] Scheduled Events
Previous Message Dev Kumkar 2013-12-11 15:00:55 Re: Scheduled Events