[GENERAL] "shared_buffers" and "temp_buffers" why manual and code are different?

Hi, I want to tune the memory usage of PG (9.3.1) on my environment. I'm really confused by the following two configuration parameters, shared_buffers, temp_buffers, Take "shared_buffers" as the example, the manual says, "Sets the amount of memory the database server uses for shared memory buff

Re: [GENERAL] Getting non_NULL right-side values on a non-matching join?

Thanks Tom. The workaround seems harmless, even good coding practice, so it's all good on my end. If it were useful to you I'd gladly build and test it, but I doubt that's the case. But just say the word! Otherwise it's just a question of time and priorities, and it seems likely to chew up at l

Re: [GENERAL] Getting non_NULL right-side values on a non-matching join?

Ken Tanzer writes: > On Fri, Nov 22, 2013 at 8:04 AM, Tom Lane wrote: >> I found a less nasty workaround: if you replace "my_field" by >> "foo.my_field" in the SELECT list of boo_top_view, the problem goes away. > I'm happy to be of assistance, but mostly glad it wasn't a me-bug! :) I > put an

Re: [GENERAL] Getting non_NULL right-side values on a non-matching join?

On Fri, Nov 22, 2013 at 8:04 AM, Tom Lane wrote: > I wrote: > > If you just need a work-around-it-right-now solution, I'd suggest > > introducing an "OFFSET 0" optimization fence into one or another of the > > levels of view below the outer joins. I've not experimented but I think > > that ought

Re: [GENERAL] include all the postgres libraries (C)

I think PGXS is, what I've been looking for.   My Makefile looks like this:  PROGRAM = test DATA = "">  PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS)   The file test.c only includes the postgres.h, but I get this error, when compiling: /usr/bin/ld: cannot find

Re: [GENERAL] Scrolling/Updating Cursors

On Nov 22, 2013, at 2:32 PM, John Meyer wrote: > Why not both? I keep on searching even when I post up a question. > Just attempting to be polite and not cross post to too much. I have pretty much exhausted my own search and now am relying on the kindness and knowledge of others ;) > On 11

Re: [GENERAL] Scrolling/Updating Cursors

Why not both? I keep on searching even when I post up a question. On 11/22/2013 2:27 PM, Kevin Wooten wrote: On Nov 19, 2013, at 6:33 PM, Kevin Wooten wrote: My apologies for posting what is almost certainly somewhat of a repeat question but I have searched and attempted everything I can

Re: [GENERAL] Scrolling/Updating Cursors

On Nov 19, 2013, at 6:33 PM, Kevin Wooten wrote: > My apologies for posting what is almost certainly somewhat of a repeat > question but I have searched and attempted everything I can think of and > cannot figure it out myself. > > The basic question is… Is it possible to get a scrollable curs

Re: [GENERAL] Easiest way to CREATE EXTENSION when upgrading from 9.0 to 9.2?

Joshua Boyd wrote: > We have a pre-production environment that was running postgresql > 9.0 and we created the extension “pgcrypto” in it by feeding psql > the contrib/pgcrypto.sql ..  We just upgraded to 9.2 (via > pg_upgrade) – naturally all the functions still exist, but > pgcrypto is not a re

Re: [GENERAL] Recursive function

Juan Daniel Santana Rodés wrote > Hi everyone... > I have a problem. I am programming a recursive function in plpgsql > language. This function use a cursor and when the function try to call > the same function throw a exception that it say me that the cursor is > using. > My friends how I can to

Re: [GENERAL] pg_upgrade ?deficiency

On Fri, Nov 22, 2013 at 03:13:33PM -0500, Tom Lane wrote: > Bruce Momjian writes: > > Not sure about backpatching. default_transaction_read_only has been > > around since 7.4. Setting it to true would cause pg_dump to fail unless > > you changed the database setting, and pg_dumpall would fail co

[GENERAL] Recursive function

Hi everyone... I have a problem. I am programming a recursive function in plpgsql language. This function use a cursor and when the function try to call the same function throw a exception that it say me that the cursor is using. My friends how I can to resolve this problem. Regards!! -- Sent

[GENERAL] Easiest way to CREATE EXTENSION when upgrading from 9.0 to 9.2?

We have a pre-production environment that was running postgresql 9.0 and we created the extension "pgcrypto" in it by feeding psql the contrib/pgcrypto.sql .. We just upgraded to 9.2 (via pg_upgrade) - naturally all the functions still exist, but pgcrypto is not a registered extension with the

Re: [GENERAL] pg_upgrade ?deficiency

> Bruce Momjian writes: > > Not sure about backpatching. default_transaction_read_only has been > > around since 7.4. Setting it to true would cause pg_dump to fail unless > > you changed the database setting, and pg_dumpall would fail completely > > as there is no way to turn off the database s

Re: [GENERAL] tsvector stemmer issue

Jeff Trout wrote: > ran into an interesting issue - and I’m not sure if anything can > be done about it - the snowball stemmer treats “severance” and > “several” as the same, which for me is a big, big issue. You can create a custom dictionary chain.  The only type I worked with was thesaurus, b

Re: [GENERAL] pg_xlog is getting bigger

Pankaj wrote: > now go to pg_xlog folder you can can see a pattern in file names of logs. > Just remove the file other then current time stamp. Or for safety remove > logs other then current date. No!!!  NEVER remove ANY files from the pg_xlog sub-directory!  That will corrupt your database. --

Re: [GENERAL] pg_upgrade ?deficiency

Bruce Momjian writes: > Not sure about backpatching. default_transaction_read_only has been > around since 7.4. Setting it to true would cause pg_dump to fail unless > you changed the database setting, and pg_dumpall would fail completely > as there is no way to turn off the database setting. N

Re: [GENERAL] corruption issue after server crash - ERROR: unexpected chunk number 0

Mike Broers wrote: > vacuumb avz, pg_dumpall, and vacuum freeze analyze on the former > standby database that received the corruption via replication all > came back without errors.  Is the vacuum freeze intended to > potentially fix problems or just reveal if other tables may have > corruption,

Re: [GENERAL] Puzzling table scan in a CTE

"sl...@centrum.sk" wrote: > I have a recursive CTE where a table scan occurs, even though > there doesn't seem to be a good reason for it. Do you have effective_cache_size set to 50% to 75% of machine RAM? Do you have cpu_tuple_cost set to between 0.03 and 0.05?  If not, do changes to these set

Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ???

On 19 Listopad 2013, 5:30, Brian Wong wrote: > I've tried any work_mem value from 1gb all the way up to 40gb, with no > effect on the error. I'd like to think of this problem as a server > process memory (not the server's buffers) or client process memory issue, > primarily because when we tested

Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ???

Em 19/11/2013 02:30, Brian Wong escreveu: I've tried any work_mem value from 1gb all the way up to 40gb, with no effect on the error. I'd like to think of this problem as a server process memory (not the server's buffers) or client process memory issue, primarily because when we tested the err

Re: [GENERAL] Puzzling table scan in a CTE

On 2013-11-22 12:49, sl...@centrum.sk wrote: Thanks for the suggestion. I've tried it with seqscan set to off, but there's still a bitmap heap scan going on: http://explain.depesz.com/s/zIJl I have random_page_cost set to 1.5 at the moment, as the database is on a solid state disk. Every

Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ???

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Brian Wong Sent: Monday, November 18, 2013 11:30 PM To: bricklen Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ??? I've tried any w

Re: [GENERAL] Solution for Synonyms

mrprice22 wrote on 22.11.2013 19:25: We are in the process of moving from Oracle to PostgreSQL. We use a stored procedure to populate some reporting tables once an hour. There are two sets of these tables, set A and set B. We use synonyms to point to the “active” set of tables at any given tim

[GENERAL] Solution for Synonyms

We are in the process of moving from Oracle to PostgreSQL. We use a stored procedure to populate some reporting tables once an hour. There are two sets of these tables, set A and set B. We use synonyms to point to the “active” set of tables at any given time. The procedure works like this: 1

[GENERAL] tsvector stemmer issue

ran into an interesting issue - and I’m not sure if anything can be done about it - the snowball stemmer treats “severance” and “several” as the same, which for me is a big, big issue. even quoting it doesn’t help. indie=> select to_tsvector('severance several'); to_tsvector - 'se

Re: [GENERAL] pg_upgrade ?deficiency

On Thu, Nov 21, 2013 at 06:22:50AM -0800, Kevin Grittner wrote: > > Well, pg_upgrade can't handle every possible configuration.  How > > do we even restore into such a database?  You marked the database > > as read-only, and pg_upgrade is going to honor that and not > > modify it. > > That interpr

Re: [GENERAL] pg_xlog is getting bigger

Go to database # psql -U postgres #checkpoint; exit now go to pg_xlog folder you can can see a pattern in file names of logs. Just remove the file other then current time stamp. Or for safety remove logs other then current date. -- View this message in context: http://postgresql.1045698

Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ???

I've tried any work_mem value from 1gb all the way up to 40gb, with no effect on the error. I'd like to think of this problem as a server process memory (not the server's buffers) or client process memory issue, primarily because when we tested the error there was no other load whatsoever. Unf

Re: [GENERAL] corruption issue after server crash - ERROR: unexpected chunk number 0

vacuumb avz, pg_dumpall, and vacuum freeze analyze on the former standby database that received the corruption via replication all came back without errors. Is the vacuum freeze intended to potentially fix problems or just reveal if other tables may have corruption, Im trying to decide if this nee

[GENERAL] Re: [GENERAL] Puzzling table scan in a CTE

Thanks for the suggestion. I've tried it with seqscan set to off, but there's still a bitmap heap scan going on: http://explain.depesz.com/s/zIJl   I have random_page_cost set to 1.5 at the moment, as the database is on a solid state disk.   Every user has a parent, but not every parent has a ch

Re: [GENERAL] Puzzling table scan in a CTE

On 2013-11-22 11:54, sl...@centrum.sk wrote: Good day, I have a recursive CTE where a table scan occurs, even though there doesn't seem to be a good reason for it. It seems the planner came to the conclusion that columns that are not actually used in the output, joins or a where clause are

Re: [GENERAL] include all the postgres libraries (C)

Janek Sendrowski wrote: > Hi, >   > I like to try some things with C and I need certain postgres libraries for it. > This time, I'm including postgres.h for example, but postgres.h doesn't > include it's files. > How do I include the whole tree, or is there a certain directory I can use. > I just

Re: [GENERAL] include all the postgres libraries (C)

Janek Sendrowski wrote: > I like to try some things with C and I need certain postgres libraries for it. > This time, I'm including postgres.h for example, but postgres.h doesn't > include it's files. > How do I include the whole tree, or is there a certain directory I can use. > I just want to c

[GENERAL] Puzzling table scan in a CTE

Good day,   I have a recursive CTE where a table scan occurs, even though there doesn't seem to be a good reason for it. It seems the planner came to the conclusion that columns that are not actually used in the output, joins or a where clause are a part of the output. It's not a performance pro

Re: [GENERAL] pg_shdepend vacuum.

I'm experiencing the same thing with 9.0.4 running on win server 2003. Reams of messages like this... (I've changed the database names in the following log snipit) 2013-11-22 09:37:42 CSTERROR: canceling autovacuum task 2013-11-22 09:37:42 CSTCONTEXT: automatic vacuum of table "x1.p

Re: [GENERAL] Getting non_NULL right-side values on a non-matching join?

I wrote: > If you just need a work-around-it-right-now solution, I'd suggest > introducing an "OFFSET 0" optimization fence into one or another of the > levels of view below the outer joins. I've not experimented but I think > that ought to fix it, at some possibly-annoying cost in query > optimiz

Re: [GENERAL] Browsing postgres database using the Eclipse QuantumDB plugin

Adrian, Thanks! Searching through those forum posts (I had tried doing general google searches first before posting here, but didn't turn up that forum) I found this: http://sourceforge.net/p/quantum/discussion/24178/thread/17724b28/?limit=25#1166 which seems to indicate that at least back in 20

Re: [GENERAL] Multiple aggs,sums in 1 que

Dorian Hoxha wrote > I have: create table tbl (a,b,c,d,e,f,g,h); > > And i need to select in 1 query ,or the most performant way: > > top 5(a) > top 5(b) > top 5(c): for each top5(c): top 5(d) > count(f) GROUP BY f > > > I can make these in separate queries but that means that postgresql would

Re: [GENERAL] Browsing postgres database using the Eclipse QuantumDB plugin

I wanted to make sure that it wasn't a permission configuration problem in postgres first, since all of the other databases have worked without a similar issue. On Fri, Nov 22, 2013 at 9:54 AM, Adrian Klaver wrote: > On 11/22/2013 05:46 AM, Mike Kienenberger wrote: >> >> Has anyone successfully

Re: [GENERAL] Browsing postgres database using the Eclipse QuantumDB plugin

Em 22/11/2013 12:54, Adrian Klaver escreveu: On 11/22/2013 05:46 AM, Mike Kienenberger wrote: Has anyone successfully connected and browsed a postgres database using the Eclipse QuantumDB plugin? You might get a answer sooner here: http://sourceforge.net/p/quantum/discussion/24178/ I can c

Re: [GENERAL] Browsing postgres database using the Eclipse QuantumDB plugin

On 11/22/2013 05:46 AM, Mike Kienenberger wrote: Has anyone successfully connected and browsed a postgres database using the Eclipse QuantumDB plugin? You might get a answer sooner here: http://sourceforge.net/p/quantum/discussion/24178/ I can connect and execute sql, but the existing table

Re: [GENERAL] PG replication across DataCenters

Michael Paquier wrote: > On Fri, Nov 22, 2013 at 10:03 PM, Kaushal Shriyan > wrote: >> I am not sure i understand the difference between async and sync replication >> and on what scenarios i should use async or sync replication. Does it mean >> if it is within same DC then sync replication is the

[GENERAL] Multiple aggs,sums in 1 query

I have: create table tbl (a,b,c,d,e,f,g,h); And i need to select in 1 query ,or the most performant way: top 5(a) top 5(b) top 5(c): for each top5(c): top 5(d) count(f) GROUP BY f I can make these in separate queries but that means that postgresql would read the table multiple-times? Is it pos

[GENERAL] Browsing postgres database using the Eclipse QuantumDB plugin

Has anyone successfully connected and browsed a postgres database using the Eclipse QuantumDB plugin? I can connect and execute sql, but the existing table list is always empty as if no meta information is ever provided to the browser plugin. At first, I thought it might be a permission problem

Re: [GENERAL] PG replication across DataCenters

On Fri, Nov 22, 2013 at 9:44 PM, Albe Laurenz wrote: > Torsten Förtsch wrote: >>> Don't use synchronous replication if you have a high transaction >>> rate and a noticable network latency between the sites. >>> >>> Wait for the next bugfix release, since a nasty bug has just >>> been discovered. >

Re: [GENERAL] PG replication across DataCenters

On Fri, Nov 22, 2013 at 10:03 PM, Kaushal Shriyan wrote: > I am not sure i understand the difference between async and sync replication > and on what scenarios i should use async or sync replication. Does it mean > if it is within same DC then sync replication is the best and if it is > across DC

[GENERAL] include all the postgres libraries (C)

Hi,   I like to try some things with C and I need certain postgres libraries for it. This time, I'm including postgres.h for example, but postgres.h doesn't include it's files. How do I include the whole tree, or is there a certain directory I can use. I just want to compile and run the files for

Re: [GENERAL] PG replication across DataCenters

On Fri, Nov 22, 2013 at 6:14 PM, Albe Laurenz wrote: > Torsten Förtsch wrote: > >> Don't use synchronous replication if you have a high transaction > >> rate and a noticable network latency between the sites. > >> > >> Wait for the next bugfix release, since a nasty bug has just > >> been discover

Re: [GENERAL] PG replication across DataCenters

Torsten Förtsch wrote: >> Don't use synchronous replication if you have a high transaction >> rate and a noticable network latency between the sites. >> >> Wait for the next bugfix release, since a nasty bug has just >> been discovered. > > Can you please explain or provide a pointer for more info

Re: [GENERAL] PG replication across DataCenters

On 22/11/13 11:57, Albe Laurenz wrote: > Don't use synchronous replication if you have a high transaction > rate and a noticable network latency between the sites. > > Wait for the next bugfix release, since a nasty bug has just > been discovered. Can you please explain or provide a pointer for m

Re: [GENERAL] PG replication across DataCenters

Em 22/11/2013 08:43, Kaushal Shriyan escreveu: Hi, I have read on the web that Postgresql DB supports replication across data centers. Any real life usecase examples if it has been implemented by anyone. Please also help me understand the caveats i need to take care if i implement this setup.

Re: [GENERAL] PG replication across DataCenters

Kaushal Shriyan wrote: > I have read on the web that Postgresql DB supports replication across data > centers. Any real life > usecase examples if it has been implemented by anyone. Well, we replicate a 1 TB database between two locations. It is a fairly active OLTP application, but certainly not

[GENERAL] PG replication across DataCenters

Hi, I have read on the web that Postgresql DB supports replication across data centers. Any real life usecase examples if it has been implemented by anyone. Please also help me understand the caveats i need to take care if i implement this setup. Regards, Kaushal

[GENERAL] Performance of Blobs, LargeObject

Hi all.   I'm having some performance-issues with storing Blobs from JDBC taking lng time (see this thread: https://github.com/impossibl/pgjdbc-ng/issues/42 ) and it looks like PG is to blame.   Are there any plans to improve PG in this area,