Re: Views and triggers more then one row returned by subquery.

[Day, David]

Tom,

You are exactly right about STABLE needed on the get_rule_seq function.
That resolved my issues.

I've been burned before by using a function within a view/WHERE situation and 
the function was not marked STABLE.
I need to start taking PREMAGEN.

I Kind of came to the same conclusion in parrallel after I ran the explain 
analyze on the select and wondered why the hell it was going about it like that.

Yes keeping the steps of a rule properly associated and ordered with a folder 
is mind bending.  Sorry you had to see it.

Thanks so much for analysis and comments..

Regards


Dave


From: Tom Lane 
Sent: Tuesday, January 12, 2021 6:24 PM
To: Day, David 
Cc: pgsql-general@lists.postgresql.org 
Subject: Re: Views and triggers more then one row returned by subquery.

"Day, David"  writes:
> My presumption of views and instead of trigger behavior is that the VIEW 
> first gets populated with the WHERE filter and then the "DELETE or UPDATE" 
> operation will fire against each of the rendered view rows. ( ? )
> If this is true then I can't explain the more then one row returned error.

This code makes my head hurt :-(

However, it's fairly easy to tell that the trigger successfully completes
on the first view row (you can check that by sticking some RAISE NOTICE
commands in it) and then the error is thrown while evaluating the next
view row.  The error has to be complaining about the "WITH rule_heads ..."
subquery in the view's targetlist; the only other subquery is the MAX()
subquery, which most certainly isn't going to return more than one row.

The trigger is evidently running rule_delete_and_decrement(), which
I am not interested in deconstructing in full, but I can see that
it modifies the contents of the my_translator table.  So what must
be happening is that the "WITH rule_heads ..." subquery is returning
more than one row after that modification occurs.

I have a rough theory as to why, though I'm not planning on tracing it
down in detail.  The result of the WITH clause itself *does not see the
deletion*, as specified somewhere in our fine manual.  (That part is
consistent with your expectation that the view output doesn't change
while this is all going on: my_translator is being scanned using the
original query snapshot, so the subquery doesn't see the already-applied
changes.)  So when we re-execute the subquery at the second view row,
the "WITH rule_heads" output is the same as before.  On the other hand,
the get_rule_seq() function is going to see the updated contents of
my_translator, since it's declared VOLATILE.  I think that this
inconsistency results in more than one row getting let through the
WHERE filter, and voila we get the error.

You might be able to fix this by marking get_rule_seq() as STABLE
so that it sees the same snapshot as the calling query.  At least,
when I change it to stable I don't see the error anymore.  Whether
things are then consistent with your intent, I can't say.  But
I will say that this code is an unmaintainable pile of spaghetti,
because when the side-effects occur and where they're visible
is going to be almost impossible to keep track of.

regards, tom lane

Re: Logical replication gradually slowing down, then hanging.

[Lukasz Biegaj]

On 16.11.2020 10:20, Lukasz Biegaj wrote:

Hello,

I'm encountering a repeating problem with logical replication.

[...]

And also a gdb backtrace:
(gdb) bt
#0  0x5623348d3ce0 in hash_seq_search ()
#1  0x56233473a396 in ReorderBufferQueueChange ()
#2  0x56233472fb80 in LogicalDecodingProcessRecord ()
[...]



Replying to myself for anyone searching for the issue in future:

I believe that the issue was fixed with patch described in thread 
 
on pgsql-hackers.



--
Lukasz Biegaj | Unity Group | https://www.unitygroup.com/
System Architect, AWS Solutions Architect

postgres optimization

[Atul Kumar]

Hi,

I am new to postgres and I find optimization as a challenge in
postgres being a newbee.

As I am unable to understand explain plan and its components like
merge join, hash join, loop join etc.

I get totally confused about how to read it and how to understand what
thing is making my query slow.

I will be grateful if you can help me in understanding the basics and
slowly in depth optimization, by understanding explain plan.

I really need to have the easiest language to understand, the explain
plan and how to read long explain plans to optimize the query (with
examples).




Regards,
Atul

Re: postgres optimization

[hubert depesz lubaczewski]

On Thu, Jan 14, 2021 at 04:58:32PM +0530, Atul Kumar wrote:
> I will be grateful if you can help me in understanding the basics and
> slowly in depth optimization, by understanding explain plan.

Consider reading https://www.depesz.com/tag/unexplainable/

depesz

Strange (and good) side effect of partitioning ?

[Phil Florent]

Hi,

I read that on Jonathan Lewis' blog :

(I believe that there may be some RDBMS which will treat (e.g.) “X between 20 
and 10” as being identical to “X between 10 and 20” )

I am puzzled. PostgreSQL seems NOT to treat X between 20 and 10” as being 
identical to “X between 10 and 20" but it's complicated.

Here is my test case:

select version();
 version
--
 PostgreSQL 13.1 (Ubuntu 13.1-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled 
by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

create table t1 (rn integer , object_name text) partition by range(rn);

create table t1a partition of t1 for values from (1) to (50001);


\d+ t1
 Table partitionnée « public.t1 »
   Colonne   |  Type   | Collationnement | NULL-able | Par défaut | Stockage | 
Cible de statistiques | Description
-+-+-+---++--+---+-
 rn  | integer | |   || plain|  
 |
 object_name | text| |   || extended |  
 |
Clé de partition : RANGE (rn)
Partitions: t1a FOR VALUES FROM (1) TO (50001)



insert into t1 select
rownum  rn,
upper(md5(random()::text)) object_name
from
(select generate_series(1,5) rownum) serie
;

explain analyze select  object_name
fromt1
where
rn between 20 and 10
;
 QUERY PLAN

 Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.002..0.002 rows=0 
loops=1)
   One-Time Filter: false
 Planning Time: 0.116 ms
 Execution Time: 0.020 ms


It's OK but:

explain analyze select  object_name
fromt1a
where
rn between 20 and 10
;
   QUERY PLAN
-
 Seq Scan on t1a  (cost=0.00..1167.00 rows=1 width=33) (actual 
time=6.553..6.553 rows=0 loops=1)
   Filter: ((rn >= 20) AND (rn <= 10))
   Rows Removed by Filter: 5
 Planning Time: 0.092 ms
 Execution Time: 6.573 ms

At first I thought it was related to partition pruning but:

set enable_partition_pruning = false;

explain analyze select  object_name
fromt1
where
rn between 20 and 10
;

 QUERY PLAN

 Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.002..0.003 rows=0 
loops=1)
   One-Time Filter: false
 Planning Time: 0.104 ms
 Execution Time: 0.021 ms


Confirmation since I still obtain "One-Time Filter: false" if I don't filter on 
the partition key:

create table t2 (rn integer , rn2 integer, object_name text) partition by 
range(rn);

create table t2a partition of t2 for values from (1) to (50001);

d+ t2
 Table partitionnée « public.t2 »
   Colonne   |  Type   | Collationnement | NULL-able | Par défaut | Stockage | 
Cible de statistiques | Description
-+-+-+---++--+---+-
 rn  | integer | |   || plain|  
 |
 rn2 | integer | |   || plain|  
 |
 object_name | text| |   || extended |  
 |
Clé de partition : RANGE (rn)
Partitions: t2a FOR VALUES FROM (1) TO (50001)

insert into t2 select
rownum  rn, rownum rn2,
upper(md5(random()::text)) object_name
from
(select generate_series(1,5) rownum) serie
;

explain analyze select  object_name
fromt2
where
rn2 between 20 and 10
;


 QUERY PLAN

 Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.002..0.003 rows=0 
loops=1)
   One-Time Filter: false
 Planning Time: 0.185 ms
 Execution Time: 0.019 ms

I don't understand why I don't obtain " One-Time Filter: false" with a classic 
table or a partition ?

Best regards,

Phil

Like Query help

[Bret Stern]

query

select company_code, item_code, item_description, product_line, 
udf_item_width, udf_item_length, sales_unit_measure, ''as mat_type from 
mas_combined_item_master where company_code='BUR' or company_code='SNJ' 
or company_code='EBC' and udf_edb_managed=''

and item_code LIKE 'S-%' order by item_code;

comment

Second column is item_code...why are these items in the results. 
Expecting the query to return results where the item_code


starts with "S-" and includes any othervalue past "S-"


results

"SNJ";"01 1X1 CORNER BULLNOSE";"1x1 Corner Double 
Bullnose";"SYZY";"";"";"EACH";""
"SNJ";"0001 BRIGHT WHITE LISTELLO";"Bright White Listello 
5/8x12";"VILL";"";"";"EACH";""
"SNJ";"0001 CUSTOM 1X6 BULLNOSE LONG";"Custom 1x6 Bullnose on 6" 
Side";"SYZY";"";"";"EACH";""

"BUR";"000936MOD1P4";"Matte Cappuccino Tile";"AMOL";"";"";"SF";""
"SNJ";"000936MOD1P4";"Matte Cappuccino Tile";"AMOL";"";"";"SF";""
"BUR";"0009S4369MOD1P2";"Matte Cappuccino BN on 6"";"AMOL";"";"";"EA";""
"SNJ";"0009S4369MOD1P2";"Matte Cappuccino BN on 6"";"AMOL";"";"";"EA";""
"BUR";"0009S4639MOD1P2";"Matte Cappucino BN on 3"";"AMOL";"";"";"EA";""
"SNJ";"0009S4639MOD1P2";"Matte Cappucino BN on 3"";"AMOL";"";"";"EA";""
"BUR";"0009SCL4369M1P2";"Matte Cappuccino Left 
Bullnose";"AMOL";"";"";"EA";""
"SNJ";"0009SCL4369M1P2";"Matte Cappuccino Left 
Bullnose";"AMOL";"";"";"EA";""

"BUR";"0009SCR4369M1P2";"Matte Cappuccino Right BN";"AMOL";"";"";"EA";""
"SNJ";"0009SCR4369M1P2";"Matte Cappuccino Right BN";"AMOL";"";"";"EA";""
"SNJ";"0014 SURF 2X4 OFFSET";"Surf 2x4 Offset Mosaic";"VILL";"";"";"EACH";""
"SNJ";"0014 SURF LISTELLO";"Surf Listello 5/8x12";"VILL";"";"";"EACH";""
"SNJ";"0015 ICE 2X4 OFFSET";"Ice 2x4 Offset Mosaic";"VILL";"";"";"EACH";""

Re: Like Query help

[aNullValue (Drew Stemen)]

At 2021-01-14T19:27:23-05:00, Bret Stern  
sent:
> query
> 
> select company_code, item_code, item_description, product_line, 
> udf_item_width, udf_item_length, sales_unit_measure, ''as mat_type from 
> mas_combined_item_master where company_code='BUR' or company_code='SNJ' 
> or company_code='EBC' and udf_edb_managed=''
> and item_code LIKE 'S-%' order by item_code;
> 
> comment
> 
> Second column is item_code...why are these items in the results. 
> Expecting the query to return results where the item_code
> 
> starts with "S-" and includes any othervalue past "S-"
> 

Based on your description, your query is not correct; you should rewrite it, 
likely using parenthesis to define your actually desired value expression. 

See also 
https://www.postgresql.org/docs/13/sql-expressions.html#SYNTAX-EXPRESS-EVAL

This may or may not be what you intended:

SELECT company_code, item_code, item_description, product_line, udf_item_width, 
udf_item_length, sales_unit_measure, ''as mat_type
FROM mas_combined_item_master
WHERE (company_code='BUR' OR company_code='SNJ' OR company_code='EBC') 
AND udf_edb_managed=''
AND item_code LIKE 'S-%'
ORDER BY item_code;

Re: Strange (and good) side effect of partitioning ?

[rob stone]

Hi,

On Thu, 2021-01-14 at 20:48 +, Phil Florent wrote:
> Hi,
> 
> I read that on Jonathan Lewis' blog :
> 
> (I believe that there may be some RDBMS which will treat (e.g.) “X
> between 20 and 10” as being identical to“X between 10 and 20” )
> 
> I am puzzled. PostgreSQL seems NOT to treat X between 20 and 10” as
> being identical to“X between 10 and 20" but it's complicated.
> 
> Here is my test case:
> 
> select version();                                                   
>          version                                                    
>          
> -
> -
>  PostgreSQL 13.1 (Ubuntu 13.1-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
> 
> create table t1 (rn integer , object_name text) partition by
> range(rn);
> 
> create table t1a partition of t1 for values from (1) to (50001);
> 
> 
> \d+ t1                                         Table partitionnée «
> public.t1 »
>    Colonne   |  Type   | Collationnement | NULL-able | Par défaut |
> Stockage | Cible de statistiques | Description
> -+-+-+---++--
> +---+-
>  rn          | integer |                 |           |            |
> plain    |                       |
>  object_name | text    |                 |           |            |
> extended |                       |
> Clé de partition : RANGE (rn)
> Partitions: t1a FOR VALUES FROM (1) TO (50001)
> 
> 
> 
> insert into t1 select                                    rownum  rn,
>         upper(md5(random()::text)) object_name
> from
>         (select generate_series(1,5) rownum) serie
> ;
> 
> explain analyze select  object_namefrom    t1
> where
>         rn between 20 and 10
> ;
>                                      QUERY PLAN                      
>              
> -
> ---
>  Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.002..0.002
> rows=0 loops=1)
>    One-Time Filter: false
>  Planning Time: 0.116 ms
>  Execution Time: 0.020 ms
> 
> It's OK but:
> 
> explain analyze select  object_namefrom    t1a
> where
>         rn between 20 and 10
> ;
>                                            QUERY PLAN                
>                            
> -
> 
>  Seq Scan on t1a  (cost=0.00..1167.00 rows=1 width=33) (actual
> time=6.553..6.553 rows=0 loops=1)
>    Filter: ((rn >= 20) AND (rn <= 10))
>    Rows Removed by Filter: 5
>  Planning Time: 0.092 ms
>  Execution Time: 6.573 ms
> 
> At first I thought it was related to partition pruning but:
> 
> set enable_partition_pruning = false;
> 
> explain analyze select  object_namefrom    t1
> where
>         rn between 20 and 10
> ;
> 
>                                      QUERY PLAN                      
>              
> 
>  Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.002..0.003
> rows=0 loops=1)
>    One-Time Filter: false
>  Planning Time: 0.104 ms
>  Execution Time: 0.021 ms
> 
> 
> Confirmation since I still obtain "One-Time Filter: false" if I don't
> filter on the partition key:
> 
> create table t2 (rn integer , rn2 integer, object_name text)
> partition by range(rn);
> 
> create table t2a partition of t2 for values from (1) to (50001);
> 
> d+ t2                                         Table partitionnée «
> public.t2 »
>    Colonne   |  Type   | Collationnement | NULL-able | Par défaut |
> Stockage | Cible de statistiques | Description
> -+-+-+---++--
> +---+-
>  rn          | integer |                 |           |            |
> plain    |                       |
>  rn2         | integer |                 |           |            |
> plain    |                       |
>  object_name | text    |                 |           |            |
> extended |                       |
> Clé de partition : RANGE (rn)
> Partitions: t2a FOR VALUES FROM (1) TO (50001)
> 
> insert into t2 select                                               
>   rownum  rn, rownum rn2,
>         upper(md5(random()::text)) object_name
> from
>         (select generate_series(1,5) rownum) serie
> ;
> 
> explain analyze select  object_namefrom    t2
> where
>         rn2 between 20 and 10
> ;
> 
> 
>                                      QUERY PLAN                      
>              
> 
>  Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.002..0.003
> rows=0 loops=1)
>    One-Time Filter: false
>  Planning Time: 0.185 ms
>  Execut

Re: Like Query help

[Bret Stern]

Works. Thanks for the help. I will read the docs

Best

On 1/14/2021 4:37 PM, aNullValue (Drew Stemen) wrote:

At 2021-01-14T19:27:23-05:00, Bret Stern  
sent:

query

select company_code, item_code, item_description, product_line,
udf_item_width, udf_item_length, sales_unit_measure, ''as mat_type from
mas_combined_item_master where company_code='BUR' or company_code='SNJ'
or company_code='EBC' and udf_edb_managed=''
and item_code LIKE 'S-%' order by item_code;

comment

Second column is item_code...why are these items in the results.
Expecting the query to return results where the item_code

starts with "S-" and includes any othervalue past "S-"


Based on your description, your query is not correct; you should rewrite it, 
likely using parenthesis to define your actually desired value expression.

See also 
https://www.postgresql.org/docs/13/sql-expressions.html#SYNTAX-EXPRESS-EVAL

This may or may not be what you intended:

SELECT company_code, item_code, item_description, product_line, udf_item_width, 
udf_item_length, sales_unit_measure, ''as mat_type
FROM mas_combined_item_master
WHERE (company_code='BUR' OR company_code='SNJ' OR company_code='EBC')
AND udf_edb_managed=''
AND item_code LIKE 'S-%'
ORDER BY item_code;

Re: Strange (and good) side effect of partitioning ?

[Tom Lane]

Phil Florent  writes:
> I read that on Jonathan Lewis' blog :
> (I believe that there may be some RDBMS which will treat (e.g.) "X between 20 
> and 10" as being identical to "X between 10 and 20" )

FWIW, I hope not, because the SQL spec is perfectly clear that it's
not supposed to work like that.  As rob stone noted nearby, you're
supposed to say BETWEEN SYMMETRIC if you want that behavior.
"X between 20 and 10" should always be false (well, unless it's
null because X is null).  But I think that's not really what
your question is.

> I am puzzled. PostgreSQL seems NOT to treat X between 20 and 10" as being 
> identical to "X between 10 and 20" but it's complicated.

There's no specific mechanism in Postgres that would cause "X between 20
and 10" to be reduced to constant-false (and I kind of think it would
be a waste of effort to add one).  So that's why in simple cases you
get a plan like

>  Seq Scan on t1a  (cost=0.00..1167.00 rows=1 width=33) (actual 
> time=6.553..6.553 rows=0 loops=1)
>Filter: ((rn >= 20) AND (rn <= 10))

I think that the other cases you show work as they do because the
code for excluding irrelevant range-based partitions is able to
conclude that no partition need be scanned.  That is, the
constant-false-one-time-filter plan arises when we have no
partitions remaining to scan, not because the plan for any one
partition would have looked different from what's above.

regards, tom lane

Re: Strange (and good) side effect of partitioning ?

[Tom Lane]

I wrote:
> There's no specific mechanism in Postgres that would cause "X between 20
> and 10" to be reduced to constant-false

Wait, I take that back.  There is a mechanism that can conclude that
"X >= 20" and "X <= 10" are contradictory, but it's not applied by
default.  Observe:

regression=# set constraint_exclusion = default;
SET
regression=# explain select * from tenk1 where unique1 between 20 and 10;
 QUERY PLAN  
-
 Index Scan using tenk1_unique1 on tenk1  (cost=0.29..8.30 rows=1 width=244)
   Index Cond: ((unique1 >= 20) AND (unique1 <= 10))
(2 rows)

regression=# set constraint_exclusion = on; 
SET
regression=# explain select * from tenk1 where unique1 between 20 and 10;
QUERY PLAN
--
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: false
(2 rows)

The default value of constraint_exclusion is "partition", which means
(you guessed it) that it's applied only to potential partitioning
constraints.  This is a heuristic based on the typical payoff of
excluding whole partitions versus skipping an empty index scan.
But if you have a workload where it's really worth spending
planner cycles looking for self-contradictory queries, you can
turn it on.

regards, tom lane

RE: Strange (and good) side effect of partitioning ?

[Phil Florent]

Hi Tom, Hi Rob

Thanks for this clear and complete explanation. My question was unclear since I 
didn't even consider the results could be identical and it was about the plans. 
I had misunderstood what J.Lewis had written since he probably meant some RDBMS 
always do a BETWEEN SYMETRIC. Our application currently has double 
compatibility with Oracle and PostgreSQL, PostgreSQL only after 2021, and I 
hope we won't be compatible with a RDBMS that would not respect SQL standard on 
this aspect.

>But if you have a workload where it's really worth spending
>planner cycles looking for self-contradictory queries, you can
>turn it on.

It was theoretical but it is a DSS tool and some queries can be dynamically 
built by the end users. Perhaps it really happens on some cases since I don't 
know if we always check criterias not to obtain self-contradictory queries. 
Since it's not OLTP our execution times are always much more important than our 
planning times anyway.

There are other places it could be more interesting to spend time for better 
performance. Debian 10/PostgreSQL 11 is our initial PostgreSQL platform. It's 
very efficient but I have to prepare Debian 11/PostgresQL 13. My first goal was 
to avoid performance regressions but I now want to always fully use planning 
capabilites of PostgreSQL. We currently have to completely deactive merge joins 
for some workload, nested loops for some other workload. It's OK but it's not 
optimal.
My current goal is to always activate (almost) everything with Debian 
11/PostgreSQL 13 and everything with Debian 12/PostgreSQL 14+.
I will try to increase default_statistics_target it could be worth the price. I 
will also try to activate enable_partitionwise_aggregate and 
enable_partitionwise_join since we use partitioning by list of hospitals and 
subpartitioning by range of times. Replacing our slow Oracle "union all" views 
by PostgreSQL partitioned tables to deal with group of hospitals has still to 
be completed.

Best regards,

Phil



De : Tom Lane 
Envoyé : vendredi 15 janvier 2021 03:12
À : Phil Florent 
Cc : pgsql-gene...@postgresql.org 
Objet : Re: Strange (and good) side effect of partitioning ?

I wrote:
> There's no specific mechanism in Postgres that would cause "X between 20
> and 10" to be reduced to constant-false

Wait, I take that back.  There is a mechanism that can conclude that
"X >= 20" and "X <= 10" are contradictory, but it's not applied by
default.  Observe:

regression=# set constraint_exclusion = default;
SET
regression=# explain select * from tenk1 where unique1 between 20 and 10;
 QUERY PLAN
-
 Index Scan using tenk1_unique1 on tenk1  (cost=0.29..8.30 rows=1 width=244)
   Index Cond: ((unique1 >= 20) AND (unique1 <= 10))
(2 rows)

regression=# set constraint_exclusion = on;
SET
regression=# explain select * from tenk1 where unique1 between 20 and 10;
QUERY PLAN
--
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: false
(2 rows)

The default value of constraint_exclusion is "partition", which means
(you guessed it) that it's applied only to potential partitioning
constraints.  This is a heuristic based on the typical payoff of
excluding whole partitions versus skipping an empty index scan.
But if you have a workload where it's really worth spending
planner cycles looking for self-contradictory queries, you can
turn it on.

regards, tom lane

Re: Error messages on duplicate schema names

[Michael Paquier]

On Wed, Jan 06, 2021 at 07:15:24PM +0200, Andrus wrote:
> Should duplicate schema names accepted or should their usage throw better
> error messages.

This means that we are one call of CommandCounterIncrement() short for
such queries, and similar safeguards already exist in this area for
GRANT/REVOKE.  The spot where I would put this new CCI is at the end
of SetDefaultACL(), like in the attached with a test added to
privileges.sql.

Any thoughts from others?
--
Michael
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 1a81e768ec..f3c1ca18ae 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -1365,6 +1365,9 @@ SetDefaultACL(InternalDefaultACL *iacls)
 		ReleaseSysCache(tuple);
 
 	table_close(rel, RowExclusiveLock);
+
+	/* prevent error when processing duplicate objects */
+	CommandCounterIncrement();
 }
 
 
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 7754c20db4..5e5f98ac68 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -1649,7 +1649,8 @@ SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -
  f
 (1 row)
 
-ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT SELECT ON TABLES TO public;
+-- placeholder for test with duplicated schema and role names
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns,testns GRANT SELECT ON TABLES TO public,public;
 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no
  has_table_privilege 
 -
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 4911ad4add..fff76e0bd0 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -985,7 +985,8 @@ CREATE TABLE testns.acltest1 (x int);
 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no
 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no
 
-ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT SELECT ON TABLES TO public;
+-- placeholder for test with duplicated schema and role names
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns,testns GRANT SELECT ON TABLES TO public,public;
 
 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no
 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no


signature.asc
Description: PGP signature

RE: Strange (and good) side effect of partitioning ?

[Phil Florent]

Hi Tom,

>The default value of constraint_exclusion is "partition", which means
>(you guessed it) that it's applied only to potential partitioning
>constraints.  This is a heuristic based on the typical payoff of
>excluding whole partitions versus skipping an empty index scan.
>But if you have a workload where it's really worth spending
>planner cycles looking for self-contradictory queries, you can
>turn it on.

Interesting. Test case was not real but planning times have to be considered 
from a more general point of view. They are not a problem with our DSS app but 
we will also migrate our OLTP applications.
Partitioning is something new for me since we currently don't use it for our 
OLTP apps. It was not a technical choice, partitioning is not included in 
standard license of our current RDBMS. I will globally check the gain/loss with 
real workloads anyway.

Best regards,

Phil




De : Tom Lane 
Envoyé : vendredi 15 janvier 2021 03:12
À : Phil Florent 
Cc : pgsql-gene...@postgresql.org 
Objet : Re: Strange (and good) side effect of partitioning ?

I wrote:
> There's no specific mechanism in Postgres that would cause "X between 20
> and 10" to be reduced to constant-false

Wait, I take that back.  There is a mechanism that can conclude that
"X >= 20" and "X <= 10" are contradictory, but it's not applied by
default.  Observe:

regression=# set constraint_exclusion = default;
SET
regression=# explain select * from tenk1 where unique1 between 20 and 10;
 QUERY PLAN
-
 Index Scan using tenk1_unique1 on tenk1  (cost=0.29..8.30 rows=1 width=244)
   Index Cond: ((unique1 >= 20) AND (unique1 <= 10))
(2 rows)

regression=# set constraint_exclusion = on;
SET
regression=# explain select * from tenk1 where unique1 between 20 and 10;
QUERY PLAN
--
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: false
(2 rows)

The default value of constraint_exclusion is "partition", which means
(you guessed it) that it's applied only to potential partitioning
constraints.  This is a heuristic based on the typical payoff of
excluding whole partitions versus skipping an empty index scan.
But if you have a workload where it's really worth spending
planner cycles looking for self-contradictory queries, you can
turn it on.

regards, tom lane

REASSIGN OWNED BY in current database only

[Andrus]

Hi!

Cluster contains lot databases.

All objects in database should owned by separate role  whose name is in form

databasename_owner

where databasename is this database name in cluster.

This role in granted to cluster users who should have acces to this 
database.


Database is restored  from other cluster where is has different name.

After that

REASSIGN OWNED BY originaldbname_owner TO restoreddbname_owner

is used to force this role in restored database.

This command changes also owner of global objects also.

How to change  owner only in current database, leaving global objects 
owner unchanged ?


Andrus.