Re: [PATCHES] [BUGS] BUG #2977: dow doesn't conform to ISO-8601
Bruce Momjian wrote: Updated version applied. I reduced the numering changes for the macros. There was also documentation text for dow and a few others that said (for typetimestamp/type values only), but in fact the field worked for timestamptz and date too, so I removed the mentions. If people get confused, I will come up with new wording, like doesn't work for interval or time, which I think is pretty obvious. I remember people got confused in the past about this, so maybe we still need something. --- Bruce Momjian wrote: I have implemented 'isodow' with the attached patch. Thanks for the patch and the new function. Adriaan van Os ---(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: [PATCHES] [pgsql-patches] Patch to avoid gprofprofilingoverwrites
Applied.
---
Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
+ CFLAGS=$CFLAGS -DPROFILE_PID_DIR -pg ${PROFILE_CFLAGS}
Kindly use AC_DEFINE instead of random -D in CFLAGS (which is the wrong
place for -D anyway). Also, what exactly is the point here of
PROFILE_CFLAGS? I thought it was supposed to allow substituting
something else for -pg, but you've managed to defeat that.
I can't see the value in having a profile flag that just adds an
environment variable. I am hoping other compilers will supply the flags
they need and we can expand this.
+ snprintf(gprofDirName, MAXPGPATH, ./gprof/%d,
getpid());
getpid is not int everywhere; use a cast. Also, the ./ bits are
silly, and if you ask me so is the MAXPGPATH-sized buffer for a string
that can't exceed 20 or so bytes.
Patch updated and attached.
--
Bruce Momjian [EMAIL PROTECTED] http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
--
Bruce Momjian [EMAIL PROTECTED] http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PATCHES] [pgsql-patches] Patch to avoidgprofprofilingoverwrites
Applied. Thanks for your help Bruce (and Tom and Nikhil). -- Korry
Re: [PATCHES] Enums patch v2
I am putting this in the patches queue so it is not lost. I believe Neil is working applying this. Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Tom Dunstan wrote: Neil Conway wrote: On Thu, 2007-02-01 at 22:50 -0500, Bruce Momjian wrote: Where are we on this? I can commit to reviewing this. The patch looked fairly solid on a quick glance through, but I won't have the cycles to review it properly for a week or two. I've brought this up to date with the operator family stuff now, and the attached patch once more applies cleanly against HEAD. Hopefully that'll make life a little easier when reviewing it. Thanks. I got sick of manually shifting the new OID values every time someone had added something new to the catalogs, so I moved all of my OIDs up to the 9000 range. Attached is a hacky bash script which can move them back to something sane. The idea is to run unused_oids first, see where the main block of unused OIDs starts, and then run shift_oids on the (unzipped) patch file before applying the patch. We discussed something similar a while ago, but no-one ever got around to implementing the script. I've attached the script and left the OIDs in my patch in the upper range rather than just running it myself before submitting the patch as I reckon that this might be a useful convention for authors of patches which add a non-trivial number of OIDs to the catalogs. If there's agreement then anyone can feel free to commit the script to CVS or put it on the wiki or whatever. Cheers Tom [ application/x-gzip is not supported, skipping... ] #!/bin/sh start=$1 end=$2 new_start=$3 filename=$4 if [ -z $filename ] ; then echo Usage: $0 start-oid end-oid new-start-oid filename exit 1 fi if [ ! -f $filename ] ; then echo $0: $filename is not a file exit 1 fi if [ $end -le $start ] ; then echo $0: End of OID range must be greater than or equal to the start exit 1 fi start_len=`echo -n $start | wc -c` end_len=`echo -n $end | wc -c` if [ $start_len -ne 4 -o $end_len -ne 4 ] ; then echo $0: Source OID range must have 4 digits exit 1 fi let new_end=$new_start+$end-$start if [ $start -le $new_start -a $end -ge $new_start -o $new_start -le $start -a $new_end -ge $start ] ; then echo $0: OID ranges may not overlap exit 1 fi i=$start j=$new_start while [ $i -le $end ] ; do #echo $i $j sed -i s/$i/$j/g $filename let i=i+1 let j=j+1 done ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] HOT WIP Patch - version 2
On Tue, 2007-02-20 at 09:48 +0200, Hannu Krosing wrote: I'm not sure about the we are more concerned about the large tables part. I see it more as a device for high-update tables. This may not always be the same as large, so there should be some fallbacks for case where you can't get the lock. Maybe just give up and move to another page ? Every design favours one or other of the various use cases. The worst performance is caused by large tables with random updates, because that causes substantially more I/O than a smaller table. A table with substantially more updaters than rows will be unlikely to ever yield a vacuum-level lock on the block, so the table will inevitably grow. But because its fairly small, it won't grow that much before an autovacuum is triggered to clean it up. The index entries will still be minimised in this case. The case of a small number of rows being very heavily updated in an otherwise very large table will not be well optimised by this simplified version of HOT. However, that case can still benefit from a Dead Space Map approach. In view of other work on DSM it was felt that simplifying HOT was the right thing to do. So DSM is still required. If no other DSM approaches happen, it should be possible to implement an 80/20 version of DSM by simply running a VACUUM using the current FSM implementation as the input blockids. In many cases that will yield a good proportion of the benefits of a full VACUUM. I hope that will be agreed if there isn't any other agreement on a full DSM solution; it would be a pity to ignore such a low complexity solution. Note also that Alvaro's multi-vacuum solution will also be required to allow VACUUMs to be effective against heavily updated, yet smaller tables. So the comment about more concerned with large tables is really a trade-off to allow a simpler solution, yet in an area that minimises the performance disadvantages. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Dead code in _bt_split?
Patch applied. Thanks. --- Heikki Linnakangas wrote: Tom Lane wrote: While testing it I realized that there seems to be a nearby bug in _bt_findsplitloc: it fails to consider the possibility of moving all the extant items to the left side. It will always return a firstright = maxoff. ISTM this would mean that it could choose a bad split if the incoming item goes at the end and both it and the last extant item are large: in this case they should be split apart, but they won't be. Heikki, do you feel like looking at that, or shall I? I refactored findsplitloc and checksplitloc so that the division of labor is more clear IMO. I pushed all the space calculation inside the loop to checksplitloc. I also fixed the off by 4 in free space calculation caused by PageGetFreeSpace subtracting sizeof(ItemIdData), even though it was harmless, because it was distracting and I felt it might come back to bite us in the future if we change the page layout or alignments. There's now a new function PageGetExactFreeSpace that doesn't do the subtraction. findsplitloc now tries the just the new item to right page split as well. If people don't like the refactoring, I can write a patch to just add that. Some of the long variable names look ugly. camelCase or underscores between words would be more readable, but I retained the old style for the sake of consistency. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [previously on HACKERS] Compacting a relation
I applied the optional VACUUM FULL version, but modified to code to say 20% rather than a factor of 5, attached. --- Simon Riggs wrote: On Mon, 2007-02-05 at 11:55 +, Simon Riggs wrote: On Sat, 2007-02-03 at 22:11 -0500, Bruce Momjian wrote: Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: vacuumlazy.c contains a hint Consider compacting this relation but AFAICT, there is no indication anywhere how compacting is supposed to be achieved. I guess this means VACUUM FULL or CLUSTER, but I don't think the hint can be processed effectively by a user. So change it ... New message is: errhint(Consider using VACUUM FULL on this relation or increasing the configuration parameter \max_fsm_pages\.))); The change of wording may be appropriate, but it is triggered when if (vacrelstats-tot_free_pages MaxFSMPages) So if you VACUUM a 15+GB table and it has only 1% freespace then it will still generate this message. Hopefully you'd agree that the message would be inappropriate in that case. It's also inappropriate because this message is generated *prior* to doing lazy_truncate_heap(), which could easily remove lots of empty pages anyhow. That might reduce it to less than MaxFSMPages anyhow, so it can currently be triggered in wholly inappropriate situations. So I suggest that we move this wording after lazy_truncate_heap() in lazy_vacuum_rel() *and* we alter the hint so that it only suggests VACUUM FULL if the table has 20% fragmentation, whatever its size. Happy to drop a patch for this, if people agree. Enclose 2 versions: v1 - move test and WARNING v2 - move test and WARNING, plus adjust hint according to relation size -- Simon Riggs EnterpriseDB http://www.enterprisedb.com [ Attachment, skipping... ] [ Attachment, skipping... ] -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: src/backend/commands/vacuumlazy.c === RCS file: /cvsroot/pgsql/src/backend/commands/vacuumlazy.c,v retrieving revision 1.83 diff -c -c -r1.83 vacuumlazy.c *** src/backend/commands/vacuumlazy.c 4 Feb 2007 03:10:55 - 1.83 --- src/backend/commands/vacuumlazy.c 21 Feb 2007 22:13:59 - *** *** 180,185 --- 180,195 /* Update shared free space map with final free space info */ lazy_update_fsm(onerel, vacrelstats); + if (vacrelstats-tot_free_pages MaxFSMPages) + ereport(WARNING, + (errmsg(relation \%s.%s\ contains more than \max_fsm_pages\ pages with useful free space, + get_namespace_name(RelationGetNamespace(onerel)), + RelationGetRelationName(onerel)), + errhint(Consider%sincreasing the configuration parameter \max_fsm_pages\., + /* Only suggest VACUUM FULL if 20% free */ + (vacrelstats-tot_free_pages vacrelstats-rel_pages * 0.20 + ? using VACUUM FULL on this relation or : ; + /* Update statistics in pg_class */ vac_update_relstats(RelationGetRelid(onerel), vacrelstats-rel_pages, *** *** 507,519 vacrelstats-tot_free_pages, empty_pages, pg_rusage_show(ru0; - - if (vacrelstats-tot_free_pages MaxFSMPages) - ereport(WARNING, - (errmsg(relation \%s.%s\ contains more than \max_fsm_pages\ pages with useful free space, - get_namespace_name(RelationGetNamespace(onerel)), - relname), - errhint(Consider using VACUUM FULL on this relation or increasing the configuration parameter \max_fsm_pages\.))); } --- 517,522 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [previously on HACKERS] Compacting a relation
Bruce Momjian wrote: I applied the optional VACUUM FULL version, but modified to code to say 20% rather than a factor of 5, attached. String construction does not work well with translations; please reformulate this. + errhint(Consider%sincreasing the configuration parameter \max_fsm_pages\., + /* Only suggest VACUUM FULL if 20% free */ + (vacrelstats-tot_free_pages vacrelstats-rel_pages * 0.20 + ? using VACUUM FULL on this relation or : ; -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [previously on HACKERS] Compacting a relation
Alvaro Herrera wrote: Bruce Momjian wrote: I applied the optional VACUUM FULL version, but modified to code to say 20% rather than a factor of 5, attached. String construction does not work well with translations; please reformulate this. +errhint(Consider%sincreasing the configuration parameter \max_fsm_pages\., + /* Only suggest VACUUM FULL if 20% free */ + (vacrelstats-tot_free_pages vacrelstats-rel_pages * 0.20 + ? using VACUUM FULL on this relation or : ; OK, updated. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: src/backend/commands/vacuumlazy.c === RCS file: /cvsroot/pgsql/src/backend/commands/vacuumlazy.c,v retrieving revision 1.84 diff -c -c -r1.84 vacuumlazy.c *** src/backend/commands/vacuumlazy.c 21 Feb 2007 22:15:21 - 1.84 --- src/backend/commands/vacuumlazy.c 21 Feb 2007 22:41:55 - *** *** 185,194 (errmsg(relation \%s.%s\ contains more than \max_fsm_pages\ pages with useful free space, get_namespace_name(RelationGetNamespace(onerel)), RelationGetRelationName(onerel)), ! errhint(Consider%sincreasing the configuration parameter \max_fsm_pages\., ! /* Only suggest VACUUM FULL if 20% free */ ! (vacrelstats-tot_free_pages vacrelstats-rel_pages * 0.20 ! ? using VACUUM FULL on this relation or : ; /* Update statistics in pg_class */ vac_update_relstats(RelationGetRelid(onerel), --- 185,194 (errmsg(relation \%s.%s\ contains more than \max_fsm_pages\ pages with useful free space, get_namespace_name(RelationGetNamespace(onerel)), RelationGetRelationName(onerel)), ! errhint((vacrelstats-tot_free_pages vacrelstats-rel_pages * 0.20 ? ! /* Only suggest VACUUM FULL if 20% free */ ! Consider using VACUUM FULL on this relation or increasing the configuration parameter \max_fsm_pages\. : ! Consider increasing the configuration parameter \max_fsm_pages\.; /* Update statistics in pg_class */ vac_update_relstats(RelationGetRelid(onerel), ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] First implementation of GIN for pg_trgm
Hi all, Here is my preliminary work on porting pg_trgm to GIN. pg_trgm can be a very good addition to tsearch2 to suggest spellings for mispelled words as explained in the README.pg_trgm file and I'd like to use it in this case. GIST implementation is a bit slow so I tried to port it to use GIN. The attached patch is the first working implementation. It's not final but I would like some feedback on how to fix the remaining problems. From a previous discussion with Teodor, it would be better to store an int in the index instead of a text (it takes less space and is faster). I couldn't find any example so if anyone has an advice to fix that, it's welcome (mostly how to pack the trigram into an int instead of a text). The last problem is that similarity calculated in the GIN index is higher than the one with GIST so I have to set the trgm_limit quite high to have decent results (a limit of 0.8 instead of 0.3 seems to be quite good). AFAICS, it comes from the fact that I couldn't find any way to get the length of the indexed trigram which is taken into account with GIST so we're not exactly filtering the results in the same way. Does anyone have an idea on how to fix this point? Thanks for your attention. -- Guillaume Index: Makefile === RCS file: /projects/cvsroot/pgsql/contrib/pg_trgm/Makefile,v retrieving revision 1.6 diff -c -r1.6 Makefile *** Makefile9 Feb 2007 17:24:33 - 1.6 --- Makefile21 Feb 2007 23:49:37 - *** *** 1,7 # $PostgreSQL: pgsql/contrib/pg_trgm/Makefile,v 1.6 2007/02/09 17:24:33 petere Exp $ MODULE_big = pg_trgm ! OBJS = trgm_op.o trgm_gist.o DATA_built = pg_trgm.sql DATA = uninstall_pg_trgm.sql --- 1,7 # $PostgreSQL: pgsql/contrib/pg_trgm/Makefile,v 1.6 2007/02/09 17:24:33 petere Exp $ MODULE_big = pg_trgm ! OBJS = trgm_op.o trgm_gist.o trgm_gin.o DATA_built = pg_trgm.sql DATA = uninstall_pg_trgm.sql Index: uninstall_pg_trgm.sql === RCS file: /projects/cvsroot/pgsql/contrib/pg_trgm/uninstall_pg_trgm.sql,v retrieving revision 1.2 diff -c -r1.2 uninstall_pg_trgm.sql *** uninstall_pg_trgm.sql 13 Mar 2006 18:04:58 - 1.2 --- uninstall_pg_trgm.sql 21 Feb 2007 23:49:37 - *** *** 20,25 --- 20,33 DROP TYPE gtrgm CASCADE; + DROP OPERATOR CLASS gin_trgm_ops USING gin; + + DROP FUNCTION gin_extract_trgm(text, internal); + + DROP FUNCTION gin_extract_trgm(text, internal, internal); + + DROP FUNCTION gin_trgm_consistent(internal, internal, text); + DROP OPERATOR % (text, text); DROP FUNCTION similarity_op(text,text); Index: pg_trgm.sql.in === RCS file: /projects/cvsroot/pgsql/contrib/pg_trgm/pg_trgm.sql.in,v retrieving revision 1.2 diff -c -r1.2 pg_trgm.sql.in *** pg_trgm.sql.in 27 Feb 2006 16:09:48 - 1.2 --- pg_trgm.sql.in 21 Feb 2007 23:49:37 - *** *** 36,42 JOIN = contjoinsel ); ! --gist key CREATE FUNCTION gtrgm_in(cstring) RETURNS gtrgm AS 'MODULE_PATHNAME' --- 36,42 JOIN = contjoinsel ); ! -- gist key CREATE FUNCTION gtrgm_in(cstring) RETURNS gtrgm AS 'MODULE_PATHNAME' *** *** 53,59 OUTPUT = gtrgm_out ); ! -- support functions CREATE FUNCTION gtrgm_consistent(gtrgm,internal,int4) RETURNS bool AS 'MODULE_PATHNAME' --- 53,59 OUTPUT = gtrgm_out ); ! -- support functions for gist CREATE FUNCTION gtrgm_consistent(gtrgm,internal,int4) RETURNS bool AS 'MODULE_PATHNAME' *** *** 89,95 AS 'MODULE_PATHNAME' LANGUAGE C; ! -- create the operator class CREATE OPERATOR CLASS gist_trgm_ops FOR TYPE text USING gist AS --- 89,95 AS 'MODULE_PATHNAME' LANGUAGE C; ! -- create the operator class for gist CREATE OPERATOR CLASS gist_trgm_ops FOR TYPE text USING gist AS *** *** 103,107 --- 103,133 FUNCTION7 gtrgm_same (gtrgm, gtrgm, internal), STORAGE gtrgm; + -- support functions for gin + CREATE FUNCTION gin_extract_trgm(text, internal) + RETURNS internal + AS 'MODULE_PATHNAME' + LANGUAGE C; + + CREATE FUNCTION gin_extract_trgm(text, internal, internal) + RETURNS internal + AS 'MODULE_PATHNAME' + LANGUAGE C; + + CREATE FUNCTION gin_trgm_consistent(internal, internal, text) + RETURNS internal + AS 'MODULE_PATHNAME' + LANGUAGE C; + + -- create the operator class for gin + CREATE OPERATOR CLASS gin_trgm_ops + FOR TYPE text USING gin + AS + OPERATOR1 % (text, text), + FUNCTION1 bttextcmp (text, text), + FUNCTION2 gin_extract_trgm (text, internal), + FUNCTION3 gin_extract_trgm (text, internal, internal), + FUNCTION4
Re: [PATCHES] [previously on HACKERS] Compacting a relation
Bruce Momjian wrote: Alvaro Herrera wrote: Bruce Momjian wrote: I applied the optional VACUUM FULL version, but modified to code to say 20% rather than a factor of 5, attached. String construction does not work well with translations; please reformulate this. + errhint(Consider%sincreasing the configuration parameter \max_fsm_pages\., + /* Only suggest VACUUM FULL if 20% free */ + (vacrelstats-tot_free_pages vacrelstats-rel_pages * 0.20 + ? using VACUUM FULL on this relation or : ; OK, updated. Thanks :-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP patch - INSERT-able log statements
On Thu, 22 Feb 2007, FAST PostgreSQL wrote: As we are triggering the sql output in log_destination, if the user gives 'syslog,sql' as options he is going to get two different looking logs (in terms of contents) depending upon his settings. Yes, exactly; it's a good thing. People add and remove things from the text logs to make them easier to read. It's one of the reasons they're harder to process. Since readability isn't a requirement for the SQL formatted ones, you can pack a lot more into there and make it available easily anyway. I keep having every part of this conversation twice, so here's take two on this one. The things that people want out of the text logs are not necessarily the same things they want from the SQL ones. For example, I have a situation where the semantics of the syslog output is being driven by Sarbanes-Oxley related mechanics. But the SQL logs are be based on my requirements, which is to include close enough to everything that it might as well be the whole set, in case I forgot something I find I need later. The SQL logs are *completely* different from the syslog setup. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] BLCKSZ fun facts
Bruce Momjian wrote: I have implemented your ideas for checking BLCKSZ = 1024, I think the check should be were the issue arises, not in some distant file without explanation. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
