Re: [GENERAL] pg_xlog on a hot_stanby slave

On 18/06/2015 04:00, Sameer Kumar wrote: On Wed, 17 Jun 2015 15:24 Xavier 12 > wrote: On 17/06/2015 03:17, Sameer Kumar wrote: On Tue, 16 Jun 2015 16:55 Xavier 12 mailto:mania...@gmail.com>> wrote: Hi everyone, Questions abo

[GENERAL] Select query regarding info

HI Everyone, Below DB query is showing below error on postgresql9.3. SELECT '\'' || t2.name || '\'', '\'' || t1.phone_number || '\'', '\'' || t1.details || '\'', '\'' || t1.description || '\'', '\'' || (CASE WHEN t1.s_id IS NULL THEN 'N/A' ELSE t3.s_type END) || '\'', '\'' || t1.s_id || '\'' FRO

[GENERAL] My postgres is not logging anymore

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello. I have a postgres cluster made by two nodes with OS Ubuntu 14.04. About two weeks ago I did a minor update of postgres to the version 9.4.1-1.pgdg70+1. After I had to shutdown the machines and I could start them just last week. Since then postgr

Re: [GENERAL] My postgres is not logging anymore

On 18/06/2015 10:34, Maila Fatticcioni wrote: > Hello. > I have a postgres cluster made by two nodes with OS Ubuntu 14.04. > About two weeks ago I did a minor update of postgres to the version > 9.4.1-1.pgdg70+1. After I had to shutdown the machines and I could > start them just last week. > Since

Re: [GENERAL] serialization failure why?

Kevin, assuming you will have some time to confirm that it has been fixed in some version some time next week, I’ve compiled the test steps in http://pastebin.com/4Uqc2kPv Thanks once again > On 17/06/2015, at 14:40, Filipe Pina wrote: > > Hi Kevin, > > I have

Re: [GENERAL] Select query regarding info

On Thu, 18 Jun 2015 07:29:37 + "Yogesh. Sharma" wrote: > HI Everyone, > > Below DB query is showing below error on postgresql9.3. > SELECT '\'' || t2.name || '\'', '\'' || t1.phone_number || '\'', '\'' || > t1.details || '\'', '\'' || t1.description || '\'', '\'' || (CASE WHEN > t1.s_id IS

Re: [GENERAL] serialization failure why?

On 17 June 2015 at 13:52, Kevin Grittner wrote: > Filipe Pina wrote: > > > if drop the foreign key constraint on stuff_ext table there are > > no failures at all… > > It is my recollection that we were excluding the queries used to > enforce referential integrity constraints from the conflict >

Re: [GENERAL] Select query regarding info

> Could you please provide below information. > > How to change standard_conforming_strings value of postgresql.conf? I would not change that option. You should rather stick to standard conforming strings and fix your query. That can be done through a simple (and automated) search & replace.

Re: [GENERAL] My postgres is not logging anymore

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/18/2015 11:48 AM, Raymond O'Donnell wrote: > On 18/06/2015 10:34, Maila Fatticcioni wrote: >> Hello. I have a postgres cluster made by two nodes with OS Ubuntu >> 14.04. About two weeks ago I did a minor update of postgres to >> the version 9.4.1

[GENERAL] PL/pgSQL: How to return two columns and multiple rows

Hello, I supose this is simple, but I did not find a solution in the documentation. I would like to be able to do something like this: select myfunc('foo','bar'); or select myfunc(foo, bar) from foobartable; or even select myfunc(foo, bar), 'baz' as baz from foobartable; Which should return som

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thursday, June 18, 2015, Sven Geggus wrote: > Hello, > > I supose this is simple, but I did not find a solution in the > documentation. > > I would like to be able to do something like this: > > select myfunc('foo','bar'); > or > select myfunc(foo, bar) from foobartable; > or even > select myf

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

Hi CREATE OR REPLACE FUNCTION public.fx(i integer, OUT a text, OUT b text) RETURNS SETOF record LANGUAGE plpgsql AS $function$ BEGIN RETURN QUERY SELECT 'foo'||id, 'bar'||id FROM generate_series(1,i) g(id); RETURN; END; $function$ postgres=# SELECT a,b FROM fx(4); ┌──┬──┐ │ a │

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thu, Jun 18, 2015, 14:38 Sven Geggus wrote: Hello, I supose this is simple, but I did not find a solution in the documentation. Because you already are returning 2 columns. I would like to be able to do something like this: select myfunc('foo','bar'); or select myfunc(foo, bar) from foob

Re: [GENERAL] My postgres is not logging anymore

On 18/06/2015 13:13, Maila Fatticcioni wrote: > On 06/18/2015 11:48 AM, Raymond O'Donnell wrote: >> On 18/06/2015 10:34, Maila Fatticcioni wrote: >>> Hello. I have a postgres cluster made by two nodes with OS Ubuntu >>> 14.04. About two weeks ago I did a minor update of postgres to >>> the version

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On 18/06/2015 13:36, Sven Geggus wrote: > Hello, > > I supose this is simple, but I did not find a solution in the documentation. > > I would like to be able to do something like this: > > select myfunc('foo','bar'); > or > select myfunc(foo, bar) from foobartable; > or even > select myfunc(foo,

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thu, Jun 18, 2015 at 7:50 AM, Chris Travers wrote: > > On Thu, Jun 18, 2015, 14:38 Sven Geggus wrote: > > Hello, > > I supose this is simple, but I did not find a solution in the documentation. > > Because you already are returning 2 columns. > > I would like to be able to do something like th

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

"Raymond O'Donnell" writes: > On 18/06/2015 13:36, Sven Geggus wrote: >> I would like to be able to do something like this: >> >> select myfunc('foo','bar'); >> or >> select myfunc(foo, bar) from foobartable; >> or even >> select myfunc(foo, bar), 'baz' as baz from foobartable; > You need to do:

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thursday, June 18, 2015, Chris Travers wrote: > > > Select (myfunc('foo','bar')).*; > > > This should be avoided. Use lateral instead,or a cte a/o offset 0. My_func is evaluated twice (once per column) if called this way > > Or > Select * from myfunc('foo','bar'); > This is ok David J.

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

David G. Johnston wrote: > Look at the "returns table (col1 type, col2 type)" form. If I got this right "returns table" is not what I want as I need to select from my function as a virtual table in this case. Regards Sven -- "Thinking of using NT for your critical apps?

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

Pavel Stehule wrote: > CREATE OR REPLACE FUNCTION public.fx(i integer, OUT a text, OUT b text) > RETURNS SETOF record > LANGUAGE plpgsql > AS $function$ > BEGIN > RETURN QUERY SELECT 'foo'||id, 'bar'||id FROM generate_series(1,i) g(id); > RETURN; > END; > $function$ I'm afraid I will almos

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thu, Jun 18, 2015 at 9:32 AM, Sven Geggus wrote: > David G. Johnston wrote: > > > Look at the "returns table (col1 type, col2 type)" form. > > If I got this right "returns table" is not what I want as I need to select > from my function as a virtual table in this case. > ​Yes, I mis-read you

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

Raymond O'Donnell wrote: >> mydb=> select myfunc('foo','bar'); > > You need to do: > >select * from myfunc('foo','bar'); This has been a misguided example. Reality should more likely look like this: select myfunc(col1,col2) from mytable; And it would of course be undesired if myfunc woul

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thu, Jun 18, 2015 at 9:52 AM, Sven Geggus wrote: > Raymond O'Donnell wrote: > > >> mydb=> select myfunc('foo','bar'); > > > > You need to do: > > > >select * from myfunc('foo','bar'); > > This has been a misguided example. Reality should more likely look like > this: > > select myfunc(col

Re: [GENERAL] writable cte triggers reverse order

=?UTF-8?B?0JzQuNGI0LAg0KLRjtGA0LjQvQ==?= writes: > for me case listed below looks like something goes wrong (at least very > strange) > ... > see on order of events -- INSERTs come first before DELETEs! The reason for this is that the AFTER INSERT trigger events are queued within the INSERT stat

[GENERAL] valgrind

Hi list, I've Installed postgresql-9.4 using apt-get as instructed here: http://www.postgresql.org/download/linux/debian/ Also installed libpq-dev with the suggested dependencies using apt-get. And created a small program in c. You can find it here: http://pastebin.com/bRHw3Wud When I run the prog

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

David G. Johnston wrote: > WITH exec_func AS ( SELECT myfunc(col1,col2) FROM mytable ) > SELECT (exec_func.myfunc).* FROM exec_func; > > This relies on the fact that currently a CTE introduces an optimization > barrier. Hm, let me summarize. My function seems to work as expected and is only call

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thu, Jun 18, 2015 at 10:31 AM, Sven Geggus wrote: > David G. Johnston wrote: > > WITH exec_func AS ( SELECT myfunc(col1,col2) FROM mytable ) > > SELECT (exec_func.myfunc).* FROM exec_func; > > > > This relies on the fact that currently a CTE introduces an optimization > > barrier. > > Hm, let

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

Sven Geggus wrote: > Using your suggestion the desired two columns are generated, but I consider > this a little bit ugly: > > mydb=> WITH exec_func AS ( select myfunc(col1,col2) from mytable ) > SELECT (exec_func.myfunc).* FROM exec_func; > HINWEIS: called with parms foo,bar: text1 value1 > HIN

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

David G. Johnston wrote: > Assuming you are on 9.3+ what you want to use is LATERAL OK, how is such a query supposed to look like? assuming "select myfunc(col1,col2) from mytable" works as the inner select? Sven -- Software patents are the software project equivalent of land mines: Each desi

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thu, Jun 18, 2015 at 12:00 PM, Sven Geggus wrote: > David G. Johnston wrote: > > > Assuming you are on 9.3+ what you want to use is LATERAL > > OK, how is such a query supposed to look like? > > assuming "select myfunc(col1,col2) from mytable" works as the inner select? > ​Syntax, descriptio

[GENERAL] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)

Guys I have an issue going on with PGBOUNCER which is slowing down the site PGBOUNCER VERSION: pgbouncer-1.5.4-2.el6 (Hosted on separate machine) (16 cpu) 98GB RAM DATABASE VERION: postgresql 9.3 When the total client connections to pgbouncer are close to 1000, site applicatio

Re: [GENERAL] [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)

On Thu, Jun 18, 2015 at 05:09:10PM +, Sheena, Prabhjot wrote: > Guys > I have an issue going on with PGBOUNCER which is slowing down the > site > > PGBOUNCER VERSION: pgbouncer-1.5.4-2.el6 (Hosted on separate machine) (16 > cpu) 98GB RAM > DATABASE VERION: postgresql 9.3 >

Re: [GENERAL] [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)

Here is the output of OS limits postgres@symds-pg:~ $ ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 790527 max locked memory

Re: [GENERAL] [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)

On Thu, Jun 18, 2015 at 05:41:01PM +, Sheena, Prabhjot wrote: > Here is the output of OS limits > > postgres@symds-pg:~ $ ulimit -a > > core file size (blocks, -c) 0 > data seg size (kbytes, -d) unlimited > scheduling priority (-e) 0 > file size (b

Re: [GENERAL] [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)

Hi Ken/ Will I have checked the ulimit value and we are nowhere hitting the max 4096 that we have currently set. Is there any other explanation why we should be thinking of bumping it to like ulimit -n 5 ( Add ulimit -n 5 to the start of whatever you use to start pgbouncer

Re: [GENERAL] [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)

"Sheena, Prabhjot" writes: > Hi Ken/ Will > > I have checked the ulimit value and we are nowhere hitting the max > 4096 that we have currently set. Is there any other explanation why > we should be thinking of bumping it to like ulimit -n 5 ( Add > ulimit -n 5 to the

Re: [GENERAL] [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Sheena, Prabhjot Sent: Thursday, June 18, 2015 3:19 PM To: k...@rice.edu; Will Platnick Cc: pgsql-performa...@postgresql.org; pgsql-general@postgresql.org Subject:

Re: [GENERAL] [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)

On Thu, Jun 18, 2015 at 07:19:13PM +, Sheena, Prabhjot wrote: > Hi Ken/ Will > > I have checked the ulimit value and we are nowhere hitting the max 4096 > that we have currently set. Is there any other explanation why we should be > thinking of bumping it to like ulimit -n 5000

Re: [GENERAL] [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)

Here is the output of pid postgres@symds-pg:~ $ cat /proc/15610/limits Limit Soft Limit Hard Limit Units Max cpu time unlimitedunlimitedseconds Max file size unlimitedunlimitedbytes Max

[GENERAL] Is there any way to measure disk activity for each query?

Hello! I'm wondering, if there any way to measure how much disk-io were generated by a query? -- Best Regards, Oleg

Re: [GENERAL] Is there any way to measure disk activity for each query?

On Thu, Jun 18, 2015 at 3:05 PM, Oleg Serov wrote: > Hello! > > I'm wondering, if there any way to measure how much disk-io were generated > by a query? > For an individual query execution, you can explain it with explain (analyze, buffers) select . It will report on the pages hit in the b

Re: [GENERAL] Test for char errors

On Wed, Jun 17, 2015 at 5:15 PM, Gao wrote: >> I don't know why the files are not the same but tests all passed. Helps are >> appreciated, thanks! >Some tests have multiple expected outputs. In the case of char, there >is not only char.out, but as well char_1.out and char_2.out. In your >case cha

[GENERAL] Issues setting up BDR with multiple databases

On NODE 1 create database christian; create user christian with password 'christian'; alter database christian owner to christian; create database lizzie; create user lizzie with password 'lizzie'; alter database lizzie owner to lizzie; create database jorge; create user jorge with passwo

Re: [GENERAL] Test for char errors

On Fri, Jun 19, 2015 at 8:29 AM, 夏高 wrote: > Thanks Michael! Could you tell me which option determines what expected > output is used? Have a look at results_differ() in pg_regress.c ;) The file selected as expected output is the one with less lines of diffs. -- Michael -- Sent via pgsql-gene

Re: [GENERAL] pg_dump 8.4.9 failing after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux

> > On 18 Jun 2015, at 02:06 , Tom Lane wrote: > > Douglas Stetner writes: >> Looking for confirmation there is an issue with pg_dump failing after >> upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux. > > Quick thought --- did you restart the Postgres service after upgrading > ope

Re: [GENERAL] pg_xlog on a hot_stanby slave

On Thu, 18 Jun 2015 15:17 Xavier 12 wrote: On 18/06/2015 04:00, Sameer Kumar wrote: On Wed, 17 Jun 2015 15:24 Xavier 12 wrote: On 17/06/2015 03:17, Sameer Kumar wrote: On Tue, 16 Jun 2015 16:55 Xavier 12 wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1

[GENERAL] Implementing PostgreSQL in High Availability

Hello everyone, I am currently facing the challenge of giving a High Availability solution for a new system we want to work with. I need PostgreSQL to meet the following requirements: 1. Auto failover in case of node is going down or completely destroyed. 2. Scale-Out architecture because the a

Re: [GENERAL] Issues setting up BDR with multiple databases

On 19 June 2015 at 08:09, Jorge Torralba wrote: > Could not add local node to cluster, status PGRES_FATAL_ERROR: ERROR: node > identity for local dsn does not match current node > > DETAIL: The dsn 'dbname=jorge host=10.5.101.179' connects to a node with > identity (6161869759719318325,2,16389)

Re: [GENERAL] Issues trying to run bdr_init_copy with new setup

On 17 June 2015 at 07:49, Jorge Torralba wrote: > Just started playing with BDR. Originally setup the environment on two > separate servers as per the quick start guid and used the sql commands to > add nodes. Moving on to command line, I am running into some issues. This has also been opened by

[GENERAL] WAL log archival on standby

Hey All, Is it possible to create WAL archive files on standby servers when using streaming replication? I know I can use archive_command or pg_receivexlog - but these will both result in me sending information to the standby servers which has already been sent via standard streaming replication.

Re: [GENERAL] WAL log archival on standby

On Fri, Jun 19, 2015 at 2:38 PM, James Sewell wrote: > Hey All, > > Is it possible to create WAL archive files on standby servers when using > streaming replication? > Yes and no, standbys do not archive WAL in ~9.4, but you could use archive_mode = 'always' with the upcoming 9.5. I know I can