pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpsql connection being reset during function?
3+ messages / 2 participants
[nested] [flat]
* psql connection being reset during function?
@ 2001-08-14 20:35 Randall Skelton <rhskelto@atm.ox.ac.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Randall Skelton @ 2001-08-14 20:35 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: psql connection being reset during function?
@ 2001-08-14 22:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Randall Skelton <rhskelto@atm.ox.ac.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2001-08-14 22:43 UTC (permalink / raw)
To: Randall Skelton <rhskelto@atm.ox.ac.uk>; +Cc: pgsql-sql
Randall Skelton <rhskelto@atm.ox.ac.uk> writes:
> 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
In present releases, plpgsql tends to leak a lot of memory intra-call;
you're probably just running out of memory. (I think we have cleaned up
the leak problems in the CVS-tip code, but that won't help you unless
you're brave enough to run a snapshot version.) Consider restructuring
your approach so that the plpgsql function just does a single conversion
and is invoked separately at each row:
UPDATE atlas3_path SET sgmt_timestamp = mydatetime(sgmty,sgmtmo,sgmtd,...);
where mydatetime takes five integers and a float and returns a timestamp.
This way, any memory leaked during plpgsql function execution is
reclaimed when the function exits, so it doesn't build up across rows.
This will probably be much faster than your other approach anyway, since
it doesn't require re-finding each row with a fresh UPDATE. A function
call is a whole lot cheaper than parsing, planning, and executing a new
query.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: psql connection being reset during function?
@ 2001-08-15 08:57 Randall Skelton <rhskelto@atm.ox.ac.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Randall Skelton @ 2001-08-15 08:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Excellent! The function you suggest is certainly a better approach... 5
minutes of processing and it was done. Thanks Tom.
Cheers,
Randall
On Tue, 14 Aug 2001, Tom Lane wrote:
> Randall Skelton <rhskelto@atm.ox.ac.uk> writes:
> > 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
>
> In present releases, plpgsql tends to leak a lot of memory intra-call;
> you're probably just running out of memory. (I think we have cleaned up
> the leak problems in the CVS-tip code, but that won't help you unless
> you're brave enough to run a snapshot version.) Consider restructuring
> your approach so that the plpgsql function just does a single conversion
> and is invoked separately at each row:
>
> UPDATE atlas3_path SET sgmt_timestamp = mydatetime(sgmty,sgmtmo,sgmtd,...);
>
> where mydatetime takes five integers and a float and returns a timestamp.
> This way, any memory leaked during plpgsql function execution is
> reclaimed when the function exits, so it doesn't build up across rows.
>
> This will probably be much faster than your other approach anyway, since
> it doesn't require re-finding each row with a fresh UPDATE. A function
> call is a whole lot cheaper than parsing, planning, and executing a new
> query.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-08-15 08:57 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-14 20:35 psql connection being reset during function? Randall Skelton <rhskelto@atm.ox.ac.uk>
2001-08-14 22:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-08-15 08:57 ` Randall Skelton <rhskelto@atm.ox.ac.uk>
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