Date: Fri, 22 Jun 2007 13:21:00 -0400
From: Wei Weng <wweng@kencast.com>
Subject: Re: [Fwd: 47G file]
In-reply-to: <467BF770.5010607@kencast.com>
To: pgsql-sql@postgresql.org
Message-id: <200706221321.00385.wweng@kencast.com>
MIME-version: 1.0
Content-type: multipart/mixed; boundary="Boundary_(ID_M/TW66uLyUBoM9uAjnUaeg)"
References: <467BF770.5010607@kencast.com>
User-Agent: KMail/1.9.4


--Boundary_(ID_M/TW66uLyUBoM9uAjnUaeg)
Content-type: text/plain; charset=utf-8
Content-transfer-encoding: 7BIT
Content-disposition: inline

On Friday 22 June 2007 12:23, Alexandre Parizot wrote:
> Wei, Can you check the database error messages in the message "Main
> Log". You can find the system information in the email "System Info 7.1".
>
>
>
> Alex.

This is the script that caused the error in 7.1.0.0. (Fixed already)

The script in CVS is correct. This is the changelog regarding this error:

revision 1.6
date: 2007-02-05 23:21:05 +0000;  author: alex;  state: Exp;  lines: +1 -1
* Merge from R7100_bugfixes_branch
        * Bug:
          Unicast delivery throw a DB exception when archiving the 
transmission.
          Fix:
          The store procedure kcFTULog() use an undeclare variable (_tmp).


Wei


--Boundary_(ID_M/TW66uLyUBoM9uAjnUaeg)
Content-type: text/plain; charset=utf-8; name=S7100_spUnicastDelivery.sql
Content-transfer-encoding: 7BIT
Content-disposition: attachment; filename=S7100_spUnicastDelivery.sql

--
-- File Transmission Unicast stored procedures
--

CREATE OR REPLACE FUNCTION kcFTUMarkUnicastSites(
   CHAR(36),
   INTEGER,
   CHAR(1) )
RETURNS VOID AS '
DECLARE
   _tuuid ALIAS FOR $1;
   _failAttempts ALIAS FOR $2;
   _allowMulticast ALIAS FOR $3;
   _isBroadcast CHAR(1);
   _ackMode CHAR(1);
   _lastSent TIMESTAMP;
   _cnt INTEGER;
BEGIN

	SELECT IsBroadcast, AckReportMode, LastTxEndTime INTO _isBroadcast, _ackMode, _lastSent
	FROM SendTransmissions WHERE TransmissionUUID = _tuuid;

	IF( _allowMulticast = ''F'' OR _ackMode <> ''N'') THEN
	BEGIN
		SELECT COUNT(TransmissionUUID) INTO _cnt FROM SendTransmissionSites WHERE TransmissionUUID=_tuuid;
		IF( _cnt = 0 AND _isBroadcast = ''T'' ) THEN
		BEGIN
			INSERT INTO SendTransmissionSites (TransmissionUUID, SiteID, Unicast, DeliveryStatus)
			SELECT _tuuid, SiteID, ''T'', ''N''
			FROM UnicastSendSites
			JOIN ReceiveSites AS R ON R.ReceiveSiteID = UnicastSendSites.ReceiveSiteID
			WHERE UnicastSendSites.Disable = ''F'' AND UnicastSendSites.AllowMulticast = _allowMulticast;
		END;
		END IF;

		IF( _cnt <> 0 AND _isBroadcast <> ''T'' ) THEN
		BEGIN
			-- update the  the site IDs in SentTransmissionSites
			UPDATE SendTransmissionSites
			SET Unicast = ''T''
				WHERE TransmissionUUID = _tuuid
			AND Unicast <> ''T'' AND DeliveryStatus = ''N''
			AND SiteID IN
				(SELECT SiteID
				FROM UnicastSendSites
				JOIN ReceiveSites AS R ON R.ReceiveSiteID = UnicastSendSites.ReceiveSiteID
				WHERE UnicastSendSites.Disable = ''F'' AND UnicastSendSites.AllowMulticast = _allowMulticast);
		END;
		END IF;
	END;
	END IF;

	-- for sites that acknowledged the receiving of the file set Unicast = ''F''
	IF( _ackmode = ''A'' ) THEN
	BEGIN
		-- report always
		UPDATE SendTransmissionSites
		SET Unicast = ''F''
		WHERE TransmissionUUID = _tuuid
		AND SiteID IN
			(SELECT SiteID
			FROM TransmissionAck AS A
			WHERE A.Status IN (''V'', ''C'', ''D'', ''N'')
			AND A.TransmissionUUID = _tuuid);
	END;
	ELSIF( _ackmode = ''F'' AND _lastSent IS NOT NULL ) THEN
	BEGIN
		-- report on incomplete
		UPDATE SendTransmissionSites
		SET Unicast = ''F''
		WHERE TransmissionUUID = _tuuid
		AND SiteID NOT IN
			(SELECT SiteID
			FROM TransmissionAck AS A
			WHERE A.Status IN (''E'', ''I'')
			AND A.TransmissionUUID = _tuuid
			AND A.ReceivedOn > _lastSent);
	END;
	END IF;

	-- skip sites which fail too often
	IF( _failAttempts > 0 ) THEN
	BEGIN
		-- report always
		UPDATE SendTransmissionSites
		SET DeliveryStatus = ''K''
		WHERE TransmissionUUID = _tuuid
		AND Unicast = ''T''
		AND SiteID IN
			(SELECT SiteID
			FROM UnicastSendSites
			JOIN ReceiveSites AS R ON R.ReceiveSiteID = UnicastSendSites.ReceiveSiteID
			WHERE UnicastSendSites.FailedConnectionCount > _failAttempts
			AND UnicastSendSites.LastConnectionTime > CURRENT_TIMESTAMP + INTERVAL ''4 hours'');
	END;
	END IF;

	RETURN;

END;
' LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION kcFTULog(
   CHAR(36),
   CHAR(36),
   INTEGER,
   INTEGER,
   VARCHAR(255),
   INTEGER,
   INTEGER,
   DECIMAL,
   DECIMAL,
   CHAR(1),
   VARCHAR(255),
   INTEGER,
   DECIMAL,
   DECIMAL)
RETURNS VOID AS '
DECLARE
   _logid ALIAS FOR $1;
   _tuuid ALIAS FOR $2;
   _tid ALIAS FOR $3;
   _siteid ALIAS FOR $4;
   _sendToAddress ALIAS FOR $5;
   _sendToPort ALIAS FOR $6;
   _duration ALIAS FOR $7;
   _sentBytes ALIAS FOR $8;
   _sentPackets ALIAS FOR $9;
   _status ALIAS FOR $10;
   _errorMessage ALIAS FOR $11;
   _channelID ALIAS FOR $12;
   _bytes ALIAS FOR $13;
   _packets ALIAS FOR $14;
   _tmp INTEGER;
BEGIN

	-- log the transmission

	INSERT INTO UnicastSendLog (
      LogID
	 , TransmissionUUID
	 , UnicastTransmissionID
	 , SiteID
	 , SendToAddress
	 , SendToPort
	 , Duration
	 , SentBytes
	 , SentPackets
	 , Status
	 , ErrorMessage
	 , ChannelID
   )
	VALUES (
      _logid
	, _tuuid
	, _tid
	, _siteid
	, _sendToAddress
	, _sendToPort
	, _duration
	, _sentBytes
	, _sentPackets
	, _status
	, _errorMessage
	, _channelID
	);

	-- update byte/packet count
	UPDATE SendTransmissions
	SET TransmissionCount = TransmissionCount + 1
	, SentTxPackets = SentTxPackets + _sentPackets
	, SentTxBytes = SentTxBytes + _sentBytes
	WHERE TransmissionUUID = _tuuid AND TransmissionCount > 0;

	IF NOT FOUND THEN
	BEGIN
		UPDATE SendTransmissions
		SET TransmissionCount = TransmissionCount + 1
		, SentTxPackets = SentTxPackets + _sentPackets
		, SentTxBytes = SentTxBytes + _sentBytes
		, TxPackets = _packets
		, TxBytes = _bytes
		WHERE TransmissionUUID = _tuuid;

		-- remote management calls
		SELECT COUNT(proname) INTO _tmp FROM pg_proc WHERE proname = ''kctxreceivefilesent'';
		IF (_tmp > 0) THEN
			PERFORM kcTxReceiveFileSent(_tuuid);
		END IF;
	END;
   END IF;

   RETURN;

END;
' LANGUAGE 'plpgsql';

--Boundary_(ID_M/TW66uLyUBoM9uAjnUaeg)--