Обсуждение: uptime function to postmaster
Hi, This patch just implement a function that returns the uptime of the postmaster. The docs are included too. Another TODO item could be implement a function that returns the startup time. If nobody objects I can do it. Finally, I do not forbid non-superusers to execute it, but we can do this if you prefer. Comments? Please review it and apply it. PS> Hold it for 8.1. ===== Euler Taveira de Oliveira euler[at]yahoo_com_br _______________________________________________________ Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet rápida e grátis
Вложения
Euler Taveira de Oliveira <eulerto@yahoo.com.br> writes:
> This patch just implement a function that returns the uptime of the
> postmaster.
Doesn't work in EXEC_BACKEND case.
regards, tom lane
Hi Tom, > > This patch just implement a function that returns the uptime of the > > postmaster. > > Doesn't work in EXEC_BACKEND case. > Sorry. I'll fix this. ===== Euler Taveira de Oliveira euler[at]yahoo_com_br _______________________________________________________ Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet rápida e grátis
Hi, > > > This patch just implement a function that returns the uptime of > the > > > postmaster. > > > > Doesn't work in EXEC_BACKEND case. > > > Sorry. I'll fix this. > Patch updated. I improved the function output too. Comments? ===== Euler Taveira de Oliveira euler[at]yahoo_com_br __________________________________________________ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/
Вложения
On Wed, Dec 22, 2004 at 12:57:34AM -0300, Euler Taveira de Oliveira wrote: Hi, > Patch updated. I improved the function output too. Is it really a good idea to have a new file for only one function? We regularly see complaints about not wanting to do basic source code cleanup because not wanting to disrupt CVS history ... -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "El hombre nunca sabe de lo que es capaz hasta que lo intenta" (C. Dickens)
Hi Alvaro, > > > Patch updated. I improved the function output too. > > Is it really a good idea to have a new file for only one function? > We regularly see complaints about not wanting to do basic source code > cleanup because not wanting to disrupt CVS history ... > I couldn't see a good place to put it. Any suggestion? ===== Euler Taveira de Oliveira euler[at]yahoo_com_br _______________________________________________________ Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet rápida e grátis
This has been saved for the 8.1 release:
http:/momjian.postgresql.org/cgi-bin/pgpatches2
---------------------------------------------------------------------------
Euler Taveira de Oliveira wrote:
> Hi,
>
>
> > > > This patch just implement a function that returns the uptime of
> > the
> > > > postmaster.
> > >
> > > Doesn't work in EXEC_BACKEND case.
> > >
> > Sorry. I'll fix this.
> >
> Patch updated. I improved the function output too.
>
> Comments?
>
>
> =====
> Euler Taveira de Oliveira
> euler[at]yahoo_com_br
>
> __________________________________________________
> Converse com seus amigos em tempo real com o Yahoo! Messenger
> http://br.download.yahoo.com/messenger/
Content-Description: uptime3.diff
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian wrote: > > This has been saved for the 8.1 release: > > http:/momjian.postgresql.org/cgi-bin/pgpatches2 > > --------------------------------------------------------------------------- > Hi, I redo this patch adding the funcionality that Matthias implemented (starttime). Basically I changed the uptime()'s return type to 'interval' (more funcional now, uh?) and rework in the Matthias function (start_time()). The last one return type is 'timestamp with time zone'. The docs are attached to, but maybe need some work on it. Comments? ===== Euler Taveira de Oliveira euler[at]yahoo_com_br _______________________________________________________ Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet rápida e grátis*** ./doc/src/sgml/func.sgml.orig 2005-01-20 18:23:48.000000000 -0200 --- ./doc/src/sgml/func.sgml 2005-01-20 16:44:52.000000000 -0200 *************** *** 8060,8065 **** --- 8060,8077 ---- </row> <row> + <entry><function>start_time()</function></entry> + <entry><type>timestamp with time zone</type></entry> + <entry>PostgreSQL startup date and time</entry> + </row> + + <row> + <entry><function>uptime()</function></entry> + <entry><type>interval</type></entry> + <entry>PostgreSQL uptime information</entry> + </row> + + <row> <entry><function>user</function></entry> <entry><type>name</type></entry> <entry>equivalent to <function>current_user</function></entry> *************** *** 8157,8162 **** --- 8169,8192 ---- </para> <indexterm zone="functions-info"> + <primary>start_time</primary> + </indexterm> + + <para> + <function>start_time()</function> returns the timestamp with time zone + which the <productname>PostgreSQL</productname> was started. + </para> + + <indexterm zone="functions-info"> + <primary>uptime</primary> + </indexterm> + + <para> + <function>uptime()</function> returns the <productname>PostgreSQL</> + uptime information. + </para> + + <indexterm zone="functions-info"> <primary>version</primary> </indexterm> *** ./src/backend/postmaster/postmaster.c.orig 2005-01-20 18:24:36.000000000 -0200 --- ./src/backend/postmaster/postmaster.c 2005-01-20 16:44:52.000000000 -0200 *************** *** 221,226 **** --- 221,229 ---- bool ClientAuthInProgress = false; /* T during new-client * authentication */ + /* Backend startup time */ + TimestampTz StartTime; + /* * State for assigning random salts and cancel keys. * Also, the global MyCancelKey passes the cancel key assigned to a given *************** *** 329,334 **** --- 332,338 ---- InheritableSocket pgStatPipe0; InheritableSocket pgStatPipe1; pid_t PostmasterPid; + TimestampTz StartTime; #ifdef WIN32 HANDLE PostmasterHandle; HANDLE initial_signal_pipe; *************** *** 371,376 **** --- 375,383 ---- char *userDoption = NULL; int i; + AbsoluteTime StartTimeSec; /* integer part */ + int StartTimeUSec; /* microsecond part */ + /* This will call exit() if strdup() fails. */ progname = get_progname(argv[0]); *************** *** 915,920 **** --- 922,933 ---- */ StartupPID = StartupDataBase(); + /* + * Get start up time + */ + StartTimeSec = GetCurrentAbsoluteTimeUsec(&StartTimeUSec); + StartTime = AbsoluteTimeUsecToTimestampTz(StartTimeSec, StartTimeUSec); + status = ServerLoop(); /* *************** *** 3669,3674 **** --- 3682,3688 ---- write_inheritable_socket(¶m->pgStatPipe1, pgStatPipe[1], childPid); param->PostmasterPid = PostmasterPid; + param->StartTime = StartTime; #ifdef WIN32 param->PostmasterHandle = PostmasterHandle; *************** *** 3871,3876 **** --- 3885,3891 ---- read_inheritable_socket(&pgStatPipe[1], ¶m->pgStatPipe1); PostmasterPid = param->PostmasterPid; + StartTime = param->StartTime; #ifdef WIN32 PostmasterHandle = param->PostmasterHandle; *** ./src/backend/tcop/postgres.c.orig 2005-01-20 18:39:18.000000000 -0200 --- ./src/backend/tcop/postgres.c 2005-01-20 18:15:07.000000000 -0200 *************** *** 144,149 **** --- 144,152 ---- #endif /* TCOP_DONTUSENEWLINE */ + /* Backend startup time */ + TimestampTz StartTime; + /* ---------------------------------------------------------------- * decls for routines only used in this file * ---------------------------------------------------------------- *************** *** 2217,2222 **** --- 2220,2228 ---- sigjmp_buf local_sigjmp_buf; volatile bool send_rfq = true; + AbsoluteTime StartTimeSec; /* integer part */ + int StartTimeUSec; /* microsecond part */ + #define PendingConfigOption(name,val) \ (guc_names = lappend(guc_names, pstrdup(name)), \ guc_values = lappend(guc_values, pstrdup(val))) *************** *** 2896,2901 **** --- 2902,2916 ---- send_rfq = true; /* initially, or after error */ /* + * Get stand-alone backend startup time + */ + if (!IsUnderPostmaster) + { + StartTimeSec = GetCurrentAbsoluteTimeUsec(&StartTimeUSec); + StartTime = AbsoluteTimeUsecToTimestampTz(StartTimeSec, StartTimeUSec); + } + + /* * Non-error queries loop here. */ *** ./src/backend/utils/adt/timestamp.c.orig 2005-01-20 18:42:00.000000000 -0200 --- ./src/backend/utils/adt/timestamp.c 2005-01-20 16:44:52.000000000 -0200 *************** *** 941,946 **** --- 941,967 ---- PG_RETURN_TIMESTAMPTZ(result); } + Datum + pgsql_start_time(PG_FUNCTION_ARGS) + { + PG_RETURN_TIMESTAMPTZ(StartTime); + } + + Datum + pgsql_uptime(PG_FUNCTION_ARGS) + { + TimestampTz tznow; + AbsoluteTime sec; + int usec; + + sec = GetCurrentTransactionStartTimeUsec(&usec); + tznow = AbsoluteTimeUsecToTimestampTz(sec, usec); + + PG_RETURN_DATUM(DirectFunctionCall2(timestamptz_age, + TimestampTzGetDatum(tznow), + TimestampTzGetDatum(StartTime))); + } + void dt2time(Timestamp jd, int *hour, int *min, int *sec, fsec_t *fsec) { *** ./src/include/catalog/pg_proc.h.orig 2005-01-20 18:43:28.000000000 -0200 --- ./src/include/catalog/pg_proc.h 2005-01-20 16:44:52.000000000 -0200 *************** *** 3604,3609 **** --- 3604,3615 ---- DATA(insert OID = 2556 ( pg_tablespace_databases PGNSP PGUID 12 f f t t s 1 26 "26" _null_ pg_tablespace_databases -_null_)); DESCR("returns database oids in a tablespace"); + /* startuptime/uptime functions */ + DATA(insert OID = 2557 ( start_time PGNSP PGUID 12 f f t f s 0 1184 "" _null_ pgsql_start_time - _null_ )); + DESCR("PostgreSQL start time"); + DATA(insert OID = 2558 ( uptime PGNSP PGUID 12 f f t f s 0 1186 "" _null_ pgsql_uptime - _null_ )); + DESCR("PostgreSQL uptime"); + /* * Symbolic values for provolatile column: these indicate whether the result *** ./src/include/utils/timestamp.h.orig 2005-01-20 18:45:58.000000000 -0200 --- ./src/include/utils/timestamp.h 2005-01-20 16:44:52.000000000 -0200 *************** *** 249,254 **** --- 249,259 ---- extern Datum now(PG_FUNCTION_ARGS); + extern Datum pgsql_start_time(PG_FUNCTION_ARGS); + extern Datum pgsql_uptime(PG_FUNCTION_ARGS); + + extern TimestampTz StartTime; + /* Internal routines (not fmgr-callable) */ extern int tm2timestamp(struct pg_tm * tm, fsec_t fsec, int *tzp, Timestamp *dt);
This has been saved for the 8.1 release:
http://momjian.postgresql.org/cgi-bin/pgpatches2
---------------------------------------------------------------------------
Euler Taveira de Oliveira wrote:
>
> Bruce Momjian wrote:
> >
> > This has been saved for the 8.1 release:
> >
> > http:/momjian.postgresql.org/cgi-bin/pgpatches2
> >
> >
> ---------------------------------------------------------------------------
> >
>
> Hi,
>
> I redo this patch adding the funcionality that Matthias implemented
> (starttime). Basically I changed the uptime()'s return type to
> 'interval' (more funcional now, uh?) and rework in the Matthias
> function (start_time()). The last one return type is 'timestamp with
> time zone'. The docs are attached to, but maybe need some
> work on it.
>
> Comments?
>
>
>
> =====
> Euler Taveira de Oliveira
> euler[at]yahoo_com_br
>
>
>
>
>
> _______________________________________________________
> Yahoo! Acesso Gr?tis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet r?pida e gr?tis
Content-Description: uptime5.diff
> *** ./doc/src/sgml/func.sgml.orig 2005-01-20 18:23:48.000000000 -0200
> --- ./doc/src/sgml/func.sgml 2005-01-20 16:44:52.000000000 -0200
> ***************
> *** 8060,8065 ****
> --- 8060,8077 ----
> </row>
>
> <row>
> + <entry><function>start_time()</function></entry>
> + <entry><type>timestamp with time zone</type></entry>
> + <entry>PostgreSQL startup date and time</entry>
> + </row>
> +
> + <row>
> + <entry><function>uptime()</function></entry>
> + <entry><type>interval</type></entry>
> + <entry>PostgreSQL uptime information</entry>
> + </row>
> +
> + <row>
> <entry><function>user</function></entry>
> <entry><type>name</type></entry>
> <entry>equivalent to <function>current_user</function></entry>
> ***************
> *** 8157,8162 ****
> --- 8169,8192 ----
> </para>
>
> <indexterm zone="functions-info">
> + <primary>start_time</primary>
> + </indexterm>
> +
> + <para>
> + <function>start_time()</function> returns the timestamp with time zone
> + which the <productname>PostgreSQL</productname> was started.
> + </para>
> +
> + <indexterm zone="functions-info">
> + <primary>uptime</primary>
> + </indexterm>
> +
> + <para>
> + <function>uptime()</function> returns the <productname>PostgreSQL</>
> + uptime information.
> + </para>
> +
> + <indexterm zone="functions-info">
> <primary>version</primary>
> </indexterm>
>
> *** ./src/backend/postmaster/postmaster.c.orig 2005-01-20 18:24:36.000000000 -0200
> --- ./src/backend/postmaster/postmaster.c 2005-01-20 16:44:52.000000000 -0200
> ***************
> *** 221,226 ****
> --- 221,229 ----
> bool ClientAuthInProgress = false; /* T during new-client
> * authentication */
>
> + /* Backend startup time */
> + TimestampTz StartTime;
> +
> /*
> * State for assigning random salts and cancel keys.
> * Also, the global MyCancelKey passes the cancel key assigned to a given
> ***************
> *** 329,334 ****
> --- 332,338 ----
> InheritableSocket pgStatPipe0;
> InheritableSocket pgStatPipe1;
> pid_t PostmasterPid;
> + TimestampTz StartTime;
> #ifdef WIN32
> HANDLE PostmasterHandle;
> HANDLE initial_signal_pipe;
> ***************
> *** 371,376 ****
> --- 375,383 ----
> char *userDoption = NULL;
> int i;
>
> + AbsoluteTime StartTimeSec; /* integer part */
> + int StartTimeUSec; /* microsecond part */
> +
> /* This will call exit() if strdup() fails. */
> progname = get_progname(argv[0]);
>
> ***************
> *** 915,920 ****
> --- 922,933 ----
> */
> StartupPID = StartupDataBase();
>
> + /*
> + * Get start up time
> + */
> + StartTimeSec = GetCurrentAbsoluteTimeUsec(&StartTimeUSec);
> + StartTime = AbsoluteTimeUsecToTimestampTz(StartTimeSec, StartTimeUSec);
> +
> status = ServerLoop();
>
> /*
> ***************
> *** 3669,3674 ****
> --- 3682,3688 ----
> write_inheritable_socket(¶m->pgStatPipe1, pgStatPipe[1], childPid);
>
> param->PostmasterPid = PostmasterPid;
> + param->StartTime = StartTime;
>
> #ifdef WIN32
> param->PostmasterHandle = PostmasterHandle;
> ***************
> *** 3871,3876 ****
> --- 3885,3891 ----
> read_inheritable_socket(&pgStatPipe[1], ¶m->pgStatPipe1);
>
> PostmasterPid = param->PostmasterPid;
> + StartTime = param->StartTime;
>
> #ifdef WIN32
> PostmasterHandle = param->PostmasterHandle;
> *** ./src/backend/tcop/postgres.c.orig 2005-01-20 18:39:18.000000000 -0200
> --- ./src/backend/tcop/postgres.c 2005-01-20 18:15:07.000000000 -0200
> ***************
> *** 144,149 ****
> --- 144,152 ----
> #endif /* TCOP_DONTUSENEWLINE */
>
>
> + /* Backend startup time */
> + TimestampTz StartTime;
> +
> /* ----------------------------------------------------------------
> * decls for routines only used in this file
> * ----------------------------------------------------------------
> ***************
> *** 2217,2222 ****
> --- 2220,2228 ----
> sigjmp_buf local_sigjmp_buf;
> volatile bool send_rfq = true;
>
> + AbsoluteTime StartTimeSec; /* integer part */
> + int StartTimeUSec; /* microsecond part */
> +
> #define PendingConfigOption(name,val) \
> (guc_names = lappend(guc_names, pstrdup(name)), \
> guc_values = lappend(guc_values, pstrdup(val)))
> ***************
> *** 2896,2901 ****
> --- 2902,2916 ----
> send_rfq = true; /* initially, or after error */
>
> /*
> + * Get stand-alone backend startup time
> + */
> + if (!IsUnderPostmaster)
> + {
> + StartTimeSec = GetCurrentAbsoluteTimeUsec(&StartTimeUSec);
> + StartTime = AbsoluteTimeUsecToTimestampTz(StartTimeSec, StartTimeUSec);
> + }
> +
> + /*
> * Non-error queries loop here.
> */
>
> *** ./src/backend/utils/adt/timestamp.c.orig 2005-01-20 18:42:00.000000000 -0200
> --- ./src/backend/utils/adt/timestamp.c 2005-01-20 16:44:52.000000000 -0200
> ***************
> *** 941,946 ****
> --- 941,967 ----
> PG_RETURN_TIMESTAMPTZ(result);
> }
>
> + Datum
> + pgsql_start_time(PG_FUNCTION_ARGS)
> + {
> + PG_RETURN_TIMESTAMPTZ(StartTime);
> + }
> +
> + Datum
> + pgsql_uptime(PG_FUNCTION_ARGS)
> + {
> + TimestampTz tznow;
> + AbsoluteTime sec;
> + int usec;
> +
> + sec = GetCurrentTransactionStartTimeUsec(&usec);
> + tznow = AbsoluteTimeUsecToTimestampTz(sec, usec);
> +
> + PG_RETURN_DATUM(DirectFunctionCall2(timestamptz_age,
> + TimestampTzGetDatum(tznow),
> + TimestampTzGetDatum(StartTime)));
> + }
> +
> void
> dt2time(Timestamp jd, int *hour, int *min, int *sec, fsec_t *fsec)
> {
> *** ./src/include/catalog/pg_proc.h.orig 2005-01-20 18:43:28.000000000 -0200
> --- ./src/include/catalog/pg_proc.h 2005-01-20 16:44:52.000000000 -0200
> ***************
> *** 3604,3609 ****
> --- 3604,3615 ----
> DATA(insert OID = 2556 ( pg_tablespace_databases PGNSP PGUID 12 f f t t s 1 26 "26" _null_
pg_tablespace_databases- _null_));
> DESCR("returns database oids in a tablespace");
>
> + /* startuptime/uptime functions */
> + DATA(insert OID = 2557 ( start_time PGNSP PGUID 12 f f t f s 0 1184 "" _null_ pgsql_start_time - _null_ ));
> + DESCR("PostgreSQL start time");
> + DATA(insert OID = 2558 ( uptime PGNSP PGUID 12 f f t f s 0 1186 "" _null_ pgsql_uptime - _null_ ));
> + DESCR("PostgreSQL uptime");
> +
>
> /*
> * Symbolic values for provolatile column: these indicate whether the result
> *** ./src/include/utils/timestamp.h.orig 2005-01-20 18:45:58.000000000 -0200
> --- ./src/include/utils/timestamp.h 2005-01-20 16:44:52.000000000 -0200
> ***************
> *** 249,254 ****
> --- 249,259 ----
>
> extern Datum now(PG_FUNCTION_ARGS);
>
> + extern Datum pgsql_start_time(PG_FUNCTION_ARGS);
> + extern Datum pgsql_uptime(PG_FUNCTION_ARGS);
> +
> + extern TimestampTz StartTime;
> +
> /* Internal routines (not fmgr-callable) */
>
> extern int tm2timestamp(struct pg_tm * tm, fsec_t fsec, int *tzp, Timestamp *dt);
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
I think we are best with just pg_startime. If people want the interval they can subtract it from CURRENT_TIMESTAMP. I have added Matthias's version to the patch queue. --------------------------------------------------------------------------- Euler Taveira de Oliveira wrote: > > Bruce Momjian wrote: > > > > This has been saved for the 8.1 release: > > > > http:/momjian.postgresql.org/cgi-bin/pgpatches2 > > > > > --------------------------------------------------------------------------- > > > > Hi, > > I redo this patch adding the funcionality that Matthias implemented > (starttime). Basically I changed the uptime()'s return type to > 'interval' (more funcional now, uh?) and rework in the Matthias > function (start_time()). The last one return type is 'timestamp with > time zone'. The docs are attached to, but maybe need some > work on it. > > Comments? > > > > ===== > Euler Taveira de Oliveira > euler[at]yahoo_com_br > > > > > > _______________________________________________________ > Yahoo! Acesso Gr?tis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet r?pida e gr?tis Content-Description: uptime5.diff > *** ./doc/src/sgml/func.sgml.orig 2005-01-20 18:23:48.000000000 -0200 > --- ./doc/src/sgml/func.sgml 2005-01-20 16:44:52.000000000 -0200 > *************** > *** 8060,8065 **** > --- 8060,8077 ---- > </row> > > <row> > + <entry><function>start_time()</function></entry> > + <entry><type>timestamp with time zone</type></entry> > + <entry>PostgreSQL startup date and time</entry> > + </row> > + > + <row> > + <entry><function>uptime()</function></entry> > + <entry><type>interval</type></entry> > + <entry>PostgreSQL uptime information</entry> > + </row> > + > + <row> > <entry><function>user</function></entry> > <entry><type>name</type></entry> > <entry>equivalent to <function>current_user</function></entry> > *************** > *** 8157,8162 **** > --- 8169,8192 ---- > </para> > > <indexterm zone="functions-info"> > + <primary>start_time</primary> > + </indexterm> > + > + <para> > + <function>start_time()</function> returns the timestamp with time zone > + which the <productname>PostgreSQL</productname> was started. > + </para> > + > + <indexterm zone="functions-info"> > + <primary>uptime</primary> > + </indexterm> > + > + <para> > + <function>uptime()</function> returns the <productname>PostgreSQL</> > + uptime information. > + </para> > + > + <indexterm zone="functions-info"> > <primary>version</primary> > </indexterm> > > *** ./src/backend/postmaster/postmaster.c.orig 2005-01-20 18:24:36.000000000 -0200 > --- ./src/backend/postmaster/postmaster.c 2005-01-20 16:44:52.000000000 -0200 > *************** > *** 221,226 **** > --- 221,229 ---- > bool ClientAuthInProgress = false; /* T during new-client > * authentication */ > > + /* Backend startup time */ > + TimestampTz StartTime; > + > /* > * State for assigning random salts and cancel keys. > * Also, the global MyCancelKey passes the cancel key assigned to a given > *************** > *** 329,334 **** > --- 332,338 ---- > InheritableSocket pgStatPipe0; > InheritableSocket pgStatPipe1; > pid_t PostmasterPid; > + TimestampTz StartTime; > #ifdef WIN32 > HANDLE PostmasterHandle; > HANDLE initial_signal_pipe; > *************** > *** 371,376 **** > --- 375,383 ---- > char *userDoption = NULL; > int i; > > + AbsoluteTime StartTimeSec; /* integer part */ > + int StartTimeUSec; /* microsecond part */ > + > /* This will call exit() if strdup() fails. */ > progname = get_progname(argv[0]); > > *************** > *** 915,920 **** > --- 922,933 ---- > */ > StartupPID = StartupDataBase(); > > + /* > + * Get start up time > + */ > + StartTimeSec = GetCurrentAbsoluteTimeUsec(&StartTimeUSec); > + StartTime = AbsoluteTimeUsecToTimestampTz(StartTimeSec, StartTimeUSec); > + > status = ServerLoop(); > > /* > *************** > *** 3669,3674 **** > --- 3682,3688 ---- > write_inheritable_socket(¶m->pgStatPipe1, pgStatPipe[1], childPid); > > param->PostmasterPid = PostmasterPid; > + param->StartTime = StartTime; > > #ifdef WIN32 > param->PostmasterHandle = PostmasterHandle; > *************** > *** 3871,3876 **** > --- 3885,3891 ---- > read_inheritable_socket(&pgStatPipe[1], ¶m->pgStatPipe1); > > PostmasterPid = param->PostmasterPid; > + StartTime = param->StartTime; > > #ifdef WIN32 > PostmasterHandle = param->PostmasterHandle; > *** ./src/backend/tcop/postgres.c.orig 2005-01-20 18:39:18.000000000 -0200 > --- ./src/backend/tcop/postgres.c 2005-01-20 18:15:07.000000000 -0200 > *************** > *** 144,149 **** > --- 144,152 ---- > #endif /* TCOP_DONTUSENEWLINE */ > > > + /* Backend startup time */ > + TimestampTz StartTime; > + > /* ---------------------------------------------------------------- > * decls for routines only used in this file > * ---------------------------------------------------------------- > *************** > *** 2217,2222 **** > --- 2220,2228 ---- > sigjmp_buf local_sigjmp_buf; > volatile bool send_rfq = true; > > + AbsoluteTime StartTimeSec; /* integer part */ > + int StartTimeUSec; /* microsecond part */ > + > #define PendingConfigOption(name,val) \ > (guc_names = lappend(guc_names, pstrdup(name)), \ > guc_values = lappend(guc_values, pstrdup(val))) > *************** > *** 2896,2901 **** > --- 2902,2916 ---- > send_rfq = true; /* initially, or after error */ > > /* > + * Get stand-alone backend startup time > + */ > + if (!IsUnderPostmaster) > + { > + StartTimeSec = GetCurrentAbsoluteTimeUsec(&StartTimeUSec); > + StartTime = AbsoluteTimeUsecToTimestampTz(StartTimeSec, StartTimeUSec); > + } > + > + /* > * Non-error queries loop here. > */ > > *** ./src/backend/utils/adt/timestamp.c.orig 2005-01-20 18:42:00.000000000 -0200 > --- ./src/backend/utils/adt/timestamp.c 2005-01-20 16:44:52.000000000 -0200 > *************** > *** 941,946 **** > --- 941,967 ---- > PG_RETURN_TIMESTAMPTZ(result); > } > > + Datum > + pgsql_start_time(PG_FUNCTION_ARGS) > + { > + PG_RETURN_TIMESTAMPTZ(StartTime); > + } > + > + Datum > + pgsql_uptime(PG_FUNCTION_ARGS) > + { > + TimestampTz tznow; > + AbsoluteTime sec; > + int usec; > + > + sec = GetCurrentTransactionStartTimeUsec(&usec); > + tznow = AbsoluteTimeUsecToTimestampTz(sec, usec); > + > + PG_RETURN_DATUM(DirectFunctionCall2(timestamptz_age, > + TimestampTzGetDatum(tznow), > + TimestampTzGetDatum(StartTime))); > + } > + > void > dt2time(Timestamp jd, int *hour, int *min, int *sec, fsec_t *fsec) > { > *** ./src/include/catalog/pg_proc.h.orig 2005-01-20 18:43:28.000000000 -0200 > --- ./src/include/catalog/pg_proc.h 2005-01-20 16:44:52.000000000 -0200 > *************** > *** 3604,3609 **** > --- 3604,3615 ---- > DATA(insert OID = 2556 ( pg_tablespace_databases PGNSP PGUID 12 f f t t s 1 26 "26" _null_ pg_tablespace_databases- _null_)); > DESCR("returns database oids in a tablespace"); > > + /* startuptime/uptime functions */ > + DATA(insert OID = 2557 ( start_time PGNSP PGUID 12 f f t f s 0 1184 "" _null_ pgsql_start_time - _null_ )); > + DESCR("PostgreSQL start time"); > + DATA(insert OID = 2558 ( uptime PGNSP PGUID 12 f f t f s 0 1186 "" _null_ pgsql_uptime - _null_ )); > + DESCR("PostgreSQL uptime"); > + > > /* > * Symbolic values for provolatile column: these indicate whether the result > *** ./src/include/utils/timestamp.h.orig 2005-01-20 18:45:58.000000000 -0200 > --- ./src/include/utils/timestamp.h 2005-01-20 16:44:52.000000000 -0200 > *************** > *** 249,254 **** > --- 249,259 ---- > > extern Datum now(PG_FUNCTION_ARGS); > > + extern Datum pgsql_start_time(PG_FUNCTION_ARGS); > + extern Datum pgsql_uptime(PG_FUNCTION_ARGS); > + > + extern TimestampTz StartTime; > + > /* Internal routines (not fmgr-callable) */ > > extern int tm2timestamp(struct pg_tm * tm, fsec_t fsec, int *tzp, Timestamp *dt); -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Hi Bruce, > > I think we are best with just pg_startime. If people want the > interval > they can subtract it from CURRENT_TIMESTAMP. I have added Matthias's > version to the patch queue. > > OK. But IIRC the Matthias implementation doesn't work in standalone mode. And talking about the 'interval', I think it's too ugly make this: select CURRENT_TIMESTAMP - pg_starttime(); Isn't it more simple do this? select pg_uptime(); I think few people will use start_time and more people will use uptime that's why I propose the 'uptime' function. Talking abouts names, IMHO we need to go with uptime() and start_time(). Why? That's because a system function and it's about server. When we implement backend uptime, we can go with connection_uptime() and connection_start_time(). Comments? Euler Taveira de Oliveira euler[at]yahoo_com_br __________________________________________________ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/
Euler Taveira de Oliveira wrote: > Hi Bruce, > > > > > I think we are best with just pg_startime. If people want the > > interval > > they can subtract it from CURRENT_TIMESTAMP. I have added Matthias's > > version to the patch queue. > > > > > OK. But IIRC the Matthias implementation doesn't work in standalone > mode. And talking about the 'interval', I think it's too ugly make > this: > select CURRENT_TIMESTAMP - pg_starttime(); > > Isn't it more simple do this? > select pg_uptime(); I think we should return intervals only when we can't return meaningful timestamp values. I don't have any logic to back up that opinion, though. > > I think few people will use start_time and more people will use uptime > that's why I propose the 'uptime' function. We need to preceed our function names with pg_ for cases like this where we are supplying pg-specific behavior. > Talking abouts names, IMHO we need to go with uptime() and > start_time(). Why? That's because a system function and it's about > server. When we implement backend uptime, we can go with > connection_uptime() and connection_start_time(). -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I think we should return intervals only when we can't return meaningful
> timestamp values. I don't have any logic to back up that opinion, though.
It's easy: a value measured as an interval will be obsolete by the time
it's delivered to the client. A start timestamp is actually meaningful
information that will still be correct when used; uptime is fragile.
regards, tom lane
Hi Bruce, > I think we should return intervals only when we can't return > meaningful > timestamp values. I don't have any logic to back up that opinion, > though. > Agreed. > > We need to preceed our function names with pg_ for cases like this > where > we are supplying pg-specific behavior. > Agreed. An updated version of the patch is attached. It is just implement 'pg_start_time' function that works in multi-user and stand-alone. Docs is attached too. Comments? Euler Taveira de Oliveira euler[at]yahoo_com_br __________________________________________________ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/
Вложения
I think we agreed on pg_postmaster_start_time() so it is clear it is the
postmaster, and not the session.
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---------------------------------------------------------------------------
Euler Taveira de Oliveira wrote:
> Hi Bruce,
>
> > I think we should return intervals only when we can't return
> > meaningful
> > timestamp values. I don't have any logic to back up that opinion,
> > though.
> >
> Agreed.
>
> >
> > We need to preceed our function names with pg_ for cases like this
> > where
> > we are supplying pg-specific behavior.
> >
> Agreed.
>
> An updated version of the patch is attached. It is just implement
> 'pg_start_time' function that works in multi-user and stand-alone. Docs
> is attached too.
>
> Comments?
>
>
> Euler Taveira de Oliveira
> euler[at]yahoo_com_br
>
> __________________________________________________
> Converse com seus amigos em tempo real com o Yahoo! Messenger
> http://br.download.yahoo.com/messenger/
Content-Description: 958887538-uptime6.diff
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Euler Taveira de Oliveira wrote: > Hi Bruce, > > > I think we should return intervals only when we can't return > > meaningful > > timestamp values. I don't have any logic to back up that opinion, > > though. > > > Agreed. > > > > > We need to preceed our function names with pg_ for cases like this > > where > > we are supplying pg-specific behavior. > > > Agreed. > > An updated version of the patch is attached. It is just implement > 'pg_start_time' function that works in multi-user and stand-alone. Docs > is attached too. I have applied the attached patch, calling the function pg_postmaster_start_time(). I realize a stand-alone backend doesn't have a postmaster, but this is probably as clear as we are going to get. Do we want this to be executed only by super-users? I know there was some discussion about that but I didn't see a conclusion. The only argument I heard was something about random seeds, but that seemed like a weak argument. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/func.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.254 diff -c -c -r1.254 func.sgml *** doc/src/sgml/func.sgml 13 Jun 2005 02:40:04 -0000 1.254 --- doc/src/sgml/func.sgml 14 Jun 2005 20:59:17 -0000 *************** *** 8120,8125 **** --- 8120,8131 ---- </row> <row> + <entry><function>pg_postmaster_start_time()</function></entry> + <entry><type>timestamp with time zone</type></entry> + <entry><command>postmaster</> start time</entry> + </row> + + <row> <entry><function>user</function></entry> <entry><type>name</type></entry> <entry>equivalent to <function>current_user</function></entry> *************** *** 8217,8222 **** --- 8223,8237 ---- </para> <indexterm zone="functions-info"> + <primary>pg_postmaster_start_time</primary> + </indexterm> + + <para> + <function>pg_postmaster_start_time()</function> returns the timestamp with time zone + when the <command>postmaster</> started. + </para> + + <indexterm zone="functions-info"> <primary>version</primary> </indexterm> Index: src/backend/postmaster/postmaster.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/postmaster/postmaster.c,v retrieving revision 1.452 diff -c -c -r1.452 postmaster.c *** src/backend/postmaster/postmaster.c 9 Jun 2005 22:01:12 -0000 1.452 --- src/backend/postmaster/postmaster.c 14 Jun 2005 20:59:21 -0000 *************** *** 222,227 **** --- 222,230 ---- bool ClientAuthInProgress = false; /* T during new-client * authentication */ + /* Backend startup time */ + TimestampTz StartTime; + /* * State for assigning random salts and cancel keys. * Also, the global MyCancelKey passes the cancel key assigned to a given *************** *** 330,335 **** --- 333,339 ---- InheritableSocket pgStatPipe0; InheritableSocket pgStatPipe1; pid_t PostmasterPid; + TimestampTz StartTime; #ifdef WIN32 HANDLE PostmasterHandle; HANDLE initial_signal_pipe; *************** *** 372,377 **** --- 376,384 ---- char *userDoption = NULL; int i; + AbsoluteTime StartTimeSec; /* integer part */ + int StartTimeUSec; /* microsecond part */ + /* This will call exit() if strdup() fails. */ progname = get_progname(argv[0]); *************** *** 914,919 **** --- 921,932 ---- */ StartupPID = StartupDataBase(); + /* + * Get start up time + */ + StartTimeSec = GetCurrentAbsoluteTimeUsec(&StartTimeUSec); + StartTime = AbsoluteTimeUsecToTimestampTz(StartTimeSec, StartTimeUSec); + status = ServerLoop(); /* *************** *** 3603,3608 **** --- 3616,3622 ---- write_inheritable_socket(¶m->pgStatPipe1, pgStatPipe[1], childPid); param->PostmasterPid = PostmasterPid; + param->StartTime = StartTime; #ifdef WIN32 param->PostmasterHandle = PostmasterHandle; *************** *** 3805,3810 **** --- 3819,3825 ---- read_inheritable_socket(&pgStatPipe[1], ¶m->pgStatPipe1); PostmasterPid = param->PostmasterPid; + StartTime = param->StartTime; #ifdef WIN32 PostmasterHandle = param->PostmasterHandle; Index: src/backend/tcop/postgres.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/tcop/postgres.c,v retrieving revision 1.447 diff -c -c -r1.447 postgres.c *** src/backend/tcop/postgres.c 3 Jun 2005 23:05:29 -0000 1.447 --- src/backend/tcop/postgres.c 14 Jun 2005 20:59:22 -0000 *************** *** 149,154 **** --- 149,157 ---- #endif /* TCOP_DONTUSENEWLINE */ + /* Backend startup time */ + TimestampTz StartTime; + /* ---------------------------------------------------------------- * decls for routines only used in this file * ---------------------------------------------------------------- *************** *** 2380,2385 **** --- 2383,2391 ---- sigjmp_buf local_sigjmp_buf; volatile bool send_rfq = true; + AbsoluteTime StartTimeSec; /* integer part */ + int StartTimeUSec; /* microsecond part */ + #define PendingConfigOption(name,val) \ (guc_names = lappend(guc_names, pstrdup(name)), \ guc_values = lappend(guc_values, pstrdup(val))) *************** *** 2970,2975 **** --- 2976,2990 ---- pgstat_bestart(); /* + * Get stand-alone backend startup time + */ + if (!IsUnderPostmaster) + { + StartTimeSec = GetCurrentAbsoluteTimeUsec(&StartTimeUSec); + StartTime = AbsoluteTimeUsecToTimestampTz(StartTimeSec, StartTimeUSec); + } + + /* * POSTGRES main processing loop begins here * * If an exception is encountered, processing resumes here so we abort Index: src/backend/utils/adt/timestamp.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v retrieving revision 1.124 diff -c -c -r1.124 timestamp.c *** src/backend/utils/adt/timestamp.c 26 May 2005 02:04:13 -0000 1.124 --- src/backend/utils/adt/timestamp.c 14 Jun 2005 20:59:24 -0000 *************** *** 938,943 **** --- 938,949 ---- PG_RETURN_TIMESTAMPTZ(result); } + Datum + pgsql_postmaster_start_time(PG_FUNCTION_ARGS) + { + PG_RETURN_TIMESTAMPTZ(StartTime); + } + void dt2time(Timestamp jd, int *hour, int *min, int *sec, fsec_t *fsec) { Index: src/include/catalog/pg_proc.h =================================================================== RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v retrieving revision 1.366 diff -c -c -r1.366 pg_proc.h *** src/include/catalog/pg_proc.h 13 Jun 2005 02:26:50 -0000 1.366 --- src/include/catalog/pg_proc.h 14 Jun 2005 20:59:29 -0000 *************** *** 3651,3656 **** --- 3651,3660 ---- DATA(insert OID = 2559 ( lastval PGNSP PGUID 12 f f t f v 0 20 "" _null_ _null_ _null_ lastval - _null_)); DESCR("current value from last used sequence"); + /* start time function */ + DATA(insert OID = 2560 ( pg_postmaster_start_time PGNSP PGUID 12 f f t f s 0 1184 "" _null_ _null_ _null_ pgsql_postmaster_start_time- _null_ )); + DESCR("postmaster start time"); + /* * Symbolic values for provolatile column: these indicate whether the result Index: src/include/utils/timestamp.h =================================================================== RCS file: /cvsroot/pgsql/src/include/utils/timestamp.h,v retrieving revision 1.43 diff -c -c -r1.43 timestamp.h *** src/include/utils/timestamp.h 25 May 2005 21:40:42 -0000 1.43 --- src/include/utils/timestamp.h 14 Jun 2005 20:59:30 -0000 *************** *** 256,261 **** --- 256,265 ---- extern Datum now(PG_FUNCTION_ARGS); + extern Datum pgsql_postmaster_start_time(PG_FUNCTION_ARGS); + + extern TimestampTz StartTime; + /* Internal routines (not fmgr-callable) */ extern int tm2timestamp(struct pg_tm * tm, fsec_t fsec, int *tzp, Timestamp *dt);
Bruce Momjian wrote: > We need to preceed our function names with pg_ for cases like this where > we are supplying pg-specific behavior. We do? I'm not sure I can see much of a consistent naming convention for functions like these: version(), obj_description(), has_xyz_privilege(), format_type(), set_config(), and the like are surely "pg-specific", for example. -Neil
Neil Conway wrote: > Bruce Momjian wrote: > > We need to preceed our function names with pg_ for cases like this where > > we are supplying pg-specific behavior. > > We do? I'm not sure I can see much of a consistent naming convention for > functions like these: version(), obj_description(), has_xyz_privilege(), > format_type(), set_config(), and the like are surely "pg-specific", for > example. Yea, seems we aren't consistent. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073