Re: [PATCHES] pg_autovacuum integration attempt #2
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
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
[PATCHES] Show tablespace name in pg_tables and pg_indexes
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
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
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
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 ::mksafefunc { return $PLContainer-reval(qq[sub { + elog(ERROR,'trusted perl functions disabled - please upgrade perl Safe module to at least 2.09');}]); } +
Re: [PATCHES] pg_autovacuum integration attempt #2
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
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
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
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
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 (alvherre[a]dcc.uchile.cl) 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
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 (alvherre[a]dcc.uchile.cl) 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
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 (alvherre[a]dcc.uchile.cl) 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
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 (alvherre[a]dcc.uchile.cl) 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
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
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
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 (alvherre[a]dcc.uchile.cl) 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
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
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
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
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 (alvherre[a]dcc.uchile.cl) 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
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
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
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
-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 default varies across platforms: 104c103 #wal_buffers = 8 # min
Re: [PATCHES] Include tablespace information in psql \d footers
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
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 # 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,
Re: [PATCHES] pg_dump owner and acl fix #2
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
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
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