[PATCHES] Diagnostic functions
Here's an update of Simon's Heap Page Diagnostic Functions. I now
consider it ready to commit.
The new functions are all under new contrib module,
contrib/pgdiagnostics. I also moved bt_metap, bt_page_items and
bt_page_stats from pgstattuple to the new module.
I copied the bt_* functions almost unmodified. I didn't try to harmonize
the user interface or return values with the new heap page functions,
though that might we worth doing in the future.
I added input value checking to the new functions so that they should
handle invalid data gracefully. All the new functions are nevertheless
superuser-only, just to err on the safe side.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Index: contrib/pgdiagnostics/Makefile
===
RCS file: contrib/pgdiagnostics/Makefile
diff -N contrib/pgdiagnostics/Makefile
*** /dev/null 1 Jan 1970 00:00:00 -
--- contrib/pgdiagnostics/Makefile 2 May 2007 09:14:39 -
***
*** 0
--- 1,24
+ #-
+ #
+ # pgdiagnostics Makefile
+ #
+ # $PostgreSQL$
+ #
+ #-
+
+ MODULE_big = pgdiagnostics
+ OBJS = rawpage.o heapfuncs.o btreefuncs.o
+ DOCS = README.pgdiagnostics
+ DATA_built = pgdiagnostics.sql
+ DATA = uninstall_pgdiagnostics.sql
+
+ ifdef USE_PGXS
+ PGXS := $(shell pg_config --pgxs)
+ include $(PGXS)
+ else
+ subdir = contrib/pgdiagnostics
+ top_builddir = ../..
+ include $(top_builddir)/src/Makefile.global
+ include $(top_srcdir)/contrib/contrib-global.mk
+ endif
+
Index: contrib/pgdiagnostics/README.pgdiagnostics
===
RCS file: contrib/pgdiagnostics/README.pgdiagnostics
diff -N contrib/pgdiagnostics/README.pgdiagnostics
*** /dev/null 1 Jan 1970 00:00:00 -
--- contrib/pgdiagnostics/README.pgdiagnostics 2 May 2007 10:17:42 -
***
*** 0
--- 1,94
+ The functions in this module allow you to inspect the contents of data pages
+ at a low level, for debugging purposes.
+
+ 1. Installation
+
+ $ make
+ $ make install
+ $ psql -e -f /usr/local/pgsql/share/contrib/pgdiagnostics.sql test
+
+ 2. Functions included:
+
+ get_raw_page
+
+ get_raw_page reads one block of the named table and returns a copy as a
+ bytea field. This allows a single time-consistent copy of the block to be
+ made. Use of this functions is restricted to superusers.
+
+ page_header
+ ---
+ page_header shows fields which are common to all PostgreSQL heap and index
+ pages. Use of this function is restricted to superusers.
+
+ A page image obtained with get_raw_page should be passed as argument:
+
+ test=# SELECT * FROM page_header(get_raw_page('pg_class',0));
+lsn| tli | flags | lower | upper | special | pagesize | version
+ --+-+---+---+---+-+--+-
+ 0/3C5614 | 1 | 1 | 216 | 256 |8192 | 8192 | 4
+ (1 row)
+
+ The returned columns correspond to the fields in the PageHeaderData-struct,
+ see src/include/storage/bufpage.h for more details.
+
+ heap_page_items
+ ---
+ heap_page_items shows all line pointers on a heap page. For those line
+ pointers that are in use, tuple headers are also shown. All tuples are
+ shown, whether or not the tuples were visible to an MVCC snapshot at the
+ time the raw page was copied. Use of this function is restricted to
+ superusers.
+
+ A heap page image obtained with get_raw_page should be passed as argument:
+
+ test=# SELECT * FROM heap_page_items(get_raw_page('pg_class',0));
+
+ See src/include/storage/itemid.h and src/include/access/htup.h for
+ explanations of the fields returned.
+
+ bt_metap
+
+ bt_metap() returns information about the btree index metapage:
+
+ test= SELECT * FROM bt_metap('pg_cast_oid_index');
+ -[ RECORD 1 ]-
+ magic | 340322
+ version | 2
+ root | 1
+ level | 0
+ fastroot | 1
+ fastlevel | 0
+
+ bt_page_stats
+ -
+ bt_page_stats() shows information about single btree pages:
+
+ test= SELECT * FROM bt_page_stats('pg_cast_oid_index', 1);
+ -[ RECORD 1 ]-+-
+ blkno | 1
+ type | l
+ live_items| 256
+ dead_items| 0
+ avg_item_size | 12
+ page_size | 8192
+ free_size | 4056
+ btpo_prev | 0
+ btpo_next | 0
+ btpo | 0
+ btpo_flags| 3
+
+ bt_page_items
+ -
+ bt_page_items() returns information about specific items on btree pages:
Re: [PATCHES] non-recursive WITH clause support
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Gregory Stark wrote: Here's an updated patch that fixes the bug I had. This is now functional basic non-recursive WITH clause support. http://community.enterprisedb.com/recursive/with-pg82stable-v2.patch.gz It's a pretty short simple patch as is; it just directly inlines any WITH clauses as if they had been written as subqueries. We'll have to do something much more clever to get recursive queries to work but for non-recursive queries that's sufficient. Example: postgres=# with a as (select 1 as x) select * from (select * from a) as x; x --- 1 (1 row) -- Gregory Stark 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 6: explain analyze is your friend
Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support
Hi Neil, * the patch is broken for expressional indexes, and silently omits copying the predicate that may be associated with an index. It also doesn't copy the index's amoptions (WITH clause), or the NULLS FIRST/etc. options that may be associated with any of the index's columns. Since this patch is only supposed to copy unique/primary indexes, I dont think we will ever have predicates associated to such indexes? Regards, Nikhils -- EnterpriseDB http://www.enterprisedb.com
Re: [PATCHES] [GENERAL] dropping role w/dependent objects
Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: [ enlarge MAX_REPORTED_DEPS to 2000 ] I was about to apply this, but stopped to reflect that it is probably not such a hot idea. My concern is that enormously long error message detail fields are likely to break client software, particularly GUI clients. A poor (e.g., truncated) display isn't unlikely, and a crash not entirely out of the question. Moreover, who's to say that 2000 is enough lines to cover all cases? And if it's not, aren't you faced with an even bigger problem? Perhaps a better solution is to keep MAX_REPORTED_DEPS where it is, and arrange that when it's exceeded, the *entire* list of dependencies gets reported to the postmaster log; we can expect that that will work. We still send the same just-the-count message to the client. We could add a hint suggesting to look in the postmaster log for the details. This would require some refactoring of checkSharedDependencies's API, I suppose, but doesn't seem especially difficult. Actually I was thinking that we could report MAX_REPORTED_DEPS (the original value) dependencies to the client log, and finish with and other N dependencies not shown here. Maybe we could mix both solutions and send a partial report to the client and a full report to the server log. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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 does not start in HEAD
ITAGAKI Takahiro wrote: I wrote: I found that autovacuum launcher does not launch any workers in HEAD. The attached autovacuum-fix.patch could fix the problem. I changed to use 'greater or equal' instead of 'greater' at the decision of next autovacuum target. I have committed a patch which might fix this issue in autovacuum.c rev 1.44. Please retest. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Clear up strxfrm() in UTF-8 with locale on Windows
ITAGAKI Takahiro wrote: The attached patch clears up the usage of strxfrm() on Windows. If the server encoding is UTF-8 and the locale is not C, we should use wcsxfrm() instead of strxfrm() because UTF-8 locale are not supported on Windows. We've already have a special version of strcoll() for Windows, but the usage of strxfrm() was still broken. When we are caught up in the bug, we see the next error message. | ERROR: invalid memory alloc request size 2147483648 If the server is wrong configured between the server encoding and the locale, strxfrm() could be failed and return values like INT_MAX or (size_t)-1. We've passed the result+1 straight to palloc(), so the server tried to allocale more than 1GB of memory and gave up. I was just about to commit this with the following two changes: * wcsxfrm() sets errno, so you can't use GetLastError() to report problems * The code added a check for return value = INT_MAX on Unix as well, but the spec for strxfrm() says that there is no specific return value for failure. Put those in there for reference. But I also recalled a previous discussion, and found this: http://archives.postgresql.org/pgsql-hackers/2005-08/msg00760.php Given this, perhaps the proper approach should instead be to just check the return value, and go from there? Should be a simple enough patch, something like the attached. Tom, can you comment? Takahiro, can you test if this patch fixes your problem? //Magnus Index: src/backend/utils/adt/selfuncs.c === RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v retrieving revision 1.233 diff -c -r1.233 selfuncs.c *** src/backend/utils/adt/selfuncs.c21 Apr 2007 21:01:45 - 1.233 --- src/backend/utils/adt/selfuncs.c2 May 2007 20:38:58 - *** *** 3152,3157 --- 3152,3165 #else xfrmlen = strxfrm(NULL, val, 0); #endif + #ifdef WIN32 + /* On win32, if strxfrm fails (for example in UTF8 encoding, since +* it's not properly supported), return the original string instead +* of trying to allocate 2Gb memory. +*/ + if (xfrmlen = INT_MAX) + return val; + #endif xfrmstr = (char *) palloc(xfrmlen + 1); xfrmlen2 = strxfrm(xfrmstr, val, xfrmlen + 1); Assert(xfrmlen2 = xfrmlen); ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Clear up strxfrm() in UTF-8 with locale on Windows
Magnus Hagander [EMAIL PROTECTED] writes: Given this, perhaps the proper approach should instead be to just check the return value, and go from there? Should be a simple enough patch, something like the attached. Tom, can you comment? Testing against INT_MAX seems like a type pun, or something. Maybe use MaxAllocSize instead? if (xfrmlen = MaxAllocSize) return val; Also, since as you note returning (size_t) -1 is not at all standard, it would be helpful to readers to note that that's what Windows does on failure and that's what you're testing for. In fact you could make a good case that the test should be just if (xfrmlen == (size_t) -1) return val; regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Updated bitmap index patch
Gavin Sherry wrote: Hi all, Attached is an updated bitmap index patch. It contains bug fixes, API changes, binary changes (page identifier to distinguish it from other indexes) and has been brought up to HEAD. I have applied this to todays HEAD performed some quick tests - looks good! I have to re-create a TPC-H dataset to test one of the previous bugs, so I'll probably look at that tomorrow or so. I worked on a few approaches to VACUUM, none very satisfactory. The problem is, breaking a compressed word representing matches can have serious consequences -- at the least, creation of new words, at the worst, creation of a new page. If a lot of this were to happen, REINDEX would be much more efficient (this is what earlier patches did). One approach I looked at was modifying the existing read API to be able to do something like kill prior tuple. This, I think, made the API quite complex and it was hard to implement, since the existing mechanism decompresses words on the fly and it would be hard to identify which TID is no longer a match. So, I dropped this idea pretty quickly. The second approach is to just manually traverse each vector and change matches to non-matches where necessary. The complexity then is in managing the consequences of breaking compressed words, doing WAL (efficiently) and calculating free space. I've only partially implemented this approach. At this stage, I don't have time to finish it due to other commitments. The second approach seems like better the way to go (as far as I understand the issues...). How much work is remaining on this? - not sure that I'll have time to look at it either ... but may as well know the size to the job :-) ! Cheers Mark ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support
NikhilS [EMAIL PROTECTED] writes: Hi Neil, * the patch is broken for expressional indexes, and silently omits copying the predicate that may be associated with an index. Since this patch is only supposed to copy unique/primary indexes, I dont think we will ever have predicates associated to such indexes? Huh? I would expect a clause INCLUDING INDEXES to mean copying *all* indexes. A clause INCLUDING CONSTRAINTS would reasonably act as you suggest, ie copy only indexes derived from SQL constraint clauses. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support
On Wed, 2007-02-05 at 17:09 +0530, NikhilS wrote: Since this patch is only supposed to copy unique/primary indexes, I dont think we will ever have predicates associated to such indexes? Nope: neilc=# create table t1 (a int, b int); CREATE TABLE neilc=# create unique index t1_a_idx on t1 ((a + b)) where (a 5); CREATE INDEX -Neil ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
