Thread: NULLs and composite types
PostgreSQL 9.0.2 (CentOS 4.4):<br /><br /> I think the crux of my problem is:<br /><br /> SELECT ROW( NULL, NULL) IS
NULL; -- returns TRUE<br /><br /> SELECT COALESCE( ROW( NULL, NULL), ROW( 1,2 )); -- returns "(,)"<br /><br />
Manifestation:<br/><br /> I have a composite type:<br /><br /> CREATE TYPE "BaseTypes"."GeoPosition"
AS(<br/> latitude FLOAT,<br /> longitude FLOAT<br /> );<br /><br /> For the problem at
hand,I have two tables (say named A and B) which each declare a field thusly:<br /><br /> ...<br /> location"
"BaseTypes"."GeoPosition",<br/> ...<br /><br /> I also have a PL/pqSQL TRIGGER (BEFORE INSERT) that intercepts
INSERTsto table A generated elsewhere, and depending on a bunch of stuff, may:<br /><br /><ol><li>Change values in NEW
fields.<li>Usinga CURSOR, go find a related record in table A and update that instead (and RETURN NULL from the TRIGGER
procedure),or just RETURN NEW.<li>Before RETURNing, the TRIGGER procedure may also INSERT or UPDATE a related record in
tableB.</ol><p>This has all worked beautifully for three years, until I added the above "location" variable to tables A
andB. At the beginning of the TRIGGER procedure, I have:<br /><p> IF (NEW.location).latitude IS NULL
OR (NEW.location).longitude IS NULL THEN<br /> NEW.location := NULL;<br />
END IF;<br /><p>My intent is to make sure that "location" never has the value "ROW( NULL, NULL)", mainly
forsubsequent rendering in a web page.<br /><p>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):<br /><p>
IF (record_row.location).latitude IS NULL OR (record_row.location).longitude IS NULL THEN<br />
record_row.location := NULL;<br /> END IF;<br />
IF record_row.location = ROW( NULL, NULL )::"GeoPosition" THEN<br />
RAISE LOG 'Debug 1';<br /> END IF;<br /><p>These are six
successivelines, and yet the RAISE statement is frequently executed.<br /><p>Right now I get rid of the problem by
manually(and frequently) executing the following statement:<br /><p>UPDATE "A" SET location = NULL WHERE location =
ROW(NULL, NULL )::"GeoPosition";<br /><p>The above changes the "corrupted" lines correctly, and DOESN'T change lines
where"location" is already fully NULL.<br /><p>What's going on? I can provide more detail if requested. Of course, an
obviousworkaround is to use in a VIEW:<br /><p>... NULLIF( location, ROW( NULL, NULL )::"GeoPosition" ) ...<br /><p>but
I'dlike to know the cause.<br /><p>ps: I know the word "location" is "non-reserved", and if that's the problem, I can
changeit; it just means changing a bunch of other stuff, which I'd rather not do unless necessary.<p>-- Dean<br /><br
/><preclass="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>
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. -- View this message in context: http://postgresql.1045698.n5.nabble.com/NULLs-and-composite-types-tp5783027p5783187.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
<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 DISTINCTFROM 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 NULLsfor 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 withNULL values (and in php retrieves a "(,)" string). Yes, I can test for that in php, but problems/work-arounds need tobe solved in the component that causes them.<br /><br /> However, I have found a satisfactory work-around in the TRIGGERfunction 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 /> Notethat 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>
"Dean Gibson (DB Administrator)" <postgresql@ultimeth.com> writes:
> 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.
I don't think there is any mechanism in core Postgres that would do that.
plpgsql, however, is a different story. It has two different methods for
representing composite-type variables, and only one of those is capable of
representing a a "simple NULL" record value. So I suspect what is
happening is that one of your plpgsql trigger functions is doing something
with the location field that causes it to become a row-of-nulls. You've
not shown us enough detail to pinpoint the problem though.
regards, tom lane
On 2013-12-12 15:57, Tom Lane wrote:
> "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com> writes:
>> 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.
> I don't think there is any mechanism in core Postgres that would do that.
>
> plpgsql, however, is a different story. It has two different methods for
> representing composite-type variables, and only one of those is capable of
> representing a a "simple NULL" record value. So I suspect what is
> happening is that one of your plpgsql trigger functions is doing something
> with the location field that causes it to become a row-of-nulls. You've
> not shown us enough detail to pinpoint the problem though.
>
> regards, tom lane
Yeah, I suspected that it was a peculiarity of PL/pgSQL.
If I remove the "NULLIF" from the INSERT and UPDATE on
"D-Star"."_CallSession" statements (below), "incorrect" (non-fully-NULL)
values are inserted. Strangely, this problem does not occur on the
UPDATE or (implied) INSERT on "D-Star"."_CallRecord". There are TABLE
INDEXes, but none of them involve the "location" field.
Here's the component and table definition:
CREATE TYPE "BaseTypes"."GeoPosition" AS( latitude FLOAT, longitude FLOAT
);
CREATE TABLE "_CallRecord"( my_call CHAR( 7 ) NOT NULL, my_ssid CHAR( 2 ) NOT
NULL, session_begin TIMESTAMP WITH TIME ZONE NOT NULL, first_tx TIMESTAMP WITH TIME ZONE NOT
NULL, last_tx TIMESTAMP WITH TIME ZONE, ircddb_tx TIMESTAMP WITH TIME ZONE, rpt1_call
CHAR( 7 ), rpt1_term CHAR( 1 ), rpt2_call CHAR( 7 ), rpt2_term CHAR( 1 ),
your_call CHAR( 7 ), your_term CHAR( 1 ), ur_call CHAR( 7 ), ur_term
CHAR( 1 ), dest_call CHAR( 7 ), dest_term CHAR( 1 ), tag CHAR( 4 ),
dstar_msg CHAR( 20 ), flag0 BIT( 4 ), flag1 BIT( 8 ), flag2
BIT(8 ), flag3 BIT( 8 ), flag4 BIT( 8 ), frames INTEGER,
silent_pct SMALLINT, bit_err_pct_x10 SMALLINT, unproto CHAR( 6 ), location
"BaseTypes"."GeoPosition", altitude_ft SMALLINT, course SMALLINT, knots
SMALLINT, symbol CHAR( 2 ), aprs_dhms CHAR( 7 ), aprs_msg VARCHAR
);
CREATE TABLE "_CallSession"( my_call CHAR( 7 ) NOT NULL, my_ssid CHAR( 2 ) NOT
NULL, session_begin TIMESTAMP WITH TIME ZONE NOT NULL, session_end TIMESTAMP WITH TIME ZONE NOT
NULL, rpt1_call CHAR( 7 ), rpt1_term CHAR( 1 ), rpt2_call CHAR( 7 ),
rpt2_term CHAR( 1 ), your_call CHAR( 7 ), your_term CHAR( 1 ), dest_call
CHAR(7 ), dest_term CHAR( 1 ), tag CHAR( 4 ), dstar_msg CHAR( 20 ),
bit_err_pct_x10SMALLINT, unproto CHAR( 6 ), location "BaseTypes"."GeoPosition",
altitude_ft SMALLINT, course SMALLINT, knots SMALLINT, symbol CHAR(
2), aprs_msg VARCHAR, cnt INTEGER DEFAULT 1 NOT NULL, active
BOOLEAN DEFAULT TRUE NOT NULL
);
Here's the trigger function ("BEFORE INSERT"):
CREATE OR REPLACE FUNCTION "Callsign"."D-StarInsert"() RETURNS TRIGGER CALLED ON NULL INPUT VOLATILE
LANGUAGE PLpgSQL AS $PLpgSQL$ DECLARE tx_delay INTERVAL := NEW.first_tx - NEW.ircddb_tx;
record_found BOOLEAN; record_row "D-Star"."_CallRecord"%ROWTYPE;
record_cursor CURSOR( delay FLOAT ) FOR SELECT * FROM
"D-Star"."_CallRecord" WHERE my_call = NEW.my_call AND
my_ssid = NEW.my_ssid AND last_tx + INTERVAL '1 second' *
GREATEST( frames * 0.02 + 2.0, delay ) > NEW.first_tx
-- Estimate duration from frame count ORDER BY last_tx DESC
LIMIT1; session_found BOOLEAN; session_row "D-Star"."_CallSession"%ROWTYPE;
session_cursor CURSOR FOR SELECT * FROM "D-Star"."_CallSession"
WHERE my_call = NEW.my_call AND my_ssid = NEW.my_ssid
AND active; session_match BOOLEAN; BEGIN IF
NEW.my_call= '*******' THEN RAISE NOTICE 'Hidden callsign TX on
RPT1=''%'' discarded', NEW.rpt1_call || NEW.rpt1_term; RETURN NULL; END IF;
IF tx_delay NOT BETWEEN INTERVAL '-0.5 second' AND
INTERVAL '+10 seconds' THEN RAISE NOTICE 'RPT=''%'' {%} clock (%) %',
COALESCE( NEW.rpt2_call,
NEW.rpt1_call ), NEW.ircddb_tx, CASE
WHEN tx_delay < INTERVAL '0' THEN
'unsynchronized' ELSE 'excessive delay'
END, tx_delay; RETURN NULL; END IF;
IF NEW.location = ROW( NULL, NULL )::"GeoPosition"
THEN RAISE NOTICE 'New record: NULL location
components = %', NEW.location; -- Can this happen? END IF;
IF (NEW.location).latitude IS NULL OR (NEW.location).longitude IS NULL THEN
NEW.location := NULL; -- This does
work !!! END IF;
IF NEW.unproto IS NOT NULL THEN NEW.my_ssid := COALESCE( NEW.my_ssid,
''); NEW.rpt1_term := COALESCE( NEW.rpt1_term, '' ); NEW.aprs_msg :=
COALESCE(NEW.aprs_msg, '' ); OPEN record_cursor( 10.0 ); ELSE
NEW.rpt2_call := COALESCE( NEW.rpt2_call, ''
); -- Implied by missing gateway OPEN record_cursor( 5.0 ); END IF;
FETCH record_cursor INTO record_row; record_found := FOUND;
OPEN session_cursor; FETCH session_cursor INTO session_row;
session_found := FOUND;
NEW.last_tx := NEW.first_tx; NEW.session_begin := NEW.first_tx;
NEW.rpt2_term := CASE WHEN NEW.rpt2_call IS NULL
THEN NULL ELSE COALESCE(
NEW.rpt2_term, '' ) END; NEW.your_call := CASE RTRIM(
NEW.ur_call||
NEW.ur_term ) WHEN '' THEN 'CQCQCQ'
WHEN '/' THEN '/CQCQCQ' ELSE NULLIF( NEW.ur_call,
'*******' ) END; NEW.your_term := CASE
WHEN NEW.your_call IS NULL THEN NULL
ELSE COALESCE( NEW.ur_term, '' ) END; NEW.dest_term
:=CASE WHEN NEW.dest_call IS NULL THEN
NULL ELSE COALESCE(
NEW.dest_term, '' ) END;
session_match := session_row.rpt1_call =
NEW.rpt1_call AND session_row.rpt1_term =
NEW.rpt1_term AND (session_row.your_call =
NEW.your_call) IS NOT FALSE AND (session_row.your_term =
NEW.your_term) IS NOT FALSE AND (session_row.tag =
NEW.tag) IS NOT FALSE AND (session_row.unproto =
NEW.unproto) IS NOT FALSE;
IF session_found AND session_match THEN
NEW.session_begin:= session_row.session_begin; END IF;
IF record_found THEN -- IF record_row.location = ROW( NULL, NULL
)::"GeoPosition" THEN -- RAISE LOG 'Matched record: NULL
location components = %', -- record_row.location; -- Diagnostic only -- END IF;
-- Since the dstar_msg and bit_err_pct come in
separate records, -- this is the best we can do without saving
two dstar_msg values. UPDATE "D-Star"."_CallRecord" SET your_call
= COALESCE(
NEW.your_call, your_call ), your_term = COALESCE(
NEW.your_term, your_term ), ur_call = COALESCE(
NEW.ur_call, ur_call ), ur_term = COALESCE(
NEW.ur_term, ur_term ), rpt1_call = COALESCE(
NEW.rpt1_call, rpt1_call ), rpt1_term = COALESCE(
NEW.rpt1_term, rpt1_term ), rpt2_call = COALESCE(
NEW.rpt2_call, rpt2_call ), rpt2_term = COALESCE(
NEW.rpt2_term, rpt2_term ), dest_call = COALESCE(
NEW.dest_call, dest_call ), dest_term = COALESCE(
NEW.dest_term, dest_term ), tag = COALESCE(
NEW.tag, tag ), dstar_msg = COALESCE(
NEW.dstar_msg, dstar_msg ), bit_err_pct_x10 = COALESCE(
NEW.bit_err_pct_x10, bit_err_pct_x10 ), silent_pct = GREATEST(
NEW.silent_pct, silent_pct ), last_tx = GREATEST(
NEW.last_tx, last_tx ), flag0 = COALESCE(
NEW.flag0, flag0 ), flag1 = COALESCE(
NEW.flag1, flag1 ), flag2 = COALESCE(
NEW.flag2, flag2 ), flag3 = COALESCE(
NEW.flag3, flag3 ), flag4 = COALESCE(
NEW.flag4, flag4 ), ircddb_tx = LEAST(
NEW.ircddb_tx, ircddb_tx ), frames = COALESCE(
NEW.frames, 1 ) + COALESCE(
frames, 0 ), unproto = COALESCE(
NEW.unproto, unproto ), location = COALESCE(
NEW.location, location ), altitude_ft = COALESCE(
NEW.altitude_ft, altitude_ft ), course = COALESCE(
NEW.course, course ), knots = COALESCE(
NEW.knots, knots ), symbol = COALESCE(
NEW.symbol, symbol ), aprs_dhms = COALESCE(
NEW.aprs_dhms, aprs_dhms ), aprs_msg = COALESCE(
NEW.aprs_msg, aprs_msg ) WHERE CURRENT OF record_cursor
RETURNING * INTO record_row; --
RETURNING clause required for subsequent data -- IF record_row.location = ROW( NULL, NULL
)::"GeoPosition" THEN -- RAISE LOG 'Updated record: NULL
location components = %', -- record_row.location; -- Diagnostic only -- END IF;
NEW := NULL; ELSE IF NEW.unproto IS NOT NULL THEN
RAISE LOG '%|%-%/%|%|%|%', DATE_TRUNC( 'Second',
NEW.first_tx ) AT TIME ZONE 'UTC',
RPAD( NEW.my_call, 7 ), RPAD( NEW.my_ssid, 2), RPAD(
COALESCE(
NEW.unproto, NEW.tag, '' ), 6 ),
RPAD( NEW.rpt1_call, 7 ) || RPAD( NEW.rpt1_term, 1 ), COALESCE(
RPAD(
NEW.rpt2_call, 7 ) || RPAD( NEW.rpt2_term, 1 ), ' (APRS)
'), COALESCE( NULLIF(
NEW.aprs_msg, '' ), NEW.dstar_msg, '' ); ELSE RAISE LOG
'%|%-%/%|%|%|%@%|%', DATE_TRUNC( 'Second',
NEW.first_tx ) AT TIME ZONE 'UTC',
RPAD( NEW.my_call, 7 ), RPAD( NEW.my_ssid, 2), RPAD(
COALESCE(
NEW.unproto, NEW.tag, '' ), 6 ),
RPAD( NEW.rpt1_call, 7 ) || RPAD( NEW.rpt1_term, 1 ),
RPAD( NEW.rpt2_call, 7 ) || RPAD( NEW.rpt2_term, 1 ), RPAD(
COALESCE(RPAD(
NEW.ur_call, 7 ) || NEW.ur_term, '' ), 8 ), RPAD( COALESCE( RPAD(
NEW.dest_call, 7 ) || NEW.dest_term, '' ), 8 ), COALESCE( NULLIF(
NEW.aprs_msg, '' ), NEW.dstar_msg, '' ); END IF; record_row := NEW;
-- IF record_row.location = ROW( NULL, NULL
)::"GeoPosition" THEN -- RAISE LOG 'Replaced record: NULL
location components = %', -- record_row.location; -- Diagnostic only -- END IF;
END IF; CLOSE record_cursor;
IF session_found THEN IF session_match OR
session_row.session_begin =
record_row.session_begin THEN -- Since the bit_err_pct update comes
after the dstar_msg update, this works fine. UPDATE "D-Star"."_CallSession"
SET your_call =
record_row.your_call, your_term =
record_row.your_term, rpt1_call =
record_row.rpt1_call, rpt1_term =
record_row.rpt1_term, rpt2_call = COALESCE(
record_row.rpt2_call, rpt2_call ), rpt2_term = COALESCE(
record_row.rpt2_term, rpt2_term ), dest_call = COALESCE(
record_row.dest_call, dest_call ), dest_term = COALESCE(
record_row.dest_term, dest_term ), tag = COALESCE(
record_row.tag, tag ), session_end = GREATEST(
record_row.last_tx, session_end ), bit_err_pct_x10 = LEAST(
record_row.bit_err_pct_x10, bit_err_pct_x10 ), dstar_msg = CASE LEAST(
record_row.bit_err_pct_x10, bit_err_pct_x10 )
WHEN record_row.bit_err_pct_x10
THEN COALESCE( record_row.dstar_msg, dstar_msg )
ELSE COALESCE( dstar_msg, record_row.dstar_msg )
END, unproto = COALESCE(
record_row.unproto, unproto ), location = NULLIF(
COALESCE( record_row.location, location ),
ROW( NULL, NULL )::"GeoPosition" ), altitude_ft = COALESCE(
record_row.altitude_ft, altitude_ft ), course = COALESCE(
record_row.course, course ), knots = COALESCE(
record_row.knots, knots ), symbol = COALESCE(
record_row.symbol, symbol ), aprs_msg = COALESCE(
record_row.aprs_msg, aprs_msg ), cnt = cnt + CAST(
NOT record_found AS INTEGER) WHERE CURRENT OF session_cursor
RETURNING * INTO session_row;
-- RETURNING clause only needed for diagnostics -- IF session_row.location = ROW( NULL,
NULL )::"GeoPosition" THEN -- RAISE LOG 'Updated session:
NULL location components = %', -- session_row.location; -- Diagnostic only
-- END IF; CLOSE session_cursor; RETURN NEW;
ELSIF NOT record_found THEN UPDATE "D-Star"."_CallSession"
SET active = FALSE WHERE CURRENT OF session_cursor;
END IF; END IF; CLOSE session_cursor;
IF NOT record_found THEN INSERT INTO "D-Star"."_CallSession"(
my_call, my_ssid,
session_begin, session_end, rpt1_call,
rpt1_term, rpt2_call,
rpt2_term, your_call,
your_term, dest_call, dest_term,
tag, dstar_msg,
bit_err_pct_x10, unproto, location,
altitude_ft, course,
knots, symbol, aprs_msg )
VALUES( record_row.my_call, record_row.my_ssid,
record_row.session_begin,
record_row.session_begin, -- Initial session_end value
record_row.rpt1_call,
record_row.rpt1_term,
record_row.rpt2_call,
record_row.rpt2_term,
record_row.your_call,
record_row.your_term,
record_row.dest_call,
record_row.dest_term, record_row.tag,
record_row.dstar_msg,
record_row.bit_err_pct_x10, record_row.unproto,
NULLIF( record_row.location,
ROW( NULL, NULL )::"GeoPosition" ),
record_row.altitude_ft, record_row.course,
record_row.knots, record_row.symbol,
record_row.aprs_msg) RETURNING * INTO session_row;
-- RETURNING clause only needed for diagnostics -- IF session_row.location = ROW( NULL, NULL
)::"GeoPosition" THEN -- RAISE LOG 'Inserted session: NULL
location components = %', -- session_row.location; -- Diagnostic only -- END IF;
ELSE RAISE WARNING '[ircsql/%-%] Duplicate INSERT
not in current myCall session', record_row.my_call,
record_row.my_ssid; END IF; RETURN NEW; END; $PLpgSQL$
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.