Re: [GENERAL] Slow update

On Feb 9, 2009, at 2:07 PM, Grzegorz Jaśkiewicz wrote: On Mon, Feb 9, 2009 at 12:50 PM, Herouth Maoz wrote: I hope someone can clue me in based on the results of explain analyze. Did you have a chance to run vmstat on it, and post it here ? Maybe - if db resides on the same disc with every

Re: [GENERAL] dbi_link help

SHARMILA JOTHIRAJAH wrote: > When I try to insert into or select from the oracle database I get > this error... > > ERROR: error from Perl function "remote_select": error from Perl > function "cache_connection": DBI > connect('database=postgres;host=...;sid=...;port=1521','postgres',...) > failed

Re: [GENERAL] ora2pg or dbi_link ?

SHARMILA JOTHIRAJAH wrote: Ive been struggling to query some of my oracle tables from postgres using the dbi_link and its not working. Have anyone tried ora2pg for querying the oracle database from postgres? ora2pg is not really made for that. It is for a one-time conversion. -- Sent via pg

Re: [GENERAL] trying to make sense of deadlocks

在 Feb 9, 2009 8:52 PM 時， Tom Lane 寫到： Richard Yen writes: It seems like all the deadlocks are for tuple (3,60), but strangely, tuple (3,60) on the account table doesn't exist. Perhaps it was deleted? According to the account table, the account with id = 39271 (which the UPDATE statements ca

Re: [GENERAL] trying to make sense of deadlocks

Richard Yen writes: > It seems like all the deadlocks are for tuple (3,60), but strangely, > tuple (3,60) on the account table doesn't exist. Perhaps it was > deleted? According to the account table, the account with id = 39271 > (which the UPDATE statements call for) corresponds to tuple

Re: [GENERAL] trying to make sense of deadlocks

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Richard Yen > Sent: Monday, February 09, 2009 4:18 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] trying to make sense of deadlocks > > Hi, > > I'm tryin

[GENERAL] trying to make sense of deadlocks

Hi, I'm trying to make sense of a situation I ran into this morning. Apparently, there were numerous deadlocks (approx. 75 in a 30-min period) while procs were trying to write to a table (16634, "account") in my database. Just to give you a sense of what's going on, process 22583 tried t

Re: [GENERAL] Out of memory on SELECT in 8.3.5

On Mon, Feb 9, 2009 at 1:58 PM, Matt Magoffin wrote: I wonder if this is the problem, or part of it. This part of the explain analyze on down, there's 1.4M rows, when the planner seems to expect the number of rows to be chopped down quite a bit more when it goes from the bitmap index scan to the

Re: [GENERAL] Out of memory on SELECT in 8.3.5

On Mon, Feb 9, 2009 at 3:57 PM, Matt Magoffin wrote: >> No, explain analyze for the query that wouldn't execute before but now >> does, with, I assume, a large work_mem. I'd like to see how it >> differes from the one with smaller work_mem. > > Ah, I pasted that in an earlier email, sent February

Re: [GENERAL] Out of memory on SELECT in 8.3.5

> No, explain analyze for the query that wouldn't execute before but now > does, with, I assume, a large work_mem. I'd like to see how it > differes from the one with smaller work_mem. Ah, I pasted that in an earlier email, sent February 10, 2009 9:58:00 AM GMT+13:00... that plan was the one usin

Re: [GENERAL] Out of memory on SELECT in 8.3.5

On Mon, Feb 9, 2009 at 2:40 PM, Matt Magoffin wrote: >>> Yes... and indeed changing vm.overcommit_ratio to 80 does allow that >>> previously-failing query to execute successfully. Do you think this is >>> also what caused the out-of-memory error we saw today just when a >>> transaction was initiat

[GENERAL] ora2pg or dbi_link ?

Hi, Ive been struggling to query some of my oracle tables from  postgres using the dbi_link and its not working. Have anyone tried ora2pg for querying the oracle database from postgres? If so whats your feedback on that? Thanks Sharmila

Re: [GENERAL] Out of memory on SELECT in 8.3.5

Stephen Frost writes: > * Matt Magoffin (postgresql@msqr.us) wrote: >> Yes... and indeed changing vm.overcommit_ratio to 80 does allow that >> previously-failing query to execute successfully. Do you think this is >> also what caused the out-of-memory error we saw today just when a >> transact

Re: [GENERAL] Out of memory on SELECT in 8.3.5

>> Yes... and indeed changing vm.overcommit_ratio to 80 does allow that >> previously-failing query to execute successfully. Do you think this is >> also what caused the out-of-memory error we saw today just when a >> transaction was initiated? > > Curious, what's the explain analyze look like for

Re: [GENERAL] Calling overloaded function with NULL argument

Patryk Kordylewski writes: > i've found a strange behaviour when you have overloaded functions with > the same name and different argument types and try to call them with NULL. > The stored procedures with "text" and "text[]" argument type gets called > - the variants with "integer" / "integer[]

Re: [GENERAL] Out of memory on SELECT in 8.3.5

On Mon, Feb 9, 2009 at 1:57 PM, Stephen Frost wrote: > * Scott Marlowe (scott.marl...@gmail.com) wrote: >> I think that you're fixing a symptom, but ignoring the cause. >> Twiddling VM parameters may help out, but this problem of too much >> memory allocated is the real issue, so yeah, you're just

Re: [GENERAL] Out of memory on SELECT in 8.3.5

On Mon, Feb 9, 2009 at 2:01 PM, Matt Magoffin wrote: >> I don't think changing work_mem down is actually going to reduce the >> memory allocated without changing the plan to something less optimal. >> In the end, all of this is putting off the inevitable, if you get enough >> PGs going and enough

[GENERAL] ora2pg or dbi_link ?

Hi, Ive been struggling to query some of my oracle tables from  postgres using the dbi_link and its not working. Have anyone tried ora2pg for querying the oracle database from postgres? If so whats your feedback on that? Thanks Sharmila

Re: [GENERAL] Convert Arbitrary Table to Array?

In article <17050.1234200...@sss.pgh.pa.us>, Tom Lane writes: > Lee Hughes writes: >> Hi, I need a function that accepts a table name and returns a 2-dimensional >> array of the table data. > Well, in 8.3 and up there are arrays of composite types, so you can > do something like >

Re: [GENERAL] Out of memory on SELECT in 8.3.5

* Matt Magoffin (postgresql@msqr.us) wrote: > Yes... and indeed changing vm.overcommit_ratio to 80 does allow that > previously-failing query to execute successfully. Do you think this is > also what caused the out-of-memory error we saw today just when a > transaction was initiated? Almost ce

Re: [GENERAL] Out of memory on SELECT in 8.3.5

* Matt Magoffin (postgresql@msqr.us) wrote: > Also, by adjusting this, would I possibly just be delaying the problem we > currently have (i.e. over time, we start to run out of memory)? I just > wonder why the system is reaching this limit at all... do you feel it is > quite normal for a system

Re: [GENERAL] Out of memory on SELECT in 8.3.5

> I don't think changing work_mem down is actually going to reduce the > memory allocated without changing the plan to something less optimal. > In the end, all of this is putting off the inevitable, if you get enough > PGs going and enough requests and whatnot, you're going to start running > out

Re: [GENERAL] Out of memory on SELECT in 8.3.5

> I'd do both. But only after I'd reduced work_mem. Given that > reducing work_mem removed the problem, it looks to me like pgsql is > requesting several large blocks of ram, then only using a small port > of them. But overcommit set to 2 means that the OS will not allow an > overcommit of memor

Re: [GENERAL] Out of memory on SELECT in 8.3.5

* Scott Marlowe (scott.marl...@gmail.com) wrote: > I think that you're fixing a symptom, but ignoring the cause. > Twiddling VM parameters may help out, but this problem of too much > memory allocated is the real issue, so yeah, you're just putting off > the inevitable. I don't think changing work

Re: [GENERAL] Out of memory on SELECT in 8.3.5

* Scott Marlowe (scott.marl...@gmail.com) wrote: > I'd do both. But only after I'd reduced work_mem. Given that > reducing work_mem removed the problem, it looks to me like pgsql is > requesting several large blocks of ram, then only using a small port > of them. But overcommit set to 2 means th

Re: [GENERAL] Out of memory on SELECT in 8.3.5

> with 100 concurrent postgres connections, if they all did something > requiring large amounts of work_mem, you could allocate 100 * 125MB (I > believe thats what you said it was set to?) which is like 12GB :-O > > in fact a single query thats doing multiple sorts of large datasets for > a messy

Re: [GENERAL] Out of memory on SELECT in 8.3.5

On Mon, Feb 9, 2009 at 1:32 PM, Matt Magoffin wrote: >> I suspect this may be it... Apparently, while you're only using about >> 2G, you've got 10G or so of outstanding commitments, and Linux is >> refusing to allocate more. >> >> You probably want to up your overcommit_ratio, esp. in light of th

Re: [GENERAL] Out of memory on SELECT in 8.3.5

On Mon, Feb 9, 2009 at 1:25 PM, Matt Magoffin wrote: >> * Matt Magoffin (postgresql@msqr.us) wrote: >>> [r...@170226-db7 ~]# cat /proc/meminfo >>> CommitLimit: 10312588 kB >>> Committed_AS: 9760756 kB >> >> I suspect this may be it... Apparently, while you're only using about >> 2G, you've

Re: [GENERAL] Out of memory on SELECT in 8.3.5

* Matt Magoffin (postgresql@msqr.us) wrote: > Thanks for the advice. Should we have more than 2GB of swap available? I > thought the goal for a Postgres system was to avoid swap use at all cost? > Would it be better for us to add more swap, or adjust this > overcommit_ratio as you discuss? You

Re: [GENERAL] Out of memory on SELECT in 8.3.5

> I suspect this may be it... Apparently, while you're only using about > 2G, you've got 10G or so of outstanding commitments, and Linux is > refusing to allocate more. > > You probably want to up your overcommit_ratio, esp. in light of the fact > that you've only got 2G of swap on this box. I'd

Re: [GENERAL] Out of memory on SELECT in 8.3.5

> * Matt Magoffin (postgresql@msqr.us) wrote: >> [r...@170226-db7 ~]# cat /proc/meminfo >> CommitLimit: 10312588 kB >> Committed_AS: 9760756 kB > > I suspect this may be it... Apparently, while you're only using about > 2G, you've got 10G or so of outstanding commitments, and Linux is > refu

[GENERAL] Calling overloaded function with NULL argument

Hello, i've found a strange behaviour when you have overloaded functions with the same name and different argument types and try to call them with NULL. The stored procedures with "text" and "text[]" argument type gets called - the variants with "integer" / "integer[]" produce the, i think exp

Re: [GENERAL] Out of memory on SELECT in 8.3.5

> Which is a longwinded way of saying "that doesn't seem to match the > symptoms, but ..." If you're not dead certain that your kernel is > configured to allow *well* north of 7000 open files, you might consider > cutting max_files_per_process in half at your next restart. I think it is, looking

Re: [GENERAL] Out of memory on SELECT in 8.3.5

* Matt Magoffin (postgresql@msqr.us) wrote: > [r...@170226-db7 ~]# cat /proc/meminfo > CommitLimit: 10312588 kB > Committed_AS: 9760756 kB I suspect this may be it... Apparently, while you're only using about 2G, you've got 10G or so of outstanding commitments, and Linux is refusing to allo

Re: [GENERAL] Out of memory on SELECT in 8.3.5

> Err, yea, overcommit_memory=2, a small amount of swap space, and a low > overcommit_ratio could cause this to happen... The default > ratio is 50 though, which should mean, on this system, there is about > 10G available for user processes, but his usage shows only a bit over 2G > being used outs

Re: [GENERAL] Out of memory on SELECT in 8.3.5

> So did the backend crashed on this one, or just produced 'out of > memory ' message ? No crash, just the error message. -- m@ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] dbi_link help

Hi, Im new to dbi_link. I had installed dbi_link and run the dbi_link.sql script . This is the script that I ran after that and it didn't have any errors.  Now the schemas dbi_link and EMPLOYEE are created in my postgres database. The user is "postgres" in both the databases with the same passwo

[GENERAL] Query for describe locks

hi ALL. i want refine this query : select pg_stat_activity.current_query , pg_class.relname, pg_locks.transaction, pg_locks.mode, pg_locks.granted, pg_stat_activity.procpid from pg_stat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_locks.pid=pg

Re: [GENERAL] Convert Arbitrary Table to Array?

Lee Hughes writes: > Good point on scalability -- I am planning to limit the number of rows > fetched through LIMIT or a Cursor once I have the basics working. Er ... why don't you just use a cursor directly on the table? > Did you mean *select array(select * from mytable);* ? No, I meant what

Re: [GENERAL] Convert Arbitrary Table to Array?

Good point on scalability -- I am planning to limit the number of rows fetched through LIMIT or a Cursor once I have the basics working. Did you mean *select array(select * from mytable);* ? Thanks for your help, I will try this approach. Lee On Mon, Feb 9, 2009 at 9:20 AM, Tom Lane wrote: >

Re: [GENERAL] Pet Peeves?

Richard Huxton wrote: Gregory Stark wrote: Steve Crawford writes: 3. Date handling Sometimes I've got data with invalid dates and it would be great if it could replace all the bad ones with, say "-00-00". Oh dear $DEITY, no. I think it would be best if we

Re: [GENERAL] Convert Arbitrary Table to Array?

Lee Hughes writes: > Hi, I need a function that accepts a table name and returns a 2-dimensional > array of the table data. Well, in 8.3 and up there are arrays of composite types, so you can do something like select array(select mytable from mytable); However you are not going

Re: [GENERAL] Pet Peeves?

Erik Jones escribió: > One workaround I came up with a while back for that is to edit the stat > file name to be in a separate directory under global (like > /global/pg_stats/pgstat.stat) and mount a ramfs there. Of > course, a custom compile isn't always an option but it removed a *ton* >

[GENERAL] Convert Arbitrary Table to Array?

Hi, I need a function that accepts a table name and returns a 2-dimensional array of the table data. I found some related posts on this and other forums and tried several approaches with plpgsql but have had no success. I know I can pull the table data out to the application tier and transform it

Re: [GENERAL] Out of memory on SELECT in 8.3.5

* Tom Lane (t...@sss.pgh.pa.us) wrote: > OTOH ... wait a minute. Have you maybe got the system configured to > start denying memory requests before it gets into significant swapping? > We typically suggest setting vm.overcommit_memory=2 on Linux, but > I'm not sure whether that results in the kern

Re: [GENERAL] Out of memory on SELECT in 8.3.5

"Matt Magoffin" writes: > I just noticed something: the "open files" limit lists 1024, which is the > default for this system. A quick count of open data files currently in use > by Postgres returns almost 7000, though. In principle, any problem with that ought to result in explicit error message

Re: [GENERAL] Slow update

Grzegorz Jaśkiewicz wrote: > On Mon, Feb 9, 2009 at 12:50 PM, Herouth Maoz wrote: > >> I hope someone can clue me in based on the results of explain analyze. >> > > Did you have a chance to run vmstat on it, and post it here ? Maybe - > if db resides on the same disc with everything else,

Re: [GENERAL] Strange thing happened when upgrading postgres on windows on my laptop

On 09/02/2009 14:12, Raul Carolus wrote: > 1. During the install, it told me I had to close open office to > continue installation. That made me tilt my head, but I closed open > office and went on my merry way. I cam across this one too when upgrading my laptop. It turned out that I had an old

[GENERAL] Strange thing happened when upgrading postgres on windows on my laptop

I had 8.2.something installed on my lappy. I downloaded 8.3.5 and unzipped it. I used Upgrade.bat to perform the upgrade. Everything went almost swimmingly, except for two strange things: 1. During the install, it told me I had to close open office to continue installation. That made me t

Re: [GENERAL] Slow update

On Mon, Feb 9, 2009 at 12:50 PM, Herouth Maoz wrote: > I hope someone can clue me in based on the results of explain analyze. Did you have a chance to run vmstat on it, and post it here ? Maybe - if db resides on the same disc with everything else, something (ab)uses that much io, and it has to w

Re: [GENERAL] Slow update

Filip Rembiałkowski wrote: > > 2009/1/21 Herouth Maoz > > > Hello. > > I have a daily process that synchronizes our reports database from > our production databases. In the past few days, it happened a > couple of times that an update query took aroun

Re: [GENERAL] Out of memory on SELECT in 8.3.5

On Mon, Feb 9, 2009 at 2:17 AM, John R Pierce wrote: > Matt Magoffin wrote: >> >> We have 100+ postgres processes running, so for an individual process, >> could the 1024 file limit be doing anything to this query? Or would I see >> an explicit error message regarding this condition? >> >> > > wit

Re: [GENERAL] Out of memory on SELECT in 8.3.5

Matt Magoffin wrote: We have 100+ postgres processes running, so for an individual process, could the 1024 file limit be doing anything to this query? Or would I see an explicit error message regarding this condition? with 100 concurrent postgres connections, if they all did something req

Re: [GENERAL] complex custom aggregate function

I think I've found a solution myself to the moving average problem, so I'm posting it here in case it works for some others (and so that everybody can check that I'm doing it right and in the "best" way...) Basically I'm 1) saving all the couples (timestamp, double) of an aggregation into an arr

Re: [GENERAL] Out of memory on SELECT in 8.3.5

On Mon, Feb 9, 2009 at 8:53 AM, Matt Magoffin wrote: >>> We have 100+ postgres processes running, so for an individual process, >>> could the 1024 file limit be doing anything to this query? Or would I >>> see >>> an explicit error message regarding this condition? >> >> you would get one of "Open

Re: [GENERAL] Out of memory on SELECT in 8.3.5

>> We have 100+ postgres processes running, so for an individual process, >> could the 1024 file limit be doing anything to this query? Or would I >> see >> an explicit error message regarding this condition? > > you would get one of "Open files rlimit 1024 reached for uid " in > syslog (which

Re: [GENERAL] Out of memory on SELECT in 8.3.5

On Mon, Feb 9, 2009 at 8:23 AM, Matt Magoffin wrote: > I just noticed something: the "open files" limit lists 1024, which is the > default for this system. A quick count of open data files currently in use > by Postgres returns almost 7000, though. > > [r...@170226-db7 ~]# lsof -u postgres |egrep

Re: [GENERAL] Out of memory on SELECT in 8.3.5

> [r...@170226-db7 ~]# su -l postgres -c "ulimit -a" > core file size (blocks, -c) 0 > data seg size (kbytes, -d) unlimited > max nice(-e) 0 > file size (blocks, -f) unlimited > pending signals (-i) 139264 > max locked memory

Re: [GENERAL] Out of memory on SELECT in 8.3.5

> erp, that stinks. Must be on an older kernel? I've got it under (a > Debian-built) 2.6.26. I can't recall if there's another way to get > limit info for an active process.. Could use Tom's suggestion of > echo'ing ulimit -a out to a file somewhere during database start-up. Yes, this is a RHE

Re: [GENERAL] Out of memory on SELECT in 8.3.5

> Yeah, I remember we found a few xml-related leaks based on your reports. > However, there's not anything here to suggest that this query is > straining the capabilities of a 64-bit system with lots o RAM. It seems > certain you're hitting some artificial process-size limit, and the only > one I

Re: [GENERAL] Out of memory on SELECT in 8.3.5

* Matt Magoffin (postgresql@msqr.us) wrote: > There is no /prod//limits file, but here are erp, that stinks. Must be on an older kernel? I've got it under (a Debian-built) 2.6.26. I can't recall if there's another way to get limit info for an active process.. Could use Tom's suggestion of