change ownership of schema public?

2021-09-27 Thread Zwettler Markus (OIZ) 
A database user is having all its objects into the public schema.
Now he requests to become the owner of the public schema to be able to do 
"reindex schema public".

I'm wondering if it is a good idea to change the ownership of the public schema?
Are there any possible side effects e.g. regarding extensions?

Thanks, Markus

statement_timeout vs DECLARE CURSOR

2021-09-27 Thread Christophe Pettus 
Hi,

We've encountered some unexpected behavior with statement_timeout not 
cancelling a query in DECLARE CURSOR, but only if the DECLARE CURSOR is outside 
of a transaction:

xof=# select version();
  version   
   
---
 PostgreSQL 13.4 on x86_64-apple-darwin19.6.0, compiled by Apple clang version 
12.0.0 (clang-1200.0.32.29), 64-bit
(1 row)

xof=# set statement_timeout = '1s';
SET
xof=# \timing
Timing is on.
xof=# select * from (with test as (select pg_sleep(10), current_timestamp as 
cur_time) select 1 from test ) as slp;
ERROR:  canceling statement due to statement timeout
Time: 1000.506 ms (00:01.001)
xof=# declare x no scroll cursor with hold for select * from (with test as 
(select pg_sleep(10), current_timestamp as cur_time) select 1 from test ) as 
slp;
DECLARE CURSOR
Time: 10001.929 ms (00:10.002)
xof=# 

but:

xof=# set statement_timeout = '1s';
SET
xof=# \timing
Timing is on.
xof=# begin;
BEGIN
Time: 0.161 ms
xof=*# declare x no scroll cursor with hold for select * from (with test as 
(select pg_sleep(10), current_timestamp as cur_time) select 1 from test ) as 
slp;
DECLARE CURSOR
Time: 0.949 ms
xof=*# fetch all from x;
ERROR:  canceling statement due to statement timeout
Time: 1000.520 ms (00:01.001)
xof=!# abort;
ROLLBACK
Time: 0.205 ms
xof=#

Re: statement_timeout vs DECLARE CURSOR

2021-09-27 Thread Christophe Pettus 



> On Sep 27, 2021, at 10:42, Christophe Pettus  wrote:
> We've encountered some unexpected behavior with statement_timeout not 
> cancelling a query in DECLARE CURSOR, but only if the DECLARE CURSOR is 
> outside of a transaction:

A bit more poking revealed the reason: The ON HOLD cursor's query is executed 
at commit time (which is, logically, not interruptible), but that's all wrapped 
in the single statement outside of a transaction.

Re: statement_timeout vs DECLARE CURSOR

2021-09-27 Thread Tom Lane 
Christophe Pettus  writes:
>> On Sep 27, 2021, at 10:42, Christophe Pettus  wrote:
>> We've encountered some unexpected behavior with statement_timeout not 
>> cancelling a query in DECLARE CURSOR, but only if the DECLARE CURSOR is 
>> outside of a transaction:

> A bit more poking revealed the reason: The ON HOLD cursor's query is executed 
> at commit time (which is, logically, not interruptible), but that's all 
> wrapped in the single statement outside of a transaction.

Hmm ... seems like a bit of a UX failure.  I wonder why we don't persist
such cursors before we get into the uninterruptible part of COMMIT.

regards, tom lane