agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
To: pgsql-sql@postgresql.org
Subject: NULLs and composite types
Date: Wed, 11 Dec 2013 16:19:25 -0800
Message-ID: <52A9010D.3070202@ultimeth.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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.
view thread (5+ messages) latest in thread
Message-ID: <52A9010D.3070202@ultimeth.com>
Permalink: ../52A9010D.3070202@ultimeth.com/
Also on: postgresql.org/message-id/52A9010D.3070202@ultimeth.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@ultimeth.com
Subject: Re: NULLs and composite types
In-Reply-To: <52A9010D.3070202@ultimeth.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox