Re: Fwd: Disable autocommit inside dbeaver

[Rob Sargent]

On 12/6/23 20:45, arun chirappurath wrote:




Hi All,

Is there a way we can disable autocommit option inside query writing 
area? Not by choosing auto commit from drop down menu.


Thanks,
Arun

maybe "begin; ; commit;"

Fwd: Disable autocommit inside dbeaver

[arun chirappurath]

Hi All,

Is there a way we can disable autocommit option inside query writing area?
Not by choosing auto commit from drop down menu.

Thanks,
Arun

Re: Trainning and Certification

[Adrian Klaver]

On 12/5/23 09:19, roger popa wrote:

Hello,

You can tell if exists an oficial Postgresql Certification issued by 
postgresql.org  ?

Or others (like PearsonVue)?
Training and Certification path, source doc ?


See this thread from June 2023:

https://www.postgresql.org/message-id/CAJKUy5jy5fzrWZ9MuYkNWC73APX0F6rV-OrCXPuBQE7mOczxWQ%40mail.gmail.com

This covers what is out there and why it is highly unlikely there will 
be a Postgres community(core) certification.




Regards


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

Re: Trainning and Certification

[Daniel Gustafsson]

> On 6 Dec 2023, at 16:30, roger popa  wrote:
> 
> Exist a top of this issue, for this request?

I'm not sure I understand what you are asking here.  If you are asking for a
postgres feature request or some form of prioritized roadmap then the answer is
no. There is no such thing in the postgres project.

--
Daniel Gustafsson

Re: Trainning and Certification

[Hans Schou]

On Wed, Dec 6, 2023 at 3:39 PM roger popa  wrote:

> You can tell if exists an oficial Postgresql Certification issued by
> postgresql.org ?
>

No.


> Or others (like PearsonVue)?
>

EnterpriseDB has certifications. I think you can get some of them for free.
https://www.enterprisedb.com/accounts/register/biganimal


-- 
핳햆햓햘 핾했햍햔햚
☏ ➁➁ ➅➃ ➇⓪ ➁⓪

Re: Max effective number of CPUs that Postgresql can handle?

[Christoph Moench-Tegeder]

## Ron Johnson (ronljohnso...@gmail.com):

> Like the Subject says, is there any point of diminishing returns at which
> the Postmaster gets "too busy" to manage all the threads?

It is possible to use 3-digit cores (i.e. 128, maybe more) quite
efficiently. The rest of the system has to fit the amount of compute,
else you end up with an unbalanced system. Also, not every workload
can benefit from this kind of machine. (then: cost of redundancy,
etc.).
I believe there was a benchmark (actual numbers, not just marketing)
done by PostgresPro on a largish Power machine, but I can't find
that right now.

Regards,
Christoph

-- 
Spare Space.

Re: Delete Account

[Adrian Klaver]

On 12/6/23 08:16, Estevan Rech wrote:

delete account,


That is still unclear. If you mean unsubscribing from list then you need 
to follow the instructions here:


https://lists.postgresql.org/unsubscribe/

If by removing data you mean your emails then read:

https://www.postgresql.org/about/policies/archives/

The short version is the emails will not be removed.

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

Re: PostgreSQL 12 error unrecognized configuration parameter zbx_tmp.wal_json_res

[Adrian Klaver]

On 12/6/23 04:21, Arne Henrik Segtnan wrote:


Hi all,

We are currently running Zabbix 5.0 with PostgreSQL 12, and history and 
trend data partitioning.

History and trend data housekeeping has been disabled in Zabbix.

In the PostgreSQL logs, we get the following error:

2023-12-06 09:12:47 CET [3509536-5] zabbix@postgres STATEMENT: select 
current_setting('zbx_tmp.wal_json_res');
2023-12-06 09:17:47 CET [3516312-1] zabbix@postgres ERROR: permission 
denied for function pg_ls_waldir
2023-12-06 09:17:47 CET [3516312-2] zabbix@postgres CONTEXT: SQL 
statement "SELECT row_to_json(T) FROM (

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(),'0/') AS WRITE,
count(*) FROM pg_ls_waldir() AS COUNT
) T"
PL/pgSQL function inline_code_block line 10 at SQL statement
2023-12-06 09:17:47 CET [3516312-3] zabbix@postgres STATEMENT: DO 
LANGUAGE plpgsql $$

DECLARE
ver integer;
res text := '{"write":0,"count":0}';
BEGIN
SELECT current_setting('server_version_num') INTO ver;

IF (SELECT NOT pg_is_in_recovery()) THEN
IF (ver >= 10) THEN
SELECT row_to_json(T) INTO res FROM (
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(),'0/') AS WRITE,
count(*) FROM pg_ls_waldir() AS COUNT
) T;

ELSE
SELECT row_to_json(T) INTO res FROM (
SELECT pg_xlog_location_diff(pg_current_xlog_location(),' 0/') 
AS WRITE,

count(*) FROM pg_ls_dir('pg_xlog') AS COUNT
) T;
END IF;
END IF;

perform set_config('zbx_tmp.wal_json_res', res, false);
END $$;
2023-12-06 09:17:47 CET [3516312-4] zabbix@postgres ERROR: unrecognized 
configuration parameter "zbx_tmp.wal_json_res"
2023-12-06 09:17:47 CET [3516312-5] zabbix@postgres STATEMENT: select 
current_setting('zbx_tmp.wal_json_res');



This seems to be related to permissions-problem and missing/unknown 
configuration parameter.

We found the following article describing how to set correct permission:

https://github.com/bitnami/charts/issues/20247 



Is this a known issue? Should we just perform the step according to 
procedure in above link to set permission, or will security be degraded 
as commented in article?



Or turn the DO into a full function created by a user with the necessary 
privileges and use SECURITY DEFINER in the function definition to confer 
those privileges to unprivileged user for the duration of the function 
execution per:


https://www.postgresql.org/docs/12/sql-createfunction.html



Please advise.

Best regards,
Arne H.




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

Re: Delete Account

[Estevan Rech]

delete account,

Re: Delete Account

[Adrian Klaver]

On 12/6/23 02:45, Estevan Rech wrote:

Hi,

I want to delete my account (softr...@gmail.com 
) and all data from 
https://www.postgresql.org/ 


Are you referring to this account:

https://www.postgresql.org/account/login/?next=/account/

or to your subscription to this mailing list?

What data are you referring to?



Thanks


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

Re: ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function

[Tom Lane]

=?UTF-8?B?Um9tYW4gxaBpbmRlbMOhxZk=?=  writes:
> - ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function in all
> PGSQL versions 14+, is it desired behavior?

It's intentional to get some other benefits, if that's what you mean.
See

https://git.postgresql.org/gitweb/?p=postgresql.git=commitdiff=ce0fdbfe972

regards, tom lane

Re: Trainning and Certification

[Daniel Gustafsson]

There is no certification offered by the PostgreSQL Project, or PostgreSQL
Global Development Group.

./daniel

> On 5 Dec 2023, at 18:19, roger popa  wrote:
> 
> Hello,
> 
> You can tell if exists an oficial Postgresql Certification issued by 
> postgresql.org ?
> Or others (like PearsonVue)?
> Training and Certification path, source doc ?
> 
> Regards

--
Daniel Gustafsson

Re: GIN INdex is not used with && operator for a text array index

[Tom Lane]

balasubramanian c r  writes:
> when Operator '@>' is used the index is used and the execution time is 60ms.

Yeah ... note that it's predicted to return just one row, and that
guess is correct:

>  Bitmap Heap Scan on public.address18  (cost=261.25..262.52 rows=1 width=4)
> (actual time=58.992..58.994 rows=1 loops=1)

> when Operator '&&' is used the index is used and the execution time is 60ms.
> It is performing sequential scan which is not expected.

I do not see why you expect that.  This case retrieves many more rows
than the other one, both in the planner's estimate and in reality:

>  Seq Scan on public.address18  (cost=0.00..77215.11 rows=247741 width=4)
> (actual time=0.063..1880.467 rows=247741 loops=1)

An indexscan is not necessarily better than a seqscan for such cases...

> When i disable the sequential scan the execution time is increased
> significantly.

... so, indeed, the planner was correct to use a seqscan.

> Not expecting this behavior currently.

Your expectation is faulty.

regards, tom lane

Max effective number of CPUs that Postgresql can handle?

[Ron Johnson]

PG 9.6.24 on an ESX VM with nproc=32 and RAM=132GB (We'll be on 14.latest
hopefully by February.)

Like the Subject says, is there any point of diminishing returns at which
the Postmaster gets "too busy" to manage all the threads?

(I'm not in control of the stack's architecture, so "change
_insert_topic_here_" -- including connection pooling -- is not in the
cards.)

Thanks

Re: Store PDF files in PostgreDB

[Ron Johnson]

On Wed, Dec 6, 2023 at 9:39 AM Priyadharshini Vellaisamy <
priya.cs...@gmail.com> wrote:

> Hi Team,
>
> Please let m know can we store PDF files in PostgreDB ?
>

You can store *anything* up to 1GB in Postgresql using data type bytea.


> If so, can we retrieve it effectively?
>

Effectively?

(We've been storing images in PG for 11 years.)

Store PDF files in PostgreDB

[Priyadharshini Vellaisamy]

Hi Team,

Please let m know can we store PDF files in PostgreDB ?

If so, can we retrieve it effectively?

Thanks,
Priya

Trainning and Certification

[roger popa]

Hello,

You can tell if exists an oficial Postgresql Certification issued by
postgresql.org ?
Or others (like PearsonVue)?
Training and Certification path, source doc ?

Regards

GIN INdex is not used with && operator for a text array index

[balasubramanian c r]

HI Team

Sorry for the spam.

We have Postgres DB where the list of addresses are stored and for a given
complete address
trigram of addresses are stored in a column which is a text array.

after looking at the list of operators that are available for gin index I
decided to use array_ops operator.
select amop.amopopr::regoperator, amop.amopstrategy, opc.opcname from
pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop where opf.oid =
opc.opcfamily and am.oid = opf.opfmethod and amop.amopfamily =
opc.opcfamily and am.amname = 'gin' and amop.amoplefttype = opc.opcintype;
 amopopr | amopstrategy |opcname
-+--+
 &&(anyarray,anyarray)   |1 | array_ops
 @>(anyarray,anyarray)   |2 | array_ops
 <@(anyarray,anyarray)   |3 | array_ops
 =(anyarray,anyarray)|4 | array_ops

 CREATE INDEX pentgram_idx ON address18 USING GIN(pentgram array_ops);

when Operator '@>' is used the index is used and the execution time is 60ms.

db=# EXPLAIN (costs, buffers, verbose, analyze) select similarity('jattan
kangra riyali 144 hp 176058', complete_address) from address18 where
pentgram @> show_trgm('jattan kangra riyali 144 hp 176058');

 Bitmap Heap Scan on public.address18  (cost=261.25..262.52 rows=1 width=4)
(actual time=58.992..58.994 rows=1 loops=1)
   Output: similarity('*'::text, complete_address)
   Recheck Cond: (address18.pentgram @> '{**}'::text[])
   Heap Blocks: exact=1
   Buffers: shared hit=1483
   ->  Bitmap Index Scan on pentgram_idx  (cost=0.00..261.25 rows=1
width=0) (actual time=58.960..58.960 rows=1 loops=1)
 Index Cond: (address18.pentgram @> '{***}'::text[])
 Buffers: shared hit=1482
 Query Identifier: -126591413296272164
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.679 ms
 Execution Time: 60.373 ms
(13 rows)

when Operator '&&' is used the index is used and the execution time is 60ms.
It is performing sequential scan which is not expected.

db=# EXPLAIN (costs, buffers, verbose, analyze) select similarity('jattan
kangra riyali 144 hp 176058', complete_address) from address18 where
pentgram && show_trgm('jattan kangra riyali 144 hp 176058');

 Seq Scan on public.address18  (cost=0.00..77215.11 rows=247741 width=4)
(actual time=0.063..1880.467 rows=247741 loops=1)
   Output: similarity(''::text, complete_address)
   Filter: (address18.pentgram && '{"}'::text[])
   Buffers: shared hit=3592 read=69907
   I/O Timings: shared/local read=267.274
 Query Identifier: 2367846469053211383
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.386 ms
 Execution Time: 1886.125 ms
(10 rows)

When i disable the sequential scan the execution time is increased
significantly.

db=# EXPLAIN (costs, buffers, verbose, analyze) select similarity('jattan
kangra riyali 144 hp 176058', complete_address) from address18 where
pentgram && show_trgm('jattan kangra riyali 144 hp 176058');


   QUERY PLAN
-
 Bitmap Heap Scan on public.address18  (cost=2266.24..79481.36 rows=247741
width=4) (actual time=186.402..3285.090 rows=247741 loops=1)
   Output: similarity(''::text, complete_address)
   Recheck Cond: (address18.pentgram && '{***}'::text[])
   Heap Blocks: exact=39632
   Buffers: shared hit=1361 read=39155 written=6260
   I/O Timings: shared/local read=981.127 write=544.491
   ->  Bitmap Index Scan on pentgram_idx  (cost=0.00..2204.31 rows=247741
width=0) (actual time=182.462..182.463 rows=247741 loops=1)
 Index Cond: (address18.pentgram && '{*}'::text[])
 Buffers: shared hit=884
 Query Identifier: 2367846469053211383
 Planning:
   Buffers: shared hit=1
 Planning Time: 6.707 ms
 Execution Time: 3292.339 ms
(14 rows)

Not expecting this behavior currently.

Few parameters in my configuration
postgresql version is 15
OS is RHEL 8.8
effective_cache_size | 131072
huge_pages   | off
maintenance_work_mem | 327680
max_parallel_maintenance_workers | 1

 max_parallel_workers | 2
 max_parallel_workers_per_gather  | 1
  shared_buffers
| 32768

Thanks
C.R.Bala

RE: ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function

[Patrick FICHE]

Hi Roman,

We had the same problem and we had to create a SERVER (loopback) and use dblink 
in order to get in done within a Stored Procedure.
I don’t know if this solution will work in your case but it could be something 
like this….

CREATE SERVER loopback_dblink
  FOREIGN DATA WRAPPER dblink_fdw
  OPTIONS (hostaddr '127.0.0.1', dbname 'db2');

GRANT USAGE ON FOREIGN SERVER loopback_dblink TO usr_db_deploy;

\c db2;
CREATE USER MAPPING FOR usr_db_deploy
SERVER loopback_dblink
OPTIONS (user 'usr_db_deploy', password '');

Then, in the procedure, you can try the following
  PERFORM dblink_connect( 'myconn', 'loopback_dblink' );
  PERFORM dblink_exec( 'myconn', 'ALTER SUBSCRIPTION ' || sSubName || ' 
REFRESH PUBLICATION' );
  PERFORM dblink_disconnect('myconn');

Regards,
Patrick

From: Roman Šindelář 
Sent: Wednesday, December 6, 2023 1:04 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function

Hello,
we prepared a test case to demonstrate our case.

TASK
Update replicated db schema (add new table) without superuser privileges.
PROBLEM
Our steps work in PostgreSQL ver 12 but we get the following error in 15
---
ERROR:  ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function
CONTEXT:  SQL statement "alter subscription test_sub REFRESH PUBLICATION"
PL/pgSQL function test.refresh_subscription(character varying) line 4 at EXECUTE
---

QUESTIONS
- ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function in all 
PGSQL versions 14+, is it desired behavior?
- Is there any other possibility or a recommendation to solve our case/task?

TEST CASE
Creates a db environment with two databases, two db users and one replicated 
table. Then we try to add a new table to the publication and without superuser 
privileges refresh replicated tables.


--
-- ALTER SUBSCRIPTION REFRESH TEST CASE
--
-- DATABASES
-- db1 - source db
-- db2 - destination db (replica)
--
-- USERS
-- postgres - superuser
-- usr_db_repl - user for replication
-- usr_db_deploy - deploy user
--
-- PG_HBA.CONF
-- hostreplication usr_db_repl  localhost trust
-- hostdb1, db2usr_db_repl  localhost trust
-- hostdb1, db2usr_db_deploylocalhost trust
-- local   db1, db2usr_db_deploy  trust
--

--
-- PREPARATION TEST ENVIRONMENT
--

-- create users
create user usr_db_repl replication;
create user usr_db_deploy;

-- create databases
create database db1;
grant create on database db1 to usr_db_deploy;
create database db2;
grant create on database db2 to usr_db_deploy;

-- create source table
\c db1 usr_db_deploy
create schema test;
create table test.tab1 (id int primary key, num int);
grant usage on schema test to usr_db_repl;
grant select on table test.tab1 to usr_db_repl;
insert into test.tab1 values (1, 10);
select * from test.tab1;

-- create publication on source side
create publication test_pub for table test.tab1;
select * from pg_publication_tables;

-- create replication slot on source side
\c db1 postgres
select pg_create_logical_replication_slot('test_sub', 'pgoutput');
select * from pg_replication_slots;

-- create table on destination side
\c db2 usr_db_deploy
create schema test;
grant usage on schema test to usr_db_repl;
create table test.tab1 (id int primary key, num int);

-- create subscription on destination side using pre-prepared replication slot
\c db2 postgres
create subscription test_sub connection 'host=localhost port=5432 dbname=db1 
user=usr_db_repl' publication test_pub with (slot_name=test_sub, 
create_slot=false);
select * from pg_subscription;

-- check replicated table
\c db2 usr_db_deploy
select * from test.tab1;

-- create functions with security definer on destination side
\c db2 postgres

create procedure test.disable_subscription(in ssubname character varying)
language plpgsql
security definer
as $procedure$
declare
begin
  execute 'alter subscription ' || sSubName || ' disable';
  raise notice 'Subscription % refreshed', sSubName;
end $procedure$
;

create procedure test.enable_subscription(in ssubname character varying)
language plpgsql
security definer
as $procedure$
declare
begin
  execute 'alter subscription ' || sSubName || ' enable';
  raise notice 'Subscription % refreshed', sSubName;
end $procedure$
;

create procedure test.refresh_subscription(in ssubname character varying)
language plpgsql
security definer
as $procedure$
declare
begin
  execute 'alter subscription ' || sSubName || ' REFRESH PUBLICATION';
  raise notice 'Subscription % refreshed', sSubName;
end $procedure$
;

--
-- TEST CASE
-- Note: All steps is run under usr_db_deploy (non-superuser)
-- Task: Add a new table into publisher on source side and replicate the table 
to destination side
--

-- 1. disable subscrition [DESTINATION DATABASE]
\c db2 usr_db_deploy
call test.disable_subscription('test_sub');

-- 2. change: create new table [SOURCE DATABASE]
\c db1 usr_db_deploy
create 

PostgreSQL 12 error unrecognized configuration parameter zbx_tmp.wal_json_res

[Arne Henrik Segtnan]

Hi all,

We are currently running Zabbix 5.0 with PostgreSQL 12, and history and
trend data partitioning.
History and trend data housekeeping has been disabled in Zabbix.

In the PostgreSQL logs, we get the following error:

2023-12-06 09:12:47 CET [3509536-5] zabbix@postgres STATEMENT: select
current_setting('zbx_tmp.wal_json_res');
2023-12-06 09:17:47 CET [3516312-1] zabbix@postgres ERROR: permission
denied for function pg_ls_waldir
2023-12-06 09:17:47 CET [3516312-2] zabbix@postgres CONTEXT: SQL statement
"SELECT row_to_json(T) FROM (
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(),'0/') AS WRITE,
count(*) FROM pg_ls_waldir() AS COUNT
) T"
PL/pgSQL function inline_code_block line 10 at SQL statement
2023-12-06 09:17:47 CET [3516312-3] zabbix@postgres STATEMENT: DO LANGUAGE
plpgsql $$
DECLARE
ver integer;
res text := '{"write":0,"count":0}';
BEGIN
SELECT current_setting('server_version_num') INTO ver;

IF (SELECT NOT pg_is_in_recovery()) THEN
IF (ver >= 10) THEN
SELECT row_to_json(T) INTO res FROM (
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(),'0/') AS WRITE,
count(*) FROM pg_ls_waldir() AS COUNT
) T;

ELSE
SELECT row_to_json(T) INTO res FROM (
SELECT pg_xlog_location_diff(pg_current_xlog_location(),' 0/') AS
WRITE,
count(*) FROM pg_ls_dir('pg_xlog') AS COUNT
) T;
END IF;
END IF;

perform set_config('zbx_tmp.wal_json_res', res, false);
END $$;
2023-12-06 09:17:47 CET [3516312-4] zabbix@postgres ERROR: unrecognized
configuration parameter "zbx_tmp.wal_json_res"
2023-12-06 09:17:47 CET [3516312-5] zabbix@postgres STATEMENT: select
current_setting('zbx_tmp.wal_json_res');


This seems to be related to permissions-problem and missing/unknown
configuration parameter.
We found the following article describing how to set correct permission:

https://github.com/bitnami/charts/issues/20247

Is this a known issue? Should we just perform the step according to
procedure in above link to set permission, or will security be degraded as
commented in article?

Please advise.

Best regards,
Arne H.

Re: ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function

[Roman Šindelář]

Hello,
we prepared a test case to demonstrate our case.

TASK
Update replicated db schema (add new table) without superuser privileges.

PROBLEM
Our steps work in PostgreSQL ver 12 but we get the following error in 15
---
ERROR:  ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function
CONTEXT:  SQL statement "alter subscription test_sub REFRESH PUBLICATION"
PL/pgSQL function test.refresh_subscription(character varying) line 4 at
EXECUTE
---

QUESTIONS
- ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function in all
PGSQL versions 14+, is it desired behavior?
- Is there any other possibility or a recommendation to solve our case/task?

TEST CASE
Creates a db environment with two databases, two db users and one
replicated table. Then we try to add a new table to the publication and
without superuser privileges refresh replicated tables.

--
-- ALTER SUBSCRIPTION REFRESH TEST CASE
--
-- DATABASES
-- db1 - source db
-- db2 - destination db (replica)
--
-- USERS
-- postgres - superuser
-- usr_db_repl - user for replication
-- usr_db_deploy - deploy user
--
-- PG_HBA.CONF
-- hostreplication usr_db_repl  localhost trust
-- hostdb1, db2usr_db_repl  localhost trust
-- hostdb1, db2usr_db_deploylocalhost trust
-- local   db1, db2usr_db_deploy  trust
--

--
-- PREPARATION TEST ENVIRONMENT
--

-- create users
create user usr_db_repl replication;
create user usr_db_deploy;

-- create databases
create database db1;
grant create on database db1 to usr_db_deploy;
create database db2;
grant create on database db2 to usr_db_deploy;

-- create source table
\c db1 usr_db_deploy
create schema test;
create table test.tab1 (id int primary key, num int);
grant usage on schema test to usr_db_repl;
grant select on table test.tab1 to usr_db_repl;
insert into test.tab1 values (1, 10);
select * from test.tab1;

-- create publication on source side
create publication test_pub for table test.tab1;
select * from pg_publication_tables;

-- create replication slot on source side
\c db1 postgres
select pg_create_logical_replication_slot('test_sub', 'pgoutput');
select * from pg_replication_slots;

-- create table on destination side
\c db2 usr_db_deploy
create schema test;
grant usage on schema test to usr_db_repl;
create table test.tab1 (id int primary key, num int);

-- create subscription on destination side using pre-prepared replication slot
\c db2 postgres
create subscription test_sub connection 'host=localhost port=5432
dbname=db1 user=usr_db_repl' publication test_pub with
(slot_name=test_sub, create_slot=false);
select * from pg_subscription;

-- check replicated table
\c db2 usr_db_deploy
select * from test.tab1;

-- create functions with security definer on destination side
\c db2 postgres

create procedure test.disable_subscription(in ssubname character varying)
language plpgsql
security definer
as $procedure$
declare
begin
  execute 'alter subscription ' || sSubName || ' disable';
  raise notice 'Subscription % refreshed', sSubName;
end $procedure$
;

create procedure test.enable_subscription(in ssubname character varying)
language plpgsql
security definer
as $procedure$
declare
begin
  execute 'alter subscription ' || sSubName || ' enable';
  raise notice 'Subscription % refreshed', sSubName;
end $procedure$
;

create procedure test.refresh_subscription(in ssubname character varying)
language plpgsql
security definer
as $procedure$
declare
begin
  execute 'alter subscription ' || sSubName || ' REFRESH PUBLICATION';
  raise notice 'Subscription % refreshed', sSubName;
end $procedure$
;

--
-- TEST CASE
-- Note: All steps is run under usr_db_deploy (non-superuser)
-- Task: Add a new table into publisher on source side and replicate
the table to destination side
--

-- 1. disable subscrition [DESTINATION DATABASE]
\c db2 usr_db_deploy
call test.disable_subscription('test_sub');

-- 2. change: create new table [SOURCE DATABASE]
\c db1 usr_db_deploy
create table test.tab2 (id int primary key, num int);
grant select on table test.tab2 to usr_db_repl;
insert into test.tab2 values (2, 20);
select * from test.tab2;

-- 3. add table into publication [SOURCE DATABASE]
\c db1 usr_db_deploy
alter publication test_pub add table test.tab2;
select * from pg_publication_tables;

-- 4. create new table on destination side [DESTINATION DATABASE]
\c db2 usr_db_deploy
create table test.tab2 (id int primary key, num int);

-- 5. enable subscription [DESTINATION DATABASE]
\c db2 usr_db_deploy
call test.enable_subscription('test_sub');

-- 6. check new table [DESTINATION DATABASE]
-- Note: no rows, we need REFRESH PUBLICATION - this will start
replication of tables that were added to the subscribed-to
publications
\c db2 usr_db_deploy
select * from test.tab2;

-- 7. refresh subscription [DESTINATION DATABASE]
-- Note: must be run as owner of subscription (superuser) but we need
run as non-superuser (usr_db_deploy)
\c db2 usr_db_deploy
call 

Delete Account

[Estevan Rech]

Hi,

I want to delete my account (softr...@gmail.com) and all data from
https://www.postgresql.org/

Thanks