Re: [HACKERS] Minimally avoiding Transaction Wraparound in VLDBs
On Thu, Sep 22, 2005 at 03:52:21PM +1000, Gavin Sherry wrote: On Wed, 31 Aug 2005, Tom Lane wrote: BTW ... the original Berkeley papers on Postgres make frequent reference to a vacuum daemon, which seems to be essentially what we're trying to build with autovacuum. Does anyone know if the Berkeley implementation ever actually had auto vacuuming, or was that all handwaving? If it did exist, why was it removed? Well, I was just poking around the executor and noticed this in ExecDelete(): /* * Note: Normally one would think that we have to delete index tuples * associated with the heap tuple now.. * * ... but in POSTGRES, we have no need to do this because the vacuum * daemon automatically opens an index scan and deletes index tuples * when it finds deleted heap tuples. -cim 9/27/89 */ I have an idea who this might be :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(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] Parser bug results in ambiguous errors/behaviour
Hi, A bug/short coming in the parser leads to some pretty ambiguous errors and/or foot shooting. Consider the following: template1=# create table foo(i int, b bool, t text); CREATE TABLE template1=# insert into foo values(1, 'f', 'foo'); INSERT 0 1 template1=# update foo set i=2,b='t' and t='bar' where i=1; UPDATE 1 Now there's an error in the SQL: b='t' AND t='bar'. We don't detect this. Result: template1=# select * from foo; i | b | t ---+---+- 2 | f | foo (1 row) It gets more interesting: template1=# update foo set b='t', i=2 and t='bar' where i=1; ERROR: argument of AND must be type boolean, not type integer Now, obviously the SQL is invalid but I think we should detect it. This happens in HEAD, 8.0 and 7.2 -- and I presume other releases. Comments? Thanks, Gavin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Minimally avoiding Transaction Wraparound in VLDBs
On Sep 22, 2005, at 3:55 PM, David Fetter wrote: On Thu, Sep 22, 2005 at 03:52:21PM +1000, Gavin Sherry wrote: On Wed, 31 Aug 2005, Tom Lane wrote: Well, I was just poking around the executor and noticed this in ExecDelete(): /* * Note: Normally one would think that we have to delete index tuples * associated with the heap tuple now.. * * ... but in POSTGRES, we have no need to do this because the vacuum * daemon automatically opens an index scan and deletes index tuples * when it finds deleted heap tuples. -cim 9/27/89 */ I have an idea who this might be :) giof http://www.taylors.org/cim/resume/cimarron.html Michael Glaesemann grzm myrealbox 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] Table Partitioning is in 8.1
On Wed, 2005-09-21 at 15:39 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Is it possible that the Release Notes do not fully explain the Constraint Exclusion feature? Or is it the consensus that it works but not quite well enough to make a song and dance about yet? I hardly think that the existing constraint-exclusion code is enough for us to claim we support table partitioning. There's too much grunt work that the DBA still has to do to set up a partitioning arrangement. So you think the DBA can do partitioning? Good. Setting up partitioning in Oracle or SQLServer2005 requires lots of syntax and multiple commands. There are fewer commands with PostgreSQL and they are ISO/ANSI compliant also. I think there is much still left to do with partitioning, so I would be the first to say that this is only the beginning. I know you are wary of overstating capabilities; so am I, but it looks like we differ slightly on where to draw the line. On reflection, the only changes I suggest are: 1) the phrase This allows for a type of table partitioning have the word basic inserted within it to become: This allows for a basic type of table partitioning 2) placing CE as a major feature of 8.1 - many people regard it at least as highly as other optimizations, though this is subjective based upon their application requirements Suggestion (2) might be seen as some kind of vanity, so having raised the issue I'll leave the floor open to others to agree or not. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Parser bug results in ambiguous errors/behaviour
Gavin Sherry wrote: A bug/short coming in the parser leads to some pretty ambiguous errors and/or foot shooting. Consider the following: template1=# create table foo(i int, b bool, t text); CREATE TABLE template1=# insert into foo values(1, 'f', 'foo'); INSERT 0 1 template1=# update foo set i=2,b='t' and t='bar' where i=1; UPDATE 1 Read it as: update foo set=2, b=('t' and t='bar') where i=1; This works because: 't' can be translated to boolean true, t='bar' to boolean false, (true and false) becomes false, of course. template1=# select * from foo; i | b | t ---+---+- 2 | f | foo (1 row) Seems to be the correct result, at least if the syntax without parenthesis is allowed by the SQL spec. It gets more interesting: template1=# update foo set b='t', i=2 and t='bar' where i=1; ERROR: argument of AND must be type boolean, not type integer update foo set b='t', i=(2 and t='bar') where i=1; This is supposed to fail. There is no (at least implicit) cast from integer to boolean. So 2 cannot be converted to a boolean value and the boolean AND operator fails. It comes down to the question if the query is valid syntax in the first place. The answers PostgreSQL gives are correct nevertheless. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: R: [HACKERS] feature proposal ...
On Wed, 2005-09-21 at 11:31 -0400, Tom Lane wrote: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: Paolo Magnoli wrote: Can't you just use a view? no because a new is not a heap ... I think Paolo's idea is much better than munging the syntax of COPY, though. Fixing COPY so that you *could* copy from a view would provide all the desired functionality without any syntactic warts. Well, I will probably help Juergen with the implementation. It seems that fetch data from VIEW is possible by portal stuff. Tom, do you think that there's any other (better) way how we can implement it? Karel -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Parser bug results in ambiguous errors/behaviour
Gavin Sherry [EMAIL PROTECTED] writes: template1=# update foo set i=2,b='t' and t='bar' where i=1; UPDATE 1 This is perfectly legal SQL. If it doesn't do what you intended, well, too bad. We're not going to fix it. 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] Parser bug results in ambiguous errors/behaviour
On Thu, 22 Sep 2005, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: template1=# update foo set i=2,b='t' and t='bar' where i=1; UPDATE 1 This is perfectly legal SQL. If it doesn't do what you intended, well, too bad. We're not going to fix it. Hmmm. Okay. It wasn't that I intended it to do anything -- it just looked incorrect. Thanks, Gavin ---(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] feature proposal ...
Tom Lane [EMAIL PROTECTED] writes: So we could refute this argument by just not making the permission check for CREATE TEMP VIEW. This is the first time I've ever heard of CREATE TEMP VIEW. What's the point of it since you can always directly do: SELECT * FROM (...) ? -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Why does VACUUM FULL bother locking pages?
On Fri, Sep 16, 2005 at 11:50:21PM -0700, Simon Riggs wrote: Alvaro Herrera wrote The only caller of both is repair_frag, whose only caller in turn is full_vacuum_rel. ...bgwriter still needs to access blocks. The WAL system relies on the locking behaviour for recoverability, see comments in LockBuffer() and SyncOneBuffer(). Oh, certainly! In this case, may I point out that scan_heap() does not bother locking pages, mentioning that we assume that holding exclusive lock on the relation will keep other backends from looking at the page. In particular, it calls PageRepairFragmentation which runs with the page unlocked AFAICT. Seems like a bug to me. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 Now I have my system running, not a byte was off the shelf; It rarely breaks and when it does I fix the code myself. It's stable, clean and elegant, and lightning fast as well, And it doesn't cost a nickel, so Bill Gates can go to hell. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] feature proposal ...
Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: So we could refute this argument by just not making the permission check for CREATE TEMP VIEW. This is the first time I've ever heard of CREATE TEMP VIEW. What's the point of it since you can always directly do: SELECT * FROM (...) ? Separation of concerns, for one thing: the code using the view need not know the exact contents of the select, or perhaps not even have permissions to access the underlying tables. (Think of a temp view created by a SECURITY DEFINER function for instance.) In this particular case it gives us separation of concerns in a different way, ie, we don't have to figure out a way to force-fit the complete syntax of SELECT inside a COPY command. 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
[HACKERS] Hierarchical Queries--Stalled No Longer...
Hey everyone, Evgen Potemkin has granted me a BSD license on the patch for hierarchical queries (WITH and CONNECT BY) and I'd like to get it on track for PostgreSQL 8.2. Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch? If so, what comments do you have as to what you'd like to see. In the mean time, I'll pull 8.1, patch it, write some regression tests, test it, and submit it back. BTW, what's the estimated schedule of 8.2?-- Respectfully,Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporationhttp://www.enterprisedb.com/
Re: [HACKERS] Why does VACUUM FULL bother locking pages?
On Thu, Sep 22, 2005 at 10:36:41AM -0400, Alvaro Herrera wrote: On Fri, Sep 16, 2005 at 11:50:21PM -0700, Simon Riggs wrote: Alvaro Herrera wrote The only caller of both is repair_frag, whose only caller in turn is full_vacuum_rel. ...bgwriter still needs to access blocks. The WAL system relies on the locking behaviour for recoverability, see comments in LockBuffer() and SyncOneBuffer(). Oh, certainly! In this case, may I point out that scan_heap() does not bother locking pages, mentioning that we assume that holding exclusive lock on the relation will keep other backends from looking at the page. In particular, it calls PageRepairFragmentation which runs with the page unlocked AFAICT. Looking again, PageRepairFragmentation is called on a copy of the page, not on the page itself, so this is not a problem. The page is only modified to exchange old Xids for FrozenTransactionId, or to set some hint bits, so this really shouldn't be too much of a problem. I still think it would be better to lock the page beforehand. -- Alvaro Herrera Architect, http://www.EnterpriseDB.com Uno puede defenderse de los ataques; contra los elogios se esta indefenso ---(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] Why does VACUUM FULL bother locking pages?
Alvaro Herrera [EMAIL PROTECTED] writes: Oh, certainly! In this case, may I point out that scan_heap() does not bother locking pages, mentioning that we assume that holding exclusive lock on the relation will keep other backends from looking at the page. In particular, it calls PageRepairFragmentation which runs with the page unlocked AFAICT. Seems like a bug to me. I agree --- and a pretty silly one considering that there are LockBuffer calls elsewhere in vacuum.c. Wonder how old that code is ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] logging blemishes
Are you saying connection received should honor %q? It seems it is a session line, rather than a server line, no? Maybe, the line just struck me as rather ugly. Never mind. Now that you mention it, the log line for connections does look wrong. [local] doesn't have remote port numbers like tcp does. The remote port is the same number as the server port. I am thinking we should suppress the 'port=' output for local connections. We properly suppress the port number in parentheses for log_line_prefix='%r'. Done. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Hierarchical Queries--Stalled No Longer...
On Thu, Sep 22, 2005 at 11:19:13AM -0400, Jonah H. Harris wrote: Hi, Evgen Potemkin has granted me a BSD license on the patch for hierarchical queries (WITH and CONNECT BY) and I'd like to get it on track for PostgreSQL 8.2. Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch? If so, what comments do you have as to what you'd like to see. Tom has repeteadly said the patch is more or less useless, and that if you wanted to implement this feature you'd better start from scratch. I've looked at the patch a couple of times and I somewhat agree with this, though I don't remember what my reservations were. One important point is that CONNECT BY is not really SQL syntax, is it? In this case, I think you should pull out the CONNECT BY part and implement only WITH, which is the SQL-mandated syntax AFAIK. One point with the patch AFAIR is that it didn't try to optimize the query at all, which may be OK as a first cut but for a real-world implementation you really need it to do. -- Alvaro Herrerahttp://www.advogato.org/person/alvherre Oh, great altar of passive entertainment, bestow upon me thy discordant images at such speed as to render linear thought impossible (Calvin a la TV) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Hierarchical Queries--Stalled No Longer...
Alvaro, I agree, there are some things that need to be done before calling it a done-deal including some planning, commenting, optimizer stuff, etc. Also, for PostgreSQL reasons, I agree that supporting ANSI/ISO WITH is the best option; I'm willing to take on implementation for both if you guys want. -Jonah On 9/22/05, Alvaro Herrera [EMAIL PROTECTED] wrote: On Thu, Sep 22, 2005 at 11:19:13AM -0400, Jonah H. Harris wrote:Hi, Evgen Potemkin has granted me a BSD license on the patch for hierarchical queries (WITH and CONNECT BY) and I'd like to get it on track for PostgreSQL 8.2. Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch? If so, what comments do you have as to what you'd like to see.Tom has repeteadly said the patch is more or less useless, and that if you wanted to implement this feature you'd better start from scratch.I've looked at the patch a couple of times and I somewhat agree withthis, though I don't remember what my reservations were.One important point is that CONNECT BY is not really SQL syntax, is it?In this case,I think you should pull out the CONNECT BY part and implement only WITH,which is the SQL-mandated syntax AFAIK.One point with the patch AFAIR is that it didn't try to optimize the query at all, which may be OK as a first cut but for a real-worldimplementation you really need it to do.--Alvaro Herrerahttp://www.advogato.org/person/alvherreOh, great altar of passive entertainment, bestow upon me thy discordant imagesat such speed as to render linear thought impossible (Calvin a la TV) -- Respectfully,Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporationhttp://www.enterprisedb.com/
Re: [HACKERS] feature proposal ...
Added to TODO: o Allow COPY to output from views --- Andrew Dunstan wrote: Rod Taylor wrote: On Wed, 2005-09-21 at 15:25 -0700, Trent Shipley wrote: Wouldn't you also need a CREATE TEMP TABLE privilege but the COPY TO file USING select_statement would only need select. (In other words using a temp table would not seem to be as secure nor as general as the requested feature.) Writing a file on the server requires significant privilege, including access to the server itself so you can retrieve the results. But we also do COPY to STDOUT which requires no special privileges on the server. Incidentally, if we are going to allow copy out from views, it would be nice and orthogonal to allow copy in too. Hasn't there been some talk about making automatically writeable views? cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Why does VACUUM FULL bother locking pages?
On Thu, 2005-09-22 at 10:36 -0400, Alvaro Herrera wrote: Seems like a bug to me. Well done. This wins the award for best bug found during beta; shame it wasn't 8.0 beta! Just as well we recommend only doing VACUUM FULL when the system is quiet Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] What has happened to pgxs?
Thomas Hallgren wrote: Hi, I tried to compile PL/Java against PostgreSQL 8.1beta2. I use pgxs and until now that has been just fine. Now pgxs suggests that the include files reside under /usr/local/pgsql. They don't of course. Not on my windows box anyway :-) I think it stems from the src/Makefile.global. On line 59 it reads: prefix := /usr/local/pgsql I guess I'm missing something. Can someone explain to me what I need to change? Take a look at Makefile.global.in. That value is defined by the --prefix flag when you run configure. The default is /usr/local/pgsql, and I am guessing you need to change that default on Win32. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] What has happened to pgxs?
Bruce Momjian wrote: Thomas Hallgren wrote: Hi, I tried to compile PL/Java against PostgreSQL 8.1beta2. I use pgxs and until now that has been just fine. Now pgxs suggests that the include files reside under /usr/local/pgsql. They don't of course. Not on my windows box anyway :-) I think it stems from the src/Makefile.global. On line 59 it reads: prefix := /usr/local/pgsql I guess I'm missing something. Can someone explain to me what I need to change? Take a look at Makefile.global.in. That value is defined by the --prefix flag when you run configure. The default is /usr/local/pgsql, and I am guessing you need to change that default on Win32. I'm using a pre-compiled installation of PostgreSQL. All I want to do is use pgxs to be able to compile PL/Java. There is other stuff that seems strange to me. Why do you append 'postgresql' to the include directories further down in the file? I had to remove that in order to compile. Regards, Thomas Hallgren ---(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] Why does VACUUM FULL bother locking pages?
Alvaro Herrera [EMAIL PROTECTED] writes: Looking again, PageRepairFragmentation is called on a copy of the page, not on the page itself, so this is not a problem. The page is only modified to exchange old Xids for FrozenTransactionId, or to set some hint bits, so this really shouldn't be too much of a problem. I still think it would be better to lock the page beforehand. Actually, the case that's a bit worrisome is the PageIsNew path: it'd be possible for a partially-valid page header to be written out. This wouldn't result in data loss, exactly, since there's nothing on the page ... but we might have a problem using the page later. The FrozenTransactionId update case is already presumed to be atomic by vacuumlazy.c, so I don't feel too bad about it, but it surely needs a comment at least. On the whole it seems like we might as well just take the exclusive buffer lock and not try to be cute. AFAICT the other routines in vacuum.c all do proper locking when they are modifying pages, so it's just this one place that is taking a short cut. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Hierarchical Queries--Stalled No Longer...
Jonah H. Harris [EMAIL PROTECTED] writes: Evgen Potemkin has granted me a BSD license on the patch for hierarchical queries (WITH and CONNECT BY) and I'd like to get it on track for PostgreSQ= L 8.2. Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch? If this is the same patch that periodically burns Gentoo users, then yes, we've seen it, and we were unimpressed. If so, what comments do you have as to what you'd like to see. A rewrite from the ground up, and use of SQL-standard syntax (WITH etc) not Oracle-proprietary. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Hierarchical Queries--Stalled No Longer...
Jonah, Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch? If so, what comments do you have as to what you'd like to see. Unfortunately, yes I have. I had to help several users who got burned by it: the patch was buggy as all-get out. For example, it wouldn't access TOAST tables, so any TEXT fields larger than 500chars got cut off; and wouldn't support user-added data types or domains. (This was a year ago, so maybe Evgen fixed these things) So it's really nice of Evgen to re-license, but the license was *not* the primary thing blocking acceptance of the patch. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Hierarchical Queries--Stalled No Longer...
So is postgresql going into the direction of WITH or CONNECT BY (or both)? I am authoring O'Reilly's SQL Cookbook and I'd like to mention it in the Hierarchical chapter to give the pg readers a heads up. Thanks and regards, Anthony Molinaro -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Josh Berkus Sent: Thursday, September 22, 2005 2:02 PM To: pgsql-hackers@postgresql.org; Jonah H. Harris Subject: Re: [HACKERS] Hierarchical Queries--Stalled No Longer... Jonah, Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch? If so, what comments do you have as to what you'd like to see. Unfortunately, yes I have. I had to help several users who got burned by it: the patch was buggy as all-get out. For example, it wouldn't access TOAST tables, so any TEXT fields larger than 500chars got cut off; and wouldn't support user-added data types or domains. (This was a year ago, so maybe Evgen fixed these things) So it's really nice of Evgen to re-license, but the license was *not* the primary thing blocking acceptance of the patch. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(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] Hierarchical Queries--Stalled No Longer...
Anthony, So is postgresql going into the direction of WITH or CONNECT BY (or both)? PostgreSQL would do ONLY with. We're not interested in Oracle-proprietary syntax. That being said, there is a CONNECT_BY() function in /contrib/tablefunc. But this would never be part of the core syntax. I am authoring O'Reilly's SQL Cookbook and I'd like to mention it in the Hierarchical chapter to give the pg readers a heads up. Keen. Please feel free to ask questions so that the PG section can be as accurate as possible. #postgresql on irc.freenode.net is quite active, and you can get any number of Postgres - SQL questions answered there. --Josh -- __Aglio Database Solutions___ Josh BerkusConsultant josh@agliodbs.comwww.agliodbs.com Ph: 415-752-2500Fax: 415-752-2387 2166 Hayes Suite 200San Francisco, CA ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Hierarchical Queries--Stalled No Longer...
Josh, Thanks man, good to know. Sorry if the question was a bit out of place on this list but I wanted to make sure I reached the right people. I love what you guys are doing and made sure postgresql was included in my book. also, while I got your ear. I bugged Simon about this earlier this year and was wondering if you guys are still planning on added the window functions added to the '03 standard? I have a ton of recipes that use them and if you guys are still planning on implementing them, I'd like to mention that as well. Thanks, Anthony -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 2:43 PM To: Anthony Molinaro Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Hierarchical Queries--Stalled No Longer... Anthony, So is postgresql going into the direction of WITH or CONNECT BY (or both)? PostgreSQL would do ONLY with. We're not interested in Oracle-proprietary syntax. That being said, there is a CONNECT_BY() function in /contrib/tablefunc. But this would never be part of the core syntax. I am authoring O'Reilly's SQL Cookbook and I'd like to mention it in the Hierarchical chapter to give the pg readers a heads up. Keen. Please feel free to ask questions so that the PG section can be as accurate as possible. #postgresql on irc.freenode.net is quite active, and you can get any number of Postgres - SQL questions answered there. --Josh -- __Aglio Database Solutions___ Josh BerkusConsultant josh@agliodbs.comwww.agliodbs.com Ph: 415-752-2500Fax: 415-752-2387 2166 Hayes Suite 200San Francisco, CA ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Hierarchical Queries--Stalled No Longer...
Anthony, also, while I got your ear. I bugged Simon about this earlier this year and was wondering if you guys are still planning on added the window functions added to the '03 standard? I have a ton of recipes that use them and if you guys are still planning on implementing them, I'd like to mention that as well. Planning, yes. Have started, no. It's a major feature implementation if we want them to be at all worthwhile; I'd like users to be able to create custom windowing aggregates, for that matter. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Hierarchical Queries--Stalled No Longer...
Josh, Great, thanks for the update. It's a major feature implementation if we want them to be at all worthwhile agreed. SS 2005 added partial support for window functions (can't create moving windows of aggregation, ie, the portion of the syntax the standard calls the framing clause) and I didn't like that. Imho, that wasn't cool at all. Either support these functions all the way or don't; halfway is silly. Btw, some of my postgres reviewers, when they came across recipes that used the new GENERATE_SERIES function, were quite happy (in particular for pivoting so you don't need to have extra tables lying around). It's a cool addition and I've gotten positive feedback from it. So, whoever dreamt it up, nice job. :) Thanks and regards, Anthony -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 3:05 PM To: pgsql-hackers@postgresql.org Cc: Anthony Molinaro Subject: Re: [HACKERS] Hierarchical Queries--Stalled No Longer... Anthony, also, while I got your ear. I bugged Simon about this earlier this year and was wondering if you guys are still planning on added the window functions added to the '03 standard? I have a ton of recipes that use them and if you guys are still planning on implementing them, I'd like to mention that as well. Planning, yes. Have started, no. It's a major feature implementation if we want them to be at all worthwhile; I'd like users to be able to create custom windowing aggregates, for that matter. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: R: [HACKERS] feature proposal ...
On Wed, Sep 21, 2005 at 11:31:42AM -0400, Tom Lane wrote: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: Paolo Magnoli wrote: Can't you just use a view? no because a new is not a heap ... I think Paolo's idea is much better than munging the syntax of COPY, though. Fixing COPY so that you *could* copy from a view would provide all the desired functionality without any syntactic warts. While I'm all for COPY from views, I think I'd rather have the syntactic warts than code warts. ISTM that CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...; COPY some_name TO stdout; is much uglier than COPY SELECT * FROM table WHERE ... TO stdout; -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: R: [HACKERS] feature proposal ...
absolutely - the main advantage of the syntax tweak is that you can add parameters more easily. best regards, hans On 22 Sep 2005, at 21:25, Jim C. Nasby wrote: On Wed, Sep 21, 2005 at 11:31:42AM -0400, Tom Lane wrote: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: Paolo Magnoli wrote: Can't you just use a view? no because a new is not a heap ... I think Paolo's idea is much better than munging the syntax of COPY, though. Fixing COPY so that you *could* copy from a view would provide all the desired functionality without any syntactic warts. While I'm all for COPY from views, I think I'd rather have the syntactic warts than code warts. ISTM that CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...; COPY some_name TO stdout; is much uglier than COPY SELECT * FROM table WHERE ... TO stdout; -- 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] Table Partitioning is in 8.1
On Thu, Sep 22, 2005 at 10:11:50AM +0100, Simon Riggs wrote: On Wed, 2005-09-21 at 15:39 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Is it possible that the Release Notes do not fully explain the Constraint Exclusion feature? Or is it the consensus that it works but not quite well enough to make a song and dance about yet? I hardly think that the existing constraint-exclusion code is enough for us to claim we support table partitioning. There's too much grunt work that the DBA still has to do to set up a partitioning arrangement. So you think the DBA can do partitioning? Good. Setting up partitioning in Oracle or SQLServer2005 requires lots of syntax and multiple commands. There are fewer commands with PostgreSQL and they are ISO/ANSI compliant also. Actually, IIRC it takes 2 commands; one to initially setup the partitioning and one to create new partitions as needed. 3 commands if you count DROP PARTITON. It's been a while since I looked at what you've done, but I seem to recall needing to manually maintain rules every time you create a new partition. -- 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: R: [HACKERS] feature proposal ...
While I'm all for COPY from views, I think I'd rather have the syntactic warts than code warts. ISTM that CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...; COPY some_name TO stdout; is much uglier than COPY SELECT * FROM table WHERE ... TO stdout; Or, you could just allow subqueries in COPY to disambiguate the syntax: COPY (SELECT * FROM table WHERE i=1) TO stdout; |-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|- AgentM [EMAIL PROTECTED] |-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Table Partitioning is in 8.1
On reflection, the only changes I suggest are: 1) the phrase This allows for a type of table partitioning have the word basic inserted within it to become: This allows for a basic type of table partitioning How about just: Initial support for table partitioning. Yes it is non-committal but that is a good thing since nobody 100% agrees anyway. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 2 forks for md5?
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Confusion fixed (thanks, Tom). psql (via libpq) tries twice, if not given a password to begin with (via .pgpass or psql's -W). Ugly but at least not incomprehensible. This seems reasonable behavior when prompting for a password from the user, since that's going to take a lot of time anyway. I'm not sure whether libpq should try to excavate a password from ~/.pgpass in advance of being told by the server that one is needed. An ideal solution might be to read ~/.pgpass during the bit of code that handles a password challenge from the server, rather than in the outer loop. Not having looked at the libpq code recently, I have no idea how painful that would be to do. Actually, it looks to me like the cause is some code in psql/startup.c which keeps trying to make a connection while it gets PQnoPasswordSupplied. libpq seemed to work just fine, picking up pgpass before a connection was attempted. My only real concern is that when you turn on log_connections the traces are confusing - from the user's perspective there is only one connection, and there are two mentioned in the log, one of which doesn't have a correspondingly logged disconnect. Maybe worth a mention in the docs? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: R: [HACKERS] feature proposal ...
On N, 2005-09-22 at 21:34 +0200, Hans-Juergen Schoenig wrote: absolutely - the main advantage of the syntax tweak is that you can add parameters more easily. Perhaps COPY from SQL FUNCTIONS is what wou need ? Or should we piggypack on (future) work needed for hierarchical queries and have COPY from WITH like this. WITH copysource (f1,f2,f3) as (SELECT ... ) COPY copysource TO stdout; The full syntax (as a railroad diagram) of WITH for hierarchical queries is available at http://gppl.moonbone.ru/with_clause.gif . But with can be used also for non-hierarchical queries, as kind of inline temp view definition, and this copy syntax would be extension of this use. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: R: [HACKERS] feature proposal ...
Jim C. Nasby wrote: While I'm all for COPY from views, I think I'd rather have the syntactic warts than code warts. ISTM that CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...; COPY some_name TO stdout; is much uglier than COPY SELECT * FROM table WHERE ... TO stdout; They aren't mutually exclusive, though. And once you have code in place for the first part, turning the direct query case into a temp_view+copy is arguably just a case of syntactic sugar. I do think the direct query should at least be parenthesized, if we go that way. So why not do what everyone is agreed on now? Whatever happens the work won't be wasted. Also, as nifty as this might be, we should also be prepared for people to complain that it runs a lot slower than vanilla COPY, because it surely will. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: R: [HACKERS] feature proposal ...
Andrew Dunstan [EMAIL PROTECTED] writes: So why not do what everyone is agreed on now? I wasn't agreed on it ;-) The primary objection I've got is that I think this will be a very considerable increment of work for exactly zero increment in functionality, compared to being able to copy from a view. (If you're not seeing why, consider that COPY is a utility statement not an optimizable statement; you'd have to change that classification, with resultant impacts all across the system.) There are other places where the effort could be more usefully spent. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: R: [HACKERS] feature proposal ...
From: Andrew Dunstan [mailto:[EMAIL PROTECTED] Also, as nifty as this might be, we should also be prepared for people to complain that it runs a lot slower than vanilla COPY, because it surely will. At which point we point out to them that it's also much faster than any of the other alternatives. :) But yes, we should mention it in the docs, if for no other reason than to help prevent people from doing COPY (SELECT * FROM table) TO ... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: R: [HACKERS] feature proposal ...
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: So why not do what everyone is agreed on now? I wasn't agreed on it ;-) The primary objection I've got is that I think this will be a very considerable increment of work for exactly zero increment in functionality, compared to being able to copy from a view. (If you're not seeing why, consider that COPY is a utility statement not an optimizable statement; you'd have to change that classification, with resultant impacts all across the system.) There are other places where the effort could be more usefully spent. By what everyone is agreed on I meant copy from a view. ;-) cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] PCTFree Results
Folks, Well, it took a while but I finally have the results of Satoshi's PCTFree patch back from the STP. Bad news about the STP, see below ... Anyway, a series of DBT2 runs doesn't seem to show any advantage to PCTFree over a 3-hour run with no vacuums: test# pctfree full_page_writesnotpm 303164 off off 1803 303165 on on 1847 303166 on off 1860 303167 off on 1801 303168 off off 1838 303169 on on 1821 303170 on off 1846 (again, check all results at http://www.testing.osdl.org/stp/##/) The differences above are small enough to be in the noise factor of DBT2 execution. The good news is that it appears that stuff which has been done since July has lessened the penalty for checkpoints somewhat; while the maximum response time is still better on the full_page_writes=off systems, the average throughput is no longer substantially different. Either that, or full_page_writes=off isn't working properly anymore. If anyone has suggestions on different tests to run, or better stats to compile, please speak up. Now, the bad news: the STP has had some failures and is down to *one* usable machine for testing. I have, like, 160 performance tests backed up which are never going to get run before we release 8.1. I'm going to be hitting up some major PostgreSQL sponsors for hardware donations, any help is welcome. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Hierarchical Queries--Stalled No Longer...
Anthony, I'm reviewing your book :). One of the reasons that I want to add this support is because, in your recipies, it's obvious that PostgreSQL is lacking in this area... likewise, we've had several EDB requests for hierarchical queries (ala Oracle-style)... For the PostgreSQL community, I'll work on adding the ANSI/ISO WITH standard syntax just as SQL Server/DB2 have. -JonahOn 9/22/05, Anthony Molinaro [EMAIL PROTECTED] wrote: So is postgresql going into the direction of WITH or CONNECT BY (orboth)?I am authoring O'Reilly's SQL Cookbook and I'd like to mention it intheHierarchical chapter to give the pg readers a heads up. Thanks and regards,Anthony Molinaro-Original Message-From: [EMAIL PROTECTED][mailto: [EMAIL PROTECTED]] On Behalf Of Josh BerkusSent: Thursday, September 22, 2005 2:02 PMTo: pgsql-hackers@postgresql.org; Jonah H. HarrisSubject: Re: [HACKERS] Hierarchical Queries--Stalled No Longer... Jonah, Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch? If so, what comments do you have as to what you'd like to see.Unfortunately, yes I have. I had to help several users who got burned by it: the patch was buggy as all-get out.For example, it wouldn't accessTOAST tables, so any TEXT fields larger than 500chars got cut off; andwouldn't support user-added data types or domains.(This was a year ago,so maybe Evgen fixed these things)So it's really nice of Evgen to re-license, but the license was *not*theprimary thing blocking acceptance of the patch.JoshJosh Berkus Aglio Database SolutionsSan Francisco---(end of broadcast)---TIP 4: Have you searched our list archives? http://archives.postgresql.org-- Respectfully,Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation http://www.enterprisedb.com/
Re: R: [HACKERS] feature proposal ...
AgentM wrote: While I'm all for COPY from views, I think I'd rather have the syntactic warts than code warts. ISTM that CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...; COPY some_name TO stdout; is much uglier than COPY SELECT * FROM table WHERE ... TO stdout; Or, you could just allow subqueries in COPY to disambiguate the syntax: COPY (SELECT * FROM table WHERE i=1) TO stdout; This is one area where I think Informix did a better job than us, though we inherited COPY so I don't think we can fault the community. In Informix, LOAD is linked to INSERT, and UNLOAD to SELECT, so you do: LOAD FROM '/datafile' [optional flags] INSERT INTO tab [optional columns] and UNLOAD is: UNLOAD TO '/datafile' [optional flags] SELECT * FROM tab where the SELECT can use a column list, where clause, joins, etc. We could adopt something similar with COPY COPY FROM '/datafile' [optional flags] INSERT INTO tab [optional columns] COPY TO '/datafile' [optional flags] SELECT * FROM tab and internally use the non-executor COPY code for a simple INSERT/SELECT, and use the view/executor for more complex cases. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] PCTFree Results
Seems like this was to be somewhat expected. Was there any stats/diagnostics included in the patch to show the effectiveness of PCTFREE? On 9/22/05, Josh Berkus josh@agliodbs.com wrote: Folks,Well, it took a while but I finally have the results of Satoshi's PCTFreepatch back from the STP.Bad news about the STP, see below ...Anyway, a series of DBT2 runs doesn't seem to show any advantage to PCTFree over a 3-hour run with no vacuums:test# pctfree full_page_writesnotpm303164off off 1803303165onon1847303166onoff 1860303167off on1801 303168off off 1838303169onon1821303170onoff 1846(again, check all results at http://www.testing.osdl.org/stp/##/ )The differences above are small enough to be in the noise factor of DBT2execution. The good news is that it appears that stuff which has beendone since July has lessened the penalty for checkpoints somewhat; while the maximum response time is still better on the full_page_writes=offsystems, the average throughput is no longer substantially different.Either that, or full_page_writes=off isn't working properly anymore. If anyone has suggestions on different tests to run, or better stats tocompile, please speak up.Now, the bad news: the STP has had some failures and is down to *one*usable machine for testing.I have, like, 160 performance tests backed up which are never going to get run before we release 8.1.I'm going to behitting up some major PostgreSQL sponsors for hardware donations, any helpis welcome.JoshJosh BerkusAglio Database Solutions San Francisco---(end of broadcast)---TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly-- Respectfully,Jonah H. Harris, Database Internals Architect EnterpriseDB Corporationhttp://www.enterprisedb.com/
Re: [HACKERS] PCTFree Results
Jonah, Seems like this was to be somewhat expected. Was there any stats/diagnostics included in the patch to show the effectiveness of PCTFREE? Lots, look up the tests on OSDL, per link. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] pgxs and pginstaller
Thomas Hallgren wrote: Bruce Momjian wrote: Thomas Hallgren wrote: Hi, I tried to compile PL/Java against PostgreSQL 8.1beta2. I use pgxs and until now that has been just fine. Now pgxs suggests that the include files reside under /usr/local/pgsql. They don't of course. Not on my windows box anyway :-) I think it stems from the src/Makefile.global. On line 59 it reads: prefix := /usr/local/pgsql I guess I'm missing something. Can someone explain to me what I need to change? Take a look at Makefile.global.in. That value is defined by the --prefix flag when you run configure. The default is /usr/local/pgsql, and I am guessing you need to change that default on Win32. I'm using a pre-compiled installation of PostgreSQL. All I want to do is use pgxs to be able to compile PL/Java. There is other stuff that seems strange to me. Why do you append 'postgresql' to the include directories further down in the file? I had to remove that in order to compile. Perhaps the builders of pginstaller could answer this question. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Hierarchical Queries--Stalled No Longer...
On Thu, Sep 22, 2005 at 05:37:51PM -0400, Jonah H. Harris wrote: Anthony, I'm reviewing your book :). One of the reasons that I want to add this support is because, in your recipies, it's obvious that PostgreSQL is lacking in this area... likewise, we've had several EDB requests for hierarchical queries (ala Oracle-style)... For the PostgreSQL community, I'll work on adding the ANSI/ISO WITH standard syntax just as SQL Server/DB2 have. Maybe the recipes could be reworked to use the connect_by() function, wherever possible ... -- Alvaro Herrerahttp://www.advogato.org/person/alvherre No necesitamos banderas No reconocemos fronteras (Jorge González) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PCTFree Results
Josh, Sorry, duh. I'll check it out. It has been a long day and I totally missed the URL :(On 9/22/05, Josh Berkus josh@agliodbs.com wrote:Jonah, Seems like this was to be somewhat expected. Was there any stats/diagnostics included in the patch to show the effectiveness of PCTFREE?Lots, look up the tests on OSDL, per link.JoshJosh BerkusAglio Database SolutionsSan Francisco -- Respectfully,Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporationhttp://www.enterprisedb.com/
Re: [HACKERS] 2 forks for md5?
Andrew Dunstan wrote: This seems reasonable behavior when prompting for a password from the user, since that's going to take a lot of time anyway. I'm not sure whether libpq should try to excavate a password from ~/.pgpass in advance of being told by the server that one is needed. An ideal solution might be to read ~/.pgpass during the bit of code that handles a password challenge from the server, rather than in the outer loop. Not having looked at the libpq code recently, I have no idea how painful that would be to do. Actually, it looks to me like the cause is some code in psql/startup.c which keeps trying to make a connection while it gets PQnoPasswordSupplied. libpq seemed to work just fine, picking up pgpass before a connection was attempted. My only real concern is that when you turn on log_connections the traces are confusing - from the user's perspective there is only one connection, and there are two mentioned in the log, one of which doesn't have a correspondingly logged disconnect. Maybe worth a mention in the docs? I turned on passwords and did see duplicate connections: LOG: connection received: host=[local] LOG: connection received: host=[local] LOG: connection authorized: user=postgres database=test LOG: disconnection: session time: 0:00:00.61 user=postgres database=test host=[local] Basically psql first tries with no password, then when it fails asking for a password, it prompts for one and connects. You will notice only one authorized: message. I think that is the real connection line, rather than the recevied lines. Not sure how we can improve this. We could print an authorization failed message. Would that help, or just be overkill? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 2 forks for md5?
Bruce Momjian pgman@candle.pha.pa.us writes: I turned on passwords and did see duplicate connections: LOG: connection received: host=[local] LOG: connection received: host=[local] LOG: connection authorized: user=postgres database=test LOG: disconnection: session time: 0:00:00.61 user=postgres database=test host=[local] Basically psql first tries with no password, then when it fails asking for a password, it prompts for one and connects. You will notice only one authorized: message. I think that is the real connection line, rather than the recevied lines. Not sure how we can improve this. We could print an authorization failed message. Would that help, or just be overkill? I think that would get people more worried rather than less so --- psql's customary behavior would make it look like you were being regularly attacked by password guessers :-(. We do already log the error message in the cases where a password is actually supplied and is wrong, so an additional message doesn't seem very helpful. One answer is to downgrade the connection received to a DEBUGn message, so that it's only seen by those who presumably have something of a clue. I don't really care for this, but you could certainly argue that the other messages are sufficient for normal purposes. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 2 forks for md5?
Tom Lane said: Bruce Momjian pgman@candle.pha.pa.us writes: I turned on passwords and did see duplicate connections: LOG: connection received: host=[local] LOG: connection received: host=[local] LOG: connection authorized: user=postgres database=test LOG: disconnection: session time: 0:00:00.61 user=postgres database=test host=[local] One answer is to downgrade the connection received to a DEBUGn message, so that it's only seen by those who presumably have something of a clue. I don't really care for this, but you could certainly argue that the other messages are sufficient for normal purposes. Why not INFO? 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] Proposed patch to clean up signed-ness warnings
With gcc 4 spreading, it seems like it's past time to do something about all those signed-vs-unsigned-char warnings that it emits. (Translation: now that I have to use gcc 4 regularly, I got annoyed enough to fix it ;-)) I looked into it a little and determined that nearly all the warnings were associated with the multibyte code. Outside the mb subsystem, our code pretty much uses char * for strings, but inside mb it's mostly unsigned char *, which is needed because there are lots of inequality comparisons in there. It seemed to me that the cleanest fix was to change the external API of the mb subsystem to take and return char *, while still using unsigned char * internally. The attached patch eliminates all signed-ness warnings in CVS tip using this approach. It's kinda long and tedious, but straightforward, and quite a lot of the changes simplify existing code by removing casts that aren't needed anymore. Two questions for the list: 1. Can anyone think of a cleaner way to do this? 2. Is there objection to applying this patch now (ie, before beta3)? It's not quite a bug fix, but I think it'll make it easier to find bugs going forward. For me, your patche seems to be a retrogression. In my understanding, the reason why PostgreSQL uses char * in many places is just it was designed in the old days when ASCII was the only charset in the world. -- SRA OSS, Inc. Japan Tatsuo Ishii ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 2 forks for md5?
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I turned on passwords and did see duplicate connections: LOG: connection received: host=[local] LOG: connection received: host=[local] LOG: connection authorized: user=postgres database=test LOG: disconnection: session time: 0:00:00.61 user=postgres database=test host=[local] Basically psql first tries with no password, then when it fails asking for a password, it prompts for one and connects. You will notice only one authorized: message. I think that is the real connection line, rather than the recevied lines. Not sure how we can improve this. We could print an authorization failed message. Would that help, or just be overkill? I think that would get people more worried rather than less so --- psql's customary behavior would make it look like you were being regularly attacked by password guessers :-(. We do already log the error message in the cases where a password is actually supplied and is wrong, so an additional message doesn't seem very helpful. One answer is to downgrade the connection received to a DEBUGn message, so that it's only seen by those who presumably have something of a clue. I don't really care for this, but you could certainly argue that the other messages are sufficient for normal purposes. I personally think the current behavior is fine. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 2 forks for md5?
Andrew Dunstan wrote: Tom Lane said: Bruce Momjian pgman@candle.pha.pa.us writes: I turned on passwords and did see duplicate connections: LOG: connection received: host=[local] LOG: connection received: host=[local] LOG: connection authorized: user=postgres database=test LOG: disconnection: session time: 0:00:00.61 user=postgres database=test host=[local] One answer is to downgrade the connection received to a DEBUGn message, so that it's only seen by those who presumably have something of a clue. I don't really care for this, but you could certainly argue that the other messages are sufficient for normal purposes. Why not INFO? Yea, we could do that, but does it make sense to downgrade the connection message, especially since the connection authorized message doesn't contain the hostname. We would have to add the host name to the connection authorized message and at that point there is little need for the connection received message. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Table Partitioning is in 8.1
Word basic added. --- Joshua D. Drake wrote: On reflection, the only changes I suggest are: 1) the phrase This allows for a type of table partitioning have the word basic inserted within it to become: This allows for a basic type of table partitioning How about just: Initial support for table partitioning. Yes it is non-committal but that is a good thing since nobody 100% agrees anyway. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: R: [HACKERS] feature proposal ...
On Thursday 2005-09-22 13:16, Andrew Dunstan wrote: Jim C. Nasby wrote: While I'm all for COPY from views, I think I'd rather have the syntactic warts than code warts. ISTM that CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...; COPY some_name TO stdout; is much uglier than COPY SELECT * FROM table WHERE ... TO stdout; They aren't mutually exclusive, though. And once you have code in place for the first part, turning the direct query case into a temp_view+copy is arguably just a case of syntactic sugar. I do think the direct query should at least be parenthesized, if we go that way. Definitely any SELECT that might occur in COPY should be a sub-select. It should meet any syntactic restrictions on a sub-select and it should be in parentheses (or for the liberal, implied parentheses). Proposed: o Allow COPY to output from views -- Pending Allow COPY to output from views, Allow COPY to output from subqueries. The rationale being that all subqueries can be the create clause of a views. So why not do what everyone is agreed on now? Whatever happens the work won't be wasted. Also, as nifty as this might be, we should also be prepared for people to complain that it runs a lot slower than vanilla COPY, because it surely will. Why would there be a material difference in speed in the case of a simple projection? For example Given CREATE TABLE foo ( col_0 ,col_1 , . , . ,col_2N) Then COPY (SELECT col_0 ,col_2 , . , . ,col_2N) TO file-like-target ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposed patch to clean up signed-ness warnings
Tatsuo Ishii [EMAIL PROTECTED] writes: 1. Can anyone think of a cleaner way to do this? For me, your patche seems to be a retrogression. In my understanding, the reason why PostgreSQL uses char * in many places is just it was designed in the old days when ASCII was the only charset in the world. Are you proposing that we change all the char * to unsigned char *? I looked at that briefly but it seems like a huge loss, both in notational ugliness and in the amount of code that would have to be touched. Also, it would force us to add a bunch of explicit casts to avoid warnings with standard library functions like strlen(). To me the bottom line is that 99% of the code only needs to know that a character string is a character string. As this patch demonstrates, there is only a tiny fraction that needs to have the unsigned declaration. I don't think we should allow that fraction to dictate a notational burden for all the rest. 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] Table Partitioning is in 8.1
Simon Riggs wrote: On Wed, 2005-09-21 at 15:39 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Is it possible that the Release Notes do not fully explain the Constraint Exclusion feature? Or is it the consensus that it works but not quite well enough to make a song and dance about yet? I hardly think that the existing constraint-exclusion code is enough for us to claim we support table partitioning. There's too much grunt work that the DBA still has to do to set up a partitioning arrangement. So you think the DBA can do partitioning? Good. Setting up partitioning in Oracle or SQLServer2005 requires lots of syntax and multiple commands. There are fewer commands with PostgreSQL and they are ISO/ANSI compliant also. I think there is much still left to do with partitioning, so I would be the first to say that this is only the beginning. I know you are wary of overstating capabilities; so am I, but it looks like we differ slightly on where to draw the line. On reflection, the only changes I suggest are: 1) the phrase This allows for a type of table partitioning have the word basic inserted within it to become: This allows for a basic type of table partitioning 2) placing CE as a major feature of 8.1 - many people regard it at least as highly as other optimizations, though this is subjective based upon their application requirements Suggestion (2) might be seen as some kind of vanity, so having raised the issue I'll leave the floor open to others to agree or not. Having heard no comments, I have moved this item up into the main features section of the release notes, and reworded it: Improve performance for partitioned tables (Simon) The new constraint_exclusion configuration parameter avoids lookups on child tables where constraints indicate that no matching rows exist in the child table. This allows for a basic type of table partitioning. If child tables store separate key ranges and this is enforced using appropriate CHECK constraints, the optimizer will skip child table accesses when the constraint guarantees no matching rows exist in the child table. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] passing parameters to CREATE INDEX
Added to TODO: * Allow CREATE INDEX to take an additional parameter for use with special index types --- Martijn van Oosterhout wrote: -- Start of PGP signed section. On Wed, Sep 21, 2005 at 08:47:04AM +0300, Hannu Krosing wrote: On K, 2005-09-21 at 09:01 +0400, Oleg Bartunov wrote: it'd be nice if parameters could be passed at the creation time only and somehow stored, so other functions could retrieve them. It's not convenient but also safe. If not changing syntax is essential, then these could be passed by some GUC variables at index create time, then stored. This way one could have as many configurables a one likes . The only major problem with that is that the parameters won't survive a dump/restore. I don't know enough about what's it's needed for to know if that's a problem... So even if an index can store the parameter itself, there would need to be a way for pg_dump to extract it. -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. -- End of PGP section, PGP failed! -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Gerbil build farm failure
Now that we have backtrace, does anyone have a clue about the cause/fix? --- Jim C. Nasby wrote: On Tue, Sep 20, 2005 at 01:17:10PM -0400, Bruce Momjian wrote: I worked with Jim Nasby and we found this is the line that is failing on Gerbil in the build farm during initdb: tqual.c, line 844 in 8.0.X if (HeapTupleHeaderGetCmin(tuple) = snapshot-curcid) This particular line was last modified in 2002. However, this was a file that was changed as part of the VACUUM tuple chain commit: revision 1.81.4.2 date: 2005/08/25 19:45:01; author: tgl; state: Exp; lines: +7 -4 Back-patch fixes for problems with VACUUM destroying t_ctid chains too soon, and with insufficient paranoia in code that follows t_ctid links. This patch covers the 8.0 branch. and the date of the commit to 8.0.X corresponds to the date that failures started to happen: http://pgbuildfarm.org/cgi-bin/show_history.pl?nm=gerbilbr=REL8_0_STABLE BTW, I want to point out for others that when initdb dumps core trying to get a stack trace out of the initdb binary will probably be useless, because initdb is just calling other binaries. In this case we had sucess with the postgres binary. Had I know this I would have had this stack trace available a couple weeks ago. :( http://lnk.nu/developer.postgresql.org/3zx.c is the annotated version of tqual. As Bruce mentioned, the line referenced in the core file probably isn't the culprit. http://lnk.nu/pgbuildfarm.org/3zz.pl has the list of files that changed to break gerbil. Here's the output from gdb: #0 HeapTupleSatisfiesSnapshot (tuple=0xfe28fc78, snapshot=0xd7, buffer=295) at tqual.c:844 844 tqual.c: No such file or directory. in tqual.c (gdb) bt #0 HeapTupleSatisfiesSnapshot (tuple=0xfe28fc78, snapshot=0xd7, buffer=295) at tqual.c:844 #1 0x0004bdd0 in heap_update () #2 0x000ec4b0 in ExecutorRun (queryDesc=0x0, direction=-4198192, count=16) at execMain.c:1592 (gdb) I'm in the process of trying to get this machine moved someplace where I could give a developer ssh access. That should hopefully happen by the end of the week. -- 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 -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 2 forks for md5?
Bruce Momjian pgman@candle.pha.pa.us writes: Yea, we could do that, but does it make sense to downgrade the connection message, especially since the connection authorized message doesn't contain the hostname. We would have to add the host name to the connection authorized message and at that point there is little need for the connection received message. The connection-authorized message could be made to carry all the info for the normal successful-connection case, but for connection failures (not only bad password, but any other startup failure) it isn't going to help. So on reflection I think we'd better keep the connection-received message --- else we'd have to add the equivalent info to all the failure-case messages. I'm coming to agree with Andrew that a documentation patch might be the best answer. But where to put it ... under the description of the log_connections GUC var? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Gerbil build farm failure
Bruce Momjian pgman@candle.pha.pa.us writes: Now that we have backtrace, does anyone have a clue about the cause/fix? The backtrace suggests a garbage snapshot value, but doesn't provide nearly enough info to guess where it's coming from. I'm waiting for the promised ssh access... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposed patch to clean up signed-ness warnings
For me, your patche seems to be a retrogression. In my understanding, the reason why PostgreSQL uses char * in many places is just it was designed in the old days when ASCII was the only charset in the world. Are you proposing that we change all the char * to unsigned char *? No, I suggest we change all char * to unsigned char * only where it points a string which could hold non ASCII character strings. I thought we learned the danger of 1) comparing chars with signed bit on, 2) passing chars with sign bit on to functions which expect int etc... I looked at that briefly but it seems like a huge loss, both in notational ugliness and in the amount of code that would have to be touched. If you are just care the amount of effort, why don't you leave as it is and use pre v4 gcc? :-) Also, it would force us to add a bunch of explicit casts to avoid warnings with standard library functions like strlen(). Counter examples could be easily found in isalpha(), toupper() etc. To me the bottom line is that 99% of the code only needs to know that a character string is a character string. As this patch demonstrates, there is only a tiny fraction that needs to have the unsigned declaration. I don't think we should allow that fraction to dictate a notational burden for all the rest. To support multiple charsets/collataions, I think we need to change the way to represent character strings from the unstructured char * to more intelligent structure (I know it's hard to implement that without significant performance loss, but I know we should do it in the future). So unsigned char* is not enough for the goal anyway, I'm not against your patches. -- SRA OSS, Inc. Japan Tatsuo Ishii ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PCTFree Results
Josh Berkus josh@agliodbs.com writes: ... The good news is that it appears that stuff which has been done since July has lessened the penalty for checkpoints somewhat; while the maximum response time is still better on the full_page_writes=off systems, the average throughput is no longer substantially different. Either that, or full_page_writes=off isn't working properly anymore. I dug through the CVS logs since 5-July (when full_page_writes was added, so I suppose that's before your unspecified July test). AFAICS the only changes that might possibly affect xlog/checkpoint performance were these: Use O_DIRECT if available when using O_SYNC for wal_sync_method. Also, write multiple WAL buffers out in one write() operation. Was your test set up so that it would have used O_DIRECT? With respect to the original point, I'm pretty nervous about either accepting or rejecting a performance-oriented patch on the strength of a single test case. This report certainly doesn't favor the PCTFREE patch, but it probably shouldn't kill it either. Anyone want to try it on some other test cases? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Spinlocks, yet again: analysis and proposed patches
Is there a TODO here? --- Tom Lane wrote: The test case I just posted shows that our spinlock code, which we had thought largely done, is once again becoming a performance bottleneck. It's time to resurrect some of the ideas we kicked around in early 2002, and didn't pursue because we decided spinlocks weren't our major performance problem. I started investigating this by trying to understand why the futex spinlock patch had helped the Red Hat customer I mentioned, when it hadn't done anything much in last year's experiments. It turns out that this customer was using an 8-way Opteron, and the futex patch helps a lot more on that architecture than others. Some random experimentation eventually turned up part of the reason: the x86 TAS assembly code we are using is pessimal for x86_64. Specifically, s_lock.h uses this for both architectures: /* Use a non-locking test before asserting the bus lock */ __asm__ __volatile__( cmpb$0,%1\n jne1f\n lock \n xchgb%0,%1 \n 1: \n and it turns out that deleting the cmpb and jne makes things go significantly faster on Opterons. So the non-locking test is not a win at all on x86_64. As best I can tell from testing, removing it wins because it causes the rate of spinlock delays to drop significantly. Why this should be is not completely obvious. I speculate that the Opterons are capable of pulling in a copy of the cache line that contains the spinlock and then running 100 iterations of the cmpb test without ever noticing that the processor holding the lock has now released it. Without the cmpb, they are forced by the locking xchgb test to actually look at the real state of the spinlock each time. I kinda suspect that the cmpb test is a no-op or loss on all Intelish processors: it can only be a win if you expect a lot of contention for the spin lock, but in percentage terms we still have a very low conflict rate, so in most executions of the TAS macro, the cmpb is just wasted cycles. Bottom line: we definitely don't want it for x86_64, and maybe not at all, but we need more research to decide the latter. The second reason that the futex patch is helping is that when a spinlock delay does occur, it allows the delaying process to be awoken almost immediately, rather than delaying 10 msec or more as the existing code does. However, given that we are only expecting the spinlock to be held for a couple dozen instructions, using the kernel futex mechanism is huge overkill --- the in-kernel overhead to manage the futex state is almost certainly several orders of magnitude more than the delay we actually want. I looked into several other methods of doing the spinlock delay instead. I think all of these were suggested at one point or another in our earlier discussions of spinlocks: 1. Use sched_yield() if available: it does just what we want, ie, yield the processor without forcing any useless time delay before we can be rescheduled. This doesn't exist everywhere but it exists in recent Linuxen, so I tried it. It made for a beautiful improvement in my test case numbers: CPU utilization went to 100% and the context swap rate to almost nil. Unfortunately, I also saw fairly frequent stuck spinlock panics when running more queries than there were processors --- this despite increasing NUM_DELAYS to 1 in s_lock.c. So I don't trust sched_yield anymore. Whatever it's doing in Linux 2.6 isn't what you'd expect. (I speculate that it's set up to only yield the processor to other processes already affiliated to that processor. In any case, it is definitely capable of getting through 1 yields without running the guy who's holding the spinlock.) 2. Reduce the length of the select() delays in s_lock. The current code delays in quanta of 10msec, but on recent Linuxen (and I think other platforms too) the scheduling quantum is 1msec, so we can get the processor back faster if we ask for a 1msec delay. I tried this and it is definitely a win on Linux; it doesn't seem to hurt anything on older systems either, they just round the delay up to 10msec like before. So I think we should do this, even though it's only a partial answer. 3. Modify the spin loop to do a little more work between TAS() tests. In the existing code there are only about half a dozen instructions total in the normal spin loop, most of them very fast, with the result that the spinning processor does its best to monopolize the system bus with locked probe instructions. This is obviously not good, as it'll interfere with the ability of the spinlock holder to complete its work and release the lock. (The bulk of the spinlock uses are for LWLocks, and with the current data structures the LWLock's own state is usually going to be in the same
Re: [HACKERS] PCTFree Results
Tom, I dug through the CVS logs since 5-July (when full_page_writes was added, so I suppose that's before your unspecified July test). AFAICS the only changes that might possibly affect xlog/checkpoint performance were these: Use O_DIRECT if available when using O_SYNC for wal_sync_method. Also, write multiple WAL buffers out in one write() operation. Was your test set up so that it would have used O_DIRECT? Yes, actually. It's Linux, and we used the defaults. It would be interesting to test on a system that doesn't support the above and see if full_page_writes=off still makes a significant difference. With respect to the original point, I'm pretty nervous about either accepting or rejecting a performance-oriented patch on the strength of a single test case. This report certainly doesn't favor the PCTFREE patch, but it probably shouldn't kill it either. Anyone want to try it on some other test cases? Right, this doesn't kill PCTFree, what it does is fail to make a case for it. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [BUGS] BUG #1883: Renaming a schema leaves inconsistent sequence
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: This item has been added to the 8.1 bugs list: http://momjian.postgresql.org/cgi-bin/pgbugs This isn't going to be fixed for 8.1. I think it's really a variant of the TODO item o %Have ALTER TABLE RENAME rename SERIAL sequence names Well, it might be a variant, but its failure is much worse. For a table rename, you just get a strange \d display: test= CREATE TABLE test (x SERIAL); NOTICE: CREATE TABLE will create implicit sequence test_x_seq for serial column test.x CREATE TABLE test= ALTER TABLE test RENAME TO test2; ALTER TABLE test= INSERT INTO test2 VALUES (DEFAULT); INSERT 0 1 test= \d test2 Table public.test2 Column | Type | Modifiers +-+- x | integer | not null default nextval('public.test_x_seq'::text) The insert into the table still works. For the schema rename, the insert into the table doesn't work anymore. The odds that a schema rename is going to have _no_ sequence dependencies in the same schema seems pretty unlikely, meaning rename schema is almost guarantted to create some broken table defaults. With this behavior, if we can't fix it in 8.1, I am wonderingf we should just disable the feature: test= CREATE SCHEMA aa; CREATE SCHEMA test= CREATE TABLE aa.test (x SERIAL); NOTICE: CREATE TABLE will create implicit sequence test_x_seq for serial column test.x CREATE TABLE test= ALTER SCHEMA aa RENAME TO bb; ALTER SCHEMA test= INSERT INTO bb.test VALUES (DEFAULT); ERROR: SCHEMA aa does NOT exist test= \d bb.test Table bb.test Column | Type |Modifiers +-+- x | integer | not null default nextval('aa.test_x_seq'::text) -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_config/share_dir
Added to TODO: * Add options to pg_config to show the share_dir, sysconfdir, pkgincludedir, and localedir --- Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Andrew Dunstan wrote: pg_config doesn't currently seem to have an option to report the share_dir. Should it? Is there a case where a user would need anything from there? pg_config serves a function of recording the configuration, so I tend to agree with Andrew that this should be available. I notice that SYSCONFDIR, PKGINCLUDEDIR, and LOCALEDIR aren't available either. 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 -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] [ADMIN] How to determine date / time of last postmaster
Alvaro Herrera wrote: On Wed, Sep 07, 2005 at 12:38:44AM -0500, Jim C. Nasby wrote: On Tue, Sep 06, 2005 at 08:22:34PM -0400, Tom Lane wrote: PG 8.1 will have a function to return postmaster start time, but not database reset time. I wonder if this is misdefined --- if you are trying to measure database uptime, the last reset would be more appropriate to track. Is it too late to add a function that returns last reset time as well? That would cover all bases and force some less confusing naming. This would be one more vote in favour of initdb before next beta. (pltemplate being the other one.) We should set a threshold in order to be able to decide ... Added to TODO: * Add function to report the time of the most recent server reload -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] SQL/XML public functions documentation for PostgreSQL 8.2
Hello, I did patch http://archives.postgresql.org/pgsql-patches/2005-09/msg00050.php SQL/XML public functions. Can somebody help me with documentation? Or can somebody write doc.. Regards Pavel Stehule _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] SQL/XML public functions documentation for PostgreSQL
Pavel Stehule wrote: Hello, I did patch http://archives.postgresql.org/pgsql-patches/2005-09/msg00050.php SQL/XML public functions. Can somebody help me with documentation? Or can somebody write doc.. That is probably something we will address when we start development of 8.2, if no one else gets to it first. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Gerbil build farm failure
On Fri, Sep 23, 2005 at 12:56:33AM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Fire lit under IT dept. Their initial plan was everything outbound but SSH would be cut-off, which I nixed, but would that suffice in the short term if it means getting the box on the net faster? AFAICS, an ssh connection to an unprivileged account should be enough. I just need to be able to duplicate your build environment. Ok, if that greases the wheels I'll have them do that. Hopefully they can get it done tomorrow. -- 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] Gerbil build farm failure
Jim C. Nasby [EMAIL PROTECTED] writes: Fire lit under IT dept. Their initial plan was everything outbound but SSH would be cut-off, which I nixed, but would that suffice in the short term if it means getting the box on the net faster? AFAICS, an ssh connection to an unprivileged account should be enough. I just need to be able to duplicate your build environment. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Gerbil build farm failure
On Thu, Sep 22, 2005 at 08:03:43PM -0400, Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Now that we have backtrace, does anyone have a clue about the cause/fix? The backtrace suggests a garbage snapshot value, but doesn't provide nearly enough info to guess where it's coming from. I'm waiting for the promised ssh access... Fire lit under IT dept. Their initial plan was everything outbound but SSH would be cut-off, which I nixed, but would that suffice in the short term if it means getting the box on the net faster? -- 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] postgresql clustering
Jonah, I stumbled on this discussion in one of my recurring searches for an open-source database app capable of true clustering (failover, load balancing, etc) that I can pair with my PHP application. A search that, sadly, most often ends in disappointment -- there's tons and tons of database marketing BS out there. Part of my frustration is do to my lack of a real understanding of the models you mentioned in your comment. I've been searching for meaningful text and comparisons of the different clustering models, but have yet to find anything that truely breaks it down well (and deep). Could you perhaps point me -- and anyone else that happens upon this post with the same frustrations -- in the right direction? I've looked at PostgreSQL and EnterpriseDB, but I can't find anything definitive as far as clustering capabilities. What kinds of projects are there for clustering PgSQL, and are any of them mature enough for commercial apps? Best, Dan Jonah H. Harris wrote: In the past couple years I've worked on several personal/business projects to cluster PostgreSQL and InnoDB (without MySQL). I've tested shared-nothing, shared-memory, and shared-disk models. IMHO, shared-disk is the only viable option for performance and/or large production business environments. Using shared-memory or shared-nothing architectures in a database are fine for high-availability, but are expensive from a business-case for added performance. I'd be happy to share any of my clustering knowledge with ya offline. Have fun! On 9/21/05, Rafik Salama [EMAIL PROTECTED] wrote: No I do not have a case study, I just read so, but what I am suggesting to start doing is that if there is no cluster implementation to give high availability of the database, I will start doing this project through the message passing technique and I already have in the university a cluster of 19 machine intel xeon, you can see it in this URL http://www.cs.aucegypt.edu/~cluster But any way I was just asking so as not to reinvent the Wheel, in case there is something like that, but since there is not, I will give it a try, at the end of the day it is open source and I can do anything and if it happens to work, who knows Thanks Rafik Salama Systems Architect CIT Global CIT Building, Free Zone Nasr City, P.O.Box 11816, Cairo, Egypt Tel : +202 271 8794 (ext. 115) Fax : +202 2748335 Cell: +2010 5410035 http://www.citglobal.com -Original Message- From: David Fetter [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 21, 2005 8:12 PM To: Rafik Salama Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] postgresql clustering On Wed, Sep 21, 2005 at 08:01:08PM +0300, Rafik Salama wrote: Dear Sirs I know that that postgresql can be configured for high availability over a clustered environment using pgcluster, Do you have a case study showing this? I am currently studying in my masters the clustering using MPI and OpenMP, PVM and others packages and I have to do a project, so I was thinking to use this opportunity to start implementing the clustering over postgresql using any of the above packages. What do you think? Let a thousand schools of thought content. Let a hundred flowers bloom. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Respectfully, Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend