Re: [HACKERS] scheduler in core
Joshua D. Drake j...@commandprompt.com writes: On Sat, 2010-02-20 at 18:19 -0500, Tom Lane wrote: Dimitri Fontaine dfonta...@hi-media.com writes: What would it take to have it included in core, I don't think this really makes sense. There's basically no argument for having it in core other than I'm too lazy to install a separate package. Unlike the case for autovacuum, there isn't anything an in-core implementation could do that an external one doesn't do as well or better. So I'm not eager to take on additional maintenance burden for such a thing. There is zero technical reason for this to be in core. That doesn't mean it isn't a really good idea. It would be nice to have a comprehensive job scheduling solution that allows me to continue abstract away from external solutions and operating system dependencies. Maybe what we need, on the technical level, is a way to distribute this code with the main product but without draining too much effort from core members there. Like we do with contribs I guess, but on a larger scale. I guess git submodules, PGAN, extensions and all that jazz are going to help. Meanwhile I'll have to learn enough of pgAgent to figure out how much it's tied to pgadmin, and we'll have to make those other facilities something real. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Greg Stark gsst...@mit.edu writes: It'll always be another (set of) processes even if it's in core. All it means to be in core is that it will be harder to make modifications and you'll be tied to the Postgres release cycle. Another set of processes all right, but that postmaster is responsible of, that it starts and ends at the right time. Main advantage over cron or another scheduler being that it'd be part of my transactional backups, of course. All you need for that is to store the schedule in a database table. This has nothing to do with where the scheduler code lives. Not true. You need custom scripts that will read what's in this database table and run it at the right timing, care about running more than one job at the same time when necessary, reports what the outcome was somewhere, etc. The simplest would be a query that writes out in cron format the setup you've made in the database, I suppose. When do you run that query? You need an untrusted trigger? What happens if your query or script writes a file cron will not be able to read, or on a server where cron is not running? I'm not saying this is any harder that other admin sys stuff we have to do to operate the systems, just that it seems it would be simpler, easier and less error prone to be able to schedule database maintenance from within the database itself, in such a way that the classic dump and restore process restores the maintenance scripts too. That would allow for automatic creation of partitions in dev and pre-prod environments where you install more than one copy of the same database at once, but would like to avoid maintaining one set of cron entries per copy. As said Tom, technically, it's obviously possible not to depend on a PostgreSQL integrated scheduler. As said JD, it still is a pretty good idea to provide one in core. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq PGresult object and encoding
Jeff Davis wrote: libpq has a PQclientEncoding() function that takes a connection object. However, the client encoding is, in some cases, a property of the result object. For instance, if your client_encoding changes, but you keep the result object around, you have no way to determine later what encoding the results are in. The result object already saves the client_encoding. Should an accessor be provided? That'd certainly save libpqxx the trouble of lugging a copy around. Jeroen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
Greg Stark wrote: So in principle I agree with this idea. I think a conservative value for the constant would be more like 100x though. If I told you we had an easy way to speed all your queries up by 10% by caching queries but were just choosing not to then I think you would be unhappy. Whereas if I told you we were spending 1% of the run-time planning queries I think most people would not be concerned. Makes sense. The main thing is that there be an order-of-magnitude difference to hide the potential extra planning cost in. If that includes a switched SSL connection, 10% of execution is probably reasonable because it's a much smaller portion of overall response time--but on a local connection it's a liability. There's a second problem though. We don't actually know how long any given query is going to take to plan or execute. We could just remember how long it took to plan and execute last time or how long it took to plan last time and the average execution time since we cached that plan. Perhaps we should track the stddev of the execution plan, or the max execution time of the plan? Ie there are still unanswered questions about the precise heuristic to use but I bet we can come up with something reasonable. I may have cut this out of my original email for brevity... my impression is that the planner's estimate is likely to err on the side of scalability, not best-case response time; and that this is more likely to happen than an optimistic plan going bad at runtime. If that is true, then the cost estimate is at least a useful predictor of statements that deserve re-planning. If it's not true (or for cases where it's not true), actual execution time would be a useful back-up at the cost of an occasional slow execution. Yeb points out a devil in the details though: the cost estimate is unitless. We'd have to have some orders-of-magnitude notion of how the estimates fit into the picture of real performance. Jeroen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] parallelizing subplan execution
Robert Haas robertmh...@gmail.com writes: Probably. For one thing, you can't use fork(), because it won't work on Windows. [...] query. IOW, we're going to need, well, a connection pool in core. *ducks, runs for cover* Well, in fact, you're slowly getting to the interesting^W crazy part of it. Now that you have a connection pool in core and a way to share the same snapshot in more than one backend, won't you like for any HotStandby slave to be able to share this snapshot too? And run the subplan there? And while at it, you'd obviously (ahem) want the slave to run the pooler too and have the possibility to ask its master if it still have a given snapshot available. So that any transaction (session?) that turns out not to be read-only can get transparently run on the master instead. So the snapshot too old error get some more reasons to be. Oh, of course, the next step after that is to have a single cluster be both a slave and a master, so that we can talk about distributing the data. Multi-nodes multi-TB (make it PB) is the future, didn't they say? We now have nodes with only some of the data (that could be only some partitions) and a way to give them subplans over the network, and a way for them to run a write query on other hosts without telling the client connection. Sounds fun, he? Regards, -- dim And I don't do drugs, not even caffeine. :) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Plans for 9.1, Grouping Sets, disabling multiqueries, contrib module for string, plpgpsm, preload dictionaries
Hello, * Now I am working on migration of plpgpsm to plpgsql 9.0 base. I hope so I understand SQL/PSM well so I am able to write production quality implementation. If you like, I can integrate it to core. It can share about 40-50% code with plpgpsm. The behave of plpgpsm is same as plpgsql - without some plpgsql's historical issues (about FOUND, about NULL and record type). SQL/PSM is litlle bit richer language. Now we have not any wide used runtime so I don't thinking about rewriting. Maybe we can rewrite these PL language for parrot or lua runtime in future. But this step isn't necessary - people hasn't performance problems with PL based on PL runtime. * Month ago there was discussion about integration sprintf function to core. I don't think it is good idea. sprintf implementation isn't simple. I prefer more simple format function based on RAISE NOTICE ... behave and code. It is significantly shorter and simpler. But for people who like sprintf we can prepare some contrib module with basic string functions - sprintf, left, right, reverse. This module can be based on cleaned pstcollection - http://pgfoundry.org/frs/download.php/2556/pstcoll-100127.tar.gz. * Last two months I spent some time with preparing workshops about SQL injection. PostgreSQL has only one issue related to this topic. It allows multi queries. With this feature any successful injection can have much more destructive impact. Now we have a GUC per user. I know, we cannot break multiqueries without breaking basic functionality. But we can break multiple queries on top level for some selected users - (web application roles). Then we are able to configure database for secure web access. It isn't protection against SQL injection. But it can down up possible risk about successful SQL injection. + downgrade rights on system tables for web application roles. * I would to like solve problem with fulltext reported by Czech users. I will try implement preloaded TSearch dictionaries. * Still I thinking about GROUPING SETS feature. Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] getting to beta
Now that PostgreSQL 9.0 alpha4 is bundled (though apparently not quite out the door yet), it seems like a good time to think about what we'll need to do to get to beta. Any thoughts? http://wiki.postgresql.org/wiki/PostgreSQL_8.5_Open_Items currently lists no open items, and the Hot Standby TODO page to which it links also lists no critical open items. The Streaming Replication TODO to which it links lists a LOT of open items. My suspicion is that the real situation is more complicated than the above picture. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
On Wed, Feb 17, 2010 at 5:52 PM, Jeroen Vermeulen j...@xs4all.nl wrote: I may have cut this out of my original email for brevity... my impression is that the planner's estimate is likely to err on the side of scalability, not best-case response time; and that this is more likely to happen than an optimistic plan going bad at runtime. Interestingly, most of the mistakes that I have seen are in the opposite direction. Yeb points out a devil in the details though: the cost estimate is unitless. We'd have to have some orders-of-magnitude notion of how the estimates fit into the picture of real performance. I'm not sure to what extent you can assume that the cost is proportional to the execution time. I seem to remember someone (Peter?) arguing that they're not related by any fixed ratio, partly because things like page costs vs. cpu costs didn't match physical reality, and that in fact some attempts to gather better empirically better values for things like random_page_cost and seq_page_cost actually ended up making the plans worse rather than better. It would be nice to see some research in this area... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] parallelizing subplan execution (was: explain and PARAM_EXEC)
On Sun, Feb 21, 2010 at 3:25 AM, Robert Haas robertmh...@gmail.com wrote: What kinds of things would be sensible to hand off in this way? Well, you'd want to find nodes that are not likely to be repeatedly re-executed with different parameters, like subplans or inner-indexscans, because otherwise you'll get pipeline stalls handing the new parameters back and forth. And you want to find nodes that are expensive for the same reason. I think the case you want to handle is when you could execute a node asynchronously. That is, if the rest of the plan can proceed without the results until they are are ready. The case that Oracle handled first and best was UNION ALL where each child can be run in separate processes. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Pavel Stehule wrote: This reasoning just doesn't fly in the PostgreSQL world. PostgreSQL is designed to be extensible, not a monolithic product. We're not going to change that because some companies have insane corporate policies. The answer, as Jefferson said in another context, is to inform their ignorance. That isn't to say that there isn't a case for an in core scheduler, but this at least isn't a good reason for it. What I remember - this is exactly same discus like was about replication thre years ago fiirst strategy - we doesn't need it in core next we was last with replacation That's a pretty poor analogy IMNSHO. There are very good technical reasons to have replication in the core. That is much less clear for a scheduler. But in any case, I didn't say that we shouldn't have a scheduler. I specifically said there might be a case for it - read the first clause of my last sentence. What I said was that the reason given, namely that Corporations didn't want to use add-on modules, was not a good reason. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Plans for 9.1, Grouping Sets, disabling multiqueries, contrib module for string, plpgpsm, preload dictionaries
Pavel Stehule pavel.steh...@gmail.com writes: * Last two months I spent some time with preparing workshops about SQL injection. PostgreSQL has only one issue related to this topic. It allows multi queries. With this feature any successful injection can have much more destructive impact. Now we have a GUC per user. I know, we cannot break multiqueries without breaking basic functionality. But we can break multiple queries on top level for some selected users - (web application roles). Then we are able to configure database for secure web access. It isn't protection against SQL injection. This seems like a waste of effort. It is already the case that multi queries are forbidden when submitting through the extended query protocol. All that an app has to do is not use simple protocol --- which, if it's trying to be secure, it's already not using because it needs out-of-line parameters. There's no need for yet another GUC. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
Robert Haas robertmh...@gmail.com writes: http://wiki.postgresql.org/wiki/PostgreSQL_8.5_Open_Items currently lists no open items, um ... are we looking at the same page? I see 8 open items there, not counting the links to the separate HS and SR pages. My suspicion is that the real situation is more complicated than the above picture. I believe Bruce is in process of going through his mailbox to find stuff that slipped through the cracks. That will probably result in a longer list... regards, tom lane PS: can we rename that page to 9.0 open items? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Pavel Stehule wrote: 2010/2/21 Andrew Dunstan and...@dunslane.net: ? ?I believe that in core may be installed by default in case of ? ?the pgAgent or similar solution... ? ?Many big companies does not allow the developers to configure and ? ?install components we need to request everthing in 10 copies ? ?of forms... ? ?By making it in core or installed by default means that we ? ?have more chance that the db scheduler would be widely accepted... This reasoning just doesn't fly in the PostgreSQL world. PostgreSQL is designed to be extensible, not a monolithic product. We're not going to change that because some companies have insane corporate policies. ?The answer, as Jefferson said in another context, is to inform their ignorance. That isn't to say that there isn't a case for an in core scheduler, but this at least isn't a good reason for it. What I remember - this is exactly same discus like was about replication thre years ago fiirst strategy - we doesn't need it in core next we was last with replacation We resisted putting replication into the core until we needed some facilities that were only available from the core. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: http://wiki.postgresql.org/wiki/PostgreSQL_8.5_Open_Items currently lists no open items, um ... are we looking at the same page? I see 8 open items there, not counting the links to the separate HS and SR pages. My suspicion is that the real situation is more complicated than the above picture. I believe Bruce is in process of going through his mailbox to find stuff that slipped through the cracks. That will probably result in a longer list... I am. I am trying to close as many as I can, and sending emails about the rest. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
2010/2/20 Andrew Dunstan and...@dunslane.net We're not going to change that because some companies have insane corporate policies. I agree, Andrew... This is an outside benefit... not a reason or justification... I believe that a general purpose scheduler is similar to the autovacuum... it is not really needed, we can always configure an external scheduler. But I liked a LOT... For me is not a question of must be in core is a question of cost/benefit. I do not see much cost, but a lot of benefits: Like Joshua said abstract away from external solutions and operating system dependencies. Like Dimitri said Main advantage over cron or another scheduler being that it'd be part of my transactional backups. To me is the reliability of having the partition creation/removal being part of the database, be able of make consolidations, cleanups and periodic consistency checks and diagnostics without external dependencies. I wonder if the scheduler already existed before the implementation of the autovacuum, its implementation would not be a function executed by the in-core scheduler? - - Lucas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Lucas wrote: I believe that in core may be installed by default in case of Those seem like totally orthogonal concepts to me. A feature may be in core but not installed by default (like many PLs). A feature might not be in core but installed by many installers (say postgis). It seems like half the people here are arguing for the former concept. It seems the other half are arguing against the latter concept. Is the real need here for a convenient way to enable and/or recommend packagers to install some non-core modules by default? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
Robert Haas robertmh...@gmail.com writes: On Feb 20, 2010, at 10:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: There is a very clear set of behaviors that CORL ought to have given the precedents of our other COR commands. If we don't make it do things that way then we are going to surprise users, and we are also going to paint ourselves into a corner because we won't be able to fix it later without creating compatibility gotchas. Exactly. I agree completely. Attached is a draft patch (no doc changes) that implements CREATE OR REPLACE LANGUAGE following the semantics used in CREATE OR REPLACE FUNCTION, namely that in addition to whatever privileges you need to do the CREATE, you need to be owner of the existing entry if any; and the recorded ownership and permissions don't change. It's not bad at all --- net addition of 40 lines. So if we want to go at it this way, it's certainly feasible. I've got mixed feelings about the ownership check. If you get past the normal CREATE LANGUAGE permission checks, then either you are superuser, or you are database owner and you are trying to recreate a language from a pg_pltemplate entry with tmpldbacreate true. So it would fail only for a database owner who's trying to do C.O.R.L. on a superuser-installed language. Which arguably is a case we ought to allow. On the other hand, the case where not throwing an error would really matter is in trying to do pg_restore --single, and in that case even if we allowed the C.O.R.L. it would still spit up on the ALTER LANGUAGE OWNER that pg_dump is presumably going to emit right afterwards (except if using --no-owner, I guess). So I'm not sure we'd really be gaining much by omitting the ownership check, and it would certainly be less consistent with other C.O.R. commands if we don't apply such a check. Comments? regards, tom lane Index: src/backend/commands/proclang.c === RCS file: /cvsroot/pgsql/src/backend/commands/proclang.c,v retrieving revision 1.89 diff -c -r1.89 proclang.c *** src/backend/commands/proclang.c 14 Feb 2010 18:42:14 - 1.89 --- src/backend/commands/proclang.c 21 Feb 2010 17:08:15 - *** *** 17,23 #include access/heapam.h #include catalog/dependency.h #include catalog/indexing.h - #include catalog/pg_authid.h #include catalog/pg_language.h #include catalog/pg_namespace.h #include catalog/pg_pltemplate.h --- 17,22 *** *** 49,55 char *tmpllibrary; /* path of shared library */ } PLTemplate; ! static void create_proc_lang(const char *languageName, Oid languageOwner, Oid handlerOid, Oid inlineOid, Oid valOid, bool trusted); static PLTemplate *find_language_template(const char *languageName); --- 48,54 char *tmpllibrary; /* path of shared library */ } PLTemplate; ! static void create_proc_lang(const char *languageName, bool replace, Oid languageOwner, Oid handlerOid, Oid inlineOid, Oid valOid, bool trusted); static PLTemplate *find_language_template(const char *languageName); *** *** 73,88 Oid funcargtypes[1]; /* ! * Translate the language name and check that this language doesn't ! * already exist */ languageName = case_translate_language_name(stmt-plname); - if (SearchSysCacheExists1(LANGNAME, PointerGetDatum(languageName))) - ereport(ERROR, - (errcode(ERRCODE_DUPLICATE_OBJECT), - errmsg(language \%s\ already exists, languageName))); - /* * If we have template information for the language, ignore the supplied * parameters (if any) and use the template information. --- 72,81 Oid funcargtypes[1]; /* ! * Translate the language name to lower case */ languageName = case_translate_language_name(stmt-plname); /* * If we have template information for the language, ignore the supplied * parameters (if any) and use the template information. *** *** 232,238 valOid = InvalidOid; /* ok, create it */ ! create_proc_lang(languageName, GetUserId(), handlerOid, inlineOid, valOid, pltemplate-tmpltrusted); } else --- 225,232 valOid = InvalidOid; /* ok, create it */ ! create_proc_lang(languageName, stmt-replace, GetUserId(), ! handlerOid, inlineOid, valOid, pltemplate-tmpltrusted); } else *** *** 306,312 valOid = InvalidOid; /* ok, create it */ ! create_proc_lang(languageName, GetUserId(), handlerOid, inlineOid, valOid, stmt-pltrusted); } } --- 300,307 valOid = InvalidOid; /* ok, create it */ ! create_proc_lang(languageName, stmt-replace, GetUserId(), ! handlerOid, inlineOid, valOid, stmt-pltrusted); } } *** *** 315,321 * Guts of language creation. */ static void ! create_proc_lang(const char *languageName, Oid
Re: [HACKERS] scheduler in core
Ron Mayer rm...@cheapcomplexdevices.com writes: Is the real need here for a convenient way to enable and/or recommend packagers to install some non-core modules by default? It would certainly help us resist assorted requests to put everything including the kitchen sink into core. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] some issue in plpgsq - exec code?
Hello I am looking on code in pl_exec.c file. I see one issue: /* -- * exec_run_select Execute a select query * -- */ static int exec_run_select(PLpgSQL_execstate *estate, PLpgSQL_expr *expr, long maxtuples, Portal *portalP) { ParamListInfo paramLI; int rc; /* * On the first call for this expression generate the plan */ if (expr-plan == NULL) exec_prepare_plan(estate, expr, 0); rc = SPI_execute_plan_with_paramlist(expr-plan, paramLI, estate-readonly_func, maxtuples); if (rc != SPI_OK_SELECT) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg(query \%s\ is not a SELECT, expr-query))); } the test of rc is too restrict. I think so rc could be one from: SPI_OK_SELECT, SPI_OK_INSERT_RETURNING:, SPI_OK_UPDATE_RETURNING:, SPI_OK_DELETE_RETURNING - and errmsg query %s doesn't return a table with this change we can reuse this rutine and maybe little bit compress code. Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
On Sun, Feb 21, 2010 at 9:26 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: http://wiki.postgresql.org/wiki/PostgreSQL_8.5_Open_Items currently lists no open items, um ... are we looking at the same page? I see 8 open items there, not counting the links to the separate HS and SR pages. I'm sorry, I left a word out: I meant to say that it lists no *major* open items. There are certainly some things listed there, but nothing to get excited about, at least not AFAICS. My suspicion is that the real situation is more complicated than the above picture. I believe Bruce is in process of going through his mailbox to find stuff that slipped through the cracks. That will probably result in a longer list... regards, tom lane PS: can we rename that page to 9.0 open items? I leave that to someone with superior wiki-fu. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sun, Feb 21, 2010 at 12:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ron Mayer rm...@cheapcomplexdevices.com writes: Is the real need here for a convenient way to enable and/or recommend packagers to install some non-core modules by default? It would certainly help us resist assorted requests to put everything including the kitchen sink into core. If you don't want people to keep requesting more features in core, you should stop doing such a good job making the functionality that gets put into core awesome. That's partly tongue-in-cheek, but there's some real truth to it. Stuff doesn't go into core unless it just works. And having things in core is appealing because it means they're available everywhere, they work the same way everywhere, and they can be fully managed within the database without a lot of futzing around. Having an extensible system is a good thing and I'm glad we do, but having a rich feature set available in core is also a very good thing for a lot of reasons, at least IMHO. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WAL-support for Pluggable Indexes
We've just rejected Knn-gist indexes as not enough time for 9.0, which is a considerable disappointment for many people. We already have a pluggable index API, but not one that supports recoverability. It is a simple patch to add recoverability to the index API, if we have the will to do so. Let's add this into 9.0 now and let index development flourish without the need for integration with core. PostgreSQL will benefit from having index types grow alongside it. There will at times be additional changes in core to optimise certain index use cases, that can come later. Let's allow Postgres to be what it was always intended to be: extensible for real world applications. The must-have list of requirements are: * must be possible to test whether rmgrid is set before allowing XLogInsert() * must allow normal rmgr APIs as well as index AM API Not looking for the ability to redefine existing rmgrs, just ability to add new ones. I'm looking for agreement to proceed now and some help from those with an interest. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
Robert Haas wrote: On Sun, Feb 21, 2010 at 9:26 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: http://wiki.postgresql.org/wiki/PostgreSQL_8.5_Open_Items currently lists no open items, um ... are we looking at the same page? I see 8 open items there, not counting the links to the separate HS and SR pages. I'm sorry, I left a word out: I meant to say that it lists no *major* open items. There are certainly some things listed there, but nothing to get excited about, at least not AFAICS. My suspicion is that the real situation is more complicated than the above picture. I believe Bruce is in process of going through his mailbox to find stuff that slipped through the cracks. That will probably result in a longer list... regards, tom lane PS: can we rename that page to 9.0 open items? I leave that to someone with superior wiki-fu. done Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sun, Feb 21, 2010 at 10:17 AM, Lucas luca...@gmail.com wrote: I wonder if the scheduler already existed before the implementation of the autovacuum, its implementation would not be a function executed by the in-core scheduler? The real genius of autovacuum is that it works out when there has been enough activity in particular tables that they need to be vacuumed. We might be able to use an in-core scheduler to wake it up every minute to look at the stats, or whatever it is that we do, but that's not all that exciting. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Robert Haas robertmh...@gmail.com writes: On Sun, Feb 21, 2010 at 10:17 AM, Lucas luca...@gmail.com wrote: I wonder if the scheduler already existed before the implementation of the autovacuum, its implementation would not be a function executed by the in-core scheduler? The real genius of autovacuum is that it works out when there has been enough activity in particular tables that they need to be vacuumed. We might be able to use an in-core scheduler to wake it up every minute to look at the stats, or whatever it is that we do, but that's not all that exciting. The wake-up-every-N-seconds part of it is actually the weakest part (search the archives for questions about autovacuum_naptime). To my mind, the killer reason why autovac needed to be integrated is so that the system itself could trigger autovac runs in response to threatened XID wraparound conditions. A facility for scheduling user jobs, almost by definition, won't have any system-internal trigger conditions. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Sun, Feb 21, 2010 at 12:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Feb 20, 2010, at 10:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: There is a very clear set of behaviors that CORL ought to have given the precedents of our other COR commands. If we don't make it do things that way then we are going to surprise users, and we are also going to paint ourselves into a corner because we won't be able to fix it later without creating compatibility gotchas. Exactly. I agree completely. Attached is a draft patch (no doc changes) that implements CREATE OR REPLACE LANGUAGE following the semantics used in CREATE OR REPLACE FUNCTION, namely that in addition to whatever privileges you need to do the CREATE, you need to be owner of the existing entry if any; and the recorded ownership and permissions don't change. It's not bad at all --- net addition of 40 lines. So if we want to go at it this way, it's certainly feasible. I've got mixed feelings about the ownership check. If you get past the normal CREATE LANGUAGE permission checks, then either you are superuser, or you are database owner and you are trying to recreate a language from a pg_pltemplate entry with tmpldbacreate true. So it would fail only for a database owner who's trying to do C.O.R.L. on a superuser-installed language. Which arguably is a case we ought to allow. On the other hand, the case where not throwing an error would really matter is in trying to do pg_restore --single, and in that case even if we allowed the C.O.R.L. it would still spit up on the ALTER LANGUAGE OWNER that pg_dump is presumably going to emit right afterwards (except if using --no-owner, I guess). So I'm not sure we'd really be gaining much by omitting the ownership check, and it would certainly be less consistent with other C.O.R. commands if we don't apply such a check. Comments? Well, I'm a big fan of CREATE OR REPLACE anything so I like the patch regardless of whether it solves the current problem, but having said that, I'm not clear on whether it does in fact solve the current problem. When PL/pgsql is installed by default, is it going to end up owned by the DB owner, or might it end up owned by the superuser? If you end up applying this you might take the to fix up the gram.y comment a little more thoroughly: CREATE [OR REPLACE] [TRUSTED] [PROCEDURAL] LANGUAGE; DROP [PROCEDURAL] LANGUAGE. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sat, 2010-02-20 at 18:19 -0500, Tom Lane wrote: Dimitri Fontaine dfonta...@hi-media.com writes: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, I don't think this really makes sense. There's basically no argument for having it in core other than I'm too lazy to install a separate package. Unlike the case for autovacuum, there isn't anything an in-core implementation could do that an external one doesn't do as well or better. So I'm not eager to take on additional maintenance burden for such a thing. There is currently no way to run a separate daemon process that runs user code as part of Postgres, so that the startup code gets run immediately we startup, re-run if we crash and shut down cleanly when the server does. If there were some way to run arbitrary code in a daemon using an extensibility API then we wouldn't ever get any requests for the scheduler, cos you could write it yourself without troubling anybody here. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sun, Feb 21, 2010 at 1:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Feb 21, 2010 at 10:17 AM, Lucas luca...@gmail.com wrote: I wonder if the scheduler already existed before the implementation of the autovacuum, its implementation would not be a function executed by the in-core scheduler? The real genius of autovacuum is that it works out when there has been enough activity in particular tables that they need to be vacuumed. We might be able to use an in-core scheduler to wake it up every minute to look at the stats, or whatever it is that we do, but that's not all that exciting. The wake-up-every-N-seconds part of it is actually the weakest part (search the archives for questions about autovacuum_naptime). To my mind, the killer reason why autovac needed to be integrated is so that the system itself could trigger autovac runs in response to threatened XID wraparound conditions. A facility for scheduling user jobs, almost by definition, won't have any system-internal trigger conditions. Right. Without prejudice to my earlier statements that I think this might possibly be a good thing to do anyway, the case for it would be a lot stronger if it provided some genuine additional functionality. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sun, Feb 21, 2010 at 1:11 PM, Simon Riggs si...@2ndquadrant.com wrote: On Sat, 2010-02-20 at 18:19 -0500, Tom Lane wrote: Dimitri Fontaine dfonta...@hi-media.com writes: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, I don't think this really makes sense. There's basically no argument for having it in core other than I'm too lazy to install a separate package. Unlike the case for autovacuum, there isn't anything an in-core implementation could do that an external one doesn't do as well or better. So I'm not eager to take on additional maintenance burden for such a thing. There is currently no way to run a separate daemon process that runs user code as part of Postgres, so that the startup code gets run immediately we startup, re-run if we crash and shut down cleanly when the server does. Good point. If there were some way to run arbitrary code in a daemon using an extensibility API then we wouldn't ever get any requests for the scheduler, cos you could write it yourself without troubling anybody here. That might be a little overly optimistic, but I get the point. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL-support for Pluggable Indexes
Simon Riggs si...@2ndquadrant.com writes: We already have a pluggable index API, but not one that supports recoverability. It is a simple patch to add recoverability to the index API, if we have the will to do so. I suggest you go re-read the archives before asserting this is a simple no-thought-required fix. If it were, it'd have been done before. The killer problem as I recall it is how to identify the plugin rmgrs to use, bearing in mind that you can't rely on looking at the catalogs. We don't have a design for that, and I don't want one that's been thrown together under intense schedule pressure. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL-support for Pluggable Indexes
On Sun, Feb 21, 2010 at 12:54 PM, Simon Riggs si...@2ndquadrant.com wrote: We've just rejected Knn-gist indexes as not enough time for 9.0, which is a considerable disappointment for many people. We already have a pluggable index API, but not one that supports recoverability. It is a simple patch to add recoverability to the index API, if we have the will to do so. Let's add this into 9.0 now and let index development flourish without the need for integration with core. PostgreSQL will benefit from having index types grow alongside it. There will at times be additional changes in core to optimise certain index use cases, that can come later. Let's allow Postgres to be what it was always intended to be: extensible for real world applications. The must-have list of requirements are: * must be possible to test whether rmgrid is set before allowing XLogInsert() * must allow normal rmgr APIs as well as index AM API Not looking for the ability to redefine existing rmgrs, just ability to add new ones. I'm looking for agreement to proceed now and some help from those with an interest. I am also disappointed that knngist didn't make it into 9.0, but it seems somewhat orthogonal to the issue you're raising here. Knngist can't exist outside of core because it requires planner support and changes to the opclass machinery; so even if we did this, it wouldn't actually benefit the proposed use case. That doesn't mean this is a bad idea, of course, just that it doesn't solve that particular problem. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
Robert Haas robertmh...@gmail.com writes: Well, I'm a big fan of CREATE OR REPLACE anything so I like the patch regardless of whether it solves the current problem, but having said that, I'm not clear on whether it does in fact solve the current problem. When PL/pgsql is installed by default, is it going to end up owned by the DB owner, or might it end up owned by the superuser? It will be owned by the bootstrap superuser, so the case is exactly the one that a non-superuser DBA would be faced with. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Sun, Feb 21, 2010 at 1:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Well, I'm a big fan of CREATE OR REPLACE anything so I like the patch regardless of whether it solves the current problem, but having said that, I'm not clear on whether it does in fact solve the current problem. When PL/pgsql is installed by default, is it going to end up owned by the DB owner, or might it end up owned by the superuser? It will be owned by the bootstrap superuser, so the case is exactly the one that a non-superuser DBA would be faced with. Or even a superuser other than the bootstrap superuser, no? I dump out the DB on my 8.4 server and try to reload on 9.0 with --single and it fails because, even though I'm a superuser, I can't replace a language owned by someone else? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] A thought on Index Organized Tables
Hi, As you all know, Index Organized tables are a way by which we can automatically cluster the data based on the primary key. While i was thinking about an implementation for postgres, it looks like an impossible with the current ideologies. In an IOT, if a record gets updated, we need to mark the old row as deleted immediately, as we do with any other table. But since Postgres supports user defined data types and if they happen to be a broken data type, then we have an unstable IOT.(as there is no guarantee, we might hit the same record) This was the reason for which, the proposal on creating indexes with snapshot was rejected. May i get a little clarification on this issue? Will we be supporting the IOT feature in postgres in future? Thanks, Gokul.
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Feb 20, 2010, at 10:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: There is a very clear set of behaviors that CORL ought to have given the precedents of our other COR commands. If we don't make it do things that way then we are going to surprise users, and we are also going to paint ourselves into a corner because we won't be able to fix it later without creating compatibility gotchas. Exactly. I agree completely. Attached is a draft patch (no doc changes) that implements CREATE OR REPLACE LANGUAGE following the semantics used in CREATE OR REPLACE FUNCTION, namely that in addition to whatever privileges you need to do the CREATE, you need to be owner of the existing entry if any; and the recorded ownership and permissions don't change. It's not bad at all --- net addition of 40 lines. So if we want to go at it this way, it's certainly feasible. I've got mixed feelings about the ownership check. If you get past the normal CREATE LANGUAGE permission checks, then either you are superuser, or you are database owner and you are trying to recreate a language from a pg_pltemplate entry with tmpldbacreate true. So it would fail only for a database owner who's trying to do C.O.R.L. on a superuser-installed language. Which arguably is a case we ought to allow. On the other hand, the case where not throwing an error would really matter is in trying to do pg_restore --single, and in that case even if we allowed the C.O.R.L. it would still spit up on the ALTER LANGUAGE OWNER that pg_dump is presumably going to emit right afterwards (except if using --no-owner, I guess). So I'm not sure we'd really be gaining much by omitting the ownership check, and it would certainly be less consistent with other C.O.R. commands if we don't apply such a check. How is pg_migrator affected by this? It always loads the the dump as the super-user. How will the pg_dump use CREATE OR REPLACE LANGUAGE? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Simon Riggs si...@2ndquadrant.com writes: There is currently no way to run a separate daemon process that runs user code as part of Postgres, so that the startup code gets run immediately we startup, re-run if we crash and shut down cleanly when the server does. If there were some way to run arbitrary code in a daemon using an extensibility API then we wouldn't ever get any requests for the scheduler, cos you could write it yourself without troubling anybody here. Please do include the Skytools / PGQ ticker as one use case in the design discussion, and pgbouncer too. Having user daemons as part as the PostgreSQL extensibility would be awesome indeed! Bonus point if you build them with PGXS and install them from SQL, so that the current extension packaging design applies. I guess we can say that the archive and restore command are precursors of managed user daemons, or say, integrated processes. So adding them to the use cases to cover would make sense. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
Robert Haas robertmh...@gmail.com writes: On Sun, Feb 21, 2010 at 1:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: It will be owned by the bootstrap superuser, so the case is exactly the one that a non-superuser DBA would be faced with. Or even a superuser other than the bootstrap superuser, no? I dump out the DB on my 8.4 server and try to reload on 9.0 with --single and it fails because, even though I'm a superuser, I can't replace a language owned by someone else? No, superusers always pass all permissions checks. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Attached is a draft patch (no doc changes) that implements CREATE OR REPLACE LANGUAGE How is pg_migrator affected by this? It always loads the the dump as the super-user. How will the pg_dump use CREATE OR REPLACE LANGUAGE? pg_dump would issue CREATE OR REPLACE LANGUAGE plpgsql which would succeed just fine, since it'd be issued by a superuser. I think the potential downsides of that are significantly smaller than having a special case that excludes plpgsql altogether --- for one example, it would still succeed in a custom installation that had been changed so that plpgsql wasn't installed by default. BTW, another problem I just noticed with the current kluge is that it fails to transfer any nondefault permissions that might have been attached to plpgsql. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Attached is a draft patch (no doc changes) that implements CREATE OR REPLACE LANGUAGE How is pg_migrator affected by this? It always loads the the dump as the super-user. How will the pg_dump use CREATE OR REPLACE LANGUAGE? pg_dump would issue CREATE OR REPLACE LANGUAGE plpgsql which would succeed just fine, since it'd be issued by a superuser. I think the potential downsides of that are significantly smaller than having a special case that excludes plpgsql altogether --- for one example, it would still succeed in a custom installation that had been changed so that plpgsql wasn't installed by default. Are we doing this just for plpgsql in pg_dump? BTW, another problem I just noticed with the current kluge is that it fails to transfer any nondefault permissions that might have been attached to plpgsql. Well, I assumed the permissions would still come, just not the CREATE LANGUAGE, but now that I think about it you might be right. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Thu, Feb 18, 2010 at 01:51:08PM -0800, David Fetter wrote: Folks, While hacking on PL/Parrot, I ran across an issue where when trying to load PL/pgsql, it's done unconditionally and fails. How do we fix pg_regress to be a little more subtle about this? For now, and for the archives, I've come up with this ugly hack: REGRESS_OPTS = --dbname=$(PL_TESTDB) NEEDS_PLPGSQL = $(shell psql -Atc SELECT setting::int 9 FROM pg_catalog.pg_settings WHERE name='server_version_num') ifeq ($(NEEDS_PLPGSQL), t) REGRESS_OPTS += $(if $PG_VERSION 9, --load-language=plpgsql, ) endif That works all the way back to 8.2, and to be honest, I'm not all that interested in making something that will work further back than that, especially for new projects. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Time travel on the buildfarm
It is currently 22:21:59 EST here. At 21:50 I committed a fix to copydir.c that cleaned up a couple of thinkos by Greg, including a misspelling that had been making all the builds fail for several hours. I went to see if any of the buildfarm had gone green yet, and indeed half a dozen members had --- but they are all claiming to be using snapshots between 00:44:53 and 00:51:20 old. Unless the cvs mirror can see into the future, that's a lie. I'm not sure how the ages on the buildfarm show_status.pl page are generated, but I'm betting somebody's local clock is off. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Oops, don't forget to rewind the directory before scanning it to
Fujii Masao masao.fu...@gmail.com writes: + Free(xldir); s/Free/FreeDir ? Yeah, that too. I think it's all good now, but please test. One thing I was wondering was whether the stat-wrong-file problem could explain the buildfarm failures that we thought were evidence of a portability issue. I was tempted to re-enable the #ifdef NOTYET code, but didn't want to pull that trigger while there were other problems outstanding. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming replication on win32, still broken
On Fri, Feb 19, 2010 at 7:54 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Heikki Linnakangas wrote: Magnus Hagander wrote: Well, it's going to make the process that reads the WAL cause actual physical I/O... That'll take a chunk out of your total available I/O, which is likely to push you to the limit of your I/O capacity much quicker. Right, doesn't seem sensible, though it would be nice to see a benchmark on that. Here's a patch to disable O_DIRECT when archiving or streaming is enabled. This is pretty hard to test, so any extra eyeballs would be nice.. Committed. Can you check that this fixed the PANIC you saw? Thanks! Yeah, SR works fine in my MinGW environment. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A thought on Index Organized Tables
Gokulakannan Somasundaram wrote: Hi, As you all know, Index Organized tables are a way by which we can automatically cluster the data based on the primary key. While i was thinking about an implementation for postgres, it looks like an impossible with the current ideologies. In an IOT, if a record gets updated, we need to mark the old row as deleted immediately, as we do with any other table. But since Postgres supports user defined data types and if they happen to be a broken data type, then we have an unstable IOT.(as there is no guarantee, we might hit the same record) This was the reason for which, the proposal on creating indexes with snapshot was rejected. May i get a little clarification on this issue? Will we be supporting the IOT feature in postgres in future? What seems like the best path to achieve the kind of performance benefits that IOTs offer is allowing index-only-scans using the visibility map. I worked on that last summer, but unfortunately didn't have the time to finish anything. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers