[PATCHES] postgresql.conf formatting cleanup
I have applied the following patch to improve the formating of postgresql.conf.sample. -- 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 Index: src/backend/utils/misc/postgresql.conf.sample === RCS file: /cvsroot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v retrieving revision 1.84 diff -c -c -r1.84 postgresql.conf.sample *** src/backend/utils/misc/postgresql.conf.sample 14 Jul 2003 20:00:22 - 1.84 --- src/backend/utils/misc/postgresql.conf.sample 18 Jul 2003 19:14:18 - *** *** 1,4 ! # # PostgreSQL configuration file # - # --- 1,4 ! # - # PostgreSQL configuration file # - # *** *** 21,36 # "pg_ctl reload". ! # ! ! ! # ! # Connection Parameters ! # ! # CONNECTIONS AND AUTHENTICATION ! # -- ! # Connection Settings #tcpip_socket = false #max_connections = 32 --- 21,31 # "pg_ctl reload". ! #--- # CONNECTIONS AND AUTHENTICATION ! #--- ! ! # - Connection Settings - #tcpip_socket = false #max_connections = 32 *** *** 44,50 #unix_socket_permissions = 0777 # octal #virtual_host = '' ! # Security & Authentication #authentication_timeout = 60 # 1-600, in seconds #ssl = false --- 39,45 #unix_socket_permissions = 0777 # octal #virtual_host = '' ! # - Security & Authentication - #authentication_timeout = 60 # 1-600, in seconds #ssl = false *** *** 53,87 #db_user_namespace = false # RESOURCE USAGE (except WAL) ! # - ! # Memory #shared_buffers = 64 # min 16, at least max_connections*2, 8KB each #sort_mem = 1024 # min 64, size in KB #vacuum_mem = 8192# min 1024, size in KB ! # Free Space Map #max_fsm_pages = 2# min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~50 bytes each ! # Kernel Resource Usage #max_files_per_process = 1000 # min 25 #preload_libraries = '' # WRITE AHEAD LOG ! # - ! # Settings #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync # the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8 # min 4, 8KB each ! # Checkpoints #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds --- 48,86 #db_user_namespace = false + #--- # RESOURCE USAGE (except WAL) ! #--- ! ! # - Memory - #shared_buffers = 64 # min 16, at least max_connections*2, 8KB each #sort_mem = 1024 # min 64, size in KB #vacuum_mem = 8192# min 1024, size in KB ! # - Free Space Map - #max_fsm_pages = 2# min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~50 bytes each ! # - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 #preload_libraries = '' + #--- # WRITE AHEAD LOG ! #--- ! ! # - Settings - #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync # the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8 # min 4, 8KB each ! # - Checkpoints - #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds *** *** 90,98 #commit_siblings = 5 # range 1-1000 # QUERY TUNING ! # ! # Planner Method Enabling #enable_hashagg = true #enable_hashjoin = true --- 89,99 #commit_siblings = 5 # range 1-1000 + #--- # QUERY TUNING ! #--- ! ! # - Planne
[PATCHES] nitpick consistency patch for pg_dump.c
This is a totally trivial patch for something that was a very minor nit that
annoyed me the other day while I was documenting my current project. It
makes pg_dump use the same layout for types as for tables, by putting "\n\t"
before the first field and "\n" before the final ");"
Can't really justify this too much except to say I had an itch and I
scratched it ;-)
cheers
andrew
*** pg_dump.c~ 2003-06-25 00:08:19.0 -0400
--- pg_dump.c 2003-07-17 15:34:52.0 -0400
***
*** 3412,3418
i_attname = PQfnumber(res, "attname");
i_atttypdefn = PQfnumber(res, "atttypdefn");
! appendPQExpBuffer(q, "CREATE TYPE %s AS (",
fmtId(tinfo->typname));
for (i = 0; i < ntups; i++)
--- 3412,3418
i_attname = PQfnumber(res, "attname");
i_atttypdefn = PQfnumber(res, "atttypdefn");
! appendPQExpBuffer(q, "CREATE TYPE %s AS (\n",
fmtId(tinfo->typname));
for (i = 0; i < ntups; i++)
***
*** 3423,3433
attname = PQgetvalue(res, i, i_attname);
atttypdefn = PQgetvalue(res, i, i_atttypdefn);
! if (i > 0)
! appendPQExpBuffer(q, ",\n\t");
! appendPQExpBuffer(q, "%s %s", fmtId(attname), atttypdefn);
}
! appendPQExpBuffer(q, ");\n");
/*
* DROP must be fully qualified in case same name appears in
--- 3423,3431
attname = PQgetvalue(res, i, i_attname);
atttypdefn = PQgetvalue(res, i, i_atttypdefn);
! appendPQExpBuffer(q, "\n\t%s %s", fmtId(attname),
atttypdefn);}
! appendPQExpBuffer(q, "\n);\n");
/*
* DROP must be fully qualified in case same name appears in
---(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] Adding Rendezvous support to postmaster
Here is a patch that adds a Rendezvous GUC variable to set the Rendezvous name. Chris, would you please test this and let me know how it works. I know we are past cutoff, but I want to get Rendezvous completely functional. I didn't bother with conditionally including it in postgresql.conf because we don't do that with other options that aren't enabled by default, like SSL and Kerberos. --- Chris Campbell wrote: > On Wednesday, Jun 11, 2003, at 10:43 US/Eastern, Bruce Momjian wrote: > > > Bruce Momjian wrote: > >> Tom Lane wrote: > >>> Bruce Momjian <[EMAIL PROTECTED]> writes: > I also _didn't_ add the Rendezvous GUC variable, so we default to > the > host name. If there is need, we can add it later. > >>> > >>> How do you figure there is not need for it? What about running more > >>> than one postmaster at a time? > >> > >> No one brought up that idea, and Chris agreed we could try it without > >> it. Chris, is that an issue? I see the port number in the Rendezvous > >> function call: > > Rendezvous advertises the port number of the service, yes, but the > service name itself (which is usually related to the host name) MUST be > unique. So if there are two postmasters running on the same machine, > the first one will be advertised, and when the second one tries to > register to be advertised, it will silently fail to register. It will > still work just fine as a postmaster process, but it won't be > advertised. > > This is identical to the situation where there are two machines on the > same network with identical Rendezvous names -- the second one to > attempt to register a service with that name will silently fail. > > Just to reassure you: nothing will break if the second postmaster fails > to register its service name -- it just won't be advertised. That's the > only consequence. There are no additional runtime costs, no strange log > messages, nothing like that. > > I'd love to have that GUC variable so that the service name could be > configured...but I think that 99% of the people that will want to use > the Rendezvous support in PostgreSQL will only be running a single > instance of postmaster on a machine. Like you said, if people need the > ability to configure the service name, the GUC variable can be added > later. The way we're doing it now, Rendezvous will be enabled and the > postmaster will be advertised by default on systems that support it. I > like that. :) If we add the variable, then it won't be configured and > advertised by default (I'm assuming). > > > Two more issues --- first, I changed 'pgsql' to 'postgresql' as the > > service name, to match our registered TCP service name. Second, if we > > do add a GUC variable, it has to conditionally be included in > > postgresql.conf.sample if Rendezvous is enabled. > > For the first issue, "_postgresql._tcp" sounds great. For the second > one...is conditional inclusion in postgresql.conf.sample hard? Would it > suffice to put a "This option can only be configured on systems with > support for Rendezvous (ex: Darwin, Mac OS X)" comment above the > (commented out) line that configures the variable? > > Thanks! > > - Chris > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- 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 Index: doc/src/sgml/runtime.sgml === RCS file: /cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v retrieving revision 1.193 diff -c -c -r1.193 runtime.sgml *** doc/src/sgml/runtime.sgml 14 Jul 2003 20:00:22 - 1.193 --- doc/src/sgml/runtime.sgml 18 Jul 2003 20:43:38 - *** *** 732,737 --- 732,747 + + RENDEZVOUS_NAME (string) + + + Specifies the Rendezvous broadcast name. By default, the + local hostname is used. + + + + Index: src/backend/postmaster/postmaster.c === RCS file: /cvsroot/pgsql-server/src/backend/postmaster/postmaster.c,v retrieving revision 1.333 diff -c -c -r1.333 postmaster.c *** src/backend/postmaster/postmaster.c 12 Jun 2003 07:36:51 - 1.333 --- src/backend/postmaster/postmaster.c 18 Jul 2003 20:43:42 - *** *** 210,215 --- 210,217 bool Log_connections = false; bool Db_user_namespace = false; + char *rendezvous_name = NULL; + /* For FNCTL_NONBLOCK */ #if defined(WIN32) || defined(__BEOS__) long ioctlsocket_ret;
Re: [PATCHES] nitpick consistency patch for pg_dump.c
trivial or not the patch was broken. *sigh*. it's been a long day. This patch will work. sorry andrew RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v retrieving revision 1.335 diff -c -w -r1.335 pg_dump.c *** pg_dump.c 25 Jun 2003 04:08:19 - 1.335 --- pg_dump.c 18 Jul 2003 21:14:34 - *** *** 3423,3433 attname = PQgetvalue(res, i, i_attname); atttypdefn = PQgetvalue(res, i, i_atttypdefn); ! if (i > 0) ! appendPQExpBuffer(q, ",\n\t"); ! appendPQExpBuffer(q, "%s %s", fmtId(attname), atttypdefn); } ! appendPQExpBuffer(q, ");\n"); /* * DROP must be fully qualified in case same name appears in --- 3423,3433 attname = PQgetvalue(res, i, i_attname); atttypdefn = PQgetvalue(res, i, i_atttypdefn); ! appendPQExpBuffer(q, "\n\t%s %s", fmtId(attname), atttypdefn);! if (i < ntups - 1) ! appendPQExpBuffer(q, ","); } ! appendPQExpBuffer(q, "\n);\n"); /* * DROP must be fully qualified in case same name appears in > > This is a totally trivial patch for something that was a very minor nit > that annoyed me the other day while I was documenting my current > project. It makes pg_dump use the same layout for types as for tables, > by putting "\n\t" before the first field and "\n" before the final ");" > > Can't really justify this too much except to say I had an itch and I > scratched it ;-) > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PATCHES] pg_ping(1) utility...
I've attached a chump little utility called pg_ping(1) and the necessary update to pg_ctl(1) to see if a database is up and running or not. There are three things that I haven't completed with this that I'll try and get to the next time I have a small chunk of time available: 1) I'd hoped to add some basic profiling support so that admins could time the amount of time it takes to have a connection be established (I've found it to be strangely useful). 2) A -f switch that does a "full" test and actually executes "SELECT TRUE" from the database. 3) Poke around to see if I could add support for getopt_long() Anyway, let me throw this out that way it can get kicked around as appropriate. Because it's so simple in its nature and because right now pg_ctl(1) is kinda broken in its current state (setup any kind of auth and pg_ctl hangs), it'd be slick 50 if this patch could sneak into the 7.4 release. :) -sc PS, it looks like there's a bug in the backend (not sure how old my CVS copy is) if you comment out all of you pg_hba.conf entries, you get a garbled message on the backend: 2003-07-18 15:53:40 [15386] FATAL: No pg_hba.conf entry for host ^L, user pgsql, database template1 ^^ -- Sean Chittenden Index: src/bin/pg_ctl/pg_ctl.sh === RCS file: /home/ncvs/pgsql/pgsql-server/src/bin/pg_ctl/pg_ctl.sh,v retrieving revision 1.32 diff -u -r1.32 pg_ctl.sh --- src/bin/pg_ctl/pg_ctl.sh20 Mar 2003 05:00:14 - 1.32 +++ src/bin/pg_ctl/pg_ctl.sh18 Jul 2003 23:04:03 - @@ -98,12 +98,12 @@ fi # Check if needed programs actually exist in path -if [ -x "$self_path/postmaster" ] && [ -x "$self_path/psql" ]; then +if [ -x "$self_path/postmaster" ] && [ -x "$self_path/pg_ping" ]; then PGPATH="$self_path" -elif [ -x "$bindir/postmaster" ] && [ -x "$bindir/psql" ]; then +elif [ -x "$bindir/postmaster" ] && [ -x "$bindir/pg_ping" ]; then PGPATH="$bindir" else -echo "The programs 'postmaster' and 'psql' are needed by $CMDNAME but" 1>&2 +echo "The programs 'postmaster' and 'pg_ping' are needed by $CMDNAME but" 1>&2 echo "were not found in the directory '$bindir'." 1>&2 echo "Check your installation." 1>&2 exit 1 @@ -358,16 +358,12 @@ fi fi -# FIXME: This is horribly misconceived. -# 1) If password authentication is set up, the connection will fail. -# 2) If a virtual host is set up, the connection may fail. -# 3) If network traffic filters are set up tight enough, the connection +# FIXME: This is less misconceived, but not perfect. +# 1) If a virtual host is set up, the connection may fail. +# 2) If network traffic filters are set up tight enough, the connection #may fail. -# 4) When no Unix domain sockets are available, the connection will -#fail. (Using TCP/IP by default ain't better.) -# 5) If the dynamic loader is not set up correctly (for this user/at -#this time), psql will fail (to find libpq). -# 6) If psql is misconfigured, this may fail. +# 3) If the dynamic loader is not set up correctly (for this user/at +#this time), pg_ping will fail (to find libpq). # Attempt to use the right port # Use PGPORT if set, otherwise look in the configuration file @@ -384,7 +380,13 @@ $silence_echo $ECHO_N "waiting for postmaster to start..."$ECHO_C while : do - if "$PGPATH/psql" -p $PGPORT -l >/dev/null 2>&1 + if [ -z "$PGPORT" ];then + PGPORT_OPT="" + else + PGPORT_OPT="-p $PGPORT" + fi + + if "$PGPATH/pg_ping" -q $PGPORT_OPT then break; else Index: src/bin/Makefile === RCS file: /home/ncvs/pgsql/pgsql-server/src/bin/Makefile,v retrieving revision 1.39 diff -u -r1.39 Makefile --- src/bin/Makefile3 Sep 2002 21:45:43 - 1.39 +++ src/bin/Makefile18 Jul 2003 19:56:09 - @@ -15,7 +15,7 @@ DIRS := initdb initlocation ipcclean pg_ctl pg_dump pg_id \ psql scripts pg_config pg_controldata pg_resetxlog \ - pg_encoding + pg_encoding pg_ping ifeq ($(with_tcl), yes) DIRS += pgtclsh Index: doc/src/sgml/ref/allfiles.sgml === RCS file: /home/ncvs/pgsql/pgsql-server/doc/src/sgml/ref/allfiles.sgml,v retrieving revision 1.54 diff -u -r1.54 allfiles.sgml --- doc/src/sgml/ref/allfiles.sgml 27 Jun 2003 14:45:25 - 1.54 +++ doc/src/sgml/ref/allfiles.sgml 18 Jul 2003 23:48:20 - @@ -116,6 +116,7 @@ + Index: doc/src/sgml/ref/pg_ping-ref.sgml === RCS file: doc/src/sgml/ref/pg_ping-ref.sgml diff -N doc/src/sgml/ref/pg_ping-ref.sgml --- /dev/null 1 Jan 1970 00:00:00 - +++ doc/src/sgml/ref/pg_ping-r
Re: [PATCHES] PERL (fwd)
I was dealing with SCO on PG issues, and they supplied the following patch against 7.3.3 for SCO OSR5. Can it be massaged for 7.4? LER Forwarded Message Date: Tuesday, July 15, 2003 13:09:40 -0700 From: Kean Johnston <[EMAIL PROTECTED]> To: Larry Rosenman <[EMAIL PROTECTED]> Cc: Subject: Re: PERL If you can get patches for OSR5 out QUICKLY, we can get them integrated on 7.4 which enters beta next week. Attached. Kean -- End Forwarded Message -- -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 --- Begin Message --- If you can get patches for OSR5 out QUICKLY, we can get them integrated on 7.4 which enters beta next week. Attached. Kean --- ./src/interfaces/ecpg/lib/Makefile.osd 2003-06-23 11:48:33.0 -0700 +++ ./src/interfaces/ecpg/lib/Makefile 2003-06-23 13:08:35.0 -0700 @@ -28,6 +28,7 @@ # Shared library stuff include $(top_srcdir)/src/Makefile.shlib +RPATHDIR=$(libdir)/ install: all installdirs install-lib --- ./src/interfaces/libpgtcl/Makefile.osd 2003-06-23 11:49:03.0 -0700 +++ ./src/interfaces/libpgtcl/Makefile 2003-06-23 13:08:56.0 -0700 @@ -31,6 +31,7 @@ # Shared library stuff include $(top_srcdir)/src/Makefile.shlib +RPATHDIR=$(libdir)/ install: all installdirs install-headers install-lib --- ./src/interfaces/libpq/Makefile.osd 2003-06-23 11:50:16.0 -0700 +++ ./src/interfaces/libpq/Makefile 2003-06-23 13:09:13.0 -0700 @@ -36,6 +36,7 @@ # Shared library stuff include $(top_srcdir)/src/Makefile.shlib +RPATHDIR=$(libdir)/ backend_src = $(top_srcdir)/src/backend --- ./src/pl/plperl/GNUmakefile.osd 2003-06-23 11:37:00.0 -0700 +++ ./src/pl/plperl/GNUmakefile 2003-06-23 13:07:00.0 -0700 @@ -18,6 +18,11 @@ override CFLAGS := $(filter-out -Wall -Wmissing-declarations -Wmissing-prototypes, $(CFLAGS)) endif +# This fails on SCO with -ztext, becuase libcrypt.a is a COFF library +ifeq ($(PORTNAME), sco) +override perl_embed_ldflags := $(filter-out -lcrypt, $(perl_embed_ldflags)) +endif + override CPPFLAGS := -I$(srcdir) -I$(perl_archlibexp)/CORE $(CPPFLAGS) @@ -30,7 +35,6 @@ include $(top_srcdir)/src/Makefile.shlib - all: all-lib SPI.c: SPI.xs --- ./src/template/sco.osd 2003-06-23 10:11:18.0 -0700 +++ ./src/template/sco 2003-06-23 10:11:07.0 -0700 @@ -2,6 +2,6 @@ CFLAGS=-O2 else CFLAGS=-O + CC="$CC -b elf" fi -CC="$CC -b elf" --- ./src/Makefile.shlib.osd2003-06-23 11:55:26.0 -0700 +++ ./src/Makefile.shlib2003-06-23 13:05:14.0 -0700 @@ -171,7 +171,7 @@ else LINK.shared= $(CC) -G endif - LINK.shared += -Wl,-z,text -Wl,-h,$(soname) + LINK.shared += -Wl,-z,text -Wl,-h,$(RPATHDIR)$(soname) endif ifeq ($(PORTNAME), svr4) --- End Message --- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] [NOVICE] connectby(... pos_of_sibling)
I can not find any patch from that date with your name on it, and I can't find a patch at all with connectby from you. Of course, please repost it if you have it (archives are down), but then again, where has the patch gone? Why isn't it in my mailbox? --- Joe Conway wrote: > Bruce Momjian wrote: > > Joe, would you comment on this change to tablefunc connectby? > > > > I actually tied up a few loose ends on this and submitted it to PATCHES > on 6/26/2003. See: > http://archives.postgresql.org/pgsql-patches/2003-06/msg00357.php > > Joe > > > > > ---(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] "fix" for plpgsql polymorphism
I'm going to resend the patches that I have outstanding since it appears
some may have been lost. Here's the first.
==
Tom Lane wrote:
You can alias $0, similar to the argument variables. And, I confirmed
that you cannot change the value, similar to the argument variables:
Perhaps you shouldn't mark it isconst; then it would actually have some
usefulness (you could use it directly as a temporary variable to hold
the intended result). I can't see much value in aliasing it if it's
const, either.
OK; the only change in this version is "isconst = false;". Now you can
use $0 as a result placeholder if desired. E.g.:
create or replace function tmp(anyelement, anyelement) returns anyarray as '
declare
v_ret alias for $0;
v_el1 alias for $1;
v_el2 alias for $2;
begin
v_ret := ARRAY[v_el1, v_el2];
return v_ret;
end;
' language 'plpgsql';
create table f(f1 text, f2 text, f3 int, f4 int);
insert into f values ('a','b',1,2);
insert into f values ('z','x',3,4);
select tmp(f1,f2) from f;
select tmp(f3,f4) from f;
Joe
Index: src/pl/plpgsql/src/pl_comp.c
===
RCS file: /opt/src/cvs/pgsql-server/src/pl/plpgsql/src/pl_comp.c,v
retrieving revision 1.59
diff -c -r1.59 pl_comp.c
*** src/pl/plpgsql/src/pl_comp.c1 Jul 2003 21:47:09 - 1.59
--- src/pl/plpgsql/src/pl_comp.c3 Jul 2003 17:59:48 -
***
*** 354,359
--- 354,395
function->fn_rettyplen = typeStruct->typlen;
function->fn_rettypelem = typeStruct->typelem;
perm_fmgr_info(typeStruct->typinput,
&(function->fn_retinput));
+
+ /*
+* install $0 reference, but only for polymorphic
+* return types
+*/
+ if (procStruct->prorettype == ANYARRAYOID ||
+ procStruct->prorettype == ANYELEMENTOID)
+ {
+ charbuf[32];
+
+ /* name for variable */
+ snprintf(buf, sizeof(buf), "$%d", 0);
+
+ /*
+* Normal return values get a var node
+*/
+ var = malloc(sizeof(PLpgSQL_var));
+ memset(var, 0, sizeof(PLpgSQL_var));
+
+ var->dtype = PLPGSQL_DTYPE_VAR;
+ var->refname = strdup(buf);
+ var->lineno = 0;
+ var->datatype = build_datatype(typeTup, -1);
+ var->isconst = false;
+ var->notnull = false;
+ var->default_val = NULL;
+
+ /* preset to NULL */
+ var->value = 0;
+ var->isnull = true;
+ var->freeval = false;
+
+ plpgsql_adddatum((PLpgSQL_datum *) var);
+ plpgsql_ns_additem(PLPGSQL_NSTYPE_VAR,
var->varno,
+
var->refname);
+ }
}
ReleaseSysCache(typeTup);
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [NOVICE] connectby(... pos_of_sibling)
I'm going to resend the patches that I have outstanding since it appears
some may have been lost. Here's the second of three.
Nabil Sayegh wrote:
Am Son, 2003-06-22 um 02.09 schrieb Joe Conway:
Sounds like all that's needed for your case. But to be complete, in
addition to changing tablefunc.c we'd have to:
1) come up with a new function call signature that makes sense and does
not cause backward compatibility problems for other people
2) make needed changes to tablefunc.sql.in
3) adjust the README.tablefunc appropriately
4) adjust the regression test for new functionality
5) be sure we don't break any of the old cases
If you want to submit a complete patch, it would be gratefully accepted
-- for review at least ;-)
Here's the patch, at least for steps 1-3
I don't know anything about regression tests :(
However, I included a patch against 7.3.3
Nice work Nabil!
I've merged the patch with cvs HEAD, added to the regression tests, and
verified no backward compatibility issues. Please apply.
FYI Nabil, if you want to run the regression test, cd to
contrib/tablefunc as user postgres (or whoever postgresql runs as, and
be sure they have full permission on contrib/tablefunc directory) and run:
make installcheck
The test script that gets run is in contrib/tablefunc/sql, the expected
output is in contrib/tablefunc/expected, and the actual output is in
contrib/tablefunc/results. If the test fails you'll find regression.diff
in contrib/tablefunc.
I'll send you a tarred copy of contrib/tablefunc (off list) to try
yourself on 7.3.3, as I don't think this patch will apply cleanly to it.
It ought to work on 7.3.3, and it includes enhance crosstab functionality.
Thanks!
Joe
Index: contrib/tablefunc/README.tablefunc
===
RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/README.tablefunc,v
retrieving revision 1.6
diff -c -r1.6 README.tablefunc
*** contrib/tablefunc/README.tablefunc 20 Mar 2003 06:46:30 - 1.6
--- contrib/tablefunc/README.tablefunc 26 Jun 2003 16:44:17 -
***
*** 4,9
--- 4,11
* Sample to demonstrate C functions which return setof scalar
* and setof composite.
* Joe Conway <[EMAIL PROTECTED]>
+ * And contributors:
+ * Nabil Sayegh <[EMAIL PROTECTED]>
*
* Copyright 2002 by PostgreSQL Global Development Group
*
***
*** 60,68
- requires anonymous composite type syntax in the FROM clause. See
the instructions in the documentation below.
! connectby(text relname, text keyid_fld, text parent_keyid_fld,
! text start_with, int max_depth [, text branch_delim])
- returns keyid, parent_keyid, level, and an optional branch string
- requires anonymous composite type syntax in the FROM clause. See
the instructions in the documentation below.
--- 62,72
- requires anonymous composite type syntax in the FROM clause. See
the instructions in the documentation below.
! connectby(text relname, text keyid_fld, text parent_keyid_fld
! [, text orderby_fld], text start_with, int max_depth
! [, text branch_delim])
- returns keyid, parent_keyid, level, and an optional branch string
+ and an optional serial column for ordering siblings
- requires anonymous composite type syntax in the FROM clause. See
the instructions in the documentation below.
***
*** 452,464
==
Name
! connectby(text, text, text, text, int[, text]) - returns a set
representing a hierarchy (tree structure)
Synopsis
! connectby(text relname, text keyid_fld, text parent_keyid_fld,
! text start_with, int max_depth [, text branch_delim])
Inputs
--- 456,469
==
Name
! connectby(text, text, text[, text], text, text, int[, text]) - returns a set
representing a hierarchy (tree structure)
Synopsis
! connectby(text relname, text keyid_fld, text parent_keyid_fld
! [, text orderby_fld], text start_with, int max_depth
! [, text branch_delim])
Inputs
***
*** 474,479
--- 479,489
Name of the key_parent field
+ orderby_fld
+
+ If optional ordering of siblings is desired:
+ Name of the field to order siblings
+
start_with
root value of the tree input as a text value regardless of keyid_fld type
***
*** 500,505
--- 510,525
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
AS t(keyid text, parent_keyid text, level int);
+
+ - or -
+
+ SELE
Re: [PATCHES] [HACKERS] allowed user/db variables
I'm going to resend the patches that I have outstanding since it appears
some may have been lost. Here's the third of three.
===
Tom Lane wrote:
Joe Conway <[EMAIL PROTECTED]> writes:
ISTM that "source" is worth knowing.
Hm, possibly. Any other opinions?
This version has the seven fields I proposed, including "source". Here's
an example that shows why I think it's valuable:
regression=# \x
Expanded display is on.
regression=# select * from pg_settings where name = 'enable_seqscan';
-[ RECORD 1 ]---
name| enable_seqscan
setting | on
context | user
vartype | bool
source | default
min_val |
max_val |
regression=# update pg_settings set setting = 'off' where name =
'enable_seqscan';
-[ RECORD 1 ]---
set_config | off
regression=# select * from pg_settings where name = 'enable_seqscan';
-[ RECORD 1 ]---
name| enable_seqscan
setting | off
context | user
vartype | bool
source | session
min_val |
max_val |
regression=# alter user postgres set enable_seqscan to 'off';
ALTER USER
(log out and then back in again)
regression=# \x
Expanded display is on.
regression=# select * from pg_settings where name = 'enable_seqscan';
-[ RECORD 1 ]---
name| enable_seqscan
setting | off
context | user
vartype | bool
source | user
min_val |
max_val |
In the first case, enable_seqscan is set to its default value. After
setting it to off, it is obvious that the value has been changed for the
session only. In the third case, you can see that the value has been set
specifically for the user.
This version of the patch also includes documentation changes. Passes
all regression tests. Please apply.
Joe
Index: doc/src/sgml/runtime.sgml
===
RCS file: /opt/src/cvs/pgsql-server/doc/src/sgml/runtime.sgml,v
retrieving revision 1.187
diff -c -r1.187 runtime.sgml
*** doc/src/sgml/runtime.sgml 25 Jun 2003 01:13:24 - 1.187
--- doc/src/sgml/runtime.sgml 26 Jun 2003 05:01:56 -
***
*** 571,584
name
text
!The name of the run-time configuration parameter
setting
text
!The current value of the run-time configuration parameter
--- 571,615
name
text
!run-time configuration parameter name
setting
text
!current value of the parameter
+
+
+context
+text
+context required to set the parameter's value
+
+
+
+vartype
+text
+parameter type
+
+
+
+source
+text
+source of the current parameter value
+
+
+
+min_val
+text
+minimum allowed value of the parameter
+
+
+
+max_val
+text
+maximum allowed value of the parameter
+
+
Index: src/backend/utils/misc/guc.c
===
RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/guc.c,v
retrieving revision 1.131
diff -c -r1.131 guc.c
*** src/backend/utils/misc/guc.c11 Jun 2003 22:13:22 - 1.131
--- src/backend/utils/misc/guc.c26 Jun 2003 04:03:00 -
***
*** 159,168
*/
enum config_type
{
! PGC_BOOL,
! PGC_INT,
! PGC_REAL,
! PGC_STRING
};
/* Generic fields applicable to all types of variables */
--- 159,208
*/
enum config_type
{
! PGC_BOOL = 0,
! PGC_INT = 1,
! PGC_REAL = 2,
! PGC_STRING = 3
! };
!
! /*
! * Used for pg_settings. Keep in sync with config_type enum above
! */
! static char *config_type_name[] =
! {
! "bool",
! "integer",
! "real",
! "string"
! };
!
! /*
! * Used for pg_settings. Keep in sync with GucContext enum in guc.h
! */
! static char *GucContextName[] =
! {
! "internal",
! "postmaster",
! "sighup",
! "backend",
! "super-user",
! "user"
! };
!
! /*
! * Used for pg_settings. Keep in sync with GucSource enum in guc.h
! */
! static char *GucSourceName[] =
! {
! "default",
! "environment variable",
! "configuration file",
! "command line",
! "database",
! "user",
! "client",
! "override",
! "session"
};
/* Generic fields applicable to all types of variables */
***
*** 2617,2639
* Return GUC variable value by variable number; optionally return canonical
* form of name. Return value is palloc'd.
*/
! char *
! GetConfigOptionByNum(int varnum, const char **varname, bool *noshow)
{
! struct config_generic *conf;
/* check requested variable number valid */
Assert((varnum >= 0) && (varnum < num_guc_variables));
conf = guc_variables[varnum]
