logo
IRC Archive / Freenode / #postgresql / 2010 / March / 24 / 8
peerce
??contrib
pg_docbot_adz
For information about 'contrib' see:
peerce
replace 'current' with 8.2
in the URL
iswaria
Thanks
fts is working in my local server :)
peerce
hmmm, the 8.2 manual is missin ghte contributed section
iswaria
Query: http://pastebin.com/S68BcAWa
I am getting this error: column "'" does not exist
LINE 11: AND 'Ramesh'::tsquery@@"'" & e.collecteur || " " || n...
endpoint_david
iswaria: you're using identifier quotes instead of string quotes
single quotes -> strings; double-quotes -> identifiers
iswaria
If i replace 8.2 for current, i am getting "The requested URL /docs/8.2/static/contrib.html was not found on this server."
endpoint_david
so ITYW: '''' & e.collecteur || ' ' || nom_entier || ' ' || nom_complet & ''''
davidfetter
iswaria
still not working
davidfetter
iswaria, if you're starting a new project, do not use such an old version of pg
iswaria
ok
peerce
heh. fermi's lack of a paradox.
Raptelan
endpoint_david: DRBD is O/S-level.
davidfetter
o/` i've got 99 problems o/`
(Action) puts on some danger mouse
endpoint_david
and a bottle of beer ain't one?
jim
99 might be :)
davidfetter
lol@ endpoint_david
newbie2
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
what the meaning of it?
jim
whatis "operator"?
whatis "argument"?
whatis "type"?
merlin83
newbie2: show us your query?
akretschmer
??paste
pg_docbot_adz
For information about 'paste' see:
newbie2
select * from c_order where c_order_id like '8%'
akretschmer
newbie2: use a paste site
newbie2
ok
merlin83
newbie2: hmm, you can't use like on an int/bigint.. you'd probably want c_order_id::text like '8%'
jim
perhaps "like" is the operator
newbie2
jim
newbie2: have you found what those three words mean yet? here's a phrase you should look up too: "type cast" and "explicit type cast"
once you know what those mean, all the other words are plain english
xenoterracide
newbie2 what are you trying to do?
newbie2 I'm guessing you want to find order_id number 8 ?
newbie2
yes
xenoterracide
that would be where = 8;
LIKE is for searching text
the only other thing I could imagine that you might be trying to do is find all orders that begin with the number 8
newbie2
xenoterracide
http://pgsql.privatepaste.com/9497880b7f < that should work
??select
pg_docbot_adz
For information about 'select' see:
newbie2
http://pgsql.privatepaste.com/e7de6d4662 --> it work
thx all
albert_kam
Hi, im using 8.2.6. Is redirect_stderr configuration parameter enabled by default ? I can see that it's commented in the postgresql.conf. The postgresql doc doesnt say about the default value when the paramter is commented.
RhodiumToad
it's off by default
if you haven't edited the value, the commented-out value is the default one
and you can always show the actual value in effect, if the server is up, by connecting and using SHOW
albert_kam
RhodiumToad: that's convinient .. thanks for the info ..
the doc also doesnt say whether the directory specified in the log_directory parameter will be automatically created when it's unavailable .. but i guess i can find out by trying :)
RhodiumToad
iirc it won't be
albert_kam
ok ..
and if i want to see who issued what sql statements, is the log level of 'notice' sufficient ?
RhodiumToad
see log_statement
(or log_min_duration_statement=0)
albert_kam
sorry .. just reached that section in the docs .. :)
RhodiumToad
make sure you set log_line_prefix to something suitably comprehensive
albert_kam
i used '<%t %u@%e> '
time to restart to test things out
thanks alot for the help
RhodiumToad
albert_kam: you really need %p
albert_kam
why PID ?
RhodiumToad
otherwise you can't sort out which session is which
albert_kam
ohh ..
ok2 .. adding
gintek
hello
albert_kam
Hi, i wonder why my log outputs so much from a single select statement ? the log file for a single sql select : http://pastie.org/884285 .. the conf file for the logging : http://pastie.org/884290
i dont expect to see the parse or bind duration, and i dont want to see other statements issued on pg_catalog n such. i prefer only seeing the info for the issued sql statement ..
a simpler output that's more readable
what did i miss in the configuration ? :)
merlin83
albert_kam: how are you executing those queries?
albert_kam
merlin83: i did ctrl+e (execute sql) on aquadata studio app for the sql "select * from mytable"
lemme try it on psql
RhodiumToad
albert_kam: you'll see parse/bind/execute for queries executed using PQexecParams
albert_kam: the three phases have to be logged separately because for prepared queries, there might be one parse and multiple binds, and with some client interfaces there can be multiple executes per bind
albert_kam
wow, the log output from psql is very clean and is what i wanted, lol
RhodiumToad
(Action) wonders where WIT is
albert_kam: looks like "aquadata studio" is doing a lot of its own metadata queries
very inefficiently, too
I mean, a whole roundtrip to the server _for each column_ just to check whether it's declared not null?
albert_kam
I see ..
RhodiumToad
on a remote server that'd be seriously painful
albert_kam
thats the gui tool we use here as the sql manager, to do sql devel .. it's a shame it issued many sql statements ..
Strange why it's WIT, should be WIB. Btw, im in Jakarta, Indonesia. But perhaps people dont usually know Indonesia, but they know Bali :)
RhodiumToad
yeah, I guessed that from running select * from pg_timezone_names where abbrev='WIT';
KeiKurono
morning
kll
albert_kam: selamat pagi!
albert_kam: oh, well, for me at least :)
albert_kam
kll: selamat siang here :)
RhodiumToad: your opinion on the database client now makes me to find other alternatives .. any recommendation for all-purpose GUI database ? perhaps http://torasql.com/ ? :)
akretschmer
??pgpool
pg_docbot_adz
For information about 'pgpool' see:
http://pgpool.projects.postgresql.org/pgpool-HA/en/
http://pgpool.projects.postgresql.org/
http://pgfoundry.org/projects/pgpool/
RhodiumToad
(Action) isn't the person to ask about GUIs
albert_kam
RhodiumToad: my apology, hee hee
RhodiumToad
"xterm -e psql" :-)
gintek
i have problem with pg_tablespace_size('mytable') and i have error does not exist
but select * from mytable work fine ?
johto
gintek; pg_total_relation_size('mytable')
gintek
any idea what i`m doing wrong with this stupid problem ?
johto
gintek; tablespaces are something completely different
gintek
johto: why in manual pg_database_size(name) Disk space used by the database with the specified name
johto
that would work if you want to know the size of the database, but I don't see how that's related
gintek
pg_total_relation_size work fine
johto: thx
RhodiumToad
databases, tablespaces and tables (relations) are completely different things
gintek
?? tablespace
pg_docbot_adz
For information about 'tablespace' see:
http://people.planetpostgresql.org/xzilla/index.php?/archives/322-tablespace-management-variables.html
wwwald
quick question concerning pgadmin
are there any Windows builds available for releases since 1.10.2?
akretschmer
??mysql
pg_docbot_adz
For information about 'mysql' see:
grandmoun
Hi
Have you already an webservice using SOAP and postgres?
pif
hi, can someone explain the window functions 'lead' and 'lag'?
asfjio
hello, is it possible to view last changes made in some database if they are not executed through psql? something like history?
johto
pif; "lead" is the row following the current row and "lag" is the previous row
pif
ok
iguanna
hi all
there was a way to set up the psql to show the row with the null values in order to distinguish from a empty row
but I don't remember which
johto
\pset null foo
iguanna
thanks
mage_
RhodiumToad: ?
RhodiumToad
?
mage_
you use FreeBSD, no ?
for you is it normal that /usr/local/lib/libpgport.a isn't provided by the -client package ?
I filled a PR for mapserver which require this file (http://www.freebsd.org/cgi/query-pr.cgi?pr=144985) but the fix of the package maintener is only to add a dependency on -server ...
it looks strange to me that you cannot build mapserver with postgis support without building the -server port
RhodiumToad
what is it referencing libpgport for?
mage_
it's required by mapserver when you enable PostGIS support
RhodiumToad
what functions is it trying to use?
mage_
I don't known .. the port fails to build without it
with :
/usr/bin/ld: cannot find -lpgport
gmake: *** [shp2img] Error 1
RhodiumToad
hm
mage_
but I don't understand why -lpgport is listed with pg_config --libs and that that file (/usr/local/lib/libpgport.a) is missing
(on a machine where only -client is installed)
RhodiumToad
the client/server division in the freebsd port of pg is a bit questionable in parts, though that's partly due to the fact that the project itself doesn't provide any clear distinction
mage_
do you think I should fill a PR for this ?
RhodiumToad
probably
mage_
ok thanks
RhodiumToad
pg_config is a problem in itself because of confusion over whether it reflects the server build or the client
it really needs splitting into two
mage_
yep ..
RhodiumToad
there should be a server version that reflects what is needed to build server modules, and a client version
mage_
I'll include that in the PR
RhodiumToad
since there are clients like dbd::pg and (I assume) mapserver that want to use it for client builds, packaging systems tend to put in a version suited to the client needs,
which then buggers up any attempt to build server modules correctly
mage_
there should really be a libpq port in FreeBSD
SmokeyD
hey everyone. When I have a pl/pgsql function with "rows:=SELECT colname FROM tablename WHERE colname=val;", how can I test if that query returned any results?
johto
IF FOUND THEN .
SmokeyD
johto, cool, thanks
RhodiumToad
that statement is wrong anyway
you want SELECT INTO
(and FOUND isn't set by assignment statements)
SmokeyD
RhodiumToad, yeah. I just figured that out :) Thanks. I just do SELECT colname FROM tablename WHERE colname=var;IF NOT FOUND THEN blah....
RhodiumToad
no
SmokeyD
without the assignment
RhodiumToad
that'll fail too
SmokeyD
ok
RhodiumToad
are you trying to get the actual value of "colname" or just check whether the row exists?
SmokeyD
just checking if it exists
I don't need the value
RhodiumToad
if exists(select * from tablename WHERE colname=var) then ...
SmokeyD
ah, that is better. Thanks
Diablo-D3
hey guys
whats the cost of a simple query?
like, is there a medium or large startup cost just to execute the query?
wulczer
Diablo-D3: there's some overhead of parsing and planning the query
you can avoid both by using prepared statements
Diablo-D3
okay so like
lets say I just want to return rows, and do no real thinking outside of just simple column matching
I have 1 server, a million rows
wulczer
you just want to get all the rows from the server?
Diablo-D3
vs
100 servers, the data set split up
how much would I be losing if I executed the same simple query on every one