[GENERAL] PL/PG SQL: select count(*) into from where - does not compute the where cond
Instead it returns the full count of the table. Any idea? Thanks Danny Abraham BMC Software CTM&D Business Unit 972-52-4286-513 danny_abra...@bmc.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] PL/PG SQL: select count(*) into from where - does not compute the where cond
Hello try to check if there are some column names are equal to variable names. This is symptom of identifier collision. regards Pavel Stehule 2009/9/23 Abraham, Danny : > Instead it returns the full count of the table. > > Any idea? > > Thanks > > Danny Abraham > BMC Software > CTM&D Business Unit > 972-52-4286-513 > danny_abra...@bmc.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
Re: [GENERAL] PL/PG SQL: select count(*) into from where - does not compute the where cond
In response to Abraham, Danny : > Instead it returns the full count of the table. > > Any idea? No. Can you show us an example? test=*# select * from o; i --- 3 1 2 (3 rows) test=*# create or replace function my_count() returns int as $$declare c int;begin select into c count(1) from o where i=2; return c; end;$$language plpgsql; CREATE FUNCTION test=*# select * from my_count(); my_count -- 1 (1 row) In other words: works for me. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/PG SQL: select count(*) into from where - does not compute the where cond
Hello try to check if there are some column names are equal to variable names. This is symptom of identifier collision. regards Pavel Stehule > > 2009/9/23 Abraham, Danny : >> Instead it returns the full count of the table. >> >> Any idea? >> >> Thanks >> >> Danny Abraham >> BMC Software >> CTM&D Business Unit >> 972-52-4286-513 >> danny_abra...@bmc.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] Re: PL/PG SQL: select count(*) into from where - does not compute the where cond
... forgot the most import issue The problem shows when the selection is from a view. The where condition does not have names contention. It is: select count(*) into from where -- 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 have ant's task insert special chars appropriately?
agostonbejo wrote: > > Hi! > > What I'm trying to do is to insert some data from a sql file into a postgres > DB by calling the ant task. My problem is that I can't get special > characters (even if they can be represented by the standard ASCII charset, > such as ä, ö, ü, é, etc.) to be inserted correctly. Those aren't ASCII. > When I check the DB with a DB browser such as Squirrel or pgAdmin, after > running the task they can only see question marks where these > characters are supposed to be. So does the actual web application using the > DB. (The web app can store and read such special characters seamlessly, > though. It's also OK if I enter some of them by hand with the DB browsers > mentioned above.) [snip] > Does anyone have any idea how to get those special characters loaded into > the DB from a pre-written sql file (possibly with the ant task)? There are three places you need to get this right: 1. The database encoding 2. The client encoding 3. The encoding of the contents of the .sql file Now, since the database is UTF8 that means it can accept the entire range of unicode characters, including all ISO-8859-1. PostgreSQL can automatically convert from ISO-8859-1 to UTF-8 for you, so it doesn't matter which you have in your .sql file. What *does* matter is that you know what encoding your .sql file is using and that you set the client encoding appropriately. Since you're using Java, it's probably simplest just to use UTF-8 all the way through. Crucially, make sure you know what the character-set of the .sql file is - any good text editor should be able to tell you / set this. -- Richard Huxton Archonet Ltd -- 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 have ant's task insert special chars appropriately?
On Wednesday 23 September 2009 11:29:02 Richard Huxton wrote: > agostonbejo wrote: > > Hi! > > > > What I'm trying to do is to insert some data from a sql file into a > > postgres DB by calling the ant task. My problem is that I can't get > > special characters (even if they can be represented by the standard ASCII > > charset, such as ä, ö, ü, é, etc.) to be inserted correctly. > > Those aren't ASCII. Actually, they are: ä = 132 ö = 148 ü = 129 é = 130 See: http://www.ascii.nl Site is in dutch, but these characters are in the table headed "Extended ASCII Codes" But for this to work, the source-file needs to be created using the ASCII codepage. Not some extension to it. -- Joost -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: PL/PG SQL: select count(*) into from where - does not compute the where cond - returns always total count
Any idea? Thanks Danny -- 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] Re: PL/PG SQL: select count(*) into from where - does not compute the where cond - returns always total count
In response to Abraham, Danny : > Any idea? Apparently no, because nobody can reproduce your problem. See my other post, supplementary: test=*# create view view_o as select * from o; CREATE VIEW test=*# create or replace function my_count() returns int as $$declare c int;begin select into c count(1) from view_o where i=2; return c; end;$$language plpgsql; CREATE FUNCTION test=*# select * from my_count(); my_count -- 1 (1 row) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) -- 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] Where can I find detail information about constraint ?
On 23/09/2009 11:23, 纪晓曦 wrote: > I want detail usage introduction about constraint,such as how to > constrain a string to be exactly 4 character and start with 'z'. > Where can I find the reference of CHECK? detail usage. Try the documentation, presumably under CREATE TABLE: http://www.postgresql.org/docs Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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 have ant's task insert special chars appropriately?
J. Roeleveld wrote: > On Wednesday 23 September 2009 11:29:02 Richard Huxton wrote: >> agostonbejo wrote: >>> Hi! >>> >>> What I'm trying to do is to insert some data from a sql file into a >>> postgres DB by calling the ant task. My problem is that I can't get >>> special characters (even if they can be represented by the standard ASCII >>> charset, such as ä, ö, ü, é, etc.) to be inserted correctly. >> Those aren't ASCII. > > Actually, they are: Sorry, but actually, they aren't. ASCII is 7-bits only and doesn't include those accented characters. > ä = 132 > ö = 148 > ü = 129 > é = 130 > See: http://www.ascii.nl > Site is in dutch, but these characters are in the table headed "Extended > ASCII > Codes" To the best of my knowledge there is no standard list of "extended ascii codes" - there are many different extensions. See all the various codepages on Windows/IBM, original Mac character set, the various 8-bit era computers etc. > But for this to work, the source-file needs to be created using the ASCII > codepage. Not some extension to it. Not sure what this last bit means. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] multiple calls to dblink_connect from within pl/pgSQL function exhaust connection limit
Hello, I'm writing a pl/pgSQL function that connects to multiple PostgreSQL databases and accumulates data from all of them into a temporary table. I use the dblink contrib module to do so. I loop through some records, form a connection string from those records, and do this: PERFORM dblink_connect(conn_str); Making many successive calls to this function of mine eventually exhausts the connection limit of my remote DBs (although I think that the default connection limit is something like 5 or 10, and it takes more calls than that). I understood from the dblink docs that calling this particular overload of dblink_connect wouldn't do so, because each subsequent call to it disconnects the last, because an "unnamed connection is opened, replacing any existing unnamed connection". This is unlikely to be a concern in my production system, because users will connect to my main db, execute this function and disconnect (dblink connections only persist for as long as the connection that originated them, I also understand from the docs). Nonetheless, I'd like to know what's happening here. Why the apparent resource leak? Thanks, Peter Geoghegan -- 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] Re: PL/PG SQL: select count(*) into from where - does not compute the where cond - returns always total count
the "does not compute the where condition" needs more definition can you supply the table schema and which columns you want? select count(*) from table will get you the count We can banter on the need to store in views once the schema is known Martin Gainty __ Verzicht und Vertraulichkeitanmerkung Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. > From: danny_abra...@bmc.com > To: danny_abra...@bmc.com; pgsql-general@postgresql.org > Date: Wed, 23 Sep 2009 05:04:09 -0500 > Subject: [GENERAL] Re: PL/PG SQL: select count(*) into from > where - does not compute the where cond - returns always total count > > Any idea? > > Thanks > > Danny > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Bing brings you health info from trusted sources. http://www.bing.com/search?q=pet+allergy&form=MHEINA&publ=WLHMTAG&crea=TXT_MHEINA_Health_Health_PetAllergy_1x1
[GENERAL] Where can I find detail information about constraint ?
I want detail usage introduction about constraint,such as how to constrain a string to be exactly 4 character and start with 'z'.Where can I find the reference of CHECK? detail usage.
Re: [GENERAL] Graphical representation of query plans
pgadmin does it pretty nicely: http://pgadmin.org/images/screenshots/pgadmin3_macosx.png As shown in the mackintosh version, it is a very nice and helpful feature! I have pgadmin 1.2.0 for PostgreSQL 8.0.15 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2. I did not see this "explain" in my version. I only have "Data Output, Message, and History". May I know in which pgadmin version the "query plan visualization" was added please? At least, 1.4 has it. Did not see it from pgadmin online doc either? http://www.pgadmin.org/docs/dev/query.html That's great! It is exactly what I am looking for! Thanks a lot! -- Lu Ying -- 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] Where can I find detail information about constraint ?
??? wrote: > I want detail usage introduction about constraint,such as how > to constrain a string to be exactly 4 character and start with 'z'. > Where can I find the reference of CHECK? detail usage. Documentation: http://www.postgresql.org/docs/current/static/ddl-constraints.html#AEN2254 The check you want would look similar to this: CHECK (length(val) = 4 AND substr(val, 1, 1) = 'z') Yours, Laurenz Albe -- 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 have ant's task insert special chars appropriately?
Richard Huxton writes: > J. Roeleveld wrote: >> Site is in dutch, but these characters are in the table headed "Extended >> ASCII >> Codes" > To the best of my knowledge there is no standard list of "extended ascii > codes" - Oh, there are plenty of them ;-). The OP's character set is most likely ISO-8859-1 or one of the other ISO-8859-n extensions to ASCII. 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] Help! Database restored with disabled triggers
On Tuesday 22 September 2009 7:28:03 pm Joe Kramer wrote: > I have database backup schema+data in text (non-compressed) format. > Backup is created using "pg_dump -i -h ... -U ... -f dump.sql". > I run it with "psql > I can't use this text dump with pg_restore because it only accept > archived dumps. And I am not sure that using pg_restore will solve > disabled triggers problem. > I need to have the backup in text format so I can open and edit it. > > There was a recipe earlier in this mailing list that involves writing > a function that will enable all triggers one-by-one. But I want to do > it a proper way, without such "hacking". > > What would be the solution for me? > > Thanks. What version of Postgres are you dumping from, restoring to? Which version of pg_dump are you using? -- Adrian Klaver akla...@comcast.net -- 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] Help! Database restored with disabled triggers
Joe Kramer writes: > I have database backup schema+data in text (non-compressed) format. > Backup is created using "pg_dump -i -h ... -U ... -f dump.sql". > I run it with "psql http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help! Database restored with disabled triggers
On Thu, Sep 24, 2009 at 12:02 AM, Adrian Klaver wrote: > On Tuesday 22 September 2009 7:28:03 pm Joe Kramer wrote: >> I have database backup schema+data in text (non-compressed) format. >> Backup is created using "pg_dump -i -h ... -U ... -f dump.sql". >> I run it with "psql > >> I can't use this text dump with pg_restore because it only accept >> archived dumps. And I am not sure that using pg_restore will solve >> disabled triggers problem. >> I need to have the backup in text format so I can open and edit it. >> >> There was a recipe earlier in this mailing list that involves writing >> a function that will enable all triggers one-by-one. But I want to do >> it a proper way, without such "hacking". >> >> What would be the solution for me? >> >> Thanks. > > What version of Postgres are you dumping from, restoring to? Which version of > pg_dump are you using? > > -- I am using client 8.1.9 to dump from server 8.3.0 (unable to use client 8.3.x) Importing to server 8.3.7. -- 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] Help! Database restored with disabled triggers
On Thu, Sep 24, 2009 at 12:53 AM, Tom Lane wrote: > Joe Kramer writes: >> I have database backup schema+data in text (non-compressed) format. >> Backup is created using "pg_dump -i -h ... -U ... -f dump.sql". >> I run it with "psql > You sure they weren't disabled in the source database? AFAICS pg_dump > just duplicates the trigger state it sees in the source. > > regards, tom lane > Yes, I'm absolutely sure they are not disabled. And in the SQL dump file there are no commands that would disable them. It simply goes on to creating triggers, but in the end they are all disabled. Regards. -- 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] Help! Database restored with disabled triggers
Joe Kramer writes: > On Thu, Sep 24, 2009 at 12:02 AM, Adrian Klaver wrote: >> What version of Postgres are you dumping from, restoring to? Which version of > I am using client 8.1.9 to dump from server 8.3.0 (unable to use client > 8.3.x) > Importing to server 8.3.7. You mean you are dumping from an 8.3 server with an 8.1 pg_dump? That is pretty much guaranteed not to work; I am surprised that the only symptom you notice is bad trigger state. Why do you feel you can't use an up-to-date pg_dump? 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] Advance SQL subquery
Hello, I have a table like : date|value --- 2009-09-19 | 1 2009-09-20 | 2 2009-09-21 | 6 2009-09-22 | 9 2009-09-23 | 1 I'd like a request which gives me the sum of each last n days. For example, if I want the sum of each 3 days, I want this result: date| sum_value --- 2009-09-19 | 1 (sum from 2009-09-17 to 2009-09-19) 2009-09-20 | 3 (sum from 2009-09-18 to 2009-09-20) 2009-09-21 | 9 (sum from 2009-09-19 to 2009-09-21) 2009-09-22 | 17 (sum from 2009-09-20 to 2009-09-22) 2009-09-23 | 16 (sum from 2009-09-21 to 2009-09-23) I try to make a subquery which is apply on each row of a query, but it does work. Has anybody an idea? Thanks in advance! Anthony -- 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] Advance SQL subquery
On Wed, Sep 23, 2009 at 8:13 AM, AnthonyV wrote: > Hello, > > I have a table like : > > date | value > --- > 2009-09-19 | 1 > 2009-09-20 | 2 > 2009-09-21 | 6 > 2009-09-22 | 9 > 2009-09-23 | 1 > > I'd like a request which gives me the sum of each last n days. > For example, if I want the sum of each 3 days, I want this result: > > date | sum_value > --- > 2009-09-19 | 1 (sum from 2009-09-17 to 2009-09-19) > 2009-09-20 | 3 (sum from 2009-09-18 to 2009-09-20) > 2009-09-21 | 9 (sum from 2009-09-19 to 2009-09-21) > 2009-09-22 | 17 (sum from 2009-09-20 to 2009-09-22) > 2009-09-23 | 16 (sum from 2009-09-21 to 2009-09-23) > > I try to make a subquery which is apply on each row of a query, but it > does work. select date, (select count(*) from foo where date between f.date - 1 and f.date + 1) from foo f; -- 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] Help! Database restored with disabled triggers
Joe Kramer writes: > On Thu, Sep 24, 2009 at 12:53 AM, Tom Lane wrote: >> You sure they weren't disabled in the source database? > Yes, I'm absolutely sure they are not disabled. And in the SQL dump > file there are no commands that would disable them. Better take another look for "ALTER TABLE foo DISABLE TRIGGER bar" commands. Given the information that this is a pre-8.3 pg_dump, that's exactly the behavior I'd expect, because it's not going to understand the values it finds in pg_trigger.tgenabled in an 8.3 server. 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]
Hi, I am getting the following error while processing a query. "server process was terminated by exception xc005" Please help me resolve this. Regards Swati
Re: [GENERAL]
Ms swati chande writes: > I am getting the following error while processing a query. > "server process was terminated by exception xc005" What query, exactly? Which PG version is this? 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] Help! Database restored with disabled triggers
On Thu, Sep 24, 2009 at 1:33 AM, Tom Lane wrote: > Joe Kramer writes: >> On Thu, Sep 24, 2009 at 12:53 AM, Tom Lane wrote: >>> You sure they weren't disabled in the source database? > >> Yes, I'm absolutely sure they are not disabled. And in the SQL dump >> file there are no commands that would disable them. > > Better take another look for "ALTER TABLE foo DISABLE TRIGGER bar" > commands. Given the information that this is a pre-8.3 pg_dump, > that's exactly the behavior I'd expect, because it's not going to > understand the values it finds in pg_trigger.tgenabled in an 8.3 > server. > Thanks, I found DISABLE TRIGGER commands and deleted them, but wish I could find a way to make pg_dump not to add them! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Source for CreatedSharedMemoryAndSemaphores
Hello All experiencing a problem with build not finding function CreatedSharedMemoryAndSemaphores any ideas to locate source and or build files for CreatedSharedMemoryAndSemaphores would be appreciated thanks! Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. _ Bing brings you health info from trusted sources. http://www.bing.com/search?q=pet+allergy&form=MHEINA&publ=WLHMTAG&crea=TXT_MHEINA_Health_Health_PetAllergy_1x1
Re: [GENERAL] Help! Database restored with disabled triggers
- "Joe Kramer" wrote: > On Thu, Sep 24, 2009 at 1:33 AM, Tom Lane wrote: > > Joe Kramer writes: > >> On Thu, Sep 24, 2009 at 12:53 AM, Tom Lane > wrote: > >>> You sure they weren't disabled in the source database? > > > >> Yes, I'm absolutely sure they are not disabled. And in the SQL > dump > >> file there are no commands that would disable them. > > > > Better take another look for "ALTER TABLE foo DISABLE TRIGGER bar" > > commands. Given the information that this is a pre-8.3 pg_dump, > > that's exactly the behavior I'd expect, because it's not going to > > understand the values it finds in pg_trigger.tgenabled in an 8.3 > > server. > > > > Thanks, I found DISABLE TRIGGER commands and deleted them, > but wish I could find a way to make pg_dump not to add them! > You are going to have to use the 8.3 pg_dump :) Adrian Klaver akla...@comcast.net -- 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] Source for CreatedSharedMemoryAndSemaphores
Martin Gainty writes: > experiencing a problem with build not finding function > CreatedSharedMemoryAndSemaphores Typo? Should be CreateSharedMemoryAndSemaphores. 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] Advance SQL subquery
On 23 sep, 17:32, mmonc...@gmail.com (Merlin Moncure) wrote: > On Wed, Sep 23, 2009 at 8:13 AM, AnthonyV wrote: > > Hello, > > > I have a table like : > > > date | value > > --- > > 2009-09-19 | 1 > > 2009-09-20 | 2 > > 2009-09-21 | 6 > > 2009-09-22 | 9 > > 2009-09-23 | 1 > > > I'd like a request which gives me the sum of each last n days. > > For example, if I want the sum of each 3 days, I want this result: > > > date | sum_value > > --- > > 2009-09-19 | 1 (sum from 2009-09-17 to 2009-09-19) > > 2009-09-20 | 3 (sum from 2009-09-18 to 2009-09-20) > > 2009-09-21 | 9 (sum from 2009-09-19 to 2009-09-21) > > 2009-09-22 | 17 (sum from 2009-09-20 to 2009-09-22) > > 2009-09-23 | 16 (sum from 2009-09-21 to 2009-09-23) > > > I try to make a subquery which is apply on each row of a query, but it > > does work. > > select date, (select count(*) from foo where date between f.date - 1 > and f.date + 1) from foo f; > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-general It works. Thank you! -- 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] Advance SQL subquery
On Wed, Sep 23, 2009 at 05:13:34AM -0700, AnthonyV wrote: > Hello, > > I have a table like : > >date|value > --- > 2009-09-19 | 1 > 2009-09-20 | 2 > 2009-09-21 | 6 > 2009-09-22 | 9 > 2009-09-23 | 1 > > I'd like a request which gives me the sum of each last n days. > For example, if I want the sum of each 3 days, I want this result: > >date| sum_value > --- > 2009-09-19 | 1 (sum from 2009-09-17 to 2009-09-19) > 2009-09-20 | 3 (sum from 2009-09-18 to 2009-09-20) > 2009-09-21 | 9 (sum from 2009-09-19 to 2009-09-21) > 2009-09-22 | 17 (sum from 2009-09-20 to 2009-09-22) > 2009-09-23 | 16 (sum from 2009-09-21 to 2009-09-23) > > I try to make a subquery which is apply on each row of a query, but it > does work. > > Has anybody an idea? We've implemented part of the SQL standard windowing functions, but not the part (ROWS BETWEEN M PRECEDING AND N FOLLOWING) that would make this most convenient. What you can do instead is something like this: SELECT "date", ( value + COALESCE(lag(value,1) OVER w, 0) + COALESCE(lag(value,2) OVER w, 0) ) AS sum FROM your_log WINDOW w AS (ORDER BY "date") ORDER BY "date"; When we add (ROWS BETWEEN M PRECEDING AND N FOLLOWING) to the window, you'll be able to use sum() and parameterize it like this: SELECT "date", SUM (value) OVER w FROM your_log WINDOW w AS ( ORDER BY "date" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) ORDER BY "date"; Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Help! Database restored with disabled triggers
On Wed, Sep 23, 2009 at 9:12 AM, Joe Kramer wrote: > I am using client 8.1.9 to dump from server 8.3.0 (unable to use client > 8.3.x) > Importing to server 8.3.7. That won't work -- 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] Source for CreatedSharedMemoryAndSemaphores
yep its in /backend/storage/ipci/ipci.c for some reason after i compile backend and try to link postgres i'm missing ipci.o ? i'll append into ipci.o into OBJS and give it a go Thanks Tom! Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > To: mgai...@hotmail.com > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Source for CreatedSharedMemoryAndSemaphores > Date: Wed, 23 Sep 2009 12:33:37 -0400 > From: t...@sss.pgh.pa.us > > Martin Gainty writes: > > experiencing a problem with build not finding function > > CreatedSharedMemoryAndSemaphores > > Typo? Should be CreateSharedMemoryAndSemaphores. > > 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 _ Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. http://clk.atdmt.com/GBL/go/171222985/direct/01/
Re: [GENERAL] Source for CreatedSharedMemoryAndSemaphores
Martin Gainty writes: > for some reason after i compile backend and > try to link postgres i'm missing ipci.o ? > i'll append into ipci.o into OBJS and give it a go I'd be really surprised if just that one file is missing; I think you must have some bigger problem. Maybe you are running into command line length limits? 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] multiple calls to dblink_connect from within pl/pgSQL function exhaust connection limit
Peter Geoghegan writes: > I use the dblink contrib module to do so. I loop through some records, > form a connection string from those records, and do this: > PERFORM dblink_connect(conn_str); > > Making many successive calls to this function of mine eventually > exhausts the connection limit of my remote DBs (although I think that > the default connection limit is something like 5 or 10, and it takes > more calls than that). I understood from the dblink docs that calling > this particular overload of dblink_connect wouldn't do so, because > each subsequent call to it disconnects the last, because an "unnamed > connection is opened, replacing any existing unnamed connection". What PG version is this? 8.4 has a connection leak in some cases: http://archives.postgresql.org/pgsql-committers/2009-09/msg00125.php This fix hasn't made any released version yet, but you could apply the source patch shown there. 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] pgadmin is changing pgpass.conf
Richard Huxton wrote: Raymond O'Donnell wrote: On 18/09/2009 16:07, Howard Cole wrote: Thanks for the update. Unfortunately this behaviour has the side effect of deleting passwords that I have set up in the file manually for other applications (namely the backup), which runs under the same user account. I guess I'll just have to come up with an alternative. Hi Howard, I'm afraid that's about the size of it. It's probably a good idea to have a separate account for executing the backup in any case. Surely pgadmin preserves any existing entries in pgpass.conf? Not in this case. There are originally two entries in pgpass.conf - one for server localhost and one for server 127.0.0.1 - the reasoning behind this is that when the backup runs as a scheduled task it sometimes seems to prefer one format to the other. However, when I open PGAdmin, one of the entries disappears. Perhaps it resolves the address and thinks they are the same entries? Anyway, the problem was resolved in the script that executes pg_dump, forcing it to use localhost or 127.0.0.1 using the -h option. As long as the -h ties in with what pgadmin writes to pgpass, there are no authentication problems. Howard Cole www.selestial.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] Source for CreatedSharedMemoryAndSemaphores
hard dependency on the 1.4 IPC library (remember the old days when you used to have load the 7 layer stack one at a time) (un)fortunately im running thru cygwin and the cygipc 1.4 library is missing or at least i cant find it whats more some of the cygwin binaries have the MZ (mark zibikowski) header which means they are native windows binaries been a while since ive compiled/linked binaries but if I can get the 1.4 cygipc source I should be able to compile/link and get a functioning binary for IPC functions thanks tom Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > To: mgai...@hotmail.com > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Source for CreatedSharedMemoryAndSemaphores > Date: Wed, 23 Sep 2009 13:31:20 -0400 > From: t...@sss.pgh.pa.us > > Martin Gainty writes: > > for some reason after i compile backend and > > try to link postgres i'm missing ipci.o ? > > i'll append into ipci.o into OBJS and give it a go > > I'd be really surprised if just that one file is missing; I think you > must have some bigger problem. Maybe you are running into command line > length limits? > > 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 _ Bing brings you health info from trusted sources. http://www.bing.com/search?q=pet+allergy&form=MHEINA&publ=WLHMTAG&crea=TXT_MHEINA_Health_Health_PetAllergy_1x1
Re: [GENERAL] multiple calls to dblink_connect from within pl/pgSQL function exhaust connection limit
Hi Tom, > What PG version is this? 8.4 has a connection leak in some cases: > http://archives.postgresql.org/pgsql-committers/2009-09/msg00125.php > > This fix hasn't made any released version yet, but you could apply the > source patch shown there. This is 8.4.0 on Windows. Looks like it's the bug you mention. I guess I'll just upgrade to 8.4.1. Thanks, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Looking for way to replicate master db to multiple mobile databases
Hi, I'm looking for a way to replicate am master database to multiple (100+) databases that are taken in to the field. Currently for each laptop we dump and load the tables. However,there is only a small percentage of data that changes on a frequent basis. I've been looking around and come across pyerplica, londiste and bucardo - the documentation on most of these is fairly sparse. It seems that Bucardo may be the best bet - at least initially. However, I thought I'd see if anyone is doing something similar and what thoughts there might be out there as to a better way to accomplish this. Thanks, Bryan.
[GENERAL] grants on pg_stats_activity
Hi all, I'm trying to monitor databases with munin. For security reasons I guess it is better to create a specialized user "munin" or like that and grant it only the necessary rights: no superuser rights, nor write access on my applications databases. My problem is, this user cannot access to info of pg_stat_activity view. It can only count the entries, but I would want to monitor waiting, and IDLE/IDLE in transaction connections. Is that possible ? As seen in an old post, I tried grant select on pg_stat_activity and pg_authid, to no avail. The PostgreSQL version I use is the 8.3. But I will need it also on 8.4 Regards, -- JC Ph'nglui mglw'nafh Cthulhu n'gah Bill R'lyeh Wgah'nagl fhtagn!
[GENERAL] More straight forward method to convert seconds::bigint to interval
I work in VoIP. HMS (Hour/Minute/Second) format appears to be the rule when working with call time totals. I admit it makes some reports easier to read. The method I used to convert a int/bigint to HMS (or the standard representation of an interval type) is as follows: select (123456.789::varchar(24) || ' seconds')::interval as HMS; hms -- 34:17:36.789 Is there a less string oriented method of converting seconds as an int to an interval? - Shane signature.asc Description: OpenPGP digital signature
Re: [GENERAL] More straight forward method to convert seconds::bigint to interval
"Shane R. Spencer" writes: > Is there a less string oriented method of converting seconds as an int > to an interval? Multiply by an interval, eg 123456.789 * interval '1 second' It works, it's fast, and you can use any scale factor you want. 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] More straight forward method to convert seconds::bigint to interval
Shane R. Spencer wrote: I work in VoIP. HMS (Hour/Minute/Second) format appears to be the rule when working with call time totals. I admit it makes some reports easier to read. The method I used to convert a int/bigint to HMS (or the standard representation of an interval type) is as follows: select (123456.789::varchar(24) || ' seconds')::interval as HMS; hms -- 34:17:36.789 Is there a less string oriented method of converting seconds as an int to an interval? - Shane I think this is cleaner/faster: select interval '1 second' * 123456.789 as HMS; hms -- 34:17:36.789 -Adam -- 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] Looking for way to replicate master db to multiple mobile databases
On Wed, Sep 23, 2009 at 11:11 AM, Bryan Montgomery wrote: > Hi, > I'm looking for a way to replicate am master database to multiple (100+) > databases that are taken in to the field. Currently for each laptop we dump > and load the tables. However,there is only a small percentage of data that > changes on a frequent basis. > > I've been looking around and come across pyerplica, londiste and bucardo - > the documentation on most of these is fairly sparse. It seems that Bucardo > may be the best bet - at least initially. > > However, I thought I'd see if anyone is doing something similar and what > thoughts there might be out there as to a better way to accomplish this. The problem domain you're working on is a bit different from regular replication. Most replication solutions are made to keep two machines that talk to each other all the time in sync. Disconnect one machine and maybe replication will resume properly, and maybe it wont. So, do you need the slave databases to be updatable? Do you need the changes to go back into the master? Do you need conflict resolution? Depending on the full fleshed out requirements, you may be stuck writing your own solution, or re-writing one somebody already wrote. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Possible Bug - error creating a tablespace
So I think I *may* have found a bug but I want to be sure before I file a bug. I did a search on the pgsql-bugs list using the search text: cannot create tablespace and got nothing back. Here's the scenario: 1) we installed CentOS 5.3 x86_64 on a 64bit Dell server 2) I installed the pgdg_centos-8.3-6.noarch rpm in order to enable the 8.3.x repo. 3) I used yum to install postgres and friends 4) Here's the weird part: -- If I start the cluster via "sudo /etc/init.d/postgres start" and then try and create a tablespace it fails with : create tablespace benchmark2 location '/eq5/pwbbench'; ERROR: could not set permissions on directory "/eq5/pwbbench": Permission denied -- however if I start the db as user postgres with: pg_ctl -D /var/lib/pgsql/data start and then create the tablespace it works. Likewise if I create the tablespace after starting the cluster via pg_ctl and then subsequently restart the db via 'sudo /etc/init.d/postgres start' I get errors trying to insert data into the previously created tablespace but if I start the cluster with pg_ctl (as user postgres) then I can also add data to the tablespace. I see that in the /etc/init.d/postgres file the start command is running the postmaster (not pg_ctl) as user postgres. So, based on the above I suspect Its a bug but I wanted to be sure. Thoughts? -- 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] Possible Bug - error creating a tablespace
Kevin Kempter writes: > 4) Here's the weird part: > -- If I start the cluster via "sudo /etc/init.d/postgres start" and then try > and create a tablespace it fails with : > create tablespace benchmark2 location '/eq5/pwbbench'; > ERROR: could not set permissions on directory "/eq5/pwbbench": Permission > denied > -- however if I start the db as user postgres with: > pg_ctl -D /var/lib/pgsql/data start > and then create the tablespace it works. SELinux ... in the first case SELinux recognizes the postmaster as being a daemon it ought to restrict, in the second case it just thinks it's an interactive program. In general, using tablespaces under SELinux requires labeling their root directories properly; postgresql_db_t if memory serves. 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] More straight forward method to convert seconds::bigint to interval
Well worth joining the list. Thanks guys. On Wed, Sep 23, 2009 at 12:48 PM, Adam Rich wrote: > Shane R. Spencer wrote: >> >> I work in VoIP. HMS (Hour/Minute/Second) format appears to be the rule >> when working with call time totals. I admit it makes some reports >> easier to read. >> >> The method I used to convert a int/bigint to HMS (or the standard >> representation of an interval type) is as follows: >> >> select (123456.789::varchar(24) || ' seconds')::interval as HMS; >> hms >> -- >> 34:17:36.789 >> >> Is there a less string oriented method of converting seconds as an int >> to an interval? >> >> - Shane >> > > I think this is cleaner/faster: > > > select interval '1 second' * 123456.789 as HMS; > > hms > -- > 34:17:36.789 > > > -Adam > > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to get variable out to shell script
On Sun, 20 Sep 2009, Abel Camarillo wrote: #!/bin/sh dbname= user= password= In general it's better to use the .pgpass/PGPASSFILE mechanism: http://www.postgresql.org/docs/current/static/libpq-pgpass.html to cache passwords like this, if you can't eliminate the need for them altogether through pg_hba.conf adjustments. That way, when you do change the database user's password, there's only one place to update for all of your scripts that talk to the database as that user. It's better still to parameterize all of these connection things into a global configuration file, but now you're talking an extra bit of coding; pgpass support you basically get for free in your app if it's talking to the database with psql. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] lazy vacuum and AccessExclusiveLock
Hello, we are facing strange situation with exclusively locked table during normal lazy vacuum. There is one big table (66GB) that is heavily inserted and updated in our database. Suddenly (after backup and delete of almost all records) we are not able to run VACUUM over this table because after 50 minutes of work it allocate AccessExclusiveLock on this table and all other connections start to timeout. It's common knowledge that VACUUM doesn't block and it looks like it's not true. I found this little excuse in documentation (http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html): "... it does not attempt to reclaim the space used by this dead data unless the space is at the end of the table and an exclusive table lock can be easily obtained. Unused space at the start or middle of the file does not result in the file being shortened and space returned to the operating system." This seems to me that situation can appear that dead tuples are in such position that VACUUM will decide to reclaim free space and block other process! Is it true? I found old thread in archive speaking about similar problem (http://archives.postgresql.org/pgsql-performance/2008-06/msg00235.php) but with a resolution that it should be no problem any more. It looks like it is still a problem. If this is true, is there any solution how to convince vacuum not to reclaim free space in any situation? Regards, Jaromir smime.p7s Description: S/MIME cryptographic signature
[GENERAL] Log File Melancholy
Hi,
I have two Snow Leopard installations, a macbook pro and a mac mini.
Whenever I start the postgresql system on the mbp the log shows:
LOG: received smart shutdown request
LOG: shutting down
LOG: database system is shut down
could not lookup DNS configuration info service: (ipc/send) invalid
destination port
LOG: could not resolve "localhost": nodename nor servname provided,
or not known
LOG: disabling statistics collector for lack of working socket
WARNING: autovacuum not started because of misconfiguration
HINT: Enable the "track_counts" option.
LOG: database system was shut down at 2009-09-21 11:24:17 EDT
LOG: database system is ready to accept connections
on the Mac Mini I get:
LOG: database system is shut down
could not lookup DNS configuration info service: (ipc/send) invalid
destination port
LOG: could not resolve "localhost": nodename nor servname provided,
or not known
LOG: disabling statistics collector for lack of working socket
WARNING: autovacuum not started because of misconfiguration
HINT: Enable the "track_counts" option.
DNSServiceDiscoveryLookupServer(): {/SourceCache/mDNSResponder/
mDNSResponder-212.1/mDNSMacOSX/DNSServiceDiscovery.c:143}
bootstrap_look_up() failed: $1003
LOG: database system was shut down at 2009-09-11 14:21:01 EDT
LOG: database system is ready to accept connections
Both are 32 bit builds...
I cannot figure out where the "could not lookup DNS..." line
is coming from...
Am I unique?
Jerry
--
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] Log File Melancholy
Jerry LeVan writes: > could not lookup DNS configuration info service: (ipc/send) invalid > destination port > LOG: could not resolve "localhost": nodename nor servname provided, > or not known > LOG: disabling statistics collector for lack of working socket > I cannot figure out where the "could not lookup DNS..." line > is coming from... Presumably from inside getaddrinfo(), since the subsequent LOG message is Postgres complaining that getaddrinfo didn't work. > Am I unique? Works for me on both 32- and 64-bit Snow Leopard installations, and for at least one Snow Leopard machine in the buildfarm. There must be something funny about the DNS configuration on your machines, but there's no way to tell what from here. 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] Possible Bug - error creating a tablespace
On 09/23/2009 04:30 PM, Tom Lane wrote: > Kevin Kempter writes: >> 4) Here's the weird part: >> -- If I start the cluster via "sudo /etc/init.d/postgres start" and then try >> and create a tablespace it fails with : > >> create tablespace benchmark2 location '/eq5/pwbbench'; >> ERROR: could not set permissions on directory "/eq5/pwbbench": Permission >> denied > >> -- however if I start the db as user postgres with: > >> pg_ctl -D /var/lib/pgsql/data start >> and then create the tablespace it works. > > SELinux ... in the first case SELinux recognizes the postmaster as being > a daemon it ought to restrict, in the second case it just thinks it's > an interactive program. > > In general, using tablespaces under SELinux requires labeling their root > directories properly; postgresql_db_t if memory serves. > > regards, tom lane > from my notes about Selinux and postgresql under Fedora 11. try executing the following, in one line as root: semanage fcontext -a --seuser system_u -t postgresql_db_t '/eq5/pwbbench(/.*)?' and after: restorecon -R -v /eq5/pwbbench -- 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] Idle processes chewing up CPU?
Hi Tom,
Makes sense to me. Seems to be happening rarely now.
I'm not all that familiar with the open source process, is this likely to be
included in the next release version?
-Brendan
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Monday, 21 September 2009 5:25 AM
To: Brendan Hill
Cc: 'Craig Ringer'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Idle processes chewing up CPU?
"Brendan Hill" writes:
> My best interpretation is that an SSL client dirty disconnected while
> running a request. This caused an infinite loop in pq_recvbuf(), calling
> secure_read(), triggering my_sock_read() over and over. Calling
> SSL_get_error() in secure_read() returns 10045 (either connection reset,
or
> WSAEOPNOTSUPP, I'm not sure) - after this, pq_recvbuf() appears to think
> errno=EINTR has occurred, so it immediately tries again.
I wonder if this would be a good idea:
#ifdef USE_SSL
if (port->ssl)
{
int err;
rloop:
+ errno = 0;
n = SSL_read(port->ssl, ptr, len);
err = SSL_get_error(port->ssl, n);
switch (err)
{
case SSL_ERROR_NONE:
port->count += n;
break;
It looks to me like the basic issue is that pq_recvbuf is expecting
a relevant value of errno when secure_read returns -1, and there's
some path in the Windows case where errno doesn't get set, and if
it just happens to have been EINTR then we've got a loop.
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
