Re: [HACKERS] Feature thought: idle in transaction timeout
Ühel kenal päeval, E, 2007-04-02 kell 19:36, kirjutas Joshua D. Drake: > Tom Lane wrote: > > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > >> Bruce Momjian wrote: > >>> Added to TODO: > >>> * Add idle_timeout GUC so locks are not held for log periods of time > > > >> That should actually be transaction_idle_timeout. It is o.k. for us to > >> be IDLE... it is not o.k. for us to be IDLE in Transaction > > > > Or "idle_in_transaction_timeout"? > > Yeah that would work and it is what I originally typed before > backspacing. I was trying to avoid the _in_ but either way. > > > Anyway I agree that using > > "idle_timeout" for this is unwise. We've been asked often enough for a > > flat-out idle timeout (ie kill session after X seconds of no client > > interaction), and while I disagree with the concept, someday we might > > Well I agree that we shouldn't kill sessions just because they are idle, As the projects to have some out-of-band capabilities in pg wire protocol which could be used to implement keepalives seem to be going nowhere, having an idle_session_timeout to have a at least some protection against server not noticing that client has left (due to network problems for example) may still be a good thing. At least it beats running psql -c "select 'kill '||procpid from pg_stat_activity where current_query = '' and current_timestamp - query_start > '00:01:00';" | bash from postgres users cron each minute to kill stale connections idle_session_timeout is something that should be off by default and would be used only in OLTP production environments where not noticing stale connections can lead to exhausting connection pool by reconnecting clients. > I can imagine all the lovely... my pgpool sessions keep getting killed! > comments. pgpool could do 'select 1' often enough to keep timeout from happening; > > cave and implement it. We should reserve the name for the behavior > > that people would expect a parameter named like that to have. > > Agreed. > > Sincerely, > > Joshua D. Drake > > > > > regards, tom lane > > > > -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Arrays of Complex Types
David Fetter <[EMAIL PROTECTED]> writes: > On Mon, Apr 02, 2007 at 10:01:44PM -0400, Alvaro Herrera wrote: >> So, hum, what happened to the idea of creating the array types only >> on demand? > Scotched, as far as I could tell, More like "you submitted a patch that entirely ignores multiple people's opinion on what is needed". Bruce may have put this into the patch queue, but do not labor under the delusion that that means it'll get applied as-is. The queue is currently operating as a list of open issues. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Calling void functions
Pavel Stehule wrote: > it's problem. You cannot do it now. One year ago I sent patch > > http://archives.postgresql.org/pgsql-patches/2006-03/msg00196.php The only comments to that were that no one knew what it was good for. But now we know, so I think we should add your patch. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Arrays of Complex Types
On Mon, Apr 02, 2007 at 10:01:44PM -0400, Alvaro Herrera wrote: > Bruce Momjian wrote: > > > > Your patch has been added to the PostgreSQL unapplied patches list > > at: > > > > http://momjian.postgresql.org/cgi-bin/pgpatches > > > > It will be applied as soon as one of the PostgreSQL committers > > reviews and approves it. > > > > So, hum, what happened to the idea of creating the array types only > on demand? Scotched, as far as I could tell, partly due to nobody's having actually done work toward such a thing, and partly because the closest thing I've heard to an objection is pretty nebulous. :) It's a lot simpler to have them always, and it fits in with the larger picture of making arrays fully composable with other operations like DOMAIN, ENUM and TYPE. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Modifying TOAST thresholds
[EMAIL PROTECTED] (Tom Lane) writes: > Chris Browne <[EMAIL PROTECTED]> writes: >> [EMAIL PROTECTED] (Tom Lane) writes: >>> ... tuning the TOAST parameters seems like >>> something we understand well enough already, we just need to put some >>> cycles into testing different alternatives. I would have no objection >>> to someone working on that during April and delivering a final patch >>> sometime before beta. > >> Here's a "drafty" patch that *tries* to do this using a GUC variable; >> it passes some interactive testing. > > I came across a couple of issues while fooling with decoupling > TOAST_TUPLE_THRESHOLD from TOAST_MAX_CHUNK_SIZE: > > * Should TOAST_TUPLE_TARGET be configurable separately from > TOAST_TUPLE_THRESHOLD? It certainly doesn't make sense for the target > to be larger, but perhaps it is sane to want it to be smaller. In the longer run, it would be desirable for there to be by-table configurability. Ergo my use of the word "default" in the variable name; that default can remain relevant even in a future 8.4 enhancement. I'm not sure what to prefer with regards to TOAST_TUPLE_TARGET; as you say, it oughtn't be larger than the THRESHOLD value, but I'm not sure how to rationally set it to a specific lower value. > * There's a hardwired assumption in the system that > TOAST_TUPLE_THRESHOLD is invariant: we do not create a toast table at > all when we can prove that the maximum tuple width is less than > TOAST_TUPLE_THRESHOLD (see needs_toast_table() in toasting.c). > Clearly this will not do if TOAST_TUPLE_THRESHOLD can be changed. > Should we abandon the notion altogether, and create a toast table > anytime the table contains any toastable types? Or should we revel > in configurability, and allow CREATE TABLE/ALTER TABLE behavior to vary > depending on the current threshold setting? We'd have to fix the > toaster routines to not try to push stuff out-of-line when there is no > out-of-line to push to ... but I think we probably had better do that > anyway for robustness, if we're allowing any variability at all in these > numbers. > > Comments? In the 8.3 context, it seems to me that "simplicity rulez." In some future version, it would be attractive to have this all variable on a table by table basis; it would at present seem preferable for the default behaviour to be as little divergent from past behaviour as possible. I think I'd be willing to live with the logic that there's no toast table defined if it was "proven" at create time that we couldn't need TOAST. That would conform with present behaviour, and remains simple. The other logical option would be to always create the TOAST table if there exist extendible columns. Those two seem to be the options that are most rational to choose between. I'm happy to defer to well-argued opinions on the matter... -- (reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc")) http://cbbrowne.com/info/lsf.html "Please, Captain. Not in front of the Klingons." -- Leonard Nimoy as Spock in Star Trek V, The Final Frontier ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Synchronized Scan benchmark results
Jeff, Your conclusions sound great - can you perhaps put the timings in a column in your table so we can confirm them? - Luke On 4/2/07 4:14 PM, "Jeff Davis" <[EMAIL PROTECTED]> wrote: > I posted some fairly detailed benchmark results for my Synchronized Scan > patch and it's interactions with Simon Riggs' Recycle Buffers patch > here: > > http://j-davis.com/postgresql/patch15-results.html > > The results are in the form of log files that contain lots of useful > debugging info: > > * log_executor_stats is on (meaning it shows cache hit rate) > * the pid, timestamp, and pagenumber being retrieved (for every 5k pages > read) > * the duration of each scan > > The results are very positive and quite conclusive. > > However, the "sync_seqscan_offset" aspect of my patch, which attempts to > use pages that were cached before the scan began, did not show a lot of > promise. That aspect of my patch may end up being cut. > > The primary aspect of my patch, the Synchronized Scanning, performed > great though. Even the CFQ scheduler, that does not appear to properly > read ahead, performed substantially better than plain 8.2.3. And even > better, Simon's patch didn't seem to hurt Synchronized Scans at all. > > Out of the 36 runs I did, a couple appear anomalous. I will retest those > soon. > > Note: I posted the versions of the patches that I used for the tests on > the page above. The version of Simon's patch that I used did not apply > cleanly to 8.2.3, but the only problem appeared to be in copy.c, so I > went ahead with the tests. If this somehow compromised the patch, then > let me know. > > Regards, > Jeff Davis > > > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Modifying TOAST thresholds
I wrote: > ... should we revel > in configurability, and allow CREATE TABLE/ALTER TABLE behavior to vary > depending on the current threshold setting? We'd have to fix the > toaster routines to not try to push stuff out-of-line when there is no > out-of-line to push to ... but I think we probably had better do that > anyway for robustness, if we're allowing any variability at all in these > numbers. Actually, upon looking closely at the toast code, it already does the right thing when there's no toast table. Good on someone for getting that right. But we still need to think about whether it's sane for CREATE/ALTER TABLE to condition the create-a-toast-table decision on a parameter that may now be changeable. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Modifying TOAST thresholds
I wrote: > Gregory Stark <[EMAIL PROTECTED]> writes: >> Is there any reason to experiment with this? I would have thought we would >> divorce TOAST_MAX_CHUNK_SIZE from TOAST_THRESHOLD and hard code it as the >> same >> expression that's there now. Ie, the largest size that can fit in a page. > No, right now it's the largest size that you can fit 4 on a page. It's > not obvious to me that 4 is optimal once it's divorced from TOAST_THRESHOLD. > It seems possible that the correct number is 1, and even if it's useful > to keep the tuples smaller than that, there's no reason to assume 4 is > the best number per page. I've just committed changes that make it trivial to experiment with the number of toast tuples per page: #define EXTERN_TUPLES_PER_PAGE 4 /* tweak only this */ /* Note: sizeof(PageHeaderData) includes the first ItemId on the page */ #define EXTERN_TUPLE_MAX_SIZE \ MAXALIGN_DOWN((BLCKSZ - \ MAXALIGN(sizeof(PageHeaderData) + (EXTERN_TUPLES_PER_PAGE-1) * sizeof(ItemIdData))) \ / EXTERN_TUPLES_PER_PAGE) #define TOAST_MAX_CHUNK_SIZE\ (EXTERN_TUPLE_MAX_SIZE -\ MAXALIGN(offsetof(HeapTupleHeaderData, t_bits)) - \ sizeof(Oid) - \ sizeof(int32) -\ VARHDRSZ) Anyone who's got time to run performance experiments, have at it ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Feature thought: idle in transaction timeout
TODO updated: * Add idle_in_transaction_timeout GUC so locks are not held for long periods of time --- Joshua D. Drake wrote: > Tom Lane wrote: > > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > >> Bruce Momjian wrote: > >>> Added to TODO: > >>> * Add idle_timeout GUC so locks are not held for log periods of time > > > >> That should actually be transaction_idle_timeout. It is o.k. for us to > >> be IDLE... it is not o.k. for us to be IDLE in Transaction > > > > Or "idle_in_transaction_timeout"? > > Yeah that would work and it is what I originally typed before > backspacing. I was trying to avoid the _in_ but either way. > > > Anyway I agree that using > > "idle_timeout" for this is unwise. We've been asked often enough for a > > flat-out idle timeout (ie kill session after X seconds of no client > > interaction), and while I disagree with the concept, someday we might > > Well I agree that we shouldn't kill sessions just because they are idle, > I can imagine all the lovely... my pgpool sessions keep getting killed! > comments. > > > cave and implement it. We should reserve the name for the behavior > > that people would expect a parameter named like that to have. > > Agreed. > > Sincerely, > > Joshua D. Drake > > > > > regards, tom lane > > > > > -- > >=== The PostgreSQL Company: Command Prompt, Inc. === > Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 > Providing the most comprehensive PostgreSQL solutions since 1997 > http://www.commandprompt.com/ > > Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate > PostgreSQL Replication: http://www.commandprompt.com/products/ > > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
I will write a technical document about Sigres in a week. Hideyuki Bruce Momjian wrote: I am still unclear why sigres is better than a temporary file system. I relize your patch is faster, but what is about your patch that makes it faster. And if we were going to add such capability, we would name it based on what it does, rather than on a 'sigres' mode. --- Hideyuki Kawashima wrote: Simon, Not checkpointing at all is not a good plan, since this will lead to an enormous build up of WAL files and a very long recovery time if the system does fail. I appreciate your detailed comments. Following your comments, I revised the problem. Sigres-0.1.3 does checkpointings. In summary, the features of Sigres-0.1.3 are as follows. 0: 10% faster than conventional PostgreSQL under tmpfs. 1: Checkpointings are continually executed. 2: Sigres mode is in default (the mode can be turned off via postgresql.conf). 3: issue_xlog_sync is called only by bgwriter (continually, via createcheckpoint) 4: The entity of XLogWrite (_XLogWrite in my code) is called by both backends and a bgwriter. For each backend, _XLogWrite is called only via AdvanceXLInsertBuffer. For a bgwriter, _XLogWrite is called via CreateCheckPoint. Please try it if you have interest. http://sourceforge.jp/projects/sigres/ Again, I really appreciate beneficial comments from this community ! Regards, -- Hideyuki -- Hideyuki Kawashima (Ph.D.) University of Tsukuba Assistant Professor ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Hideyuki Kawashima (Ph.D), University of Tsukuba, Graduate School of Systems and Information Engineering Assistant Professor, TEL: +81-29-853-5322 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Feature thought: idle in transaction timeout
Bruce Momjian <[EMAIL PROTECTED]> writes: > Added to TODO: > * Add idle_timeout GUC so locks are not held for log periods of time BTW, before I forget it: there's a non-obvious consideration here, which is not breaking the query protocol. I suspect that we cannot send an unsolicited ERROR message without getting out-of-sync with the client, which will likely take the error as the response to its next command and thenceforth be very confused. What we'll probably have to do to make this work is abort the transaction upon timeout (so that VACUUM et al can get on with things) but not report the error to the client until its next command. And if said next command happens to be ROLLBACK then there's nothing to complain of at all. Doable, probably, but seems a bit messy. regards, tom lane PS: the only case where we currently send an unsolicited ERROR is during SIGTERM or SIGQUIT shutdown; where it doesn't matter if we're out of sync because we're killing the session anyway. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Feature thought: idle in transaction timeout
Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: Bruce Momjian wrote: Added to TODO: * Add idle_timeout GUC so locks are not held for log periods of time That should actually be transaction_idle_timeout. It is o.k. for us to be IDLE... it is not o.k. for us to be IDLE in Transaction Or "idle_in_transaction_timeout"? Yeah that would work and it is what I originally typed before backspacing. I was trying to avoid the _in_ but either way. Anyway I agree that using "idle_timeout" for this is unwise. We've been asked often enough for a flat-out idle timeout (ie kill session after X seconds of no client interaction), and while I disagree with the concept, someday we might Well I agree that we shouldn't kill sessions just because they are idle, I can imagine all the lovely... my pgpool sessions keep getting killed! comments. cave and implement it. We should reserve the name for the behavior that people would expect a parameter named like that to have. Agreed. Sincerely, Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Feature thought: idle in transaction timeout
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Bruce Momjian wrote: >> Added to TODO: >> * Add idle_timeout GUC so locks are not held for log periods of time > That should actually be transaction_idle_timeout. It is o.k. for us to > be IDLE... it is not o.k. for us to be IDLE in Transaction Or "idle_in_transaction_timeout"? Anyway I agree that using "idle_timeout" for this is unwise. We've been asked often enough for a flat-out idle timeout (ie kill session after X seconds of no client interaction), and while I disagree with the concept, someday we might cave and implement it. We should reserve the name for the behavior that people would expect a parameter named like that to have. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Modifying TOAST thresholds
Chris Browne <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] (Tom Lane) writes: >> ... tuning the TOAST parameters seems like >> something we understand well enough already, we just need to put some >> cycles into testing different alternatives. I would have no objection >> to someone working on that during April and delivering a final patch >> sometime before beta. > Here's a "drafty" patch that *tries* to do this using a GUC variable; > it passes some interactive testing. I came across a couple of issues while fooling with decoupling TOAST_TUPLE_THRESHOLD from TOAST_MAX_CHUNK_SIZE: * Should TOAST_TUPLE_TARGET be configurable separately from TOAST_TUPLE_THRESHOLD? It certainly doesn't make sense for the target to be larger, but perhaps it is sane to want it to be smaller. * There's a hardwired assumption in the system that TOAST_TUPLE_THRESHOLD is invariant: we do not create a toast table at all when we can prove that the maximum tuple width is less than TOAST_TUPLE_THRESHOLD (see needs_toast_table() in toasting.c). Clearly this will not do if TOAST_TUPLE_THRESHOLD can be changed. Should we abandon the notion altogether, and create a toast table anytime the table contains any toastable types? Or should we revel in configurability, and allow CREATE TABLE/ALTER TABLE behavior to vary depending on the current threshold setting? We'd have to fix the toaster routines to not try to push stuff out-of-line when there is no out-of-line to push to ... but I think we probably had better do that anyway for robustness, if we're allowing any variability at all in these numbers. Comments? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Feature thought: idle in transaction timeout
Fixed. --- Andrew Dunstan wrote: > Bruce Momjian wrote: > > Added to TODO: > > > > * Add idle_timeout GUC so locks are not held for log periods of time > > > > > > > > ITYM long periods. > > > cheers > > > andrew -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Feature thought: idle in transaction timeout
fixed. --- Joshua D. Drake wrote: > Bruce Momjian wrote: > > Added to TODO: > > > > * Add idle_timeout GUC so locks are not held for log periods of time > > > > That should actually be transaction_idle_timeout. It is o.k. for us to > be IDLE... it is not o.k. for us to be IDLE in Transaction > > > Joshua D. Drake > > > > > > > --- > > > > Tom Lane wrote: > >> "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > >>> Russell Smith wrote: > I agree with this, it reduces the long running transaction problem a > little where the user forgot to commit/rollback their session. I may be > worth having a transaction_timeout as well, and setting it to link a few > hours by default. That way you can't have really long running > transactions unless you specifically set that. > >>> We would certainly need to be able to disable on the fly too just with > >>> SET as well. > >> AFAICS, a *transaction* timeout per se has no use whatever except as a > >> foot-gun. How will you feel when you start a 12-hour restore, go home > >> for the evening, and come back in the morning to find it aborted because > >> you forgot to disable your 4-hour timeout? > >> > >> Furthermore, if you have to set transaction_timeout to multiple hours > >> in the (vain) hope of not killing something important, what use is it > >> really? If you want to keep VACUUM able to work in a busy database, > >> you need it to be a lot less than that. > >> > >> An *idle* timeout seems less risky, as well as much easier to pick a > >> sane value for. > >> > >>regards, tom lane > >> > >> ---(end of broadcast)--- > >> TIP 7: You can help support the PostgreSQL project by donating at > >> > >> http://www.postgresql.org/about/donate > > > > > -- > >=== The PostgreSQL Company: Command Prompt, Inc. === > Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 > Providing the most comprehensive PostgreSQL solutions since 1997 > http://www.commandprompt.com/ > > Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate > PostgreSQL Replication: http://www.commandprompt.com/products/ -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Feature thought: idle in transaction timeout
Bruce Momjian wrote: Added to TODO: * Add idle_timeout GUC so locks are not held for log periods of time ITYM long periods. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Arrays of Complex Types
Bruce Momjian wrote: > > Your patch has been added to the PostgreSQL unapplied patches list at: > > http://momjian.postgresql.org/cgi-bin/pgpatches > > It will be applied as soon as one of the PostgreSQL committers reviews > and approves it. > So, hum, what happened to the idea of creating the array types only on demand? > > > David Fetter wrote: > > On Fri, Mar 30, 2007 at 05:08:42PM -0400, Tom Lane wrote: > > > David Fetter <[EMAIL PROTECTED]> writes: > > > > After several rounds of patches, it appears that it might be easier to > > > > create a new typtype entry, which I'll tentatively call 'a' because it > > > > seems a little fragile and a lot inelegant and hard to maintain to > > > > have typtype='c' and typrelid=InvalidOid mean, "this is an array of > > > > complex types." > > > > > > Uh, wouldn't it be typtype = 'c' and typelem != 0 ? > > > > Right. The attached patch passes the current regression tests and at > > least to a "smoke test" level does what it's supposed to do. I'd > > really like to help refactor the whole array system to use 'a', tho. > > > > Cheers, -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Feature thought: idle in transaction timeout
Bruce Momjian wrote: Added to TODO: * Add idle_timeout GUC so locks are not held for log periods of time That should actually be transaction_idle_timeout. It is o.k. for us to be IDLE... it is not o.k. for us to be IDLE in Transaction Joshua D. Drake --- Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: Russell Smith wrote: I agree with this, it reduces the long running transaction problem a little where the user forgot to commit/rollback their session. I may be worth having a transaction_timeout as well, and setting it to link a few hours by default. That way you can't have really long running transactions unless you specifically set that. We would certainly need to be able to disable on the fly too just with SET as well. AFAICS, a *transaction* timeout per se has no use whatever except as a foot-gun. How will you feel when you start a 12-hour restore, go home for the evening, and come back in the morning to find it aborted because you forgot to disable your 4-hour timeout? Furthermore, if you have to set transaction_timeout to multiple hours in the (vain) hope of not killing something important, what use is it really? If you want to keep VACUUM able to work in a busy database, you need it to be a lot less than that. An *idle* timeout seems less risky, as well as much easier to pick a sane value for. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Implicit casts to text
Added to TODO: * Allow all data types to cast to and from TEXT http://archives.postgresql.org/pgsql-hackers/2007-04/msg00017.php --- Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > The attached patch changes all implicit casts to text to assignment and > > cleans up the associated regression test damage. This change has been > > discussed for the longest time; I propose that we bite the bullet and > > do it now. > > [ I'm assuming this isn't an April-fool item, otherwise never mind ] > > The scheme that was in the back of my mind was to do this at the same > time as providing a general facility for casting *every* type to and > from text, by means of their I/O functions if no specialized cast is > provided in pg_cast. This would improve functionality, thus providing > a salve to the annoyance of users whose code the restriction breaks: > we can certainly argue that it wouldn't do for all those automatically > created casts to be implicit. At the same time it'd let us eliminate > redundant text-to/from-foo code that's currently in place for some but > not all datatypes. > > If we do only the restrictive part of this, it's a harder sale. > > So, +1 on the concept, but I think we want a larger patch, and it's > probably too late for that for 8.3. > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Feature thought: idle in transaction timeout
Added to TODO: * Add idle_timeout GUC so locks are not held for log periods of time --- Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > Russell Smith wrote: > >> I agree with this, it reduces the long running transaction problem a > >> little where the user forgot to commit/rollback their session. I may be > >> worth having a transaction_timeout as well, and setting it to link a few > >> hours by default. That way you can't have really long running > >> transactions unless you specifically set that. > > > We would certainly need to be able to disable on the fly too just with > > SET as well. > > AFAICS, a *transaction* timeout per se has no use whatever except as a > foot-gun. How will you feel when you start a 12-hour restore, go home > for the evening, and come back in the morning to find it aborted because > you forgot to disable your 4-hour timeout? > > Furthermore, if you have to set transaction_timeout to multiple hours > in the (vain) hope of not killing something important, what use is it > really? If you want to keep VACUUM able to work in a busy database, > you need it to be a lot less than that. > > An *idle* timeout seems less risky, as well as much easier to pick a > sane value for. > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] CheckpointStartLock starvation
ITAGAKI Takahiro <[EMAIL PROTECTED]> writes: > Heikki Linnakangas <[EMAIL PROTECTED]> wrote: >> It looks like the bgwriter gets starved waiting on the >> CheckpointStartLock. The CheckpointStartLock is held in shared mode over >> an XLogFlush when committing, which on an extremely busy system like a >> benchmark is always long enough to have a new transaction to acquire the >> CheckpointStartLock again. > If the starvation comes from giving unfair priorities on shared locks > against exclusive locks, does the below TODO item help us? Tweaking the lock rules was my first thought too, but the side-effects might be undesirable. In this particular case it would certainly be better to not have a lock at all, since having checkpoint block commits even briefly is not what we'd like. I think Heikki's plan of having backends show in PGPROC that they're in a commit critical section is basically sound, we just have to get the details straight. Since checkpoint doesn't need to be instantaneous, it's probably sufficient to just have it sleep 10 msec or so and recheck to see if all the blockers are gone, instead of doing any kind of fancy signaling. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Modifying TOAST thresholds
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom, are you going to do this for 8.3? Right, I promised to do that --- will work on it now. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Modifying TOAST thresholdsf
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---
Chris Browne wrote:
> [EMAIL PROTECTED] (Tom Lane) writes:
> > "Simon Riggs" <[EMAIL PROTECTED]> writes:
> >> Well it certainly seems worth separating them. It does seem possible
> >> that recursive toasting effected some of the earlier results we looked
> >> at.
> >
> >> Would you like me to do this, or will you?
> >
> > I'm willing to do the code changes to separate TOAST_THRESHOLD from
> > the toast chunk size, but I do not have the time or facilities to do
> > any performance testing for different parameter choices. Anyone want
> > to work on that?
> >
> >> I'd like to get some mechanism for reducing WAL volume into 8.3, whether
> >> its configurable toast or WAL reduction for UPDATEs. If for no other
> >> reason than making backup and availability solutions more manageable.
> >
> > I think the WAL-reduction proposal needs more time and thought than is
> > feasible before 8.3. OTOH, tuning the TOAST parameters seems like
> > something we understand well enough already, we just need to put some
> > cycles into testing different alternatives. I would have no objection
> > to someone working on that during April and delivering a final patch
> > sometime before beta.
>
> Here's a "drafty" patch that *tries* to do this using a GUC variable;
> it passes some interactive testing. It probably needs an
> assign_hook() function to do further validation (probably to make sure
> that sizes are rightly aligned on both 32 and 64 bit platforms); feel
> free to consider me incompetent at this stage at generating such...
>
> I would *very* much like to see something of this sort in 8.3; that
> would be of definite value to some of our applications which store
> data that is a bit too small to meet the present
> TOAST_TUPLE_THRESHOLD. (E.g. - it's worth noting that common
> SOAP-like XML requests are in the 700-1000 byte range; such values are
> generally nicely compressible and are often not likely to be used in
> summary-oriented queries on mainline tables...)
>
> I don't think I can come up with a performance "test suite" this week,
> and will be unavailable from April 6-14th; if others were to find this
> valuable, and volunteer to set up some sort of test in the interim,
> that would be super. Absent that, I should be able to do some work on
> this in the latter half of April.
>
> set toast_default_threshold TO 128;
> create table sample (id serial primary key, txt text);
> insert into sample (txt) values
> ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> create table sample (id serial primary key, txt text);
> insert into sample (txt) values
> ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
> ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
> ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
> ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
> ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
> ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
> ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
> ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
> ('123478903479023178490187324170947390278478912309780412378903412789041237890123479801234790812347890413789012347890412379801234789014237890142379801234789013427890142
Re: [HACKERS] Modifying TOAST thresholds
Tom, are you going to do this for 8.3?
---
Tom Lane wrote:
> In another thread I wrote:
> > ... One thing I was just thinking about is that it's silly to have
> > the threshold constrained so strongly by a desire that tuples in toast
> > tables not be toastable. It would be trivial to tweak the heapam.c
> > routines so that they simply don't invoke the toaster when relkind is
> > 't', and then we could have independent choices of toast-tuple size and
> > main-tuple size. This would be particularly good because in the current
> > scheme you can't modify toast-tuple size without an initdb, but if that
> > were decoupled there'd be no reason not to allow changes in the
> > main-tuple thresholds.
>
> After thinking about this more I'm convinced that the above is a good
> idea, eg in heap_insert change
>
> if (HeapTupleHasExternal(tup) || tup->t_len > TOAST_TUPLE_THRESHOLD)
> heaptup = toast_insert_or_update(relation, tup, NULL, use_wal);
> else
> heaptup = tup;
>
> to
>
> if (relation->rd_rel->relkind == RELKIND_TOASTVALUE)
> {
> /* toast table entries should never be recursively toasted */
> Assert(!HeapTupleHasExternal(tup));
> heaptup = tup;
> }
> else if (HeapTupleHasExternal(tup) || tup->t_len > TOAST_TUPLE_THRESHOLD)
> heaptup = toast_insert_or_update(relation, tup, NULL, use_wal);
> else
> heaptup = tup;
>
> I also think that we ought to add TOAST_MAX_CHUNK_SIZE to the set of
> compiled-in parameters that are recorded in pg_control and checked for
> compatibility at startup (like BLCKSZ) --- this will prevent anyone from
> shooting themselves in the foot while experimenting.
>
> Any objections?
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
--
Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Bug in UTF8-Validation Code?
On 2007-04-02, Mark Dilger <[EMAIL PROTECTED]> wrote: > Here's the code for the new chr() function: > > if (pg_database_encoding_max_length() > 1 && !lc_ctype_is_c()) Clearly wrong - this allows returning invalid UTF8 data in locale C, which is not an uncommon setting to use. Treating the parameter as bytes is wrong too - it should correspond to whatever the natural character numbering for the encoding is; for utf8 that is the Unicode code point. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] CheckpointStartLock starvation
Heikki Linnakangas <[EMAIL PROTECTED]> wrote: > It looks like the bgwriter gets starved waiting on the > CheckpointStartLock. The CheckpointStartLock is held in shared mode over > an XLogFlush when committing, which on an extremely busy system like a > benchmark is always long enough to have a new transaction to acquire the > CheckpointStartLock again. If the starvation comes from giving unfair priorities on shared locks against exclusive locks, does the below TODO item help us? | Locking | Fix priority ordering of read and write light-weight locks (Neil) | http://archives.postgresql.org/pgsql-hackers/2004-11/msg00893.php | http://archives.postgresql.org/pgsql-hackers/2004-11/msg00905.php Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Arrays of Complex Types
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- David Fetter wrote: > On Fri, Mar 30, 2007 at 05:08:42PM -0400, Tom Lane wrote: > > David Fetter <[EMAIL PROTECTED]> writes: > > > After several rounds of patches, it appears that it might be easier to > > > create a new typtype entry, which I'll tentatively call 'a' because it > > > seems a little fragile and a lot inelegant and hard to maintain to > > > have typtype='c' and typrelid=InvalidOid mean, "this is an array of > > > complex types." > > > > Uh, wouldn't it be typtype = 'c' and typelem != 0 ? > > Right. The attached patch passes the current regression tests and at > least to a "smoke test" level does what it's supposed to do. I'd > really like to help refactor the whole array system to use 'a', tho. > > Cheers, > D > -- > David Fetter <[EMAIL PROTECTED]> http://fetter.org/ > phone: +1 415 235 3778AIM: dfetter666 > Skype: davidfetter > > Remember to vote! > Consider donating to PostgreSQL: http://www.postgresql.org/about/donate [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Bug in UTF8-Validation Code?
Mark Dilger wrote: Since chr() is defined in oracle_compat.c, I decided to look at what Oracle might do. See http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions18a.htm It looks to me like they are doing the same thing that I did, though I don't have Oracle installed anywhere to verify that. Is there a difference? Reading that page again, I think I'd have to use mbrtowc() or similar in the spot where I'm currently just using the literal utf8 string. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Many unfinished patches
I am currently finishing off an improved VACUUM implementation for bitmaps. The rest of the patch is ready for review. I will try and post a patch within 24 hours. Gavin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_index updates and SI invalidation
Where are we on this? --- Tom Lane wrote: > "Pavan Deolasee" <[EMAIL PROTECTED]> writes: > > On 3/28/07, Tom Lane <[EMAIL PROTECTED]> wrote: > >> It seems a bit brute-force. Why didn't you use SearchSysCache(INDEXRELID) > >> the same as RelationInitIndexAccessInfo does? > > > I tried that initially, but it gets into infinite recursion during initdb. > > [squint...] How can that fail during a reload if it worked the first > time? Needs a closer look at what's happening. > > regards, tom lane > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Synchronized Scan benchmark results
I posted some fairly detailed benchmark results for my Synchronized Scan patch and it's interactions with Simon Riggs' Recycle Buffers patch here: http://j-davis.com/postgresql/patch15-results.html The results are in the form of log files that contain lots of useful debugging info: * log_executor_stats is on (meaning it shows cache hit rate) * the pid, timestamp, and pagenumber being retrieved (for every 5k pages read) * the duration of each scan The results are very positive and quite conclusive. However, the "sync_seqscan_offset" aspect of my patch, which attempts to use pages that were cached before the scan began, did not show a lot of promise. That aspect of my patch may end up being cut. The primary aspect of my patch, the Synchronized Scanning, performed great though. Even the CFQ scheduler, that does not appear to properly read ahead, performed substantially better than plain 8.2.3. And even better, Simon's patch didn't seem to hurt Synchronized Scans at all. Out of the 36 runs I did, a couple appear anomalous. I will retest those soon. Note: I posted the versions of the patches that I used for the tests on the page above. The version of Simon's patch that I used did not apply cleanly to 8.2.3, but the only problem appeared to be in copy.c, so I went ahead with the tests. If this somehow compromised the patch, then let me know. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Many unfinished patches
As you can see from my email traffic today, we have a significant number of patches that were never completed by the authors, or were completed but not adjusted and resubmitted based on community feedback. I feel we have more this release than usual. I warned about this last week. Not sure what we can do about it --- it is happening mostly from new contributors. What I am doing is to document them on the TODO list with URLs, or put them in the patches_hold queue: http://momjian.postgresql.org/cgi-bin/pgpatches_hold -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bug in UTF8-Validation Code?
Mark Dilger wrote: Tom Lane wrote: Mark Dilger <[EMAIL PROTECTED]> writes: pgsql=# select chr(14989485); chr - ä¸ (1 row) Is there a principled rationale for this particular behavior as opposed to any other? In particular, in UTF8 land I'd have expected the argument of chr() to be interpreted as a Unicode code point, not as actual UTF8 bytes with a randomly-chosen endianness. Not sure what to do in other multibyte encodings. "Not sure what to do in other multibyte encodings" was pretty much my rationale for this particular behavior. I standardized on network byte order because there are only two endianesses to choose from, and the other seems to be a more surprising choice. I looked around on the web for a standard for how to convert an integer into a valid multibyte character and didn't find anything. Andrew, Supernews has said upthread that chr() is clearly wrong and needs to be fixed. If so, we need some clear definition what "fixed" means. Any suggestions? mark Since chr() is defined in oracle_compat.c, I decided to look at what Oracle might do. See http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions18a.htm It looks to me like they are doing the same thing that I did, though I don't have Oracle installed anywhere to verify that. Is there a difference? mark ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Bug: Buffer cache is not scan resistant
"test" version, but I am putting in the queue so we can track it there. Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Simon Riggs wrote: > On Mon, 2007-03-12 at 09:14 +, Simon Riggs wrote: > > On Mon, 2007-03-12 at 16:21 +0900, ITAGAKI Takahiro wrote: > > > > With the default > > > value of scan_recycle_buffers(=0), VACUUM seems to use all of buffers in > > > pool, > > > just like existing sequential scans. Is this intended? > > > > Yes, but its not very useful for testing to have done that. I'll do > > another version within the hour that sets N=0 (only) back to current > > behaviour for VACUUM. > > New test version enclosed, where scan_recycle_buffers = 0 doesn't change > existing VACUUM behaviour. > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com > [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Interaction of PITR backups and Bulkoperationsavoiding WAL
Where is this patch? --- Simon Riggs wrote: > On Fri, 2007-03-09 at 11:47 -0500, Tom Lane wrote: > > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > > On Fri, 2007-03-09 at 11:15 -0500, Tom Lane wrote: > > >> It strikes me that allowing archive_command to be changed on the fly > > >> might not be such a good idea though, or at least it shouldn't be > > >> possible to flip it from empty to nonempty during live operation. > > > > > I'd rather fix it the proposed way than force a restart. ISTM wrong to > > > have an availability feature cause downtime. > > > > I don't think that people are very likely to need to turn archiving on > > and off on-the-fly. Your proposed solution introduces a great deal of > > complexity (and risk of future bugs-of-omission, to say nothing of race > > conditions) to solve a non-problem. We have better things to be doing > > with our development time. > > It's certainly a quicker fix. Unless others object, I'll set > archive_command to only be changeable at server startup. > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com > > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Bug in UTF8-Validation Code?
Mark Dilger wrote: Tom Lane wrote: Mark Dilger <[EMAIL PROTECTED]> writes: pgsql=# select chr(14989485); chr - ä¸ (1 row) Is there a principled rationale for this particular behavior as opposed to any other? In particular, in UTF8 land I'd have expected the argument of chr() to be interpreted as a Unicode code point, not as actual UTF8 bytes with a randomly-chosen endianness. Not sure what to do in other multibyte encodings. "Not sure what to do in other multibyte encodings" was pretty much my rationale for this particular behavior. I standardized on network byte order because there are only two endianesses to choose from, and the other seems to be a more surprising choice. I looked around on the web for a standard for how to convert an integer into a valid multibyte character and didn't find anything. Andrew, Supernews has said upthread that chr() is clearly wrong and needs to be fixed. If so, we need some clear definition what "fixed" means. Any suggestions? mark Another issue to consider when thinking about the corect definition of chr() is that ascii(chr(X)) = X. This gets weird if X is greater than 255. If nothing else, the name "ascii" is no longer appropriate. mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Modifying TOAST thresholds
[EMAIL PROTECTED] (Tom Lane) writes:
> "Simon Riggs" <[EMAIL PROTECTED]> writes:
>> Well it certainly seems worth separating them. It does seem possible
>> that recursive toasting effected some of the earlier results we looked
>> at.
>
>> Would you like me to do this, or will you?
>
> I'm willing to do the code changes to separate TOAST_THRESHOLD from
> the toast chunk size, but I do not have the time or facilities to do
> any performance testing for different parameter choices. Anyone want
> to work on that?
>
>> I'd like to get some mechanism for reducing WAL volume into 8.3, whether
>> its configurable toast or WAL reduction for UPDATEs. If for no other
>> reason than making backup and availability solutions more manageable.
>
> I think the WAL-reduction proposal needs more time and thought than is
> feasible before 8.3. OTOH, tuning the TOAST parameters seems like
> something we understand well enough already, we just need to put some
> cycles into testing different alternatives. I would have no objection
> to someone working on that during April and delivering a final patch
> sometime before beta.
Here's a "drafty" patch that *tries* to do this using a GUC variable;
it passes some interactive testing. It probably needs an
assign_hook() function to do further validation (probably to make sure
that sizes are rightly aligned on both 32 and 64 bit platforms); feel
free to consider me incompetent at this stage at generating such...
I would *very* much like to see something of this sort in 8.3; that
would be of definite value to some of our applications which store
data that is a bit too small to meet the present
TOAST_TUPLE_THRESHOLD. (E.g. - it's worth noting that common
SOAP-like XML requests are in the 700-1000 byte range; such values are
generally nicely compressible and are often not likely to be used in
summary-oriented queries on mainline tables...)
I don't think I can come up with a performance "test suite" this week,
and will be unavailable from April 6-14th; if others were to find this
valuable, and volunteer to set up some sort of test in the interim,
that would be super. Absent that, I should be able to do some work on
this in the latter half of April.
set toast_default_threshold TO 128;
create table sample (id serial primary key, txt text);
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
create table sample (id serial primary key, txt text);
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('123478903479023178490187324170947390278478912309780412378903412789041237890123479801234790812347890413789012347890412379801234789014237890142379801234789013427890142378901423
Re: [HACKERS] Bug in UTF8-Validation Code?
Tom Lane wrote: Mark Dilger <[EMAIL PROTECTED]> writes: pgsql=# select chr(14989485); chr - ä¸ (1 row) Is there a principled rationale for this particular behavior as opposed to any other? In particular, in UTF8 land I'd have expected the argument of chr() to be interpreted as a Unicode code point, not as actual UTF8 bytes with a randomly-chosen endianness. Not sure what to do in other multibyte encodings. "Not sure what to do in other multibyte encodings" was pretty much my rationale for this particular behavior. I standardized on network byte order because there are only two endianesses to choose from, and the other seems to be a more surprising choice. I looked around on the web for a standard for how to convert an integer into a valid multibyte character and didn't find anything. Andrew, Supernews has said upthread that chr() is clearly wrong and needs to be fixed. If so, we need some clear definition what "fixed" means. Any suggestions? mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PL/Python warnings in CVS HEAD
Where are we on Python 2.5? --- Tom Lane wrote: > Neil Conway <[EMAIL PROTECTED]> writes: > >> No, it just looks like a Python API 2.5 change to me > > > Attached is a patch that fixes the warnings. Unfortunately, it seems > > this patch won't compile against Python 2.4: the 2.5 API requires the > > use of some typedef's that AFAICS were only introduced in 2.5. > > > Since we presumably still want to support Python < 2.5, we can either > > not apply this patch and tolerate the warnings, or else we can > > workaround the incompatibility with some preprecessor hackery (e.g. > > supply the missing typedef's ourselves if Python doesn't provide them). > > Sounds like #ifdef time to me --- but it seems a bit strange; wouldn't > the Python guys have taken a bit more care for compatibility of > user-supplied code? We're hardly the only people who want to support > multiple Python versions. Perhaps they provide a compatibility hack > that you didn't spot? > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
Added to TODO:
o Have timestamp subtraction not call justify_hours()?
http://archives.postgresql.org/pgsql-sql/2006-10/msg00059.php
---
Jim C. Nasby wrote:
> Yes, but if it was '2004-01-02 01:00:00'-'2004-01-01 00:00:00' it should
> return 25:00:00, not 1 day 1:00.
>
> I agree with Tom that this should be changed; I'm just arguing that we
> might well need a backwards-compatibility solution for a while. At the
> very least we'd need to make this change very clear to users.
>
> On Tue, Feb 20, 2007 at 08:07:11PM -0500, Bruce Momjian wrote:
> >
> > One problem with removing justify_hours() is that this is going to
> > return '24:00:00', rather than '1 day:
> >
> > test=> select '2004-01-02 00:00:00'::timestamptz - '2004-01-01
> > 00:00:00'::timestamptz;
> > ?column?
> > --
> > 24:00:00
> > (1 row)
> >
> > ---
> >
> > Jim Nasby wrote:
> > > On Oct 5, 2006, at 11:50 AM, Tom Lane wrote:
> > > > regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01
> > > > 09:30:41'::timestamp);
> > > > ?column?
> > > > --
> > > > 14 days 14:28:19
> > > > (1 row)
> > > >
> > > > should be reporting '350:28:19' instead.
> > > >
> > > > This is a hack that was done to minimize the changes in the regression
> > > > test expected outputs when we changed type interval from months/
> > > > seconds
> > > > to months/days/seconds. But I wonder whether it wasn't a dumb idea.
> > > > It is certainly inconsistent, as noted in the code comments.
> > > >
> > > > I'm tempted to propose that we remove the justify_hours call, and tell
> > > > anyone who really wants the old results to apply justify_hours() to
> > > > the
> > > > subtraction result for themselves. Not sure what the fallout would
> > > > be,
> > > > though.
> > >
> > > I suspect there's applications out there that are relying on that
> > > being nicely formated for display purposes.
> > >
> > > I agree it should be removed, but we might need a form of backwards
> > > compatibility for a version or two...
> > > --
> > > Jim Nasby[EMAIL PROTECTED]
> > > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
> > >
> > >
> > >
> > > ---(end of broadcast)---
> > > TIP 3: Have you checked our extensive FAQ?
> > >
> > >http://www.postgresql.org/docs/faq
> >
> > --
> > Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us
> > EnterpriseDB http://www.enterprisedb.com
> >
> > + If your life is a hard drive, Christ can be your backup. +
> >
> > ---(end of broadcast)---
> > TIP 2: Don't 'kill -9' the postmaster
>
> --
> Jim Nasby[EMAIL PROTECTED]
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
--
Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Bug in UTF8-Validation Code?
Mark Dilger <[EMAIL PROTECTED]> writes: >> pgsql=# select chr(14989485); >> chr >> - >> ä¸ >> (1 row) Is there a principled rationale for this particular behavior as opposed to any other? In particular, in UTF8 land I'd have expected the argument of chr() to be interpreted as a Unicode code point, not as actual UTF8 bytes with a randomly-chosen endianness. Not sure what to do in other multibyte encodings. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [PATCHES] pg_standby
Simon Riggs wrote: > On Thu, 2007-03-08 at 13:29 -0500, Doug Knight wrote: > > > I would preserve the existing trigger function as little t "-t", and > > maybe implement a catchup trigger function as big t "-T"? Set it up so > > that if the first attempt to find the WAL file postgres is currently > > requesting succeeds, skip over the trigger check. If the first attempt > > fails, then do your trigger check. That way, in the OCF script, the > > postmaster can be started, the trigger file set, and connection to the > > database looped on until it succeeds as an indication for when the > > database is up and available. I think that's cleaner than comparing a > > filename from a 'ps' command. Once I've completed the OCF script and > > done some testing, I'll forward it to you for you to review and see if > > you want to include it. > > I'm happy to do this, unless other objections. > > I'll be doing another version before feature freeze. Should we be getting a patch for this for 8.3? -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Grouped Index Tuples / Clustered Indexes
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Heikki Linnakangas wrote: > I've updated the GIT patch at http://community.enterprisedb.com/git/. > Bitrot caused by the findinsertloc-patch has been fixed, making that > part of the GIT patch a little bit smaller and cleaner. I also did some > refactoring, and minor cleanup and commenting. > > Any comments on the design or patch? For your convenience, I copied the > same text I added to access/nbtree/README to > http://community.enterprisedb.com/git/git-readme.txt > > Should we start playing the name game at this point? I've been thinking > we should call this feature just Clustered Indexes, even though it's not > exactly the same thing as clustered indexes in other DBMSs. From user > point of view, they behave similarly enough that it may be best to use > the existing term. > > As a next step, I'm hoping to get the indexam API changes from the > bitmap index patch committed soon, and in a way that supports GIT as well. > > -- >Heikki Linnakangas >EnterpriseDB http://www.enterprisedb.com > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] Mentor for ASync I/O for SoC
PG Hackers, We've had a proposal to work on Async I/O for Google SoC, and it's a great looking proposal. However, none of the current SoC mentors feels up to taking it on; is there any hacker who can do it? I'd hate to drop this proposal just because we can't mentor it. Anyone? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Bug in UTF8-Validation Code?
Mark Dilger wrote:
Andrew - Supernews wrote:
On 2007-04-01, Mark Dilger <[EMAIL PROTECTED]> wrote:
Do any of the string functions (see
http://www.postgresql.org/docs/8.2/interactive/functions-string.html)
run the risk of generating invalid utf8 encoded strings? Do I need
to add checks?
Are there known bugs with these functions in this regard?
The chr() function returns an octet, rather than a character; this is
clearly
wrong and needs fixing.
Ok, I've altered the chr() function. I am including a transcript from
psql below. There are several design concerns:
1) In the current implementation, chr(0) returns a 5-byte text object
(4-bytes of overhead plus one byte of data) containing a null. In the
new implementation, this returns an error. I don't know, but it is
possible that people currently use things like "SELECT chr(0) || chr(0)
|| ..." to build up strings of nulls.
2) Under utf8, chr(X) fails for X = 128..255. This may also break
current users expectations.
3) The implicit modulus operation that was being performed by chr() is
now gone, which might break some users.
4) You can't represent the high end of the astral plain with type
INTEGER, unless you pass in a negative value, which is somewhat
unintuitive. Since chr() expects an integer (and not a bigint) the user
needs handle the sign bit correctly.
mark
-
Welcome to psql 8.3devel, 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
pgsql=# select chr(0);
ERROR: character 0x00 of encoding "SQL_ASCII" has no equivalent in "UTF8"
pgsql=# select chr(65);
chr
-
A
(1 row)
pgsql=# select chr(128);
ERROR: character 0x80 of encoding "SQL_ASCII" has no equivalent in "UTF8"
pgsql=# select chr(53398);
chr
-
Ж
(1 row)
pgsql=# select chr(14989485);
chr
-
中
(1 row)
pgsql=# select chr(4036005254);
ERROR: function chr(bigint) does not exist
LINE 1: select chr(4036005254);
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
Here's the code for the new chr() function:
Datum
chr(PG_FUNCTION_ARGS)
{
int32 cvalue = PG_GETARG_INT32(0);
text *result;
if (pg_database_encoding_max_length() > 1 && !lc_ctype_is_c())
{
int encoding,
len,
byteoff;
uint32 buf[2];
const char *bufptr;
encoding = GetDatabaseEncoding();
buf[0] = htonl(cvalue);
buf[1] = 0;
bufptr = (const char *)&buf;
for (byteoff = 0; byteoff < sizeof(uint32) && 0 == *bufptr; ++byteoff,
++bufptr);
len = pg_encoding_mblen(encoding,bufptr);
if (byteoff + len != sizeof(uint32) || !pg_verify_mbstr(encoding,
bufptr, len, true /* noError */))
report_untranslatable_char(PG_SQL_ASCII, encoding, bufptr,
sizeof(int32));
result = (text *) palloc(VARHDRSZ + len);
SET_VARSIZE(result, VARHDRSZ + len);
memcpy(VARDATA(result),bufptr,len);
}
else
{
result = (text *) palloc(VARHDRSZ + 1);
SET_VARSIZE(result, VARHDRSZ + 1);
*VARDATA(result) = (char) cvalue;
}
PG_RETURN_TEXT_P(result);
}
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] Questions about pid file creation code
Zdenek Kotala <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Just to distinguish postmasters from standalone backends in the error >> messages. I think that's still useful. > I'm not sure what you mean. It is used only in CreatePidFile function > and I think that if directory is locked by some process, I don't see any > useful reason to know if it is postmaster or standalone backend. You don't? Consider the decisions the user needs to take upon seeing the message --- should he kill that other process or not, and if so how? Knowing whether it's a postmaster seems pretty important to me. > Yes there are. But it does not sense for me. If I want to open file and > another process remove it, why I want to try created it again when > another process going to do it? That could be the track of another postmaster just now shutting down. There's no reason to fail to start in such a scenario. The looping logic is necessary anyway (to guard against races involving two postmasters trying to start at the same time), so we might as well let it handle this case too. > I'm sorry, I meant why there is a pid cleanup which stays there after > another postmaster crash. Many application only check OK there is some > pid file -> exit. And rest is on start script or some other monitoring > facility. The start script does not typically have the intelligence to get this right, particularly not the is-shmem-still-in-use part. If you check the archives you will find many of us on record telling people who think they should remove the pidfile in their start script that they're crazy. >> It's not actually trying to validate the syntax of the lock file, only >> to make certain it doesn't trigger any unexpected behavior in kill(). > I not sure if we talk about same place. Yes, we are. Read the kill(2) man page and note the special behaviors for pid = 0 or -1. The test is just trying to be darn certain we don't invoke those behaviors. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Bug in UTF8-Validation Code?
Andrew - Supernews wrote: On 2007-04-01, Mark Dilger <[EMAIL PROTECTED]> wrote: Do any of the string functions (see http://www.postgresql.org/docs/8.2/interactive/functions-string.html) run the risk of generating invalid utf8 encoded strings? Do I need to add checks? Are there known bugs with these functions in this regard? The chr() function returns an octet, rather than a character; this is clearly wrong and needs fixing. Ok, I've altered the chr() function. I am including a transcript from psql below. There are several design concerns: 1) In the current implementation, chr(0) returns a 5-byte text object (4-bytes of overhead plus one byte of data) containing a null. In the new implementation, this returns an error. I don't know, but it is possible that people currently use things like "SELECT chr(0) || chr(0) || ..." to build up strings of nulls. 2) Under utf8, chr(X) fails for X = 128..255. This may also break current users expectations. 3) The implicit modulus operation that was being performed by chr() is now gone, which might break some users. 4) You can't represent the high end of the astral plain with type INTEGER, unless you pass in a negative value, which is somewhat unintuitive. Since chr() expects an integer (and not a bigint) the user needs handle the sign bit correctly. mark - Welcome to psql 8.3devel, 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 pgsql=# select chr(0); ERROR: character 0x00 of encoding "SQL_ASCII" has no equivalent in "UTF8" pgsql=# select chr(65); chr - A (1 row) pgsql=# select chr(128); ERROR: character 0x80 of encoding "SQL_ASCII" has no equivalent in "UTF8" pgsql=# select chr(53398); chr - Ж (1 row) pgsql=# select chr(14989485); chr - 中 (1 row) pgsql=# select chr(4036005254); ERROR: function chr(bigint) does not exist LINE 1: select chr(4036005254); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] So are we calling it: Feature Freeze?
Josh Berkus wrote: > Bruce, > > > FYI, I am ready to move uncompleted patches into the 8.4 hold queue when > > we are ready. > > What about the patches for which the submitters are waiting for other > pending patches? Some of the patches in your "uncomplete" list match that > description ... Complete just means the author things he/she is done, and has responded to all requests for changes. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] So are we calling it: Feature Freeze?
Bruce, > FYI, I am ready to move uncompleted patches into the 8.4 hold queue when > we are ready. What about the patches for which the submitters are waiting for other pending patches? Some of the patches in your "uncomplete" list match that description ... -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CheckpointStartLock starvation
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> What sort of "wait for finish" mechanism do you have in mind? > I was thinking of XactLockTableWait. Ugh. I don't think the bgwriter can participate in heavyweight-lockmgr operations, or should become able to. Nor will that work for prepared xacts --- you don't want to wait for the eventual commit, only for PREPARE TRANSACTION to exit its critical section. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Is this portable?
Gregory Stark <[EMAIL PROTECTED]> writes: > IIRC there's something odd about the scope of the declared struct label. > Something like it previously extended to the end of the file but post-ANSI was > limited to the scope it's declared in (including very limited scopes where it > would be useless such as in function parameters). I think you might be thinking of the use of a previously unreferenced "struct foo" in a function declaration's parameter list, which is something that did change (and so gcc warns about it). But within a block is not that case. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Re: Invalid to_date patterns (was: [PATCHES] [GENERAL] ISO week dates)
Because this patch was not completed, I have added it to the TODO list:
* Fix to_date()-related functions to consistently issue errors
http://archives.postgresql.org/pgsql-hackers/2007-02/msg00915.php
---
Brendan Jurd wrote:
> On 2/17/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> > Bruce Momjian escribi?:
> >
> > > Maybe now would be an appropriate time to discuss the open questions in
> > > the submitting email:
> >
> > > > Brendan Jurd wrote:
> > > > > I'd also like to raise the topic of how conversion from text to ISO
> > > > > week dates should be handled, where the user has specified a bogus
> > > > > mixture of fields. Existing code basically ignores these issues; for
> > > > > example, if a user were to call to_date('1998-01-01 2454050',
> > > > > '-MM-DD J') the function returns 2006-01-01, a result of setting
> > > > > the year field from , then overwriting year, month and day with
> > > > > the values from the Julian date in J, then setting the month and day
> > > > > normally from MM and DD.
> > > > >
> > > > > 2006-01-01 is not a valid representation of either of the values the
> > > > > user specified. Now you might say "ask a silly question, get a silly
> > > > > answer"; the user shouldn't send nonsense arguments to to_date and
> > > > > expect a sensible result. But perhaps the right way to respond to a
> > > > > broken timestamp definition is to throw an error, rather than behave
> > > > > as though everything has gone to plan, and return something which is
> > > > > not correct.
> > > > >
> > > > > The same situation can arise if the user mixes ISO and Gregorian data;
> > > > > how should Postgres deal with something like to_date('2006-250',
> > > > > 'IYYY-DDD')? The current behaviour in my patch is actually to assume
> > > > > that the user meant to say 'IYYY-IDDD', since "the 250th Gregorian day
> > > > > of the ISO year 2006" is total gibberish. But perhaps it should be
> > > > > throwing an error message.
> >
> > My thinking is that erroneous patterns should throw an error, and not
> > try to second-guess the user. (IIRC this was being discussed in some
> > other thread not long ago).
>
> It seems to me there are basically two different responses to the
> problem of invalid patterns. One is to reject all patterns which
> potentially under- or over-constrain the date value, and the other is
> to only reject those patterns which, when applied to the given date
> string, actually cause a conflict.
>
> For example, on the surface the pattern '-MM-DD J' would appear to
> be invalid, because it specifies the date using both the Gregorian and
> Julian conventions. You could argue that the whole idea of using a
> pattern like this is bogus, and reject the pattern as soon as it is
> parsed.
>
> On the other hand, if a user called to_date('2007-02-17 2454149',
> '-MM-DD J'), and you attempted to resolve the pattern you would
> find that the Julian date and the Gregorian date agree perfectly with
> each other, and there is no reason to reject the conversion.
>
> My gut reaction at first was to go with the former approach. It's
> programmatically more simple, and it's easier to explain in
> documentation/error messages. But then it occurred to me that one of
> the use cases for to_date is slurping date information out of textual
> reports which may contain redundant date information. If a user
> wanted to parse something like "2007-02-17 Q1", he would probably try
> '-MM-DD "Q"Q', even though this pattern is logically
> over-constraining. Would it be fair to throw an error in such a case?
>
> Please let me know what you think.
>
> BJ
--
Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Is this portable?
Gregory Stark wrote: > "Tom Lane" <[EMAIL PROTECTED]> writes: > > > Zdenek Kotala <[EMAIL PROTECTED]> writes: > >> Alvaro Herrera wrote: > >>> Can I declare a struct in a function's declaration section? > > > >> It works fine with Sun Studio 11. > > > > AFAICT it's required by the original K&R C book. > > IIRC there's something odd about the scope of the declared struct label. > > Something like it previously extended to the end of the file but post-ANSI was > limited to the scope it's declared in (including very limited scopes where it > would be useless such as in function parameters). Hmm, thanks everybody. I was just going to say "bummer!" because I needed to build a qsort comparator for these, but then I realized that it's better if I keep worker and launcher database structs separate -- the only field they use in common is the Oid anyway. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Questions about pid file creation code
Zdenek Kotala wrote: > (PS: Is standalone backend same as --single switch?) Yes. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Full page writes improvement, code update
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Koichi Suzuki wrote: > Hi, > > Here's a patch reflected some of Simon's comments. > > 1) Removed an elog call in a critical section. > > 2) Changed the name of the commands, pg_complesslog and pg_decompresslog. > > 3) Changed diff option to make a patch. > > -- > Koichi Suzuki [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pgsql: Fix for plpython functions; return true/false for boolean,
Added to TODO: o Allow PL/Python to return boolean rather than 1/0 http://archives.postgresql.org/pgsql-patches/2007-01/msg00596$ --- Guido Goldstein wrote: > Peter Eisentraut wrote: > > Guido Goldstein wrote: > >> Is it possible to tell me which python versions you want to > >> support? > > > > The issue isn't so much which versions we want to support. There is > > certainly some flexibility with that. But when a patch breaks the > > buildfarm a) unannounced and b) without any apparent feature gain, then > > people get annoyed. > > If this breaks the buildfarm it's not my failure. > Except you can tell me what I've got to do with the > buildfarm. > > If you mean that plpython didn't compile, fine; simply tell > the people what version they should consider when sending > in patches. > > I've checked the patch with postgres 8.1.3 and 8.2.1 > with python 2.4 and 2.5 on intel 32 bit and amd 64 bit > systems; all systems running linux. > > *And* it's not a feature patch but a bug-fixing one! > Python is a language with strong typing, so silently > converting a datatype is a bug -- not a feature. > Btw, you'll lose the type information of boolean columns in > trigger functions (NEW and OLD dicts, no explicit parameters), > which does cause problems. > > > That said, we certainly try to support a few more versions of Python > [...] > > If you want to support python 2.3 use the attached patch, which also > works for the newer python versions. > The Python 2.3 branch is the oldest _officially_ supported python version. > > Anyway, to circumvent the above mentiond point a) I herewith anncounce > that the included patch might break the buildfarm. > > Cheers >Guido > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Questions about pid file creation code
Tom Lane wrote: Zdenek Kotala <[EMAIL PROTECTED]> writes: 1) Is there still some reason have negative value in postmaster.pid? Just to distinguish postmasters from standalone backends in the error messages. I think that's still useful. I'm not sure what you mean. It is used only in CreatePidFile function and I think that if directory is locked by some process, I don't see any useful reason to know if it is postmaster or standalone backend. (PS: Is standalone backend same as --single switch?) 2) Why 100? What race condition should happen? This piece of code looks like kind of magic. There are at least two race cases identified in the comments in the loop. Yes there are. But it does not sense for me. If I want to open file and another process remove it, why I want to try created it again when another process going to do it? There is only one reason and it is that user delete file manually from the system, but in this case I don't believe that administrator shot right time. Or if it still have sense do it in this way I expect some sleep instead of some loop which depends on CPU speed. 3) Why pid checking and cleanup is in postgres? I think it is role of pg_ctl or init scripts. Let's see, instead of one place in the postgres code we should do it in N places in different init scripts, and just trust to luck that a particular installation is using an init script that knows to do that? I don't think so. Besides, how is the init script going to remove it again? It won't still be running when the postmaster exits. I'm sorry, I meant why there is a pid cleanup which stays there after another postmaster crash. Many application only check OK there is some pid file -> exit. And rest is on start script or some other monitoring facility. 4) The following condition is buggy, because atoi function does not have defined result if parameter is not valid number. if (other_pid <= 0) It's not actually trying to validate the syntax of the lock file, only to make certain it doesn't trigger any unexpected behavior in kill(). I not sure if we talk about same place. kill() is called after this if. If I miss that atoi need not return 0 if fails, then following condition is more accurate: if (other_pid == 0) I don't think I've yet seen any reports that suggest that more syntax checking of the lock file would be a useful activity. Yes, I agree. Zdenek ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Use non-deprecated APIs for dynloader/darwin.c
Actually, can we now say we only support OS/X 10.3 and later. If so, we can use the patch unchanged. --- Bruce Momjian wrote: > > Ah, I already had this on the TODO list with URLs, so I will not put it > in the hold queue. > > --- > > Chris Campbell wrote: > > On Oct 8, 2006, at 14:29, Tom Lane wrote: > > > > > Looks good, but I don't think we want to abandon OSX 10.2 support > > > just yet. I'll revise this to use a configure probe for dlopen. > > > > Maybe we can abandon Mac OS X 10.2 in 8.3 and later? And not back- > > port these patches to the 7.x, 8.0, and 8.1 branches? > > > > BTW, I think the configure probe (only on Darwin, correct?) should > > test for the existence of . > > > > > My inclination is to apply this one now, since it only affects OSX > > > and should be easily testable, but to hold off on your other patch > > > for portable Bonjour support until 8.3 devel starts. The portability > > > implications of that one are unclear, and I don't know how to test it > > > either, so I think putting it in now is too much risk. > > > > The Bonjour patch wasn't intended to be portable to other platforms > > just yet. As submitted, it has the same risks/advantages as this > > dlopen() patch -- it only works on 10.3 and later, but isn't > > deprecated in 10.4. > > > > If we want to keep 10.2 support for Bonjour, we can test for both > > DNSServiceDiscovery.h and dns_sd.h in ./configure, and prefer > > dns_sd.h if it's found (which will be the case for 10.3 and 10.4) but > > use DNSServiceDiscovery.h if not (which will be the case for 10.2). > > > > Thanks! > > > > - Chris > > > > > > ---(end of broadcast)--- > > TIP 5: don't forget to increase your free space map settings > > -- > Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us > EnterpriseDB http://www.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Use non-deprecated APIs for dynloader/darwin.c
Ah, I already had this on the TODO list with URLs, so I will not put it in the hold queue. --- Chris Campbell wrote: > On Oct 8, 2006, at 14:29, Tom Lane wrote: > > > Looks good, but I don't think we want to abandon OSX 10.2 support > > just yet. I'll revise this to use a configure probe for dlopen. > > Maybe we can abandon Mac OS X 10.2 in 8.3 and later? And not back- > port these patches to the 7.x, 8.0, and 8.1 branches? > > BTW, I think the configure probe (only on Darwin, correct?) should > test for the existence of . > > > My inclination is to apply this one now, since it only affects OSX > > and should be easily testable, but to hold off on your other patch > > for portable Bonjour support until 8.3 devel starts. The portability > > implications of that one are unclear, and I don't know how to test it > > either, so I think putting it in now is too much risk. > > The Bonjour patch wasn't intended to be portable to other platforms > just yet. As submitted, it has the same risks/advantages as this > dlopen() patch -- it only works on 10.3 and later, but isn't > deprecated in 10.4. > > If we want to keep 10.2 support for Bonjour, we can test for both > DNSServiceDiscovery.h and dns_sd.h in ./configure, and prefer > dns_sd.h if it's found (which will be the case for 10.3 and 10.4) but > use DNSServiceDiscovery.h if not (which will be the case for 10.2). > > Thanks! > > - Chris > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] CheckpointStartLock starvation
Tom Lane wrote: Heikki Linnakangas <[EMAIL PROTECTED]> writes: As a proposed fix, instead of acquiring the CheckpointStartLock in RecordTransactionCommit, we set a flag in MyProc saying "commit in progress". Checkpoint will scan through the procarray and make note of any commit in progress transactions, after computing the new redo record ptr, and wait for all of them to finish before flushing clog. What sort of "wait for finish" mechanism do you have in mind? While I've always thought CheckpointStartLock is a pretty ugly solution, I'm not sure the above is better. I was thinking of XactLockTableWait. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Is this portable?
"Tom Lane" <[EMAIL PROTECTED]> writes: > Zdenek Kotala <[EMAIL PROTECTED]> writes: >> Alvaro Herrera wrote: >>> Can I declare a struct in a function's declaration section? > >> It works fine with Sun Studio 11. > > AFAICT it's required by the original K&R C book. IIRC there's something odd about the scope of the declared struct label. Something like it previously extended to the end of the file but post-ANSI was limited to the scope it's declared in (including very limited scopes where it would be useless such as in function parameters). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Is this portable?
Zdenek Kotala <[EMAIL PROTECTED]> writes: > Alvaro Herrera wrote: >> Can I declare a struct in a function's declaration section? > It works fine with Sun Studio 11. AFAICT it's required by the original K&R C book. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] Bonjour patch
With no new version from the author and no working version for all supported OS/X version, I am saving this patch for 8.4. This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Chris Campbell wrote: > On Oct 8, 2006, at 14:29, Tom Lane wrote: > > > Looks good, but I don't think we want to abandon OSX 10.2 support > > just yet. I'll revise this to use a configure probe for dlopen. > > Maybe we can abandon Mac OS X 10.2 in 8.3 and later? And not back- > port these patches to the 7.x, 8.0, and 8.1 branches? > > BTW, I think the configure probe (only on Darwin, correct?) should > test for the existence of . > > > My inclination is to apply this one now, since it only affects OSX > > and should be easily testable, but to hold off on your other patch > > for portable Bonjour support until 8.3 devel starts. The portability > > implications of that one are unclear, and I don't know how to test it > > either, so I think putting it in now is too much risk. > > The Bonjour patch wasn't intended to be portable to other platforms > just yet. As submitted, it has the same risks/advantages as this > dlopen() patch -- it only works on 10.3 and later, but isn't > deprecated in 10.4. > > If we want to keep 10.2 support for Bonjour, we can test for both > DNSServiceDiscovery.h and dns_sd.h in ./configure, and prefer > dns_sd.h if it's found (which will be the case for 10.3 and 10.4) but > use DNSServiceDiscovery.h if not (which will be the case for 10.2). > > Thanks! > > - Chris > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Questions about pid file creation code
Zdenek Kotala <[EMAIL PROTECTED]> writes: > 1) Is there still some reason have negative value in postmaster.pid? Just to distinguish postmasters from standalone backends in the error messages. I think that's still useful. > 2) Why 100? What race condition should happen? This piece of code looks > like kind of magic. There are at least two race cases identified in the comments in the loop. > 3) Why pid checking and cleanup is in postgres? I think it is role of > pg_ctl or init scripts. Let's see, instead of one place in the postgres code we should do it in N places in different init scripts, and just trust to luck that a particular installation is using an init script that knows to do that? I don't think so. Besides, how is the init script going to remove it again? It won't still be running when the postmaster exits. > 4) The following condition is buggy, because atoi function does not have > defined result if parameter is not valid number. > if (other_pid <= 0) It's not actually trying to validate the syntax of the lock file, only to make certain it doesn't trigger any unexpected behavior in kill(). I don't think I've yet seen any reports that suggest that more syntax checking of the lock file would be a useful activity. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Arrays of Complex Types
On Fri, Mar 30, 2007 at 05:08:42PM -0400, Tom Lane wrote:
> David Fetter <[EMAIL PROTECTED]> writes:
> > After several rounds of patches, it appears that it might be easier to
> > create a new typtype entry, which I'll tentatively call 'a' because it
> > seems a little fragile and a lot inelegant and hard to maintain to
> > have typtype='c' and typrelid=InvalidOid mean, "this is an array of
> > complex types."
>
> Uh, wouldn't it be typtype = 'c' and typelem != 0 ?
Right. The attached patch passes the current regression tests and at
least to a "smoke test" level does what it's supposed to do. I'd
really like to help refactor the whole array system to use 'a', tho.
Cheers,
D
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
Index: src/backend/catalog/heap.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/heap.c,v
retrieving revision 1.318
diff -c -r1.318 heap.c
*** src/backend/catalog/heap.c 2 Apr 2007 03:49:37 - 1.318
--- src/backend/catalog/heap.c 2 Apr 2007 20:09:16 -
***
*** 45,50
--- 45,51
#include "catalog/pg_statistic.h"
#include "catalog/pg_type.h"
#include "commands/tablecmds.h"
+ #include "commands/typecmds.h"
#include "miscadmin.h"
#include "optimizer/clauses.h"
#include "optimizer/var.h"
***
*** 763,768
--- 764,770
Relationpg_class_desc;
Relationnew_rel_desc;
Oid new_type_oid;
+ char *relarrayname;
pg_class_desc = heap_open(RelationRelationId, RowExclusiveLock);
***
*** 815,820
--- 817,856
relnamespace,
relid,
relkind);
+ /*
+* Add in the corresponding array types if appropriate.
+*/
+ if (relkind == RELKIND_RELATION ||
+ relkind == RELKIND_VIEW ||
+ relkind == RELKIND_COMPOSITE_TYPE)
+ {
+ relarrayname = makeArrayTypeName(relname);
+ TypeCreate(relarrayname,/* Array type name */
+ relnamespace,/* Same
namespace as parent */
+ InvalidOid, /* relation's
type oid, set here to InvalidOid to make dependency work right */
+ 0, /*
relkind, also N/A here */
+ -1, /*
Internal size, unlimited */
+ 'c', /* It's
a complex type */
+ DEFAULT_TYPDELIM,/* Use the default */
+ F_ARRAY_IN, /* Macro for
array input procedure */
+ F_ARRAY_OUT, /* Macro for
array output procedure */
+ F_ARRAY_RECV,/* Macro for
array receive (binary input) procedure */
+ F_ARRAY_SEND,/* Macro for
array send (binary output) procedure */
+ InvalidOid, /* No input
typmod */
+ InvalidOid, /* No output
typmod */
+ InvalidOid, /* Default
ANALYZE procedure */
+ new_type_oid,/* The OID just
created */
+ InvalidOid, /* No base
type--this isn't a DOMAIN */
+ NULL,/* No
default type value */
+ NULL,/*
Don't send binary */
+ false, /*
Never passed by value */
+ 'd', /* Type
alignment. Should this be something else? */
+ 'x', /*
Always TOASTable */
+ -1, /* No
typMod for regular composite types. */
+ 0, /*
Array diminsions of typbasetype */
+ false); /* Type
NOT NULL */
+ pfree(relarrayname);/* Seems like the right thing to do
here. */
+ }
/*
Re: [HACKERS] So are we calling it: Feature Freeze?
Joshua D. Drake wrote: > Hello, > > Should we announce? There is some web work etc.. to be done. Sure. I don't remember us doing anything special to annouce feature freeze, but if there is something, please go ahead. FYI, I am ready to move uncompleted patches into the 8.4 hold queue when we are ready. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Is this portable?
Alvaro Herrera wrote:
Can I declare a struct in a function's declaration section? Something
like this:
static void
foobar(void)
{
struct foo {
Oid foo;
int bar;
};
struct foo baz;
baz.foo = InvalidOid;
baz.bar = 42;
}
I tried here and GCC does not complain, with -std=c89 -pedantic.
It works fine with Sun Studio 11.
Zdenek
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] CheckpointStartLock starvation
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > As a proposed fix, instead of acquiring the CheckpointStartLock in > RecordTransactionCommit, we set a flag in MyProc saying "commit in > progress". Checkpoint will scan through the procarray and make note of > any commit in progress transactions, after computing the new redo record > ptr, and wait for all of them to finish before flushing clog. What sort of "wait for finish" mechanism do you have in mind? While I've always thought CheckpointStartLock is a pretty ugly solution, I'm not sure the above is better. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Is this portable?
Alvaro Herrera wrote:
> Can I declare a struct in a function's declaration section? Something
> like this:
>
> static void
> foobar(void)
> {
> struct foo {
> Oid foo;
> int bar;
> };
>
> struct foo baz;
>
> baz.foo = InvalidOid;
> baz.bar = 42;
>
> }
>
> I tried here and GCC does not complain, with -std=c89 -pedantic.
Sure.
--
Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[HACKERS] Is this portable?
Can I declare a struct in a function's declaration section? Something
like this:
static void
foobar(void)
{
struct foo {
Oid foo;
int bar;
};
struct foo baz;
baz.foo = InvalidOid;
baz.bar = 42;
}
I tried here and GCC does not complain, with -std=c89 -pedantic.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] Proposal: Adding JIS X 0213 support
Tatsuo Ishii wrote: Related to this, when are we going to get the Japanese po files in the core distribution? No idea. In my understanding, current message translating system has serious problem if wrong locale and encoding is provided(has this issue been solved in 8.3?). That's certainly true, and it's not solved. But how does keeping the Japanese po files out of the distribution improve the matter? Keeping out po files until the problem is solved is just my opinion. Regrettably I am also the same opinion. It is the cause of an unnecessary trouble to include japanese po file without a certain betterment. -- Hiroki Kataoka <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] CheckpointStartLock starvation
I'm seeing a problem on my benchmark machine: checkpoints stop happening after the ramp-up period. It looks like the bgwriter gets starved waiting on the CheckpointStartLock. The CheckpointStartLock is held in shared mode over an XLogFlush when committing, which on an extremely busy system like a benchmark is always long enough to have a new transaction to acquire the CheckpointStartLock again. I'm running another test with more logging to confirm that's what's happening, but I'm pretty sure that's it... As a proposed fix, instead of acquiring the CheckpointStartLock in RecordTransactionCommit, we set a flag in MyProc saying "commit in progress". Checkpoint will scan through the procarray and make note of any commit in progress transactions, after computing the new redo record ptr, and wait for all of them to finish before flushing clog. Unless someone has a better idea, I'll write a patch to do the above. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Questions about pid file creation code
I'm looking on pid file creation code (src/backend/utils/init/miscinit.c
- CreateLockFile) and I have couple of questions:
1) Is there still some reason have negative value in postmaster.pid? It
happens only if backend runs in single mode. But I think now is not
necessary to use it. And there are some confusing messages about
postgres/postmaster. See:
errhint("Is another postgres (PID %d) running in data directory \"%s\"?",
(int) other_pid, refName) :
errhint("Is another postmaster (PID %d) running in data directory \"%s\"?",
(int) other_pid, refName)) :
2) Why 100? What race condition should happen? This piece of code looks
like kind of magic.
3) Why pid checking and cleanup is in postgres? I think it is role of
pg_ctl or init scripts.
4) The following condition is buggy, because atoi function does not have
defined result if parameter is not valid number. (OK in most
implementation it really returns 0)
if (other_pid <= 0)
elog(FATAL, "bogus data in lock file \"%s\": \"%s\"",
filename, buffer)
I think usage of strtol there should be better.
Zdenek
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] One-time plans
On Mon, 2007-04-02 at 12:20 -0400, Tom Lane wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > ISTM we've just invented the concept of one-time plans to allow CREATE > > INDEX to work effectively with HOT. > > > I'd like to extend that thought back over towards constraint exclusion. > > Currently we don't allow STABLE functions to be used for constraint > > exclusion because that mean plans were valid only if they are > > immediately executed. > > > It seems like a very small act to force the plan to be one-time only > > when we have successfully used a STABLE function to exclude a table. > > No. STABLE functions are not stable enough for that --- you'd have to > assume they are unchanging across the whole transaction. Yep. I was mainly thinking about single statement transactions, which are the norm for decision support. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Add GUC temp_tablespaces toprovide a default location for
Right, no updated patch submitted. --- Simon Riggs wrote: > On Sun, 2007-03-18 at 14:05 -0500, Jaime Casanova wrote: > > On 3/17/07, Tom Lane <[EMAIL PROTECTED]> wrote: > > > "Jaime Casanova" <[EMAIL PROTECTED]> writes: > > > > On 3/5/07, Tom Lane <[EMAIL PROTECTED]> wrote: > > > >> In the second place, it's a serious violation of what little modularity > > > >> and layering we have for fd.c to be calling into commands/tablespace.c. > > > >> This is not merely cosmetic but has real consequences: one being that > > > >> it's now unsafe to call OpenTemporaryFile outside a transaction. > > > > > > > ok, you are right... what do you suggest? > > > > maybe move the GetTempTablespace function to somewhere in > > > > src/backend/utils? > > > > > > You missed the point entirely. Relocating the code to some other file > > > wouldn't change the objection: the problem is that fd.c mustn't invoke > > > any transactional facilities such as catalog lookups. It's too low > > > level for that. > > > > > > > oh, i see... > > > > > You could perhaps do it the other way around: some transactional > > > code (eg the assign hook for a GUC variable) tells fd.c to save > > > some private state controlling future temp file creations. > > > > > > > the problem with the assign hook function is that it can't read > > catalogs too if we are in a non-interactive command... > > > > so, we need a list with the oids of the tablespaces, we can initialize > > this list the first time we need a temp file (i haven't seen exactly > > where we can do this, yet), and if we set the GUC via a SET command > > then we can let the assign hook do the job. > > > > > BTW, if we are now thinking of temp files as being directed to > > > particular tablespaces, is there any reason still to have per-database > > > subdirectories for them? It might simplify life if there were just > > > one default temp directory, $PGDATA/base/pgsql_tmp/, plus one per > > > configured temp tablespace, $PGDATA/pg_tblspc//pgsql_tmp/. > > > > > > > what the directory shoud be, i understand ypur idea as just > > $PGDATA/pg_tblspc/pgsql_tmp/... > > Am I right in thinking we didn't get an updated patch in yet? > > Any help needed here? > > This seems a very important patch for manageability and it would be a > shame to miss out on it for 8.3 since its been a TODO item for so long. > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com > -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [COMMITTERS] pgsql: Add GUC temp_tablespaces toprovide a default location for
On Sun, 2007-03-18 at 14:05 -0500, Jaime Casanova wrote: > On 3/17/07, Tom Lane <[EMAIL PROTECTED]> wrote: > > "Jaime Casanova" <[EMAIL PROTECTED]> writes: > > > On 3/5/07, Tom Lane <[EMAIL PROTECTED]> wrote: > > >> In the second place, it's a serious violation of what little modularity > > >> and layering we have for fd.c to be calling into commands/tablespace.c. > > >> This is not merely cosmetic but has real consequences: one being that > > >> it's now unsafe to call OpenTemporaryFile outside a transaction. > > > > > ok, you are right... what do you suggest? > > > maybe move the GetTempTablespace function to somewhere in > > > src/backend/utils? > > > > You missed the point entirely. Relocating the code to some other file > > wouldn't change the objection: the problem is that fd.c mustn't invoke > > any transactional facilities such as catalog lookups. It's too low > > level for that. > > > > oh, i see... > > > You could perhaps do it the other way around: some transactional > > code (eg the assign hook for a GUC variable) tells fd.c to save > > some private state controlling future temp file creations. > > > > the problem with the assign hook function is that it can't read > catalogs too if we are in a non-interactive command... > > so, we need a list with the oids of the tablespaces, we can initialize > this list the first time we need a temp file (i haven't seen exactly > where we can do this, yet), and if we set the GUC via a SET command > then we can let the assign hook do the job. > > > BTW, if we are now thinking of temp files as being directed to > > particular tablespaces, is there any reason still to have per-database > > subdirectories for them? It might simplify life if there were just > > one default temp directory, $PGDATA/base/pgsql_tmp/, plus one per > > configured temp tablespace, $PGDATA/pg_tblspc//pgsql_tmp/. > > > > what the directory shoud be, i understand ypur idea as just > $PGDATA/pg_tblspc/pgsql_tmp/... Am I right in thinking we didn't get an updated patch in yet? Any help needed here? This seems a very important patch for manageability and it would be a shame to miss out on it for 8.3 since its been a TODO item for so long. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Last minute mini-proposal (I know, Iknow)forPQexecf()
Added to TODO: o Add PQexecf() that allows complex parameter substitution http://archives.postgresql.org/pgsql-hackers/2007-03/msg01803.php --- [EMAIL PROTECTED] wrote: > > That's exactly the approach I don't want to take. To implement our > > quoting-escape additions, we'll have to stop relying on sprintf and > > implement for ourselves whatever "standard C" escapes we want to > > support. > > > Ok - then it seems like it might make sense to implement PQexecf() in > terms of src/port/snprintf.c (and enhance that family of functions to > support the quoting conversion specifiers that we want). > > Let's just pick up this discussion in the next release cycle. > > Bruce - can you add a TODO for this topic? Thanks. > > > -- Korry > > > -- > Korry Douglas[EMAIL PROTECTED] > EnterpriseDB http://www.enterprisedb.com -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Oracle indemnifies PostgreSQL on its patents
All, > You can be as selective as you want about enforcing patents --- > copyright/trademark enforcement does require consistent enforcement. I'm not sure that's the case, actually. Of course, I'm not an attorney ... but then, neither are you. What is it about -hackers that people love to speculate about code they don't understand (law)? -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Oracle indemnifies PostgreSQL on its patents
Jeroen T. Vermeulen wrote: > On Sun, April 1, 2007 01:32, Tom Lane wrote: > > > The idea of OIN is to have a large patent pool that can be > > counter-asserted against anyone who doesn't want to play nice. > > Mutual assured destruction in the patent sphere, if you will. > > And from the participants' point of view, I suppose the big attraction > must be that they do away with a threat to their patents. If you have a > patent that matches what some open project (not worth suing) has been > doing for the past few years, then anyone else you might want to sue about > the patent could point to that project and say "if you have a valid > patent, why didn't you say something when they infringed it?" You can be as selective as you want about enforcing patents --- copyright/trademark enforcement does require consistent enforcement. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Implicit casts to text
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Am Montag, 2. April 2007 09:17 schrieb Tom Lane: >> The scheme that was in the back of my mind was to do this at the same >> time as providing a general facility for casting *every* type to and >> from text, by means of their I/O functions if no specialized cast is >> provided in pg_cast. > That's the first time I hear of such a scheme. It's been discussed before, eg http://archives.postgresql.org/pgsql-admin/2004-06/msg00390.php http://archives.postgresql.org/pgsql-hackers/2004-10/msg00303.php > Anyway, the point of this > exercise is to reduce misbehavior by explicit casting. I don't see how > implicitly adding more casting paths helps that or is even related to that. > Even if we had the automatic cast facility that you describe, and I find it > highly suspicious, such casts could at most be of the explicit category, so > how would that help users who currently rely on the implicit ones? Certainly they'd all be explicit-only. From a technical perspective there's no need to do the two things at the same time; I'm just opining that we could sell it easier if we did them together. If we just do this part, what users will see is that we broke their queries for what to them will appear to be no particular gain. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] So are we calling it: Feature Freeze?
Hello, Should we announce? There is some web work etc.. to be done. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] One-time plans
"Simon Riggs" <[EMAIL PROTECTED]> writes: > ISTM we've just invented the concept of one-time plans to allow CREATE > INDEX to work effectively with HOT. > I'd like to extend that thought back over towards constraint exclusion. > Currently we don't allow STABLE functions to be used for constraint > exclusion because that mean plans were valid only if they are > immediately executed. > It seems like a very small act to force the plan to be one-time only > when we have successfully used a STABLE function to exclude a table. No. STABLE functions are not stable enough for that --- you'd have to assume they are unchanging across the whole transaction. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Implicit casts to text
Am Montag, 2. April 2007 09:17 schrieb Tom Lane: > The scheme that was in the back of my mind was to do this at the same > time as providing a general facility for casting *every* type to and > from text, by means of their I/O functions if no specialized cast is > provided in pg_cast. This would improve functionality, thus providing > a salve to the annoyance of users whose code the restriction breaks: > we can certainly argue that it wouldn't do for all those automatically > created casts to be implicit. At the same time it'd let us eliminate > redundant text-to/from-foo code that's currently in place for some but > not all datatypes. That's the first time I hear of such a scheme. Anyway, the point of this exercise is to reduce misbehavior by explicit casting. I don't see how implicitly adding more casting paths helps that or is even related to that. Even if we had the automatic cast facility that you describe, and I find it highly suspicious, such casts could at most be of the explicit category, so how would that help users who currently rely on the implicit ones? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Statistics on views (execute a plan from within analyze)
On Fri, Mar 30, 2007 at 12:01:33PM -0400, Oscar Täckström wrote: > I am working on a course project on implementing collection of statistics > on views in pgsql. The statistics will be used in conjunction with view > matching in the optimizer, to improve selectivity estimates. Interesting idea... > For this to be possible, I need to be able to execute view definitions > from within the analyze code (so I can then sample the result set and > collect the statistics on the sampled tuples). Is there a preferred way > to do this? A simple solution is to use the SPI, but is this really > recommended for use from within this code? I don't want to materialize > the result set, but calculate the statistics directly in memory and just > throw away the tuples that's not part of the sample. I beleive you can use SPI to only retreive tuples as they are calculated, i.e. it doesn't materialise the dataset. However, if this is a concern, you could always use SPI to create a cursor to iterate over your set, That will give complete control... Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[HACKERS] One-time plans
ISTM we've just invented the concept of one-time plans to allow CREATE INDEX to work effectively with HOT. I'd like to extend that thought back over towards constraint exclusion. Currently we don't allow STABLE functions to be used for constraint exclusion because that mean plans were valid only if they are immediately executed. It seems like a very small act to force the plan to be one-time only when we have successfully used a STABLE function to exclude a table. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] Statistics on views (execute a plan from within analyze)
Hi, I am working on a course project on implementing collection of statistics on views in pgsql. The statistics will be used in conjunction with view matching in the optimizer, to improve selectivity estimates. For this to be possible, I need to be able to execute view definitions from within the analyze code (so I can then sample the result set and collect the statistics on the sampled tuples). Is there a preferred way to do this? A simple solution is to use the SPI, but is this really recommended for use from within this code? I don't want to materialize the result set, but calculate the statistics directly in memory and just throw away the tuples that's not part of the sample. I would be very grateful for any hints about this? Best Oscar Täckström PhD Student University of Waterloo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] HOT WIP Patch - version 6.3
Please see the HOT version 6.3 patch posted on pgsql-patches. I've implemented support for CREATE INDEX and CREATE INDEX CONCURRENTLY based on the recent discussions. The implementation is not yet complete and needs some more testing/work/discussion before we can start considering it for review. One of the regression test case fails because CIC now works in three phases. In the first phase, we just create the catalog entry for the index and commit the transaction. If the index_build fails because of any error (say, unique key constraint) the index creation fails, but the catalog entry remains. CREATE INDEX: - The implementation is based on having an extra attribute in pg_index to track the transaction xid which created the index and then use that information to decide whether the newly created index should be used in a query or not. Here are couple of TODO items: Plan Invalidation: We decided to store transaction id of the top level transaction in the cached plan if one or more potentially useful indexes are not available while planning a query. And then replan if the current transaction id is different that the one stored with the plan. I'm not very well familiar with this code, so any suggestions how to do it in a clean way ? Making index available in the creating transaction: This is an important TODO item. We would like to make the index immediately available to the transaction which created it, if the transaction is running in read-committed mode. If the transaction is running in SERIALIZABLE mode, then we can't do much because we might have skipped one or more RECENTLY_DEAD tuples while building the index and hence index can not be used. The way we build index now is that we only index the tuple at the head of the HOT-chain. So there could be DELETE_IN_PROGRESS tuples (updated/deleted by the transaction which is creating the index) which we skipped while building the index. My question is, is there a case where this transaction may use the new index and still see those tuples ? I know that the DELETE_IN_PROGRESS tuples are visible if there are any open cursors. But then plans for these open cursors can not be changed until they are closed and reopened, isn't it ? Tom mentioned about recursive plpgsql functions where the outer instance can use an older snapshot. I tried that but could not produce a scenario where the outer instance could see the DELETE_IN_PROGRESS tuple if the tuple is updated in the inner instance. Can someone help me with an example where a read-committed transaction would use the newly created index and still see the DELETE_IN_PROGRESS tuple ? CREATE INDEX CONCURRENTLY: -- One of the item which needs review and discussion is the handling on unique key checks while creating the index concurrently. We build the index in three phases. In the first phase, we just create the catalog entry and mark index invalid for inserts. This ensures that transactions started after that won't create HOT-chains that break the HOT property for the new index. In the second phase, we build the index by applying the reference snapshot to the heap tuples. In the third phase, we validate the index and insert any missing entries. In this phase, we only insert if index entry for the root tuple is missing. So there is just one insert operation which covers all the tuples in the HOT-chain. In order to check unique key violations, inside _bt_check_unique() function when a duplicate key is found, we follow the entire HOT-chain and check if any tuple in the chain is live. If so, unique key violation constraint is raised. IOW if any two HOT-chains share the same key and have one live tuple, unique key constraint is considered violated. Can anyone spot a hole in this logic ? Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Calling void functions
I'm informed that the last statement of a function that returns void cannot be a SELECT. How else is one supposed to call another function which also returns void? E.g., CREATE FUNCTION foo (a int, b int) RETURNS void LANGUAGE plpgsql AS $$ do important things $$; CREATE FUNCTION foo (a int) RETURNS void LANGUAGE sql AS $$ SELECT foo($1, default-value); $$; Hello Peter it's problem. You cannot do it now. One year ago I sent patch http://archives.postgresql.org/pgsql-patches/2006-03/msg00196.php Regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Last minute mini-proposal (I know, I know)forPQexecf()
On Sat, Mar 31, 2007 at 07:16:19PM -0400, Bruce Momjian wrote: > Andrew Dunstan wrote: > > [EMAIL PROTECTED] wrote: > > >> It's important to get the *right* interface into the first release > > >> that has it. > > >> > > > > > > Agreed, that's why I proposed the right interface to begin with :-) > > > > > > > Maybe the first thing we might usefully do would be to document > > PQExpBuffer. And you can send in a patch for that for 8.3 :-) > > The big question is whether these functions are for external use. We > do export them using libpq/exports.txt, but I assume it was only for > psql use and not for general usage. There was discussion about this before, and the conclusion then was that they're not a part of the public interface, and only intended to be used by "our own" frontends. Doesn't mean we can't put it out there if we think it's a good interface for people to use though ;-) //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Implicit casts to text
Peter Eisentraut <[EMAIL PROTECTED]> writes: > The attached patch changes all implicit casts to text to assignment and > cleans up the associated regression test damage. This change has been > discussed for the longest time; I propose that we bite the bullet and > do it now. [ I'm assuming this isn't an April-fool item, otherwise never mind ] The scheme that was in the back of my mind was to do this at the same time as providing a general facility for casting *every* type to and from text, by means of their I/O functions if no specialized cast is provided in pg_cast. This would improve functionality, thus providing a salve to the annoyance of users whose code the restriction breaks: we can certainly argue that it wouldn't do for all those automatically created casts to be implicit. At the same time it'd let us eliminate redundant text-to/from-foo code that's currently in place for some but not all datatypes. If we do only the restrictive part of this, it's a harder sale. So, +1 on the concept, but I think we want a larger patch, and it's probably too late for that for 8.3. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
