[HACKERS] Initdb on Windows 2003

[James_Hughes]

Hello all,

Below is an email thread regarding a possible bug in PostgreSQL on
Windows 2003. Any help or advice anyone can give on this would be much
appreciated.

All the best,
James Hughes

--- EMAIL THREAD FOLLOWS ---

[EMAIL PROTECTED] wrote:
 Hi Bruce,
 
 We are now seeing this issue on three machines, all of which are 
 running Windows 2003. After some looking at the code and putting some 
 extra debug output (very little) all we have determined is that 
 initdb.exe opens postgres.exe via pipes, and at some point within 
 postgres.exe this error is generated.
 
 If we knock out the check for the user being not being admin and run 
 initdb.exe as an administrator all works as expected. Adding the user 
 to the 'Power Users' group also causes the Access Denined error! I 
 don't believe that the access denined is anything to do with file 
 access or memory (shared) access as I ran some tools to see what 
 postgres.exe is trying to access.
 
 Is there an easy way on Windows to step-through the PostgreSQL code as

 it runs? Is there any other information I can provide which will help 
 you with this defect?
 
 Any help getting to the root of this problem is much appericated.
 
 All the best
 James Hughes
 
 -Original Message-
 From: Bruce Momjian [mailto:[EMAIL PROTECTED]
 Sent: 24 February 2006 18:54
 To: Hughes, James
 Cc: pgsql-bugs@postgresql.org
 Subject: Re: [BUGS] BUG #2268: initdb fails to initialize database 
 cluster with error Access is denied
 
 
 Strange.  It isn't a typical error we see, and the fact you see it on 
 two machines is even stranger.  My guess is that somehow the 
 configuration on those two machines is the same and is causing the 
 failure.
 
 --
 --
 ---
 
 James Hughes wrote:
  
  The following bug has been logged online:
  
  Bug reference:  2268
  Logged by:  James Hughes
  Email address:  [EMAIL PROTECTED]
  PostgreSQL version: 8.1.3
  Operating system:   Windows 2003 (Server)
  Description:initdb fails to initialize database cluster with
 error
  Access is denied
  Details: 
  
  When we try and initialize a database cluster using initdb.exe we 
  are getting the following output.
  
  -- OUTPUT from dbinit.exe --
  The files belonging to this database system will be owned by user
 user1.
  This user must also own the server process.
  
  The database cluster will be initialized with locale English_United 
  Kingdom.1252.
  
  creating directory c:/dataa ... ok
  creating directory c:/dataa/global ... ok creating directory 
  c:/dataa/pg_xlog ... ok creating directory 
  c:/dataa/pg_xlog/archive_status ... ok creating directory 
  c:/dataa/pg_clog ... ok creating directory c:/dataa/pg_subtrans ... 
  ok
 
  creating directory c:/dataa/pg_twophase ... ok creating directory 
  c:/dataa/pg_multixact/members ... ok creating directory 
  c:/dataa/pg_multixact/offsets ... ok creating directory 
  c:/dataa/base ... ok creating directory c:/dataa/base/1 ... ok 
  creating directory c:/dataa/pg_tblspc ... ok selecting default 
  max_connections ... Access
 
  is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  10
  selecting default shared_buffers ... Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  50
  creating configuration files ... ok
  creating template1 database in c:/dataa/base/1 ... ok initializing 
  pg_authid ... Access is denied.
  child process was terminated by signal 1
  initdb: removing data directory c:/dataa
  
  -- end: OUTPUT from dbinit.exe --
  
  user1 is a user without administrative rights. I have checked all 
  the directories and the user has permissons to write to the data 
  directory, and read other files it requires.
  
  I also tried the Windows installer (MSI) from pgFoundry. This too is

  having the same problem.
  
  We have two machines which this problem occurs on, one of which 
  originally the initialization would work on. I have checked all the 
  user rights asssignments, and even given the postgres user (user1 in

  the above output from dbinit.exe) all user rights.
  
  ---(end of
  broadcast)---
  TIP 2: Don't 'kill -9' the postmaster
  
 
 -- 
   Bruce Momjian   http://candle.pha.pa.us
   SRA OSS, Inc.   http://www.sraoss.com
 
   + If your life is a hard drive, Christ can be your backup. +
 

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +






James Hughes


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] ipcclean in 8.1 broken?

[Christopher Kings-Lynne]

No-one has a comment on this?

Christopher Kings-Lynne wrote:
I just tried using ipcclean in 8.1.3.  It doesn't work when I su to the 
pgsql user.  This part of the script:


if [ $USER = 'root' -o $LOGNAME = 'root' ]

Always fails because even tho $USER is set to 'pgsql' when su'ed, 
$LOGNAME is still root.


This is on FreeBSD 4.9

Chris


---(end of broadcast)---
TIP 1: 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



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

Re: [HACKERS] ipcclean in 8.1 broken?

[Peter Eisentraut]

Am Dienstag, 28. Februar 2006 07:42 schrieb Christopher Kings-Lynne:
 I just tried using ipcclean in 8.1.3.  It doesn't work when I su to the
 pgsql user.  This part of the script:

 if [ $USER = 'root' -o $LOGNAME = 'root' ]

 Always fails because even tho $USER is set to 'pgsql' when su'ed,
 $LOGNAME is still root.

 This is on FreeBSD 4.9

It seems to work on Linux; apparently there are different behaviors of su.  Do 
you have a suggestion for resolving this?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

Re: [HACKERS] ipcclean in 8.1 broken?

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2006-03-01 12:49:13 +0100:
 Am Dienstag, 28. Februar 2006 07:42 schrieb Christopher Kings-Lynne:
  I just tried using ipcclean in 8.1.3.  It doesn't work when I su to the
  pgsql user.  This part of the script:
 
  if [ $USER = 'root' -o $LOGNAME = 'root' ]
 
  Always fails because even tho $USER is set to 'pgsql' when su'ed,
  $LOGNAME is still root.
 
  This is on FreeBSD 4.9
 
 It seems to work on Linux; apparently there are different behaviors of su.  
 Do 
 you have a suggestion for resolving this?

use su -l? this is on FreeBSD 6:

 By default, the environment is unmodified with the exception of USER,
 HOME, and SHELL.
 ...
 -l  Simulate a full login.  The environment is discarded except for
 HOME, SHELL, PATH, TERM, and USER.  HOME and SHELL are modified
 as above.  USER is set to the target login.  PATH is set to
 ``/bin:/usr/bin''.  TERM is imported from your current environ-
 ment.

[EMAIL PROTECTED] ~ 1001:0  echo $USER $LOGNAME
smradoch smradoch
[EMAIL PROTECTED] ~ 1002:0  su -l
Password:
neuhauser# echo $USER $LOGNAME
root root
neuhauser# logout
[EMAIL PROTECTED] ~ 1003:0  su   
Password:
You have mail.
neuhauser# echo $USER $LOGNAME
smradoch smradoch
neuhauser# exit
[EMAIL PROTECTED] ~ 1004:0  uname -srm
FreeBSD 6.1-PRERELEASE amd64

su (coreutils) 4.5.3 on RHEL3 behaves exactly the same.

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Initdb on Windows 2003

[Andrew Dunstan]

1. please show the EXACT initdb command line used.

2. Which Windows user was actually running initdb?

cheers

andrew

 Hello all,

 Below is an email thread regarding a possible bug in PostgreSQL on
 Windows 2003. Any help or advice anyone can give on this would be much
 appreciated.

 All the best,
 James Hughes

 --- EMAIL THREAD FOLLOWS ---

 [EMAIL PROTECTED] wrote:
 Hi Bruce,

 We are now seeing this issue on three machines, all of which are
 running Windows 2003. After some looking at the code and putting some
 extra debug output (very little) all we have determined is that
 initdb.exe opens postgres.exe via pipes, and at some point within
 postgres.exe this error is generated.

 If we knock out the check for the user being not being admin and run
 initdb.exe as an administrator all works as expected. Adding the user
 to the 'Power Users' group also causes the Access Denined error! I
 don't believe that the access denined is anything to do with file
 access or memory (shared) access as I ran some tools to see what
 postgres.exe is trying to access.

 Is there an easy way on Windows to step-through the PostgreSQL code as

 it runs? Is there any other information I can provide which will help
 you with this defect?

 Any help getting to the root of this problem is much appericated.

 All the best
 James Hughes

 -Original Message-
 From: Bruce Momjian [mailto:[EMAIL PROTECTED]
 Sent: 24 February 2006 18:54
 To: Hughes, James
 Cc: pgsql-bugs@postgresql.org
 Subject: Re: [BUGS] BUG #2268: initdb fails to initialize database
 cluster with error Access is denied


 Strange.  It isn't a typical error we see, and the fact you see it on
 two machines is even stranger.  My guess is that somehow the
 configuration on those two machines is the same and is causing the
 failure.

 --
 --
 ---

 James Hughes wrote:
 
  The following bug has been logged online:
 
  Bug reference:  2268
  Logged by:  James Hughes
  Email address:  [EMAIL PROTECTED]
  PostgreSQL version: 8.1.3
  Operating system:   Windows 2003 (Server)
  Description:initdb fails to initialize database cluster with
 error
  Access is denied
  Details:
 
  When we try and initialize a database cluster using initdb.exe we
  are getting the following output.
 
  -- OUTPUT from dbinit.exe --
  The files belonging to this database system will be owned by user
 user1.
  This user must also own the server process.
 
  The database cluster will be initialized with locale English_United
  Kingdom.1252.
 
  creating directory c:/dataa ... ok
  creating directory c:/dataa/global ... ok creating directory
  c:/dataa/pg_xlog ... ok creating directory
  c:/dataa/pg_xlog/archive_status ... ok creating directory
  c:/dataa/pg_clog ... ok creating directory c:/dataa/pg_subtrans ...
  ok

  creating directory c:/dataa/pg_twophase ... ok creating directory
  c:/dataa/pg_multixact/members ... ok creating directory
  c:/dataa/pg_multixact/offsets ... ok creating directory
  c:/dataa/base ... ok creating directory c:/dataa/base/1 ... ok
  creating directory c:/dataa/pg_tblspc ... ok selecting default
  max_connections ... Access

  is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  10
  selecting default shared_buffers ... Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  50
  creating configuration files ... ok
  creating template1 database in c:/dataa/base/1 ... ok initializing
  pg_authid ... Access is denied.
  child process was terminated by signal 1
  initdb: removing data directory c:/dataa
 
  -- end: OUTPUT from dbinit.exe --
 
  user1 is a user without administrative rights. I have checked all
  the directories and the user has permissons to write to the data
  directory, and read other files it requires.
 
  I also tried the Windows installer (MSI) from pgFoundry. This too is

  having the same problem.
 
  We have two machines which this problem occurs on, one of which
  originally the initialization would work on. I have checked all the
  user rights asssignments, and even given the postgres user (user1 in

  the above output from dbinit.exe) all user rights.
 
  ---(end of
  broadcast)---
  TIP 2: Don't 'kill -9' the postmaster
 

 --
   Bruce Momjian   http://candle.pha.pa.us
   SRA OSS, Inc.   http://www.sraoss.com

   + If your life is a hard drive, Christ can be your backup. +


 --
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +






 James Hughes


 ---(end of
 broadcast)--- TIP 6: explain analyze is your
 friend

Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....

[Christopher Browne]

[EMAIL PROTECTED] (Mark Kirkwood) wrote:
 Do you need name, value pairs? I was thinking that something like:

 # Postgres Cluster Registration
 #
 # PG_HOME PGDATA PORT
 /usr/local/pg7.4.1   /vol01/pggeo  5435
 /usr/local/pg7.4.1   /vol01/pgicdmdb   5434
 /usr/local/pg7.4.1   /vol03/pg74   5432

 Clearly other fields are possible (like ALIAS for the names you were
 using, and OPTS for extra arguments).

 This sort of layout is easily readable (more easily readable for those
 of us used to standard UNIX config files) and simply parsable too.

As mentioned before, the port number is redundant, and therefore
shouldn't be there at all.

There needs to be a name to identify each instance, so a mandatory
field is missing.
-- 
(format nil [EMAIL PROTECTED] cbbrowne gmail.com)
http://linuxdatabases.info/info/slony.html
By golly,  I'm beginning  to think Linux  really *is* the  best thing
since sliced bread. -- Vance Petree, Virginia Power

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Initdb on Windows 2003

[James_Hughes]

Hi,

1. The command line passed is -D c:\data --user=McAfeePostgresUser
2. McAfeePostgresUser which is a local machine user with limited
privileges. Though this problem occurs with any user account you create
with non-administrator privileges.

Thanks
James 

-Original Message-
From: Andrew Dunstan [mailto:[EMAIL PROTECTED] 
Sent: 01 March 2006 13:27
To: Hughes, James
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Initdb on Windows 2003



1. please show the EXACT initdb command line used.

2. Which Windows user was actually running initdb?

cheers

andrew

 Hello all,

 Below is an email thread regarding a possible bug in PostgreSQL on 
 Windows 2003. Any help or advice anyone can give on this would be much

 appreciated.

 All the best,
 James Hughes

 --- EMAIL THREAD FOLLOWS ---

 [EMAIL PROTECTED] wrote:
 Hi Bruce,

 We are now seeing this issue on three machines, all of which are 
 running Windows 2003. After some looking at the code and putting some

 extra debug output (very little) all we have determined is that 
 initdb.exe opens postgres.exe via pipes, and at some point within 
 postgres.exe this error is generated.

 If we knock out the check for the user being not being admin and run 
 initdb.exe as an administrator all works as expected. Adding the user

 to the 'Power Users' group also causes the Access Denined error! I 
 don't believe that the access denined is anything to do with file 
 access or memory (shared) access as I ran some tools to see what 
 postgres.exe is trying to access.

 Is there an easy way on Windows to step-through the PostgreSQL code 
 as

 it runs? Is there any other information I can provide which will help

 you with this defect?

 Any help getting to the root of this problem is much appericated.

 All the best
 James Hughes

 -Original Message-
 From: Bruce Momjian [mailto:[EMAIL PROTECTED]
 Sent: 24 February 2006 18:54
 To: Hughes, James
 Cc: pgsql-bugs@postgresql.org
 Subject: Re: [BUGS] BUG #2268: initdb fails to initialize database 
 cluster with error Access is denied


 Strange.  It isn't a typical error we see, and the fact you see it on

 two machines is even stranger.  My guess is that somehow the 
 configuration on those two machines is the same and is causing the 
 failure.

 -
 -
 --
 ---

 James Hughes wrote:
 
  The following bug has been logged online:
 
  Bug reference:  2268
  Logged by:  James Hughes
  Email address:  [EMAIL PROTECTED]
  PostgreSQL version: 8.1.3
  Operating system:   Windows 2003 (Server)
  Description:initdb fails to initialize database cluster
with
 error
  Access is denied
  Details:
 
  When we try and initialize a database cluster using initdb.exe we 
  are getting the following output.
 
  -- OUTPUT from dbinit.exe --
  The files belonging to this database system will be owned by user
 user1.
  This user must also own the server process.
 
  The database cluster will be initialized with locale English_United

  Kingdom.1252.
 
  creating directory c:/dataa ... ok
  creating directory c:/dataa/global ... ok creating directory 
  c:/dataa/pg_xlog ... ok creating directory 
  c:/dataa/pg_xlog/archive_status ... ok creating directory 
  c:/dataa/pg_clog ... ok creating directory c:/dataa/pg_subtrans ...
  ok

  creating directory c:/dataa/pg_twophase ... ok creating directory 
  c:/dataa/pg_multixact/members ... ok creating directory 
  c:/dataa/pg_multixact/offsets ... ok creating directory 
  c:/dataa/base ... ok creating directory c:/dataa/base/1 ... ok 
  creating directory c:/dataa/pg_tblspc ... ok selecting default 
  max_connections ... Access

  is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  10
  selecting default shared_buffers ... Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  Access is denied.
  50
  creating configuration files ... ok creating template1 database in 
  c:/dataa/base/1 ... ok initializing pg_authid ... Access is denied.
  child process was terminated by signal 1
  initdb: removing data directory c:/dataa
 
  -- end: OUTPUT from dbinit.exe --
 
  user1 is a user without administrative rights. I have checked all 
  the directories and the user has permissons to write to the data 
  directory, and read other files it requires.
 
  I also tried the Windows installer (MSI) from pgFoundry. This too 
  is

  having the same problem.
 
  We have two machines which this problem occurs on, one of which 
  originally the initialization would work on. I have checked all the

  user rights asssignments, and even given the postgres user (user1 
  in

  the above output from dbinit.exe) all user rights.
 
  ---(end of
  broadcast)---
  

Re: [HACKERS] ipcclean in 8.1 broken?

[Brusser, Michael]

I wonder if there could be a potential problem with using this approach
-
checking on $USER == root.

Although it is a common practice, I think a superuser does not have to
be root.
If I'm right here, a better technique could be executing `id`.

Mike
 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Peter
Eisentraut
Sent: Wednesday, March 01, 2006 6:49 AM
To: pgsql-hackers@postgresql.org
Cc: Christopher Kings-Lynne
Subject: Re: [HACKERS] ipcclean in 8.1 broken?

Am Dienstag, 28. Februar 2006 07:42 schrieb Christopher Kings-Lynne:
 I just tried using ipcclean in 8.1.3.  It doesn't work when I su to
the
 pgsql user.  This part of the script:

 if [ $USER = 'root' -o $LOGNAME = 'root' ]

 Always fails because even tho $USER is set to 'pgsql' when su'ed,
 $LOGNAME is still root.

 This is on FreeBSD 4.9

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Initdb on Windows 2003

[Tom Lane]

[EMAIL PROTECTED] writes:
 Below is an email thread regarding a possible bug in PostgreSQL on
 Windows 2003. Any help or advice anyone can give on this would be much
 appreciated.

Access is denied. is not a string that appears anywhere in the
Postgres source code.  My bet is some overenthusiastic anti-virus
code is interfering with the install.

regards, tom lane

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

Re: [HACKERS] ipcclean in 8.1 broken?

[Tom Lane]

Christopher Kings-Lynne [EMAIL PROTECTED] writes:
 No-one has a comment on this?

ipcclean has never been much more than beta-quality software; it doesn't
pretend to be very portable.

Having said that, I think the anti-root check is bogus.  It was probably
added in a fit of let's make sure nobody tries to admin PG as root,
but I don't see why that applies to ipcclean.  The only thing that
really matters is whether the subsequent id/whoami lookup comes up with
the proper user id.  I'd be inclined to do the id lookup and then bomb
out if it came up with 0 (just to ensure that no one accidentally blows
away really-important shared memory segments).

regards, tom lane

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

[HACKERS] statement_cost_limit

[Simon Riggs]

On Tue, 2006-02-21 at 11:45 +1300, Mark Kirkwood wrote:
 Simon Riggs wrote:
  A new parameter that allows the administrator to place sensible limits
  on the size of queries executed. 
  
  This is useful for ad-hoc SQL access 
  - when a very large table cannot realistically be sorted etc, so
  prevents general users from saying SELECT * FROM TABLE ORDER BY 1
  
  - for preventing poorly coded SQL with missing join conditions from
  causing impossibly huge cartesian joins which can tie up an important
  production system for the weekend etc..
  
  Use EXPLAIN to find out what to set this to.
  
  Generally useful?
 
 Yes, sure does look useful to me!

e.g.statement_cost_limit = 1000

This patch was discussed briefly on bizgres-general and is now being
submitted for discussion on main -hackers list.

Best Regards, Simon Riggs
Index: src/backend/tcop/postgres.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.474
diff -c -r1.474 postgres.c
*** src/backend/tcop/postgres.c	31 Dec 2005 16:50:44 -	1.474
--- src/backend/tcop/postgres.c	8 Jan 2006 21:45:16 -
***
*** 84,89 
--- 84,90 
  /* GUC variable for maximum stack depth (measured in kilobytes) */
  int			max_stack_depth = 2048;
  
+ int StatementCostLimit = 0;
  
  /* 
   *		private variables
***
*** 724,729 
--- 725,741 
  	/* call the optimizer */
  	plan = planner(querytree, false, 0, boundParams);
  
+ if (StatementCostLimit  0 
+ plan-total_cost  StatementCostLimit)
+ 		ereport(ERROR,
+ (errcode(ERRCODE_STATEMENT_TOO_COMPLEX),
+  errmsg(statement cost limit exceeded),
+  errhint(The plan for your query shows that it would likely 
+  have an excessive run time. This may be due to a 
+  logic error in the SQL, or it maybe just a very 
+  costly query. Rewrite your query or increase the 
+  configuration parameter \statement_cost_limit\.)));
+ 
  	if (log_planner_stats)
  		ShowUsage(PLANNER STATISTICS);
  
Index: src/backend/utils/misc/guc.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.305
diff -c -r1.305 guc.c
*** src/backend/utils/misc/guc.c	30 Dec 2005 00:13:50 -	1.305
--- src/backend/utils/misc/guc.c	8 Jan 2006 21:45:21 -
***
*** 1270,1275 
--- 1270,1284 
  	},
  
  	{
+ 		{statement_cost_limit, PGC_USERSET, CLIENT_CONN_STATEMENT,
+ 			gettext_noop(Sets the maximum allowed plan cost for any query.),
+ 			gettext_noop(A value of 0 turns off the the cost limit.)
+ 		},
+ 		StatementCostLimit,
+ 		0, 0, INT_MAX, NULL, NULL
+ 	},
+ 
+ 	{
  		{max_fsm_relations, PGC_POSTMASTER, RESOURCES_FSM,
  			gettext_noop(Sets the maximum number of tables and indexes for which free space is tracked.),
  			NULL
Index: src/include/tcop/tcopprot.h
===
RCS file: /projects/cvsroot/pgsql/src/include/tcop/tcopprot.h,v
retrieving revision 1.78
diff -c -r1.78 tcopprot.h
*** src/include/tcop/tcopprot.h	15 Oct 2005 02:49:46 -	1.78
--- src/include/tcop/tcopprot.h	8 Jan 2006 21:45:22 -
***
*** 28,33 
--- 28,34 
  extern CommandDest whereToSendOutput;
  extern DLLIMPORT const char *debug_query_string;
  extern int	max_stack_depth;
+ extern int StatementCostLimit;
  
  /* GUC-configurable parameters */
  

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Automatic free space map filling

[Peter Eisentraut]

Am Montag, 27. Februar 2006 19:42 schrieb Tom Lane:
 The free-space map is not the hard part of the problem.  You still have
 to VACUUM --- that is, wait until the dead tuple is not only committed
 dead but is certainly dead to all onlooker transactions, and then remove
 its index entries as well as the tuple itself.  The first part of this
 makes it impossible for a transaction to be responsible for vacuuming
 its own detritus.

I'm not sure if I made myself clear.  The idea is that you fill the free-space 
map early with opportunitistic entries in the hope that most updates and 
deletes go through soon.  That is, these entries will be invalid for a 
short time but hopefully by the time another write looks at them, the entries 
will have become valid.  That way you don't actually have to run vacuum on 
these deleted rows.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

   http://archives.postgresql.org

Re: [HACKERS] Initdb on Windows 2003

[Andrew Dunstan]

(private email says leaving off --user=foo doesn't fix it ;-( )

Since you have apparently compiled your own, could you please try with 
the latest stable initdb.c code for your release? That is version 
1.99.2.2 for release 8.1 or 1.73.4.3 for release 8.0. They are 
downloadable here: 
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/initdb/initdb.c


Maybe there's something odd about your setup - we have a WS2k3 machine 
happily building and running on buildfarm: see

http://www.pgbuildfarm.org/cgi-bin/show_history.pl?nm=snakebr=REL8_1_STABLE

If it still doesn't work, you might get some useful info from initdb --debug

Thanks

andrew


[EMAIL PROTECTED] wrote:


Hi,

1. The command line passed is -D c:\data --user=McAfeePostgresUser
2. McAfeePostgresUser which is a local machine user with limited
privileges. Though this problem occurs with any user account you create
with non-administrator privileges.

Thanks
James 


-Original Message-
From: Andrew Dunstan [mailto:[EMAIL PROTECTED] 
Sent: 01 March 2006 13:27

To: Hughes, James
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Initdb on Windows 2003



1. please show the EXACT initdb command line used.

2. Which Windows user was actually running initdb?

cheers

andrew

 


Hello all,

Below is an email thread regarding a possible bug in PostgreSQL on 
Windows 2003. Any help or advice anyone can give on this would be much
   



 


appreciated.

All the best,
James Hughes

--- EMAIL THREAD FOLLOWS ---

[EMAIL PROTECTED] wrote:
   


Hi Bruce,

We are now seeing this issue on three machines, all of which are 
running Windows 2003. After some looking at the code and putting some
 



 

extra debug output (very little) all we have determined is that 
initdb.exe opens postgres.exe via pipes, and at some point within 
postgres.exe this error is generated.


If we knock out the check for the user being not being admin and run 
initdb.exe as an administrator all works as expected. Adding the user
 



 

to the 'Power Users' group also causes the Access Denined error! I 
don't believe that the access denined is anything to do with file 
access or memory (shared) access as I ran some tools to see what 
postgres.exe is trying to access.


Is there an easy way on Windows to step-through the PostgreSQL code 
as
 


it runs? Is there any other information I can provide which will help
 



 


you with this defect?

Any help getting to the root of this problem is much appericated.

All the best
James Hughes

-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]
Sent: 24 February 2006 18:54
To: Hughes, James
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #2268: initdb fails to initialize database 
cluster with error Access is denied



Strange.  It isn't a typical error we see, and the fact you see it on
 



 

two machines is even stranger.  My guess is that somehow the 
configuration on those two machines is the same and is causing the 
failure.


-
-
--
---

James Hughes wrote:
 


The following bug has been logged online:

Bug reference:  2268
Logged by:  James Hughes
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.1.3
Operating system:   Windows 2003 (Server)
Description:initdb fails to initialize database cluster
   


with
 


error
 


Access is denied
Details:

When we try and initialize a database cluster using initdb.exe we 
are getting the following output.


-- OUTPUT from dbinit.exe --
The files belonging to this database system will be owned by user
   


user1.
 


This user must also own the server process.

The database cluster will be initialized with locale English_United
   



 


Kingdom.1252.

creating directory c:/dataa ... ok
creating directory c:/dataa/global ... ok creating directory 
c:/dataa/pg_xlog ... ok creating directory 
c:/dataa/pg_xlog/archive_status ... ok creating directory 
c:/dataa/pg_clog ... ok creating directory c:/dataa/pg_subtrans ...

ok
   

creating directory c:/dataa/pg_twophase ... ok creating directory 
c:/dataa/pg_multixact/members ... ok creating directory 
c:/dataa/pg_multixact/offsets ... ok creating directory 
c:/dataa/base ... ok creating directory c:/dataa/base/1 ... ok 
creating directory c:/dataa/pg_tblspc ... ok selecting default 
max_connections ... Access
   


is denied.
Access is denied.
Access is denied.
Access is denied.
Access is denied.
Access is denied.
10
selecting default shared_buffers ... Access is denied.
Access is denied.
Access is denied.
Access is denied.
Access is denied.
Access is denied.
Access is denied.
Access is denied.
Access is denied.
Access is denied.
Access is denied.
50
creating configuration files ... ok creating template1 database in 
c:/dataa/base/1 ... ok initializing pg_authid ... Access is denied.

child process was terminated by signal 1
initdb: removing data 

Re: [HACKERS] [ADMIN] Reg:lo_open error..pls help me

[Tom Lane]

Michael Fuhr [EMAIL PROTECTED] writes:
 On Wed, Mar 01, 2006 at 11:03:12AM -0500, Tom Lane wrote:
 Are you sure that's where it's failing?  The fragment you showed looks
 fine as far as it goes.  The most likely bet is you forgot to wrap it in
 a transaction (BEGIN/COMMIT commands), but that would result in a
 failure at the seek/write commands because the object wouldn't be open
 anymore.

 lo_open() fails if it's not in a transaction.  The error from
 PQerrorMessage is:
 ERROR:  invalid large-object descriptor: 0

Hmm, I wonder why that is [ looks at code ... ]

The culprit seems to be this little bit in libpq's lo_open() function:

/* have to do this to reset offset in shared fd cache */
/* but only if fd is valid */
if (fd = 0  lo_lseek(conn, fd, 0L, SEEK_SET)  0)
return -1;
return fd;

Outside a transaction block, this fails since the LO FD is already
closed by the time the lo_lseek request is run.

This hack goes all the way back --- it's in our original CVS version,
and there is equivalent code in Postgres v4r2 --- but it sure looks to
me like a workaround for a long-forgotten bug.  It's forcing an extra
network round trip for every lo_open, so I'm very strongly tempted to
remove it.  Comments?

regards, tom lane

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

Re: [PERFORM] [HACKERS] temporary indexes

[Kevin Grittner]

 On Tue, Feb 28, 2006 at  3:02 pm, in message
[EMAIL PROTECTED], Jim C. Nasby
[EMAIL PROTECTED]
wrote: 
 
 Maybe it's just the way my twisted mind thinks, but I generally
prefer
 using a JOIN when possible...

Definitely.  But sometimes you don't want one row from a table for each
qualifying row in another table, you want one row from the table if one
or more qualifying rows exist in the other table.  Those are the cases
in question here.  Don't suggest that I just let the duplicates happen
and use DISTINCT, that is much more prone to logic errors in complex
queries, and typically optimizes worse.

-Kevin


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Automatic free space map filling

[Tom Lane]

Peter Eisentraut [EMAIL PROTECTED] writes:
 I'm not sure if I made myself clear.  The idea is that you fill the 
 free-space 
 map early with opportunitistic entries in the hope that most updates and 
 deletes go through soon.  That is, these entries will be invalid for a 
 short time but hopefully by the time another write looks at them, the entries 
 will have become valid.  That way you don't actually have to run vacuum on 
 these deleted rows.

How does an optimistic FSM entry avoid the need to run vacuum?  All that
will happen is that some backend will visit the page and not find usable
free space.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] Vacuum dead tuples that are between

[Kevin Grittner]

 On Tue, Feb 28, 2006 at  7:22 am, in message
[EMAIL PROTECTED], Simon Riggs
[EMAIL PROTECTED] wrote: 
 
 OTOH a few hackers discussed this recently and found that nobody
used
 serializable transactions (ST) except during pg_dump.

I've not been able to keep up with all messages on these lists, and I
missed that discussion.

We use serializable transactions heavily; our whole middle tier
architecture depends on having that transaction isolation level for all
requests which modify data.  (You probably don't want to hear the
details.)  It would be OK (although a little disappointing) if VACUUM
enhancements weren't as beneficial to us as a result; it would render
PostgreSQL entirely unusable for us if the integrity of serializable
transactions was broken unless we added some other, non-standard steps
to run them.

We only use pg_dump for version upgrades and other special cases.  PITR
is our main backup technique.

-Kevin



---(end of broadcast)---
TIP 6: explain analyze is your friend

[HACKERS] 8.2 Feature Freeze Rough Estimate

[Robert Treat]

I'm trying to plan out a few postgresql related projects and could use some 
intuition on when folks think the feature freeze for 8.2 is going to occur. I 
thought it was going to be in Nov 06, but after talking to a couple of 
people, I've also heard that Aug 06 and Feb 07 seem to be possibilities. 
Obviously this makes quite a bit of difference for long term planning... I 
don't need an exact date, but if we could get an inkling on a given month or 
2 month timeframe if its going to be this year, or just a sometime next 
year if its going to be that far out, that would be great.   TIA

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

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

   http://archives.postgresql.org

Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....

[Lamar Owen]

On Monday 27 February 2006 19:59, Josh Berkus wrote:
  My frustration level often kills any desire to contribute to open
  source. Sometimes, I think that open source is doomed. The various
  projects I track and use are very frustrating, they remind me of
  dysfunctional engineering departments in huge companies, it is very hard
  to positively discuss any new ideas. The first response is always some
  variation on no.

 Well, if you weren't a regular I'd be more encouraging.  But you already
 know how things work here, so we can give you a hard time.I'll point
 out the year-long argument over the newsysviews for the contributors, the
 two-year long process for 2PC, etc.

For what it's worth, I've been longing for a multiple cluster multi-version 
capable centralized startup and control mechanism for at least five years, 
and I think the archives would bear this out.  I just have never had the time 
to implement it, and it was always an RPM-centric thought plan for me.
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu

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

Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....

[Lamar Owen]

On Monday 27 February 2006 21:09, Bruce Momjian wrote:
 One question I have is how this feature would be an improvement over
 just pointing pg_ctl at a postgresql.conf configuration file.  That
 config file has the ability to specify most if not all server
 parameters.

The big problem is that postgresql.conf is dynamically generated during 
initdb, and its location depends upon initdb's parameters directly.  This 
makes it difficult to distribute, at least for packagers, a template of 
postgresql.conf or a 'default' postgresql.conf that plays nice with multiple 
versions and clusters, yet has centralized database tracking.
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] statement_cost_limit

[Tom Lane]

Simon Riggs [EMAIL PROTECTED] writes:
 A new parameter that allows the administrator to place sensible limits
 on the size of queries executed. 

As I said when the idea was floated originally, I don't think this is a
very good idea at all.  The planner's estimates are sufficiently often
wrong that refusing to execute queries on the strength of an estimated
cost is going to burn you in both directions.

Even if it were a good idea, the proposed location of the test is 100%
wrong, as you are only guarding one path of query submission.  Or were
you intending that the restriction be trivial to subvert?

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] Automatic free space map filling

[Bruce Momjian]

Tom Lane wrote:
 Peter Eisentraut [EMAIL PROTECTED] writes:
  I'm not sure if I made myself clear.  The idea is that you fill the 
  free-space 
  map early with opportunitistic entries in the hope that most updates and 
  deletes go through soon.  That is, these entries will be invalid for a 
  short time but hopefully by the time another write looks at them, the 
  entries 
  will have become valid.  That way you don't actually have to run vacuum on 
  these deleted rows.
 
 How does an optimistic FSM entry avoid the need to run vacuum?  All that
 will happen is that some backend will visit the page and not find usable
 free space.

Because the index isn't removed, right?  That index thing is what
usually kills us.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Vacuum dead tuples that are between transactions

[Simon Riggs]

On Wed, 2006-03-01 at 10:22 -0600, Kevin Grittner wrote:
  On Tue, Feb 28, 2006 at  7:22 am, in message
 [EMAIL PROTECTED], Simon Riggs
 [EMAIL PROTECTED] wrote: 
  
  OTOH a few hackers discussed this recently and found that nobody
 used
  serializable transactions (ST) except during pg_dump.
 
 I've not been able to keep up with all messages on these lists, and I
 missed that discussion.

It was a verbal discussion, hence not recorded on list. I should have
said nobody on that discussion; I had no doubt somebody used them. My
mention of that wasn't to add weight to the thought, just to mention a
quick straw poll had been taken...

 We use serializable transactions heavily; our whole middle tier
 architecture depends on having that transaction isolation level for all
 requests which modify data.  (You probably don't want to hear the
 details.) 

*I* would, but others may not. ;-)

  It would be OK (although a little disappointing) if VACUUM
 enhancements weren't as beneficial to us as a result; it would render
 PostgreSQL entirely unusable for us if the integrity of serializable
 transactions was broken unless we added some other, non-standard steps
 to run them.

I would never suggest breaking STs; they are part of the SQL standard. I
merely suggested an extra, optional API by which ST users could provide
additional information that could help others avoid pessimal decisions
in order to preserve correctness.

 We only use pg_dump for version upgrades and other special cases.  PITR
 is our main backup technique.

Cool.

Best Regards, Simon Riggs


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

Re: [HACKERS] [SQL] Interval subtracting

[Mark Dilger]

Tom Lane wrote:

Milen A. Radev [EMAIL PROTECTED] writes:


Milorad Poluga напи�а:


SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 days'::interval
?column?
--- 
3 mons -14 days 

Why not '2 mons  16 days' ? 




Please read the last paragraph in section 8.5.1.4 of the manual
(http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775)
. It mentions the functions named justify_days and justify_hours
that could do what you need.



justify_days doesn't currently do anything with this result --- it
thinks its charter is only to reduce day components that are = 30 days.
However, I think a good case could be made that it should normalize
negative days too; that is, the invariant on its result should be
0 = days  30, not merely days  30.  Similarly for justify_hours.
Comments anyone?  Patch anyone?


Sure, if nobody objects to this change I can write the patch.

mark

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....

[Bruce Momjian]

Lamar Owen wrote:
 On Monday 27 February 2006 21:09, Bruce Momjian wrote:
  One question I have is how this feature would be an improvement over
  just pointing pg_ctl at a postgresql.conf configuration file.  That
  config file has the ability to specify most if not all server
  parameters.
 
 The big problem is that postgresql.conf is dynamically generated during 
 initdb, and its location depends upon initdb's parameters directly.  This 
 makes it difficult to distribute, at least for packagers, a template of 
 postgresql.conf or a 'default' postgresql.conf that plays nice with multiple 
 versions and clusters, yet has centralized database tracking.

But looking at postgresql.conf I see:

#data_directory = 'ConfigDir'   # use data in another directory
...
#port = 5432

so it seems everything in this configuration file is going to be
duplicated in postgresql.conf.

We are adding an include capability for postgresql.conf.  Does that help?

Also, keep in mind this TODO item:

* Allow pg_ctl to work properly with configuration files located outside
  the PGDATA directory

  pg_ctl can not read the pid file because it isn't located in the
  config directory but in the PGDATA directory.  The solution is to
  allow pg_ctl to read and understand postgresql.conf to find the
  data_directory value.

I am thinking it should be fixed as part of this.

What if we add an option to initdb to allow the user to specify the name
and location of the postgresql.conf file?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 1: 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: [HACKERS] Automatic free space map filling

[Peter Eisentraut]

Tom Lane wrote:
 How does an optimistic FSM entry avoid the need to run vacuum?

It ensures that all freed tuples are already in the FSM.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] statement_cost_limit

[Simon Riggs]

On Wed, 2006-03-01 at 11:47 -0500, Tom Lane wrote:
 Simon Riggs [EMAIL PROTECTED] writes:
  A new parameter that allows the administrator to place sensible limits
  on the size of queries executed. 
 
 As I said when the idea was floated originally, I don't think this is a
 very good idea at all.  The planner's estimates are sufficiently often
 wrong that refusing to execute queries on the strength of an estimated
 cost is going to burn you in both directions.

That depends upon your view on risk. Some admins would rather abort a
few queries wrongly in less than a second than risk having a query run
for hours before being cancelled by statement_timeout. Most end-users
would agree with this, because if the answer is No they want to hear it
quickly so they can correct their mistake and continue.

But I think the estimates aren't sufficiently wrong to make a big
difference. People with a 100GB+ table can set it with sufficiently
useful accuracy to avoid pointless attempts to sort that table, for
example.

 Even if it were a good idea, the proposed location of the test is 100%
 wrong, as you are only guarding one path of query submission.  Or were
 you intending that the restriction be trivial to subvert?

The main idea was to guard the path by which ad-hoc queries would come,
but you might want to set it on a dev server also for example.

Its a discussion point as to whether we'd want it the way I've coded, or
whether you want to block other routes also. I can see things both ways
on that and have no problem changing the behaviour if that is the
consensus; that change would be fairly quick.

Best Regards, Simon Riggs



---(end of broadcast)---
TIP 1: 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: [HACKERS] Automatic free space map filling

[Tom Lane]

Peter Eisentraut [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 How does an optimistic FSM entry avoid the need to run vacuum?

 It ensures that all freed tuples are already in the FSM.

That has nothing to do with it, because the space isn't actually free
for re-use until vacuum deletes the tuple.

regards, tom lane

---(end of broadcast)---
TIP 1: 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: [HACKERS] statement_cost_limit

[Bruce Momjian]

I can see this as useful for newbies who don't want to accidentally
overload the system.

---

Simon Riggs wrote:
 On Wed, 2006-03-01 at 11:47 -0500, Tom Lane wrote:
  Simon Riggs [EMAIL PROTECTED] writes:
   A new parameter that allows the administrator to place sensible limits
   on the size of queries executed. 
  
  As I said when the idea was floated originally, I don't think this is a
  very good idea at all.  The planner's estimates are sufficiently often
  wrong that refusing to execute queries on the strength of an estimated
  cost is going to burn you in both directions.
 
 That depends upon your view on risk. Some admins would rather abort a
 few queries wrongly in less than a second than risk having a query run
 for hours before being cancelled by statement_timeout. Most end-users
 would agree with this, because if the answer is No they want to hear it
 quickly so they can correct their mistake and continue.
 
 But I think the estimates aren't sufficiently wrong to make a big
 difference. People with a 100GB+ table can set it with sufficiently
 useful accuracy to avoid pointless attempts to sort that table, for
 example.
 
  Even if it were a good idea, the proposed location of the test is 100%
  wrong, as you are only guarding one path of query submission.  Or were
  you intending that the restriction be trivial to subvert?
 
 The main idea was to guard the path by which ad-hoc queries would come,
 but you might want to set it on a dev server also for example.
 
 Its a discussion point as to whether we'd want it the way I've coded, or
 whether you want to block other routes also. I can see things both ways
 on that and have no problem changing the behaviour if that is the
 consensus; that change would be fairly quick.
 
 Best Regards, Simon Riggs
 
 
 
 ---(end of broadcast)---
 TIP 1: 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
 

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] 8.2 Feature Freeze Rough Estimate

[Bruce Momjian]

Robert Treat wrote:
 I'm trying to plan out a few postgresql related projects and could use some 
 intuition on when folks think the feature freeze for 8.2 is going to occur. I 
 thought it was going to be in Nov 06, but after talking to a couple of 
 people, I've also heard that Aug 06 and Feb 07 seem to be possibilities. 
 Obviously this makes quite a bit of difference for long term planning... I 
 don't need an exact date, but if we could get an inkling on a given month or 
 2 month timeframe if its going to be this year, or just a sometime next 
 year if its going to be that far out, that would be great.   TIA
 

Good question.  I haven't seen any major feature go in yet that begs for
a new release, so I am guessing feature freeze will be sometime in the
June-August 2006 timeframe.  It could be later, but I doubt it would be
earlier.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Automatic free space map filling

[Alvaro Herrera]

Tom Lane wrote:
 Peter Eisentraut [EMAIL PROTECTED] writes:
  Tom Lane wrote:
  How does an optimistic FSM entry avoid the need to run vacuum?
 
  It ensures that all freed tuples are already in the FSM.
 
 That has nothing to do with it, because the space isn't actually free
 for re-use until vacuum deletes the tuple.

I think the idea is a different free space map of sorts, whereby a
transaction that obsoletes a tuple puts its block number in that map.  A
transaction that inserts a new tuple goes to the FSM.  If nothing is
found, it then goes to the new map.  A block returned from that map is
then scanned and any tuple that's no longer visible for anyone is
reused.

The problem with this idea is scanning the block and for each tuple
determine if it's alive.  Essentially, we would be folding the find
dead tuples and compress page logic, which is currently in vacuum, back
to insert.  IMHO this is unacceptable from a performance PoV.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [SQL] Interval subtracting

[Bruce Momjian]

Mark Dilger wrote:
 Tom Lane wrote:
  Milen A. Radev [EMAIL PROTECTED] writes:
  
 Milorad Poluga :
 
 SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 
 days'::interval
 ?column?
 --- 
 3 mons -14 days 
 
 Why not '2 mons  16 days' ? 
  
  
 Please read the last paragraph in section 8.5.1.4 of the manual
 (http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775)
 . It mentions the functions named justify_days and justify_hours
 that could do what you need.
  
  
  justify_days doesn't currently do anything with this result --- it
  thinks its charter is only to reduce day components that are = 30 days.
  However, I think a good case could be made that it should normalize
  negative days too; that is, the invariant on its result should be
  0 = days  30, not merely days  30.  Similarly for justify_hours.
  Comments anyone?  Patch anyone?
 
 Sure, if nobody objects to this change I can write the patch.

Good question.  Should we restrict days to 0 - 30 or -30 - 30?  The
current system does the later:

test= select justify_days('-45 days');
   justify_days
--
 -1 mons -15 days
(1 row)

test= select justify_days('1 month -45 days');
 justify_days
--
 -15 days
(1 row)

test= select justify_days('1 month -15 days');
  justify_days

 1 mon -15 days
(1 row)

Should we be adjusting the last one?  I am unsure.  Comments?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [SQL] Interval subtracting

[Mark Dilger]

Mark Dilger wrote:

Tom Lane wrote:


Milen A. Radev [EMAIL PROTECTED] writes:


Milorad Poluga напи�а:

SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 
days'::interval

?column?--- 3 mons -14 days
Why not '2 mons  16 days' ? 





Please read the last paragraph in section 8.5.1.4 of the manual
(http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775) 


. It mentions the functions named justify_days and justify_hours
that could do what you need.




justify_days doesn't currently do anything with this result --- it
thinks its charter is only to reduce day components that are = 30 days.
However, I think a good case could be made that it should normalize
negative days too; that is, the invariant on its result should be
0 = days  30, not merely days  30.  Similarly for justify_hours.
Comments anyone?  Patch anyone?



Sure, if nobody objects to this change I can write the patch.

mark


I've modified the code and it now behaves as follows:

  select justify_days('3 months -12 days'::interval);
justify_days
  
   2 mons 18 days

  select justify_days('3 months -33 days'::interval);
   justify_days
  ---
   1 mon 27 days

  select justify_hours('3 months -33 days -12 hours'::interval);
 justify_hours
  ---
   3 mons -34 days +12:00:00

  select justify_days(justify_hours('3 months -33 days -12 hours'::interval));
justify_days
  
   1 mon 26 days 12:00:00

  select justify_hours('-73 hours'::interval);
 justify_hours
  ---
   -4 days +23:00:00

  select justify_days('-62 days'::interval);
 justify_days
  --
   -3 mons +28 days


I find the last two results somewhat peculiar, as the new functionality pushes 
the negative values upwards (from hours to days, days to months).  Changing '-73 
hours' to '-3 days -1 hour' might be more intuitive?  The '-4 days +23 hours' is 
however consistent with the behavior in the other cases.


Thoughts?  I will package this up into a patch fairly soon.

mark

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

Re: [HACKERS] [SQL] Interval subtracting

[Jim C. Nasby]

On Wed, Mar 01, 2006 at 12:59:29PM -0500, Bruce Momjian wrote:
 Good question.  Should we restrict days to 0 - 30 or -30 - 30?  The
 current system does the later:
 
   test= select justify_days('-45 days');
  justify_days
   --
-1 mons -15 days
   (1 row)
 
   test= select justify_days('1 month -45 days');
justify_days
   --
-15 days
   (1 row)
 
   test= select justify_days('1 month -15 days');
 justify_days
   
1 mon -15 days
   (1 row)
 
 Should we be adjusting the last one?  I am unsure.  Comments?

ISTM it should be looking at the sign of the overall interval, and
sticking with that consistently. So while '1 mon 5 days' and '-3 mon -8
days' both make sense, '1 mon -2 days' doesn't make nearly as much sense
in the general case. Of course this is complicated by the fact that '1
mon 20 days' doesn't necessarily equate to '2 mon -10 days'... :(

One of these days we should just create a new calendar. ;)
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

[HACKERS] PostgreSQL Anniversary Summit, Call for Contributions

[Peter Eisentraut]

PostgreSQL Anniversary Summit
=

Call for Contributions
--

The PostgreSQL Anniversary Summit will take place on July 8 and 9, 2006, in 
Toronto, Canada.  We are planning for a gathering of about 50 hackers, 
contributors, and other friends of the PostgreSQL project to celebrate the 
project's 10th anniversary, reflect on the work accomplished, establish new 
contacts, and plan for the future.  The summit will feature speaker sessions, 
workshops, discussion groups, and social events.  We are now looking for 
content proposals.  Topics can include:

- Development, how to and how not to

- Features for the future (or of the past)

- PostgreSQL-related research projects

- Issues relating to the project's organization

- PostgreSQL-related projects

- Legal issues

- Non-profit organizations

- Advocacy, marketing

- How to make PostgreSQL more appealing to $X

- Business aspects

- Other interesting event proposals such as discussions, contests, awards,
  question sessions, etc. will also be considered if you are prepared to
  organize them.

There is considerable freedom in developing the program.  Anything that is 
important to you, of interest to others, and of value to the project can be 
reasonable.  But remember that this is a conference of PostgreSQL 
contributors, so user-level talks should normally not be submitted.

Submissions and the actual sessions should be in English.  Contributions 
should generally use time slots of 45 minutes, but feel free to specify 
otherwise if you have special requirements.  We are also welcoming lightning 
talks of about 5 minutes.

Send submissions to [EMAIL PROTECTED] in free form, 
but include the following information:

- your name
- your e-mail address
- title of your contribution
- type of your contribution (talk, discussion, etc.)
- abstract of up to 100 words (for publishing in the program)
- extended description (for review by the organizers, not published)

The deadline for submissions is March 31st.

Speakers and other supporters of the conference program (exception: lightning 
talks) will be offered free registration.  They will also be first in line to 
receive financial assistance, but we cannot guarantee any such thing at the 
moment, so be prepared to pay for your travel and accomodation.

-- 
Peter Eisentraut
on behalf of the conference team


pgpgZV3YNX891.pgp
Description: PGP signature

Re: [HACKERS] bug in 7.3.2

[Suvarna]

Hello Jonah,

No we are not caching the sequence
In thetransactionfor the first time we 
use next val  then on we use curretn val.

Regards,
Suvarna


  - Original Message - 
  From: 
  Jonah H. 
  Harris 
  To: Suvarna 
  Cc: pgsql-hackers@postgresql.org 
  
  Sent: Tuesday, February 28, 2006 8:56 
  PM
  Subject: Re: [HACKERS] bug in 7.3.2
  Are you caching sequences?
  On 2/28/06, Suvarna 
  [EMAIL PROTECTED] 
  wrote:
  
we are using postgresql 7.3.2 
version.
We are facing a problem in nextval of sequence. 
The problem is as follows,
If the 
server shuts down abrupotly because of power failuar or any othercause 
then the sequences tend to skip few numbers.After restarting 
theserver the nextval of sequence doest matchwith the last 
number.-- 
  Jonah H. Harris, Database Internals ArchitectEnterpriseDB 
  Corporation732.331.1324

Re: [HACKERS] bug in 7.3.2

[Suvarna]

Hello Tom,
thankyou for the reply but,
actually the number which are missing are in the range of 20-30 and at the
max only 3 transactions are going on at any given point in time.
So if 3 numbers are missing then it was understood the missing numbers are
very large.

Regards,
suvarna

- Original Message -
From: Tom Lane [EMAIL PROTECTED]
To: Suvarna [EMAIL PROTECTED]
Cc: pgsql-hackers@postgresql.org
Sent: Tuesday, February 28, 2006 9:29 PM
Subject: Re: [HACKERS] bug in 7.3.2


 Suvarna [EMAIL PROTECTED] writes:
  We are facing a problem in nextval of sequence. The problem is as =
  follows,
  If the server shuts down abrupotly because of power failuar or any other
  cause then the sequences tend to skip few numbers.

 This is not a bug, it is the designed behavior.  It's not really
 different from the case of a number going unused because a transaction
 does nextval() and then rolls back --- you cannot assume that the
 sequence of used values has no holes, in any case.

 regards, tom lane


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[HACKERS] FW: Rép. : Re: [PERFORM] Bad plan on a v iew ([Congés])

[Jim Nasby]

Someone want to remove/suspend Antoine Bajolet [EMAIL PROTECTED] from the 
lists? He's sending these emails to the author of every list email (not sure if 
he's on more than just -perform).

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Antoine
Bajolet
Sent: Wednesday, March 01, 2006 12:48 PM
To: Jim Nasby
Subject: Rép. : Re: [PERFORM] Bad plan on a view ([Congés])


Actuellement en congés, je serais de retour le 6/03/2006
En cas de problème bloquant, veuillez contacter Gilles Pierret.

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

   http://archives.postgresql.org

Re: [HACKERS] 8.2 Feature Freeze Rough Estimate

[Jonah H. Harris]

Bruce,

I haven't had much time to finish up on the WITH recursive/hierarchical
query stuff yet, but was wondering when you see it having to be
completed if freeze is in August? Would May 1st be too late?
On 3/1/06, Bruce Momjian pgman@candle.pha.pa.us wrote:
Robert Treat wrote: I'm trying to plan out a few postgresql related projects and could use some intuition on when folks think the feature freeze for 8.2 is going to occur. I thought it was going to be in Nov 06, but after talking to a couple of
 people, I've also heard that Aug 06 and Feb 07 seem to be possibilities. Obviously this makes quite a bit of difference for long term planning... I don't need an exact date, but if we could get an inkling on a given month or
 2 month timeframe if its going to be this year, or just a sometime next year if its going to be that far out, that would be great. TIAGood question.I haven't seen any major feature go in yet that begs for
a new release, so I am guessing feature freeze will be sometime in theJune-August 2006 timeframe.It could be later, but I doubt it would beearlier.--Bruce Momjian 
http://candle.pha.pa.usSRA OSS, Inc. http://www.sraoss.com+ If your life is a hard drive, Christ can be your backup. +---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq-- Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation732.331.1324

Re: [HACKERS] FW: Rép. : Re: [PERFORM] Bad plan on a v

[Marc G. Fournier]

Gone

On Wed, 1 Mar 2006, Jim Nasby wrote:


Someone want to remove/suspend Antoine Bajolet [EMAIL PROTECTED] from the 
lists? He's sending these emails to the author of every list email (not sure if he's on more 
than just -perform).

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Antoine
Bajolet
Sent: Wednesday, March 01, 2006 12:48 PM
To: Jim Nasby
Subject: Rép. : Re: [PERFORM] Bad plan on a view ([Congés])


Actuellement en congés, je serais de retour le 6/03/2006
En cas de problème bloquant, veuillez contacter Gilles Pierret.

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

  http://archives.postgresql.org




Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [HACKERS] 8.2 Feature Freeze Rough Estimate

[Andrew Dunstan]

Jonah H. Harris wrote:


Bruce,

I haven't had much time to finish up on the WITH 
recursive/hierarchical query stuff yet, but was wondering when you see 
it having to be completed if freeze is in August?  Would May 1st be 
too late?





Doesn't feature freeze mean that's when you have to have your patches 
submitted by? Of course, dropping a patch for a huge feature like this 
at the last minute would be bad form, but it sure doesn't have to be in 
3 months or so before feature freeze.


BTW, this feature along with maybe MERGE (is anyone working on that?) 
would be enough to make a release quite featureful enough, IMNSHO.


cheers

andrew

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

  http://archives.postgresql.org

Re: [HACKERS] 8.2 Feature Freeze Rough Estimate

[Bruce Momjian]

Jonah H. Harris wrote:
 Bruce,
 
 I haven't had much time to finish up on the WITH recursive/hierarchical
 query stuff yet, but was wondering when you see it having to be completed if
 freeze is in August?  Would May 1st be too late?

Let us know when you start working on it and we will see it gets in.  It
is more communication in knowing what is in process that helps us set
the date.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] bug in 7.3.2

[Michael Fuhr]

On Wed, Mar 01, 2006 at 10:17:04AM +0530, Suvarna wrote:
 actually the number which are missing are in the range of 20-30 and at the
 max only 3 transactions are going on at any given point in time.
 So if 3 numbers are missing then it was understood the missing numbers are
 very large.

The number of transactions is irrelevant.  If a single transaction
obtains 20 values from a sequence and that transaction is rolled
back, then those 20 sequence values are gone.  Think of a sequence
as a generator of arbitrary unique numbers, not as a way to get
numbers guaranteed to have no gaps.

-- 
Michael Fuhr

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

   http://archives.postgresql.org

Re: [HACKERS] [SQL] Interval subtracting

[Bruce Momjian]

Mark Dilger wrote:
select justify_hours('-73 hours'::interval);
   justify_hours
---
 -4 days +23:00:00
 
select justify_days('-62 days'::interval);
   justify_days
--
 -3 mons +28 days
 
 
 I find the last two results somewhat peculiar, as the new functionality
 pushes the negative values upwards (from hours to days, days to months).
 Changing '-73 hours' to '-3 days -1 hour' might be more intuitive?
 The '-4 days +23 hours' is however consistent with the behavior in the
 other cases.

I don't think we can accept a change that takes a negative and turns it
into a positive and negative.  I think the answer to the last one should
be '-2 mons -2 days', which is what it does now:

test= select justify_days('-62 days'::interval);
  justify_days
-
 -2 mons -2 days
(1 row)

The open question is whether we should convert a positive and negative
to a positive, or a negative, based on the sign of the highest value,
e.g. convert '1 mons -10 days' to '20 days', and '-1 mons 10 days' to
'-20 days'?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] [SQL] Interval subtracting

[Bruce Momjian]

Stephan Szabo wrote:
  justify_days doesn't currently do anything with this result --- it
  thinks its charter is only to reduce day components that are = 30 days.
  However, I think a good case could be made that it should normalize
  negative days too; that is, the invariant on its result should be
  0 = days  30, not merely days  30.
 
 What about cases like interval '1 month -99 days', should that turn into
 interval '-3 mons +21 days' or '-2 mons -9 days'?

I think it should be the later.  It is best to have a single sign, and I
think it is possible in all cases:

'2 mons -1 days'

could be adjusted to '1 mons 29 days'.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] [SQL] Interval subtracting

[Scott Marlowe]

On Wed, 2006-03-01 at 14:18, Bruce Momjian wrote:
 Stephan Szabo wrote:
   justify_days doesn't currently do anything with this result --- it
   thinks its charter is only to reduce day components that are = 30 days.
   However, I think a good case could be made that it should normalize
   negative days too; that is, the invariant on its result should be
   0 = days  30, not merely days  30.
  
  What about cases like interval '1 month -99 days', should that turn into
  interval '-3 mons +21 days' or '-2 mons -9 days'?
 
 I think it should be the later.  It is best to have a single sign, and I
 think it is possible in all cases:
 
   '2 mons -1 days'
 
 could be adjusted to '1 mons 29 days'.

There's a part of me that thinks the WHOLE THING should be positive or
negative:

-(2 months 1 day)



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

Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....

[Mark Kirkwood]

Christopher Browne wrote:

[EMAIL PROTECTED] (Mark Kirkwood) wrote:


Do you need name, value pairs? I was thinking that something like:

# Postgres Cluster Registration
#
# PG_HOME PGDATA PORT
/usr/local/pg7.4.1   /vol01/pggeo  5435
/usr/local/pg7.4.1   /vol01/pgicdmdb   5434
/usr/local/pg7.4.1   /vol03/pg74   5432

Clearly other fields are possible (like ALIAS for the names you were
using, and OPTS for extra arguments).

This sort of layout is easily readable (more easily readable for those
of us used to standard UNIX config files) and simply parsable too.



As mentioned before, the port number is redundant, and therefore
shouldn't be there at all.


Now that's an interesting one - I would be quite keen on being able to 
override postgresql.conf's port (typically do it now using PGPORT env) - 
however if there is a concensus that it's a bad thing, then lets drop it.



There needs to be a name to identify each instance, so a mandatory
field is missing.


Yeah, agreed - my vague muttering about ALIAS was attempting to suggest 
that :-).


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] [SQL] Interval subtracting

[Bruce Momjian]

Scott Marlowe wrote:
 On Wed, 2006-03-01 at 14:18, Bruce Momjian wrote:
  Stephan Szabo wrote:
justify_days doesn't currently do anything with this result --- it
thinks its charter is only to reduce day components that are = 30 days.
However, I think a good case could be made that it should normalize
negative days too; that is, the invariant on its result should be
0 = days  30, not merely days  30.
   
   What about cases like interval '1 month -99 days', should that turn into
   interval '-3 mons +21 days' or '-2 mons -9 days'?
  
  I think it should be the later.  It is best to have a single sign, and I
  think it is possible in all cases:
  
  '2 mons -1 days'
  
  could be adjusted to '1 mons 29 days'.
 
 There's a part of me that thinks the WHOLE THING should be positive or
 negative:
 
 -(2 months 1 day)

But it isn't '-2 months, -1 day'.  I think what you are saying is what I
am saying, that we should make the signs consistent.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [SQL] Interval subtracting

[Tom Lane]

Bruce Momjian pgman@candle.pha.pa.us writes:
 I don't think we can accept a change that takes a negative and turns it
 into a positive and negative.

Yeah, I find the patch's changes to the regression results pretty
disturbing.

Perhaps the correct definition ought to be like if month part = 0
then the reduced day part should be between 0 and 30, otherwise the
reduced day part should be between 0 and -30.  However there are
still corner cases to worry about.  If the original month and day
parts are of different sign, you might not be able to do such a
reduction without changing the sign of the month part, consider
1 month -95 days.  Not clear what to do with this.

I guess I would expect a good result to satisfy one of these three
cases:
* month  0 and 0 = day  30
* month  0 and -30  day = 0
* month = 0 and -30  day  30
If you believe that then 1 month -95 days should justify to
-2 months -5 days.

regards, tom lane

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

Re: [HACKERS] [SQL] Interval subtracting

[Hannu Krosing]

Ühel kenal päeval, K, 2006-03-01 kell 15:18, kirjutas Bruce Momjian:
 Stephan Szabo wrote:
   justify_days doesn't currently do anything with this result --- it
   thinks its charter is only to reduce day components that are = 30 days.
   However, I think a good case could be made that it should normalize
   negative days too; that is, the invariant on its result should be
   0 = days  30, not merely days  30.
  
  What about cases like interval '1 month -99 days', should that turn into
  interval '-3 mons +21 days' or '-2 mons -9 days'?
 
 I think it should be the later.  It is best to have a single sign, and I
 think it is possible in all cases:
 
   '2 mons -1 days'
 
 could be adjusted to '1 mons 29 days'.

But unfortunately '2 mons -1 days'  '1 mons 29 days'

If I want something to happen 1 day less than two months from dome date,
then the only way to say that consistently *is* '2 mons -1 days'.

--
Hannu




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [SQL] Interval subtracting

[Stephan Szabo]

On Wed, 1 Mar 2006, Hannu Krosing wrote:

 Ühel kenal päeval, K, 2006-03-01 kell 15:18, kirjutas Bruce Momjian:
  Stephan Szabo wrote:
justify_days doesn't currently do anything with this result --- it
thinks its charter is only to reduce day components that are = 30 days.
However, I think a good case could be made that it should normalize
negative days too; that is, the invariant on its result should be
0 = days  30, not merely days  30.
  
   What about cases like interval '1 month -99 days', should that turn into
   interval '-3 mons +21 days' or '-2 mons -9 days'?
 
  I think it should be the later.  It is best to have a single sign, and I
  think it is possible in all cases:
 
  '2 mons -1 days'
 
  could be adjusted to '1 mons 29 days'.

 But unfortunately '2 mons -1 days'  '1 mons 29 days'

 If I want something to happen 1 day less than two months from dome date,
 then the only way to say that consistently *is* '2 mons -1 days'.

Right, but would you call justify_days on such an interval?  '2 months -1
days'  '1 mon 29 days', but '1 mon 60 days' is also  '3 mons' in
general usage.

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

   http://archives.postgresql.org

Re: [HACKERS] [SQL] Interval subtracting

[Bruce Momjian]

Hannu Krosing wrote:
 ?hel kenal p?eval, K, 2006-03-01 kell 15:18, kirjutas Bruce Momjian:
  Stephan Szabo wrote:
justify_days doesn't currently do anything with this result --- it
thinks its charter is only to reduce day components that are = 30 days.
However, I think a good case could be made that it should normalize
negative days too; that is, the invariant on its result should be
0 = days  30, not merely days  30.
   
   What about cases like interval '1 month -99 days', should that turn into
   interval '-3 mons +21 days' or '-2 mons -9 days'?
  
  I think it should be the later.  It is best to have a single sign, and I
  think it is possible in all cases:
  
  '2 mons -1 days'
  
  could be adjusted to '1 mons 29 days'.
 
 But unfortunately '2 mons -1 days'  '1 mons 29 days'
 
 If I want something to happen 1 day less than two months from dome date,
 then the only way to say that consistently *is* '2 mons -1 days'.

Right, but you asked to justify the days by calling the function.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] [SQL] Interval subtracting

[Bruce Momjian]

Tom Lane wrote:
 Bruce Momjian pgman@candle.pha.pa.us writes:
  I don't think we can accept a change that takes a negative and turns it
  into a positive and negative.
 
 Yeah, I find the patch's changes to the regression results pretty
 disturbing.
 
 Perhaps the correct definition ought to be like if month part = 0
 then the reduced day part should be between 0 and 30, otherwise the
 reduced day part should be between 0 and -30.  However there are
 still corner cases to worry about.  If the original month and day
 parts are of different sign, you might not be able to do such a
 reduction without changing the sign of the month part, consider
 1 month -95 days.  Not clear what to do with this.
 
 I guess I would expect a good result to satisfy one of these three
 cases:
   * month  0 and 0 = day  30
   * month  0 and -30  day = 0
   * month = 0 and -30  day  30
 If you believe that then 1 month -95 days should justify to
 -2 months -5 days.

I believe it.  :-)

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [SQL] Interval subtracting

[Tom Lane]

Hannu Krosing [EMAIL PROTECTED] writes:
 But unfortunately '2 mons -1 days'  '1 mons 29 days'
 If I want something to happen 1 day less than two months from dome date,
 then the only way to say that consistently *is* '2 mons -1 days'.

Sure, but if you want to represent that then you don't pass the value
through justify_days().  The entire premise of justify_days() is that
1 month is interchangeable with 30 days and we should try to make the
value look nice given that assumption.

I think everyone's independently arrived at the same thought that
justify_days should not produce a result with different signs for month
and day (except for the case with month = 0, per my last message).

regards, tom lane

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

Re: [HACKERS] [SQL] Interval subtracting

[Mark Dilger]

Tom Lane wrote:

Bruce Momjian pgman@candle.pha.pa.us writes:


I don't think we can accept a change that takes a negative and turns it
into a positive and negative.



Yeah, I find the patch's changes to the regression results pretty
disturbing.

Perhaps the correct definition ought to be like if month part = 0
then the reduced day part should be between 0 and 30, otherwise the
reduced day part should be between 0 and -30.  However there are
still corner cases to worry about.  If the original month and day
parts are of different sign, you might not be able to do such a
reduction without changing the sign of the month part, consider
1 month -95 days.  Not clear what to do with this.

I guess I would expect a good result to satisfy one of these three
cases:
* month  0 and 0 = day  30
* month  0 and -30  day = 0
* month = 0 and -30  day  30
If you believe that then 1 month -95 days should justify to
-2 months -5 days.

regards, tom lane


How would you expect justify_hours to behave?  I extrapolate from your rules 
above that:


  * month  0 and 0 = day  30 and 0 = hours  24
  * month  0 and -30  day = 0 and -24  hours = 0
  * month = 0 and -30  day = 0 and -24  hours = 0
  * month = 0 and 0 = day  30 and 0 = hours  24

Which would mean that '1 month -95 days -12 hours' should justify to -2 months 
-5 days -12 hours rather than -2 months -6 days 12 hours, but that '1 month 
-15 days -12 hours would justify to '14 days 12 hours' rather than '15 days -12 
hours'.


Is this correct?

mark

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] [SQL] Interval subtracting

[Mark Dilger]

Hannu Krosing wrote:

But unfortunately '2 mons -1 days'  '1 mons 29 days'

If I want something to happen 1 day less than two months from dome date,
then the only way to say that consistently *is* '2 mons -1 days'.


Correct me if I am wrong, but I thought that justify_days would only be called 
if the user wanted it.  I get the following behavior in psql even after the 
patch is applied:


select '2 mons -1 days'::interval;
interval

 2 mons -1 days


So there does not seem to be any justification going on without the user's 
permission.  Consequently, if you need '2 mons -1 days', don't call justify_days.


Am I missing something?

mark

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....

[Mark Kirkwood]

Bruce Momjian wrote:

Lamar Owen wrote:


On Monday 27 February 2006 21:09, Bruce Momjian wrote:


One question I have is how this feature would be an improvement over
just pointing pg_ctl at a postgresql.conf configuration file.  That
config file has the ability to specify most if not all server
parameters.


The big problem is that postgresql.conf is dynamically generated during 
initdb, and its location depends upon initdb's parameters directly.  This 
makes it difficult to distribute, at least for packagers, a template of 
postgresql.conf or a 'default' postgresql.conf that plays nice with multiple 
versions and clusters, yet has centralized database tracking.



But looking at postgresql.conf I see:

#data_directory = 'ConfigDir'   # use data in another directory
...
#port = 5432

so it seems everything in this configuration file is going to be
duplicated in postgresql.conf.

We are adding an include capability for postgresql.conf.  Does that help?

Also, keep in mind this TODO item:

* Allow pg_ctl to work properly with configuration files located outside
  the PGDATA directory

  pg_ctl can not read the pid file because it isn't located in the
  config directory but in the PGDATA directory.  The solution is to
  allow pg_ctl to read and understand postgresql.conf to find the
  data_directory value.

I am thinking it should be fixed as part of this.

What if we add an option to initdb to allow the user to specify the name
and location of the postgresql.conf file?



That is certainly a way to approach it, I see the tough bit being the 
parsing of postgresql.conf to figure out which parts of the global 
included file to ignore (i.e the stuff for the *other* clusters).


Would this work for the situation where you have older clusters on the 
box (versions that don't understand 'include')?


Additionally this would need to tackle start|stop etc for all clusters...

Cheers

Mark

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

Re: [HACKERS] [SQL] Interval subtracting

[Tom Lane]

Mark Dilger [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 I guess I would expect a good result to satisfy one of these three
 cases:
  * month  0 and 0 = day  30
  * month  0 and -30  day = 0
  * month = 0 and -30  day  30
 If you believe that then 1 month -95 days should justify to
 -2 months -5 days.

 How would you expect justify_hours to behave?  I extrapolate from your rules 
 above that:

* month  0 and 0 = day  30 and 0 = hours  24
* month  0 and -30  day = 0 and -24  hours = 0
* month = 0 and -30  day = 0 and -24  hours = 0
* month = 0 and 0 = day  30 and 0 = hours  24

Hmmm ... I think it would be better if the two functions were
independent, if possible.  Your spec above implies that justify_hours
implicitly does justify_days as well, which seems a bit restrictive.

Furthermore, justify_hours should only assume that 1 day == 24 hours,
which while broken by DST is still a lot solider assumption than
justify_days' 1 month == 30 days.  I can well believe that a lot of
people only want to make the first assumption.

So I'm inclined to think that justify_hours is responsible for reducing
the seconds part to less-than-24-hours and pushing any overflow into the
days part (but not touching months), while justify_days is responsible
for reducing the days part to less-than-30-days and pushing any overflow
into the months part (but not touching seconds).  If you want both you
apply both functions, probably in that order.  (I wonder if there are
any cases where applying justify_days before justify_hours would be
useful.  Offhand I can't see one ...)

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] [SQL] Interval subtracting

[Scott Marlowe]

On Wed, 2006-03-01 at 14:27, Bruce Momjian wrote:
 Scott Marlowe wrote:
  On Wed, 2006-03-01 at 14:18, Bruce Momjian wrote:
   Stephan Szabo wrote:
 justify_days doesn't currently do anything with this result --- it
 thinks its charter is only to reduce day components that are = 30 
 days.
 However, I think a good case could be made that it should normalize
 negative days too; that is, the invariant on its result should be
 0 = days  30, not merely days  30.

What about cases like interval '1 month -99 days', should that turn into
interval '-3 mons +21 days' or '-2 mons -9 days'?
   
   I think it should be the later.  It is best to have a single sign, and I
   think it is possible in all cases:
   
 '2 mons -1 days'
   
   could be adjusted to '1 mons 29 days'.
  
  There's a part of me that thinks the WHOLE THING should be positive or
  negative:
  
  -(2 months 1 day)
 
 But it isn't '-2 months, -1 day'.  I think what you are saying is what I
 am saying, that we should make the signs consistent.

Pretty much.  It just seems wrong to have different signs in what is
essentially a single unit.

We don't say 42 degrees, -12 minutes when measuring arc, do we?  Then
again, maybe some folks do.  It just seems wrong to me.

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

Re: [HACKERS] [SQL] Interval subtracting

[Mark Dilger]

Tom Lane wrote:

Mark Dilger [EMAIL PROTECTED] writes:


Tom Lane wrote:


I guess I would expect a good result to satisfy one of these three
cases:
* month  0 and 0 = day  30
* month  0 and -30  day = 0
* month = 0 and -30  day  30
If you believe that then 1 month -95 days should justify to
-2 months -5 days.



How would you expect justify_hours to behave?  I extrapolate from your rules 
above that:




  * month  0 and 0 = day  30 and 0 = hours  24
  * month  0 and -30  day = 0 and -24  hours = 0
  * month = 0 and -30  day = 0 and -24  hours = 0
  * month = 0 and 0 = day  30 and 0 = hours  24



Hmmm ... I think it would be better if the two functions were
independent, if possible.  Your spec above implies that justify_hours
implicitly does justify_days as well, which seems a bit restrictive.

Furthermore, justify_hours should only assume that 1 day == 24 hours,
which while broken by DST is still a lot solider assumption than
justify_days' 1 month == 30 days.  I can well believe that a lot of
people only want to make the first assumption.

So I'm inclined to think that justify_hours is responsible for reducing
the seconds part to less-than-24-hours and pushing any overflow into the
days part (but not touching months), while justify_days is responsible
for reducing the days part to less-than-30-days and pushing any overflow
into the months part (but not touching seconds).  If you want both you
apply both functions, probably in that order.  (I wonder if there are
any cases where applying justify_days before justify_hours would be
useful.  Offhand I can't see one ...)

regards, tom lane


I did not mean to imply that the two functions would be calling each other. 
Rather, I thought that a user should get sensible results if they called them 
both together.  The current code (without the patch) behaves as follows:


  select justify_days(justify_hours('1 month 95 days -36:00:00'::interval));
justify_days
  -
   4 mons 4 days -12:00:00

which seems inconsistent with the intent of the patch.  Shouldn't the patched 
version return '4 mons 3 days 12:00:00' instead?


mark

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

Re: [HACKERS] [SQL] Interval subtracting

[Tom Lane]

Mark Dilger [EMAIL PROTECTED] writes:
 The current code (without the patch) behaves as follows:

select justify_days(justify_hours('1 month 95 days -36:00:00'::interval));
  justify_days
-
 4 mons 4 days -12:00:00

So?  If we liked the current behavior we wouldn't be discussing a patch...

My thought is that justify_hours should reduce that input to
'1 month 93 days 12:00:00' and then justify_days would produce
'4 months 3 days 12:00:00'.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [SQL] Interval subtracting

[Mark Dilger]

Tom Lane wrote:

Mark Dilger [EMAIL PROTECTED] writes:


The current code (without the patch) behaves as follows:




  select justify_days(justify_hours('1 month 95 days -36:00:00'::interval));
justify_days
  -
   4 mons 4 days -12:00:00



So?  If we liked the current behavior we wouldn't be discussing a patch...

My thought is that justify_hours should reduce that input to
'1 month 93 days 12:00:00' and then justify_days would produce
'4 months 3 days 12:00:00'.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster



Your proposal is that justify_hours borrows 24 hours from the days column in 
order to bring the -12 hours up to a positive 12 hours.  Should it only do that 
if the days column is a positive number?  What if it is negative?


I think we all agree on the following but nobody is explicitly saying so:

  select justify_days(justify_hours('2 days -12:00:00'::interval))
justify_days
  -
   1 day 12:00:00

  select justify_days(justify_hours('-2 days -12:00:00'::interval))
justify_days
  -
   -2 days -12:00:00

Am I correct that the second case should still have negative hours?  If so, then 
justify_hours(...) needs to examine the sign of the days and months portion of 
the interval while performing its work.


mark

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

Re: [HACKERS] [SQL] Interval subtracting

[Bruce Momjian]

Mark Dilger wrote:
 Your proposal is that justify_hours borrows 24 hours from the days column in 
 order to bring the -12 hours up to a positive 12 hours.  Should it only do 
 that 
 if the days column is a positive number?  What if it is negative?
 
 I think we all agree on the following but nobody is explicitly saying so:
 
select justify_days(justify_hours('2 days -12:00:00'::interval))
  justify_days
-
 1 day 12:00:00

Right.

select justify_days(justify_hours('-2 days -12:00:00'::interval))
  justify_days
-
 -2 days -12:00:00

Right, unchanged.

 Am I correct that the second case should still have negative hours?  If so, 
 then 
 justify_hours(...) needs to examine the sign of the days and months portion 
 of 
 the interval while performing its work.

Yes, it would need to look at both, and this opens a new problem. 
Imagine this:

'1 mons -2 days -12:00:00'

Which sign do we head to for this?  For justify_hours, if we don't look
at the months it remains unchange, but calling justify_days we get:

'28 days -12:00:00'

which is wrong (negative and positive).  Now if we knew justify_days was
going to be called we would have had justify_hours return '-3 days
12:00:00' so the final result after calling justify_days would be '27
days 12:00:00'.

My head hurts.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 1: 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: [HACKERS] [SQL] Interval subtracting

[Mark Dilger]

Bruce Momjian wrote:

Mark Dilger wrote:

Your proposal is that justify_hours borrows 24 hours from the days column in 
order to bring the -12 hours up to a positive 12 hours.  Should it only do that 
if the days column is a positive number?  What if it is negative?


I think we all agree on the following but nobody is explicitly saying so:

  select justify_days(justify_hours('2 days -12:00:00'::interval))
justify_days
  -
   1 day 12:00:00



Right.



  select justify_days(justify_hours('-2 days -12:00:00'::interval))
justify_days
  -
   -2 days -12:00:00



Right, unchanged.


Am I correct that the second case should still have negative hours?  If so, then 
justify_hours(...) needs to examine the sign of the days and months portion of 
the interval while performing its work.



Yes, it would need to look at both, and this opens a new problem. 
Imagine this:


'1 mons -2 days -12:00:00'

Which sign do we head to for this?  For justify_hours, if we don't look
at the months it remains unchange, but calling justify_days we get:

'28 days -12:00:00'

which is wrong (negative and positive).  Now if we knew justify_days was
going to be called we would have had justify_hours return '-3 days
12:00:00' so the final result after calling justify_days would be '27
days 12:00:00'.

My head hurts.



I am just now testing a patch which handles all of this.  justify_hours *makes 
no change to months or days*, but it examines them both to determine if the 
total amount of time represented there is positive or negative.  It then makes 
sure that the hours have the same sign.


Of course, if you never get around to calling justify_days, you'll have mixed 
signs in your results.  But if days and months have different signs to begin 
with, then that isn't the fault of justify_hours, so we really haven't done any 
harm.


I'll be posting the patch shortly.

mark

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

[HACKERS] Status of INS/UPD/DEL RETURNING?

[Jonah H. Harris]

Hey guys,

What's the status of the current INSERT/UPDATE/DELETE RETURNING patch? Is it ready to go or does it need to be cleaned up?
-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324

Re: [HACKERS] Status of INS/UPD/DEL RETURNING?

[Bruce Momjian]

Jonah H. Harris wrote:
 Hey guys,
 
 What's the status of the current INSERT/UPDATE/DELETE RETURNING patch?  Is
 it ready to go or does it need to be cleaned up?

Uh, I don't remember seeing any patch like that.  Where is it?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Status of INS/UPD/DEL RETURNING?

[Jonah H. Harris]

http://candle.pha.pa.us/mhonarc/patches_hold/msg00014.html
On 3/1/06, Bruce Momjian pgman@candle.pha.pa.us wrote:
Jonah H. Harris wrote: Hey guys, What's the status of the current INSERT/UPDATE/DELETE RETURNING patch?Is it ready to go or does it need to be cleaned up?Uh, I don't remember seeing any patch like that.Where is it?
--Bruce Momjian http://candle.pha.pa.usSRA OSS, Inc. http://www.sraoss.com+ If your life is a hard drive, Christ can be your backup. +
-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324

Re: [HACKERS] Status of INS/UPD/DEL RETURNING?

[Gavin Sherry]

On Wed, 1 Mar 2006, Bruce Momjian wrote:

 Jonah H. Harris wrote:
  Hey guys,
 
  What's the status of the current INSERT/UPDATE/DELETE RETURNING patch?  Is
  it ready to go or does it need to be cleaned up?

 Uh, I don't remember seeing any patch like that.  Where is it?

Omar Kilani sent in a patch before 8.1 FF derived from (??) some playing
around I had done. It still needs work.

Thanks,

Gavin

---(end of broadcast)---
TIP 1: 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: [HACKERS] Status of INS/UPD/DEL RETURNING?

[Jonah H. Harris]

OK... I guess I'll go through the archives and see what Tom et al's
comments were and work from there. I tried to contact Omar a
couple times via email and got no response.

Thanks!On 3/1/06, Gavin Sherry [EMAIL PROTECTED] wrote:
On Wed, 1 Mar 2006, Bruce Momjian wrote: Jonah H. Harris wrote:  Hey guys,   What's the status of the current INSERT/UPDATE/DELETE RETURNING patch?Is  it ready to go or does it need to be cleaned up?
 Uh, I don't remember seeing any patch like that.Where is it?Omar Kilani sent in a patch before 8.1 FF derived from (??) some playingaround I had done. It still needs work.Thanks,
Gavin-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324

Re: [HACKERS] Status of INS/UPD/DEL RETURNING?

[Bruce Momjian]

Gavin Sherry wrote:
 On Wed, 1 Mar 2006, Bruce Momjian wrote:
 
  Jonah H. Harris wrote:
   Hey guys,
  
   What's the status of the current INSERT/UPDATE/DELETE RETURNING patch?  Is
   it ready to go or does it need to be cleaned up?
 
  Uh, I don't remember seeing any patch like that.  Where is it?
 
 Omar Kilani sent in a patch before 8.1 FF derived from (??) some playing
 around I had done. It still needs work.

OK, thanks.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] [SQL] Interval subtracting

[Mark Dilger]

A new patch is attached.  Please note the regression differences.

mark



Index: src/backend/utils/adt/timestamp.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.160
diff --context=5 -r1.160 timestamp.c
*** src/backend/utils/adt/timestamp.c   22 Nov 2005 22:30:33 -  1.160
--- src/backend/utils/adt/timestamp.c   1 Mar 2006 22:29:32 -
***
*** 2003,2013 
TMODULO(result-time, wholeday, USECS_PER_DAY);
  #else
TMODULO(result-time, wholeday, (double) SECS_PER_DAY);
  #endif
result-day += wholeday;/* could overflow... */
! 
PG_RETURN_INTERVAL_P(result);
  }
  
  /*
   *interval_justify_days()
--- 2003,2024 
TMODULO(result-time, wholeday, USECS_PER_DAY);
  #else
TMODULO(result-time, wholeday, (double) SECS_PER_DAY);
  #endif
result-day += wholeday;/* could overflow... */
!   if ((result-time  0) 
!((result-month = 0  result-day = 0) ||
! (result-month  0  result-day  0  (-1.0 * 
(double)result-day)/((double)result-month)  ((double)DAYS_PER_MONTH)) ||
! (result-month  0  result-day  0  
((double)result-day)/(-1.0 * (double)result-month)  
((double)DAYS_PER_MONTH
!   {
! #ifdef HAVE_INT64_TIMESTAMP
!   result-time += USECS_PER_DAY;
! #else
!   result-time += (double) SECS_PER_DAY;
! #endif
!   result-day--;
!   }
PG_RETURN_INTERVAL_P(result);
  }
  
  /*
   *interval_justify_days()
***
*** 2028,2037 
--- 2039,2053 
result-time = span-time;
  
wholemonth = result-day / DAYS_PER_MONTH;
result-day -= wholemonth * DAYS_PER_MONTH;
result-month += wholemonth;
+   if (result-day  0  result-month  0)
+   {
+   result-day += DAYS_PER_MONTH;
+   result-month--;
+   }
  
PG_RETURN_INTERVAL_P(result);
  }
  
  /* timestamp_pl_interval()
*** ./expected/timestamp.outSat Jun 25 20:04:18 2005
--- ./results/timestamp.out Wed Mar  1 14:26:33 2006
***
*** 488,494 
  | @ 306 days 6 hours 27 mins 59 secs ago
  | @ 2 days 6 hours 27 mins 59 secs ago
  | @ 1 day 6 hours 27 mins 59 secs ago
! | @ 6 hours 27 mins 59 secs ago
  | @ 57 days 17 hours 32 mins 1 sec
  | @ 58 days 17 hours 32 mins 1 sec
  | @ 362 days 17 hours 32 mins 1 sec
--- 488,494 
  | @ 306 days 6 hours 27 mins 59 secs ago
  | @ 2 days 6 hours 27 mins 59 secs ago
  | @ 1 day 6 hours 27 mins 59 secs ago
! | @ 1 day -17 hours -32 mins -1 secs ago
  | @ 57 days 17 hours 32 mins 1 sec
  | @ 58 days 17 hours 32 mins 1 sec
  | @ 362 days 17 hours 32 mins 1 sec
***
*** 557,563 
  | @ 306 days 6 hours 27 mins 59 secs ago
  | @ 2 days 6 hours 27 mins 59 secs ago
  | @ 1 day 6 hours 27 mins 59 secs ago
! | @ 6 hours 27 mins 59 secs ago
  | @ 57 days 17 hours 32 mins 1 sec
  | @ 58 days 17 hours 32 mins 1 sec
  | @ 362 days 17 hours 32 mins 1 sec
--- 557,563 
  | @ 306 days 6 hours 27 mins 59 secs ago
  | @ 2 days 6 hours 27 mins 59 secs ago
  | @ 1 day 6 hours 27 mins 59 secs ago
! | @ 1 day -17 hours -32 mins -1 secs ago
  | @ 57 days 17 hours 32 mins 1 sec
  | @ 58 days 17 hours 32 mins 1 sec
  | @ 362 days 17 hours 32 mins 1 sec

==

*** ./expected/timestamptz.out  Sat Jun 25 20:04:18 2005
--- ./results/timestamptz.out   Wed Mar  1 14:26:34 2006
***
*** 483,489 
  | @ 306 days 6 hours 27 mins 59 secs ago
  | @ 2 days 6 hours 27 mins 59 secs ago
  | @ 1 day 6 hours 27 mins 59 secs ago
! | @ 6 hours 27 mins 59 secs ago
  | @ 57 days 17 hours 32 mins 1 sec
  | @ 58 days 17 hours 32 mins 1 sec
  | @ 362 days 17 hours 32 mins 1 sec
--- 483,489 
  | @ 306 days 6 hours 27 mins 59 secs ago
  | @ 2 days 6 hours 27 mins 59 secs ago
  | @ 1 day 6 hours 27 mins 59 secs ago
! | @ 1 day -17 hours -32 mins -1 secs ago
  | @ 57 days 17 hours 32 mins 1 sec
  | @ 58 days 17 hours 32 mins 1 sec
  | @ 362 days 17 hours 32 mins 1 sec
***
*** 551,557 
  | @ 306 days 6 hours 27 mins 59 secs ago
  | @ 2 days 6 hours 27 mins 59 secs ago
  | @ 1 day 6 hours 27 mins 59 secs ago
! | @ 6 hours 27 mins 59 secs ago
  | @ 57 days 17 hours 32 mins 1 sec
  | @ 58 days 17 hours 32 mins 1 sec
  | @ 362 days 17 hours 32 mins 1 sec
--- 551,557 
  | @ 306 days 6 hours 27 mins 59 secs ago
  | @ 2 days 6 hours 27 mins 59 secs ago
  | @ 1 day 6 hours 27 mins 59 secs ago
! | @ 1 day -17 hours -32 mins -1 secs ago
  | @ 57 days 17 hours 32 mins 1 sec
  | @ 58 days 17 hours 32 mins 1 sec
  | @ 362 days 17 hours 32 mins 1 sec

Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....

[Mark Kirkwood]

Bruce Momjian wrote:

Mark Kirkwood wrote:


What if we add an option to initdb to allow the user to specify the name
and location of the postgresql.conf file?


That is certainly a way to approach it, I see the tough bit being the 
parsing of postgresql.conf to figure out which parts of the global 
included file to ignore (i.e the stuff for the *other* clusters).


Would this work for the situation where you have older clusters on the 
box (versions that don't understand 'include')?


Additionally this would need to tackle start|stop etc for all clusters...



I guess I am thinking we should move in a direction where all the
postgresql.conf files can be put in a single directory and pg_ctl would
know how to process multiple config files, 


Ok - that certainly makes a lot of sense. I do see a need to be able to 
handle older versions tho (I'm guessing that this could probably be made 
to work as long as a *newer* pg_ctl parsing the config files). At first 
sight this looks more complicated to implement (this not necessarily 
being a major objection in this audience :-))


 rather than create a central

file with conflicts with postgresql.conf.



With respect to this point, the minimal proposal is a register of 
instance binary homes and data directories (plus a name/alias to identify):


ALIAS PGHOME PGDATA

I don't believe this conflicts with any postgresql.conf - it is merely a 
'signpost' to where they are. (Now Mark W and myself were suggesting 
having PORT and may OPTS there too, but see prev mail about that - I'm 
ok about losing these).


Cheers

Mark

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

  http://archives.postgresql.org

Re: [HACKERS] [SQL] Interval subtracting

[Tom Lane]

Bruce Momjian pgman@candle.pha.pa.us writes:
 Imagine this:

   '1 mons -2 days -12:00:00'

 Which sign do we head to for this?  For justify_hours, if we don't look
 at the months it remains unchange, but calling justify_days we get:

   '28 days -12:00:00'

 which is wrong (negative and positive).

Ugh, that's not good.

Based on that, I guess I have to change my vote: justify_hours should
still not look at the month (because it shouldn't use the month=30days
assumption), but justify_days should be changed to be effectively a
combination of both functions --- that is, it should fix all three
fields using both the 30days and the 24hours assumptions.  Then it could
guarantee that all come out with the same sign.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] [SQL] Interval subtracting

[Mark Dilger]

Tom Lane wrote:
 Mark Dilger [EMAIL PROTECTED] writes:

Am I correct that the second case should still have negative hours?


 Yes...


If so, then justify_hours(...) needs to examine the sign of the days
and months portion of the interval while performing its work.


 No, it should ignore the months part completely, IMHO.  You are just
 confusing matters by using both functions in your examples, as then
 it's not clear which does what.

regards, tom lane

I like the idea that a person has some justify-path by which they can get all 
the signs to match.  With the patch that I just posted, this is accomplished as 
follows:


  justify_days(justify_hours(...))

Regardless of the particular weirdness of the signs in the original interval. 
But the patch also leaves open the possibility that you don't want the hours 
touched, perhaps because you're dealing with a daylight savings time period and 
can't accept the concept of a 24-hour day.  In that case:


  justify_days(...)

will get the sign on the months and days to match each other, though perhaps not 
match the hours.  In the event that you want to justify the hours, but can't 
accept having the days justified (because you have a non-30 day month), then you 
can call:


  justify_hours(...)

and get the sign on the hours portion to match the overall intent of the 
interval (positive or negative) without being forced to actually change the way 
the days and months are being represented.


This overall design seems more flexible than Tom's recent post in which he 
stated that justify_days should call justify_hours internally.  I tend not to 
agree.  However, it wouldn't hurt to have a justify_interval(...) function which 
does justify both in one shot.


mark

---(end of broadcast)---
TIP 1: 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: [HACKERS] [SQL] Interval subtracting

[Bruce Momjian]

Tom Lane wrote:
 Bruce Momjian pgman@candle.pha.pa.us writes:
  Imagine this:
 
  '1 mons -2 days -12:00:00'
 
  Which sign do we head to for this?  For justify_hours, if we don't look
  at the months it remains unchange, but calling justify_days we get:
 
  '28 days -12:00:00'
 
  which is wrong (negative and positive).
 
 Ugh, that's not good.
 
 Based on that, I guess I have to change my vote: justify_hours should
 still not look at the month (because it shouldn't use the month=30days
 assumption), but justify_days should be changed to be effectively a
 combination of both functions --- that is, it should fix all three
 fields using both the 30days and the 24hours assumptions.  Then it could
 guarantee that all come out with the same sign.

If we do that, we should just call it justify_interval().  I am thinking
this is the direction to go, and for people who want more control they
use the justify_hours and justify_days, and those are left unchanged.

Should justify_days() look at hours only if the day and hours signs
differ?  And perhaps only if the hours is between -24 and 0.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [SQL] Interval subtracting

[Mark Dilger]

Bruce Momjian wrote:

If we do that, we should just call it justify_interval().  I am thinking
this is the direction to go, and for people who want more control they
use the justify_hours and justify_days, and those are left unchanged.


I agree.  Let's leave the existing functions alone.  I can roll-up the changes 
made so far into a new function as Bruce suggests.


mark

---(end of broadcast)---
TIP 1: 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: [HACKERS] [SQL] Interval subtracting

[Tom Lane]

Bruce Momjian pgman@candle.pha.pa.us writes:
 Tom Lane wrote:
 Based on that, I guess I have to change my vote: justify_hours should
 still not look at the month (because it shouldn't use the month=30days
 assumption), but justify_days should be changed to be effectively a
 combination of both functions --- that is, it should fix all three
 fields using both the 30days and the 24hours assumptions.  Then it could
 guarantee that all come out with the same sign.

 If we do that, we should just call it justify_interval().  I am thinking
 this is the direction to go, and for people who want more control they
 use the justify_hours and justify_days, and those are left unchanged.

Well, the question is whether justify_days has a sane definition that is
different from this.  Based on your example, I'm not seeing one.

regards, tom lane

---(end of broadcast)---
TIP 1: 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: [HACKERS] [SQL] Interval subtracting

[Mark Dilger]

Tom Lane wrote:

Well, the question is whether justify_days has a sane definition that is
different from this.  Based on your example, I'm not seeing one.


Backwards compatibility is probably more important than sanity.  Let's just 
deprecate the existing functions and recommend that people use 
justify_interval(...).  By not changing the existing functions we can avoid a 
certain amount of hell.


mark

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] [SQL] Interval subtracting

[Bruce Momjian]

Tom Lane wrote:
 Bruce Momjian pgman@candle.pha.pa.us writes:
  Tom Lane wrote:
  Based on that, I guess I have to change my vote: justify_hours should
  still not look at the month (because it shouldn't use the month=30days
  assumption), but justify_days should be changed to be effectively a
  combination of both functions --- that is, it should fix all three
  fields using both the 30days and the 24hours assumptions.  Then it could
  guarantee that all come out with the same sign.
 
  If we do that, we should just call it justify_interval().  I am thinking
  this is the direction to go, and for people who want more control they
  use the justify_hours and justify_days, and those are left unchanged.
 
 Well, the question is whether justify_days has a sane definition that is
 different from this.  Based on your example, I'm not seeing one.

Uh, justify days only deals with days -- months conversions.  There is
no processing for hours.  I don't understand your comment.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [SQL] Interval subtracting

[Tom Lane]

Bruce Momjian pgman@candle.pha.pa.us writes:
 Well, the question is whether justify_days has a sane definition that is
 different from this.  Based on your example, I'm not seeing one.

 Uh, justify days only deals with days -- months conversions.  There is
 no processing for hours.  I don't understand your comment.

So it won't guarantee that hours has a consistent sign.  If you're OK
with that, then that's fine, let's make justify_days work that way and
then provide a justify_interval that processes all three fields.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [SQL] Interval subtracting

[Tom Lane]

Mark Dilger [EMAIL PROTECTED] writes:
 Bruce Momjian wrote:
 If we do that, we should just call it justify_interval().  I am thinking
 this is the direction to go, and for people who want more control they
 use the justify_hours and justify_days, and those are left unchanged.

 I agree.  Let's leave the existing functions alone.

No, we still need to fix them to not leave a large negative value in
place for seconds or days (respectively).  The current coding is
unquestionably inadequate.

regards, tom lane

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

Re: [HACKERS] [SQL] Interval subtracting

[Tom Lane]

Mark Dilger [EMAIL PROTECTED] writes:
 This overall design seems more flexible than Tom's recent post in which he 
 stated that justify_days should call justify_hours internally.

AFAIR I said the exact opposite.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] [SQL] Interval subtracting

[Bruce Momjian]

Mark Dilger wrote:
 Tom Lane wrote:
  Well, the question is whether justify_days has a sane definition that is
  different from this.  Based on your example, I'm not seeing one.
 
 Backwards compatibility is probably more important than sanity.  Let's just 
 deprecate the existing functions and recommend that people use 
 justify_interval(...).  By not changing the existing functions we can avoid a 
 certain amount of hell.

Those functions are new in 8.1 so I do think we can improve them in 8.2
if we agree.  Tom's idea of:

   * month  0 and 0 = day  30
   * month  0 and -30  day = 0
   * month = 0 and -30  day  30

seems a good change for 8.2, and the same for justify_hours(). The
question is whether justify_days should also adjust hours I think is the
issue, and the reason for a justify_interval() function.  Even if we had
people do:

justify_hours(justify_days(justify_hours()))

I don't think that would do what we want in all cases.  Consider '1 mon
-1 hour'.  That should be '29 days 23 hours' but neither existing
function, even if modified, will allow us to return that.  Only
something like justify_interval() could do it.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] [SQL] Interval subtracting

[Mark Dilger]

Tom Lane wrote:

Mark Dilger [EMAIL PROTECTED] writes:

This overall design seems more flexible than Tom's recent post in which he 
stated that justify_days should call justify_hours internally.



AFAIR I said the exact opposite.

regards, tom lane


Tom Lane also wrote:

assumption), but justify_days should be changed to be effectively a
combination of both functions --- that is, it should fix all three
fields using both the 30days and the 24hours assumptions.  Then it could
guarantee that all come out with the same sign.



How is changing justify days so that it touches the hours field different from 
having justify_days call justify_hours?


mark

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

Re: [HACKERS] [SQL] Interval subtracting

[Mark Dilger]

Bruce Momjian wrote:

Even if we had people do:

justify_hours(justify_days(justify_hours()))

I don't think that would do what we want in all cases.  Consider '1 mon
-1 hour'.  That should be '29 days 23 hours' but neither existing
function, even if modified, will allow us to return that.  Only
something like justify_interval() could do it.



justify_days(justify_hours(...)) fixes *everything* in the most recently 
submitted patch, regardless of the convoluted case you invent.  There is no data 
for which it won't work.  There is no need for justify_interval(...), except as 
syntactic sugar.


Since the backward compatibility argument didn't convince you, then we should go 
with the existing patch as-is.  Whether we introduce the new function 
justify_interval(...) could be treated as a separate question, though I don't 
mind putting that in the patch and resubmitting.


mark

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] [SQL] Interval subtracting

[Tom Lane]

Mark Dilger [EMAIL PROTECTED] writes:
 justify_days(justify_hours(...)) fixes *everything* in the most recently 
 submitted patch, regardless of the convoluted case you invent.  There is no 
 data 
 for which it won't work.

If so, one function or the other is cheating.  Per discussion,
justify_hours must never touch months, and I don't believe that
justify_days should touch seconds either.  The proposed justify_interval
function should have a result different from successive application
of the two existing functions, because it will ensure that all three
fields have similar signs whereas separate use of the two functions
can't promise that in corner cases.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [SQL] Interval subtracting

[Mark Dilger]

Tom Lane wrote:

Mark Dilger [EMAIL PROTECTED] writes:

justify_days(justify_hours(...)) fixes *everything* in the most recently 
submitted patch, regardless of the convoluted case you invent.  There is no data 
for which it won't work.



If so, one function or the other is cheating.  Per discussion,
justify_hours must never touch months, and I don't believe that
justify_days should touch seconds either.  The proposed justify_interval
function should have a result different from successive application
of the two existing functions, because it will ensure that all three
fields have similar signs whereas separate use of the two functions
can't promise that in corner cases.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster



That depends what you mean by cheating.  The justify_hours function looks to see 
what answer justify_days would give, but does not actually change the data.  I 
described this all earlier and I still don't see why there is anything wrong 
with it.


mark

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

  http://archives.postgresql.org

Re: [HACKERS] [SQL] Interval subtracting

[Tom Lane]

Mark Dilger [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 If so, one function or the other is cheating.

 That depends what you mean by cheating.  The justify_hours function
 looks to see what answer justify_days would give, but does not
 actually change the data.  I described this all earlier and I still
 don't see why there is anything wrong with it.

The problem is that you can't determine what answer justify_days would
give without using the assumption 1 month == 30 days, which is an
assumption that justify_hours must not depend on.

regards, tom lane

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

Re: [HACKERS] [SQL] Interval subtracting

[Mark Dilger]

Tom Lane wrote:

Mark Dilger [EMAIL PROTECTED] writes:


Tom Lane wrote:


If so, one function or the other is cheating.




That depends what you mean by cheating.  The justify_hours function
looks to see what answer justify_days would give, but does not
actually change the data.  I described this all earlier and I still
don't see why there is anything wrong with it.



The problem is that you can't determine what answer justify_days would
give without using the assumption 1 month == 30 days, which is an
assumption that justify_hours must not depend on.


Ahhh.  So the fact that justify_days already makes the 1 month == 30 days 
assumption is ok in that function but can't be propagated to justify_hours.



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] ipcclean in 8.1 broken?

[Christopher Kings-Lynne]

if [ $USER = 'root' -o $LOGNAME = 'root' ]

Always fails because even tho $USER is set to 'pgsql' when su'ed,
$LOGNAME is still root.

This is on FreeBSD 4.9


It seems to work on Linux; apparently there are different behaviors of su.  Do 
you have a suggestion for resolving this?


Well all I did to fix it on FreeBSD was to remove the '-o $LOGNAME = 
'root'' bit...


Chris



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

  http://archives.postgresql.org

Re: [HACKERS] ipcclean in 8.1 broken?

[Christopher Kings-Lynne]

I wonder if there could be a potential problem with using this approach
-
checking on $USER == root.

Although it is a common practice, I think a superuser does not have to
be root.


Yes, like the 'toor' account in FreeBSD... (disabled by default though)

Chris


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

  http://archives.postgresql.org

Re: [HACKERS] ipcclean in 8.1 broken?

[Mark Kirkwood]

Christopher Kings-Lynne wrote:

I wonder if there could be a potential problem with using this approach
-
checking on $USER == root.

Although it is a common practice, I think a superuser does not have to
be root.



Yes, like the 'toor' account in FreeBSD... (disabled by default though)



Might be better to check if uid == 0, however there are some traps here 
too as the most convenient methd ('id -u') is not support everywhere 
(e.g Solaris 8). I think I used awk or sed on the plain old 'id' output 
last time something like this came up.


Cheers

Mark

---(end of broadcast)---
TIP 1: 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

[HACKERS] display processing time?

[John]

I have a question about how to display query time of  postgres.  I found 
this

postgres [ -A { 0 | 1 } ] [ -B buffers ] [ -c name=value ] [ -d
debug-level ]
  [ -D datadir ] [ -e ] [ -E ] [ -f { s | i | n | m | h } ] [ -F ]
  [ -i ] [ -L ] [ -N ] [ -o file-name ] [ -O ] [ -P ]
  [ -s | -t { pa | pl | ex } ] [ -S sort_mem ] [ -W num ] database

adding -s will print the statistis and time. But I have no idea how to call 
this using postmaster -o option. Anyone give me a hint? Thanks.


-John 


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] display processing time?

[Michael Fuhr]

On Wed, Mar 01, 2006 at 10:13:02PM -0600, John wrote:
 adding -s will print the statistis and time. But I have no idea how to call 
 this using postmaster -o option. Anyone give me a hint? Thanks.

postmaster -o -s [ other options ]

Or you could enable log_statement_stats in postgresql.conf.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Uninstall script errors

[Tom Lane]

Michael Fuhr [EMAIL PROTECTED] writes:
 ...  I started to work on a patch but I wasn't sure how
 to handle the chicken-and-egg situation of dropping a type and its
 I/O functions.  Is there any way to do that other than DROP TYPE
 CASCADE?  Should the uninstall scripts be doing that?

DROP TYPE CASCADE is probably reasonable; that's the way pg_dump
handles the problem, anyway.

regards, tom lane

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

Re: [HACKERS] Automatic free space map filling

[Tom Lane]

Alvaro Herrera [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 That has nothing to do with it, because the space isn't actually free
 for re-use until vacuum deletes the tuple.

 I think the idea is a different free space map of sorts, whereby a
 transaction that obsoletes a tuple puts its block number in that map.  A
 transaction that inserts a new tuple goes to the FSM.  If nothing is
 found, it then goes to the new map.  A block returned from that map is
 then scanned and any tuple that's no longer visible for anyone is
 reused.

I thought we had sufficiently destroyed that reuse a tuple meme
yesterday.  You can't do that: there are too many aspects of the system
design that are predicated on the assumption that dead tuples do not
come back to life.  You have to do the full vacuuming bit (index entry
removal, super-exclusive page locking, etc) before you can remove a dead
tuple.

 Essentially, we would be folding the find
 dead tuples and compress page logic, which is currently in vacuum, back
 to insert.  IMHO this is unacceptable from a performance PoV.

That's the other problem: it's not apparent why pushing work from vacuum
back into foreground processing is a good idea.  Especially not why
retail vacuuming of individual tuples will be better than wholesale.

regards, tom lane

---(end of broadcast)---
TIP 1: 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: [HACKERS] [SQL] Interval subtracting

[Hannu Krosing]

Ühel kenal päeval, K, 2006-03-01 kell 14:36, kirjutas Scott Marlowe:

  But it isn't '-2 months, -1 day'.  I think what you are saying is what I
  am saying, that we should make the signs consistent.
 
 Pretty much.  It just seems wrong to have different signs in what is
 essentially a single unit.
 
 We don't say 42 degrees, -12 minutes when measuring arc, do we?  Then
 again, maybe some folks do.  It just seems wrong to me.

But we do say both quarter past three (3 hours 15 min) and quarter to
four (4 hours -15 min) when talking about time.

---
Hannu



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Automatic free space map filling

[Jim C. Nasby]

On Thu, Mar 02, 2006 at 01:01:21AM -0500, Tom Lane wrote:
  Essentially, we would be folding the find
  dead tuples and compress page logic, which is currently in vacuum, back
  to insert.  IMHO this is unacceptable from a performance PoV.
 
 That's the other problem: it's not apparent why pushing work from vacuum
 back into foreground processing is a good idea.  Especially not why
 retail vacuuming of individual tuples will be better than wholesale.

The problem is that even with vacuum_cost_delay, vacuum is still very
slow and problematic in situations such as a large tables in a heavy
transaction environment. Anything that could help reduce the need for
'traditional' vacuuming could well be a win.

Even so, I think the most productive path to pursue at this time is a
dead-space-map/known-clean-map. Either one is almost guaranteed to
provide benefits. Once we know what good they do we can move forward
from there with further improvements.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 1: 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: [HACKERS] [SQL] Interval subtracting

[Tino Wildenhain]

Hannu Krosing schrieb:
 Ühel kenal päeval, K, 2006-03-01 kell 14:36, kirjutas Scott Marlowe:
 
...
 But we do say both quarter past three (3 hours 15 min) and quarter to
 four (4 hours -15 min) when talking about time.
 
but luckily we dont write it ;)
Some people say (like ) this: quarter past 3, half past 3, three quartes
 past 3, 4. Which seems more logical. :-)

But saying would be a job for to_char, not for internal storage,
which should _always_ be canonical.

Regards
Tino

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Initdb on Windows 2003

[James_Hughes]

Thanks for the info, I shall indeed try this. 

One thing to mention is that I don't think the error occurs within
initdb, it seams to be postgres.exe that dbinit starts.   

Regards
James

-Original Message-
From: Andrew Dunstan [mailto:[EMAIL PROTECTED] 
Sent: 01 March 2006 16:26
To: Hughes, James
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Initdb on Windows 2003


(private email says leaving off --user=foo doesn't fix it ;-( )

Since you have apparently compiled your own, could you please try with
the latest stable initdb.c code for your release? That is version
1.99.2.2 for release 8.1 or 1.73.4.3 for release 8.0. They are
downloadable here: 
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/initdb/initdb.c

Maybe there's something odd about your setup - we have a WS2k3 machine
happily building and running on buildfarm: see
http://www.pgbuildfarm.org/cgi-bin/show_history.pl?nm=snakebr=REL8_1_ST
ABLE

If it still doesn't work, you might get some useful info from initdb
--debug

Thanks

andrew


[EMAIL PROTECTED] wrote:

Hi,

1. The command line passed is -D c:\data --user=McAfeePostgresUser 2.

McAfeePostgresUser which is a local machine user with limited 
privileges. Though this problem occurs with any user account you create

with non-administrator privileges.

Thanks
James

-Original Message-
From: Andrew Dunstan [mailto:[EMAIL PROTECTED]
Sent: 01 March 2006 13:27
To: Hughes, James
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Initdb on Windows 2003



1. please show the EXACT initdb command line used.

2. Which Windows user was actually running initdb?

cheers

andrew

  

Hello all,

Below is an email thread regarding a possible bug in PostgreSQL on 
Windows 2003. Any help or advice anyone can give on this would be much



  

appreciated.

All the best,
James Hughes

--- EMAIL THREAD FOLLOWS ---

[EMAIL PROTECTED] wrote:


Hi Bruce,

We are now seeing this issue on three machines, all of which are 
running Windows 2003. After some looking at the code and putting some
  


  

extra debug output (very little) all we have determined is that 
initdb.exe opens postgres.exe via pipes, and at some point within 
postgres.exe this error is generated.

If we knock out the check for the user being not being admin and run 
initdb.exe as an administrator all works as expected. Adding the user
  


  

to the 'Power Users' group also causes the Access Denined error! I 
don't believe that the access denined is anything to do with file 
access or memory (shared) access as I ran some tools to see what 
postgres.exe is trying to access.

Is there an easy way on Windows to step-through the PostgreSQL code 
as
  

it runs? Is there any other information I can provide which will help
  


  

you with this defect?

Any help getting to the root of this problem is much appericated.

All the best
James Hughes

-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]
Sent: 24 February 2006 18:54
To: Hughes, James
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #2268: initdb fails to initialize database 
cluster with error Access is denied


Strange.  It isn't a typical error we see, and the fact you see it on
  


  

two machines is even stranger.  My guess is that somehow the 
configuration on those two machines is the same and is causing the 
failure.

-
-
--
---

James Hughes wrote:
  

The following bug has been logged online:

Bug reference:  2268
Logged by:  James Hughes
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.1.3
Operating system:   Windows 2003 (Server)
Description:initdb fails to initialize database cluster


with
  

error
  

Access is denied
Details:

When we try and initialize a database cluster using initdb.exe we 
are getting the following output.

-- OUTPUT from dbinit.exe --
The files belonging to this database system will be owned by user


user1.
  

This user must also own the server process.

The database cluster will be initialized with locale English_United



  

Kingdom.1252.

creating directory c:/dataa ... ok
creating directory c:/dataa/global ... ok creating directory 
c:/dataa/pg_xlog ... ok creating directory 
c:/dataa/pg_xlog/archive_status ... ok creating directory 
c:/dataa/pg_clog ... ok creating directory c:/dataa/pg_subtrans ...
ok


creating directory c:/dataa/pg_twophase ... ok creating directory 
c:/dataa/pg_multixact/members ... ok creating directory 
c:/dataa/pg_multixact/offsets ... ok creating directory 
c:/dataa/base ... ok creating directory c:/dataa/base/1 ... ok 
creating directory c:/dataa/pg_tblspc ... ok selecting default 
max_connections ... Access


is denied.
Access is denied.
Access is denied.
Access is denied.
Access is denied.
Access is denied.
10
selecting default shared_buffers ... Access is denied.
Access is