[GENERAL] simultaneously reducing both memory usage and runtime for a query
Hi everyone, I've been trying to reduce both memory usage and runtime for a query. An issue I have encountered is that I can drastically reduce both runtime and memory usage by splitting up the query into two pieces and gluing them together outside PostgreSQL. However, I'm unable to get similar results inside PostgreSQL. I have tried different variations of the same query, but either the memory or the runtime blows up. I wonder if I'm missing something. In any case, feedback would be helpful. Details of my attempts at optimization are at http://bulldog.duhs.duke.edu/~faheem/snppy/opt.pdf See particularly Section 1 - Background and Discussion. If you want a text version, see http://bulldog.duhs.duke.edu/~faheem/snppy/opt.tex For background see http://bulldog.duhs.duke.edu/~faheem/snppy/diag.pdf (text version http://bulldog.duhs.duke.edu/~faheem/snppy/diag.tex) and http://bulldog.duhs.duke.edu/~faheem/snppy/snppy.pdf Please CC any replies to me at the above email address. Thanks. Regards, Faheem. -- 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 : > 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
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 : > > 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] 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
[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] 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 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. > > >
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
