Re: [GENERAL] Development of cross-platform GUI for Open Source DBs
2006/11/25, Ritesh Nadhani [EMAIL PROTECTED]: Hello all Let me introduce myself first. I am the ex-lead developer of SQLyog (one of the most popular GUI for MySQL which is Windows only and runs on Linux through WINE, more info at http://www.webyog.com). === New Project: wxWidgets based cross-platform GUI for Open Source databases === === Motivation === I have recently shifted to US to study for MS (and hopefully PhD in Univ of Iowa). I have also shifted to Mac OS X as my primary usage machine (after lifetime of Windows devotion). As part of my coursework, I work with MySQL and PostgreSQL extensively. I searched but couldnt find any GUI which has similar power like SQLyog by any means. I have tried (and have tried before also) various GUIs for respective databases but somehow the features in them are restrictive and are not powerful enough for developers like me who writes lots of SQL queries and needs to get things done fast. We can say that we already have enough GUIs for all open source databases (open source as well as commercial) available in the market but are they powerful enough to suffice the needs of an experienced SQL developer as well as newbies. The top three problems with existing GUI managers are: - Most of the usable/powerful GUIs are not open source which is one of the most powerful motivation for us to look for an alternate solutions. - 90% of such GUIs are DB specific. It becomes very hard for developers who work with multiple DBs as part of their work. It forces them to learn different user interface/softwares to work with the respective databases. - Of the few multiple-db GUIs, 99% (or probably 100%) use JDBC/ODBC layer to connect and work with the databases and JAVA or some other kind of high level toolkit/language to develop the GUI. This results in applications being bulky and slow and never able to provide the speed that a low level C/C++ client API provide and are supported by all of the standard databases. A simple to use GUI for all databases === The basic idea behind such a GUI is to develop a small footprint, extremely fast, multilingual, cross platform administrator/development tool for databases. One of the basic requirements thats this GUI will fulfill is to allow a developer to efficiently execute/plan queries and allow an administrator to quickly do jobs like backups/restores etc. with fewest mouse clicks and across different databases. Once the basic architecture has been set, I plan to extend it to support advanced features like MS Access like form development, query builder, scheduled backups, data synchronization, configuration management, replication manager, user manager etc. wxWidgets === Since last couple of years, wxWidgets (formerly wxWindows) has transformed itself into a highly powerful cross-platform GUI library which when compiled gives the native look and feel of the host operating system. This is something which other libraries like Qt, JAVA lack. More info about wxwidgets can be found at: http://www.wxwidgets.org. Yet unnamed DB management environment === I dont have the time nor the resources to do everything by myself. As I see, there are too many things which are best distributed among people/developers who are good at those specific things. E.g. I will require good graphics designer to develop the icons for the tool, web developer to keep the website updated, db specific veterans to best code individual db management code etc. Also, it would be a great way to learn some programming and show it as part of your undergraduate/graduate project development requirement :) Is it viable? === I am not too big a fan of reinventing the wheel or work on a project which will go bust in couple of years? So what I want is from you people a little initial idea and discussion about such a tool. Is it viable? Is it OK to develop such a tool? Will people use it? I am cross posting this to various db mailing lists as well as relevant newsgroups to get maximum idea about it. You can also contact me directly at [EMAIL PROTECTED] if you would be interested. Waiting for your comments. -- Ritesh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq The generalities of the project make sense to me: there really isn't a very good tool that is cross-platform, cross-database and has a lot of advanced options. The feature list of sqlyog is rather comprehensive, although I think that the missing relationship diagram editor leaves an obvious gap in the company of the other advanced features. Do I have time to contribute to the project? Depends. I don't have time to hack the source, but do have a lot of advice to offer (what people call consulting and usually charge a hefty sum for :)). I don't think you'll like/agree with the bulk of my advice, though. First of all, I'm sure that wxWidgets and also Qt and GTK+ are all very nice
Re: [GENERAL] MSSQL to PostgreSQL : Encoding problem
2006/11/23, Arnaud Lesauvage [EMAIL PROTECTED]: Arnaud Lesauvage a écrit : Brandon Aiken a écrit : It also might be a big/little endian problem, although I always thought that was platform specific, not locale specific. Try the UCS-2-INTERNAL and UCS-4-INTERNAL codepages in iconv, which should use the two-byte or four-byte versions of UCS encoding using the system's default endian setting. Guys, it worked UCS-4-INTERNAL was the right choice !!! I love you all ! (now I just have an out of memory problem, but that's going to be a new thread) Guys, it did not work !!! :( I thought it worked because postgres seemed to be loading the file and failing at the end with an out of memory error, but in fact I think the conversion remove all end-of-line characters (one line of 1.5GB was too much for COPY...). Still searching ! It will take you a day or two to get started, and then a day or two to get the job done, but you really might want to look into kettle or some other ETL tool to do the job. It looks to me like you're trying to screw in a screw using a hammer. t.n.a. ---(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: [GENERAL] MSSQL to PostgreSQL : Encoding problem
2006/11/21, Arnaud Lesauvage [EMAIL PROTECTED]: Hi list ! I already posted this as COPY FROM encoding error, but I have been doing some more tests since then. I'm trying to export data from MS SQL Server to PostgreSQL. The tables are quite big (20M rows), so a CSV export and a COPY FROM3 import seems to be the only reasonable solution. I believe you might have more luck working without files altogether. Use an ETL tool like kettle or even DTS with the pgsql ODBC driver. That's exactly what those tools are for. You still have to get the encodings right, though. I suggest unicode for pgsql, but only you know how the MSSQL database is encoded. t.n.a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] MSSQL to PostgreSQL : Encoding problem
2006/11/22, Arnaud Lesauvage [EMAIL PROTECTED]: Tomi NA a écrit : 2006/11/21, Arnaud Lesauvage [EMAIL PROTECTED]: Hi list ! I already posted this as COPY FROM encoding error, but I have been doing some more tests since then. I'm trying to export data from MS SQL Server to PostgreSQL. The tables are quite big (20M rows), so a CSV export and a COPY FROM3 import seems to be the only reasonable solution. I believe you might have more luck working without files altogether. Use an ETL tool like kettle or even DTS with the pgsql ODBC driver. That's exactly what those tools are for. You still have to get the encodings right, though. I suggest unicode for pgsql, but only you know how the MSSQL database is encoded. I think I'll go this way... No other choice, actually ! The MSSQL database is in SQL_Latin1_General_CP1_Cl_AS. I don't really understand what this is. It supports the euro symbol, so it is probably not pure LATIN1, right ? I suppose you'd have to look at the latin1 codepage character table somewhere...I'm a UTF-8 guy so I'm not well suited to respond to the question. :) t.n.a. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] MSSQL to PostgreSQL : Encoding problem
2006/11/22, Brandon Aiken [EMAIL PROTECTED]: Gee, didn't Unicode just so simplify this codepage mess? Remember when it was just ASCII, EBCDIC, ANSI, and localized codepages? Unicode is a heaven sent, compared to 3 or 4 codepages representing any given (obviously non-English) language, and 3 or 4 more for every other language you have to deal with in your application. Perfect? Hardly. But then again, much more so than natural languages. I'd say we'd deliver products 10-20% faster (in the company I work in) if people looked ahead a couple of decades ago and decided upon something along the lines of unicode instead of ASCII. Cheers, t.n.a. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Issue when inserting Slovak characters in database via PHP code
2006/11/4, Alain Roger [EMAIL PROTECTED]: Hi, Sorry to cross post this mail but i'm not able to know from where comes my issue. I have a postgreSQL database in UNICODE (UTF-8 in v8.1.4 and UNICODE in v8.0.1). Via my web application i type a sentence in Slovak language and it is stored into DB without any slovak characters. Instead of that, all particular characters are replace with \303\251 or \303\206 or \304\314 and so on... I was thinking that issue was coming from DB encryption but on 2 different versions of DB (see above) i get the same result. after, i was thinking that it was coming from my web browser, but even if i setup character mode in central europe and Slovak language as default coding...nothing change...i tried on IE and Firefox. Sounds like all your tests were conducted through a web interface: if the encoding you enter your data in (a function of your environment) is invalid, you might get the data you see in your database. Cheers, t.n.a. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgres import
2006/11/2, Albe Laurenz [EMAIL PROTECTED]:
psql -h host -p port -d database -U user dump.sql
It's a good enough solution in most cases, but when the rowcount
starts to skyrocket, it simply doesn't seem to cut it (at least I
couldn't make it to).
INSERT statements? You dumped with the -d flag, didn't you?
No I didn't, actually. :) The data was never in the database in the
first place: it was generated from a different source. True, it was
generated as a CSV file which I converted into INSERT statements, but
conversion between the two is not a problem (given 1.5GB of RAM).
Then the best way is to convert it back to a CSV and use the COPY
statement to load in into the table (or \copy from psql).
You don't need any third party tools for that, it's all in PostgreSQL.
I had a problem with copy, but I can't remember what exactly...come to
think of it, it could have probably done the job...assuming I define
the primary key as DEFAULT nextval('id'), as I had no id in the rows I
was importing...nice to have alternatives. Thanks for the suggestion.
t.n.a.
---(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: [GENERAL] postgres import
2006/11/1, Alban Hertroys [EMAIL PROTECTED]: Tomi NA wrote: 2006/10/31, Albe Laurenz [EMAIL PROTECTED]: You feed it to the command line interface psql. Example: psql -h host -p port -d database -U user dump.sql It's a good enough solution in most cases, but when the rowcount starts to skyrocket, it simply doesn't seem to cut it (at least I couldn't make it to). To load 1,5M rows (~230MB of INSERT statements), INSERT statements? You dumped with the -d flag, didn't you? Otherwise you'd have seen COPY statements instead, which are much faster (and of which much fewer are necessary, usually). No I didn't, actually. :) The data was never in the database in the first place: it was generated from a different source. True, it was generated as a CSV file which I converted into INSERT statements, but conversion between the two is not a problem (given 1.5GB of RAM). t.n.a. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgres import
2006/10/31, Albe Laurenz [EMAIL PROTECTED]: I have a little problem. I have an .sql file ( db dump ) and i want to import it to postgres on linux. Does anyone know how i can do it? You feed it to the command line interface psql. Example: psql -h host -p port -d database -U user dump.sql It's a good enough solution in most cases, but when the rowcount starts to skyrocket, it simply doesn't seem to cut it (at least I couldn't make it to). To load 1,5M rows (~230MB of INSERT statements), I used gvim (wonderful tool!) to transform the INSERT statements into a CSV file and then used an ETL (kettle - another wonderful tool) tool to import the data into the database. This could have probably been done much easier: I'd welcome a helpful hint so as I know next time. :) t.n.a. ---(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: [GENERAL] UTF-8
2006/10/13, Martijn van Oosterhout kleptog@svana.org: On Thu, Oct 12, 2006 at 11:09:53PM +0200, Tomi NA wrote: 2006/10/12, Martijn van Oosterhout kleptog@svana.org: On Tue, Oct 10, 2006 at 11:49:06AM +0300, Martins Mihailovs wrote: There are some misunderstood. Im using Linux 2.6.16.4, postgresql 8.1.4, (there are one of locale: lv_LV.utf8, for Latvian language). But if I want do lower, then with standard latin symbols all is ok, but with others special symbols (like umlaut in Germany) there is problems, and sorting is going not like alphabet but like latin alphabet and specials symbols after. :( You don't say what your encoding is. If it not UTF-8, that's your problem... Doesn't lv_LV.utf8 mean he *did* say what his encoding is? Not really. It says the encoding the system *expects*. However, if he actually created his database with LATIN1 encoding, it would explain the problems he's having. This is a reoccurring topic on the list: sure, it's possible to misconfigure pg so that uppercase/lowercase/ilike/tsearch2/order don't work with a single letter outside of the English alphabet, but the problem Martins seems to be facing is one we've seen here before (myself being one of those affected). There's no way Martins can set up pg - UTF or no UTF - so that collation and case insensitivity-based functions work in both Latvian an Russian. Because I have the same problem with Croatian, German and Italian, I've limited my use of pg to projects targeted at LAN or intranet environments: others are probably switching to mysql or firebird altogether as it's easier to work with just one RDBMS than two. t.n.a. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] UTF-8
2006/10/13, Martijn van Oosterhout kleptog@svana.org: While sorting for multiple languages simultaneously is an issue, that's not the problem here. Linux/GLibc *does* support correct sorting for all language/charset combinations, and that's what he's using. Just for the hell of it I setup lv_LV.utf8 on my laptop and verifed that it sorts just fine: ... Similarly, upper/lower are also supported, although postgresql doesn't take advantage of the system support in that case. I think this is the crux of the problem. Not supporting uppercase and lowercase makes an e.g. generic people search dialog not malfunction: searching for Müller will not find him if he is stored as OTTO MÜLLER in the database. Certainly not if I have to make sure the search finds one Zvonimir Šimić stored as ZVONIMIR ŠIMIĆ. Whats more, if the user gives up on the integrated search and tries to list all the people in such a database ordered by their last names, he probably won't find Šimić because the user expects him to be between S and T, not after 'Z' (where he ends up beacuse the letter code of 'Š' is greater than that of 'Z'). As for Martins' problem, he needs to support 2 non-english languages which means he's stuck with the same problem of one language being semi functional. As an aside, why doesn't pg take advantage ot the underlying system's support of upper/lower case? t.n.a. ---(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: [GENERAL] UTF-8
2006/10/12, Martijn van Oosterhout kleptog@svana.org: On Tue, Oct 10, 2006 at 11:49:06AM +0300, Martins Mihailovs wrote: There are some misunderstood. Im using Linux 2.6.16.4, postgresql 8.1.4, (there are one of locale: lv_LV.utf8, for Latvian language). But if I want do lower, then with standard latin symbols all is ok, but with others special symbols (like umlaut in Germany) there is problems, and sorting is going not like alphabet but like latin alphabet and specials symbols after. :( You don't say what your encoding is. If it not UTF-8, that's your problem... Doesn't lv_LV.utf8 mean he *did* say what his encoding is? t.n.a. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] performace review
I was just reading http://www.opencrx.org/faq.htm where RDBMS engines are one of the questions and see pgsql bashed sentence after sentence. Can anyone offer any insight as to weather it's fact or FUD? t.n.a. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] vista
On 9/19/06, Roman Neuhauser [EMAIL PROTECTED] wrote: # [EMAIL PROTECTED] / 2006-09-19 21:26:16 +1000: if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user) 1. what do those two things have in common? 2. what makes you think that anyone who uses Windows runs PostgreSQL on it? 3. my guess is you're a Windows programmer, and thus in much better position to fix the issue than Tom RedHat Lane. His point makes sense, Roman. While pg is not a company or a company product, it certainly needs to play nice with business in order for it to *have* (a significent number of) users. As much as I dislike it (probably the euphemism of the year), Windows is a fact on most development machines and on a substantial number of servers. In the case of Vista, even if none of the core commiters plan to support it, it makes a lot more sense to store the RFE and say it'll have to wait, than to say you got a problem? go fix it. Both attitudes reflect the same reality, but the fact that one is positive and one negative is obvious. Cheers, t.n.a. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] One of our own begins a new life
On 9/15/06, Joshua D. Drake [EMAIL PROTECTED] wrote: Hello, Yeah, this is a cross post and it is slightly off topic but IMHO this is important. Tomorrow one of our own, Devrim Gunduz is becoming a man. He is sucking it up, and committing to the cvs repo of project marriage. May the patches reviewers be kind to him! Congratz Devrim, have a good honey moon and we look forward to having you back in a couple of weeks! The kind of cross post off topic post that's always welcome! :) t.n.a. ---(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: [GENERAL] find a free database design software
On 9/9/06, aBBISh [EMAIL PROTECTED] wrote: hello everyone: i want find a free software for design postgresql database model please commend one ~ thanks If you mean free as in beer, azzuri is what I use from time to time: http://www.azzurri.jp/en/software/clay/download.jsp If you mean free as in freedom, than I'd like to hear some suggestions as well. The only useable one I know of is DBDesigner, but it doesn't work wery well in my environment. t.n.a. ---(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: [GENERAL] [ANNOUNCE] == PostgreSQL Weekly News - September 10 2006 ==
On 9/11/06, Tatsuo Ishii [EMAIL PROTECTED] wrote: == PostgreSQL Product News == pgpool-II-1.0.0 is out, now supporting more than two servers and with the new pgpoolAdmin tool written in PHP. http://pgfoundry.org/projects/pgpool/ You dropped the most important feature in pgpool-II: parallel query. I took a look at the feature list and it mentions failover. Can anyone tell me how exactly the 2 pg servers keep in sync when executing non-deterministic calls? t.n.a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [pgsql-advocacy] Thought provoking piece on
On 9/1/06, Peter Eisentraut [EMAIL PROTECTED] wrote: Martijn van Oosterhout wrote: Do we want to keep relying on the system libraries for collation, or do we want to use a cross-platform library like ICU or do we want to create our own collation library? ICU seems fine. +1 t.n.a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Confused about locales
On 8/19/06, John Gunther [EMAIL PROTECTED] wrote: I've been reading about locales, encodings, sort orders, the to_ascii function and, embarrasingly, I'm more confused than enlightened.: What I want is very simple: 1) I want the database to correctly accept, store, and display alphabetic characters, including European accented characters, in HTML forms. 2) I want sorting to ignore the diacritical marks so that, for example, u, u-accent, and u-umlaut are all sorted as if they were plain u. 3) I want sorting to ignore non-alphanumerics, letter case, and white space. To illustrate, the following data is in sorted order: St-Émile stendahl st ènders St. Epson Can someone tell me what combination of PostgreSQL and Linux settings I need for this? It seems like a very basic question, but I'm just dense, I guess. I've tried a half dozen time-consuming configs without success. Well, you'll obviously have to use UTF if you plan on supporting more then one language with different accented characters. The sorting issue is a bit of a problem, though. Pgsql uses the same collation in all databases in a database cluster (carved into stone at cluster init) so I don't know of a good way you could collate your datayou could concievably keep a copy of accented strings replacing the accented characters with their non-accented counterparts as you see fit and collate on that column, but that's not a very elegant way of handling the problem, is it? You might have more luck with another database like mysql 4.1+ (where accent-insensitive UTF collation is directly supported), MS SQL (where you can define encoding and collation settings at the database level, and so concievably have a database for each language, if you know exactly which languages you'll have) or Firebird (where you define an encoding at the column level and can collate any way you wish in each column). Hope I've helped, t.n.a. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Data warehouse OLAP
On 8/9/06, Stefano B. [EMAIL PROTECTED] wrote: hi, I'm working in the implementation of a datawarehouse on Postgres. For analisys of aggregated data I'd like to use some OLAP tools like for example, Mondrian, OR use the meterialized view (if better). My questions: is there any documentation about warehouse (with OLAP or materialized view) on Postgres? Which is better? OLAP tools (I think) or materialized view? Any information is welcome! Thanks You'll obviously have to identify all possible changes to your normalized data that affect the data warehouse consistency. As far as I know, pgsql doesn't directly support materialized views, so you'll write one or more update_warehouse functions and do one of the following: 1.) set up a collection of triggers to keep track of all changes since the last synchronization. Set up a periodic task (probably using pgAgent) which will invoke the update_warehouse functions which will than update the needed records. 2.) set up a collection of triggers directly calling your update_functions The first option is what probably 99% users need because of the implicit nature of the queries run against warehouse data. An hour, day or even week of the latest data very often makes no difference when analyzing an OLAP cube, but the exact tolerance level obviously depends on the exact queries analysts really run, the resources available/needed to refresh the warehouse, the nature of the data etc. The second option is nice in that it keeps the relational data in sync with the warehouse, but this can only be implemented in specific systems where the update load is tolerable, changes limited in scope and update triggers highly focused on the scope of the change. I can imagine circumstances when you'd need such a setup, but most of the time it's just a theoretical possibility. I have implemented the second approach using the very good Mondrian OLAP server and stunning JRubik analysis interface in one of my systems (small database, several dozen MB, less than a milion records, total) at the price of a ~2 sec delay when updating a row - an action that happens about 50-60 times per day. The reason was very non-technical: we needed to be able to say you can analyze data in real time. Hope this helps. Cheers, t.n.a. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] I need a function
On 8/8/06, Feri@ [EMAIL PROTECTED] wrote: Hello to wholes, I am new therein and I am going by of mysql to postgres,somebody serious so kind to explain me as I can obtain in an array the names of the draws of a given base of data in PostGres. am programming in PHP, sorry for my English, i speak spanish. thank you very much. What do you mean by draws of a given [database]? Tables? Columns? Try something like: SELECT * FROM pg_class Search the manual for pg_class: this should help a lot. Hope it helps. t.n.a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] different sort order in windows and linux version
On 7/2/06, Agent M [EMAIL PROTECTED] wrote: Certain Japanese characters cannot make a reliable round-trip through Unicode. ICU uses UTF-16 as its store, so the Japanese folks won't be happy with an ICU-only solution. However, it would still be of great Could you explain what you mean and what's special with those characters? benefit to allow ICU to handle as much as possible, leaving the string encodings to the encoding experts. At the very least, it would be great to have ICU to handle encoding on a per-column basis (perhaps extending the text datatype with encoding info). Perhaps this would be a decent stopgap solution? The backend protocol would also need a version bump- currently, it converts all strings to a single encoding. Could you give an example of what that would look like in your opinion? I was thinking more along the lines of a setting in pg_hba.conf where the server uses or does not use something like ICU...at least as an intermediate solution. Adding a LOCALE clause to a column definition (similar to the ENCODING clause of the CREATE DATABASE statement) would solve most (not all) problems with a default locale. There still might be some non-deterministic behaviour with operations between strings in different locales but it's far from a showstopper. t.n.a. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] different sort order in windows and linux version
On 7/1/06, Martijn van Oosterhout kleptog@svana.org wrote: On Fri, Jun 30, 2006 at 07:29:12PM +0200, Tomi NA wrote: If I sound harsh, please excuse me, but I feel like I'm the only one who thinks these encoding problems (collation, upper/lowercase, multiple languages in a single database) are serious...nobody seems to share the sentiment. Ah well... I agree with you, however the resistance (AFAICS) comes mostly from the fact that we would be depending on an external library to do it. I don't think postgres should try doing it itself, given that the unicode character databases are quite large by themselves. Alternativly, the postgres group could produce a customised version of ICU that's smaller (the website has details about how). But any case, this problem will need to be addressed at some point. Basically, it comes down to three possibilities, doesn't it: 1.) use an existing library 2.) write a pgsql specific implementation 3.) forget about it and tend to other issues Personally, I don't really care if it's 1) or 2): I'm just afraid it's going to be 3). Is this a licencing issue (with regard to ICU beeing under the IBM public licence)? A plugin architecture (to get rid of licencing headaches) issue? Are there any other libraries that might do the job? To be perfectly honest, I've had to tackle so many problems with encodings during the years I'd make it punishable by law to use anything *but* UTF...but I'm not president of the Galaxy yet, Zaphod is. (-: t.n.a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] performance tips please
On 6/21/06, Hugo [EMAIL PROTECTED] wrote: Hi, I am testing my application and DB (postgres 8.1.4 ) on a : DELL-Power Edge 1800 with 2 Xeon 3.2ghz, 2 Gb RAM and 2 SCSI 149 Gb each. trouble is that the same application and DB(postgres 8.0.4) runs on a: DELL pentium 3 with 526MB of RAM and an IDE 20 GB and comparing the performance of both, a get only 20-30seconds faster responses on the new server, where can I start looking to find out why is the second server performing this way. any advice is very appreciated, thanks in advance Hugo I assume the the task takes longer than 5 seconds to complete? Are you running the same OS? Can pgsql distribute the load on both Xeon processors? Is pgsql custom compiled for a specific architecture (Pentium III, for example)? How do you measure the response time? Are there other apps involved? t.n.a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] COLLATE
On 6/7/06, Martijn van Oosterhout kleptog@svana.org wrote: Kind of all three, feeding off eachother. There's just not enough interest from any front to really get it moving. It's a fairly invasive change and without significant support and interest from somewhere, chances of completion let alone acceptance are pretty slim... I don't get it. Maybe it's me or my environment. About half of the work we do in my company is building webs on the (excellent) eZ publish CMS. We routinely use MySQL even though the CMS (at least declaratively) wolks on top of pgsql. We use MySQL on inertia: it's the default db under eZ publish and it's easy to get hosting. On the other hand, I'd rather bite my hand off than use MySQL in some other Internet apps (e-shop with a card payment interface and others). Where am I going with all this? Well, if you don't live in an english-speaking country, there's no such thing as a single language web. Because of collation limitations, however, postgresql would be the first to be crossed out on my list no matter how good it is in all other respects. I understand that the needed change is uncomfortably invasive, but not beeing able to collate correctly is a show-stopping problem on a professional site. The user opens a drop-down with several dozens of cities, scrolls down a bit to where his city should be, dooesn't see it because the collator places the weird letter all the way down at the end of the list - and the user walks away. The other user can't get info about the bus lines to the city or he doesn't buy a product he want's because he doesn't see it where it's supposed to be. Another user notices the error and dissregards the site as amateurish. I understand I'm talking about a specific area of use, but isn't that where the biggest growth in both the number of new applications as well as the number of users is? As it is, I'm happy to have a great RDBMS to build intranet, rich client apps on - but that's about it. I've used MySQL and MSSQL (unfortunately) and they both support collation much better than pgsql. Not perfect, but much much better. Maybe postgresql should try to set less ambitious goals and instead of going for the holy grail of collation management (which is usualy praiseworthy) try to provide at least db-level collation definitions if table/row/cell level collation settings should proove too challenging at the moment. t.n.a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Best open source tool for database design / ERDs?
On 6/2/06, Rich Shepard [EMAIL PROTECTED] wrote: On Fri, 2 Jun 2006, Michael Dean wrote: a caveat: I don't believe, from my reading of the license, that this is an open source software product. May not be. I don't recall. Neither is Java. But, if you're using it for your own purposes, what's the issue? There are so many, so profound and - for the biggest part - not at all well understood issues about using open source software vs. the alternative that it's very hard to reply to your question with a straight face. t.n.a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] review SQL command history?
Is there a way to see SQL command history (specifically, a number of INSERTS in a batch command) that happened a couple of weeks ago? I need to see if a database was initialized with the correct data... TIA, t.n.a. ---(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: [GENERAL] Best open source tool for database design / ERDs?
I use the azzuri eclipse plugin. It's rudimentary, but get's the job done for smaller (a couple of dozens of tables) models. It has a commercial version which might be even better. t.n.a. On 28 May 2006 05:19:04 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: What open source tool do people here like for creating ER diagrams? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best open source tool for database design / ERDs?
On 5/29/06, Bjørn T Johansen [EMAIL PROTECTED] wrote: Druid works ok http://druid.sourceforge.net/index.html Are there a couple of screenshots available on the net, a flash demo perhaps? t.n.a. ---(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
[GENERAL] ALTER SEQUENCE ... RESTART WITH [variable] problem
I need to generate a couple of dozen statements reseting my sequences so that they're next values are greater than the biggest existing ids. The problem is, I can't even form a statement to update one sequence. This is what I tried: CREATE OR REPLACE FUNCTION init_sequences() RETURNS void AS $BODY$ DECLARE next_id_table1 INTEGER; BEGIN SELECT INTO next_id_table1 MAX(id)+1 FROM table1; ALTER SEQUENCE pk_table1 RESTART next_id_table1; END; $BODY$ LANGUAGE 'plpgsql'; The problem seems to be the ALTER statement: ERROR: syntax error at or near $1 at character 36 QUERY: ALTER SEQUENCE pk_table1 RESTART $1 CONTEXT: SQL statement in PL/PgSQL function init_sequences near line 5 If I change the ALTER statement like this ALTER SEQUENCE pk_table1 RESTART 200; it works. But is obviously not what I wanted. Is there a way to get the ALTER SEQUENCE statement to use a value stored in a variable? t.n.a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ALTER SEQUENCE ... RESTART WITH [variable] problem
On 5/20/06, Martijn van Oosterhout kleptog@svana.org wrote:
Seems you can't use a variable there. Your choices are to build a
string and use EXECUTE, or just do:
SELECT setval('sequence',value);
The EXECUTE string solution did the job. Thank you very much, Martijn.
t.n.a.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Is PostgreSQL an easy choice for a large CMS?
On 4/30/06, Tony Lausin [EMAIL PROTECTED] wrote: Hello all, I'm working on a CMS which requires an open source database capable of handling hundreds of thousands of users simultaneously, with a high rate of database writes, and without buckling. We're talking somewhere between nerve.com/catch27.com and xanga.com/friendster.com In my opinion, postgresql is not the way to go when building a cMS simply because of the way it handles strings. A CMS should be language/region agnostic i.e. supporting any chosen locale subset, rather then just one locale, as postgresql does. You can throw UTF-8 at the problem and it will enable data storage/retrieval, but you'll still be stuck with incorrect text ordering/sorting in any locale but the default. Truth be told, I don't know of a single RDBMS which handles this issue gracefully, but with postgresql, your basically stuck with a single language/locale. On the other hand, if you don't need multilang support, you'll find that postgresql is generally great to program and use. Tomislav ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] setting the environment locale - linux, windows
On 4/21/06, Martijn van Oosterhout kleptog@svana.org wrote: On Fri, Apr 21, 2006 at 12:49:31AM +0200, Tomi NA wrote: This is probably somewhat offtopic, but it does relate to postgresql so... Problem summary: I have a UTF-8 encoded database running on linux on which upper() and lower() string functions ignore locale specific characters. You need to look at your LC_COLLATE settings. LC_COLLATE and LC_CTYPE are fixed at initdb and constant across the DB. So, if I were to backup my database, clear the data directory, reinitialize the cluster and restore the database using the correct LC_COLLATE and LC_CTYPE, I'd have one language nailed? Are there plans to enable assigning locale at the database level? Plans, yes. Progress, a bit slow... No estimates, then? Nevertheless, thanks for the help. Tomislav ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] setting the environment locale - linux, windows
On 4/21/06, Martijn van Oosterhout kleptog@svana.org wrote: On Fri, Apr 21, 2006 at 03:34:27PM +0200, Tomi NA wrote: On 4/21/06, Martijn van Oosterhout kleptog@svana.org wrote: You need to look at your LC_COLLATE settings. LC_COLLATE and LC_CTYPE are fixed at initdb and constant across the DB. So, if I were to backup my database, clear the data directory, reinitialize the cluster and restore the database using the correct LC_COLLATE and LC_CTYPE, I'd have one language nailed? Well, you need the reinitialise the cluster with the correct language. Once initiailised it doesn't matter what you restore with. Are there plans to enable assigning locale at the database level? Plans, yes. Progress, a bit slow... No estimates, then? Nevertheless, thanks for the help. Well, I'm worked on COLLATE support which would get it down to the column level. This is pretty much what you need, since indexes could be shared between databases and each database needs the same definition for those indexes. I'm got a fair way but got a little over my head, so unless someone picks it up and completes it, it could be a while... I'd be more than happy to lend a hand if I had a 9 to 5 job...*if* I had a 9 to 5 job. A man can dream... :-\ t.n.a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] full text search: the concept of a word
I'm considering using tsearch2 in the project I'm working on right now...however, I'm not sure if tsearch2 can handle my very specific requirements - I therefore hope someone can tell me if the following is possible and how I should go about it... My textfields are trigger-generated using information from a number of tables: these fields can be, say, a couple of thousand characters wide. Up to here, there's no problem. What I'd like to do is define - possibly using regexps - what constitutes a word. For instance, my word separator is a semicolon, not a space; a dash is not a separator, and neither are language specific characters (which might be interpreted that way by a language agnostic tool)... BTW, I use UTF-8 as my database encoding if it's of any importance. What it comes down to is this: is it possible to somehow define what constitutes a word? TIA, Tomislav ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] setting the environment locale - linux, windows
This is probably somewhat offtopic, but it does relate to postgresql so... Problem summary: I have a UTF-8 encoded database running on linux on which upper() and lower() string functions ignore locale specific characters. To make things a bit more interesting, the development machines (including the db server) are linux based, while the target servers are a mixture of linux and windows servers. I need to compare strings case insensitive. Proper collation would be a plus, but I could live without it for the time beeing. How does one set the e.g. german locale in linux? Or in windows? Is it given as a parameter of initdb and frozen aftewards or does the postmaster look to it's environment for this information (LC_ALL, LC_CTYPE variables)? Are there plans to enable assigning locale at the database level? Tomislav ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] ilike and utf-8
On 4/14/06, Martijn van Oosterhout kleptog@svana.org wrote: On Fri, Apr 14, 2006 at 03:16:01PM +0200, Raphael Bauduin wrote: Hi, Does the ilike operator work fine with cyrillic text put in a UTF-8 encoded database? I've had remarks of a user (of http://myowndb.com, a web database) with text in cyrillic that his searches are not case insensitive, although I use the ilke operator in the code. And it works perfectly for my data (that are not in cyrillic).UTF-8 support for case-comparison is operatnig system dependant. Whatsystems are we comparing here?I'd like to know the same thing. I'm using GNU/linux and ISO-8859-2 (when UTF-8 isn't an option). Tomislav
Fwd: [GENERAL] how to create script of database in postgres..sql(winxp)
-- Forwarded message --From: deepak pal [EMAIL PROTECTED]Date: Apr 3, 2006 2:05 PM Subject: Re: [GENERAL] how to create script of database in postgres..sql(winxp)To: Tomi NA [EMAIL PROTECTED]it do not recognize that \i and pg_dump i use \i pg_dump mydbnamenewdb command From the examples in man pg_dump: To dump a database: $ pg_dump mydb db.out To reload this database: $ psql -d database -f db.out To dump a database called mydb to a tar file: $ pg_dump -Ft mydb db.tar To reload this dump into an existing database called newdb: $ pg_restore -d newdb db.tarHave you tried to dump the database this way? Does it work?t.n.a.
Re: [GENERAL] database design questions
On 4/3/06, Ottavio Campana [EMAIL PROTECTED] wrote:
3) faq 4.11.1 saysCREATE TABLE person (id SERIAL,name TEXT);is automatically translated into this:CREATE SEQUENCE person_id_seq;
CREATE TABLE person (id INT4 NOT NULL DEFAULT nextval('person_id_seq'),name TEXT);how can I do it with a INT8 instead of a INT4?Thank you
Is there a reason not to write explicitly?CREATE SEQUENCE person_id_seq;CREATE TABLE person (id INT8 NOT NULL DEFAULT nextval('person_id_seq'),name TEXT);Tomislav
Re: [GENERAL] PostgreSQL's XML support comparison against other RDBMSes
On 3/29/06, Martijn van Oosterhout kleptog@svana.org wrote: On Wed, Mar 29, 2006 at 02:57:44PM +0400, Ivan Zolotukhin wrote: So let's make such analysis here within PostgreSQL community! There were a lot of talks about XML support in Postgres, but they did not lead to any steady work in this direction. IMHO, it's time to start it now, no?Quite, lets start with why the module in contrib is not good enough andwhat the various existing XML projects lack. http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/xml2/http://pgfoundry.org/projects/getxml/ http://gborg.postgresql.org/project/xpsql/projdisplay.phpThe getxml and xpsql project seem to fit phantasticaly into my idea of handling XML inside a database.Say you wan't to store an OpenOffice.org document into a database, but rather than say it's a binary and lose all editing and most search capabilities, it might be possible to use xpsql to store the document into the database. Once stored, specific parts could be altered as the system changes states and during the systems lifecycle, you could at any time generate a document based on the current system state. Can anyone give me any kind of opinion on this kind of document handling?Does anyone here have any experiance with xpsql?Is xpsql under any kind of active development?Can xpsql handle documents as complex as OOo documents, with all their DTDs, schemas...whatever? t.n.a.
Re: [GENERAL] Implementation Suggestions
On 3/29/06, Ian Harding [EMAIL PROTECTED] wrote: Seriously, it's not too bad if you don't mind it's plentifulshortcomings.I was getting carpal tunnel syndrome from typingscripting language pages so I switched to RoR for a hobby app.Itworks fine, but you have to do it The Rails Way and expect no help from the Community because they are a fanboi cheerleader squad, notinterested in silly stuff like referential integrity, functions,triggers, etc.All that nonsense belongs in the application!... That's an eye opener, thanks Ian.t.n.a.
Re: [GENERAL] picking the correct locale when doing initdb
On 3/16/06, Harald Armin Massa [EMAIL PROTECTED] wrote: on bugs Magnus wrote: It's a matter of picking the correct locale when you initdb your database.and he is PERFECTLY right. Getting the locale wrong while doing intidb can really screw up ones day. But: because of initdb being at the very beginning, it is a really tough decision based on not a lot of information. I know, same problem appears in bigger databases too.On the same time my impression is that since PostgreSQL is totally easy to install on win32 (ok, still need to understand what are user rights, and have ntfs), lots of first timers are upgrading from My to Postgre. What can we do to make their first steps with encodings and locale easier? Here's an idea: let's make the locale a table-level setting, settable at table creation time so that it's concievable to use postgresql to build, say, a turist information site supporting english, german, spanish and italian at the same time. Tomislav
Re: [GENERAL] Disability the trigger
On 3/13/06, Claudio Tognolo [EMAIL PROTECTED] wrote: I can disable the Trigger?I'd like to know how this could be done, as well. What I really need is a hold-off-all-triggers-untill-I-tell-you-to command, but hey, making a trigger just not fire and vice versa would also be nice. :) Tomislav
[GENERAL] programatic database dump
I'd like to dump a postgresql database from my (java) app and copy the dump file to the client machine.This backup strategy prooved invalueable in the past (given enough room on the harddrives, which I have) and I'd like to implement it now with postgresql. Is there something like a system stored procedure that does something like that I can use? Calling pg_dump seems like a bad hack: I'd like to keep communication at the java-sql level if possible. I'll probably bare it, but I'd like to check if I've missed something, first. TIA,Tomislav
Re: [GENERAL] database/schema level triggers?
Thanks for the info, everyone.Tomislav
[GENERAL] database/schema level triggers?
Does anything like that exist in postgresql?It'd rid me of a whole lot of work if it did...and I'd still have plenty more to keep me busy. :)TIA,Tomislav
[GENERAL] collation UTF-8
I'm using PosgreSQL 8.1.2 on linux and want to load UTF-8 encoded varchars.While I can store and get at stored text correctly, the ORDER BY places all accented characters (Croatian, in this case - probably marked hr_HR) after non-accented characters. This is no showstopper, but it does affect the general perception of application quality.Now, I've seen the issue mentioned in a number of places, but often with fairly old versions of pgsql (8.0), in different circumstances etc. so my question is: is there an official way to set up UTF8 collation so that SELECT first_name FROM persons ORDER BY first_name works as expected?TIA,Tomislav
Re: [GENERAL] collation UTF-8
On 2/24/06, Martijn van Oosterhout kleptog@svana.org wrote: On Fri, Feb 24, 2006 at 06:23:07PM +0100, Tomi NA wrote: I'm using PosgreSQL 8.1.2 on linux and want to load UTF-8 encoded varchars. While I can store and get at stored text correctly, the ORDER BY places all accented characters (Croatian, in this case - probably marked hr_HR) after non-accented characters. This is no showstopper, but it does affect the general perception of application quality. Collation is a function of the OS. Basically, is the locale of yourdatabase setup for UTF-8 collation? It would probably be calledhr_HR.UTF-8.You were right about this:LC_ALL=hr_HR.UTF-8 sort test.txt(seemingly) collates the same way that pgsql does. Accented letters at the end of the alphabet. I've tried hr_HR.UTF8 as well, without results.Btw, my database is created withCREATE DATABASE mydb WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default;Yes, setup the locale correctly. In general, postgresql should give the same results as sort(1) on the command-line. Use that to experiment.LC_ALL=hr_HR.UTF-8 sort input outputI'm very sorry to report it does not work. :( Btw,set | grep LC_ returns nothing...is this a possible source of the problem? Tomislav
