Re: [PATCHES] pg_autovacuum integration attempt #2

2004-07-12 Thread Peter Eisentraut
Bruce Momjian wrote:
> I have added this patch plus your later comments to the patch queue.

The autovacuum process still uses libpq to send its queries, which is 
not the idea behind backend integration.

> Your patch has been added to the PostgreSQL unapplied patches list
> at:
>
>   http://momjian.postgresql.org/cgi-bin/pgpatches
>
> I will try to apply it within the next 48 hours.
>
> -
>--
>
> Matthew T. O'Connor wrote:
> > I have make the changes suggested after I posted my last patch, I
> > have also make several additional improvements.  it needs to be
> > tested more, but since the deadline is coming up so soon, I wanted
> > to post an update just to keep everyone abreast of what I'm doing.
> > Please review and let me know what I need to change to get it
> > applied to CVS.
> >
> > As before, this patch requires that pg_autovacuum.c and .h are
> > moved from contrib to src/backend/postmaster and
> > src/include/postmaster respectively. I have also attached the
> > pg_autovacuum.h file that needs to be put in src/include/catelog/
> > for the new pg_autovacuum system table.
> >
> > I assume I will have to write the docs for this, but right now I'm
> > focusing on the code, I get get the docs written after the feature
> > freeze.
> >
> > Matthew O'Connor
>
> [ Attachment, skipping... ]
>
> [ Attachment, skipping... ]
>
> > ---(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: [PATCHES] pg_autovacuum integration attempt #2

2004-07-12 Thread Andreas Pflug
Peter Eisentraut wrote:
Bruce Momjian wrote:
 

I have added this patch plus your later comments to the patch queue.
   

The autovacuum process still uses libpq to send its queries, which is 
not the idea behind backend integration.
 

Can we consider this a non-fatal bug that has to be solved soon after 
the patch is applied?

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


Re: [PATCHES] build infrastructure for extensions v3

2004-07-12 Thread Fabien COELHO

Dear Peter,


> ISTM that this version still does not allow building contrib without
> having installed pg_config first.


This version DOES allow building contrib without having installed
pg_config first. Indeed, I did not remove the previous infrastructure;-)

I play a little bit on words, but it is a fact anyway. With the submitted
patch, you just need to type "make" to compile contrib as usual!


(1) The main aim of the infrastructure is not for contribs but for
external modules. There is not such issue for external add-ons.
I preliminarily provided the contrib makefiles as "how to" examples.


(2) What the patch adds is the ability to ALSO install a contrib as an
afterthought. It also enable external (non contrib) modules to rely on it
so they can be installed much more simply. You have to invoke
"make -f pgxs.mk" to do that, as the makefile is a little bit different.

So the issue would be relevant if the old makefiles were to be removed,
which is not the case with the submitted patch. If they were to be
removed, I also have an opinion at the time. I may be wrong, but this my
opinion nevertheless without further arguments:


(3) I think it is not a real issue. The argument you gave is "packagers
will need to compile contribs without installing postgresql first". My
point is that if they can do that with apache, they should survive with
postgresql just the same.

More specifically, I think that it is much better to be able to compile a
contrib or any external module without having to reconfigure the sources,
and even without needing to get them.

Now if you have other arguments, or that you can explain to me why this
fact is such an issue, I may change my point of view.


(4) I could fix the contrib pgxs makefiles so that they check for
something, like:

PG := $(shell type pg_config 2> /dev/null)
ifeq ($(PG),)
# postgresql is not installed...
else
# postgresql is installed...
endif

so what?

  1. Even if there is pg_config in the path, that would not
 necessarilly mean that the user want to compile with it.

  2. This would make the contrib pgxs makefiles different from
 what they are expected to be for external modules. I intended
 these makefiles as examples, and now they would be different from
 what is needed.

If you have a cool idea to make the original extension makefile to work in
any case without modification, I'm interested, because it would enable to
have both worlds and everybody would be happy!


(5) As a improvement to the submitted version, I could think of mixing the
current (contrib/contrib-global.mk) and (src/makefiles/pgxs.mk)  files so
that the core of the extension makefile is not replicated. But that would
not change the issue we're discussing.


So I'm still reserved about your reserves. Moreover I think that this
patch adds something and does not remove anything, and that it is
desirable to have an "extension infrastructure" for external modules in
the upcoming version, whether it is this one or anything else.
Although not perfect, this one is much better than nothing.


Anyway, if having a unique infrastructure is a definite blocking
constraint, I can implement (4) above, even if I think it is not such a
good idea.

So you decide. As I'll away during 3 weeks from July 19, it would be
better to decide quickly so that I can make the necessary changes before
leaving.


Have a nice day,

-- 
Fabien Coelho - [EMAIL PROTECTED]

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


[PATCHES] Show tablespace name in pg_tables and pg_indexes

2004-07-12 Thread Klaus Naumann

Hi,

the attached patch shows the new column "tablespace" in the mentioned
views.
Apply with

~/pgsql$ patch -p1 < 03_showtblspc.diff

Greetings, Klaus

-- 
Full Name   : Klaus Naumann | (http://www.mgnet.de/) (Germany)
Phone / FAX : ++49/177/7862964  | E-Mail: ([EMAIL PROTECTED])diff -Ncr pgsql-pitr.orig/src/backend/catalog/system_views.sql 
pgsql-pitr/src/backend/catalog/system_views.sql
*** pgsql-pitr.orig/src/backend/catalog/system_views.sqlMon Jul 12 14:20:02 
2004
--- pgsql-pitr/src/backend/catalog/system_views.sql Mon Jul 12 14:59:44 2004
***
*** 41,62 
--- 41,66 
  SELECT 
  N.nspname AS schemaname, 
  C.relname AS tablename, 
+T.spcname AS tablespace,
  pg_get_userbyid(C.relowner) AS tableowner, 
  C.relhasindex AS hasindexes, 
  C.relhasrules AS hasrules, 
  (C.reltriggers > 0) AS hastriggers 
  FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
+  JOIN pg_tablespace T ON (T.oid = C.reltablespace)
  WHERE C.relkind = 'r';
  
  CREATE VIEW pg_indexes AS 
  SELECT 
  N.nspname AS schemaname, 
  C.relname AS tablename, 
+ T.spcname AS tablespace,
  I.relname AS indexname, 
  pg_get_indexdef(I.oid) AS indexdef 
  FROM pg_index X JOIN pg_class C ON (C.oid = X.indrelid) 
   JOIN pg_class I ON (I.oid = X.indexrelid) 
   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
+  JOIN pg_tablespace T ON (T.oid = C.reltablespace)
  WHERE C.relkind = 'r' AND I.relkind = 'i';
  
  CREATE VIEW pg_stats AS 

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


Re: [PATCHES] Show tablespace name in pg_tables and pg_indexes

2004-07-12 Thread Klaus Naumann
On Mon, 12 Jul 2004, Klaus Naumann wrote:

Hi,

sorry, the last patch is buggy which didn't show up in the tests :(
Two LEFTs were missing - new patch is attached.

Greetings, Klaus


>
> Hi,
>
> the attached patch shows the new column "tablespace" in the mentioned
> views.
> Apply with
>
> ~/pgsql$ patch -p1 < 03_showtblspc.diff
>
> Greetings, Klaus
>
>

-- 
Full Name   : Klaus Naumann | (http://www.mgnet.de/) (Germany)
Phone / FAX : ++49/177/7862964  | E-Mail: ([EMAIL PROTECTED])diff -Ncr pgsql-pitr.orig/src/backend/catalog/system_views.sql 
pgsql-pitr/src/backend/catalog/system_views.sql
*** pgsql-pitr.orig/src/backend/catalog/system_views.sqlMon Jul 12 14:20:02 
2004
--- pgsql-pitr/src/backend/catalog/system_views.sql Mon Jul 12 14:59:44 2004
***
*** 41,62 
--- 41,66 
  SELECT 
  N.nspname AS schemaname, 
  C.relname AS tablename, 
+T.spcname AS tablespace,
  pg_get_userbyid(C.relowner) AS tableowner, 
  C.relhasindex AS hasindexes, 
  C.relhasrules AS hasrules, 
  (C.reltriggers > 0) AS hastriggers 
  FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
+  LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
  WHERE C.relkind = 'r';
  
  CREATE VIEW pg_indexes AS 
  SELECT 
  N.nspname AS schemaname, 
  C.relname AS tablename, 
+ T.spcname AS tablespace,
  I.relname AS indexname, 
  pg_get_indexdef(I.oid) AS indexdef 
  FROM pg_index X JOIN pg_class C ON (C.oid = X.indrelid) 
   JOIN pg_class I ON (I.oid = X.indexrelid) 
   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
+  LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
  WHERE C.relkind = 'r' AND I.relkind = 'i';
  
  CREATE VIEW pg_stats AS 

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


Re: [PATCHES] [PATCH] fix libpq mutex initialization for multithreaded

2004-07-12 Thread Bruce Momjian

Patch applied.  Thanks.

---


Manfred Spraul wrote:
> Hi,
> 
> win32 doesn't support a static initializer for mutexes, thus the first 
> user must initialize the lock. The problem are concurrent "first" users 
> - the pthread_mutex_t initialization must be synchronized.
> The current implementation is broken, the attached patches fixes that: 
> mutex_initlock is a spinlock. If the pthread_mutex_t mutex is not 
> initialized, then the spinlock is acquired, if the pthread_mutex_t is 
> initialized if it's not yet initialized and then the spinlock is dropped.
> 
> Again untested due to lack of Visual C++.
> 
> --
> Manfre

> ? GNUmakefile
> ? config.log
> ? config.status
> ? src/Makefile.global
> ? src/include/pg_config.h
> ? src/include/stamp-h
> ? src/port/pg_config_paths.h
> Index: src/interfaces/libpq/fe-connect.c
> ===
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/libpq/fe-connect.c,v
> retrieving revision 1.275
> diff -c -r1.275 fe-connect.c
> *** src/interfaces/libpq/fe-connect.c 19 Jun 2004 04:22:17 -  1.275
> --- src/interfaces/libpq/fe-connect.c 20 Jun 2004 16:38:38 -
> ***
> *** 3193,3202 
>   #ifndef WIN32
>   static pthread_mutex_t singlethread_lock = PTHREAD_MUTEX_INITIALIZER;
>   #else
> ! static pthread_mutex_t singlethread_lock;
> ! static long mutex_initialized = 0;
> ! if (!InterlockedExchange(&mutex_initialized, 1L))
> ! pthread_mutex_init(&singlethread_lock, NULL);
>   #endif
>   if (acquire)
>   pthread_mutex_lock(&singlethread_lock);
> --- 3193,3208 
>   #ifndef WIN32
>   static pthread_mutex_t singlethread_lock = PTHREAD_MUTEX_INITIALIZER;
>   #else
> ! static pthread_mutex_t singlethread_lock = NULL;
> ! static long mutex_initlock = 0;
> ! 
> ! if (singlethread_lock == NULL) {
> ! while(InterlockedExchange(&mutex_initlock, 1) == 1)
> ! /* loop, another thread own the lock */ ;
> ! if (singlethread_lock == NULL)
> ! pthread_mutex_init(&singlethread_lock, NULL);
> ! InterlockedExchange(&mutex_initlock,0);
> ! }
>   #endif
>   if (acquire)
>   pthread_mutex_lock(&singlethread_lock);
> Index: src/interfaces/libpq/fe-secure.c
> ===
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/libpq/fe-secure.c,v
> retrieving revision 1.42
> diff -c -r1.42 fe-secure.c
> *** src/interfaces/libpq/fe-secure.c  19 Jun 2004 04:22:17 -  1.42
> --- src/interfaces/libpq/fe-secure.c  20 Jun 2004 16:38:39 -
> ***
> *** 867,876 
>   #ifndef WIN32
>   static pthread_mutex_t init_mutex = PTHREAD_MUTEX_INITIALIZER;
>   #else
> ! static pthread_mutex_t init_mutex;
> ! static long mutex_initialized = 0L;
> ! if (!InterlockedExchange(&mutex_initialized, 1L))
> ! pthread_mutex_init(&init_mutex, NULL);
>   #endif
>   pthread_mutex_lock(&init_mutex);
>   
> --- 867,882 
>   #ifndef WIN32
>   static pthread_mutex_t init_mutex = PTHREAD_MUTEX_INITIALIZER;
>   #else
> ! static pthread_mutex_t init_mutex = NULL;
> ! static long mutex_initlock = 0;
> ! 
> ! if (init_mutex == NULL) {
> ! while(InterlockedExchange(&mutex_initlock, 1) == 1)
> ! /* loop, another thread own the lock */ ;
> ! if (init_mutex == NULL)
> ! pthread_mutex_init(&init_mutex, NULL);
> ! InterlockedExchange(&mutex_initlock,0);
> ! }
>   #endif
>   pthread_mutex_lock(&init_mutex);
>   

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

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (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: [PATCHES] plperl fixes

2004-07-12 Thread Bruce Momjian

Patch applied.  Thanks.

---


Andrew Dunstan wrote:
> 
> The attached patch, which incorporates the previous one sent and 
> currently unapplied regarding spi_internal.c, makes some additional 
> fixes relating to return types, and also contains the fix for 
> preventing  the use of insecure versions of Safe.pm.
> 
> There is one remaing return case that does not appear to work, namely 
> return of a composite directly in a select, i.e. if  foo returns some 
> composite type, 'select * from foo()' works but 'select foo()' doesn't. 
> We will either fix that or document it as a limitation.
> 
> The function plperl_func_handler is a mess - I will try to get it 
> cleaned up (and split up) in a subsequent patch, time permitting.
> 
> Also, reiterating previous advice - this changes slightly the API for 
> spi_exec_query - the returned object has either 2 or 3 members: 'status' 
> (string) and 'proceesed' (int,- number of rows) and, if rows are 
> returned, 'rows' (array of tuple hashes).
> 
> cheers
> 
> andrew

> Index: plperl.c
> ===
> RCS file: /projects/cvsroot/pgsql-server/src/pl/plperl/plperl.c,v
> retrieving revision 1.45
> diff -c -w -r1.45 plperl.c
> *** plperl.c  1 Jul 2004 20:50:22 -   1.45
> --- plperl.c  7 Jul 2004 15:35:35 -
> ***
> *** 80,85 
> --- 80,86 
>   CommandId   fn_cmin;
>   boollanpltrusted;
>   boolfn_retistuple;  /* true, if function returns tuple */
> + boolfn_retisset;/*true, if function returns set*/
>   Oid ret_oid;/* Oid of returning type */
>   FmgrInforesult_in_func;
>   Oid result_typioparam;
> ***
> *** 95,105 
>* Global data
>**/
>   static int  plperl_firstcall = 1;
>   static PerlInterpreter *plperl_interp = NULL;
>   static HV  *plperl_proc_hash = NULL;
> ! AV *g_row_keys = NULL;
> ! AV *g_column_keys = NULL;
> ! int g_attr_num = 0;
>   
>   /**
>* Forward declarations
> --- 96,108 
>* Global data
>**/
>   static int  plperl_firstcall = 1;
> + static bool plperl_safe_init_done = false;
>   static PerlInterpreter *plperl_interp = NULL;
>   static HV  *plperl_proc_hash = NULL;
> ! static AV  *g_row_keys = NULL;
> ! static AV  *g_column_keys = NULL;
> ! static SV  *srf_perlret=NULL; /*keep returned value*/
> ! static int  g_attr_num = 0;
>   
>   /**
>* Forward declarations
> ***
> *** 215,225 
>* no commas between the next lines please. They are supposed to be
>* one string
>*/
> ! "require Safe; SPI::bootstrap(); use vars qw(%_SHARED);"
> ! "use vars qw($PLContainer); $PLContainer = new Safe('PLPerl');"
> ! 
> "$PLContainer->permit_only(':default');$PLContainer->permit(':base_math');"
> ! "$PLContainer->share(qw[&elog &spi_exec_query &DEBUG &LOG &INFO 
> &NOTICE &WARNING &ERROR %SHARED ]);"
> ! "sub ::mksafefunc { return $PLContainer->reval(qq[sub { $_[0] 
> $_[1]}]); }"
>   "sub ::mkunsafefunc {return eval(qq[ sub { $_[0] $_[1] } ]); }"
>   };
>   
> --- 218,224 
>* no commas between the next lines please. They are supposed to be
>* one string
>*/
> ! "SPI::bootstrap(); use vars qw(%_SHARED);"
>   "sub ::mkunsafefunc {return eval(qq[ sub { $_[0] $_[1] } ]); }"
>   };
>   
> ***
> *** 238,243 
> --- 237,277 
>   
>   }
>   
> + 
> + static void
> + plperl_safe_init(void)
> + {
> + static char *safe_module  =
> + "require Safe; $Safe::VERSION";
> + 
> + static char * safe_ok =
> + "use vars qw($PLContainer); $PLContainer = new Safe('PLPerl');"
> + 
> "$PLContainer->permit_only(':default');$PLContainer->permit(':base_math');"
> + "$PLContainer->share(qw[&elog &spi_exec_query &DEBUG &LOG &INFO 
> &NOTICE &WARNING &ERROR %SHARED ]);"
> + "sub ::mksafefunc { return $PLContainer->reval(qq[sub { $_[0] 
> $_[1]}]); }"
> + ;
> + 
> + static char * safe_bad = 
> + "use vars qw($PLContainer); $PLContainer = new Safe('PLPerl');"
> + 
> "$PLContainer->permit_only(':default');$PLContainer->permit(':base_math');"
> + "$PLContainer->share(qw[&elog &DEBUG &LOG &INFO &NOTICE &WARNING 
> &ERROR %SHARED ]);"
> + "sub 

Re: [PATCHES] pg_autovacuum integration attempt #2

2004-07-12 Thread Tom Lane
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> The autovacuum process still uses libpq to send its queries, which is 
> not the idea behind backend integration.

Sure, but one step at a time.  Getting it auto-launched from the
postmaster is already a good increment in usability, and offhand
I don't see that there's any part of that work that we'd have to
throw away later.

(This is not to say that I like the patch; I haven't reviewed it yet.
But I don't want to reject it just because it's not the final form
of autovacuum.)

regards, tom lane

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


Re: [PATCHES] add missing options to pg_dumpall

2004-07-12 Thread Bruce Momjian

Patch applied.  Thanks.

---



Christopher Kings-Lynne wrote:
> Hi,
> 
> This patch adds the following options to pg_dumpall, to be passed to 
> pg_dump:
> 
> -S, --superuser=NAME
> 
> -O, --no-owner
> 
> -X disable-dollar-quoting, --disable-dollar-quoting
> 
> -X disable-triggers, --disable-triggers
> 
> Chris
> 

[ application/x-gzip is not supported, skipping... ]

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

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (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 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] pg_autovacuum integration attempt #2

2004-07-12 Thread Matthew T. O'Connor
Peter Eisentraut wrote:
Bruce Momjian wrote:
I have added this patch plus your later comments to the patch queue.
The autovacuum process still uses libpq to send its queries, which is 
not the idea behind backend integration.
Actually, I intentionally had pg_autovacuum continue to use libpq based 
Tom's advise.  Please see this thread:

http://archives.postgresql.org/pgsql-hackers/2004-03/msg00931.php
And more specifically, these follow ups:
http://archives.postgresql.org/pgsql-hackers/2004-03/msg00989.php
http://archives.postgresql.org/pgsql-hackers/2004-03/msg00992.php
The short of it is that Tom felt having the autovac daemon continue to 
use libpq keeps "autovac control code still at arms length from the backend"

To me the main benifit of having pg_autovacuum integrated in as a 
backend process is not eliminating libpq from the process, but rather:
* access to GUC, elog (and other things)
* allows autovac to be started and shutdown by the backend based on a 
GUC variable
* allows autovac to have it's own system table to maintain it's data 
across restarts
* eventually should allow vacuum decisions to be based on factors beyond 
the stats system (FSM etc...)

IMHO, the use of libpq is not a bug, it's a feature.
Thoughts?
Matthew
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org


Re: [PATCHES] pg_autovacuum integration attempt #2

2004-07-12 Thread Tom Lane
"Matthew T. O'Connor" <[EMAIL PROTECTED]> writes:
> Actually, I intentionally had pg_autovacuum continue to use libpq based 
> Tom's advise.  Please see this thread:
> http://archives.postgresql.org/pgsql-hackers/2004-03/msg00931.php
> And more specifically, these follow ups:
> http://archives.postgresql.org/pgsql-hackers/2004-03/msg00989.php
> http://archives.postgresql.org/pgsql-hackers/2004-03/msg00992.php

Something seems to have truncated msg00987 in the archives, but I
looked it up in my own mail folder ...

regards, tom lane

--- Forwarded Message

Date:Mon, 22 Mar 2004 16:38:43 -0500
From:Tom Lane <[EMAIL PROTECTED]>
To:  Gavin Sherry <[EMAIL PROTECTED]>
cc:  "Matthew T. O'Connor" <[EMAIL PROTECTED]>,
 Bruce Momjian <[EMAIL PROTECTED]>,
 Christopher Kings-Lynne <[EMAIL PROTECTED]>,
 Peter Eisentraut <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
Subject: Re: [HACKERS] pg_autovacuum next steps 

Gavin Sherry <[EMAIL PROTECTED]> writes:
> So, do we want a static time, a GUC controlled time or some time which is
> modified by pg_autovacuum's/stat's collector's knowledge of the amount of
> work which goes on in any given database?

>From the point of view of the postmaster a GUC-controlled delay would
seem like the best thing.  We could discuss having the autovacuum code
try to feed back adjustments in the delay, but remember that one of the
golden virtues for the postmaster proper is simplicity; that translates
directly to robustness.  We don't want the postmaster engaging in
anything complicated that could potentially lock it up or crash it due
to a bug.

Possibly this point could be finessed by a two-level structure, that is,
postmaster launches autovacuum daemon (which is not itself a backend)
and that in turn launches backends to do the real per-database work.
The postmaster's only subsequent involvement is restarting the autovac
daemon if it dies.  The autovac daemon can be as complex as you want.

This nice-sounding arrangement is probably not directly workable because
of the fact that the postmaster has no good way to know about or control
backends if they aren't its direct children.  Perhaps the autovac daemon
*should* use libpq, that is, not fork backends but connect via the
postmaster each time it wants to run a backend.  Then the backends are
ordinary children of the postmaster and everything acts normally.
(This could amount to using the existing autovac code, and simply adding
a frammish to the postmaster to autospawn the autovac daemon as a
non-backend child process.)

regards, tom lane

--- End of Forwarded Message


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


Re: [PATCHES] nested xacts: update password file

2004-07-12 Thread Bruce Momjian

Alvaro, you call GetParentTransactionId(), but I see not definition for
it in the code.

---

Alvaro Herrera wrote:
> An untested patch to update the password file.
> 
> Something that bugged me a lot is that I tried to find the format of the
> file for testing the patch, and I couldn't find anything anywhere in the
> docs.  Apparently the docs for the file were ripped with the docs for
> the pg_passwd utility when it was ripped before the 7.3 release.
> 
> -- 
> Alvaro Herrera ()
> "Some men are heterosexual, and some are bisexual, and some
> men don't think about sex at all... they become lawyers" (Woody Allen)

[ Attachment, skipping... ]

> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faqs/FAQ.html

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (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: [PATCHES] nested xacts: update password file

2004-07-12 Thread Alvaro Herrera
On Mon, Jul 12, 2004 at 12:05:40PM -0400, Bruce Momjian wrote:
> 
> Alvaro, you call GetParentTransactionId(), but I see not definition for
> it in the code.

Let me include this patch in the next patch I'll submit shortly.

-- 
Alvaro Herrera ()
"I suspect most samba developers are already technically insane...
Of course, since many of them are Australians, you can't tell." (L. Torvalds)


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

   http://archives.postgresql.org


Re: [PATCHES] nested xacts: update password file

2004-07-12 Thread Bruce Momjian

OK.  Also, I would like you to post a list of the open items you have so
we can all know the status.

---

Alvaro Herrera wrote:
> On Mon, Jul 12, 2004 at 12:05:40PM -0400, Bruce Momjian wrote:
> > 
> > Alvaro, you call GetParentTransactionId(), but I see not definition for
> > it in the code.
> 
> Let me include this patch in the next patch I'll submit shortly.
> 
> -- 
> Alvaro Herrera ()
> "I suspect most samba developers are already technically insane...
> Of course, since many of them are Australians, you can't tell." (L. Torvalds)
> 

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

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


Re: [PATCHES] nested xacts: update password file

2004-07-12 Thread Bruce Momjian

Patch withdrawn by author.

---

Alvaro Herrera wrote:
> An untested patch to update the password file.
> 
> Something that bugged me a lot is that I tried to find the format of the
> file for testing the patch, and I couldn't find anything anywhere in the
> docs.  Apparently the docs for the file were ripped with the docs for
> the pg_passwd utility when it was ripped before the 7.3 release.
> 
> -- 
> Alvaro Herrera ()
> "Some men are heterosexual, and some are bisexual, and some
> men don't think about sex at all... they become lawyers" (Woody Allen)

[ Attachment, skipping... ]

> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faqs/FAQ.html

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (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: [PATCHES] nested xacts: update password file

2004-07-12 Thread Tom Lane
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Alvaro, you call GetParentTransactionId(), but I see not definition for
> it in the code.

It looks like Alvaro forgot to include diffs for xact.c in that patch.
I imagine what he had in mind was to add such a function to xact.c.

I would like to counsel *not* doing that, mainly because it wouldn't
have any well-defined result when not inside a subxact.  What we are
doing for other end-of-subxact functions is to pass the parent XID
explicitly as a parameter; it seems best to me to adopt that approach
for the password function too.

Another minor gripe is the comment that says this logic matches inval.c;
that's not true anymore, I think.

Other than that the patch looks okay to me, but I'll wait for Alvaro
to submit a combined patch rather than adjusting xact.c underneath him.

A todo item for you, Bruce, is to put back the missing documentation
that Alvaro complained of:

>> Something that bugged me a lot is that I tried to find the format of the
>> file for testing the patch, and I couldn't find anything anywhere in the
>> docs.  Apparently the docs for the file were ripped with the docs for
>> the pg_passwd utility when it was ripped before the 7.3 release.

regards, tom lane

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


Re: [PATCHES] nested xacts: update password file

2004-07-12 Thread Bruce Momjian
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Alvaro, you call GetParentTransactionId(), but I see not definition for
> > it in the code.
> 
> It looks like Alvaro forgot to include diffs for xact.c in that patch.
> I imagine what he had in mind was to add such a function to xact.c.
> 
> I would like to counsel *not* doing that, mainly because it wouldn't
> have any well-defined result when not inside a subxact.  What we are
> doing for other end-of-subxact functions is to pass the parent XID
> explicitly as a parameter; it seems best to me to adopt that approach
> for the password function too.
> 
> Another minor gripe is the comment that says this logic matches inval.c;
> that's not true anymore, I think.
> 
> Other than that the patch looks okay to me, but I'll wait for Alvaro
> to submit a combined patch rather than adjusting xact.c underneath him.

Alvaro is resubmitting.

> A todo item for you, Bruce, is to put back the missing documentation
> that Alvaro complained of:
> 
> >> Something that bugged me a lot is that I tried to find the format of the
> >> file for testing the patch, and I couldn't find anything anywhere in the
> >> docs.  Apparently the docs for the file were ripped with the docs for
> >> the pg_passwd utility when it was ripped before the 7.3 release.

I was confused by this.  What docs for the password file did we have?

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

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


Re: [PATCHES] nested xacts: update password file

2004-07-12 Thread Bruce Momjian

OK, but why would we document the contents of a file that are not to be
modified by the user?

---

Alvaro Herrera wrote:
> On Mon, Jul 12, 2004 at 02:18:40PM -0400, Bruce Momjian wrote:
> 
> > > >> Something that bugged me a lot is that I tried to find the format of the
> > > >> file for testing the patch, and I couldn't find anything anywhere in the
> > > >> docs.  Apparently the docs for the file were ripped with the docs for
> > > >> the pg_passwd utility when it was ripped before the 7.3 release.
> > 
> > I was confused by this.  What docs for the password file did we have?
> 
> I see this in 7.2 docs.  This is not mentioned anywhere in current docs.
> Does it work with other auth mechanisms (md5, crypt)?
> 
>   
>The format of a text password file is one entry per line; the fields
>of each entry are separated by colons. The first field is the user
>name, the second field is the encrypted password. Other fields are
>ignored (to allow password files to be shared between applications
>that use similar formats). pg_passwd
>enables users to interactively add entries to such a file, to alter
>passwords of existing entries, and to encrypt such passwords.
>   
> 
> [...]
> 
>   
>To make use of this password file, put a line like the following in
>pg_hba.conf:
> 
> 
> host  mydb 133.65.96.250   255.255.255.255 password passwords
> 
> 
>which would allow access to database mydb from host 133.65.96.250 using
>the passwords listed in the passwords file (and
>only to the users listed in that file).
>   
> 
>   
>
> It is also useful to have entries in a password file with empty
> password fields. (This is different from an empty password.) Such
> entries allow you to restrict users who can access the system. These
> entries cannot be managed by pg_passwd,
> but you can edit password files manually.
>
>   
> 
> 
> -- 
> Alvaro Herrera ()
> "La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
> Escriba un gui?n que no toque nada para no causar da?os." (Jakob Nielsen)
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (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/faqs/FAQ.html


Re: [PATCHES] nested xacts: update password file

2004-07-12 Thread Alvaro Herrera
On Mon, Jul 12, 2004 at 02:18:40PM -0400, Bruce Momjian wrote:

> > >> Something that bugged me a lot is that I tried to find the format of the
> > >> file for testing the patch, and I couldn't find anything anywhere in the
> > >> docs.  Apparently the docs for the file were ripped with the docs for
> > >> the pg_passwd utility when it was ripped before the 7.3 release.
> 
> I was confused by this.  What docs for the password file did we have?

I see this in 7.2 docs.  This is not mentioned anywhere in current docs.
Does it work with other auth mechanisms (md5, crypt)?

  
   The format of a text password file is one entry per line; the fields
   of each entry are separated by colons. The first field is the user
   name, the second field is the encrypted password. Other fields are
   ignored (to allow password files to be shared between applications
   that use similar formats). pg_passwd
   enables users to interactively add entries to such a file, to alter
   passwords of existing entries, and to encrypt such passwords.
  

[...]

  
   To make use of this password file, put a line like the following in
   pg_hba.conf:


host  mydb 133.65.96.250   255.255.255.255 password passwords


   which would allow access to database mydb from host 133.65.96.250 using
   the passwords listed in the passwords file (and
   only to the users listed in that file).
  

  
   
It is also useful to have entries in a password file with empty
password fields. (This is different from an empty password.) Such
entries allow you to restrict users who can access the system. These
entries cannot be managed by pg_passwd,
but you can edit password files manually.
   
  


-- 
Alvaro Herrera ()
"La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)


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


Re: [PATCHES] nested xacts: update password file

2004-07-12 Thread Tom Lane
Bruce Momjian <[EMAIL PROTECTED]> writes:
> OK, but why would we document the contents of a file that are not to be
> modified by the user?

Also note that the 7.2 docs are out of date anyway (looks to me like
we use spaces not colons as column separators...)

regards, tom lane

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

   http://archives.postgresql.org


Re: [PATCHES] nested xacts: update password file

2004-07-12 Thread Alvaro Herrera
On Mon, Jul 12, 2004 at 02:31:37PM -0400, Bruce Momjian wrote:

> OK, but why would we document the contents of a file that are not to be
> modified by the user?

But how is the file used?  Where do I put the file, what do I put in
pg_hba.conf to use the file?  Can I have several files, one per
pg_hba.conf entry?  Can I use multiple files with a single pg_hba.conf
entry?  What happens if I have a username that has the separator in it?

-- 
Alvaro Herrera ()
"Los dioses no protegen a los insensatos.  Éstos reciben protección de
otros insensatos mejor dotados" (Luis Wu, Mundo Anillo)


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


Re: [PATCHES] nested xacts: update password file

2004-07-12 Thread Bruce Momjian
Alvaro Herrera wrote:
> On Mon, Jul 12, 2004 at 02:31:37PM -0400, Bruce Momjian wrote:
> 
> > OK, but why would we document the contents of a file that are not to be
> > modified by the user?
> 
> But how is the file used?  Where do I put the file, what do I put in
> pg_hba.conf to use the file?  Can I have several files, one per
> pg_hba.conf entry?  Can I use multiple files with a single pg_hba.conf
> entry?  What happens if I have a username that has the separator in it?

We no longer have the capability for external password files, which is
what the 7.2 docs were talking about.  We removed it when we went to
encrypted MD5 password and pg_hba.conf entries where you can reference
external lists of users and groups.

The file you were touching is a cache of usernames written by backends
modifing the pg_shadow table and read by the postmaster.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (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 6: Have you searched our list archives?

   http://archives.postgresql.org


[PATCHES] More canonicalization fixes

2004-07-12 Thread Bruce Momjian
I just applied this new version of canonicalize_path():

/*
 * Make all paths look like Unix
 */
void
canonicalize_path(char *path)
{
#ifdef WIN32
/*
 * The Windows command processor will accept suitably quoted paths
 * with forward slashes, but barfs badly with mixed forward and back
 * slashes.
 */
char   *p;

for (p = path; *p; p++)
{
if (*p == '\\')
*p = '/';
}

/*  In Win32, if you do:
 *  prog.exe "a b" "\c\d\"
 *  the system will pass \c\d" as argv[2].
 */
if (p > path && *(p-1) == '"')
*(p-1) = '/';
#endif

/*
 *  Removing the trailing slash on a path means we never get
 *  ugly double slashes.  Don't remove a leading slash, though.
 *  Also, Win32 can't stat() a directory with a trailing slash.
 */
trim_trailing_separator(path);
}

The new thing Magnus found was this in Win32:

prog.exe "a b" "\c\d\"

returns \c\d" as argv[2]

Quite amazing. The fix on Win32 is to convert a trailing double-quote to
a slash.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (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: [PATCHES] nested xacts: update password file

2004-07-12 Thread Tom Lane
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> But how is the file used?  Where do I put the file, what do I put in
> pg_hba.conf to use the file?  Can I have several files, one per
> pg_hba.conf entry?  Can I use multiple files with a single pg_hba.conf
> entry?  What happens if I have a username that has the separator in it?

You don't do anything with it directly.  Just create some users and
assign them some passwords and see if the file updates (or even
more directly, try to log in using password auth).  It's just a
transmission mechanism to put the important parts of pg_shadow and
pg_group in a place where the postmaster can read 'em.

regards, tom lane

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


Re: [PATCHES] nested xacts: update password file

2004-07-12 Thread Alvaro Herrera
On Mon, Jul 12, 2004 at 03:19:43PM -0400, Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > On Mon, Jul 12, 2004 at 02:31:37PM -0400, Bruce Momjian wrote:
> > 
> > > OK, but why would we document the contents of a file that are not to be
> > > modified by the user?
> > 
> > But how is the file used?  Where do I put the file, what do I put in
> > pg_hba.conf to use the file?  Can I have several files, one per
> > pg_hba.conf entry?  Can I use multiple files with a single pg_hba.conf
> > entry?  What happens if I have a username that has the separator in it?
> 
> We no longer have the capability for external password files, which is
> what the 7.2 docs were talking about.  We removed it when we went to
> encrypted MD5 password and pg_hba.conf entries where you can reference
> external lists of users and groups.
> 
> The file you were touching is a cache of usernames written by backends
> modifing the pg_shadow table and read by the postmaster.

Oh, I see!  Thanks for the clarification.

-- 
Alvaro Herrera ()
"XML!" Exclaimed C++.  "What are you doing here? You're not a programming
language."
"Tell that to the people who use me," said XML.


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


Re: [PATCHES] patch for different join result order on regression test

2004-07-12 Thread Bruce Momjian

Done.

---

Andrew Dunstan wrote:
> Andrew Dunstan wrote:
> 
> >
> > This resultmap patch and regression test result file allow the join 
> > regression test to succeed under Mingw.
> >
> >
> 
> 
> Could someone please add the join result I posted in the original of 
> this email (on May 10th) as src/test/regress/expected/join_1.out?
> 
> This is the one test I am failing on Win32.
> 
> Fixup patch for pg_regress.sh will be sent shortly, allowing "make 
> check" to run cleanly.
> 
> cheers
> 
> andrew
> 
> 
> 
> ---(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
  [EMAIL PROTECTED]   |  (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: [PATCHES] has_tablespace_privilege #2

2004-07-12 Thread Bruce Momjian

Patch applied.  Thanks.

---


Christopher Kings-Lynne wrote:
> Sorry - the last version didn't make all the necessary doc changes. 
> Apply this instead.
> 
> Chris
> 

[ application/x-gzip is not supported, skipping... ]

> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
>http://archives.postgresql.org

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (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: [PATCHES] Include tablespace information in psql \d footers

2004-07-12 Thread Bruce Momjian

Patch applied.  Thanks.

I removed the display of tablespaces for sequences and toast tables:

test=> \d test
 Table "public.test"
 Column |  Type   | Modifiers
+-+---
 x  | integer |

test=> \d test2
 Table "public.test2"
 Column |  Type   | Modifiers
+-+---
 x  | integer |
Tablespace:
"tmp"

Why is the tablespace name printed on a separate line?

---



Gavin Sherry wrote:
> On Fri, 25 Jun 2004, Tom Lane wrote:
> 
> > Gavin Sherry <[EMAIL PROTECTED]> writes:
> > > Attached.
> >
> > Couple of problems with this:
> >
> > 1. Don't #ifndef WIN32 it.  In the first place, we might have tablespace
> > support on Windows later, and in the second place, even if psql is
> > running on Windows that doesn't mean the server it's talking to is.
> 
> Oops.
> 
> >
> > 2. Printing "pg_default" when reltblspace is zero is wrong.  Get the
> > database's default tablespace and print that.  Or perhaps better,
> > don't print anything at all; that would avoid cluttering the display
> > for people who don't use tablespaces.
> 
> Ahhh yes of course. Attached is a patch with docs which excludes reporting
> of the default tablespace.
> 
> Gavin

Content-Description: 

[ Attachment, skipping... ]

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

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (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


[PATCHES] Remove confusing commented-defaullts from postgresql.conf

2004-07-12 Thread Greg Sabino Mullane

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message


I know we never reached a full consensus on hackers about the 
full solution to the postgresql.conf file, but I think everyone 
agreed that having commented-out "default" values was a bad thing, 
which caused confusion among even seasoned pg people. Thus, the 
patch below which simply removes the starting '#' from all settings. 
Forgive the lack of -c to the diff: I figured saving space was 
more important as the context is not needed in this case.

--
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200407122051




Index: postgresql.conf.sample
===
RCS file: 
/projects/cvsroot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.115
diff -r1.115 postgresql.conf.sample
5c5
< # This file consists of lines of the form:
---
> # This file consists of lines of the form: GREG
11,12c11
< # allowed values can be found in the PostgreSQL documentation. The
< # commented-out settings shown in this file represent the default values.
---
> # allowed values can be found in the PostgreSQL documentation. 
28,31c27,30
< # pgdata = '/usr/local/pgsql/data'# use data in another directory
< # hba_conf = '/etc/pgsql/pg_hba.conf' # use hba info in another 
directory
< # ident_conf = '/etc/pgsql/pg_ident.conf' # use ident info in another directory
< # external_pidfile= '/var/run/postgresql.pid' # write an extra pid file
---
> pgdata = '/usr/local/pgsql/data'  # use data in another directory
> hba_conf = '/etc/pgsql/pg_hba.conf'   # use hba info in another directory
> ident_conf = '/etc/pgsql/pg_ident.conf'   # use ident info in another directory
> external_pidfile= '/var/run/postgresql.pid'   # write an extra pid file
40c39
< #listen_addresses = 'localhost'   # what IP interface(s) to listen on; 
---
> listen_addresses = 'localhost'# what IP interface(s) to listen on; 
42,43c41,42
< #port = 5432
< #max_connections = 100
---
> port = 5432
> max_connections = 100
47,51c46,50
< #superuser_reserved_connections = 2
< #unix_socket_directory = ''
< #unix_socket_group = ''
< #unix_socket_permissions = 0777   # octal
< #rendezvous_name = '' # defaults to the computer name
---
> superuser_reserved_connections = 2
> unix_socket_directory = ''
> unix_socket_group = ''
> unix_socket_permissions = 0777# octal
> rendezvous_name = ''  # defaults to the computer name
55,59c54,58
< #authentication_timeout = 60  # 1-600, in seconds
< #ssl = false
< #password_encryption = true
< #krb_server_keyfile = ''
< #db_user_namespace = false
---
> authentication_timeout = 60   # 1-600, in seconds
> ssl = false
> password_encryption = true
> krb_server_keyfile = ''
> db_user_namespace = false
68,77c67,76
< #shared_buffers = 1000# min 16, at least max_connections*2, 8KB each
< #work_mem = 1024  # min 64, size in KB
< #maintenance_work_mem = 16384 # min 1024, size in KB
< #max_stack_depth = 2048   # min 100, size in KB
< 
< #vacuum_cost_page_hit = 1 # 0-1 credits
< #vacuum_cost_page_miss = 10   # 0-1 credits
< #vacuum_cost_page_dirty = 20  # 0-1 credits
< #vacuum_cost_limit = 200  # 0-1 credits
< #vacuum_cost_naptime = 50 # 0-1000 milliseconds
---
> shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each
> work_mem = 1024   # min 64, size in KB
> maintenance_work_mem = 16384  # min 1024, size in KB
> max_stack_depth = 2048# min 100, size in KB
> 
> vacuum_cost_page_hit = 1  # 0-1 credits
> vacuum_cost_page_miss = 10# 0-1 credits
> vacuum_cost_page_dirty = 20   # 0-1 credits
> vacuum_cost_limit = 200   # 0-1 credits
> vacuum_cost_naptime = 50  # 0-1000 milliseconds
80,82c79,81
< #bgwriter_delay = 200 # 10-5000 milliseconds
< #bgwriter_percent = 1 # 0-100% of dirty buffers
< #bgwriter_maxpages = 100  # 1-1000 buffers max at once
---
> bgwriter_delay = 200  # 10-5000 milliseconds
> bgwriter_percent = 1  # 0-100% of dirty buffers
> bgwriter_maxpages = 100   # 1-1000 buffers max at once
86,87c85,86
< #max_fsm_pages = 2# min max_fsm_relations*16, 6 bytes each
< #max_fsm_relations = 1000 # min 100, ~50 bytes each
---
> max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each
> max_fsm_relations = 1000  # min 100, ~50 bytes each
91,92c90,91
< #max_files_per_process = 1000 # min 25
< #preload_libraries = ''
---
> max_files_per_process = 1000  # min 25
> preload_libraries = ''
101,102c100,101
< #fsync = true # turns forced synchronization on or off
< #wal_sync_method = fsync  # the default varies across platforms:
---
> fsync = true  # turns forced synchronization on or off
> wal_sync_method = fsync   # the d

Re: [PATCHES] Include tablespace information in psql \d footers

2004-07-12 Thread Bruce Momjian
Bruce Momjian wrote:
> 
> Patch applied.  Thanks.
> 
> I removed the display of tablespaces for sequences and toast tables:
>   
>   test=> \d test
>Table "public.test"
>Column |  Type   | Modifiers
>   +-+---
>x  | integer |
>   
>   test=> \d test2
>Table "public.test2"
>Column |  Type   | Modifiers
>   +-+---
>x  | integer |
>   Tablespace:
>   "tmp"
> 
> Why is the tablespace name printed on a separate line?

OK, I moved the tablespace name up on to the same line just like Has
oids:

test=> \d+ test3
Table "public.test3"
 Column |  Type   | Modifiers | Description
+-+---+-
 x  | integer | not null  |
Indexes:
"test3_pkey" PRIMARY KEY, btree (x)
Contains OIDs: yes
Tablespace: "tmp"

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (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: [PATCHES] Remove confusing commented-defaullts from postgresql.conf

2004-07-12 Thread Bruce Momjian

Uh, it is my understanding that all lines can't be uncommented.  Some of
the lines have defaults that are computed, like fsync_method.

Anyway, we are too busy to consider this for 7.5 unless someone wants to
research all these issues.

---

Greg Sabino Mullane wrote:
[ There is text before PGP section. ]
> 
[ PGP not available, raw data follows ]
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> NotDashEscaped: You need GnuPG to verify this message
> 
> 
> I know we never reached a full consensus on hackers about the 
> full solution to the postgresql.conf file, but I think everyone 
> agreed that having commented-out "default" values was a bad thing, 
> which caused confusion among even seasoned pg people. Thus, the 
> patch below which simply removes the starting '#' from all settings. 
> Forgive the lack of -c to the diff: I figured saving space was 
> more important as the context is not needed in this case.
> 
> --
> Greg Sabino Mullane [EMAIL PROTECTED]
> PGP Key: 0x14964AC8 200407122051
> 
> 
> 
> 
> Index: postgresql.conf.sample
> ===
> RCS file: 
> /projects/cvsroot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v
> retrieving revision 1.115
> diff -r1.115 postgresql.conf.sample
> 5c5
> < # This file consists of lines of the form:
> ---
> > # This file consists of lines of the form: GREG
> 11,12c11
> < # allowed values can be found in the PostgreSQL documentation. The
> < # commented-out settings shown in this file represent the default values.
> ---
> > # allowed values can be found in the PostgreSQL documentation. 
> 28,31c27,30
> < # pgdata = '/usr/local/pgsql/data'  # use data in another directory
> < # hba_conf = '/etc/pgsql/pg_hba.conf'   # use hba info in another 
> directory
> < # ident_conf = '/etc/pgsql/pg_ident.conf'   # use ident info in another directory
> < # external_pidfile= '/var/run/postgresql.pid'   # write an extra pid file
> ---
> > pgdata = '/usr/local/pgsql/data'# use data in another directory
> > hba_conf = '/etc/pgsql/pg_hba.conf' # use hba info in another 
> > directory
> > ident_conf = '/etc/pgsql/pg_ident.conf' # use ident info in another directory
> > external_pidfile= '/var/run/postgresql.pid' # write an extra pid file
> 40c39
> < #listen_addresses = 'localhost' # what IP interface(s) to listen on; 
> ---
> > listen_addresses = 'localhost'  # what IP interface(s) to listen on; 
> 42,43c41,42
> < #port = 5432
> < #max_connections = 100
> ---
> > port = 5432
> > max_connections = 100
> 47,51c46,50
> < #superuser_reserved_connections = 2
> < #unix_socket_directory = ''
> < #unix_socket_group = ''
> < #unix_socket_permissions = 0777 # octal
> < #rendezvous_name = ''   # defaults to the computer name
> ---
> > superuser_reserved_connections = 2
> > unix_socket_directory = ''
> > unix_socket_group = ''
> > unix_socket_permissions = 0777  # octal
> > rendezvous_name = ''# defaults to the computer name
> 55,59c54,58
> < #authentication_timeout = 60# 1-600, in seconds
> < #ssl = false
> < #password_encryption = true
> < #krb_server_keyfile = ''
> < #db_user_namespace = false
> ---
> > authentication_timeout = 60 # 1-600, in seconds
> > ssl = false
> > password_encryption = true
> > krb_server_keyfile = ''
> > db_user_namespace = false
> 68,77c67,76
> < #shared_buffers = 1000  # min 16, at least max_connections*2, 8KB each
> < #work_mem = 1024# min 64, size in KB
> < #maintenance_work_mem = 16384   # min 1024, size in KB
> < #max_stack_depth = 2048 # min 100, size in KB
> < 
> < #vacuum_cost_page_hit = 1   # 0-1 credits
> < #vacuum_cost_page_miss = 10 # 0-1 credits
> < #vacuum_cost_page_dirty = 20# 0-1 credits
> < #vacuum_cost_limit = 200# 0-1 credits
> < #vacuum_cost_naptime = 50   # 0-1000 milliseconds
> ---
> > shared_buffers = 1000   # min 16, at least max_connections*2, 8KB each
> > work_mem = 1024 # min 64, size in KB
> > maintenance_work_mem = 16384# min 1024, size in KB
> > max_stack_depth = 2048  # min 100, size in KB
> > 
> > vacuum_cost_page_hit = 1# 0-1 credits
> > vacuum_cost_page_miss = 10  # 0-1 credits
> > vacuum_cost_page_dirty = 20 # 0-1 credits
> > vacuum_cost_limit = 200 # 0-1 credits
> > vacuum_cost_naptime = 50# 0-1000 milliseconds
> 80,82c79,81
> < #bgwriter_delay = 200   # 10-5000 milliseconds
> < #bgwriter_percent = 1   # 0-100% of dirty buffers
> < #bgwriter_maxpages = 100# 1-1000 buffers max at once
> ---
> > bgwriter_delay = 200# 10-5000 milliseconds
> > bgwriter_percent = 1# 0-100% of dirty buffers
> > bgwriter_maxpages = 100 # 1-1000 buffers max at once
> 86,87c85,86
> < #max_fsm_pages = 2

Re: [PATCHES] pg_dump owner and acl fix #2

2004-07-12 Thread Bruce Momjian

Patch applied.  Thanks.

Change pg_dump to use ALTER OWNER commands instead of SET SESSION
AUTHORIZATION commands by default.  Move all GRANT and REVOKE commands
to the end of the dump to avoid restore failures in several situations.
Bring back --use-set-session-authorization option to get previous SET
behaviour

---


Christopher Kings-Lynne wrote:
> OK,
> 
> This version works wonderfully on my production database.
> 
> Chris
> 

[ application/x-gzip is not supported, skipping... ]

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

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

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


Re: [PATCHES] Include tablespace information in psql \d footers

2004-07-12 Thread Gavin Sherry
On Mon, 12 Jul 2004, Bruce Momjian wrote:

> Bruce Momjian wrote:
> >
> > Patch applied.  Thanks.
> >
> > I removed the display of tablespaces for sequences and toast tables:
> >
> > test=> \d test
> >  Table "public.test"
> >  Column |  Type   | Modifiers
> > +-+---
> >  x  | integer |
> >
> > test=> \d test2
> >  Table "public.test2"
> >  Column |  Type   | Modifiers
> > +-+---
> >  x  | integer |
> > Tablespace:
> > "tmp"
> >
> > Why is the tablespace name printed on a separate line?
>
> OK, I moved the tablespace name up on to the same line just like Has
> oids:

Works for me.

Thanks,

Gavin

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


Re: [PATCHES] Remove confusing commented-defaullts from postgresql.conf

2004-07-12 Thread Tom Lane
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Uh, it is my understanding that all lines can't be uncommented.  Some of
> the lines have defaults that are computed, like fsync_method.

Yes, fsync_method shouldn't be uncommented unless you're prepared to put
in the same default value presently computed in xlog.c.  (Now that
initdb is in C, it might not be unreasonable to move the #ifdef hacking
that's in xlog.c over to initdb...)

Also you'd need to make some other adjustments in initdb; IIRC it does
sed-like substitutions that will break if the input is not just so.
I don't recall at the moment but there may be some things of the same
ilk done during "make install".  And check the documentation to see if
any discussion needs updated.

> Anyway, we are too busy to consider this for 7.5 unless someone wants to
> research all these issues.

No reason Greg can't do it if he wants to ... but the .sample file
itself is certainly not all that needs changing.

regards, tom lane

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