Re: [HACKERS] Case insensitivity, and option?
select * from table where field ILIKE 'blAH'; -- ;-)
is almost as easy :-)
PS: no, don't do this if you want portability. I think the charset
idea's a better one.
Ron
select * from table where lower(field)=lower('BLah') will break
portability too in the sense that many DBs (perhaps all commercial
ones) do not support functional indexes. Hence
lower(field)=lower('BLah') query will not be using index when it runs
on those DBs.
Besides, the developer must always remember to use conversion when
writing queries. This does not sound bad initially but in a big
application with many developers and complex query logic this will
create quite a few annoying, time consuming and difficult to find bugs.
Case insensitive charset definitely sounds like a better idea. Has
anyone done this yet? IMHO, this should be a part of core distribution
since 99.99% of queries do not need case sensitivity.
Thanks
__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Extracting time from timestamp
Why not a cast? template1=# select current_timestamp::time; time - 11:24:22.004207 (1 row) template1=# select current_timestamp::time(0); time -- 11:24:26 (1 row) --- Christopher Kings-Lynne [EMAIL PROTECTED] wrote: phd=# select time(abstime(timestamp 'now')) from bookings; ERROR: parser: parse error at or near abstime at character 13 phd=# select time(timestamp 'now') from bookings; ERROR: parser: parse error at or near timestamp at character 13 phd=# select version(); version Try: select time(abstime(timestamp 'now')) from bookings; select time(timestamp 'now') from bookings; Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] mvcc and lock
Hi all I have read some code on transaction part. When the new transaction starts, it record the snapshot of database containing the current transaction id,etc. So depending on the snapshot , the transaction decide which tuple is visible. But transaction could also be implemented by lock. so I am not sure how the transaction is implemented, by MVCC or Lock, or by both? In my option, when tuple is processed in readonly mode(select), MVCC is enough. but when tuple is changed, lock is used. I am not sure whether the explanation is correct. Thanks for any comments. Best regards. josh ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] IO scheduler vs PostgreSQL performance measurement
Dear PostgreSQL hackers, I am developing a disk IO scheduler for Linux and am aiming to have it included in the stable 2.6 release. Due to its design, performance regressions do appear, and are often more specific to the workload in question than with other schedulers, hence one has to go beyond the generic benchmarks. Databases are one area of difficulty due to multi threaded IO and sync writes. I would appreciate it if you could give me a suggestion for a not-too-difficult to set up or interpret PostgreSQL benchmark with a reasonable running time ( an hour or so) which I can add to my performance regression tests. It would be good if this were to separately measure most common types of PostgreSQL IO work, and from there I would leave specific areas to those interested. I apologise for asking when I could search, however I am interested in something up to date and which developers on this can agree on. Thank you all for your time. Best regards, Nick Piggin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Threaded Python vs. PostGreSQL plpython
Ok, I was a good boy and tried -interfaces first. No answer. I'm trying to get a functioning version of plpython on FreeBSD, while using python from the ports system. The problem is that the ports system build python with thread support. postmaster doesn't have thread support, so when the libpython2.2.so is dynamically loaded, it fails to find the thread functions, and the load fails. The first workaround I tried was to build a custom version of the python library that doesn't have thread support. Given that plpython won't let me import the thread modules, this isn't a problem. However, it does mean I have a copy of libpython2.2.so where they dynamic loader can find it, meaning the linker will find it, meaning that future builds of other embedded software - like apache's mod_python - will wind up with the non-threaded library. This is a bad thing, and I'd like to avoid it. I tried building linking plpython.so against the static library instead of the dynamica library, but that doesn't work properly when loaded. I'm not sure what the problem is. The ideal solution would be to build PostGreSQL with thread support. I'd rather not find out the hard way that this doesn't work. Does anyone know whether or not I can do that without mangling PostGreSQL? Alternatively, getting a statically linked version of plpython built would mean I could delete the shared library. Anyone have any clues on how to go about getting plpython built with a statically linked libpython? If there's another approach that might work, I'd be interested in hearing about that as well. Thanks, mike -- Mike Meyer [EMAIL PROTECTED] http://www.mired.org/consulting.html Independent WWW/Perforce/FreeBSD/Unix consultant, email for more information. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: No index maximum? (was Re: [HACKERS] No merge sort?)
On Mon, Mar 17, 2003 at 11:23:47AM -0600, Taral wrote: Yes, that's exactly it. It's an index _scan_. It should simply be able to read the maximum straight from the btree. Still doesn't work, even with rewritten query. It sort a Limit(Sort(Index Scan)), with 1333 rows being pulled from the index. -- Taral [EMAIL PROTECTED] This message is digitally signed. Please PGP encrypt mail to me. Most parents have better things to do with their time than take care of their children. -- Me pgp0.pgp Description: PGP signature
Re: [HACKERS] SQL99 ARRAY support proposal
Joe Conway wrote: Jason Earl wrote: Actually, I think it was someone else (Joe???) that is doing the leg work, and he was the one choosing explode / implode and getting gruff for it, so I was just stepping in and defending his decision. Oops, my bad. My brain must already think that it is the weekend. My reasoning still stands, though. Whoever writes the code gets to pick the names (assuming, of course, that they can get them past the rest of the PostgreSQL hackers). dons flame proof suit Yup, that was me. I was watching from the sidelines ;-) I'll get on with coding and try to consider all of the input when it comes to picking the names. In the end, it will depend on whatever the guys with commit access will live with, so I'm not going to worry about it too much. /dons flame proof suit My 2 cents: Use split and merge. Avoids the join issue and avoids the implode/explode issue too. :-) -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] IO scheduler vs PostgreSQL performance measurement
Hi, I would appreciate it if you could give me a suggestion for a not-too-difficult to set up or interpret PostgreSQL benchmark with a reasonable running time ( an hour or so) which I can add to my performance regression tests. Check out `make check', a regression test which ships with Postgres, and pgbench, a TPC-like benchmark, in contrib/. Gavin ---(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] Error message style guide
Tom Lane wrote: It was mostly meant as a broad hint not to write open() failed, which can clearly be written more user-friendly without loss of information. For less obvious cases we can use a mixed style. Say 'could not synchronize file %s with disk (fsync failed)'. That tells people at least that it's got something to do with their I/O subsystem. There are some places where we mention the syscall so that we can spell out the exact parameters that were passed, for possible debugging use. But this could probably be pushed to the detail message. So instead of IpcMemoryCreate: shmget(key=%d, size=%u, 0%o) failed: %m (plus a long hint) perhaps Primary:Could not create shared memory segment: %m Detail: Failed syscall was shmget(key=%d, size=%u, 0%o) Hint: as before Seem good? I agree with this, but I believe the detail should really include quite a lot of detail: the file and line number where the error occurred, the error number returned by the syscall (if a syscall is involved), parameters to the function that failed, and so forth. In essence, I think enough detail should be included to make it possible to determine exactly what went wrong and, hopefully, why it went wrong. This stuff might not be terribly useful to the end user, but it'll be of great use to a knowledgeable administrator (one of my pet peeves is software that doesn't tell you why something failed, only that it did). -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] IO scheduler vs PostgreSQL performance measurement
Gavin Sherry wrote: Hi, I would appreciate it if you could give me a suggestion for a not-too-difficult to set up or interpret PostgreSQL benchmark with a reasonable running time ( an hour or so) which I can add to my performance regression tests. Check out `make check', a regression test which ships with Postgres, and pgbench, a TPC-like benchmark, in contrib/. Great, thank you. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Anti-Spam and upgrades ... testing ...
Just making sure that posts make it through after upgrading perl and installing some anti-spam software ... ignore .. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Win32 native port
Thank you, Bruce for info. Best regards Rony - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Ronald Kuczek [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 3:34 PM Subject: Re: [HACKERS] Win32 native port I will start working on it next week. -- - Ronald Kuczek wrote: Hi all, Can someone tell me what happened with Win32 native port ? Discussion bevore beginning of 7.4, TODO entry this time as urgent, but without dash. What does it mean - comes native port with 7.4 or it doesn't ? Thanks for any information. Best regards Rony ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.463 / Virus Database: 262 - Release Date: 17.03.2003 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] cursors outside transactions
On Tue, 2003-03-18 at 19:00, Hiroshi Inoue wrote: Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: The question here is do we want to offer a half-baked solution, recognizing that it's some improvement over no solution at all? Or do we feel it doesn't meet our standards? My question is how would you do this if you need this functionality and you don't have WITH HOLD cursors? ODBC(maybe JDBC also) has cross-transaction result sets (rather than cursors) since long by simply holding all results for a query at client side. JDBC is running into problems with this. Large queries cause out of memory exceptions. Why are cursors outside transactions expected eagerly ? Because it's very hard (almost impossible) for clients to provide a functionality to edit(display/scroll/update etc) large result sets effectively. I don't object to a half-baked solution if there's a prospect of a real solution. However, I've never seen it and I have little time to investigate it unfortunately. regards, Hiroshi Inoue http://www.geocities.jp/inocchichichi/psqlodbc/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Dave Cramer [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
