[GENERAL] European users mailing list
Hi, As discussed at the first meeting of what will become the European PostgreSQL Users Group after pgDay in Prato, we now have a mailing list setup at [EMAIL PROTECTED] This is for the European users group, so is not really intended as a technical list but as a place to discuss events, advocacy and other topics relevant to our work in Europe. To subscribe, send the word 'subscribe' to [EMAIL PROTECTED] To unsubscribe, send the word 'unsubscribe' Apologies for the delay in setting this up - the list is there now, archives will be sorted early next week. Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] create function error
We have gotten these errors every time we try to create a function through psql. However, if we run the same statements using phpPgAdmin or pgAdmin III query tool it works fine. Here's the error- ERROR: unterminated dollar-quoted string at or near "$$ BEGIN NEW.mod_date := now();" at character 63 ERROR: syntax error at or near "RETURN" at character 9 WARNING: there is no transaction in progress ERROR: unterminated dollar-quoted string at or near "$$ LANGUAGE plpgsql;" at character 1 ERROR: function public.setproposalmoddate() does not exist Here is a script that produces the error- CREATE OR REPLACE FUNCTION setproposalmoddate() RETURNS TRIGGER AS $mod_date$ BEGIN NEW.mod_date := now(); RETURN NEW; END; $mod_date$ LANGUAGE plpgsql; CREATE TRIGGER dcproposalmodified BEFORE UPDATE ON dcproposal FOR EACH ROW EXECUTE PROCEDURE setproposalmoddate(); What's more, if we use pgAdmin III to create the function, if we do an export and then try to import into another db with psql, we get the same error. select version() returns PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.3 20041212 (Red Hat 3.4.3-9.EL4) Thanks, -- Tony Crisera ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Question about Postgres
I apologize for cross-posting, but I need some help w/o too many advices RTFM :). After Oracle and MySQL, this becomes the third product that I need to learn to some degree, and I need a few links which would provide a 'quick tutorial' especially for folks with Oracle background like myself. Last time I had to deal with MySql it took me a few days just to figure out how to login, and then how to poke around, and then a few more to finally start writing some useful code in whatever language they use that's similar to PL/SQL. We are running a mail server, which for whatever stupid reason uses a database (stupid, b/c it only uses it for web access, mail is actually on the file system) I'd like to know a couple of things a) how do I access this thing as a DBA to poke around b) how do I poke around c) do I need to make any modifications to config file d) what is the most common 'default' values that need to be changed what's the best way to see what a performance bottleneck is (i e) why this doesn't work: [EMAIL PROTECTED] httpd]# vacuumdb -a -f -z -q -h 10.0.1.93 -p5733 vacuumdb: could not connect to database template1: FATAL: no pg_hba.conf entry for host "10.0.1.93", user "root", database "template1", SSL off Some 'details' on the server: (ps -ef) 00:00:04 /usr/bin/postmaster -i -h mt-adm.mentora.biz -p 5733 -D /var/opt/scalix/mm/postgres/data -k /var/opt/scalix/mm/postgres/data 00:00:00 postgres: stats buffer process 00:00:00 postgres: stats collector process 00:00:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data 00:00:00 postgres: stats buffer process 00:00:00 postgres: stats collector process The above is confusing.. do I have 2 instances (or databases) running on different ports? Should I shutdown the default one? (Scalix is the product that uses the db) Should I be 'playing' with /var/opt/scalix/mm/postgres/data/postgresql.conf ? [EMAIL PROTECTED] httpd]# du -skh /var/opt/scalix/mm/postgres/data 276M/var/opt/scalix/mm/postgres/data # "database" is rather small # there are no more than 20-30 users on the server at any given time # the disks are fast (50megs/sec, RAID10, SCSI) # memory is big 8g # cpu count is 2 with hyperthreading (it's a dell 2650) [EMAIL PROTECTED] data]# rpm -qa|grep post postgresql-libs-7.4.17-1.RHEL4.1 postgresql-server-7.4.17-1.RHEL4.1 postgresql-7.4.17-1.RHEL4.1 scalix-postgres-11.0.4.25-1 Files that seem important: /var/opt/scalix/mm/postgres/data [EMAIL PROTECTED] data]# cat pg_hba.conf|grep -v \# hostscalix scalix 10.0.1.201/32 md5 local allall ident sameuser postgresql.conf (comments taken out) max_connections = 100 shared_buffers = 1000 # bunch of locale params en_US.UTF-8' http://www.scalix.com/forums/viewtopic.php?t=7809&highlight=sharedbuffers There are some suggestions here, but they seem rather high (I did look at some of them ,and the majorify make sense tweaking, I don't think I fully understood the vacuum paramter) ... We run Oracle 9iR2,10gR1/2 on RH4/RH3 and Solaris 10 (Sparc) remove NSPAM to email ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] query to match '\N'
Hi, I have a table test with columns col1 col2.col2 contains an entry '\N' .I want to select all entries which have '\N' in col2.How do i do that? select * from test where col2 like '\N' ; select * from test where col2 like '\\N' ; both return 0 rows.Could some one please tell me the right query? Thanks Priya ---(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
[GENERAL] tsearch2 problems in postgres?
SELECT *, rank(ts_vec, to_tsquery('default', 'lc & 11(')) FROM us
WHERE ts_vec @@ to_tsquery('default', 'lc & 11(') LIMIT 9 OFFSET 0
this query gives an error
in tsearch2
saying syntax error
any idea on how 2 strip the non safe tsearch stuff
before feeding it to sql
this is from a web search interface?
psycopg2.ProgrammingError at /search ->
if i give inpu as lc !! (
is there a list of characters that need to stripped out before doing
@@ in tsearch2
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] Require entry of MD5 hash instead of plaintext password?
Is there a way to configure PostgreSQL 8.0 so that when prompted for a
password, the user enters the MD5 hash of his password, instead of the
normal plaintext password?
That is, when prompted for the password, instead of typing the
plaintext password as you normally would ("Foo", for example), you
would instead enter in the MD5 hash ("A8127A7CBLAHBLAHBLAHBLAH").
---(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
[GENERAL] locale and performance?
Hello, in the documentation is written that there'a a perfomrance impact if another locale than C or POSIX is used. problem is that using the standard locale results in unusable order results for languages with spcial chars. in my example "umlauts" in german. it isn't acceptable to use the database without indexes if another locale is used. are custom operator classes as described in the manual as high-performance as the native index? are there any examples how to use them in my case? are there any tests that show how the performance decreases if another locale is used? why is there a perfomance impact? it's a disappointing that postgresql is only in one configuration to suply the max. performance. any tips or hints on that? regards Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PostgreSQL install problem
HiI have a problem . I try install PostgreSQL but i cant do that becouse i have an error window. when i am config "initialise database cluster" window and click next then an error window popup , and write : "The "Secondary Logon" service is not running. The service is requied for the installer to initialize the database. Please start the service and try again" I dont know where is this service and how i can run it. So please write what i do becouse i try install this about few days , so i am very dissapointed.Thansk your help !Istvan BodoHungary _ Éhes vagy? Internetes ételrendelés, egyszerűen, házhozszállítással! Pizzák, hamburgerek, saláták, stb Minden egy helyen! KLIKK IDE!
[GENERAL] C.H.E.A.P...M.A.R.L.B.O.R.O...C.I.G.A.R.E.T.T.E.S.... ==== CEZukcY5
Some sites with cheap marlboro: http://www.google.com/search?q=new%20marlboro%20cigarettes&hl=en He may neatly move between polite clever summers. I was moving pitchers to active Quincy, who's calling in front of the shirt's window. Who excuses amazingly, when Simon irritates the cold pool against the hair? We recommend the dirty envelope. Don't try to converse the ointments wickedly, attack them virtually. Both combing now, Rosalind and Simon recollected the weird mirrors over sick ball. The tyrants, printers, and butchers are all rude and difficult. It's very lean today, I'll cook stupidly or Winifred will order the sauces. Better expect bandages now or James will believably attempt them within you. Neil, alongside puddles weak and dull, wanders without it, solving actually. It can pull crudely, unless Simone dyes jars against Rachel's shopkeeper. For Gilbert the boat's tired, between me it's sharp, whereas outside you it's measuring long. No hot dose or monument, and she'll deeply fear everybody. He might seemingly promise under sour shallow rivers. Every rich rural pickles weakly fill as the humble dusts clean. They are killing over younger, to clean, for noisy oranges. Otherwise the ache in Anthony's porter might receive some angry frogs. Let's care in the inner showers, but don't mould the dry clouds. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Data on NAS / NFS
Hi list, I'm looking for advices / experiences concerning PostgreSQL with data storage on NAS via NFS. I've found a few references in the archives or the docs, but they are old, and opinions differ: . "There are a lot of horror stories concerning running databases (not only Postgres) over NFS." http://archives.postgresql.org/pgsql-performance/2004-06/msg00217.php . "NFS and other remote file systems are not recommended for use by POSTGRESQL. NFS does not have the same file system semantics as a local file system, and these inconsistencies can cause data reliability or crash recovery problems." http://www.postgresql.org/files/documentation/books/aw_pgsql/hw_performance/node11.html . "The problem with NFS is not so much the protocol, but whether your server is reliable. If you trust that your NAS device is reliable enough, then you should be OK." http://archives.postgresql.org/pgsql-general/2005-11/msg01362.php The NAS would be from LaCie, using 4 disks (Raid5 + spare) (http://www.lacie.com/fr/products/product.htm?pid=10876), mounted via NFS from a Linux server running Postgresql . What are the implications with regard to PostgreSQL performance and (more important) reliability? Thanks in advance. Regards, -- Jean-Denis Girard SysNux Systèmes Linux en Polynésie française http://www.sysnux.pf/ Tél: +689 483 527 / GSM: +689 797 527 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] query to match '\N'
On Fri, 27 Jul 2007, pc wrote: > Hi, > > I have a table test with columns col1 col2.col2 contains an entry > '\N' .I want to select all entries which have '\N' in col2.How do i > do that? > > select * from test where col2 like '\N' ; > select * from test where col2 like '\\N' ; select * from test where col2 like '\\N' escape ''; and select * from test where col2 like 'N'; will probably work. If you're using a recent version and turn on standard_conforming_strings you can halve the number of backslashes, see below. --- On 8.2.4 with standard_conforming_strings=off (and escape_string_warning=off) sszabo=> select '\N'; ?column? -- N (1 row) sszabo=> select '\\N'; ?column? -- \N (1 row) sszabo=> select '\\N' like '\\N'; ?column? -- f (1 row) sszabo=> select '\\N' like 'N'; ?column? -- t (1 row) sszabo=> select '\\N' like '\\N' escape ''; ?column? -- t (1 row) and with standard_conforming_strings=on sszabo=> select '\N'; ?column? -- \N (1 row) sszabo=> select '\\N'; ?column? -- \\N (1 row) sszabo=> select '\N' like '\N'; ?column? -- f (1 row) sszabo=> select '\N' like '\\N'; ?column? -- t (1 row) sszabo=> select '\N' like '\N' escape ''; ?column? -- t (1 row) ---(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] create function error
On Jul 26, 2007, at 13:22 , Tony Crisera wrote: ERROR: unterminated dollar-quoted string at or near "$$ BEGIN NEW.mod_date := now();" at character 63 ERROR: syntax error at or near "RETURN" at character 9 WARNING: there is no transaction in progress ERROR: unterminated dollar-quoted string at or near "$$ LANGUAGE plpgsql;" at character 1 Note that this is *not* the script you provided below, as $$ does not appear in the script you provided. Please provide the full output of the psql session that shows the error (i.e., statements and error output). ERROR: function public.setproposalmoddate() does not exist This is irrelevant. It's just telling you the trigger can't be created because the function doesn't exist. Here is a script that produces the error- CREATE OR REPLACE FUNCTION setproposalmoddate() RETURNS TRIGGER AS $mod_date$ BEGIN NEW.mod_date := now(); RETURN NEW; END; $mod_date$ LANGUAGE plpgsql; Works fine for me: test=# CREATE OR REPLACE FUNCTION setproposalmoddate() RETURNS TRIGGER AS $mod_date$ test$#BEGIN test$#NEW.mod_date := now(); test$#RETURN NEW; test$# END; test$# $mod_date$ LANGUAGE plpgsql; CREATE FUNCTION test=# select version(); version -- PostgreSQL 8.2.4 on powerpc-apple-darwin8.9.0, compiled by GCC powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5367) (1 row) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Require entry of MD5 hash instead of plaintext password?
On Fri, Jul 27, 2007 at 09:33:37AM -0700, [EMAIL PROTECTED] wrote: > Is there a way to configure PostgreSQL 8.0 so that when prompted for a > password, the user enters the MD5 hash of his password, instead of the > normal plaintext password? What problem are you trying to solve? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] tsearch2 problems in postgres?
somewhere you need to use to_tsvector instead of to_tsquery.
Oleg
On Thu, 26 Jul 2007, JohnSense wrote:
SELECT *, rank(ts_vec, to_tsquery('default', 'lc & 11(')) FROM us
WHERE ts_vec @@ to_tsquery('default', 'lc & 11(') LIMIT 9 OFFSET 0
this query gives an error
in tsearch2
saying syntax error
any idea on how 2 strip the non safe tsearch stuff
before feeding it to sql
this is from a web search interface?
psycopg2.ProgrammingError at /search ->
if i give inpu as lc !! (
is there a list of characters that need to stripped out before doing
@@ in tsearch2
---(end of broadcast)---
TIP 6: explain analyze is your friend
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], 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] Data on NAS / NFS
On Fri, 27 Jul 2007, Jean-Denis Girard wrote: The NAS would be from LaCie, using 4 disks (Raid5 + spare) (http://www.lacie.com/fr/products/product.htm?pid=10876), mounted via NFS from a Linux server running Postgresql . Leaving aside the general issue here for others to chime in on (my opinion is that putting a PostgreSQL database on this class of network storage would be crazy but I have no hard data to support that), I would strongly recommend against any of LaCie's products in this area. The company got a decent reputation based on their early Mac Firewire products, but it's been a number of years since they released any product that I would consider worth storing even a byte of data on. Their designs are buggy from day one, the reliability is awful, and the issues stem from their bridge chipsets. It took me only seconds to find a sample page with multiple anecdotal samples on this subject: http://reviews.pricegrabber.com/hard-drives/m/11165851/ The problems with their products are so widespread I'm sure it would be easy for you to find many more if you search around a bit. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] generating part of composite key
On Jul 26, 2007, at 5:57 PM, Stuart wrote: I have a table with a composite PK like CREATE TABLE t ( grp INT NOT NULL, itm SMALLINT NOT NULL, ..., PRIMARY KEY (grp,itm)); Normally the app takes care of providing the correct grp,itm values when inserting records. However (during a long period of development), I need to repeatedly reload data into the table from a data source (a select statement) that has grp values but no itm values. These itm values need to be small numbers (1 to COUNT(itm) for each grp value) and capture the order in which the data was generated by the select. MySql seems to have an auto_number function(?) that takes an optional argument which would be grp in this case, that (judging from the manual, I don't actually use MySql) gives the behavior I want (restarts numbering from 1 when grp value changes). I'd recommend writing a function in a language that allows you to store state information between calls, such as plperl and have it handle the counting, reseting the count every time grp changes. Of course that means you need to order by grp in your select (and grp has to be the first sort key). If you can't do that, your next best bet is to populate itm with a sequence (not resetting) and then adjust itm after the fact by selecting min(itm) ... group by grp. Might want to do that in a temp table to avoid bloating the main table. Note that anything that involves resetting a sequence or anything like that is going to be a big race condition if you have multiple inserting processes. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] Reporting
On Jul 22, 2007, at 4:18 AM, Matthew Snape wrote: I have just listened to Josh Berkus on FLOSS regarding postgresql. It was suggested that postgresql has advantages over other databases when it comes to reporting. Why is this? I'll venture a guess that Josh was referring to our ability to handle complex queries better than just about any OSS database. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] upgrade to 8.2.? or implement Slony, which first?
On Jul 25, 2007, at 6:17 PM, Joseph S wrote: If you don't mind the downtime it would be simpler to upgrade to 8.2 and then worry about Slony. If you do mind you can use slony to do the upgrade which needs much less downtime as you switch servers. Double-check with the Slony guys, but ISTR that there's an issue going all the way from 7.4 to 8.2 in a single shot. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Porting MySQL data types to PostgreSQL
On Jul 26, 2007, at 11:06 AM, Jeff Davis wrote: If you really do need an unsigned type, this is a good use of postgresql's extensible type system. You can just create an unsigned type for yourself. If you do that please start a project on pgfoundry so others can contribute and benefit. In fact, if you do start one let me know and I'll try and help out. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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
