On Thu, 2020-03-19 at 15:17 -0700, Andres Freund wrote:
> I am doubtful it should be committed with the current settings. See below.
>
> > From 3ba4b572d82969bbb2af787d1bccc72f417ad3a0 Mon Sep 17 00:00:00 2001
> > From: Laurenz Albe
> > Date: Thu, 19 Mar 2020 20:26:43 +
ing one final look, I'd like to push this during my
> Monday (New Zealand time). So if anyone strongly objects to that,
> please state their case before then.
Thanks!
I have rolled your edits into the attached patch v9, rebased against
current master.
Yours,
Laurenz Albe
From 3ba4b572d82969bbb
for the table)? That might still be high enough not to needlessly
freeze too many tuples that will still be modified, but it will
reduce the impact on insert-only tables.
Yours,
Laurenz Albe
On Tue, 2020-03-17 at 17:26 -0700, Andres Freund wrote:
> On 2020-03-17 01:14:02 +0100, Laurenz Albe wrote:
> > lazy_check_needs_freeze() is only called for an aggressive vacuum, which
> > this isn't.
>
> Hm? I mean some of these will be aggressive vacuums, beca
nsert_scale_factor to 1e10.
Yours,
Laurenz Albe
From cc44042d4a07804a21abe7ad54a8dfafd3162228 Mon Sep 17 00:00:00 2001
From: Laurenz Albe
Date: Tue, 17 Mar 2020 22:51:46 +0100
Subject: [PATCH] Autovacuum tables that have received only inserts
Add "autovacuum_
the feature, it wouldn't make much sense to allow factor up to
> 100.
True, we could set the upper limit to 2, but it doesn't matter much.
Note that this is different from autovacuum_vacuum_scale_factor,
because inserted tuples are live, while dead tuples are not.
Yours,
Laurenz Albe
rted_tuples
becomes
1000 + 2 * (n + m) < m
which can never be true for non-negative n and m.
So a scale factor >= 1 disables the feature.
Yours,
Laurenz Albe
vacuum, it usually
> means "visit all pages, even those which are allvisible".
This is gone in the latest patch.
Updated patch attached.
Perhaps we can reach a consensus on this reduced functionality.
Yours,
Laurenz Albe
From 547481033898f6e8e028e45684d4bb
ntial for a lot more contention.
I think I got it.
Here is a version of the patch that does *not* freeze more tuples than
normal, except if a prior tuple on the same page is already eligible for
freezing.
lazy_check_needs_freeze() is only called for an aggressive vacuum, which
this isn't.
Does that l
after a bulk insert.
The idea of autovacuum is to do these things for you atomatically.
Yours,
Laurenz Albe
quot;really large vacuum run".
Ah, yes, you are right.
So it actually would not be worse if we use the normal freeze_min_age
for insert-only vacuums.
So do you think the patch would be ok as it is if we change only that?
Yours,
Laurenz Albe
ws have reached vacuum_freeze_table_age yet.
Then some time later you will get a really large vacuum run.
It seems to me that if we keep trying finding the formula that will vacuum
every table just right and never so the wrong thing, we will never get to
anything.
Yours,
Laurenz Albe
more parameters :^( But your reasoning is good.
How about we go with what we have now and leave that for future
discussion and patches?
Yours,
Laurenz Albe
much lower (10?) or use (0.2 * n_ins + 50) like the other autovacuum GUC.
There is the concern that that might treat large table to seldom.
I am curious - what were the findings that led you to think that 1000
is too high?
Yours,
Laurenz Albe
cuum_cleanup_index_scale_factor uses max: 1e10
> See 4d54543efa5eb074ead4d0fadb2af4161c943044
By setting the threshold very high, or by setting the scale factor to 100.
Yours,
Laurenz Albe
>
> > 3. introduce the new parameters with low base threshold and high scale
> > factor.
>
> This looks bad to me. "the bigger the table, the longer we wait" does
> not look good for me for something designed as a measure preventing
> issues with big tables.
Thanks for the feedback.
It looks like we have a loose consensus on #2, i.e. my patch.
Yours,
Laurenz Albe
igh scale factor.
I think all three are viable.
If nobody else wants to weigh in, throw a coin.
Yours,
Laurenz Albe
aragraph to the documentation that tells people how to
configure the parameters if they want to use it to get index-only scans.
Yours,
Laurenz Albe
d party LLVM may be required to build the server's
> llvmjit support. Work around by skipping the default .bc generation if
> no clang is found by PGXS, as if $(with_llvm) was false.
+1
I have struggled with this, as have several users trying to build oracle_fdw.
Yours,
Laurenz Albe
s?
If you have an insert-only table that has 10 entries, it will get
vacuumed roughly every 2 new entries. The impact is probably too
little to care, but it will increase the contention for the three
autovacuum workers available by default.
Yours,
Laurenz Albe
ble_age;
> tab->at_params.multixact_freeze_min_age = multixact_freeze_min_age;
> tab->at_params.multixact_freeze_table_age =
> multixact_freeze_table_age;
>
> I think we can set multixact_freeze_min_age to 0 as well.
Ugh, yes, that is a clear oversight.
I have fixed it in the latest version.
Yours,
Laurenz Albe
ince the parameters have similar semantics, a different wording
would confuse.
Yours,
Laurenz Albe
the purpose of this setting.
Understanding the purpose of the GUCs will make it easier to tune them
correctly.
> 6. Please run the regression tests and make sure they pass. The
> "rules" test is currently failing due to the new column in
> "pg_stat_all_tables&
Scans for 3 days. The DBAs don't have a lot of control
> over this.
>
> I think we can help users with that by giving them a bit more control
> over when auto-vacuum will run for the table. scale_factor and
> threshold.
Oh, that's a good point.
I only thought about anti-wraparound vacuum, but the feature might be useful
for index-only scans as well.
Yours,
Laurenz Albe
Thanks, Justin, for the review.
I have applied the changes where still applicable.
On Fri, 2020-03-06 at 10:52 +1300, David Rowley wrote:
> On Fri, 6 Mar 2020 at 03:27, Laurenz Albe wrote:
> > On Thu, 2020-03-05 at 19:40 +1300, David Rowley wrote:
> > > 1. I'd go for 2 new G
h number of.
Yes, I think that disabling this by default defeats the purpose.
Knowledgeable people can avoid the problem today by manually scheduling
VACUUM runs on insert-only tables, and the functionality proposed here
is specifically to improve the lives of people who don't know enough
to t
On Tue, 2020-03-03 at 16:28 +0100, Laurenz Albe wrote:
> As a more substantial base for discussion, here is a patch that:
>
> - introduces a GUC and reloption "autovacuum_vacuum_insert_limit",
> default 1000
>
> - introduces a statistics counter &quo
atch is included yet, and perhaps the new counter should
be shown in "pg_stat_user_tables".
Yours,
Laurenz Albe
From fee7443b4b8d965c77f90a631f1245217624bd44 Mon Sep 17 00:00:00 2001
From: Laurenz Albe
Date: Tue, 3 Mar 2020 16:21:01 +0100
Subject: [PATCH] Autovacuum tables that have re
f the others, lauch autovacuum with index_cleanup=off.
How would you feel about that?
Yours,
Laurenz Albe
ERROR in
> response to an attempted COMMIT would be broken already.
I agree with that.
There is always some chance that someone relies on COMMIT not
throwing an error when it rolls back, but I think that throwing an
error is actually less astonishing than *not* throwing one.
So, +1 for the proposal from me.
Yours,
Laurenz Albe
ion like Oracle did.
Yours,
Laurenz Albe
otherwise verifying its
consistency. Be aware that incorrect use of this command can hide
index corruption.
I didn't study the patch in detail, but do I get it right that there will be no
warnings about version incompatibilities with libc collations?
Yours,
Laurenz Albe
d_buffers, whereas positive
> values are absolute sizes, and 0 disables the use of ringbuffers.
Sounds reasonable.
I feel that it should be as few GUCs as possible, so I think that
having one per type of operation might be too granular.
This should of course also be a storage parameter that can be
On Mon, 2020-02-03 at 14:43 -0500, Tom Lane wrote:
> Laurenz Albe writes:
> > I noticed that "ctid" in the select list prevents an index only scan:
> > This strikes me as strange, since every index contains "ctid".
>
> There's no provision for an IOS to r
lt; 100;
QUERY PLAN
--
Update on laurenz.ios
-> Index Scan using ios_id_idx on laurenz.ios
Output: id, ''::text, ctid
Index Cond: (ios.id < 100)
(4 rows)
Is this low hanging fruit? If yes, I might take a stab at it.
Yours,
Laurenz Albe
recommended to use pg_dumpall
from the newer version". They don't say "is is not supported".
Yours,
Laurenz Albe
ad-only" if it is allowed on a streaming replication standby?
The first would be more correct, but the second may be more convenient.
Yours,
Laurenz Albe
er nice aspect is that this is an activity that can easily
be adjusted to span three months; if you embark on a new feature, the
three months may pass without your patch getting accepted.
Yours,
Laurenz Albe
; comparing the cost suffices.
Doesn't that rely on a specific implementation of double precision (IEEE)?
I thought that we don't want to limit ourselves to platforms with IEEE floats.
Yours,
Laurenz Albe
> runs on all the tables in the list.
That means that all partitions are vacuumed if only one of them needs it,
right? This will result in way more vacuuming than necessary.
Wouldn't it be an option to update the partitioned table's statistics
whenever one of the partitions is vacuumed?
Yours,
Laurenz Albe
xxx (cost=0.00..194.00 rows=60 width=4) (actual
> time=0.041..4.276 rows=11000 loops=1)
I think that this is asking for a planner support function:
https://www.postgresql.org/docs/current/xfunc-optimization.html
Yours,
Laurenz Albe
ot;dropuser"
explicitly connect to the "postgres" database rather than using
"connectMaintenanceDatabase()" like the other scripts, which would try
the database "postgres" first and fall back to "template1".
This is unrelated to the patch, but low-hanging fruit for unified behavior.
Yours,
Laurenz Albe
On Tue, 2019-11-19 at 13:21 -0500, Tom Lane wrote:
> Laurenz Albe writes:
> > On Fri, 2019-11-15 at 13:41 -0500, Tom Lane wrote:
> > > Laurenz Albe writes:
> > > > On Wed, 2019-11-13 at 17:17 -0500, Tom Lane wrote:
> > > > > It might be worth clarifyi
with more than 500, tuples are candidates for an
> autovacuum before they would with current default values.
I think this is unnecessary.
Usually you have problems only with a few tables, and it is no problem
to set autovacuum parameters on these individually.
Yours,
Laurenz Albe
On Fri, 2019-11-15 at 13:41 -0500, Tom Lane wrote:
> Laurenz Albe writes:
> > On Wed, 2019-11-13 at 17:17 -0500, Tom Lane wrote:
> > > It might be worth clarifying this point in section 5.7,
> > > https://www.postgresql.org/docs/devel/ddl-priv.html
> > > but l
On Wed, 2019-11-13 at 17:17 -0500, Tom Lane wrote:
> Laurenz Albe writes:
> > I realized only today that if role A is a member of role B,
> > A can ALTER and DROP objects owned by B.
> > I don't have a problem with that, but the documentation seems to
> > suggest otherw
.
Should I compose a doc patch, or is that too much of a corner case
to mention? I wanted to ask before I do the repetetive work.
Yours,
Laurenz Albe
A quick web search seems to indicate that Windows has no statfs(2).
What's more is that the Linux man page says that statfs(2) is
Linux-specific.
I think that if we have such a feature (which I think would be useful)
should be available for all operating systems supported by PostgreSQL.
Yours,
Laurenz Albe
.
I think that is an unpleasant surprise. I'd expect that if I drop a database
and restore it from a dump, it should be as it was before. This change would
break that assumption.
What are the use cases of a dump with foreign table data?
Unless I misunderstood something there, -1.
Yours,
Laurenz Albe
all affected indexes to keep PostgreSQL from nagging.
Perhaps the situation could be improved with a pg_upgrade option
--i-know-my-indexes-are-fine that causes a result like #2.
Together with a bold note in the release notes, this may relieve
the pain.
Yours,
Laurenz Albe
ter I
removed them, it applied fine.
Yours,
Laurenz Albe
so the function
could see no tuples older than the statement's snapshot.
So VACUUM could remove tuples that were visible when the transaction
started, but are not visible in the current statement's snapshot.
Of course a C function could completely ignore MVCC and access any
old tuple, but do we want to cater for that?
Yours,
Laurenz Albe
ontains the target.
In this case the proposed change will solve the problem.
2. Someone specified the recovery target wrong, e.g. used CET rather
than CEST in the recovery target time, so that the recovery target
was later than intended.
In that case the only solution is to start recovery from scratch.
But perhaps there are use cases I didn't think of.
Yours,
Laurenz Albe
er who uses the letter :-) Also, it would be super
> helpful if you review Alexey's patch:
> https://commitfest.postgresql.org/24/1849/
I believe that -R should be reserved for creating recovery.conf,
similar to pg_basebackup.
Everything else would be confusing.
I've been missing pg_rewind -R!
Yours,
Laurenz Albe
[CC to -hackers]
Dave Cramer wrote:
> On Mon, 26 Aug 2019 at 13:43,
Laurenz Albe
> wrote:
> > Dave Cramer wrote:
>
> > As I said, I'd entertain a connection parameter that switched the
>
> > CALL to call procedures but ideally you'd complain to the server
> &g
locking table, and tuple locks
are *not* maintained there, but in the "xmax" of the row itself.
To see all tuple locks in pg_locks would require a sequential
scan of all tables which have certain locks on them, which is not
going to happen.
Yours,
Laurenz Albe
Peter Eisentraut wrote:
> On 2019-05-08 16:49, Laurenz Albe wrote:
> > I believe we should have both:
> >
> > - Identity columns should only use sequences with an INTERNAL dependency,
> > as in Peter's patch.
>
> I have committed this.
Since this is a bug
Peter Eisentraut wrote:
> On 2019-05-08 16:49, Laurenz Albe wrote:
> > I believe we should have both:
> >
> > - Identity columns should only use sequences with an INTERNAL dependency,
> > as in Peter's patch.
>
> I have committed this.
Thanks!
> > - W
onous. I am
> finishing with the attached that I would be fine to commit and
> back-patch as needed. Does that sound fine?
It was my first reaction too that this had better be at the top.
I'm happy with the patch as it is.
Yours,
Laurenz Albe
On Thu, 2019-07-18 at 13:56 -0700, Jeff Davis wrote:
> I went ahead and committed this using Thomas's suggestion to remove the
> parentheses.
Thanks for the review and the commit!
Yours,
Laurenz Albe
ust not be set to
remote_apply if pg_receivewal
is the only synchronous standby.
Similarly, it is no use adding pg_receivewal to a
priority-based (FIRST) or a quorum-based
(ANY) synchronous replication setup if
is set to
remote_apply.
Yours,
Laurenz Albe
nd pg_receivewal
> is not the only synchronous standby in this configuration. The patch
> does not cover that case properly.
I understand the concern, I'm just worried that too much accuracy may
render the sentence hard to read.
How about adding "or priority-based" after "quorum-based"?
Yours,
Laurenz Albe
.
Similarly, if pg_receivewal is part of
a quorum-based set of synchronous standbys, it won't count towards
the quorum if is set to
remote_apply.
Yours,
Laurenz Albe
2013 or later on Windows (Andres Freund)
+
+
+
+
+ Use the same functions to open files in frontend and backend code
+ on Windows, thus fixing the "open_datasync"
+ test in "pg_test_fsync" (Laurenz Albe)
+
+
+
problem
in a way that few people would find surprising.
Yours,
Laurenz Albe
Jesper Pedersen wrote:
> Thanks for the review, and the changes.
>
> However, I think it belongs in the --synchronous section, so what about
> moving it there as attached ?
Works for me.
Marked as "ready for committer".
Yours,
Laurenz Albe
here is already something about "--synchronous" in the "Description"
section. It might make sense to add the additional information there.
How about the attached patch?
Yours,
Laurenz Albe
From c18b4b384a963e04cc5b5b50537c150858824f0a Mon Sep 17 00:00:00 2001
From: Laurenz
s
with that user.
But for your application users LDAP authentication is a fine thing, and not
hard to set up if you know a little bit about LDAP.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Tue, 2019-05-07 at 13:06 +0900, Michael Paquier wrote:
> On Fri, May 03, 2019 at 08:14:35AM +0200, Laurenz Albe wrote:
> > On Thu, 2019-05-02 at 22:43 +0200, Peter Eisentraut wrote:
> >> I think the proper way to address this would be to create some kind of
> >> depe
On Thu, 2019-05-02 at 22:43 +0200, Peter Eisentraut wrote:
> On 2019-04-29 18:28, Laurenz Albe wrote:
> > I still think thatthat there is merit to Michael's idea of removing
> > sequence "ownership" (which is just a dependency) when the DEFAULT
> > on the colum
I wrote:
> I propose the attached patch which fixes the problem.
I forgot to attach the patch. Here it is.
Yours,
Laurenz Albe
From 6bbad0acf3baae3a08d1f911b7017642c8a8afe9 Mon Sep 17 00:00:00 2001
From: Laurenz Albe
Date: Thu, 2 May 2019 14:32:27 +0200
Subject: [PATCH] Don't canonical
to infinity does not change the value.
I propose the attached patch which fixes the problem.
Yours,
Laurenz Albe
On Sat, 2019-04-27 at 14:16 +0200, Peter Eisentraut wrote:
> On 2019-04-26 15:37, Laurenz Albe wrote:
> > What do you think of the patch I just posted on this thread to
> > remove ownership automatically when the default is dropped, as Michael
> > suggested? I think that w
m
the user's perspective.
Correct me if I am wrong, but the sequence behind identity columns
should be an implementation detail that the user doesn't have to know about.
So the error message about "owned sequences" is likely to confuse users.
I have had a report by a confused user, so I think the problem is real.
Yours,
Laurenz Albe
On Thu, 2019-04-25 at 09:55 +0900, Michael Paquier wrote:
> On Sun, Apr 14, 2019 at 05:51:47PM +0200, Laurenz Albe wrote:
> > test=> INSERT INTO ser (id) VALUES (DEFAULT);
> > ERROR: more than one owned sequence found
>
> Yes this should never be user-triggerable,
t.
>
> These seem a bit redundant to me [...]
>
> OK, how about something like the attached? I reworded this a bit, though.
I like your patch better than my wording.
Thanks for the effort!
Yours,
Laurenz Albe
problem
> without forbidding anything, since normal OWNED BY dependencies are "auto".
>
> What do you think?
Here is a patch that illustrates the second approach.
I'll add this thread to the next commitfest.
Yours,
Laurenz Albe
From 7f7bae5315b7770f1327a80eb192bb098ee9df89 Mo
with write support is currently broken.
As Andres has argued above, it is too late to do anything more about
it than to document this and warn FDW authors as good as we can.
Yours,
Laurenz Albe
n by that API change as well
if it hasn't been patched.
At the very least, this should have been mentioned in the list of
incompatible changes for v11.
Yours,
Laurenz Albe
n.
That may wall be a bug in oracle_fdw, since I have no reports of
anybody running it on that operating system.
Maybe you should open an oracle_fdw issue, but I don't know how
much I can help you, since this is the first time I have heard
of SmartOS.
Yours,
Laurenz Albe
I think this
> ship simply has sailed.
I can accept that (having fixed my own FDW), but I am worried that it will
cause problems for FDW users. Well, I guess they can always avoid COPY if
they don't want FDWs to crash.
Yours,
Laurenz Albe
On Mon, 2019-04-22 at 13:27 -0700, Andres Freund wrote:
> On 2019-04-22 21:37:25 +0200, Laurenz Albe wrote:
> > Commit 3d956d956a introduced support for foreign tables as partitions
> > and COPY FROM on foreign tables.
> >
> > If a foreign data wrapper supports data mo
On Mon, 2019-04-22 at 16:24 -0400, Robert Haas wrote:
> On Mon, Apr 22, 2019 at 3:37 PM Laurenz Albe wrote:
> > Sure, it is not hard to modify a FDW to continue working with v11.
> >
> > My point is that this should not be necessary.
>
> I'm not sure whether t
On Mon, 2019-04-22 at 21:45 +0900, Etsuro Fujita wrote:
Thanks for looking into this!
> (2019/04/20 20:53), Laurenz Albe wrote:
> > On Fri, 2018-04-06 at 23:24 +, Robert Haas wrote:
> > > Allow insert and update tuple routing and COPY for foreign tables.
> &
reign table if the FDW
implements BeginForeignInsert. The attached patch implements that.
I think this should be backpatched to v11.
Yours,
Laurenz Albe
From c4b0e871658c757124dad992578da0b60fccf962 Mon Sep 17 00:00:00 2001
From: Laurenz Albe
Date: Sat, 20 Apr 2019 13:36:56 +0200
Subject: [PATCH] COP
ithout forbidding anything, since normal OWNED BY dependencies are "auto".
What do you think?
Yours,
Laurenz Albe
y column.
I think this should be backpatched.
Yours,
Laurenz Albe
From ab536da87fa8ffc70469d3dbdaf3e1b84b0ef793 Mon Sep 17 00:00:00 2001
From: Laurenz Albe
Date: Sun, 14 Apr 2019 17:37:03 +0200
Subject: [PATCH] Make sure identity columns own only a single sequence
If an identity column owns more t
use he has been superuser at some time in the past
(and doesn't hold the privilege himself).
So I'd say the behavior is fine as it is, but it would not harm to
document it better (or at all).
Yours,
Laurenz Albe
since they
> should rely on the base tables rather than on the views.
This may make things easier for those who are confused by a negative
entry, but it will obfuscate matters for those who are not.
I don't think that is a win, particularly since the semantics are
explained in great detail in the documentation of "pg_stats".
So I am -1 on that one.
Yours,
Laurenz Albe
Michael Paquier wrote:
> On Thu, Mar 07, 2019 at 09:00:24PM +0100, Laurenz Albe wrote:
> > This should be backpatched down to 9.6 where the functions have been
> > added.
>
> Thanks, applied. The second argument name of heap_page_item_attrs is
> actually "page&quo
backpatched down to 9.6 where the functions have been added.
Yours,
Laurenz Albe
From 4e9655a24a8f86b020c50431eba2376f624a3def Mon Sep 17 00:00:00 2001
From: Laurenz Albe
Date: Thu, 7 Mar 2019 20:50:05 +0100
Subject: [PATCH] Fix documentation of pageinspect function signatures
---
doc/src/sgm
insist that *everything*
is encrypted, even your commit log (unencrypted log? everyone can
read the commits?).
Yours,
Laurenz Albe
Magnus Hagander wrote:
> Maybe have the first note say "This method is deprecated bceause it has
> serious
> risks (see bellow)" and then list the actual risks at the end?
Good idea. That may attract the attention of the dogs among the readers.
Yours,
Laurenz Albe
David Steele wrote:
> I added some extra language to the warning that gets emitted in the log.
> Users are more like to see that than the documentation.
>
> I also addressed a comment from another thread by adding pg_basebackup
> as .e.g. rather than or.
Looks good to me.
ld say "*may* not start", because it will if the WAL segment is still
there.
> + recommends that backup_label be removed if not
> + restoring from a backup.
> +
Yours,
Laurenz Albe
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
o "on").
+1
Yours,
Laurenz Albe
t;backup_label" *before*
pg_stop_backup(). Is there a good reason why it is pg_stop_backup()
and not pg_start_backup() that provides that information?
Yours,
Laurenz Albe
Robert Haas wrote:
> Not sure exactly what value would accomplish that goal.
I think autovacuum_vacuum_cost_limit = 2000 is a good starting point.
Yours,
Laurenz Albe
up is in my opinion the safest variant: it refuses
to create a corrupted cluster without manual intervention and gives you a dire
warning to consider if you are doing the right thing.
Yours,
Laurenz Albe
501 - 600 of 693 matches
Mail list logo
