Re: here does postgres take its timezone information from?
Palle Girgensohn writes: > 16 nov. 2019 kl. 23:06 skrev Thomas Munro : >> Perhaps the best thing would be to revert this for the older >> PostgreSQL releases so that people doing minor version upgrades are >> inconvenienced by a system that can't start up after "pkg upgrade", >> but do it for 12 since not many people will be using that yet? > That could be a way, yes. Any thoughts on this from others following this > thread? I whined about this on the tz mailing list, and got the attention of the FreeBSD tzdata package maintainer [1]. It seems possible that he'll change that policy, so I'd advise doing nothing until that discussion settles. regards, tom lane [1] https://mm.icann.org/pipermail/tz/2019-November/028633.html
Re: Trouble incrementing a column
On Sat, Nov 23, 2019 at 4:47 PM Tom Lane wrote: > Note that you pay a fairly substantial performance penalty for deferring > the check, which is why it isn't the default, even though the SQL spec > says it ought to be. > Do you know what the worst case scenario is for the performance of deferring the check to the end of the statement (with deferred initially immediate)? Upon testing, I get a penalty of 2 to 5%, which seems pretty small, but I might not be testing the most adverse situation. See attached. The main "cost" that prevents from using DII routinely is that they can't receive foreign key constraints. Cheers, Jeff psql <<'END' create table jj_dii (id bigserial primary key, jj bigint unique deferrable initially immediate); create table jj_did (id bigserial primary key, jj bigint unique deferrable initially deferred); create table jj_nd (id bigserial primary key, jj bigint unique not deferrable); insert into jj_nd select x,x::bigint*100 from generate_series(1,10) f(x); insert into jj_did select x,x::bigint*100 from generate_series(1,10) f(x); insert into jj_dii select x,x::bigint*100 from generate_series(1,10) f(x); END for f in `seq 1 200`; do for mode in `shuf -e nd dii`; do echo "JJ $f $mode" pgbench -T30 -f <(echo "update jj_$mode set jj=jj+1") -n -M prepared done done
Re: Trouble incrementing a column
Jeff Janes writes: > On Sat, Nov 23, 2019 at 4:47 PM Tom Lane wrote: >> Note that you pay a fairly substantial performance penalty for deferring >> the check, which is why it isn't the default, even though the SQL spec >> says it ought to be. > Do you know what the worst case scenario is for the performance of > deferring the check to the end of the statement (with deferred initially > immediate)? Upon testing, I get a penalty of 2 to 5%, which seems pretty > small, but I might not be testing the most adverse situation. See attached. Hm, I would have expected more, though not factor-of-10 or anything like that. But that's just vague recollection from when we put in the feature. I'm not surprised if the numbers have moved since. > The main "cost" that prevents from using DII routinely is that they can't > receive foreign key constraints. Yeah, that's an issue. regards, tom lane
Re: sql query for postgres replication check
On Fri, Nov 22, 2019 at 01:20:59PM +, Zwettler Markus (OIZ) wrote: > I came up with the following query which should return any apply lag in > seconds. > > select coalesce(replay_delay, 0) replication_delay_in_sec > from ( >select datname, > ( > select case > when received_lsn = latest_end_lsn then 0 > else extract(epoch > from now() - latest_end_time) >end > from pg_stat_wal_receiver > ) replay_delay >from pg_database >where datname = current_database() > ) xview; > > > I would expect delays >0 in case SYNC or ASYNC replication is > somehow behind. We will do a warning at 120 secs and critical at 300 > secs. pg_stat_wal_receiver is available only on the receiver, aka the standby so it would not really be helpful on a primary. On top of that streaming replication is system-wide, so there is no actual point to look at databases either. > Would this do the job or am I missing something here? Here is a suggestion for Nagios: hot_standby_delay, as told in https://github.com/bucardo/check_postgres/blob/master/check_postgres.pl -- Michael signature.asc Description: PGP signature
Re: Constants in the foreighn key constraints
As a workaround, create a table with only one column and one value = `false` and foreign to it. On 22.11.2019 16:32, aleksey ksenzov wrote: Latest time we faced several issues which wouldn't arise provided we have possibility to use constants in foreign key constraints. brief example where it would be helpful: table_a ( id uuid, parent_id uuid, is_deleted boolean ) having possibility of FK (parent_id, false) to (id, is_deleted) would disallow setting parent record deleted flag to true if they have children, or insert record with is_deleted = true parent.
