Re: Serializable read only deferrable- implications
Sorry for the confusion I caused. The question about connection management and pg bouncer was a distraction and should have been addressed separately. When having a mixture of OLTP and OLAP on the same primary databases, is there any benefit to declaring long running report type connections as SERIALIZABLE READ ONLY DEFERRABLE in terms of impact on logical or physical replication, autovacuum, etc even if the much heavier OLTP traffic is still running as the default read committed mode? If the OLAP queries are moved to a physical read replica, I understand from this post ( https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/ ) that there are chances that a query will be killed on the replica even with hot_standby_feedback is turned on. With them running on the same server, is the main concern (other than load) that vacuum type cleanup is delayed? Maybe to sum up- If a long running report type query is run in default "read committed" mode and uses no temp tables / does no writes, would there be a benefit or change in behavior when using SERIALIZABLE READ ONLY DEFERRABLE mode?
Re: Select .... where id not in (....) returns 0 incorrectly
On 4/4/22 09:21, J. Roeleveld wrote: This was actually the case, I forgot there is 1 NULL-value in that list. Personally, I think NULL should be treated as a seperate value and not lead to strange behaviour. NULL is strange. Relational databases use ternary, not binary logic. In the woke vernacular, one could say that Postgres is non-binary. NULL literally means "no value". It is a part of the standard, so we have to deal with it, Codd help us. However, based on my lifelong experience with Oracle, NULL values are bad and are best avoided. Postgres is more forgiving than Oracle because in Postgres, the condition "is not null" can be resolved by index. In Oracle, it can not. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Select .... where id not in (....) returns 0 incorrectly
On 2022-04-05 19:25:24 -0400, Mladen Gogala wrote: > NULL is strange. Relational databases use ternary, not binary logic. > In the woke vernacular, one could say that Postgres is non-binary. > NULL literally means "no value". I prefer to think of NULL as "unknown value". That way the ternary logic makes intuitive sense: NULL = NULL? If you have two unknown values you don't know whether they are the same or not, so the result is also unknown, i.e. NULL. > It is a part of the standard, so we have to deal with it, > Codd help us. :-) > However, based on my lifelong experience with Oracle, NULL values are > bad and are best avoided. Oracle's handling of NULL values has a few extra warts, yes. I still wouldn't go as far as recommending to avoid NULL values (where they make sense semantically). > Postgres is more forgiving than Oracle because in Postgres, the > condition "is not null" can be resolved by index. In Oracle, it can > not. Actually it can (although it's a full index index scan, so the optimizer may prefer not to). It's "is null" which cannot use an index, because btree indexes in Oracle don't store NULL values (bitmap indexes do store NULL values, though - are they still an enterprise feature?). hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Cascade view drop permission checks
Hello guys! I've faced an interesting case with cascade drops. If we drop some view that is dependency for another view then drop cascade will not check permissions for cascade-droppping views. Short example is: create user alice with password 'apassword'; create user bob with password 'bpassword'; create schema sandbox_a; create schema sandbox_b; grant all on schema sandbox_a to alice; grant all on schema sandbox_b to bob; grant usage on schema sandbox_a to bob; -- alice create or replace view sandbox_a.alice_view as select category, name, setting from pg_catalog.pg_settings; grant select on sandbox_a.alice_view to bob; -- bob create or replace view sandbox_b.bob_view as select distinct category from sandbox_a.alice_view; -- alice drop view sandbox_a.alice_view cascade; -- !!! will drop sandbox_b.bob_view although alice is not an owner of sandbox_b.bob_view It seems strange to me that somebody who is not a member of owner role can drop an object bypassing permission checks. Is this behaviour OK?
