Re: [pgeu-general] [GENERAL] Call for design: PostgreSQL mugs
Marc Balmer m...@msys.ch writes: My SQL is PostgreSQL PostgreSQL really is YeSQL! -- you can check http://yesql.org too -- Dimitri Fontaine06 63 07 10 78 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] pg_dump -s dumps data?!
Hi, Sorry to be late in the thread, I'm too busy right now. Cédric called it to my immediate attention though. Martijn van Oosterhout klep...@svana.org writes: Perhaps a better way of dealing with this is providing a way of dumping extensions explicitly. Then you could say: pg_dump --extension=postgis -s That's something I'm working on in this commit fest under the “inline extensions” topic, and we should have that facility in 9.2 baring major obstacles (consensus is made). Tom Lane t...@sss.pgh.pa.us writes: On Mon, Jan 30, 2012 at 11:18 PM, Tom Lanet...@sss.pgh.pa.us wrote: What's not apparent to me is whether there's an argument for doing more than that. It strikes me that the current design is not very friendly towards the idea of an extension that creates a table that's meant solely to hold user data --- you'd have to mark it as config which seems a bit unfortunate terminology for that case. Is it important to do something about that, and if so what? My thought exactly --- maybe it's only a minor cosmetic issue that will affect few people, or maybe this will someday be a major use-case. I don't know. I was hoping Dimitri had an opinion. So, being able to stuff data into an extension has been made possible to address two use cases: - postgis - (sql only) data extensions The former is very specific and as we didn't hear back from them I guess we addressed it well enough, the latter is still WIP. It's about being able to ship data as an extension (think timezone updates, geo ip, bank cards database, exchange rates, etc). You need to be able to easily ship those (CSV isn't the best we can do here, as generally it doesn't include the schema nor the COPY recipe that can be non-trivial) and to easily update those. The case for a table that is partly user data and partly extension data is very thin, I think that if I had this need I would use inheritance and a CHECK(user_data is true/false) constraint to filter the data. So I sure would appreciate being able to call that data rather than config, and to mark any table at once. If that doesn't need any pg_dump stretching I think providing that in 9.2 would be great. 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] Using Postgresql as application server
[edited] Merlin Moncure mmonc...@gmail.com writes: http://asmith.id.au/mod_libpq.html http://asmith.id.au/source/mod_libpq2.c node.js is even thinner. node.js is single threaded and 100% asynchronous which fits very nice with libpq which is at heart a single threaded asynchronous library. Sure. Elnode shares this design, and yaws more seriously so. I wouldn't pick mod_libpq myself. http://nic.ferrier.me.uk/blog/2010_10/elnode http://yaws.hyber.org/ Just saying that the thin web server layer that directly hands the request to the database has been existing in PostgreSQL land for a long time already, no need to resort to other proprietary architectures here. 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] Dump a database excluding one table DATA?
Dmitry Koterov dmi...@koterov.ru writes: Is there any way (or hack) to dump the whole database, but to exclude the DATA from a table within this dump? (DDL of the table should not be excluded: after restoring the data the excluded table should look empty.) The pg_staging tool allows you to do that quite easily, once you've done the initial setup (involved, but nothing fancy). https://github.com/dimitri/pg_staging http://tapoueh.org/pgsql/pgstaging.html If you want to exclude the table from the dumps though (data loss issues are looking at you), what I would advice is create a dedicated schema and skipping the whole schema at dump time, using the following option: --exclude-schema=schema Do not dump any schemas matching the schema pattern. The pattern is interpreted according to the same rules as for -n. -N can be given more than once to exclude schemas matching any of several patterns. 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] Using Postgresql as application server
Chris Travers chris.trav...@gmail.com writes: I want an email to go out to the ordering manager when the quantity I have of an item drops below the re-order point. I also want this email NOT to go out if the transaction rolls back. (Wait, the order of 5 widgets I just processed rolled back because it isn't to a valid customer! We normally only sell 5 per year anyway. No need for the email.) Just use PGQ and be done with it. You have transactional and asynchronous behavior. Typically, a trigger would produce events in the queue, and a separate daemon will consume the queue and send emails. 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] Using Postgresql as application server
c k shreeseva.learn...@gmail.com writes: Many users are using it and found it stable and scalable. Important is that web server is external to the database and a mod_pgsql like mod_plsql is used to connect web server to database. Each page is considered as a stored procedure in the oracle database. I am not thinking of implementing as it is It's been around for a long time already: http://asmith.id.au/mod_libpq.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] pgloader hangs with an invalid filename
Tony Capobianco tcapobia...@prospectiv.com writes: Has anyone experienced the behavior of pgloader hanging when the .conf file has an incorrect filename? When I execute with the correct filename, everything works just fine. However, when I use an incorrect filename, I get the below: That's certainly due to current threading implementation in pgloader, I guess the control thread is still waiting for the load to terminate, which will never happen. It seems to be fixed in current version though. See if you can upgrade. 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] Adapting existing extensions to use CREATE EXTENSION
Hi, Roger Leigh rle...@codelibre.net writes: I've been looking at converting my existing debversion datatype extension to use the proper CREATE EXTENSION facility for 9.1, while also being backward compatible with 8.4 and 9.0. I've been doing the same exercise with pgfincore recently, and will probably do some more of it later. You can look at those links for the time being: http://tapoueh.org/blog/2011/06/29-multi-version-support-for-extensions.html https://github.com/klando/pgfincore/tree/master/debian http://packages.debian.org/sid/postgresql-9.0-pgfincore 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] pgloader hangs with an invalid filename
Tony Capobianco tcapobia...@prospectiv.com writes: I'm running pgloader 2.3.2 and Postgres 9. Is there an upgraded version of pgloader on the PgFoundry site? Yeah, it's now pgloader 2.3.3, 3 pre-releases have been done and the later one, 2.3.3~dev3 will soon'ish get tagged final 2.3.3. Also the development now happens at github. http://packages.debian.org/search?keywords=pgloader http://github.com/dimitri/pgloader 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
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] 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] Finding Errors in .csv Input Data
Rich Shepard rshep...@appl-ecosys.com writes: I'm sure many of you have solved this problem in the past and can offer solutions that will work for me. The context is a 73-column postgres table of data that was originally in an Access .mdb file. A colleague loaded the file into Access and wrote a .csv file for me to use since we have nothing Microsoft here. There are 110,752 rows in the file/table. After a lot of cleaning with emacs and sed, the copy command accepted all but 80 rows of data. Now I need to figure out why postgres reports them as having too many columns. Did you try pgloader yet? http://pgloader.projects.postgresql.org/ http://pgfoundry.org/projects/pgloader/ https://github.com/dimitri/pgloader http://packages.debian.org/sid/pgloader 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] Covert database from ASCII to UTF-8
Jeff Amiel becauseimj...@yahoo.com writes: It's come time to bite the bullet and convert a half-terraybyte database from ASCII to UTF8. Have gone through a bit of effort to track down the unclean ascii text and repair it but would like to avoid the outage of a many-many hour dump-restore. Those blog articles of mine might be of interest to you: 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] FTS and words near one another
Arturo Perez art...@pleeque.com writes: Is there currently anyway in 9.0 to use FTS to search for words that are next to each other? Oleg and Teodor worked on that at some point and they call it phrase search. I don't think it's been commited yet, you can see reference material in their presentation at pgcon here: http://www.pgcon.org/2009/schedule/events/119.en.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] How to Create Table from CSV
ray r...@aarden.us writes: I would like to create a table from a CSV file (the first line is headers which I want to use as column names) saved from Excel. I have You have to manually create the table and its columns, as other said. The tricky part that is hard (or impossible) to automate is deciding which data type to use for each column. Once you've done that, actually importing the data is a matter of using the COPY command or the pgloader tool. In PostgreSQL 9.1 you will be able to use CREATE FOREIGN TABLE to achieve that in one step, see: CREATE FOREIGN TABLE http://developer.postgresql.org/pgdocs/postgres/ddl-foreign-data.html http://developer.postgresql.org/pgdocs/postgres/sql-createforeigndatawrapper.html http://developer.postgresql.org/pgdocs/postgres/file-fdw.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] How to extract a value from a record using attnum or attname?
Kevin Grittner kevin.gritt...@wicourts.gov writes: PL/pgSQL seems tantalizingly close to being useful for developing a generalized trigger function for notifying the client of changes. I don't know whether I'm missing something or whether we're missing a potentially useful feature here. Does anyone see how to fill in where the commented question is, or do I need to write this function in C? See those: http://tapoueh.org/articles/blog/_Dynamic_Triggers_in_PLpgSQL.html http://www.pgsql.cz/index.php/PL_toolbox_%28en%29#Record.27s_functions for i in array_lower(keycols, 1)..array_upper(keycols, 1) loop select quote_ident(attname) from pg_catalog.pg_attribute where attrelid = tg_relid and attnum = keycols[i]::oid Beware of attisdropped, which I've not fixed in the published URL before (the tapoueh.org one). 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] How best to load modules?
Steve White swh...@aip.de writes: What are best practices regarding the loading of postgresql modules, say from the contrib/ directory; specifically, with regard to portability? I would like to distribute an SQL script which loads a module, and works with as little further fiddling as possible. See about PGXS. http://www.postgresql.org/docs/9.0/static/xfunc-c.html#XFUNC-C-PGXS known options = Within a session, or in a script, one can use \i explicit file path But within a script this has the weakness that the file path varies from one system distribution to another. One can start psql with psql ... -f explicit file path but that's a measure taken outside the script, to done either with session, or else be done by a further measure such as a shell script. Ideally, the location of the default modules directory (or installation directory) should be available within a session in some variable or from some function call. You can use pg_config to get this PATH, and in recent versions of PostgreSQL you can use $libdir as the module directory name. select name, setting from pg_settings where name ~ 'dynamic_library_path'; There has been talk about a bigger solution on http://wiki.postgresql.org/wiki/Module_Manager but little seems to have happened there in some years. It seemed stalled for a long time because the harder part of this development was to get an agreement among hackers about what to develop exactly. We've been slowly reaching that between developer meetings in 2009 and 2010, and the result should hit the official source tree before the next developer meeting in 2011 :) https://commitfest.postgresql.org/action/patch_view?id=471 https://commitfest.postgresql.org/action/patch_view?id=472 If you want to see more details about the expected-to-be-commited-soon development work, have a look there: http://pgsql.tapoueh.org/extensions/doc/html/extend-extension.html If you have enough time and interest into the feature, you can even clone the git repository where the development occurs (branches named extension and upgrade) and try it for yourself, then maybe send a mail about your findings (we call that a review): http://git.postgresql.org/gitweb?p=postgresql-extension.git;a=summary http://wiki.postgresql.org/wiki/Reviewing_a_Patch 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] Have any tricks not to recreate a standby server to switch to the former primary?
Josh Kupershmidt schmi...@gmail.com writes: From this description, it sounds like you're trying to shortcut the process of bringing your old primary server (server A) up-to-date with the currently-running server (server B). In order to bring server A up-to-date with B, you'll need to follow *all* the steps on that wiki page to set server A up as a warm standby of B, particularly the pg_start_backup(); rsync'ing of PGDATA over to A, etc. See repmgr for a way to mitigate than by using 2 standby servers: http://projects.2ndquadrant.com/repmgr https://github.com/greg2ndQuadrant/repmgr http://groups.google.com/group/repmgr 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] Updated Latch Patch
Shoma S Achar shoma.pra...@gmail.com writes: I would like to know where I could find the latest available Latch patch. If anyone knows, please share this information. I guess you would find it there: http://git.postgresql.org/gitweb?p=postgresql.gita=searchh=HEADst=commits=latch 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] Full Text Partial Match at begining
AI Rumman rumman...@gmail.com writes: Is it possible to match %text' in Postgresql 9 Full Text. See http://www.sai.msu.su/~megera/wiki/wildspeed -- 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] Can you check in SQL if a fields can be encoded using specified charset
Paul Taylor paul_t...@fastmail.fm writes: Is there a function that can say whether a textvalue can be encoded in a particular charset See convert() and friends here: http://www.postgresql.org/docs/9.0/interactive/functions-string.html#FUNCTIONS-STRING-OTHER That will issue an error when the text can't be converted, but you could have a plpgsql function catching the exception for you and returning false. Won't be very fast, but will do the job. 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] Adding data from mysql to postgresql periodically
Hi, franrtorres77 franrtorre...@gmail.com writes: I need to add periodically some data from a remote mysql database into our postgresql database. So, does anyone know how to do it having in mind that it must be runned every minute or so for adding new records to the postresql? I've been doing that with pgloader already, using mysqldump -T -w options to get csv output and a where clause (incremental dumps of some sort). It worked and allows to validate that we'd be better served with PostgreSQL, so we migrated away from MySQL as soon as possible. Don't miss the pgloader user module reformat feature, and the mysql to PostgreSQL timestamp that you have to use sometime (depends on the MySQL minor version, if memory serves). 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] finding the other statement causing a sharelock
Ivan Sergio Borgonovo m...@webthatworks.it writes: I've some strong suspect... and I'd like to exit earlier from a function if a process is running but I'm not really sure how to add a semaphore... Maybe pg_try_advisory_lock() would help you there? http://www.postgresql.org/docs/8.3/static/explicit-locking.html#ADVISORY-LOCKS http://www.postgresql.org/docs/8.3/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS 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] Need magic to clean strings from unconvertible UTF8
Andreas maps...@gmx.net writes: I can find the problematic rows. How could I delete every char in a string that can't be converted to WIN1252? 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 That's using an hand-crafted translate expression, you could also use the recode library that does a pretty good job. Maybe the easiest way here would be using some plpythonu procedure using librecode? http://packages.debian.org/sid/python-bibtex Well or the same in plperl… or even easier, process the source files before importing them? 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] Cloning database without dump/restore
Andrus kobrule...@hot.ee writes: How to clone database fast ? How to create server side pl/pgsql script which use code like in pg_dump/pg_restore and or in pg_migrator and clones existing database or other idea ? See pg_basebackup here: https://github.com/dimitri/pg_basebackup I'm wouldn't expect it to be fast though. -- 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] Group by and limit
Reid Thompson reid.thomp...@ateb.com writes: I only want the first 500 for each x. Any tips or tricks someone might know would be appreciated. I’m using postgres 8.3.7. http://troels.arvin.dk/db/rdbms/#select-top-n Consider using a more recent version of PostgreSQL, equipped with window functions! 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] pg_get_procdef ?
hubert depesz lubaczewski dep...@depesz.com writes: and check the queries. getting function definition from there should be simple. Check getddl to see how much simple it is: https://labs.omniti.com/labs/pgtreats/wiki/getddl http://pgsql.tapoueh.org/getddl/ http://pgsql.tapoueh.org/getddl/sql/function.body.sql The Omiti version will output a single file with the objects in there, the other version will split the objects each in its own file in directories, to be svn / git friendly. With the python version: ./getddl.py -f -F fun_dir -d db -h host -p port -U user 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] how to get the height of index tree?
John R Pierce pie...@hogranch.com writes: 2010/10/22 sunpeng blueva...@gmail.com mailto:blueva...@gmail.com Hi, how to get the height of R* or B tree of created index ? You could use Gevel for GiST and GIN: http://www.sai.msu.su/~megera/wiki/Gevel Don't know if something similar exists for btree, but I guess it would be very useful. 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] Replication
Jonathan Tripathy jon...@abpni.co.uk writes: What is the difference between the Hot-Standby and Warm-Standby? Is the only different that the Hot-Standby standby servers are read-only, whereas the Warm-Standby standbys can't be queried at all? That and the fact that running queries are not canceled at the time you flick the switch to have your standby a master. The ongoing read-only traffic is not affected. That's hot. 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] index in desc order
Michal Politowski mpol...@meep.pl writes: Cannot there be a (system/hardware) setup where there is a perceptible performance difference between forward and backward index scans? I think it's been reported already that backward index scans indeed can be much slower than forward index scan, but that how to model that is still unclear and undone in the cost estimations. You will have to crawl the pgsql-performance list yourself, though… 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] Visualize GiST Index
Oleg Bartunov o...@sai.msu.su writes: We never expected gevel will be used by users :-) It's very very useful when developing custom GiST indexes! -- dim -- 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] Good candidate query for window syntax?
Ketema Harris ket...@gmail.com writes: My goal is: To find the maximum number of concurrent rows over an arbitrary interval. My guess is that the following would help you: http://wiki.postgresql.org/wiki/Range_aggregation -- dim -- 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] table - sequence dependent informatio
Peter Geoghegan peter.geoghega...@gmail.com writes: On 31 August 2010 18:02, akp geek akpg...@gmail.com wrote: Hi all - Is there a way I can tell table - sequence dependent information. which sequences are being used by which table? thanks for the help Take a look at the post Finding orphaned sequences on this blog: http://blog.tapoueh.org/blog.dim.html Or use the direct link to the article : http://tapoueh.org/articles/blog/_Finding_orphaned_sequences.html Regards, -- dim -- 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] PostgreSQL and distributed transactions
Jayadevan M jayadevan.maym...@ibsplc.com writes: But the initial setup for the client is done by 'Admin' and in that work-flow, we need distributed transactions. The transaction will start from the 'Admin server, do some inserts on the 'Client' server and then either rollback or commit on both the servers. Is it possible to do this with PostgreSQL? Any helpful links? See about 2 Phase Commit, but you will need a transaction manager I guess : http://www.postgresql.org/docs/current/static/sql-prepare-transaction.html Regards, -- dim -- 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 BYTEA from/to BIGINT
Vincenzo Romano vincenzo.rom...@notorand.it writes: Now, why doing this? I am using a plain SEQUENCE to create a (kind of) session ID. That is simple but predictable. The idea is to use this function in conjunction with encrypt (from pgcrypto) and the blowfish algorithm to make that sequence numbers somehow unpredictable. I'm pretty sure there are better (or at least easier) solutions out there, but there needs to be also some fun in here. I think you'd be interested into the following: http://wiki.postgresql.org/wiki/Pseudo_encrypt Regards, -- dim -- 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] Integral PG DB replication
Gauthier, Dave dave.gauth...@intel.com writes: Will DB replication be integral in v9? If so, when (approx) will that be out? Integral meaning all the cluster at a time, yes. You can help have it out sooner by testing it and reporting your findings. I think the goals are to have 9.0.0 out by mid august or about, so that's this summer. Regards, -- dim -- 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] No PL/PHP ? Any reason?
Joshua D. Drake j...@commandprompt.com writes: Any chance that the Parrot runtime could be used for PHP and other languages? I read that some folks are working on PL/Parrot. I'd really like to have PHP and Lisp for PL languages :). http://plscheme.projects.postgresql.org/ Not exactly lisp, but But it's using gnu guile, version 2 of which should support multiple languages. It's expected to get released with scheme, javascript and lua support last I checked, and maybe with Emacs Lisp support too. http://www.gnu.org/software/soc-projects/ideas-2010.html#guile So who want to open bets for Parrot versus Guile2? More seriously though, it could be that our best bet to have pl/js is plscheme and guile2. Regards, -- dim -- 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] copy/duplicate database schemas
Merlin Moncure mmonc...@gmail.com writes: On Wed, Jun 23, 2010 at 3:21 PM, Jamie Kahgee jamie.kah...@gmail.com wrote: I have an application in a schema and now i need to create other schemas b/c the app needs to support different languages, is there an easy way to copy an entire schema to a new one (tables, contents, trigges, functions, etc..)? right now i just have schema what I want is schema schema_ar schema_ru etc... sure: 1) pg_dump -n your_schema -s 2) sed the resulting schema s/old_schema/new_schema if you are lucky, you will have few if any improper replacements, if not, tweak sed till it's right 3) cat it back into psql I think you could also : 1) pg_dump -n your_schema -s your_schema.sql 2) alter schema your_schema rename to your_schema_ar; 3) psql -f your_schema.sql 4) goto 2, pick another name Regards, -- dim -- 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] High Availability with Postgres
John R Pierce pie...@hogranch.com writes: yeah. generally when money is involved in the transactions, you gotta stick to the 'no committed data lost ever'. there's plenty of other use cases for that too. Well, it's a cost/benefit/risk evaluation you have to make. It'd be bad news that the cost for covering your risk is more expensive that the risk itself, meaning there's no benefit walking the extra mile. Regards, -- dim -- 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] libpython - cannot open shared object file
Tom Wilcox hungry...@gmail.com writes: Next problem: $libdir/fuzzystrmatch: No such file or directory. I guess I need to install the /share stuff separately.. Looks like apt-get install postgresql-contrib-8.4 should do the trick. http://packages.debian.org/lenny-backports/postgresql-contrib-8.4 Regards, -- dim -- 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] What type of index should I use?
Mike Christensen m...@kitchenpc.com writes: I have a varying(200) text column that I need to be able to do lookups on very fast (WHERE col = 'foo') Btree is what to use here. GIN covers cases where you index arrays. I estimate the table will hold around 5,000 rows, never any more. It could be that you're better off without any index, depending on the size of rows you put in there, and the overall memory usage patterns you have. Regards, -- dim -- 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] High Availability with Postgres
John R Pierce pie...@hogranch.com writes: failure modes can include things like failing fans (which will be detected, resulting in a server shutdown if too many fail), power supply failure (redundant PSUs, but I've seen the power combining circuitry fail). Any of these sorts of failures will result in a failover without corrupting the data. and of course, intentional planned failovers to do OS maintenance... you patch the standby system, fail over to it and verify its good, then patch the other system. Ah, I see the use case much better now, thank you. And I begin too see how expensive reaching such a goal is, too. Going from I can lose this many transactions to No data lost, ever is at that price, though. Regards, -- dim -- 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] Hot Standby switchover
Hi, Shoaib Mir shoaib...@gmail.com writes: Now Hot-Standby is setup like this... A -- B A -- C Now if I will like to do a switch so as to make B the new master and have my replication look like this: B -- C B -- A Where are the steps I need for doing so? First, you need a common archive somewhere all the 3 servers can read it, and you need to setup the restore_command so that you can use it. Now, you can change the primary_conninfo on C so that it connects to B. I guess that means a restart. Note that you only want to do that once server B is the new master, so has been triggered as such. That means a change of TimeLine, though, so it'll break streaming : that's when you need to read the WALs from the archive, with the restore_command. Then, to have the old master A a slave of the new master B, you need to do a full slave setup here, with base backup and all. I hope we will be able to get cascading support in 9.1 or before so that it's possible to simplify all this by having a relay server in the mix, so that you only talk to this one when changing the master. Of course enabling the old master as a slave would still need a full setup from scratch. There has been thoughts on how to bypass the base backup there on -hackers, rather promising, but I don't know of any work having been done yet. Guys are getting 9.0 out the door for now :) Regards, -- dim -- 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] High Availability with Postgres
John R Pierce pie...@hogranch.com writes: Two DB servers will be using a common external storage (with raid). This is also one of the only postgres HA configurations that won't lose /any/ committed transactions on a failure. Most all PITR/WAL replication/Slony/etc configs, the standby storage runs several seconds behind realtime. I'm not clear on what error case it protects against, though. Either the data is ok and a single PostgreSQL system will restart fine, or the data isn't and you're hosed the same with or without the second system. What's left is hardware failure that didn't compromise the data. I didn't see much hardware failure yet, granted, but I'm yet to see a motherboard, some RAM or a RAID controller failing in a way that leaves behind data you can trust. So my question would be, what case do you handle better with a shared external storage compared to shared nothing servers with some sort of replication (including WAL shipping)? Regards, -- dim -- 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] hi,i write a function in postgresql source code, how to register this function?
sunpeng blueva...@gmail.com writes: hi,i write a function in postgresql source code, how to register this function? See src/include/catalog/pg_proc.h But you should *really* consider making it a loadable module. That's the way it makes sense for any code you want to add in the server unless you're preparing a patch for PostgreSQL itself, or you're doing a new Index Access Method that you want crash safe. Regards, -- dim -- 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] Monitoring activities of PostgreSQL
Allan Kamau kamaual...@gmail.com writes: How can I monitor the actual DB activities during such times so I may better understand what the situation truly is. I have seen some users on this list posting some complex log/outputs, this are the kind of outputs I would like to capture and view. Where are they? The way you phrase the question makes me think about OProfile : http://wiki.postgresql.org/wiki/Profiling_with_OProfile Regards, -- dim -- 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] Is there a way to backup Postgres via SQL commands?
Frank Church voi...@googlemail.com writes: Are there SQL commands that can do a backup over a client connection, rather than from the command line like pgsql etc? That's pg_dump ? By that I mean some kind of SELECT commands that can retrieve the database's content as SQL commands that can be replayed to a server to restore it, rather than something that saves directly to file, or passes it through a pipe? If you want to retrieve the SQL commands that allows you to recreate a live database, use pg_dump. If you want to build a file-by-file replica of the live system (base backup) through a usual PostgreSQL connection, you can use pg_basebackup which is available on github: http://github.com/dimitri/pg_basebackup If you want to run pg_dump via an SQL query, I say I don't see any interest in doing so. Plain client-side pg_dump will get the data it needs (including necessary DDLs) through a normal PostgreSQL connection already. Arrange yourself so that you can run pg_dump! As other said, though, it can certainly be made, but not with some caveats. Do you want only the schema or the schema and the data? The first limitation I can think of is the 1GB - 4 bytes bytea datatype capacity in memory. Regards, -- dim -- 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] Cognitive dissonance
Dave Coventry dgcoven...@gmail.com writes: Formatted text, whether PDF, HTML or (heaven forbid!) Word Documents, is easier to read than unformatted plain text, and those of us without the OP's very admirable proficiency in vi remain at the mercy of the various readers and their associated search functions. However, I sure that it's not too arduous a task to extract the text in these documents and strip them of their formatting? Or am I missing something? Info documentation format. Text based, super user aware, easy to browse and search, has an index. You can even produce postgres.info today, it's just not optimised to be very friendly, it's missing mainly convenient table support and index. Regards, -- dim -- 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] Random Weighted Result Ordering
Eliot Gable egable+pgsql-gene...@gmail.com writes: I have a set of results that I am selecting from a set of tables which I want to return in a random weighted order for each priority group returned. Each row has a priority column and a weight column. I sort by the priority column with 1 being highest priority. Then, for each distinct priority, I want to do a weighted random ordering of all rows that have that same priority. I select the set of rows and pass it to a custom-built function that does the ordering. I have tested both the prioritize and the random weighted ordering functions and they do exactly what I want them to do for ordering the data that I send them. The problem comes from the fact that I tried to make them generalized. They take an array of a small complex type which holds just an arbitrary ID, the priority, and the weight. The output is the same information but the rows are in the correct order. I'd try having the function return just numbers in the right order, then use that in the ORDER BY. To have those numbers, you'd still need to join with the result of the function, tho. Hope this helps you already, I don't have time to go deeper in the subject! Regards, -- dim -- 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] pgbouncer
Mario Ignacio Rodríguez Cortés ignacio.cor...@inegi.org.mx writes: Well, i have a question if you know about this, i have a webserver in a server and i have a database server, the question is: where should I install the pgbouncer? in webserver, in database server or is the same? whats your experience. I always install an instance of pgbouncer per webserver, and sometimes I also have another pgbouncer on the PostgreSQL box too. I don't think I ever share a single pgbouncer for several web servers directly though. Regards, -- dim -- 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] libreadline and Debian 5 - not missing just badly named
J. Bagg j.b...@kent.ac.uk writes: I've just had the common problem with not finding the readline library while compiling/linking 8.4.4 on a new linux (Debian 5 - lenny). Tried: apt-get build-dep postgresql-8.4 That command will install all what you need to compile your own PostgreSQL. Some will add and some more, because it will care for doc building dependencies to. Regards, -- dim -- 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] INSERTing lots of data
Greg Smith g...@2ndquadrant.com writes: Joachim Worringen wrote: my Python application (http://perfbase.tigris.org) repeatedly needs to insert lots of data into an exsting, non-empty, potentially large table. Currently, the bottleneck is with the Python application, so I intend to multi-thread it. Each thread should work on a part of the input file. You are wandering down a path followed by pgloader at one point: http://pgloader.projects.postgresql.org/#toc6 and one that I fought with briefly as well. Simple multi-threading can be of minimal help in scaling up insert performance here, due to the Python issues involved with the GIL. Maybe we get Dimitri to chime in here, he did more of this than I did. In my case pgloader is using COPY and not INSERT. Which would mean than while one python thread is blocked on network IO the others have a chance of using the CPU. That should be a case where GIL is working ok. My tests show that it's not. Two thoughts. First, build a test performance case assuming it will fail to scale upwards, looking for problems. If you get lucky, great, but don't assume this will work--it's proven more difficult than is obvious in the past for others. Second, if you do end up being throttled by the GIL, you can probably build a solution for Python 2.6/3.0 using the multiprocessing module for your use case: http://docs.python.org/library/multiprocessing.html My plan was to go with http://docs.python.org/library/subprocess.html but it seems multiprocessing is easier to use when you want to port existing threaded code. Thanks Greg! -- 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] Documentation availability as a single page of text
Alvaro Herrera alvhe...@alvh.no-ip.org writes: There's a texinfo output that could perhaps be useful. Try make postgres.info in the doc/src/sgml directory; while it's tagged experimental and outputs a boatload of warnings, it does work for me and the text it produces is plain enough. It's pretty good indeed. Thanks for sharing the tip. But it's very impractical to read any table in info format, and it's missing an index. I guess the table format could be readable if using the equivalent of psql's \x (1 row per column) and an empty line between rows, but I have no idea how to do it. Ditto for indexing. Regards, -- dim -- 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] PostgreSQL vs. Microsoft SQL server
Merlin Moncure mmonc...@gmail.com writes: On Sat, May 1, 2010 at 2:47 PM, Thomas Løcke thomas.granv...@gmail.com wrote: Anybody know of any recent comparisons made between the two? I'm in the process of buying a new telephony related software suite, if you are writing stuff in C/C++, doing significant coding INSIDE the database (pl/pgsql, C, etc) and/or think of the database as a self contained development environment, then postgresql is definitely the right choice. our C interface (both libpq and internal) is excellent, variety of server side programming options is second to none, and postgres's flexible type system is fantastic and capable of doing all kinds of things that bend the rules of what a database is 'supposed to do'. An specific example here would be the prefix_range data type that handle indexing telephony routing queries. See it here: http://prefix.projects.postgresql.org/ Keep in mind you'll find other side projects along with PostgreSQL to help you answer to your business needs. It's not just about which is faster on some random benchmark but which helps you best doing your work. I know nothing about MSSQL, but another axe of analysis that I tend to follow is the error management. PostgreSQL is very good on this point, you really can trust your data to it. Regards, -- dim -- 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] Fuzzy string matching of product names
Leif Biberg Kristensen l...@solumslekt.org writes: On Monday 5. April 2010 22.00.41 Peter Geoghegan wrote: similar they sound. How can that actually be applied to get the functionality that I've described? I've got a similar problem in my 18th century research, when clerks usually took pride in being able to spell a name in any number of ways. I've landed on a solution where I'm sending search strings to SIMILAR TO. I usually get far too many hits, but it's much easier to browse through 100 hits than the entire dataset which is approaching 60,000 records. In both your cases I'd play with trigram search. The idea is dead simple and it performs really well. It's the poor man's Full Text Search, but for catalog look ups it's exactly what you want I suppose: http://www.postgresql.org/docs/8.3/static/pgtrgm.html Regards, -- dim -- 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] optimizing import of large CSV file into partitioned table?
Rick Casey caseyr...@gmail.com writes: So, I am wondering if there is any to optimize this process? I have been using Postgres for several years, but have never had to partition or optimize it for files of this size until now. Any comments or suggestions would be most welcomed from this excellent forum. The pgloader tool will import your data as batches of N lines, you get to say how many lines you want to consider in each transaction. Plus, you can have more than one python thread importing your big file, either sharing one writer and having the other threads doing the parsing and COPY, or having N independent threads doing the reading/parsing/COPY. http://pgloader.projects.postgresql.org/ Hope this helps, -- dim -- 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] pgreplay log file replayer released
Albe Laurenz laurenz.a...@wien.gv.at writes: It seems that Tsung currently only supports basic queries, but I assume that this can be improved. In fact from the time when PostgreSQL support was added, some more Erlang drivers have appeared and some of them covers the entire protocol. So it should be possible to update Tsung to use them, given some interest. One thing that Tsung, recording queries as proxy, will never be able to handle are encrypted connections, but I guess that's a minor problem. Yes, because you typically run the proxy only to record sessions, in order to prepare the tsung setup. Another way to go from logs is to use pgfouine, which knows how to prepare a tsung config from PostgreSQL logs: http://pgfouine.projects.postgresql.org/tsung.html On the usability side, Tsung will require that all clients are redirected to the recording proxy, while pgreplay will only require that the logging configuration settings on the server are changed. This can be an advantage in large distributed production environments. Well never use the tsung recorder in production. Ever. Run it to construct your sessions files from your application. Regards, -- dim -- 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] stopping processes, preventing connections
Scott Marlowe scott.marl...@gmail.com writes: It was a few posts back, but our discussion point was minor point upgrades and the fact that OP was running 8.3.1 and not sure there were updates to 8.3.9 (or latest) out there for debian. I'm quite sure debian has 8.3.9 out by now. Yes: http://packages.debian.org/lenny/postgresql-8.3 http://packages.debian.org/etch-backports/postgresql-8.3 You wont' find it in testing/unstable though, because next stable will contain 8.4 only, as far as I understand. Regards, -- dim -- 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] pgreplay log file replayer released
Greg Stark gsst...@mit.edu writes: Do you have a multi-threaded model that tracks which transactions each query belonged to and runs them concurrently like they were in the original setup? That's what I've been looking for. Tsung does that and has been doing it for… quite some time. It even comes with a recorder which is a PostgreSQL proxy: connect it to your server, connect your client to it, and let it record a session at a time. Then in the configuration you get to choose how many of each session you want to mix, etc. http://tsung.erlang-projects.org/ Regards, -- dim My TODO has write a Tsung blog entry (series?) and a tutorial, but you'll have to wait until after extensions and some other things, or do it yourself... sorry about that... -- 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] Postgres 8.4 Segfault on xpath
Frank jansen jan...@fumarium.de writes: I tried postgres 8.4.0 and 8.4.2 self compiled with elf compiled contrib directory, tried Debians 8.4 backport package with the 8.4 contrib package and all do the same: Segfault and terminated by Signal 11 :-( Try 8.4.3, which has some XML crash related fix: http://developer.postgresql.org/pgdocs/postgres/release-8-4-3.html Regards, -- dim -- 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] make available C extensions to others
Ivan Sergio Borgonovo m...@webthatworks.it writes: I've finished to write an extension to manipulate tsvectors and tsquery in C. I think it could be useful for someone else and I think I may take advantage at someone else looking at the code too. What would be the right place where to publish the code and how? Create a pgfoundry project, and publish some doc and pointers at the public URL for visibility: mycode.projects.postgresql.org. Then I'd say host the code elsewhere, unless you're fond of CVS. I picked up github, but any hosting would do I guess. Same for mailing lists and all. I think that's the current advice. Regards, -- dim -- 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] accessing the words in a full text index
Massa, Harald Armin c...@ghum.de writes: I want to provide a suggest word as you type feature in an application (like google suggest). All the documents are - of course - stored within a PostgreSQL database, within TEXT columns. See pg_trgm. http://www.postgresql.org/docs/8.4/static/pgtrgm.html Regards, -- dim -- 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] What's the best way to deal with the pk_seq sequence value after a restore (bulk loading)?
Wang, Mary Y mary.y.w...@boeing.com writes: Because the current value is 6818, during the restore process, it complained about duplicate key value violates unique constraint bug_pkey, because the value of bug_pk_seq for a insert has been already been used. So what is the best way to resolve this? Should I set the value for bug_pk_seq to be 1 in the beginning of the dump file? Any suggestions? See the following article: http://tapoueh.org/articles/blog/_Resetting_sequences._All_of_them,_please!.html Regards, -- dim -- 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] One solution for Packages in Postgre
venkatra...@tcs.com writes: I am new to Postgre. We are migrating an oracle db to postgre. In oracle we have used so many packages. As per my understanding, there is no oracle package like functionality in postgre. I was just trying to find some way to migrate ocale packages to postgre. This has been discussed already in the past, see the archives. In short, Oracle Packages are not something the PostgreSQL community intend to offer directly, even if we have some plans to provide better tools for managing what we have and call extensions. The following shows the current state of the design, the code is yet to be written, and that could happen this very year (it obviously missed 9.0). http://wiki.postgresql.org/wiki/ExtensionPackaging You will note that this document refers to C-coded extensions, but is compatible with an extension not containing one of those. It could well be that the feature you're missing is offered by this design. Regards, -- dim -- 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] Having a plpgsql function return multiple rows that indicate its progress in a cursor like fashion
Tom Lane t...@sss.pgh.pa.us writes: Peter Geoghegan peter.geoghega...@gmail.com writes: Aren't my requirements sufficiently common to justify developing a mechanism to report progress back to client applications during batch operations and the like? Have you experimented with RAISE NOTICE? Using it this way is a bit of a hack maybe, but I think you are entirely unaware of what would be involved in producing something that's less of a hack. Would returning a refcursor then using fetch in the application be another solution? As far as hacking is concerned, I think it boils down to materialise against value-per-call implementation, right? Not saying it's easy to implement value-per-call support in plpgsql, but should the OP think about what's involved, is that the track to follow? http://git.postgresql.org/gitweb?p=postgresql.git;a=blob;f=src/backend/utils/fmgr/README;hb=HEAD#l380 http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=d9a319525591bc437e4770b4e796a7517844a784 The first link is the fmgr/README explaining the concepts, and the second one is a recent enough patch dealing with materialise and value-per-call in the context of SQL functions. Regards, -- dim -- 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] COPY FROM wish list
Scott Bailey arta...@comcast.net writes: PgFoundry has http://pgfoundry.org/projects/pgloader/ It is a step in the right direction but definitely not as powerful as sql*loader. Yeah, it's only offering what I needed and what I've been requested to add. So far there's support for INFORMIX UNLOAD files, mysqldump -t files, fixed with files, ragged files, and some more. And it also support python threading for some parallel workload, either loading several files at once or several chunks of the same file, and then 2 modes are possible. If you need more, try asking, you never know. I'm still on the hook to maintaining it, though I've not received any bug report in a long while. I guess it's not much used anymore. I've been proposed to replace the pgloader.conf INI file with a custom COPY command parser exposing all the options, and will consider that sometime in the future. I've been writing a Postgres equivalent that does Oracle SQL*Loader/DB2 Load style import, and external tables using CSV, fixed with and XML data sources. But its not production ready. I'm hoping SQL/MED makes it in to the next release of Postgres so I can throw it all away :) XML support in pgloader will certainly take the form of applying user given XSLT filter that outputs CSV from the XML. That's the option requiring the less code in pgloader I guess :) I'd be happy to see pgloader deprecated by PostgreSQL offering its features. Meantime, do you want to join the fun on the pgloader front? -- dim -- 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] using new Hstore in PG8.4
Marek Lewczuk ma...@lewczuk.com writes: there are a lot of improvements in hstore, that is planned for PG8.5 - as far I remember it compiles fine with PG8.4 so is it save to use it with PG8.4 ? See hstore-new, which delivers the same code to be found in 9.0 as a module against 8.3 and 8.4. http://pgfoundry.org/projects/hstore-new http://packages.debian.org/sid/postgresql-8.3-hstore-new http://packages.debian.org/sid/postgresql-8.4-hstore-new It's already packaged in debian, too. Regards, -- dim -- 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] Questions on PostGreSQL Authentication mechanism...
Alvaro Herrera alvhe...@commandprompt.com writes: For example, perhaps there could be a new pair of functions pg_read_hba_file/pg_write_hba_file that would work even if the files are placed in other directories, but they (Debian) would need to propose it. I don't remember they had to provide those GUCs: http://www.postgresql.org/docs/8.4/static/runtime-config-file-locations.html hba_file (string) Specifies the configuration file for host-based authentication (customarily called pg_hba.conf). This parameter can only be set at server start The bug certainly is on PostgreSQL for providing the facility to relocate the hba_file without providing any way for pgadmin and other utilities to handle the situation? Regards, -- dim -- 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] more docs on extending postgres in C
Ivan Sergio Borgonovo m...@webthatworks.it writes: - installing postgresql-server-dev-[version] - apt-get source postgresql-server-dev-[version] - copy from there a contrib dir in my ~ (or wherever you prefer) - export USE_PGXS=1; make Don't forget apt-get build-dep postgresql-[version] I didn't have postgresql server installed on this box but I have stuff that got in to satisfy dependencies for php/python pg drivers and psql. I wasn't able to understand from where PGXS pick up the version since I installed -dev-8.3 but everything else was for 8.4 and it didn't work: See pg_config, there's the default one then one per major version. Thanks... I'd publish a summary as soon as I've clearer ideas so that the next poor guy will find easier to write contrib on Debian. I don't know if the version problem is worth a bug report to Debian (or pg people that built the pgxs system). I' working on this, see the following email and thread. http://lists.alioth.debian.org/pipermail/pkg-postgresql-public/2010-January/000546.html In short it's about doing a VPATH build and setting some variables, then building a PGXS extension is very easy. Regards, -- dim -- 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] more docs on extending postgres in C
Ivan Sergio Borgonovo m...@webthatworks.it writes: I haven't been able to find anything better than the online manual and pg source code to learn how to write extensions. Maybe this will help: http://wiki.postgresql.org/wiki/Image:Prato_2008_prefix.pdf http://github.com/dimitri/prefix Regards, -- dim -- 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] C: extending text search: from where to start
Ivan Sergio Borgonovo m...@webthatworks.it writes: Any other resource that will help me to write my own contrib? You could try out the following, but it deals a lot with GiST specifics… http://wiki.postgresql.org/wiki/Image:Prato_2008_prefix.pdf Regards, -- dim -- 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] Is there a messaging system for Postresql
Romeliz Valenciano Jr. jedi_kny...@yahoo.com writes: We're evaluating Postgresql for a possible transition from MS SqlServer. We have 100s of MS Sql servers and one way of replicating data changes is to use MQ Series Queue management system, some servers who were recently changed to Sql 2005 are using Service Broker. We're interested if there's an existing messaging system/tool present in Postgresql as well. I don't get what your tools are about, but the keywords do ring some bells. I'd bet Golconde and PGQ could serve you. http://wiki.postgresql.org/wiki/Skytools http://code.google.com/p/golconde/ Regards, -- dim -- 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] C: extending text search: from where to start
Ivan Sergio Borgonovo m...@webthatworks.it writes: I'd appreciate any pointer that will quickly put me on the right track. I'd guess you begin here: http://wiki.postgresql.org/wiki/Developer_FAQ -- dim -- 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] Avoid transaction abot if/when constraint violated
Gauthier, Dave dave.gauth...@intel.com writes: I have a long list of records I want to insert into a table in such a way as I can trap and report any/all constraint violations before rolling back (or opting to commit). Unfortunately, after I hit the first constraint violation, it aborts the transaction, and then reports “ERROR: current transaction is aborted, commands ignored until end of transaction block”. Is there a way around this? Either load to another table with no constraint then use it as a source for loading the constrained one, excluding the problematic rows: INSERT INTO ... SELECT * FROM import.table LEFT JOIN ... ; Or try using pgloader once the input format is CSV like rather than INSERT. Regards, -- dim -- 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] Pre-calculate hash join
war...@warrenandrachel.com writes: When joining two large tables [common in warehousing], a hash join is commonly selected. Calculating hash values for the merge phase is CPU intensive. Is there any way to pre-calculate value hashes to save that time? Would it even grant any performance to skip the build phase of the hash join? Maybe maintaining a materialized view then using it in the queries would be sufficient to solve your problem? -- dim -- 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] replication from multiple master servers to a single read-only slave
Omar Mehmood omarmehm...@yahoo.com writes: I really don't want to use separate schemas for each master to logically partition the data. I ensure that the data on each master will not clash with each other (in terms of any DB level contraints such as PK), so I'd much prefer they all reside in a single schema. Also, my understanding is that Slony uses DB triggers to track changes (but I want to avoid using DB triggers). If you want to avoid all PostgreSQL features… well I don't see that I can help you here. If you were to change your mind the following document talks about how to federate data from several databases to the same central one, and using either inheritance or triggers to move the incoming data from the N origin schemas to the central one. I guess how the data gets to being available for your central queries is not solved by refusing to use a schema per origin server. http://wiki.postgresql.org/wiki/Londiste_Tutorial#Federated_database Another additional constraint-- the master servers may not always have connectivity to the slave machine, so the chosen mechanism needs to be robust and not assume 100% uptime. Londiste fits this need. Regards, -- dim -- 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] dynamic insert in plpgsql
Grzegorz Jaśkiewicz gryz...@gmail.com writes: Is there any nice way to do something like that in plpgsql: EXECUTE 'INSERT INTO '||partition_table_name||' VALUES'||(NEW.*)||''; See http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers -- dim -- 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] PostgreSQL Write Performance
Greg Smith g...@2ndquadrant.com writes: If you're OK with the possibility of losing a measurement in the case of a system crash Then I'd say use synchronous_commit = off for the transactions doing that, trading durability (the 'D' of ACID) against write performances. That requires 8.3 at least, and will not fsync() before telling the client the commit is done. Regards, -- dim -- 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] PostgreSQL Write Performance
Tim Uckun timuc...@gmail.com writes: Is there a command like COPY which will insert the data but skip all triggers and optionally integrity checks. pg_bulkload does that AFAIK. http://pgbulkload.projects.postgresql.org/ Regards, -- dim -- 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 tables to XML and back
shulkae shul...@gmail.com writes: Another requirement is to convert the stored XML file back to the original tables. This helps us to clone a system. I was thinking to use Perl XML Simple module to generate XML files. What about using pg_dump and pg_restore for the cloning, or maybe a replication solution? I don't know about your other needs for XML backups, but I'd prefer pg_dump over them anytime. -- dim -- 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] Visual DATA editor for PostgreSQL?
Dmitry Koterov dmi...@koterov.ru writes: Is there a GUI utility to visually edit Postgres DATA (not a database schema!), which allows at least: - insert/update rows using screen windowed forms (possibly ugly auto-generated forms, but - still forms) - insert foreign key references by selecting them from a list (not by typing the keys manually) - work with multi-line text fields (textarea) for TEXT columns Have you tried Kexi yet? http://kexi-project.org/ Regards, -- dim -- 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] pg_dump excluding tables content but not table schema
Ivan Sergio Borgonovo m...@webthatworks.it writes: It seems that you have to actually restore the 2 backup separately. pg_restore -1 -d mydb nearly_full.bak pg_restore -1 -d mydb schema_only.bak I can't think of any other way to restore both in one transaction unless I backup in plain text. But that should have other drawback. Any hint? In general what I do is backup it all then filter at restore time, editing the restore catalog (see pg_restore options -l and -L). Incidentally I've written code for automating this for me, that's called pg_staging and is available at github and already is in debian (I miss a source release so that RPM will follow): http://github.com/dimitri/pg_staging Regards, -- dim -- 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] Extracting SQL from logs in a usable format
Le 21 déc. 2009 à 15:24, Chris Ernst a écrit : Ouch! You're right. And that's would be a deal killer for me. About 90% of the traffic is prepared queries that are run over and over with different parameters. The driver project and code are now there it seems: http://frihjul.net/pgsql http://github.com/noss/pgsql/tree Maybe you could ask the author about supporting the extended protocol, a quick browsing tonight shows me prepare/execute support. I'm sure if improvements in the pgsql driver would translate to improvements in the tsung support of it, but it should be about it. Regards, -- dim -- 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] Extracting SQL from logs in a usable format
Le 19 déc. 2009 à 16:20, Chris Ernst a écrit : Hmm.. That does look very interesting. The only thing that concerns me is where it says it supports Basic Queries (Extended queries not yet supported). I'm not sure what is meant by Extended queries. Any idea? I think it refers to the Extended Query support in the frontend / backend protocol, as in the documentation: http://www.postgresql.org/docs/8.4/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY The extended query protocol breaks down the above-described simple query protocol into multiple steps. The results of preparatory steps can be re-used multiple times for improved efficiency. Furthermore, additional features are available, such as the possibility of supplying data values as separate parameters instead of having to insert them directly into a query string So that's for parse/bind/execute communications, which are used in prepare/execute and queryParam I think. -- dim -- 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] Table Partitioning Advice Request
Vincenzo Romano vincenzo.rom...@notorand.it writes: Is there any performance study for the trigger-based implementation? Consider that if you use RULE to partition, when you DROP a partition the INSERTs are locked out because the query depends on the table being droped. That alone could lead you to stop considering RULEs for partitioning. -- dim -- 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] Justifying a PG over MySQL approach to a project
Gauthier, Dave dave.gauth...@intel.com writes: I am more concerned with getting a robust DB replication system up and running. Bucardo looks pretty good, but I've just started looking at the options. Any suggestions? Master Slave replication? Meaning no writes on the sister site. If yes, consider Londiste from Skytools. Easy to setup and maintain, and robust. http://wiki.postgresql.org/wiki/Skytools http://wiki.postgresql.org/wiki/Londiste_Tutorial Regards, -- dim -- 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] How to remove non-UTF values from a table?
Phoenix Kiula phoenix.ki...@gmail.com writes: Is there any SQL possibility to find these columns and replace them with utf-8 equivalents using some postgresql commands? Couldn't find anything in the Strings functions (chapter 9 of manual). I've bookmarked this for later: http://archives.postgresql.org/pgsql-general/2009-07/msg00904.php Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Hi, Le 11 déc. 2009 à 01:43, Bruce Momjian a écrit : Would you be up for writing the extension facility? Uh, well, I need to help with the patch commit process at this point --- if I find I have extra time, I could do it. I will keep this in mind. If you ever find the time to do it, that would be excellent! The extension facility is on the top list of Josh Berkus missing things we'll have to provide soon (or something) and a very annoying missing feature, the last stone of the high praised extensibility of PostgreSQL. http://it.toolbox.com/blogs/database-soup/postgresql-development-priorities-31886 It could also means that pg_migrator would have an easier time handling user data types etc, if extension authors are able to implement the hooks to call to migrate their data from previous to next major version ondisk format. Anyway, thanks for considering it! -- dim PS: of course I've developed some ideas of how I'd like this to work and some use cases too, so bug me on IRC or whatever for details etc :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Tom Lane t...@sss.pgh.pa.us writes: It's not impossible that we'll have to tweak pg_dump a bit; it's never had to deal with languages that shouldn't be dumped ... Ah, the best would be to have extensions maybe. Then you could do this in initdb, filling in template0: CREATE EXTENSION plpgsql ...; Then at createdb time, what would become automatic is: INSTALL EXTENSION plpgsql; And that's it. pg_dump would now about extensions and only issues this latter statement in its dump. Bruce, there are some mails in the archive with quite advanced design proposal that has been discussed and not objected to, and I even provided a rough sketch of how I wanted to attack the problem. http://archives.postgresql.org/pgsql-hackers/2009-06/msg01281.php http://archives.postgresql.org/pgsql-hackers/2009-07/msg01425.php http://archives.postgresql.org/pgsql-hackers/2009-07/msg01468.php The major version dependant SQL code is now much less of a problem than before because we have inline DO statements. So you don't need to create a function for this anymore. Real life kept me away from having the time to prepare the code patch, and I don't think that will change a bit in the 8.5 release cycle, whatever my hopes were earlier this year. But having everyone talk about the feature and come to an agreement as to what it should provide and how was the hard part of it, I think, and is now about done. Would you be up for writing the extension facility? Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Tom Lane t...@sss.pgh.pa.us writes: Right, just like every other thing that's pre-installed. If a particular installation wishes to let individual DB owners control this, the superuser can drop plpgsql from template1. It's not apparent to me why we need to allow non-superusers to override the project's decisions about what should be installed by default. I guess it boils down to being nice to hosting platforms, where they will want to give as much power as can be given to database owners without having those hosted people be superusers. So should the decision to remove plpgsql be on the hosting platform hands or the hosted database owner? Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Tom Lane t...@sss.pgh.pa.us writes: Why not? If they really want to prohibit use of a feature the upstream project has decided should be standard, that's their privilege. Well, I guess they could also automate their database creation to fix the privileges and assign the ownership of the language to the owner of the database. Then whether or not to have plpgsql there is up to the owner. For non-hosted environments, you always want to tweak some things, like installing plpgsql in the first place. So... The argument against seems to be basically this should work exactly like it did before, but if that's the standard then we can never have plpgsql installed by default at all. Don't get me wrong, I'm all for having plpgsql installed by default. I though we were talking about how to provide that and trying to decide if having to be superuser to drop plpgsql after having created the database is blocking the way forward, knowing than installing the language only requires being database owner. Regards, -- dim -- 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] pgsql 'prefix' error
Hi, Dimitri Fontaine dfonta...@hi-media.com writes: Can you test with this version and maybe better data set? [...] Of course changing that will discard any btree containing a prefix_range column, so that's going to be 1.1.0 if workable. http://github.com/dimitri/prefix http://github.com/dimitri/prefix/commit/e1bcb8e28305c9257655548a70c9fc05a21e9a1e It's being uploaded to debian now. I'm going to release 1.1.0 source tarball (only) on pgfoundry. Regards, -- dim -- 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] pgsql 'prefix' error
Hi,
Bino Oetomo b...@indoakses-online.com writes:
ERROR: duplicate key value violates unique constraint myrecords_pkey
CONTEXT: COPY myrecords, line 2: 12
I think I should add the following code comment to the documentation, if
not already done:
/*
* We invent a prefix_range ordering for convenience, but that's
* dangerous. Use the BTree opclass at your own risk.
*
* On the other hand, when your routing table does contain pretty static
* data and you test it carefully or know it will fit into the ordering
* simplification, you're good to go.
*
* Baring bug, the constraint is to have non-overlapping data.
*/
You have to remember that '12'::prefix_range could be spelled as the
regular expression '12.*'. So that '1'::prefix_range contains '12'.
The BTree opclass is not made to resist to overlapping data. Maybe in
this case though we could say that 12 contains less elements than 1 so
it's less than 1. Here's a test to redefine the pr_cmp() operator in
term of that, as a patch against current CVS (which is 1.0.0).
Can you test with this version and maybe better data set?
Note that as said earlier the indexing you need to speed up queries is
the GiST one, but it could be you want the PK constraint noneless.
prefix=# select prefix_range_cmp('1', '12');
prefix_range_cmp
--
1 -- it is 0 without the patch.
(1 row)
This means '1'::prefix_range '12'::prefix_range and you're now able to
create your PRIMARY KEY on the example data. It's still not very useful
for the general case, but could be argued as better...
Of course changing that will discard any btree containing a prefix_range
column, so that's going to be 1.1.0 if workable.
Regards,
--
dim
PS: no worry about the operators themselves, they are defined atop cmp:
static inline
bool pr_lt(prefix_range *a, prefix_range *b, bool eqval) {
int cmp = pr_cmp(a, b);
return eqval ? cmp = 0 : cmp 0;
}
static inline
bool pr_gt(prefix_range *a, prefix_range *b, bool eqval) {
int cmp = pr_cmp(a, b);
return eqval ? cmp = 0 : cmp 0;
}
? README.html
? TESTS.html
? prefix.sql
? prefixes.check.pl
? todo
? varlena.gavin.snippet.c
? debian/files
? debian/postgresql-8.3-prefix
? debian/postgresql-8.3-prefix.debhelper.log
? debian/postgresql-8.3-prefix.substvars
? debian/postgresql-8.4-prefix
? debian/postgresql-8.4-prefix.debhelper.log
? debian/postgresql-8.4-prefix.substvars
Index: prefix.c
===
RCS file: /cvsroot/prefix/prefix/prefix.c,v
retrieving revision 1.54
diff -p -u -r1.54 prefix.c
--- prefix.c 6 Oct 2009 09:55:32 - 1.54
+++ prefix.c 25 Nov 2009 16:10:47 -
@@ -520,7 +520,11 @@ int pr_cmp(prefix_range *a, prefix_range
cmp = memcmp(p, q, mlen);
if( cmp == 0 )
-return (a-first == b-first) ? (a-last - b-last) : (a-first - b-first);
+/*
+ * we are comparing e.g. '1' and '12' (the shorter contains the
+ * smaller), so let's pretend '12' '1' as it contains less elements.
+ */
+return (alen == mlen) ? 1 : -1;
return cmp;
}
--
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] pgsql 'prefix'
Hi, Le 23 nov. 2009 à 17:04, Harald Fuchs a écrit : SELECT id, record FROM myrecords WHERE record @ '127' ORDER BY length(record::text) DESC LIMIT 1; In prefix 1.0.0 you can say ORDER BY length(record) DESC directly... -- Dimitri Fontaine PostgreSQL DBA, Architecte -- 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] schema proxying virtual database
Rajesh Kumar Mallah mallah.raj...@gmail.com writes: my question is , is it a feasible idea to have some special kind of database in the postgresql cluster that mimics a schema of an existsing database. Try abusing pgbouncer to this effect. Configure several pgbouncer databases pointing to the same physical one, with different roles e.g. having each their own search_path so that they first see their application's schema. Regards, -- dim -- 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] [Q] optmizing postgres for 'single client' / many small queries
Hi, Tom Lane t...@sss.pgh.pa.us writes: Just out of curiosity, does anyone know of any ORM anywhere that doesn't suck? They seem to be uniformly awful, at least in terms of their interfaces to SQL databases. If there were some we could recommend, maybe people would be less stuck with these bogus legacy architectures. It seems like people interrested into ORMs are the one who do not want to tackle SQL... and for people having some time to spend on the possibility of finding a good ORM: http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx A more practical discussion seems to be here (I still have to read it): http://omniti.com/seeds/orms-done-right Regards, -- dim PS: In short my advice is always to choose an ORM where it's easy to bypass query generation, and stick to not letting it generate SQL. Sometime basic CRUD is ok though (INSERT/UPDATE/DELETE one object/row at a time). -- 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] PL/Perl 64-bit and sending emails
Hi, Steve Atkins st...@blighty.com writes: On Sep 3, 2009, at 11:30 AM, Mark Lubratt wrote: Or, does someone know of another way to get the backend to send an email? Have a queue table in the database you put your emails into and an external process that polls the table, sends the email and deletes the entry from the queue. Apart from avoiding the ickiness of doing high latency work from a database function this also makes sending email transaction safe - if the transaction rolls back after sending the email, the email doesn't get sent. Using listen/notify based on a trigger on the table makes it a little more responsive. This comes up fairly often. It's probably worth doing a tidy perl daemon to handle it and stashing it up on pgfoundry. Or have a look at PGQ which is made to handle this kind of queue processing: http://wiki.postgresql.org/wiki/Skytools http://wiki.postgresql.org/wiki/PGQ_Tutorial http://kaiv.wordpress.com/2007/10/19/skytools-database-scripting-framework-pgq/ Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
