Re: [GENERAL] Logical Decoding Failover

Venkata, No problem - thanks for replying. I'll try to clarify. Say I have databases A and B, where A is primary and B is a replica. I have an application that executes 3 transactions against A, resulting in WAL log sequence numbers 1, 2, and 3. The WAL is successfully replicated to B. I have a

Re: [GENERAL] Logical Decoding Failover

On Sun, Aug 7, 2016 at 9:29 PM, Colin Morelli wrote: > Venkata, > > Thanks for the reply. Unfortunately something like PgPool still won't > create the replication slots on all hosts, and record the LSN in a way that > is reusable on the secondary. > Yes, thats correct, pgPool does not have anyth

Re: [GENERAL] select array_remove(ARRAY[NULL,NULL,NULL],NULL); returns {} instead of {NULL,NULL,NULL}

"David G. Johnston" writes: > On Mon, Aug 8, 2016 at 5:51 PM, Alban Hertroys wrote: >> How does it now which unknown value to remove from that array of unknown >> values? Shouldn't the result be: >> {NULL,NULL,NULL}? > ​Is this a philosophical or technical question? > For the former I don't s

Re: [GENERAL] Column order in multi column primary key

Thanks Kevin for your response. I've Googled and debated natural vs surrogate keys and I just find surrogate keys easier to work with (maybe I'm just being lazy). It just seems that a description or name is most often the natural key. I just can't see, In my case, using a department descripti

Re: [GENERAL] select array_remove(ARRAY[NULL,NULL,NULL],NULL); returns {} instead of {NULL,NULL,NULL}

On Mon, Aug 8, 2016 at 5:51 PM, Alban Hertroys wrote: > > > On 08 Aug 2016, at 20:19, Tom Lane wrote: > > > > Alexander Farber writes: > >> I wonder, why the following returns NULL and not 0 in 9.5.3? > > > >> # select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); > > > > Because t

Re: [GENERAL] select array_remove(ARRAY[NULL,NULL,NULL],NULL); returns {} instead of {NULL,NULL,NULL}

> On 08 Aug 2016, at 20:19, Tom Lane wrote: > > Alexander Farber writes: >> I wonder, why the following returns NULL and not 0 in 9.5.3? > >> # select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); > > Because the result of the array_remove is an empty array, which is > defined to

Re: [GENERAL] Column order in multi column primary key

On Mon, Aug 8, 2016 at 4:01 PM, Craig Boucher wrote: > From: Tom Lane [mailto:t...@sss.pgh.pa.us] >> I'm pretty skeptical of the notion of redefining what your PK >> is on performance grounds. With this definition, you'd allow >> two entries with the same work_session_id, if they chanced to >> h

Re: [GENERAL] Column order in multi column primary key

Thanks Tom for the link. It could actually be beneficial if we need to migrate a customer from one database to another because wouldn't have to worry about pk constraint violations. Craig -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Monday, August 8, 2016 1:47 PM

Re: [GENERAL] Column order in multi column primary key

"Craig Boucher" writes: > I should have pointed out in my last response that I was wondering if the > performance of the pk index on work_session would be better if my primary key > was (customer_id, work_session_id) or if (work_session_id, customer_id) will > be fine. Customer_id will be rep

Re: [GENERAL] Column order in multi column primary key

On Mon, Aug 8, 2016 at 4:35 PM, Craig Boucher wrote: > Thanks David. I’ve thought about the hierarchy you mentioned but the > hierarchy can change and I need to capture it as it was when the data was > generated. > > > > I should have pointed out in my last response that I was wondering if the >

Re: [GENERAL] Column order in multi column primary key

Thanks David. I’ve thought about the hierarchy you mentioned but the hierarchy can change and I need to capture it as it was when the data was generated. I should have pointed out in my last response that I was wondering if the performance of the pk index on work_session would be better if m

Re: [GENERAL] Column order in multi column primary key

On Mon, Aug 8, 2016 at 3:06 PM, Craig Boucher wrote: > Here is one of the tables that can have millions of rows and foreign key > constraints to 5 other tables. > > > > CREATE TABLE public.work_session > > ( > > work_session_id integer NOT NULL DEFAULT nextval('worksession_ > worksessionid_seq'

Re: [GENERAL] Column order in multi column primary key

Here is one of the tables that can have millions of rows and foreign key constraints to 5 other tables. CREATE TABLE public.work_session ( work_session_id integer NOT NULL DEFAULT nextval('worksession_worksessionid_seq'::regclass), customer_id integer NOT NULL, store_id integer NOT

Re: [GENERAL] Column order in multi column primary key

On Mon, Aug 8, 2016 at 1:47 PM, Craig Boucher wrote: > PG 9.5 > > > > I’m in the process of converting our application from Sql Server to > Postgresql. I’m taking advantage of this process to make some database > design changes. > > > > Our database contains data for many customers and I have a

Re: [GENERAL] select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0

Alexander Farber writes: > Thank you, so should I maybe switch to cardinality then? Yeah, that should work. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/

Re: [GENERAL] select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0

Thank you, so should I maybe switch to cardinality then?

Re: [GENERAL] select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0

Alexander Farber writes: > I wonder, why the following returns NULL and not 0 in 9.5.3? > # select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); Because the result of the array_remove is an empty array, which is defined to be zero-dimensional in PG. regards,

[GENERAL] select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0

Good evening, I wonder, why the following returns NULL and not 0 in 9.5.3? # select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); array_length -- (1 row) # select array_length(array_remove(ARRAY[3,3,3],3), 1); array_length -- (1 row) In a code for a wor

[GENERAL] Column order in multi column primary key

PG 9.5 I'm in the process of converting our application from Sql Server to Postgresql. I'm taking advantage of this process to make some database design changes. Our database contains data for many customers and I have a customer_id column in every customer related table. One of the cha

Re: [GENERAL] Corrupted Data ?

On 08/08/2016 10:06 AM, Ioana Danes wrote: On Mon, Aug 8, 2016 at 12:55 PM, Adrian Klaver> 75315811??? Corrupted index on db3? yes Might want to look in the db3 logs to see if anything pops out. I checked the logs, no traces of errors or corruption. I just do not kno

Re: [GENERAL] Corrupted Data ?

On Mon, Aug 8, 2016 at 12:55 PM, Adrian Klaver wrote: > On 08/08/2016 09:47 AM, Ioana Danes wrote: > >> >> >> On Mon, Aug 8, 2016 at 12:37 PM, Adrian Klaver >> mailto:adrian.kla...@aklaver.com>> wrote: >> >> On 08/08/2016 09:28 AM, Ioana Danes wrote: >> >> >> >> On Mon, Aug 8, 2016 at

Re: [GENERAL] Corrupted Data ?

On 08/08/2016 09:47 AM, Ioana Danes wrote: On Mon, Aug 8, 2016 at 12:37 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 08/08/2016 09:28 AM, Ioana Danes wrote: On Mon, Aug 8, 2016 at 12:19 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>

Re: [GENERAL] Corrupted Data ?

On Mon, Aug 8, 2016 at 12:47 PM, Ioana Danes wrote: > > > On Mon, Aug 8, 2016 at 12:37 PM, Adrian Klaver > wrote: > >> On 08/08/2016 09:28 AM, Ioana Danes wrote: >> >>> >>> >>> On Mon, Aug 8, 2016 at 12:19 PM, Adrian Klaver >>> mailto:adrian.kla...@aklaver.com>> wrote: >>> >>> On 08/08/2016

Re: [GENERAL] Corrupted Data ?

On Mon, Aug 8, 2016 at 12:37 PM, Adrian Klaver wrote: > On 08/08/2016 09:28 AM, Ioana Danes wrote: > >> >> >> On Mon, Aug 8, 2016 at 12:19 PM, Adrian Klaver >> mailto:adrian.kla...@aklaver.com>> wrote: >> >> On 08/08/2016 09:11 AM, Ioana Danes wrote: >> >> Hi, >> >> I suspect

Re: [GENERAL] Corrupted Data ?

On 08/08/2016 09:28 AM, Ioana Danes wrote: On Mon, Aug 8, 2016 at 12:19 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 08/08/2016 09:11 AM, Ioana Danes wrote: Hi, I suspect I am having a case of data corruption. Here are the details: I am

Re: [GENERAL] Corrupted Data ?

On Mon, Aug 8, 2016 at 12:19 PM, Adrian Klaver wrote: > On 08/08/2016 09:11 AM, Ioana Danes wrote: > >> Hi, >> >> I suspect I am having a case of data corruption. Here are the details: >> >> I am running postgres 9.4.8: >> >> postgresql94-9.4.8-1PGDG.rhel7.x86_64 >> postgresql94-contrib-9.4.8-1PG

Re: [GENERAL] Corrupted Data ?

On 08/08/2016 09:11 AM, Ioana Danes wrote: Hi, I suspect I am having a case of data corruption. Here are the details: I am running postgres 9.4.8: postgresql94-9.4.8-1PGDG.rhel7.x86_64 postgresql94-contrib-9.4.8-1PGDG.rhel7.x86_64 postgresql94-libs-9.4.8-1PGDG.rhel7.x86_64 postgresql94-server-

[GENERAL] Corrupted Data ?

Hi, I suspect I am having a case of data corruption. Here are the details: I am running postgres 9.4.8: postgresql94-9.4.8-1PGDG.rhel7.x86_64 postgresql94-contrib-9.4.8-1PGDG.rhel7.x86_64 postgresql94-libs-9.4.8-1PGDG.rhel7.x86_64 postgresql94-server-9.4.8-1PGDG.rhel7.x86_64 on CentOS Linux rel

Re: [GENERAL] Detecting if current transaction is modifying the database

On Fri, Aug 5, 2016 at 4:24 PM, Christian Ohler wrote: Your check for a exclusive self-lock on transactionid should work. It may be possible to find a way to do it that is less expensive, so I would definitely encapsulate that in a function; but off-hand I'm not thinking of a better way. You mig

Re: [GENERAL] Should a DB vacuum use up a lot of space ?

On 08/08/2016 12:08 AM, Philippe Girolami wrote: So you are VACUUMing the lesser 'younger' tables? I VACUUM those with the highest age : SELECT age,array_agg(table_name) FROM (SELECT c.oid::regclass as table_name, greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age FROM pg_class c L

Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works on Mac, Linux

Alexander Farber writes: > More info: > # \l+ >Name| Owner | Encoding | Collate | Ctype | > ---+-+--+-+---+ > postgres | user1 | UTF8 | C | C | > template0 | user1 | UTF8 | C | C | >| | |

Re: [GENERAL] upgrade to repmgr3

hello Thanks for your comments. They are very helpful. If you have any draft level documentation available of the upgrade procedure I would be very interested in seeing it and maybe trying it out as well. Meanwhile I did some more testing with my environment using repmgr3 and noticed an issue wit

Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works on Mac, Linux

Hello Alexander > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber > Sent: Montag, 8. August 2016 10:21 > Cc: pgsql-general > Subject: Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but w

Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works on Mac, Linux

Hello Charles, unfortunately on Windows 7 this fails: psql (9.5.3) Type "help" for help. # select lower(('И'::text collate "en_US")) ; ERROR: collation "en_US" for encoding "UTF8" does not exist LINE 1: select lower(('?'::text collate "en_US")) ; ^ By the way I th

Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works on Mac, Linux

Hello > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber > Sent: Montag, 8. August 2016 10:12 > Cc: pgsql-general > Subject: Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works > on Mac, Linux > > Thank you for

Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works on Mac, Linux

Thank you for the replies. On CentOS 7 Linux with pgdg 9.5.3 the lower() method works and I have: # \l List of databases Name| Owner | Encoding | Collate |Ctype| ---+--+--+-+-+ postgres | po

Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works on Mac, Linux

Hello > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Charles > Clavadetscher > Sent: Montag, 8. August 2016 09:30 > To: 'Alexander Farber' ; 'pgsql-general' > > Subject: Re: [GENERAL] lower() silently fails for

Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works on Mac, Linux

Hello > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber > Sent: Montag, 8. August 2016 09:10 > To: pgsql-general > Subject: Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works > on Mac, Linux > > More info: >

Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works on Mac, Linux

More info: # \l+ Name| Owner | Encoding | Collate | Ctype | ---+-+--+-+---+ postgres | user1 | UTF8 | C | C | template0 | user1 | UTF8 | C | C | | | | | | template1 | user1

Re: [GENERAL] Should a DB vacuum use up a lot of space ?

>So you are VACUUMing the lesser 'younger' tables? I VACUUM those with the highest age : SELECT age,array_agg(table_name) FROM (SELECT c.oid::regclass as table_name, greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHE

Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works on Mac, Linux

Here the Windows-log excerpt (the 5 cyrillic letters stay uppercased): LOG: database system is ready to accept connections LOG: autovacuum launcher started LOG: statement: SET client_encoding = 'UTF8'; LOG: execute : SELECT out_gid AS gid FROM words_play_game($1, $2, $3::jsonb) DETAIL: param