date:20240909




On 9/8/24 23:46, Adrian Klaver wrote:

On 9/8/24 13:04, Achilleas Mantzios wrote:

Hi

for remote DDL execution (such as CREATE TABLE) is dblink my only 
option?


You will need to define in what context you are considering options.

For instance you can do remote DDL operations by passing a command or 
script via psql.


Thank you Adrian,

I was thinking of wrapping the DDL around a FUNCTION that will handle 
both the local and the (possibly various logical subscriptions), so that 
my programmers don't need to write extra scripts.


And the thing is that this creation via DDL is inside our design. 
Certain users create some backup tables of the public data in their own 
schema (via our app), then do some manipulations on the public data, 
then restore to the public or merge with the backups. When done, those 
backup tables are dropped. So the DDL is inside the app. And the 
question was if dblink is my only option, in the sense of doing this in 
a somewhat elegant manner. (and not resort to scripts, etc)






thanks

ssh to DB server and su normal users very slow :

2024-09-09 Thread KK CHN

List,

I have configured pgbackrest for the DB server and Repo Server(created the
stanza info check all fine.  in these machines.


/var/spool/pgbackrest  shows   the .Okfor each WAL  and   the Repo
server receiving the archiving of WAL in the archive directory .


I didn't  schedule a pgbackrest  back  as of now  due to  an issue I am
facing as follows.

PROBLEM Statement:

I am facing a delay in ssh  root@dbserver_IP from my Desktop PC.  More than
a minute to login to the root console from any remote terminal, but this
issue was not there all these days.

 I have done two changes in the DB server :-

1.
pg_hba.conf entry

ie;  changed the entry   #local   all all  trust

To
#local   all allmd5


It already having replication entry as
local   replication all peer

2.
Added a .pgpass entry in theDB user's~/dir/ with the following

[root@db1 ~]# cat /var/lib/edb/.pgpass
*:*:*:enterprisedb:password


Is this causing login delays ?   Local connection asking
password(pg_hba.conf entry ) and   .pgpass contain the user and password
for connecting ?


3. Even if I able to login to the DB server from the Remote Repo server
after a minute or two, in side the DB server doing a #  su
enterprisedbtaking around 90 to 120 seconds to change the user as
enterprisedb user ??

Any hints much appreciated ..

Thanks in advance,
Krishane



*For more  details   I am pasting the top output ( vCPU 16 nos , RAM 16
GB)*

top - 10:11:43 up 5 days, 17:21,  5 users,  load average: 0.97, 1.38, 1.26
Tasks: 710 total,   1 running, 708 sleeping,   1 stopped,   0 zombie
%Cpu(s):  1.3 us,  0.6 sy,  0.0 ni, 97.1 id,  0.6 wa,  0.1 hi,  0.3 si,
 0.0 st
MiB Mem :  15733.6 total,664.0 free,   6371.1 used,  13237.6 buff/cache
MiB Swap:   8060.0 total,   7985.1 free, 74.9 used.   9362.4 avail Mem

PID USER  PR  NIVIRTRESSHR S  %CPU  %MEM TIME+
COMMAND
3547252 enterpr+  20   0 4656880 262304 252032 S   8.3   1.6   0:01.97
edb-postgres
   2588 enterpr+  20   0 4622104  12704  10888 S   2.0   0.1 106:10.00
edb-postgres
3554955 enterpr+  20   0 4661692 632052 621364 S   2.0   3.9   0:00.99
edb-postgres
3555894 enterpr+  20   0 4633432 628388 621056 S   1.3   3.9   0:00.26
edb-postgres
3525520 enterpr+  20   0 465 96 543872 S   1.0   3.4   0:10.82
edb-postgres
3546456 enterpr+  20   0 4627288  40852  38016 S   1.0   0.3   0:00.30
edb-postgres
3554919 enterpr+  20   0 4655376 564024 557020 S   1.0   3.5   0:00.30
edb-postgres
3555796 enterpr+  20   0 4635024 565716 556840 S   1.0   3.5   0:00.22
edb-postgres
3556084 enterpr+  20   0 4653424  59156  51968 S   1.0   0.4   0:00.06
edb-postgres
3525597 enterpr+  20   0 4627444  44052  41088 S   0.7   0.3   0:00.47
edb-postgres
377 root   0 -20   0  0  0 I   0.3   0.0   2:43.11
kworker/5:1H-kblockd
2923344 enterpr+  20   0 4625236 225176 223104 S   0.3   1.4   1:23.93
edb-postgres
3525722 enterpr+  20   0 4627328  99220  96128 S   0.3   0.6   0:01.99
edb-postgres
3555151 root  20   0  226580   4864   3456 R   0.3   0.0   0:00.15 top
3555807 enterpr+  20   0 4627444 350228 347136 S   0.3   2.2   0:00.03
edb-postgres
3556023 enterpr+  20   0 4653636  60052  52608 S   0.3   0.4   0:00.15
edb-postgres
3556026 enterpr+  20   0 4653424  59796  52608 S   0.3   0.4   0:00.22
edb-postgres
3556074 enterpr+  20   0 4653448  59540  52224 S   0.3   0.4   0:00.11
edb-postgres
3556075 enterpr+  20   0 4653372  59412  52224 S   0.3   0.4   0:00.18
edb-postgres


and  # ps -ax   shows
[root@db1 ~]# ps -ax |grep "idle"

3511515 ?I  0:00 [kworker/5:0-inet_frag_wq]
3512991 ?Ss 0:00 postgres: enterprisedb postgres
10.21.134.205(56754) idle
3513003 ?Ss 0:00 postgres: enterprisedb er_db
10.21.13.205(56770) idle
3513005 ?Ss 0:00 postgres: enterprisedb tp_db
10.21.13.205(56772) idle
3513267 ?Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(39262) idle
3513476 ?Ss 0:00 postgres: enterprisedb er_db
10.21.13.205(56839) idle
3513704 ?Ss 0:00 postgres: enterprisedb mt_db
10.21.13.202(56608) idle
3513729 ?Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(44926) idle
3514113 ?Ss 0:00 postgres: enterprisedb mt_db
10.21.13.202(53743) idle
3514374 ?Ss 0:00 postgres: enterprisedb mt_db
10.21.13.202(58623) idle
3514397 pts/1T  0:00 top
3515012 ?Ss 0:00 postgres: enterprisedb mt_db
10.21.13.202(58686) idle
3515088 ?Ss 0:00 postgres: enterprisedb mgt_db
10.21.13.202(58586) idle
3515942 ?Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(64844) idle
3515987 ?Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(27190) idle
3516230 ?Ss 0:00 postgres: enterprisedb postgres
10.21.13.202(60354) idle
3516655 ?Ss 0:00 postgres: enterprisedb er_db
10.21.13.205(57348) i

Strange permission effect depending on DEFERRABILITY


Dear List

The below runs on PostgreSQL 16.4

We are trying to implement a certain operation based on a security 
definer function : mariner_update_availability_date


This is supposed to update a table : mariner , which has several other 
triggers :


mariner_build_natural_id_tg BEFORE INSERT OR UPDATE ON mariner FOR EACH 
ROW EXECUTE FUNCTION mariner_build_natural_id()
   mariner_force_integrity_tg AFTER INSERT OR UPDATE ON mariner FOR 
EACH ROW EXECUTE FUNCTION mariner_force_integrity()
   mariner_manage_past_tg BEFORE UPDATE ON mariner FOR EACH ROW EXECUTE 
FUNCTION mariner_manage_past()
   mariner_xadmin_prod_tmp_map_ins__crew_tg AFTER INSERT ON mariner FOR 
EACH ROW EXECUTE FUNCTION xadmin_prod_tmp_map_ins__crew()
   mariner_zb_dbmirror_trig AFTER INSERT OR DELETE OR UPDATE ON mariner 
FOR EACH ROW EXECUTE FUNCTION dbmirror_recordchange()
   zzzmariner_dmq_tg AFTER INSERT OR DELETE OR UPDATE ON mariner 
DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION export_dmq()


Yes, for those highly observant veterans, dbmirror_recordchange is 
indeed  DBMIRROR. And no, we cannot replace it, since this is our own 
ultra hacked and customized version, not replaceable by any past, 
present (and most likely future) extension.


As you noticed the last trigger is a CONSTRAINT DEFERRABLE trigger. This 
function mariner_update_availability_date is supposed to be run by a 
user : cbt_results_import strippedof any privileges to the rest of the 
system. Here is what we get : when we SET the constraint of the last 
trigger to IMMEDIATE, the function runs on behalf of its owner 
(postgres) who has all needed privileges (as superuser) to run the 
update on mariner table and also run the triggers . However, when we run 
with this CONSTRAINT as DEFERRED then it seems to NOT run the last 
deferrable trigger as postgres.


postgres@smadb-pgsql16:~$ psql
psql (16.4)
Type "help" for help.

postgres@[local]/dynacom=# set role cbt_results_import ;
SET
postgres@[local]/dynacom=> begin ;
BEGIN
postgres@[local]/dynacom=*> SET CONSTRAINTS zzzmariner_dmq_tg IMMEDIATE;
SET CONSTRAINTS
postgres@[local]/dynacom=*> select 
mariner_update_availability_date(13916, '2020-02-28');

mariner_update_availability_date
--

(1 row)

postgres@[local]/dynacom=*> commit ;
COMMIT
postgres@[local]/dynacom=> begin ;
BEGIN
postgres@[local]/dynacom=*> SET CONSTRAINTS zzzmariner_dmq_tg DEFERRED;
SET CONSTRAINTS
postgres@[local]/dynacom=*> select 
mariner_update_availability_date(13916, '2020-02-28');

mariner_update_availability_date
--

(1 row)

postgres@[local]/dynacom=*> commit ;
ERROR:  permission denied for table export_dmq
CONTEXT:  SQL statement "DELETE FROM export_dmq where id=($1).id and 
op='U' and tbl='mariner'"

PL/pgSQL function export_dmq() line 15 at EXECUTE
postgres@[local]/dynacom=>

Is this supposed to be normal? Documented anywhere ?

Thank you

Re: ssh to DB server and su normal users very slow :

2024-09-09 Thread KK CHN

update :  the  ssh -v root@db_Server_IP from my  Windows cmd   pasted
below for more details



On Mon, Sep 9, 2024 at 4:50 PM KK CHN  wrote:

> List,
>
> I have configured pgbackrest for the DB server and Repo Server(created the
> stanza info check all fine.  in these machines.
>
>
> /var/spool/pgbackrest  shows   the .Okfor each WAL  and   the Repo
> server receiving the archiving of WAL in the archive directory .
>
>
> I didn't  schedule a pgbackrest  back  as of now  due to  an issue I am
> facing as follows.
>
> PROBLEM Statement:
>
> I am facing a delay in ssh  root@dbserver_IP from my Desktop PC.  More
> than a minute to login to the root console from any remote terminal, but
> this issue was not there all these days.
>
>  I have done two changes in the DB server :-
>
> 1.
> pg_hba.conf entry
>
> ie;  changed the entry   #local   all all  trust
>
> To
> #local   all allmd5
>
>
> It already having replication entry as
> local   replication all peer
>
> 2.
> Added a .pgpass entry in theDB user's~/dir/ with the following
>
> [root@db1 ~]# cat /var/lib/edb/.pgpass
> *:*:*:enterprisedb:password
>
>
> Is this causing login delays ?   Local connection asking
> password(pg_hba.conf entry ) and   .pgpass contain the user and password
> for connecting ?
>
>
> 3. Even if I able to login to the DB server from the Remote Repo server
> after a minute or two, in side the DB server doing a #  su
> enterprisedbtaking around 90 to 120 seconds to change the user as
> enterprisedb user ??
>
> Any hints much appreciated ..
>
> Thanks in advance,
> Krishane
>
>
>
> *For more  details   I am pasting the top output ( vCPU 16 nos , RAM
> 16 GB)*
>
> top - 10:11:43 up 5 days, 17:21,  5 users,  load average: 0.97, 1.38, 1.26
> Tasks: 710 total,   1 running, 708 sleeping,   1 stopped,   0 zombie
> %Cpu(s):  1.3 us,  0.6 sy,  0.0 ni, 97.1 id,  0.6 wa,  0.1 hi,  0.3 si,
>  0.0 st
> MiB Mem :  15733.6 total,664.0 free,   6371.1 used,  13237.6 buff/cache
> MiB Swap:   8060.0 total,   7985.1 free, 74.9 used.   9362.4 avail Mem
>
> PID USER  PR  NIVIRTRESSHR S  %CPU  %MEM TIME+
> COMMAND
> 3547252 enterpr+  20   0 4656880 262304 252032 S   8.3   1.6   0:01.97
> edb-postgres
>2588 enterpr+  20   0 4622104  12704  10888 S   2.0   0.1 106:10.00
> edb-postgres
> 3554955 enterpr+  20   0 4661692 632052 621364 S   2.0   3.9   0:00.99
> edb-postgres
> 3555894 enterpr+  20   0 4633432 628388 621056 S   1.3   3.9   0:00.26
> edb-postgres
> 3525520 enterpr+  20   0 465 96 543872 S   1.0   3.4   0:10.82
> edb-postgres
> 3546456 enterpr+  20   0 4627288  40852  38016 S   1.0   0.3   0:00.30
> edb-postgres
> 3554919 enterpr+  20   0 4655376 564024 557020 S   1.0   3.5   0:00.30
> edb-postgres
> 3555796 enterpr+  20   0 4635024 565716 556840 S   1.0   3.5   0:00.22
> edb-postgres
> 3556084 enterpr+  20   0 4653424  59156  51968 S   1.0   0.4   0:00.06
> edb-postgres
> 3525597 enterpr+  20   0 4627444  44052  41088 S   0.7   0.3   0:00.47
> edb-postgres
> 377 root   0 -20   0  0  0 I   0.3   0.0   2:43.11
> kworker/5:1H-kblockd
> 2923344 enterpr+  20   0 4625236 225176 223104 S   0.3   1.4   1:23.93
> edb-postgres
> 3525722 enterpr+  20   0 4627328  99220  96128 S   0.3   0.6   0:01.99
> edb-postgres
> 3555151 root  20   0  226580   4864   3456 R   0.3   0.0   0:00.15 top
> 3555807 enterpr+  20   0 4627444 350228 347136 S   0.3   2.2   0:00.03
> edb-postgres
> 3556023 enterpr+  20   0 4653636  60052  52608 S   0.3   0.4   0:00.15
> edb-postgres
> 3556026 enterpr+  20   0 4653424  59796  52608 S   0.3   0.4   0:00.22
> edb-postgres
> 3556074 enterpr+  20   0 4653448  59540  52224 S   0.3   0.4   0:00.11
> edb-postgres
> 3556075 enterpr+  20   0 4653372  59412  52224 S   0.3   0.4   0:00.18
> edb-postgres
>
>
> and  # ps -ax   shows
> [root@db1 ~]# ps -ax |grep "idle"
>
> 3511515 ?I  0:00 [kworker/5:0-inet_frag_wq]
> 3512991 ?Ss 0:00 postgres: enterprisedb postgres
> 10.21.134.205(56754) idle
> 3513003 ?Ss 0:00 postgres: enterprisedb er_db
> 10.21.13.205(56770) idle
> 3513005 ?Ss 0:00 postgres: enterprisedb tp_db
> 10.21.13.205(56772) idle
> 3513267 ?Ss 0:00 postgres: enterprisedb er_db
> 10.23.0.203(39262) idle
> 3513476 ?Ss 0:00 postgres: enterprisedb er_db
> 10.21.13.205(56839) idle
> 3513704 ?Ss 0:00 postgres: enterprisedb mt_db
> 10.21.13.202(56608) idle
> 3513729 ?Ss 0:00 postgres: enterprisedb er_db
> 10.23.0.203(44926) idle
> 3514113 ?Ss 0:00 postgres: enterprisedb mt_db
> 10.21.13.202(53743) idle
> 3514374 ?Ss 0:00 postgres: enterprisedb mt_db
> 10.21.13.202(58623) idle
> 3514397 pts/1T  0:00 top
> 3515012 ?Ss 0:00 postgres: enterprisedb mt_db
> 10.21.13.202(58686) idle
> 3515088 ?Ss 0:0

Re: ssh to DB server and su normal users very slow :



On 9/9/24 16:18, KK CHN wrote:
update :  the      ssh -v root@db_Server_IP from my Windows cmd  
 pasted below for more details



Why is your windows cmd important here, show ssh debugging from your DB 
host to the pgbackrest host.


On Mon, Sep 9, 2024 at 4:50 PM KK CHN  wrote:

List,

I have configured pgbackrest for the DB server and Repo
Server(created the stanza info check all fine.  in these machines.


/var/spool/pgbackrest  shows   the .Ok    for each WAL  and 
 the Repo server receiving the archiving of WAL in the archive
directory .


I didn't  schedule a pgbackrest  back  as of now  due to  an issue
I am facing as follows.

PROBLEM Statement:

I am facing a delay in ssh  root@dbserver_IP from my Desktop PC. 
More than a minute to login to the root console from any remote
terminal, but this issue was not there all these days.

 I have done two changes in the DB server :-

1.
pg_hba.conf entry

ie;  changed the entry       #local   all all          trust

To
#local   all             all           md5


It already having replication entry as
local   replication     all           peer

2.
Added a .pgpass entry in the    DB user's    ~/dir/ with the following

[root@db1 ~]# cat /var/lib/edb/.pgpass
*:*:*:enterprisedb:password


Is this causing login delays ?   Local connection asking
password(pg_hba.conf entry ) and   .pgpass contain the user and
password for connecting ?


3. Even if I able to login to the DB server from the Remote Repo
server after a minute or two, in side the DB server     doing a # 
su enterprisedb    taking around 90 to 120 seconds to change the
user as    enterprisedb user ??

Any hints much appreciated ..

Thanks in advance,
Krishane



*For more  details   I am pasting the top output     ( vCPU 16 nos
, RAM 16 GB)*
*
*
top - 10:11:43 up 5 days, 17:21,  5 users,  load average: 0.97,
1.38, 1.26
Tasks: 710 total,   1 running, 708 sleeping,   1 stopped,   0 zombie
%Cpu(s):  1.3 us,  0.6 sy,  0.0 ni, 97.1 id,  0.6 wa,  0.1 hi,
 0.3 si,  0.0 st
MiB Mem :  15733.6 total,    664.0 free,   6371.1 used,  13237.6
buff/cache
MiB Swap:   8060.0 total,   7985.1 free,     74.9 used.   9362.4
avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM    
TIME+ COMMAND
3547252 enterpr+  20   0 4656880 262304 252032 S   8.3 1.6  
0:01.97 edb-postgres
   2588 enterpr+  20   0 4622104  12704  10888 S   2.0 0.1
106:10.00 edb-postgres
3554955 enterpr+  20   0 4661692 632052 621364 S   2.0 3.9  
0:00.99 edb-postgres
3555894 enterpr+  20   0 4633432 628388 621056 S   1.3 3.9  
0:00.26 edb-postgres
3525520 enterpr+  20   0 465 96 543872 S   1.0 3.4  
0:10.82 edb-postgres
3546456 enterpr+  20   0 4627288  40852  38016 S   1.0 0.3  
0:00.30 edb-postgres
3554919 enterpr+  20   0 4655376 564024 557020 S   1.0 3.5  
0:00.30 edb-postgres
3555796 enterpr+  20   0 4635024 565716 556840 S   1.0 3.5  
0:00.22 edb-postgres
3556084 enterpr+  20   0 4653424  59156  51968 S   1.0 0.4  
0:00.06 edb-postgres
3525597 enterpr+  20   0 4627444  44052  41088 S   0.7 0.3  
0:00.47 edb-postgres
    377 root       0 -20       0      0      0 I   0.3 0.0  
2:43.11 kworker/5:1H-kblockd
2923344 enterpr+  20   0 4625236 225176 223104 S   0.3 1.4  
1:23.93 edb-postgres
3525722 enterpr+  20   0 4627328  99220  96128 S   0.3 0.6  
0:01.99 edb-postgres
3555151 root      20   0  226580   4864   3456 R   0.3 0.0  
0:00.15 top
3555807 enterpr+  20   0 4627444 350228 347136 S   0.3 2.2  
0:00.03 edb-postgres
3556023 enterpr+  20   0 4653636  60052  52608 S   0.3 0.4  
0:00.15 edb-postgres
3556026 enterpr+  20   0 4653424  59796  52608 S   0.3 0.4  
0:00.22 edb-postgres
3556074 enterpr+  20   0 4653448  59540  52224 S   0.3 0.4  
0:00.11 edb-postgres
3556075 enterpr+  20   0 4653372  59412  52224 S   0.3 0.4  
0:00.18 edb-postgres


and  # ps -ax   shows
[root@db1 ~]# ps -ax |grep "idle"

3511515 ?        I      0:00 [kworker/5:0-inet_frag_wq]
3512991 ?        Ss     0:00 postgres: enterprisedb postgres
10.21.134.205(56754) idle
3513003 ?        Ss     0:00 postgres: enterprisedb er_db
10.21.13.205(56770) idle
3513005 ?        Ss     0:00 postgres: enterprisedb tp_db
10.21.13.205(56772) idle
3513267 ?        Ss     0:00 postgres: enterprisedb er_db
10.23.0.203(39262) idle
3513476 ?        Ss     0:00 postgres: enterprisedb er_db
10.21.13.205(56839) idle
3513704 ?        Ss     0:00 postgres: enterprisedb mt_db
10.21.13.202(56608) idle
3513729 ?        Ss     0:00 postgres: enterprisedb er_db
10.23.0.203(44926) idle
3514113 ?        Ss     0:00 postgres: enterprisedb mt_db
10.21.13.202(53743) idle

Connection between PostgreSQL and SAP HANA database

2024-09-09 Thread Thürmann , Andreas

Hello everyone,

I'm currently trying to establish a connection between PostgreSQL and a SAP 
HANA database. Unfortunately, so far without success.
SAP's own ODBC driver works correctly and I can query the HANA database using 
an ISQL command. However, I can't establish a connection from PostgreSQL.
I used the following commands to establish the connection:

CREATE SERVER hana_server
FOREIGN DATA WRAPPER odbc_fdw
OPTIONS (dsn 'SAP_ODBC_DNS');

CREATE FOREIGN TABLE hana_table (
  matnr VARCHAR(40)
)
SERVER hana_server
OPTIONS (table 'SAPABAP1.MARA');

CREATE USER MAPPING FOR postgres
SERVER hana_server
OPTIONS (odbc_user 'secret_user', odbc_password 'secret_password');

Can anyone tell me where the error is?

Mit freundlichen Grüßen

i. A. Andreas Thürmann
Teamleitung IT-Applikationen & BI
IT-Applikationen & BI

Schönmackers Umweltdienste GmbH & Co. KG
Hooghe Weg 1
47906 KempenTel:
Fax:
E-Mail:
+49 (2152) 207 6765

andreas.thuerm...@schoenmackers.de


Unsere aktuellen Datenschutzhinweise gemäß Artikel 13 DSGVO finden Sie hier: 
Kunden 

  / Bewerber 
 .

[cid:sud_logo2.png]



Schönmackers Umweltdienste GmbH & Co. KG, Hooghe Weg 1, 47906 Kempen, Sitz: 
Kempen, Amtsgericht Krefeld, HRA 3763,
p.h.G.: Schönmackers Umweltdienste Verwaltung GmbH, Sitz: Kempen, Amtsgericht 
Krefeld, HRB 6153,
Geschäftsführer: Oliver Zimmermann
www.schoenmackers.de 

[cid:cshop.png]





sud_logo2.png
Description: sud_logo2.png


cshop.png
Description: cshop.png

Clarify this MERGE warning? "Only columns from the target table that attempt to match data_source rows should appear in join_condition."

2024-09-09 Thread Philip Hazelden

The MERGE docs[1] give this warning:

> Only columns from the target table that attempt to match
> `data_source` rows should appear in `join_condition`.
> `join_condition` subexpressions that only reference the target
> table's columns can affect which action is taken, often in
> surprising ways.

(The docs for upcoming v17 have the same line.)

But when I tested this, it seems to work fine. For example, consider a
two-level primary key, where the source table implicitly has a fixed
value for one level:

create table t1 (k1 int, k2 int, v text);
insert into t1 values
  (1, 1, '1.1'), (1, 2, '1.2'),
  (2, 1, '2.1'), (2, 2, '2.2'), (2, 3, '2.3');

create table t2 (k2 int, v text);
insert into t2 values (1, '1.1 v2'), (3, '1.3 v2');

merge into t1 using t2
  on t1.k2 = t2.k2 and t1.k1 = 1
  when matched then update set v = t2.v
  when not matched then insert values (1, t2.k2, t2.v);

`t1` now contains

 k1 | k2 |   v
++
  1 |  1 | 1.1 v2
  1 |  2 | 1.2
  1 |  3 | 1.3 v2
  2 |  1 | 2.1
  2 |  2 | 2.2
  2 |  3 | 2.3
(6 rows)

Which is what I'd expect.

So why should I avoid doing this? It's not clear to me whether the
warning is saying "this likely won't work like you expect because it's
difficult to reason about" or "because the behavior is unspecified" or
"because there's a bug" or what.

I found a thread[2] on the psql-hackers list which has this snippet of
conversation:

>>> * It might make sense to point out in the docs that join_condition
>>> should not filter the target table too much. Like SQL server docs say,
>>> don't put things in the join that filter the target that actually
>>> belong in the WHEN .. AND quals. In a way, this should be obvious,
>>> because it's an outer join. But I don't think it is, and ISTM that the
>>> sensible thing to do is to warn against it.
>>>
>>
>> Hmm, ok. Not sure how exactly to put that in words without confusing users.
>> Do you want to suggest something?
>
> Perhaps a Warning box should say:
>
> Only columns from "target_table_name" that attempt to match
> "data_source" rows should appear in "join_condition".
> "join_condition" subexpressions that only reference
> "target_table_name" columns can only affect which action is taken,
> often in surprising ways.

Notably, the "only affect" became simply "affect" in the docs, which I
think is less clear.

This makes me think the warning is trying to say something like: "if
you can move a subexpression from `join_condition` to `WHEN ... AND`,
you should probably do so". Is that right?

(I still don't know *why* I should do that. It sounds like maybe it's
more efficient that way because this is an outer join? But I don't
know why that matters. If I'd had to guess which would be more
efficient, I'd have weakly guessed "prefer to do it in a join, indexes
will get used better that way".)

And I think it's not always possible to move a subexpression without
changing `data_source`. In the example I posted above, I don't think
it would work, since we'd no longer have a `NOT MATCHED` on `t2`'s
`(3, '1.3 v2')` row.

[1]: https://www.postgresql.org/docs/current/sql-merge.html
[2]: 
https://www.postgresql.org/message-id/flat/CANP8%2BjKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc%2BXrz8QB0nXA%40mail.gmail.com

Re: Strange permission effect depending on DEFERRABILITY

Achilleas Mantzios - cloud  writes:
> As you noticed the last trigger is a CONSTRAINT DEFERRABLE trigger. This 
> function mariner_update_availability_date is supposed to be run by a 
> user : cbt_results_import strippedof any privileges to the rest of the 
> system. Here is what we get : when we SET the constraint of the last 
> trigger to IMMEDIATE, the function runs on behalf of its owner 
> (postgres) who has all needed privileges (as superuser) to run the 
> update on mariner table and also run the triggers . However, when we run 
> with this CONSTRAINT as DEFERRED then it seems to NOT run the last 
> deferrable trigger as postgres.

AFAIR the trigger mechanisms do not change the execution environment.
If they did, then for example a trigger that stuffs CURRENT_USER into
a last_updated_by column would not give the desired results.

I'd suggest marking the problem trigger function as SECURITY DEFINER
if you want it to run as its owner.

regards, tom lane

Logical replication without direct link between publisher and subscriber?

2024-09-09 Thread Koen De Groote

I want to have a PG instance receive logical replication from a publisher.

However, the subscriber should not have network access to the publisher, in
any way. This is for security reasons. No VPN or any setup that allows the
subscriber to send traffic to the publisher host.

The publisher, however, can reach the subscriber IP and send traffic to it.

Is there a way to start a logical replication setup without having the
subscriber create a subscription? Run a few commands on the publisher and
the subscriber suddenly starts receiving data?

>From what I've googled, an option would be to use pgrecvlogical:
https://www.postgresql.org/docs/current/app-pgrecvlogical.html

Dumping changes periodically, sending them directly or uploading to cloud
storage and then downloading and applying them on the subscriber side.

But maybe there's a simpler option someone here knows about?

Any help and/or suggestions appreciated.

Thanks,
Koen

infinite loop in an update statement

2024-09-09 Thread Fabrice Chapuis

Hi,

table a and b are empty, this query does not return. It seems we enter in
infinite loop.
why this update does not return instantly?

UPDATE table_a a
 SET col1 = (SELECT MIN(b.col1)
FROM table_b b
WHERE b.col2 = a.col2)

Regards,

Fabrice

Re: Strange permission effect depending on DEFERRABILITY



On 9/9/24 17:21, Tom Lane wrote:

Achilleas Mantzios - cloud  writes:

As you noticed the last trigger is a CONSTRAINT DEFERRABLE trigger. This
function mariner_update_availability_date is supposed to be run by a
user : cbt_results_import strippedof any privileges to the rest of the
system. Here is what we get : when we SET the constraint of the last
trigger to IMMEDIATE, the function runs on behalf of its owner
(postgres) who has all needed privileges (as superuser) to run the
update on mariner table and also run the triggers . However, when we run
with this CONSTRAINT as DEFERRED then it seems to NOT run the last
deferrable trigger as postgres.

AFAIR the trigger mechanisms do not change the execution environment.
If they did, then for example a trigger that stuffs CURRENT_USER into
a last_updated_by column would not give the desired results.

I'd suggest marking the problem trigger function as SECURITY DEFINER
if you want it to run as its owner.


Thank you Tom.  With a little bit of debugging is obvious that the last 
deferred trigger function runs outside the security environment of the 
top SECURITY DEFINER function (naturally), however current_user seems to 
be on par with the security definer owner,


cbt_results_import@[local]/dynacom=> begin ;
BEGIN
cbt_results_import@[local]/dynacom=*> SET CONSTRAINTS zzzmariner_dmq_tg 
IMMEDIATE;

SET CONSTRAINTS
cbt_results_import@[local]/dynacom=*> select 
mariner_update_availability_date(13916, '2020-02-28');
NOTICE:  manage past : the current_user is postgres, session_user is 
cbt_results_import, system_user id md5:cbt_results_import
NOTICE:  manage past : the current_user is postgres, session_user is 
cbt_results_import, system_user id md5:cbt_results_import

mariner_update_availability_date
--

(1 row)

cbt_results_import@[local]/dynacom=*> commit ;
COMMIT
cbt_results_import@[local]/dynacom=> begin ;
BEGIN
cbt_results_import@[local]/dynacom=*> SET CONSTRAINTS zzzmariner_dmq_tg 
DEFERRED;

SET CONSTRAINTS
cbt_results_import@[local]/dynacom=*> select 
mariner_update_availability_date(13916, '2020-02-28');
NOTICE:  manage past : the current_user is postgres, session_user is 
cbt_results_import, system_user id md5:cbt_results_import

mariner_update_availability_date
--

(1 row)

cbt_results_import@[local]/dynacom=*> commit ;
NOTICE:  manage past : the current_user is cbt_results_import, 
session_user is cbt_results_import, system_user id md5:cbt_results_import

ERROR:  permission denied for table export_dmq
CONTEXT:  SQL statement "DELETE FROM export_dmq where id=($1).id and 
op='U' and tbl='mariner'"

PL/pgSQL function export_dmq() line 18 at EXECUTE

Thank you and sorry for missing something so obvious.



regards, tom lane

Re: infinite loop in an update statement

Fabrice Chapuis  writes:
> why this update does not return instantly?

> UPDATE table_a a
>  SET col1 = (SELECT MIN(b.col1)
> FROM table_b b
> WHERE b.col2 = a.col2)

Maybe query is waiting for a lock on one of those tables?

regards, tom lane

Re: infinite loop in an update statement


On 9/9/24 07:55, Fabrice Chapuis wrote:

Hi,

table a and b are empty, this query does not return. It seems we enter 
in infinite loop.

why this update does not return instantly?

UPDATE table_a a
  SET col1 = (SELECT MIN(b.col1)
                     FROM table_b b
                     WHERE b.col2 = a.col2)


Do you have an UPDATE trigger on table_a?



Regards,

Fabrice




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

Re: Connection between PostgreSQL and SAP HANA database


On 9/9/24 00:53, Thürmann, Andreas wrote:

Hello everyone,

I'm currently trying to establish a connection between PostgreSQL and a 
SAP HANA database. Unfortunately, so far without success.


SAP's own ODBC driver works correctly and I can query the HANA database 
using an ISQL command. However, I can't establish a connection from 
PostgreSQL.


I used the following commands to establish the connection:

CREATE SERVER hana_server

FOREIGN DATA WRAPPER odbc_fdw

OPTIONS (dsn 'SAP_ODBC_DNS');

CREATE FOREIGN TABLE hana_table (

   matnr VARCHAR(40)

)

SERVER hana_server

OPTIONS (table 'SAPABAP1.MARA');

CREATE USER MAPPING FOR postgres

SERVER hana_server

OPTIONS (odbc_user 'secret_user', odbc_password 'secret_password');

Can anyone tell me where the error is?


Postgres version?

SAP HANA version?

odbc_fdw version and where did you get it from?

How do you know you are not making a connection?

Have you looked at Postgres log for errors?


andreas.thuerm...@schoenmackers.de 



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

Re: postgresql FDW vs dblink for DDL


On 9/9/24 03:24, Achilleas Mantzios - cloud wrote:


On 9/8/24 23:46, Adrian Klaver wrote:

On 9/8/24 13:04, Achilleas Mantzios wrote:

Hi

for remote DDL execution (such as CREATE TABLE) is dblink my only 
option?


You will need to define in what context you are considering options.

For instance you can do remote DDL operations by passing a command or 
script via psql.


Thank you Adrian,

I was thinking of wrapping the DDL around a FUNCTION that will handle 
both the local and the (possibly various logical subscriptions), so that 
my programmers don't need to write extra scripts.


And the thing is that this creation via DDL is inside our design. 
Certain users create some backup tables of the public data in their own 
schema (via our app), then do some manipulations on the public data, 
then restore to the public or merge with the backups. When done, those 
backup tables are dropped. So the DDL is inside the app. And the 
question was if dblink is my only option, in the sense of doing this in 
a somewhat elegant manner. (and not resort to scripts, etc)


My sense is yes, if you want to encapsulate all of this within the 
database/app you will need to use dblink.








thanks








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

Re: How effectively do the indexing in postgres in such cases

2024-09-09 Thread Greg Sabino Mullane

Your questions are a little too vague to answer well, but let me try a bit.

1)In the query below , if the optimizer chooses tab1 as the driving table,
> the index on just col1 should be enough or it should be (col1, tab1_id)?
>

No way to tell without trying it yourself. We need information on how the
tables are joined, the cardinality, general distribution, etc. But as a
rough general rule, yes, indexes on the column of interest should be able
to handle the job well by themselves.

2)In scenarios where the customer has a lot of matching transactions (say
> in millions) post all the filters applied , and as the customer has to just
> see the latest 100 rows transaction data, the sorting will be a bottleneck.
> So what can be done to make such types of queries to return the latest
> transactions in quick time on the search screen?
>

Sorting can be quick, if you hit an index (b-trees are already sorted)
Postgres can look at only the first X rows returned and does not need to
read the whole thing. So a well-designed index is the key here.


> 3)As here also the count has to happen in the first step to make the
> customer know the total number of rows(which may be in millions), so what
> additional index will support this requirement?
>

Again, a very vague question, but for things that are in millions, an
estimate is usually sufficient, so you might be able to do something like
SELECT count(*) FROM mytab WHERE mydate BETWEEN x AND y; and use that as
your answer. Compare to the full query to see how close it is. You might
even have cutoffs, where if the results of that first one is < 10,000,
switch to a more accurate version which has more filtering (i.e. the joins
and more where conditions).


> Or if any other optimization strategy we can follow for catering to such
> queries?
>
> select * from tab1, tab2
> where tab1.part_date between '1-jan-2024' and '31-jan-2024'
> and tab1.part_date=tab2.part_date
> and tab1.tab1_id=tab2.tab2_id
> and tab1.col1=<:input_col1>
> and tab2.col2=<:input_col2>
> order by tab1.create_timestamp desc
> limit 100 offset 100;
>

It probably would help to see exact tables and queries. Why are you joining
on part_date? Is tab_id unique to either table? Running EXPLAIN on these
while you try out indexes and change the joins, etc. is a great exercise to
help you learn how Postgres works. As far as asking on lists for help with
specific queries, there is a range between totally abstract and generic
queries that nobody can help you with, and large, complex specific queries
that nobody wants to unravel and help you with. You are definitely on the
former side: try to create some actually runable sample queries that are
small, self-contained, and generate the problem you are trying to solve.

Cheers,
Greg

Re: postgresql FDW vs dblink for DDL

Adrian Klaver  writes:
> On 9/9/24 03:24, Achilleas Mantzios - cloud wrote:
>> And the thing is that this creation via DDL is inside our design. 
>> Certain users create some backup tables of the public data in their own 
>> schema (via our app), then do some manipulations on the public data, 
>> then restore to the public or merge with the backups. When done, those 
>> backup tables are dropped. So the DDL is inside the app. And the 
>> question was if dblink is my only option, in the sense of doing this in 
>> a somewhat elegant manner. (and not resort to scripts, etc)

> My sense is yes, if you want to encapsulate all of this within the 
> database/app you will need to use dblink.

postgres_fdw certainly can't do it, nor any other FDW -- the FDW APIs
simply don't cover issuance of DDL.  If you don't like dblink, you
could consider writing code within plperlu or plpythonu or another
"untrusted" PL, making use of whatever Postgres client library exists
within that PL's ecosystem to connect to the remote server.  It's also
possible that there's some third-party extension that overlaps
dblink's functionality.  dblink sure seems like the path of least
resistance, though.

regards, tom lane

Re: ssh to DB server and su normal users very slow :

2024-09-09 Thread Thiemo Kellner

Hi

What does the server side do in the wait time?

Cheers Thiemo

Re: Clarify this MERGE warning? "Only columns from the target table that attempt to match data_source rows should appear in join_condition."

On 9/9/24 06:02, Philip Hazelden wrote:

The MERGE docs[1] give this warning:

Only columns from the target table that attempt to match
`data_source` rows should appear in `join_condition`.
`join_condition` subexpressions that only reference the target
table's columns can affect which action is taken, often in
surprising ways.

(The docs for upcoming v17 have the same line.)

So why should I avoid doing this? It's not clear to me whether the
warning is saying "this likely won't work like you expect because it's
difficult to reason about" or "because the behavior is unspecified" or
"because there's a bug" or what.

I found a thread[2] on the psql-hackers list which has this snippet of
conversation:

* It might make sense to point out in the docs that join_condition
should not filter the target table too much. Like SQL server docs say,
don't put things in the join that filter the target that actually
belong in the WHEN .. AND quals. In a way, this should be obvious,
because it's an outer join. But I don't think it is, and ISTM that the
sensible thing to do is to warn against it.

FYI the SQL Server note does not shed any more light on this:

https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16

Caution

It's important to specify only the columns from the target table to use
for matching purposes. That is, specify columns from the target table
that are compared to the corresponding column of the source table. Don't
attempt to improve query performance by filtering out rows in the target
table in the ON clause; for example, such as specifying AND NOT
target_table.column_x = value. Doing so can return unexpected and
incorrect results.

This makes me think the warning is trying to say something like: "if
you can move a subexpression from `join_condition` to `WHEN ... AND`,
you should probably do so". Is that right?

(I still don't know *why* I should do that. It sounds like maybe it's
more efficient that way because this is an outer join? But I don't
know why that matters. If I'd had to guess which would be more
efficient, I'd have weakly guessed "prefer to do it in a join, indexes
will get used better that way".)

I would say because you could end with WHEN clauses that are at odds
with the JOIN clause. In other words you throw away rows in the JOIN
that you need later in the WHEN(s). Basically keep rows around until you
are sure they are not needed.

And I think it's not always possible to move a subexpression without
changing `data_source`. In the example I posted above, I don't think
it would work, since we'd no longer have a `NOT MATCHED` on `t2`'s
`(3, '1.3 v2')` row.

[1]: https://www.postgresql.org/docs/current/sql-merge.html
[2]:
https://www.postgresql.org/message-id/flat/CANP8%2BjKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc%2BXrz8QB0nXA%40mail.gmail.com

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

Re: postgresql FDW vs dblink for DDL

2024-09-09 Thread Achilleas Mantzios


Στις 9/9/24 18:40, ο/η Tom Lane έγραψε:

Adrian Klaver  writes:

On 9/9/24 03:24, Achilleas Mantzios - cloud wrote:

And the thing is that this creation via DDL is inside our design.
Certain users create some backup tables of the public data in their own
schema (via our app), then do some manipulations on the public data,
then restore to the public or merge with the backups. When done, those
backup tables are dropped. So the DDL is inside the app. And the
question was if dblink is my only option, in the sense of doing this in
a somewhat elegant manner. (and not resort to scripts, etc)

My sense is yes, if you want to encapsulate all of this within the
database/app you will need to use dblink.

postgres_fdw certainly can't do it, nor any other FDW -- the FDW APIs
simply don't cover issuance of DDL.  If you don't like dblink, you
could consider writing code within plperlu or plpythonu or another
"untrusted" PL, making use of whatever Postgres client library exists
within that PL's ecosystem to connect to the remote server.  It's also
possible that there's some third-party extension that overlaps
dblink's functionality.  dblink sure seems like the path of least
resistance, though.

Thank you Tom and Adrian.


regards, tom lane


--
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt (as agents only)

Re: Strange permission effect depending on DEFERRABILITY

2024-09-09 Thread Laurenz Albe

On Mon, 2024-09-09 at 16:14 +0300, Achilleas Mantzios - cloud wrote:
> The below runs on PostgreSQL 16.4
> 
> We are trying to implement a certain operation based on a security definer
> function : mariner_update_availability_date
> 
> This is supposed to update a table : mariner , which has several other 
> triggers : 
> 
>   [...]
>   zzzmariner_dmq_tg AFTER INSERT OR DELETE OR UPDATE ON mariner DEFERRABLE 
> INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION export_dmq()
> 
> As you noticed the last trigger is a CONSTRAINT DEFERRABLE trigger.
> This function mariner_update_availability_date is supposed to be run by a 
> user :
> cbt_results_import stripped of any privileges to the rest of the system. Here 
> is
> what we get : when we SET the constraint of the last trigger to IMMEDIATE, the
> function runs on behalf of its owner (postgres) who has all needed privileges
> (as superuser) to run the update on mariner table and also run the triggers .
> However, when we run with this CONSTRAINT as DEFERRED then it seems to NOT run
> the last deferrable trigger as postgres. 

I have proposed a patch that fixes exactly that case:
https://commitfest.postgresql.org/49/4888/

So far, the feedback seems to be that it is not considered a bug.
But that doesn't mean that we cannot change the behavior.

Yours,
Laurenz Albe

Check used privilege in a statment

2024-09-09 Thread Ahmed Ibrahim

Hi,
I am trying to know which privileges are used in a specific query but I am
facing problems when I have nested queries. Where is the best place/hook I
can check for all needed permissions for a query in the source? Currently,
trying it in the exectuter start but the nested queries are problematic.
Thanks!

Re: Check used privilege in a statment