Re: [HACKERS] Further reduction of bufmgr lock contention

[Qingqing Zhou]

"Tom Lane" <[EMAIL PROTECTED]> wrote
>
> The thing that's nagging at me at the moment is the realization that a
> partitioned hashtable will eat more shared memory than a single
> hashtable.  It wasn't that long ago that we had to do some hacking to
> ensure that the buffer hashtable couldn't run out of memory after
> startup, and I'm afraid of re-introducing that failure mode.  The lock
> manager can run out of memory without crashing the system, but the
> bufmgr can't (or at least could not in the recent past...)
>

IHMO overflow is not avoidable no matter we use hash or range. Theoretically
seems we could have a data structure like this: (1) a set of k partition
tables, each is with a LWLock and size NBuffers/k; (2) a set of k overflow
tables (actually we only need k-1) plus a LWLock protecting them, each is
with size NBuffers/k. If any partition table overflows, we can assign a
overflow table for it to contain extra hash elements. At run time, the hash
tables for buffer pool may look like this:

[partition 0]
[partition 1][overflow 2]
[partition 2][overflow 0]
[partition 3]

But I am not sure how difficult to implement it in current hash code -
another handwaiving ...

Regards,
Qingqing




---(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] bug# 2073 - Clarification

[Dhanaraj M]

Bug- raised:
=
BUG #2073: Can't drop sequence when created via SERIAL column

Answered:


Right.  We have this TODO item:

* %Disallow changing default expression of a SERIAL column?

which would prevent you from changing the default expression for a
SERIAL column.  So the answer is, don't do that, and in the future, we
might prevent it.

Clarification required:
=

Pl. look at the following code, which is taken from alter_table.sql 
(regression test)


=

mydb=# create table anothertab (atcol1 serial8, atcol2 boolean, 
constraint anothertab_chk check (atcol1 <= 3));


NOTICE:  CREATE TABLE will create implicit sequence 
"anothertab_atcol1_seq" for serial column "anothertab.atcol1"


CREATE TABLE

mydb=# alter table anothertab alter column atcol1 drop default;
ALTER TABLE

mydb=# \d
List of relations
Schema | Name  |   Type   |  Owner
+---+--+--
public | anothertab| table| dm199272
public | anothertab_atcol1_seq | sequence | dm199272

(2 rows)

mydb=# drop sequence anothertab_atcol1_seq;

ERROR:  cannot drop sequence anothertab_atcol1_seq because table 
anothertab column atcol1 requires it


HINT:  You may drop table anothertab column atcol1 instead.

=

Please tell me whether statement-2 is valid or not (as you say that the 
default sequence should not be changed).


Or the default seq. can be dropped and cant be  changed. I like to know
the expected output.


Thanks

Dhanaraj




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

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

Re: [HACKERS] TODO Item: ACL_CONNECT

[Alvaro Herrera]

Gevik Babakhani wrote:

> To my surprise the code you described above was already there :) 
> function aclchk.c:pg_database_aclmask:1696

Sure, that sort of was my point :-)

> If the above is okay and correct. Then I guess for simple systems one
> could only enter the line below in pg_hba.conf 
> "host/hostsselall all(whatever IP)   (whatever option)"

Ok, good.  This is what people was aiming for initially, I hope.  What
do people think, particularly those who wanted to manage pg_hba.conf via
SQL commands?

> New test patch:
> http://www.xs4all.nl/~gevik/patch/patch-0.2.diff

Without looking at the surrounding code, I'm a bit wary of the fact that
in ReverifyMyDatabase, pg_database_aclcheck is called with GetUserId()
but the error message is emitted with the user_name that was passed as
parameter instead.  The inconsistency could prove painful in the future;
maybe it's OK, but if it is, you should declare it in the surrounding
comments.

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

---(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] Unresolved Win32 bug reports

[Jim C. Nasby]

On Mon, Apr 24, 2006 at 10:23:07AM +0800, Qingqing Zhou wrote:
> 
> "Tom Lane" <[EMAIL PROTECTED]> wrote
> > Martijn van Oosterhout  writes:
> > > On Thu, Apr 20, 2006 at 12:17:07PM -0500, Jim C. Nasby wrote:
> > >> Here's one to add to the list: running pgbench with a moderately heavy
> > >> load on an SMP box likes to trigger a state where the database (or
> > >> pgbench) just stops doing work (CPU usage drops to nothing, as does
> disk
> > >> activity).
> >
> > > Well, this sounds like a dead-lock, the obvious step would be to
> > > attached gdb to both and get a stack-trace...
> >
> > Yeah, I wonder if it's related to that apparent bug Qingqing saw in the
> > windows semaphore code?  It's clearly windows-specific since no one's
> > ever reported any such thing on Unixen.
> >
> 
> I also suspect the EAGAIN error reports are related to the semaphore code.
> So if possible, I suggest we patch the code and test it.

There a patched build available for testing? (I'd rather not have to
figure out how to get windows builds working, unless there's some kind
of instructions somewhere...)
-- 
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 6: explain analyze is your friend

[HACKERS] 2x compile warning

[Gevik Babakhani]

I noticed the following compile warnings. Perhaps someone is interested
to know about them.

/usr/bin/flex  -o'pgc.c' pgc.l
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g
-Wno-error  -I./../include -I. -I../../../../src/include -D_GNU_SOURCE
-DMAJOR_VERSION=4 -DMINOR_VERSION=2 -DPATCHLEVEL=1  -c -o preproc.o
preproc.c -MMD
In file included from preproc.y:6606:
pgc.c: In function ‘yylex’:
pgc.c:1549: warning: label ‘find_rule’ defined but not used

_GNU_SOURCE   -c -o mac.o mac.c -MMD
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g
-I../../../../src/include -D_GNU_SOURCE   -c -o inet_net_ntop.o
inet_net_ntop.c -MMD
inet_net_ntop.c: In function ‘inet_cidr_ntop_ipv6’:
inet_net_ntop.c:292: warning: value computed is not used


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

Re: [HACKERS] Google SoC--Idea Request

[Jim C. Nasby]

Where do we stand with getting much more reasonable default values in
postgresql.conf? Maybe that should be a SoC project, or is it too small?
-- 
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 5: don't forget to increase your free space map settings

[HACKERS] ecpg memory leaks

[Michael Meskes]

Martijn, thanks for finfing those leaks. I haven't been around last
week, so it took until today to commit the fixes. But they are in now.

Michael
-- 
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

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

Re: [HACKERS] Further reduction of bufmgr lock contention

[Gavin Hamill]

On Fri, 21 Apr 2006 17:38:01 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:

> I believe the particular test case being looked at here is read-only
> (Gavin, is that correct?)

Yes - I made sure the devels made it readonly so I could farm search
requests out to Slony-replicated machines (ended up running live
searches for the whole site on a host hundreds of miles away :)

> Keep in mind that Gavin's 8-way turns back into a pumpkin on
> Monday :-(

Aye, it would've been gone earlier today but the rental company were
being a bit slack so pushed it back to Monday. The pickup is
already arranged so I can't stall them at this stage.

I guess I could play the 'help the greater good by lending your kit for
open source devel' card with them once they get it back to their office.

Otherwise, I've seen at least one offer of pSeries hardware on
the performance list - I'm sure I could make available a version of our
data with all sensitive stuff removed so that it could be tested on
other machines.

.. and to top it all off, I didn't even get to go to the ball - and I
doubt there'll be a glass slipper on offer...

gdh

---(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] TODO Item: ACL_CONNECT

[Gevik Babakhani]

Hi

> I don't understand.  The code should look like this:
> 
> if (acl in pg_database == NULL)
>   acl = acldefault
> else
>   acl = acl in pg_database
> if (has_permission(acl, user, ACL_CONNECT))
>   can connect
> else
>   can't connect
> 

To my surprise the code you described above was already there :) 
function aclchk.c:pg_database_aclmask:1696

snip...
if (isNull)
{
/* No ACL, so build default ACL */
acl = acldefault(ACL_OBJECT_DATABASE, ownerId);
aclDatum = (Datum) 0;
}

However the original acldefault:case:ACL_OBJECT_DATABASE only had
ACL_CREATE_TEMP as default for PUBLIC. I thought by adding ACL_CONNECT
to the world_owner makes connecting to a database available for public,
which is the required behavior as discussed yesterday. 

Original...
case ACL_OBJECT_DATABASE:
world_default = ACL_CREATE_TEMP /* NO_RIGHTS! */
owner_default = ACL_ALL_RIGHTS_DATABASE;
break;

Proposed
case ACL_OBJECT_DATABASE:
world_default = ACL_CREATE_TEMP | ACL_CONNECT; /* NO_RIGHTS! */
owner_default = ACL_ALL_RIGHTS_DATABASE;
break;

Would the above be correct?
The following is how I tested the code above.


1. make new new compile/install and initdb.

2. run createdb  (database pgdev is created)

3. psql  (login with user pgdev to pgdev)

4. create role user1 login; and then quit.

5. psql -U user1 -d pgdev (login success. this is the backward
 compatible and the required behavior I guess we wanted)

6. quit and login with psql like step in 3

7. 
 GRANT CONNECTION ON DATABASE pgdev to pgdev; 
(this would overwrite the ACL NULL. The public ACL still exists.)

 REVOKE CONNECTION ON DATABASE pgdev from PUBLIC; and the quit
(public cannot login to pgdev anymore :) only the owner )

8. psql -U user1 -d pgdev (login fails this time 

psql: FATAL:  couldn't connect to database pgdev
DETAIL:  User user1 doesn't have the CONNECTION privilege for database
pgdev.

)

9. quit and login with psql like step in 3
GRANT CONNECTION ON DATABASE pgdev to user1; and quit.

10. psql -U user1 -d pgdev (login success and the {user1=c/pgdev}
is added to the ACL)

* end test *

If the above is okay and correct. Then I guess for simple systems one
could only enter the line below in pg_hba.conf 
"host/hostsselall all(whatever IP)   (whatever option)"

and by granting ACL_CONNECT to roles could keep 
the pg_hba.conf simple and short.

New test patch:
http://www.xs4all.nl/~gevik/patch/patch-0.2.diff



---(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] Regression error on float8

[Magnus Hagander]

> > I'm getting the following regression errors with a backend 
> built using 
> > Visual C++:
> 
> Is HAVE_CBRT getting defined?  Either their cbrt() routine or 
> our default one seems to be generating slightly-off answers.  
> The default one (at the bottom of float.c) certainly looks a 
> bit cheesy, but if it fails this test you'd think we'd have 
> heard about that sooner.

HAVE_CBRT is not set.

If I undefine HAVE_CBRT on Linux, I get the exact same failure! So it
seems our own version of cbrt() is broken wrt our own regression tests
:-( Must be that nobody else (at least on i386) uses that code.

The mingw version does appear to work, but it's noticably more complex,
see
http://cvs.sourceforge.net/viewcvs.py/mingw/runtime/mingwex/math/cbrt.c?
rev=1.1&view=auto.

It's placed in the public domain, so we should be able to use it if we
want to
(http://cvs.sourceforge.net/viewcvs.py/mingw/runtime/DISCLAIMER?rev=1.1&;
view=auto).

What do you think is best - try to adapt that version, or update our
regression tests outputs to accept the output from our current code?

//Magnus


---(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] Further reduction of bufmgr lock contention

[Mark Wong]

Tom Lane wrote:

Simon Riggs <[EMAIL PROTECTED]> writes:

On Fri, 2006-04-21 at 13:01 -0400, Tom Lane wrote:

I've been looking into Gavin Hamill's recent report of poor performance
with PG 8.1 on an 8-way IBM PPC64 box. 


Keep in mind that Gavin's 8-way turns back into a pumpkin on Monday :-(


I have a 4-way dual-core POWER5 system available...

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] bug# 2073 - Clarification

[Tom Lane]

Dhanaraj M <[EMAIL PROTECTED]> writes:
> Pl. look at the following code, which is taken from alter_table.sql 
> (regression test)

The regression test is designed to check for the current behavior.
If we were to change the behavior, then of course we'd need to change
the regression test as well.

Personally I'm starting to like the idea of attaching the sequence's
dependency to the column default definition (ie, to the attrdef entry
not directly to the column).  That would mean that ALTER COLUMN DROP
DEFAULT would be allowed and would cause the sequence to go away.
I'm not sure about whether this would have any downsides, though.

regards, tom lane

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

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

Re: [HACKERS] 2x compile warning

[Kris Jurka]

On Mon, 24 Apr 2006, Gevik Babakhani wrote:


I noticed the following compile warnings. Perhaps someone is interested
to know about them.


Also I was testing a gcc 4.2 snapshot (20060419) and it has a whole lot of 
warnings stemming from heap_getattr's isnull check:


aclchk.c:791: warning: the address of 'isNull', will always evaluate as 
'true'


aclDatum = heap_getattr(tuple, Anum_pg_database_datacl, 
RelationGetDescr(relation), &isNull);



#define heap_getattr(tup, attnum, tupleDesc, isnull) \
( \
AssertMacro((tup) != NULL), \
( \
((attnum) > 0) ? \
( \
((attnum) > (int) (tup)->t_data->t_natts) ? \
( \
((isnull) ? (*(isnull) = true) : (dummyret)NULL), \
(Datum)NULL \
) \
: \
fastgetattr((tup), (attnum), (tupleDesc), (isnull)) \
) \
: \
heap_getsysattr((tup), (attnum), (tupleDesc), (isnull)) \
) \
)

Removing the check for (isnull) before (*(isnull) = true) as in the 
attached patch passes make check, but I have not looked at every 
heap_getattr call site to ensure it's passing a valid isnull pointer.


Kris JurkaIndex: src/include/access/heapam.h
===
RCS file: /projects/cvsroot/pgsql/src/include/access/heapam.h,v
retrieving revision 1.107
diff -c -r1.107 heapam.h
*** src/include/access/heapam.h 24 Mar 2006 04:32:13 -  1.107
--- src/include/access/heapam.h 24 Apr 2006 18:12:16 -
***
*** 45,51 
  #define fastgetattr(tup, attnum, tupleDesc, isnull)   
\
  ( 
\
AssertMacro((attnum) > 0),  
\
!   ((isnull) ? (*(isnull) = false) : (dummyret)NULL),  
\
HeapTupleNoNulls(tup) ? 
\
(   
\
(tupleDesc)->attrs[(attnum)-1]->attcacheoff >= 0 ?  
\
--- 45,51 
  #define fastgetattr(tup, attnum, tupleDesc, isnull)   
\
  ( 
\
AssertMacro((attnum) > 0),  
\
!   (*(isnull) = false),
\
HeapTupleNoNulls(tup) ? 
\
(   
\
(tupleDesc)->attrs[(attnum)-1]->attcacheoff >= 0 ?  
\
***
*** 61,67 
(   
\
att_isnull((attnum)-1, (tup)->t_data->t_bits) ? 
\
(   
\
!   ((isnull) ? (*(isnull) = true) : (dummyret)NULL),   
\
(Datum)NULL 
\
)   
\
:   
\
--- 61,67 
(   
\
att_isnull((attnum)-1, (tup)->t_data->t_bits) ? 
\
(   
\
!   (*(isnull) = true), 
\
(Datum)NULL 
\
)   
\
:   
\
***

Re: [HACKERS] Regression error on float8

[Martijn van Oosterhout]

On Mon, Apr 24, 2006 at 03:01:51PM +0200, Magnus Hagander wrote:
> If I undefine HAVE_CBRT on Linux, I get the exact same failure! So it
> seems our own version of cbrt() is broken wrt our own regression tests
> :-( Must be that nobody else (at least on i386) uses that code.



> What do you think is best - try to adapt that version, or update our
> regression tests outputs to accept the output from our current code?

Given that our output gets very very close, perhaps we should take a
hint from the end of the MinGW version, do a single Newton iteration to
fixup those last few digits.

Adding this before the last line of our version of cbrt():

tmpres -= ( tmpres - (x/(tmpres*tmpres)) )*0.;

Makes it give the same result as my system version...
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [HACKERS] TODO Item: ACL_CONNECT

[Gevik Babakhani]

> Ok, good.  This is what people was aiming for initially, I hope.  What
> do people think, particularly those who wanted to manage pg_hba.conf via
> SQL commands?

I guess for this one more people have to play with the new
functionality. 

> Without looking at the surrounding code, I'm a bit wary of the fact that
> in ReverifyMyDatabase, pg_database_aclcheck is called with GetUserId()
> but the error message is emitted with the user_name that was passed as
> parameter instead.  The inconsistency could prove painful in the future;
> maybe it's OK, but if it is, you should declare it in the surrounding
> comments.

I have added proper comment for that.

-

I guess the next step is to check for the last ACL_CONNECT privilege as
discussed below.


> At this moment the owner of the database CAN REVOKE himself form the
> ACL_OBJECT_DATABASE. If the implementation above is acceptable then I
> can work on this one :)

Hmm, what do you want to do about it?  ISTM the owner should be able to
revoke the privilege from himself ... (Maybe we could raise a WARNING
whenever anyone revokes the last CONNECT privilege to a database, so
that he can GRANT it to somebody before disconnecting.)


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

   http://archives.postgresql.org

Re: [HACKERS] Regression error on float8

[Tom Lane]

Martijn van Oosterhout  writes:
> Given that our output gets very very close, perhaps we should take a
> hint from the end of the MinGW version, do a single Newton iteration to
> fixup those last few digits.

That seems like a plan to me.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] 2x compile warning

[Tom Lane]

Kris Jurka <[EMAIL PROTECTED]> writes:
> Also I was testing a gcc 4.2 snapshot (20060419) and it has a whole lot of 
> warnings stemming from heap_getattr's isnull check:
> aclchk.c:791: warning: the address of 'isNull', will always evaluate as 
> 'true'

We need to lobby the gcc maintainers to not give warnings about valid
and perfectly reasonable code.  I'm not thrilled with changing the API
of a key macro for no other reason than that gcc has started to complain
about it.

regards, tom lane

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

Re: [HACKERS] 2x compile warning

[Bruce Momjian]

Gevik Babakhani wrote:
> I noticed the following compile warnings. Perhaps someone is interested
> to know about them.
> 
> /usr/bin/flex  -o'pgc.c' pgc.l
> gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
> -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g
> -Wno-error  -I./../include -I. -I../../../../src/include -D_GNU_SOURCE
> -DMAJOR_VERSION=4 -DMINOR_VERSION=2 -DPATCHLEVEL=1  -c -o preproc.o
> preproc.c -MMD
> In file included from preproc.y:6606:
> pgc.c: In function ?yylex?:
> pgc.c:1549: warning: label ?find_rule? defined but not used

This is a standard warning generated by bison and we can't get rid of
it.

> _GNU_SOURCE   -c -o mac.o mac.c -MMD
> gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
> -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g
> -I../../../../src/include -D_GNU_SOURCE   -c -o inet_net_ntop.o
> inet_net_ntop.c -MMD
> inet_net_ntop.c: In function ?inet_cidr_ntop_ipv6?:
> inet_net_ntop.c:292: warning: value computed is not used

I have fixed this by adding a (void) cast, patch attached.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: src/backend/utils/adt/inet_net_ntop.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/inet_net_ntop.c,v
retrieving revision 1.21
diff -c -c -r1.21 inet_net_ntop.c
*** src/backend/utils/adt/inet_net_ntop.c   15 Oct 2005 02:49:28 -  
1.21
--- src/backend/utils/adt/inet_net_ntop.c   24 Apr 2006 19:49:04 -
***
*** 289,295 
}
}
/* Format CIDR /width. */
!   SPRINTF((cp, "/%u", bits));
if (strlen(outbuf) + 1 > size)
goto emsgsize;
strcpy(dst, outbuf);
--- 289,295 
}
}
/* Format CIDR /width. */
!   (void) SPRINTF((cp, "/%u", bits));
if (strlen(outbuf) + 1 > size)
goto emsgsize;
strcpy(dst, outbuf);

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

Re: [HACKERS] Regression error on float8

[Magnus Hagander]

> > What do you think is best - try to adapt that version, or 
> update our 
> > regression tests outputs to accept the output from our current code?
> 
> Given that our output gets very very close, perhaps we should 
> take a hint from the end of the MinGW version, do a single 
> Newton iteration to fixup those last few digits.
> 
> Adding this before the last line of our version of cbrt():
> 
> tmpres -= ( tmpres - (x/(tmpres*tmpres)) )*0.;
> 
> Makes it give the same result as my system version...

That totally didn't work on visual c++ at least. It fixes those two
ones, but it breaks the other lines in the same test:

*** ./expected/float8-exp-three-digits-win32.outWed Jun  8
23:15:29 2005
--- ./results/float8.outMon Apr 24 21:57:40 2006
***
*** 321,329 
  SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f;
   five |  f1  |cbrt_f1
  --+--+---
!   |0 | 0
|   1004.3 |   10.014312837827
!   |   -34.84 | -3.26607421344208
| 1.2345678901234e+200 | 4.97933859234765e+066
| 1.2345678901234e-200 |  2.3112042409018e-067
  (5 rows)
--- 321,329 
  SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f;
   five |  f1  |cbrt_f1
  --+--+---
!   |0 |   NaN
|   1004.3 |   10.014312837827
!   |   -34.84 | -1.08869140448069
| 1.2345678901234e+200 | 4.97933859234765e+066
| 1.2345678901234e-200 |  2.3112042409018e-067
  (5 rows)



//Magnus

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

Re: [HACKERS] 2x compile warning

[Bruce Momjian]

Tom Lane wrote:
> Kris Jurka <[EMAIL PROTECTED]> writes:
> > Also I was testing a gcc 4.2 snapshot (20060419) and it has a whole lot of 
> > warnings stemming from heap_getattr's isnull check:
> > aclchk.c:791: warning: the address of 'isNull', will always evaluate as 
> > 'true'
> 
> We need to lobby the gcc maintainers to not give warnings about valid
> and perfectly reasonable code.  I'm not thrilled with changing the API
> of a key macro for no other reason than that gcc has started to complain
> about it.

Right.  The issue is that when I originally transfered that function to
a macro, the isnull parameter was optionally 0/NULL. While our code
doesn't use that ability, I see no reason to remove it.

I understand why it is complaining because you are really doing if
(&var), but it is a macro, so it can be used in other circumstances as
well.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

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

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

   http://archives.postgresql.org

Re: [HACKERS] Regression error on float8

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> That totally didn't work on visual c++ at least. It fixes those two
> ones, but it breaks the other lines in the same test:

It's a couple bricks shy of a load (doesn't handle zero or negative
input correctly) but easily fixed.  I'm just about to commit it.

regards, tom lane

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

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

Re: [HACKERS] Regression error on float8

[Magnus Hagander]

> > That totally didn't work on visual c++ at least. It fixes those two 
> > ones, but it breaks the other lines in the same test:
> 
> It's a couple bricks shy of a load (doesn't handle zero or 
> negative input correctly) but easily fixed.  I'm just about 
> to commit it.

Great. I'll check it out on VC++ as soon as it propagates out to
anoncvs.

//Magnus

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

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

Re: [HACKERS] 2x compile warning

[Martijn van Oosterhout]

On Mon, Apr 24, 2006 at 01:16:04PM -0500, Kris Jurka wrote:
> 
> 
> On Mon, 24 Apr 2006, Gevik Babakhani wrote:
> 
> >I noticed the following compile warnings. Perhaps someone is interested
> >to know about them.
> 
> Also I was testing a gcc 4.2 snapshot (20060419) and it has a whole lot of 
> warnings stemming from heap_getattr's isnull check:
> 
> aclchk.c:791: warning: the address of 'isNull', will always evaluate as 
> 'true'

Perhaps someone could check if changing the test explicitly check
against NULL:

> ((attnum) > (int) (tup)->t_data->t_natts) ? \
> ( \
> (((isnull) != NULL)? (*(isnull) = true) : (dummyret)NULL), \
> (Datum)NULL \

removes the warning. It seems silly for the GCC people to add warnings
for this kind of stuff without a simple way to bypass it...
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [HACKERS] 2x compile warning

[Kris Jurka]

On Mon, 24 Apr 2006, Martijn van Oosterhout wrote:


Perhaps someone could check if changing the test explicitly check
against NULL:


((attnum) > (int) (tup)->t_data->t_natts) ? \
( \
(((isnull) != NULL)? (*(isnull) = true) : (dummyret)NULL), \
(Datum)NULL \


removes the warning. It seems silly for the GCC people to add warnings
for this kind of stuff without a simple way to bypass it...


Yes, this coding removes the warning.

Kris Jurka


---(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] pgsql: Improve our private implementation of cbrt() to give results of

[Magnus Hagander]

Confirmed to work on Win32 Visual C++. 

With this patch, Win32 VisualC++ now passes all regression tests.
Thanks, Tom!

//Magnus 

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
> Sent: Monday, April 24, 2006 10:37 PM
> To: pgsql-committers@postgresql.org
> Subject: pgsql: Improve our private implementation of cbrt() 
> to give results of 
> 
> Log Message:
> ---
> Improve our private implementation of cbrt() to give results 
> of the accuracy expected by the regression tests.  Per 
> suggestion from Martijn van Oosterhout.
> 
> Modified Files:
> --
> pgsql/src/backend/utils/adt:
> float.c (r1.123 -> r1.124)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/
> utils/adt/float.c.diff?r1=1.123&r2=1.124)
> 
> ---(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
> 

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

Re: [HACKERS] 2x compile warning

[Tom Lane]

Kris Jurka <[EMAIL PROTECTED]> writes:
> On Mon, 24 Apr 2006, Martijn van Oosterhout wrote:
>> Perhaps someone could check if changing the test explicitly check
>> against NULL:
>> 
>> ((attnum) > (int) (tup)->t_data->t_natts) ? \
>> ( \
>> (((isnull) != NULL)? (*(isnull) = true) : (dummyret)NULL), \
>> (Datum)NULL \
>> 
>> removes the warning. It seems silly for the GCC people to add warnings
>> for this kind of stuff without a simple way to bypass it...

> Yes, this coding removes the warning.

Oh, good, that seems like a reasonable change to make (it's arguably
more clear than the original anyway).

Is this the only place where the warning shows up?  ISTM there's quite
a lot of code that uses "if (ptr)" for a NULL-ness check.

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] 2x compile warning

[Martijn van Oosterhout]

On Mon, Apr 24, 2006 at 05:39:30PM -0400, Tom Lane wrote:
> > Yes, this coding removes the warning.
> 
> Oh, good, that seems like a reasonable change to make (it's arguably
> more clear than the original anyway).
> 
> Is this the only place where the warning shows up?  ISTM there's quite
> a lot of code that uses "if (ptr)" for a NULL-ness check.

But very little code of the form "if (&local_var)" which is always true
and what causes the problem here. I think this is just a variation on
the compiler test for "if (i=1)" which is also always true but probably
a bug. That warning you avoid with an extra set of parenthesis.

I'd be surprised if there were many other issues here, only complex
macros are likely to cause this one.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [HACKERS] 2x compile warning

[Bruce Momjian]

Kris Jurka wrote:
> 
> 
> On Mon, 24 Apr 2006, Martijn van Oosterhout wrote:
> 
> > Perhaps someone could check if changing the test explicitly check
> > against NULL:
> >
> >> ((attnum) > (int) (tup)->t_data->t_natts) ? \
> >> ( \
> >> (((isnull) != NULL)? (*(isnull) = true) : (dummyret)NULL), 
> >> \
> >> (Datum)NULL \
> >
> > removes the warning. It seems silly for the GCC people to add warnings
> > for this kind of stuff without a simple way to bypass it...
> 
> Yes, this coding removes the warning.

Great, fix attached and applied.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: src/include/access/heapam.h
===
RCS file: /cvsroot/pgsql/src/include/access/heapam.h,v
retrieving revision 1.107
diff -c -c -r1.107 heapam.h
*** src/include/access/heapam.h 24 Mar 2006 04:32:13 -  1.107
--- src/include/access/heapam.h 24 Apr 2006 22:03:14 -
***
*** 100,106 
( \
((attnum) > (int) (tup)->t_data->t_natts) ? \
( \
!   ((isnull) ? (*(isnull) = true) : 
(dummyret)NULL), \
(Datum)NULL \
) \
: \
--- 100,106 
( \
((attnum) > (int) (tup)->t_data->t_natts) ? \
( \
!   ((isnull) != NULL) ? (*(isnull) = true) : 
(dummyret)NULL), \
(Datum)NULL \
) \
: \

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

Re: [HACKERS] 2x compile warning

[Kris Jurka]

On Mon, 24 Apr 2006, Bruce Momjian wrote:


Great, fix attached and applied.



You also need to change lines 48 and 64 of heapam.h to use the same 
coding.


Kris Jurka

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

Re: [HACKERS] 2x compile warning

[Bruce Momjian]

Kris Jurka wrote:
> 
> 
> On Mon, 24 Apr 2006, Bruce Momjian wrote:
> 
> > Great, fix attached and applied.
> >
> 
> You also need to change lines 48 and 64 of heapam.h to use the same 
> coding.

Done.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

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

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

   http://archives.postgresql.org

Re: [HACKERS] [GENERAL] Concurrency problem building indexes

[Jim C. Nasby]

moving to -hackers

On Sun, Apr 23, 2006 at 09:06:59AM -0500, Wes wrote:
> Postgres: 8.1.3
> OS: Mac OS X 10.4.6
> 
> I've run into another concurrency issue - parallel building of indexes.
> When I try to build multiple indexes at a time, I randomly get:
> 
>   ERROR:  tuple concurrently updated
> 
> The following thread talks about this, but there is no answer.
> 
>   
> 
> How can I safely build indexes in parallel?
> 
> At this point, I'm only trying to build two at a time.  I will be building
> indexes for tables with any where from a few rows to 100 million rows on a
> daily basis - I need to maximize performance.

Since this seems to only be an issue due to trying to update pg_class
for the table, perhaps CREATE INDEX can just ignore errors there?
-- 
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 6: explain analyze is your friend

Re: [HACKERS] [GENERAL] Concurrency problem building indexes

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Since this seems to only be an issue due to trying to update pg_class
> for the table, perhaps CREATE INDEX can just ignore errors there?

Lessee, where would ignoring an error potentially cause the greatest
damage?  I can hardly think of a less critical catalog than pg_class :-(

regards, tom lane

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

Re: [HACKERS] [GENERAL] Concurrency problem building indexes

[Jim C. Nasby]

On Mon, Apr 24, 2006 at 08:14:33PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > Since this seems to only be an issue due to trying to update pg_class
> > for the table, perhaps CREATE INDEX can just ignore errors there?
> 
> Lessee, where would ignoring an error potentially cause the greatest
> damage?  I can hardly think of a less critical catalog than pg_class :-(

Sorry, should have been more specific... as I understand it, the update
is just to set pg_class.relpages for the heap, which shouldn't be
critical.

Was the code ever changed so that it won't update relpages if the number
is the same?
-- 
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 5: don't forget to increase your free space map settings

Re: [HACKERS] [GENERAL] Concurrency problem building indexes

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Was the code ever changed so that it won't update relpages if the number
> is the same?

Long ago.  I just tested it again, and AFAICS you can create indexes
concurrently so long as the underlying table isn't changing (ie,
neither reltuples nor relpages changes).

regards, tom lane

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

Re: [HACKERS] [GENERAL] Concurrency problem building indexes

[Jim C. Nasby]

On Mon, Apr 24, 2006 at 08:42:41PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > Was the code ever changed so that it won't update relpages if the number
> > is the same?
> 
> Long ago.  I just tested it again, and AFAICS you can create indexes
> concurrently so long as the underlying table isn't changing (ie,
> neither reltuples nor relpages changes).

Hrm, the OP seemed to find a case that was having problems:
http://archives.postgresql.org/pgsql-general/2006-04/msg01009.php

Of course it's possible that he's getting that error from an entirely
different section of code, or that this is now only an issue if you're
doing a lot of indexing at once...
-- 
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 2: Don't 'kill -9' the postmaster

Re: [HACKERS] TODO Item: ACL_CONNECT

[Gevik Babakhani]

Hi,

The following is also added to a new patch.

> > At this moment the owner of the database CAN REVOKE himself form the
> > ACL_OBJECT_DATABASE. If the implementation above is acceptable then I
> > can work on this one :)
> 
> Hmm, what do you want to do about it?  ISTM the owner should be able to
> revoke the privilege from himself ... (Maybe we could raise a WARNING
> whenever anyone revokes the last CONNECT privilege to a database, so
> that he can GRANT it to somebody before disconnecting.)
> 

If one is going to revoke the last ACL_CONNECT, a warning is going to
issued then that part of the REVOKE gets canceled.

Can this patch be tested for assurance?
What is the next step after that? Should I send the patch to the
pgsql-patches list?

http://www.xs4all.nl/~gevik/patch/patch-0.4.diff

Regards,
Gevik.


---(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] Unresolved Win32 bug reports

[Qingqing Zhou]

""Jim C. Nasby"" <[EMAIL PROTECTED]> wrote
>
> There a patched build available for testing? (I'd rather not have to
> figure out how to get windows builds working, unless there's some kind
> of instructions somewhere...)
> -- 

Not yet - the patch is still pending.

Regards,
Qingqing



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

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

Re: [HACKERS] Google SoC--Idea Request

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Where do we stand with getting much more reasonable default values in
> postgresql.conf? Maybe that should be a SoC project, or is it too small?

Define "much more reasonable".

I doubt this is SoC material, simply because the issues have little to
do with coding and a lot to do with persuading people to drop default
support for old platforms.  Which is not something a student is likely
to succeed at.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] Google SoC--Idea Request

[Jonah H. Harris]

On 4/24/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> I doubt this is SoC material, simply because the issues have little to
> do with coding and a lot to do with persuading people to drop default
> support for old platforms.  Which is not something a student is likely
> to succeed at.

While the student could do some benchmarking on relatively new
hardware and make suggestions, I agree with Tom.  Having to keep
support for older platforms doesn't leave much flexibility to change
the defaults.

I just don't see enough work here to warrant a SoC project.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

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

Re: [HACKERS] Google SoC--Idea Request

[Tom Lane]

"Jonah H. Harris" <[EMAIL PROTECTED]> writes:
> While the student could do some benchmarking on relatively new
> hardware and make suggestions, I agree with Tom.  Having to keep
> support for older platforms doesn't leave much flexibility to change
> the defaults.

Another point here is that the defaults *are* reasonable for development
and for small installations; the people who are complaining are the ones
who expect to run terabyte databases without any tuning.  (I exaggerate
perhaps, but the point is valid.)

We've talked more than once about offering multiple alternative
starting-point postgresql.conf files to give people an idea of what to
do for small/medium/large installations.  MySQL have done that for years
and it doesn't seem that users are unable to cope with the concept.
But doing this is (a) mostly a matter of testing and documenting, not
coding and (b) probably too small for a SoC project anyway.

regards, tom lane

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

Re: [HACKERS] [GENERAL] Concurrency problem building indexes

[Wes]

On 4/24/06 7:54 PM, "Jim C. Nasby" <[EMAIL PROTECTED]> wrote:

>> Long ago.  I just tested it again, and AFAICS you can create indexes
>> concurrently so long as the underlying table isn't changing (ie,
>> neither reltuples nor relpages changes).
> 
> Hrm, the OP seemed to find a case that was having problems:
> http://archives.postgresql.org/pgsql-general/2006-04/msg01009.php
> 
> Of course it's possible that he's getting that error from an entirely
> different section of code, or that this is now only an issue if you're
> doing a lot of indexing at once...

I don't think there's much chance it's other code.  The index build is a
standalone operation in an external script that uses psql (so it is easy to
tailor).  This script is called as the last statement before the main
program exit.  It does:

  fork
create index1
create index2
exit

  fork
create index3
create index4
exit

  wait for termination
  exit

As my test tables are small and indexing happens almost instantly, I put a
'sleep' after one of the forks so that the two don't complete at the same
time.  When I do that, I don't get the errors.

Wes






---(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] TODO Item: ACL_CONNECT

[Alvaro Herrera]

Gevik Babakhani wrote:

> If one is going to revoke the last ACL_CONNECT, a warning is going to
> issued then that part of the REVOKE gets canceled.

Humm, no, the WARNING is issued but the REVOKE is executed anyway.

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

---(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] Checking assumptions

[Bruce Momjian]

Are we OK with the Coverity reports now?

---

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Fri, Apr 21, 2006 at 09:12:51AM +0800, Christopher Kings-Lynne wrote:
> > >I havn't been able to find any more serious issues in the Coverity
> > >report, now that they've fixed the ereport() issue. A number of the
> > >issues it complains about are things we already Assert() for. For the
> > >rest, as long as the following assumptions are true we're done (well,
> > >except for ECPG). I think they are true but it's always good to check:
> > 
> > Everytime someone does this, we fix everything except ECPG.  Surely it's 
> > time we fixed ECPG as well?
> 
> I've got a patch (not by me) that should fix most of the issues.
> However, we have no way to test for regressions. So, that's why I
> suggested (elsewhere) someone get the ECPG regression stuff working so
> we can apply fixes and check they don't break anything...
> 
> Have a nice day,
> -- 
> Martijn van Oosterhout  http://svana.org/kleptog/
> > From each according to his ability. To each according to his ability to 
> > litigate.
-- End of PGP section, PGP failed!

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.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] Google SoC--Idea Request

[Jonah H. Harris]

On 4/24/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> We've talked more than once about offering multiple alternative
> starting-point postgresql.conf files to give people an idea of what to
> do for small/medium/large installations.  MySQL have done that for years
> and it doesn't seem that users are unable to cope with the concept.
> But doing this is (a) mostly a matter of testing and documenting, not
> coding and (b) probably too small for a SoC project anyway.

Yeah, it would be nice to offer a small/med/large config file, but
there are also other considerations that affect PostgreSQL and not
MySQL.  An example is the system-wide shared memory maximum... RedHat
defaults to 32M, SuSE to 32M?, and OSX to 4M (or something crazy like
that).  So even if we give out a med/large config file, they won't
work for most people who have default Linux installs.  Tuning
PostgreSQL isn't all that hard, but it may be nice to give people a
starting point.

I don't know, I'm not averse to adding something like the following to
the SoC ideas:

Benchmark PostgreSQL and analyze results to build optimal default
configuration files for medium and large-scale systems.

Of course, the definition of medium and large vary, as does the
application (OLTP, DSS, etc.); so we'd have to define them.

Thoughts?


--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

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

   http://archives.postgresql.org

Re: [HACKERS] TODO Item: ACL_CONNECT

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Gevik Babakhani wrote:
>> If one is going to revoke the last ACL_CONNECT, a warning is going to
>> issued then that part of the REVOKE gets canceled.

> Humm, no, the WARNING is issued but the REVOKE is executed anyway.

Why are we debating this?  It won't get accepted anyway, because the
whole thing is silly.  Show me one other object type that we issue
such warnings for, or anyone else who has even suggested that we should.

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] [GENERAL] Concurrency problem building indexes

[Wes]

>> Long ago.  I just tested it again, and AFAICS you can create indexes
>> concurrently so long as the underlying table isn't changing (ie,
>> neither reltuples nor relpages changes).
> 
> Hrm, the OP seemed to find a case that was having problems:
> http://archives.postgresql.org/pgsql-general/2006-04/msg01009.php
> 
> Of course it's possible that he's getting that error from an entirely
> different section of code, or that this is now only an issue if you're
> doing a lot of indexing at once...

I just verified using 'ps' that there are no other open connections when the
index builds are running.  I tried somewhat bigger test tables (a few rows
to a little over a hundred thousand).  I can duplicate the error at will.

Wes



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

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

Re: [HACKERS] [GENERAL] Concurrency problem building indexes

[Tom Lane]

Wes <[EMAIL PROTECTED]> writes:
> I can duplicate the error at will.

[ shrug... ]  Worksforme.

There is a short interval at the end of the first CREATE INDEX on the
table where the problem would happen if another CREATE INDEX tries to
modify the pg_class row before the first one's committed.  That would be
hard to hit with any regularity though.  Subsequent CREATE INDEXes after
that should be completely reliable.  If you are modifying the table
while it's being indexed, then the same small window would apply for
each CREATE INDEX not just the first ... but you didn't admit to that.

Care to put together a self-contained test case?

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] Google SoC--Idea Request

[Jim C. Nasby]

On Mon, Apr 24, 2006 at 11:05:18PM -0400, Tom Lane wrote:
> "Jonah H. Harris" <[EMAIL PROTECTED]> writes:
> > While the student could do some benchmarking on relatively new
> > hardware and make suggestions, I agree with Tom.  Having to keep
> > support for older platforms doesn't leave much flexibility to change
> > the defaults.
> 
> Another point here is that the defaults *are* reasonable for development
> and for small installations; the people who are complaining are the ones
> who expect to run terabyte databases without any tuning.  (I exaggerate
> perhaps, but the point is valid.)
> 
> We've talked more than once about offering multiple alternative
> starting-point postgresql.conf files to give people an idea of what to
> do for small/medium/large installations.  MySQL have done that for years
> and it doesn't seem that users are unable to cope with the concept.
> But doing this is (a) mostly a matter of testing and documenting, not
> coding and (b) probably too small for a SoC project anyway.

My recollection was that there was opposition to offering multiple
config files, but that there was a proposal to make initdb smarter about
picking configuration values.

Personally, I agree that multiple config files would be fine. Or a
really fancy solution would be feeding a config option to initdb and
have it generate an appropriate postgresql.conf.
-- 
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 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Google SoC--Idea Request

[Jonah H. Harris]

On 4/25/06, Andrew Dunstan <[EMAIL PROTECTED]> wrote:
> We have already done some initdb tuning improvements for 8.2

Cool, I hadn't looked at this.

> I would have liked to increase max_connections too, but that would have
> caused problems on OSX, apparently. See previous discussion.

Yeah, their defaults really suck.

> Personally I would much rather see a tuning advisor tool in more general
> use than just provide small/medium/large config setting files.

True dat.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

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

Re: [HACKERS] [GENERAL] Concurrency problem building indexes

[Wes]

On 4/24/06 11:02 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote:

> [ shrug... ]  Worksforme.
> 
> There is a short interval at the end of the first CREATE INDEX on the
> table where the problem would happen if another CREATE INDEX tries to
> modify the pg_class row before the first one's committed.  That would be
> hard to hit with any regularity though.  Subsequent CREATE INDEXes after
> that should be completely reliable.  If you are modifying the table
> while it's being indexed, then the same small window would apply for
> each CREATE INDEX not just the first ... but you didn't admit to that.
> 
> Care to put together a self-contained test case?

I think I've got a reasonably small test case I can send you in the morning.
I did a pg_dumpall and removed the index creation commands.  The first time
I run the index build, I usually get at least one occurrence.

Where do you want me to send it to?

Of course, since your hardware is different, it may not show up since it
appears to be a timing thing..  I'm on a PB G4 1Ghz.

Wes



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

Re: [HACKERS] [GENERAL] Concurrency problem building indexes

[Tom Lane]

Wes <[EMAIL PROTECTED]> writes:
> Where do you want me to send it to?

pgsql-bugs would be appropriate.

> Of course, since your hardware is different, it may not show up since it
> appears to be a timing thing..  I'm on a PB G4 1Ghz.

My G4 is in the shop at the moment, but Apple promised it back by Friday.
I kinda doubt it's *that* platform specific though.

regards, tom lane

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

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

Re: [HACKERS] Google SoC--Idea Request

[Andrew Dunstan]

Jim C. Nasby wrote:


On Mon, Apr 24, 2006 at 11:05:18PM -0400, Tom Lane wrote:
 


"Jonah H. Harris" <[EMAIL PROTECTED]> writes:
   


While the student could do some benchmarking on relatively new
hardware and make suggestions, I agree with Tom.  Having to keep
support for older platforms doesn't leave much flexibility to change
the defaults.
 


Another point here is that the defaults *are* reasonable for development
and for small installations; the people who are complaining are the ones
who expect to run terabyte databases without any tuning.  (I exaggerate
perhaps, but the point is valid.)

We've talked more than once about offering multiple alternative
starting-point postgresql.conf files to give people an idea of what to
do for small/medium/large installations.  MySQL have done that for years
and it doesn't seem that users are unable to cope with the concept.
But doing this is (a) mostly a matter of testing and documenting, not
coding and (b) probably too small for a SoC project anyway.
   



My recollection was that there was opposition to offering multiple
config files, but that there was a proposal to make initdb smarter about
picking configuration values.

Personally, I agree that multiple config files would be fine. Or a
really fancy solution would be feeding a config option to initdb and
have it generate an appropriate postgresql.conf.
 




We have already done some initdb tuning improvements for 8.2 - shared 
buffers now tops out at 4000 instead of 1000 and initdb now sets 
max_fsm_pages at a more realistic level. (top is 200,000 instead of 
previously hardcoded 20,000).


I would have liked to increase max_connections too, but that would have 
caused problems on OSX, apparently. See previous discussion.


Personally I would much rather see a tuning advisor tool in more general 
use than just provide small/medium/large config setting files.



cheers

andrew


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

Re: [HACKERS] Google SoC--Idea Request

[Tom Lane]

"Jonah H. Harris" <[EMAIL PROTECTED]> writes:
> On 4/25/06, Andrew Dunstan <[EMAIL PROTECTED]> wrote:
>> Personally I would much rather see a tuning advisor tool in more general
>> use than just provide small/medium/large config setting files.

> True dat.

One thing that has to be figured out before we can go far with this
is the whole question of how much smarts initdb really ought to have.
Since a lot of packagers think that initdb should be run
non-interactively behind the scenes, the obvious solution of "give
initdb a --small/--medium/--large parameter" does not work all that
nicely.  But on the other hand we can't just tell people to drop in
replacement config files when the one in place contains initdb-created
specifics, such as locale settings.

Now that there's a provision for "include" directives in
postgresql.conf, one way to address this would be to split the
config info into multiple physical files, some containing purely
performance-related settings while others consider functionality.
But that seems more like a wart than a solution to me.  I feel that
we've pushed performance-tuning logic into initdb that probably ought
not be there, and we ought to factor it out again.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] Google SoC--Idea Request

[ipig]

Maybe you can develop a graphic interface just like Fedora Core setup interface 
which can choose packages installing, then the user can choose config file and 
then have a little change in parameters.
 
- Original Message - 
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Jonah H. Harris" <[EMAIL PROTECTED]>
Cc: "Andrew Dunstan" <[EMAIL PROTECTED]>; "Jim C. Nasby" <[EMAIL PROTECTED]>; 
"John DeSoi" <[EMAIL PROTECTED]>; "Pgsql Hackers" 
Sent: Tuesday, April 25, 2006 2:16 PM
Subject: Re: [HACKERS] Google SoC--Idea Request 


> "Jonah H. Harris" <[EMAIL PROTECTED]> writes:
>> On 4/25/06, Andrew Dunstan <[EMAIL PROTECTED]> wrote:
>>> Personally I would much rather see a tuning advisor tool in more general
>>> use than just provide small/medium/large config setting files.
> 
>> True dat.
> 
> One thing that has to be figured out before we can go far with this
> is the whole question of how much smarts initdb really ought to have.
> Since a lot of packagers think that initdb should be run
> non-interactively behind the scenes, the obvious solution of "give
> initdb a --small/--medium/--large parameter" does not work all that
> nicely.  But on the other hand we can't just tell people to drop in
> replacement config files when the one in place contains initdb-created
> specifics, such as locale settings.
> 
> Now that there's a provision for "include" directives in
> postgresql.conf, one way to address this would be to split the
> config info into multiple physical files, some containing purely
> performance-related settings while others consider functionality.
> But that seems more like a wart than a solution to me.  I feel that
> we've pushed performance-tuning logic into initdb that probably ought
> not be there, and we ought to factor it out again.
> 
> regards, tom lane
> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>   http://archives.postgresql.org
>
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

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

Re: [HACKERS] [GENERAL] Concurrency problem building indexes

[Wes]

On 4/25/06 1:01 AM, "Tom Lane" <[EMAIL PROTECTED]> wrote:

>> Where do you want me to send it to?
> 
> pgsql-bugs would be appropriate.

It's not that small that I would want to post it to a list.  Also, I think
I've sanitized the data, but I wouldn't want to post it on a public list.

Or are you just looking for the index build script?

>> Of course, since your hardware is different, it may not show up since it
>> appears to be a timing thing..  I'm on a PB G4 1Ghz.
> 
> My G4 is in the shop at the moment, but Apple promised it back by Friday.
> I kinda doubt it's *that* platform specific though.

You never know...  CPU speed, hard drive speed (slow), etc.

Wes



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

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