Re: [HACKERS] On-disk bitmap index patch
I think we do know, have you reviewed the results in the briefing? - Luke Sent from my GoodLink synchronized handheld (www.good.com) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 01:09 AM Eastern Standard Time To: Tom Lane Cc: Bruce Momjian; Jie Zhang; Hannu Krosing; Gavin Sherry; pgsql-hackers@postgresql.org; Luke Lonergan Subject:Re: [HACKERS] On-disk bitmap index patch On Tue, Jul 25, 2006 at 12:36:42AM -0400, Tom Lane wrote: [EMAIL PROTECTED] writes: Reading 1/4, for a larger table, has a good chance of being faster than reading 4/4 of the table. :-) Really? If you have to hit one tuple out of four, it's pretty much guaranteed that you will need to fetch every heap page. So using an index provides zero I/O savings on the heap side, and any fetches needed to read the index are pure cost. Now you have to demonstrate that the CPU costs involved in processing the index are significantly cheaper than the cost of just testing the WHERE qual at every heap tuple --- not a bet that's likely to win at a one-in-four ratio. Haha. Of course - but that's assuming uniform spread of the values. Next I would try clustering the table on the bitmap index... :-) My databases aren't as large as many of yours. Most or all of them will fit in 1 Gbytes of RAM. The I/O cost isn't substantial for these, but the WHERE clause might be. But yeah - we don't know. Waste of code or performance boost. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Units in postgresql.conf -- How to report?
Peter Eisentraut wrote: So assuming we allowed units in postgresql.conf, how would you report them with SHOW? 1. The way they were set (hard) 2. Without units (not user-friendly) 3. Always in base units (seconds or bytes) 4. The largest unit that gives an integer (4) seems the most reasonable to me in terms of interface and implementation. 4. would be the best option for human readers, but it would be a pain for a script that parses command output. Maybe 3. would be a good compromise. Yours, Laurenz Albe ---(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] Resurrecting per-page cleaner for btree
Tom Lane [EMAIL PROTECTED] wrote:
I think the only serious objection to this would be that it'd mean that
tuples that should have an index entry might not have one.
This can occurs when we do REINDEX after DELETE, because dead tuples are
excluded on REINDEX. So we cannot guarantee that all heap tuples have
corresponding index entries. Vacuumers should not suppose their existence.
But I see the change is confusable, too.
I'll add more comments.
# CREATE TABLE test (i int);
# INSERT INTO test SELECT generate_series(1, 1000);
# CREATE INDEX test_idx ON test (i);
# DELETE FROM test WHERE i % 10 = 0;
# REINDEX INDEX test_idx;
# SELECT tuple_count, dead_tuple_count FROM pgstattuple('test');
tuple_count | dead_tuple_count
-+--
900 | 100
# SELECT tuple_count, dead_tuple_count FROM pgstattuple('test_idx');
tuple_count | dead_tuple_count
-+--
900 |0
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[HACKERS] root/administartor user check option.
I would like to start a discussion about having an extra option in PG which makes possible to bypass (or not check) whether the current user has root/administrative privileges especially (or only) for Windows. The following is the situation: It would be great to be able to distribute PG as standalone database for an standalone application within Windows. Because of the security check within PG an extra Windows user must be created(postgres user). This is not always possible/wanted because of the Windows (XP) user profile settings on some operational domains. Network admins tend to lock most of the things for normal users on Windows/XP in most companies which disallows creating an extra user for PG and installing it as service on Windows clients Next to above most of the Windows users at home are local admins which is not accepted by PG (when starting up). In my case I need to distribute PG within the setup of a custom standalone application. This is done very easily with MS Access, MSDE, Firebird even with MySQL. I was thinking of an extra startup option or perhaps a compile time option in ./configure ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] why toast tables are not reindexed while clustering?
Thank you for your quick answering my question. Tom. Tom Lane wrote: SAKATA Tetsuo [EMAIL PROTECTED] writes: I'll be pleased if someone tell me the reason why when a table is clustered the toast table is not reindexed. It's not obvious that those two things should be related. You can reindex the toast table if you wish ... I have some thoughts. (1) If there are no significant reason why two things are related, we should reindex toast table while clustering. ( I searched PostgreSQL mailing list archive about this topic, only to fail finding significant discussion.) (2) If some reasons exist and it is not appropriate to reindex toast table, we describe it in the PostgreSQL documentation. (3) And then, giving some way to users that they create index(es) only on the toast table. Now, when we reindex the table after clustering it, we create all index(es) on the table that we cluster. So we create the same index(es) twice. best regards, Tetsuo. -- sakata.tetsuo _at_ oss.ntt.co.jp SAKATA, Tetsuo. Shinagawa Tokyo JAPAN. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] root/administartor user check option.
Am Dienstag, 25. Juli 2006 12:01 schrieb Gevik Babakhani: I would like to start a discussion about having an extra option in PG which makes possible to bypass (or not check) whether the current user has root/administrative privileges especially (or only) for Windows. This has been shot down many times before, and the arguments you are presenting are not new. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] root/administartor user check option.
This has been shot down many times before, and the arguments you are presenting are not new. Has there been a solution found for the arguments/issues. Is there any history about why at some point we decided to enforce the security option? ---(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
[HACKERS] 64-bit integers for GUC
ISTM that before long someone will want to use more than 2 GB for work_mem. Currently, you can't set more because it overflows the variable. I'm not sure a wholesale switch of GUC integers to 64 bit is the solution. Maybe changing some of the variables to reals would work. Comments? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] column-level privilege
Dear hackers, I am a PostgreSQL lover at China, I'd like to know when the column-level privilege can be added to a release version of PostgreSQL? and is there someone who is working on the problem? Thanks Best Regards. jkzhao 2006-07-25
Re: [HACKERS] root/administartor user check option.
Removing or disabling the test without removing some of the dangerous capabilities would be a major security hole. For example: postgres can deliver to any authenticated user the contents of any text file on the system that the database user can read. Do you want the responsibility of allowing that for any file the administrator can read? No, I thought not. Neither do we. True. This means that one just cannot copy over PG files and run the database without creating additional users and services. Just looking at how much windows standalone apps are being developed which potentially could use an embedded or light version of PG, I still think the option should be considered. Perhaps in a more restricted or striped-down version of PG. (PG Light or something). ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] root/administartor user check option.
Gevik Babakhani [EMAIL PROTECTED] writes: This has been shot down many times before, and the arguments you are presenting are not new. Has there been a solution found for the arguments/issues. Is there any history about why at some point we decided to enforce the security option? Read the list archives ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] root/administartor user check option.
Humm, perhaps it would be great learning curve for me to begin with something to be like a Embedded PG for Windows or PG Light for Windows...(Daydreaming now..) On Tue, 2006-07-25 at 08:26 -0400, Alvaro Herrera wrote: Gevik Babakhani wrote: Removing or disabling the test without removing some of the dangerous capabilities would be a major security hole. For example: postgres can deliver to any authenticated user the contents of any text file on the system that the database user can read. Do you want the responsibility of allowing that for any file the administrator can read? No, I thought not. Neither do we. True. This means that one just cannot copy over PG files and run the database without creating additional users and services. Just looking at how much windows standalone apps are being developed which potentially could use an embedded or light version of PG, I still think the option should be considered. Perhaps in a more restricted or striped-down version of PG. (PG Light or something). Postgres is BSD, so feel free to create and Insecure Postgres and distribute however you like. Note that pg_ctl contains code to be started as admin and drop the privileges early. That may be able to satisfy your requirements without being extremely insecure. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 64-bit integers for GUC
Am Dienstag, 25. Juli 2006 14:15 schrieb Tom Lane: Peter Eisentraut [EMAIL PROTECTED] writes: ISTM that before long someone will want to use more than 2 GB for work_mem. Currently, you can't set more because it overflows the variable. Yes you can, because the value is measured in KB. Right, so there is probably a bug in my patch ... Nevermind then. All the other options are OK with 32 bit ints. I'd be fairly worried about whether that wouldn't mean we fail completely on INT64_IS_BROKEN platforms ... I wonder whether platforms with INT64_IS_BROKEN can address more than 2GB of memory anyway. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Better name/syntax for online index creation
Am Dienstag, 25. Juli 2006 06:55 schrieb Bruce Momjian: Or maybe CREATE INDEX x ON tab NOLOCK Having the NOLOCK associated with the table name makes sense. Semantically, NOLOCK (or whatever word) seems most closely associated with CREATE INDEX, so having it as third word would make sense, and it would be quite easy to parse for psql. Strictly speaking, however, it would have to be NOLOCKLY in that case. :-) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] root/administartor user check option.
Gevik Babakhani wrote: Removing or disabling the test without removing some of the dangerous capabilities would be a major security hole. For example: postgres can deliver to any authenticated user the contents of any text file on the system that the database user can read. Do you want the responsibility of allowing that for any file the administrator can read? No, I thought not. Neither do we. True. This means that one just cannot copy over PG files and run the database without creating additional users and services. Just looking at how much windows standalone apps are being developed which potentially could use an embedded or light version of PG, I still think the option should be considered. Perhaps in a more restricted or striped-down version of PG. (PG Light or something). You need to start with a security audit to work out which capabilities need to be disabled. COPY to and from files would be one obvious area, loading user modules might be another. The point is that we have chosen to avoid a large set of problems by forbidding running with elevated privileges, and if you want to relax that you need to identify the members of that set of problems, in some fairly formal way. Frankly, if I were creating an app that needed an embedded db, I would probably not start with postgres. Sqlite was created just for this purpose. Ideally, for an embedded db you want to avoid the need for a server at all, if possible. That's never going to happen with postgres. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Forcing current WAL file to be archived
Where are we on these TODO items: o Allow point-in-time recovery to archive partially filled write-ahead logs [pitr] Currently only full WAL files are archived. This means that the most recent transactions aren't available for recovery in case of a disk failure. This could be triggered by a user command or a timer. o Automatically force archiving of partially-filled WAL files when pg_stop_backup() is called or the server is stopped Doing this will allow administrators to know more easily when the archive contains all the files needed for point-in-time recovery. http://archives.postgresql.org/pgsql-patches/2005-04/msg00121.php o Add reporting of the current WAL file, perhaps as part of partial log file archiving Seems they should be completed for 8.2. I have only a /contrib version for the last one. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] buildfarm web extensions - help wanted
I am looking for assistance in extending the buildfarm web app in two directions that need expertise that I lack. First, I want to build a search engine for the log files. I recently started storing these in a table, one row per log file, rather than as a single gzipped tar blob per run. This will cost some in storage (sorry, Joshua - it should be compressed anyway) but it should make display faster, and it also gives us the opportunity to build a search engine on the text. I am looking for someone who is familiar with building tsearch2-based web search engines, plus perl DBI and Template Toolkit to help me build this. Second, when we start getting pgbench results we will want them nicely plotted, so I am looking for someone who is adept at using dynamic graphing tools (GD? ) as well as (again) perl DBI and Template Toolkit) to help with this piece. If you have these skills and are willing to help, please email me. thanks andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 64-bit integers for GUC
Peter Eisentraut [EMAIL PROTECTED] writes: Am Dienstag, 25. Juli 2006 14:15 schrieb Tom Lane: I'd be fairly worried about whether that wouldn't mean we fail completely on INT64_IS_BROKEN platforms ... I wonder whether platforms with INT64_IS_BROKEN can address more than 2GB of memory anyway. No, surely they can't (on all machines we support, long is at least as wide as a pointer, cf Datum). I'm just worried about whether normal GUC behavior would work at all on such a machine. We've so far tried to preserve it works as long as you don't try to use values larger than 2G on such machines, and I'm not quite prepared to give that up. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Better name/syntax for online index creation
Peter Eisentraut wrote: Am Dienstag, 25. Juli 2006 06:55 schrieb Bruce Momjian: Or maybe CREATE INDEX x ON tab NOLOCK Having the NOLOCK associated with the table name makes sense. Semantically, NOLOCK (or whatever word) seems most closely associated with CREATE INDEX, so having it as third word would make sense, and it would be quite easy to parse for psql. Strictly speaking, however, it would have to be NOLOCKLY in that case. :-) We can use the same code we use in psql for tab completion to find a keyword in the line. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Forcing current WAL file to be archived
Bruce Momjian [EMAIL PROTECTED] writes: Where are we on these TODO items: o Allow point-in-time recovery to archive partially filled write-ahead logs [pitr] I believe we'd agreed that the necessary infrastructure for this is just a function to tell the current WAL segment name and offset. o Automatically force archiving of partially-filled WAL files when pg_stop_backup() is called or the server is stopped I see no need for that to be automatic. I'd vote for a simple function pg_finish_wal_segment() or something like that, which you call just after pg_stop_backup() if you want this behavior. Trying to tie it into pg_stop_backup() will only make things more complicated and less flexible. 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] Better name/syntax for online index creation
Semantically, NOLOCK (or whatever word) seems most closely associated with CREATE INDEX, so having it as third word would make sense, and it would be quite easy to parse for psql. Strictly speaking, however, it would have to be NOLOCKLY in that case. :-) In this case CREATE INDEX CONCURRENTLY ... sounds better to me, although the whole feature sounds nice any way you will finally call it ;-) Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] root/administartor user check option.
Gevik Babakhani wrote: This has been shot down many times before, and the arguments you are presenting are not new. Has there been a solution found for the arguments/issues. Is there any history about why at some point we decided to enforce the security option? There is nothing new about this. It it not a Windows specific requirement - we enforce it on all platforms and have long done so. Removing or disabling the test without removing some of the dangerous capabilities would be a major security hole. For example: postgres can deliver to any authenticated user the contents of any text file on the system that the database user can read. Do you want the responsibility of allowing that for any file the administrator can read? No, I thought not. Neither do we. Running Windows services as the admin user is just lazy and incompetent. The is no more polite word for it. And that goes for all services, not just postgres. The fact that it is a very widespread practice does not make it right - it does however tell you something about the level of security consciousness among both administrators and software developers in the Windows world. My understanding is that Microsoft now advises against this practice. Short answer: the solution lies in educating the lazy and incompetent users and administrators, not in introducing dangerous insecurity into postgres. cheers andrew ---(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] plPHP and plRuby
On Mon, Jul 17, 2006 at 10:45:23AM -0700, Neil Conway wrote: On Mon, 2006-07-17 at 10:11 -0700, Josh Berkus wrote: On the other hand, if we include PL/Perl, Tcl and Python but exclude Ruby from the main package we are effectively making a statement to Ruby users that their language is inferior in our consideration. Hardly -- no more so than not including JDBC and PL/Java in the main CVS is evidence that we're all Java haters. The fact that we include PL/Perl, PL/Python and PL/Tcl is more a matter of momentum/historical accident than an expression of preference, IMHO. External users will not know that, though; they will only see what is and isn't on the list of included PLs. It would be very easy for them to construe that as playing favorites. And to some extent they'd be right, just look at how much of these discussions have focused on how popular different languages are. Ultimately, I really think we need something akin to CPAN so that we don't have to bundle all kinds of stuff in the core package. In the meantime, adding PLs that we can is better than not, but we do need to be mindful of the impression it might leave on users. A page that lists the status of all PLs (specifically why they're not included if they're not) would be a good thing to have. -- 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Forcing current WAL file to be archived
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Where are we on these TODO items: o Allow point-in-time recovery to archive partially filled write-ahead logs [pitr] I believe we'd agreed that the necessary infrastructure for this is just a function to tell the current WAL segment name and offset. Yes, perhaps, though I can envision a GUC that does regularly partial archiving. I will add a question mark to the item. In fact, the description has more details: o Allow point-in-time recovery to archive partially filled write-ahead logs? [pitr] Currently only full WAL files are archived. This means that the most recent transactions aren't available for recovery in case of a disk failure. This could be triggered by a user command or a timer. o Automatically force archiving of partially-filled WAL files when pg_stop_backup() is called or the server is stopped I see no need for that to be automatic. I'd vote for a simple function pg_finish_wal_segment() or something like that, which you call just after pg_stop_backup() if you want this behavior. Trying to tie it into pg_stop_backup() will only make things more complicated and less flexible. I assumed we would have a function like pg_finish_wal_segment(), and server stop and stop_backup would call it too, the reason being, it would greatly simplify our documentation on how to use PITR if these were done automatically. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Forcing current WAL file to be archived
Bruce Momjian [EMAIL PROTECTED] writes: I assumed we would have a function like pg_finish_wal_segment(), and server stop and stop_backup would call it too, That idea is *exactly* what I'm objecting to. the reason being, it would greatly simplify our documentation on how to use PITR if these were done automatically. No it wouldn't, it'd just bloat the already excessive WAL volume. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Forcing current WAL file to be archived
Ühel kenal päeval, T, 2006-07-25 kell 10:51, kirjutas Bruce Momjian: Where are we on these TODO items: o Add reporting of the current WAL file, perhaps as part of partial log file archiving It would be nice to have a function that tells both filename and offset of current WAL file, so it would be possible to do live async streaming of up-to-subsecond changes without too much overhead. This could be used to solve all the above problems with some extra work on side of WAL-shipping framework. Marko Kreen thought he might also do some work on such a function, but he is on a vacation, so I'm not sure he will be able to get it done by feature freeze. It would be extra nice if postgres would nudge some external process on each WAL write via a signal or UDP packet (after write and before flush) so there would be virtually no delay between WAL write and notification, but just the function would also go a long way. Seems they should be completed for 8.2. I have only a /contrib version for the last one. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 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] Resurrecting per-page cleaner for btree
ITAGAKI Takahiro [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: I think the only serious objection to this would be that it'd mean that tuples that should have an index entry might not have one. This can occurs when we do REINDEX after DELETE, because dead tuples are excluded on REINDEX. Good point. So we already have that problem anyway ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: Remove hard-wired lists of timezone abbreviations in favor of
Michael Fuhr [EMAIL PROTECTED] writes: test= set timezone_abbreviations to 'India'; WARNING: time zone abbreviation ist is multiply defined DETAIL: Time zone file Default, line 282 conflicts with file India, line 11. ERROR: invalid value for parameter timezone_abbreviations: India Should the India file have @OVERRIDE? Ooops... will fix. Now that I think about it, those files all need a $PostgreSQL$ marker too. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] column-level privilege
Am Dienstag, 25. Juli 2006 09:13 schrieb jkzhao: I am a PostgreSQL lover at China, I'd like to know when the column-level privilege can be added to a release version of PostgreSQL? and is there someone who is working on the problem? It would certainly be a welcome feature, but there are no concrete plans for it yet. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Forcing current WAL file to be archived
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I assumed we would have a function like pg_finish_wal_segment(), and server stop and stop_backup would call it too, That idea is *exactly* what I'm objecting to. the reason being, it would greatly simplify our documentation on how to use PITR if these were done automatically. No it wouldn't, it'd just bloat the already excessive WAL volume. Well, it only would happen when you have PITR enabled. For example, if you do pg_stop_backup(), in what cases would you not also call pg_finish_wal_segment()? I can't think of one. Maybe the server restart case isn't necessary. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Better name/syntax for online index creation
Csaba Nagy [EMAIL PROTECTED] writes: Strictly speaking, however, it would have to be NOLOCKLY in that case. :-) In this case CREATE INDEX CONCURRENTLY ... sounds better to me, although the whole feature sounds nice any way you will finally call it ;-) That reads well to me too. We'd need to check whether it can be parsed without making CONCURRENTLY a fully-reserved word, but offhand I think it would work because ON is already a fully-reserved word ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Forcing current WAL file to be archived
Hannu Krosing wrote: ?hel kenal p?eval, T, 2006-07-25 kell 10:51, kirjutas Bruce Momjian: Where are we on these TODO items: o Add reporting of the current WAL file, perhaps as part of partial log file archiving It would be nice to have a function that tells both filename and offset of current WAL file, so it would be possible to do live async streaming of up-to-subsecond changes without too much overhead. OK, offset added to TODO item. What would the offset give us? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Forcing current WAL file to be archived
Bruce Momjian [EMAIL PROTECTED] writes: For example, if you do pg_stop_backup(), in what cases would you not also call pg_finish_wal_segment()? I can't think of one. I can't see why you would need to, unless your intention is not to run PITR at all but only to make a filesystem backup instead of using pg_dump. Normally you'd be running a continuing archival process and there's no particular need to force the current WAL segment off to archive at that exact instant. My point here is that forcing the current segment to archive is a function of whatever your continuous-archiving process is, and it's not necessarily tied to backups. We should not prejudge when people want that fairly-expensive function to be invoked. 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] column-level privilege
Peter Eisentraut [EMAIL PROTECTED] writes: Am Dienstag, 25. Juli 2006 09:13 schrieb jkzhao: I am a PostgreSQL lover at China, I'd like to know when the column-level privilege can be added to a release version of PostgreSQL? and is there someone who is working on the problem? It would certainly be a welcome feature, but there are no concrete plans for it yet. There was someone fooling with it awhile ago, but we've not heard from him lately --- IIRC he was last seen trying to deal with the fact that parts of the system assume pg_attribute tuples are fixed-width. Check the archives. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Forcing current WAL file to be archived
On Tue, 2006-07-25 at 11:20 -0400, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Where are we on these TODO items: o Allow point-in-time recovery to archive partially filled write-ahead logs [pitr] I believe we'd agreed that the necessary infrastructure for this is just a function to tell the current WAL segment name and offset. Yes, perhaps, though I can envision a GUC that does regularly partial archiving. I will add a question mark to the item. I was planning to add a new GUC archive_timeout (integer) = max # secs between log file switches -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Better name/syntax for online index creation
Ühel kenal päeval, T, 2006-07-25 kell 11:26, kirjutas Tom Lane:
Csaba Nagy [EMAIL PROTECTED] writes:
Strictly speaking, however, it would have to be NOLOCKLY in that case. :-)
In this case CREATE INDEX CONCURRENTLY ... sounds better to me, although
the whole feature sounds nice any way you will finally call it ;-)
That reads well to me too. We'd need to check whether it can be parsed
without making CONCURRENTLY a fully-reserved word, but offhand I think
it would work because ON is already a fully-reserved word ...
At some point we may add some other ops we start doing CONCURRENTLY,
like perhaps CLUSTER CONCURRENTLY or even ALTER TABLE CONCURRENTLY ADD
COLUMN x DEFAULT nextval('s'); and other table rewriting ops.
--
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia
Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com
---(end of broadcast)---
TIP 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] Forcing current WAL file to be archived
On Tue, 2006-07-25 at 11:07 -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Where are we on these TODO items: o Allow point-in-time recovery to archive partially filled write-ahead logs [pitr] I believe we'd agreed that the necessary infrastructure for this is just a function to tell the current WAL segment name and offset. Not that I remember. That was just a proposal for backpatching to 8.1/8.0 so that it would be easier to cope with PITR at those releases. o Automatically force archiving of partially-filled WAL files when pg_stop_backup() is called or the server is stopped I see no need for that to be automatic. I'd vote for a simple function pg_finish_wal_segment() or something like that, which you call just after pg_stop_backup() if you want this behavior. Trying to tie it into pg_stop_backup() will only make things more complicated and less flexible. Putting it into pg_stop_backup was what we previously agreed. Where is the loss of flexibility? I need to get this straight because I was actually intending to do this for 8.2, i.e. next few days. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] root/administartor user check option.
-Original Message- From: Gevik Babakhani [EMAIL PROTECTED] To: pgsql-hackers pgsql-hackers@postgresql.org Sent: 25/07/06 11:27 Subject: [HACKERS] root/administartor user check option. I would like to start a discussion about having an extra option in PG which makes possible to bypass (or not check) whether the current user has root/administrative privileges domains. 8.2 will run under an admin account. It sheds unwanted privileges at startup. /D ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Better name/syntax for online index creation
Tom Lane wrote: psql could actually tell these apart if it worked just a bit harder. CLUSTER with no arguments is the one case, CLUSTER with anything after it is the other. Not sure why we couldn't be bothered to get that right in psql the first time :-(. Should this go on the to-do list? Regards, Paul Bort ---(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] root/administartor user check option.
Gevik Babakhani wrote: Removing or disabling the test without removing some of the dangerous capabilities would be a major security hole. For example: postgres can deliver to any authenticated user the contents of any text file on the system that the database user can read. Do you want the responsibility of allowing that for any file the administrator can read? No, I thought not. Neither do we. True. This means that one just cannot copy over PG files and run the database without creating additional users and services. Just looking at how much windows standalone apps are being developed which potentially could use an embedded or light version of PG, I still think the option should be considered. Perhaps in a more restricted or striped-down version of PG. (PG Light or something). Postgres is BSD, so feel free to create and Insecure Postgres and distribute however you like. Note that pg_ctl contains code to be started as admin and drop the privileges early. That may be able to satisfy your requirements without being extremely insecure. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Forcing current WAL file to be archived
Ühel kenal päeval, T, 2006-07-25 kell 11:27, kirjutas Bruce Momjian: Hannu Krosing wrote: ?hel kenal p?eval, T, 2006-07-25 kell 10:51, kirjutas Bruce Momjian: Where are we on these TODO items: o Add reporting of the current WAL file, perhaps as part of partial log file archiving It would be nice to have a function that tells both filename and offset of current WAL file, so it would be possible to do live async streaming of up-to-subsecond changes without too much overhead. OK, offset added to TODO item. What would the offset give us? the offset returned by lseek() on the WAL file, that is the end of the part of the WAL file which has actually been written to. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 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] 64-bit integers for GUC
Peter Eisentraut [EMAIL PROTECTED] writes: ISTM that before long someone will want to use more than 2 GB for work_mem. Currently, you can't set more because it overflows the variable. Yes you can, because the value is measured in KB. Now, if you were to redefine it as being measured in bytes, you would have a backlash, because people already are using values above 2GB. I'm not sure a wholesale switch of GUC integers to 64 bit is the solution. I'd be fairly worried about whether that wouldn't mean we fail completely on INT64_IS_BROKEN platforms ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Forcing current WAL file to be archived
Hannu Krosing wrote: ?hel kenal p?eval, T, 2006-07-25 kell 11:27, kirjutas Bruce Momjian: Hannu Krosing wrote: ?hel kenal p?eval, T, 2006-07-25 kell 10:51, kirjutas Bruce Momjian: Where are we on these TODO items: o Add reporting of the current WAL file, perhaps as part of partial log file archiving It would be nice to have a function that tells both filename and offset of current WAL file, so it would be possible to do live async streaming of up-to-subsecond changes without too much overhead. OK, offset added to TODO item. What would the offset give us? the offset returned by lseek() on the WAL file, that is the end of the part of the WAL file which has actually been written to. Sorry, I was actually asking what use the offset would be to a user. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Forcing current WAL file to be archived
Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2006-07-25 at 11:07 -0400, Tom Lane wrote: I see no need for that to be automatic. I'd vote for a simple function pg_finish_wal_segment() or something like that, which you call just after pg_stop_backup() if you want this behavior. Trying to tie it into pg_stop_backup() will only make things more complicated and less flexible. Putting it into pg_stop_backup was what we previously agreed. Where is the loss of flexibility? I don't see why you think this function should be tied to making a backup. There are other reasons for wanting to force a WAL switch than that, and there are scenarios in which you don't need a WAL switch at the end of a backup. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Forcing current WAL file to be archived
On Tue, 2006-07-25 at 11:31 -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: For example, if you do pg_stop_backup(), in what cases would you not also call pg_finish_wal_segment()? I can't think of one. I can't see why you would need to, unless your intention is not to run PITR at all but only to make a filesystem backup instead of using pg_dump. If thats all you want you can set archive_command = 'echo %f %p /dev/null' Normally you'd be running a continuing archival process and there's no particular need to force the current WAL segment off to archive at that exact instant. That's exactly the point of contention. When we originally completed PITR we thought that was acceptable. It isn't and many people have stuck pins in effigies of me since then. :-/ My point here is that forcing the current segment to archive is a function of whatever your continuous-archiving process is, and it's not necessarily tied to backups. We should not prejudge when people want that fairly-expensive function to be invoked. The point is until that last WAL file is backed up, the whole backup is useless. It isn't good policy to have a backup's value be contingent on some future event. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Forcing current WAL file to be archived
Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2006-07-25 at 11:07 -0400, Tom Lane wrote: I see no need for that to be automatic. I'd vote for a simple function pg_finish_wal_segment() or something like that, which you call just after pg_stop_backup() if you want this behavior. Trying to tie it into pg_stop_backup() will only make things more complicated and less flexible. Putting it into pg_stop_backup was what we previously agreed. Where is the loss of flexibility? I don't see why you think this function should be tied to making a backup. There are other reasons for wanting to force a WAL switch than that, and there are scenarios in which you don't need a WAL Yes, that is why we would have a separate function too. switch at the end of a backup. Well, I figured if you just did a backup, you would want a switch in _most_ cases, and since you just did a backup, I figured an extra WAL file would be minimal additional overhead. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Forcing current WAL file to be archived
Simon Riggs [EMAIL PROTECTED] writes: I was planning to add a new GUC archive_timeout (integer) = max # secs between log file switches That's fine, but feature freeze is in a week and we don't even have the basic function for manually doing a log file switch. Let's get that done first and then think about automatic switches. Also, forcing another 16M of WAL out every few seconds is a pretty crude and inefficient way of making sure your hot standby server is up to date. As Hannu noted, an archiving script can do better than that if it can access the current WAL file name and offset. So we really want to get the function to return that info done as well. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Forcing current WAL file to be archived
Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I was planning to add a new GUC archive_timeout (integer) = max # secs between log file switches That's fine, but feature freeze is in a week and we don't even have the basic function for manually doing a log file switch. Let's get that done first and then think about automatic switches. Also, forcing another 16M of WAL out every few seconds is a pretty crude and inefficient way of making sure your hot standby server is up to date. As Hannu noted, an archiving script can do better than that if it can access the current WAL file name and offset. So we really want to get the function to return that info done as well. Agreed. One concern I have is that we have waited for Simon to complete this for 1.5 years, and now with a week left he is still working on it (or starting on it). I am wondering if someone else needs to take this on, because if Simon doesn't complete it in a week, we don't have it for 8.2, and we would then have to sit through another year of PITR complaints. :-( -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Forcing current WAL file to be archived
* Simon Riggs ([EMAIL PROTECTED]) wrote: On Tue, 2006-07-25 at 11:20 -0400, Bruce Momjian wrote: Yes, perhaps, though I can envision a GUC that does regularly partial archiving. I will add a question mark to the item. I was planning to add a new GUC archive_timeout (integer) = max # secs between log file switches I'd love to see both this GUC and the function itself make it into 8.2.. I'm tempted to agree with Bruce about running the wal-archive-function after pg_stop_backup(). The backup isn't any good without all the WALs which were used during the backup anyway (iirc) so I can't really think why you'd want any time at all between backup happening and backup actually usable. Also, compared to the backup itself I'd tend to doubt there would be much of a performance hit. It may be expensive compared to other regular queries/operations but an rsync across the entire database isn't exactly cheap. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Forcing current WAL file to be archived
Simon Riggs wrote: On Tue, 2006-07-25 at 11:31 -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: For example, if you do pg_stop_backup(), in what cases would you not also call pg_finish_wal_segment()? I can't think of one. I can't see why you would need to, unless your intention is not to run PITR at all but only to make a filesystem backup instead of using pg_dump. If thats all you want you can set archive_command = 'echo %f %p /dev/null' Uh, what good is a file system backup without the WAL files modified during the backup? Normally you'd be running a continuing archival process and there's no particular need to force the current WAL segment off to archive at that exact instant. That's exactly the point of contention. When we originally completed PITR we thought that was acceptable. It isn't and many people have stuck pins in effigies of me since then. :-/ My point here is that forcing the current segment to archive is a function of whatever your continuous-archiving process is, and it's not necessarily tied to backups. We should not prejudge when people want that fairly-expensive function to be invoked. The point is until that last WAL file is backed up, the whole backup is useless. It isn't good policy to have a backup's value be contingent on some future event. Good analysis. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Forcing current WAL file to be archived
OK, offset added to TODO item. What would the offset give us? The last offset could be remembered by the external program, and it only has to transfer from the last offset to the new one. It allows incremental streaming of the WAL files... of course the external program will be a lot more complex than the current shell scripts which can be used for WAL archiving... The problems I see with this is if in this case the normal postgres WAL archiving won't conflict with this streaming ? And if yes, wouldn't it be better to have a separate mechanism for the stream based archiving ? I mean what will happen if postgres successfully archives the WAL file and then moves it out of way before the streaming process finishes with it, the streaming process will have a problem... A few months ago I spent some time thinking about a solution where a WAL based standby could be built using only normal data base connections to the master server, and one of the ideas was to create a WAL subscription mechanism where the standby subscribes for getting WAL files, and updates it's subscription status with the last processed WAL file after each processed file. The master can then recycle the WAL files only after they were confirmed by all current subscriptions... and to avoid excessive WAL file bloat if a slave goes offline, the subscription could be canceled automatically if it gets too much behind. If this mechanism is in place, it would be also nice if the slave could ask for the WAL records to be streamed on a normal data base connection. The function which would do it could be smart enough to stream the current WAL file too up to the current offset and then wait for new records. The slave would invoke the function for each WAL file it needs to transfer, and then when finished it would update it's subscription status and continue with the next one. The streaming function should not update the subscription status as this way the slave can ask for the file again if something goes wrong with the transfer. The third thing needed to create a facility for one-connection-standby building is to be able to stream the OS files of the DB through a DB connection - I guess that can be done with a relatively simple C function... With all these things in place, a program could be written which would run on the standby machine and completely automatically set up the standby, only needing a simple connection string to the master... Cheers, Csaba. ---(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] Forcing current WAL file to be archived
OK, makes sense. That is much more sophisticated that I imagined. --- Csaba Nagy wrote: OK, offset added to TODO item. What would the offset give us? The last offset could be remembered by the external program, and it only has to transfer from the last offset to the new one. It allows incremental streaming of the WAL files... of course the external program will be a lot more complex than the current shell scripts which can be used for WAL archiving... The problems I see with this is if in this case the normal postgres WAL archiving won't conflict with this streaming ? And if yes, wouldn't it be better to have a separate mechanism for the stream based archiving ? I mean what will happen if postgres successfully archives the WAL file and then moves it out of way before the streaming process finishes with it, the streaming process will have a problem... A few months ago I spent some time thinking about a solution where a WAL based standby could be built using only normal data base connections to the master server, and one of the ideas was to create a WAL subscription mechanism where the standby subscribes for getting WAL files, and updates it's subscription status with the last processed WAL file after each processed file. The master can then recycle the WAL files only after they were confirmed by all current subscriptions... and to avoid excessive WAL file bloat if a slave goes offline, the subscription could be canceled automatically if it gets too much behind. If this mechanism is in place, it would be also nice if the slave could ask for the WAL records to be streamed on a normal data base connection. The function which would do it could be smart enough to stream the current WAL file too up to the current offset and then wait for new records. The slave would invoke the function for each WAL file it needs to transfer, and then when finished it would update it's subscription status and continue with the next one. The streaming function should not update the subscription status as this way the slave can ask for the file again if something goes wrong with the transfer. The third thing needed to create a facility for one-connection-standby building is to be able to stream the OS files of the DB through a DB connection - I guess that can be done with a relatively simple C function... With all these things in place, a program could be written which would run on the standby machine and completely automatically set up the standby, only needing a simple connection string to the master... Cheers, Csaba. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Forcing current WAL file to be archived
On Tue, 2006-07-25 at 11:53 -0400, Tom Lane wrote: That's fine, but feature freeze is in a week and we don't even have the basic function for manually doing a log file switch. Let's get that done first and then think about automatic switches. Agreed. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Forcing current WAL file to be archived
Ühel kenal päeval, T, 2006-07-25 kell 11:48, kirjutas Bruce Momjian: Hannu Krosing wrote: ?hel kenal p?eval, T, 2006-07-25 kell 11:27, kirjutas Bruce Momjian: Hannu Krosing wrote: ?hel kenal p?eval, T, 2006-07-25 kell 10:51, kirjutas Bruce Momjian: Where are we on these TODO items: o Add reporting of the current WAL file, perhaps as part of partial log file archiving It would be nice to have a function that tells both filename and offset of current WAL file, so it would be possible to do live async streaming of up-to-subsecond changes without too much overhead. OK, offset added to TODO item. What would the offset give us? the offset returned by lseek() on the WAL file, that is the end of the part of the WAL file which has actually been written to. Sorry, I was actually asking what use the offset would be to a user. There would be an external async process, which continuously polls the offset and pushes everything written between the polls to slave site. so when this process starts up it gets (file = wal1 and offset=1) and it sends first 1 bytes to slave site, at next rountd it gets (file = wal1 and offset=15000) and it sends bytes 10001-15000 to remote and so on. this way the slave has a lag no more than the poll interval in usable WAL data. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Forcing current WAL file to be archived
On Tue, 2006-07-25 at 11:45 -0400, Tom Lane wrote: there are scenarios in which you don't need a WAL switch at the end of a backup. My mind's blank today, so forgive me that I cannot see what that might be. Assuming such a case, would it be possible to have two functions? pg_stop_backup() pg_stop_backup(boolean); --parameter says log switch or not Most people use the existing parameter-less function, -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Forcing current WAL file to be archived
Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2006-07-25 at 11:31 -0400, Tom Lane wrote: My point here is that forcing the current segment to archive is a function of whatever your continuous-archiving process is, and it's not necessarily tied to backups. We should not prejudge when people want that fairly-expensive function to be invoked. The point is until that last WAL file is backed up, the whole backup is useless. It isn't good policy to have a backup's value be contingent on some future event. You are assuming here that the continuous archiving process is identical to the WAL part of the base-backup process. If what you want is an identifiable self-contained base backup then you copy off the WAL files along with the tar dump; there's no need to force a switch of the current WAL file before you copy it. I don't disagree that in many scenarios the switch is needful. What I'm saying is that we should provide a separately accessible function for it. PG's PITR support is basically designed as a toolkit that lets you build a PITR solution, not as do-everything, one-size-fits-all monolithic functionality, and I want to stay in that spirit. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Forcing current WAL file to be archived
Ühel kenal päeval, T, 2006-07-25 kell 17:52, kirjutas Csaba Nagy: OK, offset added to TODO item. What would the offset give us? The last offset could be remembered by the external program, and it only has to transfer from the last offset to the new one. It allows incremental streaming of the WAL files... of course the external program will be a lot more complex than the current shell scripts which can be used for WAL archiving... The problems I see with this is if in this case the normal postgres WAL archiving won't conflict with this streaming ? You are not forced to use it if your shell scripts do conflict. What I envisioned, was that the current WAL archiving shell script would just do some CRC check over the WAL's already shipped, or as we currently use rsync to do the actual shipping this is what happens automatically. And if yes, wouldn't it be better to have a separate mechanism for the stream based archiving ? why separate ? I'm a great believer in doing the minimum useful change, at least in systems used in production. We already have a working solution for full file shipping, so why not just augment it with streaming the currently-written-to file. I mean what will happen if postgres successfully archives the WAL file and then moves it out of way before the streaming process finishes with it, the streaming process will have a problem... This should not happen. your streaming process should be smart enought to guarantee that. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com NOTICE: This communication contains privileged or other confidential information. If you have received it in error, please advise the sender by reply email and immediately delete the message and any attachments without copying or disclosing the contents. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Forcing current WAL file to be archived
On Tue, 2006-07-25 at 11:57 -0400, Bruce Momjian wrote: Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I was planning to add a new GUC archive_timeout (integer) = max # secs between log file switches That's fine, but feature freeze is in a week and we don't even have the basic function for manually doing a log file switch. Let's get that done first and then think about automatic switches. Also, forcing another 16M of WAL out every few seconds is a pretty crude and inefficient way of making sure your hot standby server is up to date. As Hannu noted, an archiving script can do better than that if it can access the current WAL file name and offset. So we really want to get the function to return that info done as well. Agreed. One concern I have is that we have waited for Simon to complete this for 1.5 years, and now with a week left he is still working on it (or starting on it). Since we cannot agree even now on what should be done, you'll forgive me for not having completed it sooner, especially since you know more about my schedule now than others. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Patch for VS.Net 2005's strxfrm() bug
William ZHANG [EMAIL PROTECTED] writes: When I tried to compile pgsql-8.2devel with VS.Net 2005 and do regression tests, I found the problem. It's a bug inVS.Net 2005: http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=99694 So why don't you use the fixed version of VS? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] root/administartor user check option.
Dave Page wrote: -Original Message- From: Gevik Babakhani [EMAIL PROTECTED] To: pgsql-hackers pgsql-hackers@postgresql.org Sent: 25/07/06 11:27 Subject: [HACKERS] root/administartor user check option. I would like to start a discussion about having an extra option in PG which makes possible to bypass (or not check) whether the current user has root/administrative privileges domains. 8.2 will run under an admin account. It sheds unwanted privileges at startup. Which user does it switch to? I was under the impression that Gevik wanted not to have to create any extra users.Or does it abandon privileges without switching personality (if that's possible)? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plPHP and plRuby
Ultimately, I really think we need something akin to CPAN so that we don't have to bundle all kinds of stuff in the core package. In the meantime, adding PLs that we can is better than not, but we do need to be mindful of the impression it might leave on users. A page that lists the status of all PLs (specifically why they're not included if they're not) would be a good thing to have. I as a user think that there should be a clear distinction of what is a supported extension, and what is an unsupported extension . With 100 projects on pgfoundry, 150 or so on gborg, it is hard to tell which ones one can trust, and not everybody wants to beta-test on their production data (especially for things that touch the core engine directly). Maybe there should be a set of requirements fulfilling of which could get a project a special 'blessing' from the Postgresql community? Greetings Marcin Mank ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Forcing current WAL file to be archived
Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2006-07-25 at 11:31 -0400, Tom Lane wrote: My point here is that forcing the current segment to archive is a function of whatever your continuous-archiving process is, and it's not necessarily tied to backups. We should not prejudge when people want that fairly-expensive function to be invoked. The point is until that last WAL file is backed up, the whole backup is useless. It isn't good policy to have a backup's value be contingent on some future event. You are assuming here that the continuous archiving process is identical to the WAL part of the base-backup process. If what you want is an identifiable self-contained base backup then you copy off the WAL files along with the tar dump; there's no need to force a switch of the current WAL file before you copy it. If you are doing that, I think for consistency you would want a WAL file that is completely archived, rather than pulling the current one while it is being written to. I don't disagree that in many scenarios the switch is needful. What I'm saying is that we should provide a separately accessible function for it. PG's PITR support is basically designed as a toolkit that lets you build a PITR solution, not as do-everything, one-size-fits-all monolithic functionality, and I want to stay in that spirit. I don't think we want people wiring their own calculator. Sure we can give them wires and have them do it themselves, but if we can make it easier for 99% of the cases (with little downside), we should do it. PITR has become more of a toolkit only because the partial WAL file writes were not completed in the original implementation. PITR is hard enough --- we need to make it easier if possible. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Forcing current WAL file to be archived
The problems I see with this is if in this case the normal postgres WAL archiving won't conflict with this streaming ? You are not forced to use it if your shell scripts do conflict. What I envisioned, was that the current WAL archiving shell script would just do some CRC check over the WAL's already shipped, or as we currently use rsync to do the actual shipping this is what happens automatically. Hmm, that sounds pretty smart... the archive process rsyncing over the file which was previously streamed... I guess this will mean very little overhead (in fact it only means the WAL archive to be read once more than absolutely necessary, and the CPU power to compute the CRCs). And if yes, wouldn't it be better to have a separate mechanism for the stream based archiving ? why separate ? I'm a great believer in doing the minimum useful change, at least in systems used in production. We already have a working solution for full file shipping, so why not just augment it with streaming the currently-written-to file. That's good so, I also have a working script, so I'm also not very motivated to do anything more complicated... but 6 months ago I would have been really glad to have a stand-alone program which I could install along postgres on the slave, point it to the master, and get a working WAL shipping based stand-by. Instead I spent a few days setting up our standby scripts and testing it under load... and never being certain it really works and it won't break exactly when I need it most... I mean what will happen if postgres successfully archives the WAL file and then moves it out of way before the streaming process finishes with it, the streaming process will have a problem... This should not happen. your streaming process should be smart enought to guarantee that. OK, true, the streaming script should always stream only the current file. If the last offset was from a previous WAL, it can be safely reset to 0, and stream the new WAL from the beginning. So the streaming script needs to remember the last WAL and offset, not just the offset. Cheers, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Forcing current WAL file to be archived
Simon Riggs wrote: On Tue, 2006-07-25 at 11:57 -0400, Bruce Momjian wrote: Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I was planning to add a new GUC archive_timeout (integer) = max # secs between log file switches That's fine, but feature freeze is in a week and we don't even have the basic function for manually doing a log file switch. Let's get that done first and then think about automatic switches. Also, forcing another 16M of WAL out every few seconds is a pretty crude and inefficient way of making sure your hot standby server is up to date. As Hannu noted, an archiving script can do better than that if it can access the current WAL file name and offset. So we really want to get the function to return that info done as well. Agreed. One concern I have is that we have waited for Simon to complete this for 1.5 years, and now with a week left he is still working on it (or starting on it). Since we cannot agree even now on what should be done, you'll forgive me for not having completed it sooner, especially since you know more about my schedule now than others. It is not a blame issue. The issue is I would like this completed for 8.2 and I want to minimize the possibility it will not be done. I think we do know what we want done. It is just that we are not certain of the user interface. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Forcing current WAL file to be archived
Simon Riggs [EMAIL PROTECTED] writes: Assuming such a case, would it be possible to have two functions? pg_stop_backup() pg_stop_backup(boolean); --parameter says log switch or not Well, it seems everyone but me thinks that pg_stop_backup should force a WAL switch, so I'll yield on that point. But we still need the separate function too, so that people can manually force a WAL switch --- just the same as we still have a manual CHECKPOINT command. 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] [PATCHES] Patch for VS.Net 2005's strxfrm() bug
Hi.
William ZHANG [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]
When I tried to compile pgsql-8.2devel with VS.Net 2005 and do regression
tests,
I found the problem. It's a bug inVS.Net 2005:
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=99694
+ /*
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=99694
*/
+ #if _MSC_VER == 1400
+ {
+char x[1];
+
+xfrmlen = strxfrm(x, val, 0);
+ }
+ #else
xfrmlen = strxfrm(NULL, val, 0);
+ #endif
Hmm, It seems to be the bug of very unpleasant Microsoft.:D
I think that the following is desirable as an evasion measure to add.
#if defined(_MSC_VER) _MSC_VER == 1400
To be sure, it was only VS2005.
Regards,
Hiroshi Saito
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Forcing current WAL file to be archived
Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Assuming such a case, would it be possible to have two functions? pg_stop_backup() pg_stop_backup(boolean); --parameter says log switch or not Well, it seems everyone but me thinks that pg_stop_backup should force a WAL switch, so I'll yield on that point. But we still need the separate function too, so that people can manually force a WAL switch --- just the same as we still have a manual CHECKPOINT command. Agreed, with separate function too. No sense in limiting the toolkit, as you explained. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Loading the PL/pgSQL debugger (and other plugins)
On Wed, Jul 19, 2006 at 01:35:16PM -0400, Tom Lane wrote: 1) I think the most straightforward way to load an instrumentation plugin is to define a new custom GUC variable (using the custom_variable_classes mechanism). This seems a bit messy and special-purpose. I see no good reason to tie it to plpgsql; we'll just need another one for every other language. IMHO what we want is something with similar properties to preload_libraries, but processed on a per-backend basis instead of once at postmaster start. (You could almost just tell people to select the plugin they want by LOADing it, but that is hard to use if you're trying to debug a non-interactive application. A GUC variable can be set for an app without much cooperation from the app.) snip We should also think about a deregistration function. This would allow you to turn debugging on and off within an interactive session. The GUC variable is really only for coercing non-interactive applications into being debuggable --- I don't see it as being important for interactive debugging, as compared to just select plugin_init(); ... This isn't the only example of where it would be handy to be able to tell a certain backend or group of backends to do something, so you could gain more insight into what some application is doing. Turning on query logging is another example that comes to mind. Is there some way we could allow one backend to tell another backend to change certain aspects of its behavior? One idea is to have a function that can send commands to another backend via some form of IPC. That backend would then execute the commands the next time it would normally accept commands from it's client connection. Of course this creates a pretty big foot-gun, so we might want to greatly restrict what kind of commands could be executed this way. Another possibility would be allowing users to specify certain GUC settings for backends that match certain criteria when they're spawned, such as what IP the client is connecting from, or what user it's authenticating as. -- 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] Forcing current WAL file to be archived
Tom Lane wrote: The point is until that last WAL file is backed up, the whole backup is useless. It isn't good policy to have a backup's value be contingent on some future event. You are assuming here that the continuous archiving process is identical to the WAL part of the base-backup process. If what you want is an identifiable self-contained base backup then you copy off the WAL files along with the tar dump; there's no need to force a switch of the current WAL file before you copy it. I think you are right. I don't disagree that in many scenarios the switch is needful. What I'm saying is that we should provide a separately accessible function for it. PG's PITR support is basically designed as a toolkit that lets you build a PITR solution, not as do-everything, one-size-fits-all monolithic functionality, and I want to stay in that spirit. I agree that it is enough to have a separate pg_finish_wal_segment(). Adding that in your backup script between pg_stop_backup() and tarring of the archived WAL files would by a simple enough step. Yours, Laurenz Albe ---(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] [PATCHES] Patch for VS.Net 2005's strxfrm() bug
Hiroshi Saito wrote: Hmm, It seems to be the bug of very unpleasant Microsoft.:D I think that the following is desirable as an evasion measure to add. #if defined(_MSC_VER) _MSC_VER == 1400 To be sure, it was only VS2005. Why is this better than: #if _MSC_VER == 1400 Surely this will not be true if _MSC_VER is undefined? cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Loading the PL/pgSQL debugger (and other plugins)
Jim C. Nasby [EMAIL PROTECTED] writes: Another possibility would be allowing users to specify certain GUC settings for backends that match certain criteria when they're spawned, such as what IP the client is connecting from, or what user it's authenticating as. ALTER USER SET ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Forcing current WAL file to be archived
* Bruce Momjian ([EMAIL PROTECTED]) wrote: Tom Lane wrote: You are assuming here that the continuous archiving process is identical to the WAL part of the base-backup process. If what you want is an identifiable self-contained base backup then you copy off the WAL files along with the tar dump; there's no need to force a switch of the current WAL file before you copy it. If you are doing that, I think for consistency you would want a WAL file that is completely archived, rather than pulling the current one while it is being written to. I've never been terribly pleased with having to copy the current WAL while it's being written to. The setup we're using is basically: On the source system: pg_start_backup() rsync pg_stop_backup() Figure out the current WAL and do a fake archive of it On the backup server: Grab the start/end WAL logs of the backup Verify that all the WAL logs archived during the backup are available It sounds like I'd be changing do a fake-archive of the current WAL to call the archive_wal function. In either case I worry some about a possible race-condition or something going wrong which invalidates the backup. I think it would actually be really nice to have a 'verify_backup' tool which could be non-interactively run against a backup to check that the backup was successful. The one we hacked up really just checks that there are files available with the right names. Something more substantial than that (but without affecting the actual backup) would be really nice since it would improve confidence that the backup really can be restored from. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Freezing tuples on pages dirtied by vacuum
On Jul 21, 2006, at 9:03 AM, Tom Lane wrote: One possibility is that early freeze is at 1B transactions and we push forced-freeze back to 1.5B transactions (the current forced-freeze at 1B transactions seems rather aggresive anyway, now that the server will refuse to issue new commands rather than lose data due to wraparound). No, the freeze-at-1B rule is the maximum safe delay. Read the docs. But we could do early freeze at 0.5B and forced freeze at 1B and probably still get the effect you want. However, I remain unconvinced that this is a good idea. You'll be adding very real cycles to regular vacuum processing (to re-scan tuples already examined) in hopes of obtaining a later savings that is really pretty hypothetical. Where is your evidence that writes caused solely by tuple freezing are a performance issue? I didn't think vacuum would be a CPU-bound process, but is there any way to gather that evidence right now? What about adding some verbage to vacuum verbose that reports how many pages were dirtied to freeze tuples? It seems to be useful info to have, and would help establish if it's worth worrying about. -- 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] Help! - Slony-I - saving/setting/restoring GUC
[EMAIL PROTECTED] (Peter Eisentraut) writes: Chris Browne wrote: In support of PG 8.2, we need to have the log trigger function do the following: - Save value of standards_conforming_string - Set value of standards_conforming_string to FALSE - proceed with saving data to sl_log_? - Recover value of standards_conforming_string Would SET LOCAL help you? Not really. The log trigger function is an SPI function, and I don't think I want to be invoking an extra SQL request every time a tuple is updated. Consider our present handling of date localizations... Slony-I prefers to operate using ISO dates. So the log trigger function must force the datestyle correspondingly. So our code looks like... (eliding irrelevant code) int OldDateStyle = DateStyle; DateStyle = USE_ISO_DATES; /* code that generates data to stow in sl_log_n */ DateStyle = OldDateStyle; /* Retrieve user's local settings */ --- At one point, I thought that we'd modify this to: int OldDateStyle = DateStyle; int OldSCS = standards_conforming_strings; DateStyle = USE_ISO_DATES; standards_conforming_strings = FALSE; /* code that generates data to stow in sl_log_n */ DateStyle = OldDateStyle; /* Retrieve user's local settings */ standards_conforming_strings = OldSCS; Unfortunately (perhaps) standards_conforming_strings does not appear to be exported, so I'm not sure how to do this otherwise. -- (reverse (concatenate 'string gro.mca @ enworbbc)) http://www.ntlug.org/~cbbrowne/wp.html Editing is a rewording activity. -- Alan J. Perlis [And EMACS a rewording editor. Ed.] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] On-disk bitmap index patch
Luke Lonergan [EMAIL PROTECTED] writes: I think we do know, have you reviewed the results in the briefing? I find those results moderately unconvincing, primarily because they are based on choosing the least efficient possible data representation (viz char(n)), and thus the btree indexes suffer severe and quite artificial bloat. A database schema chosen with even minimal attention to PG-specific tuning would probably have btree indexes half the size. That wouldn't completely eliminate the performance differential shown, but it would bring it down into the ballpark where you have to question whether it makes sense to support another index AM. The reason I have such high sales resistance is that we've carried the hash and rtree AMs for years, hoping that someone would do the work to make them actually worth using, with little result. I don't want any more second-class-citizen index AMs, and that's why I'm questioning what the scope of applicability of bitmap indexes really is. They need to be popular enough that people will be motivated to work on them, or they shouldn't be in core. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Patch for VS.Net 2005's strxfrm() bug
From: Andrew Dunstan Hiroshi Saito wrote: Hmm, It seems to be the bug of very unpleasant Microsoft.:D I think that the following is desirable as an evasion measure to add. #if defined(_MSC_VER) _MSC_VER == 1400 To be sure, it was only VS2005. Why is this better than: #if _MSC_VER == 1400 Surely this will not be true if _MSC_VER is undefined? I experienced injustice and the reason of in OSX for it. Regards, Hiroshi Saito ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Help! - Slony-I - saving/setting/restoring GUC
Chris Browne [EMAIL PROTECTED] writes: Unfortunately (perhaps) standards_conforming_strings does not appear to be exported, so I'm not sure how to do this otherwise. Perhaps your problem is one of spelling? It's standard_conforming_strings, and it's certainly a global variable. You still haven't explained why you need this, though. There are no datatype output functions that examine this variable. 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] On-disk bitmap index patch
On Sun, Jul 23, 2006 at 05:35:37PM -0700, Luke Lonergan wrote: We presented them at the Postgres Anniversary summit talk (Bruce M. was there) and the reaction was let's get this into 8.2 because many people there (more than 5) really wanted to use it as a standard feature. A version of the slides with only the bitmap index results are located here: http://intranet.greenplum.com/bitmap-index-perf-ayush.ppt. 404 -- 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 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] Adding a pgbench run to buildfarm
On Sun, Jul 23, 2006 at 11:52:14PM -0400, Bort, Paul wrote: -hackers, With help from Andrew Dunstan, I'm adding the ability to do a pgbench run after all of the other tests during a buildfarm run. Andrew said I should solicit opinions as to what parameters to use. A cursory search through the archives led me to pick a scaling factor of 10, 5 users, and 100 transactions. All of these will be adjustable using the build-farm.conf mechanism already in place. Why is it being hard-coded? I think it makes a lot more sense to allow pg_bench options to be specified in the buildfarm config. Even better yet would be specifying them on the command line, which would allow members to run a more rigorous test once a day/week (I'm thinking one that might take 30 minutes, which could well ferret out some issues that a simple 5 minute test won't). -- 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Resurrecting per-page cleaner for btree
ITAGAKI Takahiro [EMAIL PROTECTED] writes: I think we can resurrect his idea because we will scan btree pages at-atime now; the missing-restarting-point problem went away. Have I missed something? Comments welcome. I was thinking for awhile just now that this would break the interlock that guarantees VACUUM can't delete a heap tuple that an indexscanning process is about to visit. After further thought, it doesn't, but it's non-obvious. I've added the attached commentary to nbtree/README: On-the-fly deletion of index tuples --- If a process visits a heap tuple and finds that it's dead and removable (ie, dead to all open transactions, not only that process), then we can return to the index and mark the corresponding index entry known dead, allowing subsequent index scans to skip visiting the heap tuple. The known dead marking uses the LP_DELETE bit in ItemIds. This is currently only done in plain indexscans, not bitmap scans, because only plain scans visit the heap and index in sync and so there's not a convenient way to do it for bitmap scans. Once an index tuple has been marked LP_DELETE it can actually be removed from the index immediately; since index scans only stop between pages, no scan can lose its place from such a deletion. We separate the steps because we allow LP_DELETE to be set with only a share lock (it's exactly like a hint bit for a heap tuple), but physically removing tuples requires exclusive lock. In the current code we try to remove LP_DELETE tuples when we are otherwise faced with having to split a page to do an insertion (and hence have exclusive lock on it already). This leaves the index in a state where it has no entry for a dead tuple that still exists in the heap. This is not a problem for the current implementation of VACUUM, but it could be a problem for anything that explicitly tries to find index entries for dead tuples. (However, the same situation is created by REINDEX, since it doesn't enter dead tuples into the index.) It's sufficient to have an exclusive lock on the index page, not a super-exclusive lock, to do deletion of LP_DELETE items. It might seem that this breaks the interlock between VACUUM and indexscans, but that is not so: as long as an indexscanning process has a pin on the page where the index item used to be, VACUUM cannot complete its btbulkdelete scan and so cannot remove the heap tuple. This is another reason why btbulkdelete has to get super-exclusive lock on every leaf page, not only the ones where it actually sees items to delete. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Adding a pgbench run to buildfarm
Jim Nasby wrote: Why is it being hard-coded? I think it makes a lot more sense to allow pg_bench options to be specified in the buildfarm config. Even better yet would be specifying them on the command line, which would allow members to run a more rigorous test once a day/week (I'm thinking one that might take 30 minutes, which could well ferret out some issues that a simple 5 minute test won't). They absolutely won't be hard-coded. I'm asking for values to use as defaults in the config file. Also allowing command-line parameters is interesting, but I think we should wait on it until the initial version is in place. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] On-disk bitmap index patch
Ühel kenal päeval, T, 2006-07-25 kell 12:49, kirjutas Jim C. Nasby: On Sun, Jul 23, 2006 at 05:35:37PM -0700, Luke Lonergan wrote: We presented them at the Postgres Anniversary summit talk (Bruce M. was there) and the reaction was let's get this into 8.2 because many people there (more than 5) really wanted to use it as a standard feature. A version of the slides with only the bitmap index results are located here: http://intranet.greenplum.com/bitmap-index-perf-ayush.ppt. 404 Strange. I can download it both from my work and home . -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 64-bit integers for GUC
Peter, I wonder whether platforms with INT64_IS_BROKEN can address more than 2GB of memory anyway. To be quite frank, current PostgreSQL can't effectively use more than 256mb of work_mem anyway. We'd like to fix that, but it's not fixed yet AFAIK. --Josh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] On-disk bitmap index patch
Ühel kenal päeval, T, 2006-07-25 kell 13:06, kirjutas Tom Lane: Luke Lonergan [EMAIL PROTECTED] writes: I think we do know, have you reviewed the results in the briefing? I find those results moderately unconvincing, primarily because they are based on choosing the least efficient possible data representation (viz char(n)), and thus the btree indexes suffer severe and quite artificial bloat. hmm, maybe this should be fixed in btree then ? do we really need to store padding blanks in btree ? A database schema chosen with even minimal attention to PG-specific tuning would probably have btree indexes half the size. That wouldn't completely eliminate the performance differential shown, but it would bring it down into the ballpark where you have to question whether it makes sense to support another index AM. It still depends on your data volumes. if you spend lots and lots of $ on hardware just to store surplus index bloat, it may be worth it. Remember, that the bizgres folks develop these things for real-world datawarehousing, where saving a few (tens or hundreds of) terabytes of storage and corresponging amount of RAM is a real win. The reason I have such high sales resistance is that we've carried the hash and rtree AMs for years, hoping that someone would do the work to make them actually worth using, with little result. What would be the use-case for hash indexes ? And what should be done to make them faster than btree ? I know that they are not wal-logged, but this is beside the point for DWH apps. and was'nt the rtree index recently deprecated in favour of GIST implementation of the same ? I don't want any more second-class-citizen index AMs, and that's why I'm questioning what the scope of applicability of bitmap indexes really is. They need to be popular enough that people will be motivated to work on them, or they shouldn't be in core. Is there an easy way to put them into contrib/ for some test period so that they can become popular among mainstream postgresql users ? -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] On-disk bitmap index patch
Tom, (I'm also wondering whether this doesn't overlap the use-case for GIN.) It does not. GIN is strictly for multi-value fields. I can think of applications where either GIN or Bitmaps would be an option, but for the majority, they wouldn't. One particular compelling situation for on-disk bitmaps is for terabyte tables where a btree index would not fit into memory. Index performance for an index which is 10x or more the size of RAM really sucks ... I can come up with some test results if you doubt that. Also note that low cardinality is relative. For a 1 billion row table, a column with 10,000 values is low-cardinality, having around 100,000 rows per value ... but that's still 0.01% of the table per value, making index use still applicable. --Josh ---(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] root/administartor user check option.
Which user does it switch to? I was under the impression that Gevik wanted not to have to create any extra users.Or does it abandon privileges without switching personality (if that's possible)? Correct, With the bypass option I had in mind, I wouldn't need to create an extra user. Just run PG under the current user. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Help! - Slony-I - saving/setting/restoring GUC
[EMAIL PROTECTED] (Tom Lane) writes: Chris Browne [EMAIL PROTECTED] writes: Unfortunately (perhaps) standards_conforming_strings does not appear to be exported, so I'm not sure how to do this otherwise. Perhaps your problem is one of spelling? It's standard_conforming_strings, and it's certainly a global variable. You still haven't explained why you need this, though. There are no datatype output functions that examine this variable. Apologies. I was reading too much into the problem report. After some discussion on IRC, I think we've simplified things back to what the problem is. Our logtrigger() function is already applying a non-SCS policy to quote backslashes. No need to declare anything there, as had been the case with a recent DateStyle issue. With DateStyle, we had to control the style in two places: 1. In logtrigger(), when PostgreSQL is asked to generate the string version of a datestamp. We do so by temporarily setting DateStyle. 2. When loading data, we need to make sure the connection uses a consistent DateStyle. We do so by setting the GUC variable using SET. I was under the misapprehension that we needed something akin to that step 1; apparently we only need worry about step 2. To which Peter's suggestion is entirely sufficient. Sorry about the confusion. -- let name=cbbrowne and tld=ntlug.org in name ^ @ ^ tld;; http://cbbrowne.com/info/internet.html Who is General Failure and why is he reading my hard disk? -- [EMAIL PROTECTED], Felix von Leitner ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] root/administartor user check option.
-Original Message- From: Andrew Dunstan [EMAIL PROTECTED] To: Dave Page dpage@vale-housing.co.uk Cc: Gevik Babakhani [EMAIL PROTECTED]; pgsql-hackers pgsql-hackers@postgresql.org Sent: 25/07/06 17:21 Subject: Re: [HACKERS] root/administartor user check option. Dave Page wrote: -Original Message- From: Gevik Babakhani [EMAIL PROTECTED] To: pgsql-hackers pgsql-hackers@postgresql.org Sent: 25/07/06 11:27 Subject: [HACKERS] root/administartor user check option. Or does it abandon privileges without switching personality (if that's possible)? Yes. It drops all privileges when pg_ctl (or initdb) starts, but remains running as the original user. The OS doesn't allow those privileges to be regained. /D ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] status of yet another timezone todo item
Hi, what do people think about this TODO item: o Merge hardwired timezone names with the TZ database; allow either kind everywhere a TZ name is currently taken I think it is kind of done. The hardwired timezone names are no longer hardwired and the America/New_York syntax is valid in timestamps and times as well. Joachim ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Better name/syntax for online index creation
On 7/25/06, Tom Lane [EMAIL PROTECTED] wrote: Csaba Nagy [EMAIL PROTECTED] writes: Strictly speaking, however, it would have to be NOLOCKLY in that case. :-) In this case CREATE INDEX CONCURRENTLY ... sounds better to me, although the whole feature sounds nice any way you will finally call it ;-) That reads well to me too. We'd need to check whether it can be parsed without making CONCURRENTLY a fully-reserved word, but offhand I think it would work because ON is already a fully-reserved word ... Is there a chance that the locking variant will be replaced by non-locking variant, or do we definitely want the locking variant to stay? Basically, this means whether the naming is temporary or permanent. -- marko ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Getting current transaction id
On 7/24/06, Nicolai Petri [EMAIL PROTECTED] wrote: I'm in the need for my custom written replication engine to obtain the current transaction id from a trigger function. As far as I'm told it's not possible today. Would people object much if this functionality was added for 8.2 ? It's the last piece of postgresql C code I have left in my application and I think it would be a nice information in general to have available for users. If not for anything else then for simple statistics. I attached the function I use with great success today. Using XID directly has some drawbacks, I need similary finctionality, and decided to extend XID to 8-bytes and use that externally: http://archives.postgresql.org/pgsql-patches/2006-07/msg00157.php Would that work for you? -- marko ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Better name/syntax for online index creation
Marko Kreen [EMAIL PROTECTED] writes: Is there a chance that the locking variant will be replaced by non-locking variant, No, I don't think so. Given that the concurrent form is much slower (extra table scan) and can't be wrapped into a transaction block, it'll always be a special option. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] status of yet another timezone todo item
Joachim Wieland [EMAIL PROTECTED] writes: what do people think about this TODO item: o Merge hardwired timezone names with the TZ database; allow either kind everywhere a TZ name is currently taken I think it is kind of done. Not quite --- SET TIMEZONE doesn't take the abbreviations. I'm not sure how important it is to accept SET TIME ZONE 'AKST' given that such a thing is not going to have any DST awareness; for most parts of the world, it's probably *wrong* to do this rather than SET TIME ZONE 'America/Anchorage'. But that was part of the original thought behind the TODO item. Do we want to explicitly decide not to support that? I'm not sure whether there are any other holes (I thought of AT TIME ZONE, but it seems to work with both). regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] On-disk bitmap index patch
Hannu Krosing [EMAIL PROTECTED] writes: Ãhel kenal päeval, T, 2006-07-25 kell 13:06, kirjutas Tom Lane: The reason I have such high sales resistance is that we've carried the hash and rtree AMs for years, hoping that someone would do the work to make them actually worth using, with little result. What would be the use-case for hash indexes ? And what should be done to make them faster than btree ? If we knew, we'd do it ;-) But no one's put enough effort into it to find out. and was'nt the rtree index recently deprecated in favour of GIST implementation of the same ? Yeah, we finally gave up on rtree entirely. I don't want to see any other index AMs languishing in the closet like that. If bitmap can hold its own to the extent that people are interested in working on it/improving it, then great, but I'm worried that it's not going to have a wide enough use-case to attract maintainers. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] On-disk bitmap index patch
Tom, On 7/25/06 1:31 PM, Tom Lane [EMAIL PROTECTED] wrote: Yeah, we finally gave up on rtree entirely. I don't want to see any other index AMs languishing in the closet like that. If bitmap can hold its own to the extent that people are interested in working on it/improving it, then great, but I'm worried that it's not going to have a wide enough use-case to attract maintainers. How do we close the gap? I think Jie is interested in maintaining it, and we're looking to extend the range of applications for both the AM and extensions that use the raw bitmap comparators made available to the executor. This should be just the start of some really great work on speedy access using bitmaps. Even as it sits, the on-disk bitmap is over 100x faster in cases where it's suited and the other commercial DBMS have had this popular feature for years. - Luke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] On-disk bitmap index patch
Josh Berkus josh@agliodbs.com writes: One particular compelling situation for on-disk bitmaps is for terabyte tables where a btree index would not fit into memory. Index performance for an index which is 10x or more the size of RAM really sucks ... I can come up with some test results if you doubt that. Sure... Also note that low cardinality is relative. For a 1 billion row table, a column with 10,000 values is low-cardinality, having around 100,000 rows per value ... but that's still 0.01% of the table per value, making index use still applicable. And your point is? Assuming a reasonable datatype like int4, a btree index on this table would occupy say 20GB (16 bytes per entry plus fillfactor overhead). The bitmap version would require 10,000 bitmaps of 1G bits apiece, or 1250GB (not even counting overhead). You need some wildly optimistic assumptions about the compressibility of the bitmaps to get even within hailing distance of the btree, let alone fit in RAM. A realistic assumption for the numbers you mention is that the bitmaps have 1-bits about 100 bits apart, which means you could get maybe 3-to-1 compression using the runlength scheme that's in there ... leaving the bitmap a factor of 20 bigger than the btree. AFAICS low cardinality has to mean just that, a few dozen distinct values at most, for this scheme to have any hope. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Resurrecting per-page cleaner for btree
ITAGAKI Takahiro [EMAIL PROTECTED] writes: This is a revised patch originated by Junji TERAMOTO for HEAD. [BTree vacuum before page splitting] http://archives.postgresql.org/pgsql-patches/2006-01/msg00301.php I think we can resurrect his idea because we will scan btree pages at-atime now; the missing-restarting-point problem went away. I've applied this but I'm now having some second thoughts about it, because I'm seeing an actual *decrease* in pgbench numbers from the immediately prior CVS HEAD code. Using pgbench -i -s 10 bench pgbench -c 10 -t 1000 bench (repeat this half a dozen times) with fsync off but all other settings factory-stock, what I'm seeing is that the first run looks really good but subsequent runs tail off in spectacular fashion :-( Pre-patch there was only minor degradation in successive runs. What I think is happening is that because pgbench depends so heavily on updating existing records, we get into a state where an index page is about full and there's one dead tuple on it, and then for each insertion we have * check for uniqueness marks one more tuple dead (the next-to-last version of the tuple) * newly added code removes one tuple and does a write * now there's enough room to insert one tuple * lather, rinse, repeat, never splitting the page. The problem is that we've traded splitting a page every few hundred inserts for doing a PageIndexMultiDelete, and emitting an extra WAL record, on *every* insert. This is not good. Had you done any performance testing on this patch, and if so what tests did you use? I'm a bit hesitant to try to fix it on the basis of pgbench results alone. One possible fix that comes to mind is to only perform the cleanup if we are able to remove more than one dead tuple (perhaps about 10 would be good). Or do the deletion anyway, but then go ahead and split the page unless X amount of space has been freed (where X is more than just barely enough for the incoming tuple). After all the thought we've put into this, it seems a shame to just abandon it :-(. But it definitely needs more tweaking. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] effective_cache_size is a real?
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Is it intentional that effective_cache_size is a real (as opposed to integer)? Yes --- the planner generally does all that stuff in float arithmetic to avoid worrying about overflow. Point taken, but I'm inclined to convert it to an integer anyway, because that will make the units support much easier. The variable is only used in exactly one place anyway, so making sure the calculation works right should be easy. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Change in Pervasive's PostgreSQL strategy
Today Pervasive announced that it is stepping away from PostgreSQL support and services. There's an open letter to the community at http://pervasivepostgresql.com with more info, so I won't really go into that. There are a few things I wanted to mention, though. First, I'm now officially looking for work. :) So is Larry. Second, Pervasive is keeping me employed until the end of August, so I'll continue posting from this email address and using this signature until then. While it's disappointing to me that Pervasive couldn't find a PostgreSQL strategy that worked for them it's certainly not a reflection of the technology. Even so, it's been a pleasure working here for the past year. Pervasive does have a bunch of stuff it plans to contribute to the community (such as our knowledge base, and the directory), and we'll be talking with Josh Berkus about that. -- 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] effective_cache_size is a real?
Peter Eisentraut [EMAIL PROTECTED] writes: Point taken, but I'm inclined to convert it to an integer anyway, because that will make the units support much easier. The variable is only used in exactly one place anyway, so making sure the calculation works right should be easy. Casting it to double shouldn't take many cycles, so go ahead. I assume you're planning to make the units support handle integer variables only? I can't see any other float GUC vars where units support would be real useful ... 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
