Re: [HACKERS] TOAST compression
Josh Berkus josh@agliodbs.com wrote While I'm waiting to figure out how to get the size of the toast table, at least I can provide the speed of query with/without assumed compression on the 6K text columns. Check out the table_size view in the newsysviews project. Andrew computed the regular, toast, and index sizes as a query. Will pg_total_relation_size_oid()/pg_total_relation_size_name() do the job? Regards, Qingqing ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....
Mark Woodward wrote: If you require a policy, then YOU are free to choose the policy that YOU need. You're not forced to accept other peoples' policies that may conflict with things in your environment. The problem is that there is no mechanism through which one can implement policy. You are left to roll your own each and every time. A mechanism provided, but not enforced, by postgresql would go a LONG way toward enabling a coherent policy. Unless you can have +80% of sites using the default, it isn't worth it and is more confusing than if you had never created it at all. What is wrong with defining an environment variable in /etc/profile? It isn't just an environment variable, it is a number of variables and a mechanism. Besides, profile, from an admin's perspective, is for managing users, not databases. OK, think of this, this is an actual site: I have three PostgreSQL database clusters on one server. A general purpose web service cluster that has all the web databases in it. I have a geographical database that has a U.S. street map database. I have a third which has a large music database on it. The geo and the music database are rebuilt periodically and tested off line. They are built and indexed, then tested at the office, and the physical cluster is uploaded to the server, where the specific postmaster processes are stopped, swapped, and restarted. Now, the pg_service.conf, is a HUGE plus for our process. When I work that into the coding spec, it makes testing the offline code easier because we no longer have to reconcile connection differences between lab and colo. Now, we have an environment that has multiple database clusters and server processes on one machine. How do you manage them? PostgreSQL has no facility to make this easier. Similar to pg_service.conf, I am suggesting (the concept has evolved with discussion) a pg_clusters.conf (name not important) that performs a similar job as pg_services, but is used to bring up multiple postmaster processes on one box. Currently, there is no standard way to manage this. PostgreSQL will continue to perform as it currently does, but a PostgreSQL blessed methodology of managing multiple clusters can be added to it. Individual processes can still be started and stop independently. Database clusters that are not in the pg_clusters file can still be created and started. I think Chris said it right, I don't want to make policy, I would to provide functionality. I know my service environment is not unique, and so what if it is only about 10% (or less) of the PostgreSQL users? There is a need for this, and it is a valuable enterprise level feature. DB admins will recognize and use this feature. It makes a lot of sense if you stand back and think of the admin process instead of the core database. Here's the jist of what I see: pg_clusters.conf [GEO] DPATH=/vol01/pg_geo PORT=5434 [ICDMDB] DPATH=/vol01/pg_icdmdb PORT=5433 [GENERAL] DPATH=/vol02/pg_users PORT=5432 Now, we should be able to modify pg_ctl to do something like this: pg_ctl -C GEO start pg_ctl -C ICDMDB start or pg_ctl startall ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Scrollable cursors and Sort performance
On Sun, 2006-02-26 at 19:26 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Fri, 2006-02-10 at 11:58 -0500, Tom Lane wrote: I suspect that the right thing is not to see this as a planner issue at all, but to try to drive the choice off the context in which the plan gets invoked. Possibly we could pass a need random access boolean down through the ExecInitNode calls (I seem to recall some prior discussion of doing something like that, in the context of telling Materialize that it could be a no-op in some cases). Yeh, that was me just being a little vague on implementation, but handing off from planner to executor via the Plan node is what I was hacking at now. I'll follow your recommendation and do it for the general case. Propagating it down should allow a few similar optimizations. Have you done anything with this idea yet? I was just thinking of attacking it myself. Started, but have been side-tracked by other urgent matters. Happy to complete this today with you? This mini-project has made me realise I don't understand the executor as well as I should, so I'm keen to see it through, even if I'm a bit slower at it. After looking at my old notes about Materialize, I am thinking that we should add a int flags parameter to the InitNode calls along with ExecutorStart and probably PortalStart. This would contain a bitwise OR of at least the following flag bits: need-ReScan need-backwards-scan need-mark-restore no-execute (so flags can replace ExecutorStart's explainOnly param) We'd have lots of room for expansion, but these are the ones that seem to have immediate use. And most callers of ExecutorStart/PortalStart know they don't need these things, so could just pass zero always. Design-wise I was looking at putting a named struc in there, so it would be easily expandible in the future to carry anything else that needs to be passed down through the nodes like this. I guess thats the same line-of-thought you're on too. Interesting point: how should EXPLAIN ANALYZE set these bits? For its own purposes it need not request random access, but it might be interesting to make it possible to examine both the random and nonrandom behaviors, now that these will be significantly different performancewise. Possibly we could make EXPLAIN ANALYZE EXECUTE set the random-access bits. Good point. Whichever we do will be wrong in some cases I've no real opinion on this other than a vague preference for it to be quick. Best Regards, Simon Riggs ---(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] Scrollable cursors and Sort performance
Simon Riggs [EMAIL PROTECTED] writes: On Sun, 2006-02-26 at 19:26 -0500, Tom Lane wrote: After looking at my old notes about Materialize, I am thinking that we should add a int flags parameter to the InitNode calls along with ExecutorStart and probably PortalStart. Design-wise I was looking at putting a named struc in there, so it would be easily expandible in the future to carry anything else that needs to be passed down through the nodes like this. That would be the hard way, primarily because it would require copying and modifying the struct at each level of recursion --- which'd turn what should be a nearly zero-cost patch into something with possibly nontrivial cost. Copy and modify is needed because as one descends through the plan tree the requirements change. For instance, MergeJoin requires mark/restore capability of its right input, but this will never be a requirement propagated from the top (or anyplace else). Materialize on the other hand should turn off some of the bits, since it won't pass backwards scan or mark/restore calls down to its child. These are trivial changes to implement with a flag-word representation, not so with a struct. If I saw a need for non-boolean parameters in this structure then maybe I'd agree, but there's no evidence of a need for them. What the child plan nodes need to know is will I get any mark/restore calls and such like, and those are certainly boolean conditions. I'm envisioning coding like ExecInitMergeJoin(MergeJoin *node, EState *estate, int flags) ... /* reject unsupported cases */ Assert(!(flags (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK))); ... innerPlanState(mergestate) = ExecInitNode(innerPlan(node), estate, flags | EXEC_FLAG_MARK); nodeSort.c would have a test like node-random = (flags (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK)) != 0; etc etc. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....
On Mon, Feb 27, 2006 at 09:39:59AM -0500, Mark Woodward wrote: It isn't just an environment variable, it is a number of variables and a mechanism. Besides, profile, from an admin's perspective, is for managing users, not databases. Sure, you need to control the user, group, placement of logfile and several other things. snip I think Chris said it right, I don't want to make policy, I would to provide functionality. I know my service environment is not unique, and so what if it is only about 10% (or less) of the PostgreSQL users? There is a need for this, and it is a valuable enterprise level feature. DB admins will recognize and use this feature. It makes a lot of sense if you stand back and think of the admin process instead of the core database. How is any of this different from the way Debian handles multiple simultaneous clusters? Is there any particular reason you couldn't use it or a variation thereof (other than that it enforces a particular policy, namely debian's)? The source is available [1] and a quick demonstration was posted [2]. In any case, nothing stops anyone from starting a project on pgfoundary. Nothing convinces people quite like working code. Since -core seems uninterested, I think this would be the best way to go. Have a nice day, [1] http://ftp.debian.org/debian/pool/main/p/postgresql-common/postgresql-common_43.tar.gz [2] http://archives.postgresql.org/pgsql-hackers/2006-02/msg00942.php -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] User privileges-verification required
Yeah it really was :-) BTW Ihave implemented this for multiuser-mode as well..Both the approaches as mentioned in my very first email... 1- The minimum super user count should not be less then 1. 2- Only the main database system owner is eligible to reassign. My personal believe is, this minimum functionality should be a part of multiuser mode! I was oblivious of this anddid initdb twice, may be I am dumb:-) Thanks, Nauman On 2/26/06, Christopher Kings-Lynne [EMAIL PROTECTED] wrote: In my opinion we should cater for such a situation, and two possible solutions come to my mind for this: I've done exactly this before, and had to use single user mode torecover.Annoying. 1. Place a restriction that there should be more than one superuser before you can issue a NOCREATEUSER command. I agree :)Chris
Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....
On Mon, Feb 27, 2006 at 09:39:59AM -0500, Mark Woodward wrote: It isn't just an environment variable, it is a number of variables and a mechanism. Besides, profile, from an admin's perspective, is for managing users, not databases. Sure, you need to control the user, group, placement of logfile and several other things. snip I think Chris said it right, I don't want to make policy, I would to provide functionality. I know my service environment is not unique, and so what if it is only about 10% (or less) of the PostgreSQL users? There is a need for this, and it is a valuable enterprise level feature. DB admins will recognize and use this feature. It makes a lot of sense if you stand back and think of the admin process instead of the core database. How is any of this different from the way Debian handles multiple simultaneous clusters? Is there any particular reason you couldn't use it or a variation thereof (other than that it enforces a particular policy, namely debian's)? The source is available [1] and a quick demonstration was posted [2]. Well, I'm sure that one could use debian's solution, but that's the problem, it isn't PostgreSQL's solution. Shouldn't PostgreSQL provide the mechanisms? Will debian support FreeBSD? NetBSD? Is it in the PostgreSQL admin manual? We are talking about a feature, like pg_service.conf, now that people notice it, we are saying WOW, this is the API we should push. This is a functionality, IMHO, must be the responsibility of PostgreSQL. In any case, nothing stops anyone from starting a project on pgfoundary. Nothing convinces people quite like working code. Since -core seems uninterested, I think this would be the best way to go. Argg, the pgfoundary is sort of the free speech zones that the U.S. sets up out of view of the president and the press. Yea, its there, and if you go out of your way, you can find it. Think of Arthur Dent's The plans were on display! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mark Woodward Sent: 27 February 2006 16:49 To: Martijn van Oosterhout Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf Think of Arthur Dent's The plans were on display! There are no leopards on pgFoundry. Regards, Dave ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Any conclusion on the Xeon context-switching issue?
Subject says it all really. I've got a new client who seems to be suffering from it, and I'm not sure if any conclusion was reached. -- Richard Huxton Archonet Ltd ---(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] constraints and sql92 information_schema compliance
Josh Berkus josh@agliodbs.com writes: No way. The entire point of information_schema is that it is standard; adding non-spec things to it renders it no better than direct access to the PG catalogs. Hmmm ... so, per you, we can't add extra views covering non-spec objects to the information_schema (like aggregates) because we can't modify it in any way. But per Peter we can't add new views to the pg_catalog because we want people to use information_schema. I sense a catch-22 here. I doubt Peter really meant that we can't add any new views; in particular, for information that is not available from the standard information_schema it's certainly silly to claim that people should go to information_schema for it. I do see his point that we shouldn't unnecessarily duplicate functionality that's available in a standardized view. I do have doubts about adding any large number of add-on views to pg_catalog, because of the privileged place of that schema in search paths. It'd be better to put them in a separate schema (pg_info maybe?) where they'd pose less risk of conflicts with user-defined names. Does newsysviews already do this? 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
Re: [HACKERS] Any conclusion on the Xeon context-switching issue?
Richard Huxton dev@archonet.com writes: Subject says it all really. I've got a new client who seems to be suffering from it, and I'm not sure if any conclusion was reached. What's he using? 8.1 seems to have alleviated the problem somewhat, and I've done more work in CVS tip. It'll never go away entirely, because these chips are just not very good at sharing memory, but we've certainly reduced it quite a bit from where it was in 7.x. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Any conclusion on the Xeon context-switching issue?
Tom Lane wrote: Richard Huxton dev@archonet.com writes: Subject says it all really. I've got a new client who seems to be suffering from it, and I'm not sure if any conclusion was reached. What's he using? 8.1 seems to have alleviated the problem somewhat, and I've done more work in CVS tip. It'll never go away entirely, because these chips are just not very good at sharing memory, but we've certainly reduced it quite a bit from where it was in 7.x. 7.4.12 (.12 as of last week). I've seen context-switching peak at 8 on a quad-Xeon that really shouldn't be straining. An upgrade is possible, but obviously needs testing against. Would your CVS changes be against 8.2-to-be or 8.1.x? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Any conclusion on the Xeon context-switching issue?
Richard Huxton dev@archonet.com writes: Tom Lane wrote: What's he using? 7.4.12 (.12 as of last week). I've seen context-switching peak at 8 on a quad-Xeon that really shouldn't be straining. Try 8.1.3. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Any conclusion on the Xeon context-switching issue?
Tom Lane wrote: Richard Huxton dev@archonet.com writes: Tom Lane wrote: What's he using? 7.4.12 (.12 as of last week). I've seen context-switching peak at 8 on a quad-Xeon that really shouldn't be straining. Try 8.1.3. Thanks. I'll see if we can run a parallel installation overnight or something. -- Richard Huxton Archonet Ltd ---(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] Scrollable cursors and Sort performance
On Mon, 2006-02-27 at 10:07 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Sun, 2006-02-26 at 19:26 -0500, Tom Lane wrote: After looking at my old notes about Materialize, I am thinking that we should add a int flags parameter to the InitNode calls along with ExecutorStart and probably PortalStart. Design-wise I was looking at putting a named struc in there, so it would be easily expandible in the future to carry anything else that needs to be passed down through the nodes like this. That would be the hard way, primarily because it would require copying and modifying the struct at each level of recursion --- which'd turn what should be a nearly zero-cost patch into something with possibly nontrivial cost. Yeh, didn't take me long to see the costs; I just gave that idea up prior to reading your post. I won't go into *why* I was trying that, especially since I've stopped... Following your recipe pretty close as of now. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Dead Space Map
Hi, The idea of using a so called dead space map to speed up vacuum has come up multiple times in this list in the last couple of years. I wrote an initial implementation of it to measure the performance impact it has on updates and on vacuum. Potential uses for a dead space map are: * speed up vacuum when there's few dead tuples Vacuum will need to be modified to use index lookups to find index tuples corresponding the dead heap tuples. Otherwise you have to scan through all the indexes anyway. * vacuuming pages one by one as they're written by bgwriter I'm not sure how much difference this would make, but it would be an interesting experiment. In theory, you could save a lot of total I/O, because you would not need to come back to vacuum the pages later, but you would have to read in any index pages pointing to the dead heap tuples inside bgwriter. * implementation of index-only scans An index scan would not have to check the visibility information of heap tuples on those heap pages that are marked as clean in the dead space map. This requires that the dead space map is implemented so that a page is reliably marked as dirty in all circumstances when it contains any tuples that are not visible to all backends. The obvious drawback is that heap updates need to update the dead space map too. My current implementation stores a bitmap of 32k bits in the special space of every 32k heap pages. Each bit in the bitmap corresponds one heap page. The bit is set every time a tuple is updated, and it's cleared by vacuum. This is a very simple approach, and doesn't take much space. Is there something I'm missing? Any ideas? I'm going to have some spare time to hack PostgreSQL in the coming months, and I'm thinking of refining this if there's interest. Is anyone else working on this? - Heikki ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Dead Space Map
Heikki, On 2/27/06 9:53 AM, Heikki Linnakangas [EMAIL PROTECTED] wrote: My current implementation stores a bitmap of 32k bits in the special space of every 32k heap pages. Each bit in the bitmap corresponds one heap page. The bit is set every time a tuple is updated, and it's cleared by vacuum. This is a very simple approach, and doesn't take much space. Is there something I'm missing? Any ideas? Sounds great! I'm going to have some spare time to hack PostgreSQL in the coming months, and I'm thinking of refining this if there's interest. Is anyone else working on this? This idea seems like it could dramatically improve vacuum - commonly a big issue. - 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] Dead Space Map
Heikki Linnakangas [EMAIL PROTECTED] writes: Vacuum will need to be modified to use index lookups to find index tuples corresponding the dead heap tuples. Otherwise you have to scan through all the indexes anyway. This strikes me as a fairly bad idea, because it makes VACUUM dependent on correct functioning of user-written code --- consider a functional index involving a user-written function that was claimed to be immutable and is not. There are concurrency-safety issues too, I think, having to do with the way that btree ensures we don't delete any index tuple that some scan is stopped on. * vacuuming pages one by one as they're written by bgwriter That's not happening. VACUUM has to be a transaction and the bgwriter does not run transactions; nor is it in any position to clean out index entries associated with a heap page. (To change this would at a minimum require instituting a separate bgwriter process per database; or else a wholesale rewrite of our catalog access infrastructure to allow it to work in a non-database-specific context. There are also interesting deadlock problems to think about if the bgwriter can be blocked by other transactions, or if it needs to read pages not currently in shared memory.) * implementation of index-only scans An index scan would not have to check the visibility information of heap tuples on those heap pages that are marked as clean in the dead space map. This requires that the dead space map is implemented so that a page is reliably marked as dirty in all circumstances when it contains any tuples that are not visible to all backends. The reliably part of this is likely to make it a non-starter. Another problem is that the semantics needed by this are not quite the same as the semantics of whether a page needs to be visited by vacuum. My current implementation stores a bitmap of 32k bits in the special space of every 32k heap pages. Each bit in the bitmap corresponds one heap page. The bit is set every time a tuple is updated, and it's cleared by vacuum. This is a very simple approach, and doesn't take much space. I thought the plan was to use out-of-line storage associated with each table segment file. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Automatic free space map filling
Something came to my mind today, I'm not sure if it's feasible but I would like to know opinions on it. We've seen database applications that PostgreSQL simply could not manage because one would have to vacuum continuously. Perhaps in those situations one could arrange it that an update (or delete) of a row registers the space in the free space map right away, on the assumption that by the time it is up for reuse, the transaction will likely have committed. Naturally, this would need to be secured in some way, for example a maybe bit in the FSM itself or simply checking that the supposed free space is really free before using it, perhaps combined with a timeout (don't consider until 5 seconds from now). I think with applications that have a more or less constant data volume but update that data a lot, this could assure constant disk space usage (even if it's only a constant factor above the ideal usage) without any vacuuming. Comments? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] display and expression of the home directory in Win32
Hi All. I have thought this way and that since that time. Suggestion of Magnus-san was considered and this was made. I considered many things about the pgpass guide of libpq. In windows, even the place of it was not clear. Furthermore, they are intricately concerned with an environment variable again. Then, I thought that wanted to take into consideration not only a position but its maintenance. C:\Program Files\PostgreSQL\8.1\binpqpasswd.exe --help pqpasswd installs a pgpass(libpq) connect a PostgreSQL database. Usage: pqpasswd [OPTION]... [DBNAME] Options: -l, --listshow a list of installed pgpass -r, --remove remove the my pgpass -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port -d, --dbname=DBNAME database to connect as -U, --username=USERNAME user name to connect as --helpshow this help, then exit --version output version information, then exit Report bugs to pgsql-bugs@postgresql.org. C:\Program Files\PostgreSQL\8.1\binpqpasswd.exe --list C:\Documents and Settings\saito\Application Data/postgresql/pgpass.conf hostname=localhost port=5432 dbname=* username=postgres password=** hostname=* port=5432 dbname=saito username=saito password=** hostname=localhost port=5432 dbname=* username=z-saito password=** The 2th line is used. password change can be made as follows. C:\Program Files\PostgreSQL\8.1\binpqpasswd.exe --dbname=* --username=postgres New Password: Retype New Password: Succeeded in creation. C:\Program Files\PostgreSQL\8.1\binpqpasswd.exe -l --dbname=* --username=postgres C:\Documents and Settings\saito\Application Data/postgresql/pgpass.conf hostname=localhost port=5432 dbname=* username=postgres password=** hostname=* port=5432 dbname=saito username=saito password=** hostname=localhost port=5432 dbname=* username=z-saito password=** The 1th line is used. I want the password to be enciphered in the future. However, we fully have to take the past property into consideration. Then, I want this to be equipped as first stage. any suggestion.? Regards, Hiroshi Saito scripts_pqpasswd_patch Description: Binary data ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] constraints and sql92 information_schema compliance
On Mon, Feb 27, 2006 at 11:39:30AM -0500, Tom Lane wrote: | Josh Berkus josh@agliodbs.com writes: | No way. The entire point of information_schema is that it is standard; | adding non-spec things to it renders it no better than direct access | to the PG catalogs. | | Hmmm ... so, per you, we can't add extra views covering non-spec | objects to the information_schema (like aggregates) because we | can't modify it in any way. But per Peter we can't add new | views to the pg_catalog because we want people to use | information_schema. I sense a catch-22 here. | I doubt Peter really meant that we can't add any new views; in | particular, for information that is not available from the standard | information_schema it's certainly silly to claim that people should go | to information_schema for it. I do see his point that we shouldn't | unnecessarily duplicate functionality that's available in a standardized | view. If my opinion is worth anything here, nothing should go in the information_schema unless is is specified in one of the SQL1992, SQL1999, or SQL2003 specifications. According to my objectives, if it isn't in the information_schema, I should not be using it. I've been using information_schema reflectively, and would have been confused to see anything in there that wasn't in the specs. | I do have doubts about adding any large number of add-on views to | pg_catalog, because of the privileged place of that schema in search | paths. It'd be better to put them in a separate schema (pg_info | maybe?) where they'd pose less risk of conflicts with user-defined names. | Does newsysviews already do this? A separate pg_info probably would not hurt, I suppose. Best, Clark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Automatic free space map filling
Peter Eisentraut [EMAIL PROTECTED] writes: We've seen database applications that PostgreSQL simply could not manage because one would have to vacuum continuously. Perhaps in those situations one could arrange it that an update (or delete) of a row registers the space in the free space map right away, on the assumption that by the time it is up for reuse, the transaction will likely have committed. The free-space map is not the hard part of the problem. You still have to VACUUM --- that is, wait until the dead tuple is not only committed dead but is certainly dead to all onlooker transactions, and then remove its index entries as well as the tuple itself. The first part of this makes it impossible for a transaction to be responsible for vacuuming its own detritus. Naturally, this would need to be secured in some way, The FSM is only a hint anyway --- if it points someone to a page that in reality does not have adequate free space, nothing bad happens except for the wasted cycles to visit the page and find that out. See the loop in RelationGetBufferForTuple(). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] display and expression of the home directory in Win32
Hiroshi Saito [EMAIL PROTECTED] writes: C:\Program Files\PostgreSQL\8.1\binpqpasswd.exe --help pqpasswd installs a pgpass(libpq) connect a PostgreSQL database. I must be missing something. What exactly does this accomplish that couldn't be done at least as flexibly with a simple text editor? If the argument is point-and-drool Windows users can't be expected to use a text editor, I would think that the same argument applies to a command-line program; you'd have to make a GUI application to make it easier to use than Notepad or what-have-you. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] constraints and sql92 information_schema compliance
On 2006-02-27, Tom Lane [EMAIL PROTECTED] wrote: I do have doubts about adding any large number of add-on views to pg_catalog, because of the privileged place of that schema in search paths. It'd be better to put them in a separate schema (pg_info maybe?) where they'd pose less risk of conflicts with user-defined names. Does newsysviews already do this? The current version in pgfoundry CVS uses pg_sysviews as the schema name. If you have any better suggestions for the name, or any other aspect of the project, then we're all ears. -- 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] constraints and sql92 information_schema compliance
Tom, Hmmm ... so, per you, we can't add extra views covering non-spec objects to the information_schema (like aggregates) because we can't modify it in any way. But per Peter we can't add new views to the pg_catalog because we want people to use information_schema. I sense a catch-22 here. I doubt Peter really meant that we can't add any new views; in particular, for information that is not available from the standard information_schema it's certainly silly to claim that people should go to information_schema for it. I do see his point that we shouldn't unnecessarily duplicate functionality that's available in a standardized view. Yes, I agree with him on that. However, there's a certain amount of confusion inspired by the organization that: If you want to look up the table's columns go to information_schmea, if you want the table *size* go to sysviews. But maybe that's unavoidable. Or maybe we could link the information_schema views into pg_sysviews? We'd earlier thought that the permissions stuff in information_schema made is untenable for any real database catalog use. If 03 has fixed that, though, maybe this can work. AndrewSN? I do have doubts about adding any large number of add-on views to pg_catalog, because of the privileged place of that schema in search paths. It'd be better to put them in a separate schema (pg_info maybe?) where they'd pose less risk of conflicts with user-defined names. Does newsysviews already do this? Yes, in our original conception it was the schema pg_sysviews. --Josh Berkus ---(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] display and expression of the home directory in Win32
Thank you for a quick response. From: Tom Lane C:\Program Files\PostgreSQL\8.1\binpqpasswd.exe --help pqpasswd installs a pgpass(libpq) connect a PostgreSQL database. I must be missing something. What exactly does this accomplish that couldn't be done at least as flexibly with a simple text editor? Ah, It is involved with an environment variable, and a user has to operate it, fully taking into consideration. While opening the text editor, someone may be in your back If the argument is point-and-drool Windows users can't be expected to use a text editor, I would think that the same argument applies to a command-line program; you'd have to make a GUI application to make it easier to use than Notepad or what-have-you. Um, pgAdminIII is performing it. As for it, the solution method is different though regrettable As a very important thing, I am planning future encryption. Regards, Hiroshi Saito ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] constraints and sql92 information_schema compliance
Josh Berkus josh@agliodbs.com writes: Yes, I agree with him on that. However, there's a certain amount of confusion inspired by the organization that: If you want to look up the table's columns go to information_schmea, if you want the table *size* go to sysviews. But maybe that's unavoidable. Or maybe we could link the information_schema views into pg_sysviews? We could, but I'd argue that this makes sense only if the added PG-specific stuff looks like a seamless extension of the standard definitions. If there are obvious differences in naming style, table layout, etc, I'd expect such a setup to look more like a hodgepodge than a good idea. 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
Re: [HACKERS] constraints and sql92 information_schema compliance
Andrew - Supernews wrote: On 2006-02-27, Tom Lane [EMAIL PROTECTED] wrote: I do have doubts about adding any large number of add-on views to pg_catalog, because of the privileged place of that schema in search paths. It'd be better to put them in a separate schema (pg_info maybe?) where they'd pose less risk of conflicts with user-defined names. Does newsysviews already do this? The current version in pgfoundry CVS uses pg_sysviews as the schema name. If you have any better suggestions for the name, or any other aspect of the project, then we're all ears. How fine-grained do we want to get on namespaces? I'd be slightly more inclined to have pg_info or maybe pg_utils as a place to stash not only extra system views but other utility stuff that we want to ship but is essentially droppable. cheers andrew ---(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] constraints and sql92 information_schema compliance
Andrew, How fine-grained do we want to get on namespaces? I'd be slightly more inclined to have pg_info or maybe pg_utils as a place to stash not only extra system views but other utility stuff that we want to ship but is essentially droppable. AFAIK, none of the contributors to newsysviews has any attachment to any particular name. I'd personally prefer to go with your suggestion of a more generic schema name. --Josh ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....
On Mon, Feb 27, 2006 at 11:48:50AM -0500, Mark Woodward wrote: Well, I'm sure that one could use debian's solution, but that's the problem, it isn't PostgreSQL's solution. Shouldn't PostgreSQL provide the mechanisms? Will debian support FreeBSD? NetBSD? Is it in the PostgreSQL admin manual? I meant that it's a good start. It's a fully functional solution (for its intended audience) that works now and thus might give you ideas how you want your solution to work. Argg, the pgfoundary is sort of the free speech zones that the U.S. sets up out of view of the president and the press. Yea, its there, and if you go out of your way, you can find it. Think of Arthur Dent's The plans were on display! My point is only that since trying to convince people on -hackers to write the code isn't working, perhaps someone (you?) could write it seperately for possible inclusion later. If someone writes it all themselves then they can send a patch. OTOH if several people want to collaborate on a solution, something like pgfoundary is useful. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....
Mark, Well, I'm sure that one could use debian's solution, but that's the problem, it isn't PostgreSQL's solution. Shouldn't PostgreSQL provide the mechanisms? Will debian support FreeBSD? NetBSD? Is it in the PostgreSQL admin manual? We are talking about a feature, like pg_service.conf, now that people notice it, we are saying WOW, this is the API we should push. This is a functionality, IMHO, must be the responsibility of PostgreSQL. Then stop talking about it and write a patch. So far, you've failed to convince anyone else on this list that the functionality you suggest is actually useful for anyone other that you, personally. The only way you're going to do so is to put up some code somewhere other people can use it and prove that it's useful. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(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] pg_config, pg_service.conf, postgresql.conf ....
On Mon, Feb 27, 2006 at 11:48:50AM -0500, Mark Woodward wrote: Well, I'm sure that one could use debian's solution, but that's the problem, it isn't PostgreSQL's solution. Shouldn't PostgreSQL provide the mechanisms? Will debian support FreeBSD? NetBSD? Is it in the PostgreSQL admin manual? I meant that it's a good start. It's a fully functional solution (for its intended audience) that works now and thus might give you ideas how you want your solution to work. I have a number of ideas as to how it would work and debian is certainly another reference point. Argg, the pgfoundary is sort of the free speech zones that the U.S. sets up out of view of the president and the press. Yea, its there, and if you go out of your way, you can find it. Think of Arthur Dent's The plans were on display! My point is only that since trying to convince people on -hackers to write the code isn't working, perhaps someone (you?) could write it seperately for possible inclusion later. If someone writes it all themselves then they can send a patch. OTOH if several people want to collaborate on a solution, something like pgfoundary is useful. Well, I said, at the top post, that I would write it, I'm not trying to convince anyone to to work on it. If others would like to help, that would certainly be OK. I'm trying to propose a feature, iron out how it should work, and get feedback before I implement it. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Dead Space Map
On Mon, 27 Feb 2006, Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Vacuum will need to be modified to use index lookups to find index tuples corresponding the dead heap tuples. Otherwise you have to scan through all the indexes anyway. This strikes me as a fairly bad idea, because it makes VACUUM dependent on correct functioning of user-written code --- consider a functional index involving a user-written function that was claimed to be immutable and is not. If the user-defined function is broken, you're in more or less trouble anyway. A VACUUM FULL or REINDEX can be used to recover. There are concurrency-safety issues too, I think, having to do with the way that btree ensures we don't delete any index tuple that some scan is stopped on. Hmm, I see. I'll have to study the btree implementation more thoroughly. * implementation of index-only scans An index scan would not have to check the visibility information of heap tuples on those heap pages that are marked as clean in the dead space map. This requires that the dead space map is implemented so that a page is reliably marked as dirty in all circumstances when it contains any tuples that are not visible to all backends. The reliably part of this is likely to make it a non-starter. AFAICS all heap access goes through the functions in heapam.c. They need to be modified to update the dead space map. Also on recovery. The locking semantics of the dead space map need to be thought out, but I don't see any insurmountable problems. Another problem is that the semantics needed by this are not quite the same as the semantics of whether a page needs to be visited by vacuum. True. We might have to have two bits per page. It's still not that bad, though, compared to the benefit. My current implementation stores a bitmap of 32k bits in the special space of every 32k heap pages. Each bit in the bitmap corresponds one heap page. The bit is set every time a tuple is updated, and it's cleared by vacuum. This is a very simple approach, and doesn't take much space. I thought the plan was to use out-of-line storage associated with each table segment file. You're probably referring to Alvaro's auto-vacuum todo list from July: http://archives.postgresql.org/pgsql-hackers/2005-07/msg01029.php I find it more attractive to put the bitmap in the special space, for the reasons stated earlier by Jan Wieck: http://archives.postgresql.org/pgsql-hackers/2004-03/msg00957.php That is, so that you can utilize the common buffer management code. Jan also had a plan there for the locking. - Heikki ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Any conclusion on the Xeon context-switching issue?
On Mon, 2006-02-27 at 16:54 +, Richard Huxton wrote: Tom Lane wrote: Richard Huxton dev@archonet.com writes: Subject says it all really. I've got a new client who seems to be suffering from it, and I'm not sure if any conclusion was reached. What's he using? 8.1 seems to have alleviated the problem somewhat, and I've done more work in CVS tip. It'll never go away entirely, because these chips are just not very good at sharing memory, but we've certainly reduced it quite a bit from where it was in 7.x. 7.4.12 (.12 as of last week). I've seen context-switching peak at 8 on a quad-Xeon that really shouldn't be straining. Is this causing an actual problem, or do you just like to see lower numbers under the cs column? Certainly many people have asked about this issue but few have pointed to application-level performance problems that may have resulted. -jwb ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Dead Space Map
Heikki Linnakangas [EMAIL PROTECTED] writes: On Mon, 27 Feb 2006, Tom Lane wrote: This strikes me as a fairly bad idea, because it makes VACUUM dependent on correct functioning of user-written code --- consider a functional index involving a user-written function that was claimed to be immutable and is not. If the user-defined function is broken, you're in more or less trouble anyway. Less. A non-immutable function might result in lookup failures (not finding the row you need) but not in database corruption, which is what would ensue if VACUUM fails to remove an index tuple. The index entry would eventually point to a wrong table entry, after the table item slot gets recycled for another tuple. Moreover, you haven't pointed to any strong reason to adopt this methodology. It'd only be a win when vacuuming pretty small numbers of tuples, which is not the design center for VACUUM, and isn't likely to be the case in practice either if you're using autovacuum. If you're removing say 1% of the tuples, you are likely to be hitting every index page to do it, meaning that the scan approach will be significantly *more* efficient than retail lookups. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....
Mark, Well, I'm sure that one could use debian's solution, but that's the problem, it isn't PostgreSQL's solution. Shouldn't PostgreSQL provide the mechanisms? Will debian support FreeBSD? NetBSD? Is it in the PostgreSQL admin manual? We are talking about a feature, like pg_service.conf, now that people notice it, we are saying WOW, this is the API we should push. This is a functionality, IMHO, must be the responsibility of PostgreSQL. Then stop talking about it and write a patch. So far, you've failed to convince anyone else on this list that the functionality you suggest is actually useful for anyone other that you, personally. The only way you're going to do so is to put up some code somewhere other people can use it and prove that it's useful. Maybe I'm too used to working in engineering groups. I am trying to get input for a project. Trying to iron out what the feature set should be and the objectives that should be attained. BEFORE I start coding. Just saying submit a patch is the antithesis to good engineering, it works for hacking, but if I am going to develop a feature, I wish to do it right and have it appeal to the broadest possible audience, collect as much input about the needs of users, etc. The feature set I am suggesting is, as been pointed out, similar to other projects happening outside of PostgreSQL. The debian project for instance. To say I am the only one that needs this, is of course, not true. My frustration level often kills any desire to contribute to open source. Sometimes, I think that open source is doomed. The various projects I track and use are very frustrating, they remind me of dysfunctional engineering departments in huge companies, it is very hard to positively discuss any new ideas. The first response is always some variation on no. Maybe it is that the whiteboard engineering discussion process doesn't translate well to this medium. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....
Maybe I'm too used to working in engineering groups. I am trying to get input for a project. Trying to iron out what the feature set should be and the objectives that should be attained. BEFORE I start coding. Well that is always a good idea but: Just saying submit a patch is the antithesis to good engineering, it works for hacking, but if I am going to develop a feature, I wish to do it right and have it appeal to the broadest possible audience, collect as much input about the needs of users, etc. The problem you are having is that sense many people do not see a benefit it is hard to garner the feedback, thus the fallback to submit a patch. If you submit a patch there is a chance that people will see the benefit within a simple implementation and THEN you get the feedback you want. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] constraints and sql92 information_schema compliance
On Mon, Feb 27, 2006 at 11:24:05AM -0800, Josh Berkus wrote: Andrew, How fine-grained do we want to get on namespaces? I'd be slightly more inclined to have pg_info or maybe pg_utils as a place to stash not only extra system views but other utility stuff that we want to ship but is essentially droppable. AFAIK, none of the contributors to newsysviews has any attachment to any particular name. I'd personally prefer to go with your suggestion of a more generic schema name. Agreed. pg_info or pg_util sound good. Since there's a fairly large number of views I don't know if it's worth having both pg_info and pg_util. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] constraints and sql92 information_schema compliance
On Mon, Feb 27, 2006 at 02:17:03PM -0500, Tom Lane wrote: Josh Berkus josh@agliodbs.com writes: Yes, I agree with him on that. However, there's a certain amount of confusion inspired by the organization that: If you want to look up the table's columns go to information_schmea, if you want the table *size* go to sysviews. But maybe that's unavoidable. Or maybe we could link the information_schema views into pg_sysviews? We could, but I'd argue that this makes sense only if the added PG-specific stuff looks like a seamless extension of the standard definitions. If there are obvious differences in naming style, table layout, etc, I'd expect such a setup to look more like a hodgepodge than a good idea. Agreed. Currently, newsysviews uses a completely different naming scheme, one intended to be as self-explanitory as possible and not using things like hard to remember abbreviations crammed together without underscores (no offense to the catalogs; I'm sure backend developers don't want to type out full names all the time, but it does make it substantially harder to grok for newer users). Personally, I find info_schema somewhat hard to use as well, largely because it's meant to be the same for any database, so there's terminology mapping you have to do. Plus, newsysviews has some things that make it easier on people who are doing ad-hoc queries, such as _all as well as _user versions of most things. So I'd rather keep the naming we use in newsysviews, but I can certainly see where a version of info_schema that's been extended could be of use. Perhaps there's reason to have both. Hopefully an extended info_schema would be able to rely mostly on what's already in info_schema, so there would be a minimal amount of work required on it. For that matter, if newsysviews became part of the standard install, it could well be easier to build info_schema on it instead of the base tables. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....
Mark Woodward [EMAIL PROTECTED] writes: My frustration level often kills any desire to contribute to open source. Sometimes, I think that open source is doomed. The various projects I track and use are very frustrating, they remind me of dysfunctional engineering departments in huge companies, it is very hard to positively discuss any new ideas. The first response is always some variation on no. Well, at least for PG the process has to be biased towards no, because we have to keep the code reliable and maintainable. If we bias in the direction of throwing in every little feature someone thinks up, we'll soon have a buggy, incomprehensible mess. FWIW, the proposal as it seems to have evolved (config file separate from pg_service and known only to pg_ctl) doesn't seem too unreasonable to me. I might have some use for it personally, if the implementation is capable of launching back-version postmasters as well as current-version. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Scanning for insert
Greets, [Ugh, sent one with uncompressed patch. Seems to be swallowed(No failure msg?). Ignore it if it surfaces.] The attached patch extends pg_am by adding two methods, scan for insert and insert from scan. These methods are meant to provide an index user with the ability to do conditional insertions based on the results of a conclusive locking scan--a scan that not only finds a match, but resolves its actual existence(what _bt_check_unique does), and makes any necessary locks to warrant later insertion by a call to insertfromscan. (It should also be noted that insertions are aborted by giving the insertfromscan method an InvalidItemPointer instead of adding another method, ie abortinsertscan.) I'm sending this to hackers instead of patches, because it's not complete(I know it's broken in the some of the new places), and, most importantly, I want to know if this is actually the appropriate general direction to travel in. Not to mention that I have probably missed something and I hope that someone will give me my due whackings with The Clue Stick. :P These new index interfaces are meant to provide the necessary functionality for doing conditional index insertions that will likely aid in any implementation of error logging, and other features(merge?). I have failed to find much discussion on this, so if there is a good thread about, please point me at it. The most recent discussion of something along these lines was Martijn's thread about providing non-btree indexes with uniqueness: http://archives.postgresql.org/pgsql-hackers/2006-01/msg00541.php (However, I doubt that this patch would actually help with deferred constraints or giving GiST/hash uniqueness.) This patch is mostly incremental, and doesn't make many alterations to existing code for the time being; the most significant alteration was restructuring a bit of _bt_check_unique(_bt_evaluate in this patch) to work for both scanforinsert and doinsert. (This is probably a good thing as this code is all pretty new to me.) [Sorry about some of the whitespace changes in the patch, remnants from an overzealous version.] Any thoughts and directions would be appreciated. -- Regards, James William Pye ciinsert.patch.gz Description: Binary data ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Scanning for insert
James William Pye [EMAIL PROTECTED] writes: The attached patch extends pg_am by adding two methods, scan for insert and insert from scan. These methods are meant to provide an index user with the ability to do conditional insertions based on the results of a conclusive locking scan--a scan that not only finds a match, but resolves its actual existence(what _bt_check_unique does), and makes any necessary locks to warrant later insertion by a call to insertfromscan. Is this really a good idea? The fundamental problem I see with it is that it takes away the index AM's ability to make any guarantees about its locking behavior, ie, how long locks will be held or what other operations might intervene while holding them. It'll also require the AM to save potentially large amounts of state between the two calls (eg, an entire search path might be needed for GiST). Currently any such state can live on the stack in local variables, but that won't work if it has to be remembered until a later AM call. Lastly, what happens if the caller loses control (due to elog) and never makes the followup AM call? These new index interfaces are meant to provide the necessary functionality for doing conditional index insertions that will likely aid in any implementation of error logging, and other features(merge?). If that's what you want, maybe a better answer is to simply allow aminsert to return a uniqueness-conflict indication, rather than raising the ereport for itself. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....
On Mon, Feb 27, 2006 at 03:38:23PM -0500, Mark Woodward wrote: Maybe I'm too used to working in engineering groups. I am trying to get input for a project. Trying to iron out what the feature set should be and the objectives that should be attained. BEFORE I start coding. Well yes, the problem is that what's been suggested so far doesn't provide much to give feedback on. It needs to be much more worked out. Just saying submit a patch is the antithesis to good engineering, it works for hacking, but if I am going to develop a feature, I wish to do it right and have it appeal to the broadest possible audience, collect as much input about the needs of users, etc. That works, but only as long as it's something a lot of people care about. This isn't, so until you (or somebody) comes up with a fairly complete proposal as to how it should interact with the rest of the system, it's hard to get/give feedback. Sorry, that's the way it works sometimes. Maybe it is that the whiteboard engineering discussion process doesn't translate well to this medium. Yep. the turnaround time is so high and the amount of communication so low that you pretty much have to submit huge chunks at a time to get any meaningful work done. The quick turnaround you get on a whiteboard simply doesn't exist. Don't take it personally. One effect of this system is the first-mover advantage. The first person to implement gets the biggest say in the final result. Have a ncie day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Scrollable cursors and Sort performance
On Mon, Feb 27, 2006 at 02:17:23PM +, Simon Riggs wrote: Interesting point: how should EXPLAIN ANALYZE set these bits? For its own purposes it need not request random access, but it might be interesting to make it possible to examine both the random and nonrandom behaviors, now that these will be significantly different performancewise. Possibly we could make EXPLAIN ANALYZE EXECUTE set the random-access bits. Good point. Whichever we do will be wrong in some cases I've no real opinion on this other than a vague preference for it to be quick. Wouldn't an EXPLAIN ANALYZE DECLARE ... have the right information to know if backward scan, etc was needed? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] Scrollable cursors and Sort performance
Jim C. Nasby [EMAIL PROTECTED] writes: On Mon, Feb 27, 2006 at 02:17:23PM +, Simon Riggs wrote: Possibly we could make EXPLAIN ANALYZE EXECUTE set the random-access bits. Good point. Whichever we do will be wrong in some cases I've no real opinion on this other than a vague preference for it to be quick. Wouldn't an EXPLAIN ANALYZE DECLARE ... have the right information to know if backward scan, etc was needed? There is no EXPLAIN ANALYZE DECLARE, and AFAICS it would be a contradiction in terms to have one, since DECLARE doesn't run the query. Perhaps the correct addition would be EXPLAIN ANALYZE FETCH. (EXECUTE is unrelated, now that I think harder about it.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Scrollable cursors and Sort performance
On Mon, Feb 27, 2006 at 06:01:21PM -0500, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Mon, Feb 27, 2006 at 02:17:23PM +, Simon Riggs wrote: Possibly we could make EXPLAIN ANALYZE EXECUTE set the random-access bits. Good point. Whichever we do will be wrong in some cases I've no real opinion on this other than a vague preference for it to be quick. Wouldn't an EXPLAIN ANALYZE DECLARE ... have the right information to know if backward scan, etc was needed? There is no EXPLAIN ANALYZE DECLARE, and AFAICS it would be a contradiction in terms to have one, since DECLARE doesn't run the query. Perhaps the correct addition would be EXPLAIN ANALYZE FETCH. (EXECUTE is unrelated, now that I think harder about it.) You have no idea how glad I am that I'm not the only one who doesn't know about 'new' features (this first appeared in the docs in 7.4)... :) decibel=# explain analyze declare test cursor for select * from pg_users; QUERY PLAN -- Seq Scan on pg_authid (cost=0.00..1.01 rows=1 width=79) Filter: rolcanlogin (2 rows) So, since presumably that accepts a full cursor declaration, would that suffice for controlling EXPLAIN ANALYZE? BTW, ISTM that it would also be useful to have EXPLAIN FETCH, since you could have already defined a cursor. But I suspect a more common case would be cut paste of the declare from application code into psql, which would make EXPLAIN DECLARE easier to use. Though, I never really use cursors, so... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Scrollable cursors and Sort performance
Jim C. Nasby [EMAIL PROTECTED] writes: On Mon, Feb 27, 2006 at 06:01:21PM -0500, Tom Lane wrote: There is no EXPLAIN ANALYZE DECLARE, and AFAICS it would be a contradiction in terms to have one, since DECLARE doesn't run the query. You have no idea how glad I am that I'm not the only one who doesn't know about 'new' features (this first appeared in the docs in 7.4)... :) decibel=# explain analyze declare test cursor for select * from pg_users; QUERY PLAN -- Seq Scan on pg_authid (cost=0.00..1.01 rows=1 width=79) Filter: rolcanlogin (2 rows) Please notice that it didn't run the query (no actual-time data). Perhaps it would be better if the code raised an error instead of silently ignoring the ANALYZE keyword. I think this behavior was chosen on the grounds that since DECLARE doesn't run the query, it's consistent for EXPLAIN ANALYZE DECLARE to be a no-op as well. But it's confusing now that I look at it again. In any case, one should clearly need to say FETCH to get a cursor to execute. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Dead Space Map
On Mon, Feb 27, 2006 at 01:17:36PM -0500, Tom Lane wrote: * vacuuming pages one by one as they're written by bgwriter That's not happening. VACUUM has to be a transaction and the bgwriter does not run transactions; nor is it in any position to clean out index entries associated with a heap page. (To change this would at a minimum require instituting a separate bgwriter process per database; or else a wholesale rewrite of our catalog access infrastructure to allow it to work in a non-database-specific context. There are also interesting deadlock problems to think about if the bgwriter can be blocked by other transactions, or if it needs to read pages not currently in shared memory.) Or there could be a seperate daemon that isn't associated with bgwriter. AFAIK as long as it vacuums the dirty page before bgwrite wants to write it you'd still get the IO benefit. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Dead Space Map
On Mon, Feb 27, 2006 at 03:05:41PM -0500, Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: On Mon, 27 Feb 2006, Tom Lane wrote: This strikes me as a fairly bad idea, because it makes VACUUM dependent on correct functioning of user-written code --- consider a functional index involving a user-written function that was claimed to be immutable and is not. If the user-defined function is broken, you're in more or less trouble anyway. Less. A non-immutable function might result in lookup failures (not finding the row you need) but not in database corruption, which is what would ensue if VACUUM fails to remove an index tuple. The index entry would eventually point to a wrong table entry, after the table item slot gets recycled for another tuple. Is there some (small) metadata that could be stored in the index to protect against this, perhaps XID? Granted, it's another 4 bytes, but it would only need to be in functional indexes. And there could still be a means to turn it off, if you're 100% certain that the function is immutable. lower() is probably the biggest use-case here... Moreover, you haven't pointed to any strong reason to adopt this methodology. It'd only be a win when vacuuming pretty small numbers of tuples, which is not the design center for VACUUM, and isn't likely to be the case in practice either if you're using autovacuum. If you're removing say 1% of the tuples, you are likely to be hitting every index page to do it, meaning that the scan approach will be significantly *more* efficient than retail lookups. The use case is any large table that sees updates in 'hot spots'. Anything that's based on current time is a likely candidate, since often most activity only concerns the past few days of data. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] In case nobody has seen this survey from Sun ...
Just got posted to the FreeBSD list ... has several questions that revolve around the BSD vs GPL licensing, and somewhere that 'omit' PostgreSQL as an OS option (while others include it) ... http://enews.sun.com/CTServlet?id=103018442-968290480:1141071714252 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....
Mark, My frustration level often kills any desire to contribute to open source. Sometimes, I think that open source is doomed. The various projects I track and use are very frustrating, they remind me of dysfunctional engineering departments in huge companies, it is very hard to positively discuss any new ideas. The first response is always some variation on no. Well, if you weren't a regular I'd be more encouraging. But you already know how things work here, so we can give you a hard time.I'll point out the year-long argument over the newsysviews for the contributors, the two-year long process for 2PC, etc. Sometimes you can just throw stuff up on this forum and people will say wow, cool, I could really use something that does that. and you're off and flying. Other times, though, it's hard for people on this list to see how a vague proposal would be useful, so you need to have a prototype that *shows* how useful it is. As a parallel, I personally didn't see the utility of DTrace until I saw Brian Cantrell give a demo of the full feature set. If you just read the spec, it's hard to see how it's an improvement over oprofile. Maybe it is that the whiteboard engineering discussion process doesn't translate well to this medium. Nope. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(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] possible design bug with PQescapeString()
FYI I have sent an email to cores to ask if I am OK to bring another but closely related to this issue to open discussions, whose details have already been sent to them. The reason why I'm asking is, if this issue could be open, then the issue might be open too and that makes discussions easier. At this point, I get no response from them so far. -- Tatsuo Ishii SRA OSS, Inc. Japan Tatsuo Ishii [EMAIL PROTECTED] writes: I guess I understand whay you are saying. However, I am not allowed to talk to you about it unless cores allow me. Probably we need some closed forum to discuss this kind of security issues. Considering that you've already described the problem on pgsql-hackers, I hardly see how further discussion is going to create a bigger security breach than already exists. (I'm of the opinion that the problem is mostly a client problem anyway; AFAICS the issue only comes up if client software fails to consider encoding issues while doing escaping. There is certainly no way that we can magically solve the problem in a new PG release, and so trying to keep it quiet until we can work out a solution seems pointless.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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] Scanning for insert
On Mon, Feb 27, 2006 at 05:44:20PM -0500, Tom Lane wrote: Is this really a good idea? The fundamental problem I see with it is that it takes away the index AM's ability to make any guarantees about its locking behavior, ie, how long locks will be held or what other operations might intervene while holding them. Yeah, I saw that as well. :( My only thoughts on that issue so far have been that the user must tread carefully while holding these scans; it becomes the user's responsibility. Weak at best, perhaps, so I can understand if that does not move you or anyone else into thinking positively of these proposed interfaces. ;) It'll also require the AM to save potentially large amounts of state between the two calls (eg, an entire search path might be needed for GiST). Currently any such state can live on the stack in local variables, but that won't work if it has to be remembered until a later AM call. Hrm, certainly, implementing these methods for AMs that use such state keeping techniques may be extra difficult. Massive changes may be necessary. However, I don't imagine that making such a change would be impossible, nor would it necessarily be required at all. Similar to uniqueness, the index need not be forced to implement these new interfaces--surely not upon this patch's introduction into the source(if/when). If a feature were to demand the use of scan insertions on a specific index that does not provide the interfaces, it could easily raise an informative exception about the shortcoming. Hehe, perhaps a Truly Foolish Notion, but could we use siglongjmp for such cases(state living on the stack)? Seems questionable and evil, tho; I know. ;) Lastly, what happens if the caller loses control (due to elog) and never makes the followup AM call? I imagine/hope it would get cleaned up similar to how nbtree gets cleaned up at the end of the transaction that was rolled back due to a unique constraint violation. [I seem to recall having to do some special _bt_wrtbuf to get my experimental insert unless patch working, so I guess that at eox some cleanup is done w.r.t. to those locks and whatnot. (yeah, technical term, whatnot ;)] Additionally, if the caller is very concerned with potential exceptions, perhaps a PG_TRY() block should be exercised in those areas of worry. I guess in most cases it simply comes back to becoming the scan's user's responsibility to be sure to keep things kosher. :\ If that's what you want, maybe a better answer is to simply allow aminsert to return a uniqueness-conflict indication, rather than raising the ereport for itself. Maybe so. However, I guess I was thinking along lines that many times multiple insert scans may need to be done before the final decision to actually do the insertion is made(yeah, uh, the insert unless thing is what I've had in mind ;). Also, I think part of this point is to be able to avoid the actual insertion into heap, so as to avoid a superfluous heap_insert heap_delete, and other unwinding code if a uniqueness-conflict indication were made to a user that needs to respond to such signals for the feature being implemented. (The more unique constraint violations that occur, the more I/O that gets saved with insert scans. I imagine this could be a very good thing for certain applications.) -- Regards, James William Pye ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....
Mark Woodward wrote: Mark, Well, I'm sure that one could use debian's solution, but that's the problem, it isn't PostgreSQL's solution. Shouldn't PostgreSQL provide the mechanisms? Will debian support FreeBSD? NetBSD? Is it in the PostgreSQL admin manual? We are talking about a feature, like pg_service.conf, now that people notice it, we are saying WOW, this is the API we should push. This is a functionality, IMHO, must be the responsibility of PostgreSQL. Then stop talking about it and write a patch. So far, you've failed to convince anyone else on this list that the functionality you suggest is actually useful for anyone other that you, personally. The only way you're going to do so is to put up some code somewhere other people can use it and prove that it's useful. Maybe I'm too used to working in engineering groups. I am trying to get input for a project. Trying to iron out what the feature set should be and the objectives that should be attained. BEFORE I start coding. Just saying submit a patch is the antithesis to good engineering, it works for hacking, but if I am going to develop a feature, I wish to do it right and have it appeal to the broadest possible audience, collect as much input about the needs of users, etc. You are 100% right here. Talking about it first is usually the best policy. One question I have is how this feature would be an improvement over just pointing pg_ctl at a postgresql.conf configuration file. That config file has the ability to specify most if not all server parameters. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.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
[HACKERS] Vacuum dead tuples that are between transactions
The topic of improving vacuum for use in heavy-update environments seems to come up frequently on the list. Has anyone weighed the costs of allowing VACUUM to reclaim tuples that are not older than the oldest transaction but are nonetheless invisible to all running transactions? It seems that it's not that hard Currently, a tuple is not elligible to be reclaimed by vacuum unless it was deleted by a transaction that committed before the oldest currently running transaction committed. (i.e., it's xmax is known to have committed before the oldest-currently-running xid was started.) Right? However, it seems like under certain scenarios (heavy updates to small tables while a long-running transaction is occurring) there might be a lot of tuples that are invisible to all transactions but not able to be vacuumed under the current method. Example: updating a single row over and over again while pg_dump is running. Suppose that in the system, we have a serializable transaction with xid 1000 and a read committed transaction with xid 1001. Other than these two, the oldest running xid is 2000. Suppose we consider a tuple with xmin 1200 and xmax 1201. We will assume that xid 1201 committed before xid 2000 began to run. So: (A) This tuple is invisible to the serializable transaction, since its snapshot can't ever advance. (B) The read committed transaction might be able to see it. However, if its current command started AFTER xid 1201 committed, it can't. Unless I'm missing something, it seems that when vacuuming you can leave serializable transactions (like pg_dump) out of the calculation of the oldest running transaction so long as you keep a list of them and check each tuple T against each serializable transaction X to make sure that T's xmin is greater than X, or else T's xmax committed before X started to run. Of course this is a lot of work, but this should mitigate the effect of long running serializable transactions until such time as processor power becomes your limiting factor. The read committed ones are a more difficult matter, but I think you can treat a tuple as dead if it was inserted after the read committed transaction started to run AND the tuple was deleted before the transaction's currently running command started to run. I suppose the major difficulty here is that currently a transaction has no way of knowing when another backend's command started to run? Is this too difficult to do or is it a good idea that no one has enough 'round tuits for? Regards, Paul Tillotson ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....
Mark Woodward [EMAIL PROTECTED] writes: My frustration level often kills any desire to contribute to open source. Sometimes, I think that open source is doomed. The various projects I track and use are very frustrating, they remind me of dysfunctional engineering departments in huge companies, it is very hard to positively discuss any new ideas. The first response is always some variation on no. Well, at least for PG the process has to be biased towards no, because we have to keep the code reliable and maintainable. If we bias in the direction of throwing in every little feature someone thinks up, we'll soon have a buggy, incomprehensible mess. I would submit that there is an intermediate state, and perhaps the medium is too binary, where someone says Lets send a man to Jupiter, here's why Before dismissing it out of hand, one tries to understand the reasons why, and sugest how to get there or alternate destinations. Not just say, I don't want to go to jupiter. FWIW, the proposal as it seems to have evolved (config file separate from pg_service and known only to pg_ctl) doesn't seem too unreasonable to me. I might have some use for it personally, if the implementation is capable of launching back-version postmasters as well as current-version. This is what I'm talking about, this was constructive, and while I wouldn't have thought of it, I think having something like POSTMASTER=/usr/local/pg7.4/bin/postmaster, while not something I would personally use, may apply to other users. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....
Mark Woodward wrote: Mark, Well, I'm sure that one could use debian's solution, but that's the problem, it isn't PostgreSQL's solution. Shouldn't PostgreSQL provide the mechanisms? Will debian support FreeBSD? NetBSD? Is it in the PostgreSQL admin manual? We are talking about a feature, like pg_service.conf, now that people notice it, we are saying WOW, this is the API we should push. This is a functionality, IMHO, must be the responsibility of PostgreSQL. Then stop talking about it and write a patch. So far, you've failed to convince anyone else on this list that the functionality you suggest is actually useful for anyone other that you, personally. The only way you're going to do so is to put up some code somewhere other people can use it and prove that it's useful. Maybe I'm too used to working in engineering groups. I am trying to get input for a project. Trying to iron out what the feature set should be and the objectives that should be attained. BEFORE I start coding. Just saying submit a patch is the antithesis to good engineering, it works for hacking, but if I am going to develop a feature, I wish to do it right and have it appeal to the broadest possible audience, collect as much input about the needs of users, etc. You are 100% right here. Talking about it first is usually the best policy. Thanks! One question I have is how this feature would be an improvement over just pointing pg_ctl at a postgresql.conf configuration file. That config file has the ability to specify most if not all server parameters. Like I have repeated a number of times, sometimes, there is more than one database cluster on a machine. The proposed pg_clusters.conf, could look like this: pg_clusters.conf [GEO] DATADIR=/vol01/pggeo PORT=5435 [ICDMDB] DATADIR=/vol01/pgicdmdb PORT=5434 [TOMLANE] DATADIR=/vol03/pg74 PORT=5433 POSTMASTER=/usr/local/pg7.4.1/bin/postmaster [POSTMASTER] DATADIR=/vol02/pg90 PORT=5432 # Virtual target starts all? [ALL] DB0=GEO DB1=ICDMDB DB2=TOMLANE pg_ctl start (Finds and starts the POSTMASTER entry) pg_ctl -S ICDMDB start (Starts the ICDMDB cluster) pg_ctl startall or pg_ctl -S [*|all] start or pg_ctl startall Or maybe even start will start a virtual target ALL ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_config, pg_service.conf, postgresql.conf ....
I don't see how this is much better than just pointing to different configuration file for each postmaster. --- Mark Woodward wrote: One question I have is how this feature would be an improvement over just pointing pg_ctl at a postgresql.conf configuration file. That config file has the ability to specify most if not all server parameters. Like I have repeated a number of times, sometimes, there is more than one database cluster on a machine. The proposed pg_clusters.conf, could look like this: pg_clusters.conf [GEO] DATADIR=/vol01/pggeo PORT=5435 [ICDMDB] DATADIR=/vol01/pgicdmdb PORT=5434 [TOMLANE] DATADIR=/vol03/pg74 PORT=5433 POSTMASTER=/usr/local/pg7.4.1/bin/postmaster [POSTMASTER] DATADIR=/vol02/pg90 PORT=5432 # Virtual target starts all? [ALL] DB0=GEO DB1=ICDMDB DB2=TOMLANE pg_ctl start (Finds and starts the POSTMASTER entry) pg_ctl -S ICDMDB start (Starts the ICDMDB cluster) pg_ctl startall or pg_ctl -S [*|all] start or pg_ctl startall Or maybe even start will start a virtual target ALL ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] character encoding in StartupMessage
I could not find anything in the Frontend/Backend protocol docs about character encoding in the StartupMessage. Assuming it is legal for a database or user name to have unicode characters, how is this handled when nothing yet has been said about the client encoding? Thanks, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] character encoding in StartupMessage
I could not find anything in the Frontend/Backend protocol docs about character encoding in the StartupMessage. Assuming it is legal for a database or user name to have unicode characters, how is this handled when nothing yet has been said about the client encoding? A similar badness is that if you issue CREATE DATABASE from a UTF8 database, the dbname will be stored as UTF8. Then, if you go to a LATIN1 database and create another it will be stored as LATIN1. Then, it's impossible to display both database names on the same screen or webpage as they have different encodings... Not only that but it's impossible to know what encoding it IS in since it's the encoding of the database from where you issued the CREATE DATABASE instruction from, not the encoding of the database itself. Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Dead Space Map
Jim C. Nasby [EMAIL PROTECTED] writes: On Mon, Feb 27, 2006 at 03:05:41PM -0500, Tom Lane wrote: Moreover, you haven't pointed to any strong reason to adopt this methodology. It'd only be a win when vacuuming pretty small numbers of tuples, which is not the design center for VACUUM, and isn't likely to be the case in practice either if you're using autovacuum. If you're removing say 1% of the tuples, you are likely to be hitting every index page to do it, meaning that the scan approach will be significantly *more* efficient than retail lookups. The use case is any large table that sees updates in 'hot spots'. Anything that's based on current time is a likely candidate, since often most activity only concerns the past few days of data. I'm unmoved by that argument too. If the updates are clustered then another effect kicks in: the existing btbulkdelete approach is able to collapse all the deletions on a given index page into one WAL record. With retail deletes it'd be difficult if not impossible to do that, resulting in a significant increase in WAL traffic during a vacuum. (We know it's significant because we saw a good improvement when we fixed btbulkdelete to work that way, instead of issuing a separate WAL record per deleted index entry as it once did.) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Dead Space Map
Tom Lane [EMAIL PROTECTED] writes: Heikki Linnakangas [EMAIL PROTECTED] writes: * implementation of index-only scans An index scan would not have to check the visibility information of heap tuples on those heap pages that are marked as clean in the dead space map. This requires that the dead space map is implemented so that a page is reliably marked as dirty in all circumstances when it contains any tuples that are not visible to all backends. The reliably part of this is likely to make it a non-starter. Another problem is that the semantics needed by this are not quite the same as the semantics of whether a page needs to be visited by vacuum. It would be very disappointing if this part doesn't turn out to be possible. I had always thought the semantics were the same, but now I'm realizing that vacuum doesn't need to visit tuples that have been committed even if they're not visible to some transaction. So having a vacuum can ignore this bit doesn't help you with index scans. But I think the thought process went the other direction. If you have the bit intended for index scans indicating that the tuple is not in doubt ie, it's visible to every transaction, then that also implies the tuple doesn't need to be visited by vacuum. Skipping pages that don't contain any in doubt tuples would be a huge win. Even if there might be some additional pages that vacuum could in theory be skipping too. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Vacuum dead tuples that are between transactions
Paul Tillotson [EMAIL PROTECTED] writes: The topic of improving vacuum for use in heavy-update environments seems to come up frequently on the list. Has anyone weighed the costs of allowing VACUUM to reclaim tuples that are not older than the oldest transaction but are nonetheless invisible to all running transactions? It seems that it's not that hard It's not that easy either --- you are assuming that every process advertises far more of its internal state than it actually does. Suppose that in the system, we have a serializable transaction with xid 1000 and a read committed transaction with xid 1001. Other than these two, the oldest running xid is 2000. Suppose we consider a tuple with xmin 1200 and xmax 1201. We will assume that xid 1201 committed before xid 2000 began to run. So: (A) This tuple is invisible to the serializable transaction, since its snapshot can't ever advance. Wrong --- you can't assume that simply from the transaction numbering, even assuming that you know that 1000 is serializable. 1000 might not have set its snapshot until quite some time after it started. (This is even pretty likely, if it waited for some locks before setting the snapshot.) You'd need access to the snapshot 1000 is actually using to be sure which later transactions are invisible to it. While advertising whole snapshots (rather than just xmin) in shared memory is at least theoretically possible, the costs of doing that seem nontrivial to me ... and they'd have to be paid whether any savings ensued or not. (B) The read committed transaction might be able to see it. However, if its current command started AFTER xid 1201 committed, it can't. Another issue is that there's not just one single snapshot to worry about per backend. Cursors for instance capture their own snaps. So a backend would have to somehow keep track of the oldest live snapshot it has internally. The read committed ones are a more difficult matter, but I think you can treat a tuple as dead if it was inserted after the read committed transaction started to run AND the tuple was deleted before the transaction's currently running command started to run. To do that requires not just that you have access to a backend's oldest snapshot, but that you have access to *all* its active snapshots; because such a transient tuple might be visible in some newer snap even though it's too new for the oldest snap. Doing that will create very significant problems of shared memory management, as well as performance and locking issues. There's been some talk of distinguishing global and within database xmin values, so that a long-lived transaction wouldn't interfere with vacuuming tables in other databases that that xact couldn't possibly access. That seems doable to me, but I think any finer-grained analysis is probably going to be a net loss because of the distributed overhead it'd impose on every transaction. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] character encoding in StartupMessage
Christopher Kings-Lynne [EMAIL PROTECTED] writes: I could not find anything in the Frontend/Backend protocol docs about character encoding in the StartupMessage. Assuming it is legal for a database or user name to have unicode characters, how is this handled when nothing yet has been said about the client encoding? A similar badness is that if you issue CREATE DATABASE from a UTF8 database, the dbname will be stored as UTF8. Then, if you go to a LATIN1 database and create another it will be stored as LATIN1. Yeah, this has been discussed before. Database and user names both have this affliction. I don't see any very nice solution at the moment. Once we get support for per-column locales, it might be possible to declare that the shared catalogs are always in UTF8 encoding and get the necessary conversions to happen automatically. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] ipcclean in 8.1 broken?
I just tried using ipcclean in 8.1.3. It doesn't work when I su to the pgsql user. This part of the script: if [ $USER = 'root' -o $LOGNAME = 'root' ] Always fails because even tho $USER is set to 'pgsql' when su'ed, $LOGNAME is still root. This is on FreeBSD 4.9 Chris ---(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] character encoding in StartupMessage
I don't see any very nice solution at the moment. Once we get support for per-column locales, it might be possible to declare that the shared catalogs are always in UTF8 encoding and get the necessary conversions to happen automatically. At the very least, could we always convert dbnames and store them as their own encoding? That way at least in HTML you can probably mark them out as having particular encodings or something... Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Dead Space Map
Ühel kenal päeval, E, 2006-02-27 kell 13:17, kirjutas Tom Lane: Heikki Linnakangas [EMAIL PROTECTED] writes: Vacuum will need to be modified to use index lookups to find index tuples corresponding the dead heap tuples. Otherwise you have to scan through all the indexes anyway. This strikes me as a fairly bad idea, because it makes VACUUM dependent on correct functioning of user-written code --- consider a functional index involving a user-written function that was claimed to be immutable and is not. There are concurrency-safety issues too, I think, having to do with the way that btree ensures we don't delete any index tuple that some scan is stopped on. * vacuuming pages one by one as they're written by bgwriter That's not happening. VACUUM has to be a transaction WHY does vacuum need to be a tranasction ? I thought it was a programmer workload optimisation (aka. lazyness :) ) to require ordinary lazy vacuum to be in transaction. There is no fundamental reason, why vacuum needs to run in a transaction itselt. - Hannu ---(end of broadcast)--- TIP 6: explain analyze is your friend
