Re: [GENERAL] Setting up replication

On 03/17/2015 04:08 PM, Robert Fitzpatrick wrote: I have a question about setting up replication between my postgresql-9.3.6 servers. If I'm using pg_basebackup on my FreeBSD 10.1 slave server, the postgresql.conf file is in the data directory, which pg_basebackup insists must be empty. I can't f

[GENERAL] Setting up replication

I have a question about setting up replication between my postgresql-9.3.6 servers. If I'm using pg_basebackup on my FreeBSD 10.1 slave server, the postgresql.conf file is in the data directory, which pg_basebackup insists must be empty. I can't find any info about how to relocate the postgresq

Re: [GENERAL] Group by range in hour of day

> On Mar 17, 2015, at 1:41 PM, Marc Mamin wrote: > > >>> On Tue, Mar 17, 2015, at 11:30 AM, Adrian Klaver wrote: On 03/17/2015 10:57 AM, Israel Brewster wrote: > > >> On Mar 17, 2015, at 9:30 AM, Paul Jungwirth >> wrote: >> >> So next question: how do I get the

Re: [GENERAL] Group by range in hour of day

>>On Tue, Mar 17, 2015, at 11:30 AM, Adrian Klaver wrote: >>> On 03/17/2015 10:57 AM, Israel Brewster wrote: >>> > >>> > >>> >> On Mar 17, 2015, at 9:30 AM, Paul Jungwirth >>> >> wrote: >>> >> >>> >> So next question: how do I get the "active" time per hour from this? >>> >> >>> >> I think you j

Re: [GENERAL] Group by range in hour of day

> > >-- > Adrian Klaver > adrian.kla...@aklaver.com > >On Tue, Mar 17, 2015, at 11:30 AM, Adrian Klaver wrote: >> On 03/17/2015 10:57 AM, Israel Brewster wrote: >> > >> > >> >> On Mar 17, 2015, at 9:30 AM, Paul Jungwirth >> >> wrote: >> >> >> >> So next question: how do I get the "active" tim

Re: [GENERAL] bdr replication latency monitoring

Craig, Your response was very helpful, thank you. I was looking at some of the standard bits in Postgres like txid_current_snapshot() and txid_snapshot_xmin(). Can the results from txid_snapshot_xmin be used with pg_get_transaction_committime() to get the latency? Thanks again, Steve Boyle

Re: [GENERAL] Group by range in hour of day

-- Adrian Klaver adrian.kla...@aklaver.com On Tue, Mar 17, 2015, at 11:30 AM, Adrian Klaver wrote: > On 03/17/2015 10:57 AM, Israel Brewster wrote: > > > > > >> On Mar 17, 2015, at 9:30 AM, Paul Jungwirth > >> wrote: > >> > >> So next question: how do I get the "active" time per hour from

Re: [GENERAL] Re: Great Software Opportunities: VP, Front End Developer, etc. (Ruby, Python)

Alvaro Herrera wrote: > Can you please resend this to pgsql-j...@postgresql.org? We don't > normally publish job opportunities in pgsql-general, but we'll be happy > to have this in pgsql-jobs. Thanks. Oh well. Sorry about that. Evidently everybody has read the announce by now, I guess. -- Á

Re: [GENERAL] Unexpected custom type behavior using ROW(NULL)

On Sat, Mar 14, 2015 at 7:21 PM, Denver Timothy wrote: > In 9.4.1, I do this: > > CREATE TYPE my_test_type as (part1 text, part2 text); > > \pset null NULL > > WITH test_table(test_col) AS ( > VALUES (NULL::my_test_type), (ROW(NULL, NULL)::my_test_type) > ) > SELECT *, (test_col).part1, (test_

Re: [GENERAL] Slow query with join

Update : My query SELECT * FROM v_actor JOIN f_intervenant_ref ON (actor_id = ir_actor_id) WHERE ir_dos_id = '5226' took 7 secs. If I substitute the _RETURN rule for the view and add the above join, it takes 31 ms. Mark Watson Service au client - R&D Tél. 418 659-7272 ou 1 888 692-1050 www.j

[GENERAL] libs and upgrades

(reposting - should have originally posted here in general - sorry) To prepare for server upgrades I'm planning to update the clients on a set of servers from 9.1 to 9.4. The servers on which the clients are installed are running CentOS 5 i386. Somewhere between PostgreSQL 9.1 and 9.4 the com

Re: [GENERAL] Group by range in hour of day

Some weird edge cases to be careful about: activities that cross midnight. >> Activities that last more than one full day, >> e.g. start 3/15 and end 3/17. Right. And I will run into some of those (at least the crossing midnight), > so I'll keep an eye out. If you are running the report on mor

Re: [GENERAL] Group by range in hour of day

On 03/17/2015 10:57 AM, Israel Brewster wrote: On Mar 17, 2015, at 9:30 AM, Paul Jungwirth wrote: So next question: how do I get the "active" time per hour from this? I think you just SUM() over the intersection between each hourly window and each event, right? This might be easiest using

[GENERAL] Re: Great Software Opportunities: VP, Front End Developer, etc. (Ruby, Python)

Can you please resend this to pgsql-j...@postgresql.org? We don't normally publish job opportunities in pgsql-general, but we'll be happy to have this in pgsql-jobs. Thanks. On Sun, Mar 15, 2015 at 08:55:41PM -0700, Nicholas Meyler wrote: > My repeat client is continuing to grow and expand, seek

[GENERAL] Unexpected custom type behavior using ROW(NULL)

In 9.4.1, I do this: CREATE TYPE my_test_type as (part1 text, part2 text); \pset null NULL WITH test_table(test_col) AS ( VALUES (NULL::my_test_type), (ROW(NULL, NULL)::my_test_type) ) SELECT *, (test_col).part1, (test_col).part2, test_col IS NULL AS is_null FROM test_table; And I get this

Re: [GENERAL] Group by range in hour of day

> On Mar 17, 2015, at 9:30 AM, Paul Jungwirth > wrote: > > So next question: how do I get the "active" time per hour from this? > > I think you just SUM() over the intersection between each hourly window and > each event, right? This might be easiest using tsrange, something like this: Soun

Re: [GENERAL] Group by range in hour of day

On Tuesday, March 17, 2015, Israel Brewster wrote: > > > > On Mar 17, 2015, at 8:09 AM, Paul Jungwirth > wrote: > > > >>> test=> select h, count(*) from start_end, generate_series(0, 23) as > s(h) where h between extract(hour from start_time) and extract(hour from > end_time) group by h order by

Re: [GENERAL] Group by range in hour of day

So next question: how do I get the "active" time per hour from this? I think you just SUM() over the intersection between each hourly window and each event, right? This might be easiest using tsrange, something like this: SUM(extract(minutes from (tsrange(start_time, end_time) && tsrange(

Re: [GENERAL] Group by range in hour of day

On Mar 17, 2015, at 9:05 AM, David G. Johnston wrote: > > On Tuesday, March 17, 2015, Israel Brewster > wrote: > > > > On Mar 17, 2015, at 8:09 AM, Paul Jungwirth > > wrote: > > > >>> test=> select h, count(*) from start_end, generate_series(0, 23) as s(h) > >>

Re: [GENERAL] Group by range in hour of day

> On Mar 17, 2015, at 8:09 AM, Paul Jungwirth > wrote: > >>> test=> select h, count(*) from start_end, generate_series(0, 23) as s(h) >>> where h between extract(hour from start_time) and extract(hour from >>> end_time) group by h order by h; >>> >>> h | count >>> +--- >>> 8 |

[GENERAL] select from table1 and table3 where (how table1 and table3 are related) is stored in table2?

see http://sqlfiddle.com/#!15/e30d9/8/0 for schema and sql. http://stackoverflow.com/questions/12238621/sql-subquery-has-too-many-columns -- 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] Group by range in hour of day

test=> select h, count(*) from start_end, generate_series(0, 23) as s(h) where h between extract(hour from start_time) and extract(hour from end_time) group by h order by h; h | count +--- 8 | 2 9 | 3 10 | 2 11 | 2 Note if you always want all 24 rows with a count

Re: [GENERAL] Group by range in hour of day

BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;9

Re: [GENERAL] Reg: PL/pgSQL commit and rollback

On 17 March 2015 at 15:30, Medhavi Mahansaria wrote: > Yes. I have read this document. > > But my issue is that even when it throws and exception I need to rollback > the changes made by that query and move on to the next block. > > Is there any way to accomplish that? Please do not toppost on th

Re: [GENERAL] Reg: PL/pgSQL commit and rollback

On 03/17/2015 07:30 AM, Medhavi Mahansaria wrote: Yes. I have read this document. But my issue is that even when it throws and exception I need to rollback the changes made by that query and move on to the next block. Is there any way to accomplish that? "If no error occurs, this form of bloc

Re: [GENERAL] Reg: PL/pgSQL commit and rollback

Yes. I have read this document. But my issue is that even when it throws and exception I need to rollback the changes made by that query and move on to the next block. Is there any way to accomplish that? Thanks & Regards Medhavi Mahansaria Tata Consultancy Services Limited Unit-VI, No.78, 79&

Re: [GENERAL] Reg: PL/pgSQL commit and rollback

Medhavi Mahansaria wrote: > I am writing a porting a procedure running in oracle to a PL/pgSQL function. > > I need to use commit and rollback in my function. > > I have read that usage of commit and rollback is not possible in PL/pgSQL, > however savepoints can be > used. > > even when i use s

Re: [GENERAL] Reg: PL/pgSQL commit and rollback

On 03/17/2015 06:50 AM, Medhavi Mahansaria wrote: Hi, I am writing a porting a procedure running in oracle to a PL/pgSQL function. I need to use commit and rollback in my function. I have read that usage of commit and rollback is not possible in PL/pgSQL, however savepoints can be used. even

[GENERAL] Reg: PL/pgSQL commit and rollback

Hi, I am writing a porting a procedure running in oracle to a PL/pgSQL function. I need to use commit and rollback in my function. I have read that usage of commit and rollback is not possible in PL/pgSQL, however savepoints can be used. even when i use savepoints and rollback to a savepoint

Re: [GENERAL] BDR with Postgres

On 17 March 2015 at 17:01, Deole, Pushkar (Pushkar) wrote: > Hi, > > > > I just tried a POC with PostgreSQL’s streaming replication for the use > case of my product. Since streaming replication is master-slave, when > failover occurs there are some configuration changes required to promote > sla

Re: [GENERAL] BDR with Postgres

On 17 March 2015 at 20:33, Deole, Pushkar (Pushkar) wrote: > The documentation says that all the existing nodes need to be restarted > while adding a new node since the existing nodes need to establish > connection to the new node. > It sounds like you're talking about BDR here. If so, that re

Re: [GENERAL] Slow query with join

>-Message d'origine- >De : Tom Lane [mailto:t...@sss.pgh.pa.us] >Envoyé : March-16-15 5:07 PM >À : Tomas Vondra >Cc : pgsql-general@postgresql.org; Marc Watson >Objet : Re: [GENERAL] Slow query with join > >Tomas Vondra writes: >> On 16.3.2015 19:50, Marc Watson wrote: >>> I hope someone

Re: [GENERAL] BDR with Postgres

The documentation says that all the existing nodes need to be restarted while adding a new node since the existing nodes need to establish connection to the new node. However, this doesn’t seem feasible for production deployments because existing nodes might be serving clients which would fail i

Re: [GENERAL] BDR with Postgres

Hi Pushkar, 1. How does the initial data sync of node3 with node2 takes place? 2. For node3 and replication data to/from node2, does node2 needs a restart or it is not required? Have you looked at? https://wiki.postgresql.org/wiki/BDR_Administration HTH Kind regards/met vriendelijk

Re: [GENERAL] Re: [pgadmin-support] Issue with a hanging apply process on the replica db after vacuum works on primary

05.03.2015 11:25, Jim Nasby пишет: On 2/27/15 5:11 AM, Sergey Shchukin wrote: show max_standby_streaming_delay; max_standby_streaming_delay - 30s We both need to be more clear about which server we're talking about (master or replica). What are max_standby_s

[GENERAL] BDR with Postgres

Hi, I just tried a POC with PostgreSQL's streaming replication for the use case of my product. Since streaming replication is master-slave, when failover occurs there are some configuration changes required to promote slave to master. Also, my requirement is to failback to old master again afte

Re: [GENERAL] [ADMIN] pgpool + BDR, is it possible?

Ok, I will try to explain my environment. I want to have a high availability environment in active/active mode. I´m building a cluster with 2 or more nodes, with Apache and JBOSS. Apache works as proxy and balancer and JBOSS serves my application. I have high availability in JBOSS thanks to