Re: [PATCHES] Diagnostic functions
Peter Eisentraut wrote: Heikki Linnakangas wrote: Here's an update of Simon's Heap Page Diagnostic Functions. I now consider it ready to commit. How is this better than pg_filedump? It's handy to be able to inspect pages from within a live database. The new functions work through shared_buffers, so you can see changes without having to issue a checkpoint to flush them to disk. pg_filedump takes a filename as argument, the new function takes relation name. Having them as functions also allows you to do more advanced queries like ... WHERE (t_infomask 1) 0 to show tuples with a null bitmap. diagnostics is an awfully general name, too. We already played the name game on hackers. My original suggestion was pgforensics. pgdiagfuncs and pginspect were also suggested, among others. I'm still open to suggestions, though. Have any? -- Heikki Linnakangas 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] [HACKERS] Full page writes improvement, code update
Hi, As replied to Patch queue triage by Tom, here's simplified patch to mark WAL record as compressable, with no increase in WAL itself. Compression/decompression commands will be posted separately to PG Foundary for further review. --- As suggested by Tom, I agree that WAL should not include both full page write and incremental (logical) log. I began to examine WAL record format to see if incremental log can be made from full page writes. It will be okay even before 8.4, if simplified patch to the core is accepted. I will post simplified patch to the core as follows: 1. Mark the flag to indicate that the WAL record is compressable from full page writes to incremental log. This flag will be set if a) It is not written during the hot backup, and b) Archive command is active, and c) WAL contains full page writes, and d) full_page_writes=on. No logical log will be written to WAL in this case, and 2. During recovery, xl_tot_len check will be skipped for compressed WAL records. Please note that new GUC is not needed in this patch. With this patch, compress/decompress can be developped outside the core. I'd be very grateful if this patch can be considered again. Best Regards; -- - Koichi Suzuki diff -cr pgsql_org/src/backend/access/transam/xlog.c pgsql/src/backend/access/transam/xlog.c *** pgsql_org/src/backend/access/transam/xlog.c 2007-05-02 15:56:38.0 +0900 --- pgsql/src/backend/access/transam/xlog.c 2007-05-07 16:30:38.0 +0900 *** *** 837,842 --- 837,854 return RecPtr; } + /* +* If online backup is not in progress and WAL archiving is active, mark +* backup blocks removable if any. +* This mark will be referenced during archiving to remove needless backup +* blocks in the record and compress WAL segment files. +*/ + if (XLogArchivingActive() fullPageWrites + (info XLR_BKP_BLOCK_MASK) !Insert-forcePageWrites) + { + info |= XLR_BKP_REMOVABLE; + } + /* Insert record header */ record = (XLogRecord *) Insert-currpos; *** *** 2738,2750 blk += blen; } ! /* Check that xl_tot_len agrees with our calculation */ ! if (blk != (char *) record + record-xl_tot_len) { ! ereport(emode, ! (errmsg(incorrect total length in record at %X/%X, ! recptr.xlogid, recptr.xrecoff))); ! return false; } /* Finally include the record header */ --- 2750,2778 blk += blen; } ! /* !* If physical log has not been removed, check the length to see !* the following. !* - No physical log existed originally, !* - WAL record was not removable because it is generated during !* the online backup, !* - Cannot be removed because the physical log spanned in !* two segments. !* The reason why we skip the length check on the physical log removal is !* that the flag XLR_SET_BKB_BLOCK(0..2) is reset to zero and it prevents !* the above loop to proceed blk to the end of the record. !*/ ! if (!(record-xl_info XLR_BKP_REMOVABLE) || ! record-xl_info XLR_BKP_BLOCK_MASK) { ! /* Check that xl_tot_len agrees with our calculation */ ! if (blk != (char *) record + record-xl_tot_len) ! { ! ereport(emode, ! (errmsg(incorrect total length in record at %X/%X, ! recptr.xlogid, recptr.xrecoff))); ! return false; ! } } /* Finally include the record header */ pgsql/src/backend/access/transamã ãã«çºè¦: xlog.c.orig diff -cr pgsql_org/src/include/access/xlog.h pgsql/src/include/access/xlog.h *** pgsql_org/src/include/access/xlog.h 2007-01-06 07:19:51.0 +0900 --- pgsql/src/include/access/xlog.h 2007-05-07 16:30:38.0 +0900 *** *** 66,73 /* * If we backed up any disk blocks with the XLOG record, we use flag bits in * xl_info to signal it. We support backup of up to 3 disk blocks per XLOG ! * record.(Could support 4 if we cared to dedicate all the xl_info bits for ! * this purpose; currently bit 0 of xl_info is unused and available.) */ #define XLR_BKP_BLOCK_MASK0x0E/* all info bits used for bkp blocks */ #define XLR_MAX_BKP_BLOCKS3 --- 66,74 /* * If we backed up any disk blocks with the XLOG record, we use flag bits in * xl_info to signal it. We support backup of up to 3 disk blocks per XLOG ! * record. ! * Bit 0 of xl_info is used to represent that backup
Re: [PATCHES] HOT patches
On 5/7/07, Zoltan Boszormenyi [EMAIL PROTECTED] wrote: I tried your patchset on current CVS (some minutes old) and got this single regression below. Thanks for testing the patch. This failure is expected and I had mentioned this when I posted v7. With HOT, CIC is now a three step process and a failure after the first step will leave an invalid index behind. In this particular case, CIC fails because of duplicate keys. I did not deliberately fix the regression output to highlight this change. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [PATCHES] updated WIP: arrays of composites
Andrew Dunstan [EMAIL PROTECTED] writes: I'm still wondering if we can get away without a catalog change on that - e.g. could we look up an array type by looking for a pg_type entry containing the base type's oid in the typelem field? Or would that be too slow? (a) too slow, (b) not unique. 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
[PATCHES] Implemented current_query
As suggested in the TODO list (and as I need the functionality myself), I have implemented the current_query interface to debug_query_string. I'm not sure the best place to put this, suggestions welcome.. Please review the patch attached. Cheers Tom pgsql-current_query.patch Description: Binary data ---(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] Diagnostic functions
Peter Eisentraut wrote: Am Montag, 7. Mai 2007 11:49 schrieb Heikki Linnakangas: It's handy to be able to inspect pages from within a live database. How reliable is it to inspect pages from within a database if you presumably suspect that database to be broken? As long as the page header is valid, the functions should be able to deal with it (as in not crashing). I found them extremely useful when I was developing the MVCC-safe CLUSTER patch. The index functions in pgstattuple were very useful when I worked on clustered indexes. In fact I reinvented the wheel and wrote a function of my own that did essentially the same thing as bt_page_items, before Bruce pointed out that such a thing already exists. We already played the name game on hackers. My original suggestion was pgforensics. pgdiagfuncs and pginspect were also suggested, among others. I'm still open to suggestions, though. Have any? Something involving page would at least explain what you can diagnose. Well, how about pageinspect then? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] ECPG patch to use prepare for improved performance
This patch for ECPG utilizes the PQprepare and PQexecPrepared functions to cause SQL statements from ECPG to be cached. It does this without requiring any changes in the user's source program. It was developed during the preparation for a benchmark for a large customer. This benchmark consists of several hundred programs containing several thousand embedded SQL statements. The benchmark has been successfully executed using Oracle, DB2, and PostgreSQL. In the benchmark, Postgres is shown to be slower, by far, than the other DBMS systems. In a three hour execution, using this patch, approximately 30% was saved. The following approach is used: Within the execute.c module, routines are added to manage a cache of prepared statements. These routines are used to search, insert, and delete entries in the cache. The key for these cache entries is the text of the SQL statement as passed by ECPG from the application program. Within the same module, the ECPGexecute function was replaced. This is the function that is called to execute a statement after some preliminary housekeeping is done. The original ECPGexecute function constructs an ASCII string by replacing each host variable with its current value and then calling PQexec. The new ECPGexecute function does the following: - build an array of the current values of the host variables. - search the cache for an entry indicating that this statement has already been prepare'd, via PQprepare - If no entry was found in the previous step, call PQprepare for the statement and then insert an entry for it into the cache. If this requires an entry to be re-used, execute a DEALLOCATE PREPARE.. for the previous contents. - At this point, the SQL statement has been prepare'd by PQlib, either when the statement was executed in the past, or in the previous step. - call PQexecPrepared, using the array of parameters built in the first step above. ecpg.patch Description: Binary data ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Implemented current_query
On Mon, 2007-07-05 at 19:48 +0100, Tomas Doran wrote: As suggested in the TODO list (and as I need the functionality myself), I have implemented the current_query interface to debug_query_string. Comments: * docs need a bit more detail (they should emphasize that it is the current query string submitted by the client, as opposed to the currently executing SPI command or the like). Also, the docs currently claim current_query() returns name. * use textin() to convert C-style strings to text, rather than constructing a text datum by hand * perhaps we can get away with marking current_query() stable? * AFAIK debug_query_string() still does the wrong thing when the user submits multiple queries in a single protocol message (separated by semi-colons). Not sure there's a way to fix that that is both easy and efficient, though... -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq