[HACKERS] #ifdef NOT_USED

[Jaime Casanova]

Hi, i have found several #ifdef NOT_USED marked code... i guess this
is dead code... is safe to remove it? there is some reason you just
hide it and not remove the code?

-- 
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---(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] get_rel_* functions in lsyscache.c

[Jaime Casanova]

Hi, i have a doubt...

it seems to me that the get_rel_* functions in lsyscache do the same as doing 
heap_open(); 
Calling the appropiate macro Relation*
heap_close();

is there any difference between them? in wich situation is one better
than the other?

-- 
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---(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] [BUGS] BUG #1467: fe_connect doesn't handle EINTR right

[Tom Lane]

Bruce Momjian  writes:
> Though we have had no problem reports of this, there is confirmation
> that our code is incorrect.  Would someone develop a patch to fix this? 

s/there is confirmation/there is an entirely unsupported assertion/

I'd like to see an actual demonstration of trouble before we take
this seriously.

regards, tom lane

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

Re: [HACKERS] [PATCHES] regexp_replace

[Bruce Momjian]

Atsushi Ogawa wrote:
> I think that it is good to specify the flags by one character as well
> as Perl.
> 
> I propose the following specification:
> 
> regexp_replace(source text, pattern text, replacement text, [flags text])
> returns text
> 
> The flags can use the following values:
>  g: global (replace all)
>  i: ignore case
> 
> When the flags is not specified, case sensitive, replace the first
> instance only.

This seems good to me.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

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

Re: [HACKERS] [BUGS] BUG #1467: fe_connect doesn't handle EINTR right

[Bruce Momjian]

Though we have had no problem reports of this, there is confirmation
that our code is incorrect.  Would someone develop a patch to fix this? 

Thanks.

---

Florian Hars wrote:
> 
> The following bug has been logged online:
> 
> Bug reference:  1467
> Logged by:  Florian Hars
> Email address:  [EMAIL PROTECTED]
> PostgreSQL version: 8.0.1
> Operating system:   All
> Description:fe_connect doesn't handle EINTR right
> Details: 
> 
> The file pgsql/src/interfaces/libpq/fe-connect.c contains the code fragment
> 
> retry_connect:
>   if (connect(conn->sock, addr_cur->ai_addr,
> addr_cur->ai_addrlen) < 0)
>   {
>   if (SOCK_ERRNO == EINTR)
>   /* Interrupted system call - just try again */
>   goto retry_connect;
>   }
> 
> This is not in accordance with a strict legalistic reading of the POSIX
> spec, according to which connect is not restartable so that you have to use
> select or poll after connect returned with EINTR.
> 
> See
> http://www.eleves.ens.fr:8080/home/madore/computers/connect-intr.html
> for the ugly details, your code should work on Linux, but not on Solaris or
> (Free|Open)BSD.
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] [COMMITTERS] pgsql: Fix NUMERIC modulus to properly

[Bruce Momjian]

I don't think we can justify having NUMERIC division default to
truncation, especially since most division has non-zero decimal digits.

---

Paul Tillotson wrote:
> Bruce Momjian wrote:
> 
> >Tom Lane wrote:
> >  
> >
> >>Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
> >>
> >>
> No, I don't think so.  It doesn't seem to be something that enough
> people use to risk the change in behavior --- it might break something
> that was working.  But, if folks want it backported we can do it.  It is
> only a change to properly do modulus for numeric.
> 
> 
> >>>Well, from my point of view it's an absolute mathematical error - i'd 
> >>>backport it.  I can't see anyone relying on it :)
> >>>  
> >>>
> >>Doesn't this patch break the basic theorem that
> >>
> >>a = trunc(a / b) * b + (a mod b)
> >>
> >>?  If division rounds and mod doesn't, you've got pretty serious issues.
> >>
> >>
> >
> >Well, this is a good question.  In the equation above we assume '/' is
> >an integer division.  The problem with NUMERIC when used with zero-scale
> >operands is that the result is already _rounded_ to the nearest hole
> >number before it gets to trunc(), and that is why we used to get
> >negative modulus values.  I assume the big point is that we don't offer
> >any way for users to get a NUMERIC division without rounding.
> >
> >With integers, we always round down to the nearest whole number on
> >division;  float doesn't offer a modulus operator, and C doesn't support
> >it either.
> >
> >We round NUMERICs to the specific scale because we want to give the most
> >accurate value:
> >
> > test=> select 1000::numeric(24,0) /
> > 11::numeric(24,0);
> > ?column?
> > 
> >  9090909090909090909091
> >
> >The actual values is:
> >--
> >  9090909090909090909090.90
> >
> >But the problem is that the equation at the top assumes the division is
> >not rounded.  Should we supply a NUMERIC division operator that doesn't
> >round?  integer doesn't need it, and float doesn't have the accurate
> >precision needed for modulus operators.  The user could supply some
> >digits in the division:
> > 
> > test=> select 1000::numeric(30,6) /
> > 11::numeric(24,0);
> >?column?
> > ---
> >  9090909090909090909090.909091
> > (1 row)
> >
> >but there really is no _right_ value to prevent rounding (think
> >0.999).  A non-rounding NUMERIC division would require duplicating
> >numeric_div() but with a false for 'round', and adding operators.
> >
> >  
> >
> I would prefer that division didn't round, as with integers.  You can 
> always calculate your result to 1 more decimal place and then round, but 
> there is no way to unround a rounded result.
> 
> Tom had asked whether PG passed the regression tests if we change the 
> round_var() to a trunc_var() at the end of the function div_var().
> 
> It does not pass, but I think that is because the regression test is 
> expecting that division will round up.  (Curiously, the regression test 
> for "numeric" passes, but the regression test for aggregation--sum() I 
> think--is the one that fails.)  I have attached the diffs here if anyone 
> is interested.
> 
> Regards,
> Paul Tillotson
> 

> *** ./expected/aggregates.out Sun May 29 19:58:43 2005
> --- ./results/aggregates.out  Mon Jun  6 21:01:11 2005
> ***
> *** 10,16 
>   SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
>  avg_32
>   -
> !  32.6667
>   (1 row)
>   
>   -- In 7.1, avg(float4) is computed using float8 arithmetic.
> --- 10,16 
>   SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
>  avg_32
>   -
> !  32.
>   (1 row)
>   
>   -- In 7.1, avg(float4) is computed using float8 arithmetic.
> 
> ==
> 

> test boolean  ... ok
> test char ... ok
> test name ... ok
> test varchar  ... ok
> test text ... ok
> test int2 ... ok
> test int4 ... ok
> test int8 ... ok
> test oid  ... ok
> test float4   ... ok
> test float8   ... ok
> test bit  ... ok
> test numeric  ... ok
> test strings  ... ok
> test numerology   ... ok
> test point... ok
> test lseg ... ok
> test box  ... ok
> test path ... ok
> test polygon  ... ok
> test circle   ... ok
> test date ... ok
> test time ... ok
> test timetz   ... ok
> test timestamp 

Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[]

[Bruce Momjian]

Is this a TODO item?

---

Markus Bertheau ? wrote:
> ? ???, 06/06/2005 ? 08:58 -0700, Joe Conway ?:
> > Joe Conway wrote:
> > > Actually, consistent with my last post, I think array_upper() on a 
> > > zero-element array should return NULL. A zero-element array has a 
> > > defined lower bound, but its upper bound is not zero -- it is really 
> > > undefined.
> > 
> > Just to clarify my response, this is what I propose:
> > 
> > regression=# select array_upper('[2][1:]={{},{}}'::int[],1);
> >   array_upper
> > -
> > 2
> > (1 row)
> > 
> > regression=# select array_upper('[2][1:]={{},{}}'::int[],2) IS NULL;
> >   ?column?
> > --
> >   t
> > (1 row)
> 
> Hmm, this gets really complicated and inconsistent. Complicated means
> unusable. What about modifying the dimension syntax such that the second
> number means number of elements instead of upper bound? That particular
> problem would go away then, and array_upper('[0:0]={}'::int[]) can
> return the correct 0 then.
> 
> What I'm actually worrying about is that array_upper(array(select 1
> where false)) returns 0.
> 
> An option would be to drop the possibility to let the array start at
> another index than 0. I don't know why it was decided to do that in the
> first place. It seems a rather odd feature to me.
> 
> Markus
> -- 
> Markus Bertheau ? <[EMAIL PROTECTED]>
> 
> 
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
>   subscribe-nomail command to [EMAIL PROTECTED] so that your
>   message can get through to the mailing list cleanly
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] [COMMITTERS] pgsql: Fix NUMERIC modulus to properly truncate

[Bruce Momjian]

With no conclusion on this, I have added a TODO item:

* Add NUMERIC division operator that doesn't round?

  Currently NUMERIC _rounds_ the result to the specified precision.
  This means division can return a result that multiplied by the
  divisor is greater than the dividend, e.g. this returns a value > 10:

SELECT (10::numeric(2,0) / 6::numeric(2,0))::numeric(2,0) * 6;

  The positive modulus result returned by NUMERICs might be considered
  inaccurate, in one sense.


---

Bruce Momjian wrote:
> 
> Have we made any decision on whether the old/new NUMERIC %(mod) code was
> correct, and now to handle rounding?  Should we offer a non-rounding
> division operator for NUMERIC?
> 
> ---
> 
> Paul Tillotson wrote:
> > Bruce Momjian wrote:
> > 
> > >Tom Lane wrote:
> > >  
> > >
> > >>Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
> > >>
> > >>
> > No, I don't think so.  It doesn't seem to be something that enough
> > people use to risk the change in behavior --- it might break something
> > that was working.  But, if folks want it backported we can do it.  It is
> > only a change to properly do modulus for numeric.
> > 
> > 
> > >>>Well, from my point of view it's an absolute mathematical error - i'd 
> > >>>backport it.  I can't see anyone relying on it :)
> > >>>  
> > >>>
> > >>Doesn't this patch break the basic theorem that
> > >>
> > >>  a = trunc(a / b) * b + (a mod b)
> > >>
> > >>?  If division rounds and mod doesn't, you've got pretty serious issues.
> > >>
> > >>
> > >
> > >Well, this is a good question.  In the equation above we assume '/' is
> > >an integer division.  The problem with NUMERIC when used with zero-scale
> > >operands is that the result is already _rounded_ to the nearest hole
> > >number before it gets to trunc(), and that is why we used to get
> > >negative modulus values.  I assume the big point is that we don't offer
> > >any way for users to get a NUMERIC division without rounding.
> > >
> > >With integers, we always round down to the nearest whole number on
> > >division;  float doesn't offer a modulus operator, and C doesn't support
> > >it either.
> > >
> > >We round NUMERICs to the specific scale because we want to give the most
> > >accurate value:
> > >
> > >   test=> select 1000::numeric(24,0) /
> > >   11::numeric(24,0);
> > >   ?column?
> > >   
> > >9090909090909090909091
> > >
> > >The actual values is:
> > >--
> > >9090909090909090909090.90
> > >
> > >But the problem is that the equation at the top assumes the division is
> > >not rounded.  Should we supply a NUMERIC division operator that doesn't
> > >round?  integer doesn't need it, and float doesn't have the accurate
> > >precision needed for modulus operators.  The user could supply some
> > >digits in the division:
> > >   
> > >   test=> select 1000::numeric(30,6) /
> > >   11::numeric(24,0);
> > >  ?column?
> > >   ---
> > >9090909090909090909090.909091
> > >   (1 row)
> > >
> > >but there really is no _right_ value to prevent rounding (think
> > >0.999).  A non-rounding NUMERIC division would require duplicating
> > >numeric_div() but with a false for 'round', and adding operators.
> > >
> > >  
> > >
> > I would prefer that division didn't round, as with integers.  You can 
> > always calculate your result to 1 more decimal place and then round, but 
> > there is no way to unround a rounded result.
> > 
> > Tom had asked whether PG passed the regression tests if we change the 
> > round_var() to a trunc_var() at the end of the function div_var().
> > 
> > It does not pass, but I think that is because the regression test is 
> > expecting that division will round up.  (Curiously, the regression test 
> > for "numeric" passes, but the regression test for aggregation--sum() I 
> > think--is the one that fails.)  I have attached the diffs here if anyone 
> > is interested.
> > 
> > Regards,
> > Paul Tillotson
> > 
> 
> > *** ./expected/aggregates.out   Sun May 29 19:58:43 2005
> > --- ./results/aggregates.outMon Jun  6 21:01:11 2005
> > ***
> > *** 10,16 
> >   SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
> >  avg_32
> >   -
> > !  32.6667
> >   (1 row)
> >   
> >   -- In 7.1, avg(float4) is computed using float8 arithmetic.
> > --- 10,16 
> >   SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
> >  avg_32
> >   -
> > !  32.
> >   (1 row)
> >   
> >   -- In 7.1, avg(float4) is computed using float8 arithmetic.
> > 
> > ==
> > 
> 
> > test boolean

Re: [HACKERS] pg_terminate_backend idea

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian  writes:
> > I have been running some tests to try to see the lock table corruption
> > but I have been unable to reproduce the problem.
> 
> It's possible that what Rod was complaining of is fixed in CVS tip ---
> see discussion about RemoveFromWaitQueue() bug.  If so, it would have
> been a bug that could be seen in other circumstances too, but maybe
> SIGTERM made it more probable for some reason.

Was that backpatched to 8.0.X?  If not, I can test that branch of CVS
for the problem.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] pg_terminate_backend idea

[Tom Lane]

Bruce Momjian  writes:
> I have been running some tests to try to see the lock table corruption
> but I have been unable to reproduce the problem.

It's possible that what Rod was complaining of is fixed in CVS tip ---
see discussion about RemoveFromWaitQueue() bug.  If so, it would have
been a bug that could be seen in other circumstances too, but maybe
SIGTERM made it more probable for some reason.

regards, tom lane

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

Re: [HACKERS] pg_terminate_backend idea

[Bruce Momjian]

Tom Lane wrote:
> "Magnus Hagander" <[EMAIL PROTECTED]> writes:
> > Assuming we don't get such a case, and a chance to fix it, before 8.1
> > (while still hoping we will get it fixed properly, we can't be sure, can
> > we? If we were, it'd be fixed already). In this case, will you consider
> > such a kludgy solution as a temporary fix to resolve a problem that a
> > lot of users are having? And then plan to have it removed once sending
> > SIGTERM directly to a backend can be considered safe?
> 
> Kluges tend to become institutionalized, so my reaction is "no".  It's
> also worth pointing out that with so little understanding of the problem
> Rod is reporting, it's tough to make a convincing case that this kluge
> will avoid it.  SIGTERM exit *shouldn't* be leaving any corrupted
> locktable entries behind; it's not that much different from the normal
> case.  Until we find out what's going on, introducing still another exit
> path isn't really going to make me feel more comfortable, no matter how
> close it's alleged to be to the normal path.

I have been running some tests to try to see the lock table corruption
but I have been unable to reproduce the problem.  I have attached my
crude test scripts.  I would run the scripts and then open another
session as a different user and do UPDATE and LOCK to cause the psql
session to block.

The only functional difference I can see between a SIGTERM exit and a
cancel followed by a normal exit is the call to
AbortCurrentTransaction().  Could that be significant?  Because I can't
reproduce the failure I can't know for sure.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073
#!/usr/contrib/bin/expect --
set timeout -1
eval spawn sql test
expect -nocase "test=>"
send "begin;\r"
expect -nocase "test=>"
send "lock pg_class;\r"
expect -nocase "test=>"
send "select * from pg_locks;\r"
expect -nocase "test=>"
send "update test set x=3;\r"
expect -nocase "test=>"
expect eof
exit
while :
do
XPID=`/letc/ps_sysv -ef | grep 'postgres test'|grep -v grep|awk '{print 
$2}'`
if [ "$XPID" != "" ]
thenkill $XPID
echo $XPID
XPID=`/letc/ps_sysv -ef | grep 'psql test'|grep -v execargs|awk 
'{print $2}'`
kill $XPID
fi
sleep 1
done

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

Re: [HACKERS] DBSize backend integration

[Dave Page]

 

> -Original Message-
> From: Andrew Dunstan [mailto:[EMAIL PROTECTED] 
> Sent: 24 June 2005 21:12
> To: Bruce Momjian
> Cc: Dave Page; PostgreSQL-development
> Subject: Re: [HACKERS] DBSize backend integration
> 
> 
> 
> Bruce Momjian wrote:
> 
> >
> >So drop total_relation_size(), relation_size_components(), and what
> >else?
>
> But these answer easily the question I see most asked - how 
> much space 
> in total does the relation occupy. I'd like to see at least one of 
> these, properly named and fixed w.r.t. schemas. Getting 
> total_relation_size() from relation_size_components() would 
> be easy, so 
> if we only keep one then keep relation_size_components().

relation_size_components() depends on total_relation_size() (which I
have to agree could be useful). I think relation_size_components() is
unecessary though - it looks like it was designed to show a summary
rather than as a view to be used by other clients (if that makes
sense!).

Regards, Dave.

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

Re: [HACKERS] PL/pgSQL Debugger Support

[Bob]

My understanding is that EMS Hitech is just doing client side debugging. 
 
That is they are taking your function and creating a new process to follow the flow of the program. So if they mess up something you may thing your  program is doing one thing when it is really doing something else. Maybe I'm wrong here but I assume that is what is going on.  While this is better than nothing, it doesn't compare to a built in API in pl/pgsql that would allow any tool to hook into a function and debug.  Would love to work on this if I had the low level programming skills that the main hackers have.
 
 
On 6/23/05, Josh Berkus  wrote:
Denis, all,> I got to thinking it¹d be kewl if PgAdmin3 supported an interactive> debugger for pl/pgsql. If anyone¹s interested in expertly tackling such a
> community project, with some financial sponsorship from EDB, please contact> me privately.Just FYI, EMS Hitech has a windows-only PL/pgSQL debugger.  So it's apparentlypossible even with the current tech.
Overally, though, we'd want to support something command-line like the Perldebug shell.  Then any tool could use it.--Josh BerkusAglio Database SolutionsSan Francisco---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate  subscribe-nomail command to [EMAIL PROTECTED] so that your  message can get through to the mailing list cleanly

Re: [HACKERS] DBSize backend integration

[Dave Page]

 

> -Original Message-
> From: Bruce Momjian [mailto:[EMAIL PROTECTED] 
> Sent: 24 June 2005 21:07
> To: Dave Page
> Cc: PostgreSQL-development
> Subject: Re: [HACKERS] DBSize backend integration
> 
> > > 
> > > So drop total_relation_size(), 
> relation_size_components(), and what
> > > else?
> > 
> > indexes_size()
> 
> What is the logic for removing that?  Because it is an 
> aggregate of all
> indexes?

Yes, and is of limited use in my opinion. I can see a use for
pg_relation_size when used on an individual index, but the total of all
indexes on a relation seems of little real use to me (and is relatively
easily calculated if it really is required for a more specialised
purpose).

Regards, Dave.


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] DBSize backend integration

[Andrew Dunstan]

Bruce Momjian wrote:



So drop total_relation_size(), relation_size_components(), and what
else?
 



But these answer easily the question I see most asked - how much space 
in total does the relation occupy. I'd like to see at least one of 
these, properly named and fixed w.r.t. schemas. Getting 
total_relation_size() from relation_size_components() would be easy, so 
if we only keep one then keep relation_size_components().


Just my $0.02 worth

cheers

andrew

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

Re: [HACKERS] DBSize backend integration

[Bruce Momjian]

Dave Page wrote:
>  
> 
> > -Original Message-
> > From: Bruce Momjian [mailto:[EMAIL PROTECTED] 
> > Sent: 24 June 2005 20:45
> > To: Dave Page
> > Cc: PostgreSQL-development
> > Subject: Re: [HACKERS] DBSize backend integration
> > 
> > > My personal view is that pg_database_size, pg_relation_size and
> > > pg_tablespace_size, as well as pg_size_pretty should be included. If
> > > others consider that the by name versions are also useful, then they
> > > should be included, but renamed for consistency. The other three
> > > functions should be dropped IMO.
> > 
> > So drop total_relation_size(), relation_size_components(), and what
> > else?
> 
> indexes_size()

What is the logic for removing that?  Because it is an aggregate of all
indexes?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] DBSize backend integration

[Dave Page]

 

> -Original Message-
> From: Bruce Momjian [mailto:[EMAIL PROTECTED] 
> Sent: 24 June 2005 20:45
> To: Dave Page
> Cc: PostgreSQL-development
> Subject: Re: [HACKERS] DBSize backend integration
> 
> > My personal view is that pg_database_size, pg_relation_size and
> > pg_tablespace_size, as well as pg_size_pretty should be included. If
> > others consider that the by name versions are also useful, then they
> > should be included, but renamed for consistency. The other three
> > functions should be dropped IMO.
> 
> So drop total_relation_size(), relation_size_components(), and what
> else?

indexes_size()

Regards, Dave.

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

Re: [HACKERS] Server instrumentation patch

[Dave Page]

 

> -Original Message-
> From: Bruce Momjian [mailto:[EMAIL PROTECTED] 
> Sent: 24 June 2005 18:47
> To: Dave Page
> Cc: PostgreSQL-development; Andreas Pflug
> Subject: Re: [HACKERS] Server instrumentation patch
> 
> The security issue is that we didn't want the backend to be able to
> read/write outside of /pgdata, and I think we have that 
> working, except

Andreas does indeed appear to be checking to ensure that only files
under $PGDATA can be accessed, by disallowing any paths containing '..'.

> that I have no idea how it will handle config files outside /pgdata. 
> Maybe that was in the patch --- I don't know.

My reading of the code is that it should work OK if they are symlinked
from other locations of course, however if hba_file or ident_file are
set to locations outside $PGDATA, then that will not work. The log
directory can be accessed if it is outside $PGDATA.

I'm sure Andreas can confirm this.

> I think we need to see a new patch with just the i/o 
> functions so we can
> review it. 

Andreas, can you (re)post this please?

> I personally think the I/O functions are a good 
> idea, but I
> need to be considerate of others in the community who have concerns.

Of course. I know we're pushing hard to get these included, but it's not
to try to force in a sub-standard solution, it just seems to us like
we're revisiting issues that we thought were resolved.

We'll get there in the end :-)

/D

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

Re: [HACKERS] DBSize backend integration

[Bruce Momjian]

Dave Page wrote:
> The following functions are currently in contrib/dbsize. As Bruce has
> suggested, we should discuss which functions should or shouldn't be
> moved into the backend, and which should be renamed.
> 
> int8 pg_database_size(oid)
> int8 database_size(name)
> 
> Both return the database size in bytes, the first by oid, the second by
> name. Michael has indicated that he finds the second form useful, and we
> already use the first form in pgAdmin. Either form can emulate the other
> with a simple subselect. I would suggest that the second form be renamed
> to match the first for consistency, if it is decided that they be kept.

Seems we should just name it one name and use function overloading to
support name and oid.

> int8 pg_tablespace_size(oid)  
> 
> This returns the size of the tablespace in bytes. If both forms of the
> database_size function are included, then a 'by name' equivalent should
> probably be added.

Yep.

> int8 pg_relation_size(oid)
> int8 relation_size(text)
> 
> As per *database_size(*), but per relation.
> 
> text pg_size_pretty(int8)
> 
> Converts a size in bytes to B/KB/MB/GB etc.
> 
> int8 indexes_size(text)
> 
> Returns the total size of the indexes on the named relation. A
> convenience function with questionable usefulness (IMO). Currently
> implemented as an SQL function.
> 
> int8 total_relation_size(text)
> 
> Returns relation_size(text) + indexes_size(text) +
> relation_size(text->toast tables). As per indexes_size, currently
> implemented as an SQL function.
> 
> setof record relation_size_components(text)
> 
> A 'view' returning the sizes of each component of the named relation
> (relation, indexes, toast tables etc). Broken at present because it
> isn't schema aware.
> 
> 
> My personal view is that pg_database_size, pg_relation_size and
> pg_tablespace_size, as well as pg_size_pretty should be included. If
> others consider that the by name versions are also useful, then they
> should be included, but renamed for consistency. The other three
> functions should be dropped IMO.

So drop total_relation_size(), relation_size_components(), and what
else?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

[HACKERS] DBSize backend integration

[Dave Page]

The following functions are currently in contrib/dbsize. As Bruce has
suggested, we should discuss which functions should or shouldn't be
moved into the backend, and which should be renamed.

int8 pg_database_size(oid)
int8 database_size(name)

Both return the database size in bytes, the first by oid, the second by
name. Michael has indicated that he finds the second form useful, and we
already use the first form in pgAdmin. Either form can emulate the other
with a simple subselect. I would suggest that the second form be renamed
to match the first for consistency, if it is decided that they be kept.

int8 pg_tablespace_size(oid)

This returns the size of the tablespace in bytes. If both forms of the
database_size function are included, then a 'by name' equivalent should
probably be added.

int8 pg_relation_size(oid)
int8 relation_size(text)

As per *database_size(*), but per relation.

text pg_size_pretty(int8)

Converts a size in bytes to B/KB/MB/GB etc.

int8 indexes_size(text)

Returns the total size of the indexes on the named relation. A
convenience function with questionable usefulness (IMO). Currently
implemented as an SQL function.

int8 total_relation_size(text)

Returns relation_size(text) + indexes_size(text) +
relation_size(text->toast tables). As per indexes_size, currently
implemented as an SQL function.

setof record relation_size_components(text)

A 'view' returning the sizes of each component of the named relation
(relation, indexes, toast tables etc). Broken at present because it
isn't schema aware.


My personal view is that pg_database_size, pg_relation_size and
pg_tablespace_size, as well as pg_size_pretty should be included. If
others consider that the by name versions are also useful, then they
should be included, but renamed for consistency. The other three
functions should be dropped IMO.

Thoughts?

Regards, Dave.

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

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

Re: [HACKERS] Server instrumentation patch

[Bruce Momjian]

Dave Page wrote:
>  
> 
> > -Original Message-
> > From: Michael Paesold [mailto:[EMAIL PROTECTED] 
> > Sent: 24 June 2005 17:53
> > To: Dave Page; Andreas Pflug
> > Cc: PostgreSQL-development
> > Subject: Re: [HACKERS] Server instrumentation patch
> > 
> > > My main concern is that the names are inconsistent for no obvious
> > > reason.
> > 
> > That could be fixed by having:
> > pg_database_size(name)
> > pg_database_size(oid)
> > 
> > The original idea was probably to name "internal" functions 
> > with pg_ and 
> > more user friendly ones without pg_. That does not mean it's 
> > a good idea.
> 
> Yes, agreed - it could be fixed that way easily. If the inclusion of
> /all/ functions is for backwards compatibility though, then that change
> is somewhat more of a problem.

We are moving the functions into the backend so if we are going to make
them more consistent, now is the time.  People are already going to not
have to load them from /contrib, so a more consistent API change is fine
at this stage --- it will be much harder later.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] Server instrumentation patch

[Dave Page]

 

> -Original Message-
> From: Michael Paesold [mailto:[EMAIL PROTECTED] 
> Sent: 24 June 2005 17:53
> To: Dave Page; Andreas Pflug
> Cc: PostgreSQL-development
> Subject: Re: [HACKERS] Server instrumentation patch
> 
> > My main concern is that the names are inconsistent for no obvious
> > reason.
> 
> That could be fixed by having:
> pg_database_size(name)
> pg_database_size(oid)
> 
> The original idea was probably to name "internal" functions 
> with pg_ and 
> more user friendly ones without pg_. That does not mean it's 
> a good idea.

Yes, agreed - it could be fixed that way easily. If the inclusion of
/all/ functions is for backwards compatibility though, then that change
is somewhat more of a problem.

> 
> Well, I don't feel this is really bloat. I have been using 
> them since the 
> creation of the contrib module and have found them quite useful.

Fair enough.

Regards, Dave.

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

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

Re: [HACKERS] Server instrumentation patch

[Bruce Momjian]

Dave Page wrote:
> > The current version of the patch only moves those functions he wants. 
> > Marc says he wants them all moved, and I agree.
> 
> OK - did you see Andreas' response to why he hadn't done that (it was
> actually posted in response to your original query, not Marcs)? In a
> nutshell, the functions that had not been moved returned values that
> were easily derived from the other functions, and thus could be
> considered bloat?
> 
> The functions included in the patch were:
> 
> int8 pg_tablespace_size(oid)  - Return the size of the tablespace in
> bytes
> int8 pg_database_size(oid)- Return the size of the database in
> bytes
> int8 pg_relation_size(oid)- Return the size of the relation in
> bytes
> text pg_size_pretty(int8) - Pretty-print the byte value
> 
> The ones left out were:
> 
> int8 database_size(name)  - Return the size of the database in
> bytes (by name)
> int8 relation_size(text)  - Return the size of the relation in
> bytes (by name)
> int8 indexes_size(text)   - Return the size of the indexes on the
> named relation
> int8 total_relation_size(text) - Return relation_size(text) +
> indexes_size(text) + relation_size(text->toast tables)
> setof record relation_size_components(text) - return a pretty-print set
> of values from above.
> 
> I don't feel particularly strongly either way, but given the normal
> desire not to bloat the backend necessarily, I have to question the need
> to include the latter functions.

OK, well, let's talk about what we want done, then someone can work up a
patch.  Does someone want to make a proposal here on what to do?

> > Well, from the May, 2005 discussion URL you posted, I see a 
> > clear reply
> > to the idea of adding the I/O functions to the backend:
> > 
> > 
> > http://archives.postgresql.org/pgsql-hackers/2005-05/msg00874.php
> > 
> > Now, you can agree or disagree that there are issues with the I/O
> > functions, but the concern was raised in May, and not 
> > addressed at all,
> > either via email or the patch.
> 
> Maybe that's the wrong URL, but all I see there is a vague recollection
> from Tom that there were security issues. In the next message, Andreas
> recalls how you and he worked out the issues that were raised - I
> believe this is the thread
> http://archives.postgresql.org/pgsql-hackers/2004-07/msg00793.php.
> Mhonarc has made a mess of the thread so it does seem to break in a few
> places, and it is possible I've missed part.

The security issue is that we didn't want the backend to be able to
read/write outside of /pgdata, and I think we have that working, except
that I have no idea how it will handle config files outside /pgdata. 
Maybe that was in the patch --- I don't know.

I think we need to see a new patch with just the i/o functions so we can
review it.  I personally think the I/O functions are a good idea, but I
need to be considerate of others in the community who have concerns.

> > For the second, please supply a patch that moves _all_ of dbsize into
> > the main server.  I think we have agreement on that.
> 
> If that remains the case having seen my comments above echoing Andreas'
> concerns then sure, if that's what it takes to get them in, so be it.
> Please confirm either way.

Let's discuss what to move/delete/keep in contrib.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] Server instrumentation patch

[Bruce Momjian]

Dave Page wrote:
> > I vote for all (possibly corrected) functions to be moved into core.
> 
> You have pg_database_size(oid) and database_size(name). Afaict, the
> latter is equivalent to:
> 
> SELECT pg_database_size((SELECT oid FROM pg_database WHERE datname =
> 'foo'))
> 
> My main concern is that the names are inconsistent for no obvious
> reason. I also questioned whether or not the bloat of an additional
> function is worthwhile for what is probably a very small number of psql
> users that might use it (probably quite rarely), however if people say
> they would use it and that it's wothwhile, I wouldn't argue with it's
> inclusion.

Well, this is a good time to figure out exactly what we want in the
backend (perhaps with renaming), and which ones we want to keep in
/contrib, or delete entirely.  The point is that we have to discuss this
item by item, _then_ we can look at a patch.

A patch with assumptions is just confusing to me.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Server instrumentation patch

[Michael Paesold]

Dave Page wrote:



You have pg_database_size(oid) and database_size(name). Afaict, the
latter is equivalent to:

SELECT pg_database_size((SELECT oid FROM pg_database WHERE datname =
'foo'))


The typing is even more e.g. for tables or indexes, though. Of course you 
can use the raw form, but why do we have pg_tables if there is pg_class 
anyway.



My main concern is that the names are inconsistent for no obvious
reason.


That could be fixed by having:
pg_database_size(name)
pg_database_size(oid)

The original idea was probably to name "internal" functions with pg_ and 
more user friendly ones without pg_. That does not mean it's a good idea.



I also questioned whether or not the bloat of an additional
function is worthwhile for what is probably a very small number of psql
users that might use it (probably quite rarely), however if people say
they would use it and that it's wothwhile, I wouldn't argue with it's
inclusion.


Well, I don't feel this is really bloat. I have been using them since the 
creation of the contrib module and have found them quite useful.


Best Regards,
Michael Paesold 



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

Re: [HACKERS] Server instrumentation patch

[Michael Paesold]

Andreas Pflug wrote:


Michael Paesold wrote:


Andreas Pflug wrote:


For the second, please supply a patch that moves _all_ of dbsize into
the main server.  I think we have agreement on that.



I don't think so. As I mentioned, those views are broken. Do you want 
them to be in core anyway?



Why is e.g. this one broken:
int8 database_size(name) - Return the size of the database in
bytes (by name)

It should do the same as the one with the oid except that it will resolve 
the name first, no? If not it should be fixed, not dropped. I understand 
you'd like to have those functions for the GUI frontends, but what about 
psql users? For many people it will be hard work to type the subquery to 
get the database oid.


I vote for all (possibly corrected) functions to be moved into core.


You're correct about the functions, but I was talking about the views. 
"WHERE name = $1" won't respect the schema, contrary to the current doc.


Oh, I am sorry for not reading carefully enough. Didn't know there were 
views at all. So if they are broken and cannot be fixed, well...


Best Regards,
Michael Paesold 



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

  http://archives.postgresql.org

Re: [HACKERS] [PATCHES] O_DIRECT for WAL writes

[Josh Berkus]

Jim,

> Josh, is this something that could be done in the performance lab?

That's the idea.   Sadly, OSDL's hardware has been having critical failures of 
late (I'm still trying to get test results on the checkpointing thing) and 
the GreenPlum machines aren't up yet.

I need to contact those folks in Brazil ...

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

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

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

Re: [HACKERS] [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)

[David Fetter]

On Fri, Jun 24, 2005 at 09:21:25AM -0400, Tom Lane wrote:
> [ moving to -hackers for a wider audience ]
> 
> Today's issue: should the GREATEST/LEAST functions be strict (return
> null if any input is null) or not (return null only if all inputs
> are null, else return the largest/smallest of the non-null inputs)?

I'd say non-strict unless SQL:2003 says different.

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

---(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] Server instrumentation patch

[Jim C. Nasby]

On Fri, Jun 24, 2005 at 05:10:15PM +0100, Dave Page wrote:
> You have pg_database_size(oid) and database_size(name). Afaict, the
> latter is equivalent to:
> 
> SELECT pg_database_size((SELECT oid FROM pg_database WHERE datname =
> 'foo'))
> 
> My main concern is that the names are inconsistent for no obvious
> reason. I also questioned whether or not the bloat of an additional
> function is worthwhile for what is probably a very small number of psql
> users that might use it (probably quite rarely), however if people say
> they would use it and that it's wothwhile, I wouldn't argue with it's
> inclusion.

ISTM it would be better to just add this info into newsysviews. Anyone
who regularly queries against the catalog from psql is going to want to
have them installed anyway. We could either add exact size info to
pg_*_table_storage (http://lnk.nu/cvs.pgfoundry.org/39q.sql) or create a
new view with the file sizes.
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

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

Re: [HACKERS] Server instrumentation patch

[Andreas Pflug]

Michael Paesold wrote:


Andreas Pflug wrote:


For the second, please supply a patch that moves _all_ of dbsize into
the main server.  I think we have agreement on that.



I don't think so. As I mentioned, those views are broken. Do you want 
them to be in core anyway?



Why is e.g. this one broken:
int8 database_size(name) - Return the size of the database in
bytes (by name)

It should do the same as the one with the oid except that it will 
resolve the name first, no? If not it should be fixed, not dropped. I 
understand you'd like to have those functions for the GUI frontends, 
but what about psql users? For many people it will be hard work to 
type the subquery to get the database oid.


I vote for all (possibly corrected) functions to be moved into core.


You're correct about the functions, but I was talking about the views. 
"WHERE name = $1" won't respect the schema, contrary to the current doc.


Regards,
Andreas


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

Re: [HACKERS] Server instrumentation patch

[Dave Page]

 

> -Original Message-
> From: Michael Paesold [mailto:[EMAIL PROTECTED] 
> Sent: 24 June 2005 16:48
> To: Andreas Pflug
> Cc: Dave Page; PostgreSQL-development
> Subject: Re: [HACKERS] Server instrumentation patch
> 
> Andreas Pflug wrote:
> 
> >>For the second, please supply a patch that moves _all_ of 
> dbsize into
> >>the main server.  I think we have agreement on that.
> >>
> >
> > I don't think so. As I mentioned, those views are broken. 
> Do you want them 
> > to be in core anyway?
> 
> Why is e.g. this one broken:
> int8 database_size(name) - Return the size of the database in
> bytes (by name)
>
> It should do the same as the one with the oid except that it 
> will resolve 
> the name first, no? If not it should be fixed, not dropped. I 
> understand 
> you'd like to have those functions for the GUI frontends, but 
> what about 
> psql users? For many people it will be hard work to type the 
> subquery to get 
> the database oid.
> 
> I vote for all (possibly corrected) functions to be moved into core.

You have pg_database_size(oid) and database_size(name). Afaict, the
latter is equivalent to:

SELECT pg_database_size((SELECT oid FROM pg_database WHERE datname =
'foo'))

My main concern is that the names are inconsistent for no obvious
reason. I also questioned whether or not the bloat of an additional
function is worthwhile for what is probably a very small number of psql
users that might use it (probably quite rarely), however if people say
they would use it and that it's wothwhile, I wouldn't argue with it's
inclusion.

Regards, Dave

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

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

Re: [HACKERS] creating WITH HOLD cursors using SPI

[Andrew Dunstan]

Abhijit Menon-Sen wrote:


Hi.

I've been working on making it possible for PL/Perl users to fetch large
result sets one row at a time (the current spi_exec_query interface just
returns a big hash).

The idea is to have spi_query call SPI_prepare/SPI_open_cursor, and have
an spi_fetchrow that calls SPI_cursor_fetch. It works well enough, but I
don't know how to reproduce spi_exec_query's error handling (it runs the
SPI_execute in a subtransaction).

To do something similar, I would have to create a WITH HOLD cursor in my
spi_query function. But SPI_cursor_open provides no way to do this, and
it calls PortalStart before I can set CURSOR_OPT_HOLD myself.

Suggestions?


 



Abhijit,

Thinking and reading about this some more, I think we should not try to 
mimic the error handling of the existing mechanism. Let's just provide a 
separate API using SPI_prepare/SPI_open_cursor/SPI_cursor_fetch, and 
leave the current mechanism in place - it's useful enough on small 
resultsets.


Does that make sense? If so, can you do that, or give me what you have 
and let me bang on it?


cheers

andrew

---(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] [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)

[Tom Lane]

"John Hansen" <[EMAIL PROTECTED]> writes:
> I'd vote that these functions should follow the semantics of the <, and
>> operators.
> (NULL < x) is NULL;

Well, that's a fair analogy, but then so is the analogy to MAX/MIN ...
so it seems about a wash to me.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] Server instrumentation patch

[Michael Paesold]

Andreas Pflug wrote:


For the second, please supply a patch that moves _all_ of dbsize into
the main server.  I think we have agreement on that.



I don't think so. As I mentioned, those views are broken. Do you want them 
to be in core anyway?


Why is e.g. this one broken:
int8 database_size(name) - Return the size of the database in
bytes (by name)

It should do the same as the one with the oid except that it will resolve 
the name first, no? If not it should be fixed, not dropped. I understand 
you'd like to have those functions for the GUI frontends, but what about 
psql users? For many people it will be hard work to type the subquery to get 
the database oid.


I vote for all (possibly corrected) functions to be moved into core.

Best Regards,
Michael Paesold 



---(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] Fixing r-tree semantics

[Mark Cave-Ayland]

> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED] 
> Sent: 24 June 2005 14:27
> To: Mark Cave-Ayland (External)
> Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; oleg@sai.msu.su; 
> pgsql-hackers@postgresql.org; 'PostGIS Development Discussion'
> Subject: Re: Fixing r-tree semantics

(cut)

> Well, I was proposing more or less that but with ^ because of 
> the precedent of the two existing box_above/box_below 
> operator names. However, I'm quite happy to adopt your names, 
> since that's probably a more widely used precedent.  Sold, 
> unless there are objections.
> 
> (BTW, it does look a bit odd that the "|" moves around in 
> your names. But I don't dislike it enough to not follow the 
> precedent.)

The thinking behind it was that the | represents the x-axis if you tilt you
head right 90 degrees. In effect, it would allow you to 'read' the operator
without having to go and look up what it does.

> > It would be harder for us to change these operators since they already 
> > exist, but then again it would be useful from a maintenance point of 
> > view to keep the strategy numbers and operators the same across both 
> > implementations.
> 
> Agreed, I'll use your strategy number assignments too.

Alright no problems.


Many thanks,

Mark.


WebBased Ltd
17 Research Way
Tamar Science Park
Plymouth
PL6 8BT 

T: +44 (0)1752 797131
F: +44 (0)1752 791023
W: http://www.webbased.co.uk



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] HaveNFreeProcs ?

[Jim C. Nasby]

On Thu, Jun 23, 2005 at 12:44:25AM -0400, Tom Lane wrote:
> I wrote:
> > ... because it's written to not loop more than
> > superuser_reserved_connections times, and it's hard to imagine anyone
> > would set that to more than half a dozen or so.
> 
> We could help keep people on the correct path if guc.c enforced a sane
> upper limit on superuser_reserved_connections.  I'm thinking somewhere
> around 10.
> 
> Any thoughts about that?

Maybe a warning in the docs and the sample/default config file would be
better. It seems silly to limit this just because it might cause a
performance problem (this is just a performance issue, right?)
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

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

Re: [GENERAL] [HACKERS] [PATCHES] Removing Kerberos 4

[Jim C. Nasby]

On Thu, Jun 23, 2005 at 07:34:30PM +0200, Magnus Hagander wrote:
> > Has Kerb4 been marked as depricated in the docs at all? If 
> > not it might be best to just do that and then yank it later.
> 
> Yes, since 7.4.
> 
> http://www.postgresql.org/docs/8.0/static/auth-methods.html#KERBEROS-AUT
> H
> http://www.postgresql.org/docs/7.4/static/auth-methods.html#KERBEROS-AUT
> H 
> 
> "Kerberos 4 is considered insecure and no longer recommended for general
> use."

Just as a nitpick, in the future it would probably be better to
explicitely say if something is considered depricated and will be
removed in the future. Having said that, that statement means it's
removal shouldn't come as a shock to anyone.
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

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

Re: [HACKERS] [PATCHES] O_DIRECT for WAL writes

[Jim C. Nasby]

On Fri, Jun 24, 2005 at 09:37:23AM -0400, Tom Lane wrote:
> ITAGAKI Takahiro <[EMAIL PROTECTED]> writes:
> > ... So I'll post the new results:
> 
> > checkpoint_ | writeback | 
> > segments| cache | open_sync | fsync=false   | O_DIRECT only | 
> > fsync_direct  | open_direct
> > +---+---+---+---+---+--
> > [3]   3 | off   |  38.2 tps | 138.8(+263.5%)|  38.6(+ 1.2%) |  
> > 38.5(+ 0.9%) |  38.5(+ 0.9%)
> 
> Yeah, this is about what I was afraid of: if you're actually fsyncing
> then you get at best one commit per disk revolution, and the negotiation
> with the OS is down in the noise.
> 
> At this point I'm inclined to reject the patch on the grounds that it
> adds complexity and portability issues, without actually buying any
> useful performance improvement.  The write-cache-on numbers are not
> going to be interesting to any serious user :-(

Is there anyone with a battery-backed RAID controller that could run
these tests? I suspect that in that case the differences might be closer
to 1 or 2 rather than 3, which would make the patch much more valuable.

Josh, is this something that could be done in the performance lab?
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

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

Re: [HACKERS] [PATCHES] Function's LEAST, GREATEST and DECODE

[Robert Treat]

On Fri, 2005-06-24 at 09:21, Tom Lane wrote:
> [ moving to -hackers for a wider audience ]
> 
> Today's issue: should the GREATEST/LEAST functions be strict (return
> null if any input is null) or not (return null only if all inputs are
> null, else return the largest/smallest of the non-null inputs)?
> 
> Pavel Stehule <[EMAIL PROTECTED]> writes:
> > On Thu, 23 Jun 2005, Tom Lane wrote:
> >> Pavel Stehule <[EMAIL PROTECTED]> writes:
> >> +  /* If any argument is null, then result is null (for 
> >> GREATEST and LEAST)*/
> >> 
> >> Are you sure about that?  The only reference I could find says that
> >> these functions are not strict in Oracle:
> >> 
> >> http://download-east.oracle.com/otn_hosted_doc/rdb/pdf/sql_ref_v71_vol1.pdf
> >> on page 2-185:
> >> 
> >>> The NULL keyword can appear in the list but is ignored. However, not all 
> >>> value expressions can be specified as NULL. That is, a non-NULL value 
> >>> expression must be in the list so that the data type for the expression
> >>> can be determined. 
> >>> The GREATEST and LEAST functions can result in NULL only if at run time 
> >>> all value expressions result in NULL. 
> >> 
> >> The strict interpretation is mathematically cleaner, no doubt, but
> >> offhand it seems less useful.
> >> 
> 
> > I know it, But when moustly PostgreSQL function is strict I desided so 
> > greatest and least will be strict. There is two analogy:
> 
> > one, normal comparing which implicate strinct
> > aggregate function which ignore NULL.
> 
> > Tom I don't know, what is better. Maybe Oracle,
> 
> > because
> 
> > least(nullif(col2, +max), nullif(col2, +max)) isn't really readable, but 
> > it's "precedens" for PostgreSQL. I selected more conservative solution, 
> > but my patches are only start points for discussion (really) :).
> 
> > Please, if You think, so Oracle way is good, correct it.
> 
> I'm still favoring non-strict but it deserves more than two votes.
> Anybody else have an opinion?
> 

If the sql spec has nothing to say on it, then we should probably
support Oracles take, since this seems like an Oracleism anyway. 


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


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

Re: [HACKERS] [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)

[John Hansen]

I'd vote that these functions should follow the semantics of the <, and
> operators.

(NULL < x) is NULL;

... John 


> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
> Sent: Friday, June 24, 2005 11:21 PM
> To: Pavel Stehule
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] [PATCHES] Function's LEAST, GREATEST 
> and DECODE (Oracle vararg polymorphic functions) 
> 
> [ moving to -hackers for a wider audience ]
> 
> Today's issue: should the GREATEST/LEAST functions be strict 
> (return null if any input is null) or not (return null only 
> if all inputs are null, else return the largest/smallest of 
> the non-null inputs)?
> 
> Pavel Stehule <[EMAIL PROTECTED]> writes:
> > On Thu, 23 Jun 2005, Tom Lane wrote:
> >> Pavel Stehule <[EMAIL PROTECTED]> writes:
> >> +  /* If any argument is null, then result 
> is null (for GREATEST 
> >> + and LEAST)*/
> >> 
> >> Are you sure about that?  The only reference I could find 
> says that 
> >> these functions are not strict in Oracle:
> >> 
> >> 
> http://download-east.oracle.com/otn_hosted_doc/rdb/pdf/sql_ref_v71_vo
> >> l1.pdf
> >> on page 2-185:
> >> 
> >>> The NULL keyword can appear in the list but is ignored. 
> However, not 
> >>> all value expressions can be specified as NULL. That is, 
> a non-NULL 
> >>> value expression must be in the list so that the data 
> type for the 
> >>> expression can be determined.
> >>> The GREATEST and LEAST functions can result in NULL only 
> if at run 
> >>> time all value expressions result in NULL.
> >> 
> >> The strict interpretation is mathematically cleaner, no doubt, but 
> >> offhand it seems less useful.
> >> 
> 
> > I know it, But when moustly PostgreSQL function is strict I 
> desided so 
> > greatest and least will be strict. There is two analogy:
> 
> > one, normal comparing which implicate strinct aggregate 
> function which 
> > ignore NULL.
> 
> > Tom I don't know, what is better. Maybe Oracle,
> 
> > because
> 
> > least(nullif(col2, +max), nullif(col2, +max)) isn't really 
> readable, 
> > but it's "precedens" for PostgreSQL. I selected more conservative 
> > solution, but my patches are only start points for 
> discussion (really) :).
> 
> > Please, if You think, so Oracle way is good, correct it.
> 
> I'm still favoring non-strict but it deserves more than two votes.
> Anybody else have an opinion?
> 
>   regards, tom lane
> 
> ---(end of 
> broadcast)---
> TIP 8: explain analyze is your friend
> 
> 

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

Re: [HACKERS] Server instrumentation patch

[Dave Page]

 

> -Original Message-
> From: Bruce Momjian [mailto:[EMAIL PROTECTED] 
> Sent: 24 June 2005 14:00
> To: Dave Page
> Cc: PostgreSQL-development; Andreas Pflug
> Subject: Re: Server instrumentation patch
> 
> Well, I see Marc replying that dbsize should be moved completely from
> contrib:
> 
>   
> http://archives.postgresql.org/pgsql-patches/2005-06/msg00409.php
> 
> The current version of the patch only moves those functions he wants. 
> Marc says he wants them all moved, and I agree.

OK - did you see Andreas' response to why he hadn't done that (it was
actually posted in response to your original query, not Marcs)? In a
nutshell, the functions that had not been moved returned values that
were easily derived from the other functions, and thus could be
considered bloat?

The functions included in the patch were:

int8 pg_tablespace_size(oid)- Return the size of the tablespace in
bytes
int8 pg_database_size(oid)  - Return the size of the database in
bytes
int8 pg_relation_size(oid)  - Return the size of the relation in
bytes
text pg_size_pretty(int8)   - Pretty-print the byte value

The ones left out were:

int8 database_size(name)- Return the size of the database in
bytes (by name)
int8 relation_size(text)- Return the size of the relation in
bytes (by name)
int8 indexes_size(text)   - Return the size of the indexes on the
named relation
int8 total_relation_size(text) - Return relation_size(text) +
indexes_size(text) + relation_size(text->toast tables)
setof record relation_size_components(text) - return a pretty-print set
of values from above.

I don't feel particularly strongly either way, but given the normal
desire not to bloat the backend necessarily, I have to question the need
to include the latter functions.

> > With the exception of the now removed pg_terminate_backend, 
> I am unaware
> > of any issues that are outstanding. If the committers have 
> issues they
> > *must* raise them for *any* submitted patch otherwise 
> developers will
> > lose faith in the process when their hard work gets ignored.
> 
> Well, from the May, 2005 discussion URL you posted, I see a 
> clear reply
> to the idea of adding the I/O functions to the backend:
> 
>   
> http://archives.postgresql.org/pgsql-hackers/2005-05/msg00874.php
> 
> Now, you can agree or disagree that there are issues with the I/O
> functions, but the concern was raised in May, and not 
> addressed at all,
> either via email or the patch.

Maybe that's the wrong URL, but all I see there is a vague recollection
from Tom that there were security issues. In the next message, Andreas
recalls how you and he worked out the issues that were raised - I
believe this is the thread
http://archives.postgresql.org/pgsql-hackers/2004-07/msg00793.php.
Mhonarc has made a mess of the thread so it does seem to break in a few
places, and it is possible I've missed part.

> > Now, to try to get this ball rolling again - do the 
> committers or anyone
> > else have any outstanding issues with the instrumentation or dbsize
> > patches that haven't been answered in public discussion and 
> addressed in
> > the patches already?
> 
> OK, agreed, how can we move forward?  The patch has three parts:
> 
>   o  file I/O
>   o  move dbsize from contrib
>   o  backend terminate
> 
> For the first, we need to re-discuss this on hackers.  I found this as
> the conclusion from July of 2004 as it relates to the I/O functions:
> 
>   
> http://archives.postgresql.org/pgsql-patches/2004-07/msg00561.php
> 

I've just read through that thread, and the only mention of security
concerns I can spot is one where you say yourself that they are a
non-issue!!

What are the actual outstanding concerns with these functions?

> For the second, please supply a patch that moves _all_ of dbsize into
> the main server.  I think we have agreement on that.

If that remains the case having seen my comments above echoing Andreas'
concerns then sure, if that's what it takes to get them in, so be it.
Please confirm either way.

> For backend terminate, I agree with Tom that we have to get SIGTERM
> working, and then the function can just send a SIGTERM signal.  Unless
> it is working 100%, we will not add a terminate function to 
> SQL.  I will
> post separately on this topic.

Agreed.

Regards, Dave.

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

Re: [HACKERS] Server instrumentation patch

[Andreas Pflug]

Bruce Momjian wrote:


Dave Page wrote:
 


The reason it happen that way was because we already had the code as a
contrib-style module for pgAdmin. It was posted because we recognised
that it was becoming a PITA for pgAdmin users to compile a new
server-side component and the functions seemed like they would be useful
to other tools similar to pgAdmin.

Yes, this is not the normal way to proprose new features, but I'm sure
you appreciate that as picture speaks a thousand words, posting the
*existing* code with minor changes to properly integrate it shows
exactly what is being proposed, both in functional and impelmentation
detail.
   



Sure.

 


Now, in 8.1, the same thing has happened.  Two weeks before feature
freeze, with no discussion, the patch appears, and makes no 
reference to

concerns raised during the 8.0 discussion.
 


OK, first it was the 10th of June which is a little more than two weeks,
however, Andreas clearly did reference previous discussions on the
subject - see his message
http://archives.postgresql.org/pgsql-patches/2005-06/msg00226.php in
which he points out that 2 functions are from the logger suprocess patch
from 07/2004, that the file related stuff is based on discussions
starting at
http://archives.postgresql.org/pgsql-patches/2004-07/msg00287.php,
including comments from yourself!!

   


pg_terminate_backend is even
in the patch, and there is no mention or attempt to address 
concerns we

had in 8.0.
 


No. I cannot argue with that, and for that reason I suggested that
Andreas repost the patch without that function so it can be properly
discussed and implemented in a safe way in the future. I'm sure you have
see the reposted patch.
   



OK.

 


The move of dbsize into the backend is similar.  He moves the parts of
dbsize the pgadmin needs into the backend, but makes no mention or
change to /contrib/dbsize to adjust it to the movement of the code. He
has since posted and updated version that fixes this, I think, but
again, we have to discuss how this is to be done --- do we 
move all the

dbsize functions into the backend, some, or none?  Do the other dbsize
functions stay in /contrib or get deleted?
 


Well as far as I can see, Andreas did respond to all queries about it,
and then posted his updated patch after it became apparent noone else
was going to discuss the issue further -
http://archives.postgresql.org/pgsql-patches/2005-06/msg00309.php. From
what I can see, no-one has argued or disagreed with his opinion given a
few days to do so, therefore there was nothing further to discuss. 
   



Well, I see Marc replying that dbsize should be moved completely from
contrib:

http://archives.postgresql.org/pgsql-patches/2005-06/msg00409.php

The current version of the patch only moves those functions he wants. 
Marc says he wants them all moved, and I agree.


 


With the exception of the now removed pg_terminate_backend, I am unaware
of any issues that are outstanding. If the committers have issues they
*must* raise them for *any* submitted patch otherwise developers will
lose faith in the process when their hard work gets ignored.
   



Well, from the May, 2005 discussion URL you posted, I see a clear reply
to the idea of adding the I/O functions to the backend:

http://archives.postgresql.org/pgsql-hackers/2005-05/msg00874.php

Now, you can agree or disagree that there are issues with the I/O
functions, but the concern was raised in May, and not addressed at all,
either via email or the patch.

 


Now, to try to get this ball rolling again - do the committers or anyone
else have any outstanding issues with the instrumentation or dbsize
patches that haven't been answered in public discussion and addressed in
the patches already?
   



OK, agreed, how can we move forward?  The patch has three parts:

o  file I/O
o  move dbsize from contrib
o  backend terminate

For the first, we need to re-discuss this on hackers.  I found this as
the conclusion from July of 2004 as it relates to the I/O functions:

http://archives.postgresql.org/pgsql-patches/2004-07/msg00561.php

However, the TODO items still exist so we can discuss it and hopefully
resolve it by feature freeze.

For the second, please supply a patch that moves _all_ of dbsize into
the main server.  I think we have agreement on that.
 



I don't think so. As I mentioned, those views are broken. Do you want 
them to be in core anyway?


Regards,
Andreas


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

Re: [HACKERS] [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)

[Mike Rylander]

On 6/24/05, Tom Lane <[EMAIL PROTECTED]> wrote:
> [ moving to -hackers for a wider audience ]
> 
> Today's issue: should the GREATEST/LEAST functions be strict (return
> null if any input is null) or not (return null only if all inputs are
> null, else return the largest/smallest of the non-null inputs)?
> 

[snip]

> 
> > Please, if You think, so Oracle way is good, correct it.
> 
> I'm still favoring non-strict but it deserves more than two votes.
> Anybody else have an opinion?
> 
> regards, tom lane
> 

My $0.02: I'd prefer the non-strict version.

-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

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

Re: [HACKERS] [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle

[Andrew Dunstan]

Tom Lane wrote:


[ moving to -hackers for a wider audience ]

Today's issue: should the GREATEST/LEAST functions be strict (return
null if any input is null) or not (return null only if all inputs are
null, else return the largest/smallest of the non-null inputs)?

 



My initial reaction was to say "not strict", and since that's apparently 
what Oracle does that reinforces it for me.


cheers

andrew

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

Re: [HACKERS] [PATCHES] O_DIRECT for WAL writes

[Tom Lane]

ITAGAKI Takahiro <[EMAIL PROTECTED]> writes:
> ... So I'll post the new results:

> checkpoint_ | writeback | 
> segments| cache | open_sync | fsync=false   | O_DIRECT only | 
> fsync_direct  | open_direct
> +---+---+---+---+---+--
> [3]   3 | off   |  38.2 tps | 138.8(+263.5%)|  38.6(+ 1.2%) |  38.5(+ 
> 0.9%) |  38.5(+ 0.9%)

Yeah, this is about what I was afraid of: if you're actually fsyncing
then you get at best one commit per disk revolution, and the negotiation
with the OS is down in the noise.

At this point I'm inclined to reject the patch on the grounds that it
adds complexity and portability issues, without actually buying any
useful performance improvement.  The write-cache-on numbers are not
going to be interesting to any serious user :-(

regards, tom lane

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

Re: [HACKERS] Fixing r-tree semantics

[William White]

Tom Lane wrote:

However, given that the
behavior has been broken since the rtree code was written and nobody
noticed except bwhite, I think it's pretty low-priority to back-patch.


Well, leave it to me to find the obscure bugs in other people's code, 
and miss the blatant ones in my own.


It's been awhile since I've looked at this and I've pretty much swapped 
my PG interals knowledge out of my brain.  As I recall you can (or 
could) demonstrate the error with the default test suite, but you have 
to forcibly override the search strategy cost constants so that PG will 
actually do r-tree index searches (or maybe it was comparisons, it's 
been awhile) instead of sequential scan.  Check the thread, I think I 
did send in a test case.  In reality, with the default constants, you'd 
need a rather large set before you saw any problems.


If anyone is curious, my intended application was time intervals.  That 
is to say, *real* mathematical intervals with two rational numbers as 
endpoints, not just durations (displacements) which as I recall is what 
SQL time "intervals" actually are.  Frankly, I've always considered it a 
serious oversight that PG doesn't provide this as a native type with its 
own indexing and operators, especially given that the default r-tree 
operators don't really work with right-open intervals (though that's 
another rant).  In any case 1D was pretty much universal, barring 
radical changes to the spacetime continuum.  I abandoned the project, 
but not before writing a general set of 1D interval operators to handle 
all cases of open and closed endpoints.


I was under the impression that a fix had already been applied, but it's 
nice to see it happen.  I say this because we discussed possible fixes 
-- either changes to operator semantics or new operators -- and someone 
with a wizard hat nodded in agreement.


-- Bill

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

Re: [HACKERS] Fixing r-tree semantics

[Tom Lane]

"Mark Cave-Ayland" <[EMAIL PROTECTED]> writes:
> The operators I went for were as follows:

>   A &<| B - true if A's bounding box overlaps or is below B's bounding
> box
>   A |&> B - true if B's bounding box overlaps or is above B's bounding
> box
>   A <<| B - true if A's bounding box is strictly below B's bounding
> box
>   A |>> B - true if A's bounding box is strictly above B's bounding
> box

Well, I was proposing more or less that but with ^ because of the
precedent of the two existing box_above/box_below operator names.
However, I'm quite happy to adopt your names, since that's probably
a more widely used precedent.  Sold, unless there are objections.

(BTW, it does look a bit odd that the "|" moves around in your names.
But I don't dislike it enough to not follow the precedent.)

> It would be harder for us to change these operators since they already
> exist, but then again it would be useful from a maintenance point of view to
> keep the strategy numbers and operators the same across both
> implementations.

Agreed, I'll use your strategy number assignments too.

regards, tom lane

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

Re: [HACKERS] [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)

[Tom Lane]

[ moving to -hackers for a wider audience ]

Today's issue: should the GREATEST/LEAST functions be strict (return
null if any input is null) or not (return null only if all inputs are
null, else return the largest/smallest of the non-null inputs)?

Pavel Stehule <[EMAIL PROTECTED]> writes:
> On Thu, 23 Jun 2005, Tom Lane wrote:
>> Pavel Stehule <[EMAIL PROTECTED]> writes:
>> +/* If any argument is null, then result is null (for 
>> GREATEST and LEAST)*/
>> 
>> Are you sure about that?  The only reference I could find says that
>> these functions are not strict in Oracle:
>> 
>> http://download-east.oracle.com/otn_hosted_doc/rdb/pdf/sql_ref_v71_vol1.pdf
>> on page 2-185:
>> 
>>> The NULL keyword can appear in the list but is ignored. However, not all 
>>> value expressions can be specified as NULL. That is, a non-NULL value 
>>> expression must be in the list so that the data type for the expression
>>> can be determined. 
>>> The GREATEST and LEAST functions can result in NULL only if at run time 
>>> all value expressions result in NULL. 
>> 
>> The strict interpretation is mathematically cleaner, no doubt, but
>> offhand it seems less useful.
>> 

> I know it, But when moustly PostgreSQL function is strict I desided so 
> greatest and least will be strict. There is two analogy:

> one, normal comparing which implicate strinct
> aggregate function which ignore NULL.

> Tom I don't know, what is better. Maybe Oracle,

> because

> least(nullif(col2, +max), nullif(col2, +max)) isn't really readable, but 
> it's "precedens" for PostgreSQL. I selected more conservative solution, 
> but my patches are only start points for discussion (really) :).

> Please, if You think, so Oracle way is good, correct it.

I'm still favoring non-strict but it deserves more than two votes.
Anybody else have an opinion?

regards, tom lane

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

Re: [HACKERS] Server instrumentation patch

[Bruce Momjian]

Dave Page wrote:
> The reason it happen that way was because we already had the code as a
> contrib-style module for pgAdmin. It was posted because we recognised
> that it was becoming a PITA for pgAdmin users to compile a new
> server-side component and the functions seemed like they would be useful
> to other tools similar to pgAdmin.
> 
> Yes, this is not the normal way to proprose new features, but I'm sure
> you appreciate that as picture speaks a thousand words, posting the
> *existing* code with minor changes to properly integrate it shows
> exactly what is being proposed, both in functional and impelmentation
> detail.

Sure.

> > Now, in 8.1, the same thing has happened.  Two weeks before feature
> > freeze, with no discussion, the patch appears, and makes no 
> > reference to
> > concerns raised during the 8.0 discussion.
> 
> OK, first it was the 10th of June which is a little more than two weeks,
> however, Andreas clearly did reference previous discussions on the
> subject - see his message
> http://archives.postgresql.org/pgsql-patches/2005-06/msg00226.php in
> which he points out that 2 functions are from the logger suprocess patch
> from 07/2004, that the file related stuff is based on discussions
> starting at
> http://archives.postgresql.org/pgsql-patches/2004-07/msg00287.php,
> including comments from yourself!!
> 
> > pg_terminate_backend is even
> > in the patch, and there is no mention or attempt to address 
> > concerns we
> > had in 8.0.
> 
> No. I cannot argue with that, and for that reason I suggested that
> Andreas repost the patch without that function so it can be properly
> discussed and implemented in a safe way in the future. I'm sure you have
> see the reposted patch.

OK.

> > The move of dbsize into the backend is similar.  He moves the parts of
> > dbsize the pgadmin needs into the backend, but makes no mention or
> > change to /contrib/dbsize to adjust it to the movement of the code. He
> > has since posted and updated version that fixes this, I think, but
> > again, we have to discuss how this is to be done --- do we 
> > move all the
> > dbsize functions into the backend, some, or none?  Do the other dbsize
> > functions stay in /contrib or get deleted?
> 
> Well as far as I can see, Andreas did respond to all queries about it,
> and then posted his updated patch after it became apparent noone else
> was going to discuss the issue further -
> http://archives.postgresql.org/pgsql-patches/2005-06/msg00309.php. From
> what I can see, no-one has argued or disagreed with his opinion given a
> few days to do so, therefore there was nothing further to discuss. 

Well, I see Marc replying that dbsize should be moved completely from
contrib:

http://archives.postgresql.org/pgsql-patches/2005-06/msg00409.php

The current version of the patch only moves those functions he wants. 
Marc says he wants them all moved, and I agree.

> With the exception of the now removed pg_terminate_backend, I am unaware
> of any issues that are outstanding. If the committers have issues they
> *must* raise them for *any* submitted patch otherwise developers will
> lose faith in the process when their hard work gets ignored.

Well, from the May, 2005 discussion URL you posted, I see a clear reply
to the idea of adding the I/O functions to the backend:

http://archives.postgresql.org/pgsql-hackers/2005-05/msg00874.php

Now, you can agree or disagree that there are issues with the I/O
functions, but the concern was raised in May, and not addressed at all,
either via email or the patch.

> Now, to try to get this ball rolling again - do the committers or anyone
> else have any outstanding issues with the instrumentation or dbsize
> patches that haven't been answered in public discussion and addressed in
> the patches already?

OK, agreed, how can we move forward?  The patch has three parts:

o  file I/O
o  move dbsize from contrib
o  backend terminate

For the first, we need to re-discuss this on hackers.  I found this as
the conclusion from July of 2004 as it relates to the I/O functions:

http://archives.postgresql.org/pgsql-patches/2004-07/msg00561.php

However, the TODO items still exist so we can discuss it and hopefully
resolve it by feature freeze.

For the second, please supply a patch that moves _all_ of dbsize into
the main server.  I think we have agreement on that.

For backend terminate, I agree with Tom that we have to get SIGTERM
working, and then the function can just send a SIGTERM signal.  Unless
it is working 100%, we will not add a terminate function to SQL.  I will
post separately on this topic.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 9: 

Re: [HACKERS] Fixing r-tree semantics

[Mark Cave-Ayland]

Hi Tom,

> What needs more discussion is that it seems to me to make sense to extend
the standard 
> opclasses to handle the four Y-direction operators comparable to the
X-direction 
> operators that are already there, that is "above", "below", "overabove",
and 
> "overbelow".

As part of PostGIS (http://postgis.refractions.net), I submitted a patch a
while back to add additional Y-direction operators to the code which is a
slightly modified version of rtree_gist (and yes, the PostGIS implementation
will suffer from the same issues you've found with the existing R-tree
implementations).

The operators I went for were as follows:

A &<| B - true if A's bounding box overlaps or is below B's bounding
box
A |&> B - true if B's bounding box overlaps or is above B's bounding
box
A <<| B - true if A's bounding box is strictly below B's bounding
box
A |>> B - true if A's bounding box is strictly above B's bounding
box

Since the rtree_gist implementation and operators were 2D, my thoughts were
to use another op-class only if the indexing were upgraded to 3D. So with
this in mind, I created the following new GiST strategies:

#define RTOverBelowStrategyNumber   9
#define RTBelowStrategyNumber   10
#define RTAboveStrategyNumber   11
#define RTOverAboveStrategyNumber   12

This is basically what you are suggesting but with a | instead of a ^ in the
operator name (my original choice was to try and use } to indicate the
positive sense of the Y-axis but this was not accepted as a valid operator
character which is why I changed to |).

It would be harder for us to change these operators since they already
exist, but then again it would be useful from a maintenance point of view to
keep the strategy numbers and operators the same across both
implementations. Of course strategy numbers are just used internally so
these aren't such a big issue - it's more the choice of operators that would
be useful to agree on.


Kind regards,

Mark.


WebBased Ltd
17 Research Way
Tamar Science Park
Plymouth
PL6 8BT 

T: +44 (0)1752 797131
F: +44 (0)1752 791023
W: http://www.webbased.co.uk



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

   http://archives.postgresql.org