Re: [GENERAL] Problem with pl/python procedure connecting to the internet
In cases like this I normally restart the progresql under strace/truss etc and then wade through the output, it will normally tell me which process was invoked. On 23/08/15 18:49, Tom Lane wrote: Igor Sosa Mayor joseleopoldo1...@gmail.com writes: Adrian Klaver adrian.kla...@aklaver.com writes: Or more to the point how where they built? just installed with postgresql which comes with archlinux. I did not built anything. But, really: I dont think the problem is plpython2u o 3: 1. I have in my system both and both have the same libraries (in this case, geopy); 2. other procedures with plpython (2 or 3) which DO NOT CONNECT to the internet work perfectly. Well, that hardly proves that Python code that *does* connect to the net would work. The possibility that you're using a different Python version inside Postgres and it's broken for network access is one you should take very seriously. However, what this smells like to me is a permissions problem. I think you were way too quick to dismiss the idea that SELinux (or something just like it) is restricting outbound internet connections from Postgres. It's standard for SELinux to be configured so that network-accessible daemons like Postgres are locked down harder than the very same code would be treated when being invoked from the command line --- and network access would be one of the prime candidates to be disabled by default. Have you poked around under /var/log/ to see if the kernel logs anything when the connection attempt doesn't work? For that matter, have you checked the postmaster log to see what Postgres logs about it? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] returning only part of a rule set
I have a psql function that make a general sql query, returns a set of results Q. Is there anyway that I can limit the size of the result sets ? Dave. -- 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] returning only part of a rule set
On 25/11/14 21:36, Adrian Klaver wrote: On 11/25/2014 01:27 PM, Dave Potts wrote: I have a psql function that make a general sql query, returns a set of results Q. Is there anyway that I can limit the size of the result sets ? Put a LIMIT on the query. If that is not what you want then we will need to see the code and/or get a more detailed explanation of where you want the limiting to occur. I tried that Limit applies to the total number of results from a query, what I want to do is limit the total number of returns per result set, For example if my query returns a set of items such as id, foo,bar Normally the result set is 3 set of records one with 3 entries, one with 2 entires and one with entry, the results would be. 1 xx,yy 2 xx,yy 3 xx,yy 1,dd,zz 2,dd,zz 1, ee,ff If I am only interested in get 2 entries per result set, I would expect to see 1 xx,yy 2 xx,yy 1,dd,zz 2,dd,zz 1, ee,ff Using LIMIT only gives 1 xx,yy 2 xx,yy Sorry for not explaining it very, I want to limit the size of an inviduail set of records which is part of set of records. Dave. -- 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] returning only part of a rule set
On 25/11/14 22:24, David G Johnston wrote: Thanks List, I think this is the right way to go. Adrian Klaver-4 wrote If I am only interested in get 2 entries per result set, I would expect to see Actually =2. How do you determine which rows to keep, by id or something else? 1 xx,yy 2 xx,yy 1,dd,zz 2,dd,zz 1, ee,ff Using LIMIT only gives 1 xx,yy 2 xx,yy Sorry for not explaining it very, I want to limit the size of an inviduail set of records which is part of set of records. It would help if we could see the actual query you are using to get the result sets, suitably anonymized if needed. The general answer is that you use a window clause and a row_number() function over an appropriate partiton. Put that in a subquery then in the outer query add a where clause for row_number = 2. David J. -- View this message in context: http://postgresql.nabble.com/returning-only-part-of-a-rule-set-tp5828217p5828226.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Partitioning V schema
Hi List I am looking for some general advice about the best was of splitting a large data table,I have 2 different choices, partitioning or different schemas. The data table refers to the number of houses that can be include in a city, as such there are large number of records. I am wondering if decided to partition the table if the update speed/access might be faster that just declaring a different schema per city. Under the partition the data table would appear to be smaller, so I should get an increase in speed, but the database still have to do some sort of indexing. If I used different schemas, it resolves data protection issues, but doing a backup might become a nightmare In general which is the fast access method? regards Dave. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] debugging postgres server memory checker
hi List, As a result of writing a C 'postgres' function, I have a memory corruption problem. Something is either being free twice or I am accessing beyond the data structure and corrupting the malloc/free/sbrk memory pool. My attempts to memory support in glibc, that been stopped because something seems to clear the envron before the postgres/postmaster program is started(well ps does not report an envronment setting for this process) Memory checking programs such as valgrind want direct control over the postgres/postmaster process, ie they must fork the process and not the postgres startup script from /etc/rc2.d Q. How do inoke the postgres startup in such a way that my current terminal becomes the controling terminal for the postgres/postmaster process? I do not want to run the postgres/postmaster program as a background deamin style process. Thanks in advance Dave. -- -- 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 to debugging a an external C function(IMMUTABLE STRICT )
Hi I have written an external C function to be called by postgres called using the LANGUAGE 'C' IMMUNTABLE STRICT interface Most of the time when call it, I get the expected results. Some times I get random rubbish in the result set. Postgres always gets the type of the arguments correct, ie it knowns the column x is a integer, column y is a float8 I called elog(NOTICE from within my code, the results always look correct, so I am assuming that I am sometimes returning a random pointer, or have got the arguments to BlessTupleDesc, MemoryContextSwitchTo wrong! If there any debug support in Postgres to catch this type of thing? Are there any useful functions have can be compiled in when building postgres??? I do not have access to things like Purify Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[FIXED] Re: [GENERAL] problems connecting to php via pg_connect and PGCLUSTER
Thanks guys its sorted. Both your suggestions worked :-) Scott Marlowe wrote: On Mon, Feb 6, 2012 at 4:03 PM, Dave Potts dave.po...@pinan.co.uk wrote: I have two versions of postgres installed, 8.4 and 9.1 installed on the same machine To connect to my 9.1 database, I defined the envromental variable PGCLUSTER=9.1/main and use psql to connect via php pg_connect I have try saying define(PG_OPTIONS , --cluster=9.1/main); and using the phrase $con = pg_connect(dbname=.PG_DB. host=.PG_HOST. user=.PG_USER. options=.PG_OPTIONS); Get rid of the options and just put in the port of the cluster you want to connect to. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problems connecting to php via pg_connect and PGCLUSTER
I have two versions of postgres installed, 8.4 and 9.1 installed on the same machine To connect to my 9.1 database, I defined the envromental variable PGCLUSTER=9.1/main and use psql to connect via php pg_connect I have try saying define(PG_OPTIONS , --cluster=9.1/main); and using the phrase $con = pg_connect(dbname=.PG_DB. host=.PG_HOST. user=.PG_USER. options=.PG_OPTIONS); When I attempt to connect to postgres, I get the error [Mon Feb 06 22:37:40 2012] [error] [client 127.0.0.1] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: unrecognised configuration parameter cluster in /var/www/re/php/pgrouting.php on line 33, referer: http://127.0.0.1/re/routing-final.html Any suggestions as to the correct way of connecting to postgres? Dave. -- -- 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] problems connecting to php via pg_connect and PGCLUSTER
Hi Chris Thanks for the suggestion, I tried a making the changes are you suggest, I still getting an error from Postgres ie define(PG_OPTIONS , --cluster=9.1/main); define(PG_DB , tripe); define(PG_HOST, localhost); define(PG_USER, dp42); define(PG_PORT, 5432); define(TABLE, route_table); $con = pg_connect(dbname='.PG_DB.' host='.PG_HOST.' user='.PG_USER.' options='.PG_OPTIONS.'); Error message 04:41 2012] [error] [client 127.0.0.1] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: unrecognised configuration parameter cluster in /var/www/re/php/pgrouting.php on line 34, referer: http://127.0.0.1/re/routing-final.html The problem is with the database server it has issues with the string --cluster=9.1/main The system is happy enough if I set PGCLUSTER as follows export PGCLUSTER=9.1/main psql -d tripe I am justing trying to the same with pg_connect wrote: On 07/02/12 10:03, Dave Potts wrote: I have two versions of postgres installed, 8.4 and 9.1 installed on the same machine To connect to my 9.1 database, I defined the envromental variable PGCLUSTER=9.1/main and use psql to connect via php pg_connect I have try saying define(PG_OPTIONS , --cluster=9.1/main); and using the phrase $con = pg_connect(dbname=.PG_DB. host=.PG_HOST. user=.PG_USER. options=.PG_OPTIONS); When I attempt to connect to postgres, I get the error [Mon Feb 06 22:37:40 2012] [error] [client 127.0.0.1] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: unrecognised configuration parameter cluster in /var/www/re/php/pgrouting.php on line 33, referer: http://127.0.0.1/re/routing-final.html Any suggestions as to the correct way of connecting to postgres? Try putting quotes around the options, eg: pg_connect(host=localhost options='.PG_OPTIONS.'); Alternatively, set the port and the path to the socket directory (not the actual file), eg: pg_connect('dbname=xxx host=/tmp user=xxx port=5433'); If the socket file is in /tmp/ -- Postgresql php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] default database selector
I am running Ubuntu 11.04, I have Postgres 8.4 and 9.1 installed. My default when I say psql it connects to postgres 8.4 ie I set export PGCLUSTER=9.1/main it connects to 9.1 Q. How can I connect to 9.1 by default without having to set PGCLUSTER? I looked in /etc/postgresql-common, there did not seem to be anything obivious in there for which version to use. Dave. -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] debugging the server[ module causes server cash]
Hi I have just inherited a private C module that lives in the context of the server with little or no documentation. It crashes taking the database down, is there any advice on how to debug this type of beast ? This is not a problem with the postgres server but user module. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Loading long lat values converting degrees to decimal values
Hi List I am trying to load some data points stored as lat/long values Q. Do I convert the degree values (0-59.59) to a decimal value in range 0-100, or is setting the right srs value good enough? Dave. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Loging of postgres requests
I am using a 3rd front end to generate postgres requests , its reportng an error with the database. Is there anyway of loging which sql requests the application is actual sending to postgres. I need to known if the error is being created by the application generating invalid SQL or if there is a problem with the desgin of the database tables. Dave. begin:vcard fn:David Potts n:Potts;David x-mozilla-html:FALSE version:2.1 end:vcard -- 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] Loging of postgres requests
Thanks everbody I have isolated the issue Davel, Terry Fielder wrote: You can turn up the verbosity of postgres logger to log all SQL statements. Look in postgresql.conf In particular, you can set postgres to log statements that take over x milliseconds to execute. If you set log_min_duration_statement to 0, then it will log ALL statements, which could also give you what you want if you want to see all SQL statements. Terry Terry Fielder [EMAIL PROTECTED] Associate Director Software Development and Deployment Great Gulf Homes / Ashton Woods Homes Fax: (416) 441-9085 Dave Potts wrote: I am using a 3rd front end to generate postgres requests , its reportng an error with the database. Is there anyway of loging which sql requests the application is actual sending to postgres. I need to known if the error is being created by the application generating invalid SQL or if there is a problem with the desgin of the database tables. Dave. begin:vcard fn:David Potts n:Potts;David x-mozilla-html:FALSE version:2.1 end:vcard -- 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] pain of postgres upgrade with extensions
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 - dump version N database - create empty version N+1 database - install N+1's version of each needed contrib module into new database - restore dump, ignoring object already exists errors There is a TODO to figure out some cleaner way of handling this sort of thing ... I think I smell a GSOC project I think there is a slight misunderstanding here, I was refering to extensions items such as postgis, plr, pgperl, etc. These have a slight different foot print to the projects in the contrib directory. Dave - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200803121533 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkfYMBMACgkQvJuQZxSWSsjmmwCg1JvB0G2py5jSbJdSZpWR8YyV D4YAoLg2ZinEEGoNEU7S2mcL3bqhmNIh =7pvA -END PGP SIGNATURE- begin:vcard fn:David Potts n:Potts;David x-mozilla-html:FALSE version:2.1 end:vcard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres schema printer
Is there such a thing as an opensource schema printer for postgres ? Dave. begin:vcard fn:David Potts n:Potts;David x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster