[PATCHES] Auto Partitioning Patch - WIP version 1
Hi,
Please find attached the WIP version 1 of the auto partitioning patch. There
was discussion on this a while back on -hackers at:
http://archives.postgresql.org/pgsql-hackers/2007-03/msg00375.php
Please note that this patch tries to automate the activities that currently
are carried out manually. It does nothing fancy beyond that for now. There
were a lot of good suggestions, I have noted them down but for now I have
tried to stick to the initial goal of automating existing steps for
providing partitioning.
Things that this patch does:
i) Handle new syntax to provide partitioning:
CREATE TABLE tabname (
...
) PARTITION BY
RANGE(ColId)
| LIST(ColId)
(
PARTITION partition_name CHECK(...),
PARTITION partition_name CHECK(...)
...
);
ii) Create master table.
iii) Create children tables based on the number of partitions specified and
make them inherit from the master table.
The following things are TODOs:
iv) Auto generate rules using the checks mentioned for the partitions, to
handle INSERTs/DELETEs/UPDATEs to navigate them to the appropriate child.
Note that checks specified directly on the master table will get inherited
automatically.
v) Based on the PRIMARY, UNIQUE information specified, pass it on to the
children tables.
vi) [stretch goal] Support HASH partitions
Will try to complete the above mentioned TODOs as soon as is possible.
Comments, feedback appreciated.
Thanks and Regards,
Nikhils
--
EnterpriseDB http://www.enterprisedb.com
Index: src/backend/commands/tablecmds.c
===
RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.218
diff -c -r1.218 tablecmds.c
*** src/backend/commands/tablecmds.c 19 Mar 2007 23:38:29 - 1.218
--- src/backend/commands/tablecmds.c 30 Mar 2007 06:31:37 -
***
*** 6864,6866
--- 6864,6945
}
}
}
+
+ /*
+ * --
+ * DefinePartitions
+ * Create new partitions. They end up inheriting from the parent
+ * relation.
+ * Once they have been created, rules need to be assigned to the parent to
+ * provide the UPDATEs/INSERTs/DELETEs to percolate down to the children
+ * Callers expect this function to end with CommandCounterIncrement if it
+ * makes any changes.
+ * --
+ */
+ void
+ DefinePartitions(CreateStmt *stmt)
+ {
+ CreateStmt *childStmt;
+ RangeVar inr;
+ Oid childOid;
+ PartitionAttrs *partAttr;
+
+ if (stmt-partAttr == NULL)
+ return;
+
+ Assert(IsA(stmt-partAttr, PartitionAttrs));
+ partAttr = (PartitionAttrs *)(stmt-partAttr);
+
+ /*
+ * All the partitions will inherit from the parent, set the parent in the
+ * inhRelations structure
+ */
+ inr = *stmt-relation;
+
+
+ /*
+ * Create the children tables. The parser has already made sure that we
+ * have atleast one partition in the list
+ */
+ if (partAttr-partFunc == PART_LIST || partAttr-partFunc == PART_RANGE)
+ {
+ List *partitionList = partAttr-partitions;
+ ListCell *temp_part;
+
+ Assert(list_length(partitionList) 0);
+ if (list_length(partitionList) 0)
+ {
+ foreach(temp_part, partitionList)
+ {
+ Partition *temp_partition = lfirst(temp_part);
+ /*
+ * Create a working copy for each child
+ */
+ childStmt = (CreateStmt *)copyObject((void *)stmt);
+
+ /*
+ * Child has to use all columns from the parent, otherwise we will get
+ * unnecessary merging columns notices as part of the
+ * DefineRelation
+ */
+ childStmt-tableElts = NIL;
+
+ childStmt-inhRelations = lappend(NULL, inr);
+ childStmt-relation-relname = temp_partition-partName-relname;
+ childOid = DefineRelation(childStmt, RELKIND_RELATION);
+ }
+ }
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg(Invalid PARTITION type specified)));
+
+ /*
+ *TODO: Add logic to create rules on the parent table
+ */
+ /*
+ * Make the changes carried out so far visible
+ */
+ CommandCounterIncrement();
+ }
Index: src/backend/nodes/copyfuncs.c
===
RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.372
diff -c -r1.372 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 27 Mar 2007 23:21:09 - 1.372
--- src/backend/nodes/copyfuncs.c 30 Mar 2007 06:31:37 -
***
*** 2070,2075
--- 2070,2078
COPY_NODE_FIELD(options);
COPY_SCALAR_FIELD(oncommit);
COPY_STRING_FIELD(tablespacename);
+ /*
+ * There is no need to copy partAttr as of now
+ */
return newnode;
}
Index: src/backend/parser/analyze.c
===
RCS file:
Re: [PATCHES] [HACKERS] Full page writes improvement, code update
Without a switch, because both full page writes and corresponding logical log is included in WAL, this will increase WAL size slightly (maybe about five percent or so). If everybody is happy with this, we don't need a switch. Sorry, I still don't understand that. What is the corresponding logical log ? It seems to me, that a full page WAL record has enough info to produce a dummy LSN WAL entry. So insead of just cutting the full page wal record you could replace it with a LSN WAL entry when archiving the log. Then all that is needed is the one flag, no extra space ? Andreas ---(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] Multibyte LIKE optimization
Andrew - Supernews [EMAIL PROTECTED] wrote: Actually, I think your proposal is fundamentally correct, merely incomplete. Yeah, I fixed the patch to handle '_' correctly. Doing octet-based rather than character-based matching of strings is a _design goal_ of UTF8. I think all safe ASCII-supersets encodings are comparable by bytes, not only UTF-8. Their all multibyte characters consist of bytes larger than 127. I updated the patch on this presupposition. It uses octet-based matching usually and character-based matching at '_'. There was 30%+ of performance win in selection using multibytes LIKE '%foo%'. encoding | HEAD | patched ---+-+- SQL_ASCII | 7094ms | 7062ms LATIN1| 7083ms | 7078ms UTF8 | 17974ms | 11635ms (64.7%) EUC_JP| 17032ms | 12109ms (71.1%) If this patch is acceptable, please drop JOHAB encoding from server encodings before it is applied. Trailing bytes of JOHAB can be less than 128. http://archives.postgresql.org/pgsql-hackers/2007-03/msg01475.php Regards, --- ITAGAKI Takahiro NTT Open Source Software Center mbtextmatch.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
[PATCHES] Dead Space Map version 3 (simplified)
Attached is an updated DSM patch. I've left the core function of DSM only and dropped other complicated features in this release. VACUUM finishs faster with the patch, but it's obvious... DSM vacuum sweeps only pages that have many dead tuples and leave some of them after vacuum. I'll examine the sweep behavior and the performance from now. * Features - DSM tracks pages worth vacuuming using 1bit/page bit. The threshold is two dead tuples or 2kB of deadspaces. - DSM is constructed at page flush. Almost of the works are done by bgwriter if it is properly configured. - 'VACUUM' command uses DSM. 'VACUUM ALL' always scans all pages. - This is including n_dead_tuples statistics fix. http://momjian.us/mhonarc/patches/msg2.html * Configuration - max_dsm_relations (=1000) Counterpart to max_fsm_relations, but count tables only; Indexes are not tracked by DSM. - max_dsm_pages (=1024000) Counterpart to max_dsm_pages. Default values are configurated to 5 times of max_fsm_pages at initdb. - min_dsm_target (=8MB) Minimum size of tables of which dead space is tracked to avoid tracking small tables, including system catalogs. * Limitation - XID-wraparound vacuum is still required. VACUUM with DSM cannot update relfrozenxid, so we sometimes needs full-scan. - No recovery support. All contents of DSM and FSM are lost on crash. - DSM uses fixed size memory allocated at server start. We cannot change the value on-the-fly. If we want the feature, we need something like shared-memory-allocator or swap-supported memory management module. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center deadspacemap_v3.patch.gz Description: Binary data ---(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] Fwd: Re: [pgsql-patches] pg_get_domaindef
Resubmitting this patch without the legal disclaimers attached to it.
Rgds,
Arul Shaji
-- FWD: --
Subject: Re: [pgsql-patches] pg_get_domaindef
Date: Thu, 25 Jan 2007 03:18:30 +1100
From: Arul Shaji [EMAIL PROTECTED]
To: Neil Conway [EMAIL PROTECTED]
Cc: pgsql-patches@postgresql.org
Please find attached the patch with modifications
Rgds,
Arul Shaji
On Sat, 20 Jan 2007 04:44, FAST PostgreSQL wrote:
On Fri, 19 Jan 2007 17:02, Neil Conway wrote:
On Sat, 2007-01-20 at 02:28 +1100, FAST PostgreSQL wrote:
Attached is a small patch that implements the pg_get_domaindef(oid)
function.
A few minor comments:
- don't use C++-style comments
OK. Can do.
- why does this code append a - to the output when SPI_processed != 1,
rather than erroring out?
get_ruledef() does the same. As the user gets a '-' in that case when a
non-existent oid is given, I just wanted to be consistent. Maybe a wrong
idea ?
- you probably want to elog(ERROR) if typeTuple is invalid:
Of course.
+ if (typnotnull || constraint != NULL)
+ {
+ if ( ( (contype != NULL) (strcmp(contype,
c) != 0) ) || typnotnull )
+ {
+ appendStringInfo(buf, CONSTRAINT );
+ }
+ if (typnotnull)
+ {
+ appendStringInfo(buf, NOT NULL );
+ }
+ }
+ if (constraint != NULL)
+ {
+ appendStringInfo(buf,
quote_identifier(constraint));
+ }
This logic seems pretty awkward. Perhaps simpler would be a check for
typnotnull (and then appending CONSTRAINT NOT NULL), and then handling
the non-typnotnull branch separately.
Yeah agree.
-Neil
Rgds,
Arul Shaji
---(end of broadcast)---
TIP 6: explain analyze is your friend
---
*** pgsql/src/include/catalog/pg_proc.h 2007-01-24 13:58:40.291574752 +1100
--- workingpgsql/src/include/catalog/pg_proc.h 2007-01-24 15:12:02.410350976 +1100
***
*** 2315,2321
DESCR(deparse an encoded expression);
DATA(insert OID = 1665 ( pg_get_serial_sequence PGNSP PGUID 12 1 0 f f t f s 2 25 25 25 _null_ _null_ _null_ pg_get_serial_sequence - _null_ ));
DESCR(name of sequence for a serial column);
!
/* Generic referential integrity constraint triggers */
DATA(insert OID = 1644 ( RI_FKey_check_ins PGNSP PGUID 12 1 0 f f t f v 0 2279 _null_ _null_ _null_ RI_FKey_check_ins - _null_ ));
--- 2315,2322
DESCR(deparse an encoded expression);
DATA(insert OID = 1665 ( pg_get_serial_sequence PGNSP PGUID 12 1 0 f f t f s 2 25 25 25 _null_ _null_ _null_ pg_get_serial_sequence - _null_ ));
DESCR(name of sequence for a serial column);
! DATA(insert OID = 2950 ( pg_get_domaindef PGNSP PGUID 12 1 0 f f t f s 1 25 26 _null_ _null_ _null_ pg_get_domaindef - _null_ ));
! DESCR(domain description);
/* Generic referential integrity constraint triggers */
DATA(insert OID = 1644 ( RI_FKey_check_ins PGNSP PGUID 12 1 0 f f t f v 0 2279 _null_ _null_ _null_ RI_FKey_check_ins - _null_ ));
*** pgsql/src/include/utils/builtins.h 2007-01-18 15:27:15.0 +1100
--- workingpgsql/src/include/utils/builtins.h 2007-01-24 14:12:48.167677992 +1100
***
*** 534,539
--- 534,540
extern Datum pg_get_expr_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_userbyid(PG_FUNCTION_ARGS);
extern Datum pg_get_serial_sequence(PG_FUNCTION_ARGS);
+ extern Datum pg_get_domaindef(PG_FUNCTION_ARGS);
extern char *deparse_expression(Node *expr, List *dpcontext,
bool forceprefix, bool showimplicit);
extern List *deparse_context_for(const char *aliasname, Oid relid);
*** pgsql/src/backend/utils/adt/ruleutils.c 2007-01-24 13:58:28.417379904 +1100
--- workingpgsql/src/backend/utils/adt/ruleutils.c 2007-01-24 15:18:57.944180232 +1100
***
*** 113,118
--- 113,123
static void *plan_getviewrule = NULL;
static char *query_getviewrule = SELECT * FROM pg_catalog.pg_rewrite WHERE ev_class = $1 AND rulename = $2;
+ /* Queries to extract domain description */
+ static void *plan_getdomainbyOid = NULL;
+ static char *query_getdomainbyOid = SELECT * FROM pg_catalog.pg_type WHERE oid = $1 AND typtype = 'd';
+ static void *plan_getconstraintbycontypid = NULL;
+ static char *query_getconstraintbycontypid = SELECT oid, contype FROM pg_catalog.pg_constraint WHERE contypid = $1;
/* --
* Local functions
***
*** 135,140
--- 140,146
int prettyFlags);
static char *pg_get_expr_worker(text *expr, Oid relid, char *relname,
int prettyFlags);
+ static char *pg_get_domaindef_worker(Oid domainOid, int prettyFlags);
static void make_ruledef(StringInfo buf, HeapTuple
Re: [PATCHES] Make CLUSTER MVCC-safe
Alvaro Herrera wrote: Heikki Linnakangas wrote: Here's an update, fixing conflict by Tom's recent commit of Simon's patch to skip WAL-inserts when archiving is not enabled. Hmm, wouldn't it be better if the rewriteheap.c file was in access/heap/ instead of commands/? Yeah, maybe. I thought of it as a subsystem of cluster, and possibly other commands like alter table. But it really is pretty low-level. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] COPY-able sql log outputs
Am Dienstag, 3. April 2007 20:33 schrieb FAST PostgreSQL: Attached is the completed patch for the COPY-able sql log outputs. Could you please remove random whitespace changes from this patch? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] DEALLOCATE ALL
Neil Conway escribió: As to the implementation, calling hash_remove() in a loop seems a pretty unfortunate way to clear a hash table -- adding a hash_reset() function to the dynahash API would be cleaner and faster. I wonder why hash_drop cannot be used? -- 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] [HACKERS] Full page writes improvement, code update
On Fri, 2007-03-30 at 10:22 +0200, Zeugswetter Andreas ADI SD wrote: Without a switch, because both full page writes and corresponding logical log is included in WAL, this will increase WAL size slightly (maybe about five percent or so). If everybody is happy with this, we don't need a switch. Sorry, I still don't understand that. What is the corresponding logical log ? It seems to me, that a full page WAL record has enough info to produce a dummy LSN WAL entry. So insead of just cutting the full page wal record you could replace it with a LSN WAL entry when archiving the log. Then all that is needed is the one flag, no extra space ? The full page write is required for crash recovery, but that isn't required during archive recovery because the base backup provides the safe base. Archive recovery needs the normal xlog record, which in some cases has been optimised away because the backup block is present, since the full block already contains the changes. If you want to remove the backup blocks, you need to put back the information that was optimised away, otherwise you won't be able to do the archive recovery correctly. Hence a slight increase in WAL volume to allow it to be compressed does make sense. -- Simon Riggs 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
Simon Riggs wrote: On Fri, 2007-03-30 at 10:22 +0200, Zeugswetter Andreas ADI SD wrote: Without a switch, because both full page writes and corresponding logical log is included in WAL, this will increase WAL size slightly (maybe about five percent or so). If everybody is happy with this, we don't need a switch. Sorry, I still don't understand that. What is the corresponding logical log ? It seems to me, that a full page WAL record has enough info to produce a dummy LSN WAL entry. So insead of just cutting the full page wal record you could replace it with a LSN WAL entry when archiving the log. Then all that is needed is the one flag, no extra space ? The full page write is required for crash recovery, but that isn't required during archive recovery because the base backup provides the safe base. Is that always true? Could the backup not pick up a partially-written page? Assuming it's being written to as the backup is in progress. (We are talking about when disk blocks are smaller than PG blocks here, so can't guarantee an atomic write for a PG block?) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Full page writes improvement, code update
On Fri, 2007-03-30 at 11:27 +0100, Richard Huxton wrote: Is that always true? Could the backup not pick up a partially-written page? Assuming it's being written to as the backup is in progress. (We are talking about when disk blocks are smaller than PG blocks here, so can't guarantee an atomic write for a PG block?) Any page written during a backup has a backup block that would not be removable by Koichi's tool, so yes, you'd still be safe. i.e. between pg_start_backup() and pg_stop_backup() we always use full page writes, even if you are running in full_page_writes=off mode. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(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] [HACKERS] Full page writes improvement, code update
Simon Riggs wrote: On Fri, 2007-03-30 at 11:27 +0100, Richard Huxton wrote: Is that always true? Could the backup not pick up a partially-written page? Assuming it's being written to as the backup is in progress. (We are talking about when disk blocks are smaller than PG blocks here, so can't guarantee an atomic write for a PG block?) Any page written during a backup has a backup block that would not be removable by Koichi's tool, so yes, you'd still be safe. i.e. between pg_start_backup() and pg_stop_backup() we always use full page writes, even if you are running in full_page_writes=off mode. Ah, that's OK then. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] DEALLOCATE ALL
On 3/30/07, Alvaro Herrera [EMAIL PROTECTED] wrote: Neil Conway escribió: As to the implementation, calling hash_remove() in a loop seems a pretty unfortunate way to clear a hash table -- adding a hash_reset() function to the dynahash API would be cleaner and faster. I wonder why hash_drop cannot be used? hash_destroy()? Each element need separate destruction. -- marko ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Current enums patch
Am Dienstag, 27. März 2007 03:36 schrieb Tom Dunstan: Here's the current version of the enums patch. Not much change from last time, the only thought-inducing stuff was fixing up some macros that changed with the VARLENA changes, and adding a regression test to do basic checking of RI behavior, after the discussions that we had recently on the ri_trigger stuff with generic types. The actual behavior was fixed by Tom's earlier patch, so this is just a sanity check. Your patch doesn't compile anymore. ccache cc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g -I. -I../../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o parse_coerce.o parse_coerce.c -MMD -MP -MF .deps/parse_coerce.Po parse_coerce.c: In function 'can_coerce_type': parse_coerce.c:460: error: too few arguments to function 'find_coercion_pathway' parse_coerce.c: In function 'find_coercion_pathway': parse_coerce.c:1817: error: too few arguments to function 'find_coercion_pathway' parse_coerce.c:1822: error: too few arguments to function 'find_coercion_pathway' This was only changed a few days ago, so you need to update your patch. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] RESET SESSION
This is draft version, for discussion. New commands: CLOSE ALL DEALLOCATE ALL RESET PLANS RESET TEMPS-- please suggest better name RESET SESSION So in the end RESET SESSION basically executes following commands: ABORT; DEALLOCATE ALL; CLOSE ALL; RESET ALL; SET SESSION AUTHORIZATION DEFAULT; UNLISTEN *; RESET TEMPS; RESET PLANS; I think CLOSE ALL and DEALLOCATE ALL are useful in their own, without considering poolers. They just make user lives easier. The ALL just fits there. RESET PLANS and RESET TEMPS are for the principle that components for RESET SESSION should be available for users for special-case situations and fine-tuning poolers. Also they add very little complexity. (RESET PLANS could be useful its awn also). Todo: * Docs * Disallow some commands inside TX? * DEALLOCATE PREPARE ALL gives bison conflicts. is it needed? * Send more details to client. Variants: - CommandComplete: RESET XXX, CLOSE ALL, DEALLOCATE ALL (CLOSE name and DEALLOCATE name would be good in normal case too, that help anyone who wants to track what exists) - RESET SESSION should send ParamStatus of all parms -- marko session.reset.diff Description: Binary data ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] DEALLOCATE ALL
Marko Kreen escribió: On 3/30/07, Alvaro Herrera [EMAIL PROTECTED] wrote: Neil Conway escribió: As to the implementation, calling hash_remove() in a loop seems a pretty unfortunate way to clear a hash table -- adding a hash_reset() function to the dynahash API would be cleaner and faster. I wonder why hash_drop cannot be used? hash_destroy()? Each element need separate destruction. Hmm, so maybe something like hash_destroy_deep, like the List equivalent? If it's a simple pfree() for every element this would be simple enough. If this is the case, an even simpler idea would be to allocate the elements in the same MemoryContext as the hash itself (or in children thereof); then calling hash_destroy() would delete (reset?) the context and thus all elements are freed at once as well. If by destruction you mean something different than pfree, then maybe hash_remove in a loop is the best solution, the other idea being passing a function pointer to hash_destroy_deep to call on each element, which is probably too messy an API. In any case it's not likely that there are going to be thousands of prepared statements, so is this really an issue? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] DEALLOCATE ALL
On 3/30/07, Alvaro Herrera [EMAIL PROTECTED] wrote: If by destruction you mean something different than pfree, then maybe hash_remove in a loop is the best solution, the other idea being passing a function pointer to hash_destroy_deep to call on each element, which is probably too messy an API. Yes, callback function is needed, either in HASHCTL or as argument to deep_free(). In any case it's not likely that there are going to be thousands of prepared statements, so is this really an issue? I think the issue is here that its very common thing to do, so open-coding it everywhere is waste, there should be some utility function for that. void hash_foreach(HTAB, void (*cb_func)(void *)); -- marko ---(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] DEALLOCATE ALL
Marko Kreen escribió: On 3/30/07, Alvaro Herrera [EMAIL PROTECTED] wrote: In any case it's not likely that there are going to be thousands of prepared statements, so is this really an issue? I think the issue is here that its very common thing to do, so open-coding it everywhere is waste, there should be some utility function for that. void hash_foreach(HTAB, void (*cb_func)(void *)); Extra points if you can implement a map() function for hashes ;-) (I think it's called mutator in our sources for other kind of stuff) I think it would be void *hash_map(HTAB, void *(*map_func) (void *)) Not sure what the return value would be though :-( (Maybe this is extra complication enough that it's not worth the hassle) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PATCHES] index support is NULL
http://www.sigaev.ru/misc/indexnulls-0.8.gz Initially patch was developed by Martijn van Oosterhout. But it's reworked and support of searching NULLS to GiST too. Patch adds new column named amsearchnull to pg_am. To recognize IS NULL clause ScanKey-sk_flags contains (SK_ISNULL SK_INDEXFINDNULL) and ScanKey-sk_strategy sets to InvalidStrategy. IS NOT NULL isn't supported. The patch was already suggested to community (http://archives.postgresql.org/pgsql-patches/2006-12/msg00019.php and http://archives.postgresql.org/pgsql-hackers/2007-02/msg01162.php), but the single objection was: http://archives.postgresql.org/pgsql-patches/2006-12/msg00028.php Objection was about representing IS [NOT] NULL clause in SkanKey structure, because it required to set strategy to BTEqualStrategyNumber, and Tom doubted about needing of such support. Now ScanKey-sk_strategy = InvalidStrategy and btree code transforms it to BTEqualStrategyNumber in _bt_preprocess_keys(), btcostestimate knows that fact too. GiST doesn't need to transform strategy - it looks at SK_INDEXFINDNULL only. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] RESET SESSION
Marko Kreen [EMAIL PROTECTED] writes: RESET TEMPS-- please suggest better name RESET TEMP (should allow RESET TEMPORARY too). I see no reason to add a new keyword when the existing one will do fine. So in the end RESET SESSION basically executes following commands: ABORT; I object to having RESET SESSION include an ABORT. That will make it a transaction control statement and complicate matters for a whole lot of things. If you don't know whether you're in a transaction or not, you can send ABORT for yourself, but in practice I think most client code already tracks that (or easily can). regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Fwd: Re: [pgsql-patches] pg_get_domaindef
Thanks, but after lots of discussion, it turns out the community doesn't
want to add such functions, and I have removed it from the TODO list.
---
FAST PostgreSQL wrote:
Resubmitting this patch without the legal disclaimers attached to it.
Rgds,
Arul Shaji
-- FWD: --
Subject: Re: [pgsql-patches] pg_get_domaindef
Date: Thu, 25 Jan 2007 03:18:30 +1100
From: Arul Shaji [EMAIL PROTECTED]
To: Neil Conway [EMAIL PROTECTED]
Cc: pgsql-patches@postgresql.org
Please find attached the patch with modifications
Rgds,
Arul Shaji
On Sat, 20 Jan 2007 04:44, FAST PostgreSQL wrote:
On Fri, 19 Jan 2007 17:02, Neil Conway wrote:
On Sat, 2007-01-20 at 02:28 +1100, FAST PostgreSQL wrote:
Attached is a small patch that implements the pg_get_domaindef(oid)
function.
A few minor comments:
- don't use C++-style comments
OK. Can do.
- why does this code append a - to the output when SPI_processed != 1,
rather than erroring out?
get_ruledef() does the same. As the user gets a '-' in that case when a
non-existent oid is given, I just wanted to be consistent. Maybe a wrong
idea ?
- you probably want to elog(ERROR) if typeTuple is invalid:
Of course.
+ if (typnotnull || constraint != NULL)
+ {
+ if ( ( (contype != NULL) (strcmp(contype,
c) != 0) ) || typnotnull )
+ {
+ appendStringInfo(buf, CONSTRAINT );
+ }
+ if (typnotnull)
+ {
+ appendStringInfo(buf, NOT NULL );
+ }
+ }
+ if (constraint != NULL)
+ {
+ appendStringInfo(buf,
quote_identifier(constraint));
+ }
This logic seems pretty awkward. Perhaps simpler would be a check for
typnotnull (and then appending CONSTRAINT NOT NULL), and then handling
the non-typnotnull branch separately.
Yeah agree.
-Neil
Rgds,
Arul Shaji
---(end of broadcast)---
TIP 6: explain analyze is your friend
---
[ Attachment, skipping... ]
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
--
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 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PATCHES] RESET SESSION
Tom Lane wrote: Marko Kreen [EMAIL PROTECTED] writes: RESET TEMPS-- please suggest better name RESET TEMP (should allow RESET TEMPORARY too). I see no reason to add a new keyword when the existing one will do fine. So in the end RESET SESSION basically executes following commands: ABORT; I object to having RESET SESSION include an ABORT. That will make it a transaction control statement and complicate matters for a whole lot of things. If you don't know whether you're in a transaction or not, you can send ABORT for yourself, but in practice I think most client code already tracks that (or easily can). It seems strange RESET SESSION would not terminate open transactions, but on the other hand calling RESET SESSION in a transaction seems odd too. I think we should at least throw an error if someone tries RESET SESSION inside a transaction, and hopefully administrators will realize they have a problem. Perhaps we can even offer a hint how to close open transactions. -- 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] RESET SESSION
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: I object to having RESET SESSION include an ABORT. I think we should at least throw an error if someone tries RESET SESSION inside a transaction, and hopefully administrators will realize they have a problem. PreventTransactionChain? Sure, if you like. Should the single-purpose commands do that too? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Full page writes improvement, code update
Simon Riggs [EMAIL PROTECTED] writes: Any page written during a backup has a backup block that would not be removable by Koichi's tool, so yes, you'd still be safe. How does it know not to do that? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Full page writes improvement, code update
On Fri, 2007-03-30 at 16:35 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Any page written during a backup has a backup block that would not be removable by Koichi's tool, so yes, you'd still be safe. How does it know not to do that? Not sure what you mean, but I'll take a stab... I originally questioned Koichi-san's request for a full_page_compress parameter, which is how it would tell whether/not. After explanation, I accepted the need for a parameter, but I think we're looking for a new name for it. -- 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] Full page writes improvement, code update
Simon; Tom; Koichi is writing. Your question is how to determine WAL record generated between pg_start_backup and pg_stop_backup and here's an answer. XLogInsert( ) already has a logic to determine if inserting WAL record is between pg_start_backup and pg_stop_backup. Currently it is used to remove full_page_writes when full_page_writes=off. We can use this to mark WAL records. We have one bit not used in WAL record header, the last bit of xl_info, where upper four bits are used to indicate the resource manager and three of the rest are used to indicate number of full page writes included in the record. So in my proposal, this unused bit is used to mark that full page writes must not be removed at offline optimization by pg_complesslog. Sorry I didn't have mailing list capability from home and have just completed my subscription from home. I had to create new thread to continue my post. Sorry for confusion. Please refer to the original thread about this discussion. Best Regards; -- -- Koichi Suzuki ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Fwd: Re: [pgsql-patches] pg_get_domaindef
On Fri, Mar 30, 2007 at 01:45:21PM -0400, Bruce Momjian wrote: Thanks, but after lots of discussion, it turns out the community doesn't want to add such functions, and I have removed it from the TODO list. From what I recall of the discussion, the lack of interest was in actually stepping up and doing it, not in the feature itself. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(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] Fwd: Re: [pgsql-patches] pg_get_domaindef
David Fetter [EMAIL PROTECTED] writes: On Fri, Mar 30, 2007 at 01:45:21PM -0400, Bruce Momjian wrote: Thanks, but after lots of discussion, it turns out the community doesn't want to add such functions, and I have removed it from the TODO list. From what I recall of the discussion, the lack of interest was in actually stepping up and doing it, not in the feature itself. Um, no: there were serious concerns first about the snapshot semantics (MVCC vs SnapshotNow behavior) and second that we'd be condemning ourselves to support duplicative functionality in pg_dump and the backend for the foreseeable future. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
