Hi
I'm building a web app where changes to customer orders are logged in the
following table, and I worried about the time that it will take to locate
records that need further processing/actioning. Here's the table:
create table CUSTOMER.WCCustOrderStatusLog (
WCCustOrderID integer,
WCOrderStatusID integer,
-- date/time at which some process acknowledged the status
-- change, and took the appropriate action, like raising
-- an e-mail confirmation
Acknowledged timestamp,
-- stamped with now() when the e-mailer process begins to process
-- the order/email message. When complete Processing goes back
-- to null and Acknowledged is stamped with now()
Processing timestamp,
-- date status change occured
LastUpdated timestamp,
primary key (WCCustOrderID, WCOrderStatusID)
) without oids;
I need a separate e-mailing process to locate orders (using the above
table) that have WCOrderStatusID = (4,5 or 99) AND a null value for
"Acknowledged" and "Processing"
I have a function which provides the next order number for processing:
CREATE OR REPLACE FUNCTION CUSTOMER.GetNextCustEmailAck(integer) RETURNS
integer AS '
-- locates the next order number that requires an e-mail confirmation
-- to be sent - this generally occurs as the order passes from
-- one status to the next. These status charges are recorded
-- within the WCCustOrderStatusLog table
DECLARE
pStatusID ALIAS FOR $1;
vCustOrderID integer := null;
BEGIN
-- locate the next order
select WCCustOrderID into vCustOrderID
from CUSTOMER.WCCustOrderStatusLog
where WCOrderStatusID = pStatusID
and Acknowledged is null
and Processing is null
for update
limit 1;
if (vCustOrderID is not null) then
-- mark the record as being processed
update CUSTOMER.WCCustOrderStatusLog
set Processing = now()
where WCCustOrderID = vCustOrderID
and WCOrderStatusID = pStatusID;
end if;
-- return -1 to indicate failure to locate order
if (vCustOrderID is null) then
return -1;
end if;
-- return the order number
return vCustOrderID;
END;
' LANGUAGE 'plpgsql';
My question is whether postgres can index null values, and if not, do I
have to accept a full table scan when locating records. Or can I mitigate
this by the use either of date values that signify null, but are non-null
(eg 1-Jan-1970). Or am I better off adding extra flag fields (integer)
which always have a Y/N (1,0) value corresponding to whether the
appropriate date field is null or not, and then use these to locate the
records.
eg
create table CUSTOMER.WCCustOrderStatusLog (
WCCustOrderID integer,
WCOrderStatusID integer,
-- New flag field
AcknowledgedIsNull integer,
Acknowledged timestamp,
-- New flag field
ProcessingIsNull integer,
Processing timestamp,
-- date status change occured
LastUpdated timestamp,
primary key (WCCustOrderID, WCOrderStatusID)
) without oids;
Also will adding an index to WCOrderStatusID reduce the cost of the
sequential scan?
Or is there some other strategy that would be better?
Thanks.
John Sidney-Woollett
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
