date:20190228

Re: New vacuum option to do only freezing

2019-02-28 Thread Masahiko Sawada

On Thu, Feb 28, 2019 at 2:46 AM Bossart, Nathan  wrote:
>
> On 2/27/19, 2:08 AM, "Masahiko Sawada"  wrote:
> >> +   if (skip_index_vacuum)
> >> +   appendStringInfo(&buf, ngettext("%.0f tuple is left as 
> >> dead.\n",
> >> +  
> >>  "%.0f tuples are left as dead.\n",
> >> +  
> >>  nleft),
> >> +nleft);
> >>
> >> I think we could emit this metric for all cases, not only when
> >> DISABLE_INDEX_CLEANUP is used.
> >
> > I think that tups_vacuumed shows total number of vacuumed tuples and
> > is already shown in the log message. The 'nleft' counts the total
> > number of recorded dead tuple but not counts tuples are removed during
> > HOT-pruning. Is this a valuable for users in non-DISABLE_INDEX_CLEANUP
> > case?
>
> I think it is valuable.  When DISABLE_INDEX_CLEANUP is not used or it
> is used for a relation with no indexes, it makes it clear that no
> tuples were left marked as dead.  Also, it looks like all of the other
> information here is provided regardless of the options used.  IMO it
> is good to list all of the stats so that users have the full picture
> of what VACUUM did.
>

I see your point. That seems good to me.

Attached the updated version patch. I've incorporated all review
comments I got and have changed the number of tuples being reported as
'removed tuples'. With this option, tuples completely being removed is
only tuples marked as unused during HOT-pruning, other dead tuples are
left. So we count those tuples during HOT-pruning and reports it as
removed tuples.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


v7-0001-Add-DISABLE_INDEX_CLEANUP-option-to-VACUUM-comman.patch
Description: Binary data


v7-0002-Add-diable-index-cleanup-option-to-vacuumdb.patch
Description: Binary data

Re: Prevent extension creation in temporary schemas

2019-02-28 Thread Sergei Kornilov

Hi

> I found that this strange error appears after making
> temporary tables.
>
> test=> CREATE TEMPORARY TABLE temp (id int);
> CREATE TABLE
> test=> CREATE EXTENSION file_fdw WITH SCHEMA pg_temp_3;
> ERROR: function file_fdw_handler() does not exist
>
> I would try to understand this problem for community and
> my experience.

This behavior seems as not related to extensions infrastructure:

postgres=#  CREATE TEMPORARY TABLE temp (id int);
CREATE TABLE
postgres=# set search_path to 'pg_temp_3';
SET
postgres=# create function foo() returns int as 'select 1' language sql;
CREATE FUNCTION
postgres=# select pg_temp_3.foo();
 foo 
-
   1
(1 row)

postgres=# select foo();
ERROR:  function foo() does not exist
LINE 1: select foo();
   ^
HINT:  No function matches the given name and argument types. You might need to 
add explicit type casts.
postgres=# show search_path ;
 search_path 
-
 pg_temp_3
(1 row)

regards, Sergei

RE: reloption to prevent VACUUM from truncating empty pages at the end of relation

2019-02-28 Thread Tsunakawa, Takayuki

From: Alvaro Herrera [mailto:alvhe...@2ndquadrant.com]
> Robert used the phrase "attractive nuisance", which maybe sounds like a
> good thing to have to a non native speaker, but it actually isn't -- he
> was saying we should avoid a GUC at all, and I can see the reason for
> that.  I think we should have a VACUUM option and a reloption, but no
> GUC.

Uh, thanks.  I've just recognized I didn't know the meaning of "nuisance."  
I've looked up the meaning in the dictionary.  Nuisance is like a trouble 
maker...

Why do you think that it's better for VACUUM command to have the option?  I 
think it's a table property whose value is determined based on the application 
workload, not per VACUUM execution.  Rather, I think GUC is more useful to 
determine the behavior of the entire database and/or application.

If we want to change a given execution of VACUUM, then we can ALTER TABLE SET, 
VACUUM, and ALTER TABLE SET back.


Regards
Takayuki Tsunakawa

RE: Problem with default partition pruning

2019-02-28 Thread Imai, Yoshikazu

Hosoya-san

On Wed, Feb 27, 2019 at 6:51 AM, Yuzuko Hosoya wrote:
> > From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp]
> > Sent: Wednesday, February 27, 2019 11:22 AM
> >
> > Hosoya-san,
> >
> > On 2019/02/22 17:14, Yuzuko Hosoya wrote:
> > > Hi,
> > >
> > > I found the bug of default partition pruning when executing a range
> query.
> > >
> > > -
> > > postgres=# create table test1(id int, val text) partition by range
> > > (id); postgres=# create table test1_1 partition of test1 for values
> > > from (0) to (100); postgres=# create table test1_2 partition of
> > > test1 for values from (150) to (200); postgres=# create table
> > > test1_def partition of test1 default;
> > >
> > > postgres=# explain select * from test1 where id > 0 and id < 30;
> > >QUERY PLAN
> > > 
> > >  Append  (cost=0.00..11.83 rows=59 width=11)
> > >->  Seq Scan on test1_1  (cost=0.00..5.00 rows=58 width=11)
> > >  Filter: ((id > 0) AND (id < 30))
> > >->  Seq Scan on test1_def  (cost=0.00..6.53 rows=1 width=12)
> > >  Filter: ((id > 0) AND (id < 30))
> > > (5 rows)
> > >
> > > There is no need to scan the default partition, but it's scanned.
> > > -
> > >
> > > In the current implement, whether the default partition is scanned
> > > or not is determined according to each condition of given WHERE
> > > clause at get_matching_range_bounds().  In this example,
> > > scan_default is set true according to id > 0 because id >= 200
> > > matches the default partition.  Similarly, according to id < 30,
> scan_default is set true.
> > > Then, these results are combined according to AND/OR at
> perform_pruning_combine_step().
> > > In this case, final result's scan_default is set true.
> > >
> > > The modifications I made are as follows:
> > > - get_matching_range_bounds() determines only offsets of range bounds
> > >   according to each condition
> > > - These results are combined at perform_pruning_combine_step()
> > > - Whether the default partition is scanned or not is determined at
> > >   get_matching_partitions()
> > >
> > > Attached the patch.  Any feedback is greatly appreciated.
> >
> > Thank you for reporting.  Can you please add this to March CF in Bugs
> > category so as not to lose
> track
> > of this?
> >
> > I will try to send review comments soon.
> >
> Thank you for your reply.  I added this to March CF.

I tested with simple use case and I confirmed it works correctly like below.

In case using between clause:
postgres=# create table test1(id int, val text) partition by range (id); 
postgres=# create table test1_1 partition of test1 for values from (0) to 
(100); 
postgres=# create table test1_2 partition of test1 for values from (150) to 
(200);
postgres=# create table test1_def partition of test1 default; 

[HEAD]
postgres=# explain analyze select * from test1 where id between 0 and 50;
QUERY PLAN  
   
---
 Append  (cost=0.00..58.16 rows=12 width=36) (actual time=0.008..0.008 rows=0 
loops=1)
   ->  Seq Scan on test1_1  (cost=0.00..29.05 rows=6 width=36) (actual 
time=0.005..0.005 rows=0 loops=1)
 Filter: ((id >= 0) AND (id <= 50))
   ->  Seq Scan on test1_def  (cost=0.00..29.05 rows=6 width=36) (actual 
time=0.002..0.002 rows=0 loops=1)
 Filter: ((id >= 0) AND (id <= 50))


[patched]
postgres=# explain analyze select * from test1 where id between 0 and 50;
   QUERY PLAN   
 
-
 Append  (cost=0.00..29.08 rows=6 width=36) (actual time=0.006..0.006 rows=0 
loops=1)
   ->  Seq Scan on test1_1  (cost=0.00..29.05 rows=6 width=36) (actual 
time=0.004..0.005 rows=0 loops=1)
 Filter: ((id >= 0) AND (id <= 50))



I considered about another use case. If default partition contains rows whose 
id = 300 and then we add another partition which have constraints like id >= 
300 and id < 400, I thought we won't scan the rows anymore. But I noticed we 
simply can't add such a partition.

postgres=# insert into test1 values (300);
INSERT 0 1
postgres=# create table test1_3 partition of test1 for values from (300) to 
(400); 
ERROR:  updated partition constraint for default partition "test1_def" would be 
violated by some row


So I haven't come up with bad cases so far :)

--
Yoshikazu Imai

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

2019-02-28 Thread Laurenz Albe

Tsunakawa, Takayuki wrote:
> Why do you think that it's better for VACUUM command to have the option?  I 
> think it's a
> table property whose value is determined based on the application workload, 
> not per VACUUM
> execution.  Rather, I think GUC is more useful to determine the behavior of 
> the entire
> database and/or application.

I cannot speak for Alvaro, but I think that many people think that a global 
setting
is too dangerous (I personally don't think so).

And if we don't have a GUC, an option to VACUUM would be convenient for one-time
clean-up of a table where taking a truncation lock would be too disruptive.

> If we want to change a given execution of VACUUM, then we can ALTER TABLE 
> SET, VACUUM,
> and ALTER TABLE SET back.

True. That ALTER TABLE would probably need a SHARE UPDATE EXCLUSIVE lock on the 
table,
and that's no worse than VACUUM itself.

Yours,
Laurenz Albe

extension patch of CREATE OR REPLACE TRIGGER

2019-02-28 Thread Osumi, Takamichi

Dear hackers

Hi there !
One past thread about introducing CREATE OR REPLACE TRIGGER into the syntax
had stopped without complete discussion in terms of LOCK level.

The past thread is this. I'd like to inherit this one.
https://www.postgresql.org/message-id/flat/0B4917A40C80E34BBEC4BE1A7A9AB7E276F5D9%40g01jpexmbkw05#39f3c956d549c134474724d2b775399c
Mr. Tom Lane mentioned that this change requires really careful study in this
thread.

First of all, please don't forget I don't talk about DO CLAUSE in this thread.
Secondly, Mr. Surafel Temesgen pointed out a bug but it doesn't appear.

Anyway, let's go back to the main topic.
>From my perspective, how CREATE OR REPLACE TRIGGER works is,
when there is no counterpart replaced by a new trigger,
CREATE TRIGGER is processed with SHARE ROW EXCLUSIVE LOCK as usual.

On the other hand, when there's,
REPLACE TRIGGER procedure is executed with ACCESS EXCLUSIVE LOCK.

This feeling comes from my idea
that acquiring ACCESS EXCLUSIVE LOCK when replacing trigger occurs
provides data consistency between transactions and protects concurrent pg_dump.

In order to make this come true, as the first step,
I've made a patch to add CREATE OR REPLACE TRIGGER with some basic tests in
triggers.sql.

Yet, I'm still wondering which part of LOCK level in this patch should be
raised to ACCESS EXCLUSIVE LOCK.
Could anyone give me an advise about
how to protect the process of trigger replacement in the way I suggested above ?

1 2 >

1 - 100 of 157 matches

Mail list logo