Re: [HACKERS] temp tables broken in CVS HEAD?
test=> create temp table t2 (i integer); ERROR: permission denied for schema pg_temp_1 The immediate problem is a simple thinko (lack of attention to a function's return convention), but I'm gonna go back and review that whole patch. It obviously wasn't tested well at all. I know what's going on here and will submit a fix for this sometime tonight. -sc I think Tom applied a patch already for this. :) So I noticed. I went to update my sources before sending off a patch and was greeted with a rather nice conflict. Thanks for getting to that Tom. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32, PITR, nested transactions, tablespaces
Greg Stark <[EMAIL PROTECTED]> writes: > This is the only place where I see hardly any movement on major items the > whole development cycle, then a rush of radical changes just before the > freeze. [blink] There's been plenty of stuff done all through this development cycle (and previous ones too). Read the CVS logs if you've forgotten. Bruce has got some kind of idee fixe that we've accomplished little during the 7.5 development cycle, but I respectfully disagree. I could point out the indexability of "WHERE int8col = 42" as just one thing that is a major step forward in usability. It is true that there are a few more big-ticket items that seem to be just over the horizon, but isn't that the usual state of affairs? If we were always willing to wait "just one more month" for the next almost-done project, we'd never get a release out at all. Now having said that, I did just vote in the core discussion to push the freeze date out a month. But I don't have any patience for the idea that 7.5 won't be a pretty respectable release if we freeze it Monday per original plan. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] false infinite recursion detected
Gaetano Mendola <[EMAIL PROTECTED]> writes: > kalman=# select * from v_v_test; > ERROR: infinite recursion detected in rules for relation "v_test" Fixed, thanks for the report! regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Win32, PITR, nested transactions, tablespaces
Bruce Momjian <[EMAIL PROTECTED]> writes: > Reality check --- why should they keep going if the stuff if appication > is going to be months away, and these big features are going to change > CVS significantly anyway? > > Let's be honest --- if they aren't going to make it, they will stop > working hard and do something else with their time. I subscribe to a lot of development mailing lists. This is the only place I've seen people say things like this. You're not the only one, someone else made a similar comment recently too. Usually in other projects when a new development cycle starts and the tree frees up after a release there's a rush of new features. That's when people finally feel free to start applying all the radical changes they dreamed up during the previous development cycle. Normally they hold off on them if they're towards the end of the development cycle even if they're not in a formal freeze. It just just takes time for these radical changes to settle out and they feel more comfortable applying them early in the cycle. This is the only place where I see hardly any movement on major items the whole development cycle, then a rush of radical changes just before the freeze. It's a little weird. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Extended customizing, SQL functions,
[EMAIL PROTECTED] writes: > > On Fri, May 28, 2004 at 12:46:29 -0400, > > [EMAIL PROTECTED] wrote: > > > >> It occurs to me that there is a need for internal state variables that > >> can be accessed either by functions or something similar. What you're describing is called "materialized views" and indeed a full featured implementation capable of caching things like sum() and count() would be a very nice thing to have. But it's also a lot of work. A partial solution handling just your setup could be done using triggers like you describe, but it has the major disadvantage of serializing all insert/delete/updates on the source table. They all become serialized around the record with the cache of the sum/count. > > But there still needs to be multiple copies to take into account that > > different transactions may need to see different values of the same > > variable. > > Yea, what I'm about to say will cause a lot of people to disagree with me, > and I don't even like the idea for some very small set of examples, > but > > No transactions. Well sure you can do that, in which case there are other systems that are more appropriate than postgres. I would suggest you look at memcached or perhaps mysql. > Then they have to vacuum constantly. It just seems like an ugly and wasteful > process. If you were using other databases the equivalent work would have to happen in the middle of the critical path of the transaction, which is even uglier and more wasteful. If you're using 7.4 the new pg_autovacuum daemon will handle this for you, you can pretend it isn't happening. The only alternative is giving up transactions, like you say, in which case you may as well use a tool that doesn't spend so much effort providing them. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [pgsql-hackers-win32] select like...not using index
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > Sometime between yesterday and today queries in the form of > "select * from t where t.f like 'k%'" have been broken so that they > never use the index (on win32, not sure about others). Not here ... and I've seen no commits that I'd have thought would affect that. Check for local configuration changes. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] temp tables broken in CVS HEAD?
Bruce Momjian <[EMAIL PROTECTED]> writes: > Also, what is a "thinko"? When in doubt, consult the Hacker's Dictionary ... http://www.catb.org/~esr/jargon/html/T/thinko.html regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] temp tables broken in CVS HEAD?
On Fri, 2004-05-28 at 23:19, Bruce Momjian wrote: > Also, what is a "thinko"? A mental "typeo"... brain fart... of if you are old enough to qualify, "a senior moment". ---(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] Nested xacts: looking for testers and review
Alvaro Herrera wrote: > On Fri, May 28, 2004 at 11:11:27PM -0400, Bruce Momjian wrote: > > Alvaro Herrera wrote: > > > > I think this applies to all GUC variables, but I wonder if we want to > > > save the value of each one at subtransaction start and recover it at > > > abort? Things could easily get huge. Maybe only saving the ones that > > > are different from the default value, and from the last saved value. > > > > We have an on-commit field in the guc structures to handle > > commit/rollback settings. Do we need to extend that to subtransactions? > > Yes IMHO. I'm not sure actually _how_ should this be handled. Maybe > the on-commit field should go away and be replaced by something more > general (probably a stack, like everything else). Let me look at the > code. > > > I don't think you can save off only the defaults in an efficient manner. > > What do you mean by efficient? Space efficient? It will be much more > efficient to save only the changed values. Time efficient? We will > have to traverse the whole list anyway, whether we only save the changed > values or all of them. > > Remember, we already traverse the whole list of shared buffers, the > whole CatCache, the whole Relcache, maybe do some repallocs, and lots of > other stuff. Traversing the whole GUC array does not seem all that > expensive to me ... After all, we are saving lots of I/O if subxacts > are used correctly (try, rollback, try again -- you save the XLog for > the first try.) My comment was based on the fact that guc already does some special _saves_ when you change a value and triggers some stuff on xact end. I was just thinking it would be cleaner to use that infrastructure rather than do a scan not knowing if any GUC will change or not, but if a scan is easier, I think that would be fine. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: 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] Nested xacts: looking for testers and review
On Fri, May 28, 2004 at 11:11:27PM -0400, Bruce Momjian wrote: > Alvaro Herrera wrote: > > I think this applies to all GUC variables, but I wonder if we want to > > save the value of each one at subtransaction start and recover it at > > abort? Things could easily get huge. Maybe only saving the ones that > > are different from the default value, and from the last saved value. > > We have an on-commit field in the guc structures to handle > commit/rollback settings. Do we need to extend that to subtransactions? Yes IMHO. I'm not sure actually _how_ should this be handled. Maybe the on-commit field should go away and be replaced by something more general (probably a stack, like everything else). Let me look at the code. > I don't think you can save off only the defaults in an efficient manner. What do you mean by efficient? Space efficient? It will be much more efficient to save only the changed values. Time efficient? We will have to traverse the whole list anyway, whether we only save the changed values or all of them. Remember, we already traverse the whole list of shared buffers, the whole CatCache, the whole Relcache, maybe do some repallocs, and lots of other stuff. Traversing the whole GUC array does not seem all that expensive to me ... After all, we are saving lots of I/O if subxacts are used correctly (try, rollback, try again -- you save the XLog for the first try.) -- Alvaro Herrera () "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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] temp tables broken in CVS HEAD?
\Sean Chittenden wrote: > >> test=> create temp table t2 (i integer); > >> ERROR: permission denied for schema pg_temp_1 > > > > The immediate problem is a simple thinko (lack of attention to a > > function's return convention), but I'm gonna go back and review that > > whole patch. It obviously wasn't tested well at all. > > I know what's going on here and will submit a fix for this sometime > tonight. -sc I think Tom applied a patch already for this. Also, what is a "thinko"? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: 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] Nested xacts: looking for testers and review
Alvaro Herrera wrote: > On Fri, May 28, 2004 at 05:43:41PM -0700, Stephan Szabo wrote: > > > On Wed, 26 May 2004, Alvaro Herrera wrote: > > > > > I have tested it and it passes all regression tests (including ones I > > > added), plus some more tests I threw at it mainly for concurrency. > > > Everything behaves as expected. At this time I'd like to have it > > > reviewed by the critic eye of the committers, and tested by whoever > > > would be using it. > > > > I unfortunately didn't really follow the discussions in the past (sorry :( > > ), but are the transaction state modifying statements done in a > > subtransaction supposed to live beyond subtransaction rollback? > > Hmm, I suppose not. > > I think this applies to all GUC variables, but I wonder if we want to > save the value of each one at subtransaction start and recover it at > abort? Things could easily get huge. Maybe only saving the ones that > are different from the default value, and from the last saved value. We have an on-commit field in the guc structures to handle commit/rollback settings. Do we need to extend that to subtransactions? I don't think you can save off only the defaults in an efficient manner. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SELECT * FROM LIMIT 1; is really slow
I wrote a while ago > So pg_subtrans remains the same, and we assign a new Xid to each > subtransaction. Each tuple gets Xmin/Xmax according to the Xid of the > current subtransaction. Within the transaction tree we don't use the > Xid to check for visibility, but Cmin/Cmax and the abort bitmap. ... and meanwhile I forgot why this subthread started: I haven't solved the original problem, which is that I want to avoid enlarging the HeapTupleHeader. But currently there is need for storing both a Cmin and a Xmax :-( Manfred: the code you read adds a single bit to the infomask, which is there basically to be able to say whether the Cmin has been overwritten with a Xmax. So the assumption was that when we see that this has happenned, the Cmin is no longer important (== every future command can already see the tuple), and we have to check the Xmax in pg_clog to see if the deleting subtransaction has aborted (in which case the tuple is visible to us). This idea was a result of a couple of hour of chat with Bruce where I explained that I didn't want to enlarge the HeapTupleHeader, and he came up with the bit idea. -- Alvaro Herrera () "En las profundidades de nuestro inconsciente hay una obsesiva necesidad de un universo lógico y coherente. Pero el universo real se halla siempre un paso más allá de la lógica" (Irulan) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] temp tables broken in CVS HEAD?
test=> create temp table t2 (i integer); ERROR: permission denied for schema pg_temp_1 The immediate problem is a simple thinko (lack of attention to a function's return convention), but I'm gonna go back and review that whole patch. It obviously wasn't tested well at all. I know what's going on here and will submit a fix for this sometime tonight. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SELECT * FROM LIMIT 1; is really slow
On Fri, May 28, 2004 at 04:45:28PM -0400, Tom Lane wrote: > However, I just remembered why we rejected this idea to start with :-(. > If we do it this way then when the overall xact commits, we no longer > have state that tells which particular tuples are good or not. We would > have to trawl for tuples written by aborted subtransactions and mark > them dead before committing, else other transactions would think they > were good. > > What this says is that we still need persistent pg_subtrans status. > I'm not sure if we can use CIDs as subtrans IDs this way and still have > a reasonably efficient storage representation for the global pg_subtrans > table. I'm not sure if I understand your last assertion. We can in no way use the CID as subtrans ID in pg_subtrans, of course (it starts from 0 at each main transaction start). So pg_subtrans remains the same, and we assign a new Xid to each subtransaction. Each tuple gets Xmin/Xmax according to the Xid of the current subtransaction. Within the transaction tree we don't use the Xid to check for visibility, but Cmin/Cmax and the abort bitmap. When the Xmin/xmax does not belong to our transaction tree, we use pg_subtrans and pg_clog. -- Alvaro Herrera () "We are who we choose to be", sang the goldfinch when the sun is high (Sandman) ---(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] Nested xacts: looking for testers and review
On Fri, May 28, 2004 at 05:43:41PM -0700, Stephan Szabo wrote: > On Wed, 26 May 2004, Alvaro Herrera wrote: > > > I have tested it and it passes all regression tests (including ones I > > added), plus some more tests I threw at it mainly for concurrency. > > Everything behaves as expected. At this time I'd like to have it > > reviewed by the critic eye of the committers, and tested by whoever > > would be using it. > > I unfortunately didn't really follow the discussions in the past (sorry :( > ), but are the transaction state modifying statements done in a > subtransaction supposed to live beyond subtransaction rollback? Hmm, I suppose not. I think this applies to all GUC variables, but I wonder if we want to save the value of each one at subtransaction start and recover it at abort? Things could easily get huge. Maybe only saving the ones that are different from the default value, and from the last saved value. -- Alvaro Herrera () "Cómo ponemos nuestros dedos en la arcilla del otro. Eso es la amistad; jugar al alfarero y ver qué formas se pueden sacar del otro" (C. Halloway en La Feria de las Tinieblas, R. Bradbury) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested xacts: looking for testers and review
On Wed, 26 May 2004, Alvaro Herrera wrote: > I have tested it and it passes all regression tests (including ones I > added), plus some more tests I threw at it mainly for concurrency. > Everything behaves as expected. At this time I'd like to have it > reviewed by the critic eye of the committers, and tested by whoever > would be using it. > > I'm open for comments and suggestions and general input. Thank you. I unfortunately didn't really follow the discussions in the past (sorry :( ), but are the transaction state modifying statements done in a subtransaction supposed to live beyond subtransaction rollback? For example, sszabo=# begin; BEGIN sszabo=# begin; BEGIN sszabo=# set transaction read only; SET sszabo=# select * from a; a --- 6 (1 row) sszabo=# rollback; ROLLBACK sszabo=# update a set a=6; ERROR: transaction is read-only ---(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] Extended customizing, SQL functions,
On Friday 28 May 2004 04:08 pm, [EMAIL PROTECTED] wrote: (B> (B> No transactions. (B> (B (BThen it won't work. Let's say we take your example. The variable "s" is (Bsupposed to be the sum of a column in a table. (B (BWhen someone modifies the data in the table, when is "s" adjusted? What if (Bit is modified but the transaction is aborted? Who sees the "correct" value (Bof "s" at any point in time? (B (B-- (BJonathan Gardner (B[EMAIL PROTECTED] (B (B---(end of broadcast)--- (BTIP 8: explain analyze is your friend
[HACKERS] false infinite recursion detected
Hi all, I distilled another our regression test failing: create table test ( id integer, quota integer ); create or replace view v_test AS select id, quota from test; create or replace view v_v_test AS select t1.id, t1.quota from v_test t1 join v_test t2 using ( id ) ; kalman=# select * from v_v_test; ERROR: infinite recursion detected in rules for relation "v_test" Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] passing a whole record variable into a SQL command is not implemented
Hi all, I'm running our regression test on top of postgresql 7.5 devel. I found this function not working anymore ( is a distillated ): create table test ( quota integer ); CREATE OR REPLACE FUNCTION sp_test ( ) RETURNS TEXT AS' DECLARE quota RECORD; my_ret TEXT := ; BEGIN FOR quota IN SELECT quota FROM test LOOP my_ret := my_ret || quota.quota || '',''; END LOOP; RETURN my_ret; END; ' LANGUAGE 'plpgsql' WITH ( iscachable ); when I execute it I obtain: ERROR: passing a whole record variable into a SQL command is not implemented the problem is that the variable quota have the same column name for the table test, for us it's a minimum issue due the fact that is easily fixed changing the variable name, but I'm wondering if behind there is something wrong. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] -Wall and Wmissing-prototype
There are many warnings that do not show up with -Wall I usually use at least: -Wall -ansi -pedantic These are also a good idea: -Wimplicit-function-declaration -Wstrict-prototypes -Wmissing-prototypes -Wmissing-declarations -Werror You will also get additional warnings when optimization is turned on due to flow analysis that you will not get in debug mode. > -Original Message- > From: Gaetano Mendola [mailto:[EMAIL PROTECTED] > Sent: Friday, May 28, 2004 3:31 PM > To: [EMAIL PROTECTED] > Subject: [HACKERS] -Wall and Wmissing-prototype > > > Hi all, > I compiled postgres7.5devel and I see that during > compilation are used togheter: > -Wall -Wmissing-proptotype -Wmissing-declaration > there is any reason to specify after -Wall others > warning ? > > > > Regards > Gaetano Mendola > > > > > ---(end of > broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] select like...not using index
> Sometime between yesterday and today queries in the form of > "select * from t where t.f like 'k%'" have been broken so that they > never use the index (on win32, not sure about others). > > On win32, at least, they have been broken for a while but this was due > to a known issue based on the locales. AFAICT, the current cvs has > addressed this issue and (show lc_collate returns C) there seems to be > no reason why the queries aren't working properly. > > Merlin Did you do an ANALYZE on the table? Are there a lot of duplicate keys? How big is the table? What does the "explain" look like? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Extended customizing, SQL functions,
> On Fri, May 28, 2004 at 12:46:29 -0400, > [EMAIL PROTECTED] wrote: >> It occurs to me that there is a need for internal state variables that >> can >> be accessed either by functions or something similar. > > But there still needs to be multiple copies to take into account that > different transactions may need to see different values of the same > variable. > Yea, what I'm about to say will cause a lot of people to disagree with me, and I don't even like the idea for some very small set of examples, but No transactions. I know this is a very bad thing, and I hate even thinking about it, but there is a real "need" for this sort of function in some very limited cases. Let me exaplin, and this really isn't a SQL issue, so much as flexability to break some rules issue. My client is sold on PostgreSQL, it works for them perfectly with one exception. (I have to be careful about NDA stuff here) The have a database of information that is coming in at a high speed regular basis. One bit of information is a value. To get this value they must perform SELECT sum(field) FROM table. Well, this simply does not scale. They've used a trigger system with a small summary table where they update, the number in the sumary field. That works fine, except, that after a few thousand updates, the SELECT time takes a while. Then they have to vacuum constanty. It just seems like an ugly and wastefull process. There is a quick solution, create an internal variable in shared memory that can be seen by all back-end processes. It is protected by a mutex. Now, I could roll my own system pretty easily, and probably will do so. It won't take too much, however, it would be neat if this was in PostgreSQL. I fully expect that people would worry about this, and I don't blame them. It is a *bad* idea. Like I said, I could roll my own, but I'm curious if anyone else sees any benefit to this feature. If it is a feature that people want, it would best be done from within PostgreSQL. If it is not something generally wanted, then I'll keep it here or try to get it on gborg or pgfoundary. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] -Wall and Wmissing-prototype
Hi all, I compiled postgres7.5devel and I see that during compilation are used togheter: -Wall -Wmissing-proptotype -Wmissing-declaration there is any reason to specify after -Wall others warning ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] On query rewrite
Sailesh, > BTW, do lots of people use the GEQO ? I do. I've several clients with data mining databases that literally require 45-way joins on some queries. Even a state-of-the-art CPU balks at that. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] cancel
This message was cancelled from within Mozilla. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Win32, PITR, nested transactions, tablespaces
On Fri, 28 May 2004, Bruce Momjian wrote: Marc G. Fournier wrote: On Fri, 28 May 2004, Bruce Momjian wrote: Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: Off the top of my head ... the Installer and Service Manager come to mind ... and Tom's fsync changes ... Right now, I'd say feature freeze is looking more like next Friday (June 4th), and we're evaluate it then ... that should hopefully give the above time to flesh out and get into CVS ... I intend to be done with fsync by Monday, but I won't have any time to review these other patches until after that. So pushing feature freeze back a week sounds like a good idea to me too. We need enough time to make unhasty decisions about whether each of these features is ready enough to go into 7.5 (or whatever we call it). Agreed, but I am concerned we are entering a period like we did with 7.3 where we delay things a week at a time rather than in one-month chunks. The *only* thing we are pushing back the beta for is so that the Win32 stuff can be completed ... There is no reason why those working on the other features should be slowing down as a result that they think they might not make it, since even if they don't make it, it would be *really* nice to see the features added at the start of the next dev cycle ... Reality check --- why should they keep going if the stuff if appication is going to be months away, and these big features are going to change CVS significanly anyway? Let's be honest --- if they aren't going to make it, they will stop working hard and do something else with their time. 'k, sounds reasonable ... so, since the feature freeze will most likely happen next Friday (not guaranteed, of course), everyone working on features that will not be done in time should just stop working on it ... that should free up a bunch of ppl for testing those features that are already in ... Of course, that *is* based on the Reality Check you've so kindly provided me ... And, of course, that puts us into the next dev cycle with a bunch of half finished, not ready to commit, features that will most likely be committed days ahead of the feature freeze ... instead of a bunch of features that were finished off *during* the beta cycle and ready to be committed the day after we create the branch ... Your Reality sounds like such a waste of time and talent, doesn't it? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32, PITR, nested transactions, tablespaces
Marc G. Fournier wrote: > On Fri, 28 May 2004, Bruce Momjian wrote: > > > Tom Lane wrote: > >> "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > >>> Off the top of my head ... the Installer and Service Manager come to mind > >>> ... and Tom's fsync changes ... > >> > >>> Right now, I'd say feature freeze is looking more like next Friday (June > >>> 4th), and we're evaluate it then ... that should hopefully give the above > >>> time to flesh out and get into CVS ... > >> > >> I intend to be done with fsync by Monday, but I won't have any time to > >> review these other patches until after that. So pushing feature freeze > >> back a week sounds like a good idea to me too. We need enough time to > >> make unhasty decisions about whether each of these features is ready > >> enough to go into 7.5 (or whatever we call it). > > > > Agreed, but I am concerned we are entering a period like we did with 7.3 > > where we delay things a week at a time rather than in one-month chunks. > > The *only* thing we are pushing back the beta for is so that the Win32 > stuff can be completed ... > > There is no reason why those working on the other features should be > slowing down as a result that they think they might not make it, since > even if they don't make it, it would be *really* nice to see the features > added at the start of the next dev cycle ... Reality check --- why should they keep going if the stuff if appication is going to be months away, and these big features are going to change CVS significanly anyway? Let's be honest --- if they aren't going to make it, they will stop working hard and do something else with their time. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: 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] Win32, PITR, nested transactions, tablespaces
On Fri, 28 May 2004, Bruce Momjian wrote: Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: Off the top of my head ... the Installer and Service Manager come to mind ... and Tom's fsync changes ... Right now, I'd say feature freeze is looking more like next Friday (June 4th), and we're evaluate it then ... that should hopefully give the above time to flesh out and get into CVS ... I intend to be done with fsync by Monday, but I won't have any time to review these other patches until after that. So pushing feature freeze back a week sounds like a good idea to me too. We need enough time to make unhasty decisions about whether each of these features is ready enough to go into 7.5 (or whatever we call it). Agreed, but I am concerned we are entering a period like we did with 7.3 where we delay things a week at a time rather than in one-month chunks. The *only* thing we are pushing back the beta for is so that the Win32 stuff can be completed ... There is no reason why those working on the other features should be slowing down as a result that they think they might not make it, since even if they don't make it, it would be *really* nice to see the features added at the start of the next dev cycle ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] Nested xacts: looking for testers and review
Alvaro Herrera wrote: > On Fri, May 28, 2004 at 01:43:16PM -0400, Bruce Momjian wrote: > > > In this case, I want to try all of the inserts, but any of them can > > fail, then I want the bottom part done. > > I wonder where everyone eas when I asked this question a lot of time > ago. I said I thought the behavior should be like I described, and no > one objected. Sorry, I didn't understand the question at the time, or wasn't paying attention. > Personally I think it would be a mistake to allow the COMMIT for the > subtransaction to ignore the fact that the subxact was aborted. However > I realize what you are proposing, and maybe this can be implemented > using a parameter to COMMIT (indicating to not propagate the error if > it's in aborted state, but commit normally otherwise). > > However if everyone disagrees, I can take that part out, and the code > would be simpler. IMHO however, it would be less reliable. Imagine this case used in a script: BEGIN; DROP TABLE test; CREATE TABLE test(x int); COMMIT; This will not work because the drop might fail. However you could use this: BEGIN; BEGIN; DROP TABLE test; COMMIT; CREATE TABLE test(x int); COMMIT; It is done in a transaction so the table replace is an atomic operation. One interesting idea would be for COMMIT to affect the outer transaction, and END not affect the outer transaction. Of course that kills the logic that COMMIT and END are the same, but it is an interesting idea, and doesn't affect backward compatibility because END/COMMIT behave the same in non-nested transactions. If this is the type of issue we are dealing with for the patch, I feel very good. Good job Alvaro. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Extended customizing, SQL functions, internal variables, API
On Fri, May 28, 2004 at 12:46:29 -0400, [EMAIL PROTECTED] wrote: > It occurs to me that there is a need for internal state variables that can > be accessed either by functions or something similar. But there still needs to be multiple copies to take into account that different transactions may need to see different values of the same variable. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Win32, PITR, nested transactions, tablespaces
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > Off the top of my head ... the Installer and Service Manager come to mind > ... and Tom's fsync changes ... > Right now, I'd say feature freeze is looking more like next Friday (June > 4th), and we're evaluate it then ... that should hopefully give the above > time to flesh out and get into CVS ... I intend to be done with fsync by Monday, but I won't have any time to review these other patches until after that. So pushing feature freeze back a week sounds like a good idea to me too. We need enough time to make unhasty decisions about whether each of these features is ready enough to go into 7.5 (or whatever we call it). 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
[HACKERS] select like...not using index
Sometime between yesterday and today queries in the form of "select * from t where t.f like 'k%'" have been broken so that they never use the index (on win32, not sure about others). On win32, at least, they have been broken for a while but this was due to a known issue based on the locales. AFAICT, the current cvs has addressed this issue and (show lc_collate returns C) there seems to be no reason why the queries aren't working properly. Merlin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Win32, PITR, nested transactions, tablespaces
On Fri, 28 May 2004, Magnus Hagander wrote: Today is May 28. So if nobody will not commit above within 3 days, non of them will be in 7.5(8.0). Am I correct? Not necessarily ... there are several Win32 related features that still haven't been committed (that I've seen) that will push back the release, and hopefully give some time for the other ones to get in ... Are you saying that the completion of the win32 port will be what decides feature-freeze? When it's ready, it's out of time for other parts? Yes ... the other features are important too, but right now, we are falling behind on marketshare because we are without a native Windows port ... the other features keep us ahead of the pack, but we *need* the Win32 stuff to close off the gap that has been growing ... As Jan has already mentioned, we have several big features already in 7.5 (ARC, bgwriter, etc), so it isn't as if 7.5 will revolve only around Win32 ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SELECT * FROM LIMIT 1; is really slow
On Fri, May 28, 2004 at 03:19:29PM -0400, Tom Lane wrote: > We'd still need a plain CommandCounterIncrement facility, which means > that actually a subtransaction would have to be a group of CIDs not just > one. Right, this is why I suggested runlength (the group is contiguous). > So there'd also need to be a data structure showing the CIDs > associated with each open subtransaction --- this is what you'd > consult to go and set the "aborted" bits if the subxact rolls back. Right. We only need to store the "borders" though. Not even that: only the start, because the end is what is current at AbortSubTransaction() time. I'll try this. -- Alvaro Herrera () "El miedo atento y previsor es la madre de la seguridad" (E. Burke) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested xacts: looking for testers and review
Alvaro Herrera wrote: > On Fri, May 28, 2004 at 04:51:07PM +0900, Tatsuo Ishii wrote: > > > 2) certain behavior was different from what I expected (please correct me > >if my expectation is wrong). > > Yes, the expected behavior is different: if an aborted subtransaction is > closed with a COMMIT or END command, the parent transaction is aborted > too. This is to inhibit an application which blindly expects the > subtransaction to succeed to reach an invalid state. If you want to > return to non-aborted state, end the subtransaction with ROLLBACK > instead. I am interested to know if people agree with this behavior, reproduced below: test=# begin; BEGIN test=# insert into t1 values(1); INSERT 17216 1 test=# begin; BEGIN test=# aaa; ERROR: syntax error at or near "aaa" at character 1 ERROR: syntax error at or near "aaa" at character 1 LINE 1: aaa; ^ test=# end; COMMIT test=# select * from t1; <-- I thought this should work since subtransaction was closed ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block test=# end; COMMIT The problem I see with the behavior shown is that there is no way to use subtransactions in scripts, where the queries can't be changed. Consider this: BEGIN; DO SOME WORK... BEGIN; INSERT ... COMMIT; BEGIN; INSERT ... COMMIT; BEGIN; INSERT ... COMMIT; BEGIN; INSERT ... COMMIT; DO MORE WORK... COMMIT; In this case, I want to try all of the inserts, but any of them can fail, then I want the bottom part done. I guess my big question is that if they issue a COMMIT for a subtransaction that failed, do we assume they made a mistake and fail the outer transaction, or do we just accept it and not affect the outer transaction. In my logic, the subtransaction COMMIT is part of the subtransaction and should not affect the outer transaction's state. Unfortunately, we don't have any similar behavior in our 7.4 code because whether you issue COMMIT or ABORT, it does not affect the outer session. Do any other databases have nested transactions, and how to they handle it? I think we should issue a warning but not affect the outer transaction. > But Bob Henkel and you are right: there needs to be documentation. > Initially I figured I could do that later because there is no new > syntax, but it is obviously needed to explain all sorts of assumptions > and behavior like this. Right now I think just posting examples will work fine. I think the above case shows we are not ready for documentation yet. What I would like is for folks to focus on testing so we can find any open issues like this one and address them. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] SELECT * FROM LIMIT 1; is really slow
On Fri, May 28, 2004 at 03:48:11PM -0400, Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > On Fri, May 28, 2004 at 03:19:29PM -0400, Tom Lane wrote:
> >> We'd still need a plain CommandCounterIncrement facility, which means
> >> that actually a subtransaction would have to be a group of CIDs not just
> >> one.
>
> > Right. We only need to store the "borders" though. Not even that: only
> > the start, because the end is what is current at AbortSubTransaction()
> > time.
>
> Nope. Think about sub-subtransactions.
They are all aborted if the parent is, so
BEGIN;
BEGIN; -- cid = 1
BEGIN; -- cid = 2
INSERT INTO foo VALUES (1) -- cid=3
COMMIT;
ROLLBACK; -- aborts from Cid 1 to Cid 3
-- here we can't see the tuple because Xmin == my-xid
-- and Cmin=1 is aborted
COMMIT;
I assume this is how you think it works, isn't it? Because if not I am
in serious trouble. But if it is, then CommandIds from 1 ("start Cid")
and 3 ("current Cid") are aborted. And this can be kept in the master
bitmap --- in fact, I didn't think about another one.
--
Alvaro Herrera ()
"Un poeta es un mundo encerrado en un hombre" (Victor Hugo)
---(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] Win32, PITR, nested transactions, tablespaces
>> Today is May 28. So if nobody will not commit above within 3 >days, non >> of them will be in 7.5(8.0). Am I correct? > >Not necessarily ... there are several Win32 related features >that still >haven't been committed (that I've seen) that will push back >the release, >and hopefully give some time for the other ones to get in ... Are you saying that the completion of the win32 port will be what decides feature-freeze? When it's ready, it's out of time for other parts? >Off the top of my head ... the Installer and Service Manager >come to mind >... and Tom's fsync changes ... As already said, installer is a pgfoundry project, like other installers (and yes; I'll put something up there RSN. Almost ready). The fsync changes is the big thing. As for your other question, I beleive these are the only things we'd classify as "addded features". There are a few known bugs (see Bruces page http://momjian.postgresql.org/main/writings/pgsql/win32.html) as well. //Magnus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SELECT * FROM LIMIT 1; is really slow
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> On Fri, May 28, 2004 at 02:47:01PM -0400, Tom Lane wrote:
>> Something that just now occurred to me: could we identify
>> subtransactions with commands? That is, cmin *is* the subtransaction
>> ID, and xmin/xmax are always the parent xact? I'm not sure this works
>> either, but it might be something to think about.
> It looks simpler to me than the current design.
> The only problem would be _how_ to mark a bunch of CommandIds as
> aborted -- keeping them all in memory seems too heavy. A bitmap could
> be an interesting idea, but for a very big transaction we could need at
> most 2^32 bits, which is way too much.
Not at all. In the first place, if there was anyone out there using
anything like 2^32 commands per xact, we'd have heard loud complaints
about the limit. ("My xact ran for a week, and then fell over because
of this stupid *%#*!*# limit! Fix it!") Since we've never ever heard
such a complaint, I think it's safe to assume there is plenty of
headroom there. I think ordinary cases are very unlikely to have more
than a few thousand commands per xact, and I don't think this will
change much with the availability of subtransactions. So I'd go for a
plain bitmap. (I *think* we only need one bit per CID, since we only
really care about "aborted or not" --- the visibility rules are simpler
than for concurrent xacts.)
We'd still need a plain CommandCounterIncrement facility, which means
that actually a subtransaction would have to be a group of CIDs not just
one. So there'd also need to be a data structure showing the CIDs
associated with each open subtransaction --- this is what you'd consult
to go and set the "aborted" bits if the subxact rolls back.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SELECT * FROM LIMIT 1; is really slow
On Fri, May 28, 2004 at 02:47:01PM -0400, Tom Lane wrote: > We could possibly avoid this particular issue with sufficiently complex > visibility rules. (I am thinking that we might be able to say that the > inner xact can't see the tuple in question unless the creating command > was "done" in the terms of the outer transaction, in which case perhaps > we don't need its cmin anymore. But I fear that that won't work either. > For instance a serializable cursor opened before the tuple was created > should not be able to see it, so it sure seems like we need cmin.) > And I don't feel confident that there are no other, even harder-to-avoid, > cases to worry about. Hm, the serializable cursor was the example I was looking for to show why the current idea does not work. > Something that just now occurred to me: could we identify > subtransactions with commands? That is, cmin *is* the subtransaction > ID, and xmin/xmax are always the parent xact? I'm not sure this works > either, but it might be something to think about. This seems a nice idea. We wouldn't need pg_subtrans at all, for starters -- no multiple Xids for a transaction tree. And the cmin/cmax test would only be done inside the backend running the xact tree, so it doesn't need to be stored permanently, nor shared. We would need to be able to mark individual CommandIds as aborted, and while checking Cmin and Cmax, not only see how they compare to the CurrentCommandId, but also whether they aborted. It looks simpler to me than the current design. The only problem would be _how_ to mark a bunch of CommandIds as aborted -- keeping them all in memory seems too heavy. A bitmap could be an interesting idea, but for a very big transaction we could need at most 2^32 bits, which is way too much. Runlength encoding maybe? Any graphic-library hacker around here with knowledge about compressing bit strings? I know nothing of this stuff. -- Alvaro Herrera () Si no sabes adonde vas, es muy probable que acabes en otra parte. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] On query rewrite
Sailesh Krishnamurthy <[EMAIL PROTECTED]> writes: > Thanks again. To confirm the actual cost comparison with plan > enumeration is a dynamic programming algorithm, is it not ? > Selinger-style with 2-way join paths enumerated, then 3-way using the > best 2-way etc. ? Correct. For details see make_one_rel_by_joins in path/allpaths.c and make_rels_by_joins in path/joinrels.c (dunno why what's basically a single algorithm is split across two files). There are some heuristics involved concerning whether to consider clauseless joins, so it's not totally trivial. > BTW, do lots of people use the GEQO ? Only people writing queries that join more than a dozen or so tables. GEQO is another thing we've improved (I think) recently, but it's still pretty weak IMHO. The algorithm is really designed to solve Traveling Salesman problems, which bear only a crude resemblance to the behavior of join problems. I'd like to see a more principled solution in there someday. 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] SELECT * FROM LIMIT 1; is really slow
On Thu, 27 May 2004 16:50:24 -0400, Alvaro Herrera <[EMAIL PROTECTED]> wrote: >Now you are on the subject, can I ask you to take a peek at what I did >regarding tuple headers? I did read your patch, but I didn't understand it. :-( >At first I thought I'd have to add back Xmax as a field on its own Veto! This would increase heap tuple header size ==> less tuples per page ==> more pages per table ==> more I/O ==> performance loss. > is there a situation >on which we should need to peek at Cmin after setting Xmax for a >particusar tuple? http://archives.postgresql.org/pgsql-hackers/2002-05/msg00090.php Servus Manfred ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] list rewrite committed
Greg Stark <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> For instance, here >> http://archives.postgresql.org/pgsql-hackers/2004-03/msg00696.php are some >> profiles documenting a case where nearly 40% of the runtime goes into >> lappend's in 7.4. I haven't had time to repeat the test case but I'd think >> that time is near-zero in CVS tip. > Of course now ExecEvalExpr's share of runtime in that test is probably up to > 28-35% in those tests. Or did you get to doing the things you proposed with > making it a macro? I did. I'd like to re-run that test, but have no time for it right now --- too much to do before feature freeze. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] contrib/ compile warnings
Strange, compiled here fine. --- Neil Conway wrote: > I see the following on this machine: (OSX 10.3.4, gcc 3.3) > > [neilc:/Users/neilc/build-pgsql/contrib]% make -s all > ld: warning multiple definitions of symbol _nextval > pending.o definition of _nextval in section (__TEXT,__text) > ../../src/backend/postgres definition of _nextval > ld: warning multiple definitions of symbol _setval > pending.o definition of _setval in section (__TEXT,__text) > ../../src/backend/postgres definition of _setval > /Users/neilc/pgsql/contrib/pgcrypto/rijndael.tbl:3: warning: > `pow_tab' defined but not used > /Users/neilc/pgsql/contrib/pgcrypto/rijndael.tbl:22: warning: > `log_tab' defined but not used > /Users/neilc/pgsql/contrib/pgcrypto/rijndael.tbl:41: warning: > `sbx_tab' defined but not used > /Users/neilc/pgsql/contrib/pgcrypto/rijndael.tbl:60: warning: > `isb_tab' defined but not used > > -Neil > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 8: explain analyze is your friend
Re: [HACKERS] contrib/ compile warnings
Neil Conway <[EMAIL PROTECTED]> writes: > I see the following on this machine: (OSX 10.3.4, gcc 3.3) Interesting. There are no warnings in contrib at all on my machines (probably could duplicate the OS X failure on my laptop, but no time today). Any theories why this would be platform-specific? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] pg_autovacuum Integration
Since the Feature Freeze is coming on quickly and I have yet to submit a patch that integrated pg_autovacuum into the backend (though I have been working on it), I wanted to see what people thing about a few things. Since we are nearing feature freeze, I know won't complete all the improvements to pg_autovacuum that are on my list, so I need to prioritize. Here is what I would like to do (in order of decreasing prioity for 7.5): (Please pardon any minor inaccuracies as I am stuck at a clients office where I don't have access to postgresql src) 1) Move pg_autovacuum from: /contrib/pg_autovacuum/ to: /src/bin/pg_autovacuum/ This can be done immediately with only a minor change to the Makefile. Since people seem to be pleased with pg_autovacuum and want it integrated into th backend, I think it is safe to move it out of contrib. Do people agree? 2) Once pg_autovacuum is moved into /src/bin (formally included into the project) then I want to add a few columns to the system tables (pg_class?) that will store the data pg_autovacuum needs to remember what was happening the last time it was running (This is just the insert, update and delete counts from the stats system at the time of the last known vacuum). For 7.5 pg_autovacuum will update these values, however it will be much better if the vacuum and analyze commands themselves update these values, that way pg_autovacuum won't do any work on a table that was recently vacuumed manually. 3) Modify the postmaster to launch and kill pg_autovacuum according to a new GUC variable. Obviously there is a lot more on the todo list for pg_autovacuum, but the 3 steps described above would make a significant improvement. Does that sounds reasonable? Matthew O'Connor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Win32, PITR, nested transactions, tablespaces
Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > Off the top of my head ... the Installer and Service Manager come to mind > > ... and Tom's fsync changes ... > > > Right now, I'd say feature freeze is looking more like next Friday (June > > 4th), and we're evaluate it then ... that should hopefully give the above > > time to flesh out and get into CVS ... > > I intend to be done with fsync by Monday, but I won't have any time to > review these other patches until after that. So pushing feature freeze > back a week sounds like a good idea to me too. We need enough time to > make unhasty decisions about whether each of these features is ready > enough to go into 7.5 (or whatever we call it). Agreed, but I am concerned we are entering a period like we did with 7.3 where we delay things a week at a time rather than in one-month chunks. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested xacts: looking for testers and review
On Fri, May 28, 2004 at 01:43:16PM -0400, Bruce Momjian wrote: > In this case, I want to try all of the inserts, but any of them can > fail, then I want the bottom part done. I wonder where everyone eas when I asked this question a lot of time ago. I said I thought the behavior should be like I described, and no one objected. Personally I think it would be a mistake to allow the COMMIT for the subtransaction to ignore the fact that the subxact was aborted. However I realize what you are proposing, and maybe this can be implemented using a parameter to COMMIT (indicating to not propagate the error if it's in aborted state, but commit normally otherwise). However if everyone disagrees, I can take that part out, and the code would be simpler. IMHO however, it would be less reliable. > In my logic, the subtransaction COMMIT is part of the subtransaction and > should not affect the outer transaction's state. In some cases yes, but not all. In others, the outer transaction could trust that the inner one worked; to make the example you posted work, I'd use a program rather than a script, and check the return values (or the transaction state). If the subxact is in aborted state, issue ROLLBACK and try again; if not, commit. > Unfortunately, we don't have any similar behavior in our 7.4 code > because whether you issue COMMIT or ABORT, it does not affect the outer > session. Of course. This is new functionality. > Right now I think just posting examples will work fine. I think the > above case shows we are not ready for documentation yet. What I would > like is for folks to focus on testing so we can find any open issues > like this one and address them. Ok. -- Alvaro Herrera () "In Europe they call me Niklaus Wirth; in the US they call me Nickel's worth. That's because in Europe they call me by name, and in the US by value!" ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SELECT * FROM LIMIT 1; is really slow
Alvaro Herrera <[EMAIL PROTECTED]> writes: > On Fri, May 28, 2004 at 03:48:11PM -0400, Tom Lane wrote: >> Nope. Think about sub-subtransactions. > They are all aborted if the parent is, so > BEGIN; > BEGIN; -- cid = 1 > BEGIN; -- cid = 2 > INSERT INTO foo VALUES (1) -- cid=3 > COMMIT; > ROLLBACK; -- aborts from Cid 1 to Cid 3 > -- here we can't see the tuple because Xmin == my-xid > -- and Cmin=1 is aborted > COMMIT; > I assume this is how you think it works, isn't it? [ thinks about it for a while... ] Yeah, I guess you are right. Since we don't have threading, an outer transaction cannot assign any new CIDs while a subtransaction is in progress. Therefore, when a subtransaction ends, all CIDs from its start to current belong to either itself or its subtransactions. On abort we can just mark *all* of these as aborted. If we had to do anything at subtrans commit, we'd need more state, but we don't have to do anything at subtrans commit. So you're right, the per-open-subtrans state is just its starting CID. Slick. However, I just remembered why we rejected this idea to start with :-(. If we do it this way then when the overall xact commits, we no longer have state that tells which particular tuples are good or not. We would have to trawl for tuples written by aborted subtransactions and mark them dead before committing, else other transactions would think they were good. What this says is that we still need persistent pg_subtrans status. I'm not sure if we can use CIDs as subtrans IDs this way and still have a reasonably efficient storage representation for the global pg_subtrans table. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested xacts: looking for testers and review
On Fri, May 28, 2004 at 04:05:40PM -0400, Bruce Momjian wrote: Hm, you are right that there needs to be a more automatic way of doing this. > One interesting idea would be for COMMIT to affect the outer > transaction, and END not affect the outer transaction. Of course that > kills the logic that COMMIT and END are the same, but it is an > interesting idea, and doesn't affect backward compatibility because > END/COMMIT behave the same in non-nested transactions. How about "COMMIT SUB" and "END SUB"? I don't feel it's good to give different meaning to COMMIT versus END, but this is only a gut kind of thing and I could be convinced otherwise. It is even easier to differentiate COMMIT/END than adding a parameter to them. I mean, COMMIT SUB would not affect the state of the outer transaction, while COMMIT would. -- Alvaro Herrera () "In fact, the basic problem with Perl 5's subroutines is that they're not crufty enough, so the cruft leaks out into user-defined code instead, by the Conservation of Cruft Principle." (Larry Wall, Apocalypse 6) ---(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] SELECT * FROM LIMIT 1; is really slow
Manfred Koizar <[EMAIL PROTECTED]> writes: > On Thu, 27 May 2004 16:50:24 -0400, Alvaro Herrera >> At first I thought I'd have to add back Xmax as a field on its own > Veto! This would increase heap tuple header size ==> less tuples per > page ==> more pages per table ==> more I/O ==> performance loss. If putting back xmax is the price we must pay for nested transactions, then we *will* pay that price. Maybe not in this release, but it will inevitably happen. Don't bother hollering "veto" ;-) I suspect that in fact this may be necessary. The justification for overlapping cmin and xmax hinges on two things: 1. within the creating transaction, we can handle the xmax=xmin case specially; 2. another xact could only want to store xmax into a committed tuple, therefore the original xact is done and we don't need cmin anymore. However this breaks down with nested xacts. For instance imagine this situation: * Outer transaction creates a tuple. * Inner transaction deletes this tuple (which it presumably can see). * Inner transaction rolls back. The tuple must still be visible to the outer xact. However now we have a problem: we've wiped out its cmin, which we need for visibility tests in the outer xact. We could possibly avoid this particular issue with sufficiently complex visibility rules. (I am thinking that we might be able to say that the inner xact can't see the tuple in question unless the creating command was "done" in the terms of the outer transaction, in which case perhaps we don't need its cmin anymore. But I fear that that won't work either. For instance a serializable cursor opened before the tuple was created should not be able to see it, so it sure seems like we need cmin.) And I don't feel confident that there are no other, even harder-to-avoid, cases to worry about. Something that just now occurred to me: could we identify subtransactions with commands? That is, cmin *is* the subtransaction ID, and xmin/xmax are always the parent xact? I'm not sure this works either, but it might be something to think about. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] yet another contrib module
Sounds fine. --- Oleg Bartunov wrote: > Hello, > > June 1 is pretty close now, so I'm asking about yet another > contrib module, pg_trgm which is rather mature and quite useful. > Is't worth to put it into 7.5 contrib ? > > trgm - Trigram matching for PostgreSQL > -- > > The pg_trgm contrib module provides functions and index classes > for determining the similarity of text based on trigram > matching. > > Also, we plan to submit schema support to contrib/tsearch2 this weekend. > > Regards, > Oleg > _ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SELECT * FROM LIMIT 1; is really slow
Alvaro Herrera <[EMAIL PROTECTED]> writes: > On Fri, May 28, 2004 at 03:19:29PM -0400, Tom Lane wrote: >> We'd still need a plain CommandCounterIncrement facility, which means >> that actually a subtransaction would have to be a group of CIDs not just >> one. > Right, this is why I suggested runlength (the group is contiguous). Not necessarily. > Right. We only need to store the "borders" though. Not even that: only > the start, because the end is what is current at AbortSubTransaction() > time. Nope. Think about sub-subtransactions. A runlength encoding might be worth using, though, since you're right that subxacts would tend to get runs of consecutive CIDs. In theory we could also use a runlength representation for the master bitmap of aborted CIDs, but that would be costly since you would have to do a search, and not just a trivial index, to check the state of a particular CID. My inclination would be to stick with a bitmap for now. It'd be easy enough to revise the implementation later if that gut feel proves out wrong. 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])
[HACKERS] Extended customizing, SQL functions, internal variables, API
I'm not really sure what to call this feature, but I have been talking to
a potential customer and they need a particular feature and they need to
to be very FAST.
Take this query:
select sum(num) from table;
Now, if that table is very large, this can take a lot of time.
Using a trigger, one can update a summary table for changes, i.e. delete,
update, insert. Problem with this is each transaction to the summary table
creates a new row. If you are doing a 100 updates, inserts, deletes a
second, this soon starts to take a long time to access.
Frequent vacuuming now has to happen.
It occurs to me that there is a need for internal state variables that can
be accessed either by functions or something similar.
At PostgreSQL start time, some subsystem allocates and initializes
internal variables. Child processes of PostgreSQL, have this sort of
metaphore:
>>> VAR.c >
/* called at init time, create var if nessisary */
PGVAR *var = var_allocate('varname', size);
if( var_lock(var) )
{
/* do something worth while */
var_unlock(var);
}
<<<
>>> VAR.sql >
SELECT var_varname;
Does anyone see a need for this?
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32, PITR, nested transactions, tablespaces
> I certainly get the feeling that things are being rushed just a bit too > much, and think having a extra few days of breathing space makes sense. > > cheers > > andrew I have that feeling too, and I'm working still working on pg_autovacuum integration which I was hoping to get in, so I would welcome a few more days to a week. Matthew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] On query rewrite
On Thu, May 27, 2004 at 19:35:56 -0700, Sailesh Krishnamurthy <[EMAIL PROTECTED]> wrote: > > Another question about regular RULE processing .. suppose after > applying a rule the resultant query tree is eligible for another rule, > does pgsql's rule system keep iterating over and over until it reaches > a fixed point or is there some heuristic in operation (just apply the > rules twice ..) ? From my cursory inspection of the code it looks like > the latter, but I'd like to know for sure. Rule processing continues as long as there are rules to apply or the query is terminated. You might want to read up on rules in the documentation. They are the mechanism used to make updateable views and can do some other interesting things. And because they are fully visible to the optimizer (unlike triggers) they don't prevent optimization. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump --comment?
At 12:15 AM 29/05/2004, Jon Jensen wrote: However, I would like to see an option to include the timestamp if someone wants it. The timestamp is already in the header for custom dumps. Have a look at the first few lines from a 'pg_restore -l'. In terms of general comments, there is already a 'WARNING' type TOC entry for custom dumps. This emits a warning message when pg_restore is called. A similar mechanism could be used to dump SQL comments. eg. new TOC type of 'SQL_COMMENT' with a defn starting with '--' would work with old & new pg_dump versions. It might be a good idea to investigate the likely uses for these comments, since the TOC entries may be suppressed depending on whether they are deemed to be part of the schema or the data. Once the modes of use are defined, a better mechanism may become apparent. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] tablespaces and DB administration
Josh Berkus wrote: > The whole point of tablespaces is to > allow placing individual tables and indexes > on seperate volumes. That was one reason. I seem to recall several more: * Putting data on cost appropriate media Mentioned previously in this thread * Balancing I/O across spindles Also mentioned previously, many times * Inode stability The free space bitmap never changes during production. The inode table never changes during production (save for last modified and last accessed timestamps). This makes the filesystem VERY likely to be functional in case of a crash. Jounaled file systems have reduced the need for this. * Device independence Since a tablespace needs only some "file names" and the ability to lseek() to any point in a file, a tablespace file can be a file on the UFS (or its variantes), a disk partition, or even a tape drive device. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested xacts: looking for testers and review
On Sat, May 29, 2004 at 12:27:39AM +0900, Tatsuo Ishii wrote: > Can I ask you one more question? > > Is there any limit for nesting leveles of subtransactions? In theory 2^16 I think, but I haven't tested it. It tried to 30 or so only. Not sure if it's practical. -- Alvaro Herrera () "Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] temp tables broken in CVS HEAD?
Oliver Jowett <[EMAIL PROTECTED]> writes: > test=> create temp table t2 (i integer); > ERROR: permission denied for schema pg_temp_1 I think this is a side effect of Sean's recent permissions-hacking. I knew I should have objected more strongly ... The immediate problem is a simple thinko (lack of attention to a function's return convention), but I'm gonna go back and review that whole patch. It obviously wasn't tested well at all. regards, tom lane ---(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] On query rewrite
> "Tom" == Tom Lane <[EMAIL PROTECTED]> writes: Tom> This particular issue is handled as part of our Path Tom> enumeration mechanism, but the more hard-wired sorts of Tom> transformations that you are asking about live mostly in Thanks again. To confirm the actual cost comparison with plan enumeration is a dynamic programming algorithm, is it not ? Selinger-style with 2-way join paths enumerated, then 3-way using the best 2-way etc. ? BTW, do lots of people use the GEQO ? Tom> optimizer/prep/* and plan/planner.c. In particular you Tom> probably want to look at prepjointree.c and prepqual.c. Tom> (Note prepqual also looks considerably different in CVS tip Tom> than in prior releases.) Thanks .. I've extracted cvstip .. sigh .. one of these days I'll have to do another merge with the TelegraphCQ code. You guys hack too much :-) -- Pip-pip Sailesh http://www.cs.berkeley.edu/~sailesh ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested xacts: looking for testers and review
Can I ask you one more question? Is there any limit for nesting leveles of subtransactions? -- Tatsuo Ishii > On Fri, May 28, 2004 at 04:51:07PM +0900, Tatsuo Ishii wrote: > > > 2) certain behavior was different from what I expected (please correct me > >if my expectation is wrong). > > Yes, the expected behavior is different: if an aborted subtransaction is > closed with a COMMIT or END command, the parent transaction is aborted > too. This is to inhibit an application which blindly expects the > subtransaction to succeed to reach an invalid state. If you want to > return to non-aborted state, end the subtransaction with ROLLBACK > instead. > > But Bob Henkel and you are right: there needo to be documentation. > Initially I figured I could do that later because there is no new > syntax, but it is obviously needed to explain all sorts of assumptions > and behavior like this. > > -- > Alvaro Herrera () > "Limítate a mirar... y algun día veras" > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Win32, PITR, nested transactions, tablespaces
On Fri, 28 May 2004, Andrew Dunstan wrote: The Installer is a pgFoundry project, and unaffected by feature freeze. See http://pginstaller.projects.postgresql.org (Note: Magnus hasn't put anything there yet, though.) 'k, wasn't sure about that one ... but the SVC and fsync stuff is still outstanding, and they are both backend commits ... Bruce, do you have a full list of what is outstanding on the Win32 side, or is it just those two? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested xacts: looking for testers and review
> On Fri, May 28, 2004 at 04:51:07PM +0900, Tatsuo Ishii wrote: > > > 2) certain behavior was different from what I expected (please correct me > >if my expectation is wrong). > > Yes, the expected behavior is different: if an aborted subtransaction is > closed with a COMMIT or END command, the parent transaction is aborted > too. This is to inhibit an application which blindly expects the > subtransaction to succeed to reach an invalid state. If you want to > return to non-aborted state, end the subtransaction with ROLLBACK > instead. Oh, I got it. Thanks. > But Bob Henkel and you are right: there needo to be documentation. > Initially I figured I could do that later because there is no new > syntax, but it is obviously needed to explain all sorts of assumptions > and behavior like this. -- Tatsuo Ishii ---(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] pg_dump --comment?
On May 28, 2004, at 10:48 AM, Andrew Dunstan wrote: A better answer to this particular problem might be incremental dumps, though :-) Oh would incremental dumps be ever so hungrily accepted by ever so many shops. I had imagined that PITR transaction log archiving would allow one to perform an equivalent to "repay initial full data dump", then foreach transaction log, replay. If no writes have occurred, then the transaction log would be small / nongrowing, right? For example, we perform a full dump hourly, scp to a backup db box who then imports it in its entirety, giving us, on average, a half-hour's worth of potential data lossage at any given moment in time if the primary box goes horribly bad. With current database size / machine speed, etc., this process takes ~8 minutes, so we're not sweating it. But we know that the vast majority of the data did not change in the past hour, so the majority of that work was not truly necessary. With PITR log archiving, could we kick off this transfer + replay activity on the second box to ultimately just ship deltas? I suspect that we could not get such fixed-time guarantees anymore, since any individual transaction log file would not become full until, well, enough writes have occurred to push it over the edge, so our 'data hanging in the wind not yet on the backup box' becomes a function of 'most recent N Kbytes of changed data'. By lowering transaction segment size (or whatever controls the size of individual transaction log files) we could force archiving to occur more / less frequently according to write traffic, right? Poor man's (very) async replication. If all of this is correct, kudos to the forward-thinkers involved with PITR and the abstract archive transaction log protocol. If my proposed usage is flawed, then negative kudos to my puny mind. James Robinson Socialserve.com ---(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] list rewrite committed
Tom Lane <[EMAIL PROTECTED]> writes: > For instance, here > http://archives.postgresql.org/pgsql-hackers/2004-03/msg00696.php are some > profiles documenting a case where nearly 40% of the runtime goes into > lappend's in 7.4. I haven't had time to repeat the test case but I'd think > that time is near-zero in CVS tip. Of course now ExecEvalExpr's share of runtime in that test is probably up to 28-35% in those tests. Or did you get to doing the things you proposed with making it a macro? -- greg ---(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] Nested xacts: looking for testers and review
On Fri, May 28, 2004 at 04:51:07PM +0900, Tatsuo Ishii wrote: > 2) certain behavior was different from what I expected (please correct me >if my expectation is wrong). Yes, the expected behavior is different: if an aborted subtransaction is closed with a COMMIT or END command, the parent transaction is aborted too. This is to inhibit an application which blindly expects the subtransaction to succeed to reach an invalid state. If you want to return to non-aborted state, end the subtransaction with ROLLBACK instead. But Bob Henkel and you are right: there needo to be documentation. Initially I figured I could do that later because there is no new syntax, but it is obviously needed to explain all sorts of assumptions and behavior like this. -- Alvaro Herrera () "Limítate a mirar... y algun día veras" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Win32, PITR, nested transactions, tablespaces
Marc G. Fournier wrote: On Fri, 28 May 2004, Tatsuo Ishii wrote: Hi Bruce, Is that core's decision that: o below all four major features will be incorporated into 7.5 We don't know. If they can be added to CVS without major changes, they will be in 7.5. As far as I can tell: Win32 has 98% of its code in CVS, so it will make it Tablespaces - Christopher says it is ready, and has run tests PITR - some functionality might be in 7.5, but we aren't sure Nested transactions - Alvaro thinks it is close, but we don't know Today is May 28. So if nobody will not commit above within 3 days, non of them will be in 7.5(8.0). Am I correct? Not necessarily ... there are several Win32 related features that still haven't been committed (that I've seen) that will push back the release, and hopefully give some time for the other ones to get in ... Off the top of my head ... the Installer and Service Manager come to mind ... and Tom's fsync changes ... Right now, I'd say feature freeze is looking more like next Friday (June 4th), and we're evaluate it then ... that should hopefully give the above time to flesh out and get into CVS ... The Installer is a pgFoundry project, and unaffected by feature freeze. See http://pginstaller.projects.postgresql.org (Note: Magnus hasn't put anything there yet, though.) I certainly get the feeling that things are being rushed just a bit too much, and think having a extra few days of breathing space makes sense. cheers andrew ---(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] pg_dump --comment?
Jon Jensen wrote: On Fri, 28 May 2004, Christopher Kings-Lynne wrote: I've encountered a situation where I'd like to store some information about the database when I do a pg_dump. For instance, the timestamp of the dump. And some other information that I pull from the database. I think every dump should dump the timestamp regardless... That would cause me a lot of trouble. Every night I do a pg_dump on all my databases to a temporary file. Then I use cmp to compare that dump to last night's dump. If they're identical I just delete the new dump so that only the old one remains, with its original timestamp. That way rsync doesn't see any change, and doesn't waste any time comparing it when we do backups. It's also handy to see the last day the dump changed by looking at the file's timestamp. Granted, this is only of interest on databases that don't change at all, but on a multi-user system we have a surprising number of databases that don't change at all for days (alongside the ones that change all the time, of course). However, I would like to see an option to include the timestamp if someone wants it. That makes sense. A better answer to this particular problem might be incremental dumps, though :-) cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] On query rewrite
Sailesh Krishnamurthy <[EMAIL PROTECTED]> writes: > What about things like: > 1. DISTINCT PULLUP (Where you realize that you don't have to have an > explicit duplicate elimination operation because of what's done in the > subquery) > 2. DISTINCT pushdown (when a dup elim. can be pushed down if the upper > querytree is performign DISTINCT set operations (UNION, INTERSECT etc) > 3. Discarding DISTINCT in a subquery because the upper query uses the > subquery with existential quantification Our bottom-up planning approach isn't very conducive to #2 or #3, but we do make a stab at #1. See create_unique_path() and is_distinct_query() in optimizer/util/pathnode.c (note this is new code in CVS tip, 7.4 did not have any such optimization). > In general, I'm trying to understand all the transformations that > pgsql will try to do .. I'm not trying to figure out plan enumeration > for basic boxes (simple query tree). This particular issue is handled as part of our Path enumeration mechanism, but the more hard-wired sorts of transformations that you are asking about live mostly in optimizer/prep/* and plan/planner.c. In particular you probably want to look at prepjointree.c and prepqual.c. (Note prepqual also looks considerably different in CVS tip than in prior releases.) regards, tom lane ---(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] Win32, PITR, nested transactions, tablespaces
Marc G. Fournier wrote: Right now, I'd say feature freeze is looking more like next Friday (June 4th), and we're evaluate it then ... that should hopefully give the above time to flesh out and get into CVS ... Speaking of CVS, a CERT advisory was issued yesterday documenting a vulnerability: http://www.us-cert.gov/cas/techalerts/TA04-147A.html For what it's worth, Mike Mascari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_dump --comment?
On Fri, 28 May 2004, Christopher Kings-Lynne wrote: > > I've encountered a situation where I'd like to store some information > > about the database when I do a pg_dump. For instance, the timestamp of > > the dump. And some other information that I pull from the database. > > I think every dump should dump the timestamp regardless... That would cause me a lot of trouble. Every night I do a pg_dump on all my databases to a temporary file. Then I use cmp to compare that dump to last night's dump. If they're identical I just delete the new dump so that only the old one remains, with its original timestamp. That way rsync doesn't see any change, and doesn't waste any time comparing it when we do backups. It's also handy to see the last day the dump changed by looking at the file's timestamp. Granted, this is only of interest on databases that don't change at all, but on a multi-user system we have a surprising number of databases that don't change at all for days (alongside the ones that change all the time, of course). However, I would like to see an option to include the timestamp if someone wants it. Jon ---(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
[HACKERS] temp tables broken in CVS HEAD?
Against current CVS HEAD:
$ ./createuser test
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
$ ./createdb -O test test
CREATE DATABASE
$ ./psql -U test test
Welcome to psql 7.5devel, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
test=> \l
List of databases
Name| Owner | Encoding
---++---
template0 | oliver | SQL_ASCII
template1 | oliver | SQL_ASCII
test | test | SQL_ASCII
(3 rows)
test=> create table t1 (i int);
CREATE TABLE
test=> create temp table t2 (i integer);
ERROR: permission denied for schema pg_temp_1
test=> select * from pg_namespace;
nspname | nspowner | nspacl
+--+-
pg_toast |1 |
pg_temp_1 |1 |
pg_catalog |1 | {oliver=U*C*/oliver,=U/oliver}
public |1 | {oliver=U*C*/oliver,=UC/oliver}
information_schema |1 | {oliver=U*C*/oliver,=U/oliver}
(5 rows)
The same thing under 7.4.2 successfully creates the temp table. I think
this is something recent as it's triggered by the JDBC driver's
regression tests, which ran (under the same conditions IIRC) without
problems against CVS HEAD as of about a week ago.
-O
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Win32, PITR, nested transactions, tablespaces
On Fri, 28 May 2004, Tatsuo Ishii wrote: Hi Bruce, Is that core's decision that: o below all four major features will be incorporated into 7.5 We don't know. If they can be added to CVS without major changes, they will be in 7.5. As far as I can tell: Win32 has 98% of its code in CVS, so it will make it Tablespaces - Christopher says it is ready, and has run tests PITR - some functionality might be in 7.5, but we aren't sure Nested transactions - Alvaro thinks it is close, but we don't know Today is May 28. So if nobody will not commit above within 3 days, non of them will be in 7.5(8.0). Am I correct? Not necessarily ... there are several Win32 related features that still haven't been committed (that I've seen) that will push back the release, and hopefully give some time for the other ones to get in ... Off the top of my head ... the Installer and Service Manager come to mind ... and Tom's fsync changes ... Right now, I'd say feature freeze is looking more like next Friday (June 4th), and we're evaluate it then ... that should hopefully give the above time to flesh out and get into CVS ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] contrib/ compile warnings
I see the following on this machine: (OSX 10.3.4, gcc 3.3) [neilc:/Users/neilc/build-pgsql/contrib]% make -s all ld: warning multiple definitions of symbol _nextval pending.o definition of _nextval in section (__TEXT,__text) ../../src/backend/postgres definition of _nextval ld: warning multiple definitions of symbol _setval pending.o definition of _setval in section (__TEXT,__text) ../../src/backend/postgres definition of _setval /Users/neilc/pgsql/contrib/pgcrypto/rijndael.tbl:3: warning: `pow_tab' defined but not used /Users/neilc/pgsql/contrib/pgcrypto/rijndael.tbl:22: warning: `log_tab' defined but not used /Users/neilc/pgsql/contrib/pgcrypto/rijndael.tbl:41: warning: `sbx_tab' defined but not used /Users/neilc/pgsql/contrib/pgcrypto/rijndael.tbl:60: warning: `isb_tab' defined but not used -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] Nested xacts: looking for testers and review
This reply was meant to be directed to Alvaro. |-+--> | | [EMAIL PROTECTED]| | | .com | | | Sent by: | | | [EMAIL PROTECTED]| | | tgresql.org| | | | | | | | | 05/28/2004 07:45 AM| | | | |-+--> >--| | | | To: Tatsuo Ishii <[EMAIL PROTECTED]> | | cc: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] | | Subject: Re: [HACKERS] Nested xacts: looking for testers and review | >--| I think before this can be tested fully by a wide audience there needs to be some basic documentation. Or is there? What is the new syntax? Can we see some basic examples that you have used in your testing? Regards Bob |-+--> | | Tatsuo Ishii | | | <[EMAIL PROTECTED]>| | | Sent by: | | | [EMAIL PROTECTED]| | | tgresql.org| | | | | | | | | 05/28/2004 02:51 AM| | | | |-+--> > --| | | | To: [EMAIL PROTECTED] | | cc: [EMAIL PROTECTED] | | Subject: Re: [HACKERS] Nested xacts: looking for testers and review | > --| I have tested the patches with May 28 16:20 JST snapshot. Here is my first impression: 1) errors, rules regression tests are failed (I'm not sure this is due to your patches) 2) certain behavior was different from what I expected (please correct me if my expectation is wrong). test=# begin; BEGIN test=# insert into t1 values(1); INSERT 17216 1 test=# begin; BEGIN test=# aaa; ERROR: syntax error at or near "aaa" at character 1 ERROR: syntax error at or near "aaa" at character 1 LINE 1: aaa; ^ test=# end; COMMIT test=# select * from t1; <-- I thought this should work since subtransaction was closed ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block test=# end; COMMIT test=# select * from t1; i --- (0 rows) 3) no docs found in the patches. > Hackers, > > Ok, I've finally coded solutions to most problems regarding nested > transactions. This means: > > - reversing for the lock manager, catcache, relcache, buffer manager, > asynchronous notifies, storage manager. > > - transaction block state support, including appropiate XLog recording > > - pg_subtrans subsystem (including changing state from SUBTRANS > COMMITTED to COMMITTED when appropiate). Also pg_clog XLog recovery > was handed to SLRU so pg_subtrans and pg_clog share a rmgr identity. > > - visibility rules. > > I'm missing one item: deferred triggers. The problem with this is that > the deftrig queue is not implemented using normal Lists, so there's no > efficient way to reassign to the parent when the subtransaction commits. > Also I'm not sure what should happen to the "immediate" pointer --- a > subtransaction should have it's own private copy, or it should inherit > the parent's? Please whoever implemented this speak up (Stephan > Szabo?), as I'm not sure of the semantics. > > > I have tested it and it passes all regression tests (including ones I > added), plus some more tests I threw at it mainly for concurrency. > Everything behaves as expected. At this time I'd like to have it > reviewed by the critic eye of the committers, and tested by whoever > would be using it. > > I'm open for comments and suggestions and general input. Thank you. > > -- > Alvaro Herrera () > La web junta la gente porque no importa que clase de mutante sexual seas, > tienes millones de posibles parejas. Pon "buscar gen
Re: [HACKERS] Nested xacts: looking for testers and review
I think before this can be tested fully by a wide audience there needs to be some basic documentation. Or is there? What is the new syntax? Can we see some basic examples that you have used in your testing? Regards Bob |-+--> | | Tatsuo Ishii | | | <[EMAIL PROTECTED]>| | | Sent by: | | | [EMAIL PROTECTED]| | | tgresql.org| | | | | | | | | 05/28/2004 02:51 AM| | | | |-+--> >--| | | | To: [EMAIL PROTECTED] | | cc: [EMAIL PROTECTED] | | Subject: Re: [HACKERS] Nested xacts: looking for testers and review | >--| I have tested the patches with May 28 16:20 JST snapshot. Here is my first impression: 1) errors, rules regression tests are failed (I'm not sure this is due to your patches) 2) certain behavior was different from what I expected (please correct me if my expectation is wrong). test=# begin; BEGIN test=# insert into t1 values(1); INSERT 17216 1 test=# begin; BEGIN test=# aaa; ERROR: syntax error at or near "aaa" at character 1 ERROR: syntax error at or near "aaa" at character 1 LINE 1: aaa; ^ test=# end; COMMIT test=# select * from t1; <-- I thought this should work since subtransaction was closed ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block test=# end; COMMIT test=# select * from t1; i --- (0 rows) 3) no docs found in the patches. > Hackers, > > Ok, I've finally coded solutions to most problems regarding nested > transactions. This means: > > - reversing for the lock manager, catcache, relcache, buffer manager, > asynchronous notifies, storage manager. > > - transaction block state support, including appropiate XLog recording > > - pg_subtrans subsystem (including changing state from SUBTRANS > COMMITTED to COMMITTED when appropiate). Also pg_clog XLog recovery > was handed to SLRU so pg_subtrans and pg_clog share a rmgr identity. > > - visibility rules. > > I'm missing one item: deferred triggers. The problem with this is that > the deftrig queue is not implemented using normal Lists, so there's no > efficient way to reassign to the parent when the subtransaction commits. > Also I'm not sure what should happen to the "immediate" pointer --- a > subtransaction should have it's own private copy, or it should inherit > the parent's? Please whoever implemented this speak up (Stephan > Szabo?), as I'm not sure of the semantics. > > > I have tested it and it passes all regression tests (including ones I > added), plus some more tests I threw at it mainly for concurrency. > Everything behaves as expected. At this time I'd like to have it > reviewed by the critic eye of the committers, and tested by whoever > would be using it. > > I'm open for comments and suggestions and general input. Thank you. > > -- > Alvaro Herrera () > La web junta la gente porque no importa que clase de mutante sexual seas, > tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con > ciervos incendiánse", y el computador dirá "especifique el tipo de ciervo" > (Jason Alexander) > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings * PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * -
[HACKERS] yet another contrib module
Hello, June 1 is pretty close now, so I'm asking about yet another contrib module, pg_trgm which is rather mature and quite useful. Is't worth to put it into 7.5 contrib ? trgm - Trigram matching for PostgreSQL -- The pg_trgm contrib module provides functions and index classes for determining the similarity of text based on trigram matching. Also, we plan to submit schema support to contrib/tsearch2 this weekend. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] tablespaces and DB administration
[EMAIL PROTECTED] wrote: A single postgresql process may not see much benefit, because it does not do background I/O, Not yet, I believe that in 7.5 there is a component that do it. Am I wrong ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] On query rewrite
> "Tom" == Tom Lane <[EMAIL PROTECTED]> writes: >> Are these alternatives (pulling up vs not pulling up >> subqueries) considered in different plans ? Tom> That particular choice is not --- we do it if we can, else Tom> not. Comparisons between different alternatives are always Tom> handled by computing cost estimates and choosing the lowest. Thank you Tom (and Alvarro) So I've understood pull_up_subqueries (called SELECT MERGE in Starburst). What about things like: 1. DISTINCT PULLUP (Where you realize that you don't have to have an explicit duplicate elimination operation because of what's done in the subquery) 2. DISTINCT pushdown (when a dup elim. can be pushed down if the upper querytree is performign DISTINCT set operations (UNION, INTERSECT etc) 3. Discarding DISTINCT in a subquery because the upper query uses the subquery with existential quantification In general, I'm trying to understand all the transformations that pgsql will try to do .. I'm not trying to figure out plan enumeration for basic boxes (simple query tree). -- Pip-pip Sailesh http://www.cs.berkeley.edu/~sailesh ---(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] Nested xacts: looking for testers and review
I have tested the patches with May 28 16:20 JST snapshot. Here is my first impression: 1) errors, rules regression tests are failed (I'm not sure this is due to your patches) 2) certain behavior was different from what I expected (please correct me if my expectation is wrong). test=# begin; BEGIN test=# insert into t1 values(1); INSERT 17216 1 test=# begin; BEGIN test=# aaa; ERROR: syntax error at or near "aaa" at character 1 ERROR: syntax error at or near "aaa" at character 1 LINE 1: aaa; ^ test=# end; COMMIT test=# select * from t1; <-- I thought this should work since subtransaction was closed ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block test=# end; COMMIT test=# select * from t1; i --- (0 rows) 3) no docs found in the patches. > Hackers, > > Ok, I've finally coded solutions to most problems regarding nested > transactions. This means: > > - reversing for the lock manager, catcache, relcache, buffer manager, > asynchronous notifies, storage manager. > > - transaction block state support, including appropiate XLog recording > > - pg_subtrans subsystem (including changing state from SUBTRANS > COMMITTED to COMMITTED when appropiate). Also pg_clog XLog recovery > was handed to SLRU so pg_subtrans and pg_clog share a rmgr identity. > > - visibility rules. > > I'm missing one item: deferred triggers. The problem with this is that > the deftrig queue is not implemented using normal Lists, so there's no > efficient way to reassign to the parent when the subtransaction commits. > Also I'm not sure what should happen to the "immediate" pointer --- a > subtransaction should have it's own private copy, or it should inherit > the parent's? Please whoever implemented this speak up (Stephan > Szabo?), as I'm not sure of the semantics. > > > I have tested it and it passes all regression tests (including ones I > added), plus some more tests I threw at it mainly for concurrency. > Everything behaves as expected. At this time I'd like to have it > reviewed by the critic eye of the committers, and tested by whoever > would be using it. > > I'm open for comments and suggestions and general input. Thank you. > > -- > Alvaro Herrera () > La web junta la gente porque no importa que clase de mutante sexual seas, > tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con > ciervos incendiánse", y el computador dirá "especifique el tipo de ciervo" > (Jason Alexander) > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Fixing the Turkish problem
Hi, On Sun, 23 May 2004, Tom Lane wrote: > > However, we still fail to sort small I (i dotless) and i. "i dotless" > > comes before i in Turkish Alphabet, but ORDER BY sorts i before "i > > dotless". > > For that, you have to complain to your locale's designer. We just do > what strcoll tells us to. Ok, complained :-) We've reported a bug before, and now latest snapshot of Glibc contains the correct order for Turkish characters. initdb and sort on 7.5devel works perfectly . I'm glad that Turkish locale problems were solved before 7.5 was out. Thanks Tom. Regards, -- Devrim GUNDUZ devrim~gunduz.org devrim.gunduz~linux.org.tr http://www.TDMSoft.com http://www.gunduz.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
