Re: [HACKERS] Shouldn't psql -1 imply ON_ERROR_STOP?
Am 25.07.2009 um 15:00 schrieb Peter Eisentraut: When you run a file with psql -1/--single-transaction, and a command fails, you get bombarded with ERROR: current transaction is aborted, commands ignored until end of transaction block for the rest of the file. Shouldn't -1 imply ON_ERROR_STOP or some variant by default? Sounds reasonable, +1 from me. Regards Michael Paesold -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Am 19.08.2008 um 20:47 schrieb Tom Lane: Bruce Momjian [EMAIL PROTECTED] writes: Joshua Drake wrote: Is our backpatch policy documented? It does not appear to be in developer FAQ. Seems we need to add it. I'm not sure that I *want* a formal written-down backpatch policy. Whether (and how far) to backpatch has always been a best-judgment call in the past, and we've gotten along fine with that. I think having a formal policy is just likely to lead to even more complaints: either patching or not patching could result in second-guessing by someone who feels he can construe the policy to match the result he prefers. Agreeing to you and some later posters in this thread, I would not vote for a formal policy either. But IMHO there should be a general, informal note about backpatching in developer docs/faqs. A place where you can point to, and a chance for new people to read about the postgres way of handling backpatching. Btw., how backpatching is handled here is one of the reasons I trust my data to postgres. Best Regards Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
Peter Eisentraut wrote: On Tuesday 19 August 2008 19:12:16 Tom Lane wrote: Well, why not just make a one-eighty and say that the default postgresql.conf is *empty* (except for whatever initdb puts into it)? Well, my original implementation of GUC had an empty default configuration file, which was later craptaculated to its current form based on seemingly popular demand. I am very happy to work back toward the empty state, and there appears to be growing support for that. Yeah, +1 from me. Perhaps we should still add some comments about the parameters changed most often, including a link to the documentation of GUC parameters. As a kind of starting point for (new) users. Best Regards Michael Paesold -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: CITEXT 2.0 v4
David E. Wheeler writes: On Jul 17, 2008, at 03:45, Michael Paesold wrote: Wouldn't it be possible to create a variant of regexp_replace, i.e. regexp_replace(citext,citext,text), which would again lower-case the first two arguments before passing the input to regexp_replace(text,text,text)? Sure, but then you end up with this: template1=# select regexp_replace( 'Fxx'::citext, 'X'::citext, 'o'); regexp_replace foo (1 row) Yeah, you are right, I see. :-) Which is just wrong. I'm going to look at the regex C functions today and see if there's an easy way to just always pass them the 'i' flag, which would do the trick. That still won't help replace(), split_part(), or translate(), however. Calling regex functions with the case-insensitivity option would be great. It should also be possible to rewrite replace() into regexp_replace() by first escaping the regex meta characters. Actually re-implementing those functions in a case insensitive way would still be an option, but of course some amount of work. The question is, how much use case there is. Best Regards Michael Paesold -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: CITEXT 2.0 v4
Am 16.07.2008 um 20:38 schrieb David E. Wheeler: The trouble is that, right now: template1=# select regexp_replace( 'fxx'::citext, 'X'::citext, 'o'); regexp_replace fxx (1 row) So there's an inconsistency there. I don't know how to make that work case-insensitively. Wouldn't it be possible to create a variant of regexp_replace, i.e. regexp_replace(citext,citext,text), which would again lower-case the first two arguments before passing the input to regexp_replace(text,text,text)? Best Regards Michael Paesold -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] patch - Collation at database level
Tom Lane wrote: Zdenek Kotala [EMAIL PROTECTED] writes: Martijn van Oosterhout napsal(a): Not necessarily. pg_class is not shared yet without it you can't even find pg_database. Same deal with pg_type. All it means is that pg_collation in template1 must contain all the collations used in template1, which shouldn't be hard to arrange. I think, Collation situation is different, All the argument here is based on the premise that we should have database-level collation specifications, which AFAICS is not required nor suggested by the SQL spec. I wonder why we are allowing a nonstandard half-measure to drive our thinking, rather than solving the real problem which is column-level collations. Wouldn't you still need per-database and per-table default collations? At least MySQL does have such a concept. Best Regards Michael Paesold -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuuming leaked temp tables (once again)
Tom Lane writes: Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: We might have to rearrange the logic a bit to make that happen (I'm not sure what order things get tested in), but a log message does seem like a good idea. I'd go for logging anytime an orphaned table is seen, and dropping once it's past the anti-wraparound horizon. I don't think this requires much of a rearrangement -- see autovacuum.c 1921ff. So everyone is happy with the concept of doing it as above? If so, I'll work on it this weekend sometime. I think it is the most reasonable thing to do. Regarding the log messages about orphaned tables, it would be nice if you could add a hint/detail message explaining how to cleanup those tables. If that's possible. Best Regards Michael Paesold -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to Sponsor a Feature
Greg Smith wrote: On Wed, 11 Jun 2008, Andrew Dunstan wrote: If we want to help people to sponsor features, then I think we need to deal with subjects like finding someone to undertake the development, the sponsor's relationship with the developer, methods and times of payment, etc. The bit on the wiki is helpful for developers trying to get a new feature implemented but I think that's where its scope ends. There seem to be occasional person wandering by here that it really doesn't help though. Periodically you'll see I want feature $X in PostgreSQL. I'm willing to help fund it. What do I do?. In most of those that have wandered by recently, $X is a known feature any number of other people want. Good sample cases here are recent requests to help fund or implement materialized views, supporting queries on read-only slaves, and SQL window support. I don't think these people need guidance on how to manage the project, they need some sort of way to feel comfortable saying will pledge $Y for feature $X in a way that makes sense on both sides. That's what I thought, too. That page just needs a different title. Best Regards Michael Paesold -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How embarrassing: optimization of a one-shot query doesn't work
Am 01.04.2008 um 01:26 schrieb Tom Lane: While testing the changes I was making to Pavel's EXECUTE USING patch to ensure that parameter values were being provided to the planner, it became painfully obvious that the planner wasn't actually *doing* anything with them. For example execute 'select count(*) from foo where x like $1' into c using $1; wouldn't generate an indexscan when $1 was of the form 'prefix%'. ... The implication of this is that 8.3 is significantly worse than 8.2 in optimizing unnamed statements in the extended-Query protocol; a feature that JDBC, at least, relies on. The fix is simple: add PlannerInfo to eval_const_expressions's parameter list, as was done for estimate_expression_value. I am slightly hesitant to do this in a stable branch, since it would break any third-party code that might be calling that function. I doubt there is currently any production-grade code doing so, but if anyone out there is actively using those planner hooks we put into 8.3, it's conceivable this would affect them. Still, the performance regression here is bad enough that I think there is little choice. Comments/objections? Yeah, please fix this performance regression in the 8.3 branch. This would affect most of the JDBC applications out there, I think. Best Regards Michael Paesold -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [JDBC] [HACKERS] How embarrassing: optimization of a one-shot query doesn't work
Am 01.04.2008 um 13:14 schrieb Dave Cramer: On 1-Apr-08, at 6:25 AM, Michael Paesold wrote: Am 01.04.2008 um 01:26 schrieb Tom Lane: While testing the changes I was making to Pavel's EXECUTE USING patch to ensure that parameter values were being provided to the planner, it became painfully obvious that the planner wasn't actually *doing* anything with them. For example execute 'select count(*) from foo where x like $1' into c using $1; wouldn't generate an indexscan when $1 was of the form 'prefix%'. ... The implication of this is that 8.3 is significantly worse than 8.2 in optimizing unnamed statements in the extended-Query protocol; a feature that JDBC, at least, relies on. The fix is simple: add PlannerInfo to eval_const_expressions's parameter list, as was done for estimate_expression_value. I am slightly hesitant to do this in a stable branch, since it would break any third-party code that might be calling that function. I doubt there is currently any production-grade code doing so, but if anyone out there is actively using those planner hooks we put into 8.3, it's conceivable this would affect them. Still, the performance regression here is bad enough that I think there is little choice. Comments/objections? Yeah, please fix this performance regression in the 8.3 branch. This would affect most of the JDBC applications out there, I think. Was the driver ever changed to take advantage of the above strategy? IIRC, it is used in most cases with the v3 protocol, as long as you don't set a prepare-threshold. Best Regards Michael Paesold -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] quote_literal(integer) does not exist
Tom Lane wrote: I don't offhand see anything else I'd consider weakening the casting rules for. If anyone else is interested, I took ... substring(text,integer) | substring(text,integer,integer) | substring(text,text) | substring(text,text,text) | texticlike(text,text) | ~~* texticnlike(text,text)| !~~* texticregexeq(text,text) | ~* texticregexne(text,text) | !~* textlike(text,text) | ~~ textnlike(text,text) | !~~ textregexeq(text,text)| ~ textregexne(text,text)| !~ upper(text) | Thoughts? In one of our applications, we have some numbers (e.g. product numbers) that have meaning attached to individual digits. Product numbers usually contain letters, too, but for historical reasons they do not in this application. So we put them into integer columns for efficiency. We still want to run queries like product_no LIKE '51%' on them. Well, for the application, I don't see much of a problem here. This will probably cost 3-5 lines of code in the whole application. It will just cause some inconvenience when working with psql interactively. And I have not yet seen another DBMS that does not accept almost any input type for the typical string operations such as substring or LIKE. It feels a little bit strange that I will have to do all that typecasting now. Just my $0.02. Best Regards Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Proposed patch for operator lookup caching
Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: We are also talking about catlog changes for 8.3. Are we comfortable doing catalog changes between the beta and RC? The catalog changes in question seem entirely safe ... certainly much more so than this patch ... I do see your point that another beta might be prudent, but on the other hand I'm not sure it's really needed. The only difference between a beta and an RC is that we try not to change the code anymore after RC. To me RC means we think this might be the release candidate and I would like to get some testing in of this in beta before hitting that point. And an additional beta might encourage more testing too. I agree with Bruce here. If you want to apply that operator lookup cache patch, I would have another beta. (And I am not personally against it, because I feel major performance fixes may sometimes slip in as bug fixes.) If you all decide against that patch, we might as well just go for RC1. The catalog changes seem rather trivial, and just a required initdb is no reason for calling it another beta, IMHO. Great work on that patch, btw.! Best Regards Michael Paesold ---(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] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Simon Riggs wrote: On Thu, 2007-10-25 at 13:41 -0300, Alvaro Herrera wrote: ... FWIW I disagree with cancelling just any autovac work automatically; in my patch I'm only cancelling if it's analyze, on the grounds that if you have really bad luck you can potentially lose a lot of work that vacuum did. We can relax this restriction when we have cancellable vacuum. That was requested by others, not myself, but I did agree with the conclusions. The other bad luck might be that you don't complete some critical piece of work in the available time window because an automated job kicked in. Yeah, I thought we had agreed that we must cancel all auto vacuum/analyzes, on the ground that foreground operations are usually more important than maintenance tasks. Remember the complaint we already had on hackers just after beta1: auto *vacuum* blocked a schema change, and of course the user complained. Best Regards Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Alvaro Herrera wrote: Michael Paesold wrote: Simon Riggs wrote: On Thu, 2007-10-25 at 13:41 -0300, Alvaro Herrera wrote: ... FWIW I disagree with cancelling just any autovac work automatically; in my patch I'm only cancelling if it's analyze, on the grounds that if you have really bad luck you can potentially lose a lot of work that vacuum did. We can relax this restriction when we have cancellable vacuum. That was requested by others, not myself, but I did agree with the conclusions. The other bad luck might be that you don't complete some critical piece of work in the available time window because an automated job kicked in. Yeah, I thought we had agreed that we must cancel all auto vacuum/analyzes, on the ground that foreground operations are usually more important than maintenance tasks. What this means is that autovacuum will be starved a lot of the time, and in the end you will only vacuum the tables when you run out of time for Xid wraparound. Well, only if you do a lot of schema changes. In the previous discussion, Simon and me agreed that schema changes should not happen on a regular basis on production systems. Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Best Regards Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Feature Freeze date for 8.4
Alvaro Herrera write: Marko Kreen escribió: As we seem discussing developement in general, there is one obstacle in the way of individual use of DSCMs - context diff format as only one accepted. Both leading DSCMs - GIT and Mercurial do not support it. Hmm, in Subversion you can specify a separate diff command and args, which can be used to generate context diffs. Is it not possible with git/Hg? It's possible in Mercurial, too. There is a bundled extension (extdiff, http://www.selenic.com/mercurial/wiki/index.cgi/ExtdiffExtension), which can do that. You can configure your own command aliases, e.g. hg cdiff ... to do -c diffs. Best Regards Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] rolcanlogin vs. the flat password file
Tom Lane wrote: With the attached patch to not drop nologin roles from the flat password file, it acts more sanely: postgres=# create user foo nologin; CREATE ROLE postgres=# \c - foo Password for user foo: FATAL: password authentication failed for user foo Previous connection kept postgres=# alter user foo password 'foo'; ALTER ROLE postgres=# \c - foo Password for user foo: correct password entered here FATAL: role foo is not permitted to log in Previous connection kept Should we just do this, or is it worth working harder? IMHO this is exactly what we want. It does only offer more information when you already got authentication right and therefore doesn't open an information leak. Not sure about the warning when creating a role with a password but nologin. Could be useful. Best Regards Michael Paesold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Simon Riggs wrote: I think the best way to handle this is to have two limits. First limit attempts to autovacuum, but can be cancelled. When we hit second limit, sometime later, then autovacuum cannot be cancelled. That would give us a breathing space if we need it. Sounds quite reasonable. Best Regards Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Simon Riggs wrote: On Fri, 2007-10-12 at 01:24 -0400, Alvaro Herrera wrote: Yes, I think it is easy to mark the is for xid wraparound bit in the WorkerInfo struct and have the cancel work only if it's off. However, what I think should happen is that the signal handler for SIGINT in a worker for xid wraparound should not cancel the current vacuum. Instead turn it into a no-op, if possible. That way we also disallow a user from cancelling vacuums for xid wraparound. I think he can do that with pg_cancel_backend, and it could be dangerous. I think that is dangerous too because the user may have specifically turned AV off. That anti-wraparound vacuum might spring up right in a busy period and start working its way through many tables, all of which cause massive writes to occur. That's about as close to us causing an outage as I ever want to see. We need a way through that to allow the user to realise his predicament and find a good time to VACUUM. I never want to say to anybody nothing you can do, just sit and watch, your production system will be working again in no time. Restart? no that won't work either. You are probably right that VACUUM going full-steam is a bad idea in most situations. Except for anti-wraparound vacuum, cancellation seems the most reasonable thing to do. Because autovacuum will usually pickup the table in time again. The only problem I would see is if someone has an application that does a lot of schema changes (doesn't sound like a good idea anyway). In that case they would better issue manual vacuums on such tables. Best Regards Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Simon Riggs wrote: After some thought, you and Michael have persuaded me that there is cause to do this for VACUUM as well, but just autovacuum, I think. That also makes the patch simpler, since we don't need to delve inside the av worker to see what it is doing. Alvaro: That means we can just skip your patch altogether, or at least we can discuss them separately now. ... The only danger I can see is that the autovacuum is always killed and never gets to finish, leading to degrading performance at first and shutdown to prevent xid wraparound at the extreme. Doesn't seem likely under normal circumstances, though. Yeh agreed. Table locks aren't that common, so I think we are safe for 100s of millions of transactions. The user has log messages to warn of that, so I think we're good. Hmm, I am not sure we are there, yet. Autovacuum does take extra care to vacuum tables nearing xid wrap-around, right? It even does so when autovacuum is disabled in the configuration. So in case a vacuum is needed for that very reason, the vacuum should *not* be canceled, of course. So we don't really need the information, whether the AV worker is doing VACUUM or ANALYZE, but whether it is critical against xid wrap-around. Could that be done as easily as in Alvaro's patch for distinguishing vacuum/analyze? Alvaro? The other thing I am wondering about is, whether it would be a safer approach to let the DBA decide whether to cancel AV vacuums or just disable cost-delay, as Heikki suggested. There might be valid work-loads for both options... Btw., I am grateful you took up the work here, Simon. Best Regards Michael Paesold ---(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] First steps with 8.3 and autovacuum launcher
Simon Riggs wrote: OK, I've got this working now. It successfully handles this test case, which trips up on an auto ANALYZE every time I run it. ... I notice when we cancel an AV worker it always says cancelling autovacuum of table, even when its just an ANALYZE. Wasn't important before but now looks a little strange. ... Any other input anyone? What about VACUUM (not just ANALYZE)? The starter of the thread Possible bugreport 8.3 beta1 on Win32: Looking like a deadlock with AutoVacuum complained about vacuum, not analyze. It is just as Tom said earlier: it will be before end of beta that people will complain about more than just restoring dumps. ;-) So does this approach work for both analyze as well as vacuum? Best Regards Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: How about getting ShareUpdateExclusiveLock on manual analyze and plain AccessShareLock on autovacuum-induced analyze? Wouldn't fix the original problem because those two lock types don't conflict; hence might as well keep the behavior simple. What about a new separate lock type for analyze? Couldn't that really solve the issue? I know I'm just hand-waving here ;-) Best Regards Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Tom Lane wrote: In the worst case autovac could be starved out for a long time. I don't have any immediate good idea about how to fix that, but the worst consequences could be avoided if we disable the cancellation ability when running an anti-wraparound vacuum. Further down the road (*not* 8.3), when we teach autovac about maintenance windows, it might also disregard cancels during a maintenance window. During maintenance window, it could instead lower vacuum cost delay in the case it would otherwise abort. That would also make sure that the task would finally finish at some point (even when the maintenance window ends too early... or perhaps any cleanup task started during a maintenance window should keep it's maintenance priority?) Best Regards Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Per-function GUC settings: trickier than it looked
Florian G. Pflug wrote: Tom Lane wrote: So, to reiterate, my idea is .) Make SET TRANSACTION a synonym for SET LOCAL at the SQL-Level. .) In pl/pgsql, SET TRANSACTION sets a new value that is kept after the function exits, even if the function has a matching SET-clause. .) SET LOCAL in pl/pgsql set a new value that is kept if the function has no matching SET-clause. If it has one, the value is restored. In any case, we emit a warning that SET LOCAL is going away. .) One day, make SET LOCAL in pl/pgsql mean local to the surrounding BEGIN/END block. Independent of any SET-clauses the function might or might not have. I don't think it's a good idea to change SET LOCAL now and plan on changing it again later ;-). If we really want BEGIN-block-local SET capability, I'd prefer to think of some new keyword for that. But I'm not convinced it's interesting --- given the proposed behavior of function SET-clauses, attaching a SET to your function seems like it'll cover the need for restoring outer values. Hm... could we still have SET TRANSACTION as a synonym for SET LOCAL? That would blend nicely with SET TRANSACTION ISOLATION LEVEL and SET TRANSACTION READ ONLY. I don't think it's a very good idea to make SET TRANSACTION an alias for SET LOCAL, because SET TRANSACTION has already got its own meaning in the SQL spec - it sets transaction modes. Although I agree with you that variables set with SET LOCAL are also attached to the transaction (by definition), I would still rather separate transaction-local GUCs from spec-defined transaction modes. As precedence, they have two separate reference pages already: http://www.postgresql.org/docs/8.1/interactive/sql-set.html http://www.postgresql.org/docs/8.1/interactive/sql-set-transaction.html [ thinking... ] Hey, wait a moment. Regarding SET TRANSACTION READ ONLY - This is not strictly speaking a GUC, but still, if we pretend that there are no subtransaction, that command should too propage to the outermost transaction on release, shouldn't it? ... I believe that for consistencies sake, the set transaction read only should have propagated to the outermost transaction on release s1. Sounds reasonable to me. I understand SAVEPOINT/RELEASE come from the SQL standard. So does the SQL standard say anything about this? Best Regards Michael Paesold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Per-function GUC settings: trickier than it looked
Tom Lane wrote: Michael Paesold [EMAIL PROTECTED] writes: I don't think it's a very good idea to make SET TRANSACTION an alias for SET LOCAL, because SET TRANSACTION has already got its own meaning in the SQL spec - it sets transaction modes. Yeah --- I'm not sure we could even do it without getting shift/reduce conflicts in bison. There is some attraction to the idea of keeping SET LOCAL's current behavior and inventing a third form of SET that has the lasts-till-end-of-current-main-transaction behavior. However (1) we'd have to pick some other keyword than TRANSACTION; (2) I still don't see how to document SET LOCAL's current behavior without introducing the concept of subtransaction into it, and I think we shouldn't do that. Basically my perspective on SET LOCAL is that its current behavior is a bug, and even though it's been that way for a couple major releases now, it's still something we oughta fix while we are busy whacking that part of the code around. Florian's example with SET TRANSACTION READ ONLY proves that it's a bug --- RELEASE is not defined to change any transaction modes. Yeah, I think your original proposal was really sound. I would not expect the current SET LOCAL behaviour in the context of savepoints. If we really need the current behaviour, we should find a new name for this lasts-until-savepoint-release-or-transaction-end thingy. Best Regards Michael Paesold ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] tsearch patch and namespace pollution
Bruce Momjian wrote: I would be happy if all text search functions began with 'ts', 'ts_', or 'to_ts', and if we don't clean this up now, it is going to be harder in the future. +1 from me. \df is also much more useful then. I think users can expect some migration for text search in 8.3 as a benefit of getting into core and be dump-able. I guess so. Especially if you change some functions, they will have to change source code anyway. So you can as well cleanup all functions that don't fit into a sound naming schema. Best Regards Michael Paesold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] tsearch2 in PostgreSQL 8.3?
Bruce Momjian wrote: Uh, how are we going to prevent the auto-casting to tsvector from using the default GUC config, e.g. CREATE INDEX i ON x USING GIN(col)? This is where I started to see the need for education and error-prone nature of the default GUC just wasn't worth having it, though I know others disagree. It can be removed quite easily. AFAIR, this feature was added on suggestion of Tom Lane. It was certainly only added in this tsearch-to-core release cycle, see here: http://archives.postgresql.org/pgsql-hackers/2007-03/msg01384.php Teodor Sigaev wrote: 2) added operator class for text and varchar CREATE INDEX idxname ON tblname USING GIN ( textcolumn ); So just remove the operator class or don't specify it as default operator class for GIN, and the thing is gone. Perhaps there is a better way to do this, though. [...digging...] The idea was born in the thread starting here (involving Tom Lane, Joshua Drake, and Teodor Sigaev): http://archives.postgresql.org/pgsql-hackers/2007-03/msg00912.php with the conclusion here: http://archives.postgresql.org/pgsql-hackers/2007-03/msg00936.php Best Regards Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 2PC-induced lockup
Simon Riggs wrote: On Wed, 2007-07-11 at 18:09 -0400, Tom Lane wrote: There seems like a number of ways that unresolved prepared transactions can cause problems. We really need to have startup mention how many prepared transactions there are, so we have some chance of understanding and resolving potential problems. While I have no particular objection to such a log entry, I doubt it will fix anything; how many people will really think to look in the postmaster log? Even if it were just you and me. From my perspective, thats enough. At least, such a message seems much more useful than the list of historic startup messages that were removed recently. Just my two €-cents. Best Regards Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Still recommending daily vacuum...
Alvaro Herrera wrote: So what you are proposing above amounts to setting scale factor = 0.05. The threshold is unimportant -- in the case of a big table it matters not if it's 0 or 1000, it will be almost irrelevant in calculations. In the case of small tables, then the table will be vacuumed in almost every iteration if the threshold is 0, which is fine because the table is small anyway. So why not let the threshold be 0 and be done with it? For very small tables, setting a threshold of 0 could mean a vacuum after every single row update (or every other row). I think that is just burning cycles. What about a threshold of 10 or 50, to have at least some sanity limit? Even though the cost of vacuum of a small table is low, it is still not free, IMHO, no? Best Regards Michael Paesold ---(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] Still recommending daily vacuum...
Joshua D. Drake wrote: Alvaro Herrera wrote: Joshua D. Drake wrote: Did we change the default autovac parameters for 8.3 (beyond turning it on?) because on any reasonably used database, they are way to conservative. We're still on time to change them ... Any concrete proposals? I could provide numbers from production high use databases. We could probably back those down a little and make more reasonable numbers. Please do so. Perhaps others can also tell their typical settings. Best Regards Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] msvc and vista fun
Andrew Dunstan wrote: Relevant perl code executed by buildfarm: chdir $pgsql/src/tools/msvc; @makeout = `build 21`; chdir $branch_root; my $status = $? 8; I know the docs say otherwise, but would it be possible that chdir somehow resets $? on windows, sometimes, under some circumstances? Perhaps just move up the my $status.. one line up? Best Regards Michael Paesold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] GUC time unit spelling a bit inconsistent
Bruce Momjian wrote: Simon Riggs wrote: Please lets be real about this and allow the abbreviations suggested. Agreed. Your efforts to introduce units is excellent and much appreciated by all; please don't make them harder to use than the plain numbers were. Agreed. Agreed. I don't see the point in following a standard few people know about. It's not about a certain standard. There are so many different ways in the world to write time units, so in a certain context a standard is really useful to constrain the format/syntax, but... This all was about usability of a configuration file, wasn't it? Now, Peter, you improved that very much with this change. But do you at the same time want to cripple the usefulness again by insisting on a certain _syntax_, while the _semantics_ are completely clear to (guessing) 99% of the people who will changes these settings? To put it different, there are reasons we try to comply with the SQL standard, not just because we feel like it. Anyone, look at the many archive posts from Tom Lane and others, explaining why we strictly stick to the SQL standard in some cases but allow to extend standard in others. I just see no compelling reason to comply with a certain standard here. Best Regards Michael Paesold ---(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] GUC time unit spelling a bit inconsistent
Marko Kreen wrote: Considering Postgres will never user either meter or mile in settings, I don't consider your argument valid. I don't see the value of having units globally unique (literally). It's enough if they unique in the context of postgresql.conf. Thus +1 of having additional shortcuts Tom suggested. Also +1 for having them case-insensitive. Agreed. Although I suggest perhaps to not press for m as minutes, because it really is ambiguous for months or minutes, esp. in a context like log_rotation_age. Please lets have the unambiguous abbreviations. Please lets make it all case-insensitive. After all this discussion, what about a straight forward vote? Bruce, we had those before, no? Best Regards Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] GUC time unit spelling a bit inconsistent
Peter Eisentraut wrote: Am Donnerstag, 21. Juni 2007 00:38 schrieb Gregory Stark: I think people are worried that an 'm' in one column might mean something different than an 'm' in another column, and perhaps that is confusing. To whom? the person writing it? If everyone around here had gotten their way we'd already be in a situation were you could write log_rotation_age = 5m log_rotation_size = 5m There are valid reasons against 5m as mega-bytes, because here m does not refer to a unit, it refers to a quantifier (if that is a reasonable English word) of a unit. So it should really be 5mb. log_rotation_age = 5m log_rotation_size = 5mb That is quite clear now, except, I admit, that the first could be mistaken to mean 5 months, and perhaps this is a valid reason to not allow 'm' for minutes. Nothing about meters here, though. Btw.: I'm currently at DebConf in Edinburgh. On Scottish motorway signage, 5m means five miles. Even the Americans do that better. So, no, you can't have m for minutes. ;) Even with the ;) here and the context, the last sentence sounds to me quite arrogant. Most people here have tried to bring arguments and reasoning... you put it off with irrelevant anecdotes in the wrong context. Best Regards Michael Paesold ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] GUC time unit spelling a bit inconsistent
Marko Kreen wrote: On 6/21/07, Michael Paesold [EMAIL PROTECTED] wrote: Marko Kreen wrote: Considering Postgres will never user either meter or mile in settings, I don't consider your argument valid. I don't see the value of having units globally unique (literally). It's enough if they unique in the context of postgresql.conf. Thus +1 of having additional shortcuts Tom suggested. Also +1 for having them case-insensitive. Agreed. Although I suggest perhaps to not press for m as minutes, because it really is ambiguous for months or minutes, esp. in a context like log_rotation_age. IMHO, as postgresql.conf is not a scientific article to Nature, we can be more relaxed about this. Currently admin-friendlyness should top scientific precision. As minute is much more needed unit that month it should get shorter abbrevation. If we _do_ have unit for months for some reason, I would even suggest removing it to make m unambigious. That's ok with me, too. But instead of letting this argument about m get us nowhere, let's at least to the other improvements. :-) Best Regards Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Reducing NUMERIC size for 8.3
Andreas Pflug wrote: Simon Riggs wrote: The objections to applying this patch originally were: 2. it would restrict number of digits to 508 and there are allegedly some people that want to store 508 digits. If 508 digits are not enough, are1000 digits be sufficient? Both limits appear quite arbitrary to me. This 1000 is just a restriction on the typmod of numeric. You can still use a much higher number of digits, if you use unconstrained numeric: test= create table test (n numeric); CREATE TABLE test= insert into test values (10::numeric ^ 9); INSERT 0 1 test= select length(n) from test; length 100017 (1 row) Best Regards Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Load Distributed Checkpoints, revised patch
Heikki Linnakangas wrote: Here's an updated WIP version of the LDC patch. I just spreads the writes, that achieves the goal of smoothing the checkpoint I/O spikes. I think sorting the writes etc. is interesting but falls in the category of further development and should be pushed to 8.4. Why do you think so? Is it too much risk to adapt the sorted writes? The numbers shown by ITAGAKI Takahiro looked quite impressive, at least for large shared_buffers configurations. The reactions where rather positive, too. In general, I am hoping that this patch, together with Automatic adjustment of bgwriter_lru_maxpages will finally make default postgresql configurations experience much less impact from check points. For my tast, postgresql has recently got way to many nobs which one must tweak by hand... I welcome any approach on auto-tuning (and auto vacuum!). Patch status says waiting on update from author: http://archives.postgresql.org/pgsql-patches/2007-04/msg00331.php Any updates on this? Best Regards Michael Paesold ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] tsearch_core patch: permissions and security issues
Bruce Momjian wrote: I an attempt to communicate what full text search does, and what features we are thinking of adding/removing, I have put up the introduction in HTML: http://momjian.us/expire/fulltext/HTML/fulltext-intro.html Very good idea, Bruce! After reading the discussion and the introduction, here is what I think tsearch in core should at least accomplish in 8.3. Please bear in mind, that (a) I am talking from a user perspective (there might be technical arguments against my thoughts) and (b) I have no hands-on experience with Tsearch2 yet, so more experienced users might have different needs. - Basic full text search usable for non-superusers - Out-of-the-box working configuration for as many languages as reasonable (Teodor named quite a number of languages working as-is, so this is really an improvement over contrib, great!) - No foot-guns accessible to non-superuser - Agreement on function names, perhaps some should be changed. For instance to_tsquery() and plainto_tsquery() seem rather unintuitive because they don't have a common prefix, and they are not consistent about using underscores. Perhaps to_tsquery() and to_tsquery_plain()? - Future compatibility for all features available to non-superusers - Stop words in tables, not in external files. - At least for superusers, all features available in contrib now, should be available, too (don't know about pg_dump). What I don't really like is the number of commands introduced without any strong reference to full text search. E.g. CREATE CONFIGURATION gives no hint at all that this is about full text search. IMHO there are more configurations than just full text ones. :-) So perhaps better spell this CREATE FULLTEXT CONFIGURATION etc.? (Think about tab completion in psql, for instance.) I guess this is in line with what Tom said about mapping objects and CREATE ATTRIBUTE vs. CREATE/ALTER CONFIGURATION. (http://archives.postgresql.org/pgsql-hackers/2007-06/msg00522.php) After all, I would really welcome having full text search capabilities in core. Best Regards Michael Paesold ---(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] Autovacuum launcher doesn't notice death of postmaster immediately
Matthew T. O'Connor schrieb: Tom Lane wrote: Andrew Hammond [EMAIL PROTECTED] writes: Hmmm... it seems to me that points new users towards not using autovacuum, which doesn't seem like the best idea. I think it'd be better to say that setting the naptime really high is a Bad Idea. It seems like we should have an upper limit on the GUC variable that's less than INT_MAX ;-). Would an hour be sane? 10 minutes? This is independent of the problem at hand, though, which is that we probably want the launcher to notice postmaster death in less time than autovacuum_naptime, for reasonable values of same. Do we need a configurable autovacuum naptime at all? I know I put it in the original contrib autovacuum because I had no idea what knobs might be needed. I can't see a good reason to ever have a naptime longer than the default 60 seconds, but I suppose one might want a smaller naptime for a very active system? A PostgreSQL database on my laptop for testing. It should use as little resources as possible while being idle. That would be a scenario for naptime greater than 60 seconds, wouldn't it? Best Regards Michael Paesold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Postmaster startup messages
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Does anyone actually read these? LOG: database system was shut down at 2007-05-30 17:54:39 CEST LOG: checkpoint record is at 0/42C4FC LOG: redo record is at 0/42C4FC; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 0/593; next OID: 10820 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready Why not just: LOG: database system is ready I like the report of the previous system state (the first line). I agree that the four in the middle could be reduced to DEBUG1 or some such. +1 from me. In case of recovery, I think one should still get the full output, no? It might be important information then. Best Regards Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Bug in UTF8-Validation Code?
Andrew Dunstan wrote: Albe Laurenz wrote: A fix could be either that the server checks escape sequences for validity This strikes me as essential. If the db has a certain encoding ISTM we are promising that all the text data is valid for that encoding. The question in my mind is how we help people to recover from the fact that we haven't done that. I would also say that it's a bug that escape sequences can get characters into the database that are not valid in the specified encoding. If you compare the encoding to table constraints, there is no way to simply escape a constraint check. This seems to violate the principle of consistency in ACID. Additionally, if you include pg_dump into ACID, it also violates durability, since it cannot restore what it wrote itself. Is there anything in the SQL spec that asks for such a behaviour? I guess not. A DBA will usually not even learn about this issue until they are presented with a failing restore. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: conversion efforts (Re: [HACKERS] SCMS question)
Alvaro Herrera wrote: Warren Turkal wrote: On Saturday 24 February 2007 15:18, Alvaro Herrera wrote: Keep in mind that the repository as converted by Josh, above, is strangely corrupted in weird and unpredictable ways. Would you care to elaborate on that statement? I'd like to check my converted repositories for what you're referring to. I don't know :-( I've tried to use the Trac site looking for particular changesets and found that for some of them, the list of files are out of sync with reality, and sometimes the diff don't have anything to do with what the commit message says. I've never been sure if the problem is the repo itself, or the Trac interface. After discovering the problem independently a couple of times (the second time I had forgotten that I had already found a problem), I stopped using it and reverted to using cvs2cl and cvsup. I imagine the problems are caused by manual mangling of the files in the early days, like the perl5 dir stuff you found. Hmm, if you only checked using the Trac interface, maybe this is an issue with re-creating the SVN repo. Joshua, do you run trac-admin /path/to/trac/env resync after rebuilding the repository? (This command would re-sync the trac database with the repository.) Otherwise I would certainly expect such issues as Alvaro describes. Best Regards Michael Paesold ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Type casting bug in 8.1.[67]?
Hello all, after upgrading from 8.1.5 to 8.1.7, I got errors in the server log when updating decimal values using string constants. I tried the same using psql (pasted the query from below) and it fails, too. Downgrading to 8.1.5 resolved the issue. ERROR: attribute 4 has wrong type DETAIL: Table has type numeric, but query expects numeric. STATEMENT: UPDATE reminder SET reminder_charges='0' WHERE reminder_id=29362 reminder_charges is defined as: reminder_charges | numeric(5,2) | not null I guess this is a bug. Best Regards Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Type casting bug in 8.1.[67]?
Tom Lane wrote: Michael Paesold [EMAIL PROTECTED] writes: after upgrading from 8.1.5 to 8.1.7, I got errors in the server log when updating decimal values using string constants. Have you got a constraint or functional index on that column? Yes. Check constraints: tc_reminder_charges CHECK (reminder_charges = 0::numeric) As I read from your other post, you already figured that the issue is related to check constraints (or functional indexes). Best Regards, Michael Paesold ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Ooops ... seems we need a re-release pronto
Tom Lane wrote:
Bruno Wolff III [EMAIL PROTECTED] writes:
Is a test going to get added to the regression tests to catch similar
regressions in the future?
I've been thinking about that. It seems that the regression tests have
fairly poor coverage of use of typmod-bearing data types in general;
most of our tests of complicated queries tend to use simple datatypes
like int or text. I don't have any immediate thoughts what to do about
that --- massive expansion of the tests doesn't seem justified --- but
this isn't the first bug we've hit in this area. It's just a bit more
embarrassing than most :-(
I think at least the most simple cases should be added. At the very least a
test that would have caught this issue. This is really the first time that
I had to pull a minor release and go back to a previous version. ;-)
As far as I understand, it's as simple as this (untested):
CREATE TABLE tab (
c DECIMAL(5,2) NOT NULL,
CHECK (c = 0)
);
INSERT INTO tab ('0');
Right?
Or at least:
UPDATE tab SET c='0';
Best Regards
Michael Paesold
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] string_to_array eats too much memory?
Tom Lane writes: Tatsuo Ishii [EMAIL PROTECTED] writes: string_to_array() consumes too much memory. For example, to make ~70k array elements, string_to_array seems to eat several Gig bytes of memory. I'd argue that the problem comes from enlarging the work arrays only 64 elements at a time in accumArrayResult(). Most of the rest of the code deals with resizing arrays using a double it each time it has to grow approach, I wonder why this is different? Without reading the code, I guess that simply means O(n^2) runtime. This should be fixed, then, right? Best Regards, Michael Paesold ---(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] Mirror problems for download
Not being subscribed to any more appropriate list, I post this here on hackers. I just wanted to download the postgresql-8.0.9 tarball. The page I got was this: Choose a download mirror Downloading: /source/v8.0.9/postgresql-8.0.9.tar.gz We could not query the database or no mirrors could be found! Download PostgreSQL from the primary site Read this if you would like to host a mirror. Of course the primary FTP site is already unavailable (530 - maximum number of users reached). I get the same error for older releases, too. Can someone look into this? Best Regards Michael Paesold [ CC: to [EMAIL PROTECTED] ] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Mirror problems for download
Shane Ambler wrote: Michael Paesold wrote: Not being subscribed to any more appropriate list, I post this here on hackers. I just wanted to download the postgresql-8.0.9 tarball. The page I got was this: Choose a download mirror Downloading: /source/v8.0.9/postgresql-8.0.9.tar.gz We could not query the database or no mirrors could be found! Download PostgreSQL from the primary site Read this if you would like to host a mirror. Of course the primary FTP site is already unavailable (530 - maximum number of users reached). I get the same error for older releases, too. Can someone look into this? Your seeing a general overload problem - with new versions just released everyone is jumping on and downloading at the same time. This message (see above) does not look just like an overload problem, no? We could not query the database or no mirrors could be found! Best Regards Michael Paesold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Mirror problems for download
Magnus Hagander wrote: Michael Paesold wrote: I just wanted to download the postgresql-8.0.9 tarball. The page I got was this: Choose a download mirror Downloading: /source/v8.0.9/postgresql-8.0.9.tar.gz We could not query the database or no mirrors could be found! Download PostgreSQL from the primary site Read this if you would like to host a mirror. Of course the primary FTP site is already unavailable (530 - maximum number of users reached). I get the same error for older releases, too. Can someone look into this? Thanks for reporting this. It has now been fixed - it was a problem with the mirror checking script being fooled by a temporary file that couldn't be removed because it was owned by the wrong user. Db is updating now, all mirrors should be back in 10 minutes or so. Thanks for fixing. Works again for me. (Man, it's convenient with wireless internet on the airplane somtimes...) :-) Best Regards Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] continuing daily testing of dbt2 against postgresql
[EMAIL PROTECTED] wrote: Mark Wong [EMAIL PROTECTED] writes: After over a year of problems (old site http://developer.osdl.org/markw/postgrescvs/) I have resumed producing daily results of dbt-2 against PostgreSQL CVS code with results here: http://dbt.osdl.org/dbt2.html This is good to hear! I am curious where we are now compared to where we were a year ago ... do you still have the old data, and is the test setup still comparable? The test setup is on completely different hardware. I still have the old data and it's accessible, but it'll take a little bit of work to regenerate the links. I'll try to work on that. I think it would also help if you would create reference runs for the latest 8.0 and 8.1 releases on the new hardware. Best Regards Michael Paesold ---(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] 8.2 beta blockers
Tom Lane wrote: I see the following items standing between us and putting out 8.2beta1: * Set client encoding based on OS environment - Peter E. [snip] Personally I'm willing to commit to making the VALUES-list docs and userlock replacement code happen tomorrow. Bruce seems to be close on the release notes, and if the other two items aren't ready, well, beta1 can ship without 'em. If you talk about the client encoding thing here... Since the client encoding change is a feature addition and a behavioral change, I think this should really be done before going to beta, IMHO. Btw. was there any mailing list discussion on this item? Other than this: http://archives.postgresql.org/pgsql-hackers/2003-05/msg00737.php. I really hope that this change will only affect psql, not pg_dump, as Peter wrote in 2003. I would strongly object to such a change (as much as my voice counts). The current behavior of dumping with the database encoding is exactly the right thing to do. I have a database in UTF-8 here. Using any LATIN based encoding for dumping the database will simply break, because there are always characters that don't map into the encoding. Even with psql there could be issues with existing scripts, but I see a benefit at least. Best Regards Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] FE/BE protocol vs. parameterized queries
Tom Lane wrote: The infrastructure for the former planning method (using the first Bind's parameters as sample values for estimation, but not as constants) is still there, but it's not being used now. Does anyone want to argue for changing things to plan named statements that way? I'm of two minds about it myself; you can make a good case that it'd usually be a win, but it's also not hard to envision scenarios where it'd be a loss. Although I don't have a clear opinion myself, I sometimes read on this list that people are using prepared statements to get safe, stable plans, i.e. plans that don't depend on the specific parameter input. If you change that, I don't think they will be happy at all. I suggest leaving it as-is for 8.2. I think the user (i.e. driver) should be able to tell the backend, if they want planning for the first bind, or right at prepare. Best Regards Michael Paesold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Win32 hard crash problem
Magnus Hagander wrote: Another point that at least I don't know - what kind of connection pool is it? Is it an external one (like pgpool) to which the java app connects (using FE/BE protocol, emulating a proper postmaster but pooling access to the database), or is it running inside the app server (like for example .net connection pooling does, which simply means that when you run the Open() method on the connection object it will pick something off an *internal* pool)? Googling for 3CPO [1] shows that it is a Java-based connection pool that implements connection pooling using the JDBC API, i.e. it is an *internal* pool running inside the app servers JVM. PG Admin cannot in any case connect through this pool. Best Regards Michael Paesold [1] http://sourceforge.net/projects/c3p0 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Open items for 8.2
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Am Dienstag, 5. September 2006 05:58 schrieb Tom Lane: A couple of recently discussed FE/BE protocol issues are: not storing a plan at all for unnamed-statement cases, and thus allowing bind parameters to be treated as constants; allowing parameter types to go unresolved rather than throwing an error. Perhaps it's too late to consider these for 8.2, but they seem no more invasive than some other items on the open-issues list. Do we have a patch for that today? We could have a patch for the first one today --- I was thinking about it last night and intending to code it today. The second one is merely a matter of removing an error check that exists now; the question really is do people want that behavior. (I asked that on the jdbc list and got zero response, so actually I was thinking that it was a dead issue; but as long as it's on the open-items list we ought to discuss it.) I personally think it's a good idea to do it, as it should improve the plans for one-shot queries. Unfortunately I don't certainly know how the JDBC driver issues queries when called through a PreparedStatement but without a prepare-threshold[*] set. If it uses the unnamed-statement, then I guess the proposed change would be a win. Best Regards Michael Paesold [*] This option determines, after how many executes of a prepared statement, the driver will switch to server-side prepares. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [OT] MySQL is bad, but THIS bad?
Joshua D. Drake wrote: Thomas Hallgren wrote: Couldn't we just install something that replaced invalid dates with a randomly generated but otherwise correct dates? That way they would become completely invisible. No one could even tell that the date was invalid to start with. No we can't, because then we are taking an invalid date, which is potentially valid data (to the user) and modifying it to a valid date that is indeed invalid data. I think you should have read a `;-)' after Thomas' suggestion. ;-) Best Regards, Michael ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Foreign keys for non-default datatypes
Tom Lane wrote: If we did this then RI checks would no longer be subvertible by rules or user triggers. Stephan Szabo writes: I don't think that it'd really help because it's the actions that are generally subvertible not the checks and since those are looking at the potentially not indexed fk side, I don't think the above would apply. Oh, right, we'd probably still need to do planning in that case. Unless we wanted to insist on having an FK-side index too for every FK, which is something I'm not for. I don't really understand the implications here, but I hope that the following usecase will still work afterwards: Two tables A, B. B (id) references A (id), with ON DELETE CASCADE Usually deleting a row from A will cause all referencing rows in B to be deleted, too. Nevertheless B has a BEFORE DELETE trigger check_delete that checks if a row of B may be deleted or not. I.e. it contains a IF ... RAISE EXCEPTION... Will this trigger still be called, so it can abort the delete? If not, I am against that change because it will break the consistency-enforcements of one of our applications. In other words, if you only change the checks of the FKs, I see no problem at all; but if you change the actions of FKs to not call user defined triggers, I have a problem. Please correct any of my wrong assumptions. ;-) Best Regards, Michael ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] problem with large maintenance_work_mem settings and
Stefan Kaltenbrunner wrote: hubert depesz lubaczewski wrote: On 3/4/06, Stefan Kaltenbrunner [EMAIL PROTECTED] wrote: forgot to mention that this is 8.1.3 compiled from source. Further testing shows that not only CREATE INDEX has some issue with large maintenance_work_mem settings : what does it show: cat /proc/sys/kernel/shmmax 1421326592 not that I think it is related to the problem at all. I can second that. Maintenance work mem is not allocated in shared memory. It looks like I'm hitting the MaxAllocSize Limit in src/include/utils/memutils.h. There are two issues you have mentioned. This one is more obvious: the limitation of the memory that can be allocated. The other one is the very bad performance for index creation. I can only guess, but is sound like this is related to the recent discussion on hackers about issues with the qsort performance. If the theory is true, your index creation should be much faster with a much lower setting for maintenance_work_mem, so that it uses external sort. See the discussion starting here: http://archives.postgresql.org/pgsql-hackers/2006-02/msg00590.php Best Regards, Michael Paesold ---(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] Foreign keys for non-default datatypes
Tom Lane writes: Michael Paesold [EMAIL PROTECTED] writes: Will this trigger still be called, so it can abort the delete? We'd certainly still call triggers and check row-level constraints, and any error would abort the whole statement (leaving A unmodified). The case that I think we'd forbid if the implementation could support doing so is where a BEFORE trigger cancels the B-update operation by returning NULL. This currently leaves you with a row in B that violates the FK constraint (once the A row is gone). Triggers that modify the row to be stored are not a problem, because B will have an AFTER trigger that rechecks the row against A anyway. AFAICS it's only the silent-cancellation case that subverts RI constraints. Rules on B that rewrite the DELETE or UPDATE into something else are also problematic. This is all moot at the moment since Stephan pointed out that we still need planning for the FK actions (ie the cascaded deletes/updates). So I'm not currently thinking of redoing the implementation of actions. Ok, thank you for the explanation. At least I am not worried about a future reimplementation of the RI triggers. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Not so happy with psql's new multiline behavior
Tom Lane wrote: At a minimum this code has to be fixed to understand the difference between backslash commands and SQL lines, and not combine them in history entries; otherwise we should revert it. I'm leaning to revert since I haven't actually seen a case where pulling back multiple lines helped me ... but maybe that just reflects that I don't retype multiline SQL commands all that much. Reverting or not, this is rather a matter of how annoying it is right now (for the developers using CVS tip). I think the old behaviour needs improvement. You could either use \e and have nice editing capabilities, but have no tab completition, no backslash-commands in between, and your nice multiple-lines-query fell apart as soon as you exited psql. I have not tried CVS tip for a while, but what you describe needs fixing. Backslash-commands should definately work. Mark [EMAIL PROTECTED] wrote: To check it out, try /bin/zsh (it seems to come with Linux and Solaris these days), and type out: Actually I am quite impressed by the way zsh works, I've just tried it. I think it could even work that way in psql, including the slash commands. For everyone who has never tried zsh, now is the time. ;-) When you edit a multiline function in zsh, you can easily press Control-C, then type man zsh, return, and press up to continue editing the function as it was left when you pressed Control-C. This could work the same way in psql. You edit your query, press Control-C, issue a backslash command, press up, finish your query. The zsh that comes with my linux distribution is BSD licensed, so we could even borrow code. :-) On the other hand, I don't know if everybody will like it this way. Perhaps this should be implemented as a plugin. (Worst case scenario, but I wonder wether we can make all people happy ever.) Best Regards, Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Not so happy with psql's new multiline behavior
Alvaro Herrera wrote: Michael Paesold wrote: When you edit a multiline function in zsh, you can easily press Control-C, then type man zsh, return, and press up to continue editing the function as it was left when you pressed Control-C. Not sure about zsh's Ctrl-C, but in bash I press Esc-# and a # is prepended to the current line and entered into the history. This is what I use when I want to review some manpage or something. Nice, didn't know about that. It also works in psql, but unsurprisingly it also prepends #. We could fix it by having it prepend -- instead, or maybe enclose the current editing buffer in /* */. (This only works in a single line fashion in bash, but I don't see why we couldn't make it work multiline in psql.) The main big difference between zsh and bash is that zsh allows real in-place multiline editing. You can use your arrow keys to navigate through the buffer. I don't know how the new psql mode works. Does it do multi-line editing even including returns? I probably should try it out myself... Best Regards, Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ipcclean in 8.1 broken?
Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: (I'm not finding it right now, but I'm pretty sure that the SUS specifies that numeric userid == 0 for superuser, whereas root is not required to be the name, so this would be more correct anyway.) Can we assume 'id' is on all unix systems? What's your point? The script fails anyway if that bit doesn't work. Is 'id' better than what we have now if 'id' isn't widely supported? I don't think this is really a question of portability. The variables $USER and $LOGNAME are not always set to the current (effective) user, e.g. on linux. That's Chris' current problem, I think. Just compare the difference of using su with and without the -l argument: $ su # echo $LOGNAME ; echo $USER mip mip # exit $ su -l # echo $LOGNAME ; echo $USER root root # Of course, if you just want to question the use of id, that's a different story. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Pl/Python -- current maintainer?
James William Pye wrote: On Sun, Feb 26, 2006 at 01:08:52PM -0500, Tom Lane wrote: That design is broken on its face, as the system does not guarantee to call the validator. Hrm. Other than language creations that do not specify a validator, at what times will Postgres not call the validator upon function creation? SET check_function_bodies = off; Best Regards, Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Domains and supporting functions
Elein wrote: http://www.varlena.com/GeneralBits/128.php Known Problems and Issues: * Creating the table with an email PRIMARY KEY did not use our comparison function. It was necessary to create a unique index which explicitly used the email operator class. * ORDER BY requires USING op clause. * LIKE does not work. Use defined operator % instead. There are convincing arguments for and against this behavior. Feel free to argue one way or the other. I once created a case-insensitive ivarchar type based just reusing the varcharin/out functions and some pl/pgsql functions. I can send you the complete .sql file, if you want. I have not looked at your type, but when I saw LIKE does not work, I thought I'd send you this part of the ivarchar type, which should explain how I got the LIKE functionality to work. -- Support case insensitive LIKE operations -- Support functions CREATE FUNCTION ivarcharlike( ivarchar, text ) RETURNS boolean AS 'BEGIN RETURN texticlike($1::text,$2); END' LANGUAGE PLpgSQL IMMUTABLE STRICT; CREATE FUNCTION ivarcharnlike( ivarchar, text ) RETURNS boolean AS 'BEGIN RETURN texticnlike($1::text,$2); END' LANGUAGE PLpgSQL IMMUTABLE STRICT; -- Operators used by LIKE and NOT LIKE CREATE OPERATOR ~~ ( PROCEDURE=ivarcharlike, LEFTARG=ivarchar, RIGHTARG=text, NEGATOR= !~~, RESTRICT=iclikesel, JOIN=iclikejoinsel ); CREATE OPERATOR !~~ ( PROCEDURE=ivarcharnlike, LEFTARG=ivarchar, RIGHTARG=text, NEGATOR= ~~, RESTRICT=icnlikesel, JOIN=icnlikejoinsel ); LIKE is really not much more than syntactic sugar for the ~~ operator. Hope this is useful. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Need pointers to standard pg database(s) for testing
Ron wrote: I assume we have such? You could look at the Sample Databases project on pgfoundry: http://pgfoundry.org/projects/dbsamples/ Best Regards, Michael Paesold ---(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] Spaces in psql output (Was: FW: PGBuildfarm member snake Branch HEAD Status changed)
Bruce Momjian wrote: The failure, I think, it because of the newline patch we got for psql yesterday. I am seeking a diff from pgcrypto to fix it. My openssl is too old. A side affect of this newline patch is that all fields are now filled with white space up to the displayed column width, even for the last (or only column). I guess this is somehow required for consistent display. Otherwise, it makes copypaste from a psql session more painful, because of all the added white-space. I hope that psql output intended for script output using the available flags (i.e. not the nice display output) is unaffected? Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Spaces in psql output (Was: FW: PGBuildfarm member snake Branch HEAD Status changed)
Martijn van Oosterhout wrote: On Fri, Feb 10, 2006 at 08:06:53PM +0100, Michael Paesold wrote: A side affect of this newline patch is that all fields are now filled with white space up to the displayed column width, even for the last (or only column). My intention was to only change formatted output. Unformatted should be unchanged from previous. The extra spaces is an interesting side-effect. In the past it would only have worked for the last column anyway, right? Right, my explanation was not correct. It should have been the last column is now also filled with spaces. Of course all but the last column were always filled with spaces. Anyway, it is a fixable issue and I'd consider doing it if people think it's worth it. I personally don't like the added spaces (feels inefficient), but that is only a matter of taste, so you can rather ignore it. I am not sure about people who perhaps rely on the output format in scripts (even if it's bad to rely on that specific output format, because the output of psql can be changed to be more suitable for scripts). For multi-column output things have not really changed anyway. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
Csaba Nagy wrote: Is there any chance for psql opening a new session if it's inside a transaction and use that to do whatever querying is needed ? Just something like the control connection on ftp (analogy not very good). That could cause other surprises though (could fail for example due to too many connections open), and I have no idea about psql internals so it might be completely against it's philosophy... Perhaps not multiple connections, but multiple transactions per connection, like Oracle supports, AFAIK. All with a big ;-) of course. I doubt it would be easy to implement that. The assumption one-connection-has-one-transaction is probably pretty deeply burried in many backend components. Has this been changed by the prepared-transactions stuff? I may be mistaken, which would be very positive news. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.0.5 Bug in unique indexes?
Joshua D. Drake wrote: Tom Lane wrote: What's the database's locale? This could be the same problem fixed in 8.0.6, if the locale has weird ideas about what string equality means. lc_collate | C lc_ctype | C You don't user pl/perl, do you -- i.e. I guess you read the latest release notes and the thread here before that? Best Regards, Michael ---(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] 8.0.5 Bug in unique indexes?
Joshua D. Drake wrote: Michael Paesold wrote: You don't user pl/perl, do you -- i.e. I guess you read the latest release notes and the thread here before that? Yes I did. I didn't know that the person was running plPerl. I have verified that they are. We are now going to check if upgrading to 8.0.6 with a deletion of the duplicates and a reindex resolves the issue. I thought I'd ask because this sound so familiar... Best Regards, Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] TRUNCATE, VACUUM, ANALYZE privileges
Stephen Frost wrote: I'm not a particularly big fan of this though because, while I'd like to be able to give TRUNCATE permissions I'm not a big fan of SET RELIABILITY because it would affect PITR backups. As far as I have understood the discussion... with WAL archiving turned on, the whole RELIABILITY changes would be no-ops, no? Just as the CTAS optimization etc. only skip WAL if WAL archiving is turned off. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Bruce Momjian wrote: The --single-transaction mode would apply even if the dump was created using an earlier version of pg_dump. pg_dump has *not* been altered at all. (And I would again add that the idea was not my own) I assume you mean this: http://archives.postgresql.org/pgsql-patches/2005-12/msg00257.php I guess with the ALTER commands I don't see much value in the --single-transaction flag. I am sure others suggested it, but would they suggest it now given our current direction. I just want to add that --single-transaction has a value of it's own. There were times when I wanted to restore parts of a dump all-or-nothing. This is possible with PostgreSQL, unlike many other DBM systems, because people like Tom Lane have invested in ensuring that all DDL is working without implicitly committing an enclosing transaction. Using pg_restore directly into a database, it is not possible to get a single transaction right now. One has to restore to a file and manually added BEGIN/COMMIT. Just for that I think --single-transaction is a great addition and a missing feature. I think more people have a use-case for that. Best Regards, Michael Paesold -- Telefonieren Sie schon oder sparen Sie noch? NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: plperl vs LC_COLLATE (was Re: [HACKERS] Possible savepoint bug)
Tom Lane wrote: Michael Paesold [EMAIL PROTECTED] writes: This is a theory. The whole database was loaded using pg_restore, I still have the original dump so I will have a look at the dump now. The database actually contains some plperl functions. OK, I think I have reproduced the problem. initdb in C locale, then start postmaster with LANG=en_US.UTF-8 in its environment. Then: I had reproduced the problem here with a stripped down dump file from my backup, but your test case is much simpler, as usual. :-) In the meantime, Michael, I'd suggest modifying your postmaster start script to force LANG=C, and then reindexing all indexes you have on text/varchar/char columns. That should get you out of the immediate problem and prevent it from recurring before we have a fix. I had already reindexed all tables in a clean session and have now added export LANG=C to the profile of the postgres unix account. I cannot reproduce the bug after doing so. Thank you for your quick help debugging the problem. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Possible savepoint bug
Tom Lane wrote: I wrote: Michael Paesold [EMAIL PROTECTED] writes: I am seeing a similar unique index bug here... This is PostgreSQL 8.1.1 on RHEL 3, Intel Xeon (i686). It looks like the problem is that index entries are being inserted out of order. After further investigation, it seems that the original sort order of the index was not C-locale, but something else --- I can reproduce the current index ordering except for a small number of new-ish tuples if I sort the data in en_US. We go out of our way to prevent the backend's locale from changing after initdb. Did you do something to override that? No, I am sure I did not do anything to change the locale itentionally. The cluster was initialized with initdb --no-locale... (and this is what it still is). Another theory is that this is a manifestation of the known problem with plperl sometimes changing the backend's locale setting. Is it possible that the index was created in a session that had previously run some plperl functions? This is a theory. The whole database was loaded using pg_restore, I still have the original dump so I will have a look at the dump now. The database actually contains some plperl functions. Restoring to a file I find some perhaps interesting facts perhaps relevant: *) SET check_function_bodies = false; So at least the syntax checking function should not be called. *) Old plperl call handler: The dump from 7.4.x created the public.plperl_call_handler() function, which I only dropped after the full dump was loaded. CREATE FUNCTION plperl_call_handler() RETURNS language_handler AS '$libdir/plperl', 'plperl_call_handler' LANGUAGE c; ALTER FUNCTION public.plperl_call_handler() OWNER TO postgres; CREATE TRUSTED PROCEDURAL LANGUAGE plperl HANDLER plperl_call_handler; *) There is a single plperl function that is only used in a view. (Btw. this view is totally unrelated to the given table and should never be used in the same backend session.) From the points above, I don't think the plperl function should have been called during load. Perhaps I am mistaken and plperl did really override the locale setting. Looking at the environment set for the postgres unix user, which is used to run Postgres, I see that LANG is set to the default value of en_US.UTF-8. So it seems possible that setting LANG to C here, could fix the problem. This still doesn't explain why the initial sort order is wrong here. The creation order in the dump is: CREATE TABLE... (without indexes) COPY ... ALTER TABLE ONLY properties ADD CONSTRAINT pk_properties... Please tell me if you need further information. Best Regards, Michael Paesold ---(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] Possible savepoint bug
Rod Taylor schrieb: On Wed, 2005-11-09 at 14:20 -0500, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: As you can see, we have duplicates within the table (heap) of a primary key value. The index itself only references one of these tuples. Can you put together a test case to reproduce this? It doesn't have to fail every time, as long as it fails once in awhile ... Seems not. I've done millions of iterations of the same type of functionality that happens with these structures and haven't produced a single case. These are fairly low usage structures, so I think I've done about 3 months worth of work, which in production had 20 bad tuples. I tried playing with various delays, vacuum schedules, and number of parallel processes. I am seeing a similar unique index bug here... This is PostgreSQL 8.1.1 on RHEL 3, Intel Xeon (i686). We don't use SAVEPOINTs and we don't use autovacuum. It's quite unlikely that the problem is directly related to VACUUM since that is only run via cron during night hours. The symptoms are duplicate entries in a unique index. billing= \d properties Table billing.properties Column | Type| Modifiers --+---+--- language | character(2) | not null key_name | character varying | not null value| character varying | not null Indexes: pk_properties PRIMARY KEY, btree (language, key_name) Check constraints: tc_properties_key_name CHECK (key_name::text ~ '^[a-zA-Z][a-zA-Z0-9_.]+$'::text) tc_properties_language CHECK (language = 'de'::bpchar OR language = 'en'::bpchar) billing= reindex table properties; ERROR: could not create unique index DETAIL: Table contains duplicated values. billing= select ctid,xmin,xmax,cmin,cmax,language,key_name from properties where key_name = 'enum.server_task_log.status.keys'; ctid | xmin | xmax | cmin | cmax | language | key_name -++--+--+--+--+-- (31,64) | 505433 |0 |5 |0 | de | enum.server_task_log.status.keys (31,57) | 505261 |0 |7 |0 | de | enum.server_task_log.status.keys (31,56) | 505261 |0 |5 |0 | en | enum.server_task_log.status.keys (3 rows) The state is the effect of only UPDATEs of the rows after a SELECT ... FOR UPDATE in the same transaction. It happend twice right now but I deleted the other rows... the table should still contain the data. I have disabled scheduled vacuums for now. I could send the index and table files off-list. This is the only effected table right now. It is not updated frequently but is rather static. I upgraded to 8.1.1 around Dec 21, there should have been near zero updates since then until today. Perhaps it's a problem with multi-column unique indexes? Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Anyone for adding -fwrapv to our standard CFLAGS?
Tom Lane wrote: It seems that gcc is up to some creative reinterpretation of basic C semantics again; specifically, you can no longer trust that traditional C semantics of integer overflow hold: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=175462 While I don't think we are anywhere using exactly the same trick that the referenced mysql code is using, it certainly seems likely to me that a compiler that is willing to replace x 0 -x 0 with false might be able to break some of the integer overflow checks we do use. I think we need to add -fwrapv to CFLAGS anytime the compiler will take it, same as we recently started doing with -fno-strict-aliasing. What about this one from the bug (by Jakub Jelinek): Now, -fwrapv can be an answer if you are unwilling to fix the broken code, but be prepared that the performance will be terrible, as GCC will not be able to optimize many loops in a way that it is allowed by the standard. Performance will be terrible does not sound that good. Is there any other GCC guy you could talk about this? I don't think GCC==Jakub Jelinek? What do others suggest? There should be a portable way to detect overflow, no? Best Regards, Michael Paesold [Tom, I removed you from CC: because your spam filter tends to eat my mail; you should get it through the lists, though.] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Regression test horology failure
Attached are regression diffs for 7.4.10, compiled from source on RHEL 3 U6 (gcc 3.2.3 20030502, glibc-2.3.2-95.37) using: make distclean ./configure '--with-perl' '--prefix=/usr/local/postgresql-7.4.10' make make install make check The tests fail for PST/PDT in 2034. Looking at the buildfarm there is no other RHEL 3 system building the 7.4 branch at the moment. The same is true for 7.3.12 and 7.4.7 by the way. Is this a local problem in my glibc/tz libraries? I am not really worried because I will upgrade to 8.1 shortly, but understanding the problem would be a good thing. Tom, you are building 7.3 for RedHat, do you see any similar regression failures? Best Regrads, Michael Paesold *** ./expected/horology.out Thu Sep 25 08:58:06 2003 --- ./results/horology.out Tue Dec 13 15:25:07 2005 *** *** 1755,1765 | Tue Dec 31 17:32:01 1996 PST | @ 34 years| Tue Dec 31 17:32:01 2030 PST | Fri Dec 31 17:32:01 1999 PST | @ 34 years| Sat Dec 31 17:32:01 2033 PST | Sat Jan 01 17:32:01 2000 PST | @ 34 years| Sun Jan 01 17:32:01 2034 PST ! | Wed Mar 15 02:14:05 2000 PST | @ 34 years| Wed Mar 15 02:14:05 2034 PST ! | Wed Mar 15 03:14:04 2000 PST | @ 34 years| Wed Mar 15 03:14:04 2034 PST ! | Wed Mar 15 08:14:01 2000 PST | @ 34 years| Wed Mar 15 08:14:01 2034 PST ! | Wed Mar 15 12:14:03 2000 PST | @ 34 years| Wed Mar 15 12:14:03 2034 PST ! | Wed Mar 15 13:14:02 2000 PST | @ 34 years| Wed Mar 15 13:14:02 2034 PST | Sun Dec 31 17:32:01 2000 PST | @ 34 years| Sun Dec 31 17:32:01 2034 PST | Mon Jan 01 17:32:01 2001 PST | @ 34 years| Mon Jan 01 17:32:01 2035 PST | Sat Sep 22 18:19:20 2001 PDT | @ 34 years| Sat Sep 22 18:19:20 2035 PDT --- 1755,1765 | Tue Dec 31 17:32:01 1996 PST | @ 34 years| Tue Dec 31 17:32:01 2030 PST | Fri Dec 31 17:32:01 1999 PST | @ 34 years| Sat Dec 31 17:32:01 2033 PST | Sat Jan 01 17:32:01 2000 PST | @ 34 years| Sun Jan 01 17:32:01 2034 PST ! | Wed Mar 15 02:14:05 2000 PST | @ 34 years| Wed Mar 15 02:14:05 2034 PDT ! | Wed Mar 15 03:14:04 2000 PST | @ 34 years| Wed Mar 15 03:14:04 2034 PDT ! | Wed Mar 15 08:14:01 2000 PST | @ 34 years| Wed Mar 15 08:14:01 2034 PDT ! | Wed Mar 15 12:14:03 2000 PST | @ 34 years| Wed Mar 15 12:14:03 2034 PDT ! | Wed Mar 15 13:14:02 2000 PST | @ 34 years| Wed Mar 15 13:14:02 2034 PDT | Sun Dec 31 17:32:01 2000 PST | @ 34 years| Sun Dec 31 17:32:01 2034 PST | Mon Jan 01 17:32:01 2001 PST | @ 34 years| Mon Jan 01 17:32:01 2035 PST | Sat Sep 22 18:19:20 2001 PDT | @ 34 years| Sat Sep 22 18:19:20 2035 PDT == ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Regression test horology failure
Tom Lane schrieb: Michael Paesold [EMAIL PROTECTED] writes: The tests fail for PST/PDT in 2034. This probably indicates that you've got TZ data reflecting the new US DST rules. We have not updated the pre-8.0 regression test results to deal with that. You're right as far as I can tell. 8.1 has the expected output. The failing tests are different instances of this basic example: Expected: Wed Mar 15 08:14:01 2000 PST + 34 years = Wed Mar 15 08:14:01 2034 PST Here (and PostgreSQL = 8.x): Wed Mar 15 08:14:01 2000 PST + 34 years = Wed Mar 15 08:14:01 2034 PDT Still, I don't understand why March 15th should be day light saving time. But hey, I don't live in the PST8PDT time zone. ;-) Thanks for your answer. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Optional postgres database not so optional in 8.1
Martijn van Oosterhout wrote: What distribution? I've never seen this postgres database you speak of. It certainly not on any systems I've used. It's new in 8.1 and is used as the default connection database for createdb, etc. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Optional postgres database not so optional in 8.1
Joshua D. Drake wrote: psql -l; ignores -dtemplate1, and createdb doesn't have such an option. I can't test createdb here but on the psql... what happens if you just: psql -Upostgres template1? It's the '-l' option (list all databases) that does not honor the database given on the command line. This does not work, if the postgres database is dropped in 8.1: psql -l template1 psql -l -d template1 of course psql template1 will just work fine. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PG_DUMP and table locking in PG7.4
Yann Michel wrote: Hi, On Wed, Nov 16, 2005 at 01:25:43PM +0800, Christopher Kings-Lynne wrote: I belive a lock is acquired on every table including inherited children BEFORE doing ANY dumping. To allow pg_dump to get a consistent dump snapshot. Well, thanks for all the answers. Are the locks then released once they are not needed any more like in 2PC? That should still leaqve the taken snapshot of the released table in a consistent state but might enable other transactions to work on that one table once it is released. I'm asking, because we have a bigger datawarehouse and dump the data for a backup every night. Unfortunately, the backup now takes realy long. That means, other processes that insert data will have to wait which is sometime really long! I was searching for a way to avoid this. No, a share lock on the table does not mean that other transactions can't insert or update anymore. What it does, is to prevent tables from being dropped or truncated. To get a consistent snapshot of the data in the tables itself, pg_dump just uses the SERIALIZABLE transaction isolation level. This is implemented via MVCC in postgresql, which makes sure that neither readers nor writers will block waiting. So only if you do full table locks in your application (using LOCK TABLE statements), you will suffer from pg_dump backups. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Interval aggregate regression failure (expected seems
Gregory Maxwell wrote: On 07 Nov 2005 14:22:37 -0500, Greg Stark [EMAIL PROTECTED] wrote: IIRC, floating point registers are actually longer than a double so if the entire calculation is done in registers and then the result rounded off to store in memory it may get the right answer. Whereas if it loses the extra bits on the intermediate values (the infinite repeating fractions) that might be where you get the imprecise results. Hm. I thought -march=pentium4 -mcpu=pentium4 implies -mfpmath=sse. SSE is a much better choice on P4 for performance reasons, and never has excess precision. I'm guessing from the above that I'm incorrect, in which case we should always be compiled with -mfpmath=sse -msse2 when we are complied -march=pentium4, this should remove problems caused by excess precision. The same behavior can be had on non sse platforms with -ffloat-store. Just for the record (and those interested): using 'CFLAGS=-O2 -mcpu=pentium4 -march=pentium4 -mfpmath=sse -msse2' actually passes the regression tests. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Interval aggregate regression failure (expected seems
Tom Lane wrote: I wrote: Michael Paesold [EMAIL PROTECTED] writes: I am definatly not going to use -march=pentium4 in any production system. Should I open a bug report with RedHat (gcc vendor)? Yeah, but they'll probably want a smaller test case than Postgres fails its regression tests :-( I have just confirmed that the problem still exists in FC4's current compiler (gcc 4.0.1, gcc-4.0.1-4.fc4), which probably will boost up the priority of the complaint quite a long way in Red Hat's eyes. I've also confirmed that the problem is in interval_div; you can reproduce the failure with select '41 years 1 mon 11 days'::interval / 10; [snip] Would you mind reporting this to RedHat Bugzilla? I believe a bug report from you would have more weight then mine, because you actually understand what's going on here. :-) Otherwise I am going to do do my best... Best Regards, Michael Paesold ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Interval aggregate regression failure (expected seems wrong)
Using both PostgreSQL 8.1.0 and CVS current of Nov 7, 9:00 am CET I get a regression failure in the interval tests. I am no export for the interval type, but the expected 9 days 28 hours seem wrong, don't they? The actual value seems to be the same. Is it possible that this is broken on the platform where the expected results were generated? *** ./expected/interval.out Tue Oct 25 19:13:07 2005 --- ./results/interval.out Mon Nov 7 09:11:27 2005 *** *** 218,224 select avg(f1) from interval_tbl; avg - ! @ 4 years 1 mon 9 days 28 hours 18 mins 23 secs (1 row) -- test long interval input --- 218,224 select avg(f1) from interval_tbl; avg - ! @ 4 years 1 mon 10 days 4 hours 18 mins 23 secs (1 row) -- test long interval input The last commit to interval.out seems to be this one, and it changed exactly this line. revision 1.14 date: 2005/10/25 17:13:07; author: tgl; state: Exp; lines: +1 -1 Well, this is CVS tip, so there is a chance this is fixed in REL_8_1_STABLE which has a 1.14.0.2. At least the release tarball should be rebuilt, no? Sorry, if this is just noise. Just wanted to be sure you know about it. Best Regards, Michael ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Interval aggregate regression failure (expected seems
Michael Glaesemann wrote: On Nov 7, 2005, at 17:24 , Michael Paesold wrote: Using both PostgreSQL 8.1.0 and CVS current of Nov 7, 9:00 am CET I get a regression failure in the interval tests. I am no export for the interval type, but the expected 9 days 28 hours seem wrong, don't they? The actual value seems to be the same. Is it possible that this is broken on the platform where the expected results were generated? What platform are you testing on? With or without integer-datetimes? Ok, forgot. This is *without* integer-datetimes, RHEL 3 (Linux 2.4.21, glibc 2.3.2, gcc 3.2.3 20030502) on i686 (Xeon without x86-64). I just ran make check on for PostgreSQL 8.1.0 on Mac OS X 10.4.3 [snip] I didn't have any regression failures. I'd also expect we'd see a lot more failures on the build farm if it were the case that it was broken just on the platform that the expected results were generated on. From a quick look at the current build farm failures on HEAD and REL8_1_STABLE, it doesn't look like any of the failures are failing here. I just started to wonder about buildfarm, too, but found that most build farm members have --enable-integer-datetimes. Could that be an explanation? Is it possible that the code is wrong with --enable-integer-datetimes? So what do you have in results/interval.out? @ 4 years 1 mon 9 days 28 hours 18 mins 23 secs seems wrong to me, no? Tom wrote for that commit: revision 1.14 date: 2005/10/25 17:13:07; author: tgl; state: Exp; lines: +1 -1 Remove justify_hours call from interval_mul and interval_div, and make some small stylistic improvements in these functions. Also fix several places where TMODULO() was being used with wrong-sized quotient argument, creating a risk of overflow --- interval2tm was actually capable of going into an infinite loop because of this. Perhaps it is an intended behavior? If so, it still fails without integer-datetimes. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Interval aggregate regression failure (expected seems
Michael Paesold wrote: On Nov 7, 2005, at 17:24 , Michael Paesold wrote: Using both PostgreSQL 8.1.0 and CVS current of Nov 7, 9:00 am CET I get a regression failure in the interval tests. I am no export for the interval type, but the expected 9 days 28 hours seem wrong, don't they? The actual value seems to be the same. Is it possible that this is broken on the platform where the expected results were generated? Perhaps it is an intended behavior? If so, it still fails without integer-datetimes. Well, no, it also fails with integer-datetimes for me in the same way. pg_config output below. And yes, I did cvs up; make distclean; ./configure... ; make ; make install ; make check. Could this be DST-related? I thought plain interval was not affected by DST changes. BINDIR = /usr/local/postgresql-8cvs/bin DOCDIR = /usr/local/postgresql-8cvs/doc INCLUDEDIR = /usr/local/postgresql-8cvs/include PKGINCLUDEDIR = /usr/local/postgresql-8cvs/include INCLUDEDIR-SERVER = /usr/local/postgresql-8cvs/include/server LIBDIR = /usr/local/postgresql-8cvs/lib PKGLIBDIR = /usr/local/postgresql-8cvs/lib LOCALEDIR = MANDIR = /usr/local/postgresql-8cvs/man SHAREDIR = /usr/local/postgresql-8cvs/share SYSCONFDIR = /usr/local/postgresql-8cvs/etc PGXS = /usr/local/postgresql-8cvs/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--prefix=/usr/local/postgresql-8cvs' '--with-pgport=54321' '--with-perl' 'CFLAGS=-O2 -mcpu=pentium4 -march=pentium4' '--enable-casserts' '--enable-debug' '--enable-integer-datetimes' CC = gcc CPPFLAGS = -D_GNU_SOURCE CFLAGS = -O2 -mcpu=pentium4 -march=pentium4 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -fno-strict-aliasing -g CFLAGS_SL = -fpic LDFLAGS = -Wl,-rpath,/usr/local/postgresql-8cvs/lib LDFLAGS_SL = LIBS = -lpgport -lz -lreadline -lncurses -lcrypt -lresolv -lnsl -ldl -lm -lbsd VERSION = PostgreSQL 8.2devel Best Regards, Michael Paesold ---(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] Interval aggregate regression failure (expected seems
Michael Glaesemann wrote: So what do you have in results/interval.out? @ 4 years 1 mon 9 days 28 hours 18 mins 23 secs seems wrong to me, no? select avg(f1) from interval_tbl; avg - @ 4 years 1 mon 9 days 28 hours 18 mins 23 secs (1 row) The point of the change to the interval datatype in 8.1 is to keep track of months, days, and seconds (which in turn are represented as hours, minutes and seconds). Previous releases tracked only months and seconds. This has advantages for using intervals with dates and timestamps that involve daylight saving time changes. Admittedly, it looks odd at first, but it falls out of the change in behavior of the interval datatype. There are two new functions, justify_days and justify_hours, that you can use to put intervals into more traditional forms. http://developer.postgresql.org/docs/postgres/functions-datetime.html Thank you very much for the insight. Doesn't explain why you're getting a regression failure though. Well, I have something now. It seems to be a compiler/optimization issue. I wrote: CFLAGS = -O2 -mcpu=pentium4 -march=pentium4 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -fno-strict-aliasing -g I had set CFLAGS to -O2 -mcpu=pentium4 -march=pentium4. I have been using these settings for testing PostgreSQL tip for some time now and never had any problems. Removing the cpu and architecture optimization part changes the behavior of the interval aggrate, so the results/interval.out now also looks like the expected output. select avg(f1) from interval_tbl; avg - @ 4 years 1 mon 9 days 28 hours 18 mins 23 secs (1 row) Switching -mcpu=pentium4 -march=pentium4 back on, results in wrong output. This is 100% reproducable. Can somebody with more knowledge explain why the compiler should stumble over just this? Pure luck? I have tested these combination of CFLAGS: -O2 OK -O2 -mcpu=i686 -march=i686 OK (good, RPMS are built with these) -O2 -mcpu=pentium4 -march=i686 OK -O2 -mcpu=pentium4 -march=pentium4 fails I am definatly not going to use -march=pentium4 in any production system. Should I open a bug report with RedHat (gcc vendor)? Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] expanded \df+ display broken in beta4
Tom Lane wrote: Michael Paesold [EMAIL PROTECTED] writes: Robert Treat wrote: ISTM even a GUC to enable/disable would have been better scheme than what we have now; we are basically leaving no options for those who found the old behavior useful, while what we had before would at least let people switch back and forth. I think Robert is right here and the new behaviour is a step backwards. Should we revert the patch for the time being, and take another go at it in 8.2? As the last option, if there is no quick fix, I'd say yes. Better than making an incomplete change now and revert that later to get a better solution. Is it practical to have the old behavior for \df (and anything else with particularly wide output) while still forcing \x off for \d? I think there are quite some ways to handle the problem: 1) \x to get the new behaviour, \xx or \x+ to get the new one. 2) Have \x+ or something automatically switch based on screen width. Other suggestions... Well, unfortunately, I guess now is not the time to discuss this for 8.1. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] expanded \df+ display broken in beta4
Bruce Momjian wrote: Michael Paesold wrote: Tom Lane wrote: Michael Paesold [EMAIL PROTECTED] writes: Robert Treat wrote: ISTM even a GUC to enable/disable would have been better scheme than what we have now; we are basically leaving no options for those who found the old behavior useful, while what we had before would at least let people switch back and forth. I think Robert is right here and the new behaviour is a step backwards. Should we revert the patch for the time being, and take another go at it in 8.2? One idea is to hack \d not to honor \x, and let the others honor it. That would probably hit most of the cases people will use in 8.1. In fact, \d is pretty special because it is more of a group of outputs, unlike \df, which is a single table output. +1 from me. That seems like a workable compromise and should probably meet the needs of the author of the patch to change the \x behavior. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] expanded \df+ display broken in beta4
Robert Treat wrote: On Tue, 2005-10-25 at 14:51, Bruce Momjian wrote: Good point. We modified 8.1 so backslash commands do not honor \x because things like \d look silly in \x, but \df+ looks better with \x, no question. Ideally I think \x should allow three modes, on, off, and auto, with auto doing \x if the row output is wider than the screen. If we had this, backslash commands could be auto, or we can set all queries to auto by default. ISTM even a GUC to enable/disable would have been better scheme than what we have now; we are basically leaving no options for those who found the old behavior useful, while what we had before would at least let people switch back and forth. I think Robert is right here and the new behaviour is a step backwards. Best Regards, Michael Paesold ---(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] [PATCHES] [BUGS] BUG #1962: ECPG and VARCHAR
Tom Lane wrote: Michael Paesold [EMAIL PROTECTED] writes: Can you remember regressions in stable branches in the past? Yes. Relax. If this were a data-corruption-in-the-backend issue, I might feel that it mandates an immediate re-release. But it isn't and it doesn't. You'll note that Michael M. himself didn't bother to backpatch the fix on first discovery ... so why should anyone else take it more seriously? OK. :-) Michael Paesold ---(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] [PATCHES] [BUGS] BUG #1962: ECPG and VARCHAR
[moved to hackers] Is this a regression in the stable branches? If so, shouldn't we do a new release rather immediately? What do others think about this situation? Can you remember regressions in stable branches in the past? How were those it handled? I think waiting for months (i.e. for the next major bug fixes) is not the correct answer here. IMHO, the latest released version should be known best in all components. Best Regards, Michael Paesold Bruce Momjian wrote: Michael Fuhr wrote: On Thu, Oct 13, 2005 at 09:49:20AM -0600, Michael Fuhr wrote: ecpg in 8.0.4 seems not to like the macros. I get the same error, but not if I do this: VARCHAR t[256]; VARCHAR o[256]; ecpg in 8.1beta3 works either way. This appears to be the guilty commit, which was made to 7.4, 8.0, and HEAD (8.1): http://archives.postgresql.org/pgsql-committers/2005-08/msg00266.php It was recently fixed in HEAD only: http://archives.postgresql.org/pgsql-committers/2005-10/msg00043.php Good catch! I have backpatched these fixes to the 8.0 and 7.4 branches as you suggested, (identical) patches attached. The big problem is that we might not make releases on these branches for months, so anyone needing the fix should download CVS for those branches. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Updated documentation for new sequence binding
Bruce Momjian wrote: pgman wrote: I have marged Tom's description of the new sequence binding with text I was working on. I modified it to follow the existing we used to do X, now we do Y pattern in the surrounding entries: http://candle.pha.pa.us/tmp/pgsql/release.html#RELEASE-8-1 Sorry, this is a better URL: http://candle.pha.pa.us/main/writings/pgsql/sgml/release.html#RELEASE-8-1 Btw. I think the header Add proper sequence function dependencies for DEFAULT is in the wrong font, i.e. it's all monospace. Best Regards Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Proposed patch for sequence-renaming problems
Tom Lane wrote: However, we could certainly add the NEXT VALUE FOR syntax if that will satisfy your concern about syntax. Since the NEXT VALUE FOR syntax has a special meaning, would it be better to support the oracle-style syntax sequence.nextval for now (and use the ::regclass for this)? I am not sure how easy that is considering schema.sequence.nextval. Just a thought. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] State of support for back PG branches
Tom Lane wrote: To my mind the main rationale for continuing to support 7.2 is that it was the last pre-schema release, and so people whose apps haven't yet been fixed to cope with schemas will be on their own once we drop it. While each release has some portability gotchas, I don't think there have been any quite that big since then. If we drop support for 7.2, it wouldn't be out of the question for us to drop 7.3 and 7.4 too (at least not from where I sit ... I'm sure some will differ). Yes. For one of our application that makes massive use of triggers the transition from 7.2 to 7.3 was less painful than 7.4 to 8.0. Many of the triggers were written with the assumption that foreign key checks were effectively delayed until the end of the calling statement. Just another opinion. Nevertheless if it's feasible to support 7.4 for a while, I would recommend it. Also from a marketing statepoint -- having back branches supported for a visible amount of time increases people's confident in PostgreSQL and it's stability. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] expected authentication request from server, but
Michal Jeczalik wrote: On Mon, 26 Sep 2005, Martijn van Oosterhout wrote: On Mon, Sep 26, 2005 at 09:59:53AM +0200, Micha? J?czalik wrote: Connection with psql works OK. Connecting from different (older) hosts with DBI/PHP works perfect as well. Something must have changed in recent versions of libpq and now it doesn't work. I looked into source code and found that the server's message at the very start of conversation appears to be something else than the libpq expects. But how to fix it?! Has the protocol change or what? Umm, I think the protocol version is up to 3 or 4 now. I think libpq supports all the way back to 7.0, I don't know if anyone tests earlier than that. If you really need this to work, I suggest pulling the libpq from that release (CVS or tarball) or slightly later and installing that on the new machines. Heh, that's what I'm currently doing, but it's not so easy to compile 6.4 on a new system. configure states that the compiler cannot create executables, but I ripped this check out of it. Anyway, something goes wrong while generating the Makefile: Try to compile 7.0 or 7.1, those versions will probably still support the 6.4 wire protocol. I am sure someone with more historical knowledge (or reading the release notes back to 6.4) can exactly tell you the latest version compatible with 6.4. It was already said, but please -- upgrade. Any version before 7.2 is going to eat your data after a certain amount of transactions. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] What has happened to pgxs?
Thomas Hallgren wrote: I'm using a pre-compiled installation of PostgreSQL. All I want to do is use pgxs to be able to compile PL/Java. There is other stuff that seems strange to me. Why do you append 'postgresql' to the include directories further down in the file? I had to remove that in order to compile. Perhaps this is just a problem with the pre-compiled installation? Perhaps they are using strange configure options and than relocate the stuff with the installer? I don't know much about Windows stuff, just a wild guess. I would file a bug report in the pginstaller project on pgfoundry. pgxs should work. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] What has happened to pgxs?
Thomas Hallgren wrote: Sorry, that conclusion was wrong. What happens is: 1. I change the prefix in Makefile.global to say, C:/Progra~1/PostgreSQL/8.1-beta2 (this is the default for the installer). 2. I compile. That triggers the append of 'postgresql' on all directories since my install location *does not* contain the word 'postgres' nor 'pgsql'. ... Thomas Hallgren wrote: There's another issue with the Makefile.global.in that I feel should be addressed here. The file contains a lot of entries like: ifeq $(findstring pgsql, $(pkgincludedir)) ifeq $(findstring postgres, $(pkgincludedir)) override pkgincludedir := $(pkgincludedir)/postgresql endif endif Bruce, others, could this comparision be made case-insensitive at least, so that it at least finds PostgreSQL and does not append postgresql in that case? That would be the least invasive fix for the Windows case, I guess, where the default installation directory contains PostgreSQL. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Parser bug results in ambiguous errors/behaviour
Gavin Sherry wrote:
A bug/short coming in the parser leads to some pretty ambiguous errors
and/or foot shooting. Consider the following:
template1=# create table foo(i int, b bool, t text);
CREATE TABLE
template1=# insert into foo values(1, 'f', 'foo');
INSERT 0 1
template1=# update foo set i=2,b='t' and t='bar' where i=1;
UPDATE 1
Read it as:
update foo set=2, b=('t' and t='bar') where i=1;
This works because: 't' can be translated to boolean true, t='bar' to
boolean false, (true and false) becomes false, of course.
template1=# select * from foo;
i | b | t
---+---+-
2 | f | foo
(1 row)
Seems to be the correct result, at least if the syntax without parenthesis
is allowed by the SQL spec.
It gets more interesting:
template1=# update foo set b='t', i=2 and t='bar' where i=1;
ERROR: argument of AND must be type boolean, not type integer
update foo set b='t', i=(2 and t='bar') where i=1;
This is supposed to fail. There is no (at least implicit) cast from integer
to boolean. So 2 cannot be converted to a boolean value and the boolean AND
operator fails.
It comes down to the question if the query is valid syntax in the first
place. The answers PostgreSQL gives are correct nevertheless.
Best Regards,
Michael Paesold
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Spinlocks, yet again: analysis and proposed patches
Tom Lane wrote: I guess what this means is that there's no real problem with losing the cache line while manipulating the LWLock, which is what the patch was intended to prevent. Instead, we're paying for swapping two cache lines (the spinlock and the LWLock) across processors instead of just one line. But that should at worst be a 2x inflation of the time previously spent in LWLockAcquire/Release, which is surely not yet all of the application ;-). Why the heck is this so bad? Should we expect that apparently minor changes in shared data structures might be costing equivalently huge penalties in SMP performance elsewhere? Unfortunately I don't have root on the Opteron and can't run oprofile. But I'd really like to see some oprofile stats from these two cases so we can figure out what in the world is going on here. Can anyone help? I will try the patch here and see if it gives the same result. If so I could try to run with oprofile if you can give me a quick start. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Bug with cursor declaration in plpgsql? (Repost)
[Note: reposted because it didn't show up on the list after a day]
I have used to declare cursors in the DECLARE section of a PL/pgSQL
function. The example here seems to be broken in CVS tip:
CREATE FUNCTION test () RETURNS void AS '
DECLARE
credit_cursor CURSOR (p_account integer, p_reference integer) FOR
SELECT * FROM booking
WHERE account_id=p_account AND reference=p_reference
AND unassigned_amount = amount AND amount 0 AND side=''credit''
AND position_closed AND type NOT IN (''RC'', ''RP'')
ORDER BY journal_id ASC;
BEGIN
END
'
LANGUAGE PLPGSQL;
I get:
ERROR: syntax error at or near , at character 237
LINE 9: credit_cursor CURSOR (p_account integer, p_reference integ...
The same function works perfectly well in 7.4.8 and 8.0.3.
A bug?
Best Regards,
Michael Paesold
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Spinlocks, yet again: analysis and proposed patches
Tom Lane wrote: But the cmpb instruction in the 8.0 version of TAS would have done that, and I think we've already established that the cmpb is a loss on most machines (except maybe single-physical-CPU Xeons). Note that this was a regular Pentium 4 system, not a Xeon. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
