[GENERAL] Experiences with pl/Java
Hi, was wondering if there is anyone wanted to share some experiences gained and some knowledge on pl/Java. Have looked into it for a couple of days now and am getting the impression it is not something ready to use in production environment. Also have trouble sending to the developer mailing list (the last email on the mail archive of that list is almost three weeks old) which raises questions on how active this project is. Have worked with PostgreSQL and pg/SQL before and also used Apache Derby in the past. On Apache Derby I have implemented some stored procedures using Java code and my interst now was on seeing how much of this could be re-used this pl/Java so PostgreSQL could potentially become a second data base backend my application would run on. But trying to port my java procedures from Derby to PostgreSQL had a bad start and for me pl/Java and the support around it so far suggest not to pursue this any further. Why?. Porting the simlest procedure was possible, but trying procedures with out parameters was not. Has anyone made the same experience with pl/Java or is it just my lack of understanding (in conjunction with a lack of documentation on pl/Java). Thanks a lot in advance Thomas -- 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] EDB installer should check for valid %COMSPEC%
Hi On Mon, Nov 19, 2012 at 4:29 AM, Craig Ringer cr...@2ndquadrant.com wrote: Hi folks Some recent discussion on Stack Overflow has revealed another exciting way for Windows computers to be subtly broken. For as yet unknown reasons - probably related to security/virus scanner software, since everything else seems to be - some Windows machines have an invalid COMSPEC environment variable. Two variants have been sighted in the wild: %SystemRoot%\system32\cmd.exe; (note the trailing semicolon), and: C:\Windows\System32 Both will produce the delightfully helpful initdb failure: initdb: could not execute command C:/Program Files/PostgreSQL/9.2/bin/postgres.exe --boot -x1 -F : No error while running: cscript //NoLogo C:\Program Files\PostgreSQL\9.2/installer/server/initcluster.vbs NT AUTHORITY\NetworkService postgres C:\Program Files\PostgreSQL\9.2 C:\Program Files\PostgreSQL\9.2\data 5432 DEFAULT which will exit with: Script exit code: 1 In the one I was looking into, fixing COMSPEC in the System control panel's Environment Variables page by removing the trailing semicolon corrected the issue. It can be verified as correct by opening a new command prompt after you've changed the variable (not just re-using an existing already-open one) and running: %COMSPEC% /C echo test ok which should print: test ok not something like: 'C:\Windows\System32\cmd.exe;' is not recognized as an internal or external command, operable program or batch file. Since I can find several reports of this spanning over a couple of years, I'd love to see a test for this integrated into the EDB installer. Just verify that popen() actually works before running the initdb script, and if it doesn't, check %COMSPEC% to see if it really points to cmd.exe . Interesting - thanks for the info Craig. Sandeep; can you please look into adding such a check to the installer. We already test the VBscript interpreter, so this should go in the same place. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Fuzzystrmatch contrib module on RHEL63
Hi, On Sun, 2012-11-18 at 21:13 -0500, Ike Nnabugwu wrote: I am building an rpm package fuzzystrmatch to be installed on RHEL63 but I do not know where to get the modules for it.I will appreciate any pointers that will assist with this task. All contrib modules are already available in -contrib subpackage, including fuzzystrmatch, which is available for RHEL 6.3. If you want a package that contains only that module for some reason, take a look at this spec file: http://svn.pgrpms.org/browser/rpm/redhat/9.2/postgresql/EL-6/postgresql-9.2.spec It will give you some idea about building a module-only package. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[GENERAL] How to upgrade postgres 8.4 - 9.1 contrib?
Hello, We a little while ago upgraded our databases from 8.4 to 9.1. We upgraded using pg_upgrade but didn't do anything special for extensions (other than that the server had the contrib rpm installed). Everything works just fine but recently we noticed that a lot of the functions from hstore are no longer available. Interestingly enough the type is still there as are the columns of that type and the basic operators also still work: proddb= select hstore 'a = b' - 'a'; ?column? -- b (1 row) However: proddb= select hstore(text 'a', text 'b'); ERROR: function hstore(text, text) does not exist LINE 1: select hstore(text 'a', text 'b'); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. More reading of the docs revealed this: E.7.2.7. Contrib All contrib modules are now installed with CREATE EXTENSION rather than by manually invoking their SQL scripts (Dimitri Fontaine, Tom Lane) To update an existing database containing the 9.0 version of a contrib module, use CREATE EXTENSION ... FROM unpackaged to wrap the existing contrib module's objects into an extension. When updating from a pre-9.0 version, drop the contrib module's objects using its old uninstall script, then use CREATE EXTENSION. It is unclear what to do. Clearly 8.4 9.0 but if we were to run the old uninstall script this would DROP all columns of hstore type with disastrous consequences for us. I would argue that the above comment should warn about that! What is the official guide line? Thanks, Bene -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] remote connection refused
I'd like to have a checklist upon what to do, what to investigate, when an external connection is refused. if i connect by : psql -h localhost -p 5432 -U yt mydb it works as usual however even locally but using the LAN address : psql -h 192.168.0.20 -p 5432 -U yt mydb i got a : psql: could not connect to server: Connexion refusée Is the server running on host 192.168.0.20 and accepting TCP/IP connections on port 5432? obviously I've verified by ping the computer is reachable. something i suspect : $ lal /var/run/postgresql total 12 drwxrwsr-x 2 postgres postgres 140 nov. 19 09:53 . drwxr-xr-x 26 root root 900 nov. 19 09:45 .. -rw--- 1 postgres postgres 6 nov. 19 09:53 9.1-main.pid -rw-r--r-- 1 postgres postgres 4 nov. 19 07:10 pgbouncer.pid srwxrwxrwx 1 postgres postgres 0 nov. 19 09:53 .s.PGSQL.5432 -rw--- 1 postgres postgres 71 nov. 19 09:53 .s.PGSQL.5432.lock srwxrwxrwx 1 postgres postgres 0 nov. 19 07:10 .s.PGSQL.6432 this the locked file : .s.PGSQL.5432.lock is that normal or not ? something to say, i had a brutal shutdown due to over heating of my laptop recently. on the pg_hba.conf i do have a bunch of IPV6 addresses allowing connection then i don't think the prob comes from here, but nevermind... in such a situation what is your checklist in order to find what I've missed ? -- Yvon
Re: [GENERAL] remote connection refused
Usually, after what you've already confirmed, it's likely to be one of the following: - check listen_address is set correctly in postgresql.conf. (try listen_address = * and restart postgres) - check port 5432 is open on iptables. (service iptables status, netstat -a | grep 5432) Cheers From: Yvon Thoraval yvon.thora...@gmail.com To: pgsql-general@postgresql.org pgsql-general@postgresql.org, Date: 19/11/2012 12:14 Subject:[GENERAL] remote connection refused Sent by:pgsql-general-ow...@postgresql.org I'd like to have a checklist upon what to do, what to investigate, when an external connection is refused. if i connect by : psql -h localhost -p 5432 -U yt mydb it works as usual however even locally but using the LAN address : psql -h 192.168.0.20 -p 5432 -U yt mydb i got a : psql: could not connect to server: Connexion refusée Is the server running on host 192.168.0.20 and accepting TCP/IP connections on port 5432? obviously I've verified by ping the computer is reachable. something i suspect : $ lal /var/run/postgresql total 12 drwxrwsr-x 2 postgres postgres 140 nov. 19 09:53 . drwxr-xr-x 26 root root 900 nov. 19 09:45 .. -rw--- 1 postgres postgres 6 nov. 19 09:53 9.1-main.pid -rw-r--r-- 1 postgres postgres 4 nov. 19 07:10 pgbouncer.pid srwxrwxrwx 1 postgres postgres 0 nov. 19 09:53 .s.PGSQL.5432 -rw--- 1 postgres postgres 71 nov. 19 09:53 .s.PGSQL.5432.lock srwxrwxrwx 1 postgres postgres 0 nov. 19 07:10 .s.PGSQL.6432 this the locked file : .s.PGSQL.5432.lock is that normal or not ? something to say, i had a brutal shutdown due to over heating of my laptop recently. on the pg_hba.conf i do have a bunch of IPV6 addresses allowing connection then i don't think the prob comes from here, but nevermind... in such a situation what is your checklist in order to find what I've missed ? -- Yvon = Romax Technology Limited Rutherford House Nottingham Science Technology Park Nottingham, NG7 2PZ England Telephone numbers: +44 (0)115 951 88 00 (main) For other office locations see: http://www.romaxtech.com/Contact = === E-mail: i...@romaxtech.com Website: www.romaxtech.com = Confidentiality Statement This transmission is for the addressee only and contains information that is confidential and privileged. Unless you are the named addressee, or authorised to receive it on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation. =
Re: [GENERAL] remote connection refused
Hi, On Mon, 2012-11-19 at 13:08 +0100, Yvon Thoraval wrote: in such a situation what is your checklist in order to find what I've missed ? What is listen_addresses in postgresql.conf ? It should be * or so. Also, I would make sure that there is not a firewall on the machine itself. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[GENERAL] Maintaining state across function calls
I want to process all the records in a table through a C-language (well,
C++) function (i.e. one function call per row of the table) in such a way
that the function hangs onto its internal state across calls. Something
like
SELECT my_function(a, b, c) FROM my_table ORDER BY d;
The value returned in the last row of the table would be the result I'm
looking for. (This could be neatened up by using a custom aggregate and
putting my calculation in the sfunc but that's a minor detail).
The question is: what's the best practice way of letting a
C/C++-language function hang onto internal state across calls? So far I'm
thinking something along the lines of:
Datum my_function(int a, int b, int c, int reset)
{
static my_data *p = NULL;
if (reset) //(re)initialise internal state
{
delete p;
p = NULL;
}
else
{
if (!p)
{
p = new my_data;
}
//make use of internal state to do calculations or whatever
}
}
The user would be responsible for calling my_function with reset set to
true to wipe previous internal state before using the function in a new
query; doing this also frees the memory associated with the function.
This system is of course prone to leakage if the user forgets to wipe the
internal state after use, but it will only leak sizeof(my_data) per
connection, and the OS will garbage-collect all that when the connection
dies anyway.
Alternatively, use this in a custom aggregate and make the ffunc do the
garbage collection, which should prevents leakage altogether.
Is this a reasonable thing to do? What are the risks? Is there a more
best-practice way to achieve the same result?
Many thanks,
Matt
--
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] remote connection refused
YES fine, thanks a lot ! it was left to default 'locahost'... 2012/11/19 Devrim GÜNDÜZ dev...@gunduz.org Hi, On Mon, 2012-11-19 at 13:08 +0100, Yvon Thoraval wrote: in such a situation what is your checklist in order to find what I've missed ? What is listen_addresses in postgresql.conf ? It should be * or so. Also, I would make sure that there is not a firewall on the machine itself. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz -- Yvon
Re: [GENERAL] Maintaining state across function calls
On 11/19/2012 08:41 PM, m...@byrney.com wrote: I want to process all the records in a table through a C-language (well, C++) function (i.e. one function call per row of the table) in such a way that the function hangs onto its internal state across calls. Something like SELECT my_function(a, b, c) FROM my_table ORDER BY d; The value returned in the last row of the table would be the result I'm looking for. (This could be neatened up by using a custom aggregate and putting my calculation in the sfunc but that's a minor detail). [snip] Alternatively, use this in a custom aggregate and make the ffunc do the garbage collection, which should prevents leakage altogether. You don't generally need to do this cleanup yourself. Use appropriate palloc memory contexts and it'll be done for you when the memory context is destroyed. I would want to implement this as an aggregate using the standard aggregate / window function machinery. Have a look at how the existing aggregates like string_agg are implemented in the Pg source code. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Maintaining state across function calls
On 11/19/2012 08:41 PM, m...@byrney.com wrote: I want to process all the records in a table through a C-language (well, C++) function (i.e. one function call per row of the table) in such a way that the function hangs onto its internal state across calls. Something like SELECT my_function(a, b, c) FROM my_table ORDER BY d; The value returned in the last row of the table would be the result I'm looking for. (This could be neatened up by using a custom aggregate and putting my calculation in the sfunc but that's a minor detail). [snip] Alternatively, use this in a custom aggregate and make the ffunc do the garbage collection, which should prevents leakage altogether. You don't generally need to do this cleanup yourself. Use appropriate palloc memory contexts and it'll be done for you when the memory context is destroyed. I would want to implement this as an aggregate using the standard aggregate / window function machinery. Have a look at how the existing aggregates like string_agg are implemented in the Pg source code. Thanks for your reply. A follow-up question: to use the palloc/pfree functions with a C++ STL container, do I simply give the container an allocator which uses palloc and pfree instead of the default allocator, which uses new and delete? Matt -- 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] Experiences with pl/Java
i used it for a project about 6 months ago. it took a little bit of effort to get things going, and the documentation could use some work, but it was reliable once i got oriented. the developer does read the mailing list, and responds to requests for help. i think you could use it in production so long as you have good processes in place and can dedicate some developer time to learning supporting it. but it's definitely not plug and play right now. richard From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Thomas Hill [thomas.k.h...@t-online.de] Sent: Monday, November 19, 2012 3:02 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Experiences with pl/Java Hi, was wondering if there is anyone wanted to share some experiences gained and some knowledge on pl/Java. Have looked into it for a couple of days now and am getting the impression it is not something ready to use in production environment. Also have trouble sending to the developer mailing list (the last email on the mail archive of that list is almost three weeks old) which raises questions on how active this project is. Have worked with PostgreSQL and pg/SQL before and also used Apache Derby in the past. On Apache Derby I have implemented some stored procedures using Java code and my interst now was on seeing how much of this could be re-used this pl/Java so PostgreSQL could potentially become a second data base backend my application would run on. But trying to port my java procedures from Derby to PostgreSQL had a bad start and for me pl/Java and the support around it so far suggest not to pursue this any further. Why?. Porting the simlest procedure was possible, but trying procedures with out parameters was not. Has anyone made the same experience with pl/Java or is it just my lack of understanding (in conjunction with a lack of documentation on pl/Java). Thanks a lot in advance Thomas -- 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
Re: [GENERAL] How to upgrade postgres 8.4 - 9.1 contrib?
Benedikt Grundmann bgrundm...@janestreet.com writes: What is the official guide line? You could try (1) run the 9.0 version of the hstore install script and then (2) do the CREATE EXTENSION FROM UNPACKAGED bit. I'd strongly recommend testing this procedure in a scratch copy of your installation first, though. In a quick look through the diffs between the 8.4 and 9.0 hstore install scripts, it looks like this should mostly work, except that 9.0 has some new operators added to the GIST and GIN index opclasses. If you care about whether those are indexable, you would need to drop those opclasses (and any dependent indexes) before running the 9.0 install script, and then recreate the indexes after. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to upgrade postgres 8.4 - 9.1 contrib?
On Mon, Nov 19, 2012 at 3:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Benedikt Grundmann bgrundm...@janestreet.com writes: What is the official guide line? You could try (1) run the 9.0 version of the hstore install script and then (2) do the CREATE EXTENSION FROM UNPACKAGED bit. I'd strongly recommend testing this procedure in a scratch copy of your installation first, though. Thanks Tom. Due to complicated-not-relevant-reasons it might take a while for us to do so but if we do try this out I'll let the list know if it worked. Cheers, Bene -- 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] Experiences with pl/Java
On 19 November 2012 08:02, Thomas Hill thomas.k.h...@t-online.de wrote: was wondering if there is anyone wanted to share some experiences gained and some knowledge on pl/Java. Have looked into it for a couple of days now and am getting the impression it is not something ready to use in production environment. Also have trouble sending to the developer mailing list (the last email on the mail archive of that list is almost three weeks old) which raises questions on how active this project is. I'd caution you against using pl/java in production. I came up against a problem with it that I could not find a satisfactory solution for. Essentially, each Postgres backend (connection process) must start its own JVM, and does so using the JNI. If native memory runs out (I realise that this may not actually be due to malloc() returning NULL, but the effect is about the same), this failure is handled rather poorly. It results in an OutOfMemoryError due to native memory exhaustion. This results in a segfault of the Postgres backend, originating from within libjvm.so. There is a workaround - which is to set the maximum JVM heap size to a sufficiently low value - but in general the need to do so left me with a very low opinion of pl/java as a project. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- 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] Maintaining state across function calls
m...@byrney.com writes: The question is: what's the best practice way of letting a C/C++-language function hang onto internal state across calls? A static variable for that is a really horrid idea. Instead use fcinfo-flinfo-fn_extra to point to some workspace palloc'd in the appropriate context. If you grep the PG sources for fn_extra you'll find plenty of examples. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Experiences with pl/Java
Hello Peter, glad to meet you again after http://2012.pgconf.eu ! On ÎÎµÏ 19 Îοε 2012 16:26:56 you wrote: On 19 November 2012 08:02, Thomas Hill thomas.k.h...@t-online.de wrote: was wondering if there is anyone wanted to share some experiences gained and some knowledge on pl/Java. Have looked into it for a couple of days now and am getting the impression it is not something ready to use in production environment. Also have trouble sending to the developer mailing list (the last email on the mail archive of that list is almost three weeks old) which raises questions on how active this project is. I'd caution you against using pl/java in production. I came up against a problem with it that I could not find a satisfactory solution for. Essentially, each Postgres backend (connection process) must start its own JVM, and does so using the JNI. If native memory runs out (I realise that this may not actually be due to malloc() returning NULL, but the effect is about the same), this failure is handled rather poorly. It results in an OutOfMemoryError due to native memory exhaustion. This is the situation in J2EE app servers as well, once OutOfMemoryError is thrown there is not much the admin can do besides killing/restarting the app server. This results in a segfault of the Postgres backend, originating from within libjvm.so. There is a workaround - which is to set the maximum JVM heap size to a sufficiently low value - Wouldn't that just make the problem manifest itself earlier? but in general the need to do so left me with a very low opinion of pl/java as a project. However, in most cases this error denotes a system (jvm itself) memory leak, an insufficient garbage collector operation or a poorly designed application. In the java 7, i heard good stories about the new G1 garbage collector. We have never tried pl/java, despite being a postgresql/java house, and this is definitely something we are looking forward to explore, but i agree with Thomas that the traffic on the list is low which points to what Peter is suggesting about being scared to use this in production environments. That would be very cool if it was much more active and stable. - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt -- 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] Experiences with pl/Java
I don't know much about PostgreSQL with plJava, but I can give a few tips about memory stability when using Java (see inline): Em 19/11/2012 14:55, Achilleas Mantzios escreveu: Hello Peter, glad to meet you again after http://2012.pgconf.eu ! On Δευ 19 �οε 2012 16:26:56 you wrote: On 19 November 2012 08:02, Thomas Hill thomas.k.h...@t-online.de wrote: was wondering if there is anyone wanted to share some experiences gained and some knowledge on pl/Java. Have looked into it for a couple of days now and am getting the impression it is not something ready to use in production environment. Also have trouble sending to the developer mailing list (the last email on the mail archive of that list is almost three weeks old) which raises questions on how active this project is. I'd caution you against using pl/java in production. I came up against a problem with it that I could not find a satisfactory solution for. Essentially, each Postgres backend (connection process) must start its own JVM, and does so using the JNI. If native memory runs out (I realise that this may not actually be due to malloc() returning NULL, but the effect is about the same), this failure is handled rather poorly. It results in an OutOfMemoryError due to native memory exhaustion. This is the situation in J2EE app servers as well, once OutOfMemoryError is thrown there is not much the admin can do besides killing/restarting the app server. If you uses JDK 6, would worth make a test using JRockit - it's memory management is far more stable than default Oracle/Sun JVM. I've servers that run for months without restart, even reloading classes to accomodate code changes. The biggest problem with JVM is class reloading, since nursery is not garbage collected (or, at least, is not effective as it should be). JRockit uses a different approach, so it's not affected by this problem. But eventually even JRockit will run out of memory if you don't use the right approach. In the past, one choice was using static classes whenever possible. This is not true (neither recommended) anymore, and in most of the cases having thousands of cicles creation-use-release makes GC more effective (and your VM more stable). This results in a segfault of the Postgres backend, originating from within libjvm.so. There is a workaround - which is to set the maximum JVM heap size to a sufficiently low value - Wouldn't that just make the problem manifest itself earlier? but in general the need to do so left me with a very low opinion of pl/java as a project. However, in most cases this error denotes a system (jvm itself) memory leak, an insufficient garbage collector operation or a poorly designed application. In the java 7, i heard good stories about the new G1 garbage collector. I've been using JDK 7 with G1 GC for hosting NetBeans and Tomcat, and once you don't have lots of class reload, you get a stable environment. JDK 7 still suffer of nursery (or whatever the new name they put in) issues, and classes are not properly disposed. I don't even think this is a Java issue - most Object Oriented Virtual Machines that relay on garbage collection suffer of the same issue (references are kept and then objects are never released, so classes cannot be disposed). Regards, Edson Richter We have never tried pl/java, despite being a postgresql/java house, and this is definitely something we are looking forward to explore, but i agree with Thomas that the traffic on the list is low which points to what Peter is suggesting about being scared to use this in production environments. That would be very cool if it was much more active and stable. - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt -- 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] Experiences with pl/Java
Edson Richter [edsonrich...@hotmail.com] writes: I don't know much about PostgreSQL with plJava, but I can give a few tips about memory stability when using Java (see inline): ... In the past, one choice was using static classes whenever possible. This is not true (neither recommended) anymore, and in most of the cases having thousands of cicles creation-use-release makes GC more effective (and your VM more stable). PL/Java requires that the methods being directly called from PostgreSQL are static. while i don't disagree with the advice, PL/Java is limited in this respect. richard -- 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] Experiences with pl/Java
Em 19/11/2012 15:26, Welty, Richard escreveu: Edson Richter [edsonrich...@hotmail.com] writes: I don't know much about PostgreSQL with plJava, but I can give a few tips about memory stability when using Java (see inline): ... In the past, one choice was using static classes whenever possible. This is not true (neither recommended) anymore, and in most of the cases having thousands of cicles creation-use-release makes GC more effective (and your VM more stable). PL/Java requires that the methods being directly called from PostgreSQL are static. while i don't disagree with the advice, PL/Java is limited in this respect. :-) as I said, I know little about pl/Java... thanks for pointing this out. So, after calling the static method, probably inside the static methods, the programmer will create his/her logic. Writing this logic considering GC behavior would make your apps more stable. I would recommend to not use any Java library that keeps caches (ones like EclipseLink or Hibernate), unless you know exactly how to configure caches and soft/weak references - and their limitations. Regards, Edson richard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PITR manual doesn't reference pg_receivexlog?
http://www.postgresql.org/docs/current/static/continuous-archiving.htmldoesn't mention pg_receivexlog. But http://www.postgresql.org/docs/current/static/app-pgreceivexlog.htmlsays pg_receivexlog can be used for PITR backups. Should the PITR page reference pg_receivexlog?
Re: [GENERAL] Maintaining state across function calls
m...@byrney.com writes: The question is: what's the best practice way of letting a C/C++-language function hang onto internal state across calls? A static variable for that is a really horrid idea. Instead use fcinfo-flinfo-fn_extra to point to some workspace palloc'd in the appropriate context. If you grep the PG sources for fn_extra you'll find plenty of examples. regards, tom lane Thanks for this. Out of curiosity, why is a static a bad way to do this? -- 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] Experiences with pl/Java
Edson Richter [edsonrich...@hotmail.com] writes: Em 19/11/2012 15:26, Welty, Richard escreveu: PL/Java requires that the methods being directly called from PostgreSQL are static. while i don't disagree with the advice, PL/Java is limited in this respect. :-) as I said, I know little about pl/Java... thanks for pointing this out. So, after calling the static method, probably inside the static methods, the programmer will create his/her logic. Writing this logic considering GC behavior would make your apps more stable. I would recommend to not use any Java library that keeps caches (ones like EclipseLink or Hibernate), unless you know exactly how to configure caches and soft/weak references - and their limitations. i would probably recommend severely limiting what you try to do in PL/Java. in my particular project, the overall goal was incrementally doing push updates to an Apache Solr search engine. this entailed building xml descriptions of the update that was required, sending it with an http request (which required untrusted pl/java), and providing a recovery retry mechanism in case the http request failed, which limited itself to using the provided jdbc. i got it working and working well, but i put a lot of care into insuring that the garbage collecter never got stressed very hard and i didn't try to do more than was strictly necessary. i'd argue that if you find yourself wanting to use hibernate or eclipselink down in PL/Java, you should rethink your application design. richard -- 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] Maintaining state across function calls
m...@byrney.com writes: Thanks for this. Out of curiosity, why is a static a bad way to do this? Well, it wouldn't allow more than one instance of the function per query, and it wouldn't reset correctly after an error, and surely you agree that your proposal of making the user do a separate reset step is an unreliable and unpleasant-to-use kluge. 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] Split_part on a CR
Hi, I'm having a hard time figuring out how to use split_part with a carriage return (hex 0a) as the delimiter. Would someone please offer me a clue? Thanks! Jeff Ross -- 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] Experiences with pl/Java
Thanks to all for providing feedback and sharing opinions. Looks like
you have gone much further on it than I thought someone would have. So I
think I might spend some more time with it, but not plan to use it for
my application in a production environment.
My initial attempts were to try to re-use/port some simple procedures
which are running fine on Apache Derby, but then I got stuck quite early
in the process and could not find documentation showing how things needs
to be done and helping me to understand what I am doing wrong.
My first use case was calling a procedure which does not have any
parameter and this I actually got to run, i.e.
public static String CURRENT_CLIENTID() throws SQLException {
String vcFKClientID = 000;
return vcFKClientID;
}
CREATE OR REPLACE FUNCTION rte.CURRENT_CLIENTID()
RETURNS character varying AS
'onlyPostgreSQLPk.Functions.CURRENT_CLIENTID'
LANGUAGE java VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION rte.CURRENT_CLIENTID()
OWNER TO postgres;
= select rte.CURRENT_CLIENTID() returns '000'
My second use case was to create a procedure with an out parameter, i.e.
public static void SP_getNextID(int iNextVal[], String vcIDName)
throws SQLException {
Connection conn = getDefaultConnection();
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
String cSQL = SELECT \LastValue\ \n + FROM rte.\TBL_IDs\
\n
+ WHERE \IDName\ = ' + vcIDName + '\n;
ResultSet rs = stmt.executeQuery(cSQL);
while (rs.next()) {
iNextVal[0] = rs.getInt(1) + 1;
rs.updateInt(LastValue, iNextVal[0]);
rs.updateRow();
}
rs.close();
stmt.close();
return;
}
CREATE OR REPLACE FUNCTION rte.SP_getNextID(OUT iNextID integer, IN
vcIDName character varying)
RETURNS integer AS
'allDatabasesPk.Procedures.SP_getNextID(int[], java.lang.String)'
The static method seems to be found. But when calling the function using
pgadmin and issuing a 'Select rte.SP_getNextID('xy');' I am getting error
Too many parameters - expected 1
which I find confusing as I am only passing one parameter!?
I tried some variations I could think of, but without success.
Unfortunately I have not found an exmaple anywhere on the web showing
how this needs to be done.
Would be great if someone could have a look at above and spot what I am
doing wrong.
Thanks a lot in advance.
Kind regards
Thomas
Am 19.11.2012 20:19, schrieb Welty, Richard:
Edson Richter [edsonrich...@hotmail.com] writes:
Em 19/11/2012 15:26, Welty, Richard escreveu:
PL/Java requires that the methods being directly called from PostgreSQL are
static.
while i don't disagree with the advice, PL/Java is limited in this respect.
:-) as I said, I know little about pl/Java... thanks for pointing this out.
So, after calling the static method, probably inside the static methods,
the programmer will create his/her logic. Writing this logic considering
GC behavior would make your apps more stable.
I would recommend to not use any Java library that keeps caches (ones
like EclipseLink or Hibernate), unless you know exactly how to configure
caches and soft/weak references - and their limitations.
i would probably recommend severely limiting what you try to do in PL/Java.
in my particular project, the overall goal was incrementally doing push updates
to an Apache
Solr search engine. this entailed building xml descriptions of the update that
was required,
sending it with an http request (which required untrusted pl/java), and providing a
recovery retry
mechanism in case the http request failed, which limited itself to using the
provided jdbc.
i got it working and working well, but i put a lot of care into insuring that
the garbage collecter
never got stressed very hard and i didn't try to do more than was strictly
necessary. i'd argue
that if you find yourself wanting to use hibernate or eclipselink down in
PL/Java, you should
rethink your application design.
richard
--
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] Split_part on a CR
Jeff Ross jr...@wykids.org writes: Hi, I'm having a hard time figuring out how to use split_part with a carriage return (hex 0a) as the delimiter. Um, 0x0a is line feed last I checked. But you should be able to write the literal as E'\r' (if you wanted CR) or E'\n' (if you wanted LF). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Split_part on a CR
Jeff, I'm having a hard time figuring out how to use split_part with a carriage return (hex 0a) as the delimiter. Would someone please offer me a clue? 0x0a is actually a new line. This works for me: select split_part(text_column, E'\n', 1) from table; -- GC -- 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] Split_part on a CR
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Jeff Ross Sent: Monday, November 19, 2012 2:49 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Split_part on a CR Hi, I'm having a hard time figuring out how to use split_part with a carriage return (hex 0a) as the delimiter. Would someone please offer me a clue? Thanks! Jeff Ross SELECT split_part(E'a\nb\nc', E'\n', 2) -- == 'b' David J. -- 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] Split_part on a CR
On 11/19/12 12:58, Tom Lane wrote: Jeff Ross jr...@wykids.org writes: Hi, I'm having a hard time figuring out how to use split_part with a carriage return (hex 0a) as the delimiter. Um, 0x0a is line feed last I checked. But you should be able to write the literal as E'\r' (if you wanted CR) or E'\n' (if you wanted LF). regards, tom lane Arrrggghhh! I *knew* it was going to be something simple. Thanks again all, Jeff -- 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] Experiences with pl/Java
thanks - not sure how plJava works here and if the implementation is
identical to Apache Derby - what I can tell however is that defining the
types the way I did (integer on one side vs an array of integers on the
other side) is exactely how Apache Derby needs this as there out parms
always need to be defined as arrays in the method declaration and are
then automatically returned as integers - I will try to use integers on
both sides for plJava tomorrow, but if this would solve the problem this
would also mean that method declaration is different and depending on
data base backend implementation - which would make could re-use impossible
Am 19.11.2012 22:58, schrieb Welty, Richard:
i looked your stuff over. i never needed to make out params work, so i'm kind
of guessing here, but the first thing i'd look at is the type mismatch between
the args given in the pl/pgsql 'create or replace function' vs the args in the
java declaration, that is, the int on one side vs the array of ints on the
other. due to the lack of examples available that you previously pointed out, i
have no real idea how that should look in a working example.
richard
From: Thomas Hill [thomas.k.h...@t-online.de]
Sent: Monday, November 19, 2012 2:55 PM
To: Welty, Richard; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Experiences with pl/Java
Thanks to all for providing feedback and sharing opinions. Looks like
you have gone much further on it than I thought someone would have. So I
think I might spend some more time with it, but not plan to use it for
my application in a production environment.
My initial attempts were to try to re-use/port some simple procedures
which are running fine on Apache Derby, but then I got stuck quite early
in the process and could not find documentation showing how things needs
to be done and helping me to understand what I am doing wrong.
My first use case was calling a procedure which does not have any
parameter and this I actually got to run, i.e.
public static String CURRENT_CLIENTID() throws SQLException {
String vcFKClientID = 000;
return vcFKClientID;
}
CREATE OR REPLACE FUNCTION rte.CURRENT_CLIENTID()
RETURNS character varying AS
'onlyPostgreSQLPk.Functions.CURRENT_CLIENTID'
LANGUAGE java VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION rte.CURRENT_CLIENTID()
OWNER TO postgres;
= select rte.CURRENT_CLIENTID() returns '000'
My second use case was to create a procedure with an out parameter, i.e.
public static void SP_getNextID(int iNextVal[], String vcIDName)
throws SQLException {
Connection conn = getDefaultConnection();
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
String cSQL = SELECT \LastValue\ \n + FROM rte.\TBL_IDs\
\n
+ WHERE \IDName\ = ' + vcIDName + '\n;
ResultSet rs = stmt.executeQuery(cSQL);
while (rs.next()) {
iNextVal[0] = rs.getInt(1) + 1;
rs.updateInt(LastValue, iNextVal[0]);
rs.updateRow();
}
rs.close();
stmt.close();
return;
}
CREATE OR REPLACE FUNCTION rte.SP_getNextID(OUT iNextID integer, IN
vcIDName character varying)
RETURNS integer AS
'allDatabasesPk.Procedures.SP_getNextID(int[], java.lang.String)'
The static method seems to be found. But when calling the function using
pgadmin and issuing a 'Select rte.SP_getNextID('xy');' I am getting error
Too many parameters - expected 1
which I find confusing as I am only passing one parameter!?
I tried some variations I could think of, but without success.
Unfortunately I have not found an exmaple anywhere on the web showing
how this needs to be done.
Would be great if someone could have a look at above and spot what I am
doing wrong.
Thanks a lot in advance.
Kind regards
Thomas
Am 19.11.2012 20:19, schrieb Welty, Richard:
Edson Richter [edsonrich...@hotmail.com] writes:
Em 19/11/2012 15:26, Welty, Richard escreveu:
PL/Java requires that the methods being directly called from PostgreSQL are
static.
while i don't disagree with the advice, PL/Java is limited in this respect.
:-) as I said, I know little about pl/Java... thanks for pointing this out.
So, after calling the static method, probably inside the static methods,
the programmer will create his/her logic. Writing this logic considering
GC behavior would make your apps more stable.
I would recommend to not use any Java library that keeps caches (ones
like EclipseLink or Hibernate), unless you know exactly how to configure
caches and soft/weak references - and their limitations.
i would probably recommend severely limiting what you try to do in PL/Java.
in my particular project, the overall goal was incrementally doing push updates
to an Apache
Solr search engine. this entailed building xml descriptions of the
Re: [GENERAL] Experiences with pl/Java
On 11/19/12 2:56 PM, Thomas Hill wrote: thanks - not sure how plJava works here and if the implementation is identical to Apache Derby - what I can tell however is that defining the types the way I did (integer on one side vs an array of integers on the other side) is exactely how Apache Derby needs this as there out parms always need to be defined as arrays in the method declaration and are then automatically returned as integers - I will try to use integers on both sides for plJava tomorrow, but if this would solve the problem this would also mean that method declaration is different and depending on data base backend implementation - which would make could re-use impossible can't answer with respect to Derby, but we've got one internal application that requires pljava which was ported over from Oracle, and uses JDBC to implement trigger based replication of static data between oracle 10g and postgres servers, apparently the pljava code moved without any hassle. getting pljava working on a given platform is a pain. I had to build it for AIX which was definitely not fun, but once I got it working, the apps work just fine. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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 SYS CPU - need advise
OK, so far I settled on excluding connection caching on app side (Apache::DBI and prepare_cached) from equation and adding pgbouncer as a counter-measure. This seems to stabilize the situation - at least I'm not able to push server into high-sys-cpu stall the way how I used to do. I'm still interested in getting to the bottom of the issue vs alleviating it with connection poller, so I might setup a server on Centos 6.3 (kernel ver 2.6.32) and give it a try. Alternatively, if anyone have ideas on playing with sysctl parameters related to scheduler - please let me know. -- vlad -- 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] Maintaining state across function calls
On 11/19/2012 10:09 PM, m...@byrney.com wrote: Thanks for your reply. A follow-up question: to use the palloc/pfree functions with a C++ STL container, do I simply give the container an allocator which uses palloc and pfree instead of the default allocator, which uses new and delete? If at all possible, isolate your C++ code from the PostgreSQL aggregate implementation. Pass the C++ code pre-allocated buffers to work with if you can, and manage the allocations in the Pg C code. Turn your C++ code into library that presents only `extern C` interfaces and opaque types if yu can. C++ exception handling and the PostgreSQL backend's longjmp() based error handling will interact in exciting and interesting ways. Avoid calling `palloc`, `pfree` etc from within C++ if you can. If you really must, ensure that your C++ code doesn't use any RAII, stack-allocated objects with dtors, etc. Otherwise you'll have to translate error handling mechanisms at every boundary between C++ and Pg code, something I'm not even certain is possible to do reliably. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Maintaining state across function calls
On 20 November 2012 01:30, Craig Ringer cr...@2ndquadrant.com wrote: Otherwise you'll have to translate error handling mechanisms at every boundary between C++ and Pg code, something I'm not even certain is possible to do reliably. I think it's probably the case that PLV8 is the most mature example of wrapping a C++ library that is liable to throw C++ exceptions within Postgres backend code, in a sane way (that is, avoiding unwinding the stack via longjmp() over a part of the stack where a destructor needs to be called, which is undefined in C++). -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- 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] Experiences with pl/Java
i think pl/java may expect the method signatures to match up precisely. not
entirely sure, as there are no
examples published as to how pl/java expects out parameters to work.
richard
From: Thomas Hill [thomas.k.h...@t-online.de]
Sent: Monday, November 19, 2012 5:56 PM
To: Welty, Richard; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Experiences with pl/Java
thanks - not sure how plJava works here and if the implementation is
identical to Apache Derby - what I can tell however is that defining the
types the way I did (integer on one side vs an array of integers on the
other side) is exactely how Apache Derby needs this as there out parms
always need to be defined as arrays in the method declaration and are
then automatically returned as integers - I will try to use integers on
both sides for plJava tomorrow, but if this would solve the problem this
would also mean that method declaration is different and depending on
data base backend implementation - which would make could re-use impossible
Am 19.11.2012 22:58, schrieb Welty, Richard:
i looked your stuff over. i never needed to make out params work, so i'm kind
of guessing here, but the first thing i'd look at is the type mismatch
between the args given in the pl/pgsql 'create or replace function' vs the
args in the java declaration, that is, the int on one side vs the array of
ints on the other. due to the lack of examples available that you previously
pointed out, i have no real idea how that should look in a working example.
richard
From: Thomas Hill [thomas.k.h...@t-online.de]
Sent: Monday, November 19, 2012 2:55 PM
To: Welty, Richard; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Experiences with pl/Java
Thanks to all for providing feedback and sharing opinions. Looks like
you have gone much further on it than I thought someone would have. So I
think I might spend some more time with it, but not plan to use it for
my application in a production environment.
My initial attempts were to try to re-use/port some simple procedures
which are running fine on Apache Derby, but then I got stuck quite early
in the process and could not find documentation showing how things needs
to be done and helping me to understand what I am doing wrong.
My first use case was calling a procedure which does not have any
parameter and this I actually got to run, i.e.
public static String CURRENT_CLIENTID() throws SQLException {
String vcFKClientID = 000;
return vcFKClientID;
}
CREATE OR REPLACE FUNCTION rte.CURRENT_CLIENTID()
RETURNS character varying AS
'onlyPostgreSQLPk.Functions.CURRENT_CLIENTID'
LANGUAGE java VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION rte.CURRENT_CLIENTID()
OWNER TO postgres;
= select rte.CURRENT_CLIENTID() returns '000'
My second use case was to create a procedure with an out parameter, i.e.
public static void SP_getNextID(int iNextVal[], String vcIDName)
throws SQLException {
Connection conn = getDefaultConnection();
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
String cSQL = SELECT \LastValue\ \n + FROM rte.\TBL_IDs\
\n
+ WHERE \IDName\ = ' + vcIDName + '\n;
ResultSet rs = stmt.executeQuery(cSQL);
while (rs.next()) {
iNextVal[0] = rs.getInt(1) + 1;
rs.updateInt(LastValue, iNextVal[0]);
rs.updateRow();
}
rs.close();
stmt.close();
return;
}
CREATE OR REPLACE FUNCTION rte.SP_getNextID(OUT iNextID integer, IN
vcIDName character varying)
RETURNS integer AS
'allDatabasesPk.Procedures.SP_getNextID(int[], java.lang.String)'
The static method seems to be found. But when calling the function using
pgadmin and issuing a 'Select rte.SP_getNextID('xy');' I am getting error
Too many parameters - expected 1
which I find confusing as I am only passing one parameter!?
I tried some variations I could think of, but without success.
Unfortunately I have not found an exmaple anywhere on the web showing
how this needs to be done.
Would be great if someone could have a look at above and spot what I am
doing wrong.
Thanks a lot in advance.
Kind regards
Thomas
Am 19.11.2012 20:19, schrieb Welty, Richard:
Edson Richter [edsonrich...@hotmail.com] writes:
Em 19/11/2012 15:26, Welty, Richard escreveu:
PL/Java requires that the methods being directly called from PostgreSQL
are static.
while i don't disagree with the advice, PL/Java is limited in this respect.
:-) as I said, I know little about pl/Java... thanks for pointing this out.
So, after calling the static method, probably inside the static methods,
the programmer will create his/her logic. Writing this logic considering
GC
Re: [GENERAL] High SYS CPU - need advise
On Fri, Nov 16, 2012 at 11:19 AM, Vlad marche...@gmail.com wrote: We're looking for spikes in 'blk' which represents when lwlocks bump. If you're not seeing any then this is suggesting a buffer pin related issue -- this is also supported by the fact that raising shared buffers didn't help. If you're not seeing 'bk's, go ahead and disable the stats macro. most blk comes with 0, some with 1, few hitting 100. I can't say that during stall times the number of blk 0 vs blk non-0 are very different. right. this is feeling more and more like a buffer pin issue. but even then we can't be certain -- it could be symptom, not the cause. to prove it we need to demonstrate that everyone is spinning and waiting, which we haven't done. classic spinlock contention manifests in high user cpu. we are binding in kernel, so I wonder if it's all the select() calls. we haven't yet ruled out kernel regression. If I were you, I'd be investigating pgbouncer to see if your app is compliant with transaction mode processing, if for no other reason than it will mitigate high load issues. *) How many specific query plans are needed to introduce the condition, Hopefully, it's not too many. If so, let's start gathering the plans. If you have a lot of plans to sift through, one thing we can attempt to eliminate noise is to tweak log_min_duration_statement so that during stall times (only) it logs offending queries that are unexpectedly blocking. unfortunately, there are quite a few query plans... also, I don't think setting log_min_duration_statement will help us, cause when server is hitting high load average, it reacts slowly even on a key press. So even non-offending queries will be taking long to execute. I see all sorts of queries a being executed long during stall: spanning from simple LOG: duration: 1131.041 ms statement: SELECT 'DBD::Pg ping test' to complex ones, joining multiple tables. We are still looking into all the logged queries in attempt to find the ones that are causing the problem, I'll report if we find any clues. right. merlin -- 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 SYS CPU - need advise
On Fri, Nov 16, 2012 at 12:26 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Fri, Nov 16, 2012 at 8:21 AM, Merlin Moncure mmonc...@gmail.com wrote: On Fri, Nov 16, 2012 at 9:52 AM, Vlad marche...@gmail.com wrote: *) failing that, LWLOCK_STATS macro can be compiled in to give us some information about the particular lock(s) we're binding on. Hopefully it's a lwlock -- this will make diagnosing the problem easier. I've enabled that macro, seeing flying lwlock messages in the log (see below), even when there is no high-sys-cpu stall observed at the moment. Should I be looking for something in particular? We're looking for spikes in 'blk' which represents when lwlocks bump. Unfortunately LWLock contention is only a weak indication of spinlock contention. For example, if backends are furiously acquiring and releasing the same LWLock in shared mode and no one is getting an exclusive mode, then there can be high spinlock contention but there will never be a blk. yup. this problem doesn't smell like lwlock issues. typically there the problem manifests as low cpu performance, everybody waits. classic spinlock contention (at least from what i've seen) is very high *userspace* cpu utilization and low work output. this time it's different -- OP is bogging in the kernel so it's not impossible we're troubleshooting the symptom, not the cause. In 9.3 there is a new field that tells how many spin delays there were on the mutex that is behind each lock. That was commit b79ab00144e64217d41, maybe he can port that back to his version. But that only tells you about LWLock mutexes, not about all the other ones in PG. The attached patch logs every spin delay with where in the source it comes from. yeah, OP should fire this off. good stuff. I'll bet lunch (if we ever happen to meet) it's on buffer pin. merlin -- 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] [SOLVED] WINDOWS : PostgreSQL 8.4 Server Start Error
How did you perform the winsock solution?
[GENERAL] get source of udf
Hi list, I would like to get the source of a udf. In mssql I run this query: SELECT OBJECT_DEFINITION(OBJECT_ID) FROM sys.objects WHERE name='function_name'; And I get the entire source of the function. How must I do this in PostgreSQL? Thanks, Peter
Re: [GENERAL] High SYS CPU - need advise
I just did a little experiment: extracted top four queries that were executed the longest during stall times and launched pgbench test with 240 clients. Yet I wasn't able to put the server into a stall with that. Also load average was hitting 120+, it was all user cpu, single digit % system. The system remained pretty responsive (on a keypress), in contrary to when high-sys-cpu stall happens, showing similar LA numbers. This makes me think that there is probably a different (than ones I tried) query or condition that is responsible for creating high-sys-cpu. Also, from my limited knowledge of postgresql and kernel internals, this experiment puts under question idea that linux scheduler might be the corner stone Here is a longer log, first column is PID. High-sys-cpu stall happened around 10:42:37 https://dl.dropbox.com/u/109778/postgresql-2012-11-19_103425-stripped.log I'll try to put pgbouncer in place and repeat, also though this will take longer time to setup. -- Vlad
Re: [GENERAL] High SYS CPU - need advise
On Sun, Nov 18, 2012 at 4:24 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Fri, Nov 16, 2012 at 12:13 PM, Vlad marche...@gmail.com wrote: ok, I've applied that patch and ran. The stall started around 13:50:45...50 and lasted until the end https://dl.dropbox.com/u/109778/postgresql-2012-11-16_134904-stripped.log That isn't as much log as I expected. But I guess only the tip of the iceberg gets logged, it could be that most spinlocks are contended but then get acquired just before the sleep (and log) would have happened. I'm not sure how to figure out just how much spinning is going on below the surface, but my gut feeling is there isn't enough of it to explain the amount of slowdown you are seeing (and it would probably be accounted for as user time rather than system time) yes. classic spinlock contention symptoms are high user cpu and almost work getting done. something else is going on here? Maybe we could whip up a dummy standalone program that uses PG's spinlock code in a intentionally contended way and see how your system reports resource usage for that. Maybe your system reports fighting for cache lines or whatever is going on behind the scenes as system time rather than user time, or something. I don't think this is the case. It's looking more and more like scheduler issues, although it's unclear if that is due to some kind of regression or just symptoms of backends piling up. I'm also starting to wonder if the spinlock contention we are seeing (which according to your patch really isn't all that much really) is just symptomatic of scheduler issues. It would be a little easier to figure out what was going on there if the log included the pid (%p) so we can see if it is the same process doing a bunch of consecutive sleeps, or a bunch of different processes. Also, logging the millisecond (%m rather than %t) would be helpful. But the way that the point of contention jumps around a lot points to a scheduler issue rather than a postgres-specific issue. A backend gets pre-empted while holding a spinlock, and then the kernel is resistant to rescheduling it even though all other processes are piled up behind it. But again, that theory would lead to more log lines than we see, I would think. yeah. I asked for some longer strace logs but then everyone went home for the weekend. Anyway, pgbouncer might help by ensuring that a connection gets pre-empted by pgbouncer at a point where it has no locks, rather than by the kernel at a point where it does hold locks. yes -- I think pgbouncer is the key here because it's going to tell us if the scheduler is the culprit here. If you look carefully at the strace logs, you'll see that system call times become unpredictable as they are getting scheduled out...particularly yielding calls like select(). So we need to determine if this is normal high load behavior or if there is an underlying mechanic. Any chance you could try an older kernel and see if that reduces the problem? This is one thing to try. Personally I think pgbouncer/transaction mode is low hanging fruit if it 'drops in' to OP's app. If problem goes away this will pretty much point the finger at Linux AFAICT (in which case we can start messing around with the kernel). merlin -- 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_TERMINATE_BACKEND not working.
Chris Yes, i am logging in as Super User also, other side same user's connection(Superuser) i am trying to kill. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PG-TERMINATE-BACKEND-not-working-tp5732379p5732694.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High SYS CPU - need advise
On Mon, Nov 19, 2012 at 10:50 AM, Vlad marche...@gmail.com wrote: I just did a little experiment: extracted top four queries that were executed the longest during stall times and launched pgbench test with 240 clients. Yet I wasn't able to put the server into a stall with that. Also load average was hitting 120+, it was all user cpu, single digit % system. The system remained pretty responsive (on a keypress), in contrary to when high-sys-cpu stall happens, showing similar LA numbers. This makes me think that there is probably a different (than ones I tried) query or condition that is responsible for creating high-sys-cpu. Also, from my limited knowledge of postgresql and kernel internals, this experiment puts under question idea that linux scheduler might be the corner stone Here is a longer log, first column is PID. High-sys-cpu stall happened around 10:42:37 https://dl.dropbox.com/u/109778/postgresql-2012-11-19_103425-stripped.log I'll try to put pgbouncer in place and repeat, also though this will take longer time to setup. yeah. interesting -- contention was much higher this time and that changes things. strange how it was missed earlier. you're getting bounced around a lot in lwlock especially (unfortunately we don't know which one). I'm going to hazard another guess: maybe the trigger here is when the number of contending backends exceeds some critical number (probably based on the number of cores) you see a quick cpu spike (causing more backends to lock and pile up) as cache line bouncing sets in. That spike doesn't last long, because the spinlocks quickly accumulate delay counts then punt to the scheduler which is unable to cope. The exact reason why this is happening to you in exactly this way (I've never seen it) is unclear. Also the line between symptom and cause is difficult to draw. unfortunately, in your case spinlock re-scheduling isn't helping. log entries like this one: 18764 [2012-11-19 10:43:50.124 CST] LOG: JJ spin delay from file sinvaladt.c line 512 delay 212, pointer 0x7f514959a394 at character 29 are suggesting major problems. you're dangerously close to a stuck spinlock which is lights out for the database. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trigger based replication with ENABLE REPLICA triggers and session_replication_role best practice(s)
Hello, we have based all our replication infrastructure on a heavily hacked version of DBMirror, which now runs in a single master (office DB) - multiple slaves (vessels DBs) mode for 80+ slaves and about 300 tables and in multiple masters (the same vessels DBs as above) (having partitions of the data) - single slave (the same office db as above) mode for just two tables. Now we are in the process of designing a new solution which requires to have some form of multi-master functionality, without being so much concerned about conflict resolution at this stage. The issue that we are facing is to prevent replication data originating from a vessel DB and consumed into the office DB, (or replication data originating from the office DB and consumed into a vessel DB) to be bounced back to the originating server because of the invocation of the DBMirror trigger. We have thought of : Solution 1) explicitly disabling the triggers at the start of the transaction and re-enabling them, but that would require knowledge of the name of table in question prior to execution of the replication command, but in our case this not known unless parsing the SQL file which came from the originating server. Since the number of those multi-master tables is not large, we could explicit insert the ALTER TABLE tblname DISABLE TRIGGER tblname_dbmirror_trig commands prior to actual SQL execution for each table involved and then insert the respective ALTER TABLE tblname ENABLE TRIGGER tblname_dbmirror_trig commands after the SQL execution. However this would require hardcoding those commands into the code which runs the replication SQL, and this has many and obvious disadvantages. Then i looked upon : Solution 2) the ENABLE REPLICA TRIGGER in combination with session_replication_role. Setting default ALTER DATABASE dbname SET session_replication_role TO REPLICA (single master + multi-master ones) in combination with ALTER TABLE ENABLE REPLICA TRIGGER tblname_dbmirror_trig for *all* tables involved in replication would allow the default behavior of INSERTS/UPDATES/DELETES to result in firing the DBmirror trigger, which would work similar to the default way it has been running for the single direction replicated tables. Then in the code which plays the replication SQLs for the multi-master tables we would simply set smth like : BEGIN ; SET local session_replication_role TO origin; execute SQL here END; preventing the trigger to be called, and thus eliminating the bounce-back effect. One thing that worries me is setting the database-wide session_replication_role to smth different than the default. In our case, it would be ideal to be able to set session_replication_role to some value which would have the complimentary effect of REPLICA, smth like e.g. NOREPLICA which would result in on-demand temporary disablement of the triggers when leaving session_replication_role to non-REPLICA (such as the default value=origin). One third solution would be to : Solution 3) make the code updating those multi-master tables, replication-aware by putting SET local session_replication_role TO REPLICA; inside the affecting transactions. But this also has the obvious disadvantage of making application and system logic blend together, and also making the app programmer prone to errors. The most elegant solution IMHO is the 2nd but i am concerned that setting the database-wide session_replication_role to smth different than the default might just hide some future risks. What are your thoughts on that? Thank you a lot for any input. - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt
