[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. +






Ja

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 y

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 machi

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=snake&br=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 dat

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 the transaction for 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 
the server 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: 
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  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.   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 theJune-August 2006 timeframe.  It could be later, but I doubt it would beearlier.--  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-- 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] Automatic free space map filling

[Hannu Krosing]

Ühel kenal päeval, T, 2006-02-28 kell 19:47, kirjutas Alvaro Herrera:
> Hannu Krosing wrote:
> 
> > Due to current implementation of vacuum,
> > you have to abandon continuous vacuuming during vacuum of bigtable, but
> > i have written and submitted to "patches" list a patch which allows
> > vacuums not to block each other out, this is stalled due to Tom's
> > "unesyness" about its possible hidden effects, but it should be
> > available from "patches" list to anyone in distress :p
> 
> Do you use it in production?  Have you noticed any ill effects?

No, I don't run it in production at this time, as I solved the immediate
problem by splitting small and big tables to different databases and
having client applications rewritten accordingly.

I did run a parallel load (queries from log of real database, plus
parallel vacuums on tables) for some time and saw no ill effects there.

I will likely start using it in production on some databases during next
few months as new restructuring of databases brings back the case where
huge and tiny tables are in the same database.

--
Hannu



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

   http://archives.postgresql.org

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  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  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  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] pg_config, pg_service.conf, postgresql.conf ....

[Bruce Momjian]

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, rather than create a central
file with conflicts with postgresql.conf.

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

[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  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.us  SRA 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 min

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