DJChill	hi
linuxpoet	ho
DJChill	this is hard to set up lol
DJChill	trying to figure out how to set up my own chat
maciej229	Hey i am having a lot of trouble with postgres
maciej229	is anyone familiar with crosstab?
mst	DJChill: 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
maciej229	sorry here http://pgsql.privatepaste.com/51f87608d9
endpoint_david	??pivot
pg_docbot_adz	Nothing found
endpoint_david	maciej229: http://www.postgresql.org/docs/current/static/tablefunc.html
maciej229	ya i have looked at that but get an error
maciej229	unknown function crosstab(unknown,unknown)
oskie	are functions automatically run as a transaction? on other words, you don't need BEGIN; .. COMMIT;?
maciej229	is tablefunc included in 8.4.3
maciej229	?
endpoint_david	maciej229: it's in contrib/
endpoint_david	so not installed by default
endpoint_david	oskie: yes
endpoint_david	an error in the function will rollback the entire transaction
oskie	ok, cool
maciej229	david could i do this with supplied functions
endpoint_david	maciej229: you may need to install the postgresql-contrib package on whatever your particular OS is
maciej229	as i dont have admin access to the db
Redxross	Hi 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_david	maciej229: it's possible, but it would be a lot slower; I'm not sure off-hand, most examples have just used crosstab()
oskie	Class(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?
oskie	FOREIGN KEY (namespace, name, TRUE) REFERENCES Class (namespace, name, active) did not work :(
Redxross	Anyone got any ideas why initdb fails =(
oicu	unless you show us HOW it's failing that is an unanswerable question
oskie	Redxross: well do you have libxlt.so.1? try sudo ldd /usr/bin/initdb
maciej229	ok thanks david.  I was assigned to the largest database in a fortune 50 company when i have little db experience. Dont know why.
oicu	wtf is libxlt?
Redxross	oskie, upon running it, its not found along with a bunch of toher .so.
Redxross	other*
Redxross	oskie, how do I fix it?
oskie	Redxross: try running ldconfig as root.. or make sure the right dirs are in /etc/ld.so.conf
Redxross	oskie, ok will try it now
endpoint_david	Redxross: 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_david	s/the postgres/the postgres user/
Redxross	endpoint_david, I do... I will look into it too
oicu	Redxross, if initdb is built correctly it should need almost no external libraries
oicu	where did this come from?
Redxross	oicu, 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
oicu	i mean where did your initdb come from?
sternocera	hmm. 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?
Redxross	it was preinstalled by the company who hosts our server
Redxross	but i ran yum update
Redxross	i think thats what you are asking right?
oicu	yes, i guess. what OS is running?
endpoint_david	sternocera: you'd need the flags arg for all matches ('g')
Redxross	centos with selinux
endpoint_david	perhaps?
sternocera	endpoint_david: Alright. I don't get why it returns SET OF text[], and not just text[]
fejes	can anyone give me some advice on how to determine how long an autovacuum should take on a table with 260M+ rows?
oicu	well, on my centos/selinux box initdb doesn't require libxlt
endpoint_david	sternocera: did you read http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP ?
endpoint_david	specifically the examples about regexp_matches?
sternocera	sorry, just am, thanks.
oicu	it is liked with libxslt for some insane reason
fejes	just in case no one noticed it earlier, can anyone help with some questions on autovacuum?
RhodiumToad	what are the questions?
fejes	I have an autovacuum that's been running for 3 days...
fejes	1) can it be paused/terminated/etc safely?
fejes	2) is there a way to tell how long it would take to finish if left on it's own?
andres	fejes: it depends heavily on the pg version and when you vacuumed last
andres	fejes: also your settings are relevant
fejes	*nods*
fejes	I don't have permissions to modify the config file, so it's lileky to be entirely untuned.
fejes	I am working on getting those permissions.
RhodiumToad	how big is the table, and what are your settings for maintenance_work_mem, and all the vacuum_* cost/delay settings?
RhodiumToad	you can use SHOW to display the values in effect
fejes	16Mb on mainenance_work_mem
fejes	that's harsh.
fejes	ok.
fejes	no wonder it's taking forever
andres	Sounds like a default config.
fejes	yep
andres	I guess shared_buffers is at 24m or such?
XoniX	/j postfix
RhodiumToad	if the table has a lot of dead rows in it, then that maintenance_work_mem is liable to be a huge bottleneck, yes
RhodiumToad	btw, cancelling the vacuum with pg_cancel_backend is safe
fejes	the IT guys who set this up are unwilling to share passwords, and aren't willing to put in the time.
fejes	thanks - RhodiumToad... I'll give that a try.
fejes	shared buffers is 256Mb
fejes	also brutal.
RhodiumToad	fejes: if you can connect to postgres as the postgres user, you can in fact alter the config anyway, though it's not really encouraged
RhodiumToad	256mb for shared buffers is not the default, so that at least has been tuned
andres	RhodiumToad: but not necessarily restart the server.
fejes	RhodiumToad: I can't.  they gave me a user with admin rights, for creating tables...
fejes	not much else.
RhodiumToad	andres: true, but you can reload the config
fejes	this 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)
sternocera	Hi. How can I have a regex match the last character of my string?
endpoint_david	'.$' ?
sternocera	endpoint_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"
cojack	how to set schema to current use?
endpoint_david	sternocera: might you just want ([^A-Za-z]|$) in that case?
endpoint_david	i.e., non-alphabetic or end of string?
Action: sternocera basically doesn't know regex
endpoint_david	.$ will require there to be a character there
sternocera	That works
sternocera	great, finished (I think)
endpoint_david	regexes are good to learn; I'd highly recommend studying them
sternocera	endpoint_david: check it out: http://pgsql.privatepaste.com/b1b4d76a37
cojack	endpoint_david: posix standards says about /i modulator,       If this modifier is set, letters in the pattern match both upper and lower case letters.
sternocera	endpoint_david: One for the wiki?
endpoint_david	cojack: eh?
endpoint_david	sternocera: not seeing any obvious issues
sternocera	endpoint_david: As I've said, I'm aware of at least two other people asking about it.
endpoint_david	sternocera: could definitely use a better name/explanation
sternocera	endpoint_david: I like the name, agree about the explanation
cojack	endpoint_david: http://www.postgresql.org/docs/current/static/functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE
Tapout	unable 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?
RhodiumToad	Tapout: no other error message?
Tapout	before 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_david	cojack: right, but I'm not understanding what you pointed it out for
cojack	endpoint_david, enought [a-z]/i
endpoint_david	cojack: it was [^A-Za-z], which matches everything except [A-Za-z]
cojack	endpoint_david, enought [^a-z]/i
endpoint_david	cojack: I'm not sure if that's true or not
cojack	or use [[:alpha:]]
Tapout	RhodiumToad, 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
cojack	but I'm not sure POSIX character classes exists in postgresql
endpoint_david	cojack: they do
endpoint_david	I was using sternocera's code examples
RhodiumToad	Tapout: what are your settings for log_destination etc?
cojack	endpoint_david: there is still missing utf-8 string in those characters
cojack	only english alphabet works
Action: endpoint_david points cojack to sternocera for further improvements
cojack	if you using some strings like BóD this will not work
Tapout	log_destination = 'stderr'
Action: cojack realy want to know how japanise developers regexp utf-32 encode characters ;D
RhodiumToad	Tapout: and silent_mode is off? and logging_collector also off?
endpoint_david	cojack: :-)
ryan-g	does pg add not null constraints automatically?
sternocera	cojack: 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
sternocera	That's locale defined, but I thought that é and stuff came just after e typically
toruvinn	cojack, won't \p{Letter} work?
cojack	sternocera: Im sure, becouse I have to add my letters into regexp pattern
sternocera	cojack: Do you use C locale?
cojack	C locale?
sternocera	yes
sternocera	What's your servers locale? That changes sort order of strings
cojack	sternocera: 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*
Tapout	RhodiumToad, she's hosed eh?
sternocera	provided they don't use C locale
sternocera	*I think*
cojack	huh, out of beer, have to go sleep
RhodiumToad	Tapout: have you tried increasing the debug level?
RhodiumToad	Tapout: 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
RhodiumToad	sternocera: [A-Z] doesn't work in all alphabets; it depends on collating sequence
RhodiumToad	sternocera: e.g. if a locale has certain letters collating after Z, they won't be included
wulczer_1	sternocera: select regexp_matches('ó', '[A-Za-z]'); gives me no results in UTF8 encoding
RhodiumToad	sternocera: this is why [[:upper:]] and [[:lower:]] exist
RhodiumToad	wulczer_1: in what locale?
sternocera	RhodiumToad: OK, thanks
Gary_B	whats a tool in windows with a "diagram designer" style view
wulczer_1	RhodiumToad: server and client encoding are UTF8
wulczer_1	and my user's locale is en_GB.UTF8, even though the 'ó' letter is obviously not in the English alphabet
wulczer_1	hm, that's interesting http://pastebin.com/h7Fj5iKq
wulczer_1	some funny characters get caught by [[:lower:]] and some don't
RhodiumToad	actually, come to think of it, [[:lower:]] in regexps is broken anyway
RhodiumToad	what pg version are you using?
wulczer_1	9.0devel
sternocera	RhodiumToad: Please peer review: http://pgsql.privatepaste.com/7a70c2d60e
RhodiumToad	ahh, 9.0 has some fixes for it
RhodiumToad	sternocera: what's with the () inside [] ?
sternocera	RhodiumToad: I guess it's superfluous
RhodiumToad	well, it'll mean that the characters ( and ) are matched by that expression
sternocera	d'oh
Tapout	RhodiumToad, nothing more comes out, exit 1
sternocera	Yeah, 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_B	oops just seen i can use the query designer in pgadmin to make a graphical view! bye all
wulczer_1	sternocera: is "D'Oh" the correct capitalization of "d'oh"? :o)
sternocera	wulczer_1: heh
sternocera	davidfetter: please peer review: http://pgsql.privatepaste.com/8c896a1e57
sternocera	davidfetter: One for the wiki?
fejes	is 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_adz	For information about 'tuning' see:
pg_docbot_adz	http://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_adz	http://wiki.postgresql.org/wiki/Performance_Optimization :: http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
davidfetter	sternocera, "irishly aware?"
sternocera	davidfetter: Indeed
fejes	thanks
Tapout	RhodiumToad, anyway to force it ?
davidfetter	ok, need to get back to work :)
RhodiumToad	force what?
sternocera	davidfetter: 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