Re: [HACKERS] relation 71478240 deleted while still in use on 8.1
okay, I got it. The main reason behind it - is that I do drop table in transaction. Every 10 minutes. So during that period, when 'replication' is running - the thing becomes unstable, and this error can appear. -- Grzegorz Jaskiewicz C/C++ freelance for hire ---(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] Bitmapscan changes
On Mar 22, 2007, at 7:25 AM, Pavan Deolasee wrote: Grzegorz, if you can try HOT as well, that will be great. I tried, and it worked very well with 4.2 v of patch, as I remember. My point was, since 'the day' comes closer, and you guys work on close areas inside pg - I would like to be able to safely run both patches. I will give both a go, once I get some free time here. -- Grzegorz Jaskiewicz starving C/C++ freelance for hire ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Fixing hash index build time
Ühel kenal päeval, K, 2007-03-21 kell 17:25, kirjutas Bruce Momjian: Added to TODO: o During index creation, pre-sort the tuples to improve build speed http://archives.postgresql.org/pgsql-hackers/2007-03/msg01199.php Maybe the TODO text should mention, that it is about HASH indexes ? --- Tom Lane wrote: I wrote: I'm not sure if this has been discussed before, but I suddenly realized while responding to the above message that the reason for the awful performance is pretty obvious: hashbuild starts with a minimum-size index (two buckets) and repeatedly splits buckets as insertions are done, exactly the same as ordinary dynamic growth of the index would do. This means that for an N-row table, approximately N/entries-per-bucket splits need to occur during index build, which results in roughly O(N^2) performance because we have to reprocess already-inserted entries over and over. Well, unfortunately this theory seems to be all wet. Given that the bucket loading is reasonably even, the time to split a bucket is about constant and so there's no O(N^2) effect. (The multiplier hidden inside O(N) is pretty awful, but it doesn't change with N.) The real reason why performance falls off a cliff for building large hash indexes seems to be much harder to fix: basically, once the size of your index exceeds working memory, it's nap time. Given that the incoming data has randomly distributed hash values, each bucket is about as likely to be touched next as any other; there is no locality of access and so the working set is the same size as the index. Once it doesn't fit in RAM anymore you're into swap hell. The only way I can see to fix that is to try to impose some locality of access during the index build. This is not impossible: for example, given a choice for the number of buckets, we could sort all the index tuples by hashed bucket number and then start inserting. btree does a preliminary sort, and its index build times are way more reasonable than hash's currently are, so the cost of the sort isn't outrageous. (I note this is mainly because we know how to do sorting with locality of access...) Before we start inserting we will know exactly how many tuples there are, so we can pre-create the right number of buckets and be sure that no on-the-fly splits will be needed for the rest of the build. If we guessed wrong about the number of buckets there will be some places in the process where we concurrently insert into several buckets not just one, or perhaps come back to a bucket that we touched earlier, but that's still maintaining plenty of locality of access. This is looking like more work than I want to do in the near future, but I thought I'd put it into the archives for someone to tackle. Bruce, would you add a TODO item linking to this: * Improve hash index build time by sorting 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 -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com NOTICE: This communication contains privileged or other confidential information. If you have received it in error, please advise the sender by reply email and immediately delete the message and any attachments without copying or disclosing the contents. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Patch for pg_dump
On 3/21/07, Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian [EMAIL PROTECTED] writes: I guess this matches this TODO item: o Allow selection of individual object(s) of all types, not just tables (...) Code-wise, the patch seems a bit of a mess too --- it will certainly not scale up to dumping some functions and some other things, as one would expect for instance if one said pg_dump -Q myfunc -t mytab It doesn't even look like it will handle multiple -Q switches. I think a minimum expectation is that -Q would work like -t now does. Well it was my first patch :-) So I suggest to use a generic argument like --object=function, which could be extended later to object=type, table, sequence, trigger... But now I have another problem: how to specify a name ? regards, D.
Re: [HACKERS] CREATE INDEX and HOT - revised design
speaking with pavan off list he seems to think that only 'create index' is outside transaction, not the other ddl flavors of it because they are generally acquiring a excl lock. so, in that sense it is possibly acceptable to me although still a pretty tough pill to swallow (thinking, guc time). It would also preclude ever integrating vanilla 'create index' to create table command, fwiw. Just to signal that it is in use: we did use create index in transactions occasionally when we had to do DB schema upgrade on production systems for application upgrades which span multiple versions of our application (normally we upgrade versions one by one, but we have some systems which are upgraded rarely). In these occasions it was riskier than usually to run the cumulated upgrade scripts outside a transaction block. But that was mostly a convenience feature, we could always rearrange our upgrade scripts to do all the rest first and then all the index creation at the end if all the rest succeeded... but if implicit index creation fails (e.g. when adding a new field to a table which happens also to be a primary key) inside the transaction, that would hurt... mostly in more work/more risks of extended downtime, but it will have a factor of inconvenience. Cheers, Csaba. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] CREATE INDEX and HOT - revised design
On 3/22/07, Csaba Nagy [EMAIL PROTECTED] wrote: speaking with pavan off list he seems to think that only 'create index' is outside transaction, not the other ddl flavors of it because they are generally acquiring a excl lock. so, in that sense it is possibly acceptable to me although still a pretty tough pill to swallow (thinking, guc time). It would also preclude ever integrating vanilla 'create index' to create table command, fwiw. Just to signal that it is in use: we did use create index in transactions occasionally when we had to do DB schema upgrade on production systems for application upgrades which span multiple versions of our application (normally we upgrade versions one by one, but we have some systems which are upgraded rarely). In these occasions it was riskier than usually to run the cumulated upgrade scripts outside a transaction block. But that was mostly a convenience feature, we could always rearrange our upgrade scripts to do all the rest first and then all the index creation at the end if all the rest succeeded... but if implicit index creation fails (e.g. when adding a new field to a table which happens also to be a primary key) inside the transaction, that would hurt... mostly in more work/more risks of extended downtime, but it will have a factor of inconvenience. What I am hearing from many users is that its probably not such a nice thing to put such restriction. Thats fair. It really helps to think about a solution once you know what is acceptable and what is not. I am back to the drawing board. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
[HACKERS] Function cache regeneration
Hi, I'm looking for a way to regenerate the plpgsql function cache, without CREATE OR REPLACE function or restarting database... Sometimes, tables could be dropped and recreated, and then all our function working on these tables failed... In fact we've more than 100 functions in our intranet database, and we don't want nor recreating all of them nor restarting the database... Is there a way to do this in SQL? Thanks Hubert FONGARNAND _ _ __ _ _ __ __ _ | ___(_) __| |_ _ ___(_) __ _| | / ___|| |_ __ _ / _|/ _(_)_ __ __ _ | |_ | |/ _` | | | |/ __| |/ _` | | \___ \| __/ _` | |_| |_| | '_ \ / _` | | _| | | (_| | |_| | (__| | (_| | | ___) | || (_| | _| _| | | | | (_| | |_| |_|\__,_|\__,_|\___|_|\__,_|_| |/ \__\__,_|_| |_| |_|_| |_|\__, | |___/ ___ Ce message et les éventuels documents joints peuvent contenir des informations confidentielles. Au cas où il ne vous serait pas destiné, nous vous remercions de bien vouloir le supprimer et en aviser immédiatement l'expéditeur. Toute utilisation de ce message non conforme à sa destination, toute diffusion ou publication, totale ou partielle et quel qu'en soit le moyen est formellement interdite. Les communications sur internet n'étant pas sécurisées, l'intégrité de ce message n'est pas assurée et la société émettrice ne peut être tenue pour responsable de son contenu.
Re: [HACKERS] CREATE INDEX and HOT - revised design
Pavan Deolasee wrote: What I am hearing from many users is that its probably not such a nice thing to put such restriction. Thats fair. It really helps to think about a solution once you know what is acceptable and what is not. That's likely to be the reaction for almost any restriction you can imagine. Performance improvements are great, but you can't ask people for whom current performance is adequate to pay a price in functionality for them. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Google SoC idea: FTS support in GUI tools
Hello, There are many users (especially novice) asking about better and easier configuration of tsearch2, which is quite complicated currently. Even new FTS features in 8.3 (e.g. much clearer configuration with SQL commands) won't help much for users that want simple several clicks setup of a small system with full text indexing. Adding support of nice user-friendly FTS configuration in popular PostgreSQL GUI administration tools would help a lot to spread PostgreSQL among wide group of potential users. Also certain GUI features can be helpful even for experienced users. That is why I would like to propose idea to implement FTS configuration functionality in GUI tools in a frame of Google SoC 2007. Basic plan of the work is to code all interfaces and debug ideas firstly in phpPgAdmin and then re-implement ready solutions in pgadmin3. I consider below features as most useful and would like to hear any comments what to change/add to this list. - interfaces to add/modify FTS configurations, dictionaries and tokens with their processing rules (with drop down menus to exclude typos and other useful features) - user-frienly visualisation of ts_debug() to simplify understanding of what processing happened with the input text and swtiches of configuration against which we tested the input - FTS operators in usual phpPgAdmin table select interface - online query rewriting (special table to store rewrite rules and test if given rule works as expected); this is especially helpful because does not require reindexing - possible interface to index statistics and heuristics to understand if its healthy (to be discussed with Oleg) - user-friendly index creation (tsvector column + FTS index + trigger on parent text field) - ability to check if FTS configured properly. This includes: * some knowledge contained in the interface to perform tests * possibly several FTS core features like check if dictionary files exist and are accessible (will be proposed before 8.3 feature freeze, to be discussed with Oleg) * ability to reset configuration and re-parse all dictionary files - all user actions should generate on demand valid SQL suitable for copy-paste into other clients - possibly implement simple PHP interface for text searches with themes support to simplify building of FTS-enabled web-interfaces (just copypaste this PHP code and one line of HTML form code to your site and switch on FTS on given table in phpPgAdmin) Open questions: - what to change/add to this list? - should we support old tsearch2 configuration for backward compatibilty with versions prior to 8.3? This seriously increases amount of work to be done. - should we analyze FTS configuration experience and interfaces existing in $$$ databases? One of the advantage of this work would be independance from PostgreSQL 8.3 release that does not fit into SoC schedule. It means that one does not need to wait till next PostgreSQL release since SoC results can be released just after it finishes with nearest phpPgAdmin release. Any feedback is welcome. Best regards, Ivan Zolotukhin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Re: [PATCHES] As proposed the complete changes to pg_trigger and pg_rewrite
On 3/21/2007 10:24 PM, Bruce Momjian wrote: Ah, so you wait for me to go on vacation to apply it! Well, I am back now, buddy. ;-) Got to use my chances, don't I? :-p One thing that bothers me about the patch is that it seems you are adding functionality that allows you to enable/disable trigger firing in groups, which is fine, but you are hard-coding the use of that grouping to be replication, e.g. origin, replica, etc. Should these designations be more generic in case there are other uses for enabling/disabling groups of triggers? That would be fine with me, I just wasn't able to come up with any sensible naming scheme other than replication related. Can you? Jan --- Jan Wieck wrote: For discussion: Attached is the completed patch that changes pg_trigger and extends pg_rewrite in order to allow triggers and rules to be defined with different, per session controllable, behaviors for replication purposes. This will allow replication systems like Slony-I and, as has been stated on pgsql-hackers, other products to control the firing mechanism of triggers and rewrite rules without modifying the system catalog directly. The firing mechanisms are controlled by a new superuser-only GUC variable, session_replication_role, together with a change to pg_trigger.tgenabled and a new column pg_rewrite.ev_enabled. Both columns are a single char data type now (tgenabled was a bool before). The possible values in these attributes are: 'O' - Trigger/Rule fires when session_replication_role is origin (default) or local. This is the default behavior. 'D' - Trigger/Rule is disabled and fires never 'A' - Trigger/Rule fires always regardless of the setting of session_replication_role 'R' - Trigger/Rule fires when session_replication_role is replica The GUC variable can only be changed as long as the system does not have any saved SPI plans. This will prevent changing the session role and accidentally executing stored procedures or functions that have plans cached that expand to the wrong query set due to differences in the rule firing semantics. The SQL syntax for changing a triggers/rules firing semantics is ALTER TABLE tabname when TRIGGER|RULE name; when ::= ENABLE | ENABLE ALWAYS | ENABLE REPLICA | DISABLE psql's \d command as well as pg_dump are extended in a backward compatible fashion. Any volunteers to do the corresponding documentation changes should this patch be accepted? -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] CREATE INDEX and HOT - revised design
On 3/22/07, Pavan Deolasee [EMAIL PROTECTED] wrote: On 3/22/07, Csaba Nagy [EMAIL PROTECTED] wrote: speaking with pavan off list he seems to think that only 'create index' is outside transaction, not the other ddl flavors of it because they are generally acquiring a excl lock. so, in that sense it is possibly acceptable to me although still a pretty tough pill to swallow (thinking, guc time). It would also preclude ever integrating vanilla 'create index' to create table command, fwiw. Just to signal that it is in use: we did use create index in transactions occasionally when we had to do DB schema upgrade on production systems for application upgrades which span multiple versions of our application (normally we upgrade versions one by one, but we have some systems which are upgraded rarely). In these occasions it was riskier than usually to run the cumulated upgrade scripts outside a transaction block. But that was mostly a convenience feature, we could always rearrange our upgrade scripts to do all the rest first and then all the index creation at the end if all the rest succeeded... but if implicit index creation fails (e.g. when adding a new field to a table which happens also to be a primary key) inside the transaction, that would hurt... mostly in more work/more risks of extended downtime, but it will have a factor of inconvenience. What I am hearing from many users is that its probably not such a nice thing to put such restriction. Thats fair. It really helps to think about a solution once you know what is acceptable and what is not. I am back to the drawing board. The objections to 'create index' forced to being non-transactional come from what I can see two general cases: * update scripts * functions that create tables, etc ISTM that in both cases nobody would complain too much if the lock was escalated to exclusive lock in those cases. So, maybe an alternative solution is this: * Make create index/create index concurrently extra-transactional as Pavan suggested. * Introduce index creation ability to alter/create table. If as Pavan suggests this integrates well with HOT due to excl lock, alter table can be left transactional. Now, there is at least alternative path to take in use cases previously covered by create index + transaction. now, there is a clean break between classic DDL (alter table, etc) and non-transactional maintenance commands, like vacuum which create index becomes part of. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] fixing dllist?
Another change that could be done to Dllist is removing the Dllist
pointer from the Dlelem struct:
Index: src/include/lib/dllist.h
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/lib/dllist.h,v
retrieving revision 1.27
diff -c -p -r1.27 dllist.h
*** src/include/lib/dllist.h5 Jan 2007 22:19:55 - 1.27
--- src/include/lib/dllist.h18 Mar 2007 05:53:12 -
*** typedef struct Dlelem
*** 50,56
struct Dlelem *dle_next;/* next element */
struct Dlelem *dle_prev;/* previous element */
void *dle_val;/* value of the element */
- struct Dllist *dle_list;/* what list this element is in */
} Dlelem;
typedef struct Dllist
--- 49,54
This means that to remove a element from a list or move it to the front of the
list, you need not only know the element pointer itself, but also the list
pointer. This capability is not used much however; the only patch of any
significance needed is below. The rest of the callers know the list pointer
already.
I tried to measure a performance difference with pgbench (using a test
small enough to fit in memory, fsync off and initialized with -s 10,
test runs with -c 5) but the differences seem to be way down in the
noise.
Are there objections to this change?
Index: src/backend/utils/cache/catcache.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/utils/cache/catcache.c,v
retrieving revision 1.136
diff -c -p -r1.136 catcache.c
*** src/backend/utils/cache/catcache.c 5 Jan 2007 22:19:42 - 1.136
--- src/backend/utils/cache/catcache.c 18 Mar 2007 06:38:23 -
*** CatCachePrintStats(int code, Datum arg)
*** 327,332
--- 327,335
static void
CatCacheRemoveCTup(CatCache *cache, CatCTup *ct)
{
+ uint32 hashValue;
+ Index hashIndex;
+
Assert(ct-refcount == 0);
Assert(ct-my_cache == cache);
*** CatCacheRemoveCTup(CatCache *cache, CatC
*** 343,349
}
/* delink from linked list */
! DLRemove(ct-cache_elem);
/* free associated tuple data */
if (ct-tuple.t_data != NULL)
--- 346,354
}
/* delink from linked list */
! hashValue = CatalogCacheComputeTupleHashValue(cache, ct-tuple);
! hashIndex = HASH_INDEX(hashValue, cache-cc_nbuckets);
! DLRemove(cache-cc_bucket[hashIndex], ct-cache_elem);
/* free associated tuple data */
if (ct-tuple.t_data != NULL)
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
---(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] Function cache regeneration
On 3/22/07, Hubert FONGARNAND [EMAIL PROTECTED] wrote: Hi, I'm looking for a way to regenerate the plpgsql function cache, without CREATE OR REPLACE function or restarting database... Sometimes, tables could be dropped and recreated, and then all our function working on these tables failed... In fact we've more than 100 functions in our intranet database, and we don't want nor recreating all of them nor restarting the database... Is there a way to do this in SQL? there is technique to recompile pl/pgsql command, etc taking the function name or oid i think. search the archives... plan invalidation is coming to 8.3 i think. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Fixing hash index build time
Hannu Krosing wrote: ?hel kenal p?eval, K, 2007-03-21 kell 17:25, kirjutas Bruce Momjian: Added to TODO: o During index creation, pre-sort the tuples to improve build speed http://archives.postgresql.org/pgsql-hackers/2007-03/msg01199.php Maybe the TODO text should mention, that it is about HASH indexes ? It is in the HASH section of the TODO list. --- --- Tom Lane wrote: I wrote: I'm not sure if this has been discussed before, but I suddenly realized while responding to the above message that the reason for the awful performance is pretty obvious: hashbuild starts with a minimum-size index (two buckets) and repeatedly splits buckets as insertions are done, exactly the same as ordinary dynamic growth of the index would do. This means that for an N-row table, approximately N/entries-per-bucket splits need to occur during index build, which results in roughly O(N^2) performance because we have to reprocess already-inserted entries over and over. Well, unfortunately this theory seems to be all wet. Given that the bucket loading is reasonably even, the time to split a bucket is about constant and so there's no O(N^2) effect. (The multiplier hidden inside O(N) is pretty awful, but it doesn't change with N.) The real reason why performance falls off a cliff for building large hash indexes seems to be much harder to fix: basically, once the size of your index exceeds working memory, it's nap time. Given that the incoming data has randomly distributed hash values, each bucket is about as likely to be touched next as any other; there is no locality of access and so the working set is the same size as the index. Once it doesn't fit in RAM anymore you're into swap hell. The only way I can see to fix that is to try to impose some locality of access during the index build. This is not impossible: for example, given a choice for the number of buckets, we could sort all the index tuples by hashed bucket number and then start inserting. btree does a preliminary sort, and its index build times are way more reasonable than hash's currently are, so the cost of the sort isn't outrageous. (I note this is mainly because we know how to do sorting with locality of access...) Before we start inserting we will know exactly how many tuples there are, so we can pre-create the right number of buckets and be sure that no on-the-fly splits will be needed for the rest of the build. If we guessed wrong about the number of buckets there will be some places in the process where we concurrently insert into several buckets not just one, or perhaps come back to a bucket that we touched earlier, but that's still maintaining plenty of locality of access. This is looking like more work than I want to do in the near future, but I thought I'd put it into the archives for someone to tackle. Bruce, would you add a TODO item linking to this: * Improve hash index build time by sorting 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 -- Hannu Krosing Database Architect Skype Technologies O? Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com NOTICE: This communication contains privileged or other confidential information. If you have received it in error, please advise the sender by reply email and immediately delete the message and any attachments without copying or disclosing the contents. ---(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 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Fixing hash index build time
Hannu Krosing [EMAIL PROTECTED] writes: Ãhel kenal päeval, K, 2007-03-21 kell 17:25, kirjutas Bruce Momjian: Added to TODO: o During index creation, pre-sort the tuples to improve build speed Maybe the TODO text should mention, that it is about HASH indexes ? It's under the hash-index heading. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] fixing dllist?
Alvaro Herrera [EMAIL PROTECTED] writes: Another change that could be done to Dllist is removing the Dllist pointer from the Dlelem struct: I think this is a bad idea. The patch you propose makes CatCacheRemoveCTup significantly more expensive (extra hash calculation). Moreover, the savings involved is entirely illusory: palloc chunks are powers of 2, therefore 3 pointers take the same space as 4. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Function cache regeneration
Hubert FONGARNAND [EMAIL PROTECTED] writes: I'm looking for a way to regenerate the plpgsql function cache, without CREATE OR REPLACE function or restarting database... You don't have to restart the whole database, just start fresh connections. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] LIKE optimization in UTF-8 and locale-C
ITAGAKI Takahiro [EMAIL PROTECTED] writes: I found LIKE operators are slower on multi-byte encoding databases than single-byte encoding ones. It comes from difference between MatchText() and MBMatchText(). We've had an optimization for single-byte encodings using pg_database_encoding_max_length() == 1 test. I'll propose to extend it in UTF-8 with locale-C case. If this works for UTF8, won't it work for all the backend-legal encodings? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TOASTing smaller things
I advocate the following: - Enable specification of TOAST policy on a per column basis As a first step, then: - Enable vertical partitioning of tables using per-column specification of storage policy. - Luke On 3/21/07 1:12 PM, Bruce Momjian [EMAIL PROTECTED] wrote: Is this a TODO? --- Jan Wieck wrote: On 3/21/2007 2:05 PM, Tom Lane wrote: Chris Browne [EMAIL PROTECTED] writes: #define TOAST_DENOMINATOR 17 /* Use this as the divisor; current default behaviour falls from TOAST_DENOMINATOR = 4 */ #define TOAST_TUPLE_THRESHOLD^I\ ^IMAXALIGN_DOWN((BLCKSZ - \ ^I^I^I^I MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) \ ^I^I^I^I / TOAST_DENOMINATOR) Given that you are quoting code that was demonstrably broken since the original coding of TOAST up till a month or two back, it passes regression is not adequate proof of it's right. In fact I think it's not right; you have not got the roundoff condition straight. 4. A different mechanism would be to add a fifth storage column strategy (the present four are PLAIN, EXTENDED, EXTERNAL, MAIN), let's say, TOAST. FORCE_COMPRESSION, FORCE_EXTERNAL and FORCE_EXTERNAL_UNCOMPRESSED. Anything along this line would require invoking the toaster on every single tuple, since we'd always have to crawl through all the columns to see if toasting was supposed to happen. No thanks. Not necessarily. A flag in Relation telling if the table has any column marked like that could be set while constructing the relcache entry. Which of these sounds preferable? It's a bit late in the cycle to be proposing any of these for 8.3. Certainly. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Hi, i want to contribute...
Hi, I want to contribute with new features in Data mining. In the University of Nariño (Colombia) works in new operators and primitives in Knowledge Discovery in Databases and We had integrated several operators in a own version named PostgresKDD, We like integrate this features in the official version. How can i get help for start??? I dont speak english good but i'll try =). -- Free Software Lives!!! Free Software Rules!!!
Re: [HACKERS] Hi, i want to contribute...
Please read the developer's FAQ on the web site first. That should have all the information you need. --- andress calderon wrote: Hi, I want to contribute with new features in Data mining. In the University of Nari?o (Colombia) works in new operators and primitives in Knowledge Discovery in Databases and We had integrated several operators in a own version named PostgresKDD, We like integrate this features in the official version. How can i get help for start??? I dont speak english good but i'll try =). -- Free Software Lives!!! Free Software Rules!!! -- 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: [HACKERS] Hi, i want to contribute...
Bruce Momjian wrote: Please read the developer's FAQ on the web site first. That should have all the information you need. Andress, First let me say welcome! We are always glad to have more contributors. As Bruce said, the first step would be reading the developers FAQ. You can find it here: http://www.postgresql.org/docs/faqs.FAQ_DEV.html There is also good information here: http://www.postgresql.org/developer/coding You might also want to check the TODO list to see if some of the things you have already done, fit in. http://www.postgresql.org/docs/faqs.TODO.html Lastly, I would say, you might want to submit to -hackers a little more specific information about what you actually have done. Again, welcome! Sincerely, Joshua D. Drake --- andress calderon wrote: Hi, I want to contribute with new features in Data mining. In the University of Nari?o (Colombia) works in new operators and primitives in Knowledge Discovery in Databases and We had integrated several operators in a own version named PostgresKDD, We like integrate this features in the official version. How can i get help for start??? I dont speak english good but i'll try =). -- Free Software Lives!!! Free Software Rules!!! -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Regression failure in PL/Tcl
Hi,
I'm seeing the following regression failure in PL/Tcl. It seems to be
just an ordering issue, and the order in which a trigger is fired.
This is a pristine, freshly updated copy of CVS head as of right now.
I'm not sure why the buildfarm is not having this problem.
alvherre=# select version();
version
--
PostgreSQL 8.3devel on x86_64-unknown-linux-gnu, compiled by GCC gcc-4.1 (GCC)
4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
(1 fila)
alvherre=# \q
$ uname -a
Linux perhan 2.6.18-3-amd64 #1 SMP Mon Dec 4 17:04:37 CET 2006 x86_64 GNU/Linux
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
*** ./expected/pltcl_queries.outSat May 27 16:24:16 2006
--- ./results/pltcl_queries.out Thu Mar 22 11:41:41 2007
***
*** 166,175
select * from T_pkey1 order by key1 using @, key2;
key1 | key2 | txt
--+--+--
- 1 | KEY1-3 | should work
1 | key1-1 | test key
1 | key1-2 | test key
1 | key1-3 | test key
2 | key2-3 | test key
2 | key2-9 | test key
(6 rows)
--- 166,175
select * from T_pkey1 order by key1 using @, key2;
key1 | key2 | txt
--+--+--
1 | key1-1 | test key
1 | key1-2 | test key
1 | key1-3 | test key
+ 1 | KEY1-3 | should work
2 | key2-3 | test key
2 | key2-9 | test key
(6 rows)
***
*** 185,190
--- 185,191
-- show dump of trigger data
insert into trigger_test values(1,'insert');
+ NOTICE: args: {23 skidoo}
NOTICE: NEW: {i: 1, v: insert}
NOTICE: OLD: {}
NOTICE: TG_level: ROW
***
*** 195,202
NOTICE: TG_table_name: trigger_test
NOTICE: TG_table_schema: public
NOTICE: TG_when: BEFORE
- NOTICE: args: {23 skidoo}
update trigger_test set v = 'update' where i = 1;
NOTICE: NEW: {i: 1, v: update}
NOTICE: OLD: {i: 1, v: insert}
NOTICE: TG_level: ROW
--- 196,203
NOTICE: TG_table_name: trigger_test
NOTICE: TG_table_schema: public
NOTICE: TG_when: BEFORE
update trigger_test set v = 'update' where i = 1;
+ NOTICE: args: {23 skidoo}
NOTICE: NEW: {i: 1, v: update}
NOTICE: OLD: {i: 1, v: insert}
NOTICE: TG_level: ROW
***
*** 207,214
NOTICE: TG_table_name: trigger_test
NOTICE: TG_table_schema: public
NOTICE: TG_when: BEFORE
- NOTICE: args: {23 skidoo}
delete from trigger_test;
NOTICE: NEW: {}
NOTICE: OLD: {i: 1, v: update}
NOTICE: TG_level: ROW
--- 208,215
NOTICE: TG_table_name: trigger_test
NOTICE: TG_table_schema: public
NOTICE: TG_when: BEFORE
delete from trigger_test;
+ NOTICE: args: {23 skidoo}
NOTICE: NEW: {}
NOTICE: OLD: {i: 1, v: update}
NOTICE: TG_level: ROW
***
*** 219,223
NOTICE: TG_table_name: trigger_test
NOTICE: TG_table_schema: public
NOTICE: TG_when: BEFORE
- NOTICE: args: {23 skidoo}
--- 220,223
==
---(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] Regression failure in PL/Tcl
Alvaro Herrera wrote: Hi, I'm seeing the following regression failure in PL/Tcl. It seems to be just an ordering issue, and the order in which a trigger is fired. This is a pristine, freshly updated copy of CVS head as of right now. I removed the derived files (moral equivalent of make distclean) and the failure persists. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] CREATE INDEX and HOT - revised design
On 3/21/07, Bruce Momjian [EMAIL PROTECTED] wrote: A different idea is to flag the _index_ as using HOT for the table or not, using a boolean in pg_index. The idea is that when a new index is created, it has its HOT boolean set to false and indexes all tuples and ignores HOT chains. Then doing lookups using that index, the new index does not follow HOT chains. We also add a boolean to pg_class to indicate no new HOT chains should be created and set that to false once the new index is created. Then, at some later time when all HOT chains are dead, we can enable HOT chain following for the new index and allow new HOT chains to be created. A more sophisticated idea would be to place an xid, rather than a boolean, in pg_index to indicate which chains were created after the index was created to control whether the index should follow that HOT chain, or ignore it. The xmax of the head of the HOT chain can be used as an indicator of when the chain was created. Transactions started before the pg_index xid could continue following the old rules and insert into the _new_ index for HOT chain additions, and new transactions would create HOT chains that could skip adding to the new index. Cleanup of the hybrid HOT chains (some indexes take part, some do not) would be more complex. Bruce, thanks for bringing up this idea. As I think more about this idea, I think I am able to extend this further to solve the problems we discussed around it. One of my conerns were that the change the basic structure of heap with HOT-chains so that it should be possible to just look at the heap tuple and say whether it has any index pointer or not. The way I propose to extend/modify the idea is to use pg_index xid as suggested by Bruce to mark the index. This xid would guide the visibility of the index. As we all know, CREATE INDEX locks out UPDATEs on the table and further UPDATEs are possible only after the transaction creating the new index commits. When CREATE INDEX starts, it acquires ShareLock on the table. At this point we may have one or more HOT-update chains in the table. Tuples in this chain may be visible to one or more running transactions. The fact that we have ShareLock on the table means that all tuples in the chain except the one at the head either RECENTLY_DEAD or were UPDATEd by the same transaction that is now running CREATE INDEX. With this background, I propose to index ONLY the head of the HOT-chain. The TID of the root tuple is used instead of the actual TID of the tuple being indexed. This index will not be available to the transactions which are started before the CREATE INDEX transaction. Just like we use indisvalid flag to avoid including an invalid index in the plan, we use the pg_index xid to decide whether to use the index in the plan or not. Only transactions with txid pg_index:xid can see the index and use it. In fact, the serializable transactions started before CREATE INDEX can not anyway see the index so all this is done to handle read-committed transactions. In this proposal we indexed only the latest version. But none of the transactions started after CREATE INDEX can anyway see the older tuples and hence we should be fine even if we don't index them in the new index. And none of the older transaction can see the index, so again we are safe. The design also helps us to preserve the heap HOT semantics and chain pruning and does not need VACUUM or any special handling. Can anyone spot a hole in this logic ? Comments ? Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] TOASTing smaller things
Luke Lonergan wrote: I advocate the following: - Enable specification of TOAST policy on a per column basis As a first step, then: - Enable vertical partitioning of tables using per-column specification of storage policy. Wouldn't it be enough to enable having the toast table on a different table space? Regards, Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Regression failure in PL/Tcl
Alvaro Herrera [EMAIL PROTECTED] writes: I'm seeing the following regression failure in PL/Tcl. It seems to be just an ordering issue, and the order in which a trigger is fired. Works on my x86_64 box. Are you running the test in C locale (because it looks a bit like a sort-ordering issue)? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Regression failure in PL/Tcl
Alvaro Herrera wrote: Hi, I'm seeing the following regression failure in PL/Tcl. It seems to be just an ordering issue, and the order in which a trigger is fired. This is a pristine, freshly updated copy of CVS head as of right now. I'm not sure why the buildfarm is not having this problem. What is the collation setting for the instance you are running against? If you didn't initdb --no-locale (which is what buildfarm clients do) then you might expect something like this. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Regression failure in PL/Tcl
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: I'm seeing the following regression failure in PL/Tcl. It seems to be just an ordering issue, and the order in which a trigger is fired. Works on my x86_64 box. Are you running the test in C locale (because it looks a bit like a sort-ordering issue)? Oh, I wasn't aware of that. I did initdb --no-locale per Andrew's suggestion and it worked. Sorry for the noise. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] TOASTing smaller things
Andreas, On 3/22/07 9:40 AM, Andreas Pflug [EMAIL PROTECTED] wrote: Wouldn't it be enough to enable having the toast table on a different table space? Yes, but the ultimate goal would allow the allocation of a storage mechanism that is unique to each column. The most frequently used mechanism for our customers would likely be to have each column associated with it's own internal relation (like a separate TOAST table), which puts each column into it's own dense page storage. Beside the advantages of separating out keys columns from data columns, compression and encryption approaches that use column packed data are much more effective. As Tom points out there are complications WRT update, access, etc that need to be worked out to support this, but it's an important capability to have IMO. - Luke ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] CREATE INDEX and HOT - revised design
Pavan Deolasee [EMAIL PROTECTED] writes: When CREATE INDEX starts, it acquires ShareLock on the table. At this point we may have one or more HOT-update chains in the table. Tuples in this chain may be visible to one or more running transactions. The fact that we have ShareLock on the table means that all tuples in the chain except the one at the head either RECENTLY_DEAD or were UPDATEd by the same transaction that is now running CREATE INDEX. This logic seems pretty questionable to me in view of the recent VACUUM FULL bug fixes. We now know that an update chain can contain tuples that appear DEAD but are later than ones that are RECENTLY_DEAD. How are you defining a HOT chain exactly --- will it be just a part of an update chain that consists only of contiguous tuples that appear live or RECENTLY_DEAD to the CREATE INDEX transaction? In fact, the serializable transactions started before CREATE INDEX can not anyway see the index so all this is done to handle read-committed transactions. You are laboring under an illusion that system catalog accesses are MVCC. SnapshotNow does not behave that way: the system can see the new index as soon as it's committed. (It had better, since it has to start updating the index immediately, whether it's safe to scan it or not.) I'm not sure whether that's fundamental to your argument or not, but it's certainly wrong. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Bug in CREATE/DROP TABLESPACE command
William Garrison [EMAIL PROTECTED] writes: Tom Lane wrote: What do you mean by one batch exactly? Both CREATE and DROP TABLESPACE refuse to run in a transaction block, so I'm confused about this. Not a transaction block. A batch of commands submitted to the server in a single call. In MSSQL land, I call that a batch. I don't know the PostgreSql term. If you are using the pgadmin3 GUI, then I mean pressing F5 once is a single batch. Pressing it twice is two batches. Oh, it's a single simple-Query message. You could reproduce the problem with psql if you put multiple commands into a -c command line switch. This is a basic oversight in PreventTransactionChain: it doesn't reject the case where the command is submitted as part of a multi-query string in a single Query message. This is relatively easy to fix in CVS HEAD --- we can just teach exec_simple_query to pass isTopLevel = true only when the querystring contains a single command, or maybe better only for the last command of a querystring. I don't see any very practical way to fix it in older releases though; at least not anything I'd want to backpatch when it can't be tested first in HEAD. Anyone have an idea about a reasonable back-branch fix? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] CREATE INDEX and HOT - revised design
On 3/22/07, Tom Lane [EMAIL PROTECTED] wrote: Pavan Deolasee [EMAIL PROTECTED] writes: When CREATE INDEX starts, it acquires ShareLock on the table. At this point we may have one or more HOT-update chains in the table. Tuples in this chain may be visible to one or more running transactions. The fact that we have ShareLock on the table means that all tuples in the chain except the one at the head either RECENTLY_DEAD or were UPDATEd by the same transaction that is now running CREATE INDEX. This logic seems pretty questionable to me in view of the recent VACUUM FULL bug fixes. We now know that an update chain can contain tuples that appear DEAD but are later than ones that are RECENTLY_DEAD. How are you defining a HOT chain exactly --- will it be just a part of an update chain that consists only of contiguous tuples that appear live or RECENTLY_DEAD to the CREATE INDEX transaction? HOT-chain is something which contains contiguous tuples that share the same index keys for all the existing indexes and share a single index entry. It does not really matter if there are RECENTLY_DEAD tuples that appear before a DEAD tuple because from VACUUM FULL bug fix we know that they are DEAD too. My argument is that its enough to index only the LIVE tuple which is at the end of the chain if we don't use the new index for queries in transactions which were started before CREATE INDEX. I am proposing to do that by storing an xid in the pg_index row. A special case is where a tuple is UPDATEd multiple times by the same transaction which is also creating the index, in which case there are more than one LIVE versions of the tuple. But again we are safe by indexing only the latest version because all other versions would be invisible (even to us) once CREATE INDEX commits. In fact, the serializable transactions started before CREATE INDEX can not anyway see the index so all this is done to handle read-committed transactions. You are laboring under an illusion that system catalog accesses are MVCC. SnapshotNow does not behave that way: the system can see the new index as soon as it's committed. (It had better, since it has to start updating the index immediately, whether it's safe to scan it or not.) I'm not sure whether that's fundamental to your argument or not, but it's certainly wrong. Oh, thanks for pointing that out. But thats certainly not fundamental to the argument as you probably already guessed. The xid still controls the usage of index for query planning, somewhat similar to isindvalid flag for CREATE INDEX CONCURRENTLY. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] [BUGS] Relation not found error but table exits.
TANIDA Yutaka [EMAIL PROTECTED] writes: My customer found a problem about PL/pgsql functions and TRUNCATE command. If you execute PL/pgsql function includeing TRUNCATE command concurrently, causes relation ... does not exist. or relation with OID X does not exist against exists table. Here's a testcase to reproduce this. After some thought I have a theory about what's happening here. The test case involves lots of TRUNCATEs, which each will do an update on the relation's pg_class row. Now an incoming operation on the table has to look up the relation's OID before it can obtain lock, so that means that it is scanning pg_class using the relname index concurrently with these updates. That scan is done using SnapshotNow rules, which means that it's possible for this sequence of events to occur: 1. TX A updates pg_class row. 2. TX B visits the updated row while scanning; it's not committed good, so it's ignored. 3. TX A commits. 4. TX B visits the old row in its scan. By now it's committed dead, so it's also ignored. 5. Hence TX B fails to find any live row matching the requested table name, and comes back with relation does not exist. I'm not sure about a good way to fix this. It sorta looks like we need a different visibility rule for scanning pg_class when we don't yet have any lock on the relation, but I'm unclear what that rule ought to be. This also ties into the discussions we've had off-and-on about making catalog lookups behave in an MVCC fashion instead of using SnapshotNow. I'm still pretty hesitant to go there, but maybe we could do something involving MVCC for unlocked lookups and then SnapshotNow for (re)reading a table's schema info once we've got lock on it. Ideas anyone? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: CREATE INDEX and HOT (was [HACKERS] Question: pg_classattributes and race conditions ?)
On Mon, Mar 19, 2007 at 12:05:19PM +, Simon Riggs wrote: I was unwilling to compromise to have HOT if only one index existed, but IMHO allowing HOT with = 3 indexes is an acceptable compromise for this release. (We can always use vertical partitioning techniques to allow additional access paths to be added to the same table - I'd be very happy to document that with worked examples, if requried). I'm not sure where we're sitting with this, but I've got another idea I haven't seen (one that I think is better than an arbitrary limit on the number of indexes)... what if we just disallow non-concurrent index builds on hot tables? It sounds like the additional pain involved in chilling an entire table and keeping it chilled for the index build is even more overhead than just doing a concurrent index build. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CREATE INDEX and HOT - revised design
Pavan Deolasee wrote: My argument is that its enough to index only the LIVE tuple which is at the end of the chain if we don't use the new index for queries in transactions which were started before CREATE INDEX. I am proposing to do that by storing an xid in the pg_index row. A special case is where a tuple is UPDATEd multiple times by the same transaction which is also creating the index, in which case there are more than one LIVE versions of the tuple. But again we are safe by indexing only the latest version because all other versions would be invisible (even to us) once CREATE INDEX commits. What if CREATE INDEX is run in a SERIALIZABLE transaction? In fact, the serializable transactions started before CREATE INDEX can not anyway see the index so all this is done to handle read-committed transactions. You are laboring under an illusion that system catalog accesses are MVCC. SnapshotNow does not behave that way: the system can see the new index as soon as it's committed. (It had better, since it has to start updating the index immediately, whether it's safe to scan it or not.) I'm not sure whether that's fundamental to your argument or not, but it's certainly wrong. Oh, thanks for pointing that out. But thats certainly not fundamental to the argument as you probably already guessed. The xid still controls the usage of index for query planning, somewhat similar to isindvalid flag for CREATE INDEX CONCURRENTLY. I am glad you found the pg_index xid actually helps in other ways. -- 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 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] CREATE INDEX and HOT - revised design
On Thu, 2007-03-22 at 22:11 +0530, Pavan Deolasee wrote: With this background, I propose to index ONLY the head of the HOT-chain. The TID of the root tuple is used instead of the actual TID of the tuple being indexed. This index will not be available to the transactions which are started before the CREATE INDEX transaction. Just like we use indisvalid flag to avoid including an invalid index in the plan, we use the pg_index xid to decide whether to use the index in the plan or not. Only transactions with txid pg_index:xid can see the index and use it. In fact, the serializable transactions started before CREATE INDEX can not anyway see the index so all this is done to handle read-committed transactions. In this proposal we indexed only the latest version. But none of the transactions started after CREATE INDEX can anyway see the older tuples and hence we should be fine even if we don't index them in the new index. And none of the older transaction can see the index, so again we are safe. The design also helps us to preserve the heap HOT semantics and chain pruning and does not need VACUUM or any special handling. Well, ISTM you've nailed it. CREATE INDEX returns as soon as possible, but people will have to wait for their next transaction before they can see it and use it too. Nice role reversal to avoid having CREATE INDEX wait. No restrictions on the number of indexes, no restrictions on multiple concurrent index builders and we can do this in just one pass. The ShareLock taken by CREATE INDEX guarantees all transactions that wrote data to the table have completed and that no new data can be added until after the index build commits. So the end of the chain is visible to CREATE INDEX and won't change. As long as you index the latest committed version on each HOT chain, then I think it works. Clearly want to ignore aborted versions. Sounds like you'll need to read the HOT chains in sequence to ensure we don't repeat the VACUUM FULL error. If there are no HOT chains then it will be just a normal seq scan of each block, so there's no real speed loss for situations where no HOT updates have taken place, such as reload from pg_dump. Sounds like you'll need to store the Next TransactionId rather than the TransactionId of the CREATE INDEX. We don't need to store the ComboId as well, since all commands are planned in ComboId sequence, assuming plan invalidation blows away any earlier plans held by our own backend. There is a slight hole in that SERIALIZABLE transactions won't be able to use any indexes they build during their transaction, since they may need to be able to see prior data, but I don't think anybody is going to complain about that restriction. Anyone? Maybe we can use this technique for CREATE INDEX CONCURRENTLY as well, so that it doesn't have to wait either. That needs some careful thinking... it may not work the same because of the locking differences. -- 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: [HACKERS] TOASTing smaller things
[EMAIL PROTECTED] (Luke Lonergan) writes: Andreas, On 3/22/07 9:40 AM, Andreas Pflug [EMAIL PROTECTED] wrote: Wouldn't it be enough to enable having the toast table on a different table space? Yes, but the ultimate goal would allow the allocation of a storage mechanism that is unique to each column. The most frequently used mechanism for our customers would likely be to have each column associated with it's own internal relation (like a separate TOAST table), which puts each column into it's own dense page storage. Beside the advantages of separating out keys columns from data columns, compression and encryption approaches that use column packed data are much more effective. As Tom points out there are complications WRT update, access, etc that need to be worked out to support this, but it's an important capability to have IMO. Hmm. Are you trying to do something sort of like CStore? http://db.csail.mit.edu/projects/cstore/ That seems to have some relevant ideas... -- let name=cbbrowne and tld=cbbrowne.com in name ^ @ ^ tld;; http://cbbrowne.com/info/finances.html Where do you *not* want to go today? Confutatis maledictis, flammis acribus addictis (http://www.hex.net/~cbbrowne/msprobs.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CREATE INDEX and HOT - revised design
Simon Riggs [EMAIL PROTECTED] writes: There is a slight hole in that SERIALIZABLE transactions won't be able to use any indexes they build during their transaction, since they may need to be able to see prior data, but I don't think anybody is going to complain about that restriction. Anyone? Practically every statement I've seen in this thread that used the phrase SERIALIZABLE transaction was wrong to some extent, and this one is no different. The issue is not whether the whole transaction is serializable or not, it's how old is the oldest still-live snapshot, a thing that CREATE INDEX can't tell with any certainty in READ COMMITTED mode. So if your solution involves any explicit dependence on the transaction serializability mode, it's probably wrong. I'm not totally sure if you are expecting to be able to tell that, but I do know that the planner has no idea what snapshots a plan it makes will be used with. 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
[HACKERS] Re: [PATCHES] As proposed the complete changes to pg_trigger and pg_rewrite
Jan Wieck wrote: On 3/21/2007 10:24 PM, Bruce Momjian wrote: Ah, so you wait for me to go on vacation to apply it! Well, I am back now, buddy. ;-) Got to use my chances, don't I? :-p One thing that bothers me about the patch is that it seems you are adding functionality that allows you to enable/disable trigger firing in groups, which is fine, but you are hard-coding the use of that grouping to be replication, e.g. origin, replica, etc. Should these designations be more generic in case there are other uses for enabling/disabling groups of triggers? That would be fine with me, I just wasn't able to come up with any sensible naming scheme other than replication related. Can you? The best I could think of would be to create numbered groups of triggers, but because I can't think of any use for that, and no one else has, I think the patch is fine unchanged. --- Jan Wieck wrote: For discussion: Attached is the completed patch that changes pg_trigger and extends pg_rewrite in order to allow triggers and rules to be defined with different, per session controllable, behaviors for replication purposes. This will allow replication systems like Slony-I and, as has been stated on pgsql-hackers, other products to control the firing mechanism of triggers and rewrite rules without modifying the system catalog directly. The firing mechanisms are controlled by a new superuser-only GUC variable, session_replication_role, together with a change to pg_trigger.tgenabled and a new column pg_rewrite.ev_enabled. Both columns are a single char data type now (tgenabled was a bool before). The possible values in these attributes are: 'O' - Trigger/Rule fires when session_replication_role is origin (default) or local. This is the default behavior. 'D' - Trigger/Rule is disabled and fires never 'A' - Trigger/Rule fires always regardless of the setting of session_replication_role 'R' - Trigger/Rule fires when session_replication_role is replica The GUC variable can only be changed as long as the system does not have any saved SPI plans. This will prevent changing the session role and accidentally executing stored procedures or functions that have plans cached that expand to the wrong query set due to differences in the rule firing semantics. The SQL syntax for changing a triggers/rules firing semantics is ALTER TABLE tabname when TRIGGER|RULE name; when ::= ENABLE | ENABLE ALWAYS | ENABLE REPLICA | DISABLE psql's \d command as well as pg_dump are extended in a backward compatible fashion. Any volunteers to do the corresponding documentation changes should this patch be accepted? -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # -- 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: [HACKERS] [RFC] CLUSTER VERBOSE
Added to TODO for CLUSTER: o %Add VERBOSE option to report tables as they are processed, like VACUUM VERBOSE --- Grzegorz Jaskiewicz wrote: On Mar 16, 2007, at 9:53 AM, Heikki Linnakangas wrote: Grzegorz Jaskiewicz wrote: Because CLUSTER is divided into two major operations, (data reordering, index rebuild) - I see it this way: CLUSTER on I: index name T: table name, data reordering CLUSTER on I: index name T: table name, index rebuild Something like that would be nice to see how long each step takes, like vacuum verbose. yup. I am looking for opinions, on what information should be presented. What would be useful is some kind of a metric of how (de)clustered the table was before CLUSTER, and the same # of dead vs. live row counts that vacuum verbose prints. Is that information available in cluster.c atm ? I am looking for some hints here. One of the reasons I decided to go with this patch, is to learn something - and cluster seems to be touching very 'bone' of postgres, tuples system (just like vacuum), and indices. I would appreciate any hints. We don't really have a good metric for clusteredness, as have been discussed before, so if you can come up with a good one that would be useful in the planner as well, that would be great. I really don't know where and how should I calculate such param. Any hints ? thanks. -- Grzegorz Jaskiewicz C/C++ freelance for hire ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- 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 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] Bug in UTF8-Validation Code?
Added to TODO:
* Fix cases where invalid byte encodings are accepted by the database,
but throw an error on SELECT
http://archives.postgresql.org/pgsql-hackers/2007-03/msg00767.php
Is anyone working on fixing this bug?
---
Mario Weilguni wrote:
Hi,
I've a problem with a database, I can dump the database to a file, but
restoration fails, happens with 8.1.4.
Steps to reproduce:
create database testdb with encoding='UTF8';
\c testdb
create table test(x text);
insert into test values ('\244'); == Is akzepted, even if not UTF8.
pg_dump testdb -f testdb.dump -Fc
pg_restore -f testdb.dump -d testdb = fails with an error:
ERROR: invalid byte sequence for encoding UTF8: 0xa4
The problem itself comes from a CSV file, which is imported with \copy
without
proper quoting (so I have to fix this anyway), but I still think this is an
error, making restoration very complicated in such cases...
Or am I doing something completly wrong here?
Best regards,
Mario Weilguni
---(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] http://momjian.us
EnterpriseDB http://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] CREATE INDEX and HOT - revised design
On Thu, 2007-03-22 at 16:16 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: There is a slight hole in that SERIALIZABLE transactions won't be able to use any indexes they build during their transaction, since they may need to be able to see prior data, but I don't think anybody is going to complain about that restriction. Anyone? Practically every statement I've seen in this thread that used the phrase SERIALIZABLE transaction was wrong to some extent, and this one is no different. The issue is not whether the whole transaction is serializable or not, it's how old is the oldest still-live snapshot, a thing that CREATE INDEX can't tell with any certainty in READ COMMITTED mode. So if your solution involves any explicit dependence on the transaction serializability mode, it's probably wrong. I'm not totally sure if you are expecting to be able to tell that, but I do know that the planner has no idea what snapshots a plan it makes will be used with. Thanks for correcting me. Reworded: There is a slight hole in that snapshots older than the CREATE INDEX must never be allowed to use the index. That means that SERIALIZABLE transactions and some other situations will need to be restricted. Personally, I would argue that such a restriction was an acceptable loss of functionality, since I can't think of a situation where such a thing would need to occur, though one may turn up. Currently, I don't know how to prevent this from happening. We'll need to examine this in more detail to see if there is a way. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] xml2 contrib patch supporting default XML namespaces
Mike Rylander wrote: A related question, however: Will the XML features being included in 8.3 support namespace prefix registration? If not, handling arbitrary XML via XPath that includes unprefixed (default) namespaces (for me that is the majority of the XML I deal with, and no, I can't change that) will have exactly the same problems using the new mechanisms as with the current xml2 contrib module. I ask because, based on the design emails I've seen on -hackers, nothing surrounding explicit support for said issue jumped out at me. If it won't you have 10 days to get in a patch. cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] TOASTing smaller things
Chris, Hmm. Are you trying to do something sort of like CStore? http://db.csail.mit.edu/projects/cstore/ That seems to have some relevant ideas... I think something like is a good way to put it. As you know Cstore was a prototype for Vertica and these are in the same class as SybaseIQ and SandDB. The huge drawback of the pure column approach is update/insert while query is difficult if not impossible. I think there are hybrid approaches that yield most, if not all of the benefits of the column store approach without the disadvantages. For instance, a bitmap index with index only access in a row-store may outperform the column store on queries. Note the index only access part. The next advantage of a column store is deep compression, preserved through the executor access path - we can do this with selective vertical partitioning using a page-segmented WAH compression similar to what we did with bitmap index. Lastly, vectorization of the operators in the executor can be implemented with vertical partitioning and an access path that does projection before feeding the columns into the executor - this can be done in Postgres with a cache-bypass method. Some of this requires working out answers to the visibility challenges inherent to MVCC, but it's all possible IMO. So - under the guise of TOASTing smaller things, it seems relevant to think about vertical partitioning, perhaps making use of what's already in Postgres as baby steps toward more advanced features. - Luke ---(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] CLUSTER and MVCC
Bruce Momjian wrote: Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: We wouldn't clean up tuples that are visible to a transaction, but if you have one long-running transaction like pg_dump in a database with otherwise short transaction, you'll have a lot of tuples that are not vacuumable because of the long-running process, but are not in fact visible to any transaction. It sounds to me like you are proposing to remove the middles of update chains, which would break READ-COMMITTED updates initiated by the older transactions. Now admittedly pg_dump isn't going to issue any such updates, but VACUUM doesn't know that. Since a multi-statement transaction can't change its transaction isolation level after its first statement, would adding a boolean to PGPROC help VACUUM be more aggressive about removing rows? I am thinking something like PGPROC.cannot_be_serializable. In researching, I found we already do this by updating PGPROC.xid for every command in non-serialzable transactions: * GetTransactionSnapshot * Get the appropriate snapshot for a new query in a transaction. * * The SerializableSnapshot is the first one taken in a transaction. * In serializable mode we just use that one throughout the transaction. * In read-committed mode, we take a new snapshot each time we are called. -- 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: [HACKERS] Hi, i want to contribute...
On Thu, 2007-03-22 at 10:36 -0500, andress calderon wrote: Hi, I want to contribute with new features in Data mining. In the University of Nariño (Colombia) works in new operators and primitives in Knowledge Discovery in Databases and We had integrated several operators in a own version named PostgresKDD, We like integrate this features in the official version. How can i get help for start??? I dont speak english good but i'll try =). Look at Regional Lists in the link above: http://www.postgresql.org/community/lists/ You may find PostgreSQL users that speak your native language and can help you communicate with the PostgreSQL developers, who are predominantly English-speaking. We're happy to see that you're interested in helping PostgreSQL! Regards, Jeff Davis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] CLUSTER and MVCC
Bruce Momjian [EMAIL PROTECTED] writes: In researching, I found we already do this by updating PGPROC.xid for every command in non-serialzable transactions: Dunno how you arrived at that conclusion, but it's quite wrong. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] CLUSTER and MVCC
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: In researching, I found we already do this by updating PGPROC.xid for every command in non-serialzable transactions: Dunno how you arrived at that conclusion, but it's quite wrong. Looking in the function I now see you are right: if (serializable) MyProc-xmin = TransactionXmin = xmin; So, can't this be improved to allow more aggressive vacuuming? -- 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 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] TOASTing smaller things
Ühel kenal päeval, N, 2007-03-22 kell 10:19, kirjutas Luke Lonergan: Andreas, On 3/22/07 9:40 AM, Andreas Pflug [EMAIL PROTECTED] wrote: Wouldn't it be enough to enable having the toast table on a different table space? Yes, but the ultimate goal would allow the allocation of a storage mechanism that is unique to each column. The most frequently used mechanism for our customers would likely be to have each column associated with it's own internal relation (like a separate TOAST table), which puts each column into it's own dense page storage. Maybe monetdb ( http://monetdb.cwi.nl/ ) can be of some inspiration ? btw, it may be a good idea to have a verion of bizgresMPP which has monetdb as partition db, if monetdb is as efficient as they tell it is . Beside the advantages of separating out keys columns from data columns, compression and encryption approaches that use column packed data are much more effective. As Tom points out there are complications WRT update, access, etc that need to be worked out to support this, but it's an important capability to have IMO. - Luke ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] CLUSTER and MVCC
Bruce Momjian [EMAIL PROTECTED] writes: So, can't this be improved to allow more aggressive vacuuming? Not at that level. We do not keep track of the oldest still-used snapshot in a transaction. I'm dubious that it'd be worth the bookkeeping trouble to try --- often as not, the problem with a long running transaction is that it's a long running statement, anyway. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TOASTing smaller things
Hi Hannu, On 3/22/07 3:21 PM, Hannu Krosing [EMAIL PROTECTED] wrote: Maybe monetdb ( http://monetdb.cwi.nl/ ) can be of some inspiration ? btw, it may be a good idea to have a verion of bizgresMPP which has monetdb as partition db, if monetdb is as efficient as they tell it is . Yep - I've talked this over with the MonetDB folks in the past. The major benefits they observe are those same things we talk about here, implementation of long loops for operators and de-abstraction of operations like compare() when appropriate, say comparing two INT columns in a sort. We can get many of those benefits without by vectorizing the executor of PostgreSQL even without the full column partitioning. We're in the midst of working some of those changes as we speak. Early indications are that we see large performance gains from this approach. Note that the actual instruction counts per row don't change, but the more effective use of L2 I and D cache and superscaler instruction units on the CPU create the big gains. The MonetDB people present some effective literature on this, but the important gains mostly come from the vectorization, not the operator de-abstraction IMO, which is good news for us all. - Luke ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] LIKE optimization in UTF-8 and locale-C
Ühel kenal päeval, N, 2007-03-22 kell 11:08, kirjutas Tom Lane: ITAGAKI Takahiro [EMAIL PROTECTED] writes: I found LIKE operators are slower on multi-byte encoding databases than single-byte encoding ones. It comes from difference between MatchText() and MBMatchText(). We've had an optimization for single-byte encodings using pg_database_encoding_max_length() == 1 test. I'll propose to extend it in UTF-8 with locale-C case. If this works for UTF8, won't it work for all the backend-legal encodings? I guess it works well for % but not for _ , the latter has to know, how many bytes the current (multibyte) character covers. The length is still easy to find out for UTF8 encoding, so it may be feasible to write UTF8MatchText() that is still faster than MBMatchText(). -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] xml2 contrib patch supporting default XML namespaces
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---
Mike Rylander wrote:
On 3/6/07, Mike Rylander [EMAIL PROTECTED] wrote:
On 3/6/07, Peter Eisentraut [EMAIL PROTECTED] wrote:
Mike Rylander wrote:
The patch adds support for default XML namespaces in xml2 by providing
a mechanism for supplying a prefix to a named namespace URI.
How does it support multiple namespaces in one document?
It supports one default (unprefixed) namespace URI per document, which
ISTM is the overwhelmingly common case (and the itch that I must
scratch).
I think there is some confusion about what the current xml2 contrib
module supports and what my patch adds. The current code, as it
stands today, supports multiple namespaces just fine. The only
requirement is that each namespace have a prefix, or else one is
forced to use the local-name() construct with every single node for
those nodes in unprefixed (default) namespaces. This patch simply
adds support for registering a prefix for an unprefixed namespace,
which is an extremely common case in XML and causes the use of overly
verbose contortions when designing XPath expressions. To illustrate
this, xml2 currently supports all of these statements:
SELECT xpath_nodeset('xyfoo/y/x','/x/y');
SELECT xpath_nodeset('xa:y xmlns:a=uri:for:afoo/a:y/x','/x/a:y');
SELECT xpath_nodeset('b:x xmlns:b=uri:for:ba:y
xmlns:a=uri:for:afoo/a:y/b:x','/b:x/a:y');
All number and manner of /prefixed/ namespaces work fine today.
However, in order to match an element or attribute with an unprefixed
namespace, the xpath becomes a study in overly verbose, human error
inducing repetition. For instance, consider the extremely common case
of an xhtml document that does not use a prefix for the xhtml
namespace. Using the xml2 contrib module as it stands today, without
my patch, using XPath to get the title of the document might look
something like this:
/*[local-name()=html]/*[local-name()=head]/*[local-name()=title]
Now just imagine the XPath needed to get a portion of the body in a
nested div based on the existence of some other node ... the logic
gets lost in the noise simply because of the overhead of
namespace-qualifying the elements.
Namespaces were introduced in XML to address verbosity issues (among
other things), but as XPath was designed primarily as a language for
use inside XSLT (where namespace support is fully integrated) it
didn't get the treatment needed to handle unprefixed namespaces. To
address /that/ issue, my patch allows the registration of a supplied
prefix for a supplied URI, which solves the common default namespace
problem in a completely backward compatible way. The above example
XPath can now become:
/x:html/x:head/x:title
simply by supplying 2 more arguments to the _ns version of any of the
xpath_ functions available in xml2. I challenge anyone to claim that
the [local-name()=foo] variant is easier to read and less error prone
than the second, namespace-prefixed variant. They are exactly
equivalent, but the second (quite obviously) is Better(tm).
I understand that XML support is planned and at least partially
implemented for 8.3, but many production instances will be unable (or,
in fact, unwilling) to upgrade to 8.3 for quite some time. Because
this patch is completely backward compatible it can (theoretically) be
included in future 8.1 and 8.2 releases, and for those of us that need
more full XML support in the short term the upgrade of a contrib
module is probably a very viable option -- it is for me, anyway.
So, to sum up, please let me know what I can do to increase the
chances of getting this patch included. Alternatively, if my patch is
being vetoed, please let me know that too so that I can create a local
maintenance plan for this.
Thanks in advance. I've attached the patch again for reference.
--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org
[ Attachment, skipping... ]
---(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 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] xpath_array with namespaces support
Applying newest version of this patch now; still needs documentation.
---
Nikolay Samokhvalov wrote:
On 3/5/07, Nikolay Samokhvalov [EMAIL PROTECTED] wrote:
On 3/4/07, Nikolay Samokhvalov [EMAIL PROTECTED] wrote:
I'll fix these issues and extend the patch with resgression tests and
docs for xpath_array(). I'll resubmit it very soon.
Here is a new version of the patch. I didn't change any part of docs yet.
Since there were no objections I've changed the name of the function
to xmlpath().
Updated version of the patch contains bugfix: there were a problem
with path queries that pointed to elements (cases when a set of
document parts that correspond to subtrees should be returned).
Example is (included in regression test):
xmltest=# SELECT xmlpath('//b', 'aone btwo/b three betc/b/a');
xmlpath
-
{btwo/b,betc/b}
(1 row)
Waiting for more feedback, please check it.
--
Best regards,
Nikolay
[ Attachment, skipping... ]
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
--
Bruce Momjian [EMAIL PROTECTED] http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 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] xpath_array with namespaces support
Andrew Dunstan wrote: Would it be better to use some more unlikely name for the dummy root element used to process fragments than x ? Why do we even need to support xpath on fragments? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] xml2 contrib patch supporting default XML namespaces
Bruce Momjian wrote: Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. I was hoping that we're deprecating contrib/xml2, so I wouldn't add more features to it. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] xml2 contrib patch supporting default XML namespaces
Peter Eisentraut wrote: Bruce Momjian wrote: Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. I was hoping that we're deprecating contrib/xml2, so I wouldn't add more features to it. Author states: I understand that XML support is planned and at least partially implemented for 8.3, but many production instances will be unable (or, in fact, unwilling) to upgrade to 8.3 for quite some time. Because this patch is completely backward compatible it can (theoretically) be included in future 8.1 and 8.2 releases, and for those of us that need more full XML support in the short term the upgrade of a contrib module is probably a very viable option -- it is for me, anyway. -- 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 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [PATCHES] xml2 contrib patch supporting default XML namespaces
Bruce Momjian wrote: Peter Eisentraut wrote: I was hoping that we're deprecating contrib/xml2, so I wouldn't add more features to it. Author states: I understand that XML support is planned and at least partially implemented for 8.3, but many production instances will be unable (or, in fact, unwilling) to upgrade to 8.3 for quite some time. Because this patch is completely backward compatible it can (theoretically) be included in future 8.1 and 8.2 releases, and for those of us that need more full XML support in the short term the upgrade of a contrib module is probably a very viable option -- it is for me, anyway. But we don't add new features to stable branches, even if they're backward compatible. Regards, Dave ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [PATCHES] xml2 contrib patch supporting default XML namespaces
Dave Page wrote: Bruce Momjian wrote: Peter Eisentraut wrote: I was hoping that we're deprecating contrib/xml2, so I wouldn't add more features to it. Author states: I understand that XML support is planned and at least partially implemented for 8.3, but many production instances will be unable (or, in fact, unwilling) to upgrade to 8.3 for quite some time. Because this patch is completely backward compatible it can (theoretically) be included in future 8.1 and 8.2 releases, and for those of us that need more full XML support in the short term the upgrade of a contrib module is probably a very viable option -- it is for me, anyway. But we don't add new features to stable branches, even if they're backward compatible. I was quoting the text only to state the author realizes /contrib/xml2 is depricated in 8.3. This is not going into 8.2.X, only 8.3. -- 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 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] xml2 contrib patch supporting default XML namespaces
Bruce Momjian [EMAIL PROTECTED] writes: Peter Eisentraut wrote: I was hoping that we're deprecating contrib/xml2, so I wouldn't add more features to it. Author states: I understand that XML support is planned and at least partially implemented for 8.3, but many production instances will be unable (or, in fact, unwilling) to upgrade to 8.3 for quite some time. Because this patch is completely backward compatible it can (theoretically) be included in future 8.1 and 8.2 releases, and for those of us that need more full XML support in the short term the upgrade of a contrib module is probably a very viable option -- it is for me, anyway. Well, it's not going to be put in future 8.1 or 8.2 releases, so the above argument is not a reason to include it now. What the author should do if he wants to offer a new feature for past release branches is to put up a project on pgfoundry. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] xml2 contrib patch supporting default XML namespaces
On 3/22/07, Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian [EMAIL PROTECTED] writes: Peter Eisentraut wrote: I was hoping that we're deprecating contrib/xml2, so I wouldn't add more features to it. Author states: I understand that XML support is planned and at least partially implemented for 8.3, but many production instances will be unable (or, in fact, unwilling) to upgrade to 8.3 for quite some time. Because this patch is completely backward compatible it can (theoretically) be included in future 8.1 and 8.2 releases, and for those of us that need more full XML support in the short term the upgrade of a contrib module is probably a very viable option -- it is for me, anyway. Well, it's not going to be put in future 8.1 or 8.2 releases, so the above argument is not a reason to include it now. What the author should do if he wants to offer a new feature for past release branches is to put up a project on pgfoundry. regards, tom lane Hmm.. OK. Well, thank you all for clarifying that. I thought (perhaps only hoped?) that the bar was lower for contrib than for core as far as features go, but it seems that assumption is incorrect. I'll look at starting a pgfoundry project soon. A related question, however: Will the XML features being included in 8.3 support namespace prefix registration? If not, handling arbitrary XML via XPath that includes unprefixed (default) namespaces (for me that is the majority of the XML I deal with, and no, I can't change that) will have exactly the same problems using the new mechanisms as with the current xml2 contrib module. I ask because, based on the design emails I've seen on -hackers, nothing surrounding explicit support for said issue jumped out at me. Thanks again. -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [PATCHES] xml2 contrib patch supporting default XML namespaces
Mike Rylander wrote: A related question, however: Will the XML features being included in 8.3 support namespace prefix registration? That is certainly the plan. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] xml2 contrib patch supporting default XML namespaces
Peter Eisentraut wrote: Mike Rylander wrote: A related question, however: Will the XML features being included in 8.3 support namespace prefix registration? That is certainly the plan. Let me bounce my ostrich (sp?) head up here and say, thanks for your work on this Peter. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: CREATE INDEX and HOT (was [HACKERS] Question: pg_classattributes and race conditions ?)
Jim C. Nasby wrote: On Mon, Mar 19, 2007 at 12:05:19PM +, Simon Riggs wrote: I was unwilling to compromise to have HOT if only one index existed, but IMHO allowing HOT with = 3 indexes is an acceptable compromise for this release. (We can always use vertical partitioning techniques to allow additional access paths to be added to the same table - I'd be very happy to document that with worked examples, if requried). I'm not sure where we're sitting with this, but I've got another idea I haven't seen (one that I think is better than an arbitrary limit on the number of indexes)... what if we just disallow non-concurrent index builds on hot tables? It sounds like the additional pain involved in chilling an entire table and keeping it chilled for the index build is even more overhead than just doing a concurrent index build. I thought about making it even simpler. Only allow CREATE INDEX builds on non HOT tables. However as I mentioned in another thread, this idea dies if you expect to be able to have HOT enabled by default in any future release. Chilling needs to be able to be done with a regular Vacuum style lock for chilling to be a usable reality. I'm sure there are use cases or this, but it seems unlikely that a high update table is going to have an index added to it. Am I a long way from reality when saying that? Regards Russell Smith ---(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] CREATE INDEX and HOT - revised design
Simon Riggs wrote: On Thu, 2007-03-22 at 16:16 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: There is a slight hole in that SERIALIZABLE transactions won't be able to use any indexes they build during their transaction, since they may need to be able to see prior data, but I don't think anybody is going to complain about that restriction. Anyone? Practically every statement I've seen in this thread that used the phrase SERIALIZABLE transaction was wrong to some extent, and this one is no different. The issue is not whether the whole transaction is serializable or not, it's how old is the oldest still-live snapshot, a thing that CREATE INDEX can't tell with any certainty in READ COMMITTED mode. So if your solution involves any explicit dependence on the transaction serializability mode, it's probably wrong. I'm not totally sure if you are expecting to be able to tell that, but I do know that the planner has no idea what snapshots a plan it makes will be used with. Thanks for correcting me. Reworded: There is a slight hole in that snapshots older than the CREATE INDEX must never be allowed to use the index. That means that SERIALIZABLE transactions and some other situations will need to be restricted. Personally, I would argue that such a restriction was an acceptable loss of functionality, since I can't think of a situation where such a thing would need to occur, though one may turn up. Currently, I don't know how to prevent this from happening. We'll need to examine this in more detail to see if there is a way. I have seen and used transactions that create indexes inside a transaction, use them for the life of the transaction, and then drop them at the end. I think this is an acceptable solution to not be able to use the index in the current transaction if the table you are building the index on is HOT enabled. That way it is not really a functionality loss, it's just a restriction put in place if you are using a certain feature. We do not want to go breaking existing code. However HOT is enabled by default on tables, then we have a different situation. And if the expectation is that HOT will be enabled by default in future releases, then this needs to be considered now. Regards Russell Smith ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] LIKE optimization in UTF-8 and locale-C
Hannu Krosing [EMAIL PROTECTED] wrote: We've had an optimization for single-byte encodings using pg_database_encoding_max_length() == 1 test. I'll propose to extend it in UTF-8 with locale-C case. If this works for UTF8, won't it work for all the backend-legal encodings? I guess it works well for % but not for _ , the latter has to know, how many bytes the current (multibyte) character covers. Yes, % is not used in trailing bytes for all encodings, but _ is used in some of them. I think we can use the optimization for all of the server encodings except JOHAB. Also, I took notice that locale settings are not used in LIKE matching, so the following is enough for checking availability of byte-wise matching functions. or am I missing something? #define sb_match_available()(GetDatabaseEncoding() == PG_JOHAB)) Multi-byte encodings supported by a server encoding. | % 0x25 | _ 0x5f | \ 0x5c | --++++- EUC_JP| unused | unused | unused | EUC_CN| unused | unused | unused | EUC_KR| unused | unused | unused | EUC_TW| unused | unused | unused | JOHAB | unused | *used* | *used* | UTF8 | unused | unused | unused | MULE_INTERNAL | unused | unused | unused | Just for reference, encodings only supported as a client encoding. | % 0x25 | _ 0x5f | \ 0x5c | --++++- SJIS | unused | *used* | *used* | BIG5 | unused | *used* | *used* | GBK | unused | *used* | *used* | UHC | unused | unused | unused | GB18030 | unused | *used* | *used* | Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] CREATE INDEX and HOT - revised design
On 3/23/07, Simon Riggs [EMAIL PROTECTED] wrote: The ShareLock taken by CREATE INDEX guarantees all transactions that wrote data to the table have completed and that no new data can be added until after the index build commits. So the end of the chain is visible to CREATE INDEX and won't change. As long as you index the latest committed version on each HOT chain, then I think it works. Its slightly different for the HOT-chains created by this transaction which is creating the index. We should index the latest version of the row which is not yet committed. But thats ok because when CREATE INDEX commits this latest version would also get committed. Sounds like you'll need to store the Next TransactionId rather than the TransactionId of the CREATE INDEX. Yes, I agree. There is a slight hole in that SERIALIZABLE transactions won't be able to use any indexes they build during their transaction, since they may need to be able to see prior data, but I don't think anybody is going to complain about that restriction. Anyone? Oh, I did not see that. If there are no HOT-chains in the table, we can set the xid to InvalidTransactionId so that the index is usable immediately after it is created in the current transaction, as well as those transactions which were started before CREATE INDEX. We can possibly further improve it by checking if there are no HOT-chains except those created by this transaction and set xid to InvalidTransactionId. IMO with that we shall address most of the use cases. There are few which might still get impacted, but even for them there won't be any correctness problem. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] LIKE optimization in UTF-8 and locale-C
Dennis Bjorklund [EMAIL PROTECTED] wrote:
The problem with the like pattern _ is that it has to know how long the
single caracter is that it should pass over. Say you have a UTF-8 string
with 2 characters encoded in 3 bytes ('ÖA'). Where the first character
is 2 bytes:
0xC3 0x96 'A'
and now you want to match that with the LIKE pattern:
'_A'
Thanks, it all made sense to me. My proposal was completely wrong.
The optimization of MBMatchText() seems to be the right way...
Maybe one should simply write a special version of LIKE for the UTF-8
encoding since it's probably the most used encoding today. But I don't
think you can use the C locale and that it would work for UTF-8.
But then, present LIKE matching is not locale aware. we treat multi-byte
characters properly, but always perform a char-by-char comparison.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] LIKE optimization in UTF-8 and locale-C
ITAGAKI Takahiro skrev:
I guess it works well for % but not for _ , the latter has to know, how
many bytes the current (multibyte) character covers.
Yes, % is not used in trailing bytes for all encodings, but _ is
used in some of them. I think we can use the optimization for all
of the server encodings except JOHAB.
The problem with the like pattern _ is that it has to know how long the
single caracter is that it should pass over. Say you have a UTF-8 string
with 2 characters encoded in 3 bytes ('ÖA'). Where the first character
is 2 bytes:
0xC3 0x96 'A'
and now you want to match that with the LIKE pattern:
'_A'
How would that work in the C locale?
Maybe one should simply write a special version of LIKE for the UTF-8
encoding since it's probably the most used encoding today. But I don't
think you can use the C locale and that it would work for UTF-8.
/Dennis
---(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] LIKE optimization in UTF-8 and locale-C
On 2007-03-22, Tom Lane [EMAIL PROTECTED] wrote: ITAGAKI Takahiro [EMAIL PROTECTED] writes: I found LIKE operators are slower on multi-byte encoding databases than single-byte encoding ones. It comes from difference between MatchText() and MBMatchText(). We've had an optimization for single-byte encodings using pg_database_encoding_max_length() == 1 test. I'll propose to extend it in UTF-8 with locale-C case. If this works for UTF8, won't it work for all the backend-legal encodings? It works for UTF8 only because UTF8 has special properties which are not shared by, for example, EUC_*. Specifically, in UTF8 the octet sequence for a multibyte character will never appear as a subsequence of the octet sequence of a string of other multibyte characters. i.e. given a string of two two-octet characters AB, the second octet of A plus the first octet of B is not a valid UTF8 character (and likewise for longer characters). (And while I haven't tested it, it looks like the patch posted doesn't account properly for the use of _, so it needs a bit more work.) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] LIKE optimization in UTF-8 and locale-C
On 2007-03-23, ITAGAKI Takahiro [EMAIL PROTECTED] wrote: Thanks, it all made sense to me. My proposal was completely wrong. Actually, I think your proposal is fundamentally correct, merely incomplete. Doing octet-based rather than character-based matching of strings is a _design goal_ of UTF8. Treating UTF8 like any other multibyte charset and converting everything to wide-chars is, in my opinion, always going to result in suboptimal performance. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(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
