Re: [GENERAL] Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?
On Mon, Sep 24, 2007 at 12:50:22PM +0530, Anoo Sivadasan Pillai wrote: > I am using "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC > gcc.exe (GCC) 3.4.2 (mingw-special)" on Windows 2003 server > > While I am trying to update a prmary key It is failing with the > following message "ERROR: duplicate key violates unique constraint > "master_pkey" " > > Can anybody explain why this happens so? Sending the script that I > tried. > > CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ; > > INSERT INTO master VALUES ( 1, 'm1' ) ; > > INSERT INTO master VALUES ( 2, 'm2' ) ; > > UPDATE master SET m1 = m1 + 1; One way to do this is with Postgres's UPDATE ... FROM construct: CREATE TABLE foo(i INTEGER PRIMARY KEY); INSERT INTO foo(i) VALUES (1), (2), (3), (4), (5); UPDATE foo SET i=foo.i+1 FROM (SELECT i FROM foo ORDER BY i DESC) f WHERE f.i = foo.i; While specific to Postgres, this technique avoids a lot of messing around with boundary conditions :) Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] NZDT Question
On 9/26/07, Tom Lane <[EMAIL PROTECTED]> wrote: > > regression=# set timezone to 'Pacific/Auckland'; > SET > regression=# select '2007-10-01 06:12:40.097244+12'::timestamptz; > timestamptz > --- > 2007-10-01 07:12:40.097244+13 > (1 row) > > What have you got "timezone" set to? Did you remember to restart > the postmaster after updating? Aha! Thanks... when I ran set timezone, it did not recognize 'Pacific/Auckland'. I hadn't installed share/timezone in correct location. Works great now! Cheers, Mike ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] NZDT Question
"Mike C" <[EMAIL PROTECTED]> writes: > I've just upgraded from Postgres 8.1.0 to 8.1.10 to update the NZ > timezone changes, but it doesn't seem to make a difference (I've also > patched linux). Ideas? Hm, works for me: Welcome to psql 8.1.10, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit regression=# set timezone to 'Pacific/Auckland'; SET regression=# select '2007-10-01 06:12:40.097244+12'::timestamptz; timestamptz --- 2007-10-01 07:12:40.097244+13 (1 row) What have you got "timezone" set to? Did you remember to restart the postmaster after updating? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] subquery/alias question
On Sep 25, 2007, at 21:44 , Tom Lane wrote: ... which in English means we just do the calculation once ... As always, thanks, Tom, for the explanation (and Alvaro, who probably already knew this :)) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] NZDT Question
Hi, I've just upgraded from Postgres 8.1.0 to 8.1.10 to update the NZ timezone changes, but it doesn't seem to make a difference (I've also patched linux). Ideas? Below is how I'm testing (artificially set time into future): -bash-3.1$ date Mon Oct 1 07:12:36 NZDT 2007 -bash-3.1$ psql test Welcome to psql 8.1.10, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit business=# select now(); now --- 2007-10-01 06:12:40.097244+12 (1 row) business=# select current_timestamp; now --- 2007-10-01 06:12:43.697349+12 (1 row) Note the 1 hour difference between postgres and date. Regards, Mike ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] subquery/alias question
Michael Glaesemann <[EMAIL PROTECTED]> writes: > I believe you'd have to write it like > select dom_id, dom_name, count(usr_dom_id) as usr_count >from domains >join users on (usr_dom_id = dom_id) >having count(usr_dom_id) > 0 >order by dom_name; > I don't know how the performance would compare. I think the backend > is smart enough to know it doesn't need to perform two seq scans to > calculate count(usr_dom_id), but I wasn't sure. It has been smart enough for a few years now --- don't recall when exactly, but nodeAgg.c quoth * Perform lookups of aggregate function info, and initialize the * unchanging fields of the per-agg data. We also detect duplicate * aggregates (for example, "SELECT sum(x) ... HAVING sum(x) > 0"). When * duplicates are detected, we only make an AggStatePerAgg struct for the * first one. The clones are simply pointed at the same result entry by * giving them duplicate aggno values. ... which in English means we just do the calculation once ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] subquery/alias question
On Sep 25, 2007, at 17:30 , Alvaro Herrera wrote: Michael Glaesemann wrote: select dom_id, dom_name, usr_count from domains natural join (select usr_dom_id as dom_id, count(usr_dom_id) as usr_count from users) u where usr_count > 0 order by dom_name; Maybe the usr_count should be tested in a HAVING clause instead of WHERE? And put the count(*) in the result list instead of a subselect. That feels more natural to me anyway. I believe you'd have to write it like select dom_id, dom_name, count(usr_dom_id) as usr_count from domains join users on (usr_dom_id = dom_id) having count(usr_dom_id) > 0 order by dom_name; I don't know how the performance would compare. I think the backend is smart enough to know it doesn't need to perform two seq scans to calculate count(usr_dom_id), but I wasn't sure. Madison, how do the two queries compare with explain analyze? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] subquery/alias question
Michael Glaesemann wrote: > > On Sep 25, 2007, at 16:59 , Madison Kelly wrote: > >> SELECT >> d.dom_id, >> d.dom_name, >> (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) >> AS >> usr_count >> FROM >> domains d >> WHERE >> (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) > 0 >> ORDER BY d.dom_name ASC; > > Why not just use a join? Something like this would work, I should think: > > select dom_id, >dom_name, >usr_count > from domains > natural join (select usr_dom_id as dom_id, >count(usr_dom_id) as usr_count > from users) u > where usr_count > 0 > order by dom_name; Maybe the usr_count should be tested in a HAVING clause instead of WHERE? And put the count(*) in the result list instead of a subselect. That feels more natural to me anyway. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] lowering impact of checkpoints
On Tue, 25 Sep 2007, Gregory Stark wrote: I'm surprised you don't start by suggesting lowering bgwriter_delay for a busy dedicated system. Does it cause too much wasted cpu work in the "all" cycle in 8.2? I've just found it easier to sort through this class of problem by getting the maxpages parameters into at least the 200-500 range before even thinking about lowering the delay. There may very well be a different way to approach this problem by making the delay more of a primary tunable. Certainly there's potentially an advantage to lowering the delay in that it gets writes trickling out to disk more regularly. I also wonder if it doesn't make more sense in 8.2 if your goal is to avoid drop-outs to just give up on the lru cycle entirely and set the delay to something like 60s and the all_percent to 100. There are some workloads where flushing the buffers that haven't been used recently in the lru cycle is more useful than what the all scan does; it's hard to figure out whether your system is such a case or not in 8.2 though. In addition, the main problem with using a longer cycle/higher percentage is that the way some operating systems buffer writes favors writing small blocks more frequently. In the Linux case there are situations where writes sit there for a full 30 seconds so getting the physical disk started earlier is a benefit. I'd be concerned that all_percent=100 would end up generating something close to a checkpoint I/O spike every cycle, and that the background writer waiting for that big write to finish might delay checkpoint requests from processing in a timely fashion. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] subquery/alias question
On Sep 25, 2007, at 16:59 , Madison Kelly wrote: SELECT d.dom_id, d.dom_name, (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) AS usr_count FROM domains d WHERE (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) > 0 ORDER BY d.dom_name ASC; Why not just use a join? Something like this would work, I should think: select dom_id, dom_name, usr_count from domains natural join (select usr_dom_id as dom_id, count(usr_dom_id) as usr_count from users) u where usr_count > 0 order by dom_name; Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Can't connect (2 dbs) or login (2 others)
On 9/25/07, Morris Goldstein <[EMAIL PROTECTED]> wrote: > On 9/25/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > But since it hit all of your machines, and at about the same time, I > > tend to think that someone did something to these machines that caused > > this issue, and it's not a 7.4.x problem. > > I'm sure it is pilot error, and we're still trying to figure out exactly > which pilot and what error. > > > Did you update / upgrade kernels, device drivers, hardware, etc... > > What is common between all these systems besides postgresql? Was > > there a power outage? All machines had the same admin one day who had > > a brain cramp and did something stupid? > > This occurred as part of an upgrade -- new OS, kernel, drivers. > > > Simply put, we need more info on how this happened. > > > > We've recovered. There is root cause analysis going on. The question is > whether I can use an argument about 8.0 vs. 7.4 reliability from this fiasco > to help us get to 8.0. > 8.0 actually is more reliable than 7.4, I assume. Well,if you're going to upgrade look at 8.1 as a minimum, 8.2 if possible. I can't say for sure that 8.0, 8.1 or 8.2 would have handled this much better, but having something like Point In Time Replication or other forms of replication readily available could have certainly limited your downtime in this instance. I would highly recommend 8.2.5 as your upgrade target. Look over the release notes for 8.0, 8.1 and 8.2. I will say that 8.2 is noticeably faster than 7.4, and is at least as stable for me. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] subquery/alias question
Hi all, I've read 7.2.1.3 (as short as it is) in the PgSQL docs, but don't see what I am doing wrong... Maybe you can help? I've got a query; SELECT d.dom_id, d.dom_name, (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) AS usr_count FROM domains d ORDER BY d.dom_name ASC; Where 'usr_count' returns the number of entries in 'users' that point to a given entry in 'domains'. Pretty straight forward so far. The trouble is: SELECT d.dom_id, d.dom_name, (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) AS usr_count FROM domains d WHERE usr_count > 0 ORDER BY d.dom_name ASC; Causes the error: ERROR: column "usr_count" does not exist It works if I use: SELECT d.dom_id, d.dom_name, (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) AS usr_count FROM domains d WHERE (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) > 0 ORDER BY d.dom_name ASC; This seems terribly inefficient (and ugly), and I can't see why the results from 'usr_count' can't be counted... I can use 'usr_count' to sort the results... Thanks all! Madi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_restore - invalid file problem
On 9/25/07, Ralph Smith <[EMAIL PROTECTED]> wrote: > Where should I start looking for sources of the error? See: http://www.postgresql.org/docs/7.4/static/backup.html#BACKUP-DUMP-ALL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Can't connect (2 dbs) or login (2 others)
"Morris Goldstein" <[EMAIL PROTECTED]> writes: > We've recovered. There is root cause analysis going on. The question is > whether I can use an argument about 8.0 vs. 7.4 reliability from this fiasco > to help us get to 8.0. > 8.0 actually is more reliable than 7.4, I assume. I don't know that I'd make that argument for those two versions. If you compare 7.4.x to 8.2.x, then yes I'd say the later version is noticeably more stable. If nothing else, its ability to defend itself against transaction wraparound is a big leg up over 7.4. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] pg_restore - invalid file problem
In preparing to upgrade, (a long story), of our version 7.4 database cluster, I'm trying pg_restore using the file from pg_dumpall, also version 7.4 into a virgin install of PostgreSQL 7.4 on Ubuntu. Postmaster is up and running (c/o ps -ef) The error I get: pg_restore: [archiver] input file does not appear to be a valid archive Where should I start looking for sources of the error? Thank you! Ralph
Re: [GENERAL] cascade and restrict options to alter domain drop constraint
Marc Munro <[EMAIL PROTECTED]> writes: > I am puzzled by the cascade and restrict options to the alter domain > drop constraint command. They don't do anything. I think they were put in because we have this meme that the SQL spec requires RESTRICT/CASCADE options on every type of DROP, but so far as I can see, SQL99 has no such option for ALTER DOMAIN DROP CONSTRAINT. So maybe it's a bug that we take the options for it. Or maybe it's better to leave them there, in case people are expecting them to be there on the same reasoning. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Manually clearing "database "foo" is being accessed by other users"
Steve Crawford wrote: > Sysadmin wrote: >> Hi all, >> >> I'm finding that routinely when I try to reload a database on a server >> where I know there are no connections to a given DB I get the error: >> >> $ dropdb foo && createdb foo -O bar && psql foo -f /path/to/db.out >> dropdb: database removal failed: ERROR: database "foo" is being >> accessed by other users >> >> This means I need to restart the postmaster, but the server contains >> many DBs, of which some may actually be in use. How can I tell postgres >> that the database 'foo' should be marked as not in use / clear or drop >> any open connections / etc? > > If you connect to "foo" and run "select * from pg_stat_activity;" what > does it show? > > Have you tried changing pg_hba.conf (and reloading PG and waiting for > current connections to terminate of course) to deny access to foo before > running your commands? > > Is there a pg_dumpall (or anything else that might access that db at the > PG superuser level) running at the time? > > Cheers, > Steve Thanks, Steve! Turns out a daemon was indeed still connected to the database... that command pointed that out, and I assure you I gave myself a decent smack in the forehead for it. :) Madi ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Can't connect (2 dbs) or login (2 others)
On 9/25/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > But since it hit all of your machines, and at about the same time, I > tend to think that someone did something to these machines that caused > this issue, and it's not a 7.4.x problem. I'm sure it is pilot error, and we're still trying to figure out exactly which pilot and what error. Did you update / upgrade kernels, device drivers, hardware, etc... > What is common between all these systems besides postgresql? Was > there a power outage? All machines had the same admin one day who had > a brain cramp and did something stupid? This occurred as part of an upgrade -- new OS, kernel, drivers. Simply put, we need more info on how this happened. > We've recovered. There is root cause analysis going on. The question is whether I can use an argument about 8.0 vs. 7.4 reliability from this fiasco to help us get to 8.0. 8.0 actually is more reliable than 7.4, I assume. Morris
Re: [GENERAL] lowering impact of checkpoints
"Greg Smith" <[EMAIL PROTECTED]> writes: > On Tue, 25 Sep 2007, hubert depesz lubaczewski wrote: > >> name | setting | unit >> ---+---+ >> bgwriter_all_maxpages | 5 | >> bgwriter_all_percent | 0.333 | [null] >> bgwriter_delay| 200 | ms >> bgwriter_lru_maxpages | 5 | >> bgwriter_lru_percent | 1 | [null] > > The background writer can help smooth out checkpoints a bit in 8.2.4, Not to disagree with anything Greg says here but you should keep in mind that all of these parameters will have to be retuned from scratch with 8.3 which has been optimized somewhat for just this problem. > If these are all positive changes, you might even want to increase these > further; potentially you could double all of the above and still not have the > settings high enough, and if that's the case you may have to adjust > bgwriter_delay downward. I'm surprised you don't start by suggesting lowering bgwriter_delay for a busy dedicated system. Does it cause too much wasted cpu work in the "all" cycle in 8.2? I also wonder if it doesn't make more sense in 8.2 if your goal is to avoid drop-outs to just give up on the lru cycle entirely and set the delay to something like 60s and the all_percent to 100. Effectively saying to flush all dirty buffers once a minute to smooth the checkpoint. I haven't tried doing anything like that though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Can't connect (2 dbs) or login (2 others)
On 9/25/07, Morris Goldstein <[EMAIL PROTECTED]> wrote: > Thanks for your help with pg_resetxlog. It recovered all of our databases, > and it looks like we got lucky in that no updates were lost. > > We are deciding on the goals for our next release, and one of the issues on > the table is an upgrade to postgres 8. Can you comment on the improvements > in performance and especially reliability over postgres 7.4? In particular, > if the risk of pg_xlog corruption is lower, that would support the move to > postgres 8 in a particularly effective way, while the pain of the recent > episode is still vivid. My experience has been that 7.4 was rock solid stable. But our uses may not be similar to yours. I would question how you managed to get your servers into this state. if one server out of four had this problem I would have said to examine your system to see if it has bad memory, CPU, or drive arrays. But since it hit all of your machines, and at about the same time, I tend to think that someone did something to these machines that caused this issue, and it's not a 7.4.x problem. Did you update / upgrade kernels, device drivers, hardware, etc... What is common between all these systems besides postgresql? Was there a power outage? All machines had the same admin one day who had a brain cramp and did something stupid? Simply put, we need more info on how this happened. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] cascade and restrict options to alter domain drop constraint
I am puzzled by the cascade and restrict options to the alter domain drop constraint command. I do not see how a dropping a check constraint should cascade to anything, or indeed be restricted by anything. My reasoning is simple: if I drop a check constraint on a domain, no data should be affected, so no dependant objects should be affected. Could someone please explain what I am missing? Thanks in advance __ Marc signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Can't connect (2 dbs) or login (2 others)
Thanks for your help with pg_resetxlog. It recovered all of our databases, and it looks like we got lucky in that no updates were lost. We are deciding on the goals for our next release, and one of the issues on the table is an upgrade to postgres 8. Can you comment on the improvements in performance and especially reliability over postgres 7.4? In particular, if the risk of pg_xlog corruption is lower, that would support the move to postgres 8 in a particularly effective way, while the pain of the recent episode is still vivid. Morris
Re: [GENERAL] Yum Repository for Postgres 8.2.5
Hi, On Tue, 2007-09-25 at 12:39 -0400, David Siebert wrote: > I am using CentOS 5 and would like to update to 8.2.5. Does anyone > know of a repository that carries it? RedHat 5 should work as well. I am about to create a yum repository for PGDG RPMs. The infrastructure is ready, but I have some other priorities nowadays. I think it will be available next weekend or so. Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
[GENERAL] Yum Repository for Postgres 8.2.5
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am using CentOS 5 and would like to update to 8.2.5. Does anyone know of a repository that carries it? RedHat 5 should work as well. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3-nr1 (Windows XP) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQEVAwUBRvk5peLnn4qAcbUGAQKM0gf8CmRQF0YllkW7y7bjA0ybbki29rRdGhaD /drowClY5EHtrU7ClF6aQ+gLk7LR4cXAs4AFGLtTGJSIncL9W9OiivzgryWzfYCQ H6K21TQj+IFC5pdhUZgBLQMTLU7LFSpT4IQztR59o+izYdRZJWh7OH2s8JdLMHdE StEc/H3igvDmpE3l1NlS6BBAHRYH/tCR9FCb+IODpZBSO5Xppgv+6Hk+pEZ3ncfJ ONaIkNfCsxhhDtE02ih3fmp7Qusd2IysL+EWEl6QqLqwGuHEY9fseMn1636SK4YF qsSX1K+6Bhtm5D5CmjtxMw1nw3s2DoU1gjpdAw8PH6chAw9T0pwR5Q== =LPq9 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] lowering impact of checkpoints
On Tue, 25 Sep 2007, hubert depesz lubaczewski wrote: name | setting | unit ---+---+ bgwriter_all_maxpages | 5 | bgwriter_all_percent | 0.333 | [null] bgwriter_delay| 200 | ms bgwriter_lru_maxpages | 5 | bgwriter_lru_percent | 1 | [null] The background writer can help smooth out checkpoints a bit in 8.2.4, but these settings are barely doing anything; they aren't even in the right ballpark for a system that's doing 600-2000 TPS. You need to be careful here because making the background writer run too often can result in things running slower all the time, and in some cases it can even make the checkpoints worse. I would suggest changing these parameters one at a time, in the following order, and see what happens to the number of slow queries at checkpoint time after each change: bgwriter_lru_maxpages: increase to 500 bgwriter_lru_percent: increase to 5 bgwriter_all_maxpage: increase to 250 bgwriter_all_percent: increase to 2 If these are all positive changes, you might even want to increase these further; potentially you could double all of the above and still not have the settings high enough, and if that's the case you may have to adjust bgwriter_delay downward. Here is a message I'd suggest reading carefully from someone who went through the process you're starting now and ended up with a much more aggressive set of settings even than these: http://archives.postgresql.org/pgsql-hackers/2006-12/msg00383.php As Kevin suggests there, one thing that varies a bit based on the exact work you're doing is the ratio between how heavily you balance the all vs. lru weighting. His final settings use the all writer a bit more heavily than I'd normally recommend, but with his particular system that worked out well. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] set returning functions.
On 9/25/07, Pavel Stehule <[EMAIL PROTECTED]> wrote: > 2007/9/25, Scott Marlowe <[EMAIL PROTECTED]>: > > On 9/24/07, Pavel Stehule <[EMAIL PROTECTED]> wrote: > > > 2007/9/25, Rhys Stewart <[EMAIL PROTECTED]>: > > > > yes indeed. thats exactly it scott!!! > > > > > > > > On 9/24/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > > > > On 9/24/07, Pavel Stehule <[EMAIL PROTECTED]> wrote: > > > > > > Hello Rhys > > > > > > > > > > > > its not mystery > > > > > > > > > > Yeah, but I think he wanted to be able to make his own function he > > > > > could call like: > > > > > > > > > > select myfunc(10); > > > > > > > > > > > Then you have to use sql language > > > > > > create or replace function mysrf(int) > > > returns setof integer as $$ > > > select i from generate_series(1, $1) g(i); > > > $$ language sql; > > > > Do you HAVE to use sql plsql to get this to work? I thought that all > > pl languages worked like this with pgsql. > > > > if you can call SRF function in normal context (like SELECT srf()), > you have to use SQL language You can also do this in a C function also. you can always wrap your plpgsql function in an sql function and sneak around the problem. That said, it would be extremely nice if pl/pgsql functions could work this way. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Porblems migrating a server.
On Sep 25, 2007, at 10:37 , Alvaro Herrera wrote: Michael Glaesemann wrote: You might need to use adddepend, which is a contrib module included in 8.1 (not 8.2 AIUI). (Perhaps 8.2 includes this functionality in core? You can probably check the release notes for 8.2 for details.) Not in core -- the code was pushed to pgfoundry. Ah, that's right. Thanks, Alvaro. Here's a link: http://pgfoundry.org/projects/adddepends/ Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Porblems migrating a server.
Michael Glaesemann wrote: > > On Sep 25, 2007, at 9:00 , David Siebert wrote: >> Any suggestions on what the command line should look like? > > You might need to use adddepend, which is a contrib module included in 8.1 > (not 8.2 AIUI). (Perhaps 8.2 includes this functionality in core? You can > probably check the release notes for 8.2 for details.) Not in core -- the code was pushed to pgfoundry. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Porblems migrating a server.
On Sep 25, 2007, at 9:00 , David Siebert wrote: I have a very old postgres server that I am trying to move the data off of. It is running 7.1 and has been trouble free for 6 plus years. I am trying to move the data base off to a server running 8.1. In my opinion you should look at 8.2, not 8.1. And 8.3 is on the horizon :) I have managed to back up the data using PG_Dump using like this. " pg_dump -b -Fc -h stan.someplace.com -u phone >phone.data" Be sure to use the 8.2 pg_dump, not the 7.1 pg_dump, against the 7.1 database. But I have had no luck getting PG_Restore to restore the data. It would be helpful if you provided the exact pg_restore command you're using (again, it should be the 8.2 version of pg_restore) and the exact error you're getting. Otherwise it's difficult for us to know what's going on. Any suggestions on what the command line should look like? You might need to use adddepend, which is a contrib module included in 8.1 (not 8.2 AIUI). (Perhaps 8.2 includes this functionality in core? You can probably check the release notes for 8.2 for details.) I really want to migrate it before the PII 266 it is running on gives up the ghost :) Not only the hardware :) 7.1 includes known data-eating bugs. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PG_DUMP not working
Dan99 escribió: > Hi, > > I found out this morning that I cannot get pg_dump to work at all on > my database. It refuses to create a dump and instead just freezes. > When using the verbose option (-v) i get the following output and then > it stops (it at one point ran for days on end before i even noticed) > > pg_dump: saving encoding > pg_dump: saving database definition > pg_dump: reading schemas > pg_dump: reading user-defined types > pg_dump: reading user-defined functions > pg_dump: reading user-defined aggregate functions > pg_dump: reading user-defined operators > pg_dump: reading user-defined operator classes > pg_dump: reading user-defined tables > > I think this problem is somehow related to a VIEW problem that I > have. I created a VIEW of semi-large tables, which did not come back > with any errors. However, when I go to view it, it never finishes > loading. Also, I cannot drop this view as this as well never > finishes. Perhaps somebody has a lock on a table or view. Try select relation::regclass, database, transaction, pid, mode, granted from pg_locks; Do you see anything related to the view you created? -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "This is a foot just waiting to be shot"(Andrew Dunstan) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] A few basic troubleshooting questions
On Sep 25, 2007, at 9:29 AM, Kevin Kempter wrote: Hi List; I have a few basic troubleshooting questions... 1) If I have autovacuum turned on, how do I know which table is being vacuumed when in pg_stat_activity I only see VACUUM? I've been using this query but it doesn't always work... is there a better way? CREATE Temp table tmp_p as SELECT procpid from pg_stat_activity where current_query = 'VACUUM' ; SELECT relname as current_vacuum_activity from pg_class where oid in ( select relation from pg_locks where pid = any (select procpid from tmp_p) ) ; 2) if I see a 'ROLLBACK' in pg_stat_activity, how can I determine what query/update/etc is being rolled back? For both 1) and 2), pg_stat_activity has more columns than just procpid. Here's a query I use to good effect for monitoring active queries: SELECT procpid, to_char((now() - query_start), 'DD HH:MI:SS') as query_time, client_addr, current_query FROM pg_stat_activity ORDER BY now() - query_start DESC 3) How do I know for sure what processes are are waiting on a specific lock ? for example I have a process that has an ungranted lock on table X. Is there an easy way via pg_locks to determine which processes are waiting on the ungranted lock on table X? Yes, read the documentation on pg_locks: http://www.postgresql.org/ docs/8.2/interactive/view-pg-locks.html. Note that there pid corresponds to procpid in pg_stat_activity. 4) How do I determine in general if the db has a memory bottleneck vs CPU bottleneck vs I/O bottleneck? I know about pg_statio, just not sure how to guage where the db is the most constrained. You will need OS tools to handle those metrics. Look into vmstat and ipcs for memory, iostat for I/O, and top for cpu. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PG_DUMP not working
On 9/18/07, Dan99 <[EMAIL PROTECTED]> wrote: > Hi, > > I found out this morning that I cannot get pg_dump to work at all on > my database. It refuses to create a dump and instead just freezes. > When using the verbose option (-v) i get the following output and then > it stops (it at one point ran for days on end before i even noticed) > > pg_dump: saving encoding > pg_dump: saving database definition > pg_dump: reading schemas > pg_dump: reading user-defined types > pg_dump: reading user-defined functions > pg_dump: reading user-defined aggregate functions > pg_dump: reading user-defined operators > pg_dump: reading user-defined operator classes > pg_dump: reading user-defined tables > > I think this problem is somehow related to a VIEW problem that I > have. I created a VIEW of semi-large tables, which did not come back > with any errors. However, when I go to view it, it never finishes > loading. Also, I cannot drop this view as this as well never > finishes. > > PGSQL version: 7.4.2 Two things. What's in the postgresql logs (if you're not logging pgsql output, then turn it on and watch it while you're running pg_dump. Update your pgsql. 7.4.2 is old in two ways. the 7.4 branch is pretty old. plan an upgrade as soon as you can get this backup to work. Secondly, pg 7.4 is up to a number near 20 now, i.e. 7.4.18. There are over two years of bug fixes you're missing, and one of them could well be the solution to your problem. Upgrading from 7.4 to 8.2 requires a dump and reload, but 7.4.2 to 7.4.18 is just an rpm -Uvh or apt-get update away ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] A few basic troubleshooting questions
Hi List; I have a few basic troubleshooting questions... 1) If I have autovacuum turned on, how do I know which table is being vacuumed when in pg_stat_activity I only see VACUUM? I've been using this query but it doesn't always work... is there a better way? CREATE Temp table tmp_p as SELECT procpid from pg_stat_activity where current_query = 'VACUUM' ; SELECT relname as current_vacuum_activity from pg_class where oid in ( select relation from pg_locks where pid = any (select procpid from tmp_p) ) ; 2) if I see a 'ROLLBACK' in pg_stat_activity, how can I determine what query/update/etc is being rolled back? 3) How do I know for sure what processes are are waiting on a specific lock ? for example I have a process that has an ungranted lock on table X. Is there an easy way via pg_locks to determine which processes are waiting on the ungranted lock on table X? 4) How do I determine in general if the db has a memory bottleneck vs CPU bottleneck vs I/O bottleneck? I know about pg_statio, just not sure how to guage where the db is the most constrained. Thanks in advance /Kevin
[GENERAL] Porblems migrating a server.
I have a very old postgres server that I am trying to move the data off of. It is running 7.1 and has been trouble free for 6 plus years. I am trying to move the data base off to a server running 8.1. I have managed to back up the data using PG_Dump using like this. " pg_dump -b -Fc -h stan.someplace.com -u phone >phone.data" But I have had no luck getting PG_Restore to restore the data. Any suggestions on what the command line should look like? Is it a problem going from 7.1 to 8.1? If so how do I get around it? The data does use some large objects for text files if that is any help. We set this up as a test system and it ran so well no one wanted to take it down. I really want to migrate it before the PII 266 it is running on gives up the ghost :) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] set returning functions.
2007/9/25, Scott Marlowe <[EMAIL PROTECTED]>: > On 9/24/07, Pavel Stehule <[EMAIL PROTECTED]> wrote: > > 2007/9/25, Rhys Stewart <[EMAIL PROTECTED]>: > > > yes indeed. thats exactly it scott!!! > > > > > > On 9/24/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > > > On 9/24/07, Pavel Stehule <[EMAIL PROTECTED]> wrote: > > > > > Hello Rhys > > > > > > > > > > its not mystery > > > > > > > > Yeah, but I think he wanted to be able to make his own function he > > > > could call like: > > > > > > > > select myfunc(10); > > > > > > > > Then you have to use sql language > > > > create or replace function mysrf(int) > > returns setof integer as $$ > > select i from generate_series(1, $1) g(i); > > $$ language sql; > > Do you HAVE to use sql plsql to get this to work? I thought that all > pl languages worked like this with pgsql. > if you can call SRF function in normal context (like SELECT srf()), you have to use SQL language Pavel ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] set returning functions.
On 9/24/07, Pavel Stehule <[EMAIL PROTECTED]> wrote: > 2007/9/25, Rhys Stewart <[EMAIL PROTECTED]>: > > yes indeed. thats exactly it scott!!! > > > > On 9/24/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > > On 9/24/07, Pavel Stehule <[EMAIL PROTECTED]> wrote: > > > > Hello Rhys > > > > > > > > its not mystery > > > > > > Yeah, but I think he wanted to be able to make his own function he > > > could call like: > > > > > > select myfunc(10); > > > > > Then you have to use sql language > > create or replace function mysrf(int) > returns setof integer as $$ > select i from generate_series(1, $1) g(i); > $$ language sql; Do you HAVE to use sql plsql to get this to work? I thought that all pl languages worked like this with pgsql. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] rules and command status question
Josh Harrison escribió: > Hello, > I have a question in the postgres document chapter 34. Rules and Command > Status. > The last paragraph of that page says that > > "The programmer can ensure that any desired INSTEAD rule is the one that > sets the command status in the second case, by giving it the alphabetically > last rule name among the active rules, so that it gets applied last." > > Can someone help me understand what this means. How can I change my INSERT > ...INSTEAD rule so that query returns the number of rows inserted ( instead > of 0 rows inserted). You can't. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] lowering impact of checkpoints
On Tue, 2007-09-25 at 11:58 +0200, hubert depesz lubaczewski wrote: > hi, > our system is handling between 600 and 2000 transactions per second. all > of them are very small, very fast. typical query runs in under 1ms. > yes - sometimes we get queries that take longer than then should get. > simple check shows that we have a very visible pattern of > every-5-minutes peak. > in the minute that there is checkpoint - we get usually 15-20 times more > queries "over 500 ms" than in other minutes. > > we are using 8.2.4 (upgrade will be soon), with these settings: > # select name, setting, unit from pg_settings where name ~* > 'bgwriter|wal|checkpoint'; > name | setting | unit > ---+---+ > bgwriter_all_maxpages | 5 | > bgwriter_all_percent | 0.333 | [null] > bgwriter_delay| 200 | ms > bgwriter_lru_maxpages | 5 | > bgwriter_lru_percent | 1 | [null] > checkpoint_segments | 32| > checkpoint_timeout| 300 | s > checkpoint_warning| 30| s > wal_buffers | 128 | 8kB > wal_sync_method | fdatasync | [null] > (10 rows) > > is there anything i can change to make it "smoother"? Sounds like bgwriter is not flushing dirty pages quickly enough, so there is still a lot of work to do at checkpoint time. You probably need to tune it. This can be a tough thing to do properly though. There are no magic values to suggest, as what will work is highly dependent on your hardware and your applications pattern of use. If possible, up the settings for bgwriter_all_percent a *little* and perhaps bgwriter_all_maxpages and see if it helps. You can change these with a reload. If you are doing this on a production system as opposed to a test system, keep a close eye on what is going on, as it is possible that you can make things worse. I would start with something like 2% for bgwriter_all_maxpages and see if that helps things out. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] rules and command status question
Hello, I have a question in the postgres document chapter 34. Rules and Command Status. The last paragraph of that page says that "The programmer can ensure that any desired INSTEAD rule is the one that sets the command status in the second case, by giving it the alphabetically last rule name among the active rules, so that it gets applied last." Can someone help me understand what this means. How can I change my INSERT ...INSTEAD rule so that query returns the number of rows inserted ( instead of 0 rows inserted). Thanks in advance Josh
Re: [GENERAL] Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?
It's normal behaviour, because after the first update it will be 2 same values
for m2 and you don't want that since you have a unique constraint for that
column. try this:
CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;
INSERT INTO master VALUES ( 1, 1 ) ;
INSERT INTO master VALUES ( 2, 3) ;
UPDATE master SET m2 = m2+1 ;
- Original Message -
From: Anoo Sivadasan Pillai
To: Ardian Xharra
Cc: pgsql-general@postgresql.org
Sent: Tuesday, September 25, 2007 12:27 AM
Subject: Re: [GENERAL] Why the ERROR: duplicate key violates unique
constraint "master_pkey" is raised? - Is this a Bug?
Hi,
I am not using any sequences, The following batch can reproduce the behaviour.
CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;
INSERT INTO master VALUES ( 1, 1 ) ;
INSERT INTO master VALUES ( 2, 2) ;
UPDATE master SET m2 = m2 + 1;
With Cheers,
Anoo S
From: Ardian Xharra [mailto:[EMAIL PROTECTED]
Sent: 25 September 2007 00:38
To: Anoo Sivadasan Pillai; pgsql-general@postgresql.org
Cc: Anoo Sivadasan Pillai
Subject: Re: [GENERAL] Why the ERROR: duplicate key violates unique
constraint "master_pkey" is raised? - Is this a Bug?
Probably you are using a sequence, and if so you need to update the value of
sequence prior to update:
SELECT setval('master_m1_seq',((SELECT id_m1 FROM master ORDER BY 1 DESC
LIMIT 1)+1));
- Original Message -
From: Anoo Sivadasan Pillai
To: pgsql-general@postgresql.org
Cc: Anoo Sivadasan Pillai
Sent: Monday, September 24, 2007 3:20 AM
Subject: [GENERAL] Why the ERROR: duplicate key violates unique constraint
"master_pkey" is raised? - Is this a Bug?
I am using "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.4.2 (mingw-special)" on Windows 2003 server
While I am trying to update a prmary key It is failing with the following
message "ERROR: duplicate key violates unique constraint "master_pkey" "
Can anybody explain why this happens so? Sending the script that I tried.
CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;
INSERT INTO master VALUES ( 1, 'm1' ) ;
INSERT INTO master VALUES ( 2, 'm2' ) ;
UPDATE master SET m1 = m1 + 1;
Update fails with the message - ERROR: duplicate key violates unique
constraint "master_pkey"
If I insert data in the reverse order it is making no problem. Is this a
Bug ?
I tried ,
TRUNCATE TABLE master;
INSERT INTO master VALUES ( 3, 'm3' ) ;
INSERT INTO master VALUES ( 2, 'm2' ) ;
UPDATE master SET m1 = m1 + 1;
It works perfectly.
Anoo S
Visit our Website at www.rmesi.co.in
This message is confidential. You should not copy it or disclose its contents
to anyone. You may use and apply the information for the intended purpose only.
Internet communications are not secure; therefore, RMESI does not accept legal
responsibility for the contents of this message. Any views or opinions
presented are those of the author only and not of RMESI. If this email has come
to you in error, please delete it, along with any attachments. Please note that
RMESI may intercept incoming and outgoing email communications.
Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging
to RMESI. Where the email and any attachments do contain information of a
confidential nature, including without limitation information relating to trade
secrets, special terms or prices these shall be deemed for the purpose of the
Freedom of Information Act 2000 as information provided in confidence by RMESI
and the disclosure of which would be prejudicial to RMESI's commercial
interests.
This email has been scanned for viruses by Trend ScanMail.
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.488 / Virus Database: 269.13.30/1030 - Release Date: 25/09/2007
08:02
Fw: [GENERAL] PgpoolAdmin installation
- Original Message - From: Ashish Karalkar To: Devrim GÜNDÜZ Sent: Tuesday, September 25, 2007 4:30 PM Subject: Re: [GENERAL] PgpoolAdmin installation I got the answer for the same and is sloved it is just a file which gives information about the php installations. I have verified and checkd that postgreSQl supprot is enabled and also multibyte string support is unabled. . I have attched the step by step installation html with the mail which is provided with the pgpool admin. all things go right till the end .(till step 14). but when i drop the install directory and try to run the login http://localhost/pgpoolAdmin-1.0.0/login.php Nothing happens . any idea what is going wrong With Regards Ashish - Original Message - From: "Devrim GÜNDÜZ" <[EMAIL PROTECTED]> To: "Ashish Karalkar" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, September 25, 2007 3:57 PM Subject: Re: [GENERAL] PgpoolAdmin installation Title: pgpool Administration Tool Introduction Install Login pgpool Status Node Status Query Cache Partitioning Rule pgpool.conf Setting pgpoolAdmin Setting Change Password Logout Error Code Introduction The pgpool Administration Tool is management tool of pgpool. It is possible to monitor, start, stop pgpool and change setting for pgpool. Screen The menu is displayed left and the function in each menu is displayed right on Screen of pgpoolAdmin. After login, the following menus are displayed. pgpool Status Node Status Query Cache Partitioning Rule pgpool.conf Setting pgpoolAdmin Setting Change Password Logout There is a help button on the right of the screen. Help can be displayed by clicking it. Version 1.0 Alpha Copyright © 2006 pgpool Global Development Group. All rights reserved. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Version 8.2.4 ecpg - function not found
On Thu, Sep 06, 2007 at 08:51:54AM -0400, Paul Tilles wrote:
> ECPGis_informix_null
>
> ECPGset_informix_null
>
> In 8.2.4, I do not see these functions. Instead, I see functions
>
> ECPGis_noind_null
>
> ECPGset_noind_null
>
> Are they functionally the same?
Yes. The 7.4 version had some naming problems.
> Also, the 8.2.4 doc (Section 31.9.1) describes the functions risnull and
> rsetnull. These are the names of the original Informix functions. Are
> they available for use through ecpg?
Yes, they are. Just have a look at compatlib. They essantially only call
the above mentioned functions:
int
rsetnull(int t, char *ptr)
{
ECPGset_noind_null(t, ptr);
return 0;
}
int
risnull(int t, char *ptr)
{
return (ECPGis_noind_null(t, ptr));
}
Hope this helps.
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PgpoolAdmin installation
Hi, On Tue, 2007-09-25 at 14:53 +0530, Ashish Karalkar wrote: > "step 5 ) It accesses install/phpinfo.php from Web a browser. It is > confirmed that the function of php_mstring and php_pgsql is effective. > a.. "Multibyte Support" is "enabled". > b.. "PostgreSQL Support" is "enabled". " > > But I dont see any phpinfo.php file in the install directory. No idea, but AFAICS there is no need for that file. Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
[GENERAL] lowering impact of checkpoints
hi, our system is handling between 600 and 2000 transactions per second. all of them are very small, very fast. typical query runs in under 1ms. yes - sometimes we get queries that take longer than then should get. simple check shows that we have a very visible pattern of every-5-minutes peak. in the minute that there is checkpoint - we get usually 15-20 times more queries "over 500 ms" than in other minutes. we are using 8.2.4 (upgrade will be soon), with these settings: # select name, setting, unit from pg_settings where name ~* 'bgwriter|wal|checkpoint'; name | setting | unit ---+---+ bgwriter_all_maxpages | 5 | bgwriter_all_percent | 0.333 | [null] bgwriter_delay| 200 | ms bgwriter_lru_maxpages | 5 | bgwriter_lru_percent | 1 | [null] checkpoint_segments | 32| checkpoint_timeout| 300 | s checkpoint_warning| 30| s wal_buffers | 128 | 8kB wal_sync_method | fdatasync | [null] (10 rows) is there anything i can change to make it "smoother"? depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PgpoolAdmin installation
Thanks for your replay, Previously I have installed pgpool and trying to install pgpoolAdmin. Now I have installed pgpool-II and that problem is sloved. Now I am facing another problem in installation document it is said ; "step 5 ) It accesses install/phpinfo.php from Web a browser. It is confirmed that the function of php_mstring and php_pgsql is effective. a.. "Multibyte Support" is "enabled". b.. "PostgreSQL Support" is "enabled". " But I dont see any phpinfo.php file in the install directory. What may be the reason? With Reagards Ashish Karalkar - Original Message - From: "Devrim GÜNDÜZ" <[EMAIL PROTECTED]> To: "Ashish Karalkar" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, September 25, 2007 1:37 PM Subject: Re: [GENERAL] PgpoolAdmin installation ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] "not in" clause too slow?
On Fri, Sep 21, 2007 at 12:09:50PM +0200, Ottavio Campana wrote: > 2) how can I speed it up? by using indexes? or by changing the query? Note that NOT IN cannot be optimised in the same way as NOT EXISTS due to the different ways they handle NULL. In particular if the subquery of the NOT IN produces a NULL *anywhere* it will always return FALSE, hence it often needs to scan the entire subquery even when an index might be better. You might know this cannot happen, but postgres can't always tell. NOT EXISTS doesn't have this problem. Blame the SQL standard if you like. -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] "not in" clause too slow?
Alban Hertroys ha scritto: > Ottavio Campana wrote: >> 2) how can I speed it up? by using indexes? or by changing the query? > > Do you have indices on mytable.id and copy_mytable.id? > Does using NOT EXISTS get you any better results? Eventually I had to select not all the table fields but only the primary key and successively loop on the table again. Thus I've been able to exploit the indexes. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] PgpoolAdmin installation
Hi, On Tue, 2007-09-25 at 12:57 +0530, Ashish Karalkar wrote: > can anybody please tell me what is this PCP and PCP directory, I dont > have that one on my box,do i have to install this pcp package and if > yes please point me to the link? Did you install pgpool-II ? Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] UNIQUE_VIOLATION exception, finding out which index would have been violated
On Tue, Sep 25, 2007 at 04:55:37AM -0200, Petri Simolin wrote: > I have created a function which inserts a row in a table which has 2 unique > indexes on two different columns. > > I am wondering, if there is a way in case of UNIQUE_VIOLATION exception to > find out which index would have been violated? In PL/pgSQL you could extract the constraint name from SQLERRM, which should be a string like 'duplicate key violates unique constraint "foo_id1_key"'. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] PgpoolAdmin installation
Dear list member, I am trying to install pgpooladmin tool and the documantation talks about following parameter pcp_attach_node command pass /usr/local/bin/pcp_attach_node pcp_detach_node command pass /usr/local/bin/pcp_detach_node pcp_node_count command pass /usr/local/bin/pcp_node_count pcp_node_info command pass /usr/local/bin/pcp_node_info pcp_proc_count command pass /usr/local/bin/pcp_proc_count pcp_proc_info command pass /usr/local/bin/pcp_proc_info pcp_stop_pgpool command pass /usr/local/bin/pcp_stop_pgpool pcp_systemdb_info command pass /usr/local/bin/pcp_systemdb_info pg_md5 command pass /usr/local/bin/pg_md5 pcp.conf file pass /usr/local/etc/pcp.conf can anybody please tell me what is this PCP and PCP directory, I dont have that one on my box,do i have to install this pcp package and if yes please point me to the link? With Regards Ashish...
Re: [GENERAL] [Urgent] Regexp_replace question
On 25/09/2007, Michael Fuhr <[EMAIL PROTECTED]> wrote: > > How can I remove characters that form a part of regular expressions? > > Why do you want to do that? Because these values were inserted into the DB due to a faulty application. So cleansing was called for. I just ended up doing it with replace instead of regexp_replace, one character at a time. Thanks! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
