Re: [GENERAL] Connection Pooling
On Fri, Mar 26, 2010 at 11:17 PM, David Kerr d...@mr-paradox.net wrote: Howdy all, I have some apps that are connecting to my DB via direct JDBC and I'd like to pool their connections. I've been looking at poolers for a while, and pgbouncer and pgpool-ii seem to be some of the most popular, so i've started with those. I'm setting up pgbouncer, and i've hit a bit of a snag. Hopefully someone can tell me if pgbouncer or pgpool are capable of this (and if so, how to do it) or alternatly a pooler that can... What I'd like to be able to do is this (not using pooler syntax, this is just a high level of what i want to achive) Say i set max pool size = 10 connections. and max # of pools = 5. That means that i should have 5 connections to my database covering 50 connections total. I can't really seem to make that work with pgbouncer without naming the pools separetly. (pool1 = dbname = a, pool2 = dbname =a) which means my app is tied to a pool (or has to specifically code to rotate pools...) which is not really desireable. is what i'm looking to do possible in pgbouncer or pgpool? or anything? Thanks Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general You may also have a look at Commons DBCP from Apache software foundation, http://commons.apache.org/dbcp/;. I have used it for a few projects and have had no problems. Allan. -- 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] Connection Pooling
Allan Kamau wrote: You may also have a look at Commons DBCP from Apache software foundation, http://commons.apache.org/dbcp/;. I have used it for a few projects and have had no problems. for that matter, JDBC has its own connection pooling in java. -- 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] Get the list of permissions/privileges on schema
Okay..then could you please suggest me what could be the correct way? So far I have done following to meet my requirements: * I want users to use only stored procedures to create, alter, delete tables in mydb schema*. == For this I have *revoked all permissions from mydb *schema and stored procedures are defined with SECURITY DEFINER in postgres user context. I have given execute permission to set of users to these stored procedures to achieve my goal. *I want only allowed users to create table with foreign key references.* == This can be achieve using SET ROLE current user but *Postgresql doesn't allow SET ROLE in SECURITY DEFINER function* *context* so I have created a my_sudo function which gets invoked from my stored procedure. This sudo function creates a temporary SECURITY DEFINER function and changes owner to the current user before executing create table command. Now, as sudo function runs actual create command as current user context and he/she will not have permission on mydb schema, I have to grant the ALL permissions on mydb schema to current user temporary and then restore his/her actual privileges back to make sure that users actual permission doesn't change. Hence, I am asking how can I store the schema permissions list and restore it back once store procedure execution completed. Please let me know where I am going wrong here? I am trying to get my things done out of what PostGreSQL supports. It would be really nice if anyone could help me to achieve my requirements. Please feel free to let me know if you have any questions. Thanks a lot, Dipti On Fri, Mar 26, 2010 at 3:52 AM, John R Pierce pie...@hogranch.com wrote: dipti shah wrote: Thanks a lot guys but I am not looking for security definer function. I know it. My requirements are very complicated and I have to nailed down the stuffs by storing schema permissions somewhere, execute my store procedure, and restored the stored schema permissions. Like this I would make sure that thogh my store procedure manipulates schema permissions, at the end, users will have their permissions intact. thats totally the wrong way to do things in SQL.
[GENERAL] How can I import a perl module into a plperl function ?
Hi, I need to use sscanf in a plperl function. How can I import the module String::Scanf into my function ? Thanks ! Claudio Grillo cjgri...@gmail.com Brazil.
Re: [GENERAL] How can I import a perl module into a plperl function ?
On Sat, Mar 27, 2010 at 08:17:26AM -0300, Grillo Grillo wrote: Hi, I need to use sscanf in a plperl function. How can I import the module String::Scanf into my function ? use pl/perlu depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] Large index operation crashes postgres
Paul, I kindly received the information about the table data (quoting here): It changes as it goes down the table, it's a right mixture. ST_LineString |2 | 5398548 ST_LineString |3 | 2877681 ST_LineString |4 | 2160809 ST_LineString |5 | 1696900 ST_LineString |6 | 1362231 ST_LineString |7 | 1107733 ST_LineString |8 | 915616 ST_LineString |9 | 766904 ST_LineString | 10 | 646150 ST_LineString | 11 | 550356 ST_LineString | 12 | 473357 ST_LineString | 13 | 410038 ST_LineString | 14 | 358185 ST_LineString | 15 | 313985 ST_LineString | 16 | 278846 ST_LineString | 17 | 248253 ST_LineString | 18 | 220736 ST_LineString | 19 | 198809 ST_LineString | 20 | 179552 ST_LineString | 21 | 162140 ST_LineString | 22 | 147957 ST_LineString | 23 | 134321 ST_LineString | 24 | 123805 ST_LineString | 25 | 113805 ST_LineString | 26 | 105329 ST_LineString | 27 |96809 ST_LineString | 28 |90105 ST_LineString | 29 |83137 ST_LineString | 30 |77846 ST_LineString | 31 |72963 ST_LineString | 32 |67830 ST_LineString | 33 |63849 ST_LineString | 34 |60241 ST_LineString | 35 |56312 ST_LineString | 36 |52805 ST_LineString | 37 |49919 ST_LineString | 38 |47402 ST_LineString | 39 |44860 ST_LineString | 40 |41987 ST_LineString | 41 |40055 ST_LineString | 42 |38173 ST_LineString | 43 |36649 ST_LineString | 44 |34464 ST_LineString | 45 |32637 ST_LineString | 46 |31695 ST_LineString | 47 |29851 ST_LineString | 48 |28546 ST_LineString | 49 |27419 ST_LineString | 50 |26784 ST_LineString | 1993 |2 ST_LineString | 1995 |1 ST_LineString | 1997 |6 ST_LineString | 1998 |5 ST_LineString | 1999 |3 ST_LineString | 2000 |9 ST_Point| | 20648939 ST_MultiPolygon | | 6188 ST_Polygon | | 8054680 One thing you could try is indexing each geometry type in turn and watch the memory usage. If indexing starts sequentially from the beginning to the end, we reach ST_Point around row 22.000.000. This is from the count of geometry operations exactly where the slowdown begins. Does this track us to the leak? Thanks regards Frans 2010/3/26 Paul Ramsey pram...@cleverelephant.ca: Could you - put in your version information - tell me what kind of spatial objects you have? polygons of 100 vertices? lines of two vertices? etc. That will help me pick similar data for the memory testing. -- 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] Large index operation crashes postgres
Paul, do you know, if Postgis 1.3.6 will survive, if I install Geos 3.2 over it? Currently, there's Geos 3.1.1 installed. I remember, I had to downgrade Postgis/Geos to be compatible with Postgres 8.3.9. As I herited the database from a 8.3.9 dump, I needed to fit my installation before restoring the dump. Thanks Frans 2010/3/26 Paul Ramsey pram...@cleverelephant.ca: Occams razor says it's PostGIS. However, I'm concerned about how old the code being run is. In particular, the library underneath PostGIS, GEOS, had a *lot* of memory work done on it over the last year. I'd like to see if things improve if you upgrade to GEOS 3.2. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
