Re: [GENERAL] Postgres PHP error
On Mon, Dec 3, 2012 at 2:18 PM, rahul143 wrote: > Hi All > > Im receiving the following error, on a php page, conneting to postgres 7.4, > installed on Mandrake 10.0 > > Error Description: pg_connect(): Unable to connect to PostgreSQL server: > FATAL: sorry, too many clients already . > > Can anyone tell me what this means? > This error means that the maximum number of clients that can connect at the same time has been reached on server. > Is there a performance setting I need to set under the postgresql conf > file. > I have checked, and its set to 100 users. > This error strangely has only happened once. Last week Monday. Increase your maximum number of users. Then, you can still improve your application by reducing the time they hold connections on server. -- Michael Paquier http://michael.otacoo.com
Re: [GENERAL] Postgres PHP error
Richard Huxton escribió: > On 03/12/12 05:18, rahul143 wrote: > >Hi All > > > >Im receiving the following error, on a php page, conneting to postgres 7.4, > >installed on Mandrake 10.0 > > > > Others have answered your question. However... As I noted elsewhere, please ignore this guy. He's a bot re-injecting very old list email. -- Álvaro Herrerahttp://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] Postgres PHP error
On 03/12/12 05:18, rahul143 wrote: Hi All Im receiving the following error, on a php page, conneting to postgres 7.4, installed on Mandrake 10.0 Others have answered your question. However... Please make sure you have regular scheduled backups for that database. That is quite an old (9 years) version of PostgreSQL and you'll be unlikely to find many people with a similar version who can help you with problems in the event of a crash. It's probably a good idea to see if you can install the latest version from source on that machine and use it's version of pg_dump to dump the database regularly too. I'd expect to have to do a little work to move the data into an up-to-date version of PostgreSQL and it's always better to know what issues you'll have before doing it for real. -- 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] Postgres PHP error
On 12/02/2012 09:18 PM, rahul143 wrote: Hi All Im receiving the following error, on a php page, conneting to postgres 7.4, installed on Mandrake 10.0 Error Description: pg_connect(): Unable to connect to PostgreSQL server: FATAL: sorry, too many clients already . Can anyone tell me what this means? Is there a performance setting I need to set under the postgresql conf file. I have checked, and its set to 100 users. This error strangely has only happened once. Last week Monday. It means you had 100 clients connected to your PostgreSQL server which was set to a max of 100 clients thus all new attempts failed. It's not strange at all to see this only intermittently - it merely requires something that delays the processing of requests from your web-app. An easy example is a query that locks a critical table long enough for 100 connections from the web app to build up. Or your database server could have been briefly too busy to handle the incoming queries fast enough. Don't just increase the max clients setting. You need to diagnose the cause (maintenance query that shouldn't have been run during heavy use periods, inadequate hardware, app failing to close connections properly or quickly enough, bad database tuning, etc.) before prescribing a cure (procedure change, new hardware, better tuning, connection pooling, query caching, etc.). Cheers, Steve -- 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] function
On 03/12/2012 16:55, Raymond O'Donnell wrote: > On 30/11/2012 10:25, AZL . wrote: >> >> write a select query and take each row values and do some update or >> insert task postgresql 9.1, can any one give a template of function >> syntax for implementing such a task > > You're probably looking for a with...loop construct in plpgsql: > > http://www.postgresql.org/docs/9.2/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING Sorry, that should have been FOR LOOP. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] function
On 30/11/2012 10:25, AZL . wrote: > > write a select query and take each row values and do some update or > insert task postgresql 9.1, can any one give a template of function > syntax for implementing such a task You're probably looking for a with...loop construct in plpgsql: http://www.postgresql.org/docs/9.2/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING You can probably do it with a CTE also, which would avoid the overhead of calling the function. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Strange Connection Problem….
Hi, I recently modified one of my Fedora boxes by changing it's name and ip. I also disabled the internal wifi ( connection speed was dropping to 1 mb/sec ) and configured a USB wifi stick ( wow 270~300 mb/sec ). As I checked out the refurbed box networking was ok and I was able to connect to Postgresql using pgsql and some of my personal apps. However I could not connect to Postgresql from my other machines. I tried ssh from another machine to the modified machine and of course ssh complained about have a bad key ( had renamed the machine to a machine that I had given away recently and the key to the old machine was still present.) After I fixed the ssh problem I *was* able to connect to Postgresql on the refurbed machine. Do the postgresql libraries silently check to see if there is a ssh 'footprint' available for a target machine and reject the connection attempt if they do not match? 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] High SYS CPU - need advise
Merlin Moncure escribió: > Didn't we just discuss this exact problem on the identically named > thread? > http://postgresql.1045698.n5.nabble.com/High-SYS-CPU-need-advise-td5732045.html Ignore this guy. It's a bot reinjecting old messages, or something like that, probably because of some bug in mail list scrubbing software. My impression is that it's eventually going to publish every email on a blog somewhere, or something like that. -- Álvaro Herrerahttp://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] Exception Handling in C-Language Functions?
On Sun, Dec 2, 2012 at 11:18 PM, rahul143 wrote:
> I have the created a C-Language function (code is below). Now, I
> wonder: How do I handle exceptions, for example if malloc cannot assign
> the necessary memory? Do "palloc" and "pfree" handle such a case
> cleanly? Should I simply use an "assert"?
>
> #include "postgres.h"
> #include
> #include
> #include "fmgr.h"
> #include "libinn.h"
>
> PG_FUNCTION_INFO_V1(ffiinews_uwildmat);
>
> /* Wrapper for INN's function uwildmat. Needs parameters in UTF-8. */
> Datum ffiinews_uwildmat(PG_FUNCTION_ARGS) {
> VarChar *text = PG_GETARG_VARCHAR_P(0);
> VarChar *pattern = PG_GETARG_VARCHAR_P(1);
> int text_len = VARSIZE(text)-VARHDRSZ;
> int pattern_len = VARSIZE(pattern)-VARHDRSZ;
> char *tmp_text = (char *)malloc(text_len+1);
> if (tmp_text == NULL)
> ; /* What now? */
> char *tmp_pattern = (char *)malloc(pattern_len+1);
> if (tmp_pattern == NULL)
> ; /* What now? */
> strncpy(tmp_text, VARDATA(text), text_len);
> tmp_text[text_len] = '\0';
> strncpy(tmp_pattern, VARDATA(pattern), pattern_len);
> tmp_pattern[pattern_len] = '\0';
> bool matches = uwildmat(tmp_text, tmp_pattern);
yes, you should always use database memory api: palloc/pfree and if
necessary memory context switching. memory allocation error then
raises database exception. any situation that raises an exception or
other critical needs to be caught and rethrown as database exception
(ereport, etc).
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 Sun, Dec 2, 2012 at 9:08 AM, rahul143 wrote: > Hello everyone, > > I'm seeking help in diagnosing / figuring out the issue that we have with > our DB server: > > Under some (relatively non-heavy) load: 300...400 TPS, every 10-30 seconds > server drops into high cpu system usage (90%+ SYSTEM across all CPUs - it's > pure SYS cpu, i.e. it's not io wait, not irq, not user). Postgresql is > taking 10-15% at the same time. Those periods would last from few seconds, > to minutes or until Postgresql is restarted. Needless to say that system is > barely responsive, with load average hitting over 100. We have mostly select > statements (joins across few tables), using indexes and resulting in a small > number of records returned. Should number of requests per second coming drop > a bit, server does not fall into those HIGH-SYS-CPU periods. It all seems > like postgres runs out of some resources or fighting for some locks and that > causing kernel to go into la-la land trying to manage it. > > > So far we've checked: > - disk and nic delays / errors / utilization > - WAL files (created rarely) > - tables are vacuumed OK. periods of high SYS not tied to vacuum process. > - kernel resources utilization (sufficient FS handles, shared MEM/SEM, VM) > - increased log level, but nothing suspicious/different (to me) is reported > there during periods of high sys-cpu > - ran pgbench (could not reproduce the issue, even though it was producing > over 40,000 TPS for prolonged period of time) > > Basically, our symptoms are exactly as was reported here over a year ago > (though for postgres 8.3, we ran 9.1): > http://archives.postgresql.org/pgsql-general/2011-10/msg00998.php > > I will be grateful for any ideas helping to resolve or diagnose this > problem. Didn't we just discuss this exact problem on the identically named thread? http://postgresql.1045698.n5.nabble.com/High-SYS-CPU-need-advise-td5732045.html If you're the same poster, it's good to reference the thread and any conclusions made in order to save everyone's time. As at happens, I have been working an angle that may help solve this problem. Are you willing/able to run patched postgres and what's your tolerance for risk? 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] query performance, though it was timestamps,maybe just table size?
On Sun, Dec 2, 2012 at 12:44 AM, Jeff Janes wrote: > Could you do it for the recursive > SQL (the one inside the function) like you had previously done for the > regular explain? > > Cheers, > > Jeff > Here they are: for the 65 million row table: "Index Scan using ctn_source on massive (cost=0.00..189.38 rows=1 width=28) (actual time=85.802..85.806 rows=1 loops=1)" " Index Cond: (customer_id = ::bigint)" " Filter: (dateof <@ '["2012-07-03 14:00:00","2012-07-10 14:00:00"]'::tsrange)" " Buffers: shared read=6" "Total runtime: 85.891 ms" for the 30 million row table: "Index Scan using ctn_dateof on massive (cost=0.00..80.24 rows=1 width=24) (actual time=0.018..0.020 rows=1 loops=1)" " Index Cond: (customer_id = ::bigint)" " Filter: (dateof <@ '[2012-07-03,2012-07-11)':: daterange)" " Buffers: shared hit=5" "Total runtime: 0.046 ms" Thank you. On Sun, Dec 2, 2012 at 12:44 AM, Jeff Janes wrote: > On Fri, Nov 30, 2012 at 12:22 PM, Henry Drexler > wrote: > > On Fri, Nov 30, 2012 at 1:42 PM, Jeff Janes > wrote: > >> > >> Can you report the EXPLAIN (ANALYZE, BUFFERS) instead? > > > > > > Thanks, here they are: > > > > for the approx 65 million row approx 50 min version: > > > > EXPLAIN (ANALYZE, BUFFERS) > > select > > massive_expansion(ctn,the_range) > > from > > critical_visitors; > > > > "Seq Scan on critical_visitors (cost=0.00..168722.28 rows=628778 > width=40) > > (actual time=0.655..3003921.066 rows=628778 loops=1)" > > " Buffers: shared hit=4513040 read=1591722 dirtied=5234 written=10" > > "Total runtime: 3004478.053 ms" > > > > > > for the approx 30 million row approx 4 min version: > > > > EXPLAIN (ANALYZE, BUFFERS) > > select > > massive_expansion(ctn,the_range) > > from > > critical_visitors; > > > > "Seq Scan on critical_visitors (cost=0.00..746587.90 rows=2782315 > width=40) > > (actual time=393.001..277108.379 rows=2782315 loops=1)" > > " Buffers: shared hit=26370078 read=400301 dirtied=33772 written=1030" > > "Total runtime: 278988.544 ms" > > > > I can't much sense out of those. Could you do it for the recursive > SQL (the one inside the function) like you had previously done for the > regular explain? > > Cheers, > > Jeff >
Re: [GENERAL] query performance, though it was timestamps,maybe just table size?
On Sun, Dec 2, 2012 at 12:44 AM, Jeff Janes wrote: > Could you do it for the recursive > SQL (the one inside the function) like you had previously done for the > regular explain? > > Cheers, > > Jeff > Here they are: for the 65 million row table: "Index Scan using ctn_source on massive (cost=0.00..189.38 rows=1 width=28) (actual time=85.802..85.806 rows=1 loops=1)" " Index Cond: (ctn = 1302050134::bigint)" " Filter: (dateof <@ '["2012-07-03 14:00:00","2012-07-10 14:00:00"]'::tsrange)" " Buffers: shared read=6" "Total runtime: 85.891 ms" for the 30 million row table: "Index Scan using ctn_dateof on massive (cost=0.00..80.24 rows=1 width=24) (actual time=0.018..0.020 rows=1 loops=1)" " Index Cond: (ctn = 1302050134::bigint)" " Filter: (dateof <@ '[2012-07-03,2012-07-11)'::daterange)" " Buffers: shared hit=5" "Total runtime: 0.046 ms" Thank you.
Re: MODERATOR WARNING Re: [GENERAL] Exception Handling in C-Language Functions?
OK :)
Is there a way to do this automatically?
Cheers,
David.
On Mon, Dec 03, 2012 at 03:14:54AM -0300, Alvaro Herrera wrote:
> MODERATOR WARNING
>
> I noticed that this guy Rahul seems to be reinjecting old list emails
> somehow. Please don't approve anything coming from him. Observe this
> example:
>
> http://postgresql.1045698.n5.nabble.com/Re-GENERAL-MS-Access-and-Stored-procedures-td1843848.html
> http://postgresql.1045698.n5.nabble.com/Re-GENERAL-MS-Access-and-Stored-procedures-td5734652.html
> The original message was posted in 2005!
>
> The message here is this one (also in 2005):
> http://postgresql.1045698.n5.nabble.com/GENERAL-Exception-Handling-in-C-Language-Functions-td1843896.html
>
> I have no idea what's going on. Maybe it's something to do with Nabble.
> There are others pending moderation in pgsql-admin and pgsql-hackers too.
>
> rahul143 wrote:
> > I have the created a C-Language function (code is below). Now, I
> > wonder: How do I handle exceptions, for example if malloc cannot assign
> > the necessary memory? Do "palloc" and "pfree" handle such a case
> > cleanly? Should I simply use an "assert"?
> >
> > #include "postgres.h"
> > #include
> > #include
> > #include "fmgr.h"
> > #include "libinn.h"
> >
> > PG_FUNCTION_INFO_V1(ffiinews_uwildmat);
> >
> > /* Wrapper for INN's function uwildmat. Needs parameters in UTF-8. */
> > Datum ffiinews_uwildmat(PG_FUNCTION_ARGS) {
> > VarChar *text = PG_GETARG_VARCHAR_P(0);
> > VarChar *pattern = PG_GETARG_VARCHAR_P(1);
> > int text_len = VARSIZE(text)-VARHDRSZ;
> > int pattern_len = VARSIZE(pattern)-VARHDRSZ;
> > char *tmp_text = (char *)malloc(text_len+1);
> > if (tmp_text == NULL)
> > ; /* What now? */
> > char *tmp_pattern = (char *)malloc(pattern_len+1);
> > if (tmp_pattern == NULL)
> > ; /* What now? */
> > strncpy(tmp_text, VARDATA(text), text_len);
> > tmp_text[text_len] = '\0';
> > strncpy(tmp_pattern, VARDATA(pattern), pattern_len);
> > tmp_pattern[pattern_len] = '\0';
> > bool matches = uwildmat(tmp_text, tmp_pattern);
> >
> >
> >
> > -
> >
> >
> >
> >
> > --
> > View this message in context:
> > http://postgresql.1045698.n5.nabble.com/GENERAL-Exception-Handling-in-C-Language-Functions-tp5734656.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
>
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
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] guids / bytea and index use ?
rahul143 wrote: > We are currently using a 32byte varchar for our primary keys. We tried to > reduce this down to 16 bytes but varchar didn’t seem to store this > correctly. I’d like to use bytea instead so we could use 16bytes, but are > indexes used properly ? Does anyone have any other suggestions on how to > store guids ? We don’t have any experience in recompiling the postgres > source code etc. http://www.postgresql.org/docs/9.2/static/datatype-uuid.html You should use this data type. In old versions of PostgreSQL, a bytea should do. I am not sure what you mean with "used properly". As long as you search for equality on the field, the index would be eligible. 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] Postgres PHP error
rahul143 wrote: > Im receiving the following error, on a php page, conneting to postgres 7.4, > installed on Mandrake 10.0 > > Error Description: pg_connect(): Unable to connect to PostgreSQL server: > FATAL: sorry, too many clients already . > > Can anyone tell me what this means? > Is there a performance setting I need to set under the postgresql conf file. > I have checked, and its set to 100 users. > This error strangely has only happened once. Last week Monday. The parameter is max_connections, and it seems that last week Monday you had exhausted that limit. You should try to figure out what caused the problem rather than indiscriminately raising max_connections. You could turn on log_connections, that would help finding the cause. I guess I don't have to tell you that you should upgrade. 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] execute if statement
On 12/1/2012 11:59 AM, Peter Kroon wrote: I do not wish to create a function for each query I have. query's aren't IF statements. SELECT stuff FROM table WHERE conditions ;<= thats a query. -- 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] execute if statement
On 2012-12-01, Peter Kroon wrote: > --f46d043be1f4bd2dec04cfcfbd6a > Content-Type: text/plain; charset=ISO-8859-1 > > M... > > How do I execute dynamic sql that starts with an if statement. "if" is not SQL. > I'm converting mssql code to pgsql. probably best to rewrite at a higher level. -- ⚂⚃ 100% natural -- 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] Bug in postgres 9.2 installation: pg_archivecleanup: command not found
On 12/3/2012 12:07 AM, Guillaume Lelarge wrote: This is a contrib program, so you need to install the contrib package on your system. very odd that an "apt-get install postgresql-9.2" would have a dependency on a contrib module. -- 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] Bug in postgres 9.2 installation: pg_archivecleanup: command not found
On Sun, 2012-12-02 at 08:09 -0800, rahul143 wrote: > I have installed postgres 9.2 on ubuntu 12.04 like this: > > sudo add-apt-repository ppa:pitti/postgresql > sudo apt-get update > sudo apt-get install postgresql-9.2 > > However after this, i get the following error: > > 2012-11-12 17:49:38 GMT WARNING: archive_cleanup_command "pg_archivecleanup > /var/lib/postgresql/9.2/wals/ %r": return code 32512 > sh: 1: pg_archivecleanup: not found > > The same happens if I try directly as root: > > root@slave-db ~ # pg_archivecleanup > pg_archivecleanup: command not found > > What can I do about it? > This is a contrib program, so you need to install the contrib package on your system. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
