Re: [GENERAL] Compiling/Installing as a non-admin user
You are talking about /usr/local/data; so I assume that you are trying this on Linux or some other Nixen.You just need to use the --prefix option to configure... Here's what I typically do: Download/'CVS checkout' the sources. Enter the source directory, and invoke configure like this:./configure --enable-debug --prefix `pwd`/db CFLAGS=-O0And the run 'make' and 'make install'. This will install the data in your sources_dir/db/data. HTH...Regards,-- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com On 11/1/06, Ritesh Nadhani [EMAIL PROTECTED] wrote: Hello AllMe and my professor are planning to work upon machine learning inpostgresql over tsearch2. So I have some questions:We have a server where Postgresql is running without any problem withpostgres username and admin rights. I have a user account in that server. I plan to compile and run another postgresql for our testing soI was thinking of how to do that? My prior knowledge of using postgresqlhas always been as admin where I have full rights.As I see, using the default MAKE for postgresql will set the data directory etc. in /usr/local/data etc which I dont have access to as a user.So I would like to compile and run postgresql as a normal user withevery thing like data kept in my usr directory. I should be able to run the instance over separate port and can start and stop it.Basically, I want to run the server as in user modeHow should I configure the MAKE and INSTALL in this circumstances? Whatare your suggestions Ritesh---(end of broadcast)---TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Compiling/Installing as a non-admin user
Gurjeet Singh wrote: You are talking about /usr/local/data; so I assume that you are trying this on Linux or some other Nixen. You just need to use the --prefix option to configure... Here's what I typically do: Download/'CVS checkout' the sources. Enter the source directory, and invoke configure like this: ./configure --enable-debug --prefix `pwd`/db CFLAGS=-O0 And the run 'make' and 'make install'. This will install the data in your sources_dir/db/data. And then of course you'll need to remember to set your port to something other than 5432, and tweak your PATH, PGPORT etc or define some aliases/wrappers so you don't end up running against the default installation. Oh, and you'll need to tweak the startup scripts and logging configuration so you get logs somewhere useful. I think Tom Lane has a script that lets him switch between different installations (versions in his case). I only tend to have two versions active at any one time, so I just define an alias for psql. alias psql82='/usr/local/pgsql82/bin/psql -p 5434' On the rare occasion when I run an 8.2 createdb I need to remember to put the port number in manually of course. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Compiling/Installing as a non-admin user
Ritesh Nadhani wrote: As I see, using the default MAKE for postgresql will set the data directory etc. in /usr/local/data etc which I dont have access to as a user. Specify the --prefix=DIR argument to configure, pointing to a directory you can write (presumably within your $HOME). You can also pick a port with --with-port=NNN if you need it. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] Pgsql on Solaris
[EMAIL PROTECTED] wrote: Thanks to all who replied. The reason I wanted to go 64-bit on our Fujitsu sparc-alikes was that our sysadmins switched to 64-bit Perl as part of the standard package, and that broke our DBD::Pg interface. With no warning, we started getting a message about Pg.so, Wrong ELF Class: ELFCLASS32. My assumption had been that 64-bit was the latest and greatest, so of course config should have found the appropriate libraries etc. and set me up for a 64-bit make. The answer to my question seems to be yes, I could cause 64-bit compilation, but it's not the obvious way to go. You only need the client libraries as 64-bit for this particular problem . Of course, if server and client are on the same machine then it's probably simpler to have everything the same. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Trouble with plpgsql generic trigger function using
Lenorovitz, Joel wrote: I'd like to create a trigger function whose use can extend to multiple tables by employing the special variables available (e.g., TG_RELNAME). [snip] Any advice on outputting the values of the variables to the console for inspection during testing would be welcome as well (RAISE EXCEPTION doesn't allow a variable value in the message string, plus it seems a little harsh). You can embed variables into RAISEd messages. You'd normally use RAISE NOTICE for this sort of thing. RAISE LEVEL 'My variables % and %', var1, var2; CREATE OR REPLACE FUNCTION trigger_fxn() RETURNS TRIGGER AS $$ BEGIN IF ((TG_OP = 'INSERT') AND (TG_WHEN = 'BEFORE')) THEN IF (SELECT COUNT(*) FROM text(TG_RELNAME)) 4 This won't work, because plpgsql pre-plans queries. You'll need to use the EXECUTE facility: EXECUTE 'SELECT COUNT(*) FROM ' || TG_RELNAME; You'll want the FOR ... IN ... EXECUTE form to read a value into a variable. See Looping through query results in the manual for details. Some of the other procedural languages treat queries as text anyway, so they'll let you do what you're trying. -- Richard Huxton Archonet Ltd ---(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] Encoding, Unicode, locales, etc.
On Tue, Oct 31, 2006 at 11:47:56PM -0500, Tom Lane wrote: Because we depend on libc's locale support, which (on many platforms) isn't designed to switch between locales cheaply. The fact that we allow a per-database encoding spec at all was probably a bad idea in hindsight --- it's out front of what the code can really deal with. My recollection is that the Japanese contingent argued for it on the grounds that they needed to deal with multiple encodings and didn't care about encoding/locale mismatch because they were going to use C locale anyway. For everybody else though, it's a gotcha waiting to happen. Could this paragraph be put into the docs and/or the FAQ, please ? Along with the recommendation that if you require multiple encodings for your databases you better had your OS locale configured properly for UTF8 and use UNICODE databases or do initdb with the C-locale. This stuff is certainly far from ideal, but the amount of work involved to fix it is daunting; see many past pg-hackers discussions. Here are a few data points from my Debian/Testing system in favour of not worrying too much about installed ICU size as it is being used by other packages anyways: libicu36 Reverse Depends: openoffice.org-writer * OOo openoffice.org-filter-so52 openoffice.org-core libxerces27 * Xerces XML parser (Apache camp) libboost-regex1.33.1 libboost-dbg icu Reverse Depends: libicu36 libicu36 libxercesicu26* Xerces, again libxercesicu25 libicu28-dev libicu28 libicu21c102 icu-i18ndata icu-data libwine * Wine This, of course, does not decrease the work required to get this going in PostgreSQL. Thanks for the great work, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trouble with plpgsql generic trigger function using
Lenorovitz, Joel wrote: I'd like to create a trigger function whose use can extend to multiple tables by employing the special variables available (e.g., TG_RELNAME). Below is a simple version of such a function that ought to prevent insertion of greater than 4 total records in the table that calls it. I'm not sure that I'm using or dereferencing the trigger variables correctly, however, particularly in the query. I have tried many syntax, type casting, and alternate variable assignment variations, but, aside from parsing successfully, this code does not seem to work as intended.Can somebody correct this specific example to have it work You need to use EXECUTE to execute your dynamic query. You can't just put a string in a query and have it be handled as an identifier. during testing would be welcome as well (RAISE EXCEPTION doesn't allow a variable value in the message string, plus it seems a little harsh). Not true, and you don't need to raise an exception; a notice'd do just fine. Try this: RAISE NOTICE 'Trigger fired on table %', TG_RELNAME; CREATE OR REPLACE FUNCTION trigger_fxn() RETURNS TRIGGER AS $$ BEGIN IF ((TG_OP = 'INSERT') AND (TG_WHEN = 'BEFORE')) THEN IF (SELECT COUNT(*) FROM text(TG_RELNAME)) 4 You'll want to DECLARE an integer variable and use SELECT INTO with it. And EXECUTE, as mentioned. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgres import
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). -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] blocking mode
From time to time I get Notice: Unknown: Cannot set connection to blocking mode in Unknown on line 0. What it is? Is it really a problem with postgresql. I am using Apache/2.2.3 (Unix) PHP/5.1.6/ postgresql 8.1 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql and reiserfs
On 10/31/06, Devrim GUNDUZ [EMAIL PROTECTED] wrote: Hi, On Tue, 2006-10-31 at 09:31 -0800, Richard Broersma Jr wrote: I heard that reiser4 is not yet stable. And that there is a chance that it wont be since its author is in detention. Here are the links: http://linux.slashdot.org/linux/06/10/15/0057203.shtml http://yro.slashdot.org/yro/06/10/11/0142216.shtml?tid=123 Also, SuSE announced that they will be switching to ext3 in their next SLES releases: http://news.com.com/Novell+makes+file-storage+software +shift/2100-1016_3-6125509.html Red Hat, major player in Enterprise game, is supporting ext* for years. reiserfs is not enabled by default. So, IMHO, since less people will be using reiser, I would not use that in my installations. Most of the recommendations that I've seen are to use good-old-reliable EXT3 which keeps your data safe Also ext2 is preferred on many installations, especially when people want to avoid journals. If i was a betting man, I would guess that (currently being developed) ext4 will win the filesystem popularity contest at some point in the future, and will probably dominate enterprise linux installations. merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Encoding, Unicode, locales, etc.
Thanks Tom, for your reply. Tom Lane wrote: Carlos Moreno [EMAIL PROTECTED] writes: Why is it that the database cluster is resrticted to a single locale (or single set of locales) instead of being configurable on a per-database basis? Because we depend on libc's locale support, which (on many platforms) isn't designed to switch between locales cheaply [...] This stuff is certainly far from ideal, but the amount of work involved to fix it is daunting; see many past pg-hackers discussions. Fair enough --- and good to know. 2) On the same token (more or less), I have a test database, for which I ran initdb without specifying encoding or locale; then, I create a database with UTF8 encoding. There's no such thing as you didn't specify a locale. If you didn't specify one on the initdb command line, then it was taken from the environment. Try show lc_collate and show lc_ctype to see what got used. Yes, that's what I meant --- I meant that I did not use the --locale or -E command- line switches for the initdb command. Both lc_ctype and lc_collate show en_US.UTF-8 I try lower of a string that contains characters with accents (e.g., Spanish or French characters), and it works as it should according to Spanish or French rules --- it returns a string with the same characters in lowecase, with the same accent. Why did that work? My Linux machine has all en_US.UTF-8 locales, and en_US is not even aware of characters with accents, You sure? I'd sort of expect a UTF8 locale to know this stuff anyway. In any case, Postgres doesn't know anything about case conversion beyond what toupper/tolower tell it, so your experimental result is sufficient proof that that locale includes these conversions. Are you sure there's nothing about the way PostgreSQL interacts with C conversion functions? I ask because, as part of a sanity check, I repeated the tests --- now with two machines; one that has PG 8.1.4, and the other one has 7.4.14, and they behave differently. The one that does the case conversion correctly (read: as I expect it as per Spanish or French rules) is 8.1.4 with en_US locale (LC_CTYPE and LC_COLLATE both showing en_US.UTF-8). PG 7.4.14, *even with locale es_ES*, does not do the case conversion (characters with accent or tilde are left untouched). I wonder if someone could shed some light on this little mystery??? Perhaps to add more confusion to my experimental/informal tests, PG 8.1.4 is running on a FC4 AMD64 X2 box (the command locale at the shell prompt shows all en_US.utf8), and PG 7.4.14 is running on a laptop with FC5 on an Intel Celeron M (the command locale shows exactly the same in that case). Does this perhaps account for the difference? Thanks, Carlos -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Grouping My query
Dear Sir/Madam, I have a staff directory, which group staff in various categories I would like to group the staff members in there specific categories, Below is query is query I am running, it works fine and returns the output below. My question is, how do I Group the staff members in there respective categories using the staff_catid(Category Table id) , staff_subcatid(Sub_Category Table id) fields. SELECT staff_lname,staff_fname,staff_id,staff_catid,staff_subcatid,cat_acron,subcat_acron,staff_sortorder FROM staffmembers LEFT OUTER JOIN category ON cat_id = staff_catid LEFT OUTER JOIN sub_category ON subcat_id = staff_subcatid INNER JOIN usercat_mode ON mod_mode_id = staff_s_subcatid AND mod_user_id = '7146' ORDER BY staff_sortorder; staff_lname | staff_fname | staff_id | staff_catid | staff_subcatid | cat_acron | subcat_acron | staff_sortorder -+-+--+-++---+--+- Vacant | Vacant | 8836 | 500 |534 | ADSDS | ODA | 0 n | a | 7148 | 500 |534 | ADSDS | ODA | 0 b | Sb | 7150 | 500 |534 | ADSDS | WEB | 1 x | cc | 7174 | 500 |534 | ADSDS | WEB | 1 e | dd | 7173 | 500 |534 | ADSDS | WEB | 2 n | e | 7149 | 500 |534 | NIS | EDCU| 2 p | A | 7156 | 500 |534 | NIS | EDCU| 3 ii | R | 7175 | 500 |534 | NIS | EDCU| 3 Kung| Wf | 7147 | 500 |534 | NIS | INSU| 4 u | Martin | 7178 | 500 |534 | NIS | INSU| 4 oo | e | 7179 | 500 |534 | NIS | INSU| 5 w | Mary| 7146 | 500 |534 | NIS | INSU| 5 l | www | 7151 | 500 |534 | NIS | INSU| 6 ww | Ct | 7145 | 500 |534 | QAUSS | CS | 7 none| none| 7152 | 500 |534 | QAUSS | CS | 8 | Hm | 7155 | 500 |534 | QAUSS | CS | 9 e | B | 7153 | 500 |534 | QAUSS | CS | 10 | W | 7157 | 500 |534 | QAUSS | IT | 11 None| None| 7158 | 500 |534 | QAUSS | IT | 12 t | Rrd | 8825 | 500 |534 | QAUSS | IT | 13 none| none| 7163 | 500 |534 | QAUSS | IT | 14 | r | 7160 | 500 |534 | QAUSS | IT | 15 m | John| 8838 | 500 |534 | QAUSS | IT | 16 66 | 666 | 9341 | 500 |534 | QAUSS | SATU| 17 v | Pradeep | 7161 | 500 |534 | QAUSS | SATU| 18 a | Pamela | 7164 | 500 |534 | QAUSS | SATU| 19 Below is an output I would like to achieve can this be achived my using the Group by or I can write a script to achieve the output below please do assist. As you can see the staff members are group in their respective Categories and Subcategory unlike the output above. staff_lname | staff_fname | staff_id | staff_catid | staff_subcatid | cat_acron | subcat_acron | staff_sortorder -+-+--+-++---+--+- ADSDS ODA Vacant | Vacant | 8836 | 500 |534 | ADSDS | ODA | 0 n | a | 7148 | 500 |534 | ADSDS | ODA | 0 WEB b | Sb | 7150 | 500 |534 | ADSDS | WEB | 1 x | cc | 7174 | 500 |534 | ADSDS | WEB | 1 e | dd | 7173 | 500 |
Re: [HACKERS] [GENERAL] Index greater than 8k
We are trying to get something faster than ~ '%foo%'; Which Tsearch2 does not give us :) Hmm, why not with GIN? :) Although, right now GIN doesn't support prefix search, it's possible to use it. Brain storm method: Develop a dictionary which returns all substring for lexeme, for example for word foobar it will be 'foobar fooba foob foo fo oobar ooba oob oo obar oba ob bar ba ar'. And make GIN functional index over your column (to save disk space). So, your query will be looked as select ... where to_tsvector(text_column) @@ 'foo'; Notices: Time of search in GIN weak depend on number of words (opposite to tsearch2/GiST), but insertion of row may be slow enough -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Index greater than 8k
Teodor Sigaev wrote: We are trying to get something faster than ~ '%foo%'; Which Tsearch2 does not give us :) Hmm, why not with GIN? :) Although, right now GIN doesn't support prefix search, it's possible to use it. Well they run 8.1 :) Joshua D. Drake Brain storm method: Develop a dictionary which returns all substring for lexeme, for example for word foobar it will be 'foobar fooba foob foo fo oobar ooba oob oo obar oba ob bar ba ar'. And make GIN functional index over your column (to save disk space). So, your query will be looked as select ... where to_tsvector(text_column) @@ 'foo'; Notices: Time of search in GIN weak depend on number of words (opposite to tsearch2/GiST), but insertion of row may be slow enough -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
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: [HACKERS] [GENERAL] Index greater than 8k
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, Oct 31, 2006 at 08:55:04PM -0800, Joshua D. Drake wrote: We are not storing bytea [...] [...] Hmm, have you tried to create a functional trigram index on the equivalent of strings(bytea_column) or something like that? Hrm. Sorry for my impolite interuption, but... is there such a thing as a functional trigram index? (this would be very cool). Thanks - -- tomás -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFFSG33Bcgs9XrR2kYRAnB7AJ4l6UPK/4vhtgr7Ux2/L7VtYq6d7ACeLBZP IMPCEj5zqhYR7b2eYPgjRRE= =6uiR -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] limit left join rows to 1
Andreas Kretschmer wrote: Jure Ložar [EMAIL PROTECTED] schrieb: Hi. Is it possible to limit number of left join rows that match condition to 1? I don't want to have hits from first table multiplied when more then 1 row matches on left join condition. I'm not sure if i understand you correctly, but perhaps this is what you are searching for: Suppose, you have 2 tables, master and detail: test=# select * from master; id 1 2 (2 rows) test=# select * from detail; id | val +- 1 | 200 2 | 200 1 | 100 (3 rows) This is the left join: test=# select m.id, d.val from master m left join detail d on m.id=d.id; id | val +- 1 | 100 1 | 200 2 | 200 (3 rows) But you need only one row from detail, which? Suppose, this one with the max(val) value: test=# select m.id, d.val from master m left join (select id, max(val) as val from detail group by id) d on m.id=d.id; id | val +- 1 | 200 2 | 200 (2 rows) Is this okay for you? Andreas Yes. It's good. Not exactly what I ment but it works. Thank you. Jure ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] news.postgresql.org down
Does anyone know what's happened to the news.postgresql.org server? I haven't been able to access it now for a couple of days. Regards, Ben CARILLION VALUES Openness - Collaboration - Mutual Dependency - Professional Delivery -Sustainable Profitable Growth - Innovation ** This e-mail transmission, including any attachments, is confidential to the intended recipient. It may contain privileged and confidential information. If you have received this e-mail in error, please delete it and notify the [EMAIL PROTECTED] You must not disclose its contents to anyone, retain, copy, distribute or take action in reliance upon it. Carillion may monitor outgoing and incoming e-mails. By replying to this e-mail you give your consent to such monitoring. Carillion plc: Registered in England No. 3782379 Registered Office: Birch Street Wolverhampton WV1 4HY. This message has been scanned for viruses by BlackSpider MailControl http://www.blackspider.com/, however, Carillion does not accept any responsibility for viruses and it is your responsibility to scan or otherwise check this e-mail and any attachments. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Pgsql on Solaris
On 10/31/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: How widespread is the use of PostgreSQL on Solaris? I am beginning to sense that community support is not currently very strong on this platform, and that pgsql may not be the best candidate for my current project -- installing LXR on a 64-bit Solaris system. I recently compiled pg on a sun solaris 10/ultra sparc iii with no issues. I had never used sparc or solaris prevously. Once I got a handle on Sun's particular spin on unix, I determined the box to be quite an amazing workhorse. merlin ---(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] blocking mode
am Wed, dem 01.11.2006, um 10:58:21 +0100 mailte Bobby Gontarski folgendes: From time to time I get Notice: Unknown: Cannot set connection to blocking mode in Unknown on line 0. What it is? Is it really a problem with postgresql. I am using Apache/2.2.3 (Unix) PHP/5.1.6/ postgresql 8.1 As far as i know, this is a PHP-bug, not a PostgreSQL-bug. Ask Google for PHP-bug #31411 Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(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] blocking mode
Bobby Gontarski wrote: From time to time I get Notice: Unknown: Cannot set connection to blocking mode in Unknown on line 0. What it is? Is it really a problem with postgresql. I am using Apache/2.2.3 (Unix) PHP/5.1.6/ postgresql 8.1 I can't find the string connection to blocking mode in the source for 8.1. If this was a PostgreSQL error you should be able to get it to appear in your PostgreSQL logs. Perhaps look at whatever is reporting the error, although the Unknown on line 0 suggests to me that it might not be easy to track this down. -- Richard Huxton Archonet Ltd ---(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] Compiling/Installing as a non-admin user
Ritesh Nadhani wrote on 01.11.2006 07:51: Hello All Me and my professor are planning to work upon machine learning in postgresql over tsearch2. So I have some questions: We have a server where Postgresql is running without any problem with postgres username and admin rights. I have a user account in that server. I plan to compile and run another postgresql for our testing so I was thinking of how to do that? My prior knowledge of using postgresql has always been as admin where I have full rights. As I see, using the default MAKE for postgresql will set the data directory etc. in /usr/local/data etc which I dont have access to as a user. So I would like to compile and run postgresql as a normal user with every thing like data kept in my usr directory. I should be able to run the instance over separate port and can start and stop it. Basically, I want to run the server as in user mode How should I configure the MAKE and INSTALL in this circumstances? What are your suggestions I have no experience with PG on Unix/Linux so if I'm missing something please bear with me. The port where postmaster is listening on is defined in postgresql.conf which is local to the data directory. Couldn't you simply initdb using e.g. $HOME/pgdata, then edit $HOME/pgdata/postgresql.conf change the port and then start (the already installed) postmaster from within your acount using the -D switch pointing to $HOME/pgdata? As far as I can tell, all you'd need is execute rights on the installed postmaster (maybe that's the problem...) Thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Grouping My query
Martin Kuria wrote: Dear Sir/Madam, I have a staff directory, which group staff in various categories I would like to group the staff members in there specific categories, Below is an output I would like to achieve can this be achived my using the Group by or I can write a script to achieve the output below please do assist. As you can see the staff members are group in their respective Categories and Subcategory unlike the output above. staff_lname | staff_fname | staff_id | staff_catid | staff_subcatid | cat_acron | subcat_acron | staff_sortorder -+-+--+-++---+--+- ADSDS ODA Vacant | Vacant | 8836 | 500 |534 | ADSDS | ODA | 0 n | a | 7148 | 500 |534 | ADSDS | ODA | 0 WEB b | Sb | 7150 | 500 |534 | ADSDS | WEB | 1 x | cc | 7174 | 500 |534 | ADSDS | WEB | 1 e | dd | 7173 | 500 |534 | ADSDS | WEB | 2 Am I right in thinking you're trying to do headings here? heading1: ADSDS heading2: ODA ...rows... heading2: WEB ...rows... This is a formatting issue - do it in your client code. There are report -generating systems available for many languages. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Updated: Compiling/Installing as a non-admin user
Hello All Thanks for all the suggestions. I was able to configire and compile it. I set the data directory to one of my directories, use 'initdb' successfully. I am also able to start the pgsql and can see that it is running on port as I had configured. Now the problem is when I try to create a db using createdb, I get the error: createdb: could not connect to database postgres: FATAL: role ritesh does not exist I am not sure what is that? Doing a google gave me: http://pgfoundry.org/pipermail/pgcluster-general/2006-May/000699.html You must be postgres user to run this. Another option might be passing a user switch . I cannot run with postgres as I dont have access and I am not sure how to use the user switch? Any help? I guess once I am through with this, I am successful! Ritesh Richard Huxton wrote: Gurjeet Singh wrote: You are talking about /usr/local/data; so I assume that you are trying this on Linux or some other Nixen. You just need to use the --prefix option to configure... Here's what I typically do: Download/'CVS checkout' the sources. Enter the source directory, and invoke configure like this: ./configure --enable-debug --prefix `pwd`/db CFLAGS=-O0 And the run 'make' and 'make install'. This will install the data in your sources_dir/db/data. And then of course you'll need to remember to set your port to something other than 5432, and tweak your PATH, PGPORT etc or define some aliases/wrappers so you don't end up running against the default installation. Oh, and you'll need to tweak the startup scripts and logging configuration so you get logs somewhere useful. I think Tom Lane has a script that lets him switch between different installations (versions in his case). I only tend to have two versions active at any one time, so I just define an alias for psql. alias psql82='/usr/local/pgsql82/bin/psql -p 5434' On the rare occasion when I run an 8.2 createdb I need to remember to put the port number in manually of course. HTH ---(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] Encoding, Unicode, locales, etc.
Karsten Hilbert [EMAIL PROTECTED] writes: Could this paragraph be put into the docs and/or the FAQ, please ? Section 21.2. Character Set Support already has something about it: Important: Although you can specify any encoding you want for a database, it is unwise to choose an encoding that is not what is expected by the locale you have selected. The LC_COLLATE and LC_CTYPE settings imply a particular encoding, and locale-dependent operations (such as sorting) are likely to misinterpret data that is in an incompatible encoding. Since these locale settings are frozen by initdb, the apparent flexibility to use different encodings in different databases of a cluster is more theoretical than real. It is likely that these mechanisms will be revisited in future versions of PostgreSQL. One way to use multiple encodings safely is to set the locale to C or POSIX during initdb, thus disabling any real locale awareness. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Encoding, Unicode, locales, etc.
On Wed, Nov 01, 2006 at 11:41:43AM +0100, Karsten Hilbert wrote: Could this paragraph be put into the docs and/or the FAQ, please ? Along with the recommendation that if you require multiple encodings for your databases you better had your OS locale configured properly for UTF8 and use UNICODE databases or do initdb with the C-locale. Err, multiple encodings don't work full-stop. Any particular locale (as defined by POSIX) is only really designed to work with one encoding. The fact that the C locale produces an order when sorting UTF8 text is really just luck. In hindsight the people in POSIX who decided to tie locale and encoding into one variable should probably be shot, but it's a bit late now. This stuff is certainly far from ideal, but the amount of work involved to fix it is daunting; see many past pg-hackers discussions. Here are a few data points from my Debian/Testing system in favour of not worrying too much about installed ICU size as it is being used by other packages anyways: We'd need a suitable patch first before we start worrying about that. I think diskspace is less of an issue now. There are discussions going on about having the clog and the xlog taking dozens of megabytes. At the end of the day I don't think 10MB for the Unicode data it going to be that big a deal, *if* the patch solves all the problems in this area in a reasonably clean way... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[GENERAL] time value '24:00:00'
hello, can the the current time family functions (CURRENT_TIMESTAMP, LOCALTIME, etc) reach the '24:00:00' value ? I want to compare LOCALTIME = '24:00:00'::TIME and I am curios to know if LOCALTIME '24:00:00'::TIME is sufficient. thanks, razvan radu ---(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] [GENERAL] Index greater than 8k
On 11/1/06, Teodor Sigaev [EMAIL PROTECTED] wrote: [snip] Brain storm method: Develop a dictionary which returns all substring for lexeme, for example for word foobar it will be 'foobar fooba foob foo fo oobar ooba oob oo obar oba ob bar ba ar'. And make GIN functional index over your column (to save disk space). [snip] Time of search in GIN weak depend on number of words (opposite to tsearch2/GiST), but insertion of row may be slow enough With the right folding the number of possible trigrams for ascii text is fairly small.. much smaller than the number of words in used in a large corpus of text so the GIN performance for searches should be pretty good. Real magic would be to teach the regex operator to transparently make use of such an index. ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Encoding, Unicode, locales, etc.
On Wed, Nov 01, 2006 at 08:50:30PM +0100, Martijn van Oosterhout wrote: Could this paragraph be put into the docs and/or the FAQ, please ? Along with the recommendation that if you require multiple encodings for your databases you better had your OS locale configured properly for UTF8 and use UNICODE databases or do initdb with the C-locale. Err, multiple encodings don't work full-stop. Well, yes, I was thinking of multiple client encodings which can be supported either via a C-locale-initdb with the databases set to the encoding you require (but sorting/etc won't work, I know) or by doing a unicode-initdb and using unicode databases. In each case the client encodings can be multiple ones - as long as conversion is possible. Sorting etc may still be wrong, but at least the proper characters are going in and coming back. Any particular locale (as defined by POSIX) is only really designed to work with one encoding. Sure. What I meant is that if you have a unicode database you can use several client encodings and get back the properly encoded characters. The fact that the C locale produces an order when sorting UTF8 text is really just luck. Yes. Here are a few data points from my Debian/Testing system in favour of not worrying too much about installed ICU size as it is being used by other packages anyways: We'd need a suitable patch first before we start worrying about that. I think diskspace is less of an issue now. Well, size did come up in a recent discussion so I thought I'd mention the above facts. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(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] [GENERAL] Index greater than 8k
[EMAIL PROTECTED] wrote: On Tue, Oct 31, 2006 at 08:55:04PM -0800, Joshua D. Drake wrote: We are not storing bytea [...] [...] Hmm, have you tried to create a functional trigram index on the equivalent of strings(bytea_column) or something like that? Hrm. Sorry for my impolite interuption, but... is there such a thing as a functional trigram index? (this would be very cool). Heh :-) I meant an index, using the pg_trgm opclass (which indexes trigrams; hence the trigram part), on a function that would extract the text from a bytea column; instead of indexing the trigrams of the bytea column directly. Hence the functional part. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Index greater than 8k
On Tue, 31 Oct 2006, Joshua D. Drake wrote: Yes we do (and can) expect to find text among the bytes. We have searches running, we are just running into the maximum size issues for certain rows. you can use substr() to be safe, if schema change doesn't available Sincerely, Joshua D. Drake Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Compiling/Installing as a non-admin user
Thanks to all of you. I have get it up and running according to my needs. Help by everybody is appreciated! Richard Huxton wrote: Gurjeet Singh wrote: You are talking about /usr/local/data; so I assume that you are trying this on Linux or some other Nixen. You just need to use the --prefix option to configure... Here's what I typically do: Download/'CVS checkout' the sources. Enter the source directory, and invoke configure like this: ./configure --enable-debug --prefix `pwd`/db CFLAGS=-O0 And the run 'make' and 'make install'. This will install the data in your sources_dir/db/data. And then of course you'll need to remember to set your port to something other than 5432, and tweak your PATH, PGPORT etc or define some aliases/wrappers so you don't end up running against the default installation. Oh, and you'll need to tweak the startup scripts and logging configuration so you get logs somewhere useful. I think Tom Lane has a script that lets him switch between different installations (versions in his case). I only tend to have two versions active at any one time, so I just define an alias for psql. alias psql82='/usr/local/pgsql82/bin/psql -p 5434' On the rare occasion when I run an 8.2 createdb I need to remember to put the port number in manually of course. HTH ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] time value '24:00:00'
why don't you just use '00:00:00'::time and avoid the issue? IMHO there shouldn't even be a 24:00:00, because that would imply that there is a 24:00:01 - which there is not. It should go from 23:59 to 00:00 But then, I didn't write the spec for time in general, so maybe there is a 24:00 which is identical to 00:0= UC On Wednesday 01 November 2006 13:15, [EMAIL PROTECTED] wrote: hello, can the the current time family functions (CURRENT_TIMESTAMP, LOCALTIME, etc) reach the '24:00:00' value ? I want to compare LOCALTIME = '24:00:00'::TIME and I am curios to know if LOCALTIME '24:00:00'::TIME is sufficient. thanks, razvan radu ---(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 -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Grouping My query
Dear Richard, Thanks for your response, you are right I am trying to do headings, I thought I could do it from writing a SQL statement, but as you have suggested it can only be achieved on the client code, I wish one day it can be achieved with a SQL Statament thanks again . Kind Regards. +-+ | Martin W. Kuria (Mr.) [EMAIL PROTECTED] ++ From: Richard Huxton dev@archonet.com To: Martin Kuria [EMAIL PROTECTED] CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] Grouping My query Date: Wed, 01 Nov 2006 18:50:46 + Martin Kuria wrote: Dear Sir/Madam, I have a staff directory, which group staff in various categories I would like to group the staff members in there specific categories, Below is an output I would like to achieve can this be achived my using the Group by or I can write a script to achieve the output below please do assist. As you can see the staff members are group in their respective Categories and Subcategory unlike the output above. staff_lname | staff_fname | staff_id | staff_catid | staff_subcatid | cat_acron | subcat_acron | staff_sortorder -+-+--+-++---+--+- ADSDS ODA Vacant | Vacant | 8836 | 500 |534 | ADSDS | ODA | 0 n | a | 7148 | 500 |534 | ADSDS | ODA | 0 WEB b | Sb | 7150 | 500 |534 | ADSDS | WEB | 1 x | cc | 7174 | 500 |534 | ADSDS | WEB | 1 e | dd | 7173 | 500 |534 | ADSDS | WEB | 2 Am I right in thinking you're trying to do headings here? heading1: ADSDS heading2: ODA ...rows... heading2: WEB ...rows... This is a formatting issue - do it in your client code. There are report -generating systems available for many languages. -- Richard Huxton Archonet Ltd _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(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] Encoding, Unicode, locales, etc.
Carlos Moreno [EMAIL PROTECTED] writes: ... The one that does the case conversion correctly (read: as I expect it as per Spanish or French rules) is 8.1.4 with en_US locale (LC_CTYPE and LC_COLLATE both showing en_US.UTF-8). PG 7.4.14, *even with locale es_ES*, does not do the case conversion (characters with accent or tilde are left untouched). IIRC, 7.4 has no chance of doing upper/lower sanely with multibyte UTF8 characters, because it only knows about the old-line toupper/tolower ctype.h functions. 8.0 and up know about wctype.h and can do this as you'd expect. See the CVS history at http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/oracle_compat.c regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Updated: Compiling/Installing as a non-admin user
Before using the createdb binary, su the Linux console as the same user which you used while doing the initdb process on your $PGDATA folder.Thanks,Shoaib MirEnterpriseDB ( www.enterprisedb.com)On 11/1/06, Ritesh Nadhani [EMAIL PROTECTED] wrote: Hello AllThanks for all the suggestions. I was able to configire and compile it.I set the data directory to one of my directories, use 'initdb'successfully. I am also able to start the pgsql and can see that it is running on port as I had configured.Now the problem is when I try to create a db using createdb, I get theerror:createdb: could not connect to database postgres: FATAL:role ritesh does not existI am not sure what is that?Doing a google gave me:http://pgfoundry.org/pipermail/pgcluster-general/2006-May/000699.html You must be postgres user to run this. Another option might bepassing a user switch .I cannot run with postgres as I dont have access and I am not sure howto use the user switch? Any help? I guess once I am through with this, I am successful!RiteshRichard Huxton wrote: Gurjeet Singh wrote: You are talking about /usr/local/data; so I assume that you are trying this on Linux or some other Nixen. You just need to use the --prefix option to configure... Here's what I typically do: Download/'CVS checkout' the sources. Enter the source directory, and invoke configure like this: ./configure --enable-debug --prefix `pwd`/db CFLAGS=-O0 And the run 'make' and 'make install'. This will install the data in your sources_dir/db/data. And then of course you'll need to remember to set your port to something other than 5432, and tweak your PATH, PGPORT etc or define some aliases/wrappers so you don't end up running against the default installation. Oh, and you'll need to tweak the startup scripts and logging configuration so you get logs somewhere useful. I think Tom Lane has a script that lets him switch between different installations (versions in his case). I only tend to have two versions active at any one time, so I just define an alias for psql. alias psql82='/usr/local/pgsql82/bin/psql -p 5434' On the rare occasion when I run an 8.2 createdb I need to remember to put the port number in manually of course. HTH---(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
