free blog   apache   shopping directory   php powered
DJChillhi
linuxpoetho
DJChillthis is hard to set up lol
DJChilltrying to figure out how to set up my own chat
maciej229Hey i am having a lot of trouble with postgres
maciej229is anyone familiar with crosstab?
mstDJChill: oh gods. if you're trying to install GNUworld or whatever the smeg it's called, run away. now. RUN RUN RUN RUN.
maciej229 i have a query that returns a result set like this:  item | size | stock 123  | XL   | 10 123  | XXL | 5 123  | XS   | 3  and i would like get the results like this:  item | XL | XXL | XS 123  | 10  | 5     | 3
maciej229sorry here http://pgsql.privatepaste.com/51f87608d9
endpoint_david??pivot
pg_docbot_adzNothing found
endpoint_davidmaciej229: http://www.postgresql.org/docs/current/static/tablefunc.html
maciej229ya i have looked at that but get an error
maciej229unknown function crosstab(unknown,unknown)
oskieare functions automatically run as a transaction? on other words, you don't need BEGIN; .. COMMIT;?
maciej229is tablefunc included in 8.4.3
maciej229?
endpoint_davidmaciej229: it's in contrib/
endpoint_davidso not installed by default
endpoint_davidoskie: yes
endpoint_davidan error in the function will rollback the entire transaction
oskieok, cool
maciej229david could i do this with supplied functions
endpoint_davidmaciej229: you may need to install the postgresql-contrib package on whatever your particular OS is
maciej229as i dont have admin access to the db
RedxrossHi guys.  I am using this command in my script to initialize a cluster by, sudo /sbin/service postgresql initdb but it fails and when I look into pgstartup.log it says, /usr/bin/initdb: error while loading shared libraries: libxlt.so.1: cannot open shared object file: Permission denied.  This is on CentOS with selinux set to enforcing
endpoint_davidmaciej229: it's possible, but it would be a lot slower; I'm not sure off-hand, most examples have just used crosstab()
oskieClass(namespace TEXT,name TEXT,active BOOLEAN,...,PRIMARY KEY (namespace,name,active))   DefaultClass(namespace,name) <- how do I make a foreign key in DefaultClass that matches namespace,name and active=true in Class? is it possible?
oskieFOREIGN KEY (namespace, name, TRUE) REFERENCES Class (namespace, name, active) did not work :(
RedxrossAnyone got any ideas why initdb fails =(
oicuunless you show us HOW it's failing that is an unanswerable question
oskieRedxross: well do you have libxlt.so.1? try sudo ldd /usr/bin/initdb
maciej229ok thanks david.  I was assigned to the largest database in a fortune 50 company when i have little db experience. Dont know why.
oicuwtf is libxlt?
Redxrossoskie, upon running it, its not found along with a bunch of toher .so.
Redxrossother*
Redxrossoskie, how do I fix it?
oskieRedxross: try running ldconfig as root.. or make sure the right dirs are in /etc/ld.so.conf
Redxrossoskie, ok will try it now
endpoint_davidRedxross: if the unix perms are readable for the postgres, you may be running into SELinux restrictions; check the audit log if you have root
endpoint_davids/the postgres/the postgres user/
Redxrossendpoint_david, I do... I will look into it too
oicuRedxross, if initdb is built correctly it should need almost no external libraries
oicuwhere did this come from?
Redxrossoicu, i did this the last time and i had no such issues.  not sure what you mean where it came from?  i was running the command in my script
Action: endpoint_david read as x*s*lt originally and was even more confused
oicui mean where did your initdb come from?
sternocerahmm. Why does regexp_matches($$o'brein'S&country shi'S f$$, '''S[^a-zA-Z]') just return an array with one element and not two?
Redxrossit was preinstalled by the company who hosts our server
Redxrossbut i ran yum update
Redxrossi think thats what you are asking right?
oicuyes, i guess. what OS is running?
endpoint_davidsternocera: you'd need the flags arg for all matches ('g')
Redxrosscentos with selinux
endpoint_davidperhaps?
sternoceraendpoint_david: Alright. I don't get why it returns SET OF text[], and not just text[]
fejescan anyone give me some advice on how to determine how long an autovacuum should take on a table with 260M+ rows?
oicuwell, on my centos/selinux box initdb doesn't require libxlt
endpoint_davidsternocera: did you read http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP ?
endpoint_davidspecifically the examples about regexp_matches?
sternocerasorry, just am, thanks.
oicuit is liked with libxslt for some insane reason
fejesjust in case no one noticed it earlier, can anyone help with some questions on autovacuum?
RhodiumToadwhat are the questions?
fejesI have an autovacuum that's been running for 3 days...
fejes1) can it be paused/terminated/etc safely?
fejes2) is there a way to tell how long it would take to finish if left on it's own?
andresfejes: it depends heavily on the pg version and when you vacuumed last
andresfejes: also your settings are relevant
fejes*nods*
fejesI don't have permissions to modify the config file, so it's lileky to be entirely untuned.
fejesI am working on getting those permissions.
RhodiumToadhow big is the table, and what are your settings for maintenance_work_mem, and all the vacuum_* cost/delay settings?
RhodiumToadyou can use SHOW to display the values in effect
fejes16Mb on mainenance_work_mem
fejesthat's harsh.
fejesok.
fejesno wonder it's taking forever
andresSounds like a default config.
fejesyep
andresI guess shared_buffers is at 24m or such?
XoniX   /j postfix
RhodiumToadif the table has a lot of dead rows in it, then that maintenance_work_mem is liable to be a huge bottleneck, yes
RhodiumToadbtw, cancelling the vacuum with pg_cancel_backend is safe
fejesthe IT guys who set this up are unwilling to share passwords, and aren't willing to put in the time.
fejesthanks - RhodiumToad... I'll give that a try.
fejesshared buffers is 256Mb
fejesalso brutal.
RhodiumToadfejes: if you can connect to postgres as the postgres user, you can in fact alter the config anyway, though it's not really encouraged
RhodiumToad256mb for shared buffers is not the default, so that at least has been tuned
andresRhodiumToad: but not necessarily restart the server.
fejesRhodiumToad: I can't.  they gave me a user with admin rights, for creating tables...
fejesnot much else.
RhodiumToadandres: true, but you can reload the config
fejesthis is incredibly helpful guys - thanks SO MUCH for your help.
andres(At least not without major hackery. It should be possible, but I wouldnt suggest it)
sternoceraHi. How can I have a regex match the last character of my string?
endpoint_david'.$' ?
sternoceraendpoint_david: I'm combining it with a something else: ([^A-Za-z]|.$)     Doesn't seem to work.
Action: sternocera nearly has his "culturally aware initcap"
cojackhow to set schema to current use?
endpoint_davidsternocera: might you just want ([^A-Za-z]|$) in that case?
endpoint_davidi.e., non-alphabetic or end of string?
Action: sternocera basically doesn't know regex
endpoint_david.$ will require there to be a character there
sternoceraThat works
sternoceragreat, finished (I think)
endpoint_davidregexes are good to learn; I'd highly recommend studying them
sternoceraendpoint_david: check it out: http://pgsql.privatepaste.com/b1b4d76a37
cojackendpoint_david: posix standards says about /i modulator,       If this modifier is set, letters in the pattern match both upper and lower case letters.
sternoceraendpoint_david: One for the wiki?
endpoint_davidcojack: eh?
endpoint_davidsternocera: not seeing any obvious issues
sternoceraendpoint_david: As I've said, I'm aware of at least two other people asking about it.
endpoint_davidsternocera: could definitely use a better name/explanation
sternoceraendpoint_david: I like the name, agree about the explanation
cojackendpoint_david: http://www.postgresql.org/docs/current/static/functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE
Tapoutunable to load up postgresql, keep getting; 2010-04-19 16:54:44 CDT LOG:  startup process (PID 29354) exited with exit code 1
Tapout  ... anyone know what i should do?
RhodiumToadTapout: no other error message?
Tapoutbefore that, "shut down last on ..." <that error message> and then 2010-04-19 16:54:44 CDT LOG:  aborting startup due to startup process failure
endpoint_davidcojack: right, but I'm not understanding what you pointed it out for
cojack endpoint_david, enought [a-z]/i
endpoint_davidcojack: it was [^A-Za-z], which matches everything except [A-Za-z]
cojack endpoint_david, enought [^a-z]/i
endpoint_davidcojack: I'm not sure if that's true or not
cojackor use [[:alpha:]]
TapoutRhodiumToad, not much to go on eh?  nothign in /var/log/postgresql/... and i've got it setup to stderr and tail -f messages, is only showing those few lines
cojackbut I'm not sure POSIX character classes exists in postgresql
endpoint_davidcojack: they do
endpoint_davidI was using sternocera's code examples
RhodiumToadTapout: what are your settings for log_destination etc?
cojackendpoint_david: there is still missing utf-8 string in those characters
cojackonly english alphabet works
Action: endpoint_david points cojack to sternocera for further improvements
cojackif you using some strings like BD this will not work
Tapoutlog_destination = 'stderr'
Action: cojack realy want to know how japanise developers regexp utf-32 encode characters ;D
RhodiumToadTapout: and silent_mode is off? and logging_collector also off?
endpoint_davidcojack: :-)
ryan-gdoes pg add not null constraints automatically?
sternoceracojack: Are you sure? I thought it would work with all latin alphabets, because , for example, is between a-z
Tapout#silent_mode = off  #logging-collector
sternoceraThat's locale defined, but I thought that and stuff came just after e typically
toruvinncojack, won't \p{Letter} work?
cojacksternocera: Im sure, becouse I have to add my letters into regexp pattern
sternoceracojack: Do you use C locale?
cojackC locale?
sternocerayes
sternoceraWhat's your servers locale? That changes sort order of strings
cojacksternocera: I never before check string into postgresql, always into php
sternocera[^A-Za-z] ought to work with any language that uses latin characters
sternocera*I think*
TapoutRhodiumToad, she's hosed eh?
sternoceraprovided they don't use C locale
sternocera*I think*
cojackhuh, out of beer, have to go sleep
RhodiumToadTapout: have you tried increasing the debug level?
RhodiumToadTapout: e.g. log_min_messages=debug5 or the command-line equivalent
Action: sternocera noticed that RhodiumToad hasn't corrected him, so he must be right :-)
Action: RhodiumToad doesn't always pay attention to everyone
RhodiumToadsternocera: [A-Z] doesn't work in all alphabets; it depends on collating sequence
RhodiumToadsternocera: e.g. if a locale has certain letters collating after Z, they won't be included
wulczer_1sternocera: select regexp_matches('', '[A-Za-z]'); gives me no results in UTF8 encoding
RhodiumToadsternocera: this is why [[:upper:]] and [[:lower:]] exist
RhodiumToadwulczer_1: in what locale?
sternoceraRhodiumToad: OK, thanks
Gary_Bwhats a tool in windows with a "diagram designer" style view
wulczer_1RhodiumToad: server and client encoding are UTF8
wulczer_1and my user's locale is en_GB.UTF8, even though the '' letter is obviously not in the English alphabet
wulczer_1hm, that's interesting http://pastebin.com/h7Fj5iKq
wulczer_1some funny characters get caught by [[:lower:]] and some don't
RhodiumToadactually, come to think of it, [[:lower:]] in regexps is broken anyway
RhodiumToadwhat pg version are you using?
wulczer_19.0devel
sternoceraRhodiumToad: Please peer review: http://pgsql.privatepaste.com/7a70c2d60e
RhodiumToadahh, 9.0 has some fixes for it
RhodiumToadsternocera: what's with the () inside [] ?
sternoceraRhodiumToad: I guess it's superfluous
RhodiumToadwell, it'll mean that the characters ( and ) are matched by that expression
sternocerad'oh
TapoutRhodiumToad, nothing more comes out, exit 1
sternoceraYeah, I don't know regex, I'm juts hacking this together for my own use and thought others could benefit from it (I've heard it asked before several times)
Gary_Boops just seen i can use the query designer in pgadmin to make a graphical view! bye all
wulczer_1sternocera: is "D'Oh" the correct capitalization of "d'oh"? :o)
sternocerawulczer_1: heh
sternoceradavidfetter: please peer review: http://pgsql.privatepaste.com/8c896a1e57
sternoceradavidfetter: One for the wiki?
fejesis there a good resource for how to get started tuning a postgresql database?  aka, where it helps you to figure out what parameters to change, and how to determine the best value to set them at?
sternocera?? tuning
pg_docbot_adzFor information about 'tuning' see:
pg_docbot_adzhttp://www.varlena.com/GeneralBits/Tidbits/perf.html :: http://www.revsys.com/writings/postgresql-performance.html :: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server :: http://www.varlena.com/GeneralBits/116.php
pg_docbot_adzhttp://wiki.postgresql.org/wiki/Performance_Optimization :: http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
davidfettersternocera, "irishly aware?"
sternoceradavidfetter: Indeed
fejesthanks
TapoutRhodiumToad, anyway to force it ?
davidfetterok, need to get back to work :)
RhodiumToadforce what?
sternoceradavidfetter: So I take it you see no problems with posting it to the wiki?
Popular searches: This request will break your system   OmniCppComplete wget   slackware sound intel g45   xmonad and synergy   weechat urgent-hint   debian Debian DPKG wvdial arm   Screensaver GL Bump   

Generated by irclog2html.pl 2.1mg by Jeff Waugh - return