[PATCHES] contrib/otherlock
Someone (David Fetter?) mentioned long ago that contrib/userlock could not be moved into core because it is GPLed, and the author couldn't be contacted to ask about re-licensing. Andrew(@supernews) wrote a specification for the userlock functionality, and I implemented the attached code based on his specification, without consulting the existing source. It's available under a BSD license, if there's any use for it. -- ams /* * Abhijit Menon-Sen [EMAIL PROTECTED] * 2006-09-03 */ #include postgres.h #include storage/lock.h #include miscadmin.h #include fmgr.h PG_MODULE_MAGIC; static int lock(uint32, uint32, uint32); static int unlock(uint32, uint32, uint32); Datum user_lock(PG_FUNCTION_ARGS); Datum user_unlock(PG_FUNCTION_ARGS); Datum user_write_lock(PG_FUNCTION_ARGS); Datum user_write_unlock(PG_FUNCTION_ARGS); Datum user_write_lock_o(PG_FUNCTION_ARGS); Datum user_write_unlock_o(PG_FUNCTION_ARGS); Datum user_write_lock_oid(PG_FUNCTION_ARGS); Datum user_write_unlock_oid(PG_FUNCTION_ARGS); Datum user_write_lock_oid_i(PG_FUNCTION_ARGS); Datum user_write_unlock_oid_i(PG_FUNCTION_ARGS); Datum user_unlock_all(PG_FUNCTION_ARGS); static int lock(uint32 group, uint32 id, uint32 lockmode) { int r; LOCKTAG tag; tag.locktag_field1 = MyDatabaseId; tag.locktag_field2 = group; tag.locktag_field3 = id; tag.locktag_field4 = 0; tag.locktag_type = LOCKTAG_USERLOCK; tag.locktag_lockmethodid = USER_LOCKMETHOD; r = LockAcquire(tag, lockmode, true, true); if ( r 1 ) r = 1; return r; } static int unlock(uint32 group, uint32 id, uint32 lockmode) { int r; LOCKTAG tag; tag.locktag_field1 = MyDatabaseId; tag.locktag_field2 = group; tag.locktag_field3 = id; tag.locktag_field4 = 0; tag.locktag_type = LOCKTAG_USERLOCK; tag.locktag_lockmethodid = USER_LOCKMETHOD; if (LockRelease(tag, lockmode, true)) r = 1; else r = 0; return r; } PG_FUNCTION_INFO_V1(user_lock); Datum user_lock(PG_FUNCTION_ARGS) { int r; uint32 group = PG_GETARG_INT32(0); uint32 id = PG_GETARG_INT32(1); uint32 lockmode = PG_GETARG_INT32(2); r = lock(group, id, lockmode); PG_RETURN_INT32(r); } PG_FUNCTION_INFO_V1(user_unlock); Datum user_unlock(PG_FUNCTION_ARGS) { int r; uint32 group = PG_GETARG_INT32(0); uint32 id = PG_GETARG_INT32(1); uint32 lockmode = PG_GETARG_INT32(2); r = unlock(group, id, lockmode); PG_RETURN_INT32(r); } PG_FUNCTION_INFO_V1(user_write_lock); Datum user_write_lock(PG_FUNCTION_ARGS) { int r; uint32 group = PG_GETARG_INT32(0); uint32 id = PG_GETARG_INT32(1); r = lock(group, id, ExclusiveLock); PG_RETURN_INT32(r); } PG_FUNCTION_INFO_V1(user_write_unlock); Datum user_write_unlock(PG_FUNCTION_ARGS) { int r; uint32 group = PG_GETARG_INT32(0); uint32 id = PG_GETARG_INT32(1); r = unlock(group, id, ExclusiveLock); PG_RETURN_INT32(r); } PG_FUNCTION_INFO_V1(user_write_lock_o); Datum user_write_lock_o(PG_FUNCTION_ARGS) { int r; uint32 group = PG_GETARG_INT32(0); uint32 id = PG_GETARG_OID(1); r = lock(group, id, ExclusiveLock); PG_RETURN_INT32(r); } PG_FUNCTION_INFO_V1(user_write_unlock_o); Datum user_write_unlock_o(PG_FUNCTION_ARGS) { int r; uint32 group = PG_GETARG_INT32(0); uint32 id = PG_GETARG_OID(1); r = unlock(group, id, ExclusiveLock); PG_RETURN_INT32(r); } PG_FUNCTION_INFO_V1(user_write_lock_oid); Datum user_write_lock_oid(PG_FUNCTION_ARGS) { int r; uint32 id = PG_GETARG_OID(0); r = lock(0, id, ExclusiveLock); PG_RETURN_INT32(r); } PG_FUNCTION_INFO_V1(user_write_unlock_oid); Datum user_write_unlock_oid(PG_FUNCTION_ARGS) { int r; uint32 id = PG_GETARG_OID(0); r = unlock(0, id, ExclusiveLock); PG_RETURN_INT32(r); } PG_FUNCTION_INFO_V1(user_write_lock_oid_i); Datum user_write_lock_oid_i(PG_FUNCTION_ARGS) { int r; uint32 id = PG_GETARG_INT32(0); r = lock(0, id, ExclusiveLock); PG_RETURN_INT32(r); } PG_FUNCTION_INFO_V1(user_write_unlock_oid_i); Datum user_write_unlock_oid_i(PG_FUNCTION_ARGS) { int r; uint32 id = PG_GETARG_INT32(0); r = unlock(0, id, ExclusiveLock); PG_RETURN_INT32(r); } PG_FUNCTION_INFO_V1(user_unlock_all); Datum user_unlock_all(PG_FUNCTION_ARGS) { LockReleaseAll(USER_LOCKMETHOD, true); PG_RETURN_INT32(1); } CREATE FUNCTION user_lock(int4,int4,int4) returns int4 AS 'MODULE_PATHNAME', 'user_lock' LANGUAGE C VOLATILE STRICT; CREATE FUNCTION user_unlock(int4,int4,int4) returns int4 AS 'MODULE_PATHNAME', 'user_unlock' LANGUAGE C VOLATILE STRICT; CREATE FUNCTION user_write_lock(int4,int4) returns int4 AS 'MODULE_PATHNAME', 'user_write_lock' LANGUAGE C VOLATILE STRICT; CREATE FUNCTION user_write_unlock(int4,int4) returns int4 AS 'MODULE_PATHNAME', 'user_write_unlock' LANGUAGE C VOLATILE STRICT; CREATE FUNCTION user_write_lock(int4,oid) returns int4 AS
[PATCHES] Documentation fix for --with-ldap
This is just a 'one line' change in the documentation of the --with-ldap flag of ./configure Yours, Laurenz Albe ldapdoc.patch Description: ldapdoc.patch ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Interval month, week - day
On Sun, Sep 03, 2006 at 10:21:11PM -0400, Bruce Momjian wrote: When I tried the ecpg regression tests it complained there was no results/ directory. I created one and it worked. Hmm, anyone else experiencing this? The pg_regress.sh has this code that should create it: outputdir=results/ if [ ! -d $outputdir ]; then mkdir -p $outputdir || { (exit 2); exit; } fi Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Interval month, week - day
Michael Meskes [EMAIL PROTECTED] writes: On Sun, Sep 03, 2006 at 10:21:11PM -0400, Bruce Momjian wrote: When I tried the ecpg regression tests it complained there was no results/ directory. I created one and it worked. Hmm, anyone else experiencing this? The pg_regress.sh has this code that should create it: outputdir=results/ if [ ! -d $outputdir ]; then mkdir -p $outputdir || { (exit 2); exit; } fi I'll bet you should lose the slash in $outputdir. test(1) might or might not be friendly about stripping that off. 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
[PATCHES] Fix PGPORT reassignment in ecpg regression tests
The pg_regress.sh script for ecpg regression tests checks to make sure the port number is between 1024 and 65535. If it isn't, it uses 65432. (c310-315. This is the same behavior as the standard regression tests, I believe.) However, it if does reassign the port number, it was changing it back to the original, supplied port number after creating the installation. This would cause the tests to fail as the tests were run against a different port (the original supplied port) while the server was listening on 65432. This patch removes the subsequent assignment back to the original port number. Passes both the standard regression tests and, more importantly, those for ecpg, with normal and abnormally high port numbers. Michael Glaesemann grzm seespotcode net Index: src/interfaces/ecpg/test/pg_regress.sh === RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/test/ pg_regress.sh,v retrieving revision 1.9 diff -c -r1.9 pg_regress.sh *** src/interfaces/ecpg/test/pg_regress.sh 29 Aug 2006 13:23:26 - 1.9 --- src/interfaces/ecpg/test/pg_regress.sh 4 Sep 2006 14:22:17 - *** *** 644,650 if [ x$temp_install != x ] then do_temp_install - PGPORT=$temp_port; export PGPORT else # not temp-install dont_temp_install fi --- 644,649 ---(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] Backend SSL configuration enhancement
Bruce Momjian [EMAIL PROTECTED] writes: This has been saved for the 8.3 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold This version was withdrawn by the author for rework, no? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] setseed() doc
The doc doesn't state in what range the argument to setseed() should be. Some tests suggest that only values in the range -1.0 to 1.0 work as a seed and values outside of that give the same sequence of random numbers. I've attached a trivial one line patch (this is the patch list after all), but feel free to document it in any way that is appropriate. setseed() also return an integer, but I have no clue of what it is. The doc doesn't say anything about it. The doc say that some functions here depend on the libc that is used, but such things as the range of the argument and what the return value is should be in the doc, shouldn't it? /Dennis Index: doc/src/sgml/func.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.332 diff -u -r1.332 func.sgml --- doc/src/sgml/func.sgml 22 Aug 2006 00:49:19 - 1.332 +++ doc/src/sgml/func.sgml 4 Sep 2006 18:18:26 - @@ -795,7 +795,7 @@ row entryliteralfunctionsetseed/function(typedp/type)/literal/entry entrytypeint/type/entry - entryset seed for subsequent literalrandom()/literal calls/entry + entryset seed for subsequent literalrandom()/literal calls (value between -1.0 and 1.0)/entry entryliteralsetseed(0.54823)/literal/entry entryliteral1177314959/literal/entry /row ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] setseed() doc
Tom Lane skrev: entryliteralfunctionsetseed/function(typedp/type)/literal/entry entrytypeint/type/entry - entryset seed for subsequent literalrandom()/literal calls/entry + entryset seed for subsequent literalrandom()/literal calls (value between -1.0 and 1.0)/entry Looking at the code, it would appear that the intended range is 0 to 1. Ok. What about the return value? The doc didn't say anything about it. /Dennis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] setseed() doc
Dennis Bjorklund [EMAIL PROTECTED] writes: What about the return value? The doc didn't say anything about it. AFAICT it's just junk. It happens to be the input times MAX_RANDOM_VALUE, but what use is that? I wonder if we shouldn't change the function to return VOID ... that option wasn't available when it was coded originally, else it'd probably have been done that way. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Have psql show current sequnce values - (Resubmission)
Dhanaraj M [EMAIL PROTECTED] writes: Sorry for resubmitting this patch. Just now I found a problem. Instead of assigning initial sequence value to 1, I assign LLONG_MAX to avoid the buffer overflow problem. Please find the current version here. This patch is a mess. In the first place, it's completely unkosher for an application to scribble on a PGresult's contents, even if you do take steps like the above to try to make sure there's enough space. But said step does not work anyway -- LLONG_MAX might not exist on the client, or might exist but be smaller than the server's value. Another problem with it is it's not schema-aware and not proof against quoting requirements for the sequence name (try it with a mixed-case sequence name for instance). It also ought to pay some attention to the possibility that the SELECT for last_value fails --- quite aside from communication failure or such, there might be a permissions problem preventing the last_value from being read. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Interval month, week - day
Tom Lane wrote: Michael Meskes [EMAIL PROTECTED] writes: On Sun, Sep 03, 2006 at 10:21:11PM -0400, Bruce Momjian wrote: When I tried the ecpg regression tests it complained there was no results/ directory. I created one and it worked. Hmm, anyone else experiencing this? The pg_regress.sh has this code that should create it: outputdir=results/ if [ ! -d $outputdir ]; then mkdir -p $outputdir || { (exit 2); exit; } fi I'll bet you should lose the slash in $outputdir. test(1) might or might not be friendly about stripping that off. Yep, I saw this error: mkdir: results/: No such file or directory gmake: *** [installcheck] Error 2 I have removed the trailing slash from CVS; tests run fine now. Thanks. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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] Fix linking of OpenLDAP libraries
Albe Laurenz [EMAIL PROTECTED] writes: # The backend doesn't need everything that's in LIBS, however ! LIBS := $(filter-out -lz -lreadline -ledit -ltermcap -lncurses -lcurses -lldap_r $(PTHREAD_LIBS), $(LIBS)) This seems pretty risky. What if PTHREAD_LIBS contains -L switches? They'd get removed even if needed for other libraries. It would probably be safer not to put LDAP into LIBS at all, but invent two new macros for configure to set, say LDAP_LIBS and LDAP_LIBS_R, and add these to the link lines in the backend and libpq respectively. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Backend SSL configuration enhancement
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: This has been saved for the 8.3 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold This version was withdrawn by the author for rework, no? Right, and the thread in patches_hold shows that. The reason it is in there is so we can ping the author at the start of 8.3 to get an updated version. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Partial Index wording as per BUG
Patch applied. Thanks. parameterised changed to parameterized, for consistency with existing documentation. (Yea, I know they are both correct.) --- Simon Riggs wrote: Doc patch in response to bug raised: [BUGS] partial indexes not used on parameterized queries? 10 July 2006 -- Simon Riggs EnterpriseDB http://www.enterprisedb.com [ Attachment, skipping... ] ---(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 -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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] Information_schema fixes for sequences and
Patch applied. Thanks. I did not batckpatch because someone would need to re-initdb to see the changes, and we haven't gotten any complaints about the bug. --- Greg Sabino Mullane wrote: -- Start of PGP signed section. More to come, but these two are probably worth backpatching. Sequences were not being shown due to the use of lowercase 's' instead of 'S', and the views were not checking for table visibility with regards to temporary tables and sequences. -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200608181942 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 [ Attachment, skipping... ] -- End of PGP section, PGP failed! -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Information schema - finalize key_column_usage
This patch fails in initdb with: creating information schema ... FATAL: column ss.confrelid does not exist at character 30245 Please fix and resubmit soon. --- Greg Sabino Mullane wrote: -- Start of PGP signed section. Correctly populates the position_in_unique_constraint column in the information_schema.key_column_usage view. -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200608182231 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 [ Attachment, skipping... ] -- End of PGP section, PGP failed! -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Have psql show current sequnce values -
Due to Tom's feedback: This has been saved for the 8.3 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Dhanaraj M wrote: Sorry for resubmitting this patch. Just now I found a problem. Instead of assigning initial sequence value to 1, I assign LLONG_MAX to avoid the buffer overflow problem. Please find the current version here. Dhanaraj M wrote: Hi all, This patch was discussed a few months ago. I could not complete this patch at that time. I hope that the current version of my patch is acceptable. Patch details: ** 1. Assign a new field called 'Seq Value' for \ds command 2. All the sequence values are '1' initially 3. After executing the query, call AssignSeqValue() 4. This function assigns the respective sequence values back to the resultset Please review and comment on this patch. Thanks Dhanaraj Tom Lane wrote: Dhanaraj M [EMAIL PROTECTED] writes: However, it was not possible to display the seq. value using this. Hence, I made a small change in the currval() function, so that it retrieves the last_value even if the the value is not cached. Breaking currval()'s semantics is not an acceptable solution for this. The best, fully backward compatible solution is for psql to issue SELECT last_value FROM seq queries to get the values. This might be a bit tricky to wedge into the structure of describe.c, but I don't see any fundamental reason why it can't be done. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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
Re: [PATCHES] [HACKERS] Unable to post to -patches (was: Visual C++
Patch applied. Placed in src/tools/msvc. Thanks. --- Magnus Hagander wrote: a.hub.org[200.46.208.251], delay=1, status=sent (250 2.7.1 Ok, discarded, id=258 35-09 - BANNED: P=p003,L=1,M=multipart/mixed | P=p002,L=1/2,M=application/x-gzip ,T=gz,N=vcbuild.tar.gz | P=p...) Seems -patches is rejecting any mail with attached .tar.gz files, if I read that correctly? Hm, I just managed to send a patch labeled application/octet-stream without any problem. Not sure what's the point in banning application/x-gzip, unless that's a common virus signature? I doubt it would be, and if it is then really, it's still not a very smart thing to do IMHO :) Anyway try the other MIME type. Hmm. I can't really control the MIME type out of my system (remember, running Exchange here..). But I guess I can rename the file ;-) Attempting here to get it into the archives at least.. //Magnus [note, file is a .tar.gz even though it doesn't look that way] Content-Description: vcbuild.tar.gz.bin [ Attachment, skipping... ] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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] plpgsql, return can contains any expression
I am going to need this in 24 hours or it might not make it into 8.2. --- bruce wrote: While this patch has new regression tests, it doesn't have new expected output for it. Please update the patch to supply that. Thanks. --- Pavel Stehule wrote: Hello This patch allows using any row expression in return statement and does transformation from untyped row to composite types if it's necessary. Regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Trivial patch to double vacuum speed
Patch applied. Thanks. --- Gregory Stark wrote: Tom Lane [EMAIL PROTECTED] writes: The reason the patch is so short is that it's a kluge. If we really cared about supporting this case, more wide-ranging changes would be needed (eg, there's no need to eat maintenance_work_mem worth of RAM for the dead-TIDs array); and a decent respect to the opinions of mankind would require some attention to updating the header comments and function descriptions, too. The only part that seems klugy to me is how it releases the lock and reacquires it rather than wait in the first place until it can acquire the lock. Fixed that and changed lazy_space_alloc to allocate only as much space as is really necessary. Gosh, I've never been accused of offending all mankind before. --- vacuumlazy.c 31 Jul 2006 21:09:00 +0100 1.76 +++ vacuumlazy.c 28 Aug 2006 09:58:41 +0100 @@ -16,6 +16,10 @@ * perform a pass of index cleanup and page compaction, then resume the heap * scan with an empty TID array. * + * As a special exception if we're processing a table with no indexes we can + * vacuum each page as we go so we don't need to allocate more space than + * enough to hold as many heap tuples fit on one page. + * * We can limit the storage for page free space to MaxFSMPages entries, * since that's the most the free space map will be willing to remember * anyway. If the relation has fewer than that many pages with free space, @@ -106,7 +110,7 @@ TransactionId OldestXmin); static BlockNumber count_nondeletable_pages(Relation onerel, LVRelStats *vacrelstats, TransactionId OldestXmin); -static void lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks); +static void lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks, unsigned nindexes); static void lazy_record_dead_tuple(LVRelStats *vacrelstats, ItemPointer itemptr); static void lazy_record_free_space(LVRelStats *vacrelstats, @@ -206,7 +210,8 @@ * This routine sets commit status bits, builds lists of dead tuples * and pages with free space, and calculates statistics on the number * of live tuples in the heap. When done, or when we run low on space - * for dead-tuple TIDs, invoke vacuuming of indexes and heap. + * for dead-tuple TIDs, or after every page if the table has no indexes + * invoke vacuuming of indexes and heap. * * It also updates the minimum Xid found anywhere on the table in * vacrelstats-minxid, for later storing it in pg_class.relminxid. @@ -247,7 +252,7 @@ vacrelstats-rel_pages = nblocks; vacrelstats-nonempty_pages = 0; - lazy_space_alloc(vacrelstats, nblocks); + lazy_space_alloc(vacrelstats, nblocks, nindexes); for (blkno = 0; blkno nblocks; blkno++) { @@ -282,8 +287,14 @@ buf = ReadBuffer(onerel, blkno); - /* In this phase we only need shared access to the buffer */ - LockBuffer(buf, BUFFER_LOCK_SHARE); + /* In this phase we only need shared access to the buffer unless we're + * going to do the vacuuming now which we do if there are no indexes + */ + + if (nindexes) + LockBuffer(buf, BUFFER_LOCK_SHARE); + else + LockBufferForCleanup(buf); page = BufferGetPage(buf); @@ -450,6 +461,12 @@ { lazy_record_free_space(vacrelstats, blkno, PageGetFreeSpace(page)); + } else if (!nindexes) { + /* If there are no indexes we can vacuum the page right now instead + * of doing a second scan */ + lazy_vacuum_page(onerel, blkno, buf, 0, vacrelstats); + lazy_record_free_space(vacrelstats, blkno, PageGetFreeSpace(BufferGetPage(buf))); + vacrelstats-num_dead_tuples = 0; } /* Remember the location of the last page with nonremovable tuples */ @@ -891,16 +908,20 @@ * See the comments at the head of this file for rationale. */ static void -lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks) +lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks, unsigned nindexes) { longmaxtuples; int maxpages; - maxtuples = (maintenance_work_mem * 1024L) / sizeof(ItemPointerData); - maxtuples = Min(maxtuples, INT_MAX); - maxtuples = Min(maxtuples, MaxAllocSize /
Re: [HACKERS] [PATCHES] Trivial patch to double vacuum speed on tables with no indexes
Bruce Momjian [EMAIL PROTECTED] writes: Patch applied. Thanks. Wait a minute. This patch changes the behavior so that LockBufferForCleanup is applied to *every* heap page, not only the ones where there are removable tuples. It's not hard to imagine scenarios where that results in severe system-wide performance degradation. Has there been any real-world testing of this idea? 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] Trivial patch to double vacuum speed
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Patch applied. Thanks. Wait a minute. This patch changes the behavior so that LockBufferForCleanup is applied to *every* heap page, not only the ones where there are removable tuples. It's not hard to imagine scenarios where that results in severe system-wide performance degradation. Has there been any real-world testing of this idea? I see the no-index case now: + if (nindexes) + LockBuffer(buf, BUFFER_LOCK_SHARE); + else + LockBufferForCleanup(buf); Let's see what Greg says, or revert. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] setseed() doc
Tom Lane wrote: Dennis Bjorklund [EMAIL PROTECTED] writes: entryliteralfunctionsetseed/function(typedp/type)/literal/entry entrytypeint/type/entry - entryset seed for subsequent literalrandom()/literal calls/entry + entryset seed for subsequent literalrandom()/literal calls (value between -1.0 and 1.0)/entry Looking at the code, it would appear that the intended range is 0 to 1. Docs updated. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/func.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.332 diff -c -c -r1.332 func.sgml *** doc/src/sgml/func.sgml 22 Aug 2006 00:49:19 - 1.332 --- doc/src/sgml/func.sgml 4 Sep 2006 21:45:15 - *** *** 795,801 row entryliteralfunctionsetseed/function(typedp/type)/literal/entry entrytypeint/type/entry !entryset seed for subsequent literalrandom()/literal calls/entry entryliteralsetseed(0.54823)/literal/entry entryliteral1177314959/literal/entry /row --- 795,801 row entryliteralfunctionsetseed/function(typedp/type)/literal/entry entrytypeint/type/entry !entryset seed for subsequent literalrandom()/literal calls (value between 0 and 1.0)/entry entryliteralsetseed(0.54823)/literal/entry entryliteral1177314959/literal/entry /row ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Trivial patch to double vacuum speed on tables with no indexes
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Patch applied. Thanks. Wait a minute. This patch changes the behavior so that LockBufferForCleanup is applied to *every* heap page, not only the ones where there are removable tuples. It's not hard to imagine scenarios where that results in severe system-wide performance degradation. Has there been any real-world testing of this idea? I see the no-index case now: + if (nindexes) + LockBuffer(buf, BUFFER_LOCK_SHARE); + else + LockBufferForCleanup(buf); Let's see what Greg says, or revert. Hm, that's a good point. I could return it to the original method where it released the share lock and did he LockBufferForCleanup only if necessary. I thought it was awkward to acquire a lock then release it to acquire a different lock on the same buffer but it's true that it doesn't always have to acquire the second lock. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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] Information schema - finalize key_column_usage
Please fix and resubmit soon. Attached version should now work properly. -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200609041803 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 Index: information_schema.sql === RCS file: /projects/cvsroot/pgsql/src/backend/catalog/information_schema.sql,v retrieving revision 1.34 diff -c -r1.34 information_schema.sql *** information_schema.sql 4 Sep 2006 21:03:18 - 1.34 --- information_schema.sql 4 Sep 2006 22:00:33 - *** *** 921,931 CAST(relname AS sql_identifier) AS table_name, CAST(a.attname AS sql_identifier) AS column_name, CAST((ss.x).n AS cardinal_number) AS ordinal_position, !CAST(null AS cardinal_number) AS position_in_unique_constraint -- FIXME FROM pg_attribute a, ! (SELECT r.oid, nc.nspname AS nc_nspname, c.conname, nr.nspname AS nr_nspname, r.relname, ! _pg_expandarray(c.conkey) AS x FROM pg_namespace nr, pg_class r, pg_namespace nc, pg_constraint c WHERE nr.oid = r.relnamespace --- 921,937 CAST(relname AS sql_identifier) AS table_name, CAST(a.attname AS sql_identifier) AS column_name, CAST((ss.x).n AS cardinal_number) AS ordinal_position, !( ! SELECT CAST(a AS cardinal_number) ! FROM pg_constraint, !(SELECT a FROM generate_series(1,(SELECT array_upper(ss.confkey,1))) f(a)) AS foo ! WHERE conrelid = ss.confrelid ! AND conkey[foo.a] = ss.confkey[(ss.x).n] !) AS position_in_unique_constraint FROM pg_attribute a, ! (SELECT r.oid, nc.nspname AS nc_nspname, c.conname, c.confkey, c.confrelid, nr.nspname AS nr_nspname, r.relname, ! _pg_expandarray(c.conkey) AS x FROM pg_namespace nr, pg_class r, pg_namespace nc, pg_constraint c WHERE nr.oid = r.relnamespace signature.asc Description: This is a digitally signed message part
Re: [PATCHES] plpgsql, return can contains any expression
Pavel Stehule [EMAIL PROTECTED] writes: This patch allows using any row expression in return statement and does transformation from untyped row to composite types if it's necessary. This patch doesn't seem to cope with cases where the supplied tuple has the wrong number of columns, and it doesn't look like it's being careful about dropped columns either. Also, that's a mighty bizarre-looking choice of cache memory context in coerce_to_tuple ... but then again, why are you bothering with a cache at all for temporary arrays? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Information schema - finalize key_column_usage
Greg Sabino Mullane [EMAIL PROTECTED] writes: Attached version should now work properly. Applied, thanks. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] DOC: catalog.sgml
On Sun, Sep 03, 2006 at 12:01:06AM -0400, Tom Lane wrote: But ever since 7.3 the convention for identifying system objects has been pretty well-defined: anything that lives in one of the predefined schemas. What problem were you having using that approach in newsysviews? It was just an issue of trawling through pg_dump to confirm that. -- Jim C. Nasby, Database Architect [EMAIL PROTECTED] 512.569.9461 (cell) http://jim.nasby.net ---(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] contrib/otherlock
This has been saved for the 8.3 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Abhijit Menon-Sen wrote: Someone (David Fetter?) mentioned long ago that contrib/userlock could not be moved into core because it is GPLed, and the author couldn't be contacted to ask about re-licensing. Andrew(@supernews) wrote a specification for the userlock functionality, and I implemented the attached code based on his specification, without consulting the existing source. It's available under a BSD license, if there's any use for it. -- ams [ Attachment, skipping... ] [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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
[PATCHES] XML syntax patch
I have received an update XML syntax patch from Nikolay (summer of code) based on David Fetter's patch from 2005. Comments? It would be nice to have for 8.2. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: src/include/utils/builtins.h === RCS file: /projects/cvsroot/pgsql/src/include/utils/builtins.h,v retrieving revision 1.281 diff -u -r1.281 builtins.h --- src/include/utils/builtins.h 28 Jul 2006 18:33:04 - 1.281 +++ src/include/utils/builtins.h 4 Sep 2006 23:33:10 - @@ -905,4 +905,9 @@ /* utils/mmgr/portalmem.c */ extern Datum pg_cursor(PG_FUNCTION_ARGS); +/* SQL/XML auxilliary functions (now as a part of varchar.c) */ +extern Datum text_xmlagg_accum(PG_FUNCTION_ARGS); +extern Datum text_xmlagg(PG_FUNCTION_ARGS); + + #endif /* BUILTINS_H */ Index: src/include/catalog/pg_proc.h === RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v retrieving revision 1.422 diff -u -r1.422 pg_proc.h --- src/include/catalog/pg_proc.h 19 Aug 2006 01:36:33 - 1.422 +++ src/include/catalog/pg_proc.h 4 Sep 2006 23:33:02 - @@ -2756,6 +2756,12 @@ DESCR(COVAR_SAMP(double, double) aggregate final function); DATA(insert OID = 2817 ( float8_corrPGNSP PGUID 12 f f t f i 1 701 1022 _null_ _null_ _null_ float8_corr - _null_ )); DESCR(CORR(double, double) aggregate final function); +DATA(insert OID = 5011 ( text_xmlagg_accum PGNSP PGUID 12 f f f f i 2 25 25 25 _null_ _null_ _null_ text_xmlagg_accum - _null_ )); +DESCR(XMLAGG accumulate function); +DATA(insert OID = 5012 ( text_xmlaggPGNSP PGUID 12 f f t f i 1 25 25 _null_ _null_ _null_ text_xmlagg - _null_ )); +DESCR(XMLAGG aggregate final function); +DATA(insert OID = 5010 ( xmlagg PGNSP PGUID 12 t f f f i 1 25 25 _null_ _null_ _null_ aggregate_dummy - _null_ )); +DESCR(XMLAGG); /* To ASCII conversion */ DATA(insert OID = 1845 ( to_ascii PGNSP PGUID 12 f f t f i 1 25 25 _null_ _null_ _null_ to_ascii_default - _null_ )); Index: src/include/catalog/pg_aggregate.h === RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_aggregate.h,v retrieving revision 1.57 diff -u -r1.57 pg_aggregate.h --- src/include/catalog/pg_aggregate.h 28 Jul 2006 18:33:04 - 1.57 +++ src/include/catalog/pg_aggregate.h 4 Sep 2006 23:32:43 - @@ -221,6 +221,9 @@ DATA(insert ( 2242 bitand - 0 1560 _null_ )); DATA(insert ( 2243 bitor - 0 1560 _null_ )); +/* xml */ +DATA(insert ( 5010 text_xmlagg_accum text_xmlagg 0 25 _null_ )); + /* * prototypes for functions in pg_aggregate.c */ Index: src/include/nodes/nodes.h === RCS file: /projects/cvsroot/pgsql/src/include/nodes/nodes.h,v retrieving revision 1.187 diff -u -r1.187 nodes.h --- src/include/nodes/nodes.h 2 Aug 2006 01:59:47 - 1.187 +++ src/include/nodes/nodes.h 4 Sep 2006 23:33:05 - @@ -140,6 +140,8 @@ T_RangeTblRef, T_JoinExpr, T_FromExpr, + T_XmlExpr, + T_XmlParams, /* * TAGS FOR EXPRESSION STATE NODES (execnodes.h) @@ -167,6 +169,7 @@ T_MinMaxExprState, T_CoerceToDomainState, T_DomainConstraintState, + T_XmlExprState, /* * TAGS FOR PLANNER NODES (relation.h) Index: src/include/nodes/execnodes.h === RCS file: /projects/cvsroot/pgsql/src/include/nodes/execnodes.h,v retrieving revision 1.160 diff -u -r1.160 execnodes.h --- src/include/nodes/execnodes.h 25 Aug 2006 04:06:56 - 1.160 +++ src/include/nodes/execnodes.h 4 Sep 2006 23:33:05 - @@ -706,6 +706,24 @@ } MinMaxExprState; /* + * XmlExprState node + * + */ +typedef struct XmlExprState +{ + ExprState xprstate; + XmlExprOp op; + List *nargs; /* the named arguments */ + List *args; /* the arguments, only last should be non xml */ + List *xml_args; /* xml arguments, result is always cstring */ + Oid *nargs_tcache; + char **nargs_ncache; + Oid arg_typeId; + XmlParams *params; + int level; /* info about tabs now, shared tag's table in future */ +} XmlExprState; + +/* * CoerceToDomainState node * */ Index: src/include/nodes/primnodes.h === RCS file: /projects/cvsroot/pgsql/src/include/nodes/primnodes.h,v retrieving revision 1.115 diff -u -r1.115 primnodes.h --- src/include/nodes/primnodes.h 27 Jul 2006 19:52:07 - 1.115 +++ src/include/nodes/primnodes.h 4 Sep 2006 23:33:07 - @@ -706,6 +706,57 @@ } MinMaxExpr; /* + * XmlExpr - holder SQL/XML functions XMLROOT, XMLFOREST, XMLELEMENT, + * XMLPI, XMLCOMMENT, XMLCONCAT + */ +typedef enum XmlExprOp +{ + IS_XMLUNKNOWN = 0, + IS_XMLAGG, +