postgresql api

[vyshu Ysh]

Hi Team,

Is there any postgresql API which tells when the master/standby server is
down ?

Thanks,
Sudha

Re: Logical locking beyond pg_advisory

[marcelo]

/Would be nice if you explain more about what kind of problem you want 
to solve./


There are two main questions "to solve"
a) generally speaking, I like to get some form of "exclusive access" to 
the row before updating or deleting. None of the optimistic / pesimistic 
automatic variants of concurrency management glad me. Nor, of course, 
the "versioning" variants.
b) some of the tables I´m working on have a "number" column (_not the 
PK_) which sometimes come from material, external sources, but sometimes 
must be assigned by the system. This could be solved two main ways
b.1) Use a trigger to get the famous "max(n) + 1". At least in one of 
the cases, the number automatically generated must fall into different 
ranges conditioned by the value of another column, and the ranges vary 
between versions of the database.
b.2) "Lock the entire table", get programmatically the next number for 
the correct range, assign it and free the table lock.


Of course, and beforehand, all database manipulations are done thru 
applications.
Till yesterday, I was working with the "advisory" family. Having a 
bigint as the only "lock identifier" I was working with a hash of the 
table name XORed with the id of the row or zero for the entire table. 
(All my tables have an autosequential integer id as PK).
Even if I found a very robust hash algorithm for the table name, I 
cannot discard some collision once the id was xored. I tested five or 
six table names, along 2 ids every one, without collision. But...
Of course, I need the "full table lock" for inserts. So, it´s a very 
separated concern with updates and deletions. But...


TIA


On 17/09/2018 03:19 , Fabrízio de Royes Mello wrote:


Em dom, 16 de set de 2018 às 17:53, marcelo > escreveu:

>
> I need a mechanism of "logical locking" more ductile than the 
pg_advisory family.
> I'm thinking of a table ("lock_table") that would be part of the 
database, with columns

> * tablename varchar - name of the table "locked"
> * rowid integer, - id of the row "locked"
> * ownerid varchar, - identifier of the "user" who acquired the lock
> * acquired timestamp - to be able to release "abandoned" locks after 
a certain time

>
> and a group of functions
> 1) lock_table (tablename varchar, ownerid varchar) bool - get to 
lock over the entire table, setting rowid to zero
> 2) unlock_table (tablename varchar, ownerid varchar) bool - unlock 
the table, if the owner is the recorded one
> 3) locked_table (tablename varchar, ownerid varchar) bool - ask if 
the table is locked by some user other than the ownerid argument
> 4) lock_row (tablename varchar, rowid integer, ownerid varchar) bool 
- similar to pg_try_advisory_lock
> 5) unlock_row (tablename varchar, rowid integer, ownerid varchar) 
bool - similar to pg_advisory_unlock

> 6) unlock_all (ownerid varchar) bool - unlock all locks owned by ownerid
>
> The timeout (default, maybe 15 minutes) is implicitly applied if the 
lock is taken by another user (there will be no notification).
> Redundant locks are not queued, they simply return true, may be 
after an update of the acquired column.
> Successful locks insert a new row, except the rare case of a 
timeout, which becomes an update (ownerid and acquired)

> Unlock operations deletes the corresponding row
>
> My question is double
> a) What is the opinion on the project?

Would be nice if you explain more about what kind of problem you want 
to solve.


> b) What are the consequences of the large number of inserts and 
deletions


The first thing came to my mind with this approach is table bloat.

> c) Performance. In fact, pg_advisory* implies a network roundtrip, 
but (I think) no table operations.


Yeap... no table operations.

Regards,

--
   Fabrízio de Royes Mello         Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento




---
El software de antivirus Avast ha analizado este correo electrónico en busca de 
virus.
https://www.avast.com/antivirus

Re: postgresql api

[Enrico Pirozzi]

Hi,

Have you tried to query the pg_stat_replication?


Enrico


Il lun 17 set 2018, 11:52 vyshu Ysh  ha scritto:

> Hi Team,
>
> Is there any postgresql API which tells when the master/standby server is
> down ?
>
> Thanks,
> Sudha
>

Pgbouncer and postgres

[Nicola Contu]

Hello,

I'm trying to get pgbouncer working but I'm getting this error :


2018-09-17 12:20:15.304 87772 NOISE safe_accept(12) = Resource temporarily 
unavailable
2018-09-17 12:20:36.183 87772 NOISE safe_accept(11) = 13 (127.0.0.1:59100)
2018-09-17 12:20:36.183 87772 NOISE new fd from accept=13
2018-09-17 12:20:36.183 87772 NOISE safe_accept(11) = Resource temporarily 
unavailable
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'logfile' = 
'/var/log/pgbouncer.log' ok:1
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'pidfile' = 
'/home/postgres/pgbouncer.pid'
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'pidfile' = 
'/home/postgres/pgbouncer.pid' ok:1
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'admin_users' = 'admin'
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'admin_users' = 'admin' ok:1
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'user' = 'postgres'
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'user' = 'postgres' ok:1
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'max_db_connections' = '220'
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'max_db_connections' = 
'220' ok:1
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'log_connections' = '0'
2018-09-17 12:21:14.413 88424 DEBUG parse_ini_file: 'log_connections' = '0' ok:1
2018-09-17 12:21:14.413 88424 DEBUG parse_ini_file: 'log_disconnections' = '0'
2018-09-17 12:21:14.413 88424 DEBUG parse_ini_file: 'log_disconnections' = '0' 
ok:1
2018-09-17 12:21:14.413 88424 DEBUG parse_ini_file: 'verbose' = '3'
2018-09-17 12:21:14.413 88424 DEBUG parse_ini_file: 'verbose' = '3' ok:1
2018-09-17 12:21:14.413 88424 DEBUG loading auth_file: 
"/etc/pgbouncer/users.txt"
2018-09-17 12:21:14.417 88424 NOISE event: 128, SBuf: 192, PgSocket: 400, 
IOBuf: 4108
2018-09-17 12:21:14.417 88424 LOG File descriptor limit: 1024 (H:4096), 
max_client_conn: 100, max fds possible: 230
2018-09-17 12:21:14.417 88424 DEBUG pktbuf_dynamic(128): 0x25277c0
2018-09-17 12:21:14.417 88424 DEBUG make_room(0x25277c0, 4): realloc newlen=256
2018-09-17 12:21:14.417 88424 DEBUG pktbuf_dynamic(128): 0x252ebb0
2018-09-17 12:21:14.418 88424 NOISE connect(4, unix:/tmp/.s.PGSQL.6543) = 
Connection refused
2018-09-17 12:21:14.418 88424 NOISE safe_close(4) = 0
2018-09-17 12:21:14.418 88424 LOG Stale pidfile, removing
2018-09-17 12:21:14.419 88424 DEBUG adns_create_context: evdns2
2018-09-17 12:21:14.421 88424 DEBUG add_listen: 127.0.0.1:6543
2018-09-17 12:21:14.422 88424 NOISE old TCP_DEFER_ACCEPT on 11 = 0
2018-09-17 12:21:14.422 88424 NOISE install TCP_DEFER_ACCEPT on 11
2018-09-17 12:21:14.422 88424 LOG listening on 127.0.0.1:6543
2018-09-17 12:21:14.422 88424 DEBUG add_listen: unix:/tmp/.s.PGSQL.6543
2018-09-17 12:21:14.422 88424 LOG listening on unix:/tmp/.s.PGSQL.6543
2018-09-17 12:21:14.423 88424 LOG process up: pgbouncer 1.8.1, libevent 
2.0.21-stable (epoll), adns: evdns2, tls: OpenSSL 1.0.2k-fips  26 Jan 2017
2018-09-17 12:21:14.756 88424 DEBUG loading auth_file: 
"/etc/pgbouncer/users.txt"
2018-09-17 12:21:48.917 88424 NOISE safe_accept(12) = 13 (unix:)
2018-09-17 12:21:48.917 88424 NOISE new fd from accept=13
2018-09-17 12:21:48.917 88424 WARNING tune_socket(13) failed: Operation not 
supported
2018-09-17 12:21:48.917 88424 NOISE safe_close(13) = 0
2018-09-17 12:21:48.918 88424 NOISE safe_accept(12) = Resource temporarily 
unavailable
2018-09-17 12:22:14.422 88424 LOG Stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 
0 B/s, xact 0 us, query 0 us wait time 0 us
2018-09-17 12:23:14.423 88424 LOG Stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 
0 B/s, xact 0 us, query 0 us wait time 0 us
2018-09-17 12:23:26.561 88424 LOG Got SIGINT, shutting down
2018-09-17 12:23:26.872 88424 LOG server connections dropped, exiting
2018-09-17 12:23:26.872 88424 NOISE safe_close(11) = 0
2018-09-17 12:23:26.872 88424 NOISE safe_close(12) = 0



Postgres version : 10.5

Libevent : 2.0.5

Pgbouncer : 1.8.1



It used to work. Now after trying to install latest version of pgbouncer 
(1.9.0) does not work anymore. Then I re-compiled the v 1.8.1



Content of the pgbouncer.ini

[databases]
DB_NAME = host=IP_ADDD port=5432 dbname=DB_NAME auth_user=pgbouncer 
pool_size=120

[pgbouncer]
listen_port = 6543
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/users.txt
auth_query = select uname,phash from user_lookup($1)
logfile = /var/log/pgbouncer.log
pidfile = /home/postgres/pgbouncer.pid
admin_users = admin
user=postgres
max_db_connections = 220
log_connections=0
log_disconnections=0
verbose=3



Content of the users .txt

[root@cmd-dev1 pgbouncer]# cat users.txt
"pgbouncer" "***"



I tried to change the listen address to 127.0.0.1 or to the IP of the VM. 
Nothing changed.


Can anyone help?


Thanks

Pgbouncer and postgres

[Nicola Contu]

Hello,

I'm trying to get pgbouncer working but I'm getting this error :


2018-09-17 12:20:15.304 87772 NOISE safe_accept(12) = Resource temporarily
unavailable
2018-09-17 12:20:36.183 87772 NOISE safe_accept(11) = 13 (127.0.0.1:59100)
2018-09-17 12:20:36.183 87772 NOISE new fd from accept=13
2018-09-17 12:20:36.183 87772 NOISE safe_accept(11) = Resource temporarily
unavailable
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'logfile' =
'/var/log/pgbouncer.log' ok:1
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'pidfile' =
'/home/postgres/pgbouncer.pid'
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'pidfile' =
'/home/postgres/pgbouncer.pid' ok:1
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'admin_users' = 'admin'
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'admin_users' = 'admin'
ok:1
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'user' = 'postgres'
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'user' = 'postgres' ok:1
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'max_db_connections' =
'220'
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'max_db_connections' =
'220' ok:1
2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'log_connections' = '0'
2018-09-17 12:21:14.413 88424 DEBUG parse_ini_file: 'log_connections' = '0'
ok:1
2018-09-17 12:21:14.413 88424 DEBUG parse_ini_file: 'log_disconnections' =
'0'
2018-09-17 12:21:14.413 88424 DEBUG parse_ini_file: 'log_disconnections' =
'0' ok:1
2018-09-17 12:21:14.413 88424 DEBUG parse_ini_file: 'verbose' = '3'
2018-09-17 12:21:14.413 88424 DEBUG parse_ini_file: 'verbose' = '3' ok:1
2018-09-17 12:21:14.413 88424 DEBUG loading auth_file:
"/etc/pgbouncer/users.txt"
2018-09-17 12:21:14.417 88424 NOISE event: 128, SBuf: 192, PgSocket: 400,
IOBuf: 4108
2018-09-17 12:21:14.417 88424 LOG File descriptor limit: 1024 (H:4096),
max_client_conn: 100, max fds possible: 230
2018-09-17 12:21:14.417 88424 DEBUG pktbuf_dynamic(128): 0x25277c0
2018-09-17 12:21:14.417 88424 DEBUG make_room(0x25277c0, 4): realloc
newlen=256
2018-09-17 12:21:14.417 88424 DEBUG pktbuf_dynamic(128): 0x252ebb0
2018-09-17 12:21:14.418 88424 NOISE connect(4, unix:/tmp/.s.PGSQL.6543) =
Connection refused
2018-09-17 12:21:14.418 88424 NOISE safe_close(4) = 0
2018-09-17 12:21:14.418 88424 LOG Stale pidfile, removing
2018-09-17 12:21:14.419 88424 DEBUG adns_create_context: evdns2
2018-09-17 12:21:14.421 88424 DEBUG add_listen: 127.0.0.1:6543
2018-09-17 12:21:14.422 88424 NOISE old TCP_DEFER_ACCEPT on 11 = 0
2018-09-17 12:21:14.422 88424 NOISE install TCP_DEFER_ACCEPT on 11
2018-09-17 12:21:14.422 88424 LOG listening on 127.0.0.1:6543
2018-09-17 12:21:14.422 88424 DEBUG add_listen: unix:/tmp/.s.PGSQL.6543
2018-09-17 12:21:14.422 88424 LOG listening on unix:/tmp/.s.PGSQL.6543
2018-09-17 12:21:14.423 88424 LOG process up: pgbouncer 1.8.1, libevent
2.0.21-stable (epoll), adns: evdns2, tls: OpenSSL 1.0.2k-fips  26 Jan 2017
2018-09-17 12:21:14.756 88424 DEBUG loading auth_file:
"/etc/pgbouncer/users.txt"
2018-09-17 12:21:48.917 88424 NOISE safe_accept(12) = 13 (unix:)
2018-09-17 12:21:48.917 88424 NOISE new fd from accept=13
2018-09-17 12:21:48.917 88424 WARNING tune_socket(13) failed: Operation not
supported
2018-09-17 12:21:48.917 88424 NOISE safe_close(13) = 0
2018-09-17 12:21:48.918 88424 NOISE safe_accept(12) = Resource temporarily
unavailable
2018-09-17 12:22:14.422 88424 LOG Stats: 0 xacts/s, 0 queries/s, in 0 B/s,
out 0 B/s, xact 0 us, query 0 us wait time 0 us
2018-09-17 12:23:14.423 88424 LOG Stats: 0 xacts/s, 0 queries/s, in 0 B/s,
out 0 B/s, xact 0 us, query 0 us wait time 0 us
2018-09-17 12:23:26.561 88424 LOG Got SIGINT, shutting down
2018-09-17 12:23:26.872 88424 LOG server connections dropped, exiting
2018-09-17 12:23:26.872 88424 NOISE safe_close(11) = 0
2018-09-17 12:23:26.872 88424 NOISE safe_close(12) = 0


Postgres version : 10.5

Libevent : 2.0.5

Pgbouncer : 1.8.1

OS : Centos 7


It used to work. Now after trying to install latest version of pgbouncer
(1.9.0) does not work anymore. Then I re-compiled the v 1.8.1



Content of the pgbouncer.ini

[databases]
DB_NAME = host=IP_ADDD port=5432 dbname=DB_NAME auth_user=pgbouncer
pool_size=120

[pgbouncer]
listen_port = 6543
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/users.txt
auth_query = select uname,phash from user_lookup($1)
logfile = /var/log/pgbouncer.log
pidfile = /home/postgres/pgbouncer.pid
admin_users = admin
user=postgres
max_db_connections = 220
log_connections=0
log_disconnections=0
verbose=3


Content of the users .txt

[root@cmd-dev1 pgbouncer]# cat users.txt
"pgbouncer" "***"


I tried to change the listen address to 127.0.0.1 or to the IP of the VM.
Nothing changed.


Can anyone help?


Thanks

Re: Pgbouncer and postgres

[Adrian Klaver]

On 9/17/18 5:33 AM, Nicola Contu wrote:

Hello,

I'm trying to get pgbouncer working but I'm getting this error :




What is generating the log below?

To me it looks like everything is on track until this:

2018-09-17 12:21:48.917 88424 WARNING tune_socket(13) failed: Operation 
not supported

2018-09-17 12:21:48.917 88424 NOISE safe_close(13) = 0
2018-09-17 12:21:48.918 88424 NOISE safe_accept(12) = Resource 
temporarily unavailable
2018-09-17 12:22:14.422 88424 LOG Stats: 0 xacts/s, 0 queries/s, in 0 
B/s, out 0 B/s, xact 0 us, query 0 us wait time 0 us
2018-09-17 12:23:14.423 88424 LOG Stats: 0 xacts/s, 0 queries/s, in 0 
B/s, out 0 B/s, xact 0 us, query 0 us wait time 0 us

2018-09-17 12:23:26.561 88424 LOG Got SIGINT, shutting down
2018-09-17 12:23:26.872 88424 LOG server connections dropped, exiting
2018-09-17 12:23:26.872 88424 NOISE safe_close(11) = 0
2018-09-17 12:23:26.872 88424 NOISE safe_close(12) = 0


Postgres version : 10.5

Libevent : 2.0.5

Pgbouncer : 1.8.1

OS : Centos 7


Have there been updates to CentOS during this?




It used to work. Now after trying to install latest version of pgbouncer 
(1.9.0) does not work anymore. Then I re-compiled the v 1.8.1


Did you compile 1.9.0 from source or install from package?





Content of the pgbouncer.ini

[databases]
DB_NAME = host=IP_ADDD port=5432 dbname=DB_NAME auth_user=pgbouncer 
pool_size=120


[pgbouncer]
listen_port = 6543
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/users.txt
auth_query = select uname,phash from user_lookup($1)
logfile = /var/log/pgbouncer.log
pidfile = /home/postgres/pgbouncer.pid
admin_users = admin
user=postgres
max_db_connections = 220
log_connections=0
log_disconnections=0
verbose=3


Content of the users .txt

[root@cmd-dev1 pgbouncer]# cat users.txt
"pgbouncer" "***"


I tried to change the listen address to 127.0.0.1 or to the IP of the 
VM. Nothing changed.



Can anyone help?


Thanks





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

Re: Pgbouncer and postgres

[Adrian Klaver]

On 9/17/18 7:05 AM, Nicola Contu wrote:

The log is from pgbouncer.

I installed updates after this problem, but they did not resolve anything.


I was thinking more of updates that where done between the last time it 
worked and the time it started failing.


Along that line, when was the last time it worked?



I compiled 1.9.0 from source on the same path of the previous one. Then 
back to 1.8.1 on the same path.


I also tried changing the listening port on pgbouncer, but that does not 
change too much.


Yeah, your log shows the port/socket coming up:

2018-09-17 12:21:14.422 88424 LOG listening on 127.0.0.1:6543
2018-09-17 12:21:14.422 88424 DEBUG add_listen: unix:/tmp/.s.PGSQL.6543
2018-09-17 12:21:14.422 88424 LOG listening on unix:/tmp/.s.PGSQL.6543
2018-09-17 12:21:14.423 88424 LOG process up: pgbouncer 1.8.1, libevent 
2.0.21-stable (epoll), adns: evdns2, tls: OpenSSL 1.0.2k-fips  26 Jan 2017


I can connect via telnet on that port, but we I try with anything else 
(like Apache) it makes pgbouncer crashing.


Are there log entries from that?




Il giorno lun 17 set 2018 alle ore 15:44 Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> ha scritto:


On 9/17/18 5:33 AM, Nicola Contu wrote:
 > Hello,
 >
 > I'm trying to get pgbouncer working but I'm getting this error :
 >
 >

What is generating the log below?

To me it looks like everything is on track until this:

 > 2018-09-17 12:21:48.917 88424 WARNING tune_socket(13) failed:
Operation
 > not supported
 > 2018-09-17 12:21:48.917 88424 NOISE safe_close(13) = 0
 > 2018-09-17 12:21:48.918 88424 NOISE safe_accept(12) = Resource
 > temporarily unavailable
 > 2018-09-17 12:22:14.422 88424 LOG Stats: 0 xacts/s, 0 queries/s,
in 0
 > B/s, out 0 B/s, xact 0 us, query 0 us wait time 0 us
 > 2018-09-17 12:23:14.423 88424 LOG Stats: 0 xacts/s, 0 queries/s,
in 0
 > B/s, out 0 B/s, xact 0 us, query 0 us wait time 0 us
 > 2018-09-17 12:23:26.561 88424 LOG Got SIGINT, shutting down
 > 2018-09-17 12:23:26.872 88424 LOG server connections dropped, exiting
 > 2018-09-17 12:23:26.872 88424 NOISE safe_close(11) = 0
 > 2018-09-17 12:23:26.872 88424 NOISE safe_close(12) = 0
 >
 >
 > Postgres version : 10.5
 >
 > Libevent : 2.0.5
 >
 > Pgbouncer : 1.8.1
 >
 > OS : Centos 7

Have there been updates to CentOS during this?

 >
 >
 > It used to work. Now after trying to install latest version of
pgbouncer
 > (1.9.0) does not work anymore. Then I re-compiled the v 1.8.1

Did you compile 1.9.0 from source or install from package?

 >
 >
 >
 > Content of the pgbouncer.ini
 >
 > [databases]
 > DB_NAME = host=IP_ADDD port=5432 dbname=DB_NAME auth_user=pgbouncer
 > pool_size=120
 >
 > [pgbouncer]
 > listen_port = 6543
 > listen_addr = *
 > auth_type = md5
 > auth_file = /etc/pgbouncer/users.txt
 > auth_query = select uname,phash from user_lookup($1)
 > logfile = /var/log/pgbouncer.log
 > pidfile = /home/postgres/pgbouncer.pid
 > admin_users = admin
 > user=postgres
 > max_db_connections = 220
 > log_connections=0
 > log_disconnections=0
 > verbose=3
 >
 >
 > Content of the users .txt
 >
 > [root@cmd-dev1 pgbouncer]# cat users.txt
 > "pgbouncer" "***"
 >
 >
 > I tried to change the listen address to 127.0.0.1 or to the IP of
the
 > VM. Nothing changed.
 >
 >
 > Can anyone help?
 >
 >
 > Thanks
 >
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Pgbouncer and postgres

[Nicola Contu]

I'm not aware of any update installed. There were like 600+ updates in fact.

These are the logs when connecting via telnet :

2018-09-17 14:18:18.933 65617 NOISE safe_accept(11) = 14 (10.151.2.145:39478
)
2018-09-17 14:18:18.933 65617 NOISE new fd from accept=14
2018-09-17 14:18:18.933 65617 NOISE safe_accept(11) = Resource temporarily
unavailable
2018-09-17 14:18:28.385 65617 NOISE safe_accept(12) = 15 (::1/49712)
2018-09-17 14:18:28.385 65617 NOISE new fd from accept=15
2018-09-17 14:18:28.385 65617 NOISE safe_accept(12) = Resource temporarily
unavailable
2018-09-17 14:18:30.703 65617 NOISE safe_accept(11) = 16 (10.151.2.145:39520
)
2018-09-17 14:18:30.703 65617 NOISE new fd from accept=16
2018-09-17 14:18:30.703 65617 NOISE safe_accept(11) = Resource temporarily
unavailable
2018-09-17 14:18:34.416 65617 NOISE safe_accept(11) = 17 (10.151.2.145:39530
)
2018-09-17 14:18:34.416 65617 NOISE new fd from accept=17
2018-09-17 14:18:34.416 65617 NOISE safe_accept(11) = Resource temporarily
unavailable

[root@cmd-dev1 pgbouncer]# telnet localhost 6543
Trying ::1...
Connected to localhost.
Escape character is '^]'.
Connection closed by foreign host.

Il giorno lun 17 set 2018 alle ore 16:11 Adrian Klaver <
adrian.kla...@aklaver.com> ha scritto:

> On 9/17/18 7:05 AM, Nicola Contu wrote:
> > The log is from pgbouncer.
> >
> > I installed updates after this problem, but they did not resolve
> anything.
>
> I was thinking more of updates that where done between the last time it
> worked and the time it started failing.
>
> Along that line, when was the last time it worked?
>
> >
> > I compiled 1.9.0 from source on the same path of the previous one. Then
> > back to 1.8.1 on the same path.
> >
> > I also tried changing the listening port on pgbouncer, but that does not
> > change too much.
>
> Yeah, your log shows the port/socket coming up:
>
> 2018-09-17 12:21:14.422 88424 LOG listening on 127.0.0.1:6543
> 2018-09-17 12:21:14.422 88424 DEBUG add_listen: unix:/tmp/.s.PGSQL.6543
> 2018-09-17 12:21:14.422 88424 LOG listening on unix:/tmp/.s.PGSQL.6543
> 2018-09-17 12:21:14.423 88424 LOG process up: pgbouncer 1.8.1, libevent
> 2.0.21-stable (epoll), adns: evdns2, tls: OpenSSL 1.0.2k-fips  26 Jan 2017
>
> > I can connect via telnet on that port, but we I try with anything else
> > (like Apache) it makes pgbouncer crashing.
>
> Are there log entries from that?
>
> >
> >
> > Il giorno lun 17 set 2018 alle ore 15:44 Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> ha
> scritto:
> >
> > On 9/17/18 5:33 AM, Nicola Contu wrote:
> >  > Hello,
> >  >
> >  > I'm trying to get pgbouncer working but I'm getting this error :
> >  >
> >  >
> >
> > What is generating the log below?
> >
> > To me it looks like everything is on track until this:
> >
> >  > 2018-09-17 12:21:48.917 88424 WARNING tune_socket(13) failed:
> > Operation
> >  > not supported
> >  > 2018-09-17 12:21:48.917 88424 NOISE safe_close(13) = 0
> >  > 2018-09-17 12:21:48.918 88424 NOISE safe_accept(12) = Resource
> >  > temporarily unavailable
> >  > 2018-09-17 12:22:14.422 88424 LOG Stats: 0 xacts/s, 0 queries/s,
> > in 0
> >  > B/s, out 0 B/s, xact 0 us, query 0 us wait time 0 us
> >  > 2018-09-17 12:23:14.423 88424 LOG Stats: 0 xacts/s, 0 queries/s,
> > in 0
> >  > B/s, out 0 B/s, xact 0 us, query 0 us wait time 0 us
> >  > 2018-09-17 12:23:26.561 88424 LOG Got SIGINT, shutting down
> >  > 2018-09-17 12:23:26.872 88424 LOG server connections dropped,
> exiting
> >  > 2018-09-17 12:23:26.872 88424 NOISE safe_close(11) = 0
> >  > 2018-09-17 12:23:26.872 88424 NOISE safe_close(12) = 0
> >  >
> >  >
> >  > Postgres version : 10.5
> >  >
> >  > Libevent : 2.0.5
> >  >
> >  > Pgbouncer : 1.8.1
> >  >
> >  > OS : Centos 7
> >
> > Have there been updates to CentOS during this?
> >
> >  >
> >  >
> >  > It used to work. Now after trying to install latest version of
> > pgbouncer
> >  > (1.9.0) does not work anymore. Then I re-compiled the v 1.8.1
> >
> > Did you compile 1.9.0 from source or install from package?
> >
> >  >
> >  >
> >  >
> >  > Content of the pgbouncer.ini
> >  >
> >  > [databases]
> >  > DB_NAME = host=IP_ADDD port=5432
> dbname=DB_NAME auth_user=pgbouncer
> >  > pool_size=120
> >  >
> >  > [pgbouncer]
> >  > listen_port = 6543
> >  > listen_addr = *
> >  > auth_type = md5
> >  > auth_file = /etc/pgbouncer/users.txt
> >  > auth_query = select uname,phash from user_lookup($1)
> >  > logfile = /var/log/pgbouncer.log
> >  > pidfile = /home/postgres/pgbouncer.pid
> >  > admin_users = admin
> >  > user=postgres
> >  > max_db_connections = 220
> >  > log_connections=0
> >  > log_disconnections=0
> >  > verbose=3
> >  >
> >  >
> >  > Content of the users .txt
> >  >
> >  > [root@cmd-dev1 pgboun

Re: postgresql api

[vyshu Ysh]

Hi,

I wanted to query from my c# application. Am using npgsql, is there any
parameter or api ?

On Mon, 17 Sep 2018, 5:22 pm Enrico Pirozzi,  wrote:

> Hi,
>
> Have you tried to query the pg_stat_replication?
>
>
> Enrico
>
>
> Il lun 17 set 2018, 11:52 vyshu Ysh  ha scritto:
>
>> Hi Team,
>>
>> Is there any postgresql API which tells when the master/standby server is
>> down ?
>>
>> Thanks,
>> Sudha
>>
>

Re: Pgbouncer and postgres

[Adrian Klaver]

On 9/17/18 7:19 AM, Nicola Contu wrote:

I'm not aware of any update installed. There were like 600+ updates in fact.

These are the logs when connecting via telnet :


How about those from when Apache tries to connect and pgbouncer crashes?


Connection closed by foreign host.



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

Re: Pgbouncer and postgres

[Nicola Contu]

[Mon Sep 17 10:24:59.041589 2018] [php7:notice] [pid 63893:tid
140586929567488] [client 10.160.41.3:51764] PHP WARNING: pg_connect():
Unable to connect to PostgreSQL server: could not connect to server:
Connection refused\n\tIs the server running on host "IP_ADDR" and
accepting\n\tTCP/IP connections on port 6543? in
/home/cmd3/adodb5/drivers/adodb-postgres64.inc.php on line 727

So basically the same I get when connecting via psql on port 6543

Il giorno lun 17 set 2018 alle ore 16:23 Adrian Klaver <
adrian.kla...@aklaver.com> ha scritto:

> On 9/17/18 7:19 AM, Nicola Contu wrote:
> > I'm not aware of any update installed. There were like 600+ updates in
> fact.
> >
> > These are the logs when connecting via telnet :
>
> How about those from when Apache tries to connect and pgbouncer crashes?
>
> > Connection closed by foreign host.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Pgbouncer and postgres

[Nicola Contu]

The log is from pgbouncer.

I installed updates after this problem, but they did not resolve anything.

I compiled 1.9.0 from source on the same path of the previous one. Then
back to 1.8.1 on the same path.

I also tried changing the listening port on pgbouncer, but that does not
change too much.
I can connect via telnet on that port, but we I try with anything else
(like Apache) it makes pgbouncer crashing.


Il giorno lun 17 set 2018 alle ore 15:44 Adrian Klaver <
adrian.kla...@aklaver.com> ha scritto:

> On 9/17/18 5:33 AM, Nicola Contu wrote:
> > Hello,
> >
> > I'm trying to get pgbouncer working but I'm getting this error :
> >
> >
>
> What is generating the log below?
>
> To me it looks like everything is on track until this:
>
> > 2018-09-17 12:21:48.917 88424 WARNING tune_socket(13) failed: Operation
> > not supported
> > 2018-09-17 12:21:48.917 88424 NOISE safe_close(13) = 0
> > 2018-09-17 12:21:48.918 88424 NOISE safe_accept(12) = Resource
> > temporarily unavailable
> > 2018-09-17 12:22:14.422 88424 LOG Stats: 0 xacts/s, 0 queries/s, in 0
> > B/s, out 0 B/s, xact 0 us, query 0 us wait time 0 us
> > 2018-09-17 12:23:14.423 88424 LOG Stats: 0 xacts/s, 0 queries/s, in 0
> > B/s, out 0 B/s, xact 0 us, query 0 us wait time 0 us
> > 2018-09-17 12:23:26.561 88424 LOG Got SIGINT, shutting down
> > 2018-09-17 12:23:26.872 88424 LOG server connections dropped, exiting
> > 2018-09-17 12:23:26.872 88424 NOISE safe_close(11) = 0
> > 2018-09-17 12:23:26.872 88424 NOISE safe_close(12) = 0
> >
> >
> > Postgres version : 10.5
> >
> > Libevent : 2.0.5
> >
> > Pgbouncer : 1.8.1
> >
> > OS : Centos 7
>
> Have there been updates to CentOS during this?
>
> >
> >
> > It used to work. Now after trying to install latest version of pgbouncer
> > (1.9.0) does not work anymore. Then I re-compiled the v 1.8.1
>
> Did you compile 1.9.0 from source or install from package?
>
> >
> >
> >
> > Content of the pgbouncer.ini
> >
> > [databases]
> > DB_NAME = host=IP_ADDD port=5432 dbname=DB_NAME auth_user=pgbouncer
> > pool_size=120
> >
> > [pgbouncer]
> > listen_port = 6543
> > listen_addr = *
> > auth_type = md5
> > auth_file = /etc/pgbouncer/users.txt
> > auth_query = select uname,phash from user_lookup($1)
> > logfile = /var/log/pgbouncer.log
> > pidfile = /home/postgres/pgbouncer.pid
> > admin_users = admin
> > user=postgres
> > max_db_connections = 220
> > log_connections=0
> > log_disconnections=0
> > verbose=3
> >
> >
> > Content of the users .txt
> >
> > [root@cmd-dev1 pgbouncer]# cat users.txt
> > "pgbouncer" "***"
> >
> >
> > I tried to change the listen address to 127.0.0.1 or to the IP of the
> > VM. Nothing changed.
> >
> >
> > Can anyone help?
> >
> >
> > Thanks
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: scram-sha-256 authentication broken in FIPS mode

[Alessandro Gherardi]

 Here's a patch for enabling FIPS in OpenSSL - by calling FIPS_mode_set(1) - on 
Windows if the FIPS registry entry 
HKLM\System\CurrentControlSet\Control\Lsa\FipsAlgorithmPolicy\Enabled is set to 
1. That's the entry that the .NET crypto libraries look at to decide whether or 
not to operate in FIPS mode.
I thought of submitting a pull request for adding this logic to OpenSSL, 
however (1) https://www.openssl.org/source/ says they are working on a new FIPS 
module, so I doubt they would take a pull request for OpenSSL 1.0.X and (2) For 
Linux, this logic doesn't exist on the standard OpenSSL distribution but only 
on the RHEL-specific OpenSSL patch (See method init_fips_mode() in 
https://git.centos.org/raw/rpms/openssl.git/c7/SOURCES!openssl-1.0.2i-fips.patch
 ).
Therefore, I believe the best option, at least for now, is calling 
FIPS_mode_set(1) in the application.




0001-On-Windows-call-FIPS_mode_set-1-if-FIPS-registry-ent.patch
Description: Binary data

Re: Logical locking beyond pg_advisory

[Merlin Moncure]

On Sun, Sep 16, 2018 at 3:53 PM marcelo  wrote:
>
> I need a mechanism of "logical locking" more ductile than the pg_advisory 
> family.
> I'm thinking of a table ("lock_table") that would be part of the database, 
> with columns
> * tablename varchar - name of the table "locked"
> * rowid integer, - id of the row "locked"
> * ownerid varchar, - identifier of the "user" who acquired the lock
> * acquired timestamp - to be able to release "abandoned" locks after a 
> certain time
>
> and a group of functions
> 1) lock_table (tablename varchar, ownerid varchar) bool - get to lock over 
> the entire table, setting rowid to zero
> 2) unlock_table (tablename varchar, ownerid varchar) bool - unlock the table, 
> if the owner is the recorded one
> 3) locked_table (tablename varchar, ownerid varchar) bool - ask if the table 
> is locked by some user other than the ownerid argument
> 4) lock_row (tablename varchar, rowid integer, ownerid varchar) bool - 
> similar to pg_try_advisory_lock
> 5) unlock_row (tablename varchar, rowid integer, ownerid varchar) bool - 
> similar to pg_advisory_unlock
> 6) unlock_all (ownerid varchar) bool - unlock all locks owned by ownerid
>
> The timeout (default, maybe 15 minutes) is implicitly applied if the lock is 
> taken by another user (there will be no notification).
> Redundant locks are not queued, they simply return true, may be after an 
> update of the acquired column.
> Successful locks insert a new row, except the rare case of a timeout, which 
> becomes an update (ownerid and acquired)
> Unlock operations deletes the corresponding row
>
> My question is double
> a) What is the opinion on the project?
> b) What are the consequences of the large number of inserts and deletions
> c) Performance. In fact, pg_advisory* implies a network roundtrip, but (I 
> think) no table operations.

Why can't you use the advisory lock functions?  The challenge with
manually managed locks are they they are slow and you will lose the
coordination the database provides you.  For example, if your
application crashes you will have to clean up all held locks yourself.
Building out that infrastructure will be difficult.

merlin

Re: Code of Conduct plan

[Dmitri Maziuk]

On Sun, 16 Sep 2018 12:52:34 +
Martin Mueller  wrote:

> ... The overreach is dubious on both practical and theoretical grounds. 
> "Stick to your knitting " or the KISS principle seem good advice in this 
> context. 

Moderated mailing lists ain't been broken all these years, therefore they need 
fixing. Obviously.

-- 
Dmitri Maziuk 

Re: Logical locking beyond pg_advisory

[Chris Travers]

On Mon, Sep 17, 2018 at 5:09 PM Merlin Moncure  wrote:

> On Sun, Sep 16, 2018 at 3:53 PM marcelo  wrote:
> >
> > I need a mechanism of "logical locking" more ductile than the
> pg_advisory family.
> > I'm thinking of a table ("lock_table") that would be part of the
> database, with columns
> > * tablename varchar - name of the table "locked"
> > * rowid integer, - id of the row "locked"
> > * ownerid varchar, - identifier of the "user" who acquired the lock
> > * acquired timestamp - to be able to release "abandoned" locks after a
> certain time
> >
> > and a group of functions
> > 1) lock_table (tablename varchar, ownerid varchar) bool - get to lock
> over the entire table, setting rowid to zero
> > 2) unlock_table (tablename varchar, ownerid varchar) bool - unlock the
> table, if the owner is the recorded one
> > 3) locked_table (tablename varchar, ownerid varchar) bool - ask if the
> table is locked by some user other than the ownerid argument
> > 4) lock_row (tablename varchar, rowid integer, ownerid varchar) bool -
> similar to pg_try_advisory_lock
> > 5) unlock_row (tablename varchar, rowid integer, ownerid varchar) bool -
> similar to pg_advisory_unlock
> > 6) unlock_all (ownerid varchar) bool - unlock all locks owned by ownerid
> >
> > The timeout (default, maybe 15 minutes) is implicitly applied if the
> lock is taken by another user (there will be no notification).
> > Redundant locks are not queued, they simply return true, may be after an
> update of the acquired column.
> > Successful locks insert a new row, except the rare case of a timeout,
> which becomes an update (ownerid and acquired)
> > Unlock operations deletes the corresponding row
> >
> > My question is double
> > a) What is the opinion on the project?
> > b) What are the consequences of the large number of inserts and deletions
> > c) Performance. In fact, pg_advisory* implies a network roundtrip, but
> (I think) no table operations.
>
> Why can't you use the advisory lock functions?  The challenge with
> manually managed locks are they they are slow and you will lose the
> coordination the database provides you.  For example, if your
> application crashes you will have to clean up all held locks yourself.
> Building out that infrastructure will be difficult.
>

First, I think in an ideal world, you wouldn't handle this problem with
either approach but sometimes you have to.

I have done both approaches actually.  LedgerSMB uses its own lock table
because locks have to persist across multiple HTTP requests and we have
various automatic cleanup processes.

When I was working on  the queue management stuff at Novozymes we used
advisory locks extensively.

These two approaches have serious downsides:
1.  Lock tables are *slow* and require careful thinking through cleanup
scenarios.  In LedgerSMB we tied to the application session with an ON
DELETE event that would unlock the row.  We estimated that for every 2
seconds that the db spent doing useful work, it spent 42 seconds managing
the locks.  Additionally the fact that locks take effect on snapshot
advance is a problem here.

2.  In my talk, "PostgreSQL at 10TB and  Beyond" I talk about a problem we
had using advisory locks for managing rows that were being processed for
deletion.  Since the deletion was the scan for items at the head of an
index, under heavy load we could spend long enough checking dead rows that
the locks could go away with our snapshot failing to advance.  This would
result in duplicate processing.  So the fact that advisory locks don't
really follow snapshot semantics is a really big problem here since it
means you can have race conditions in advisory locks that can't happen with
other locking issues.  I still love advisory locks but they are not a good
tool for this.

The real solution most of the time is actually to lock the rows by
selecting FOR UPDATE and possibly SKIP LOCKED.  The way update/delete row
locking in PostgreSQL works is usually good enough except in a few rare
edge cases.  Only in *very rare* cases do lock tables or advisory locks
make sense for actual row processing.

>
> merlin
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Joshua D. Drake]

On 09/17/2018 08:11 AM, Dmitri Maziuk wrote:

On Sun, 16 Sep 2018 12:52:34 +
Martin Mueller  wrote:


... The overreach is dubious on both practical and theoretical grounds. "Stick to 
your knitting " or the KISS principle seem good advice in this context.

Moderated mailing lists ain't been broken all these years, therefore they need 
fixing. Obviously.


Folks,

At this point it is important to accept that the CoC is happening. We 
aren't going to stop that. The goal now is to insure a CoC that is 
equitable for all community members and that has appropriate 
accountability. At hand it appears that major concern is the CoC trying 
to be authoritative outside of community channels. As well as wording 
that is a bit far reaching. Specifically I think people's main concern 
is these two sentences:


"To that end, we have established this Code of Conduct for community 
interaction and participation in the project’s work and the community at 
large. This Code is meant to cover all interaction between community 
members, whether or not it takes place within postgresql.org 
infrastructure, so long as there is not another Code of Conduct that 
takes precedence (such as a conference's Code of Conduct)."


If we can constructively provide feedback about those two sentences, 
great (or constructive feedback on other areas of the CoC). If we can't 
then this thread needs to stop. It has become unproductive.


My feedback is that those two sentences provide an overarching authority 
that .Org does not have the right to enforce and that it is also largely 
redundant because we allow that the idea that if another CoC exists, 
then ours doesn't apply. Well every single major collaboration channel 
we would be concerned with (including something like Blogger) has its 
own CoC within its Terms of use. That effectively neuters the PostgreSQL 
CoC within places like Slack, Facebook, Twitter etc...


JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Pgbouncer and postgres

[Adrian Klaver]

On 9/17/18 7:26 AM, Nicola Contu wrote:
[Mon Sep 17 10:24:59.041589 2018] [php7:notice] [pid 63893:tid 
140586929567488] [client 10.160.41.3:51764 ] 
PHP WARNING: pg_connect(): Unable to connect to PostgreSQL server: could 
not connect to server: Connection refused\n\tIs the server running on 
host "IP_ADDR" and accepting\n\tTCP/IP connections on port 
6543? in /home/cmd3/adodb5/drivers/adodb-postgres64.inc.php on line 727


To me it looks like IP_ADDR is not resolving to a host.

In your pgbouncer.ini I see:

DB_NAME = host=IP_ADDD port=5432 dbname=DB_NAME auth_user=pgbouncer 
pool_size=120


but the error is about connecting to port 6543 which is your pgbouncer 
'server'.


Are you sure your connection string is pointing at the correct thing?



So basically the same I get when connecting via psql on port 6543


What is the full psql command?



Il giorno lun 17 set 2018 alle ore 16:23 Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> ha scritto:


On 9/17/18 7:19 AM, Nicola Contu wrote:
 > I'm not aware of any update installed. There were like 600+
updates in fact.
 >
 > These are the logs when connecting via telnet :

How about those from when Apache tries to connect and pgbouncer crashes?

 > Connection closed by foreign host.


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Code of Conduct plan

[Chris Travers]

On Mon, Sep 17, 2018 at 5:28 PM Joshua D. Drake 
wrote:

> On 09/17/2018 08:11 AM, Dmitri Maziuk wrote:
>
> On Sun, 16 Sep 2018 12:52:34 +
> Martin Mueller  
>  wrote:
>
>
> ... The overreach is dubious on both practical and theoretical grounds. 
> "Stick to your knitting " or the KISS principle seem good advice in this 
> context.
>
> Moderated mailing lists ain't been broken all these years, therefore they 
> need fixing. Obviously.
>
>
> Folks,
>
> At this point it is important to accept that the CoC is happening. We
> aren't going to stop that. The goal now is to insure a CoC that is
> equitable for all community members and that has appropriate
> accountability. At hand it appears that major concern is the CoC trying to
> be authoritative outside of community channels. As well as wording that is
> a bit far reaching. Specifically I think people's main concern is these two
> sentences:
>
> "To that end, we have established this Code of Conduct for community
> interaction and participation in the project’s work and the community at
> large. This Code is meant to cover all interaction between community
> members, whether or not it takes place within postgresql.org
> infrastructure, so long as there is not another Code of Conduct that takes
> precedence (such as a conference's Code of Conduct)."
>

Exactly.  And actually the first sentence is not new.  The second one is a
real problem though.  I am going to try one last time at an additional
alternative.

" To that end, we have established this Code of Conduct for community
interaction and participation in the project’s work and the community at
large.   This code of conduct covers all interaction between community
members on the postgresql.org infrastructure.  Conduct outside the
postgresql.org infrastructure may call the Code of Conduct committee to act
as long as the interaction (or interaction pattern) is community-related,
other parties are unable to act, and the Code of Conduct committee
determines that it is in the best interest of the community to apply this
Code of Conduct."

This solves a number of important problems.

1.  It provides a backstop (as Tom Lane suggested was needed) against a
conference refusing to enforce their own code of conduct in a way the
community finds acceptable while the current wording does not provide any
backstop as long as there is a code of conduct for a conference.
2.  It provides a significant barrier to applying the code of conduct to,
say, political posts on, say, Twitter.
3.  It preserves the ability of the Code of Conduct Committee to act in the
case where someone takes a pattern of harassment off-list and
off-infrastructure.  And it avoids arguing whether Facebook's Community
Standards constitute "another Code of Conduct that takes precedence."

>
> If we can constructively provide feedback about those two sentences, great
> (or constructive feedback on other areas of the CoC). If we can't then this
> thread needs to stop. It has become unproductive.
>
> My feedback is that those two sentences provide an overarching authority
> that .Org does not have the right to enforce and that it is also largely
> redundant because we allow that the idea that if another CoC exists, then
> ours doesn't apply. Well every single major collaboration channel we would
> be concerned with (including something like Blogger) has its own CoC within
> its Terms of use. That effectively neuters the PostgreSQL CoC within places
> like Slack, Facebook, Twitter etc...
>

Fascinating that this would, on its face, not apply to a harassment
campaign carried out over twitter, but it would apply to a few comments
made over drinks at a bar.

>
> JD
>
> --
> Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
> ***  A fault and talent of mine is to tell it exactly how it is.  ***
> PostgreSQL centered full stack support, consulting and development.
> Advocate: @amplifypostgres || Learn: https://postgresconf.org
> * Unless otherwise stated, opinions are my own.   *
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Pgbouncer and postgres

[Adrian Klaver]

On 9/17/18 8:39 AM, Nicola Contu wrote:
Please reply to list also.
Ccing list.

No wait, IP_ADDR has been modified by me, but there is the IP of the 
machine 10.151.x.


To be clear in your posts you are replacing the actual IP with IP_ADDR, 
correct?





Here is the psql command .

[root@cmd-dev1 ~]# psql -U ncontu -p 6543 cmd3dev
psql: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.


So what does the pgbouncer log show when you do above?






Il giorno lun 17 set 2018 alle ore 17:35 Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> ha scritto:


On 9/17/18 7:26 AM, Nicola Contu wrote:
 > [Mon Sep 17 10:24:59.041589 2018] [php7:notice] [pid 63893:tid
 > 140586929567488] [client 10.160.41.3:51764
 ]
 > PHP WARNING: pg_connect(): Unable to connect to PostgreSQL
server: could
 > not connect to server: Connection refused\n\tIs the server
running on
 > host "IP_ADDR" and accepting\n\tTCP/IP connections on port
 > 6543? in /home/cmd3/adodb5/drivers/adodb-postgres64.inc.php on
line 727

To me it looks like IP_ADDR is not resolving to a host.

In your pgbouncer.ini I see:

DB_NAME = host=IP_ADDD port=5432 dbname=DB_NAME auth_user=pgbouncer
pool_size=120

but the error is about connecting to port 6543 which is your pgbouncer
'server'.

Are you sure your connection string is pointing at the correct thing?

 >
 > So basically the same I get when connecting via psql on port 6543

What is the full psql command?

 >
 > Il giorno lun 17 set 2018 alle ore 16:23 Adrian Klaver
 > mailto:adrian.kla...@aklaver.com>
>> ha scritto:
 >
 >     On 9/17/18 7:19 AM, Nicola Contu wrote:
 >      > I'm not aware of any update installed. There were like 600+
 >     updates in fact.
 >      >
 >      > These are the logs when connecting via telnet :
 >
 >     How about those from when Apache tries to connect and
pgbouncer crashes?
 >
 >      > Connection closed by foreign host.
 >
 >
 >     --
 >     Adrian Klaver
 > adrian.kla...@aklaver.com 
>
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Code of Conduct plan

[Steve Litt]

On Mon, 17 Sep 2018 08:27:48 -0700
"Joshua D. Drake"  wrote:

> On 09/17/2018 08:11 AM, Dmitri Maziuk wrote:
> > On Sun, 16 Sep 2018 12:52:34 +
> > Martin Mueller  wrote:
> >  
> >> ... The overreach is dubious on both practical and theoretical
> >> grounds. "Stick to your knitting " or the KISS principle seem good
> >> advice in this context.  
> > Moderated mailing lists ain't been broken all these years,
> > therefore they need fixing. Obviously.  
> 
> Folks,
> 
> At this point it is important to accept that the CoC is happening. We 
> aren't going to stop that. The goal now is to insure a CoC that is 
> equitable for all community members and that has appropriate 
> accountability. At hand it appears that major concern is the CoC
> trying to be authoritative outside of community channels. As well as
> wording that is a bit far reaching. Specifically I think people's
> main concern is these two sentences:
> 
> "To that end, we have established this Code of Conduct for community 
> interaction and participation in the project’s work and the community
> at large. This Code is meant to cover all interaction between
> community members, whether or not it takes place within
> postgresql.org infrastructure, so long as there is not another Code
> of Conduct that takes precedence (such as a conference's Code of
> Conduct)."
> 
> If we can constructively provide feedback about those two sentences, 
> great (or constructive feedback on other areas of the CoC). If we
> can't then this thread needs to stop. It has become unproductive.
> 
> My feedback is that those two sentences provide an overarching
> authority that .Org does not have the right to enforce and that it is
> also largely redundant because we allow that the idea that if another
> CoC exists, then ours doesn't apply. Well every single major
> collaboration channel we would be concerned with (including something
> like Blogger) has its own CoC within its Terms of use. That
> effectively neuters the PostgreSQL CoC within places like Slack,
> Facebook, Twitter etc...

The perfect is the enemy of the good. Whatever CoC is decided upon, it
will be updated later. If it's easier, for now, to pass it with
enforcement WITHIN the Postgres community, why not do that? If, later
on, we get instances of people retaliating, in other venues, for
positions taken in Postgres, that can be handled when it comes up.

SteveT

Steve Litt 
September 2018 featured book: Quit Joblessness: Start Your Own Business
http://www.troubleshooters.com/startbiz

Re: Pgbouncer and postgres

[Nicola Contu]

Yeah, I replaced the IP of the machine with the word IP_ADDR.

On the pgbouncer log I get this while trying to connect via psql:

2018-09-17 15:56:49.452 67611 WARNING tune_socket(16) failed: Operation not
supported
2018-09-17 15:56:49.452 67611 NOISE safe_close(16) = 0
2018-09-17 15:56:49.452 67611 NOISE safe_accept(13) = Resource temporarily
unavailable
2018-09-17 15:56:50.728 67611 NOISE safe_accept(13) = 16 (unix:)
2018-09-17 15:56:50.729 67611 NOISE new fd from accept=16
2018-09-17 15:56:50.729 67611 WARNING tune_socket(16) failed: Operation not
supported
2018-09-17 15:56:50.729 67611 NOISE safe_close(16) = 0
2018-09-17 15:56:50.729 67611 NOISE safe_accept(13) = Resource temporarily
unavailable
2018-09-17 15:56:56.418 67611 NOISE safe_accept(11) = 16 (10.151.2.145:58696
)
2018-09-17 15:56:56.418 67611 NOISE new fd from accept=16
2018-09-17 15:56:56.418 67611 NOISE safe_accept(11) = Resource temporarily
unavailable


Il giorno lun 17 set 2018 alle ore 17:46 Adrian Klaver <
adrian.kla...@aklaver.com> ha scritto:

> On 9/17/18 8:39 AM, Nicola Contu wrote:
> Please reply to list also.
> Ccing list.
>
> > No wait, IP_ADDR has been modified by me, but there is the IP of the
> > machine 10.151.x.
>
> To be clear in your posts you are replacing the actual IP with IP_ADDR,
> correct?
>
> >
> >
> > Here is the psql command .
> >
> > [root@cmd-dev1 ~]# psql -U ncontu -p 6543 cmd3dev
> > psql: server closed the connection unexpectedly
> > This probably means the server terminated abnormally
> > before or while processing the request.
>
> So what does the pgbouncer log show when you do above?
>
> >
> >
> >
> >
> > Il giorno lun 17 set 2018 alle ore 17:35 Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> ha
> scritto:
> >
> > On 9/17/18 7:26 AM, Nicola Contu wrote:
> >  > [Mon Sep 17 10:24:59.041589 2018] [php7:notice] [pid 63893:tid
> >  > 140586929567488] [client 10.160.41.3:51764
> >  ]
> >  > PHP WARNING: pg_connect(): Unable to connect to PostgreSQL
> > server: could
> >  > not connect to server: Connection refused\n\tIs the server
> > running on
> >  > host "IP_ADDR" and accepting\n\tTCP/IP connections on
> port
> >  > 6543? in /home/cmd3/adodb5/drivers/adodb-postgres64.inc.php on
> > line 727
> >
> > To me it looks like IP_ADDR is not resolving to a host.
> >
> > In your pgbouncer.ini I see:
> >
> > DB_NAME = host=IP_ADDD port=5432 dbname=DB_NAME auth_user=pgbouncer
> > pool_size=120
> >
> > but the error is about connecting to port 6543 which is your
> pgbouncer
> > 'server'.
> >
> > Are you sure your connection string is pointing at the correct thing?
> >
> >  >
> >  > So basically the same I get when connecting via psql on port 6543
> >
> > What is the full psql command?
> >
> >  >
> >  > Il giorno lun 17 set 2018 alle ore 16:23 Adrian Klaver
> >  > mailto:adrian.kla...@aklaver.com>
> >  > >> ha scritto:
> >  >
> >  > On 9/17/18 7:19 AM, Nicola Contu wrote:
> >  >  > I'm not aware of any update installed. There were like 600+
> >  > updates in fact.
> >  >  >
> >  >  > These are the logs when connecting via telnet :
> >  >
> >  > How about those from when Apache tries to connect and
> > pgbouncer crashes?
> >  >
> >  >  > Connection closed by foreign host.
> >  >
> >  >
> >  > --
> >  > Adrian Klaver
> >  > adrian.kla...@aklaver.com 
> >  >>
> >  >
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Logical locking beyond pg_advisory

[marcelo]

On 17/09/2018 12:21 , Chris Travers wrote:



On Mon, Sep 17, 2018 at 5:09 PM Merlin Moncure > wrote:


On Sun, Sep 16, 2018 at 3:53 PM marcelo mailto:marcelo.nico...@gmail.com>> wrote:
>
> I need a mechanism of "logical locking" more ductile than the
pg_advisory family.
> I'm thinking of a table ("lock_table") that would be part of the
database, with columns
> * tablename varchar - name of the table "locked"
> * rowid integer, - id of the row "locked"
> * ownerid varchar, - identifier of the "user" who acquired the lock
> * acquired timestamp - to be able to release "abandoned" locks
after a certain time
>
> and a group of functions
> 1) lock_table (tablename varchar, ownerid varchar) bool - get to
lock over the entire table, setting rowid to zero
> 2) unlock_table (tablename varchar, ownerid varchar) bool -
unlock the table, if the owner is the recorded one
> 3) locked_table (tablename varchar, ownerid varchar) bool - ask
if the table is locked by some user other than the ownerid argument
> 4) lock_row (tablename varchar, rowid integer, ownerid varchar)
bool - similar to pg_try_advisory_lock
> 5) unlock_row (tablename varchar, rowid integer, ownerid
varchar) bool - similar to pg_advisory_unlock
> 6) unlock_all (ownerid varchar) bool - unlock all locks owned by
ownerid
>
> The timeout (default, maybe 15 minutes) is implicitly applied if
the lock is taken by another user (there will be no notification).
> Redundant locks are not queued, they simply return true, may be
after an update of the acquired column.
> Successful locks insert a new row, except the rare case of a
timeout, which becomes an update (ownerid and acquired)
> Unlock operations deletes the corresponding row
>
> My question is double
> a) What is the opinion on the project?
> b) What are the consequences of the large number of inserts and
deletions
> c) Performance. In fact, pg_advisory* implies a network
roundtrip, but (I think) no table operations.

Why can't you use the advisory lock functions?  The challenge with
manually managed locks are they they are slow and you will lose the
coordination the database provides you.  For example, if your
application crashes you will have to clean up all held locks yourself.
Building out that infrastructure will be difficult.


First, I think in an ideal world, you wouldn't handle this problem 
with either approach but sometimes you have to.


I have done both approaches actually.  LedgerSMB uses its own lock 
table because locks have to persist across multiple HTTP requests and 
we have various automatic cleanup processes.


When I was working on  the queue management stuff at Novozymes we used 
advisory locks extensively.


These two approaches have serious downsides:
1.  Lock tables are *slow* and require careful thinking through 
cleanup scenarios.  In LedgerSMB we tied to the application session 
with an ON DELETE event that would unlock the row.  We estimated that 
for every 2 seconds that the db spent doing useful work, it spent 42 
seconds managing the locks.  Additionally the fact that locks take 
effect on snapshot advance is a problem here.


2.  In my talk, "PostgreSQL at 10TB and  Beyond" I talk about a 
problem we had using advisory locks for managing rows that were being 
processed for deletion.  Since the deletion was the scan for items at 
the head of an index, under heavy load we could spend long enough 
checking dead rows that the locks could go away with our snapshot 
failing to advance.  This would result in duplicate processing.  So 
the fact that advisory locks don't really follow snapshot semantics is 
a really big problem here since it means you can have race conditions 
in advisory locks that can't happen with other locking issues.  I 
still love advisory locks but they are not a good tool for this.


The real solution most of the time is actually to lock the rows by 
selecting FOR UPDATE and possibly SKIP LOCKED. The way update/delete 
row locking in PostgreSQL works is usually good enough except in a few 
rare edge cases.  Only in *very rare* cases do lock tables or advisory 
locks make sense for actual row processing.



merlin



--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor 
lock-in.

http://www.efficito.com/learn_more
I´m using an ORM (Devart´s) to access the database, so, I cannot "select 
... FOR UPDATE". The application paradigm is that a user have a list of 
records (after a query) and she could update or delete any of them as 
the business rules allows it. So, at least an advisory lock is a must.
I´m convinced by now: I would stay with advisory locks... expecting no 
app crash could occur...

Thank you all.
Marcelo


---
El software de antivirus Avast ha analizado este correo electrónico en busca 

Re: Pgbouncer and postgres

[Adrian Klaver]

On 9/17/18 8:59 AM, Nicola Contu wrote:

Yeah, I replaced the IP of the machine with the word IP_ADDR.

On the pgbouncer log I get this while trying to connect via psql:

2018-09-17 15:56:49.452 67611 WARNING tune_socket(16) failed: Operation 
not supported

2018-09-17 15:56:49.452 67611 NOISE safe_close(16) = 0
2018-09-17 15:56:49.452 67611 NOISE safe_accept(13) = Resource 
temporarily unavailable

2018-09-17 15:56:50.728 67611 NOISE safe_accept(13) = 16 (unix:)
2018-09-17 15:56:50.729 67611 NOISE new fd from accept=16
2018-09-17 15:56:50.729 67611 WARNING tune_socket(16) failed: Operation 
not supported

2018-09-17 15:56:50.729 67611 NOISE safe_close(16) = 0
2018-09-17 15:56:50.729 67611 NOISE safe_accept(13) = Resource 
temporarily unavailable
2018-09-17 15:56:56.418 67611 NOISE safe_accept(11) = 16 
(10.151.2.145:58696 )

2018-09-17 15:56:56.418 67611 NOISE new fd from accept=16
2018-09-17 15:56:56.418 67611 NOISE safe_accept(11) = Resource 
temporarily unavailable


Can you connect to the actual database running on port 5432?

Deciphering the above log is beyond my capabilities. I think it is time 
to file an issue here:


https://github.com/pgbouncer/pgbouncer/issues



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

Re: Code of Conduct plan

[Steve Litt]

On Mon, 17 Sep 2018 17:39:20 +0200
Chris Travers  wrote:


> Exactly.  And actually the first sentence is not new.  The second one
> is a real problem though.  I am going to try one last time at an
> additional alternative.
> 
> " To that end, we have established this Code of Conduct for community
> interaction and participation in the project’s work and the community
> at large.   This code of conduct covers all interaction between
> community members on the postgresql.org infrastructure.  Conduct
> outside the postgresql.org infrastructure may call the Code of
> Conduct committee to act as long as the interaction (or interaction
> pattern) is community-related, other parties are unable to act, and
> the Code of Conduct committee determines that it is in the best
> interest of the community to apply this Code of Conduct."

Chris,

Would you be satisfied with the CoC if the current 2nd paragraph of the
Introduction were replaced by the paragraph you wrote above?

 
SteveT

Steve Litt 
September 2018 featured book: Quit Joblessness: Start Your Own Business
http://www.troubleshooters.com/startbiz

Re: Pgbouncer and postgres

[Nicola Contu]

Yes I can connect to port 5432 without any problem.

The thing is that I am not seeing anything on the postgres log when
connecting to 6543.

Sure, thanks for your help.
I will create an issue there.


Il giorno lun 17 set 2018 alle ore 18:07 Adrian Klaver <
adrian.kla...@aklaver.com> ha scritto:

> On 9/17/18 8:59 AM, Nicola Contu wrote:
> > Yeah, I replaced the IP of the machine with the word IP_ADDR.
> >
> > On the pgbouncer log I get this while trying to connect via psql:
> >
> > 2018-09-17 15:56:49.452 67611 WARNING tune_socket(16) failed: Operation
> > not supported
> > 2018-09-17 15:56:49.452 67611 NOISE safe_close(16) = 0
> > 2018-09-17 15:56:49.452 67611 NOISE safe_accept(13) = Resource
> > temporarily unavailable
> > 2018-09-17 15:56:50.728 67611 NOISE safe_accept(13) = 16 (unix:)
> > 2018-09-17 15:56:50.729 67611 NOISE new fd from accept=16
> > 2018-09-17 15:56:50.729 67611 WARNING tune_socket(16) failed: Operation
> > not supported
> > 2018-09-17 15:56:50.729 67611 NOISE safe_close(16) = 0
> > 2018-09-17 15:56:50.729 67611 NOISE safe_accept(13) = Resource
> > temporarily unavailable
> > 2018-09-17 15:56:56.418 67611 NOISE safe_accept(11) = 16
> > (10.151.2.145:58696 )
> > 2018-09-17 15:56:56.418 67611 NOISE new fd from accept=16
> > 2018-09-17 15:56:56.418 67611 NOISE safe_accept(11) = Resource
> > temporarily unavailable
>
> Can you connect to the actual database running on port 5432?
>
> Deciphering the above log is beyond my capabilities. I think it is time
> to file an issue here:
>
> https://github.com/pgbouncer/pgbouncer/issues
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: postgresql api

[Raymond O'Donnell]

On 17/09/18 13:31, vyshu Ysh wrote:

Hi,

I wanted to query from my c# application. Am using npgsql, is there any 
parameter or api ?


select * from pg_stat_replication;

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

Re: Pgbouncer and postgres

[Adrian Klaver]

On 9/17/18 9:09 AM, Nicola Contu wrote:

Yes I can connect to port 5432 without any problem.

The thing is that I am not seeing anything on the postgres log when 
connecting to 6543.


Pretty sure you won't as 6543 is controlled by pgbouncer and pgbouncer 
seems to be failing before it gets around to connecting to the actual 
database.




Sure, thanks for your help.
I will create an issue there.


Il giorno lun 17 set 2018 alle ore 18:07 Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> ha scritto:


On 9/17/18 8:59 AM, Nicola Contu wrote:
 > Yeah, I replaced the IP of the machine with the word IP_ADDR.
 >
 > On the pgbouncer log I get this while trying to connect via psql:
 >
 > 2018-09-17 15:56:49.452 67611 WARNING tune_socket(16) failed:
Operation
 > not supported
 > 2018-09-17 15:56:49.452 67611 NOISE safe_close(16) = 0
 > 2018-09-17 15:56:49.452 67611 NOISE safe_accept(13) = Resource
 > temporarily unavailable
 > 2018-09-17 15:56:50.728 67611 NOISE safe_accept(13) = 16 (unix:)
 > 2018-09-17 15:56:50.729 67611 NOISE new fd from accept=16
 > 2018-09-17 15:56:50.729 67611 WARNING tune_socket(16) failed:
Operation
 > not supported
 > 2018-09-17 15:56:50.729 67611 NOISE safe_close(16) = 0
 > 2018-09-17 15:56:50.729 67611 NOISE safe_accept(13) = Resource
 > temporarily unavailable
 > 2018-09-17 15:56:56.418 67611 NOISE safe_accept(11) = 16
 > (10.151.2.145:58696 
)
 > 2018-09-17 15:56:56.418 67611 NOISE new fd from accept=16
 > 2018-09-17 15:56:56.418 67611 NOISE safe_accept(11) = Resource
 > temporarily unavailable

Can you connect to the actual database running on port 5432?

Deciphering the above log is beyond my capabilities. I think it is time
to file an issue here:

https://github.com/pgbouncer/pgbouncer/issues



-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Code of Conduct plan

[Steve Atkins]

> On Sep 17, 2018, at 4:57 PM, Steve Litt  wrote:
> 
> On Mon, 17 Sep 2018 08:27:48 -0700
> "Joshua D. Drake"  wrote:
> 
>> 
>> At this point it is important to accept that the CoC is happening. We 
>> aren't going to stop that. The goal now is to insure a CoC that is 
>> equitable for all community members and that has appropriate 
>> accountability. At hand it appears that major concern is the CoC
>> trying to be authoritative outside of community channels. As well as
>> wording that is a bit far reaching. Specifically I think people's
>> main concern is these two sentences:
>> 
>> "To that end, we have established this Code of Conduct for community 
>> interaction and participation in the project’s work and the community
>> at large. This Code is meant to cover all interaction between
>> community members, whether or not it takes place within
>> postgresql.org infrastructure, so long as there is not another Code
>> of Conduct that takes precedence (such as a conference's Code of
>> Conduct)."
>> 
>> If we can constructively provide feedback about those two sentences, 
>> great (or constructive feedback on other areas of the CoC). If we
>> can't then this thread needs to stop. It has become unproductive.
>> 
>> My feedback is that those two sentences provide an overarching
>> authority that .Org does not have the right to enforce and that it is
>> also largely redundant because we allow that the idea that if another
>> CoC exists, then ours doesn't apply. Well every single major
>> collaboration channel we would be concerned with (including something
>> like Blogger) has its own CoC within its Terms of use. That
>> effectively neuters the PostgreSQL CoC within places like Slack,
>> Facebook, Twitter etc...
> 
> The perfect is the enemy of the good. Whatever CoC is decided upon, it
> will be updated later. If it's easier, for now, to pass it with
> enforcement WITHIN the Postgres community, why not do that? If, later
> on, we get instances of people retaliating, in other venues, for
> positions taken in Postgres, that can be handled when it comes up.

I'll note that a fairly common situation with mailing lists I've seen is people
taking an on-list disagreement off-list and being offensive there. I've not
had that happen to me personally on the pgsql-* lists, but I have had it
happen on other technical mailing lists. That harassment would be "outside
of community channels".

A CoC that doesn't cover that situation (or it's equivalent on IRC) isn't
going to be particularly easy to apply.

Whether the CoC can be applied or not isn't necessarily the most important
thing about it - it's more a statement of beliefs - but if the situation comes
up where someone is behaving unacceptably via IRC or email and "we"
say that we aren't interested in helping, or our hands are tied, because
"off-list" communication isn't covered by the CoC that's likely to lead to
a loud and public mess.

Cheers,
  Steve

Determine last LSN before promotion?

[Jeremy Finzel]

Is there any easy way to determine what the last LSN was prior to
promotion, except for (as I understand it), using pg_xlogdump on the
.partial WAL file after promotion and find the last LSN?  Am I missing
something obvious?

The question I am trying to answer has to do with replication slots and how
to determine when new activity begins on the master just following the
promotion point on the standby.

Thanks,
Jeremy

Re: Code of Conduct plan

[Chris Travers]

On Mon, Sep 17, 2018 at 6:08 PM Steve Litt 
wrote:

> On Mon, 17 Sep 2018 17:39:20 +0200
> Chris Travers  wrote:
>
>
> > Exactly.  And actually the first sentence is not new.  The second one
> > is a real problem though.  I am going to try one last time at an
> > additional alternative.
> >
> > " To that end, we have established this Code of Conduct for community
> > interaction and participation in the project’s work and the community
> > at large.   This code of conduct covers all interaction between
> > community members on the postgresql.org infrastructure.  Conduct
> > outside the postgresql.org infrastructure may call the Code of
> > Conduct committee to act as long as the interaction (or interaction
> > pattern) is community-related, other parties are unable to act, and
> > the Code of Conduct committee determines that it is in the best
> > interest of the community to apply this Code of Conduct."
>
> Chris,
>
> Would you be satisfied with the CoC if the current 2nd paragraph of the
> Introduction were replaced by the paragraph you wrote above?
>

Yes.  Or something like it.  It need not be exact.

I recognize a need  to be able to take enforcement to some areas off-list
activity, for what it's worth.

>
>
> SteveT
>
> Steve Litt
> September 2018 featured book: Quit Joblessness: Start Your Own Business
> http://www.troubleshooters.com/startbiz
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Dimitri Maziuk]

On 09/17/2018 10:39 AM, Chris Travers wrote:
> On Mon, Sep 17, 2018 at 5:28 PM Joshua D. Drake 
> wrote:
...
>> My feedback is that those two sentences provide an overarching authority
>> that .Org does not have the right to enforce 
...
> Fascinating that this would, on its face, not apply to a harassment
> campaign carried out over twitter, but it would apply to a few comments
> made over drinks at a bar.

There is a flip side: if you have written standards, you can be held
liable for not enforcing them. Potentially including enforcement of
twitbook AUP on the list subscribers who also have a slackogger account.

-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu



signature.asc
Description: OpenPGP digital signature

Re: Logical locking beyond pg_advisory

[Chris Travers]

On Mon, Sep 17, 2018 at 6:04 PM marcelo  wrote:

>
>
> I´m using an ORM (Devart´s) to access the database, so, I cannot "select
> ... FOR UPDATE". The application paradigm is that a user have a list of
> records (after a query) and she could update or delete any of them as the
> business rules allows it. So, at least an advisory lock is a must.
> I´m convinced by now: I would stay with advisory locks... expecting no app
> crash could occur...
>

I would say to fix this in the ORM rather than reinvent what the database
already gives you in the database.



> Thank you all.
> Marcelo
>
>
> 
>  Libre
> de virus. www.avast.com
> 
> <#m_-9091154853724945458_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Why is JSONB field automatically cast as TEXT?

[Ben Uphoff]

First post here at PostgreSQL; please forgive any etiquette mistakes…

I have a query that extracts a field from a JSONB column, e.g.:

SELECT (((mytable.ajsonbcolumn -> ‘somedata’) -> ‘nested’) ->> ‘first_name’) AS 
fname FROM mytable

When I save it into a view, PostgreSQL transforms it thusly:

SELECT (((mytable.ajsonbcolumn -> ‘somedata’::text) -> ‘nested’::text) ->> 
‘first_name’::text) AS fname FROM mytable

(note the ::text casts).

Why does it do this? It seems unnecessary and pollutes my SQL with a ton of 
extra text.

Thanks for your thoughts. -Ben

*** PLEASE NOTE *** This E-Mail/telefax message 
and any documents accompanying this transmission may contain information that 
is privileged, confidential, and/or exempt from disclosure under applicable law 
and is intended solely for the addressee(s) named above. If you are not the 
intended addressee/recipient, you are hereby notified that any use of, 
disclosure, copying, distribution, or reliance on the contents of this 
E-Mail/telefax information is strictly prohibited and may result in legal 
action against you. Please reply to the sender advising of the error in 
transmission and immediately delete/destroy the message and any accompanying 
documents. Thank you.

Re: Why is JSONB field automatically cast as TEXT?

[David G. Johnston]

On Monday, September 17, 2018, Ben Uphoff  wrote:
>
> SELECT (((mytable.ajsonbcolumn -> ‘somedata’::text) -> ‘nested’::text) ->>
> ‘first_name’::text) AS fname FROM mytable
>
> It’s casting the untyped literal constants (somedata, neated, first_name)
to text because everything must be typed.  It is not casting the first or
intermediate jsonb results to text.  The final output is text because of
the ->> operator.

:: binds more tightly than the other operators.

Jsonb->('somedata'::text)

David J.

Too many BitmapAnds in the wild

[Seamus Abshere]

hey,

We see a fair number of incorrectly chosen BitmapAnd plans in the wild at 
Faraday... enough that googling the problem ends up at our old posts to this 
mailing list 😀. An attractive solution was proposed by Jeff Janes [1]

- *cost += 0.1 * cpu_operator_cost * path->rows;
+ *cost += 6 * cpu_operator_cost * path->rows;

It appears this constant hasn't been changed for 7 years [2].

Is there any reason not to at least adjust this up by a factor of 2? 10? If 
Jeff is right and 6 is closer to ideal, then 0.1 must be _way_ too low?

Thanks,
Seamus


[1] 
https://www.postgresql.org/message-id/flat/CAMkU%3D1yV7WQLetrCVPqn%3DdTPdNzW3JD29ZsK0zJmgzO2tdcx-Q%40mail.gmail.com#aa3b3316cbe44fb3fd913ce019a86bb8
[2] 
https://github.com/postgres/postgres/blame/master/src/backend/optimizer/path/costsize.c#L1056



--
Seamus Abshere, SCEA
+598 9954 9954
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere

Re: Logical locking beyond pg_advisory

[marcelo]

On 17/09/2018 14:27 , Chris Travers wrote:



On Mon, Sep 17, 2018 at 6:04 PM marcelo > wrote:




I´m using an ORM (Devart´s) to access the database, so, I cannot
"select ... FOR UPDATE". The application paradigm is that a user
have a list of records (after a query) and she could update or
delete any of them as the business rules allows it. So, at least
an advisory lock is a must.
I´m convinced by now: I would stay with advisory locks...
expecting no app crash could occur...


I would say to fix this in the ORM rather than reinvent what the 
database already gives you in the database.



You are right. But you know...


Thank you all.
Marcelo



Libre de virus. www.avast.com




<#m_-9091154853724945458_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>



--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor 
lock-in.

http://www.efficito.com/learn_more




---
El software de antivirus Avast ha analizado este correo electrónico en busca de 
virus.
https://www.avast.com/antivirus

Re: Too many BitmapAnds in the wild

[Tom Lane]

Seamus Abshere  writes:
> hey,
> We see a fair number of incorrectly chosen BitmapAnd plans in the wild at 
> Faraday... enough that googling the problem ends up at our old posts to this 
> mailing list 😀. An attractive solution was proposed by Jeff Janes [1]

> - *cost += 0.1 * cpu_operator_cost * path->rows;
> + *cost += 6 * cpu_operator_cost * path->rows;

> It appears this constant hasn't been changed for 7 years [2].

AFAICT you are pointing at this:

cost_bitmap_tree_node(Path *path, Cost *cost, Selectivity *selec)
{
if (IsA(path, IndexPath))
{
*cost = ((IndexPath *) path)->indextotalcost;
*selec = ((IndexPath *) path)->indexselectivity;

/*
 * Charge a small amount per retrieved tuple to reflect the costs of
 * manipulating the bitmap.  This is mostly to make sure that a bitmap
 * scan doesn't look to be the same cost as an indexscan to retrieve a
 * single tuple.
 */
*cost += 0.1 * cpu_operator_cost * path->rows;
}

which does not seem like the place to be putting your thumb on the scale
if you want to discourage bitmap ANDs.  That would increase the estimate
for plain bitmap scans as well as ANDs.  Moreover, there's no plausible
reasoning for this adjustment being more than a minimal one.

There's a different fudge factor in cost_bitmap_and_node (also in
cost_bitmap_or_node) that would probably be more plausible to twiddle:

 * The runtime cost of the BitmapAnd itself is estimated at 100x
 * cpu_operator_cost for each tbm_intersect needed.  Probably too small,
 * definitely too simplistic?

if (l != list_head(path->bitmapquals))
totalCost += 100.0 * cpu_operator_cost;

I'd be the first to say that that number has no experimental basis,
plus modeling it as a quasi-constant is theoretically wrong; surely
it ought to vary depending on how big we think the bitmap might be.

I'm not, however, very enamored of just replacing the "100" with some
other random constant without any evidence to back up the change.
Let's see some test cases, at least.

regards, tom lane

Re: scram-sha-256 authentication broken in FIPS mode

[Michael Paquier]

On Mon, Sep 17, 2018 at 02:55:55PM +, Alessandro Gherardi wrote:
> Therefore, I believe the best option, at least for now, is calling
> FIPS_mode_set(1) in the application. 

I am not so sure about that.  As you rightly mention, CentOS and RedHat
patch OpenSSL to allow FIPS to work.  Per my research, Ubuntu can also
enable FIPS but that's not the case of Debian, which is very popular (I
may be wrong about the last one but I use it daily).

One question I have is how are you actually able to use FIPS on Windows
with OpenSSL?  Is that from one of the tarballs available in
openssl.org, which are more than 1 year old?  Pure upstream code does
not give this option, and CentOS/RHEL use a customly-made patch, based
on which Postgres does not complain when calling the low-level hashing
functions, and we rely now on FIPS being enabled system-wide.  And that
actually works.  It seems to me that you are yourself using a custom
patch for OpenSSL, and that's actually a different flavor than the Linux
version as in your case the low-level hashing functions complain if
called directly in FIPS mode.

At the end, I think that we ought to wait and see if upstream OpenSSL
comes up with support for FIPS and how it integrates with it, on both
Linux *and* Windows, and then consider if Postgres needs to do more.
There is little point in merging now a patch for something which may or
may not be supported by OpenSSL now.  My bet, as things stand, is that
we could finish with something similar to what happens on Linux with a
system-wide switch that Postgres knows nothing about.  Perhaps that will
not be the case, but let's think about that once we know for sure.
--
Michael


signature.asc
Description: PGP signature

Re: How to watch for schema changes

[Igor Korot]

Hi, Melvin,

On Tue, Jul 3, 2018 at 2:00 PM Melvin Davidson  wrote:
>
>
>
> >Unfortunately I'm stuck with 9.1.
>
> Have you thought about just setting   log_statement = 'ddl' in postgresql.conf
> and just greping the log for CREATE and ALTER?

Is there a way to query a server for a place where the log file is?
Or I will have to hard-code it?

Thank you.

>

Re: How to watch for schema changes

[Christophe Pettus]

> On Sep 17, 2018, at 07:09, Igor Korot  wrote:
> 
> Is there a way to query a server for a place where the log file is?

SHOW log_directory;

It's either relative to the PGDATA directory, or an absolute path.

--
-- Christophe Pettus
   x...@thebuild.com

Re: How to watch for schema changes

[Igor Korot]

Hi,

On Mon, Sep 17, 2018 at 9:19 PM Christophe Pettus  wrote:
>
>
> > On Sep 17, 2018, at 07:09, Igor Korot  wrote:
> >
> > Is there a way to query a server for a place where the log file is?
>
> SHOW log_directory;
>
> It's either relative to the PGDATA directory, or an absolute path.

And I presume it depends on the string I put in the log file?

Thank you.

>
> --
> -- Christophe Pettus
>x...@thebuild.com
>