Re: [GENERAL] How can you get "WAL segment has already been removed" when doing synchronous replication ?!

On Thu, Jul 11, 2013 at 11:31 PM, hubert depesz lubaczewski wrote: > On Thu, Jul 11, 2013 at 11:29:24PM +0530, Raghavendra wrote: >> On Thu, Jul 11, 2013 at 11:18 PM, hubert depesz lubaczewski < >> dep...@depesz.com> wrote: >> >> > We are seeing situation like this: >> > 1. 9.2.4 database >> > 2.

Re: [GENERAL] Full text search

Hi Raghavendra, Thanks for your response, however i want same kind of result using full text search. is it possible? Regards, Itishree On Fri, Jul 12, 2013 at 12:14 PM, Raghavendra < raghavendra@enterprisedb.com> wrote: > > On Fri, Jul 12, 2013 at 11:48 AM, itishree sukla > wrote: > >> He

Re: [GENERAL] V8.4 TOAST table problem

Hi, I have a very similar problem... details below. On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles wrote: > Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to > take advantage of autovacuum features. This server exists in a very closed > environment (isolated network, limited

Re: [GENERAL] Full text search

On Fri, Jul 12, 2013 at 12:56 PM, itishree sukla wrote: > Hi Raghavendra, > > Thanks for your response, however i want same kind of result using full text > search. is it possible? > What do you mean when you say you are using full text search? Like what is the query that you used? That would be

[GENERAL] function returning select result to JDBC

I need to write a function (invoked by a jdbc client) that returns select query result. That function could return also set of row. How should i declare return type of function? -- View this message in context: http://postgresql.1045698.n5.nabble.com/function-returning-select-result-to-JDBC-tp

Re: [GENERAL] function returning select result to JDBC

On Fri, Jul 12, 2013 at 3:26 PM, giozh wrote: > I need to write a function (invoked by a jdbc client) that returns select > query result. > That function could return also set of row. How should i declare return > type > of function? > > > Bunch of examples here: http://wiki.postgresql.org/wiki/R

Re: [GENERAL] How can you get "WAL segment has already been removed" when doing synchronous replication ?!

On Fri, Jul 12, 2013 at 12:30:22PM +0530, Amit Langote wrote: > >> Increasing the wal_keep_segments ? > > I know that I can increase wal_keep_segments to "solve" it, but > > shouldn't it be *impossible* to happen with synchronous replication? > > After all - all commits should wait for slave to be

[GENERAL]

Hi all I'm making some experiments with table archiving and I'd like to "replace" a full table F with an empty one E. In order to do this I see only one way: ALTER TABLE F RENAME TO T; ALTER TABLE E RENAME TO F; ALTER TABLE T RENAME TO E; -- optional This implies there's a moment when the full ta

[GENERAL] Changing the function used in an index.

How does the planner know that the function used in an index has changed? If the function's body is changed keeping the argument and return types the planner will not use it anymore: create table t (i integer); insert into t (i) select generate_series(1, 10); analyze t; A simple function to r

Re: [GENERAL] Changing the function used in an index.

Clodoaldo Neto wrote > How does the planner know that the function used in an index has changed? > If the function's body is changed keeping the argument and return types > the > planner will not use it anymore: Not a hacker but the function catalog entry is MVCC just like any other record and so

[GENERAL] Distributed systems and primary keys

Hello list, Can anyone point me to some reading material on how auto-generated sequence primary keys are handled on distributed systems? I think the advice used to be to use GUIDs, but I thought I read somewhere that PostgreSQL now assigns a pool of numbers to each node when a sequence is implemen

Re: [GENERAL] transactional swap of tables

Vincenzo Romano wrote: > I'd like to "replace" a full table F with an empty one E. > In order to do this I see only one way: > > ALTER TABLE F RENAME TO T; > ALTER TABLE E RENAME TO F; > ALTER TABLE T RENAME TO E; -- optional > > This implies there's a moment when the full table doesn't exist. >

Re: [GENERAL] Distributed systems and primary keys

On 07/12/2013 07:23 AM, Melvin Call wrote: Hello list, Can anyone point me to some reading material on how auto-generated sequence primary keys are handled on distributed systems? I think the advice used to be to use GUIDs, but I thought I read somewhere that PostgreSQL now assigns a pool of nu

Re: [GENERAL] transactional swap of tables

> Vincenzo Romano wrote: > >> I'd like to "replace" a full table F with an empty one E. >> In order to do this I see only one way: >> >> ALTER TABLE F RENAME TO T; >> ALTER TABLE E RENAME TO F; >> ALTER TABLE T RENAME TO E; -- optional >> >> This implies there's a moment when the full table doesn'

[GENERAL] pg_upgrade could not create catalog dump while upgrading from 9.0 to 9.2

Hello, I am upgrading from postgresql 9.0 to 9.2 using the utility pg_upgrade. Creation of catalog dump failed .The reason is in 9.2 view pg_roles has a column "rolecreatedblink" which is missing in 9.0.Please help me to move forward. Thanks in Advance *Last lines in pg_upgradeutility.log*

Re: [GENERAL] V8.4 TOAST table problem

David, (As a preface, I have already gone forward with completely rebuilding the database which seems to have finally fixed the problem. Rebuilding the table itself had no effect, and I couldn't wait much longer to move forward.) Yes, this seems similar, however, the key difference being that VA

Re: [GENERAL] Distributed systems and primary keys

In Postgres-XC, GTM assigns sequence value to all the transactions in its cluster. XC is a kind of tightly-coupled distributed system. In a loosely-coupled distributed system, where each database is autonomous, we may need another mechanism. I've learned that logical replication (used to be bi

Re: [GENERAL] Distributed systems and primary keys

On Fri, Jul 12, 2013 at 9:46 AM, Joshua D. Drake wrote: > > On 07/12/2013 07:23 AM, Melvin Call wrote: > >> Hello list, >> >> Can anyone point me to some reading material on how auto-generated >> sequence primary keys are handled on distributed systems? I think the >> advice used to be to use GUID

Re: [GENERAL] Distributed systems and primary keys

On Fri, Jul 12, 2013 at 10:04 AM, Koichi Suzuki wrote: > In Postgres-XC, GTM assigns sequence value to all the transactions in its > cluster. XC is a kind of tightly-coupled distributed system. In a > loosely-coupled distributed system, where each database is autonomous, we > may need another

Re: [GENERAL] V8.4 TOAST table problem

Did you have a long running trasnaction? Especially a prepared transaction, blocking the vacuum from reclaiming the space? On Fri, Jul 12, 2013 at 8:10 AM, Bradley McCune wrote: > David, > > (As a preface, I have already gone forward with completely rebuilding the > database which seems to have f

Re: [GENERAL] transactional swap of tables

Vincenzo Romano wrote: > Would you please elaborate more on the "wait[ing] long enough > after the COMMIT" ? You can note the time when you commit the transaction, and then poll pg_stat_activity until there are no active transactions which started before that. You can sometimes simplify this a

Re: [GENERAL] Changing the function used in an index.

Clodoaldo Neto writes: > How does the planner know that the function used in an index has changed? > If the function's body is changed keeping the argument and return types the > planner will not use it anymore: Ordinarily, changing a function definition like that *would* break things. The only

Re: [GENERAL] V8.4 TOAST table problem

The only transactions present were "" for current_query. I even stopped the remote services, restarted the PostgreSQL server (assumingly, there should be no transactions occurring now), and performed another VACUUM FULL followed by REINDEX due to known fullvac index bloat in pre-9 pgsql version.

Re: [GENERAL] How can you get "WAL segment has already been removed" when doing synchronous replication ?!

On Thu, Jul 11, 2013 at 11:01 AM, hubert depesz lubaczewski wrote: > On Thu, Jul 11, 2013 at 11:29:24PM +0530, Raghavendra wrote: >> On Thu, Jul 11, 2013 at 11:18 PM, hubert depesz lubaczewski < >> dep...@depesz.com> wrote: >> >> > >> > Yet, every now and then we're getting: >> > FATAL: requested

Re: [GENERAL] transactional swap of tables

On Fri, Jul 12, 2013 at 9:24 AM, Kevin Grittner wrote: > Vincenzo Romano wrote: > >> I'd like to "replace" a full table F with an empty one E. >> In order to do this I see only one way: >> >> ALTER TABLE F RENAME TO T; >> ALTER TABLE E RENAME TO F; >> ALTER TABLE T RENAME TO E; -- optional >> >>

Re: [GENERAL] initdb of pg 9.0.13 fails on pg_authid

Hello! I also have the same problem and same configuration (x86_64, gcc 4.8.0), but -O flag didn't help me. I've used few variants, like: ./configure --prefix=/usr CFLAGS="-O" ./configure --prefix=/usr CFLAGS="-O0" ./configure --prefix=/usr CFLAGS="-O1" ./configure --prefix=/usr CFLAGS="-O2" ./conf

Re: [GENERAL] pg_upgrade could not create catalog dump while upgrading from 9.0 to 9.2

On 07/12/2013 04:56 AM, shankar.kotamar...@gmail.com wrote: Hello, I am upgrading from postgresql 9.0 to 9.2 using the utility pg_upgrade. Creation of catalog dump failed .The reason is in 9.2 view pg_roles has a column "rolecreatedblink" which is missing in 9.0.Please help me to move forward. Th

Re: [GENERAL] V8.4 TOAST table problem

Idle in Transaction? Or plain Idle? Idle in Transaction stops vacuum from reclaiming space and is indicative of a broken application. On Fri, Jul 12, 2013 at 9:39 AM, Bradley McCune wrote: > The only transactions present were "" for current_query. I even > stopped the remote services, restarted

Re: [GENERAL] V8.4 TOAST table problem

Prepared transactions that are sitting still do the same thing, and show no connections. On Fri, Jul 12, 2013 at 2:25 PM, Scott Marlowe wrote: > Idle in Transaction? Or plain Idle? Idle in Transaction stops vacuum from > reclaiming space and is indicative of a broken application. > > > On Fri, J

Re: [GENERAL] V8.4 TOAST table problem

Scott, Purely idle. I compared these transactions with our other "healthy" databases, and they checked out. On Fri, Jul 12, 2013 at 4:25 PM, Scott Marlowe wrote: > Prepared transactions that are sitting still do the same thing, and show > no connections. > > > On Fri, Jul 12, 2013 at 2:25 PM,

Re: [GENERAL] V8.4 TOAST table problem

So what id select * from pg_prepared_xacts ; show? On Fri, Jul 12, 2013 at 2:30 PM, Bradley McCune wrote: > Scott, > > Purely idle. I compared these transactions with our other "healthy" > databases, and they checked out. > > > On Fri, Jul 12, 2013 at 4:25 PM, Scott Marlowe wrote: > >> Prepared

Re: [GENERAL] V8.4 TOAST table problem

Well, the issue was corrected by completely rebuilding the database a few days ago (all the way to reinitializing the database directory). With that said, I did check that table at the time, and I received an empty result set from such a SELECT statement. The same goes for max_prepared_transactio

Re: [GENERAL] V8.4 TOAST table problem

It's always a good idea to keep a copy of the database for a post mortem if possible. If you've found a bug, it's nice to find and fix it. If you were suffering from an operational failure of some sort, then it helps to figure that out too. On Fri, Jul 12, 2013 at 2:42 PM, Bradley McCune wrote:

[GENERAL] Build RPM from Postgres Source

Hi, I need to have some different settings(like NAMEDATALEN etc.) and that's why I'm trying to build postgres from the source and to create an rpm to be send for install. Now, the build works fine but, I don't see a way to create an RPM. I'll greatly appreciate any help. Thanks, Kul --

[GENERAL] passing text value with single quote to stored procedure

Hi all I am currently having an issue with passing a value with a single quote in it to stored procedure i am trying to pass this text value QA_SUMMER ' 2013_(EU/US) to stored procedure text variable. it gives me error. Here is more detail on this My stored procedure is something like

Re: [GENERAL] passing text value with single quote to stored procedure

On 07/12/2013 03:05 PM, Prabhjot Sheena wrote: Hi all I am currently having an issue with passing a value with a single quote in it to stored procedure i am trying to pass this text value QA_SUMMER ' 2013_(EU/US) to stored procedure text variable. it gives me error. Here is more de

[GENERAL] Re: pg_upgrade could not create catalog dump while upgrading from 9.0 to 9.2

Thanks Adrian for your reply.You were right i am using Enterprise DB version for 9.2 and community version for 9.0. I am including the screenshot -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-u