Re: [GENERAL] Looking for Silicon Valley/Peninsula/San Francisco users group
On 04/29/2011 10:01 PM, Greg Smith wrote: On 04/29/2011 06:13 PM, Jeff Davis wrote: I'm not sure which reference you found, but SFPUG is certainly active with meetings every month. http://pugs.postgresql.org/sfpug ; last meeting listed there is January 2009. Yeah, that site is kind of. not maintained. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Developement Organizers of the PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Switching Database Engines
On 04/28/2011 12:19 PM, Carlos Mennens wrote: It seems that the 'mysql2postgres.pl' tool has instructions embedded into the file so I ran the command as instructed to take the output file and insert it into my PostgreSQL server and got the following error message: $ psql -p 5432 -h db1 -U wiki -f mediawiki_upgrade.pg Password for user wiki: BEGIN SET SET SET psql:mediawiki_upgrade.pg:25: ERROR: relation category does not exist My guess is that you need to run the main MediaWiki installer for PostgreSQL first, to create a blank install, in order for the category table to exist. The export tool is aimed to get the data out, not the schema to create all the tables. After you create a blank instance, then you do the data export. If you have additional problems, try running that like this instead: $ psql -p 5432 -h db1 -U wiki -e -f mediawiki_upgrade.pg Note the extra -e on the command line. That will show you the line it is executing as the script runs, so you'll see the one that fails too. Very handy for debugging what's gone wrong in this sort of situation. I wouldn't fight with this too much though. Unless you have some really customized stuff in your wiki, there really is nothing wrong with the idea of dumping everything into XML, creating a blank PostgreSQL-backed MediaWiki install, then restoring into that. That's what I always do in order to get a plain text backup of my server, and to migrate a wiki from one server to another. There are all kinds of issues you could have left here before this works, trying to do a database-level export/reload--encoding, foreign key problems, who knows what else. The database-agnostic export/import into XML avoids all of those. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Short-circuit boolean evaluation
Hi, Does Postgresql perform short-circuit boolean evaluation both in SQL and PL/pgSQL functions? As an example, suppose I have a function called do_stuff which is computationally intensive. In the example below, will it be called for rows for which the first predicate (foobar.id = $1) is false? SELECT count(*) FROM foobar WHERE foobar.id = $1 AND do_stuff (foobar.name); Thanks! Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Short-circuit boolean evaluation
No. 2011/4/30, Jon Smark jon.sm...@yahoo.com: Hi, Does Postgresql perform short-circuit boolean evaluation both in SQL and PL/pgSQL functions? As an example, suppose I have a function called do_stuff which is computationally intensive. In the example below, will it be called for rows for which the first predicate (foobar.id = $1) is false? SELECT count(*) FROM foobar WHERE foobar.id = $1 AND do_stuff (foobar.name); Thanks! Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Short-circuit boolean evaluation
No. It will not be called Or No. Postgresql does not short-circuit boolean evaluations ? On Apr 30, 2011, at 10:27, pasman pasmański pasma...@gmail.com wrote: No. 2011/4/30, Jon Smark jon.sm...@yahoo.com: Hi, Does Postgresql perform short-circuit boolean evaluation both in SQL and PL/pgSQL functions? As an example, suppose I have a function called do_stuff which is computationally intensive. In the example below, will it be called for rows for which the first predicate (foobar.id = $1) is false? SELECT count(*) FROM foobar WHERE foobar.id = $1 AND do_stuff (foobar.name); Thanks! Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Short-circuit boolean evaluation
On Sat, Apr 30, 2011 at 10:34:32AM -0400, David Johnston wrote: No. It will not be called Or No. Postgresql does not short-circuit boolean evaluations ? SQL is a somewhat declarative language. There is no order to evaluation as such. So you can't talk about short circuiting either. This applies to any SQL database. You can somewhat enforce order with subselects and CASE and other such constructs. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
[GENERAL] histogram
I have a column of 2 million float values from 0 to 1. I would like to figure out how many values fit into buckets spaced by 0.10, e.g. from 0 to 0.10, from 0.10 to 0.20, etc. What is the best way to do this? Thanks, Joel -- - for hire: mac osx device driver ninja, kernel extensions and usb drivers -++--- http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont -++--- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] histogram
Hi, try something like this: select trunc(random() * 10.)/10. , count(*) from generate_series(1,200) group by 1 order by 2 regards Thomas Am 30.04.2011 18:37, schrieb Joel Reymont: I have a column of 2 million float values from 0 to 1. I would like to figure out how many values fit into buckets spaced by 0.10, e.g. from 0 to 0.10, from 0.10 to 0.20, etc. What is the best way to do this? Thanks, Joel -- - for hire: mac osx device driver ninja, kernel extensions and usb drivers -++--- http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont -++--- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] histogram
Thank you Thomas! Is there a way for the code below to determine the number of rows in the table and use it? Thanks, Joel On Apr 30, 2011, at 5:48 PM, Thomas Markus wrote: Hi, try something like this: select trunc(random() * 10.)/10. , count(*) from generate_series(1,200) group by 1 order by 2 -- - for hire: mac osx device driver ninja, kernel extensions and usb drivers -++--- http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont -++--- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] histogram
What is the meaning of group by 1 order by 2 e.g. what to the numbers 1 and 2 stand for? What would change if I do the following? group by 1 order by 1 On Apr 30, 2011, at 5:48 PM, Thomas Markus wrote: Hi, try something like this: select trunc(random() * 10.)/10. , count(*) from generate_series(1,200) group by 1 order by 2 -- - for hire: mac osx device driver ninja, kernel extensions and usb drivers -++--- http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont -++--- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] histogram
I think this should do what I want select trunc(distance * 10.)/10., count(*) from doc_ads group by 1 order by 1 Thanks, Joel -- - for hire: mac osx device driver ninja, kernel extensions and usb drivers -++--- http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont -++--- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Short-circuit boolean evaluation
David Johnston pol...@yahoo.com writes: No. It will not be called Or No. Postgresql does not short-circuit boolean evaluations ? The correct answer is maybe. See http://www.postgresql.org/docs/9.0/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] histogram
re: 1 and 2. They're horrible (imho) reference to the attributes of the returned tuple. Or at best an exposure of the implementation. :) Order by 2 if you want the most frequent (highest counts) of your distances at the bottom of the output (or ordery by 2 desc) if you want them at the top of your output. Joel Reymont wrote: I think this should do what I want select trunc(distance * 10.)/10., count(*) from doc_ads group by 1 order by 1 Thanks, Joel -- - for hire: mac osx device driver ninja, kernel extensions and usb drivers -++--- http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont -++--- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GIN index not used
Alban thank for your ideas It probably is, the default Postgres settings are quite modest and GIN indexes are memory hungry. I think you need to increase shared_buffers. With 2.5GB of memory (such a strange number) the docs suggest about 250MB. See http://www.postgresql.org/docs/current/static/runtime-config-resource.html for details. Well I did not mentioned that it all runs on Virtual Machine so that's why 2.5GB. I could not assigned more. Today I have tried the VM machine on PC where I assigned 9GB of memory to this VM. There I set the shared_buffers on 900MB. You were right it wa's definitely caused by the size of memory. I do not understand why before it was such quick even, if I had less memory. Thanks very much for your help :-)-- View this message in context: http://postgresql.1045698.n5.nabble.com/GIN-index-not-used-tp4344826p4361529.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Huge spikes in number of connections doing PARSE
hubert depesz lubaczewski dep...@depesz.com writes: we have n (~ 40 i think) web servers. each webserver has it's own pgbouncer (in session pooling). In some cases I have found useful to have those webserver's pgbouncer connect to another pgbouncer on the database host. But if your problem is tied to real active connection, I don't see what it would solve. Still, if you're searching ideas… Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Huge spikes in number of connections doing PARSE
On Sat, Apr 30, 2011 at 08:55:09PM +0200, Dimitri Fontaine wrote: hubert depesz lubaczewski dep...@depesz.com writes: we have n (~ 40 i think) web servers. each webserver has it's own pgbouncer (in session pooling). In some cases I have found useful to have those webserver's pgbouncer connect to another pgbouncer on the database host. But if your problem is tied to real active connection, I don't see what it would solve. Still, if you're searching ideas… thanks. we'll probably look into it, but the situaion kind of stalled now. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple instances with same version?
durumdara durumd...@gmail.com writes: I want to ask that have some way to install PGSQL 9.0 as two instances in one machine? Most important question. The OS is can be Windows or Linux. debian and ubuntu packaging support this quite well, see pg_lsclusters and associated man pages: http://manpages.debian.net/cgi-bin/man.cgi?query=pg_lsclusters http://manpages.debian.net/cgi-bin/man.cgi?query=pg_createcluster Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] converting databases form SQL_ASCII to UTF8
Geoffrey Myers li...@serioustechnology.com writes: So, now the question is, is this effort even worth our effort? What is the harm in leaving our databases SQL_ASCII encoded? You're declaring bankruptcy on being able to make any sense of the data you stored. Is that really what you think you need? For converting, you might be interested into those two blog entries: http://tapoueh.org/articles/blog/_Getting_out_of_SQL_ASCII,_part_1.html http://tapoueh.org/articles/blog/_Getting_out_of_SQL_ASCII,_part_2.html Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Partitioning an existing table
disclaimer : I didn't read the presentation paper Greg Smith talked about yet, nor his partitioning chapter yet, so it might be about the same trick. Phoenix Kiula phoenix.ki...@gmail.com writes: How about doing this with existing massive tables? (Over 120 million rows) I could create a new parent table with child tables, and then INSERT all these millions of rows to put them into the right partition. But is that recommended? If you're partitioning by date, for example, then what I regularly do is to consider the existing table to be the first partition with data from origin to now. Then what I do is to create a new parent table and is children, prepare the trigger(s), etc. The switch is then a light transaction which only renames the current table to say name_past_201104, have it inherits the parent table, and finally rename the new parent table to the name. Later on you still can rejigger your data around if you wish. With time based partitioning it's best to wait until the old partition is not the target of INSERTs or UPDATEs any more. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] PostgreSQL Core Team
Thom Brown t...@linux.com writes: Excellent! Magnus is a very valuable contributor to the PostgreSQL community and I think the community can only benefit from this addition to the core team. +1 Congrats, Magnus! -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] histogram
Given that you are actively implementing the code that uses the 1 and 2 I don't see how it is that egregious. When generating calculated fields it is cleaner than the alternative: Select trunc(distance * 10.)/10., count(*) From doc_ads Group by (trunc(distance * 10.)) Order by (trunc(distance * 10.)) It would be nice if you could do: Select trunc(distance * 10.)/10. AS bin, count(*) AS frequency From doc_ads Group by bin Order by bin But I do not believe that is allowed (though I may have my syntax wrong...) David J. re: 1 and 2. They're horrible (imho) reference to the attributes of the returned tuple. Or at best an exposure of the implementation. :) Joel Reymont wrote: I think this should do what I want select trunc(distance * 10.)/10., count(*) from doc_ads group by 1 order by 1 Thanks, Joel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Values larger than 1/3 of a buffer page cannot be indexed (hstore)
Hi, 2011/3/13 Viktor Nagy viktor.n...@toolpart.hu when trying to insert a long-long value, I get the following error: ERROR: Index row size 3120 exceeds maximum 2712 for index ir_translation_ltns HINT: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index of an MD5 hash of the value, or use full text indexing. I get the same error but I'm using a hstore attribute (called 'tags'). Unfortunately, the trick with the MD5 function index does not work neither: CREATE INDEX planet_osm_point_tags ON planet_osm_point ((md5(tags))); ERROR: Funktion md5(hstore) does not exist SQL state: 42883 Any ideas on how to index my hstore attribute? Yours, S. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] histogram
David Johnston wrote: Given that you are actively implementing the code that uses the 1 and 2 I don't see how it is that egregious. When generating calculated fields it is cleaner than the alternative: Select trunc(distance * 10.)/10., count(*) From doc_ads Group by (trunc(distance * 10.)) Order by (trunc(distance * 10.)) It would be nice if you could do: Select trunc(distance * 10.)/10. AS bin, count(*) AS frequency From doc_ads Group by bin Order by bin But I do not believe that is allowed (though I may have my syntax wrong...) David J. re: 1 and 2. They're horrible (imho) reference to the attributes of the returned tuple. Or at best an exposure of the implementation. :) Joel Reymont wrote: I think this should do what I want select trunc(distance * 10.)/10., count(*) from doc_ads group by 1 order by 1 Thanks, Joel I think we're supposed to bottom-post here. I agree in the case of generated columns and old servers but you see the practice more commonly than really necessary. But in 8.4 at least select trunc(distance * 10.0 )/10.0 as histo, count(*) as tally from d group by histo order by tally; works just fine for me -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Values larger than 1/3 of a buffer page cannot be indexed (hstore)
Stefan Keller sfkel...@gmail.com writes: Any ideas on how to index my hstore attribute? Use a GIST or GIN index. The only thing that a btree index on hstore can do for you is to support equality comparisons on the whole hstore value, which is pretty unlikely to be what you're after. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
