Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VrKOg-0005ZK-17 for pgsql-sql@arkaria.postgresql.org; Fri, 13 Dec 2013 04:30:42 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VrKOf-0001kx-HC for pgsql-sql@arkaria.postgresql.org; Fri, 13 Dec 2013 04:30:41 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VrKOe-0001kq-4x for pgsql-sql@postgresql.org; Fri, 13 Dec 2013 04:30:40 +0000 Received: from mail.mailpen.net ([209.59.217.159]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VrKOZ-0007fx-Ec for pgsql-sql@postgresql.org; Fri, 13 Dec 2013 04:30:38 +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 176211140B6 for ; Thu, 12 Dec 2013 20:30:32 -0800 (PST) Message-ID: <52AA8DAE.6070309@ultimeth.com> Date: Thu, 12 Dec 2013 20:31:42 -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: Re: NULLs and composite types References: <52A9010D.3070202@ultimeth.com> <1386876331115-5783187.post@n5.nabble.com> <52AA3156.20902@ultimeth.com> <3726.1386892654@sss.pgh.pa.us> In-Reply-To: <3726.1386892654@sss.pgh.pa.us> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) 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 On 2013-12-12 15:57, Tom Lane wrote: > "Dean Gibson (DB Administrator)" 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_x10 SMALLINT, 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 LIMIT 1; 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql