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

[armand pirvu]

Yep relaoded

But darn typo

Finger going too fast I guess

Thanks bunch
AP 

> On Aug 16, 2017, at 8:03 PM, Ian Barwick  wrote:
> 
> On 08/17/2017 05:26 AM, armand pirvu wrote:
>> 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 postgresql server in replication mode: 
>> FATAL:  no pg_hba.conf entry for replication connection from host 
>> "172.16.26.4", user "repuser", SSL off
>> DETAIL:  dsn was:  host=172.16.26.7 port=5433 dbname=levregdb user=repuser
>> My pg_hba.conf from both
>> local  replication  repuser  md5
>> host   replication  repuser  127.0.0.1/32  mds5
>> host   replication  repuser  0.0.0.0/0 mds5
>> local  all repuser  md5
>> host   all repuser  127.0.0.1/32  md5
>> host   all repuser  0.0.0.0/0 md5
>> So what am I missing ?
>> Strange is that I followed
> 
> Did you reload the configuration after changing pg_hba.conf, e.g.
> "SELECT pg_reload_conf()"?
> 
> Also, looks like you have a typo:
> 
> > host   replication  repuser  127.0.0.1/32  mds5
> > host   replication  repuser  0.0.0.0/0 mds5
> 
> mds5 -> md5
> 
> 
> Regards
> 
> Ian Barwick
> 
> -- 
> Ian Barwick   http://www.2ndQuadrant.com/ 
> <http://www.2ndquadrant.com/>
> PostgreSQL Development, 24x7 Support, Training & Services

[GENERAL] no pg_hba.conf entry for replication connection

[armand pirvu]

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 postgresql server in replication mode: FATAL:  
no pg_hba.conf entry for replication connection from host "172.16.26.4", user 
"repuser", SSL off
DETAIL:  dsn was:  host=172.16.26.7 port=5433 dbname=levregdb user=repuser

My pg_hba.conf from both

local  replication  repuser  md5
host   replication  repuser  127.0.0.1/32  mds5
host   replication  repuser  0.0.0.0/0 mds5
local  all repuser  md5
host   all repuser  127.0.0.1/32  md5
host   all repuser  0.0.0.0/0 md5

So what am I missing ?
Strange is that I followed

Thks for help

AP





-- 
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] Where is pg_hba.conf

[armand pirvu]

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 /Library/PostgreSQL/9.6/data/pg_hba.conf
ls: /Library/PostgreSQL/9.6/data/pg_hba.conf: Permission denied
armandps-MacBook-Air:~ armandp$ sudo ls -l 
/Library/PostgreSQL/9.6/data/pg_hba.conf
-rw---  1 postgres  daemon  4248 Aug 10 21:17 
/Library/PostgreSQL/9.6/data/pg_hba.conf

Is it possible that global search fails from a permission error ?

Hope this helps



> On Aug 13, 2017, at 9:00 AM, Igor Korot  wrote:
> 
> Hi,
> I have a Mac with OSX 10.8 installed. It has Postgre 9.1.
> According to https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
> and 
> http://www.thegeekstuff.com/2014/02/enable-remote-postgresql-connection/?utm_source=tuicool
> I need to modify the pg_hba.conf file to get access to the DB
> remotely.
> 
> However, I can't find this file anywhere on the system.
> I am able to connec to the server locally with psql. I can also
> connect to the server
> from the ODBC driver from my program. However doing a global search I
> can't find that file.
> 
> Can someone please help?
> 
> Thank you.
> 
> 
> -- 
> 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] pglogical sync

[armand pirvu]

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  wrote:
> 
> 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 
> table first.
> Parameters:
> subscription_name - name of the existing subscription
> relation - name of existing table, optionally qualified
> 
> How exactly to you run this ? What am I missing ?
> 
> 
> testrep=# select 
> pglogical.alter_subscription_resynchronize_table(subscription_name := 
> 'mysubscription', 'myschema.tbl1');
> ERROR:  positional argument cannot follow named argument
> LINE 1: ...nize_table(subscription_name := 'mysubscription', 'myschema
>  ^
> testrep=# select 
> pglogical.alter_subscription_resynchronize_table(subscription_name := 
> 'mysubscription', 'myschema.tbl1'::regclass);
> ERROR:  positional argument cannot follow named argument
> LINE 1: ...nize_table(subscription_name := 'mysubscription', 'myschema
>
> 
> 
> 
> Thanks for help
> 
> Armand
> 
> 
> 

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

[armand pirvu]

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  wrote:
> 
> So I ventured in uninstalling the 
> https://www.2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/
>  
> <https://www.2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/>
> 
> 
> 
> yum install 
> http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-3.noarch.rpm
>  
> <http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-3.noarch.rpm>
> yum install postgresql95-pglogical
> 
> testrep=# create extension pglogical;
> ERROR:  could not find function "pglogical_table_data_filtered" in file 
> "/usr/pgsql-9.5/lib/pglogical.so"
> 
> Even if I get back to 1.0.2 same problem which makes me think the issue might 
> be in latest package
> 
> 
> postgresql95-pglogical  x86_64
>   1.2.2-1_2ndQuadrant.el7.centosok 
> postgresql95-pglogical  x86_64
>   2.0.1-1.el7   broken
> 
> 
> 
> 
> What I did notice is the repo different name
> 
> The main issue is that now I am dead in the water on this project
> 
> Any idea how to fix or at leat get back the 1.2.2-1 version ?
> 
> 
> Thanks
> Armand
> 

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

[armand pirvu]

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




yum install 
http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-3.noarch.rpm
 

yum install postgresql95-pglogical

testrep=# create extension pglogical;
ERROR:  could not find function "pglogical_table_data_filtered" in file 
"/usr/pgsql-9.5/lib/pglogical.so"

Even if I get back to 1.0.2 same problem which makes me think the issue might 
be in latest package


postgresql95-pglogical  x86_64  
1.2.2-1_2ndQuadrant.el7.centos  ok 
postgresql95-pglogical  x86_64  
2.0.1-1.el7 broken




What I did notice is the repo different name

The main issue is that now I am dead in the water on this project

Any idea how to fix or at leat get back the 1.2.2-1 version ?


Thanks
Armand

Re: [GENERAL] pglogical repo

[armand pirvu]

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  wrote:
> 
> On 8/10/2017 10:30 AM, armand pirvu wrote:
>> Looking at the installatoion steps and the yum repositories
>> 
>> sudo yum 
>> installhttp://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
>> 
>> 
>> 1.0-3 is for Postgres 9.6 ? Or can it be used on 9.5 as well ? I am on 9.5
> 
> 
> That is clearly answered here, 
> https://www.2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/
> 
> 
> -- 
> john r pierce, recycling bits in santa cruz
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pglogical repo

[armand pirvu]

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   


1.0-3 is for Postgres 9.6 ? Or can it be used on 9.5 as well ? I am on 9.5

Thanks
Armand



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pglogical sync

[armand pirvu]

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 
table first.
Parameters:
subscription_name - name of the existing subscription
relation - name of existing table, optionally qualified

How exactly to you run this ? What am I missing ?


testrep=# select 
pglogical.alter_subscription_resynchronize_table(subscription_name := 
'mysubscription', 'myschema.tbl1');
ERROR:  positional argument cannot follow named argument
LINE 1: ...nize_table(subscription_name := 'mysubscription', 'myschema
 ^
testrep=# select 
pglogical.alter_subscription_resynchronize_table(subscription_name := 
'mysubscription', 'myschema.tbl1'::regclass);
ERROR:  positional argument cannot follow named argument
LINE 1: ...nize_table(subscription_name := 'mysubscription', 'myschema
   



Thanks for help

Armand

Re: [GENERAL] hot standby questions

[armand pirvu]

Thank you guys

I think I kinda shot myself in the foot. I took a look at the wiki and I put in 
-x and so far so good

— Armand



> On Aug 3, 2017, at 11:03 PM, Lucas Possamai  wrote:
> 
> 
> 
> 2017-08-04 5:58 GMT+12:00 Jeff Janes  <mailto:jeff.ja...@gmail.com>>:
> On Wed, Aug 2, 2017 at 8:19 PM, armand pirvu  <mailto:armand.pi...@gmail.com>> wrote:
> 
> 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 pg_stop_backup()"
> 
> pg_basebackup does the equivalent of pg_start_backup and pg_stop_backup for 
> you.  It is not helpful, and might even sometimes be harmful, to do them 
> yourself when using pg_basebackup.
>  
> 
> Pretty much every where I looked at -x is not mentioned to be used
> 
> So what gives ? What did I miss ? It's gotta be soomething
> 
> That certainly isn't my experience.  If you find sites that don't mention -x, 
> -X, or --xlog-method, then I would be reluctant to take any of that site's 
> other advice seriously.  
> 
> But note that in version 10, -x will go away and the default will be changed 
> so that not specifying anything will be the same as -X stream.  perhaps you 
> are reading advice aimed at a future version.
> 
> Cheers,
> 
> Jeff
> 
> 
> I use pg_basebackup every day and the way I do is:
> 
> pg_basebackup -D /destination --checkpoint=fast --xlog-method=stream
> 
> The --xlog-method=stream option will copy the wal_files as well (to pg_xlogs 
> directory).
> 
> That works pretty well for me. You can either add the compress option too. 
> Hope this was helpful.
> 
> Cheers,
> Lucas

Re: [GENERAL] unexpected pageaddr

[armand pirvu]

Well this reproduces with pg_basebackup

Disturbing. I wonder what else if lurking around



> On Aug 2, 2017, at 10:34 PM, armand pirvu  wrote:
> 
> 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  postgres@192.168.5.252:/var/lib/pgsql/9.5
> psql postgres -c "select pg_stop_backup()"
> 
> But
> 
> cp: cannot stat ‘/var/lib/pgsql/pg_xlog_archive/00010078’: No 
> such file or directory
> < 2017-08-02 22:30:40.949 CDT >LOG:  unexpected pageaddr 0/3D00 in log 
> segment 00010078, offset 0
> 
> and later
> 
> 
> cp: cannot stat ‘/var/lib/pgsql/pg_xlog_archive/00010079’: No 
> such file or directory
> < 2017-08-02 22:30:40.949 CDT >LOG:  unexpected pageaddr 0/3D00 in log 
> segment 00010079, offset 0
> cp: cannot stat ‘/var/lib/pgsql/pg_xlog_archive/00010079’: No 
> such file or directory
> 
> Things seem okay but the unexpected pageaddr  error is a bit unsettling .
> 
> Any thougts ? Something to be really worried about ? Did I miss it again ? :))
> 
> 
> Thanks
> Armand
> 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] unexpected pageaddr

[armand pirvu]

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  postgres@192.168.5.252:/var/lib/pgsql/9.5
psql postgres -c "select pg_stop_backup()"

But

cp: cannot stat ‘/var/lib/pgsql/pg_xlog_archive/00010078’: No 
such file or directory
< 2017-08-02 22:30:40.949 CDT >LOG:  unexpected pageaddr 0/3D00 in log 
segment 00010078, offset 0

and later


cp: cannot stat ‘/var/lib/pgsql/pg_xlog_archive/00010079’: No 
such file or directory
< 2017-08-02 22:30:40.949 CDT >LOG:  unexpected pageaddr 0/3D00 in log 
segment 00010079, offset 0
cp: cannot stat ‘/var/lib/pgsql/pg_xlog_archive/00010079’: No 
such file or directory

Things seem okay but the unexpected pageaddr  error is a bit unsettling .

Any thougts ? Something to be really worried about ? Did I miss it again ? :))


Thanks
Armand



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] hot standby questions

[armand pirvu]

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 pg_stop_backup()"


will get me on target
< 2017-08-02 22:09:45.348 CDT >LOG:  database system was interrupted; last 
known up at 2017-08-02 22:06:28 CDT
< 2017-08-02 22:09:45.353 CDT >LOG:  entering standby mode
< 2017-08-02 22:09:45.353 CDT >LOG:  invalid primary checkpoint record
< 2017-08-02 22:09:45.353 CDT >LOG:  invalid secondary checkpoint record
< 2017-08-02 22:09:45.353 CDT >PANIC:  could not locate a valid checkpoint 
record
< 2017-08-02 22:09:45.523 CDT >LOG:  startup process (PID 10895) was terminated 
by signal 6: Aborted
< 2017-08-02 22:09:45.523 CDT >LOG:  aborting startup due to startup process 
failure


where as
psql postgres -c "select pg_start_backup('backup')"
pg_basebackup -D /var/lib/pgsql/sample  -Ft -z -P -x
psql postgres -c "select pg_stop_backup()"

I am good to go

Pretty much every where I looked at -x is not mentioned to be used

So what gives ? What did I miss ? It's gotta be soomething

Thanks
Armand



-- 
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] upsert and update filtering

[armand pirvu]

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  wrote:
> 
> armand pirvu  wrote:
>> But what if in the conflict situation I want to performa the update ONLY if 
>> the record is different. The update seems to happen no matter what
>> In other words is there anyway I can filter the update to happen (based on 
>> the sample date) only for 112 since col2 is different ?
> 
> That's quite possible. An ON CONFLICT's UPDATE accepts a WHERE clause,
> which can reference both existing and excluded tuples. That WHERE clause
> can back out of the UPDATE based on whatever criteria you like.
> 
> -- 
> Peter Geoghegan



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] upsert and update filtering

[armand pirvu]

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 rows)

testdb=# select * from dimc2 order by 1;
 col1 |col2
--+
  111 | foo111
  112 | foo122
  211 | foo211


In general it is my understanding it gows like this


insert into dimc1  select * from dimc2  
on conflict (col1) do update 
SET  
col2 = EXCLUDED.col2
;


insert into dimc1  select * from dimc2  
on conflict (col1) do update 
SET  
col2 = EXCLUDED.col2 returning *
;



So far so good

But what if in the conflict situation I want to performa the update ONLY if the 
record is different. The update seems to happen no matter what
In other words is there anyway I can filter the update to happen (based on the 
sample date) only for 112 since col2 is different ? 



testdb=# select * from dimc1 order by 1;
 col1 |col2
--+
  112 | foo112

testdb=# select * from dimc2 order by 1;
 col1 |col2
--+
  112 | foo122




Thanks
Armand



-- 
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] hash join performance question

[armand pirvu]

> On Jul 18, 2017, at 10:30 PM, Tom Lane  wrote:
> 
> armand pirvu  writes:
>> testdb3=# explain analyze SELECT a.company_id  FROM csischema.dim_company a, 
>> woc.dim_company b
>> testdb3-# WHERE a.company_id = b.company_id;
>>  QUERY PLAN  
>>  
>> ---
>> Hash Join  (cost=711.05..54938.35 rows=18980 width=4) (actual 
>> time=34.067..1118.603 rows=18980 loops=1)
>>   Hash Cond: (a.company_id = b.company_id)
>>   ->  Seq Scan on dim_company a  (cost=0.00..47097.82 rows=1850582 width=4) 
>> (actual time=0.013..523.249 rows=1786376 loops=1)
>>   ->  Hash  (cost=473.80..473.80 rows=18980 width=4) (actual 
>> time=20.203..20.203 rows=18980 loops=1)
>> Buckets: 32768  Batches: 1  Memory Usage: 924kB
>> ->  Seq Scan on dim_company b  (cost=0.00..473.80 rows=18980 
>> width=4) (actual time=0.007..10.076 rows=18980 loops=1)
>> Planning time: 0.511 ms
>> Execution time: 1121.068 ms
>> (8 rows)
> 
>> I was expecting at least the PK of csischema.dim_company to be used . In 
>> another DBMS that was the case. The larger table , csischema.dim_company 
>> used the PK.
> 
> That looks like a perfectly reasonable plan to me.  If you think it isn't,
> perhaps because you're assuming that both tables are fully cached in RAM,
> then you should reduce random_page_cost to teach the planner that that's
> the execution scenario you're expecting.  Everything always in RAM would
> correspond to random_page_cost = 1, and some rough calculations suggest
> that that would reduce the estimated cost of a
> nestloop-with-inner-indexscan enough to make the planner choose that way.
> 
>   regards, tom lane


Thank you Tom

Made a bit reading about the random_page_cost value 
I understand not all optimizers are equal
But for example in Ingres world



K Join(col1)
Heap
Pages 57 Tups 18981
D696 C1139
 /  \
Proj-rest   $tk1
Sorted(col1)I(a)
Pages 76 Tups 18981 B-Tree(col1)
D25 C190Pages 2140 Tups 426435
 /
$tk2
I(b)
B-Tree(NU)
Pages 98 Tups 18981
ds8(armandp):/u1/sys_admin/armandp> time sql -uirs testdb  /dev/null

real0m0.37s
user0m0.04s
sys 0m0.01s


And that is pretty constant, whether pages are in the cache or not
More important IMHO , rather than scan the smaller table , I just scan it’s PK 
which is an index at the end of the day, which then I join with the larger 
table PK
Now granted I have hash joins disabled on Ingres so not sure this is a true 
apple to apple .
And that what made me raise the question
I would like to know why in Postgres smaller table gets scanned  as opposed to 
use it’s PK

After all , one column is far less expensive to traverse top to bottom than all 
columns


Thank you
Armand




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] hash join performance question

[armand pirvu]

Hi

testdb3=# \d csischema.dim_company;
   Table "csischema.dim_company"
 Column  |Type | Modifiers 
-+-+---
 company_id  | integer | not null
 company_name| character varying(100)  | 
 city| character varying(100)  | 
 state   | character varying(100)  | 
 postal_code | character varying(100)  | 
 country | character varying(100)  | 
 latitude| double precision| 
 longitude   | double precision| 
 update_datetime | timestamp without time zone | 
 company_source  | character varying(1)| 
Indexes:
"dim_company_pkey" PRIMARY KEY, btree (company_id)

testdb3=# \d woc.dim_company;
  Table "woc.dim_company"
 Column  |Type | Modifiers 
-+-+---
 company_id  | integer | not null
 company_name| character varying(100)  | 
 city| character varying(100)  | 
 state   | character varying(100)  | 
 postal_code | character varying(100)  | 
 country | character varying(100)  | 
 latitude| double precision| 
 longitude   | double precision| 
 update_datetime | timestamp without time zone | 
 company_source  | character varying(1)| 
Indexes:
"dim_company_pkey" PRIMARY KEY, btree (company_id)

testdb3=# select count(*) from csischema.dim_company;
  count  
-
 1786376
(1 row)

testdb3=# select count(*) from woc.dim_company;
 count 
---
 18980
(1 row)

woc.dim_company is a subset of csischema.dim_company meaning all company_id 
from woc.dim_company  are in csischema.dim_company
Ratio is around 1%




SELECT a.company_id  FROM csischema.dim_company a, woc.dim_company b
WHERE a.company_id = b.company_id;


testdb3=# explain analyze SELECT a.company_id  FROM csischema.dim_company a, 
woc.dim_company b
testdb3-# WHERE a.company_id = b.company_id;
  QUERY PLAN
   
---
 Hash Join  (cost=711.05..54938.35 rows=18980 width=4) (actual 
time=34.067..1118.603 rows=18980 loops=1)
   Hash Cond: (a.company_id = b.company_id)
   ->  Seq Scan on dim_company a  (cost=0.00..47097.82 rows=1850582 width=4) 
(actual time=0.013..523.249 rows=1786376 loops=1)
   ->  Hash  (cost=473.80..473.80 rows=18980 width=4) (actual 
time=20.203..20.203 rows=18980 loops=1)
 Buckets: 32768  Batches: 1  Memory Usage: 924kB
 ->  Seq Scan on dim_company b  (cost=0.00..473.80 rows=18980 width=4) 
(actual time=0.007..10.076 rows=18980 loops=1)
 Planning time: 0.511 ms
 Execution time: 1121.068 ms
(8 rows)


I was expecting at least the PK of csischema.dim_company to be used . In 
another DBMS that was the case. The larger table , csischema.dim_company used 
the PK.


Any hints, thoughts what am I not seing ?


Thank you
Armand



-- 
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] insert on conflict

[armand pirvu]

> On Jun 27, 2017, at 3:30 PM, Peter Geoghegan  wrote:
> 
> On Tue, Jun 27, 2017 at 12:40 PM, armand pirvu  wrote:
>> so how is it working in fact ? Isn't it working like looping in the
>> IVEE.dim_company  and for each company_id if the record does have a
>> correspondent in csischema.dim_company then update csischema.dim_company set
>> company_name = EXCLUDED.company_name  where company_id=...  ? If so isn't it
>> supposed to use the PK for each company_id ? Or is it more like building a
>> whole list from IVEE.dim_company and treat like a join ? Just trying to
>> understand
> 
> The processing here has to happen a tuple at a time. That's because
> the index structure itself is the only authoritative source of truth
> about whether or not there is a possible conflict. An MVCC snapshot
> isn't good enough, because it sees a consistent view of things, not
> the true physical reality of what exists or does not exist in the
> index.
> 
> What you end up with here is a weird nested loop join, you might say.
> The implementation couldn't do it any other way (this could never
> behave more like a merge join), because we must eagerly check for
> conflicts right as we insert (our insert would be registered by
> *other* inserters/upserters as a conflict). If the implementation did
> ever do it that way, it would break the important UPSERT guarantees
> around concurrency.
> 
> MERGE does this in other systems, which is okay for those other
> systems because MERGE makes no special promises about concurrency
> (e.g., you can get a unique violation in the joined-on column with
> MERGE). But, MERGE would be faster for bulk loading, which is what
> MERGE is good for.
> 
> -- 
> Peter Geoghegan

Hi Peter


So for example if IVEE.dim_company has 10k rows and csischema.dim_company has 
40 rows, what will happen for each row in IVEE.dim_company we check 
csischema.dim_company and if the check tells row is in it switches to update 
and this would mean yes I scan IVEE.dim_company, however should an update be 
needed in csischema.dim_company it will use the csischema.dim_company PK since 
we pass one value gotten from IVEE.dim_company
The question I guess is what happens IF I IVEE.dim_company accounts for far 
more than 5% of csischema.dim_company ? Will that translate into a scan on 
csischema.dim_company ?

What I am looking at now looks like a potential racing contention which so I am 
wondering if there are better ways to do it


Thanks
Armand

[GENERAL] insert on conflict

[armand pirvu]

Hi 

Got question 

birstdb=# \d csischema.dim_company
   Table "csischema.dim_company"
 Column  |Type | Modifiers 
-+-+---
 company_id  | integer | not null
 company_name| character varying(100)  | 
 city| character varying(100)  | 
 state   | character varying(100)  | 
 postal_code | character varying(100)  | 
 country | character varying(100)  | 
 latitude| double precision| 
 longitude   | double precision| 
 update_datetime | timestamp without time zone | 
 company_source  | character varying(1)| 
Indexes:
"dim_company_pkey" PRIMARY KEY, btree (company_id)

birstdb=# \d IVEE.dim_company
 Table "ivee.dim_company"
 Column  |Type | Modifiers 
-+-+---
 company_id  | integer | 
 company_name| character varying(100)  | 
 city| character varying(100)  | 
 state   | character varying(100)  | 
 postal_code | character varying(100)  | 
 country | character varying(100)  | 
 latitude| double precision| 
 longitude   | double precision| 
 update_datetime | timestamp without time zone | 
 company_source  | character varying(1)| 




insert into csischema.dim_company select * from IVEE.dim_company on conflict 
(company_id) do update 
SET  
company_name = EXCLUDED.company_name , 
city = EXCLUDED.city ,  
state = EXCLUDED.state ,
postal_code = EXCLUDED.postal_code ,
country = EXCLUDED.country , 
latitude = EXCLUDED.latitude ,
longitude = EXCLUDED.longitude ,
update_datetime = EXCLUDED.update_datetime ,
company_source = EXCLUDED.company_source; 
  QUERY PLAN
   
---
 Insert on dim_company  (cost=0.00..188.32 rows=1232 width=1126)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: dim_company_pkey
   ->  Seq Scan on dim_company dim_company_1  (cost=0.00..188.32 rows=1232 
width=1126)
(4 rows)


so how is it working in fact ? Isn't it working like looping in the 
IVEE.dim_company  and for each company_id if the record does have a 
correspondent in csischema.dim_company then update csischema.dim_company set 
company_name = EXCLUDED.company_name  where company_id=...  ? If so isn't it 
supposed to use the PK for each company_id ? Or is it more like building a 
whole list from IVEE.dim_company and treat like a join ? Just trying to 
understand

Thanks
Armand

Re: [GENERAL] Strange port in pg_upgrade output

[armand pirvu]

Got it



Thank you

> On Jun 12, 2017, at 4:16 PM, Adrian Klaver  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 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 16:00 pts/000:00:00 /usr/pgsql-9.6/bin/postgres 
>> -D /var/lib/pgsql/9.6/data -p 50432 -b -c synchronous_commit=off -c 
>> fsync=off -c full_page_writes=off -c listen_addresses= -c 
>> unix_socket_permissions=0700 -c unix_so
>> postgres 11160 10967  0 16:00 pts/000:00:00 sh -c 
>> "/usr/pgsql-9.6/bin/pg_restore" --host '/var/lib/pgsql' --port 50432 
>> --username 'postgres' --exit-on-error --verbose --dbname 'dbname=birstdb' 
>> "pg_upgrade_dump_25288.custom" >> "pg_upgrad
>> postgres 11161 11160  6 16:00 pts/000:00:00 
>> /usr/pgsql-9.6/bin/pg_restore --host /var/lib/pgsql --port 50432 --username 
>> postgres --exit-on-error --verbose --dbname dbname=birstdb 
>> pg_upgrade_dump_25288.custom
>> sudo grep -i port /var/lib/pgsql/9.6/data/postgresql.conf
>> [sudo] password for armandp:
>> port = 5432  # (change requires restart)
>>  # supported by the operating system:
>> Is it something that I missed or is it intentionally using a non default 
>> port to avoid unintended client connections ?
> 
> https://www.postgresql.org/docs/9.6/static/pgupgrade.html 
> <https://www.postgresql.org/docs/9.6/static/pgupgrade.html>
> 
> "Obviously, no one should be accessing the clusters during the upgrade. 
> pg_upgrade defaults to running servers on port 50432 to avoid unintended 
> client connections. You can use the same port number for both clusters when 
> doing an upgrade because the old and new clusters will not be running at the 
> same time. However, when checking an old running server, the old and new port 
> numbers must be different."
> 
>> Thanks
>> Armand
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>

[GENERAL] Strange port in pg_upgrade output

[armand pirvu]

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 16:00 pts/000:00:00 /usr/pgsql-9.6/bin/postgres -D 
/var/lib/pgsql/9.6/data -p 50432 -b -c synchronous_commit=off -c fsync=off -c 
full_page_writes=off -c listen_addresses= -c unix_socket_permissions=0700 -c 
unix_so
postgres 11160 10967  0 16:00 pts/000:00:00 sh -c 
"/usr/pgsql-9.6/bin/pg_restore" --host '/var/lib/pgsql' --port 50432 --username 
'postgres' --exit-on-error --verbose --dbname 'dbname=birstdb' 
"pg_upgrade_dump_25288.custom" >> "pg_upgrad
postgres 11161 11160  6 16:00 pts/000:00:00 /usr/pgsql-9.6/bin/pg_restore 
--host /var/lib/pgsql --port 50432 --username postgres --exit-on-error 
--verbose --dbname dbname=birstdb pg_upgrade_dump_25288.custom

sudo grep -i port /var/lib/pgsql/9.6/data/postgresql.conf
[sudo] password for armandp: 
port = 5432 # (change requires restart)
# supported by the operating system:


Is it something that I missed or is it intentionally using a non default port 
to avoid unintended client connections ?


Thanks
Armand



-- 
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] Vacuum and state_change

[armand pirvu]

> On Jun 9, 2017, at 5:42 PM, Adrian Klaver  wrote:
> 
> On 06/09/2017 02:26 PM, armand pirvu wrote:
>>> On Jun 9, 2017, at 4:20 PM, Adrian Klaver  wrote:
>>> 
>>> On 06/09/2017 02:01 PM, armand pirvu wrote:
>>>>> On Jun 9, 2017, at 3:52 PM, Adrian Klaver  
>>>>> wrote:
>>>>> 
>>>>> On 06/09/2017 01:31 PM, armand pirvu wrote:
>>>>> 
>>>>>>> 
>>> 
>>> 
>>>> By temporary tables I mean just regular table not tables created by 
>>>> "create temporary table" . I should have been more precise. We call them 
>>>> temporary since we do drop them after all is said and done. Maybe we 
>>>> should change the way we call them
>>> 
>>> You will want to look at this before making that decision:
>>> 
>>> https://www.postgresql.org/docs/9.6/static/sql-createtable.html
>>> 
>>> Temporary Tables
>>> 
>>> 
>>> Basically, temporary tables are session specific.
>>> 
>> I noticed that, but since we use multiple schemas can not have a session 
>> temp table in non temp schema
> 
> A true temporary table is going to be in its own temporary schema:
> 
> https://www.postgresql.org/docs/9.6/static/runtime-config-client.html#GUC-SEARCH-PATH
>  
> <https://www.postgresql.org/docs/9.6/static/runtime-config-client.html#GUC-SEARCH-PATH>
> 
> "Likewise, the current session's temporary-table schema, pg_temp_nnn, is 
> always searched if it exists. It can be explicitly listed in the path by 
> using the alias pg_temp. If it is not listed in the path then it is searched 
> first (even before pg_catalog). However, the temporary schema is only 
> searched for relation (table, view, sequence, etc) and data type names. It is 
> never searched for function or operator names.”

The search_path I am aware of it. But it is more than that in our case hence we 
decided to have several schemas with quote/unquote or what we call temp tables 
or we can recall them as staging tables

Their purpose is to help with the transition from old system to new (in some 
way anyways)



> 
>> We have those in place for a specific reason in case we mess some processing 
>> in between and we want to be able to have the data which we started with.
>>> 
> 
>> As of now I don’t think we have a draw back per se. We are poised to go live 
>> on Postgres soon though so I was thinking maybe have this upgrade done 
>> before going live ? Just a thought
> 
> Yeah well if you are in pre-production why not, if no other reason then you 
> get another year of community support on the back end.
> 

My sentiments exactly


> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>

Re: [GENERAL] Vacuum and state_change

[armand pirvu]

> On Jun 9, 2017, at 4:20 PM, Adrian Klaver  wrote:
> 
> On 06/09/2017 02:01 PM, armand pirvu wrote:
>>> On Jun 9, 2017, at 3:52 PM, Adrian Klaver  wrote:
>>> 
>>> On 06/09/2017 01:31 PM, armand pirvu wrote:
>>> 
>>>>> 
> 
> 
>> By temporary tables I mean just regular table not tables created by "create 
>> temporary table" . I should have been more precise. We call them temporary 
>> since we do drop them after all is said and done. Maybe we should change the 
>> way we call them
> 
> You will want to look at this before making that decision:
> 
> https://www.postgresql.org/docs/9.6/static/sql-createtable.html
> 
> Temporary Tables
> 
> 
> Basically, temporary tables are session specific.
> 

I noticed that, but since we use multiple schemas can not have a session temp 
table in non temp schema

We have those in place for a specific reason in case we mess some processing in 
between and we want to be able to have the data which we started with.



> 
>> 9.5 but considering I can track what auto vacuum does I was thinking to use 
>> that as a reason to the upgrade advantage
> 
> It is nice, you just have to weigh against what effect the other changes:
> 
> https://www.postgresql.org/docs/9.6/static/release-9-6.html
> 
> might have on your setup.
> 
> 

As of now I don’t think we have a draw back per se. We are poised to go live on 
Postgres soon though so I was thinking maybe have this upgrade done before 
going live ? Just a thought



> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com



-- 
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] Vacuum and state_change

[armand pirvu]

> On Jun 9, 2017, at 3:52 PM, Adrian Klaver  wrote:
> 
> On 06/09/2017 01:31 PM, armand pirvu wrote:
> 
>>> 
>>> Are these large tables?
> 
> 
>> I would say yes
>> select count(*) from csischema.tf_purchased_badge;
>>  9380749
>> select count(*) from csischema.tf_purchases_person;
>>  19902172
>> select count(*) from csischema.tf_demographic_response_person;
>>  80868561
>> select count(*) from csischema.tf_transaction_item_person;
>>  3281084
>> Interesting enough two completed
> 
> So the two 'smaller' tables which would make sense.
> 
>>   relname   | seq_scan | seq_tup_read | idx_scan | 
>> idx_tup_fetch | n_tup_ins | n_tup_upd | n_live_tup | n_dead_tup | 
>> n_mod_since_analyze | last_vacuum |last_autovacuum| 
>> autovacuum_count
>> +--+--+--+---+---+---+++-+-+---+--
>>  tf_transaction_item_person |  160 |0 |   476810 |
>> 1946119 |  2526 |473678 |3226110 |  0 |   
>> 116097 | | 2017-06-09 11:15:24.701997-05 |2
>>  tf_purchased_badge |  358 |   1551142438 |  2108331 |
>> 7020502 |  5498 |   1243746 |9747336 | 107560 |   
>> 115888 | | 2017-06-09 15:09:16.624363-05 |1
>> I did notice though that checkpoints seem a bit too often aka below 5 min 
>> from start to end
> 
> You probably should take a look at:
> 
> https://www.postgresql.org/docs/9.6/static/wal-configuration.html
> 
> and
> 
> https://www.postgresql.org/docs/9.6/static/routine-vacuuming.html#AUTOVACUUM
> 
> 
> 
>> These tables suffer quite some data changes IIRC but that comes via some 
>> temp tables which reside in a temp  schema and some previous messages from 
>> the log suggest that it might have ran into  ladder locking in early stages, 
>> aka tmp table locked from vacuum  and any further processing waiting for it 
>> and causing some other waits on those largish tables
> 
> Did you do a manual VACUUM of the temporary tables?
> 
> If not see below.
> 
>> Considering the temp ones are only for load and yes some processing goes in 
>> there , I am thinking disabling auto vacuum for the temp tables . Or should 
>> I disable auto vacuum all together and run say as a bath job on a weekend 
>> night ?
> 
> I don't think temporary tables are the problem as far as autovacuum goes:
> 
> https://www.postgresql.org/docs/9.6/static/routine-vacuuming.html#AUTOVACUUM
> 
> "Temporary tables cannot be accessed by autovacuum. Therefore, appropriate 
> vacuum and analyze operations should be performed via session SQL commands.”


By temporary tables I mean just regular table not tables created by "create 
temporary table" . I should have been more precise. We call them temporary 
since we do drop them after all is said and done. Maybe we should change the 
way we call them



> 
>>> If you are on Postgres 9.6:
>>> 
>>> https://www.postgresql.org/docs/9.6/static/progress-reporting.html
>>> 
>> Aside that there are vacuum improvements and such, any other strong 
>> compelling reason to upgrade to 9.6 ?
> 
> 
> That would depend on what version you are on now. If it is out of support 
> then there would be a reason to upgrade, not necessarily to 9.6 though.

9.5 but considering I can track what auto vacuum does I was thinking to use 
that as a reason to the upgrade advantage



> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com



-- 
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] Vacuum and state_change

[armand pirvu]

> On Jun 9, 2017, at 11:23 AM, Adrian Klaver  wrote:
> 
> On 06/09/2017 09:13 AM, armand pirvu wrote:
>>> On Jun 9, 2017, at 11:01 AM, Adrian Klaver >> <mailto: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);
>>>> 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 |  query_start  |
>>>>  state_change  | state  | query
>>>> ---+---+---+---++--
>>>> 10677 | 2017-06-09 10:25:49.189848-05 | 2017-06-09 10:33:43.598805-05 | 
>>>> 2017-06-09 10:33:43.599091-05 | idle   | SELECT 1
>>>> 11096 | 2017-06-09 10:27:03.686588-05 | 2017-06-09 10:33:56.28736-05  | 
>>>> 2017-06-09 10:33:56.287364-05 | active | select distinct pid, 
>>>> backend_start, query_start, state_change, state, query from 
>>>> pg_catalog.pg_stat_activity order by 1;
>>>> 13277 | 2017-06-09 07:48:49.506686-05 | 2017-06-09 07:48:52.887185-05 | 
>>>> 2017-06-09 07:48:52.887188-05 | active | autovacuum: VACUUM 
>>>> csischema.tf_purchased_badge
>>>> 13484 | 2017-06-09 10:31:54.127672-05 | 2017-06-09 10:33:47.137938-05 | 
>>>> 2017-06-09 10:33:47.138226-05 | idle   | SELECT 1
>>>> 16886 | 2017-06-09 07:56:49.033893-05 | 2017-06-09 07:56:49.078369-05 | 
>>>> 2017-06-09 07:56:49.078371-05 | active | autovacuum: VACUUM 
>>>> csischema.tf_purchases_person
>>>> 25387 | 2017-06-09 05:32:08.079397-05 | 2017-06-09 05:32:08.385728-05 | 
>>>> 2017-06-09 05:32:08.385731-05 | active | autovacuum: VACUUM 
>>>> csischema.tf_demographic_response_person
>>>> 37465 | 2017-06-09 08:50:58.992002-05 | 2017-06-09 08:51:21.506829-05 | 
>>>> 2017-06-09 08:51:21.506831-05 | active | autovacuum: VACUUM 
>>>> csischema.tf_transaction_item_person
>>>> I did notice that state_change did not change one bit
>>> 
>>> Did the state change?
>>> 
>> No and that was what got me worried
> 
> Are these large tables?
> 
> 

I would say yes

select count(*) from csischema.tf_purchased_badge;
 9380749

select count(*) from csischema.tf_purchases_person;
 19902172

select count(*) from csischema.tf_demographic_response_person;
 80868561

select count(*) from csischema.tf_transaction_item_person;
 3281084

Interesting enough two completed

  relname   | seq_scan | seq_tup_read | idx_scan | 
idx_tup_fetch | n_tup_ins | n_tup_upd | n_live_tup | n_dead_tup | 
n_mod_since_analyze | last_vacuum |last_autovacuum| 
autovacuum_count 
+--+--+--+---+---+---+++-+-+---+--
 tf_transaction_item_person |  160 |0 |   476810 |   
1946119 |  2526 |473678 |3226110 |  0 |  116097 
| | 2017-06-09 11:15:24.701997-05 |2
 tf_purchased_badge |  358 |   1551142438 |  2108331 |   
7020502 |  5498 |   1243746 |9747336 | 107560 |  115888 
| | 2017-06-09 15:09:16.624363-05 |1


I did notice though that checkpoints seem a bit too often aka below 5 min from 
start to end


2017-06-09 14:18:38.552 CDT,,,888,,593a1810.378,271,,2017-06-08 22:37:52 
CDT,,0,LOG,0,"checkpoint starting: time",""
2017-06-09 14:21:12.210 CDT,,,888,,593a1810.378,272,,2017-06-08 22:37:52 
CDT,,0,LOG,0,"checkpoint complete: wrote 12070 buffers (9.2%); 0 
transaction log file(s) added, 0 removed, 4 recycled; write=148.714 s, 
sync=4.834 s, total=153.657 s; sync files=17, longest=1.841 s, average=0.284 s; 
distance=89452 kB, estimate=89452 kB",""

2017-06-09 14:23:38.278 CDT,,,888,,593a1810.378,273,,2017-06-08 22:37:52 
CDT,,0,LOG,0,"checkpoint starting: time",""
2017-06-09 14:24:38.629 CDT,,,888,,593a1810.378,274,,2017-06-08 22:37:52 
CDT,,0,LOG,0,"checkpoint complete: wrote 593 buffers (0.5%); 0 transaction 
log file(s) added, 0 removed, 1 recycled; write=59.825 s, sync=0.474 s, 
total=60.3

Re: [GENERAL] Vacuum and state_change

[armand pirvu]

> On Jun 9, 2017, at 11:01 AM, Adrian Klaver  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);
>> 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 |  query_start  | 
>> state_change  | state  | query
>> ---+---+---+---++--
>>  10677 | 2017-06-09 10:25:49.189848-05 | 2017-06-09 10:33:43.598805-05 | 
>> 2017-06-09 10:33:43.599091-05 | idle   | SELECT 1
>>  11096 | 2017-06-09 10:27:03.686588-05 | 2017-06-09 10:33:56.28736-05  | 
>> 2017-06-09 10:33:56.287364-05 | active | select distinct pid, backend_start, 
>> query_start, state_change, state, query from pg_catalog.pg_stat_activity 
>> order by 1;
>>  13277 | 2017-06-09 07:48:49.506686-05 | 2017-06-09 07:48:52.887185-05 | 
>> 2017-06-09 07:48:52.887188-05 | active | autovacuum: VACUUM 
>> csischema.tf_purchased_badge
>>  13484 | 2017-06-09 10:31:54.127672-05 | 2017-06-09 10:33:47.137938-05 | 
>> 2017-06-09 10:33:47.138226-05 | idle   | SELECT 1
>>  16886 | 2017-06-09 07:56:49.033893-05 | 2017-06-09 07:56:49.078369-05 | 
>> 2017-06-09 07:56:49.078371-05 | active | autovacuum: VACUUM 
>> csischema.tf_purchases_person
>>  25387 | 2017-06-09 05:32:08.079397-05 | 2017-06-09 05:32:08.385728-05 | 
>> 2017-06-09 05:32:08.385731-05 | active | autovacuum: VACUUM 
>> csischema.tf_demographic_response_person
>>  37465 | 2017-06-09 08:50:58.992002-05 | 2017-06-09 08:51:21.506829-05 | 
>> 2017-06-09 08:51:21.506831-05 | active | autovacuum: VACUUM 
>> csischema.tf_transaction_item_person
>> I did notice that state_change did not change one bit
> 
> Did the state change?
> 

No and that was what got me worried 


>> Does that mean that something is not quite right with the vacuums ?
> 
> Might want to take a look at:
> 
> https://www.postgresql.org/docs/9.6/static/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW
>  
> <https://www.postgresql.org/docs/9.6/static/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW>
> 
> 
>> Thank you
>> Armand
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>

[GENERAL] Vacuum and state_change

[armand pirvu]

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 |  query_start  |
 state_change  | state  |   
   query   
---+---+---+---++--
 10677 | 2017-06-09 10:25:49.189848-05 | 2017-06-09 10:33:43.598805-05 | 
2017-06-09 10:33:43.599091-05 | idle   | SELECT 1
 11096 | 2017-06-09 10:27:03.686588-05 | 2017-06-09 10:33:56.28736-05  | 
2017-06-09 10:33:56.287364-05 | active | select distinct pid, backend_start, 
query_start, state_change, state, query from pg_catalog.pg_stat_activity order 
by 1;
 13277 | 2017-06-09 07:48:49.506686-05 | 2017-06-09 07:48:52.887185-05 | 
2017-06-09 07:48:52.887188-05 | active | autovacuum: VACUUM 
csischema.tf_purchased_badge
 13484 | 2017-06-09 10:31:54.127672-05 | 2017-06-09 10:33:47.137938-05 | 
2017-06-09 10:33:47.138226-05 | idle   | SELECT 1
 16886 | 2017-06-09 07:56:49.033893-05 | 2017-06-09 07:56:49.078369-05 | 
2017-06-09 07:56:49.078371-05 | active | autovacuum: VACUUM 
csischema.tf_purchases_person
 25387 | 2017-06-09 05:32:08.079397-05 | 2017-06-09 05:32:08.385728-05 | 
2017-06-09 05:32:08.385731-05 | active | autovacuum: VACUUM 
csischema.tf_demographic_response_person
 37465 | 2017-06-09 08:50:58.992002-05 | 2017-06-09 08:51:21.506829-05 | 
2017-06-09 08:51:21.506831-05 | active | autovacuum: VACUUM 
csischema.tf_transaction_item_person

I did notice that state_change did not change one bit

Does that mean that something is not quite right with the vacuums ? 

Thank you
Armand





-- 
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] jsonb case insensitive search

[armand pirvu]

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  wrote:
> 
> 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  wrote:
>> 
>> On May 31, armand pirvu modulated:
>> 
>>> The idea is that I would like to avoid having an index for each key
>>> possibly wanted to search, but rather say have the whole record then
>>> search by the key and get the key value, thus having one index serving
>>> multiple purposes so to speak
>>> 
>> 
>> First, benchmarking would be important to figure out if any proposed
>> indexing actually speeds up the kinds of queries you want to perform.
>> With the recently added parallel query features, a simpler indexing
>> scheme with some brute-force search might be adequate?
>> 

Not sure what you mean by benchmarking
But I think comparative times , aka 2 seconds vs a couple milliseconds is quite 
a difference. 
A table scan while in certain cases is okay , in a case when there is heavy 
usage on the same part/area , it will become a problem. 


>> But, you could use a search idiom like this:
>> 
>>(lower(json_column::text)::json) -> lower('key') = 'value'::json
>> 
>> This will down-convert the case on all values and keys.  The left-hand
>> parenthetic expression could be precomputed in an expression index to
>> avoid repeated case conversion. But, typical searches will still have
>> to scan the whole index to perform the projection and match the final
>> value tests on the right-hand side.
>> 
>> If you want to do things like substring matching on field values, you
>> might stick with text and using regexp matches:
>> 
>>(lower(json_column::text)) ~ ‘valuepattern'

In this case a regular index will be ignored even though IMO it should scan the 
index and get the needed information
The criteria I am after gets back 9 rows max out of 100k+ records so I say the 
restriction is darn good. Wouldn’t that be the case for the optimizer to pick 
the path with the least resistance aka best restriction ? Granted it uses a 
lower function which and the search in the text column which is the third in 
the index is not really starting form left. But the index starts with show_id , 
file_id and those are always part of the key. I can see though once the 
show_id, file_id is NOT a good restriction anymore , than the last column will 
make the difference . Either case will that not translate into an index scan ? 
Or the index to be considered in this case, event the last column search has to 
follow the left to right, aka not in between search ?  


>> 
>> or more structural searches:
>> 
>>(lower(json_column::text)) ~ '"key": "[^"]*substring[^"]*"'
>> 
>> Here, the left-hand expression could be trigram indexed to help with
>> sparse, substring matching without a full index scan.  We've had good
>> luck using trigram indexing with regexp matching, though I've honestly
>> never used it for the purpose sketched above...
>> 
>> Karl
> 


Seems to me trigram could be the answer since I have some decent results once I 
applied it, more to dig

Overall could it be that the optimizer blatantly ignores a scan index which is 
cheaper than a table scan, or jsonb implementation still has a long way to come 
up  or the way it is used in my case is not the one designed for ?


thanks
Armand






-- 
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] jsonb case insensitive search

[armand pirvu]

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  wrote:
> 
> On May 31, armand pirvu modulated:
> 
>> The idea is that I would like to avoid having an index for each key
>> possibly wanted to search, but rather say have the whole record then
>> search by the key and get the key value, thus having one index serving
>> multiple purposes so to speak
>> 
> 
> First, benchmarking would be important to figure out if any proposed
> indexing actually speeds up the kinds of queries you want to perform.
> With the recently added parallel query features, a simpler indexing
> scheme with some brute-force search might be adequate?
> 
> But, you could use a search idiom like this:
> 
> (lower(json_column::text)::json) -> lower('key') = 'value'::json
> 
> This will down-convert the case on all values and keys.  The left-hand
> parenthetic expression could be precomputed in an expression index to
> avoid repeated case conversion. But, typical searches will still have
> to scan the whole index to perform the projection and match the final
> value tests on the right-hand side.
> 
> If you want to do things like substring matching on field values, you
> might stick with text and using regexp matches:
> 
> (lower(json_column::text)) ~ 'valuepattern'
> 
> or more structural searches:
> 
> (lower(json_column::text)) ~ '"key": "[^"]*substring[^"]*"'
> 
> Here, the left-hand expression could be trigram indexed to help with
> sparse, substring matching without a full index scan.  We've had good
> luck using trigram indexing with regexp matching, though I've honestly
> never used it for the purpose sketched above...
> 
> Karl



-- 
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] jsonb case insensitive search

[armand pirvu]

> On May 31, 2017, at 2:32 PM, David G. Johnston  
> wrote:
> 
> On Wed, May 31, 2017 at 12:18 PM, armand pirvu  <mailto:armand.pi...@gmail.com>> wrote:
> 
> For the example mentioned
> 
> SELECT * 
> FROM cfg_files_data 
> WHERE cfg_files_data.show_id = 32 
> AND cfg_files_data.file_id = 123
> AND lower((file_data_record ->> 'Company')) = lower('CompuTestSystems');
> ;
> 
> 
> create index cfg_files_data_record_idx on cfg_files_data (show_id, file_id, 
> ​​lower(file_data_record::text));
> 
> 
> Not sure why the index is ignored
> 
> Because ​"lower((file_data_record ->> 'Company'))" is not the same as 
> ​"lower(file_data_record::text)”


I see, missed it 

Cause this works
create index fooidx on cfg_files_data (show_id, file_id, lower(file_data_record 
->> 'Company'));

The idea is that I would like to avoid having an index for each key possibly 
wanted to search, but rather say have the whole record then search by the key 
and get the key value, thus having one index serving multiple purposes so to 
speak


I looked at JSQuery but does not seem that I can have a composite index like 
fooidx ???!


> 
> 
> But is is possible to go for a broader search, aka being able to search by 
> any key:value , efficient and case insensitive ? What am I missing in this 
> picture ?
> 
> 
> Use a trigger to maintain an all lower case copy of the json file_data_record 
> and use the copy for predicates while using the original ​for select-list 
> outputs.
> 
> David J.



Uhh that would be let’s just say less than optimal. But maybe JSON itself is 
not intended to be used this way ? Or the functionality is just not there yet ?


Thanks
Armand

[GENERAL] jsonb case insensitive search

[armand pirvu]

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 = 123
AND lower((file_data_record ->> 'Company')) = lower('CompuTestSystems');
;


- Table definition
   Table "csischema.cfg_files_data"
   Column   |   Type   |   
Modifiers   
+--+---
 file_data_id   | bigint   | not null default 
nextval('cfg_files_data_file_data_id_seq'::regclass)
 file_id| bigint   | not null
 show_id| bigint   | not null
 file_data_record   | jsonb| not null
 additional_info| jsonb| 
 file_data_add_by   | character varying(100)   | not null
 file_data_add_date | timestamp with time zone | not null default now()
Indexes:
"cfg_files_data_pkey" PRIMARY KEY, btree (file_data_id, show_id)


- show_id, file_id data distribution

select show_id, file_id, count(*) from cfg_files_data group by show_id, file_id 
order by 1;
 show_id | file_id | count 
-+-+---
   4 |   9 | 3
   4 |  68 |22
   4 |   2 | 6
   6 |   3 |13
   6 |   5 |   215
  13 |  13 |13
  13 |   8 |22
  21 |  11 |13
  21 |  10 |22
  26 |  12 |13
  30 |  16 | 6
  32 | 123 | 53731
  35 | 125 | 5
  40 | 122 | 53731
  46 |  69 | 4
  46 |  64 | 4
  46 |  67 | 4
  46 |  70 | 4

For the example mentioned

SELECT * 
FROM cfg_files_data 
WHERE cfg_files_data.show_id = 32 
AND cfg_files_data.file_id = 123
AND lower((file_data_record ->> 'Company')) = lower('CompuTestSystems');
;


create index cfg_files_data_record_idx on cfg_files_data (show_id, file_id, 
lower(file_data_record::text));

SELECT * 
FROM cfg_files_data 
WHERE cfg_files_data.show_id = 35 
AND cfg_files_data.file_id = 125
AND lower((file_data_record ->> 'Company')) = lower('CompuTestSystems');
;


 QUERY PLAN 


 Index Scan using cfg_files_data_record_idx on cfg_files_data  (cost=0.54..8.57 
rows=1 width=359) (actual time=0.287..0.305 rows=5 loops=1)
   Index Cond: ((show_id = 35) AND (file_id = 125))
   Filter: (lower((file_data_record ->> 'Company'::text)) = 
'computestsystems'::text)
 Planning time: 0.271 ms
 Execution time: 0.370 ms
(5 rows)


SELECT * 
FROM cfg_files_data 
WHERE cfg_files_data.show_id = 32 
AND cfg_files_data.file_id = 123
AND lower((file_data_record ->> 'Company')) = lower('CompuTestSystems');
;

  QUERY PLAN
   
---
 Seq Scan on cfg_files_data  (cost=0.00..7276.20 rows=134 width=359) (actual 
time=194.817..194.848 rows=2 loops=1)
   Filter: ((show_id = 32) AND (file_id = 123) AND (lower((file_data_record ->> 
'Company'::text)) = 'computestsystems'::text))
   Rows Removed by Filter: 107829
 Planning time: 1.006 ms
 Execution time: 194.905 ms

Shall I understand that prior to apply the lower((file_data_record ->> 
'Company'::text)) = 'computestsystems'::text) filtering, it goes by show_id and 
field_id and for 32,123 since it is way above 5% it gets into a seq scan ? 
Although the end result is just 2 rows


SET enable_seqscan = OFF;
   QUERY PLAN   
 
-
 Bitmap Heap Scan on cfg_files_data  (cost=2485.18..8544.40 rows=134 width=359) 
(actual time=173.314..173.317 rows=2 loops=1)
   Recheck Cond: (show_id = 32)
   Filter: ((file_id = 123) AND (lower((file_data_record ->> 'Company'::text)) 
= 'computestsystems'::text))
   Rows Removed by Filter: 53729
   Heap Blocks: exact=2437
   ->  Bitmap Index Scan on cfg_files_data_pkey  (cost=0.00..2485.15 rows=53743 
width=0) (actual time=12.195..12.195 rows=53731 loops=1)
 Index Cond: (show_id = 32)
 Planning time: 0.232 ms
 Execution time: 173.392 ms
(9 rows)

And that got me to look at 

SELECT * 

Re: [GENERAL] column names and dollar sign

[Armand Pirvu (home)]

Thanks  Tom


Armand

On May 17, 2017, at 4:14 PM, Tom Lane  wrote:

> "Armand Pirvu (home)"  writes:
>> 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 ?
> 
> Postgres thinks it's a valid identifier character, as long as it's
> not the first character.  I don't believe it's standard SQL, but
> we hacked it in a long time ago for compatibility with Oracle or
> somebody like that.
> 
>   regards, tom lane



-- 
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] column names and dollar sign

[Armand Pirvu (home)]

Thank you Steve

— Armand

On May 17, 2017, at 4:10 PM, Steve Atkins  wrote:

> 
>> On May 17, 2017, at 2:02 PM, Armand Pirvu (home)  
>> wrote:
>> 
>> 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 ?
> 
> No special meaning to postgresql - in postgresql a dollar sign is a valid 
> character in an identifier.
> 
> It might have some special meaning to the app that was using it, perhaps.
> 
> Cheers,
>  Steve
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] column names and dollar sign

[Armand Pirvu (home)]

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 pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pglogical and slony

[Armand Pirvu (home)]

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 pglogical, Slony  which is 
trigger based ?


Postgres 9.5.5

edb1 192.168.5.251  -   master pglogical
edb2 192.168.5.252  -   slave pglogical


edb2 192.168.5.252  -   master slony
edb3 192.168.5.253  -   slave slony



sudo -u postgres /usr/pgsql-9.5/bin/slonik << _EOF_
cluster name=armandpc123;
node 1 admin conninfo = 'dbname=csidb host=192.168.5.252 user=postgres';
node 2 admin conninfo = 'dbname=csidb host=192.168.5.253 user=postgres';
init cluster (id=1, comment='Node 1');
store node (id=2, comment='Node 2',event node=1);
store path (server=1, client=2, conninfo='dbname=csidb host=192.168.5.252 
user=postgres', connretry=10);
store path (server=2, client=1, conninfo='dbname=csidb host=192.168.5.253 
user=postgres', connretry=10);
create set (id=1, origin=1, comment='some test tables');
set add table (id=1, set id=1, origin=1, fully qualified name='public.tbl2', 
comment='tbl2 table', key='tbl2_pkey');
_EOF_


- master (edb2 192.168.5.252)
sudo -u postgres /usr/pgsql-9.5/bin/slon armandpc123 "dbname=csidb 
user=postgres host=192.168.5.252"
- slave (edb3 192.168.5.253)
sudo -u postgres /usr/pgsql-9.5/bin/slon armandpc123 "dbname=csidb 
user=postgres host=192.168.5.253"


sudo -u postgres /usr/pgsql-9.5/bin/slonik << _EOF_
cluster name=armandpc123;
node 1 admin conninfo = 'dbname=csidb host=192.168.5.252 user=postgres';
node 2 admin conninfo = 'dbname=csidb host=192.168.5.253 user=postgres';
subscribe set (id=1, provider=1, receiver=2, forward=no);
_EOF_



csidb=# \d tbl2;
Table "public.tbl2"
 Column | Type  | Modifiers 
+---+---
 col1   | integer   | not null
 col2   | character(10) | 
Indexes:
"tbl2_pkey" PRIMARY KEY, btree (col1)
Triggers:
_armandpc123_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON tbl2 FOR EACH 
ROW EXECUTE PROCEDURE _armandpc123.denyaccess('_armandpc123')
_armandpc123_truncatedeny BEFORE TRUNCATE ON tbl2 FOR EACH STATEMENT 
EXECUTE PROCEDURE _armandpc123.deny_truncate()
Disabled user triggers:
_armandpc123_logtrigger AFTER INSERT OR DELETE OR UPDATE ON tbl2 FOR EACH 
ROW EXECUTE PROCEDURE _armandpc123.logtrigger('_armandpc123', '1', 'k')
_armandpc123_truncatetrigger BEFORE TRUNCATE ON tbl2 FOR EACH STATEMENT 
EXECUTE PROCEDURE _armandpc123.log_truncate('1')




csidb=# alter table tbl2 ENABLE ALWAYS TRIGGER _armandpc123_logtrigger;
ALTER TABLE
csidb=# \d tbl2
Table "public.tbl2"
 Column | Type  | Modifiers 
+---+---
 col1   | integer   | not null
 col2   | character varying(10) | 
Indexes:
"tbl2_pkey" PRIMARY KEY, btree (col1)
Triggers:
_armandpc123_truncatetrigger BEFORE TRUNCATE ON tbl2 FOR EACH STATEMENT 
EXECUTE PROCEDURE _armandpc123.log_truncate('1')
Disabled user triggers:
_armandpc123_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON tbl2 FOR EACH 
ROW EXECUTE PROCEDURE _armandpc123.denyaccess('_armandpc123')
_armandpc123_truncatedeny BEFORE TRUNCATE ON tbl2 FOR EACH STATEMENT 
EXECUTE PROCEDURE _armandpc123.deny_truncate()
Triggers firing always:
_armandpc123_logtrigger AFTER INSERT OR DELETE OR UPDATE ON tbl2 FOR EACH 
ROW EXECUTE PROCEDURE _armandpc123.logtrigger('_armandpc123', '1', 'k')
trig_hist_aud_prev_tbl2 AFTER INSERT OR DELETE OR UPDATE ON tbl2 FOR EACH 
ROW EXECUTE PROCEDURE func_audit_tname()


csidb=# alter table tbl2 disable trigger _armandpc123_denyaccess;

Manual insert in edb2.csidb.tbl2 replicates fine to edb3.csidb.tbl2, but 
nothing coming in say like replicating from edb1.csidb.tbl2 via pglogical 
although triggers are enabled

Is this possible ? 


Thank you

Armand




-- 
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] views and fdw usage and performance

[Armand Pirvu (home)]

On May 9, 2017, at 7:11 PM, Adrian Klaver  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 pushes to prod it does work in dev. The jdbc connection 
> 
> That would concern me, as anything bad that happened in the dev schema could 
> bring the entire database to its knees, including the prod schema.
> 
> How does data get into the prod schema if the connection is to the dev schema?

If you are a user in say category  B you get to dev where you do your thing. If 
you deem okay you push to prod.
If you are a user in say category A you get to prod



> 
>> routes to jt2. In the mean time it wad needed that some tables in prod are 
>> synced at all times from dev. Hence the view/fdw.
>> What I meant by connections was more to say the type of load or users doing 
>> something in each schema.
> 
> The issue being that if you are pushing data from jt2 --> jt1 you are also 
> pushing the load in the same direction.

I see but short of using something like Slony in between the  two schemas I 
don’t see a pretty simple choice


> 
>> So my questions still remain

And about the plan from the fdw am I right or wrong ? I am inclined to say I am 
right based on the numbers in the timings


>> Sent from my iPhone
>> On May 9, 2017, at 6:52 PM, Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote:
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com



-- 
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] views and fdw usage and performance

[armand pirvu]

Well

Jt1 is prod and jt2 is dev
Before someone pushes to prod it does work in dev. The jdbc connection routes 
to jt2. In the mean time it wad needed that some tables in prod are synced at 
all times from dev. Hence the view/fdw.
What I meant by connections was more to say the type of load 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  wrote:
> 
>> On 05/09/2017 02:36 PM, Armand Pirvu (home) wrote:
>> 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 table, have a view named tbl3 based on 
>> jt1.tbl3
>> 2) use the postgtres fdw and in jt2 have a foreign table tbl3 to jt1.tbl3
> 
> Why?
> If it is all on the same database why not just use jt1.tbl3?
> 
>> create table tbl3 (col1 integer, col2 integer, col3 integer, primary key 
>> (col1));
>> insert into tbl3
>> select generate_series(1, 1000), 111,222;
>> Questions:
>> q1 - Any issues with this logic ? Or any other ways to do this better ? jt2 
>> usage in terms of concurrent users and so on is by far much less than jt1, 
>> at least one order of magnitude less
> 
> Connections are to the database not the schema.
> 
>> q2 - query performance (select * from tbl3 where col1=499123;
>> ) . While using views it is clear cut the output of explain, on fdw not so 
>> much
>> explain analyze select * from jt2.tbl3 where col1=874433;
>>QUERY PLAN
>> 
>>  Foreign Scan on tbl3  (cost=100.00..138.66 rows=11 width=12) (actual 
>> time=0.204..0.205 rows=1 loops=1)
>>  Planning time: 0.043 ms
>>  Execution time: 0.374 ms
>> (3 rows)
>> explain analyze select * from jt1.tbl3 where col1=874433;
>>QUERY PLAN
>> -
>>  Index Scan using tbl3_pkey on tbl3  (cost=0.43..8.45 rows=1 width=12) 
>> (actual time=0.010..0.011 rows=1 loops=1)
>>Index Cond: (col1 = 874433)
>>  Planning time: 0.035 ms
>>  Execution time: 0.021 ms
>> (4 rows)
>> Do I understand correctly that the output of (explain analyze select * from 
>> jt2.tbl3 where col1=874433) is in essence (for all practical purposes) the 
>> same as the one from (explain analyze select * from jt1.tbl3 where 
>> col1=874433;) and not a sequential scan like the following ?
>> explain analyze select * from jt1.tbl3 where col2=874433;
>>QUERY PLAN
>> 
>>  Seq Scan on tbl3  (cost=0.00..179053.25 rows=1 width=12) (actual 
>> time=498.020..498.020 rows=0 loops=1)
>>Filter: (col2 = 874433)
>>Rows Removed by Filter: 998
>>  Planning time: 0.030 ms
>>  Execution time: 498.034 ms
>> (5 rows)
>> Thanks
>> Armand
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

[GENERAL] views and fdw usage and performance

[Armand Pirvu (home)]

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 table, have a view named tbl3 based on 
jt1.tbl3

2) use the postgtres fdw and in jt2 have a foreign table tbl3 to jt1.tbl3

create table tbl3 (col1 integer, col2 integer, col3 integer, primary key 
(col1));

insert into tbl3 
select generate_series(1, 1000), 111,222;

Questions:

q1 - Any issues with this logic ? Or any other ways to do this better ? jt2 
usage in terms of concurrent users and so on is by far much less than jt1, at 
least one order of magnitude less

q2 - query performance (select * from tbl3 where col1=499123;
) . While using views it is clear cut the output of explain, on fdw not so much



explain analyze select * from jt2.tbl3 where col1=874433;
   QUERY PLAN   


 Foreign Scan on tbl3  (cost=100.00..138.66 rows=11 width=12) (actual 
time=0.204..0.205 rows=1 loops=1)
 Planning time: 0.043 ms
 Execution time: 0.374 ms
(3 rows)


explain analyze select * from jt1.tbl3 where col1=874433;
   QUERY PLAN   
 
-
 Index Scan using tbl3_pkey on tbl3  (cost=0.43..8.45 rows=1 width=12) (actual 
time=0.010..0.011 rows=1 loops=1)
   Index Cond: (col1 = 874433)
 Planning time: 0.035 ms
 Execution time: 0.021 ms
(4 rows)


Do I understand correctly that the output of (explain analyze select * from 
jt2.tbl3 where col1=874433) is in essence (for all practical purposes) the same 
as the one from (explain analyze select * from jt1.tbl3 where col1=874433;) and 
not a sequential scan like the following ?

explain analyze select * from jt1.tbl3 where col2=874433;
   QUERY PLAN   


 Seq Scan on tbl3  (cost=0.00..179053.25 rows=1 width=12) (actual 
time=498.020..498.020 rows=0 loops=1)
   Filter: (col2 = 874433)
   Rows Removed by Filter: 998
 Planning time: 0.030 ms
 Execution time: 498.034 ms
(5 rows)



Thanks
Armand



-- 
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] data transformation and replication

[Armand Pirvu (home)]

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  wrote:

> On 05/08/2017 08:31 PM, Armand Pirvu (home) wrote:
>> My bad
>> db1 I have two tables t1 and t2 (or more)
>> db2 has one table t3 for example which can get data aggregated from one or 
>> more multiple tables from the above set . I can updates/inserts/deletes in 
>> db1.t1 and/or db1.t2 which combined may mean related data in db.t3 would 
>> need to be inserted/deleted/updated. Think of it like ETL processing if you 
>> will. This is what I mean by data massaging/transformation
>> db1 and db2 are two different servers.
> 
> What are the Postgres versions?
> 
>> So I was initially thinking that I can have on db2 the same set of tables 
>> from db1, replication being done using pglogical. Once data gets to db2 t1 
>> and t2, I can have on db2 a set of functions/triggers which can transform 
>> the data and as such do the relevant inserts/updates/delete from db2.t3
>> Apparently though that is not possible unless I am missing something
> 
> Probably this:
> 
> https://www.2ndquadrant.com/en/resources/pglogical/pglogical-docs/
> 
> "4.11 Triggers
> 
> Apply process and the initial COPY process both run with 
> session_replication_role set to replica which means that ENABLE REPLICA and 
> ENABLE ALWAYS triggers will be fired."
> 
> https://www.postgresql.org/docs/9.6/static/sql-altertable.html
> 
> "DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
> 
>  ...  The trigger firing mechanism is also affected by the configuration 
> variable session_replication_role. Simply enabled triggers will fire when the 
> replication role is "origin" (the default) or "local". Triggers configured as 
> ENABLE REPLICA will only fire if the session is in "replica" mode, and 
> triggers configured as ENABLE ALWAYS will fire regardless of the current 
> replication mode.
> ...
> "
> 
> So did you ENABLE REPLICA or ALWAYS on the db2 table triggers?
> 
> 
>> I reached that conclusion by using a trigger and a function like the 
>> auditing one to track insers/updates/deletes in an audit table
>> Having these said I was thinking
>> (a) -
>> On db1 I will have the t3 table as is on dsb2. All data transformation goes 
>> into db1.t3 which on it's turn will replicate to db2.t3 using pglogical
>> (b) -
>> On db2 I will have the t1 t2 as they are on db1. Those are replicated using 
>> Slony/Bucardo. Once data lands on db2.t1 and db2.t2 another set of 
>> triggers/functions responsible for data transformation will do the 
>> inserts/deletes/updates in db2.t3
>> I wold much prefer pglogical approach as stated in the what I see as a 
>> failed case
>> If the only options is Slony/Bucardo , so be it. but that begs the following 
>> questions
>> - which one has the smallest overhead ?
>> - which one is the easiest to manage ?
>> - which one is the most reliable ?
>> - I recall data transformation can be used in Bucardo but did not see any 
>> examples on that. Any pointers ?
>> Thanks
>> Armand
>> On May 8, 2017, at 4:49 PM, Adrian Klaver > <mailto: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
>>>> 
>>>> 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 
>>>> some transformation in between in real time
>>>> 
>>>> I was looking at something like
>>>> 
>>>> db1 -> db2 replicates the same set of tables and with the same structures 
>>>> using pglogical for example
>>>> db2.tbl1 -> db2.tbl2 data gets massages/transformed based on what 
>>>> replicates from db1.tbl1 using triggers and functions
>>>> 
>>>> 
>>>> Other than that I reckon db1 -> db2 would be trigger based using something 
>>>> like slonik maybe (?) and data massage/transformation gets moved from db2 
>>>> to db1 machine and then db1.tbl2 -> db2.tbl2 using pglogical
>>> 
>>> I was following you until the last part, "... moved from db2 to db1 machine 
>>> and then db1.tbl2 -> db2.tbl2 ..."
>>> 
>>> Is this correct?
>>> 
>>> If so why db1 --> db2 --> db1 --> db2?
>>> 
>>> A complete answer is going to depend on at least an outline of what you 
>>> mean by massage/transform?
>>> 
>>>> 
>>>> 
>>>> Is this doable ? If so any pointers as to where to look about it ?
>>>> 
>>>> 
>>>> Many thanks
>>>> Armand
>>>> 
>>>> 
>>>> 
>>>> 
>>> 
>>> 
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: [GENERAL] data transformation and replication

[Armand Pirvu (home)]

My bad


db1 I have two tables t1 and t2 (or more)
db2 has one table t3 for example which can get data aggregated from one or more 
multiple tables from the above set . I can updates/inserts/deletes in db1.t1 
and/or db1.t2 which combined may mean related data in db.t3 would need to be 
inserted/deleted/updated. Think of it like ETL processing if you will. This is 
what I mean by data massaging/transformation


db1 and db2 are two different servers.


So I was initially thinking that I can have on db2 the same set of tables from 
db1, replication being done using pglogical. Once data gets to db2 t1 and t2, I 
can have on db2 a set of functions/triggers which can transform the data and as 
such do the relevant inserts/updates/delete from db2.t3

Apparently though that is not possible unless I am missing something

I reached that conclusion by using a trigger and a function like the auditing 
one to track insers/updates/deletes in an audit table

Having these said I was thinking

(a) - 
On db1 I will have the t3 table as is on dsb2. All data transformation goes 
into db1.t3 which on it's turn will replicate to db2.t3 using pglogical

(b) -
On db2 I will have the t1 t2 as they are on db1. Those are replicated using 
Slony/Bucardo. Once data lands on db2.t1 and db2.t2 another set of 
triggers/functions responsible for data transformation will do the 
inserts/deletes/updates in db2.t3

I wold much prefer pglogical approach as stated in the what I see as a failed 
case 


If the only options is Slony/Bucardo , so be it. but that begs the following 
questions
- which one has the smallest overhead ?
- which one is the easiest to manage ?
- which one is the most reliable ?
- I recall data transformation can be used in Bucardo but did not see any 
examples on that. Any pointers ?

Thanks
Armand



On May 8, 2017, at 4:49 PM, Adrian Klaver  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
>> 
>> 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 
>> some transformation in between in real time
>> 
>> I was looking at something like
>> 
>> db1 -> db2 replicates the same set of tables and with the same structures 
>> using pglogical for example
>> db2.tbl1 -> db2.tbl2 data gets massages/transformed based on what replicates 
>> from db1.tbl1 using triggers and functions
>> 
>> 
>> Other than that I reckon db1 -> db2 would be trigger based using something 
>> like slonik maybe (?) and data massage/transformation gets moved from db2 to 
>> db1 machine and then db1.tbl2 -> db2.tbl2 using pglogical
> 
> I was following you until the last part, "... moved from db2 to db1 machine 
> and then db1.tbl2 -> db2.tbl2 ..."
> 
> Is this correct?
> 
> If so why db1 --> db2 --> db1 --> db2?
> 
> A complete answer is going to depend on at least an outline of what you mean 
> by massage/transform?
> 
>> 
>> 
>> Is this doable ? If so any pointers as to where to look about it ?
>> 
>> 
>> Many thanks
>> Armand
>> 
>> 
>> 
>> 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

[GENERAL] data transformation and replication

[Armand Pirvu (home)]

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 some 
transformation in between in real time

I was looking at something like 

db1 -> db2 replicates the same set of tables and with the same structures using 
pglogical for example 
db2.tbl1 -> db2.tbl2 data gets massages/transformed based on what replicates 
from db1.tbl1 using triggers and functions


Other than that I reckon db1 -> db2 would be trigger based using something like 
slonik maybe (?) and data massage/transformation gets moved from db2 to db1 
machine and then db1.tbl2 -> db2.tbl2 using pglogical 


Is this doable ? If so any pointers as to where to look about it ?


Many thanks
Armand




-- 
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] getting column names

[Armand Pirvu (home)]

Thanks David


Worked like a charm and results are correct



Armand

On Apr 4, 2017, at 5:00 PM, David G. Johnston  
wrote:

> On Tue, Apr 4, 2017 at 2:51 PM, Armand Pirvu (home)  
> wrote:
> 
> But if I join back to foo1 like below I get
> 
> select a.audit_id, a.table_name, b[1],b[2]
> from
> foo1 a,
> (select
> array(
> select
> column_name::text from
> information_schema.columns
> where
> table_name=a.table_name
> and
> (
> column_name like '%add_by%'
> or
> column_name like '%add_date%'
> )) b) as foo
> ;
> 
> ERROR:  invalid reference to FROM-clause entry for table "a"
> LINE 10: table_name=a.table_name
> ^
> HINT:  There is an entry for table "a", but it cannot be referenced from this 
> part of the query.
> 
> ​​https://www.postgresql.org/docs/9.6/static/sql-select.html​
> ​
> ​Adding LATERAL before the second "from_item" should get rid of the error - 
> whether it results in a working and/or correct query I don't know.​
> 
> David J.
> 

[GENERAL] getting column names

[Armand Pirvu (home)]

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 * from foo2;
 foo2_add_by | foo2_add_date 
-+---
(0 rows)

levregdb=# \d foo2;
   Table "csiprev.foo2"
Column | Type  | Modifiers 
---+---+---
 foo2_add_by   | character(10) | 
 foo2_add_date | character(10) | 



My intention is to have an output like

 6012 | foo2|foo2_add_by|foo2_add_date
 6013 | foo2|foo2_add_by|foo2_add_date
 6014 | foo2|foo2_add_by|foo2_add_date


select a.audit_id, a.table_name, b[1],b[2]
from 
foo1 a,
(select
array(
select 
column_name::text from 
information_schema.columns 
where 
table_name='foo2'
and 
(
column_name like '%add_by%'
or
column_name like '%add_date%'
)) b) as foo
;


 audit_id | table_name |  b  |   b   
--++-+---
 6012 | foo2   | foo2_add_by | foo2_add_date
 6013 | foo2   | foo2_add_by | foo2_add_date
 6014 | foo2   | foo2_add_by | foo2_add_date


But if I join back to foo1 like below I get 

select a.audit_id, a.table_name, b[1],b[2]
from 
foo1 a,
(select
array(
select 
column_name::text from 
information_schema.columns 
where 
table_name=a.table_name
and 
(
column_name like '%add_by%'
or
column_name like '%add_date%'
)) b) as foo
;

ERROR:  invalid reference to FROM-clause entry for table "a"
LINE 10: table_name=a.table_name
^
HINT:  There is an entry for table "a", but it cannot be referenced from this 
part of the query.


Any idea what am I doing wrong ?
Can I do it ?


Thanks
Armand



-- 
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] store key name pattern search

[Armand Pirvu (home)]

Thank you 


— Armand


On Apr 4, 2017, at 10:50 AM, Oleg Bartunov  wrote:

> 
> On Tue, Apr 4, 2017 at 11:41 AM, Armand Pirvu (home)  
> wrote:
> Hi
> 
> I have the following case
> 
> 
> select * from foo;
>   col1
> -
>  "show_id"=>"1", "group_id"=>"32", "group_name"=>"slb", 
> "group_add_by"=>"557651"
>  "show_id"=>"2", "group_id"=>"33", "group_name"=>"slc", 
> "item_add_by"=>"557652"
> (2 rows)
> 
> Is there anyway I can do a pattern search by hstore key name something like
> 
> select * from foo where skeys(col1) like '%add_by%';
> 
> I looked on the doc but did not see anything , or did I miss it ?
> 
> No. You may convert to json and use jsquery 
> https://github.com/postgrespro/jsquery 
> 
> Thanks
> 
> Armand
> 
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 

[GENERAL] store key name pattern search

[Armand Pirvu (home)]

Hi

I have the following case


select * from foo;
  col1  
 
-
 "show_id"=>"1", "group_id"=>"32", "group_name"=>"slb", "group_add_by"=>"557651"
 "show_id"=>"2", "group_id"=>"33", "group_name"=>"slc", "item_add_by"=>"557652"
(2 rows)

Is there anyway I can do a pattern search by hstore key name something like 

select * from foo where skeys(col1) like '%add_by%';

I looked on the doc but did not see anything , or did I miss it ?

Thanks

Armand



-- 
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] audit function and old.column

[Armand Pirvu (home)]

Sorry for the delay

This is the one I used
https://wiki.postgresql.org/wiki/Audit_trigger



And the cfg_global_audit



  Column  |   Type   |   Modifiers
--+--+
 audit_id | bigint   | not null
 table_name   | text | not null
 show_id  | bigint   | 
 user_name| character varying(100)   | 
 action_timestamp | timestamp with time zone | not null default now()
 action   | text | not null
 query| text | 
 pflag| boolean  | 
 pjob | bigint   | 
 old_values   | hstore   | 
 new_values   | hstore   | 



I have OLD and NEW that is true

I went this way

  select hstore(new.*)->(select * from (select skeys(hstore(new.*)) ) as 
cfg_skeys where  skeys like ('%_add_by%')) into skeys_add_by;
  insert into cfg_global_audit
  values (audit_tx_id, tg_table_name::text, new.show_id, skeys_add_by, 
current_timestamp, 'U', current_query(),'N', 0,hstore(old.*), hstore(new.*));


See the problem is that I might have group_add_by as a key, but I can also have 
instead of group_add_by, item_add_by

if I would have a function that I could search in an store buy a key name 
pattern matching , aka get me the value for a key like ‘%add_by%’ it would be 
great


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  wrote:

> On 03/23/2017 02:00 PM, Armand Pirvu (home) wrote:
> 
> Try number two.
> 
>> Hello
>> 
>> I am using in a project the audit trigger from the wiki
> 
> Witch one?
> (It seems the other spelling is banned)
> 
>> 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 column I need to record in the case of an update for 
>> example. The bad thing is also that it's position is not always the same. 
>> Meaning it can be 4th , 2nd and so on
>> 
>> Just trying to avoid to have a function for each table and a bunch of hard 
>> coding which would be less than ideal
> 
> So in the below what is the table schema for cfg_global_audit?
> 
>> 
>> 
>> 
>> drop function func_global_audit();
>> create or replace function func_global_audit() returns trigger as $$
>> declare
>> audit_tx_id bigint;
>> begin
>>select nextval('seq_aud_hist_prev'::regclass) into audit_tx_id;
>>if tg_op = 'UPDATE' then
>>insert into cfg_global_audit
>>values (audit_tx_id, tg_table_name::text, current_user::text, 
>> current_timestamp, 'U', current_query(),'N', hstore(old.*), hstore(new.*), 
>> akeys(hstore(new.*) - hstore(old.*)));
>>return new;
>>end if;
>> end;
>> $$
>> language plpgsql security definer;
>> 
>> drop trigger trig_cfg_group on cfg_group;
>> create trigger trig_cfg_group after insert or update or delete on cfg_group 
>> for each row execute procedure func_global_audit();
>> 
>> 
>> 
>> levregdb=# select old_values from cfg_global_audit;
>>  
>>  old_values
>> 
>> 
>> -
>> "show_id"=>"1", "group_id"=>"33", "group_name"=>"Region 1", 
>> "group_note"=>"test1", "group_type"=>"Files", "group_add_by"=>"557651", 
>> "group_add_date"=>"2016-09-28 09:52:47.672398-05", "additional_info
>> "=>NULL, "group_description"=>""
>> (1 row)
>> 
>> So the idea is that I need 557651 which is the group_add_by old value 
>> recorded in user_name of the audit table
> 
> Don't you have the OLD values in  wherever you put hstore(OLD.*)?
> 
> You have 557651, so I am not sure what you mean by you need it?
> 
>> 
>> 1- is there any way I can look

[GENERAL] audit function and old.column

[Armand Pirvu (home)]

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 column I need to record in the case of an update for 
example. The bad thing is also that it's position is not always the same. 
Meaning it can be 4th , 2nd and so on

Just trying to avoid to have a function for each table and a bunch of hard 
coding which would be less than ideal 



drop function func_global_audit();
create or replace function func_global_audit() returns trigger as $$
declare
audit_tx_id bigint;
begin
select nextval('seq_aud_hist_prev'::regclass) into audit_tx_id;
if tg_op = 'UPDATE' then
insert into cfg_global_audit
values (audit_tx_id, tg_table_name::text, current_user::text, 
current_timestamp, 'U', current_query(),'N', hstore(old.*), hstore(new.*), 
akeys(hstore(new.*) - hstore(old.*)));
return new;
end if;
end;
$$
language plpgsql security definer;

drop trigger trig_cfg_group on cfg_group;
create trigger trig_cfg_group after insert or update or delete on cfg_group for 
each row execute procedure func_global_audit();



levregdb=# select old_values from cfg_global_audit;

   old_values   
   
 

-
 "show_id"=>"1", "group_id"=>"33", "group_name"=>"Region 1", 
"group_note"=>"test1", "group_type"=>"Files", "group_add_by"=>"557651", 
"group_add_date"=>"2016-09-28 09:52:47.672398-05", "additional_info
"=>NULL, "group_description"=>""
(1 row)

So the idea is that I need 557651 which is the group_add_by old value recorded 
in user_name of the audit table

1- is there any way I can look in the OLD record for such column something like 
OLD.%add_by% ?
2 - I was thinking also getting the column name which is easy and prepend with 
old. string and then do a select old.group_add_by into myvar. Apparently it 
does not work

I mean yes I can do
select 'old.'||column_name from information_schema.columns where table_name = 
'cfg_group' and column_name like '%_add_by%' ;

But 
create or replace function func_global_audit() returns trigger as $$
declare
audit_tx_id bigint;
cfg_schema text;
cfg_by_col text;
cfg_by_col1 text;
begin
  select current_schema into cfg_schema;
  select nextval('seq_aud_hist_prev'::regclass) into audit_tx_id;
 if tg_op = 'UPDATE' then
 execute 'select COLUMN_NAME::text FROM INFORMATION_SCHEMA.COLUMNS WHERE 
table_schema='||quote_literal(cfg_schema)||' and 
table_name='||quote_literal(tg_table_name)||' and column_name like 
'||quote_literal('%_add_by%')  into cfg_by_col;
 cfg_by_col1 := 'old.'||cfg_by_col;
 raise notice '%', cfg_by_col1;
 insert into cfg_global_audit values  (audit_tx_id, 
tg_table_name::text, cfg_by_col1::text, current_timestamp, 'U', 
current_query(),'N', hstore(old.*), hstore(new.*), akeys(hstore(new.*) - 
hstore(old.*)));
 return new;
 end if;
 end;
 $$
language plpgsql security definer;

drop trigger trig_cfg_group on cfg_group;
create trigger trig_cfg_group after insert or update or delete on cfg_group for 
each row execute procedure func_global_audit();


And I get old.group_add_by instead of 557651

3 - I tried


drop function func_global_audit();
create or replace function func_global_audit() returns trigger as $$
declare
audit_tx_id bigint;
cfg_schema text;
cfg_by_col text;
cfg_by_col1 text;
begin
 select current_schema into cfg_schema;
 select nextval('seq_aud_hist_prev'::regclass) into audit_tx_id;
if tg_op = 'UPDATE' then
execute 'select COLUMN_NAME::text FROM INFORMATION_SCHEMA.COLUMNS WHERE 
table_schema='||quote_literal(cfg_schema)||' and 
table_name='||quote_literal(tg_table_name)||' and column_name like 
'||quote_literal('%_add_by%')  into cfg_by_col;
cfg_by_col1 := 'old.'||cfg_by_col;
raise notice '%', cfg_by_col1;
execute 'insert into cfg_global_audit1 select $1'   using  cfg_by_col1;
return new;
end if;
end;
$$
language plpgsql security definer;



And the same thing


Can someone point me what am I doing wrong ? 


Thks




-- 
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] Plpgsql - Custom fields Postgres 9.5

[armand pirvu]

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:
> 
> 
> 
> 2016-12-15 14:00 GMT+13:00 David G. Johnston :
>>> On Wed, Dec 14, 2016 at 5:12 PM, rob stone  wrote:
>> 
>>> 
>>> On Wed, 2016-12-14 at 17:00 -0700, David G. Johnston wrote:
>>> > On Wed, Dec 14, 2016 at 4:49 PM, Patrick B 
>>> > wrote:
>>> > > ERROR:  function logextract(integer, integer) does not exist
>>> > > LINE 1: select logextract(20160901,20161001);
>>> > >
>>> >
>>> > So change the constants you are passing into your function to text
>>> > (i.e., surrounding them with single quotes) so it matches the new
>>> > function signature.
>>> >
>>> > There exists an element of understanding the options you are being
>>> > given and adapting if something basic like this is overlooked.
>>> >
>>> > David J.
>>> 
>>> 
>>> 1) Have you run a \df+ and made sure the function has been created
>>> correctly?
>> 
>> ​It was created originally using integer arguments - and thus was being 
>> called that way.  It was intentionally changed to use "text" arguments per a 
>> suggestion but without any recognition that the call site needed to change 
>> as well - hence the error.​  Running \df+ would give the expected output.  
>> What could be a problem is if the original function wasn't dropped so while 
>> the text arg'd one was created the actual call would still reference the old 
>> int arg'd version and any changes would not appear to have been made.
>> 
>>> 
>>> 2) In your first post there is a single apostrophe after the execute
>>> instruction. Can't see the closing apostrophe but then my eyesight is
>>> not the best.
>> 
>> ​I'd recommend using the "format" function but last time I did that the 
>> person I way trying to help got mad...​
>> 
>>> 
>>> 3) I've always found it easier to TO_CHAR a date column when using it
>>> for comparison purposes.
>> 
>> ​I'm not following this "use text" approach at all...​I get the logistics 
>> but PostgreSQL allows for comparison of date typed data...
>> 
>> David J.
>> 
> 
> 
> I've done:
> 
> 1. Deleted all the functions;
> 2. Created a new function:
> 
>  CREATE or REPLACE FUNCTION l_extract(date_end text))
> RETURNS void AS $$
> 
> DECLARE
> date_start date := CURRENT_DATE;
> 
> begin
>   execute '
>   COPY 
>   (
>   SELECT
>   uuid,
>   clientid,
>   *
>   FROM
>   logging
>   WHERE
>   logtime
>   BETWEEN
>  ' || date_start || '
>   AND
>   ' || date_end || '
>   )
>   TO ''/var/lib/postgresql/'|| date_start ||'_logs.csv''';
> end
> $$ language 'plpgsql';
> 
> 3. Calling the function:
> select l_extract('20160901');
> 
> select l_extract('2016-09-01'); --> doesn't work either
> 
> 
> 
> 4. Error:
> 
> ERROR:  operator does not exist: timestamp without time zone >= integer
> 
> LINE 13: BETWEEN
> 
>  ^
> 
> HINT:  No operator matches the given name and argument type(s). You might 
> need to add explicit type casts.
> 
> QUERY:  
> 
>   COPY 
>   (
>   SELECT
>   uuid,
>   clientid,
>   *
>   FROM
>   logging
>   WHERE
>   logtime
>   BETWEEN
> 2016-12-15
>   AND
>   20160901
>   )
> 
>   TO '/var/lib/postgresql/2016-12-15_logs.csv'
> 
> CONTEXT:  PL/pgSQL function iknock_log_extract(text) line 7 at EXECUTE
> 
> 
> 
> 
> 
> 5. \d+ logging:
> 
> log_time   | timestamp(3) without time zone
> 
> 
> 
> 6. Query below works:
> 
>   SELECT
> 
>   uuid,
> 
>   clientid,
> 
>   *
> 
>   FROM
> 
>   logging
> 
>   WHERE
> 
>   logtime
> 
>   BETWEEN
> 
>  '2016-12-15'
> 
>   AND
> 
>   '20160901'
> 
> 
> 
> Still can't understand what's going on =\
> 
> 

Re: [GENERAL] variable value in array_to_string

[Armand Pirvu (home)]

1 - I added some new notes.
2 - That code I know and works fine. I used it in  slightly different contexts 
in other projects

Here it is a case . 

A table with two columns , data type is irrelevant


I have a need in which I may get a.col1,a.col2 and other variations

Point is yes I could build any variation re-running a slightly different way 
the query in the catalogs

But why hit 3 or 4 times when I could manipulate an array  like below ? This 
code just came in my mind (and works)


CREATE OR REPLACE FUNCTION test1 () RETURNS void AS $$
DECLARE
var1 text[];
var2 text;
var3 text;
var4 text;
begin
execute 
'select ARRAY( select column_name::text from information_schema.columns where 
table_name='
||
quote_literal('foo')
||')'  into var1;
raise notice '%', var1;
select array_to_string(array(select  'a.'||unnest(var1)),',') into var4;
raise notice '%', var4;
END;
$$ LANGUAGE plpgsql ;


levregdb1=# select test1(); 

 NOTICE:  
{col1,col2}
NOTICE:  a.col1,a.col2

Note the a. being prepended to each of the array’s elements


3 - I appreciate the help. Keep in mind I am not a developer and this is a new 
venture for me.  On the same token  I am not an ignorant either . What 
irritates me is the TRUE lack in this case of explanations in the docs. I know 
another DBMS just  as bad in the documentation area (worse in fact)


I turned to the community. And yes I know how to make cases since I worked 
support.

Frankly I am turning off since I sure as heck don’t like being patronized. Did 
not accept this (and never will) from my own mom let alone somebody else

My apologies for asking help. Will try not to do it again


Thanks and laters



On Nov 21, 2016, at 7:12 PM, David G. Johnston  
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 (home)  
> wrote:
> Played with unnest  but not much luck
> 
> If you want help you will need to show your work - ideally with examples that 
> can execute with meaningful data on an empty database.
> 
> The follow gives the expected results:
> 
> DO $$
> DECLARE
> foo text[];
> foo1 text;
> delim text := ',';
> begin
> foo := ARRAY['one','two']::text[];
> foo1 := array_to_string(foo, delim);
> RAISE NOTICE '%', foo1;
> END;
> $$;
> 
> NOTICE: one,two
> 
> David J.
> 

Re: [GENERAL] variable value in array_to_string

[Armand Pirvu (home)]

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)  wrote:

> My bad on the back tick. No idea why it turned that way
> 
> OK got that David. 
> 
> The idea is that I have the pk columns in an array which I would like to 
> manipulate from the array itself rather than running same query variations 
> multiple times
> 
> For example I get in foo
> 
> {item_id,show_id}
> 
> And from 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  
> wrote:
> 
>> On Mon, Nov 21, 2016 at 3:09 PM, Armand Pirvu (home) 
>>  wrote:
>> Hi
>> 
>> Is there anyway I can pass a variable in the array_to_string function ?
>> 
>> 
>> ​Yes, just like you can pass variables to any other 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, 
>> information_schema.key_column_usage d '
>> ||
>> 'where c.constraint_name=d.constraint_name and c.table_name=d.table_name and 
>> c.table_schema='
>> ||
>> quote_literal(‘myschema')
>> ||
>> ' and c.table_schema=d.table_schema and c.constraint_name like '
>> ||
>> quote_literal('%_pkey%')
>> ||
>> ') ' into foo;
>> raise notice '%', foo;
>> foo1 :=ARRAY_TO_STRING(foo, ', ');
>> raise notice '%', foo1;
>> END;
>> $$ LANGUAGE plpgsql ;
>> 
>> 
>> The idea is that I want to slice and dice and not run the query x amount of 
>> times
>> 
>> 
>> I would suggest you learn to use the "format()" function, EXECUTE USING,​ 
>> and dollar quoting.
>> 
>> 
>> So, I copy-pasted your example and placed it into a DO block
>> 
>> The character before "myschema" came out as a back-tick instead of a 
>> single-quote.
>> 
>> array_to_string has the signature (text[], text) but you've defined foo as 
>> being just text.  Changing that results in a query that executes - though 
>> given my system doesn't have conforming data I get no results.
>> 
>> David J.
> 

Re: [GENERAL] variable value in array_to_string

[Armand Pirvu (home)]

My bad on the back tick. No idea why it turned that way

OK got that David. 

The idea is that I have the pk columns in an array which I would like to 
manipulate from the array itself rather than running same query variations 
multiple times

For example I get in foo

{item_id,show_id}

And from 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  
wrote:

> On Mon, Nov 21, 2016 at 3:09 PM, Armand Pirvu (home)  
> wrote:
> Hi
> 
> Is there anyway I can pass a variable in the array_to_string function ?
> 
> 
> ​Yes, just like you can pass variables to any other 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, 
> information_schema.key_column_usage d '
> ||
> 'where c.constraint_name=d.constraint_name and c.table_name=d.table_name and 
> c.table_schema='
> ||
> quote_literal(‘myschema')
> ||
> ' and c.table_schema=d.table_schema and c.constraint_name like '
> ||
> quote_literal('%_pkey%')
> ||
> ') ' into foo;
> raise notice '%', foo;
> foo1 :=ARRAY_TO_STRING(foo, ', ');
> raise notice '%', foo1;
> END;
> $$ LANGUAGE plpgsql ;
> 
> 
> The idea is that I want to slice and dice and not run the query x amount of 
> times
> 
> 
> I would suggest you learn to use the "format()" function, EXECUTE USING,​ and 
> dollar quoting.
> 
> 
> So, I copy-pasted your example and placed it into a DO block
> 
> The character before "myschema" came out as a back-tick instead of a 
> single-quote.
> 
> array_to_string has the signature (text[], text) but you've defined foo as 
> being just text.  Changing that results in a query that executes - though 
> given my system doesn't have conforming data I get no results.
> 
> David J.

[GENERAL] variable value in array_to_string

[Armand Pirvu (home)]

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, 
information_schema.key_column_usage d '
||
'where c.constraint_name=d.constraint_name and c.table_name=d.table_name and 
c.table_schema='
||
quote_literal(‘myschema')
||
' and c.table_schema=d.table_schema and c.constraint_name like '
||
quote_literal('%_pkey%')
||
') ' into foo;
raise notice '%', foo;
foo1 :=ARRAY_TO_STRING(foo, ', ');
raise notice '%', foo1;
END;
$$ LANGUAGE plpgsql ;


The idea is that I want to slice and dice and not run the query x amount of 
times



Thank you

Armand

-- 
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] functions - triggers cross schemas

[Armand Pirvu (home)]

Ok that was my oversight in understanding that the path does not change upon 
entering a function. I assumed that .it does by simply picking it from the 
schema2.test1 

Thanks a bunch

Armand

On Oct 13, 2016, at 5:31 PM, David G. Johnston  
wrote:

> On Thu, Oct 13, 2016 at 3:18 PM, Armand Pirvu (home)  
> 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) values ('foo1','foo2')
> I get an entry in schema1.test1_hist and not in schema2.test1_hist
> 
> ​See:
> 
> https://www.postgresql.org/docs/current/static/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER
> 
> ​TG_TABLE_SCHEMA
> 
> ​Any object name not schema qualified will use search_path for resolution.  
> Search path doesn't change upon entering a function unless the function 
> defines its own - and your's does not.
> 
> David J.
> 

[GENERAL] functions - triggers cross schemas

[Armand Pirvu (home)]

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  inherits the schema of its table. But does that 
mean that 
a - the trigger will be created in the same schema 
or
b - it will use the current schema and pass it to the function ? It sure seems 
so , even if I drop the function and trigger from schema1 I still have the same 
behavior. Even I do 
"CREATE TRIGGER test1_audit_trig_prev
after insert or update or delete on schema2.test1
  FOR EACH ROW EXECUTE PROCEDURE tbl_audit_func_prev();
"
I still get the same behavior

The only way I could make it to add in each function
SET search_path = schema1 and SET search_path = schema2

I was expecting since I am doing an insert into schema2.test1 from schema1 to 
see executed the function from schema2 which would then put data in 
schema2.test1_hist

I did play with security definer/invoker but no luck


Any other suggestion other than hardcoding in set search_path within the 
function code, and aside plproxy ?


Thanks
Armand

-- Code
In both schemas I have


create table test1 (col1 bigserial, col2 char(10), col3 char(10), primary key 
(col1, col2));

create table test1_hist (
stmt_seq bigint not null , 
stmt_type char(6) not null,
stmt_subtype char(1) not null,
stmt_date timestamp not null,
like test1);

In schema1
create sequence seq_audit_func;

CREATE OR REPLACE FUNCTION tbl_audit_func () RETURNS TRIGGER AS $$
DECLARE
  temp_new RECORD;
  temp_old RECORD;
  tgopdet1 char(1);
  tgopdet2 char(1);
  vdate timestamp;
begin
  IF (TG_OP = 'INSERT') THEN 
  temp_new := NEW;
  tgopdet2='I';
  vdate = now();
  EXECUTE 'INSERT INTO  ' || TG_TABLE_NAME::regclass || '_hist  SELECT 
$1,$2,$3,$4, $5.* ' using  nextval('seq_audit_func'), TG_OP, tgopdet2, vdate, 
temp_new;
  END IF;
  IF (TG_OP = 'UPDATE') THEN
  temp_old := OLD;
  temp_new := NEW;
  tgopdet1='D';
  tgopdet2='I';
  vdate = now();
  EXECUTE 'INSERT INTO  ' || TG_TABLE_NAME::regclass || '_hist  SELECT 
$1,$2,$3,$4, $5.* ' using  nextval('seq_audit_func'), TG_OP, tgopdet1, vdate, 
temp_old;
  EXECUTE 'INSERT INTO  ' || TG_TABLE_NAME::regclass || '_hist  SELECT 
$1,$2,$3,$4, $5.* ' using  nextval('seq_audit_func'), TG_OP, tgopdet2, vdate, 
temp_new;
  END IF;
  IF (TG_OP = 'DELETE') THEN
  temp_old := OLD;
  tgopdet1='D';
  vdate = now();
  EXECUTE 'INSERT INTO  ' || TG_TABLE_NAME::regclass || '_hist  SELECT 
$1,$2,$3,$4, $5.* ' using  nextval('seq_audit_func'), TG_OP, tgopdet1, vdate, 
temp_old;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql  ;


drop trigger test1_audit_trig on test1;
CREATE TRIGGER test1_audit_trig
after insert or update or delete on test1
  FOR EACH ROW EXECUTE PROCEDURE tbl_audit_func();


In schema2
create sequence seq_audit_func_prev;

CREATE OR REPLACE FUNCTION tbl_audit_func_prev () RETURNS TRIGGER AS $$
DECLARE
  temp_new RECORD;
  temp_old RECORD;
  tgopdet1 char(1);
  tgopdet2 char(1);
  vdate timestamp;
begin
  IF (TG_OP = 'INSERT') THEN 
  temp_new := NEW;
  tgopdet2='I';
  vdate = now();
  EXECUTE 'INSERT INTO  ' || TG_TABLE_NAME::regclass || '_hist  SELECT 
$1,$2,$3,$4, $5.* ' using  nextval('seq_audit_func_prev'), TG_OP, tgopdet2, 
vdate, temp_new;
  END IF;
  IF (TG_OP = 'UPDATE') THEN
  temp_old := OLD;
  temp_new := NEW;
  tgopdet1='D';
  tgopdet2='I';
  vdate = now();
  EXECUTE 'INSERT INTO  ' || TG_TABLE_NAME::regclass || '_hist  SELECT 
$1,$2,$3,$4, $5.* ' using  nextval('seq_audit_func_prev'), TG_OP, tgopdet1, 
vdate, temp_old;
  EXECUTE 'INSERT INTO  ' || TG_TABLE_NAME::regclass || '_hist  SELECT 
$1,$2,$3,$4, $5.* ' using  nextval('seq_audit_func_prev'), TG_OP, tgopdet2, 
vdate, temp_new;
  END IF;
  IF (TG_OP = 'DELETE') THEN
  temp_old := OLD;
  tgopdet1='D';
  vdate = now();
  EXECUTE 'INSERT INTO  ' || TG_TABLE_NAME::regclass || '_hist  SELECT 
$1,$2,$3,$4, $5.* ' using  nextval('seq_audit_func_prev'), TG_OP, tgopdet1, 
vdate, temp_old;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql ;



drop trigger test1_audit_trig_prev on test1;
CREATE TRIGGER test1_audit_trig_prev
after insert or update or delete on test1
  FOR EACH ROW EXECUTE PROCEDURE tbl_audit_func_prev();







-- 
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] executing os commands from a function

[Armand Pirvu (home)]

Thanks for replies.
Good deal of info

While plsh seems to be an easier approach , I am also inclined in a an ESQL/C 
type app to listen to an event

@Igor Neyman
It is just maybe I have missed a sample of a C app  in that area, similar like 
ESQLC

Cheers
Armand




On Sep 30, 2016, at 10:45 AM, David G. Johnston  
wrote:

> On Thu, Sep 29, 2016 at 2:41 PM, Armand Pirvu (home)  
> wrote:
> I used plsh extension but I had to use two functions and a trigger, see code 
> below
> 
> ​I don't see any way to not use a trigger given your requirement.  And as 
> written I don't see that you need the second function​ - just write the 
> trigger function in plsh.  Admittedly its probably better to have two 
> functions from an architecture standpoint.
> 
> Listen/Notify doesn't seem to make sense as you want the server to ensure 
> that the file exists - not some other client that may or may not be connected.
> 
> ​David J.

[GENERAL] executing os commands from a function

[Armand Pirvu (home)]

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 functions and a trigger, see code 
below

CREATE or REPLACE FUNCTION func2 (var1 text) RETURNS text AS '
#!/bin/bash
 touch /home/postgres/$1;
' LANGUAGE plsh;
commit;

CREATE FUNCTION func1() RETURNS trigger AS '
BEGIN
perform   func2(NEW.col1);
RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER trigf1 BEFORE INSERT on test
FOR EACH ROW EXECUTE PROCEDURE func1();

testdb=# insert into test3 values (777);
INSERT 0 1
testdb=# select * from test3;
 col1 
--
  777

[postgres@edb1 ~]$ ls -ltr
-rw--- 1 postgres postgres 0 Sep 29 16:30 777


It works but can I be simpler ? Any other alternatives ? In Ingres for example 
I can use dbevent and an esqlc app which listens 


Thank you 

Armand



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] jsonb search

[Armand Pirvu (home)]

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 installed the extension

1   -   Exact matching without knowing the hierarchy, just the key and 
element, I built a set like

col1 |   col2   
--+--
   1 | {"Home Email": {"EmailAddress": "1...@yahoo.com"}}
   2 | {"Home Email": {"EmailAddress": "2...@yahoo.com"}}
   3 | {"Home Email": {"EmailAddress": "3...@yahoo.com"}}


JSQuqery is super 

SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "1...@yahoo.com"';

Now I can do a performance boost using

CREATE INDEX idx1 ON test1 USING GIN (col2 jsonb_value_path_ops);

I see this yield

from 

testdb=# explain analyze^JSELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = 
"1...@yahoo.com"';
Seq Scan on test1  (cost=0.00..12423.00 rows=500 width=68) (actual 
time=0.016..160.777 rows=1 loops=1)
  Filter: (col2 @@ '*."EmailAddress" = "1...@yahoo.com"'::jsquery)
  Rows Removed by Filter: 49
Planning time: 0.042 ms
Execution time: 160.799 ms
(5 rows)


to

testdb-# SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "1...@yahoo.com"';
Bitmap Heap Scan on test1  (cost=31.88..1559.32 rows=500 width=68) (actual 
time=0.018..0.019 rows=1 loops=1)
  Recheck Cond: (col2 @@ '*."EmailAddress" = "1...@yahoo.com"'::jsquery)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on idx1  (cost=0.00..31.75 rows=500 width=0) (actual 
time=0.011..0.011 rows=1 loops=1)
Index Cond: (col2 @@ '*."EmailAddress" = "1...@yahoo.com"'::jsquery)
Planning time: 0.039 ms
Execution time: 0.038 ms
(7 rows)

A whooping 4000 times improvement




But I also noticed a vodka index 


testdb=# CREATE INDEX idx2 ON
testdb-# test1 USING vodka (col2);
ERROR:  access method "vodka" does not exist

What am I missing ?

2   -   Is there anyway I can accomplish a pattern and/or case 
insensitive search using JSQuery similar to 


select * from test2 where upper((col2 -> 'Home Email') ->> 'EmailAddress') 
ilike '%3%YAH%';

select * from test2 where (col2 -> 'Home Email') ->> 'EmailAddress' like 
'%3%yah%';


If so what indexing strategy can be used to have similar gains as above ? 


Many thanks for any help


Armand