Re: [PATCHES] [HACKERS] Autovacuum loose ends
"Matthew T. O'Connor" writes: > I don't know either, but this brings up another question. Stats > wraparound. The n_tup_ins/upd/del columns in the stats system are > defined as bigint, what happens when the total number of upd for example > exceeds the capacity for bigint, or overflows to negative, anyone have > any idea? We'll all be safely dead, for one thing ;-) At one update per nanosecond, it'd take approximately 300 years to wrap a 64-bit counter. Somehow I don't have a problem with the idea that Postgres would need to be rebooted that often. We'd want to fix the 32-bit nature of XIDs long before 64-bit stats counters get to be a real-world issue ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] Autovacuum loose ends
Alvaro Herrera wrote: On Sun, Jul 24, 2005 at 02:33:38PM -0400, Tom Lane wrote: Hmm, I wonder whether the minimum shouldn't be 10. Or even 60. It's ok with me. What do other people think? Effectiely, this is going to be the minimum amount of "down time" for autovacuum between checking databases, right? So if the minimum is 10 seconds, and there I have six databases, then it will check each database at most once per minute? If so, then I'm not sure what I think if I have a few hundred databases, 10s might be too long. What's the use-case for having the stat reset feature at all? I don't know. Maybe the people who added it can tell? I don't know either, but this brings up another question. Stats wraparound. The n_tup_ins/upd/del columns in the stats system are defined as bigint, what happens when the total number of upd for example exceeds the capacity for bigint, or overflows to negative, anyone have any idea? Matt ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Autovacuum loose ends
We have to consider what happens at stat reset -- AFAICS there's no problem, because as soon as the table sees some activity, it will be picked up by pgstat. However, it would be bad if stats are reset right after some heavy activity on a table. Maybe the only thing we need is documentation. What's the use-case for having the stat reset feature at all? I believe I was the root cause of the pg_stat_reset() function. The idea at the time was that if you decide to do a round of index optimisation, you want to be able to search for unused indexes and heavily seq. scanned tables. If you reset the stats you have 'clean' data to work with. For instance, you can get 24 hours of clean stats data. Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Regression - GNUmakefile - pg_usleep
"Rocco Altier" <[EMAIL PROTECTED]> writes: > This time I actually have the patches :-) I've applied the parts of this that add -lm to contrib modules, but not the parts that add -lpgport. That's because libpgport is not built to be relocatable, and so including it fails hard on platforms that care: /usr/ccs/bin/ld +h libtsearch2.sl.0 -b +b /home/postgres/testversion/lib dict_ex.o dict.o snmap.o stopword.o common.o prs_dcfg.o dict_snowball.o dict_ispell.o dict_syn.o wparser.o wparser_def.o ts_cfg.o tsvector.o rewrite.o crc32.o query.o gistidx.o tsvector_op.o rank.o ts_stat.o snowball/SUBSYS.o ispell/SUBSYS.o wordparser/SUBSYS.o -L../../src/port -L/usr/local/lib -lpgport -lm `gcc -L../../src/port -Wl,-z -Wl,+b -Wl,/home/postgres/testversion/lib -print-libgcc-file-name` -o libtsearch2.sl.0 /usr/ccs/bin/ld: DP relative code in file ../../src/port/libpgport.a(exec.o) - shared library must be position independent. Use +z or +Z to recompile. make: *** [libtsearch2.sl.0] Error 1 We will need to either eliminate the dependencies on libpgport, or use the extra-compilation technique used in, eg, libpq. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] Proposed patch to remove .so pattern rules from platform Makefiles
I've wanted for a long time to get rid of the pattern rules in the
port-specific Makefiles that generate shared libraries from single
object files. These patterns duplicate (or, more often, fail to
completely duplicate) the knowledge in Makefile.shlib. So from
a maintenance point of view centralizing that knowledge is a good
thing.
The stumbling block has been partly that the regression-test makefile
depended on the pattern rules (easily fixed by using Makefile.shlib)
and partly that pgxs.mk (and its predecessor contrib-global.mk) depended
on the pattern rules to handle Makefiles that wanted to build multiple
.so files. Since Makefile.shlib is designed to handle only one shlib
per build, there wasn't any obvious way to fix that.
The attached proposed patch gets around this by invoking Makefile.shlib
in a way that produces a pattern rule "lib%.so : %.o". This is
moderately ugly but it gets the job done without changing Makefile.shlib
itself. Possibly it could be done more cleanly if we were willing
to introduce pattern rules inside Makefile.shlib.
I am not sure if the patch works on non-Unix platforms --- could someone
test on Win32 and Cygwin, in particular? AIX is weird enough to need
testing too.
Any other comments?
regards, tom lane
*** src/makefiles/Makefile.aix.orig Wed Oct 9 12:21:54 2002
--- src/makefiles/Makefile.aix Sun Jul 24 16:19:25 2005
***
*** 23,33
MKLDEXPORT=$(top_srcdir)/src/backend/port/aix/mkldexport.sh
- %$(EXPSUFF): %.o
- $(MKLDEXPORT) $*.o > $*$(EXPSUFF)
-
- %$(DLSUFFIX): %.o %$(EXPSUFF)
- @echo Making shared library $@ from $*.o, $*$(EXPSUFF) and postgres.imp
- $(CC) $(LDFLAGS) $(LDFLAGS_SL) -o $@ $*.o
-Wl,-bI:$(top_builddir)/src/backend/$(POSTGRES_IMP) -Wl,-bE:$*$(EXPSUFF) $(LIBS)
-
sqlmansect = 7
--- 23,26
*** src/makefiles/Makefile.beos.origThu Dec 16 22:49:58 2004
--- src/makefiles/Makefile.beos Sun Jul 24 16:19:25 2005
***
*** 8,19
DLSUFFIX = .so
CFLAGS_SL = -fpic -DPIC
- %.so: %.o
- ifdef PGXS
- ln -fs $(DESTDIR)$(bindir)/postgres _APP_
- else
- ln -fs $(top_builddir)/src/backend/postgres _APP_
- endif
- $(CC) -nostart -Xlinker -soname=$@ -o $@ _APP_ $<
-
sqlmansect = 7
--- 8,11
*** src/makefiles/Makefile.bsdi.origTue Dec 21 13:42:04 2004
--- src/makefiles/Makefile.bsdi Sun Jul 24 16:19:26 2005
***
*** 20,26
CFLAGS_SL =
endif
- %.so: %.o
- $(CC) -shared -o $@ $<
-
sqlmansect = 7
--- 20,23
*** src/makefiles/Makefile.cygwin.orig Thu Dec 16 22:52:48 2004
--- src/makefiles/Makefile.cygwin Sun Jul 24 16:19:26 2005
***
*** 16,26
DLSUFFIX = .dll
CFLAGS_SL =
- %.dll: %.o
- $(DLLTOOL) --export-all --output-def $*.def $<
- $(DLLWRAP) -o $@ --def $*.def $< $(DLLINIT) $(SHLIB_LINK)
- rm -f $*.def
-
ifneq (,$(findstring backend,$(subdir)))
ifeq (,$(findstring conversion_procs,$(subdir)))
override CPPFLAGS+= -DBUILDING_DLL
--- 16,21
*** src/makefiles/Makefile.darwin.orig Thu Dec 16 22:49:59 2004
--- src/makefiles/Makefile.darwin Sun Jul 24 16:19:27 2005
***
*** 10,18
BE_DLLLIBS= -bundle_loader $(top_builddir)/src/backend/postgres
endif
- # Rule for building shared libs (currently used only for regression test
- # shlib ... should go away, since this is not really enough knowledge)
- %.so: %.o
- $(CC) -bundle -o $@ $< $(BE_DLLLIBS)
-
sqlmansect = 7
--- 10,13
*** src/makefiles/Makefile.dgux.origWed Aug 29 15:14:40 2001
--- src/makefiles/Makefile.dgux Sun Jul 24 16:19:27 2005
***
*** 2,8
DLSUFFIX = .so
CFLAGS_SL = -fpic
- %.so: %.o
- $(CC) -shared -o $@ $<
-
sqlmansect = 5
--- 2,5
*** src/makefiles/Makefile.freebsd.orig Tue Dec 21 13:42:10 2004
--- src/makefiles/Makefile.freebsd Sun Jul 24 16:19:28 2005
***
*** 13,30
CFLAGS_SL = -fpic -DPIC
endif
-
- %.so: %.o
- ifdef ELF_SYSTEM
- $(LD) -x -shared -o $@ $<
- else
- $(LD) $(LDREL) $(LDOUT) $<.obj -x $<
- @echo building shared object $@
- @rm -f [EMAIL PROTECTED]
- @${AR} cq [EMAIL PROTECTED] `lorder $<.obj | tsort`
- ${RANLIB} [EMAIL PROTECTED]
- @rm -f $@
- $(LD) -x -Bshareable -Bforcearchive -o $@ [EMAIL PROTECTED]
- endif
-
sqlmansect = 7
--- 13,16
*** src/makefiles/Makefile.hpux.origTue Dec 21 13:42:14 2004
--- src/makefiles/Makefile.hpux Sun Jul 24 16:10:28 2005
***
*** 49,69
CFLAGS_SL = +z
endif
- # Rule for building shared libs (currently used only for regression test
- # shlib ... should go away, since this is not really enough knowledge)
- %$(DLSUFFIX): %.o
- ifeq ($(GCC), yes)
- ifeq ($(with_gnu_ld), yes)
- $(CC) $(LDFLAGS) -shared -o $@ $< `$(CC) $(LDFLAGS)
-print-libgcc-file-name`
- else
- $(LD) -b -o $@ $< `$(CC) $(LDFLAGS) -print-libgcc-file-name`
- endif
- else
-
Re: [PATCHES] [HACKERS] Autovacuum loose ends
On Sun, Jul 24, 2005 at 02:33:38PM -0400, Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > - pg_statistic is completely ignored. > > ... pg_statistic still needs vacuuming, surely. It's only ANALYZE > that you can/should skip for it. Sorry, yes, it's ignored only for analyze. > > - The postmaster's main loop sleeps Min(60, autovacuum_naptime), in > > order to be able to pick naptimes smaller than 60 seconds. In order > > not to make the loop a busy-wait, I forced a minimum of 1 to that GUC > > var. > > Hmm, I wonder whether the minimum shouldn't be 10. Or even 60. It's ok with me. What do other people think? > > We have to consider what > > happens at stat reset -- AFAICS there's no problem, because as soon as > > the table sees some activity, it will be picked up by pgstat. > > However, it would be bad if stats are reset right after some heavy > > activity on a table. Maybe the only thing we need is documentation. > > What's the use-case for having the stat reset feature at all? I don't know. Maybe the people who added it can tell? > > - There are stat messages emitted for a database-wide vacuum, just like > > any other. This means that all tables in the database would end up in > > pgstat; and also all databases, including those with datallowconn = false. > > This may not be good. I'm not sure what exactly to do about it. Do > > we want to disallow such stats? Disable message sending (or > > collecting) in some circumstances? > > Needs thought... Ok. -- Alvaro Herrera () "I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Regression - GNUmakefile - pg_usleep
"Rocco Altier" <[EMAIL PROTECTED]> writes: >> It seems highly unlikely that this will accomplish anything, >> given that SHLIB_LINK is not used to construct regress.so. >> > I have another patch in queue for this. Oh, I see, you want to use SHLIB_LINK in the %$(DLSUFFIX): %.o rule. > Right now there are 2 different sets of linker rules. One for a single > file -> .so (from Makefile.aix), and another for multiple files -> > lib.so (from Makefile.shlib). Actually, that's not the reason it's like this, at all. The percent-rules in the per-platform Makefiles are a hangover from long before we had Makefile.shlib, and most of them pretty well suck. As noted in Makefile.hpux, # Rule for building shared libs (currently used only for regression test # shlib ... should go away, since this is not really enough knowledge) As far as I can see, src/test/regress/GNUmakefile is the only place still depending on those rules. I've wanted for quite some time to change the regression makefile to use Makefile.shlib to build regress.so, whereupon we could get rid of the percent-rules in the per-platform Makefiles entirely. Maybe it's time to have a go at that. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Autovacuum loose ends
Alvaro Herrera <[EMAIL PROTECTED]> writes: > - pg_statistic is completely ignored. ... pg_statistic still needs vacuuming, surely. It's only ANALYZE that you can/should skip for it. > - The postmaster's main loop sleeps Min(60, autovacuum_naptime), in > order to be able to pick naptimes smaller than 60 seconds. In order > not to make the loop a busy-wait, I forced a minimum of 1 to that GUC > var. Hmm, I wonder whether the minimum shouldn't be 10. Or even 60. > - Now that we have a real Xid wraparound check, we could go back to > having any table with no stat entry ignored, which was the original > coding. There's no danger of wraparound, and there'd be no work done > to a table that doesn't have any activity. Agreed. > We have to consider what > happens at stat reset -- AFAICS there's no problem, because as soon as > the table sees some activity, it will be picked up by pgstat. > However, it would be bad if stats are reset right after some heavy > activity on a table. Maybe the only thing we need is documentation. What's the use-case for having the stat reset feature at all? > - datallowcon is still ignored. Now it's safe to do so, because we have > a real Xid wraparound check. Changing it requires extending the > pg_database flat-file (should be fairly easy). I think this is all right, as long as a database that shows no stats traffic is only connected to when it needs to be vacuumed for XID wrap prevention purposes. > - There are stat messages emitted for a database-wide vacuum, just like > any other. This means that all tables in the database would end up in > pgstat; and also all databases, including those with datallowconn = false. > This may not be good. I'm not sure what exactly to do about it. Do > we want to disallow such stats? Disable message sending (or > collecting) in some circumstances? Needs thought... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] Autovacuum loose ends
On Thu, Jul 14, 2005 at 10:52:56AM -0400, Tom Lane wrote:
> I've applied Alvaro's latest integrated-autovacuum patch. There are
> still a number of loose ends to be dealt with before beta, though:
Ok, here's a patch that deals with some of this:
- The stat collector is modified so as to keep shared relations separate
from regular ones. Also, backends sends messages separately.
Autovacuum takes advantage of this, so it correctly identifies the
appropiate time to operate on a shared relation, irrespective of the
database where they were modified. Note however that it uses each
database's pg_autovacuum settings. This means it could be vacuumed
sooner in one database than another, but I don't think it's a problem.
- Temp tables are completely ignored.
- pg_statistic is completely ignored.
- databases with no stat entry are still ignored, except that they are
checked for Xid wraparound like any other. The "oldest" one is chosen
for vacuum in a particular autovacuum run.
- A database-wide vacuum forces a pg_database flat-file update, so that
the wraparound check actually works.
- The postmaster's main loop sleeps Min(60, autovacuum_naptime), in
order to be able to pick naptimes smaller than 60 seconds. In order
not to make the loop a busy-wait, I forced a minimum of 1 to that GUC
var.
Some comments:
- Now that we have a real Xid wraparound check, we could go back to
having any table with no stat entry ignored, which was the original
coding. There's no danger of wraparound, and there'd be no work done
to a table that doesn't have any activity. We have to consider what
happens at stat reset -- AFAICS there's no problem, because as soon as
the table sees some activity, it will be picked up by pgstat.
However, it would be bad if stats are reset right after some heavy
activity on a table. Maybe the only thing we need is documentation.
- datallowcon is still ignored. Now it's safe to do so, because we have
a real Xid wraparound check. Changing it requires extending the
pg_database flat-file (should be fairly easy).
- There are stat messages emitted for a database-wide vacuum, just like
any other. This means that all tables in the database would end up in
pgstat; and also all databases, including those with datallowconn = false.
This may not be good. I'm not sure what exactly to do about it. Do
we want to disallow such stats? Disable message sending (or
collecting) in some circumstances?
- I haven't done anything yet w.r.t. the custom vacuum_delay nor sleep
scale factor.
- There are still no docs.
--
Alvaro Herrera ()
"Porque Kim no hacia nada, pero, eso sí,
con extraordinario éxito" ("Kim", Kipling)
Index: src/backend/access/transam/xlog.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.210
diff -c -r1.210 xlog.c
*** src/backend/access/transam/xlog.c 23 Jul 2005 15:31:16 - 1.210
--- src/backend/access/transam/xlog.c 24 Jul 2005 17:55:51 -
***
*** 465,471
TimeLineID endTLI,
uint32 endLogId, uint32 endLogSeg);
static void WriteControlFile(void);
- static void ReadControlFile(void);
static char *str_time(time_t tnow);
static void issue_xlog_fsync(void);
--- 465,470
***
*** 3383,3390
errmsg("could not close control file: %m")));
}
! static void
! ReadControlFile(void)
{
pg_crc32crc;
int fd;
--- 3382,3394
errmsg("could not close control file: %m")));
}
! /*
! * Read and verify the control file, filling the ControlFile struct.
! *
! * If nextXid is not NULL, the latest Checkpoint's nextXid is returned.
! */
! void
! ReadControlFile(TransactionId *nextXid)
{
pg_crc32crc;
int fd;
***
*** 3525,3530
--- 3529,3537
ControlFile->lc_ctype),
errhint("It looks like you need to initdb or install locale
support.")));
+ if (PointerIsValid(nextXid))
+ *nextXid = ControlFile->checkPointCopy.nextXid;
+
/* Make the fixed locale settings visible as GUC variables, too */
SetConfigOption("lc_collate", ControlFile->lc_collate,
PGC_INTERNAL, PGC_S_OVERRIDE);
***
*** 3650,3656
* for the reasons why).
*/
if (!IsBootstrapProcessingMode())
! ReadControlFile();
}
/*
--- 3657,3663
* for the reasons why).
*/
if (!IsBootstrapProcessingMode())
! ReadControlFile(NULL);
}
/*
***
*** 4232,4238
* Note: in most control paths, *ControlFile is already vali
Re: [PATCHES] Regression - GNUmakefile - pg_usleep
This time I actually have the patches :-) -rocco > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Rocco Altier > Sent: Sunday, July 24, 2005 1:15 PM > To: Tom Lane > Cc: Patches (PostgreSQL) > Subject: Re: [PATCHES] Regression - GNUmakefile - pg_usleep > > > > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane > > Sent: Sunday, July 24, 2005 10:47 AM > > To: Rocco Altier > > Cc: Patches (PostgreSQL) > > Subject: Re: [PATCHES] Regression - GNUmakefile - pg_usleep > > > > > > "Rocco Altier" <[EMAIL PROTECTED]> writes: > > > Attached patch fixes the SHLIB_LINK to add pgport now that > > pg_usleep is > > > added. > > > > It seems highly unlikely that this will accomplish anything, > > given that > > SHLIB_LINK is not used to construct regress.so. > > > I have another patch in queue for this. > > > > This is needed for AIX to resolve symbols at compile time. > > > > I'm still wondering why that platform has such a hard time finding > > symbols that are in the backend. > > > Right now there are 2 different sets of linker rules. One > for a single > file -> .so (from Makefile.aix), and another for multiple files -> > lib.so (from Makefile.shlib). > > The patch I proposed to Makefile.shlib to pull all $LIBS in > was rejected > since this would cause libpq to link against backend libraries, etc. > The suggested solution was to only pull in libraries as > needed (which is > what is happening here, since pgport would be required). > > I think the 2 sets of link rules should be more similar, since the > contrib moudules should get the same link option, instead of based on > how many files need to be linked together, thus Makefile.aix should be > changed to use SHLIB_LINK. > > I am attaching all the patches for so for AIX to let it get > past Contrib > on the buildfarm. > > Makefile.aix.patch - will bring Makefile.aix in line with > Makefile.shlib > by using SHLIB_LINK > contrib.libs.patch - will pull in the needed libraries for contrib > modules > regress.pgport.patch - same idea as contrib patch, pull in the needed > library > > I hope this makes it a bit clearer what I am trying to > achieve by having > all the pieces together in one place. > > Thanks, > -rocco > > PS. I am starting to look at using the facilities in AIX to > support run > time linked libraries instead of at compile time. This does > require AIX > 4.2+, so I will try to leave the 4.1 code in place. > > ---(end of > broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] > so that your >message can get through to the mailing list cleanly > Makefile.aix.patch Description: Makefile.aix.patch contrib.libs.patch Description: contrib.libs.patch regress.pgport.patch Description: regress.pgport.patch ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Regression - GNUmakefile - pg_usleep
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane > Sent: Sunday, July 24, 2005 10:47 AM > To: Rocco Altier > Cc: Patches (PostgreSQL) > Subject: Re: [PATCHES] Regression - GNUmakefile - pg_usleep > > > "Rocco Altier" <[EMAIL PROTECTED]> writes: > > Attached patch fixes the SHLIB_LINK to add pgport now that > pg_usleep is > > added. > > It seems highly unlikely that this will accomplish anything, > given that > SHLIB_LINK is not used to construct regress.so. > I have another patch in queue for this. > > This is needed for AIX to resolve symbols at compile time. > > I'm still wondering why that platform has such a hard time finding > symbols that are in the backend. > Right now there are 2 different sets of linker rules. One for a single file -> .so (from Makefile.aix), and another for multiple files -> lib.so (from Makefile.shlib). The patch I proposed to Makefile.shlib to pull all $LIBS in was rejected since this would cause libpq to link against backend libraries, etc. The suggested solution was to only pull in libraries as needed (which is what is happening here, since pgport would be required). I think the 2 sets of link rules should be more similar, since the contrib moudules should get the same link option, instead of based on how many files need to be linked together, thus Makefile.aix should be changed to use SHLIB_LINK. I am attaching all the patches for so for AIX to let it get past Contrib on the buildfarm. Makefile.aix.patch - will bring Makefile.aix in line with Makefile.shlib by using SHLIB_LINK contrib.libs.patch - will pull in the needed libraries for contrib modules regress.pgport.patch - same idea as contrib patch, pull in the needed library I hope this makes it a bit clearer what I am trying to achieve by having all the pieces together in one place. Thanks, -rocco PS. I am starting to look at using the facilities in AIX to support run time linked libraries instead of at compile time. This does require AIX 4.2+, so I will try to leave the 4.1 code in place. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Patch to fix plpython on OS X
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Michael Fuhr wrote: >> Thanks -- I overlooked that in src/test/regress/README. > We should probably generalise that section of the README a bit. People > might skip over it thinking "this isn't a locale difference". I'm wondering why we still have a README there at all --- it's entirely superseded by the SGML documentation. http://developer.postgresql.org/docs/postgres/regress-evaluation.html regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Patch to fix plpython on OS X
Michael Fuhr wrote: On Sun, Jul 24, 2005 at 08:40:42AM -0500, Andrew Dunstan wrote: This is completely unnecessary - pg_regress has an alternative result mechanism that doesn't rely on a resultmap file. Just name your alternative result file foo_n.out instead of foo.out, for some n in [0-9]. In this case, call it, say, plpython_error_1.out. Job done, and no OS dependence. Thanks -- I overlooked that in src/test/regress/README. We should probably generalise that section of the README a bit. People might skip over it thinking "this isn't a locale difference". cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Patch to fix plpython on OS X
On Sun, Jul 24, 2005 at 08:40:42AM -0500, Andrew Dunstan wrote: > This is completely unnecessary - pg_regress has an alternative result > mechanism that doesn't rely on a resultmap file. Just name your alternative > result file foo_n.out instead of foo.out, for some n in [0-9]. In this case, > call it, say, plpython_error_1.out. Job done, and no OS dependence. Thanks -- I overlooked that in src/test/regress/README. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Patch to fix plpython on OS X
Michael Fuhr <[EMAIL PROTECTED]> writes: > A problem with this patch is that it assumes a version of Python > based on the OS, which might clean up the current buildfarm but > that isn't really correct. Is there a better way to handle this? Yes --- just let pg_regress deal with it as if it were a locale problem. I've committed it that way. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Regression - GNUmakefile - pg_usleep
"Rocco Altier" <[EMAIL PROTECTED]> writes: > Attached patch fixes the SHLIB_LINK to add pgport now that pg_usleep is > added. It seems highly unlikely that this will accomplish anything, given that SHLIB_LINK is not used to construct regress.so. > This is needed for AIX to resolve symbols at compile time. I'm still wondering why that platform has such a hard time finding symbols that are in the backend. Probably a better fix is to make the new do_sleep function use plain old sleep() instead of pg_usleep(). regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Patch to fix plpython on OS X
Michael Fuhr said: > I just built Python 2.3 and it does indeed format the error differently > than later versions (the format appears to have changed in 2.3.1): > [snip] > I've attached two new files that should go in the plpython directory: > > resultmap > expected/plpython_error_py23.out > > A problem with this patch is that it assumes a version of Python > based on the OS, which might clean up the current buildfarm but > that isn't really correct. Is there a better way to handle this? This is completely unnecessary - pg_regress has an alternative result mechanism that doesn't rely on a resultmap file. Just name your alternative result file foo_n.out instead of foo.out, for some n in [0-9]. In this case, call it, say, plpython_error_1.out. Job done, and no OS dependence. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Patch to fix plpython on OS X
On Sat, Jul 23, 2005 at 10:38:59PM -0400, Tom Lane wrote:
> Well, if it is just a Python version issue then all we need do is add
> a variant expected-output file to match. I was just expressing a
> desire to know that for sure before we wallpaper over the symptom...
I just built Python 2.3 and it does indeed format the error differently
than later versions (the format appears to have changed in 2.3.1):
% python2.3
Python 2.3 (#1, Jul 24 2005, 06:18:30)
[GCC 3.4.2] on sunos5
Type "help", "copyright", "credits" or "license" for more information.
>>> str(u'\x80')
Traceback (most recent call last):
File "", line 1, in ?
UnicodeEncodeError: 'ascii' codec can't encode character '\u80' in position 0:
ordinal not in range(128)
% python2.4
Python 2.4.1 (#1, Apr 6 2005, 09:52:02)
[GCC 3.4.2] on sunos5
Type "help", "copyright", "credits" or "license" for more information.
>>> str(u'\x80')
Traceback (most recent call last):
File "", line 1, in ?
UnicodeEncodeError: 'ascii' codec can't encode character u'\x80' in position 0:
ordinal not in range(128)
One could check the version of Python that PL/Python is using with
the following function (assuming that Python isn't so broken that
it would use the core of one version but find modules from another):
CREATE FUNCTION pyversion() RETURNS text AS $$
import sys
return sys.version
$$ LANGUAGE plpythonu;
I've attached two new files that should go in the plpython directory:
resultmap
expected/plpython_error_py23.out
A problem with this patch is that it assumes a version of Python
based on the OS, which might clean up the current buildfarm but
that isn't really correct. Is there a better way to handle this?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
plpython_error/.*-darwin=plpython_error_py23
-- test error handling, i forgot to restore Warn_restart in
-- the trigger handler once. the errors and subsequent core dump were
-- interesting.
SELECT invalid_type_uncaught('rick');
WARNING: plpython: in function invalid_type_uncaught:
DETAIL: plpy.SPIError: Unknown error in PLy_spi_prepare
ERROR: type "test" does not exist
SELECT invalid_type_caught('rick');
WARNING: plpython: in function invalid_type_caught:
DETAIL: plpy.SPIError: Unknown error in PLy_spi_prepare
ERROR: type "test" does not exist
SELECT invalid_type_reraised('rick');
WARNING: plpython: in function invalid_type_reraised:
DETAIL: plpy.SPIError: Unknown error in PLy_spi_prepare
ERROR: type "test" does not exist
SELECT valid_type('rick');
valid_type
(1 row)
--
-- Test Unicode error handling.
--
SELECT unicode_return_error();
ERROR: plpython: function "unicode_return_error" could not create return value
DETAIL: exceptions.UnicodeEncodeError: 'ascii' codec can't encode character
'\u80' in position 0: ordinal not in range(128)
INSERT INTO unicode_test (testvalue) VALUES ('test');
ERROR: plpython: function "unicode_trigger_error" could not modify tuple
DETAIL: exceptions.UnicodeEncodeError: 'ascii' codec can't encode character
'\u80' in position 0: ordinal not in range(128)
SELECT unicode_plan_error1();
WARNING: plpython: in function unicode_plan_error1:
DETAIL: plpy.Error: Unknown error in PLy_spi_execute_plan
ERROR: plpython: function "unicode_plan_error1" could not execute plan
DETAIL: exceptions.UnicodeEncodeError: 'ascii' codec can't encode character
'\u80' in position 0: ordinal not in range(128)
SELECT unicode_plan_error2();
ERROR: plpython: function "unicode_plan_error2" could not execute plan
DETAIL: exceptions.UnicodeEncodeError: 'ascii' codec can't encode character
'\u80' in position 0: ordinal not in range(128)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] per user/database connections limit again
Bruce Momjian napsal(a):
I am ready to apply this patch. Would you make the additional changes
you suggested? Is there any way to see the limits except to query
pg_authid?
Yes I will - pg_dump is already done (I attached it because it should be
aplied with orginal patch), documentation depends partly on roles doc so
it will prolly have to wait.
I also added limit to pg_roles and pg_shadow views when I was patching
pg_dump so you can get it from them.
--
Regards
Petr Jelinek (PJMODOS)
Index: src/backend/catalog/system_views.sql
===
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.16
diff -c -r1.16 system_views.sql
*** src/backend/catalog/system_views.sql28 Jun 2005 05:08:52 -
1.16
--- src/backend/catalog/system_views.sql24 Jul 2005 12:22:08 -
***
*** 14,19
--- 14,20
rolcreatedb,
rolcatupdate,
rolcanlogin,
+ rolmaxconn,
''::text as rolpassword,
rolvaliduntil,
rolconfig
***
*** 26,31
--- 27,33
rolcreatedb AS usecreatedb,
rolsuper AS usesuper,
rolcatupdate AS usecatupd,
+ rolmaxconn AS usemaxconn,
rolpassword AS passwd,
rolvaliduntil::abstime AS valuntil,
rolconfig AS useconfig
Index: src/bin/pg_dump/pg_dumpall.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.64
diff -c -r1.64 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c18 Jul 2005 19:12:09 - 1.64
--- src/bin/pg_dump/pg_dumpall.c24 Jul 2005 12:22:35 -
***
*** 394,409
PGresult *res;
int i;
! if (server_version >= 70100)
res = executeQuery(conn,
"SELECT usename, usesysid,
passwd, usecreatedb, "
! "usesuper, valuntil, "
"(usesysid = (SELECT datdba
FROM pg_database WHERE datname = 'template0')) AS clusterowner "
"FROM pg_shadow");
else
res = executeQuery(conn,
"SELECT usename, usesysid,
passwd, usecreatedb, "
! "usesuper, valuntil, "
"(usesysid = (SELECT datdba
FROM pg_database WHERE datname = 'template1')) AS clusterowner "
"FROM pg_shadow");
--- 394,415
PGresult *res;
int i;
! if (server_version >= 80100)
! res = executeQuery(conn,
! "SELECT usename, usesysid,
passwd, usecreatedb, "
! "usesuper, valuntil,
usemaxconn, "
! "(usesysid = (SELECT datdba
FROM pg_database WHERE datname = 'template0')) AS clusterowner "
! "FROM pg_shadow");
! else if (server_version >= 70100)
res = executeQuery(conn,
"SELECT usename, usesysid,
passwd, usecreatedb, "
! "usesuper, valuntil, '0' AS
usemaxconn, "
"(usesysid = (SELECT datdba
FROM pg_database WHERE datname = 'template0')) AS clusterowner "
"FROM pg_shadow");
else
res = executeQuery(conn,
"SELECT usename, usesysid,
passwd, usecreatedb, "
! "usesuper, valuntil, '0' AS
usemaxconn, "
"(usesysid = (SELECT datdba
FROM pg_database WHERE datname = 'template1')) AS clusterowner "
"FROM pg_shadow");
***
*** 453,458
--- 459,468
appendPQExpBuffer(buf, " VALID UNTIL '%s'",
PQgetvalue(res, i,
5));
+ if (strcmp(PQgetvalue(res, i, 6), "0") != 0)
+ appendPQExpBuffer(buf, " MAX CONNECTIONS '%s'",
+ PQgetvalue(res, i,
6));
+
appendPQExpBuffer(buf, ";\n");
printf("%s", buf->data);
***
*** 612,623
printf("--\n-- Database creation\n--\n\n");
! if (server_version >= 8)
res = executeQuery(c
Re: [PATCHES] PL/PGSQL: Dynamic Record Introspection
Tom Lane schrieb: "Titus von Boxberg" <[EMAIL PROTECTED]> writes: It works for me if we want to have an "NFIELDS" construct. Personally I'm still not convinced that we need one --- what's the use-case? I have removed the NFIELDS construct I'd prefer arbitrary expression, but I suppose there's no harm in doing the simple case first and generalizing if there's demand. I took the "no harm" way. Attached please find the updated patch. The patch is against HEAD of 050721. I switched the syntax to your proposal, renamed the functions in pl_comp.c and updated the sgml doc source and regression test files accordingly. Regards Titus *** ./doc/src/sgml/plpgsql.sgml.origSat Jul 2 08:59:47 2005 --- ./doc/src/sgml/plpgsql.sgml Sat Jul 23 17:24:54 2005 *** *** 867,872 --- 867,921 + To obtain the values of the fields the record is made up of, + the record variable can be qualified with the column or field + name. This can be done either by literally using the column name + or the column name for indexing the record can be taken out of a scalar + variable. The syntax for this notation is Record_variable.(IndexVariable). + To get information about the column field names of the record, + a special expression exists that returns all column names as an array: + RecordVariable.(*) . + Thus, the RECORD can be viewed + as an associative array that allows for introspection of it's contents. + This feature is especially useful for writing generic triggers that + operate on records with unknown structure. + Here is an example procedure that shows column names and values + of the predefined record NEW in a trigger procedure: + + + CREATE OR REPLACE FUNCTION show_associative_records() RETURNS TRIGGER AS $$ + DECLARE + colname TEXT; + colcontent TEXT; + colnamesTEXT[]; + colnINT4; + coliINT4; + BEGIN + -- obtain an array with all field names of the record + colnames := NEW.(*); + RAISE NOTICE 'All column names of test record: %', colnames; + -- show field names and contents of record + coli := 1; + coln := array_upper(colnames,1); + RAISE NOTICE 'Number of columns in NEW: %', coln; + FOR coli IN 1 .. coln LOOP + colname := colnames[coli]; + colcontent := NEW.(colname); + RAISE NOTICE 'column % of NEW: %', quote_ident(colname), quote_literal(colcontent); + END LOOP; + -- Do it with a fixed field name: + -- will have to know the column name + RAISE NOTICE 'column someint of NEW: %', quote_literal(NEW.someint); + RETURN NULL; + END; + $$ LANGUAGE plpgsql; + --CREATE TABLE test_records (someint INT8, somestring TEXT); + --CREATE TRIGGER tr_test_record BEFORE INSERT ON test_records FOR EACH ROW EXECUTE PROCEDURE show_associative_records(); + + + + + Note that RECORD is not a true data type, only a placeholder. One should also realize that when a PL/pgSQL function is declared to return type record, this is not quite the *** ./src/pl/plpgsql/src/pl_comp.c.orig Wed Jul 6 16:42:10 2005 --- ./src/pl/plpgsql/src/pl_comp.c Thu Jul 21 21:28:15 2005 *** *** 995,1001 new = palloc(sizeof(PLpgSQL_recfield)); new->dtype = PLPGSQL_DTYPE_RECFIELD; ! new->fieldname = pstrdup(cp[1]); new->recparentno = ns->itemno; plpgsql_adddatum((PLpgSQL_datum *) new); --- 995,1002 new = palloc(sizeof(PLpgSQL_recfield)); new->dtype = PLPGSQL_DTYPE_RECFIELD; ! new->fieldindex.fieldname = pstrdup(cp[1]); ! new->fieldindex_flag = RECFIELD_USE_FIELDNAME; new->recparentno = ns->itemno; plpgsql_adddatum((PLpgSQL_datum *) new); *** *** 1101,1107 new = palloc(sizeof(PLpgSQL_recfield)); new->dtype = PLPGSQL_DTYPE_RECFIELD; ! new->fieldname = pstrdup(cp[2]); new->recparentno = ns->itemno; plpgsql_adddatum((PLpgSQL_datum *) new); --- 1102,1109 new = palloc(sizeof(PLpgSQL_recfield)); new->dtype = PLPGSQL_DTYPE_RECFIELD; ! new->fieldindex.fieldname = pstrdup(cp[2]); ! new->fieldindex_flag = RECFIELD_USE_FIELDNAME;
