From: Wei Weng <wweng@kencast.com>
To: pgsql-sql@postgresql.org
Subject: Re: [Fwd: 47G file]
Date: Fri, 22 Jun 2007 13:21:00 -0400
Message-ID: <200706221321.00385.wweng@kencast.com> (raw)
In-Reply-To: <467BF770.5010607@kencast.com>
References: <467BF770.5010607@kencast.com>
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
Attachments:S7100_spUnicastDelivery.sql (text/plain, 4.6K) download
--
-- 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';
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: wweng@kencast.com
Subject: Re: [Fwd: 47G file]
In-Reply-To: <200706221321.00385.wweng@kencast.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox