Re: [GENERAL] BDR replication slots

2016-04-19 6:51 GMT+02:00 Nikhil : > Hello, > > I have a 2 node BDR group and replication is happening properly. if i > bring down one of the node's interface, after sometime the replication > slots are becoming inactive (pg_replication_slots view). Then if i bring > back interface slots are not t

Re: [GENERAL] Re: what's the exact command definition in read committed isolation level?

> > First session: > postgres=# create table t(id integer); > CREATE TABLE > postgres=# insert into t values (1); > INSERT 0 1 > postgres=# begin; > BEGIN > postgres=# select count(*) from t; > count > --- > 1 > (1 row) > > 1 row in the table. In another session, insert another row in t:

Re: [GENERAL] BDR replication slots

On interface down: -- <10.102.31.213(27599)postgres13082016-04-19 06:31:36 GMTprocess_journal%LOG: terminating walsender process due to replication timeout Once interface is brought back 425906 <12692016-04-19 08:32:58 GMT%LOG: starting

[GENERAL] How are files of tables/indexes/etc deleting?

When I drop a table, the file which contains real data will be deleted, but only when I will commit transaction. It seems like such job must do vacuum, but I cannot locate the code which does the real job. Could anybody explain in details how it works?

Re: [GENERAL] Multimaster

On 18 April 2016 at 16:28, Konstantin Knizhnik wrote: > I intend to make the same split in pglogical its self - a receiver and > apply worker split. Though my intent is to have them communicate via a > shared memory segment until/unless the apply worker gets too far behind and > spills to disk.

Re: [GENERAL] error while installing auto_explain contrib module

On 04/18/2016 11:46 PM, Sachin Kotwal wrote: Hi, I am trying to install auto_explain on PostgreSQL-9.3.11 OS X EI Capitan - 10.11 Did you install the 9.3.11 server from source also? I am getting following error: -- $ pwd /Users/sachin/postgres_git/postgres/contrib/auto_explain What

[GENERAL] Vacuum never completed....

We had a server approach wraparound yesterday on a 9.3 box. I restarted in single user mode and only one table was above autovacuum_freeze_max_age. The dataset was ~750G. We left vacuum running for 21hrs and it still did not complete. What was interesting is that disk IO graph kept cycling, lot

Re: [GENERAL] error while installing auto_explain contrib module

On Tue, Apr 19, 2016 at 7:02 PM, Adrian Klaver wrote: > On 04/18/2016 11:46 PM, Sachin Kotwal wrote: > >> Hi, >> >> I am trying to install auto_explain on PostgreSQL-9.3.11 >> OS X EI Capitan - 10.11 >> > > Did you install the 9.3.11 server from source also? Yes. I have clone git repository and

Re: [GENERAL] Vacuum never completed....

On Apr 19, 2016 6:37 AM, wrote: > > We had a server approach wraparound yesterday on a 9.3 box. I restarted in single user mode and only one table was above autovacuum_freeze_max_age. The dataset was ~750G. We left vacuum running for 21hrs and it still did not complete. What was interesting is tha

Re: [GENERAL] How are files of tables/indexes/etc deleting?

Иван Фролков wrote: > When I drop a table, the file which contains real data will be deleted, but > only when I will commit > transaction. It seems like such job must do vacuum, but I cannot locate the > code which does the real > job. Could anybody explain in details how it works? See RelationD

Re: [GENERAL] error while installing auto_explain contrib module

Sachin Kotwal writes: > On Tue, Apr 19, 2016 at 7:02 PM, Adrian Klaver > wrote: >> Did you install the 9.3.11 server from source also? > Yes. I have clone git repository and checkout for pg-9.3.11 The reason for that question is that the most obvious explanation for this failure is that the au

Re: [GENERAL] Re: what's the exact command definition in read committed isolation level?

2016-04-18 23:16 GMT+08:00 Tom Lane : > > No, that's not true: a trigger is a function and what it can see is > determined by the rules of the PL it's written in. Typically a > function that's marked STABLE or IMMUTABLE will see the same snapshot > as the calling query, but a function that's VOLAT

Re: [GENERAL] Multimaster

On 19.04.2016 15:56, Craig Ringer wrote: On 18 April 2016 at 16:28, Konstantin Knizhnik mailto:k.knizh...@postgrespro.ru>> wrote: I intend to make the same split in pglogical its self - a receiver and apply worker split. Though my intent is to have them communicate via a shared m

[GENERAL] Enhancement Request

I'd like to propose two enhancements to the PostgreSQL code, but I'm not sure if this is the correct mailing list. So if it's not then please let me know where I need to post this. These are monitoring-centric enhancement requests since I'm trying to implement accurate monitoring in a secure f

[GENERAL] index build faster on 8G laptop than 30G server

I've been running an index build for almost an hour on my 30G server that takes ~ 20 mins on my puny old macbook. It seems like I've tuned all I can.. what am I missing? Thanks, Bill Records to index: 33305041 --- Server: PostgreSQL 9.2.15 on x86_64-redhat-linux-gnu, compiled by gcc (GCC)

Re: [GENERAL] index build faster on 8G laptop than 30G server

On 4/19/2016 2:28 PM, Bill Ross wrote: I've been running an index build for almost an hour on my 30G server that takes ~ 20 mins on my puny old macbook. It seems like I've tuned all I can.. what am I missing? maintenance_work_mem is the main thing that affects index building times, I didn

Re: [GENERAL] index build faster on 8G laptop than 30G server

I see you are comparing 9.2 and 9.4 -- if you were not already aware there is a kernel restriction on shared memory on Linux systems which, if I recall correctly, must be adjusted in versions before 9.3... https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#shared_buffers Not saying it

Re: [GENERAL] index build faster on 8G laptop than 30G server

On Tue, Apr 19, 2016 at 5:28 PM, Bill Ross wrote: > I've been running an index build for almost an hour on my 30G server that > takes ~ 20 mins on my puny old macbook. > > It seems like I've tuned all I can.. what am I missing? > > Concurrent traffic on the server ? Locks / conflicts with running

Re: [GENERAL] index build faster on 8G laptop than 30G server

On Tue, Apr 19, 2016 at 2:30 PM, John R Pierce wrote: > On 4/19/2016 2:28 PM, Bill Ross wrote: >> >> I've been running an index build for almost an hour on my 30G server that >> takes ~ 20 mins on my puny old macbook. >> >> It seems like I've tuned all I can.. what am I missing? > > > > > maintena

Re: [GENERAL] index build faster on 8G laptop than 30G server

Thanks for the fast response! Server was completely idle except the one client (and one doing a slow update that I forgot). Updating maintenance_work_mem to 8G I see more memory now in use: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 4531 ec2-user 20 0 10.1g 3

[GENERAL] Function PostgreSQL 9.2

Hi all, I've got two tables: - users - companies I'm trying to create a function that: - if users.code is empty, it gives a default value - And the increment_client_code in company should auto increment for the next client code What I've done so far: DROP FUNCTION IF EXISTS client_c

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, Apr 19, 2016 at 3:23 PM, drum.lu...@gmail.com wrote: > Hi all, > > I've got two tables: > > - users > - companies > > I'm trying to create a function that: > > >- if users.code is empty, it gives a default value >- And the increment_client_code in company should auto increment for

Re: [GENERAL] Function PostgreSQL 9.2

On 20 April 2016 at 10:38, David G. Johnston wrote: > On Tue, Apr 19, 2016 at 3:23 PM, drum.lu...@gmail.com < > drum.lu...@gmail.com> wrote: > >> Hi all, >> >> I've got two tables: >> >> - users >> - companies >> >> I'm trying to create a function that: >> >> >>- if users.code is empty, it gi

Re: [GENERAL] Function PostgreSQL 9.2

On 04/19/2016 03:23 PM, drum.lu...@gmail.com wrote: Hi all, I've got two tables: - users - companies I'm trying to create a function that: * if users.code is empty, it gives a default value * And the increment_client_code in company should auto increment for the next client code What

Re: [GENERAL] Enhancement Request

On Tue, Apr 19, 2016 at 4:37 PM, Rob Brucks wrote: > I'd like to propose two enhancements to the PostgreSQL code, but I'm not > sure if this is the correct mailing list. So if it's not then please let > me know where I need to post this. > > These are monitoring-centric enhancement requests sinc

Re: [GENERAL] How do BEGIN/COMMIT/ABORT operate in a nested SPI query?

> From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] > > > I am attempting to create a new language implementation. The language > > is Andl (andl.org), so the handler is plandl. > > This is a question about executing SPI queries from inside plandl. > > > > The documentation makes it clear that S

[GENERAL] [ADMIN] how to upgrade PG

Hello, Our PG runs on Ubuntu boxes and it's 9.3.4, thinking of upgrading to 9.3.12 or 9.5.2. Is the normal approach of upgrade something like below, no matter if it's major version or minor version upgrade? My focus is on pg_dump at the moment. #1 pg_dump Dump DB Apt-get update Apt-get install

Re: [GENERAL] Enhancement Request

On Tue, Apr 19, 2016 at 5:59 PM, Melvin Davidson wrote: > > Enhancement or feature requests should probably go to *Custormer > Feedback * > https://postgresql.uservoice.com/forums/21853-general > ​I'm on the PostgreSQL home page: what serie

Re: [GENERAL] [ADMIN] how to upgrade PG

On Mon, Apr 18, 2016 at 10:19 PM, Huang, Suya wrote: > Hello, > > > > Our PG runs on Ubuntu boxes and it’s 9.3.4, thinking of upgrading to > 9.3.12 or 9.5.2. > > > > Is the normal approach of upgrade something like below, no matter if it’s > major version or minor version upgrade? My focus is on

Re: [GENERAL] Function PostgreSQL 9.2

> > Information. eg.: > > The schema for the tables. > > Why is not just adding a DEFAULT value to the users.code not an option? > > The customer can add their own value to the users.code column. That's why I can't have a default value. > What the default code should be or how it is to be calc

Re: [GENERAL] Function PostgreSQL 9.2

Just forgot to say: [...] > > >> >> Does increment_client_code relate to users or some other table, say >> clients? >> >> > nope.. there is no link between them > > If the users.code is empty/null, then the trigger has to get the last number from client_code_increment and put on the users.code

Re: [GENERAL] Function PostgreSQL 9.2

This is such a poorly designed, hacked together “thing” – it isn’t a database, it’s someone’s idea of how to store data when they don’t know how to store data, like they moved it from Access or Excel. Just start over and design a proper relational schema with best practices and you’ll save