Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Simon Riggs
On Wed, 2005-07-06 at 17:17 -0700, Joshua D. Drake wrote: Tom, I think you're the only person that could or would be trusted to make such a change. Even past the 8.1 freeze, I say we need to do something now on this issue. I think if we document full_page_writes as similar to fsync in

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Simon Riggs
On Thu, 2005-07-07 at 00:29 -0400, Bruce Momjian wrote: Tom Lane wrote: Bruno Wolff III [EMAIL PROTECTED] writes: Are you sure about that? That would probably be the normal case, but are you promised that the hardware will write all of the sectors of a block in order? I don't

[HACKERS] A couple of patches for PostgreSQL 64bit support

2005-07-07 Thread Koichi Suzuki
Hi, all, I have posted a couple of patches with regard to 64bit environment support to PATCHES ml. It expands size of shared memory to 64bit space and extends XID to 64bit. Please take a look at it. -- --- Koichi Suzuki Open Source Engineeering

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Zeugswetter Andreas DAZ SD
Are you sure about that? That would probably be the normal case, but are you promised that the hardware will write all of the sectors of a block in order? I don't think you can possibly assume that. If the block crosses a cylinder boundary then it's certainly an unsafe assumption,

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Zeugswetter Andreas DAZ SD
Here's an idea: We read the page that we would have backed up, calc the CRC and write a short WAL record with just the CRC, not the block. When we recover we re-read the database page, calc its CRC and compare it with the CRC from the transaction log. If they differ, we know that the

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Tom Lane
Zeugswetter Andreas DAZ SD [EMAIL PROTECTED] writes: Only workable solution would imho be to write the LSN to each 512 byte block (not that I am propagating that idea). We're not doing anything like that, as it would create an impossible space-management problem (or are you happy with limiting

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Tom Lane
I wrote: We still don't know enough about the situation to know what a solution might look like. Is the slowdown Josh is seeing due to the extra CPU cost of the CRCs, or the extra I/O cost, or excessive locking of the WAL-related data structures while we do this stuff, or ???. Need more

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Zeugswetter Andreas DAZ SD
Only workable solution would imho be to write the LSN to each 512 byte block (not that I am propagating that idea). Only workable was a stupid formulation, I meant a solution that works with a LSN. We're not doing anything like that, as it would create an impossible space-management

[HACKERS] windows regression failure - prepared xacts

2005-07-07 Thread Andrew Dunstan
I am consistently seeing the regression failure shown below on my Windows machine. See http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=lorisdt=2005-07-07%2013:54:13 (On the plus side, I am now building happily and passing regression tests with ASPerl, and hope to add ASPython and ASTcl to

Re: [HACKERS] [INTERFACES] By Passed Domain Constraints

2005-07-07 Thread Robert Perry
Tom Thank you very much. This sounds like my problem exactly. I personally, feel that the change you have described is the right way to go for PostgreSQL. But, since the thing that I expected to work does not and would with your suggested change I guess that my opinion is pretty

[HACKERS] SQL99 - Nested Tables

2005-07-07 Thread Darren Alcorn
I was interested as to if there were plans to develop SQL99 nested tables. I know with AJAX(tm) starting to grow in popularity that the XML features of SQL2003 would prove useful for EnterpriseDB. I have developed applications using Microsoft SQL Server since 1999. I have only been programming in

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Bruce Momjian
Simon Riggs wrote: SCSI tagged queueing certainly allows 512-byte blocks to be reordered during writes. Then a torn-page tell-tale is required that will tell us of any change to any of the 512-byte sectors that make up a block/page. Here's an idea: We read the page that we would have

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Tom Lane
Bruce Momjian pgman@candle.pha.pa.us writes: Yes, that is a good idea! ... which was shot down in the very next message. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Bruce Momjian
Zeugswetter Andreas DAZ SD wrote: Are you sure about that? That would probably be the normal case, but are you promised that the hardware will write all of the sectors of a block in order? I don't think you can possibly assume that. If the block crosses a cylinder boundary then

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: Zeugswetter Andreas DAZ SD [EMAIL PROTECTED] writes: Only workable solution would imho be to write the LSN to each 512 byte block (not that I am propagating that idea). We're not doing anything like that, as it would create an impossible

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Tom Lane
Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: What we *could* do is calculate a page-level CRC and store it in the page header just before writing out. Torn pages would then manifest as a wrong CRC on read. No correction ability, but at least a reliable detection

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Bruce Momjian
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: As far as #2, my posted proposal was to write the full pages to WAL when they are written to the file system, and not when they are first modified in the shared buffers --- That is *completely* unworkable. Or were you planning

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Tom Lane
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: The point here is that fsync-off is only realistic for development or playpen installations. You don't turn it off in a production machine, and I can't see that you'd turn off the full-page-write option either. So we have not

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Bruce Momjian
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: The point here is that fsync-off is only realistic for development or playpen installations. You don't turn it off in a production machine, and I can't see that you'd turn off the full-page-write option

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Joshua D. Drake
Just to make my position perfectly clear: I don't want to see this option shipped in 8.1. It's reasonable to have it in there for now as an aid to our performance investigations, but I don't see that it has any value for production. Well, this is the first I am hearing that, and of course

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Bruce Momjian
Joshua D. Drake wrote: Just to make my position perfectly clear: I don't want to see this option shipped in 8.1. It's reasonable to have it in there for now as an aid to our performance investigations, but I don't see that it has any value for production. Well, this is the first I

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Bruce Momjian
Simon Riggs wrote: On Wed, 2005-07-06 at 18:22 -0400, Bruce Momjian wrote: Well, I added #1 yesterday as 'full_page_writes', and it has the same warnings as fsync (namely, on crash, be prepared to recovery or check your system thoroughly. Yes, which is why I comment now that the GUC

[HACKERS] Must be owner to truncate?

2005-07-07 Thread Stephen Frost
Greetings, The current permissions checks for truncate seem to be excessive. It requires that you're the owner of the relation instead of requiring that you have delete permissions on the relation. It was pointed out that truncate doesn't call triggers but it seems like that would be

Re: [HACKERS] SQL99 - Nested Tables

2005-07-07 Thread Josh Berkus
Darren, I was interested as to if there were plans to develop SQL99 nested tables. I know with AJAX(tm) starting to grow in popularity that the XML features of SQL2003 would prove useful for EnterpriseDB. I realize that nested tables are in SQL99, but so is SQLJ and a few other really dumb

Re: [HACKERS] A couple of patches for PostgreSQL 64bit support

2005-07-07 Thread Josh Berkus
Koichi, I have posted a couple of patches with regard to 64bit environment support to PATCHES ml. It expands size of shared memory to 64bit space and extends XID to 64bit. Please take a look at it. In case you weren't aware, feature freeze was last Friday. So your patch is liable to

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Josh Berkus
Tom, Josh, is OSDL up enough that you can try another comparison run? Thankfully, yes. If so, please undo the previous patch (which disabled page dumping entirely) and instead try removing this block of code, starting at about xlog.c line 620 in CVS tip: Will do. Results in a few days.

Re: [HACKERS] Must be owner to truncate?

2005-07-07 Thread Tom Lane
Stephen Frost [EMAIL PROTECTED] writes: The current permissions checks for truncate seem to be excessive. It requires that you're the owner of the relation instead of requiring that you have delete permissions on the relation. It was pointed out that truncate doesn't call triggers

Re: [HACKERS] Must be owner to truncate?

2005-07-07 Thread Stephen Frost
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: The current permissions checks for truncate seem to be excessive. It requires that you're the owner of the relation instead of requiring that you have delete permissions on the relation. It was pointed out

Re: [HACKERS] SQL99 - Nested Tables

2005-07-07 Thread David Fetter
On Thu, Jul 07, 2005 at 09:52:44AM -0700, Josh Berkus wrote: Darren, I was interested as to if there were plans to develop SQL99 nested tables. I know with AJAX(tm) starting to grow in popularity that the XML features of SQL2003 would prove useful for EnterpriseDB. I realize that

Re: [HACKERS] Must be owner to truncate?

2005-07-07 Thread Andrew - Supernews
On 2005-07-07, Stephen Frost [EMAIL PROTECTED] wrote: * truncate is not MVCC-safe. Erm, that's why it gets a stronger lock, so I don't really see what this has to do with it. It's not MVCC-safe even with the AccessExclusive lock; it damages snapshots that were taken before the truncate

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Kenneth Marshall
On Thu, Jul 07, 2005 at 11:36:40AM -0400, Tom Lane wrote: Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: What we *could* do is calculate a page-level CRC and store it in the page header just before writing out. Torn pages would then manifest as a wrong CRC on

Re: [HACKERS] SQL99 - Nested Tables

2005-07-07 Thread Darren Alcorn
I was mainly interested because of the simplicity it seems to add for implementing an application using the database. While those accustomed to writing SQL queries using joins and keys might prefer it for many understandable reasons, there is something to be said for multidimensional data

[HACKERS] 8.1dev ssl broke?

2005-07-07 Thread Joshua D. Drake
I pulled cvs today and performed the following: ./configure --with-openssl --prefix=/tmp/pgsqldev make install cd /tmp/pgsqldev initdb --no-locale -D data -l data/serverlog pg_hba.conf lines: # local is for Unix domain socket connections only #local all all

Re: [HACKERS] 8.1dev ssl broke?

2005-07-07 Thread Joshua D. Drake
FYI: I also followed the instructions per: http://developer.postgresql.org/docs/postgres/ssl-tcp.html Joshua D. Drake wrote: I pulled cvs today and performed the following: ./configure --with-openssl --prefix=/tmp/pgsqldev make install cd /tmp/pgsqldev initdb --no-locale -D data -l

Re: [HACKERS] SQL99 - Nested Tables

2005-07-07 Thread Josh Berkus
Darren, I was mainly interested because of the simplicity it seems to add for implementing an application using the database. While those accustomed to writing SQL queries using joins and keys might prefer it for many understandable reasons, there is something to be said for multidimensional

Re: [HACKERS] 8.1dev ssl broke?

2005-07-07 Thread Michael Fuhr
On Thu, Jul 07, 2005 at 12:30:35PM -0700, Joshua D. Drake wrote: I pulled cvs today and performed the following: ./configure --with-openssl --prefix=/tmp/pgsqldev Did the build actually find OpenSSL? Does ldd postgres show it linked against libcrypto and libssl (I'm assuming those are shared

Re: [HACKERS] 8.1dev ssl broke?

2005-07-07 Thread Joshua D. Drake
Michael Fuhr wrote: On Thu, Jul 07, 2005 at 12:30:35PM -0700, Joshua D. Drake wrote: I pulled cvs today and performed the following: ./configure --with-openssl --prefix=/tmp/pgsqldev Did the build actually find OpenSSL? Does ldd postgres show it linked against libcrypto and libssl (I'm

[HACKERS] no subject

2005-07-07 Thread Ayush Parashar
---(end of broadcast)--- TIP 3: 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] Must be owner to truncate?

2005-07-07 Thread Jim C. Nasby
On Thu, Jul 07, 2005 at 01:48:59PM -0400, Tom Lane wrote: Stephen Frost [EMAIL PROTECTED] writes: The current permissions checks for truncate seem to be excessive. It requires that you're the owner of the relation instead of requiring that you have delete permissions on the relation.

[HACKERS] Multi-byte and client side character encoding tests for copy command..

2005-07-07 Thread Ayush Parashar
Hi there, I am new to this list. I have made some additions to multi-byte regression tests ( ./src/test/mb), to include regression tests for copy command. This can be used to test multi-byte extensions of postgresql and client character encoding, for copy command. The test uses the following

Re: [HACKERS] Must be owner to truncate?

2005-07-07 Thread Stephen Frost
* Jim C. Nasby ([EMAIL PROTECTED]) wrote: On Thu, Jul 07, 2005 at 01:48:59PM -0400, Tom Lane wrote: I don't really agree with the viewpoint that truncate is just a quick DELETE, and so I do not agree that DELETE permissions should be enough to let you do a TRUNCATE. What about adding a

Re: [HACKERS] Must be owner to truncate?

2005-07-07 Thread Stephen Frost
* Andrew - Supernews ([EMAIL PROTECTED]) wrote: On 2005-07-07, Stephen Frost [EMAIL PROTECTED] wrote: * truncate is not MVCC-safe. Erm, that's why it gets a stronger lock, so I don't really see what this has to do with it. It's not MVCC-safe even with the AccessExclusive lock; it

[HACKERS] Pg_autovacuum on FreeBSD

2005-07-07 Thread Rodrigo Moreno
Hi All, The pg_autovacuum on FreeBSD and pg 803 is not working. Just do nothing, no log, nothing in screen, no daemonize. It was ok on pg746. Could some one help me ? Best Regards Rodrigo Moreno ---(end of broadcast)--- TIP 6: Have you searched

Re: [HACKERS] SQL99 - Nested Tables

2005-07-07 Thread David Fetter
On Thu, Jul 07, 2005 at 12:53:14PM -0700, Josh Berkus wrote: Darren, I was mainly interested because of the simplicity it seems to add for implementing an application using the database. While those accustomed to writing SQL queries using joins and keys might prefer it for many

[HACKERS] temp_buffers

2005-07-07 Thread Joshua D. Drake
Hello, Can someone give me a reasonable explanation of what temp_buffers is for? Sincerely, Joshua D. Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated

Re: [HACKERS] temp_buffers

2005-07-07 Thread Alvaro Herrera
On Thu, Jul 07, 2005 at 04:16:58PM -0700, Joshua D. Drake wrote: Can someone give me a reasonable explanation of what temp_buffers is for? Number of buffers to be used for temp tables. Think shared_buffers, but local to a connection instead of shared. They are also used for new relations, in

Re: [HACKERS] SQL99 - Nested Tables

2005-07-07 Thread Josh Berkus
David, Nested tables is a Good Thing(TM) though :) Hmmm. I don't buy that they are an a priori Good Thing. What are they good for? I can't think of a single occasion in my 12-year database career where I found myself wanting one. Seems to me that, SQL standard or not, nested tables are

Re: [HACKERS] SQL99 - Nested Tables

2005-07-07 Thread David Fetter
On Thu, Jul 07, 2005 at 05:14:27PM -0700, Josh Berkus wrote: David, Nested tables is a Good Thing(TM) though :) Hmmm. I don't buy that they are an a priori Good Thing. What are they good for? I can't think of a single occasion in my 12-year database career where I found myself wanting

Re: [HACKERS] Pg_autovacuum on FreeBSD

2005-07-07 Thread Mark Kirkwood
Rodrigo Moreno wrote: Hi All, The pg_autovacuum on FreeBSD and pg 803 is not working. Just do nothing, no log, nothing in screen, no daemonize. It was ok on pg746. Could some one help me ? What version of FreeBSD are you running? Mark ---(end of

Re: [HACKERS] Must be owner to truncate?

2005-07-07 Thread Christopher Kings-Lynne
The current permissions checks for truncate seem to be excessive. It requires that you're the owner of the relation instead of requiring that you have delete permissions on the relation. It was pointed out that truncate doesn't call triggers but it seems like that would be something

Re: [HACKERS] Must be owner to truncate?

2005-07-07 Thread Christopher Kings-Lynne
There are other reasons for restricting it: * truncate takes a much stronger lock than a plain delete does. * truncate is not MVCC-safe. I don't really agree with the viewpoint that truncate is just a quick DELETE, and so I do not agree that DELETE permissions should be enough to let you do a

Re: [HACKERS] Pg_autovacuum on FreeBSD

2005-07-07 Thread Christopher Kings-Lynne
The pg_autovacuum on FreeBSD and pg 803 is not working. Just do nothing, no log, nothing in screen, no daemonize. It was ok on pg746. Could some one help me ? They both work fine for me on my test box... Are you aware that they change the port? You need to put postgresql=YES in your

[HACKERS] Hmmm 8.1 pg_dumpall cannot dump older db's?

2005-07-07 Thread Christopher Kings-Lynne
I can't seem to dump old db's: -bash-2.05b$ pg_dumpall -s -h database-dev dump.sql Password: pg_dumpall: could not connect to database postgres: FATAL: database postgres does not exist Seems that it is expecting the new 'postgres' database to exist on old installations? Chris

Re: [HACKERS] Must be owner to truncate?

2005-07-07 Thread Tom Lane
Stephen Frost [EMAIL PROTECTED] writes: * Andrew - Supernews ([EMAIL PROTECTED]) wrote: It's not MVCC-safe even with the AccessExclusive lock; This seems like something which should probably be fixed, You've missed the point entirely: this *cannot* be fixed, at least not without giving up the

Re: [HACKERS] temp_buffers

2005-07-07 Thread Tom Lane
Alvaro Herrera [EMAIL PROTECTED] writes: On Thu, Jul 07, 2005 at 04:16:58PM -0700, Joshua D. Drake wrote: Can someone give me a reasonable explanation of what temp_buffers is for? Number of buffers to be used for temp tables. Think shared_buffers, but local to a connection instead of shared.

Re: [HACKERS] Hmmm 8.1 pg_dumpall cannot dump older db's?

2005-07-07 Thread Tom Lane
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Seems that it is expecting the new 'postgres' database to exist on old installations? Ooops :-( Seems like maybe we want it to try postgres and then fall back to trying template1? regards, tom lane

Re: [HACKERS] Hmmm 8.1 pg_dumpall cannot dump older db's?

2005-07-07 Thread Christopher Kings-Lynne
Seems that it is expecting the new 'postgres' database to exist on old installations? Ooops :-( Seems like maybe we want it to try postgres and then fall back to trying template1? Actually, also ONLY assume postgres is a special database if the backend is 8.1 or higher. We don't want to

Re: [HACKERS] Hmmm 8.1 pg_dumpall cannot dump older db's?

2005-07-07 Thread Christopher Kings-Lynne
Seems that it is expecting the new 'postgres' database to exist on old installations? Ooops :-( Seems like maybe we want it to try postgres and then fall back to trying template1? No idea :) I haven't followed the new postgres database changes particularly well... Chris

Re: [HACKERS] Hmmm 8.1 pg_dumpall cannot dump older db's?

2005-07-07 Thread Tom Lane
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Ooops :-( Seems like maybe we want it to try postgres and then fall back to trying template1? Actually, also ONLY assume postgres is a special database if the backend is 8.1 or higher. We don't want to mess with poor people who have

Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC

2005-07-07 Thread Tom Lane
Josh Berkus josh@agliodbs.com writes: If so, please undo the previous patch (which disabled page dumping entirely) and instead try removing this block of code, starting at about xlog.c line 620 in CVS tip: Will do. Results in a few days. Great. BTW, don't bother testing snapshots between

Re: [HACKERS] process crash when a plpython function returns

2005-07-07 Thread James William Pye
On Mon, 2005-06-27 at 08:12 -0600, Michael Fuhr wrote: also in this context it would be helpful if sys.defaultencoding would be set to the database encoding so strings get encoded to utf-8 when postgres works in unicode mode rather then the default encoding of ascii. This could avoid