Re: [GENERAL] Alternative to Multi-Master Replication with 2 Data centers??

On sáb, 2014-03-29 at 12:50 -0700, John R Pierce wrote: > On 3/29/2014 11:35 AM, ethode wrote: > > Currently we're considering several options, of which Magic > > replication appears to be the top option. > > fixed that for you, hope that helps! > > kidding aside, there's no magic bullet here tha

Re: [ADMIN][GENERAL] openvz and shared memory trouble

On Sat, Mar 29, 2014 at 6:17 PM, Adrian Klaver wrote: > On 03/29/2014 08:19 AM, Willy-Bas Loos wrote: > >> The error that shows up is a Bus error. >> That's on the replication slave. >> Here's the log about it: >> 2014-03-29 12:41:33 CET db: ip: us: FATAL: could not receive data from >> WAL strea

[GENERAL] PSQL log file

Hi Is there any option in psql version 9.2.4, to insert the date time also inside the psql log files(in path /var/log/postgresql) ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PSQL log file

> Is there any option in psql version 9.2.4, to insert the date time > also inside the psql log files(in path /var/log/postgresql) ? If you look in postgresql.conf for your installation, you can change the log prefix by setting log_line_prefix. If you add %t to that string, you'll get a timesta

Re: [ADMIN][GENERAL] openvz and shared memory trouble

On 03/31/2014 04:12 AM, Willy-Bas Loos wrote: On Sat, Mar 29, 2014 at 6:17 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 03/29/2014 08:19 AM, Willy-Bas Loos wrote: The error that shows up is a Bus error. That's on the replication slave. Here's the l

Re: [ADMIN][GENERAL] openvz and shared memory trouble

On 03/31/2014 04:12 AM, Willy-Bas Loos wrote: On Sat, Mar 29, 2014 at 6:17 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: And it makes me wonder what else may be issues that arise from that. But especially, what i can do about it. Had to go through it a couple of times, and lo

Re: [ADMIN][GENERAL] openvz and shared memory trouble

Adrian Klaver writes: > On 03/31/2014 04:12 AM, Willy-Bas Loos wrote: >> I'm still worried that it's like Tom Lane said in another discussion:"So >> basically, you've got a broken kernel here: it claimed to give PG circa >> (135MB) of memory, but what's actually there is only about (128MB). I >> d

[GENERAL] char array overhead

I'm angling toward using a very wide char(1) array. Is the one-byte overhead for char(n<126) applied to each element or to the array?

Re: [ADMIN][GENERAL] openvz and shared memory trouble

On 03/31/2014 08:01 AM, Tom Lane wrote: Adrian Klaver writes: On 03/31/2014 04:12 AM, Willy-Bas Loos wrote: I'm still worried that it's like Tom Lane said in another discussion:"So basically, you've got a broken kernel here: it claimed to give PG circa (135MB) of memory, but what's actually th

Re: [ADMIN][GENERAL] openvz and shared memory trouble

Adrian Klaver writes: > At this point the memory allocation as a problem is as much conjecture > as anything else, at least to me. So what is causing SIGBUS is an open > question in my mind. Agreed, it's unproven what's causing the SIGBUS in the case at hand. However, in the case I investigated

Re: [GENERAL] char array overhead

On Mar 31, 2014, at 8:08 AM, Rob Sargent wrote: > I'm angling toward using a very wide char(1) array. Is the one-byte overhead > for char(n<126) applied to each element or to the array? Each element, it's a variable length type. There's probably a better way of storing your data, but if you

Re: [GENERAL] Alternative to Multi-Master Replication with 2 Data centers??

> We are load balancing 2 data centers. Chapter 8 of Scalable Internet Architectures has a good discussion of running master-master setups in separate data centers. I'd read that whole chapter for some of the challenges you'll face. > If DC1 goes down our LB is failing over to DC2. This sounds l

Re: [GENERAL] char array overhead

On 03/31/2014 09:48 AM, Steve Atkins wrote: On Mar 31, 2014, at 8:08 AM, Rob Sargent wrote: I'm angling toward using a very wide char(1) array. Is the one-byte overhead for char(n<126) applied to each element or to the array? Each element, it's a variable length type. There's probably a be

Re: [GENERAL] Complex query

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Leonardo M. Ramé > Sent: Monday, March 31, 2014 2:38 PM > To: PostgreSql-general > Subject: [GENERAL] Complex query > > Hi, I'm looking for help with this query. > >

Re: [GENERAL] Complex query

On 31/03/2014 19:38, Leonardo M. Ramé wrote: > Hi, I'm looking for help with this query. > > Table Tasks: > > IdTask StatusCode StatusName > -- > 1 R Registered > 1 S Started > 1 D Dictated > 1 F Fi

[GENERAL] Wanted: ALTER TRIGGER ... OWNED BY EXTENSION

An extension I'm working on dynamically creates trigger functions and installs them on tables in public. The triggers are automatically created when one of the extension's config tables is populated. Even though I mark the trigger *functions *as owned by my extensi

Re: [GENERAL] Complex query

Leonardo M. Ramé-2 wrote > Hi, I'm looking for help with this query. > > Table Tasks: > > IdTask StatusCode StatusName > -- > 1 R Registered > 1 S Started > 1 D Dictated > 1 F Finished > 1 T

Re: [GENERAL] Complex query

On 2014-03-31 18:48:58 +, Igor Neyman wrote: > > -Original Message- > > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > > ow...@postgresql.org] On Behalf Of Leonardo M. Ramé > > Sent: Monday, March 31, 2014 2:38 PM > > To: PostgreSql-general > > Subject: [GENERAL] Comp

[GENERAL] getting the current query from pg_stat_activity

Hello, I have two different postgresql servers running slightly versions. On one them, if I try to use pg_stat_activity to get the current queries, I get 1$ psql psql (9.0.13) Type "help" for help. postgres=# select procpid, query_start, waiting, current_query from pg_stat_activity; procpid |

Re: [GENERAL] Complex query

> -Original Message- > From: Leonardo M. Ramé [mailto:l.r...@griensu.com] > Sent: Monday, March 31, 2014 2:56 PM > To: Igor Neyman > Cc: PostgreSql-general > Subject: Re: [GENERAL] Complex query > > On 2014-03-31 18:48:58 +, Igor Neyman wrote: > > > -Original Message- > > > Fro

Re: [GENERAL] char array overhead

Rob Sargent wrote: > Jsyk, I'm toying with a rather large number of small valued > datapoints per sample. I'm tying text and smallint as well. You could try "char"[] ... -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services --

Re: [GENERAL] getting the current query from pg_stat_activity

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Si Chen Sent: Monday, March 31, 2014 2:57 PM To: pgsql-general@postgresql.org Subject: [GENERAL] getting the current query from pg_stat_activity Hello, I have two different postgresql servers runn

[GENERAL] Complex query

Hi, I'm looking for help with this query. Table Tasks: IdTask StatusCode StatusName -- 1 R Registered 1 S Started 1 D Dictated 1 F Finished 1 T Transcribed --

Re: [GENERAL] Wanted: ALTER TRIGGER ... OWNED BY EXTENSION

Moshe Jacobson wrote: > Even though I mark the trigger *functions *as owned by my extension, the > trigger definitions themselves are still dumped by pg_dump and restored by > pg_restore. This is a problem when pg_restore is using parallelism (-j), > and one thread starts issuing CREATE TRIGGER co

Re: [GENERAL] Complex query

On 2014-03-31 11:46:28 -0700, David Johnston wrote: > Leonardo M. Ramé-2 wrote > > Hi, I'm looking for help with this query. > > > > Table Tasks: > > > > IdTask StatusCode StatusName > > -- > > 1 R Registered > > 1 S Started > > 1

Re: [GENERAL] Complex query

Leonardo M. Ramé-2 wrote > select lag.id, lag.idtask, lag.code, lag.lg from (select idtask, code, id, > lag(code, -1) over () as lg from tasks_test) as lag First you want to include an ORDER BY in the OVER(...) clause, and probably a PARTITION BY as well. Then you move that to a sub-query (for e

Re: [ADMIN][GENERAL] openvz and shared memory trouble

On 03/31/2014 08:28 AM, Tom Lane wrote: Adrian Klaver writes: At this point the memory allocation as a problem is as much conjecture as anything else, at least to me. So what is causing SIGBUS is an open question in my mind. Agreed, it's unproven what's causing the SIGBUS in the case at hand.

Re: [GENERAL] Complex query

On 2014-03-31 12:16:53 -0700, David Johnston wrote: > Leonardo M. Ramé-2 wrote > > select lag.id, lag.idtask, lag.code, lag.lg from (select idtask, code, id, > > lag(code, -1) over () as lg from tasks_test) as lag > > First you want to include an ORDER BY in the OVER(...) clause, and probably > a

[GENERAL] Getting sequence-generated IDs from multiple row insert

Hi folks, I've just run into a subtle but fairly serious race condition while using web.py's SQL library to insert multiple rows into the database and return their IDs (a "serial primary key" column). Specifically I'm using the multiple_insert() function that web.py defines here: https://github.c

Re: [GENERAL] Why does "checkpointer" is consumig ~1.2Gb of RAM?

Em 31/03/2014 00:38, Scott Marlowe escreveu: On Sun, Mar 30, 2014 at 8:43 PM, Edson Richter wrote: I'm curious about the "checkpointer" process and its

Re: [GENERAL] Getting sequence-generated IDs from multiple row insert

On Mon, Mar 31, 2014 at 03:28:14PM -0400, Ben Hoyt wrote: > , but I've just hit a case where two sessions each doing a > multiple insert don't use sequential IDs. For example, the range code above > for the first insert gave 2117552...2117829. And the second insert gave > 2117625...2117818. Which

Re: [GENERAL] getting the current query from pg_stat_activity

Si Chen-2 wrote > I have two different postgresql servers running slightly [different] > versions. Versions 9.0 and 9.2 are NOT slightly different. These are two MAJOR RELEASES (which allow for API changes) apart (i.e., one major release in between - 9.1) The release notes for 9.2 note this par

Re: [GENERAL] Why does "checkpointer" is consumig ~1.2Gb of RAM?

It's not. If the RES shows 1215M And the SHR is 1214M, then checkpointer is only using 1M. The difference between the two is the shared memory. RES includes SHR. On Mon, Mar 31, 2014 at 1:44 PM, Edson Richter wrote: > Em 31/03/2014 00:38, Scott Marlowe escreveu: > > > On Sun, Mar 30, 2014 at 8:

Re: [GENERAL] Wanted: ALTER TRIGGER ... OWNED BY EXTENSION

Alvaro Herrera writes: > Moshe Jacobson wrote: >> Even though I mark the trigger *functions *as owned by my extension, the >> trigger definitions themselves are still dumped by pg_dump and restored by >> pg_restore. This is a problem when pg_restore is using parallelism (-j), >> and one thread sta

Re: [GENERAL] getting the current query from pg_stat_activity

Thanks! That's very helpful and answers my question. On Mon, Mar 31, 2014 at 12:52 PM, David Johnston wrote: > Si Chen-2 wrote > > I have two different postgresql servers running slightly [different] > > versions. > > Versions 9.0 and 9.2 are NOT slightly different. These are two MAJOR > RELE

Re: [GENERAL] Wanted: ALTER TRIGGER ... OWNED BY EXTENSION

On Mon, Mar 31, 2014 at 4:20 PM, Tom Lane wrote: > I'm suspicious that the problem is exactly lack of pg_depend records --- > pg_dump/pg_restore relies on those for correct ordering of parallel > operations. What method are you using to create these triggers (not > the functions, the per-table p

Re: [GENERAL] Getting sequence-generated IDs from multiple row insert

Andrew Sullivan-8 wrote >> So currently I've changed my code to use RETURNING and then I'm ordering >> the results based on a secondary column that I know the order of. This >> works, but seems clunky, so I'm wondering if there's a nicer way. > > This is probably what I'd do, assuming that "furthe

Re: [GENERAL] Wanted: ALTER TRIGGER ... OWNED BY EXTENSION

On Mon, Mar 31, 2014 at 4:32 PM, Moshe Jacobson wrote: > There is a trigger function on the extension's config table that creates > the trigger functions and installs them (both from the same function). I am > about to try playing with pg_depend to see if it has the desired effect. I've added t

Re: [GENERAL] Why does "checkpointer" is consumig ~1.2Gb of RAM?

Em 31/03/2014 17:06, Scott Marlowe escreveu: It's not. If the RES shows 1215M And the SHR is 1214M, then checkpointer is only using 1M. The difference between the two is the shared memory. RES includes SHR. Thanks, now I understa

Re: [GENERAL] Why does "checkpointer" is consumig ~1.2Gb of RAM?

And yet I still got it wrong. Bad day for me. Should be: The difference between the two is the memory it's using. Bad day apparently. On Mon, Mar 31, 2014 at 2:46 PM, Edson Richter wrote: > Em 31/03/2014 17:06, Scott Marlowe escreveu: > > It's not. If the RES shows 1215M And the SHR is 1214M,

Re: [GENERAL] Getting sequence-generated IDs from multiple row insert

On Mon, Mar 31, 2014 at 01:34:04PM -0700, David Johnston wrote: > > If order is an implicit property of the source data then you need to > explicitly encode that order during (or before) import. Sure, but the problem the OP had I thought was that the RETURNING clause doesn't guarantee that the r

Re: [GENERAL] Wanted: ALTER TRIGGER ... OWNED BY EXTENSION

Moshe Jacobson writes: > I've added the requisite rows to pg_depend, and I know it was correct > because \dx+ cyanaudit (my extension) now shows all of the triggers. > However, pg_dump still dumps them, and pg_restore still restores them, > causing the same errors as I had before. I don't think

Re: [GENERAL] Wanted: ALTER TRIGGER ... OWNED BY EXTENSION

On Mon, Mar 31, 2014 at 5:19 PM, Tom Lane wrote: > Basically what you need to end up with is > > * trigger function has a membership dependency on the extension > Yes, the dependency is set up when the trigger function is dynamically created by using ALTER EXTENSION ... ADD FUNCTION * pg_trigge

Re: [GENERAL] Postgres as In-Memory Database?

On Sunday, March 30, 2014, Stefan Keller wrote: > Hi Jeff > > 2013/11/20 Jeff Janes > > > > >> >> I don't know what you mean about enhancements in the buffer pool. For an >> in-memory database, there shouldn't be a buffer pool in the first place, as >> it is *all* in memory. >> > > You are rig

Re: [GENERAL] Doubts on startup costs

Start-up cost is the cost required to fetch the first tuple. So yes it is possible for the startup cost of innermost node/leaf node to be zero as show in below example:. postgres=# explain select * from tbl,tbl2 where tbl2.id=tbl.id order by tbl.id; QUERY PLA

Re: [GENERAL] Postgres as In-Memory Database?

On 01 Apr 2014, at 4:20, Jeff Janes wrote: > On Sunday, March 30, 2014, Stefan Keller wrote: > There seem to be two main things which make in-memory dbs special: > 1. Index: Having all data in memory there is no need for a default index. A > full-table scan "suddenly" becomes the default. > >