Re: NULLs and composite types - Mailing list pgsql-sql

From Dean Gibson (DB Administrator)
Subject Re: NULLs and composite types
Date
Msg-id 52AA8DAE.6070309@ultimeth.com
Whole thread Raw
In response to Re: NULLs and composite types  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-sql
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.




pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: NULLs and composite types
Next
From: Achilleas Mantzios
Date:
Subject: Query caching (with 8.3)