Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

I presume you point at me. Keep the record straight. I got mad not for the help but for the high horse attitude. We all have good and bad. No one is perfect and no one deserves this crap Sent from my iPhone > On Dec 14, 2016, at 7:19 PM, Patrick B wrote: > > > >

[GENERAL] unexpected pageaddr

Hi Setting up the hot_standby the only way I could it get the base remote cluster running was including pg_xlog Now using rsync as opposed to pg_basebackup seems ok aka psql postgres -c "select pg_start_backup('backup')" rsync -a /var/lib/pgsql/9.5/data

[GENERAL] hot standby questions

Hi Just trying to put together the hot_standby setup All docs I read are pointing to use as prefered method to use pg_basebackup to set the base So far so good But psql postgres -c "select pg_start_backup('backup')" pg_basebackup -D /var/lib/pgsql/sample -Ft -z -P psql postgres -c "select

Re: [GENERAL] unexpected pageaddr

Well this reproduces with pg_basebackup Disturbing. I wonder what else if lurking around > On Aug 2, 2017, at 10:34 PM, armand pirvu <armand.pi...@gmail.com> wrote: > > Hi > > Setting up the hot_standby the only way I could it get the base remote > cluster runni

Re: [GENERAL] Where is pg_hba.conf

Normally should reside in the data dir. My case below armandps-MacBook-Air:~ armandp$ ps -fu postgres |grep data 502 29591 1 0 Thu09PM ?? 0:01.63 /Library/PostgreSQL/9.6/bin/postgres -D /Library/PostgreSQL/9.6/data armandps-MacBook-Air:~ armandp$ ls -l

[GENERAL] pglogical repo

Hi folks Looking at the installatoion steps and the yum repositories sudo yum install http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-2.noarch.rpm [ ] pglogical-rhel-1.0-1.noarch.rpm [ ] pglogical-rhel-1.0-2.noarch.rpm [ ] pglogical-rhel-1.0-3.noarch.rpm

[GENERAL] pglogical sync

Hi folks A question about pglogical sync I have a setup with a schema named myschema. All works well but looking at sync pglogical.alter_subscription_resynchronize_table(subscription_name name, relation regclass) Resynchronize one existing table. WARNING: This function will truncate the

[GENERAL] Re: could not find function "pglogical_table_data_filtered" in file "/usr/pgsql-9.5/lib/pglogical.so"

Looks like upgrading from 9.5.2 to 9.5.8 did it I had another box on 9.5.5 and it was broken there too > On Aug 10, 2017, at 2:13 PM, armand pirvu <armand.pi...@gmail.com> wrote: > > So I ventured in uninstalling the > https://www.2ndquadrant.com/en/resources/pglogical/pglo

Re: [GENERAL] pglogical sync

I see says the blind man Looked in the code and correct the syntax and ran on subscriber all good > On Aug 10, 2017, at 12:29 PM, armand pirvu <armand.pi...@gmail.com> wrote: > > Hi folks > > A question about pglogical sync > > I have a setup with a schema named

Re: [GENERAL] pglogical repo

Updated since last time I installed it Okay Will go move from 1.0.2 to 1.0.3 to stay up to date Thks > On Aug 10, 2017, at 1:21 PM, John R Pierce <pie...@hogranch.com> wrote: > > On 8/10/2017 10:30 AM, armand pirvu wrote: >> Looking at the installatoion steps and

Re: [GENERAL] no pg_hba.conf entry for replication connection

Yep relaoded But darn typo Finger going too fast I guess Thanks bunch AP > On Aug 16, 2017, at 8:03 PM, Ian Barwick <ian.barw...@2ndquadrant.com> wrote: > > On 08/17/2017 05:26 AM, armand pirvu wrote: >> Hi >> master (172.16.26.7) and slave (172.16.26.4) >>

[GENERAL] could not find function "pglogical_table_data_filtered" in file "/usr/pgsql-9.5/lib/pglogical.so"

So I ventured in uninstalling the https://www.2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/ yum install

[GENERAL] hash join performance question

Hi testdb3=# \d csischema.dim_company; Table "csischema.dim_company" Column |Type | Modifiers -+-+--- company_id | integer | not null company_name| character

Re: [GENERAL] hash join performance question

> On Jul 18, 2017, at 10:30 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > armand pirvu <armand.pi...@gmail.com> writes: >> testdb3=# explain analyze SELECT a.company_id FROM csischema.dim_company a, >> woc.dim_company b >> test

[GENERAL] insert on conflict

Hi Got question birstdb=# \d csischema.dim_company Table "csischema.dim_company" Column |Type | Modifiers -+-+--- company_id | integer | not null company_name|

Re: [GENERAL] insert on conflict

> On Jun 27, 2017, at 3:30 PM, Peter Geoghegan <p...@bowt.ie> wrote: > > On Tue, Jun 27, 2017 at 12:40 PM, armand pirvu <armand.pi...@gmail.com> wrote: >> so how is it working in fact ? Isn't it working like looping in the >> IVEE.dim_company and for each c

[GENERAL] upsert and update filtering

Hi create table dimc1 ( col1 integer not null, col2 char(10), primary key (col1) ); create table dimc2 ( col1 integer not null, col2 char(10), primary key (col1) ); testdb=# select * from dimc1 order by 1; col1 |col2 --+ 111 | foo111 112 | foo112 (2

Re: [GENERAL] upsert and update filtering

That's because the access on this case is done to the existing row using the table's name / alias , and to the rows that we attempt to insert using the excluded Thank you Peter Armand > On Jul 31, 2017, at 4:31 PM, Peter Geoghegan <p...@bowt.ie> wrote: > > armand p

Re: [GENERAL] hot standby questions

l.com > <mailto:jeff.ja...@gmail.com>>: > On Wed, Aug 2, 2017 at 8:19 PM, armand pirvu <armand.pi...@gmail.com > <mailto:armand.pi...@gmail.com>> wrote: > > Hi > > Just trying to put together the hot_standby setup > All docs I read are pointing to use a

[GENERAL] no pg_hba.conf entry for replication connection

Hi master (172.16.26.7) and slave (172.16.26.4) master runs on port 5433 though SELECT pglogical.create_subscription( subscription_name := 'shw_sub', replication_sets := '{shw_set}', provider_dsn := 'host=172.16.26.7 port=5433 dbname=levregdb user=repuser'); ERROR: could not connect to the

Re: [GENERAL] views and fdw usage and performance

or users doing something in each schema. So my questions still remain Sent from my iPhone > On May 9, 2017, at 6:52 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > >> On 05/09/2017 02:36 PM, Armand Pirvu (home) wrote: >> Hi >> I have two schemas jt1, and jt2 in

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 4:20 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 06/09/2017 02:01 PM, armand pirvu wrote: >>> On Jun 9, 2017, at 3:52 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: >>> >>> On 06/09/2017 01:31 PM, arman

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 5:42 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 06/09/2017 02:26 PM, armand pirvu wrote: >>> On Jun 9, 2017, at 4:20 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: >>> >>> On 06/09/2017 02:01 PM, arma

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 11:23 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 06/09/2017 09:13 AM, armand pirvu wrote: >>> On Jun 9, 2017, at 11:01 AM, Adrian Klaver <adrian.kla...@aklaver.com >>> <mailto:adrian.kla...@aklaver.com>> wrote:

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 3:52 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 06/09/2017 01:31 PM, armand pirvu wrote: > >>> >>> Are these large tables? > > >> I would say yes >> select count(*) from csischema.t

Re: [GENERAL] Strange port in pg_upgrade output

Got it Thank you > On Jun 12, 2017, at 4:16 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 06/12/2017 02:07 PM, armand pirvu wrote: >> Hi >> I was doing a test upgrade from 9.5 to 9.6 and the following lines caught my >> eye >> postgres

[GENERAL] Strange port in pg_upgrade output

Hi I was doing a test upgrade from 9.5 to 9.6 and the following lines caught my eye postgres 10967 10911 0 15:59 pts/000:00:00 /usr/pgsql-9.6/bin/pg_upgrade -d /var/lib/pgsql/9.5/data -D /var/lib/pgsql/9.6/data -b /usr/pgsql-9.5/bin -B /usr/pgsql-9.6/bin -k -v postgres 11141 1 0

[GENERAL] jsonb case insensitive search

Hi The goal would be to be able to search case insensitive by any key:value combined with some other columns like outlined below, but initially would be Company:CompuTestSystems A sample would be SELECT * FROM cfg_files_data WHERE cfg_files_data.show_id = 32 AND cfg_files_data.file_id =

Re: [GENERAL] jsonb case insensitive search

> On May 31, 2017, at 2:32 PM, David G. Johnston <david.g.johns...@gmail.com> > wrote: > > On Wed, May 31, 2017 at 12:18 PM, armand pirvu <armand.pi...@gmail.com > <mailto:armand.pi...@gmail.com>> wrote: > > For the example mentioned >

Re: [GENERAL] jsonb case insensitive search

Thank you Karl and David Ideally as far as I can tell the index would need to be show_id, file_id, lower(…) The question is if this is possible ? Thanks Armand > On Jun 1, 2017, at 12:24 PM, Karl Czajkowski <kar...@isi.edu> wrote: > > On May 31, armand pirvu modulated:

Re: [GENERAL] jsonb case insensitive search

I apologize before hand replying again on my own reply . I know it is frowned upon . My inline comments. > On Jun 1, 2017, at 2:05 PM, armand pirvu <armand.pi...@gmail.com> wrote: > > Thank you Karl and David > > Ideally as far as I can tell the index would need t

[GENERAL] Vacuum and state_change

Hi Had a couple of processes blocking the vacuum so I terminated them using select pg_terminate_backend(pid); Running the following select distinct pid, backend_start, query_start, state_change, state, query from pg_catalog.pg_stat_activity order by 1; pid | backend_start |

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 11:01 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 06/09/2017 08:45 AM, armand pirvu wrote: >> Hi >> Had a couple of processes blocking the vacuum so I terminated them using >> select pg_terminate_backend(pid); >> Runni

[GENERAL] jsonb search

Hi In my quest of JSONB querying and searching without having to actually cast into a text, I found JSQuery I do admit my JSONB knowledge shortcoming and I am not a developer but a DBA. As such some examples would be greatly appreciated since I tend to understand better I compiled and

[GENERAL] executing os commands from a function

All I know this may sound like heresy since it involves executing an OS command from a function , but here goes After an insert in a table, I want to touch a file I.e After insert into table test values (100) I want in a dir to have file 100 I used plsh extension but I had to use two

Re: [GENERAL] executing os commands from a function

, David G. Johnston <david.g.johns...@gmail.com> wrote: > On Thu, Sep 29, 2016 at 2:41 PM, Armand Pirvu (home) <armand.pi...@gmail.com> > wrote: > I used plsh extension but I had to use two functions and a trigger, see code > below > > ​I don't see any way t

Re: [GENERAL] variable value in array_to_string

for asking help. Will try not to do it again Thanks and laters On Nov 21, 2016, at 7:12 PM, David G. Johnston <david.g.johns...@gmail.com> wrote: > Please don't top-post - it makes following the thread a lot harder. > > On Mon, Nov 21, 2016 at 4:15 PM, Armand Pirvu (h

Re: [GENERAL] variable value in array_to_string

here on I was looking to get say a.item_id , a.show_id and all sort of variations Thanks for help On Nov 21, 2016, at 4:26 PM, David G. Johnston <david.g.johns...@gmail.com> wrote: > On Mon, Nov 21, 2016 at 3:09 PM, Armand Pirvu (home) <armand.pi...@gmail.com>

Re: [GENERAL] variable value in array_to_string

Played with unnest but not much luck NOTICE: {item_id,show_id} NOTICE: item_id It takes only the first array element in consideration Ug On Nov 21, 2016, at 5:02 PM, Armand Pirvu (home) <armand.pi...@gmail.com> wrote: > My bad on the back tick. No idea why it turned that wa

[GENERAL] variable value in array_to_string

Hi Is there anyway I can pass a variable in the array_to_string function ? CREATE OR REPLACE FUNCTION test1 () RETURNS void AS $$ DECLARE foo text; foo1 text; begin execute 'select ARRAY( SELECT d.COLUMN_NAME::text from information_schema.constraint_table_usage c,

[GENERAL] functions - triggers cross schemas

2 schemas , schema1 and schema2, with same tables , a base table and a tracking one. Search path is set to schema1 by default. insert into schema2.test1 (col2 , col3) values ('foo1','foo2') I get an entry in schema1.test1_hist and not in schema2.test1_hist I understand that the trigger

Re: [GENERAL] functions - triggers cross schemas

u, Oct 13, 2016 at 3:18 PM, Armand Pirvu (home) <armand.pi...@gmail.com> > wrote: > 2 schemas , schema1 and schema2, with same tables , a base table and a > tracking one. > > > Search path is set to schema1 by default. > insert into schema2.test1 (col2 , col3) val

[GENERAL] audit function and old.column

Hello I am using in a project the audit trigger from the wiki One particular aspect is that not all tables that I am after have the same columns . And it is one in particular I am stumbling onto , and it ends i n _add_by. It can be group_add_by, car_add_by and so on. The old value of this

Re: [GENERAL] getting column names

Thanks David Worked like a charm and results are correct Armand On Apr 4, 2017, at 5:00 PM, David G. Johnston <david.g.johns...@gmail.com> wrote: > On Tue, Apr 4, 2017 at 2:51 PM, Armand Pirvu (home) <armand.pi...@gmail.com> > wrote: > > But if I join back

[GENERAL] getting column names

Hi Can somebody please tell me if the below is possible ? I may not see the forest from the trees Thanks Armand levregdb=# select * from foo1; audit_id | table_name --+ 6012 | foo2 6013 | foo2 6014 | foo2 select * from foo2; levregdb=# select *

Re: [GENERAL] audit function and old.column

eat In this case I could go around like it I will post another question related to hstore search since although it has a common ground it is different regarding the goal Thanks Armand On Mar 23, 2017, at 6:38 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 03/23/2017 02:00 PM,

[GENERAL] store key name pattern search

Hi I have the following case select * from foo; col1 - "show_id"=>"1", "group_id"=>"32", "group_name"=>"slb", "group_add_by"=>"557651"

Re: [GENERAL] store key name pattern search

Thank you — Armand On Apr 4, 2017, at 10:50 AM, Oleg Bartunov <obartu...@gmail.com> wrote: > > On Tue, Apr 4, 2017 at 11:41 AM, Armand Pirvu (home) <armand.pi...@gmail.com> > wrote: > Hi > > I have the following case > > > select * from foo; >

[GENERAL] pglogical and slony

Thanks to Adrian I got pointed in the direction of enabling triggers to use with pglogical In other words, whatever comes down the pglogical in terms of inserts/updates/deletes, I can track those in some audit table or do something else That got me thinking why not putting at the end of

Re: [GENERAL] data transformation and replication

pointers ? Thanks Armand On May 8, 2017, at 4:49 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/08/2017 12:46 PM, Armand Pirvu (home) wrote: >> Hi >> >> Here it is a scenario which I am faced with and I am hoping to find a >> pointer/tip/help >

[GENERAL] views and fdw usage and performance

Hi I have two schemas jt1, and jt2 in the same db In both I have the same table tbl3 The idea is to keep in sync jt1.tbl3 from jt2.tbl3 each time I have an insert/update/delete on jt2.tbl3 So I was thinking about the following cases to avoid replication 1) in jt2 rather than have the tbl3

Re: [GENERAL] views and fdw usage and performance

On May 9, 2017, at 7:11 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/09/2017 05:02 PM, armand pirvu wrote: >> Well >> Jt1 is prod and jt2 is dev > > You are talking schemas, not databases, correct? > > Correct >> Before someone pushe

Re: [GENERAL] column names and dollar sign

Thanks Tom Armand On May 17, 2017, at 4:14 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > "Armand Pirvu (home)" <armand.pi...@gmail.com> writes: >> Ran into the following statement > >> CREATE TABLE test( >> Date$ date, >> Month_N

[GENERAL] column names and dollar sign

Hi Ran into the following statement CREATE TABLE test( Date$ date, Month_Number$ int, Month$ varchar(10), Year$ int ); While it does execute, I wonder if the $ has any special meaning ? Can anyone shed some light please ? Thanks Armand -- Sent via

Re: [GENERAL] column names and dollar sign

Thank you Steve — Armand On May 17, 2017, at 4:10 PM, Steve Atkins <st...@blighty.com> wrote: > >> On May 17, 2017, at 2:02 PM, Armand Pirvu (home) <armand.pi...@gmail.com> >> wrote: >> >> Hi >> >> Ran into the following sta

Re: [GENERAL] data transformation and replication

9.5 both But the enable always trigger I missed that Once that set it runs Thank you for your help Armand On May 9, 2017, at 8:26 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/08/2017 08:31 PM, Armand Pirvu (home) wrote: >> My bad >> db1 I have two tabl

[GENERAL] data transformation and replication

Hi Here it is a scenario which I am faced with and I am hoping to find a pointer/tip/help db1 is the OLTP system db2 is the Reporting system The data from db1 needs to get to db2, but the database on those two have tables with different layout/structure and hence data will need to suffer