Re: [HACKERS] Bumping block size to 16K on FreeBSD...
Sean, can we get a copy of your test set? And any scripts that you have for running the tests? On Thu, 28 Aug 2003, Tom Lane wrote: > Sean Chittenden <[EMAIL PROTECTED]> writes: > > Early performance tests on my laptop suggest it's about 8% faster for > > writing when both the FS and PostgreSQL use 16K blocks. > > BTW, I don't really believe that one set of tests, conducted on one > single machine, are anywhere near enough justification for changing this > value. Especially not if it's a laptop rather than a typical server > configuration. You've got considerably less I/O bandwidth in proportion > to CPU horsepower than a server. Why is that an issue? Well, a larger > block size will substantially increase our WAL overhead (because we tend > to dump whole blocks into WAL at the slightest provocation) and on > slower machines the CRC64 calculations involved in WAL entries are a > significant cost. On a machine with less CPU and more disk horsepower > than you tested, the tradeoffs could be a lot different. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] ALTER TABLE
I've been fiddling away on ALTER TABLE and have a few questions about a possible datatype change. Just alter portions. I presume I'll need to do a table rewrite. What is the best way to change a single datum? heap_modify() takes a single relation type where I will need to deal with different types. Simply build a new tuple with old datums (easy enough)? Do we care about OIDs being renumbered? If clustering is enabled, should I re-cluster the table at the same time? Read table in index order rather than sequential scan. All other work would be the same in either case. Since the table is being rewritten and tuples rebuilt anyway, is it safe to remove dropped columns completely from pg_attribute (renumbering the others) or should a NULL be stored? I'm sure lots of problems will be found with cached rules / views for datatype changes that I've not even started looking for. Thus far I have: ALTER TABLE ... ADD COLUMN .. SERIAL ALTER TABLE .. ADD COLUMN .. DEFAULT 42 NOT NULL A new combination syntax that does all of the checks / defaults in a single pass of the table rather than one per operation has also been implemented. ALTER TABLE test ADD CHECK(col > 4), add column bob integer default 2 not null, add column bob2 serial check(bob2 <= 255), drop column col2 cascade; signature.asc Description: This is a digitally signed message part
[HACKERS] FE/BE Protocol - Specific version
Will it be possible to tell a PostgreSQL back end to use *only* the new version of the FE/BE protocol? That is, will it be possible to set up a database that will reject connection attempts that do not use the new protocol version? Thanks, Bruce ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] [cguttesen@yahoo.dk: Re: Some additional tests run on my performance testing]
'nother test in support of 16K blocks for FreeBSD, this time it was 25% faster to import. -sc -- Sean Chittenden --- Begin Message --- Hi. I'm implementing postgresql 7.3.4 on FreeBSD 5.1, and decided to place the pgsql-folder on it's own partition so it was easier to test which blocksize to go for. So I newfs'ed it with 8 and 16 kb blocksize did an import of a 1.5 GB pg-dump. > numbers you suggest above, I loaded a DB with 8k and > 16K blocks > (translation: almost all write activities). > > them to stay about the same across the board. If > someone wants to do > some good read tests, I'd be interested in those > results. > The 8 kb blocksize took 60 min. to import, and the 16 kb ditto took 45 min. So I'm settling on 16 kb blocks. Softupdates was enabled in both scenarios, db was dropped and recreated and server rebooted before each import. The fragsize was the recommended 1/8 of blocksize, i.e. 1 and 2 kb. 2 GB ECC RAM. I haven't done any further testing than that, but it seems that FreeBSD internally caches 16 kb blocksize better than 8 kb. regards Claus Yahoo! Mail (http://dk.mail.yahoo.com) - Gratis: 6 MB lagerplads, spamfilter og virusscan ___ [EMAIL PROTECTED] mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-database To unsubscribe, send any mail to "[EMAIL PROTECTED]" --- End Message --- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Bumping block size to 16K on FreeBSD...
On Thu, Aug 28, 2003, scott.marlowe wrote: > On Thu, 28 Aug 2003, Marc G. Fournier wrote: > > On Thu, 28 Aug 2003, Thomas Swan wrote: > > > > > Has anyone looked at changing the default block size across the board > > > and what the performance improvements/penalties might be? Hardware has > > > changed quite a bit over the years. > > > > I *think* that the reason for the performance improvement on FreeBSD is > > that our FS block size is 16k, instead of 8k ... are there any other > > OSs that have increased theirs? > > Linux, is still, as far as I know, limited to the max page size of the CPU > it's on, which for most x86 is 4k. I don't know about the page size issue, but Linux has the additional problem that ext2/ext3 do not support fragments or variable block sizes within the same filesystem. Therefore, Linux wastes an excessive amount of space for larger block sizes. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Bumping block size to 16K on FreeBSD...
On Thu, Aug 28, 2003, Tom Lane wrote: > Sean Chittenden <[EMAIL PROTECTED]> writes: > > Are there any objections > > to me increasing the block size for FreeBSD installations to 16K for > > the upcoming 7.4 release? > > I'm a little uncomfortable with introducing a cross-platform variation > in the standard block size. That would have implications for things > like whether a table definition that works on FreeBSD could be expected > to work elsewhere; to say nothing of recommendations for shared_buffer > settings and suchlike. > > Also, there is no infrastructure for adjusting BLCKSZ automatically at > configure time, and I don't much want to add it. On recent versions of FreeBSD (and Solaris too, I think), the default UFS block size is 16K, and file fragments are 2K. This works great for many workloads, but it kills pgsql's random write performance unless pgsql uses 16K blocks as well, due to the read-modify-write involved. Either the filesystem or the database needs to be changed in order to get decent performance. I have not compared 16K UFS/16K pgsql to 8K UFS/8K pgsql, so I can't say which option makes more sense, though. There probably isn't anything wrong with the pgsql default, except that it's set in stone. It's entirely feasible for administrators to create 8K/1K UFS filesystems specifically for pgsql, but they need to be aware of the issue. On the other hand, I don't see how it would be a bad thing if pgsql were able to adapt at runtime either. Thus, I've come up with two possible fixes: (1) Document the problem with having a filesystem block size larger than the database block size. With a simple call to statvfs(2), the postmaster could warn about this on startup, too. (2) Make BLCKSZ a runtime constant, stored as part of the database. Grepping through the source, I didn't see any places using BLCKSZ where efficiency appeared to be so critical that you had to have constant folding. Of course, one could introduce a 'lg2blksz' constant to avoid divides and multiplies. This would NOT introduce cross-platform incompatibilities, only efficiency problems with databases that have been moved across filesystems in some cases. The ability to adapt at database creation time is also useful in that it allows the database to be tuned to the characteristics of the particular device on which it resides.[1] I don't know very much about pgsql, so corrections and feedback regarding these ideas would be appreciated. [1] Right now, the seek time to transfer time ratio of the drive is mostly hidden by the operating system's clustering and read-ahead. I tried modifying pgsql to use direct I/O, but it seems that pgsql doesn't do its own clustering or read-ahead, so that was a lose... ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] FE/BE Protocol - Specific version
Will it be possible to tell a PostgreSQL back end to use *only* the new version of the FE/BE protocol? That is, will it be possible to set up a database that will reject connection attempts that do not use the new protocol version? Thanks, Bruce signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [cguttesen@yahoo.dk: Re: Some additional tests run on
what sort of hardware was this run on? On Thu, 28 Aug 2003, Sean Chittenden wrote: > 'nother test in support of 16K blocks for FreeBSD, this time it was > 25% faster to import. -sc > > -- > Sean Chittenden > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Bumping block size to 16K on FreeBSD...
> > > Early performance tests on my laptop suggest it's about 8% > > > faster for writing when both the FS and PostgreSQL use 16K > > > blocks. > > > > BTW, I don't really believe that one set of tests, conducted on > > one single machine, are anywhere near enough justification for > > changing this value. Especially not if it's a laptop rather than > > a typical server configuration. You've got considerably less I/O > > bandwidth in proportion to CPU horsepower than a server. Why is > > that an issue? Well, a larger block size will substantially > > increase our WAL overhead (because we tend to dump whole blocks > > into WAL at the slightest provocation) and on slower machines the > > CRC64 calculations involved in WAL entries are a significant cost. > > On a machine with less CPU and more disk horsepower than you > > tested, the tradeoffs could be a lot different. > > Sean, can we get a copy of your test set? And any scripts that you > have for running the tests? Unfortunately not, my tests were simply re-initdb'ing and loading in my schema. I have some read tests I'm going to perform here in a bit, but I'm waiting for kde to finish compiling before I start testing. I have another tests machine that I'm going to task with comparing 16K and 8K blocks. It's not SCSI, but I don't have any available machines that I can newfs + reinstall PostgreSQL on. I was thinking about running the regression tests 10x ... -sc -- Sean Chittenden ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Weird constraint output
> Obviously psql uses either use pg_constraint.consrc or > pg_get_expr(conbin, conrelid) which both will give this lot of > parentheses, so it's not a bug, but a feature > For easier reengineering, I invented several pg_get_xxx functions with a > pretty-print option, which omits this messy parentheses, because I > noticed how ugly this looks while writing pgAdmin3 (especially for large > views with many joins and big expressions). > There have been suggestions to use the pretty-print options for psql (I > don't know which have been realized so far), and very harsh rejects > doing this in pg_dump, namely from Tom). I have changed psql to use pg_get_viewdef(oid, true). I agree with Tom for not using it in dumps just yet though. Is there a function for getting nice constraint defs? Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] ALTER TABLE
Do you have this working:
ALTER TABLE / ADD COLUMN .. DEFAULT nextval('asdf')...
(eg. a default that needs to be re-evaluated for each row)
Also, did you remember about checking domain constraints, etc.?
Chris
- Original Message -
From: "Rod Taylor" <[EMAIL PROTECTED]>
To: "PostgreSQL Development" <[EMAIL PROTECTED]>
Sent: Friday, August 29, 2003 8:31 AM
Subject: [HACKERS] ALTER TABLE
I've been fiddling away on ALTER TABLE and have a few questions about a
possible datatype change. Just alter portions.
I presume I'll need to do a table rewrite. What is the best way to
change a single datum? heap_modify() takes a single relation type where
I will need to deal with different types. Simply build a new tuple with
old datums (easy enough)? Do we care about OIDs being renumbered?
If clustering is enabled, should I re-cluster the table at the same
time? Read table in index order rather than sequential scan. All other
work would be the same in either case.
Since the table is being rewritten and tuples rebuilt anyway, is it safe
to remove dropped columns completely from pg_attribute (renumbering the
others) or should a NULL be stored?
I'm sure lots of problems will be found with cached rules / views for
datatype changes that I've not even started looking for.
Thus far I have:
ALTER TABLE ... ADD COLUMN .. SERIAL
ALTER TABLE .. ADD COLUMN .. DEFAULT 42 NOT NULL
A new combination syntax that does all of the checks / defaults in a
single pass of the table rather than one per operation has also been
implemented.
ALTER TABLE test ADD CHECK(col > 4),
add column bob integer default 2 not null,
add column bob2 serial check(bob2 <= 255),
drop column col2 cascade;
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nasty problem in hash indexes
On Thu, Aug 28, 2003 at 05:37:39PM -0400, Tom Lane wrote: > > If so, then how many other bugs are lurking in the hash index code > > waiting to bite? > > Who's to say? We've found bugs in the btree logic recently, > too. I'd rather print a loud warning when a hash index is created, but keep the code in the tree, than just remove it entirely. That way, we'll avoid unnecessary bit-rot to some degree, and if someone feels that they absolutely positively need hash indexes, they will have some existing work to begin from. -Neil ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Linux2.6 overcommit behaviour
On Thu, Aug 28, 2003 at 05:22:36PM +0530, Shridhar Daithankar wrote: > BTW, what is the sway of switching disk IO scheduler in 2.6? Could not find > any references to sysctl switching. Andrew Morton's TODO list still list it > as TODO. Sorry, I was mistaken: you can switch I/O schedulers by specifying a flag at boot-time: http://marc.theaimsgroup.com/?l=linux-kernel&m=105743728122143&w=2 -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Code revision
On Thu, Aug 28, 2003 at 08:06:05PM +0200, Mendola Gaetano wrote: > is not usefull have a mailing list in where people can > partecipate in a sort of "code revision" about the > actual code ? I think pgsql-patches is used for that purpose by some people. If you're implementing a major change, its usually best to send a proposal for the change to pgsql-hackers for discussion. Once a consensus is reached, implement the change and send the patch to pgsql-patches, where people will usually comment on the patch if they feel it needs changes. -Neil ---(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] Bumping block size to 16K on FreeBSD...
On Thu, Aug 28, 2003 at 01:00:44PM -0700, Sean Chittenden wrote: > Other than you feeling uneasy about the possibility of uncovering bugs > because this hasn't been widely done like this before, do you have any > other concerns, or do you think the possibility of finding bugs very > likely? In case Tom didn't make this clear, I'm strongly opposed to making this change without doing the necessary (non-FreeBSD-specific) legwork. The bottom-line is that if we're going to be changing the block size on a regular basis, it needs to be completely transparent to the user, from a functionality perspective. That's currently not the case: changing the BLCKSZ changes the meaning of shared_buffers and effective_cache_size, for example, so tuning documents written for other operating systems won't apply as easily to PostgreSQL on FreeBSD. Until the user-visible effects of BLCKSZ have been ironed over[1], I definately think you shouldn't include the patch in the FreeBSD port. [1] - Other improvements, like making it easier to change the blocksize (making it a configure option?) would be cool too. -Neil ---(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] [seanc@FreeBSD.org: Re: Performance tests I did with
On Wed, 27 Aug 2003, Sean Chittenden wrote: > FreeBSD uses 16K blocks for its FS... Actually, FreeBSD can use 8K blocks as well. The reason for using 16K blocks is not speeed, but to reduce filesystem overhead and, more importantly, to allow larger cylinder groups. But for a database with a lot of random access of small records, your caching will probably do better if you use 8K filesystem blocks; you're like to be able to effectively cache more data. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.NetBSD.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Bumping block size to 16K on FreeBSD...
On Thu, 28 Aug 2003, Neil Conway wrote: > On Thu, Aug 28, 2003 at 01:00:44PM -0700, Sean Chittenden wrote: > > Other than you feeling uneasy about the possibility of uncovering bugs > > because this hasn't been widely done like this before, do you have any > > other concerns, or do you think the possibility of finding bugs very > > likely? > > In case Tom didn't make this clear, I'm strongly opposed to making > this change without doing the necessary (non-FreeBSD-specific) legwork. > The bottom-line is that if we're going to be changing the block size > on a regular basis, it needs to be completely transparent to the user, > from a functionality perspective. That's currently not the case: > changing the BLCKSZ changes the meaning of shared_buffers and > effective_cache_size, for example, so tuning documents written for > other operating systems won't apply as easily to PostgreSQL on > FreeBSD. Until the user-visible effects of BLCKSZ have been ironed > over[1], I definately think you shouldn't include the patch in the > FreeBSD port. "tuning documents" is *not* a valid reason for not doing this ... that's like saying "we can make it faster on some operating systems, but because we're going to have to modify the tuning documents, we're not going to do it" ... wait, that is exactly what you are saying ... Now, Tom made one point in his original that *was* valid ... a table definition made under a 16k BLCKSZ db will not necessarily work under an 8k compiled server .. the example that he made to me was that a table of float8 under a 16k server could have N fields, but if you tried to dump/import that table into an 8k BLCKSZ one with that max # of fields, it would fail ... that is a *serious* concern against doing this ... Now, here's a question for someone running a non-FreeBSD OS ... if we were to jump the BLCKSZ to 16k, would it cause a degradation in performance, or would it make no difference to them? Would they see an 8% reduction in performance? The thing is ... there has been presented a strong, valid reason for moving to 16k (at least under FreeBSD) ... and there has been a valid reason for not making it "easily configurable" ... but, are there any strong reasons not to just move to 16k across the board? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] ALTER TABLE
Rod Taylor <[EMAIL PROTECTED]> writes: > I've been fiddling away on ALTER TABLE and have a few questions about a > possible datatype change. Just alter portions. > I presume I'll need to do a table rewrite. What is the best way to > change a single datum? heap_modify() takes a single relation type where > I will need to deal with different types. Simply build a new tuple with > old datums (easy enough)? Do we care about OIDs being renumbered? Have you consulted the archives? ISTM we discussed these issues to death a year or two back. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] FE/BE Protocol - Specific version
Bruce Badger <[EMAIL PROTECTED]> writes: > Will it be possible to tell a PostgreSQL back end to use *only* the new > version of the FE/BE protocol? > That is, will it be possible to set up a database that will reject > connection attempts that do not use the new protocol version? I cannot imagine a reason that that would be a good idea ... but convince me if you can ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Bumping block size to 16K on FreeBSD...
On Fri, Aug 29, 2003 at 12:06:59AM -0300, Marc G. Fournier wrote: > "tuning documents" is *not* a valid reason for not doing this ... that's > like saying "we can make it faster on some operating systems, but because > we're going to have to modify the tuning documents, we're not going to do > it" ... wait, that is exactly what you are saying ... No, it's a perfectly valid reason for not doing this (in the present, half-baked form that has been presented). PostgreSQL is at the moment fairly simple to configure. Adding a significant amount of complexity to the configuration / tuning process and making a given configuration non-portable between different platforms and different compiles of PostgreSQL is something I'd like to avoid, if possible. And I think it's possible to avoid it, it's just that the original patch makes no attempt to do so. For example, why does shared_buffers need to be specified in disk pages, anyway? ISTM it could just as easily be specified in bytes, and PostgreSQL could internally round up/down to the nearest multiple of the BLCKSZ that this instance of PostgreSQL happened to be compiled with. > Now, Tom made one point in his original that *was* valid ... a table > definition made under a 16k BLCKSZ db will not necessarily work under an > 8k compiled server .. the example that he made to me was that a table of > float8 under a 16k server could have N fields, but if you tried to > dump/import that table into an 8k BLCKSZ one with that max # of fields, it > would fail ... that is a *serious* concern against doing this ... Uh, yeah -- I was talking about that as well. I said "it needs to be completely transparent to the user, from a functionality perspective". If changing the BLCKSZ makes things faster or slower, then fine; if it changes the meaning of various random configuration parameters, makes certain schemas work or not work, and makes other changes to postgres functionality, then it's not fine. -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Bumping block size to 16K on FreeBSD...
On Thu, 28 Aug 2003, David Schultz wrote: > (2) Make BLCKSZ a runtime constant, stored as part of the database. Now this I really like. It would make benchmarking 8K vs. 16K blocksizes much easer, as well as of course avoiding the "initdb required after rebuilding" problem. BTW, pretty much every BSD system is going to be using 16K block sizes on large partitions; the cylinder group size and filesystem overhead is way, way too small when using 8K blocks. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.NetBSD.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] FE/BE Protocol - Specific version
On Fri, 2003-08-29 at 13:23, Tom Lane wrote: > Bruce Badger <[EMAIL PROTECTED]> writes: > > Will it be possible to tell a PostgreSQL back end to use *only* the new > > version of the FE/BE protocol? > > That is, will it be possible to set up a database that will reject > > connection attempts that do not use the new protocol version? > > I cannot imagine a reason that that would be a good idea ... but > convince me if you can ... Well, I'm asking because of a specific application. StORE is a version control system for VisualWorks Smalltalk. PostgreSQL is often the chosen back end for StORE. There are many publicly accessible StORE repositories running on PostgreSQL. The StORE to PostgreSQL mapping code currently encodes Byte arrays using Base64, and stores them in bytea fields. It should not have happened like this, but it did - my fault, a stop-gap got used before the gap was properly filled. I would like to migrate to using escaped byte arrays in a new version of the StORE->PostgreSQL mapping software, but we have to be able to accommodate the mixture of old and new databases, and old and new client libraries. I would prefer to have each StORE database have only one encoding used throughout. So "old" databases would still use Base64, and "new" databases would use escaped strings. The new databases will contain an additional table indicating the version of the StORE mapping library they were created by, and this will enable newer implementations to use the correct encoding in any given situation. The old mapping library, of course, will just keep on trying to use the old Base64 encoding. As the new FE/BE protocol is not supported by the "old" mapping library, we could stop old versions of the library getting confused (and perhaps corrupting) "new" databases by setting new databases to refuse connections using old FE/BE protocol version. So, being able to stop connections trying to use old protocol versions would be very helpful in this case. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nasty problem in hash indexes
On Thu, 28 Aug 2003, Neil Conway wrote: > On Thu, Aug 28, 2003 at 05:37:39PM -0400, Tom Lane wrote: > > > If so, then how many other bugs are lurking in the hash index code > > > waiting to bite? > > > > Who's to say? We've found bugs in the btree logic recently, > > too. > > I'd rather print a loud warning when a hash index is created, but keep > the code in the tree, than just remove it entirely. That way, we'll > avoid unnecessary bit-rot to some degree, and if someone feels that > they absolutely positively need hash indexes, they will have some > existing work to begin from. Sorry, but if hash indexes really do present a possible race condition where you could get a short read WITH NO ERROR, then they should at least be commented out and if you create one USING HASH we should print a notice that we're actually creating a btree for you and hash has been deprecated at this point. I can see leaving the code in as something to work on, but you shouldn't have to worry about whether or not your database is gonna have a short read without an error. Postgresql's philosophy has always seemed to be correctness first, convenience and performance second. I like that philosophy, compared to many other databases out there. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Nasty problem in hash indexes
"scott.marlowe" <[EMAIL PROTECTED]> writes: > On Thu, 28 Aug 2003, Neil Conway wrote: >> On Thu, Aug 28, 2003 at 05:37:39PM -0400, Tom Lane wrote: >>> Who's to say? We've found bugs in the btree logic recently, >>> too. >> >> I'd rather print a loud warning when a hash index is created, but keep >> the code in the tree, than just remove it entirely. > Postgresql's philosophy has always seemed to be correctness first, > convenience and performance second. I agree --- we either fix this bug or remove hash indexes. There's no third choice. However, I don't agree with killing hash indexes just because there *might* be more bugs in them. If we have an impractical- to-fix bug in front of us, then it's time for harsh measures, but otherwise ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nasty problem in hash indexes
On Fri, 29 Aug 2003, Tom Lane wrote: > "scott.marlowe" <[EMAIL PROTECTED]> writes: > > On Thu, 28 Aug 2003, Neil Conway wrote: > >> On Thu, Aug 28, 2003 at 05:37:39PM -0400, Tom Lane wrote: > >>> Who's to say? We've found bugs in the btree logic recently, > >>> too. > >> > >> I'd rather print a loud warning when a hash index is created, but keep > >> the code in the tree, than just remove it entirely. > > > Postgresql's philosophy has always seemed to be correctness first, > > convenience and performance second. > > I agree --- we either fix this bug or remove hash indexes. There's no > third choice. However, I don't agree with killing hash indexes just > because there *might* be more bugs in them. If we have an impractical- > to-fix bug in front of us, then it's time for harsh measures, but > otherwise ... Sorry if I gave the impression earlier that we should get rid of hash indexes because there might be more bugs. I didn't really mean it that way. I just meant that if this one was going to be a hard fix, then that might be one of the mitigating factors for how much work someone's going to be willing to put into this. If it's an easy fix then it's likely worth the effort to keep the hash indexes around. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bumping block size to 16K on FreeBSD...
I am not 100% sure that 16K blocksize is the best size, for instance : Using FreebSD 5.1 - I got the best read and write performance using a blocksize of 32K with 4K fragments - [ reading and writing 8K blocks, ufs1 and ufs2 fs ]. I dont have the results in front of me, but I think I tried fs blocksizes from 4K upwards I am also not convinced that using 16K in Pg will be better than 8K (you would expect sequential performance to improve, but maybe at the expense of random ) regards Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ALTER TABLE
Rod Taylor kirjutas R, 29.08.2003 kell 03:31: > I've been fiddling away on ALTER TABLE and have a few questions about a > possible datatype change. Just alter portions. > > I presume I'll need to do a table rewrite. What is the best way to > change a single datum? heap_modify() takes a single relation type where > I will need to deal with different types. Simply build a new tuple with > old datums (easy enough)? Do we care about OIDs being renumbered? AFAIK alter table change column should do the equivalent of alter table x add column temp_name newdatatype; update table x set temp_name=convert(name); alter table x drop colum name; alter table x rename column temp_name to name; This should not renumber OIDS. > ALTER TABLE test ADD CHECK(col > 4), > add column bob integer default 2 not null, > add column bob2 serial check(bob2 <= 255), > drop column col2 cascade; or with your combined syntax alter table x add column temp_name newdatatype = convert(current_name), drop column current_name, rename column temp_name tocurrent_ name; --- Hannu ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Weird constraint output
Christopher Kings-Lynne wrote: I have changed psql to use pg_get_viewdef(oid, true). I agree with Tom for not using it in dumps just yet though. While there still might be a pg_dump option to do this. Is there a function for getting nice constraint defs? Of course there is, use pg_get_constraintdef(text, bool) for this. pg_get_constraintdef(text, bool) pg_get_expr(text, oid, bool) pg_get_indexdef(text, int4, bool) pg_get_ruledef(text, bool) pg_get_viewdef(text, bool) all have the pretty-print bool. Note that pg_get_indexdef's second parameter selects if the complete definition is returned (=0) or only that column's definition. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] pgsql 7.4b2 bug on column defaults?
Hi, just testing beta2 I got strange things on default values with functions: create table test2 (i integer, t timestamp default 'now()'); test1=# \d test2 Table "public.test2" Column |Type | Modifiers +-+--- i | integer | t | timestamp without time zone | default '29/08/2003 11:26:23.937421'::timestamp without time zone test1=# insert into test2 values(1); INSERT 9841455 1 test1=# insert into test2 values(1); INSERT 9841456 1 test1=# insert into test2 values(1); INSERT 9841457 1 test1=# select * from test2; i | t ---+ 1 | 29/08/2003 11:26:23.937421 1 | 29/08/2003 11:26:23.937421 1 | 29/08/2003 11:26:23.937421 (3 rows) Seems that functions are valuated when the table is created, It is a known change or a bug ? thanks, Giuseppe -- --- Giuseppe Tanzilli [EMAIL PROTECTED] CSF Sistemi srl phone ++39 0775 7771 Via del Ciavattino Anagni FR Italy smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] pgsql 7.4b2 bug on column defaults?
Hi, I see it work without quotes around function, but I got it from a 7.2 dump with quotes around it. Maybe it require a note for people dumping from 7.2, I don't have 7.3 around to try, sorry bye Giuseppe Tanzilli - CSF wrote: Hi, just testing beta2 I got strange things on default values with functions: create table test2 (i integer, t timestamp default 'now()'); test1=# \d test2 Table "public.test2" Column |Type | Modifiers +-+--- i | integer | t | timestamp without time zone | default '29/08/2003 11:26:23.937421'::timestamp without time zone test1=# insert into test2 values(1); INSERT 9841455 1 test1=# insert into test2 values(1); INSERT 9841456 1 test1=# insert into test2 values(1); INSERT 9841457 1 test1=# select * from test2; i | t ---+ 1 | 29/08/2003 11:26:23.937421 1 | 29/08/2003 11:26:23.937421 1 | 29/08/2003 11:26:23.937421 (3 rows) Seems that functions are valuated when the table is created, It is a known change or a bug ? thanks, Giuseppe -- --- Giuseppe Tanzilli [EMAIL PROTECTED] CSF Sistemi srl phone ++39 0775 7771 Via del Ciavattino Anagni FR Italy smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] Obscure: correctness of lock manager???
Hi Tom,
the problem persists, even when starting from scratch. I did the following:
# wget
ftp://ftp.de.postgresql.org/mirror/postgresql/source/v7.3.4/postgresql-7.3.4.tar.gz
# tar xzf postgresql-7.3.4.tar.gz
# cd postgresql-7.3.4/
# cat ../mypatch
--- src/backend/storage/lmgr/lock.c~2002-11-01 01:40:23.0 +0100
+++ src/backend/storage/lmgr/lock.c 2003-08-29 11:23:02.0 +0200
@@ -467,6 +467,8 @@
LWLockAcquire(masterLock, LW_EXCLUSIVE);
+ printf("lock\n"); fflush(stdout);
+
/*
* Find or create a lock with this tag
*/
@@ -682,8 +684,13 @@
/*
* Sleep till someone wakes me up.
*/
+
+ printf("before wait\n"); fflush(stdout);
+
status = WaitOnLock(lockmethod, lockmode, lock, holder);
+ printf("after wait\n"); fflush(stdout);
+
/*
* NOTE: do not do any material change of state between here and
* return. All required changes in locktable state must have been
# patch -p0 < ../mypatch
# gmake
# gmake install
After running DBT3 with scale factor 0.025 and 8 concurrent processes:
$ wc -l run/dbt3_logfile
51941 run/dbt3_logfile
$ grep lock run/dbt3_logfile | wc -l
51941
$ grep wait run/dbt3_logfile | wc -l
0
Well, I just added three printf() statements. I cannot imagine
how that could break postgresql.
I repeated the test with following additional modifications:
# cat ../mypatch2
--- src/backend/storage/lmgr/lock.c~2003-08-29 11:26:37.0 +0200
+++ src/backend/storage/lmgr/lock.c 2003-08-29 11:57:26.0 +0200
@@ -39,6 +39,7 @@
#include "utils/memutils.h"
#include "utils/ps_status.h"
+#include
/* This configuration variable is used to set the lock table size */
intmax_locks_per_xact; /* set by guc.c */
@@ -1160,6 +1161,7 @@
ProcLockWakeup(lockMethodTable, lock);
LWLockRelease(masterLock);
+ sched_yield();
return TRUE;
}
@@ -1337,6 +1339,8 @@
elog(LOG, "LockReleaseAll: done");
#endif
+ sched_yield();
+
return TRUE;
}
This should lead to very heavy scheduling, such that processes are
better interleaved. After running DBT3: same result.
With my other patch producing thorough log output, the sched_yield()
leads to higher probability for observing badly granted locks.
So it is very unlikely that my printf()s and postprocessing of the
logfile leads to that problem. I have even observed cases where
the error occurs within the first 10 locks, such that I can
compute the lock state by hand and verify by hand that there really
exist locks of mode 7 which are granted in parallel to different
processes.
Although I cannot be sure that my environment (kernel, libc,
compiler, ...) produces that behaviour, I think that there
remains some probability for a bug in the lock manager. I have
repeated the tests on two different machines, one of them a
dual-processor Athlon MP-1900+, the other a single processor
Athlon 3000+. OK, both systems are running Redhat 9, so there
remain some chances that something very obscure happens on the
OS level which is reproducible on both systems.
In order to find out possible OS effects, the above tests should
be repeated by other people on other platforms. Please, if anyone
could kindly do that, report the results here.
Tom, it sounds really strange, and I also cannot nearly believe it,
but I could imagine why that problem (if it really exists) was
not detected before. The following is no claim, it is just an idea
how it could have happened. Please don't take it as a personal
threat, I just want to explain that it _could_ be possible that
a non-working lock manager has not led to any noticable problems.
Also, I don't want to stimulate a discussion whether the following
is right or not. It could be wrong.
(1) Most of the locks are con-conflicting by nature.
(2) If I understand it right, read-only txns use time-domain-addressing
and thus never conflict with any other txns. Only read-write
txns can ever produce races on data.
(3) Ciritical regions are often only a small percentage of the overall
running time of a process.
(4) Rescheduling by the OS occurs not when processes are woken up,
but rather only when a process blocks for itself or when a
timer interrupt occurs.
(5) Current processors are by a factor of 10 million faster than
timer interrupts (typically 100/s). When a process does not
block for itself, it will be interrupted only after 10 million
instructions in average. Thus the probability to hit a critical
region just in that seldom moment is extremely low.
(6) I ran my tests on extremely small databases which fit in the
buffer cache of the OS. Real-world apps are doing much more
physical disk IO. At disk IO, rescheduling _always_ occurs at
the same place. When processes are running less than 10ms u
Re: [HACKERS] ALTER TABLE
On Thu, 2003-08-28 at 22:06, Christopher Kings-Lynne wrote:
> Do you have this working:
>
> ALTER TABLE / ADD COLUMN .. DEFAULT nextval('asdf')...
>
> (eg. a default that needs to be re-evaluated for each row)
>
> Also, did you remember about checking domain constraints, etc.?
Yes, and all necessary checks are performed.
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] ALTER TABLE
On Fri, 2003-08-29 at 04:22, Hannu Krosing wrote: > Rod Taylor kirjutas R, 29.08.2003 kell 03:31: > > I've been fiddling away on ALTER TABLE and have a few questions about a > > possible datatype change. Just alter portions. > > > > I presume I'll need to do a table rewrite. What is the best way to > > change a single datum? heap_modify() takes a single relation type where > > I will need to deal with different types. Simply build a new tuple with > > old datums (easy enough)? Do we care about OIDs being renumbered? > > AFAIK alter table change column should do the equivalent of > > alter table x add column temp_name newdatatype; > update table x set temp_name=convert(name); > alter table x drop colum name; > alter table x rename column temp_name to name; I presume we want the attnum needs to be preserved to preserve the constraints that are pointing at the column. Yes, constraints may need a cast injects (view too), but if we're not going to do that there is little advantage over doing the job by hand. as the above 4 step process. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Obscure: correctness of lock manager???
Thomas Schoebel-Theuer <[EMAIL PROTECTED]> writes:
> the problem persists, even when starting from scratch. I did the following:
> + printf("lock\n"); fflush(stdout);
> +
> $ grep lock run/dbt3_logfile | wc -l
I'd bet that your logfile is not accumulating postmaster stdout, but
only stderr. Or maybe not even stderr --- where are you getting it
from exactly? Perhaps you're logging client-side output, rather than
that of the backends.
> Tom, it sounds really strange, and I also cannot nearly believe it,
> but I could imagine why that problem (if it really exists) was
> not detected before.
If I actually believed your experiment, it would prove that LockAcquire
wasn't being called at all. It is trivial to demonstrate that this is
not so, eg, attach to a backend with gdb and set a breakpoint at
LockAcquire. Or, if you'd like some more macroscopic proof that the
lock manager is doing something, try this:
psql session 1:
create table a(f1 int);
create table b(f1 int);
begin;
lock table a;
psql session 2:
begin;
lock table b;
lock table a;
-- note that it blocks waiting for session 1's lock
back in session 1:
lock table b;
-- note deadlock failure report
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] ALTER TABLE
On Thu, 2003-08-28 at 23:22, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > I've been fiddling away on ALTER TABLE and have a few questions about a > > possible datatype change. Just alter portions. > > > I presume I'll need to do a table rewrite. What is the best way to > > change a single datum? heap_modify() takes a single relation type where > > I will need to deal with different types. Simply build a new tuple with > > old datums (easy enough)? Do we care about OIDs being renumbered? > > Have you consulted the archives? ISTM we discussed these issues to > death a year or two back. I thought I had. Google doesn't give anything overly useful in a group search for 'alter table modify' or 'alter table change type' Found a nice long thread on dropping a column though. The discussion about SET / DROP NOT NULL had a little bit on modify. If you could point me in the right direction it would be appreciated. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] FE/BE Protocol - Specific version
> So, being able to stop connections trying to use old protocol versions > would be very helpful in this case. Wouldn't it be better to have StORE run a select version() after connecting? Even better would be to call PQprotocolVersion() as it is a protocol issue. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] FE/BE Protocol - Specific version
Rod Taylor <[EMAIL PROTECTED]> writes: >> So, being able to stop connections trying to use old protocol versions >> would be very helpful in this case. > Wouldn't it be better to have StORE run a select version() after > connecting? Well, his point is that old versions of his client code wouldn't know to do that. However, I don't think that what he's suggesting is a suitable answer either --- he wants to rely on a chance coincidence, namely that we're upgrading the FE/BE protocol at the same time that he wants to make an incompatible application-level change. What I'd do, if I wanted to lock out old clients from accessing particular tables, is just rename the tables to something else. (Or keep using the same names, but put the tables in a schema or database that old clients won't look in.) The clients wouldn't fail very gracefully, perhaps, but the protocol-level hack doesn't qualify as graceful in my book either ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] bug with constraint dependencies? or bug with
Greg Stark <[EMAIL PROTECTED]> writes: > [On that note, one of my pet peeves is that the default names for constraints > are of the form $1 which requires quoting, but that \d doesn't quote them so > that you can almost but not quite copy the \d output into an sql statement > recreating the constraint.] Oh look, that's already better in 7.4. It's hard to keep up with you guys. -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Obscure: correctness of lock manager???
Tom, I just realized that I probably could have misinterpreted the locktag information. This could have caused the conflicts in my postprocessing. Apologies if that was the reason. I'm running further checks to resolve that problem. However, what remains strange is that the lockmanager is never blocking, at least when running DBT3. The short patch shows with high confidence that there is no blocking at all. I'm pretty sure that I got the output of the backend processes, because I included getpid() in the full version of the instrumentation. The stripped-down version showed up the "lock\n" lines, so I am sure that I got the backend output. I'll incude getpid() even there to be really sure. If the lock manager were correct in that never any blocking orrurs in DBT3, then I would have another problem: the experiment would be probably meaningless for my research. In that case, do you know any benchmark / application which leads to heavy conflicts in the lock manager such that blocking occurs? Do you have any clue whether it really could happen that really NEVER any blocking occurs when running the DBT3 benachmark with 8 or 16 parallel backend processes??? Thanks for your patience, Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pgsql 7.4b2 bug on column defaults?
Giuseppe Tanzilli - CSF <[EMAIL PROTECTED]> writes: > create table test2 (i integer, t timestamp default 'now()'); Use "DEFAULT now()". I'm surprised you didn't get a syntax error from the above. > It is a known change or a bug ? It's an intentional change, yes. regards, tom lane ---(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] FE/BE Protocol - Specific version
On Fri, 2003-08-29 at 23:35, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > >> So, being able to stop connections trying to use old protocol versions > >> would be very helpful in this case. > > > Wouldn't it be better to have StORE run a select version() after > > connecting? > > Well, his point is that old versions of his client code wouldn't know to > do that. However, I don't think that what he's suggesting is a suitable > answer either --- he wants to rely on a chance coincidence, namely that > we're upgrading the FE/BE protocol at the same time that he wants to > make an incompatible application-level change. Exactly right. I made a mistake and this is a chance to fix it. Really, just that simple. > What I'd do, if I wanted to lock out old clients from accessing > particular tables, is just rename the tables to something else. > (Or keep using the same names, but put the tables in a schema or > database that old clients won't look in.) The clients wouldn't fail > very gracefully, perhaps, but the protocol-level hack doesn't qualify > as graceful in my book either ... I don't think that there is a graceful way to fix this. Unless I can pull off the "blocking old protocol versions" trick, it seems that the most likely option will come from another chance coincidence, i.e. that the StORE schema changes. Unfortunately, I am not in a position to initiate a change in the StORE schema. StORE is also used with other RDBMSs, and the users of those have no incentive to bless a change in the schema just to help out the PostgreSQL users. So, if it did come to pass that rejecting connections on the basis of protocol version was possible, then I could fix the broken encoding implementation. Otherwise, I think I'll have to wait for the next chance coincidence. ... unless anyone has any better ideas? :-/ All the best, Bruce ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] FE/BE Protocol - Specific version
On Fri, 2003-08-29 at 23:23, Rod Taylor wrote: > > So, being able to stop connections trying to use old protocol versions > > would be very helpful in this case. > > Wouldn't it be better to have StORE run a select version() after > connecting? Even better would be to call PQprotocolVersion() as it is a > protocol issue. Well, if we could compel every existing user of StORE + PostrgeSQL to upgrade to a new version of the mapping software, yes. I think that we must accept that one or more people may hang onto old versions of the mapping software which, of course, would not contain the checks you suggest :-( ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Obscure: correctness of lock manager???
Thomas Schoebel-Theuer <[EMAIL PROTECTED]> writes: > Do you have any clue whether it really could happen that really > NEVER any blocking occurs when running the DBT3 benachmark with 8 > or 16 parallel backend processes??? I haven't looked at dbt3 at all ... what does it do? We do make a point of trying to prevent concurrent readers and writers from blocking each other, so depending on how the benchmark is set up, a low probability of lock conflict is certainly possible. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] massive quotes?
On Thu, 28 Aug 2003, Andrew Dunstan wrote: > What is the state of things regarding having to use massive strings of > quotes like this (taken from the 7.3 docs)?: > > a_output := a_output || '' if v_'' || > referrer_keys.kind || '' like '' > || referrer_keys.key_string || '' > then return '' || referrer_keys.referrer_type > || ''; end if;''; > > This is truly ugly, IMNSHO. Perl has its q() construct - any chance of > us doing something here? > > I'm prepared to put in effort to implement a solution if there is > agreement on what the solution should be. Maybe some sort of magical > operator/function? I was thinking the most natural thing would be to use something similar to COPY's stdin quoting: CREATE FUNCTION bob() RETURNS INTEGER AS stdin LANGUAGE 'plpgsql'; BEGIN ... END; \. Another possibility would be shell/Perl-style here documents, like this: CREATE FUNCTION bob() RETURNS INTEGER AS <
Re: [HACKERS] massive quotes?
Jon Jensen wrote: On Thu, 28 Aug 2003, Andrew Dunstan wrote: What is the state of things regarding having to use massive strings of quotes like this (taken from the 7.3 docs)?: a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '' || referrer_keys.key_string || '' then return '' || referrer_keys.referrer_type || ''; end if;''; This is truly ugly, IMNSHO. Perl has its q() construct - any chance of us doing something here? I'm prepared to put in effort to implement a solution if there is agreement on what the solution should be. Maybe some sort of magical operator/function? I was thinking the most natural thing would be to use something similar to COPY's stdin quoting: CREATE FUNCTION bob() RETURNS INTEGER AS stdin LANGUAGE 'plpgsql'; BEGIN ... END; \. Another possibility would be shell/Perl-style here documents, like this: CREATE FUNCTION bob() RETURNS INTEGER AS < The former seems preferable since it uses a syntax PostgreSQL already supports in another context. Jon Nice idea. I would probably never have thought of it :-) Makes function text almost first class, in the way that Oracle's is AFAICS, I also prefer the first version, not least because it hoists the LANGUAGE clause to the top where it seems to me it belongs. I have no idea how hard this would be. cheers andrew ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] FE/BE Protocol - Specific version
Bruce Badger <[EMAIL PROTECTED]> writes: > Unfortunately, I am not in a position to initiate a change in the StORE > schema. StORE is also used with other RDBMSs, and the users of those > have no incentive to bless a change in the schema just to help out the > PostgreSQL users. Hm, I must have misunderstood what you wanted to do, because it sure sounded like a schema change to me. But anyway --- although I do not want to offer this as a supported option, there is nothing to stop you from altering PG_PROTOCOL_EARLIEST in your build. See src/include/libpq/pqcomm.h. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Bumping block size to 16K on FreeBSD...
On Fri, Aug 29, 2003 at 12:06:59AM -0300, Marc G. Fournier wrote: > The thing is ... there has been presented a strong, valid reason for > moving to 16k (at least under FreeBSD) ... and there has been a valid It sounds to me, actually, like there is a strong reason for telling people running FreeBSD, "Hey, you can get this big speedup at the possible expense of compatibility by compiling with changes XYZ." But quietly putting that into packages for distribution strikes me as the sort of support headache that one really doesn't want. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] FE/BE Protocol - Specific version
On Friday 29 August 2003 15:37, Bruce Badger wrote: > On Fri, 2003-08-29 at 23:35, Tom Lane wrote: > > Rod Taylor <[EMAIL PROTECTED]> writes: > > >> So, being able to stop connections trying to use old protocol versions > > >> would be very helpful in this case. > > > > > > Wouldn't it be better to have StORE run a select version() after > > > connecting? > > > > Well, his point is that old versions of his client code wouldn't know to > > do that. However, I don't think that what he's suggesting is a suitable > > answer either --- he wants to rely on a chance coincidence, namely that > > we're upgrading the FE/BE protocol at the same time that he wants to > > make an incompatible application-level change. > So, if it did come to pass that rejecting connections on the basis of > protocol version was possible, then I could fix the broken encoding > implementation. Otherwise, I think I'll have to wait for the next > chance coincidence. > > ... unless anyone has any better ideas? :-/ 1. Run "new" versions of the database on a different port - only the new client will know to look there. 2. Write a small proxy to simulate #1 (in case PG is serving other clients) - only allow access to the StORE db from localhost 3. Write a small proxy to determine which version of the protocol is in use, and allow/deny access as required. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] FE/BE Protocol - Specific version
On Fri, 2003-08-29 at 12:09, Tom Lane wrote:
> Bruce Badger <[EMAIL PROTECTED]> writes:
> > Unfortunately, I am not in a position to initiate a change in the StORE
> > schema. StORE is also used with other RDBMSs, and the users of those
> > have no incentive to bless a change in the schema just to help out the
> > PostgreSQL users.
>
> Hm, I must have misunderstood what you wanted to do, because it sure
> sounded like a schema change to me. But anyway --- although I do not
> want to offer this as a supported option, there is nothing to stop you
> from altering PG_PROTOCOL_EARLIEST in your build. See
> src/include/libpq/pqcomm.h.
>
Tom, just curious as to what your resistance is to this feature? ISTM
that making this admin modifiable doesn't hurt anyone and could be
helpful to some people.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] FE/BE Protocol - Specific version
Robert Treat <[EMAIL PROTECTED]> writes: > Tom, just curious as to what your resistance is to this feature? ISTM > that making this admin modifiable doesn't hurt anyone and could be > helpful to some people. Admin modifiable at what level? I don't believe that the "feature" is valuable enough to warrant defining, implementing, and documenting a GUC variable, let alone adding a pg_hba.conf column which one could imagine someone wanting instead. I suggested a simple source code change, and I think that's quite sufficient when we have only one request for it with a not-very-compelling rationale. There are much more useful tweaks (eg, altering BLCKSZ) that we have equivalent levels of support for. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [GENERAL] before trigger problem
http://developer.postgresql.org/ftpsite/binary/v7.3.4/RPMS/
Robert Treat
On Thu, 2003-08-28 at 11:18, Marie G. Tuite wrote:
> Same issue - are there rpms anywhere for 7.3.4?
>
> Thanks.
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane
> Sent: Wednesday, August 27, 2003 10:43 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED] Org; [EMAIL PROTECTED]
> Subject: Re: [GENERAL] before trigger problem
>
>
> "Marie G. Tuite" <[EMAIL PROTECTED]> writes:
> > I have a problem with a before trigger raising a heap_mark4update error.
> It
> > happens when there are many updates on the same key. OIDs are off
> > presumably? Am running 7.3.2 on RH 7.3. Have looked around the archives
> > and found nothing useful - a mention of a known issue and a potential
> patch?
>
> Is this the same issue described at
> http://archives.postgresql.org/pgsql-hackers/2003-03/msg01099.php
> ? If so, update to 7.3.4.
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
