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.00001638448158.00000869548259.00000327145959.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
SETsgmt_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) || ''
ANDsgmth = '' || 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
DIRTYHACK!!! 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 *
includea 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