Hi all,
I am running 7.1.2 and I have the unenviable task of cleaning up a
database which has columns:
sgmty integer, -- YEAR
sgmtmo integer, -- MONTH
sgmtd integer, -- DAY
sgmth integer, -- HOUR
sgmtm integer, -- MINUTE
sgmts float, -- SECOND
+ 190 other floats per row
The data is basically an atmospheric model dump at 1 frame per second. Why
exactly time is stored like this remains a mystery... The values for
seconds resemble a 16 bit float output directly inserted (via libpq) into
the database:
0.999992423414
1.999986999391
3.000021808504
...
4.000016384481
58.000008695482
59.000003271459
59.999997847437
...
My first thought was simply to add a new column called gmt_timestamp
and write a simple function in plpgsql to update each record.
My first-cut function:
CREATE FUNCTION mydatetime() RETURNS text AS'
DECLARE
-- defines a record and text variable
rec RECORD;
date_time TEXT;
BEGIN
-- loop over all entries in path
FOR rec IN SELECT * FROM path LOOP
date_time:= ''UPDATE path SET sgmt_timestamp = '' ||
quote_literal(
rec.sgmty || ''-'' ||
rec.sgmtmo || ''-'' ||
rec.sgmtd || '' '' ||
rec.sgmth || '':'' ||
rec.sgmtm || '':'' ||
cast(rec.sgmts as integer)
)
|| '' WHERE'' ||
'' sgmty = '' || quote_literal(rec.sgmty) ||
'' AND sgmtmo = '' || quote_literal(rec.sgmtmo) ||
'' AND sgmtd = '' || quote_literal(rec.sgmtd) ||
'' AND sgmth = '' || quote_literal(rec.sgmth) ||
'' AND sgmtm = '' || quote_literal(rec.sgmtm) ||
'' AND sgmts = '' || quote_literal(rec.sgmts)
|| '';'';
EXECUTE date_time;
END LOOP;
--return date_time;
return ''done'';
END;
' LANGUAGE 'plpgsql';
Surely this can be improved upon, but it leads to problem #1. In the case
of sgmts = 59.999997847437 my explicit cast of, 'cast(rec.sgmts as
integer)' creates a problem in that I make a timestamp with '60' in the
seconds column. A time stamp of this sort is not handled by the postgres
timestamp type and the function falls over.
My revised function explicitly propogates 60 seconds to be a minute, 60
minutes to be an hour, 24 hours to be a day and so on.
CREATE FUNCTION mydatetime() RETURNS text AS'
DECLARE
-- defines a record and text variable
rec RECORD;
year INTEGER;
month INTEGER;
day INTEGER;
hour INTEGER;
minute INTEGER;
second INTEGER;
addone INTEGER;
date_time TEXT;
date_time_two TEXT;
BEGIN
-- loop over all entries in atlas3_path
FOR rec IN SELECT * FROM atlas3_path LOOP
/* THIS IS A DIRTY HACK!!! Should never have excluded a time stamp;
* casting allows the seconds to be 60 which is causes
* problems for the timestamp postgres type.
* Emperically checked to ensure month does not flip for this
* dataset (i.e. 1996-01-31 23:59:60 does not occur)
* Original code has now been modified to
* include a proper timestamp calculation.
*/
day := 0;
hour := 0;
minute := 0;
second := 0;
IF cast(rec.sgmts as integer) > 59 THEN
second := cast(rec.sgmts as integer) - 60;
minute := 1;
ELSE
second := cast(rec.sgmts as integer);
END IF;
IF minute + rec.sgmtm > 59 THEN
minute := minute + rec.sgmtm - 60;
hour := 1;
ELSE
minute := minute + rec.sgmtm;
END IF;
IF hour + rec.sgmth > 23 THEN
hour := hour + rec.sgmth - 24;
day := 1;
ELSE
hour := hour + rec.sgmth;
END IF;
day := day + rec.sgmtd;
date_time:= ''UPDATE atlas3_path SET sgmt_timestamp = '' ||
quote_literal(
rec.sgmty || ''-'' ||
rec.sgmtmo || ''-'' ||
day || '' '' ||
hour || '':'' ||
minute || '':'' ||
second
)
|| '' WHERE'' ||
'' sgmty = '' || quote_literal(rec.sgmty) ||
'' AND sgmtmo = '' || quote_literal(rec.sgmtmo) ||
'' AND sgmtd = '' || quote_literal(rec.sgmtd) ||
'' AND sgmth = '' || quote_literal(rec.sgmth) ||
'' AND sgmtm = '' || quote_literal(rec.sgmtm) ||
'' AND sgmts = '' || quote_literal(rec.sgmts)
|| '';'';
EXECUTE date_time;
END LOOP;
--return date_time;
return ''done'';
END;
' LANGUAGE 'plpgsql';
Again this is rather verbose and unelegant. Nevertheless it leads to
problem #2: While this works perfectly for a small table of 10 entries, it
crashes the database connection when I try to update 311537 rows using
psql and 'select mydatetime()'.
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
My logs read:
...
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: MoveOfflineLogs: remove 000000000000006C
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
Server process (pid 10219) exited with status 9 at Tue Aug 14 19:34:41 2001
Terminating any active server processes...
Server processes were terminated at Tue Aug 14 19:34:42 2001
Reinitializing shared memory and semaphores
The Data Base System is starting up
DEBUG: database system was interrupted at 2001-08-14 19:33:51 BST
DEBUG: CheckPoint record at (0, 2081443972)
DEBUG: Redo record at (0, 2080561900); Undo record at (0, 1828733240);
Shutdown
FALSE
DEBUG: NextTransactionId: 5627; NextOid: 15659730
DEBUG: database system was not properly shut down; automatic recovery in progress...
DEBUG: redo starts at (0, 2080561900)
DEBUG: open(logfile 0 seg 126) failed: No such file or directory
DEBUG: redo done at (0, 2113927744)
DEBUG: database system is in production state
Any and all thoughts are greatly appreciated!
Cheers,
Randall
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
