From: Kyotaro Horiguchi
> If we can skip the table-copy when ALTER TABLE SET LOGGED on
> wal_level=minimal, is your objective achived?
I expect so, if we can skip the table copy during ALTER TABLE SET
LOGGED/UNLOGGED. On the other hand, both approaches have different pros and
cons. It's nice
At Fri, 11 Sep 2020 17:36:19 +0900 (JST), Kyotaro Horiguchi
wrote in
> At Fri, 11 Sep 2020 05:15:32 +, "tsunakawa.ta...@fujitsu.com"
> wrote in
> > From: Peter Smith
> > On Thu, Sep 10, 2020 at 7:16 PM tsunakawa.ta...@fujitsu.com
> > > wrote:
> > > > ALTER TABLE takes long time proporti
At Fri, 11 Sep 2020 05:15:32 +, "tsunakawa.ta...@fujitsu.com"
wrote in
> From: Peter Smith
> On Thu, Sep 10, 2020 at 7:16 PM tsunakawa.ta...@fujitsu.com
> > wrote:
> > > ALTER TABLE takes long time proportional to the amount of existing data,
> > while wal_level = none doesn't.
> >
> > Ri
From: Peter Smith
On Thu, Sep 10, 2020 at 7:16 PM tsunakawa.ta...@fujitsu.com
> wrote:
> > ALTER TABLE takes long time proportional to the amount of existing data,
> while wal_level = none doesn't.
>
> Right, but if wal_level=none is considered OK for that table with
> existing data, then why no
On Thu, Sep 10, 2020 at 7:16 PM tsunakawa.ta...@fujitsu.com
wrote:
> ALTER TABLE takes long time proportional to the amount of existing data,
> while wal_level = none doesn't.
Right, but if wal_level=none is considered OK for that table with
existing data, then why not just create the table UNL
From: Peter Smith
> Earlier, Osumi-san was rejecting the idea of using ALTER TABLE tbl SET
> UNLOGGED on basis that it is too time consuming for large data to
> switch the table modes [1].
> Doesn't wal_level=none essentially just behave as if every table was
> UNLOGGED; not just the ones we are
Hi.
I expect I have some basic misunderstanding because IMO now this
thread seems to have come full circle.
Earlier, Osumi-san was rejecting the idea of using ALTER TABLE tbl SET
UNLOGGED on basis that it is too time consuming for large data to
switch the table modes [1].
Now the latest idea is
From: Amit Kapila
> Sure, but on a daily basis, one requires only incremental WAL to
> complete the backup but in this case, it would require the entire
> database back up unless we have some form of block-level incremental
> backup method.
Regarding the backup time, I think users can shorten it
On Thu, Aug 27, 2020 at 7:04 AM tsunakawa.ta...@fujitsu.com
wrote:
>
> From: Amit Kapila
> > So you want your users to shutdown and restart the server before Copy
> > because that would be required if you want to change the wal_level.
>
> Yes. They seem to be fine with it, as far as I heard from
From: Amit Kapila
> So you want your users to shutdown and restart the server before Copy
> because that would be required if you want to change the wal_level.
Yes. They seem to be fine with it, as far as I heard from a person who is
involved in the system design.
> However, even if we do tha
On Wed, Aug 26, 2020 at 12:54 PM tsunakawa.ta...@fujitsu.com
wrote:
>
>
> Following this idea, what do you think about adding a new value "none" to
> wal_level, where no WAL is generated? The setting of wal_level is recorded
> in pg_control. The startup process can see the value and reject rec
Hello,
I think it's worth thinking about a sophisticated feature like Oracle's
UNRECOVERABLE data loading (because SQL Server's BCP load utility also has such
a feature, but for an empty table), how about an easier approach like MySQL? I
expect this won't complicate Postgres code much.
The c
On Thu, Aug 20, 2020 at 5:49 AM osumi.takami...@fujitsu.com
wrote:
>
> Hello.
> > > During the crash recovery, those points are helpful to recognize and
> > > detach such blocks in order to solve a situation that the loaded data is
> > > partially
> > synced to the disk and the rest isn't.
> >
>
At Thu, 20 Aug 2020 00:18:52 +, "osumi.takami...@fujitsu.com"
wrote in
> Hello.
>
> Apologies for the delay.
> > > When the server crash occurs during data loading of COPY UNLOGGED,
> > > it's a must to keep index consistent of course.
> > > I'm thinking that to rebuild the indexes on the t
Hello.
Apologies for the delay.
> > When the server crash occurs during data loading of COPY UNLOGGED,
> > it's a must to keep index consistent of course.
> > I'm thinking that to rebuild the indexes on the target table would work.
> >
> > In my opinion, UNLOGGED clause must be designed to guarant
On Fri, 17 Jul 2020 at 13:23, osumi.takami...@fujitsu.com
wrote:
>
> Hi,
>
> > AFAICS, we can already accomplish basically the same thing as what you want
> > to
> > do like this:
> >
> > alter table foo set unlogged;
> > copy foo from ...;
> > alter table foo set logged;
> This didn't satisfy wh
On Wed, Jul 22, 2020 at 11:11 AM osumi.takami...@fujitsu.com
wrote:
>
> > If you are going to suggest users not to replicate such tables then why
> > can't you
> > suggest them to create such tables as UNLOGGED in the first place? Another
> > idea could be that you create an 'unlogged'
> > table
Hi. Amit-san
> If you are going to suggest users not to replicate such tables then why can't
> you
> suggest them to create such tables as UNLOGGED in the first place? Another
> idea could be that you create an 'unlogged'
> table, copy the data to it. Then perform Alter Table .. SET Logged and
On Fri, Jul 17, 2020 at 9:53 AM osumi.takami...@fujitsu.com
wrote:
>
> Lastly, I have to admit that
> the status of target table where data is loaded by COPY UNLOGGED would be
> marked
> as invalid and notified to standbys under replication environment
> from the point in time when the operation
Hi,
> AFAICS, we can already accomplish basically the same thing as what you want to
> do like this:
>
> alter table foo set unlogged;
> copy foo from ...;
> alter table foo set logged;
This didn't satisfy what I wanted.
In case that 'foo' has huge amount of rows at the beginning,
this example wo
"osumi.takami...@fujitsu.com" writes:
>> Aside from that, though, how does this improve upon the existing capability
>> to copy into an unlogged temporary table?
> [>] unlogged temporary table can’t be inherited over sessions first of all.
Unlogged tables don't have to be temporary.
> And unlo
Hi David Johnston
Thank you for your comment.
Aside from that, though, how does this improve upon the existing capability to
copy into an unlogged temporary table?
[>] unlogged temporary table can’t be inherited over sessions first of all.
And unlogged table needs to be recreated due to startup
On 2020/07/09 15:17, osumi.takami...@fujitsu.com wrote:
Fujii-san
Thank you for your interest in this idea.
This feature can work safely with wal_level=replica or logical?
Or it can work only with wal_level=minimal?
If yes, what is the main difference
between this method and wal_skip_thresh
On Thu, Jul 9, 2020 at 11:47 AM osumi.takami...@fujitsu.com
wrote:
>
>
> In terms of streaming replication,
> I'd like to ask for advice of other members in this community.
> Now, I think this feature requires to re-create standby
> immediately after the COPY UNLOGGED like Oracle's clause
>
This
From: David G. Johnston
> This step seems to invalidate the idea outright. The checkpoint command is
> superuser only and isn’t table specific. This seems to require both those
> things to be changed.
Perhaps FlushRelationBuffers() followed by smgrsync() can be used instead. Or,
depending o
Fujii-san
Thank you for your interest in this idea.
> This feature can work safely with wal_level=replica or logical?
> Or it can work only with wal_level=minimal?
>If yes, what is the main difference
> between this method and wal_skip_threshold?
I'm thinking this feature can be used
when you set
On Wednesday, July 8, 2020, osumi.takami...@fujitsu.com <
osumi.takami...@fujitsu.com> wrote:
>
> 5. Sync the data to disk by performing checkpoint.
>
This step seems to invalidate the idea outright. The checkpoint command is
superuser only and isn’t table specific. This seems to require both th
On 2020/07/09 11:36, osumi.takami...@fujitsu.com wrote:
Hello, hackers.
I've been thinking to suggest
a peformance-oriented feature for COPY FROM.
It's UNLOGGED clause, which means data loading skipping WAL generation.
This feature can work safely with wal_level=replica or logical?
Or it c
Hello, hackers.
I've been thinking to suggest
a peformance-oriented feature for COPY FROM.
It's UNLOGGED clause, which means data loading skipping WAL generation.
How to make it work is the following.
1. Aquire ACCESS EXCLUSIVE mode to lock the target table and its indexes.
2. Mark those relatio
29 matches
Mail list logo
