Re: No enough privileges for autovacuum worker

[Андрей Сычёв]

Because   in  trigger  where  table  fpbackup.fp_vpn_data_2021w12  has
been created there are several DDL commands:

EXECUTE 'CREATE TABLE "' || fp_schema_name || '"."' || fp_table_name || '" 
(LIKE '|| fp_parent_table_name || ' INCLUDING DEFAULTS INCLUDING CONSTRAINTS 
INCLUDING INDEXES) WITHOUT OIDS TABLESPACE ' || fp_tablespace;

EXECUTE 'ALTER TABLE "' || fp_schema_name || '"."' || fp_table_name || '" OWNER 
TO fpbkwriter';

The  trigger was called by user "worker" that already had permission on
USAGE  of schema dict, so table creation was successful. But after that
the ownership of the table was moving to "fpbkwriter" that had no permission on
USAGE of schema dict, thus the error occured.

In reality, there was no user "fpbkwriter" at the beginning. Only user
"worker"  was  created. And when the user "fpbkwriter" was created, he  was
not granted by enough privileges mistakenly.

Re: No enough privileges for autovacuum worker

[Adrian Klaver]

On 3/25/21 1:25 AM, Андрей Сычёв wrote:

Because   in  trigger  where  table  fpbackup.fp_vpn_data_2021w12  has
been created there are several DDL commands:

EXECUTE 'CREATE TABLE "' || fp_schema_name || '"."' || fp_table_name || '" 
(LIKE '|| fp_parent_table_name || ' INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES) 
WITHOUT OIDS TABLESPACE ' || fp_tablespace;

EXECUTE 'ALTER TABLE "' || fp_schema_name || '"."' || fp_table_name || '" OWNER 
TO fpbkwriter';

The  trigger was called by user "worker" that already had permission on
USAGE  of schema dict, so table creation was successful. But after that
the ownership of the table was moving to "fpbkwriter" that had no permission on
USAGE of schema dict, thus the error occured.

In reality, there was no user "fpbkwriter" at the beginning. Only user
"worker"  was  created. And when the user "fpbkwriter" was created, he  was
not granted by enough privileges mistakenly.



Alright that I understand. Still after the ALTER TABLE ... OWNER TO 
fpbkwriter, the index was running as fpbkwriter yet there where no 
errors? Did that mean the table was never queried or the index never used?



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

About CVE-2018-1058

[Mahongwei (March, ICSL)]

Hi

>From this blog: 
>https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058%3A_Protect_Your_Search_Path

Do you think that this is just a mitigation, not a real fix, is there any plan 
to fix it?

Thanks

Re: Slow index creation

[Paul van der Linden]

Extra tables is not something that will work out in my workflow...
I've managed to cut the time in half already, but perhaps there's more to
be won.
In one of the calculations done on the st_area, I used a log(base,value),
but since the only 2-param log function present in postgres takes numerics
as parameters, i inserted a typecast to numeric.
Apparently that conversion takes quite some time, rewriting it to
log(value)/log(base) (which are present with float params, so no need to
convert to numeric) took only half the original time

On Wed, Feb 24, 2021 at 9:37 PM Bjornar Skinnes 
wrote:

> Why not create a table with cols a, b, c and d. Where you insert a row for
> each combination and key and index abc then return d?
>
> ons. 24. feb. 2021, 21:15 skrev Paul van der Linden <
> paul.doskabou...@gmail.com>:
>
>> Thanks for all the suggestions,
>>
>> When the server is not in use for mission-critical work, I'll definitely
>> going to do some testing based on your ideas.
>> Will let you know what comes out of that
>>
>> Cheers,
>> Paul
>>
>> On Thu, Feb 18, 2021 at 6:49 PM hubert depesz lubaczewski <
>> dep...@depesz.com> wrote:
>>
>>> On Thu, Feb 18, 2021 at 10:24:25AM -0700, Michael Lewis wrote:
>>> >   [1]
>>> https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/
>>> >
>>> > Thanks for this reference. I enjoy your blog, but haven't made the
>>> time to read all the archives somehow. Stuff doesn't stick very
>>> > well when it isn't yet "needed" info besides.
>>> > I have seen overhead from 'raise notice' in small functions that are
>>> sometimes called many thousands of times in a single query, but
>>> > hadn't done the test to verify if the same overhead still exists for
>>> raise debug or another level below both client_min_messages
>>> > and log_min_messages. Using your examples, I saw about .006 ms for
>>> each call to RAISE DEBUG with a client/log_min as notice/warning.
>>>
>>> Sure, this overhead is definitely possible, but kinda besides the point
>>> - there will be some slowdowns in other places, and it will be good to
>>> track them.
>>> That's why I suggested to do it on small sample of data.
>>>
>>> Best regards,
>>>
>>> depesz
>>>
>>>

Re: Hello - About how to install PgAdmin4 on Debian 10

[Adrian Klaver]

On 3/25/21 12:49 PM, Carlos Montenegro wrote:

Hello dear Adrian and Ray.
Good day !

Thank you both for helping me.
It has worked and have installed pgadmin4, but have this issue when try 
to open the server:


What packages did you install?

Define open server.

What is the complete text of the actual error message?



pgAdmin4 server could not be contacted
Any suggestions?
Best


On Wed, Mar 24, 2021 at 4:10 PM Adrian Klaver > wrote:


On 3/24/21 1:17 PM, Carlos Montenegro wrote:
 > Yes Ray,
 > I followed those instructions, but it seems the repository location
 > changed. It is not available, but thank you so much for your support.

It worked for me using the instructions listed here(per Ray's post):

https://www.pgadmin.org/download/pgadmin-4-apt/

 >
 > Best,
 > Carlos
 >
 > On Wed, Mar 24, 2021 at 1:55 PM Ray O'Donnell mailto:r...@rodonnell.ie>
 > >> wrote:
 >
 >     On 24/03/2021 19:08, Carlos Montenegro wrote:
 >      > Hello dear Ray.
 >      > Thanks for your answer.
 >      >
 >      > Yes, I see pgadmin4 and then tried, but any success.
 >      > How did you make the installation? Any advice?
 >
 >     Hi Carlos,
 >
 >     I had forgotten - pgAdmin has its own apt repository, which
you need
 >     to add:
 >
 > https://www.pgadmin.org/download/pgadmin-4-apt/

 >     >
 >
 >     Once you've done that, then "apt update" and "apt install..."
should do
 >     the job.
 >
 >     I hope this helps.
 >
 >     Ray.
 >
 >
 >     --
 >     Raymond O'Donnell // Galway // Ireland
 > r...@rodonnell.ie 
>
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

PostgreSQL Views Where Clause Character Columns getting Typecasted to Text and Indexes not getting Used

[Jagmohan Kaintura]

Hi All,

PostgreSQL version : 12.5

When we are compiling View definition into database , the where clause on
the Character Columns is getting internally typecasted to text.
Example : Source Code being Applied :

" FROM tms_vessel_visit_aggregate a, tms_vessel_visit v
LEFT OUTER JOIN tms_vsl_svc s ON (v.svc_c = s.svc_c)
WHERE v.vessel_visit_c = a.vessel_visit_c and v.vessel_call_nr =
a.vessel_call_nr
"

Now when it's stored internally in the database the columns are getting
typecast with *text *but those table columns are not text they are
varchar(10).

Example :
   FROM tms_vessel_visit_aggregate a,
tms_vessel_visit v
 LEFT JOIN tms_vsl_svc s ON *v.svc_c::text* = *s.svc_c::text*
  WHERE *v.vessel_visit_c::text* = *a.vessel_visit_c::text* AND
v.vessel_call_nr = a.vessel_call_nr

Even when we placed the casting in the Original Source to varchar(10), its
typecasting internally to *::text .*

These Columns are Primary Key columns and because of this Type Casting on
those columns Index scan is not happening and we are always getting
Sequential Scan.
Example :

 ->  Subquery Scan on "*SELECT* 1"  (cost=36.88..115.01
rows=995 width=13) (actual time=0.763..3.144 rows=995 loops=1)
   Buffers: shared hit=65
   ->  Hash Join  (cost=36.88..105.06 rows=995
width=6361) (actual time=0.763..2.964 rows=995 loops=1)

* Hash Cond: (((v.vessel_visit_c)::text =
(a.vessel_visit_c)::text) AND (v.vessel_call_nr = a.vessel_call_nr))*
   Buffers: shared hit=65

* ->  Seq Scan on tms_vessel_visit v
 (cost=0.00..62.95 rows=995 width=18) (actual time=0.006..0.292 rows=995
loops=1)*   Buffers: shared hit=53
 ->  Hash  (cost=21.95..21.95 rows=995
width=13) (actual time=0.735..0.736 rows=995 loops=1)
   Buckets: 1024  Batches: 1  Memory
Usage: 52kB
   Buffers: shared hit=12

*  ->  Seq Scan on
tms_vessel_visit_aggregate a  (cost=0.00..21.95 rows=995 width=13) (actual
time=0.009..0.238 rows=995 loops=1) *
 Buffers: shared hit=12
 ->  Subquery Scan on "*SELECT* 2"  (cost=1.35..2.86
rows=1 width=88) (actual time=0.079..0.111 rows=14 loops=1)
   Buffers: shared hit=2
   ->  Hash Join  (cost=1.35..2.85 rows=1
width=6422) (actual time=0.078..0.107 rows=14 loops=1)

*Hash Cond: (((a_1.vessel_visit_c)::text =
(v_1.vessel_visit_c)::text) AND (a_1.vessel_call_nr = v_1.vessel_call_nr))
   *Buffers: shared hit=2

*  ->  Seq Scan on tms_vessel_visit_aggregate_bak
a_1  (cost=0.00..1.33 rows=33 width=60) (actual time=0.007..0.011
rows=33loops=1)   *Buffers: shared hit=1
 ->  Hash  (cost=1.14..1.14 rows=14
width=98) (actual time=0.043..0.043 rows=14 loops=1)
   Buckets: 1024  Batches: 1  Memory
Usage: 9kB
   Buffers: shared hit=1

*  ->  Seq Scan on tms_vessel_visit_bak v_1
 (cost=0.00..1.14 rows=14 width=98) (actual time=0.007..0.034 rows=14
loops=1) *Buffers: shared hit=1


Executing Same Stuff in Oracle as its not doing implicit typecasting it
gives us perfect Index scans as expected.

|   8 | UNION ALL PUSHED PREDICATE   ||
  |   ||  |
|   9 |  NESTED LOOPS||
1 |30 | 3   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID| TMS_VESSEL_VISIT_AGGREGATE |
1 |12 | 2   (0)| 00:00:01 |
|* 11 |INDEX UNIQUE SCAN | TMS_VESSEL_VISIT_AGGREGATE_PK  |
1 |   | 1   (0)| 00:00:01 |
|  12 |   TABLE ACCESS BY INDEX ROWID| TMS_VESSEL_VISIT   |
1 |18 | 1   (0)| 00:00:01 |
|* 13 |INDEX UNIQUE SCAN | TMS_VESSEL_VISIT_PK|
1 |   | 0   (0)| 00:00:01 |
|  14 |  NESTED LOOPS||
1 |29 | 2   (0)| 00:00:01 |
|  15 |   TABLE ACCESS BY INDEX ROWID| TMS_VESSEL_VISIT_AGGREGATE_BAK |
1 |12 | 1   (0)| 00:00:01 |
|* 16 |INDEX UNIQUE SCAN | TMS_VESSEL_VISIT_AGG_BAK_PK|
1 |   | 0   (0)| 00:00:01 |
|  17 |   TABLE ACCESS BY INDEX ROWID| TMS_VESSEL_VISIT_BAK   |
1 |17 | 1   (0)| 00:00:01 |
|* 18 |INDEX UNIQUE SCAN | TMS_VESSEL_VISIT_BAK_PK|
1 |   | 0   (0)| 00:00:01 |
---


Wh

Re: PostgreSQL Views Where Clause Character Columns getting Typecasted to Text and Indexes not getting Used

[Rob Sargent]

> On Mar 25, 2021, at 10:32 PM, Jagmohan Kaintura  
> wrote:
> 
> 
> Hi All,
> 
> PostgreSQL version : 12.5
> 
> When we are compiling View definition into database , the where clause on the 
> Character Columns is getting internally typecasted to text. 
> Example : Source Code being Applied :
> 
> " FROM tms_vessel_visit_aggregate a, tms_vessel_visit v
> LEFT OUTER JOIN tms_vsl_svc s ON (v.svc_c = s.svc_c)
> WHERE v.vessel_visit_c = a.vessel_visit_c and v.vessel_call_nr = 
> a.vessel_call_nr
> "
> 
> Now when it's stored internally in the database the columns are getting 
> typecast with text but those table columns are not text they are varchar(10).
> 
> Example :
>FROM tms_vessel_visit_aggregate a,
> tms_vessel_visit v
>  LEFT JOIN tms_vsl_svc s ON v.svc_c::text = s.svc_c::text
>   WHERE v.vessel_visit_c::text = a.vessel_visit_c::text AND v.vessel_call_nr 
> = a.vessel_call_nr
> 
> Even when we placed the casting in the Original Source to varchar(10), its 
> typecasting internally to ::text .
> 
> These Columns are Primary Key columns and because of this Type Casting on 
> those columns Index scan is not happening and we are always getting 
> Sequential Scan. 
> Example :
> 
>  ->  Subquery Scan on "*SELECT* 1"  (cost=36.88..115.01 
> rows=995 width=13) (actual time=0.763..3.144 rows=995 loops=1)
>Buffers: shared hit=65
>->  Hash Join  (cost=36.88..105.06 rows=995 
> width=6361) (actual time=0.763..2.964 rows=995 loops=1)
>  Hash Cond: (((v.vessel_visit_c)::text = 
> (a.vessel_visit_c)::text) AND (v.vessel_call_nr = a.vessel_call_nr))
>  Buffers: shared hit=65
>  ->  Seq Scan on tms_vessel_visit v  
> (cost=0.00..62.95 rows=995 width=18) (actual time=0.006..0.292 rows=995 
> loops=1)
>Buffers: shared hit=53
>  ->  Hash  (cost=21.95..21.95 rows=995 
> width=13) (actual time=0.735..0.736 rows=995 loops=1)
>Buckets: 1024  Batches: 1  Memory 
> Usage: 52kB
>Buffers: shared hit=12
>->  Seq Scan on 
> tms_vessel_visit_aggregate a  (cost=0.00..21.95 rows=995 width=13) (actual 
> time=0.009..0.238 rows=995 loops=1)
>  Buffers: shared hit=12
>  ->  Subquery Scan on "*SELECT* 2"  (cost=1.35..2.86 
> rows=1 width=88) (actual time=0.079..0.111 rows=14 loops=1)
>Buffers: shared hit=2
>->  Hash Join  (cost=1.35..2.85 rows=1 width=6422) 
> (actual time=0.078..0.107 rows=14 loops=1)
>  Hash Cond: (((a_1.vessel_visit_c)::text = 
> (v_1.vessel_visit_c)::text) AND (a_1.vessel_call_nr = v_1.vessel_call_nr))
>  Buffers: shared hit=2
>  ->  Seq Scan on 
> tms_vessel_visit_aggregate_bak a_1  (cost=0.00..1.33 rows=33 width=60) 
> (actual time=0.007..0.011 rows=33loops=1)
>Buffers: shared hit=1
>  ->  Hash  (cost=1.14..1.14 rows=14 width=98) 
> (actual time=0.043..0.043 rows=14 loops=1)
>Buckets: 1024  Batches: 1  Memory 
> Usage: 9kB
>Buffers: shared hit=1
>->  Seq Scan on tms_vessel_visit_bak 
> v_1  (cost=0.00..1.14 rows=14 width=98) (actual time=0.007..0.034 rows=14 
> loops=1)
>  Buffers: shared hit=1
> 
> 
> Executing Same Stuff in Oracle as its not doing implicit typecasting it gives 
> us perfect Index scans as expected.
> 
> |   8 | UNION ALL PUSHED PREDICATE   ||   
> |   ||  |
> |   9 |  NESTED LOOPS||   
>   1 |30 | 3   (0)| 00:00:01 |
> |  10 |   TABLE ACCESS BY INDEX ROWID| TMS_VESSEL_VISIT_AGGREGATE |   
>   1 |12 | 2   (0)| 00:00:01 |
> |* 11 |INDEX UNIQUE SCAN | TMS_VESSEL_VISIT_AGGREGATE_PK  |   
>   1 |   | 1   (0)| 00:00:01 |
> |  12 |   TABLE ACCESS BY INDEX ROWID| TMS_VESSEL_VISIT   |   
>   1 |18 | 1   (0)| 00:00:01 |
> |* 13 |INDEX UNIQUE SCAN | TMS_VESSEL_VISIT_PK|   
>   1 |   | 0   (0)| 00:00:01 |
> |  14 |  NESTED LOOPS||   
>   1 |29 | 2   (0)| 00:00:01 |
> |  15 |   TABLE ACCESS BY INDEX ROWID| TMS_VESSEL_VISIT_AGGREGATE_BAK |   
>   1 |12 | 1   (0)| 00:00:01 |
> |* 16 |INDEX UNIQUE SCAN | TMS_VESSEL_VISIT_AGG_BAK_PK|   
>   1 |   | 0   (0)| 00:00:01 |
> |  17 |

Re: PostgreSQL Views Where Clause Character Columns getting Typecasted to Text and Indexes not getting Used

[dinesh kumar]

Hi Jagmohan,

The issue is reproducible in my environment.

postgres=# SELECT version();
  version

 PostgreSQL 13.0, compiled by Visual C++ build 1900, 64-bit
(1 row)

postgres=# CREATE TABLE public.test(t *varchar(10)*);
CREATE TABLE
postgres=# CREATE VIEW public.test_view AS SELECT * FROM public.test WHERE
t='10';
CREATE VIEW

postgres=# SELECT pg_get_viewdef('public.test_view');
 pg_get_viewdef

  SELECT test.t+
FROM public.test   +
   WHERE ((test.t)::text = '10'::text);
(1 row)

@Jagmohan,
Would you please confirm that, whenever you execute the view's base query
without casting, is the query plan showing the index?

On Fri, Mar 26, 2021 at 10:02 AM Jagmohan Kaintura 
wrote:

> Hi All,
>
> PostgreSQL version : 12.5
>
> When we are compiling View definition into database , the where clause on
> the Character Columns is getting internally typecasted to text.
> Example : Source Code being Applied :
>
> " FROM tms_vessel_visit_aggregate a, tms_vessel_visit v
> LEFT OUTER JOIN tms_vsl_svc s ON (v.svc_c = s.svc_c)
> WHERE v.vessel_visit_c = a.vessel_visit_c and v.vessel_call_nr =
> a.vessel_call_nr
> "
>
> Now when it's stored internally in the database the columns are getting
> typecast with *text *but those table columns are not text they are
> varchar(10).
>
> Example :
>FROM tms_vessel_visit_aggregate a,
> tms_vessel_visit v
>  LEFT JOIN tms_vsl_svc s ON *v.svc_c::text* = *s.svc_c::text*
>   WHERE *v.vessel_visit_c::text* = *a.vessel_visit_c::text* AND
> v.vessel_call_nr = a.vessel_call_nr
>
> Even when we placed the casting in the Original Source to varchar(10), its
> typecasting internally to *::text .*
>
> These Columns are Primary Key columns and because of this Type Casting on
> those columns Index scan is not happening and we are always getting
> Sequential Scan.
> Example :
>
>  ->  Subquery Scan on "*SELECT* 1"
>  (cost=36.88..115.01 rows=995 width=13) (actual time=0.763..3.144 rows=995
> loops=1)
>Buffers: shared hit=65
>->  Hash Join  (cost=36.88..105.06 rows=995
> width=6361) (actual time=0.763..2.964 rows=995 loops=1)
>
> * Hash Cond: (((v.vessel_visit_c)::text =
> (a.vessel_visit_c)::text) AND (v.vessel_call_nr = a.vessel_call_nr))*
>  Buffers: shared hit=65
>
> * ->  Seq Scan on tms_vessel_visit v
>  (cost=0.00..62.95 rows=995 width=18) (actual time=0.006..0.292 rows=995
> loops=1)*   Buffers: shared hit=53
>  ->  Hash  (cost=21.95..21.95 rows=995
> width=13) (actual time=0.735..0.736 rows=995 loops=1)
>Buckets: 1024  Batches: 1  Memory
> Usage: 52kB
>Buffers: shared hit=12
>
> *  ->  Seq Scan on
> tms_vessel_visit_aggregate a  (cost=0.00..21.95 rows=995 width=13) (actual
> time=0.009..0.238 rows=995 loops=1) *
>Buffers: shared hit=12
>  ->  Subquery Scan on "*SELECT* 2"  (cost=1.35..2.86
> rows=1 width=88) (actual time=0.079..0.111 rows=14 loops=1)
>Buffers: shared hit=2
>->  Hash Join  (cost=1.35..2.85 rows=1
> width=6422) (actual time=0.078..0.107 rows=14 loops=1)
>
> *Hash Cond: (((a_1.vessel_visit_c)::text =
> (v_1.vessel_visit_c)::text) AND (a_1.vessel_call_nr = v_1.vessel_call_nr))
>*Buffers: shared hit=2
>
> *  ->  Seq Scan on tms_vessel_visit_aggregate_bak
> a_1  (cost=0.00..1.33 rows=33 width=60) (actual time=0.007..0.011
> rows=33loops=1)   *Buffers: shared
> hit=1
>  ->  Hash  (cost=1.14..1.14 rows=14
> width=98) (actual time=0.043..0.043 rows=14 loops=1)
>Buckets: 1024  Batches: 1  Memory
> Usage: 9kB
>Buffers: shared hit=1
>
> *  ->  Seq Scan on tms_vessel_visit_bak
> v_1  (cost=0.00..1.14 rows=14 width=98) (actual time=0.007..0.034 rows=14
> loops=1) *Buffers: shared
> hit=1
>
>
> Executing Same Stuff in Oracle as its not doing implicit typecasting it
> gives us perfect Index scans as expected.
>
> |   8 | UNION ALL PUSHED PREDICATE   |
>  |   |   ||  |
> |   9 |  NESTED LOOPS|
>  | 1 |30 | 3   (0)| 00:00:01 |
> |  10 |   TABLE ACCESS BY INDEX ROWID| TMS_VESSEL_VISIT_AGGREGATE
> | 1 |12 | 2   (0)| 00:00:01 |
> |* 11 |INDEX UNIQUE SCAN | TMS_VESSEL_VISIT_AGGREGATE

Re: PostgreSQL Views Where Clause Character Columns getting Typecasted to Text and Indexes not getting Used

[Tom Lane]

Jagmohan Kaintura  writes:
> When we are compiling View definition into database , the where clause on
> the Character Columns is getting internally typecasted to text.

That's normal.  In Postgres, varchar has no operators of its own;
everything you might do with it involves a (no-cost) cast to text.
You might think of varchar as being a domain over text, though for
historical reasons it's not implemented exactly that way.

> These Columns are Primary Key columns and because of this Type Casting on
> those columns Index scan is not happening and we are always getting
> Sequential Scan.

This, on the other hand, is just nonsense.  You haven't presented
any examples in which an indexscan looks likely to be a win.
Postgres generally won't bother with an index when scanning a tiny
table.  Nor is an index necessarily helpful when doing a join,
unless the join is designed to access just a small part of the table.

> Please help us in identifying the solution.

Please read
https://wiki.postgresql.org/wiki/Slow_Query_Questions

regards, tom lane