free blog   apache   shopping directory   php powered
henrydanceI'm assuming I'm understanding the error correctly: ERROR:  invalid input syntax for type numeric: "."
henrydanceAlso, when I search for "." I get no results
andreshenrydance: as I said, insert the table first into some staging table using normal text type
asfjiohello, 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?
henrydanceandres: Got it, thanks!
cojackasfjio: perhaps it'll be
asfjiocojack: 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%')"?
selckinin 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
selckin*explain output
cojackasfjio: I have no idea, you have to check the pefrom each of function
cojackand if you will check it, tell what is better, I also have similar problem
asfjiocojack: how can i check the performance?
cojackasfjio: $start = time(); $stop = $start - time(); :>
cojackor better use time()+microtime();
asfjiocojack: 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)"
asfjiofor ilike "Seq Scan on table  (cost=0.00..117298.34 rows=1 width=250)","  Filter: ((search)::text ~~* 'searchfor'::text)"
asfjioit seams to be equal :)
asfjiocojack: okay, thanks for the help.
cojackexactly
cojackbut, what for more rows?
cojacktry to add 10k rows with the same value, and then execute the query
AlexBasfjio: Consider the trgrm-extension.
cojackdon't forget about index on
asfjioAlexB: what is trgrm-extension? trim()?
AlexBasfjio: http://www.postgresql.org/docs/current/static/pgtrgm.html
asfjioAlexB , cojack : thank you again.
v0idnullHi, I have a question about PL/pgsql or any other language, can you have a dynamic list of arguments?
cojacknope
cojackin plpgsql I never see those future
Chepra_??variadic
pg_docbot_adzFor information about 'variadic' see:
pg_docbot_adzhttp://it.toolbox.com/blogs/database-soup/saving-drupal-with-variadic-parameters-33609
pg_docbot_adzhttp://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS
Chepra_??variadic v0idnull
pg_docbot_adzNothing found
Chepra_oh
Chepra_v0idnull: the last link is yours :)
cojackChepra_: it's not this at all
cojackbecouse some language programing can you create a function without list of argument it self
cojackand In function you can get it by other magic function
v0idnullany* types aren't supported by python or perl unfortunately
cojackin plpgsql if you create a function without list of arguments this function have no arguments
v0idnullcojack: variadic seems to be what I need
soosfarmhello, I'm running out of semaphores
cojackif it is
soosfarmhow can I increase the number in linux?
v0idnullcojack: if it's in an array, I don't mind, the end result is still the same
v0idnulland it seems that I don't have to specify type
v0idnull=]
asfjioAlexB: one question - this trgm can be used with tsearch2 for example, as i read, or i'm mistaken?
selckinif you have 2 columns each with an index, what would make it do seq scan when you only have a where on those 2?
akretschmerselckin: depends, see explain analyse <your query>
eevar2selckin: an index scan would use random access, which is more expensive than a seq scan once you select enough rows
eevar2^^  there are probably other cases as well
selckinhmm, thanks
eevar2and actually reading the index itself isn't free either
eevar2so pointless extra burden if you're gonna read the whole table anyway
selckinwell it should always be in memory in this case
plaessince what Postgres version ENUM type was introduced?
selckinbut yeah it seems like this query would be returning a large part of the table
akretschmerselckin: yeah, for small tables a seq-scan is maybe faster than an index-scan
plaesENUM is supported since 8.3?
sternoceraplaes: correct
SzymonKAny performance guru? :> Is there way to optimize that: http://pgsql.privatepaste.com/6b769ed812 ?
johtouse EXISTS() instead of (SELECT count(*)) > 0
akretschmerSzymonK: line 9: rows=151350, actual 5060
akretschmerwrong statstics
SzymonKjohto: thanks, 500 ms faster :) but it's still 200 ms, any other hints?
eevar2analyze your db and paste the new query?
johtoSzymonK; you're on 8.4, yes?
SzymonKYes
johtothen what eevar2 said
SzymonKAnalyze your db = ANALYZE without parameters, yep?
johtoyes
henrydanceI'm trying to run the following: http://pgsql.privatepaste.com/6a88252605
henrydanceI see that there affected rows, but when I check the data, the rows haven't been updated
henrydance*are
henrydanceDo all queries need to pass validation in order for any query to be run?
SzymonKHere is new query plan: http://pgsql.privatepaste.com/7968ebeda0 after analyze (db);
johtoif you're running those queries in a single transaction, the ERROR will abort that transaction
henrydancejohto: 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_padactorhello. I'm having a character encoding issue.  I'm getting an error  ERROR:  invalid byte sequence for encoding "UTF8": 0xf36e6963
roger_padactorThis error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
henrydanceroger_padactor: Are you using a 3rd party app?
roger_padactorthis the select statement
roger_padactorhttp://pastebin.com/qR0Bepb3
roger_padactorbut when i try it in the sql window it works fine. and in psql
roger_padactorno
henrydanceroger_padactor: I had a similar problem before. The problem was with the app.
roger_padactorhow did you figure out what was up?
eevar2http://www.joelonsoftware.com/articles/Unicode.html
henrydanceI was running the query from an sql dump and the encoding had been put in the first line as something else
roger_padactorI'm not using a third party for this query. So i don't know when the character encoding would have gotten chainged
eevar2roger_padactor: the encoding probably changes as you copy it out from wherever and/or when you paste it into psql/your sql window
henrydancejohto: I'm not sure if my last message was posted, my connection dropped
roger_padactoryeah probably.
johto"I was running the query from an sql dump and the encoding had been put in  the first line as something else"
henrydanceNo, I use the following queries above the queries that get the error: http://pgsql.privatepaste.com/b14fcbb580
henrydanceOn those queries, the table does get updated before the query at the bottom runs
roger_padactoreevar2:  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
henrydanceSo 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
ignashi
ignasi am having some interesting problems with affix tables and tsearch on ubuntu
ignas"wrong affix file format for flag" when it encounters the line "COMPOUNDMIN 1"
ignasin en_gb affix file
ignasis it a common problem? is it a ubuntu problem or is this tsearch/postgres problem?
eevar2roger_padactor: 6e, 69, 63 = n, i, c -- which imo makes f3 your ? in some 8-bit charset
roger_padactorThanks. I think i figured out the problem. The page the error was coming from  html header didn't have a charset meta tag
roger_padactorfor some stupid reason
eevar2meta tags tend to not work btw. set the content-type header
cojackIf I drop public schema and create it again It'll break something?
roger_padactoreevar2:  ok
Zeekis 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.
johtono
asfjiois there a way to trim the elements of such an array {'text','text ', ' text', ' text '} without making loop in stored procedure (pgsql 8.3)?
johtoselect trim(s) from unnest(array) as s;
Sonderbladeis 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?
johtothat's going to suck performance-wise
Sonderbladewhat method would not suck?
johtothey all pretty much suck :-P
asfjiojohto: 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.
johtocool
sternoceraWhy does calling plpy.notice() Put the notice ouput in parenthesis and add quotes, plus append a ","? Thanks
johtosternocera; sounds like you have a tuple instead of something else
johtovery vague, but I'm not a python expert
sternocerajohto: It couldn't be that, because I'm trying it on string literals among other things. Thanks though.
g-hennuxhi!
g-hennuxwhen 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?
johtoyes
sternocerag-hennux: You're confusing two separate issues.
sternoceraNo one can connect if there not listed in pg_hba.conf
sternoceras/there/they're/
g-hennuxsternocera: yep, but i have to list the database in that file
g-hennuxso 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
nerudaselect datname, procpid,current_query from pg_stat_activity; is showing queries that I killed from psql with Ctrl-C....is that normal?
sternocerag-hennux: No, it's per cluster/postgres instance
johtog-hennux; you can use "all"
johtoand revoke connect privilege from the databases you don't want him to connect to
sternoceraoh yes, you can specify database
sternocerabut general you won't
g-hennuxsternocera: of course i want to; i don't want the dev user to be able to connect to the live database, for example
johtorevoke the connect privilege
johtoHBA is way too clumsy for that IMO
sternocerag-hennux: You're using the same database cluster for development as your live server?
jamshidhow can i store word documents in postgresql
sternocerajamshid: The only way is as a bytea
johtoeither use "bytea" or "large objects"
sternoceraor, yeah, a lo
g-hennuxsternocera: yes
g-hennuxsternocera: not good?
sternocerag-hennux: Well, do you really need to do so? I doubt it.
g-hennuxwell, i have just different databases with different access privileges
Croephahello and good morning everyone
Croephaat least its morning for me
depeszhi Croepha
Croephaso, 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... ?
dimshow your query
nerudaselect datname, procpid,current_query from pg_stat_activity; is showing queries that I killed from psql with Ctrl-C....is that normal?
johtoneruda; what queries are those?
thresharctrl-c may not cancel the query. did the psql prompt come back?
nerudaone 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...
Croephadim: http://pastebin.com/xQqjfnmn
Kakohi, 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)
depeszKako: which pg version?
Croephadim, my question is in my test query at the bottom
johtokoki; select id, array_to_string(array_agg(names), ',') from table group by id;
johtokoki; if you're on <8.4, you need array_accum instead of array_agg()
Kakodespesz: 8.2.15 and 8.4.3
Kakodepesz: 8.2.15 and 8.4.3 (sorry)
johtowhoops. Kako those two were for you
johto?? array_accum
pg_docbot_adzFor information about 'array_accum' see:
pg_docbot_adzhttp://www.postgresql.org/docs/current/static/xaggr.html
depeszKako: please check what johto wrote.
johtoKako; there's the array_accum() you need for 8.2.15
Kakodepesz, johto: thanks, im reading...
kokijohto: ??? lost the context
johtokoki; sorry, I mistabbed.
g-hennuxjohto: REVOKE CONNECT doesn't seem to work at all
g-hennuxthe user can still connect anyway
johtohmm?
Kakodepesz, johto: thanks again, was exactly for what I was searching.
kokino worries, i'm glad i'm not *that crazy* to completely loose context
johtog-hennux; you probably have CONNECT on public
g-hennuxand that supersedes the other one?
johto(public is a pseudorole meaning "everyone")
g-hennuxah ok, thanks :)
g-hennuxbut the owner will still be able to connect, right?
johtoyes
FallLinewhat'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)
johtoleast/greatest
FallLineAh thanks
FallLinei can't reuse named columns in a select statement, can i?
eggyknaplike SELECT a, a FROM b ?
eggyknapYes, you can do that.
FallLinelike, where A is a complex expression
johtonot without a subquery
FallLinedamn
eggyknapYeah, stick it in a subquery with an alias, and refer to the alias multiple times
realtimeis it possible to impersonate a given user so I can test my system like I was that user?
Popular searches: lala laffffffhfh   

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