Re: [PATCHES] uptime function to postmaster

2005-01-22 Thread Bruce Momjian

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.0 -0200
> --- ./doc/src/sgml/func.sgml  2005-01-20 16:44:52.0 -0200
> ***
> *** 8060,8065 
> --- 8060,8077 
> 
>   
> 
> +start_time()
> +timestamp with time zone
> +PostgreSQL startup date and time
> +   
> + 
> +   
> +uptime()
> +interval
> +PostgreSQL uptime information
> +   
> + 
> +   
>  user
>  name
>  equivalent to current_user
> ***
> *** 8157,8162 
> --- 8169,8192 
>  
>   
>  
> + start_time
> +
> + 
> +
> +  start_time() returns the timestamp with time zone
> +  which the PostgreSQL was started.
> +
> + 
> +
> + uptime
> +
> + 
> +
> +  uptime() returns the PostgreSQL
> +  uptime information.
> +
> + 
> +
>   version
>  
>   
> *** ./src/backend/postmaster/postmaster.c.orig2005-01-20 
> 18:24:36.0 -0200
> --- ./src/backend/postmaster/postmaster.c 2005-01-20 16:44:52.0 
> -0200
> ***
> *** 221,226 
> --- 221,229 
>   boolClientAuthInProgress = 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;
>   
> + AbsoluteTimeStartTimeSec;   /* 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.orig2005-01-20 18:39:18.0 
> -0200
> --- ./src/backend/tcop/postgres.c 2005-01-20 18:15:07.0 -0200
> ***
> *** 144,149 
> --- 144,152 
>   #endif   /* TCOP_DONTUSENEWLINE */
>   
>   
> + /* Backend startup time */
> + TimestampTz StartTime;
> + 
>   /* 
>*  decls for routines only used in this file
>* 
> ***
> *** 2217, 
> --- 2220,2228 
>   sigjmp_buf  local_sigjmp_buf;
> 

Re: [PATCHES] vacuum hint on elog

2005-01-22 Thread Neil Conway
Tom Lane wrote:
The code beginning at freelist.c line 645 is intended to deal with this.
Ah, good point -- sorry, I missed that. The code as-is should be fine, then.
-Neil
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


Re: [PATCHES] vacuum hint on elog

2005-01-22 Thread Tom Lane
Neil Conway <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> The code beginning at freelist.c line 645 is intended to deal with this.

> Ah, good point -- sorry, I missed that. The code as-is should be fine, then.

Well, one point is that the flag bit is checked elsewhere in the same
file without similar code to see if it's stale.  I'm not sure if the
other places can be reached without having reached line 645 first.

I tend to agree with Alvaro that a cleaner approach would be to PG_TRY
around the places that can set the flag, and get rid of the cleanup
logic at line 645.  We didn't have PG_TRY when that code was written,
but we do now ...

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


Re: [PATCHES] [pgsql-hackers-win32] pg_autovacuum fails to start - 8.0

2005-01-22 Thread Bruce Momjian

[ Old version superceeded.]


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.

---


Dave Page wrote:
>  
> 
> > -Original Message-
> > From: [EMAIL PROTECTED] 
> > [mailto:[EMAIL PROTECTED] On Behalf Of Dave Page
> > Sent: 20 January 2005 15:29
> > To: Harald Massa; [EMAIL PROTECTED]
> > Cc: Patches (PostgreSQL)
> > Subject: Re: [PATCHES] [pgsql-hackers-win32] pg_autovacuum 
> > fails to start - 8.0 Release
> > 
> > It appears I didn't read the docs properly when I write that 
> > bit - the dependencies parameter passed to CreateService() is 
> > supposed to be double-null terminated - a subtle point I missed :-(
> > 
> > The attached patch fixes this. Harald - I can email an 
> > updated .exe if you want to test, otherwise, you should be 
> > able to use the current version if you cleanup the 
> > HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pg_autova
> > cuum\DependOnService registry key.
> 
> Sorry - the attached patch supercedes the original. It also directs
> errors when installing/removing the windows service to stderr, rather
> than other logs.
> 
> Regards, Dave.

Content-Description: pg_autovacuum.c.diff

[ Attachment, skipping... ]

> 
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
>   joining column's datatypes do not match

-- 
  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

---(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


Re: [PATCHES] [pgsql-hackers-win32] pg_autovacuum fails to start - 8.0 Release

2005-01-22 Thread Bruce Momjian

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.

---


Dave Page wrote:
>  
> 
> > -Original Message-
> > From: [EMAIL PROTECTED] 
> > [mailto:[EMAIL PROTECTED] On Behalf 
> > Of Harald Massa
> > Sent: 20 January 2005 13:30
> > To: [EMAIL PROTECTED]
> > Subject: [pgsql-hackers-win32] pg_autovacuum fails to start - 
> > 8.0 Release
> > 
> > I am trying to install pg_autovacuum as a win32 service.
> > 
> > pg_autovacuum -I -N ourdomain\postgres -W secretpassword -E 
> > pgsql-8.0 -d 4
> > -L c:\ghum\data\pg_log\autovacuum.log -U postgres -P 
> > moresecretpasswords
> > 
> > when trying to start:
> > 
> > C:\Programme\PostgreSQL\8.0\bin>sc start pg_autovacuum
> > [SC] StartService FAILED 1075:
> > 
> > Der Abh?ngigkeitsdienst ist nicht vorhanden oder wurde zum 
> > L?schen markiert.
> > 
> > (english: 1075 The dependency service does not exist or has 
> > been marked for
> > deletion.  ERROR_SERVICE_DEPENDENCY_DELETED)
> 
> It appears I didn't read the docs properly when I write that bit - the 
> dependencies parameter passed to CreateService() is supposed to be 
> double-null terminated - a subtle point I missed :-(
> 
> The attached patch fixes this. Harald - I can email an updated .exe if you 
> want to test, otherwise, you should be able to use the current version if you 
> cleanup the 
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pg_autovacuum\DependOnService
>  registry key.
> 
> Regards, Dave

Content-Description: pg_autovacuum.c.diff

[ Attachment, skipping... ]

> 
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings

-- 
  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

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PATCHES] [DOCS] bug in 8.0 manual, section 37.6, PL/Perl Triggers

2005-01-22 Thread Bruce Momjian
Rainer Brandt wrote:
> Hi,
> 
> All places that mention the trigger event info hash pointer $_TD
> end with the "->" characters.  The hash keys are omitted.
> That is certainly not what you wanted.
> (Also, the code examples will not compile under Perl.  (I didn't
> try, but can see that they won't.))
> 
> Probably due to the character '>' and some Docbook -> HTML conversion?

Good catch. I have fixed CVS HEAD and 8.0.X.  Patch attached.  Thanks.

-- 
  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/cvs.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/cvs.sgml,v
retrieving revision 1.30
diff -c -c -r1.30 cvs.sgml
*** doc/src/sgml/cvs.sgml   6 Jan 2005 01:49:22 -   1.30
--- doc/src/sgml/cvs.sgml   22 Jan 2005 21:33:58 -
***
*** 214,220 
 
   file1   file2   file3   file4   file5
  
!  1.1 1.1 1.1 1.1  /--1.1*  <-*-  TAG
   1.2*-   1.2 1.2-1.2*-
   1.3  \- 1.3*-   1.3   / 1.3
   1.4  \  1.4  /  1.4
--- 214,220 
 
   file1   file2   file3   file4   file5
  
!  1.1 1.1 1.1 1.1  /--1.1*  <-*-  TAG
   1.2*-   1.2 1.2-1.2*-
   1.3  \- 1.3*-   1.3   / 1.3
   1.4  \  1.4  /  1.4
Index: doc/src/sgml/indexcost.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/indexcost.sgml,v
retrieving revision 2.18
diff -c -c -r2.18 indexcost.sgml
*** doc/src/sgml/indexcost.sgml 29 Nov 2003 19:51:37 -  2.18
--- doc/src/sgml/indexcost.sgml 22 Jan 2005 21:33:59 -
***
*** 205,211 
  
   
  *indexSelectivity = clauselist_selectivity(root, indexQuals,
!rel->relid, JOIN_INNER);
   
  
 
--- 205,211 
  
   
  *indexSelectivity = clauselist_selectivity(root, indexQuals,
!rel->relid, JOIN_INNER);
   
  
 
Index: doc/src/sgml/plhandler.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/plhandler.sgml,v
retrieving revision 1.4
diff -c -c -r1.4 plhandler.sgml
*** doc/src/sgml/plhandler.sgml 5 Jan 2005 23:42:03 -   1.4
--- doc/src/sgml/plhandler.sgml 22 Jan 2005 21:33:59 -
***
*** 121,127 
  /*
   * Called as a trigger procedure
   */
! TriggerData*trigdata = (TriggerData *) fcinfo->context;
  
  retval = ...
  }
--- 121,127 
  /*
   * Called as a trigger procedure
   */
! TriggerData*trigdata = (TriggerData *) fcinfo->context;
  
  retval = ...
  }
Index: doc/src/sgml/release.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/release.sgml,v
retrieving revision 1.321
diff -c -c -r1.321 release.sgml
*** doc/src/sgml/release.sgml   15 Jan 2005 21:11:46 -  1.321
--- doc/src/sgml/release.sgml   22 Jan 2005 21:34:22 -
***
*** 7138,7144 
   
  Fix many CLUSTER failures (Tom)
  Allow ALTER TABLE RENAME works on indexes (Tom)
! Fix plpgsql to handle datetime->timestamp and timespan->interval (Bruce)
  New configure --with-setproctitle switch to use setproctitle() (Marc, Bruce)
  Fix the off by one errors in ResultSet from 6.5.3, and more.
  jdbc ResultSet fixes (Joseph Shraibman)
--- 7138,7144 
   
  Fix many CLUSTER failures (Tom)
  Allow ALTER TABLE RENAME works on indexes (Tom)
! Fix plpgsql to handle datetime->timestamp and timespan->interval (Bruce)
  New configure --with-setproctitle switch to use setproctitle() (Marc, Bruce)
  Fix the off by one errors in ResultSet from 6.5.3, and more.
  jdbc ResultSet fixes (Joseph Shraibman)
***
*** 8017,8023 
   
  Bug Fixes
  -
! Fix text<->float8 and text<->float4 conversion functions(Thomas)
  Fix for creating tables with mixed-case constraints(Billy)
  Change exp()/pow() behavior to generate error on underflow/overflow(Jan)
  Fix bug in pg_dump -z
--- 8017,8023 
   
  Bug Fixes
  -
! Fix text<->float8 and text<->float4 conversion functions(Thomas)
  Fix for creating tables with mixed-case constraints(Billy)
  Change exp()/pow() behavior to generate error on underflow/overflow(Jan)
  Fix bug in pg_dump -z
***
*** 9225,9231 
  using an axis-crossing algorithm(Thomas)
  Add routine to convert circle-box(Thomas)
  Merge conflicting operators for different geometric data types(Thomas)
! Replace distance operator "<===>" with "<->"(Thomas)

Re: [PATCHES] vacuum hint on elog

2005-01-22 Thread Tom Lane
Neil Conway <[EMAIL PROTECTED]> writes:
> It occurred to me that if we elog(ERROR) during VACUUM, the vacuum 
> activity hint will not be reset.

The code beginning at freelist.c line 645 is intended to deal with this.

> Attached is a patch which resets the vacuum activity hint in 
> AbortTransaction().

I dislike exposing such low-level issues to AbortTransaction().
If an explicit reset is needed, there should be something like
an AtAbort_Buffers() call that does this as well as any other
low-level issues needed (eg AbortBufferIO()).  Note that you
missed AbortSubTransaction() anyway.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] pg_autovacuum/Win32 Fixes

2005-01-22 Thread Dave Page
 

> -Original Message-
> From: Neil Conway [mailto:[EMAIL PROTECTED] 
> Sent: 22 January 2005 10:13
> To: Dave Page
> Cc: pgsql-patches@postgresql.org
> Subject: Re: [PATCHES] pg_autovacuum/Win32 Fixes
> 
> Dave Page wrote:
> > Theis patch supercedes *all* my earlier ones from today - 
> apologies for
> > the noise, clearly I need a beer or 3 and a few nights away from the
> > laptop.
> 
> @@ -1166,7 +1166,9 @@
>   exit(0);
>   #ifdef WIN32
>   case 'E':
> - args->service_dependencies = optarg;
> + ZeroMemory(deps, sizeof(deps));
> + snprintf(deps, sizeof(deps) - 2, "%s", optarg);
> + args->service_dependencies = (char *)deps;
>   break;
>   case 'I':
>   args->install_as_service++;
> 
> After googling around I can see what this code is intended to 
> do; in the 
> future a comment might be nice. 

Sorry - as you probably noticed it was one of 'those days'.

> Also, why not strncpy()?

No reason, just a hangover from some experimentation I was doing. I've
no objections if you want to change it.

> Barring any objections I'll apply this patch to REL8_0_STABLE 
> and HEAD 
> on Monday.

Thanks, Dave.

---(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


Re: [PATCHES] vacuum hint on elog

2005-01-22 Thread Neil Conway
Alvaro Herrera wrote:
Hmm ... I think you should rather use a PG_TRY/PG_CATCH block.
Thanks for the suggestion, Alvaro -- I think that's a better way to go. 
It means we can keep vacuum-specific stuff in vacuum.c, rather than 
adding to AbortTransaction(). I'll post a revised patch tomorrow.

While we're on the subject, the
set_bool_flag
do_something_complex
reset_bool_flag
coding pattern is really more fragile than it would initially seem to 
be. It is basically another variant of resource management, in the same 
way that manual memory management or explicit reference counting can be 
tricky to get right. For example, if a function that enables the vacuum 
hint recursively invokes itself, it is liable to reset the vacuum hint 
earlier than intended (vacuum_rel() comes close to making this mistake, 
although it does things right). We could make the vacuum hint a counter 
rather than a bool (bump the counter on "enable hint", decrement it on 
"disable hint", and treat "hint > 0" as "enabled"), but that just 
changes the error case slightly -- if you forget to bump/decrement the 
counter, you're still in trouble.

Perhaps to make this a bit less error prone we could add an assert/elog 
to StrategyHintVacuum(), which would raise an error/warning if the hint 
is enabled when it is already true. We shouldn't warn if the flag is 
disabled when it is already false, since (a) that is harmless (b) it is 
legitimate in an exception handler, as you suggested.

-Neil
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match


Re: [PATCHES] vacuum hint on elog

2005-01-22 Thread Alvaro Herrera
On Sat, Jan 22, 2005 at 11:28:52PM +1100, Neil Conway wrote:
> It occurred to me that if we elog(ERROR) during VACUUM, the vacuum 
> activity hint will not be reset. This will result in all subsequent I/O 
> by the current backend being treated by the bufmgr as though it resulted 
> from VACUUM. While elog(ERROR) during VACUUM is not a common occurrence, 
> I don't think it's wise to assume it is impossible.

Hmm ... I think you should rather use a PG_TRY/PG_CATCH block.  (Or
discover whether this is already done somewhere else ...)

-- 
Alvaro Herrera (<[EMAIL PROTECTED]>)
"El día que dejes de cambiar dejarás de vivir"

---(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


[PATCHES] vacuum hint on elog

2005-01-22 Thread Neil Conway
It occurred to me that if we elog(ERROR) during VACUUM, the vacuum 
activity hint will not be reset. This will result in all subsequent I/O 
by the current backend being treated by the bufmgr as though it resulted 
from VACUUM. While elog(ERROR) during VACUUM is not a common occurrence, 
I don't think it's wise to assume it is impossible.

Attached is a patch which resets the vacuum activity hint in 
AbortTransaction().

Barring any objections, I intend to apply this to REL8_0_STABLE and HEAD 
sometime on Monday.

-Neil
Index: src/backend/access/transam/xact.c
===
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/access/transam/xact.c,v
retrieving revision 1.195
diff -c -r1.195 xact.c
*** src/backend/access/transam/xact.c	31 Dec 2004 21:59:29 -	1.195
--- src/backend/access/transam/xact.c	22 Jan 2005 12:07:16 -
***
*** 32,37 
--- 32,38 
  #include "executor/spi.h"
  #include "libpq/be-fsstubs.h"
  #include "miscadmin.h"
+ #include "storage/buf_internals.h"
  #include "storage/fd.h"
  #include "storage/proc.h"
  #include "storage/sinval.h"
***
*** 1606,1611 
--- 1607,1621 
  	 */
  	LWLockReleaseAll();
  
+ 	/*
+ 	 * Reset the VACUUM activity hint. If we did elog(ERROR) during
+ 	 * VACUUM, this might still be set to true, so reset it here. For
+ 	 * the sake of correctness, we make sure to reset the flag fairly
+ 	 * early, before we've done any I/O that may be required for txn
+ 	 * abort.
+ 	 */
+ 	StrategyHintVacuum(false);
+ 
  	/* Clean up buffer I/O and buffer context locks, too */
  	AbortBufferIO();
  	UnlockBuffers();

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match


Re: [PATCHES] pg_autovacuum/Win32 Fixes

2005-01-22 Thread Neil Conway
Dave Page wrote:
Theis patch supercedes *all* my earlier ones from today - apologies for
the noise, clearly I need a beer or 3 and a few nights away from the
laptop.
@@ -1166,7 +1166,9 @@
exit(0);
 #ifdef WIN32
case 'E':
-   args->service_dependencies = optarg;
+   ZeroMemory(deps, sizeof(deps));
+   snprintf(deps, sizeof(deps) - 2, "%s", optarg);
+   args->service_dependencies = (char *)deps;
break;
case 'I':
args->install_as_service++;
After googling around I can see what this code is intended to do; in the 
future a comment might be nice. Also, why not strncpy()?

Barring any objections I'll apply this patch to REL8_0_STABLE and HEAD 
on Monday.

-Neil
---(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