Re: [HACKERS] [PATCHES] Forcing current WAL file to be archived
Albe Laurenz wrote: Simon Riggs wrote: Patch included to implement xlog switching, using an xlog record processing instruction and forcibly moving xlog pointers. 1. Happens automatically on pg_stop_backup() Oh - so it will not be possible to do an online backup _without_ forcing a WAL switch any more? Well, previously, you would have always had to simulate a wal switch, by working out which is the current wal file and copying that. Otherwise your online backup wouldn't be complete. What Simon is describing sounds like a big step forward from that situation. It should let me delete half the code in my pitr backup/failover scripts. Definitely a Good Thing. Laurenz Albe Tim begin:vcard fn:Tim Allen n:Allen;Tim email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Worthwhile optimisation of position()?
Thomas Hallgren wrote:
Christopher Kings-Lynne wrote:
Is it worth allowing this:
select count(*) from users_users where position('ch' in username) = 0;
To be able to use an index, like:
select count(*) from users_users where username like 'ch%';
At the moment the position() syntax will do a seqscan, but the like
syntax will use an index.
You must compare position('ch' in username) to '%ch%' instead of 'ch%'
in this respect.
The position function must look for 'ch' everywhere in the string so
there's no way it can use an index.
I think the '= 0' bit is what Chris was suggesting could be the basis
for an optimisation.
Tim
--
---
Tim Allen [EMAIL PROTECTED]
Proximity Pty Ltd http://www.proximity.com.au/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [ADMIN] Major Problem, need help! Can't run our website!
ITS ONT Alcazar, Jose Aguedo C wrote: Anyone! Before anything else, I have no background in PostgreSQL. But I have a little knowledge in Linux. We used postgreSQL to run one of our website. It runs in Redhat Linux 7.3. Our System Administrator, who used to maintain this server, had resigned and didn't have a proper documentation on how to maintain this server. Right now, our NEW System Administrator is clearing some logs in /var/lib/pgsql/data/pg_xlog in able to free some space in the /var file system. It used to work before, but now, its not working anymore. Information below is the message we are encountering when we are trying to connect to the website. Please, ANYONE, help us! We've seen reports of people firing this particular foot-gun before, haven't we? Would it make sense to rename pg_xlog to something that doesn't sound like it's just full of log files? Eg pg_wal - something where the half-educated will have no idea what it is, and therefore not think they know what they can do with it. Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 4D Geometry
Peter Eisentraut wrote: Chris Traylor wrote: Configure options are generally a pain in the neck, Granted. Especially, if all the ifdefs start making the source hard to read, but they are a viable compile-time way to allow the user to make the decision for themselves. This missing piece of information here is that 98.6% of our users never compile the source code, so that decision will have to be made by the packager who will always use the option that is acceptable to the plurality of the users. That is why we have removed most feature-related compile-time choices and are very hesitant to add new ones. The other point to be made is that every such compile-time option bifurcates the postgres universe into two mutually-incompatible sections. The postgres community has enough of a challenge supporting the one version of the database - there's no point in making things harder. Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Autovacuum in the backend
Josh Berkus wrote: Alvaro, coffee-with-cream vacuums. I tried this and now my Hoover makes this horrible noise and smokes. ;-) Probably related to the quality of American coffee ;). All: Seriously, all: when I said that users were asking for Autovac in the backend (AVitB), I wasn't talking just the newbies on #postgresql. I'm also talking companies like Hyperic, and whole groups like the postgresql.org.br. This is a feature that people want, and unless there's something fundamentally unstable about it, it seems really stupid to hold it back because we're planning VACUUM improvements for 8.2. AVitB has been on the TODO list for 2 versions. There's been 2 years to question its position there. Now people are bringing up objections when there's no time for discussion left? This stinks. Complete agreement from me. Incremental improvements are good - pointing out that there are some other incremental improvements that would also be good to make is not an argument for delaying the first set of incremental improvements. In our case, we want to be able to install postgres at dozens (ideally hundreds... no, thousands :) ) of customer sites, where the customers in general are not going to have anyone onsite who has a clue about postgres. The existing contrib autovacuum gives a good solution to setting things up to maintain the database in a reasonable state of health without need for further intervention from us. It's not perfect, of course, but if it means the difference between having to unleash our support team on a customer once a month and once a year, that's a good deal for us. Having it integrated into the backend will make it much easier for us, we (hopefully...) won't have to fiddle with extra startup scripts, and we'll have one fewer point of failure (eg some customer might accidentally turn off the separate pg_autovacuum daemon). Being able to customise the autovacuum parameters on a per-table basis is also attractive. Just my AUD0.02. I realise that keeping _our_ customers happy is not necessarily anyone else's priority. I'd like to be able to invest some coding time, but can't. I haven't even gotten around to completing Gavin's survey form (sorry Gav, I'll get to it soon, I hope! :)), so I can't demand to be listened to. But for what it's worth, Alvaro, please keep going, don't be dissuaded. Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] LGPL
John Hansen wrote: So, what's the story with readline? There is a greyish clause in the GPL that says that linking to things normally distributed with your operating system doesn't incur the obligations of the GPL. So assuming that readline, which is GPL, is normally distributed with your operating system, you are at liberty to use it without being bound to apply the GPL to your code. Another example is the Linux kernel - you (or at least, many do, even if not you personally, I don't know your predilections :)) use it all the time, and it's GPL, but using it doesn't mean that all your apps are subject to the GPL. BTW the GPL is all about distribution - for your own private use on your own computer, you can link whatever you like to whatever you like - the issues crop up when you try to distribute such a system to anyone else, you then become obliged to give that someone else the rights that the GPL requires. So linking to GPL (or LGPL) code is not acceptable for the PostgreSQL project itself, but might be acceptable for you personally, depending on what you're doing. Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Views, views, views! (long)
Josh Berkus wrote: PG hackers, [snip] What We Need From Hackers -- (other than patch approval, that is) As stated above, these system views, once incorporated into a pg distribution, are likely to be with us *forever*. As such, we really can't afford to do major refactoring of the column names and structure once they're released. So it's really, really, important for everyone on hackers to look over the definitions below and find stuff that we've missed or doesn't make any sense. Also, we'd like to know about 8.1 changes that affect these views. This all looks good to me, from a quick read through. I don't claim to have examined the details, but the general idea is definitely something that would be very worth having. A nice thing to add would be a more human-comprehensible view of the pg_locks table. I keep meaning to write a view for it myself, but haven't ever gotten a round tuit. Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Patent issues and 8.1
Bruce Momjian wrote: pgman wrote: ... What I would like to do is to pledge that we will put out an 8.0.X to address any patent conflict experienced by our users. This would include ARC or anything else. This way we don't focus just on ARC but have a plan for any patent issues that appear, and we don't have to adjust our development cycle until an actual threat appears. This pledge sounds like an open-ended commitment of an infinite number of development hours. I don't think you can pledge to address any patent conflict. There is a limit to the number of tgl-hours in a day :). One advantage we have is that we can easily adjust our code to work around patented code by just installing a new binary. (Patents that affect our storage format would be more difficult. A fix would have to perhaps rewrite the on-disk data.) easily? Maybe, maybe not. I don't think you can assume that the fix to as-yet-unknown patent conflicts is necessarily going to be easy. Even the USPTO occasionally grants patents on things that aren't trivial. Just my AUD0.02, which should probably be worth even less given the size of my contribution to postgresql to date. Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] bug fix request
Christopher Kings-Lynne wrote: Also, sometimes when you copy and paste SQL into a psql window, it executes help on commands for each line, although it doesn't affect the paste. That is also really annoying. I'll add to this email when it happens to me again, cos I tried a few pastes and couldn't reproduce it :/ That last one is probably due to tabs in the pasted text. Happens to me all the time... Chris Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Status report: regex replacement
On Fri, 7 Feb 2003 00:49, Hannu Krosing wrote: Tatsuo Ishii kirjutas N, 06.02.2003 kell 17:05: Perhaps we should not call the encoding UNICODE but UTF8 (which it really is). UNICODE is a character set which has half a dozen official encodings and calling one of them UNICODE does not make things very clear. Right. Also we perhaps should call LATIN1 or ISO-8859-1 more precisely way since ISO-8859-1 can be encoded in either 7 bit or 8 bit(we use this). I don't know what it is called though. I don't think that calling 8-bit ISO-8859-1 ISO-8859-1 can confuse anybody, but UCS-2 (ISO-10646-1), UTF-8 and UTF-16 are all widely used. UTF-8 seems to be the most popular, but even XML standard requires all compliant implementations to deal with at least both UTF-8 and UTF-16. Strong agreement from me, for whatever value you wish to place on my opinion. UTF-8 is a preferable name to UNICODE. The case for distinguishing 7-bit from 8-bit latin1 seems much weaker. Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Proposal: CREATE CONVERSION
On Wed, 10 Jul 2002 08:21, Peter Eisentraut wrote: Hannu Krosing writes: ... I would even reccommend going a step further and storing all 'national' character sets in unicode. Sure. However, Tatsuo maintains that the customary Japanese character sets don't map very well with Unicode. Personally, I believe that this is an issue that should be fixed, not avoided, but I don't understand the issues well enough. Presumably improving the Unicode support to cover the full UTF32 (or UCS4) range would help with this. Last time I checked, PostgreSQL only supports the UCS2 subset of Unicode, ie 16 bits. From the Unicode propaganda I've read, it seems that one of the main goals of the expansion of the range beyond 16 bits was to answer the complaints of Japanese users. Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] MS interview
The Register has an interesting interview with the vp of Microsoft's SQL Server team: http://www.theregister.co.uk/content/53/21003.html Near the end he gets specifically asked about Red Hat Database as a competitive threat, and he responds that he doesn't think anyone can match their investment of 800 professionals to work on SQL Server. Now I'm sure he didn't mean it to sound this way, but what I conclude from that is that you fellows are all an order of magnitude or two more productive than anyone at Microsoft :-). Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vague idea for allowing per-column locale
On Tue, 14 Aug 2001, Tatsuo Ishii wrote: Storing everything as Unicode is not a good idea, actually. First, Unicode tends to consume more storage space than other character sets. For example, UTF-8, one of the most commonly used encoding for Unicode consumes 3 bytes for Japanese characters, while SJIS only consumes 2 bytes. Second, a round trip converison between Unicode and other character sets is not always possible. Third, sorting issue. There is no convenient way to sort Unicode correctly. UTF-16 can handle most Japanese characters in two bytes, afaict. Generally it seems that utf8 encodes European text more efficiently on average, whereas utf16 is better for most Asian languages. I may be mistaken, but I was under the impression that sorting of unicode characters was a solved problem. The IBM ICU class library (which does have a C interface), for example, claims to provide everything you need to sort unicode text in various locales, and uses utf16 internally: http://oss.software.ibm.com/developerworks/opensource/icu/project/index.html The licence is, I gather, the X licence, which presumably is compatible enough with BSD; not that I would necessarily advocate building this into postgres at a fundamental level, but it demonstrates that it can be done. Note that I'm not speaking from experience here, I've just read the docs, and a book on unicode, never actually performed a Japanese-language (or any other non-English language) sort, so no need to take me too seriously :). Tatsuo Ishii Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] analyze strangeness
We are seeing what seems to me to be very peculiar behaviour. We have a schema upgrade script that alters the schema of an existing production database. One of the things we do is create two new indexes. The script then immediately performs a vacuum analyze. The problem is (or was) that this analyze didn't seem to work. Queries performed thereafter would run slowly. Doing another vacuum analyze later on would fix this, and queries would then perform well. We have two approaches that fix this. The first was to just sleep for two seconds between creating the indexes and doing the vacuum analyze. The second was to perform an explicit checkpoint between index creation and vacuum analyze. The second approach seems the most sound, the sleep approach relies too much on coincidence. But both work in our tests so far. However, why is this so? Can analyze not work properly unless the data files have all been fsynced to disk? Does the WAL really stop analyze from working? Even stranger, it turns out that doing the checkpoint _after_ the vacuum analyze also fixes this behaviour, ie queries perform well immediately. This part is _so_ strange that I'm tempted to just not believe it ever happened... except that it seems it did. Any insights? Is this expected behaviour? Can anyone explain why this is happening? We have a workaround (checkpoint), so we're not too concerned, but would like to understand what's going on. Platform is PG7.1.2 on Red Hat Linux 6.2, x86. Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Database corruption in 7.0.3
We have an application that we were running quite happily using pg6.5.3 in various customer sites. Now we are about to roll out a new version of our application, and we are going to use pg7.0.3. However, in testing we've come across a couple of isolated incidents of database corruption. They are sufficiently rare that I can't reproduce the problem, nor can I put my finger on just what application behaviour causes the problems. The symptoms most often involve some sort of index corruption, which is reported by vacuum and it seems that vacuum can fix it. On occasion vacuum reports "invalid OID" or similar (sorry, don't have exact wording of message). On one occasion the database has been corrupted to the point of unusability (ie vacuum admitted that it couldn't fix the problem), and a dump/restore was required (thankfully that at least worked). The index corruption also occasionally manifests itself in the form of spurious uniqueness constraint violation errors. The previous version of our app using 6.5.3 has never shown the slightest symptom of database misbehaviour, to the best of my knowledge, despite fairly extensive use. So our expectations are fairly high :-). One thing that is different about the new version of our app is that we now use multiple connections to the database (previously we only had one). We can in practice have transactions in progress on several connections at once, and it is possible for some transactions to be rolled back under application control (ie explicit ROLLBACK; statement). I realise I haven't really provided an awful lot of information that would help identify the problem, so I shall attempt to be understanding if no-one can offer any useful suggestions. But I hope someone can :-). Has anyone seen this sort of problem before? Are there any known database-corrupting bugs in 7.0.3? I don't recall anyone mentioning any in the mailing lists. Is using multiple connections likely to stimulate any known areas of risk? BTW we are using plain vanilla SQL, no triggers, no new types defined, no functions, no referential integrity checks, nothing more ambitious than a multi-column primary key. The platform is x86 Red Hat Linux 6.2. Curiously enough, on one of our testing boxes and on my development box we have never seen this, but we have seen it several times on our other test box and at least one customer site, so there is some possibility it's related to dodgy hardware. The customer box with the problem is a multi-processor box, all the other boxes we've tested on are single-processor. TIA for any help, Tim -- ------- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] WAL SHM principles
On Thu, 8 Mar 2001, Martin Devera wrote: Bruce Momjian [EMAIL PROTECTED] writes: Unfortunately, this alone is a *fatal* objection. See nearby discussions about WAL behavior: we must be able to control the relative timing of WAL write/flush and data page writes. Bummer. BTW, what means "bummer" ? It's a Postgres-specific extension to the SQL standard. It means "I am disappointed". As far as I can tell, you _may_ use it as a column or table name. :-) Tim -- ------- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
RE: [HACKERS] Why vacuum?
On Thu, 14 Dec 2000, Christopher Kings-Lynne wrote: Plenty of other databases need to be 'vacuumed'. For instance, if you have an ms access database with 5 MB of data in it, and then delete all the data, leaving only the forms, etc - you will be left with a 5MB mdb file still! If you then run 'Compact Database' (which is another word for 'vacuum'), the mdb file will be reduced down to 500k... Ooh... Hope MS Access isn't going to be taken seriously as a benchmark here :-). The same is also true of MapInfo, by the way, but I'm not holding that up as a benchmark either ;-). Chris Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/
