logo
IRC Archive / Freenode / #postgresql / 2010 / June / 14 / 3
henrydance
I'm assuming I'm understanding the error correctly: ERROR: invalid input syntax for type numeric: "."
Also, when I search for "." I get no results
andres
henrydance: as I said, insert the table first into some staging table using normal text type
asfjio
hello, question about autocomplete search field. are normaly those fields search using "col like 'searchText%'"? and if i want to use like '%searchText%' isn't it going to be very slow if records are a lot?
henrydance
andres: Got it, thanks!
cojack
asfjio: perhaps it'll be
asfjio
cojack: yes actually i saw autocompletes only of the first type. another question what should be better when i do the like to use "col ilike 's%'" or "like lower('s%')"?
selckin
in expain out i have a bitmap heap scan, and then a "recheck cond", what does the recheck mean? all the things it is filtered on are indexed
*explain output
cojack
asfjio: I have no idea, you have to check the pefrom each of function
and if you will check it, tell what is better, I also have similar problem
asfjio
cojack: how can i check the performance?
cojack
asfjio: $start = time(); $stop = $start - time(); :>
or better use time()+microtime();
asfjio
cojack: i used some explanation in pgadmin3 and .. for like combined with upper (in my case) - "Seq Scan on table (cost=0.00..117298.34 rows=1 width=250)"," Filter: ((search)::text ~~ 'SEARCHFOR'::text)"
for ilike "Seq Scan on table (cost=0.00..117298.34 rows=1 width=250)"," Filter: ((search)::text ~~* 'searchfor'::text)"
it seams to be equal :)
cojack: okay, thanks for the help.
cojack
exactly
but, what for more rows?
try to add 10k rows with the same value, and then execute the query
AlexB
asfjio: Consider the trgrm-extension.
cojack
don't forget about index on
asfjio
AlexB: what is trgrm-extension? trim()?
asfjio
AlexB , cojack : thank you again.
v0idnull
Hi, I have a question about PL/pgsql or any other language, can you have a dynamic list of arguments?
cojack
nope
in plpgsql I never see those future
Chepra_
??variadic
pg_docbot_adz
For information about 'variadic' see:
http://it.toolbox.com/blogs/database-soup/saving-drupal-with-variadic-parameters-33609
http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS
Chepra_
??variadic v0idnull
pg_docbot_adz
Nothing found
Chepra_
oh
v0idnull: the last link is yours :)
cojack
Chepra_: it's not this at all
becouse some language programing can you create a function without list of argument it self
and In function you can get it by other magic function
v0idnull
any* types aren't supported by python or perl unfortunately
cojack
in plpgsql if you create a function without list of arguments this function have no arguments
v0idnull
cojack: variadic seems to be what I need
soosfarm
hello, I'm running out of semaphores
cojack
if it is
soosfarm
how can I increase the number in linux?
v0idnull
cojack: if it's in an array, I don't mind, the end result is still the same
and it seems that I don't have to specify type
=]
asfjio
AlexB: one question - this trgm can be used with tsearch2 for example, as i read, or i'm mistaken?
selckin
if you have 2 columns each with an index, what would make it do seq scan when you only have a where on those 2?
akretschmer
selckin: depends, see explain analyse <your query>
eevar2
selckin: an index scan would use random access, which is more expensive than a seq scan once you select enough rows
^^ there are probably other cases as well
selckin
hmm, thanks
eevar2
and actually reading the index itself isn't free either
so pointless extra burden if you're gonna read the whole table anyway
selckin
well it should always be in memory in this case
plaes
since what Postgres version ENUM type was introduced?
selckin
but yeah it seems like this query would be returning a large part of the table
akretschmer
selckin: yeah, for small tables a seq-scan is maybe faster than an index-scan
plaes
ENUM is supported since 8.3?
sternocera
plaes: correct
SzymonK
Any performance guru? :> Is there way to optimize that: http://pgsql.privatepaste.com/6b769ed812 ?
johto
use EXISTS() instead of (SELECT count(*)) > 0
akretschmer
SzymonK: line 9: rows=151350, actual 5060
wrong statstics
SzymonK
johto: thanks, 500 ms faster :) but it's still 200 ms, any other hints?
eevar2
analyze your db and paste the new query?
johto
SzymonK; you're on 8.4, yes?
SzymonK
Yes
johto
then what eevar2 said
SzymonK
Analyze your db = ANALYZE without parameters, yep?
johto
yes
henrydance
I'm trying to run the following: http://pgsql.privatepaste.com/6a88252605
I see that there affected rows, but when I check the data, the rows haven't been updated
*are
Do all queries need to pass validation in order for any query to be run?
SzymonK
Here is new query plan: http://pgsql.privatepaste.com/7968ebeda0 after analyze (db);
johto
if you're running those queries in a single transaction, the ERROR will abort that transaction
henrydance
johto: I'm creating all the queries to convert a database and sending it in a text file. Is there a way to tell the server to run the first query first?
roger_padactor
hello. I'm having a character encoding issue. I'm getting an error ERROR: invalid byte sequence for encoding "UTF8": 0xf36e6963
This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
henrydance
roger_padactor: Are you using a 3rd party app?
roger_padactor
this the select statement
but when i try it in the sql window it works fine. and in psql
no
henrydance
roger_padactor: I had a similar problem before. The problem was with the app.
roger_padactor
how did you figure out what was up?
henrydance
I was running the query from an sql dump and the encoding had been put in the first line as something else
roger_padactor
I'm not using a third party for this query. So i don't know when the character encoding would have gotten chainged
eevar2
roger_padactor: the encoding probably changes as you copy it out from wherever and/or when you paste it into psql/your sql window
henrydance
johto: I'm not sure if my last message was posted, my connection dropped
roger_padactor
yeah probably.
johto
"I was running the query from an sql dump and the encoding had been put in the first line as something else"
henrydance
No, I use the following queries above the queries that get the error: http://pgsql.privatepaste.com/b14fcbb580
On those queries, the table does get updated before the query at the bottom runs
roger_padactor
eevar2: my html pages are UTF-8 my db client encoding is UTF8 my php doesn't change it that I know of. The puzzle begins
henrydance
So on the 2nd set, when I need to shorten the fields to get them to fit into numerics, I get the error and none of the fields update. On the 1st set, the fields update first and then INSERT runs
ignas
hi
i am having some interesting problems with affix tables and tsearch on ubuntu
"wrong affix file format for flag" when it encounters the line "COMPOUNDMIN 1"
in en_gb affix file
is it a common problem? is it a ubuntu problem or is this tsearch/postgres problem?
eevar2
roger_padactor: 6e, 69, 63 = n, i, c -- which imo makes f3 your ? in some 8-bit charset
roger_padactor
Thanks. I think i figured out the problem. The page the error was coming from html header didn't have a charset meta tag
for some stupid reason
eevar2
meta tags tend to not work btw. set the content-type header
cojack
If I drop public schema and create it again It'll break something?
roger_padactor
eevar2: ok
Zeek
is there a way to request a query take a low priority compared to other DB activities? This would be on otherwise idle tables so no concern over any locks on those from a table perspective.
johto
no
asfjio
is there a way to trim the elements of such an array {'text','text ', ' text', ' text '} without making loop in stored procedure (pgsql 8.3)?
johto
select trim(s) from unnest(array) as s;
Sonderblade
is there a way in postgresql to find the shortest path between two users in a database with Users joined to itself with a many-to-many UserFriends table?
johto
that's going to suck performance-wise
Sonderblade
what method would not suck?
johto
they all pretty much suck :-P
asfjio
johto: obviously the unnest() function does not exists in postgres 8.3, anyway i've figured out how to do it in v8.3. thank you.
johto
cool
sternocera
Why does calling plpy.notice() Put the notice ouput in parenthesis and add quotes, plus append a ","? Thanks
johto
sternocera; sounds like you have a tuple instead of something else
very vague, but I'm not a python expert
sternocera
johto: It couldn't be that, because I'm trying it on string literals among other things. Thanks though.
g-hennux
hi!
when a user has the right to create a database, he may still not be able to connect to that freshly created db because of missing pg_hba.conf rules, is that correct?
johto
yes
sternocera
g-hennux: You're confusing two separate issues.
No one can connect if there not listed in pg_hba.conf
s/there/they're/
g-hennux
sternocera: yep, but i have to list the database in that file
so it may well be that the dev user connects to dev_current, creates a database dev_test, but then cannot use dev_test, because of a missing pg_hba.conf entry
neruda
select datname, procpid,current_query from pg_stat_activity; is showing queries that I killed from psql with Ctrl-C....is that normal?
sternocera
g-hennux: No, it's per cluster/postgres instance
johto
g-hennux; you can use "all"
and revoke connect privilege from the databases you don't want him to connect to
sternocera
oh yes, you can specify database
but general you won't
g-hennux
sternocera: of course i want to; i don't want the dev user to be able to connect to the live database, for example
johto
revoke the connect privilege
HBA is way too clumsy for that IMO
sternocera
g-hennux: You're using the same database cluster for development as your live server?
jamshid
how can i store word documents in postgresql
sternocera
jamshid: The only way is as a bytea
johto
either use "bytea" or "large objects"
sternocera
or, yeah, a lo
g-hennux
sternocera: yes
sternocera: not good?
sternocera
g-hennux: Well, do you really need to do so? I doubt it.
g-hennux
well, i have just different databases with different access privileges
Croepha
hello and good morning everyone
at least its morning for me
depesz
hi Croepha
Croepha
so, why does postgres want to force me to use my generate_series output 'in the GROUP BY clause or be used in an aggregate function' why cant i use it like a regular table... ?
dim
show your query
neruda
select datname, procpid,current_query from pg_stat_activity; is showing queries that I killed from psql with Ctrl-C....is that normal?
johto
neruda; what queries are those?
threshar
ctrl-c may not cancel the query. did the psql prompt come back?
neruda
one was run directly from psql for web app testing purposes and another was run by iReport to pull some data for a report, both the psql session and the iReport have been closed though...
Kako
hi, ist ther an implementation for STRAGG for postgresql? I need to aggregate rows to a single string in depence of an id (ex: SELECT id, STRAGG(names,',') FROM table GROUP BY id)
depesz
Kako: which pg version?
Croepha
dim, my question is in my test query at the bottom
johto
koki; select id, array_to_string(array_agg(names), ',') from table group by id;
koki; if you're on <8.4, you need array_accum instead of array_agg()
Kako
despesz: 8.2.15 and 8.4.3
depesz: 8.2.15 and 8.4.3 (sorry)
johto
whoops. Kako those two were for you
?? array_accum
pg_docbot_adz
For information about 'array_accum' see:
depesz
Kako: please check what johto wrote.
johto
Kako; there's the array_accum() you need for 8.2.15
Kako
depesz, johto: thanks, im reading...
koki
johto: ??? lost the context
johto
koki; sorry, I mistabbed.
g-hennux
johto: REVOKE CONNECT doesn't seem to work at all
the user can still connect anyway
johto
hmm?
Kako
depesz, johto: thanks again, was exactly for what I was searching.
koki
no worries, i'm glad i'm not *that crazy* to completely loose context
johto
g-hennux; you probably have CONNECT on public
g-hennux
and that supersedes the other one?
johto
(public is a pseudorole meaning "everyone")
g-hennux
ah ok, thanks :)
but the owner will still be able to connect, right?
johto
yes
FallLine
what's the function to get min/max in postgresql (non-aggregate, but rather from a list of values, e.g., max(1,2,3,4)
johto
least/greatest
FallLine
Ah thanks
i can't reuse named columns in a select statement, can i?
eggyknap
like SELECT a, a FROM b ?
Yes, you can do that.
FallLine
like, where A is a complex expression
johto
not without a subquery
FallLine
damn
eggyknap
Yeah, stick it in a subquery with an alias, and refer to the alias multiple times
realtime
is it possible to impersonate a given user so I can test my system like I was that user?