logo
IRC Archive / Freenode / #postgresql / 2010 / April / 20 / 1
DJChill
hi
linuxpoet
ho
DJChill
this is hard to set up lol
trying to figure out how to set up my own chat
maciej229
Hey i am having a lot of trouble with postgres
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
endpoint_david
??pivot
pg_docbot_adz
Nothing found
maciej229
ya i have looked at that but get an error
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
?
endpoint_david
maciej229: it's in contrib/
so not installed by default
oskie: yes
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?
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.
other*
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
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
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
endpoint_david
(Action) 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
but i ran yum update
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 ?
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...
1) can it be paused/terminated/etc safely?
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
fejes: also your settings are relevant
fejes
*nods*
I don't have permissions to modify the config file, so it's lileky to be entirely untuned.
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?
you can use SHOW to display the values in effect
fejes
16Mb on mainenance_work_mem
that's harsh.
ok.
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
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.
thanks - RhodiumToad... I'll give that a try.
shared buffers is 256Mb
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
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...
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) 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?
i.e., non-alphabetic or end of string?
sternocera
(Action) basically doesn't know regex
endpoint_david
.$ will require there to be a character there
sternocera
That works
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?
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
... 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
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
only english alphabet works
endpoint_david
(Action) points cojack to sternocera for further improvements
cojack
if you using some strings like BóD this will not work
Tapout
log_destination = 'stderr'
cojack
(Action) 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
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
*I think*
Tapout
RhodiumToad, she's hosed eh?
sternocera
provided they don't use C locale
*I think*
cojack
huh, out of beer, have to go sleep
RhodiumToad
Tapout: have you tried increasing the debug level?
Tapout: e.g. log_min_messages=debug5 or the command-line equivalent
sternocera
(Action) noticed that RhodiumToad hasn't corrected him, so he must be right :-)
RhodiumToad
(Action) doesn't always pay attention to everyone
sternocera: [A-Z] doesn't work in all alphabets; it depends on collating sequence
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
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
and my user's locale is en_GB.UTF8, even though the 'ó' letter is obviously not in the English alphabet
hm, that's interesting http://pastebin.com/h7Fj5iKq
some funny characters get caught by [[:lower:]] and some don't
RhodiumToad
actually, come to think of it, [[:lower:]] in regexps is broken anyway
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
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
davidfetter: please peer review: http://pgsql.privatepaste.com/8c896a1e57
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:
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?