Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Alvaro Herrera wrote: > I wrote an identical patch on Saturday and watched it pass > CLOBBER_CACHE_ALWAYS test on Sunday. But the reason I didn't push is I > couldn't understand *why* is there a problem here. I imagine that the > source of the issue is supposed to be a relcache invalidation that takes > place (in the original code) after reindex_index changes relpersistence, > and before RelationSetNewRelfilenode() creates the filenode. But at > what point does that code absorb invalidation messages? Or is there a > completely different mechanism that causes the problem? If so, what? Ah, it's the anti-collision stuff in GetNewOid(), isn't it. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Michael Paquier wrote: > The complaint comes from jaguarundi, like here: > http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=jaguarundi&dt=2014-11-16%2015%3A33%3A23 > > As Tom mentioned, adding a new parameter to set the persistence > through RelationSetNewRelfilenode works. Patch, actually tested with > CLOBBER_CACHE_ALWAYS, attached. I wrote an identical patch on Saturday and watched it pass CLOBBER_CACHE_ALWAYS test on Sunday. But the reason I didn't push is I couldn't understand *why* is there a problem here. I imagine that the source of the issue is supposed to be a relcache invalidation that takes place (in the original code) after reindex_index changes relpersistence, and before RelationSetNewRelfilenode() creates the filenode. But at what point does that code absorb invalidation messages? Or is there a completely different mechanism that causes the problem? If so, what? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Mon, Nov 17, 2014 at 5:56 AM, Christian Ullrich wrote:
> * Alvaro Herrera wrote:
>
>> Michael Paquier wrote:
>>
>>> Btw, perhaps this diff should be pushed as a different patch as this is a
>>> rather different thing:
>>> - if (heapRelation->rd_rel->relpersistence ==
>>> RELPERSISTENCE_UNLOGGED
>>> &&
>>> + if (indexRelation->rd_rel->relpersistence ==
>>> RELPERSISTENCE_UNLOGGED &&
>>> !smgrexists(indexRelation->rd_smgr, INIT_FORKNUM)
>>> As this is a correctness fix, it does not seem necessary to back-patch
>>> it:
>>> the parent relation always has the same persistence as its indexes.
>>
>>
>> There was an argument for doing it this way that only applies if this
>> patch went in, but I can't remember now what it was.
>>
>> Anyway I pushed the patch after some slight additional changes. Thanks!
>
>
> The buildfarm says -DCLOBBER_CACHE_ALWAYS does not like this patch.
The complaint comes from jaguarundi, like here:
http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=jaguarundi&dt=2014-11-16%2015%3A33%3A23
Adding a new parameter to RelationSetNewRelFile
As Tom mentioned, adding a new parameter to set the persistence
through RelationSetNewRelfilenode works. Patch, actually tested with
CLOBBER_CACHE_ALWAYS, attached.
http://www.postgresql.org/message-id/27238.1416073...@sss.pgh.pa.us
Regards,
--
Michael
From a1a7e4182bd1b5d66260220f040aee4a35e91c75 Mon Sep 17 00:00:00 2001
From: Michael Paquier
Date: Mon, 17 Nov 2014 17:04:05 +
Subject: [PATCH] Fix CLOBBER_CACHE_ALWAYS broken by 85b506b
Previous commit that removed ATChangeIndexesPersistence to replace it
with a lower-level API able to define a new relpersistence for a relation
when reindexing it failed with CLOBBER_CACHE_ALWAYS enabled. This patch
fixes it by setting the relpersistence of the newly-created relation after
its relfilenode is created by passing a new parameter to RelationSetNewRelfilenode
able to set the persistence of a relation.
---
src/backend/catalog/index.c| 5 +
src/backend/commands/sequence.c| 3 ++-
src/backend/commands/tablecmds.c | 6 --
src/backend/utils/cache/relcache.c | 6 +++---
src/include/utils/relcache.h | 4 +++-
5 files changed, 13 insertions(+), 11 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index b57aa95..825235a 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -3191,12 +3191,9 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence)
indexInfo->ii_ExclusionStrats = NULL;
}
- /* Set the relpersistence of the new index */
- iRel->rd_rel->relpersistence = persistence;
-
/* We'll build a new physical relation for the index */
RelationSetNewRelfilenode(iRel, InvalidTransactionId,
- InvalidMultiXactId);
+ InvalidMultiXactId, persistence);
/* Initialize the index and rebuild */
/* Note: we do not need to re-establish pkey setting */
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index e5f7765..4f7f586 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -304,7 +304,8 @@ ResetSequence(Oid seq_relid)
* Same with relminmxid, since a sequence will never contain multixacts.
*/
RelationSetNewRelfilenode(seq_rel, InvalidTransactionId,
- InvalidMultiXactId);
+ InvalidMultiXactId,
+ seq_rel->rd_rel->relpersistence);
/*
* Insert the modified tuple into the new storage file.
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 093224f..c57751e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1196,7 +1196,8 @@ ExecuteTruncate(TruncateStmt *stmt)
* as the relfilenode value. The old storage file is scheduled for
* deletion at commit.
*/
- RelationSetNewRelfilenode(rel, RecentXmin, minmulti);
+ RelationSetNewRelfilenode(rel, RecentXmin, minmulti,
+ rel->rd_rel->relpersistence);
if (rel->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED)
heap_create_init_fork(rel);
@@ -1209,7 +1210,8 @@ ExecuteTruncate(TruncateStmt *stmt)
if (OidIsValid(toast_relid))
{
rel = relation_open(toast_relid, AccessExclusiveLock);
-RelationSetNewRelfilenode(rel, RecentXmin, minmulti);
+RelationSetNewRelfilenode(rel, RecentXmin, minmulti,
+ rel->rd_rel->relpersistence);
if (rel->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED)
heap_create_init_fork(rel);
heap_close(rel, NoLock);
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 2250c56..fa75771 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -3008,7 +3008,7 @@ RelationBuildLocalRelation(const char *relname,
*/
void
RelationSetNewRelfilenode(Relation relation, TransactionId freezeXid,
- MultiXactId minmulti)
+ MultiXactId minmulti, char relpersistence)
{
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
* Alvaro Herrera wrote: Michael Paquier wrote: Btw, perhaps this diff should be pushed as a different patch as this is a rather different thing: - if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED && + if (indexRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED && !smgrexists(indexRelation->rd_smgr, INIT_FORKNUM) As this is a correctness fix, it does not seem necessary to back-patch it: the parent relation always has the same persistence as its indexes. There was an argument for doing it this way that only applies if this patch went in, but I can't remember now what it was. Anyway I pushed the patch after some slight additional changes. Thanks! The buildfarm says -DCLOBBER_CACHE_ALWAYS does not like this patch. -- Christian -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Sat, Nov 15, 2014 at 2:23 AM, Alvaro Herrera wrote: > > Michael Paquier wrote: > > > Btw, perhaps this diff should be pushed as a different patch as this is a > > rather different thing: > > - if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED > > && > > + if (indexRelation->rd_rel->relpersistence == > > RELPERSISTENCE_UNLOGGED && > > !smgrexists(indexRelation->rd_smgr, INIT_FORKNUM) > > As this is a correctness fix, it does not seem necessary to back-patch it: > > the parent relation always has the same persistence as its indexes. > > There was an argument for doing it this way that only applies if this > patch went in, but I can't remember now what it was. > > Anyway I pushed the patch after some slight additional changes. Thanks! > Thanks! -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog: http://fabriziomello.github.io >> Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello >> Github: http://github.com/fabriziomello
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Michael Paquier wrote: > Btw, perhaps this diff should be pushed as a different patch as this is a > rather different thing: > - if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED > && > + if (indexRelation->rd_rel->relpersistence == > RELPERSISTENCE_UNLOGGED && > !smgrexists(indexRelation->rd_smgr, INIT_FORKNUM) > As this is a correctness fix, it does not seem necessary to back-patch it: > the parent relation always has the same persistence as its indexes. There was an argument for doing it this way that only applies if this patch went in, but I can't remember now what it was. Anyway I pushed the patch after some slight additional changes. Thanks! -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Thanks for the updated patch, Fabrizio.
On Tue, Nov 11, 2014 at 7:44 AM, Fabrízio de Royes Mello <
fabriziome...@gmail.com> wrote:
> > A couple of minor comments about this patch:
> > 1) Reading it, I am wondering if it would not be finally time to
> > switch to a macro to get a relation's persistence, something like
> > RelationGetPersistence in rel.h... Not related directly to this patch.
>
> Good idea... I'll provide a patch soon.
>
I created a thread dedicated to that:
http://www.postgresql.org/message-id/cab7npqseb+hwitxfwkqypa7+9bjolnxio47qsro3hcbsoq0...@mail.gmail.com
Now few people cared enough to comment :)
> 2) reindex_index has as new argument a relpersislence value for the
> > new index. reindex_relation has differently a new set of flags to
> > enforce the relpersistence of all the underling indexes. Wouldn't it
> > be better for API consistency to pass directly a relpersistence value
> > through reindex_relation? In any case, the comment block of
> > reindex_relation does not contain a description of the new flags.
>
> I did it because the ReindexDatabase build a list of oids to run
> reindex_relation for each item of the list. I can change the list to store
> the relpersistence also, but this can lead us to a concurrency trouble
> because if one session execute REINDEX DATABASE and other session run
> "ALTER TABLE name SET {LOGGED|UNLOGGED}" during the building of the list
> the reindex can lead us to a inconsistent state.
>
Fair point. I forgot this code path.
>
Except a typo with s/rebuilded/rebuilt/ in the patch, corrected in the
patch attached with some extra comments bundled, it seems to be that this
patch can be passed to a committer, so marking it so.
Btw, perhaps this diff should be pushed as a different patch as this is a
rather different thing:
- if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED
&&
+ if (indexRelation->rd_rel->relpersistence ==
RELPERSISTENCE_UNLOGGED &&
!smgrexists(indexRelation->rd_smgr, INIT_FORKNUM)
As this is a correctness fix, it does not seem necessary to back-patch it:
the parent relation always has the same persistence as its indexes.
Regards,
--
Michael
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 912038a..a0a81e8 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1980,7 +1980,7 @@ index_build(Relation heapRelation,
* created it, or truncated twice in a subsequent transaction, the
* relfilenode won't change, and nothing needs to be done here.
*/
- if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED &&
+ if (indexRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED &&
!smgrexists(indexRelation->rd_smgr, INIT_FORKNUM))
{
RegProcedure ambuildempty = indexRelation->rd_am->ambuildempty;
@@ -3130,7 +3130,7 @@ IndexGetRelation(Oid indexId, bool missing_ok)
* reindex_index - This routine is used to recreate a single index
*/
void
-reindex_index(Oid indexId, bool skip_constraint_checks)
+reindex_index(Oid indexId, bool skip_constraint_checks, char relpersistence)
{
Relation iRel,
heapRelation;
@@ -3191,6 +3191,9 @@ reindex_index(Oid indexId, bool skip_constraint_checks)
indexInfo->ii_ExclusionStrats = NULL;
}
+ /* Set the relpersistence of the new index */
+ iRel->rd_rel->relpersistence = relpersistence;
+
/* We'll build a new physical relation for the index */
RelationSetNewRelfilenode(iRel, InvalidTransactionId,
InvalidMultiXactId);
@@ -3310,6 +3313,12 @@ reindex_index(Oid indexId, bool skip_constraint_checks)
* performance, other callers should include the flag only after transforming
* the data in a manner that risks a change in constraint validity.
*
+ * REINDEX_REL_FORCE_UNLOGGED: if true, set the persistence of the rebuilt
+ * indexes to unlogged.
+ *
+ * REINDEX_REL_FORCE_LOGGED: if true, set the persistence of the rebuilt
+ * indexes to permanent.
+ *
* Returns true if any indexes were rebuilt (including toast table's index
* when relevant). Note that a CommandCounterIncrement will occur after each
* index rebuild.
@@ -3389,11 +3398,19 @@ reindex_relation(Oid relid, int flags)
foreach(indexId, indexIds)
{
Oid indexOid = lfirst_oid(indexId);
+ char relpersistence = rel->rd_rel->relpersistence;
if (is_pg_class)
RelationSetIndexList(rel, doneIndexes, InvalidOid);
- reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS));
+ /* Check for flags to enforce UNLOGGED or PERMANENT persistence */
+ if (flags & REINDEX_REL_FORCE_UNLOGGED)
+relpersistence = RELPERSISTENCE_UNLOGGED;
+ else if (flags & REINDEX_REL_FORCE_PERMANENT)
+relpersistence = RELPERSISTENCE_PERMANENT;
+
+ reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
+ relpersistence);
CommandCounterIncrement();
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 6a578ec..e067abc 100644
--- a
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Thu, Nov 6, 2014 at 3:42 AM, Michael Paquier
wrote:
>
> On Sat, Sep 13, 2014 at 11:02 PM, Fabrízio de Royes Mello
> wrote:
> > Patch rebased and added to commitfest [1].
> It looks like a good thing to remove ATChangeIndexesPersistence, this
> puts the persistence switch directly into reindex process.
>
> A couple of minor comments about this patch:
> 1) Reading it, I am wondering if it would not be finally time to
> switch to a macro to get a relation's persistence, something like
> RelationGetPersistence in rel.h... Not related directly to this patch.
Good idea... I'll provide a patch soon.
> 2) reindex_index has as new argument a relpersislence value for the
> new index. reindex_relation has differently a new set of flags to
> enforce the relpersistence of all the underling indexes. Wouldn't it
> be better for API consistency to pass directly a relpersistence value
> through reindex_relation? In any case, the comment block of
> reindex_relation does not contain a description of the new flags.
I did it because the ReindexDatabase build a list of oids to run
reindex_relation for each item of the list. I can change the list to store
the relpersistence also, but this can lead us to a concurrency trouble
because if one session execute REINDEX DATABASE and other session run
"ALTER TABLE name SET {LOGGED|UNLOGGED}" during the building of the list
the reindex can lead us to a inconsitence state.
Added comments to comment block of reindex_relation.
> 3) Here you may as well just set the value and be done:
> +/*
> + * Check if need to set the new relpersistence
> + */
> +if (iRel->rd_rel->relpersistence != relpersistence)
> +iRel->rd_rel->relpersistence = relpersistence;
Hmmm... I really don't know why I did it... fixed.
Thanks!
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 912038a..50cf0ef 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1980,7 +1980,7 @@ index_build(Relation heapRelation,
* created it, or truncated twice in a subsequent transaction, the
* relfilenode won't change, and nothing needs to be done here.
*/
- if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED &&
+ if (indexRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED &&
!smgrexists(indexRelation->rd_smgr, INIT_FORKNUM))
{
RegProcedure ambuildempty = indexRelation->rd_am->ambuildempty;
@@ -3130,7 +3130,7 @@ IndexGetRelation(Oid indexId, bool missing_ok)
* reindex_index - This routine is used to recreate a single index
*/
void
-reindex_index(Oid indexId, bool skip_constraint_checks)
+reindex_index(Oid indexId, bool skip_constraint_checks, char relpersistence)
{
Relation iRel,
heapRelation;
@@ -3191,6 +3191,9 @@ reindex_index(Oid indexId, bool skip_constraint_checks)
indexInfo->ii_ExclusionStrats = NULL;
}
+ /* Set the relpersistence of the new index */
+ iRel->rd_rel->relpersistence = relpersistence;
+
/* We'll build a new physical relation for the index */
RelationSetNewRelfilenode(iRel, InvalidTransactionId,
InvalidMultiXactId);
@@ -3310,6 +3313,12 @@ reindex_index(Oid indexId, bool skip_constraint_checks)
* performance, other callers should include the flag only after transforming
* the data in a manner that risks a change in constraint validity.
*
+ * REINDEX_REL_FORCE_UNLOGGED: if true, force the new rebuilded indexes to be
+ * unlogged.
+ *
+ * REINDEX_REL_FORCE_LOGGED: if true, force the new rebuilded indexes to be
+ * permanent.
+ *
* Returns true if any indexes were rebuilt (including toast table's index
* when relevant). Note that a CommandCounterIncrement will occur after each
* index rebuild.
@@ -3389,11 +3398,19 @@ reindex_relation(Oid relid, int flags)
foreach(indexId, indexIds)
{
Oid indexOid = lfirst_oid(indexId);
+ char relpersistence = rel->rd_rel->relpersistence;
if (is_pg_class)
RelationSetIndexList(rel, doneIndexes, InvalidOid);
- reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS));
+ /* Check for flags to force UNLOGGED or PERMANENT persistence */
+ if (flags & REINDEX_REL_FORCE_UNLOGGED)
+relpersistence = RELPERSISTENCE_UNLOGGED;
+ else if (flags & REINDEX_REL_FORCE_PERMANENT)
+relpersistence = RELPERSISTENCE_PERMANENT;
+
+ reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
+ relpersistence);
CommandCounterIncrement();
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 6a578ec..95b9a4f 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -589,7 +589,8 @@ rebuild_relation(Relation OldHeap,
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Sat, Sep 13, 2014 at 11:02 PM, Fabrízio de Royes Mello wrote: > Patch rebased and added to commitfest [1]. It looks like a good thing to remove ATChangeIndexesPersistence, this puts the persistence switch directly into reindex process. A couple of minor comments about this patch: 1) Reading it, I am wondering if it would not be finally time to switch to a macro to get a relation's persistence, something like RelationGetPersistence in rel.h... Not related directly to this patch. 2) reindex_index has as new argument a relpersislence value for the new index. reindex_relation has differently a new set of flags to enforce the relpersistence of all the underling indexes. Wouldn't it be better for API consistency to pass directly a relpersistence value through reindex_relation? In any case, the comment block of reindex_relation does not contain a description of the new flags. 3) Here you may as well just set the value and be done: +/* + * Check if need to set the new relpersistence + */ +if (iRel->rd_rel->relpersistence != relpersistence) +iRel->rd_rel->relpersistence = relpersistence; Regards, -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Tue, Aug 26, 2014 at 1:42 AM, Fabrízio de Royes Mello <
fabriziome...@gmail.com> wrote:
>
>
> On Fri, Aug 22, 2014 at 5:23 PM, Alvaro Herrera
wrote:
> >
> > Fabrízio de Royes Mello wrote:
> > > On Fri, Aug 22, 2014 at 4:45 PM, Alvaro Herrera <
alvhe...@2ndquadrant.com>
> > > wrote:
> >
> > > > I pointed out, in the email just before pushing the patch, that
perhaps
> > > > we should pass down the new relpersistence flag into
finish_heap_swap,
> > > > and from there we could pass it down to reindex_index() which is
where
> > > > it would be needed. I'm not sure it's worth the trouble, but I
think we
> > > > can still ask Fabrizio to rework that part.
> >
> > > I can rework this part if it's a real concern.
> >
> > I guess we can make a better assessment by seeing what it would take.
> > I'm afraid it will turn out to be really ugly.
> >
> > Now, there's some desire to have unlogged indexes on logged tables; I
> > guess if we have that, then eventually there will also be a desire to
> > swap individual indexes from logged to unlogged. Perhaps whatever fix
> > we come up with here would pave the road for that future feature.
> >
>
> Álvaro,
>
> I did a refactoring to pass down the relpersistence to "finish_heap_swap"
and then change the catalog inside the "reindex_index" instead of in the
rewrite table phase.
>
> That way we can get rid the function ATChangeIndexesPersistence in the
src/backend/commands/tablecmds.c.
>
> Thoughts?
>
Patch rebased and added to commitfest [1].
Regards,
[1] https://commitfest.postgresql.org/action/commitfest_view?id=24
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index ee10594..173f412 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1973,7 +1973,7 @@ index_build(Relation heapRelation,
* created it, or truncated twice in a subsequent transaction, the
* relfilenode won't change, and nothing needs to be done here.
*/
- if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED &&
+ if (indexRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED &&
!smgrexists(indexRelation->rd_smgr, INIT_FORKNUM))
{
RegProcedure ambuildempty = indexRelation->rd_am->ambuildempty;
@@ -3099,7 +3099,7 @@ IndexGetRelation(Oid indexId, bool missing_ok)
* reindex_index - This routine is used to recreate a single index
*/
void
-reindex_index(Oid indexId, bool skip_constraint_checks)
+reindex_index(Oid indexId, bool skip_constraint_checks, char relpersistence)
{
Relation iRel,
heapRelation;
@@ -3160,6 +3160,12 @@ reindex_index(Oid indexId, bool skip_constraint_checks)
indexInfo->ii_ExclusionStrats = NULL;
}
+ /*
+ * Check if need to set the new relpersistence
+ */
+ if (iRel->rd_rel->relpersistence != relpersistence)
+ iRel->rd_rel->relpersistence = relpersistence;
+
/* We'll build a new physical relation for the index */
RelationSetNewRelfilenode(iRel, InvalidTransactionId,
InvalidMultiXactId);
@@ -3358,11 +3364,19 @@ reindex_relation(Oid relid, int flags)
foreach(indexId, indexIds)
{
Oid indexOid = lfirst_oid(indexId);
+ char relpersistence = rel->rd_rel->relpersistence;
if (is_pg_class)
RelationSetIndexList(rel, doneIndexes, InvalidOid);
- reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS));
+ /* Check for flags to force UNLOGGED or PERMANENT persistence */
+ if (flags & REINDEX_REL_FORCE_UNLOGGED)
+relpersistence = RELPERSISTENCE_UNLOGGED;
+ else if (flags & REINDEX_REL_FORCE_PERMANENT)
+relpersistence = RELPERSISTENCE_PERMANENT;
+
+ reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
+ relpersistence);
CommandCounterIncrement();
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index ff80b09..f285026 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -588,7 +588,8 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
*/
finish_heap_swap(tableOid, OIDNewHeap, is_system_catalog,
swap_toast_by_content, false, true,
- frozenXid, cutoffMulti);
+ frozenXid, cutoffMulti,
+ OldHeap->rd_rel->relpersistence);
}
@@ -1474,7 +1475,8 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
bool check_constraints,
bool is_internal,
TransactionId frozenXid,
- MultiXactId cutoffMulti)
+ MultiXactId cutoffMulti,
+ char newrelpersistence)
{
ObjectAddress object;
Oid mapped_tables[4];
@@ -1518,6 +1520,12 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
reindex_flags = REINDEX_REL_SUPPRESS_INDEX_USE;
if (check_constraints)
reindex_flags |= REINDEX_REL_CHECK_CONSTRAINTS;
+
+ if (n
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Fri, Aug 22, 2014 at 5:23 PM, Alvaro Herrera
wrote:
>
> Fabrízio de Royes Mello wrote:
> > On Fri, Aug 22, 2014 at 4:45 PM, Alvaro Herrera <
alvhe...@2ndquadrant.com>
> > wrote:
>
> > > I pointed out, in the email just before pushing the patch, that
perhaps
> > > we should pass down the new relpersistence flag into finish_heap_swap,
> > > and from there we could pass it down to reindex_index() which is where
> > > it would be needed. I'm not sure it's worth the trouble, but I think
we
> > > can still ask Fabrizio to rework that part.
>
> > I can rework this part if it's a real concern.
>
> I guess we can make a better assessment by seeing what it would take.
> I'm afraid it will turn out to be really ugly.
>
> Now, there's some desire to have unlogged indexes on logged tables; I
> guess if we have that, then eventually there will also be a desire to
> swap individual indexes from logged to unlogged. Perhaps whatever fix
> we come up with here would pave the road for that future feature.
>
Álvaro,
I did a refactoring to pass down the relpersistence to "finish_heap_swap"
and then change the catalog inside the "reindex_index" instead of in the
rewrite table phase.
That way we can get rid the function ATChangeIndexesPersistence in the
src/backend/commands/tablecmds.c.
Thoughts?
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index ee10594..173f412 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1973,7 +1973,7 @@ index_build(Relation heapRelation,
* created it, or truncated twice in a subsequent transaction, the
* relfilenode won't change, and nothing needs to be done here.
*/
- if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED &&
+ if (indexRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED &&
!smgrexists(indexRelation->rd_smgr, INIT_FORKNUM))
{
RegProcedure ambuildempty = indexRelation->rd_am->ambuildempty;
@@ -3099,7 +3099,7 @@ IndexGetRelation(Oid indexId, bool missing_ok)
* reindex_index - This routine is used to recreate a single index
*/
void
-reindex_index(Oid indexId, bool skip_constraint_checks)
+reindex_index(Oid indexId, bool skip_constraint_checks, char relpersistence)
{
Relation iRel,
heapRelation;
@@ -3160,6 +3160,12 @@ reindex_index(Oid indexId, bool skip_constraint_checks)
indexInfo->ii_ExclusionStrats = NULL;
}
+ /*
+ * Check if need to set the new relpersistence
+ */
+ if (iRel->rd_rel->relpersistence != relpersistence)
+ iRel->rd_rel->relpersistence = relpersistence;
+
/* We'll build a new physical relation for the index */
RelationSetNewRelfilenode(iRel, InvalidTransactionId,
InvalidMultiXactId);
@@ -3358,11 +3364,19 @@ reindex_relation(Oid relid, int flags)
foreach(indexId, indexIds)
{
Oid indexOid = lfirst_oid(indexId);
+ char relpersistence = rel->rd_rel->relpersistence;
if (is_pg_class)
RelationSetIndexList(rel, doneIndexes, InvalidOid);
- reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS));
+ /* Check for flags to force UNLOGGED or PERMANENT persistence */
+ if (flags & REINDEX_REL_FORCE_UNLOGGED)
+relpersistence = RELPERSISTENCE_UNLOGGED;
+ else if (flags & REINDEX_REL_FORCE_PERMANENT)
+relpersistence = RELPERSISTENCE_PERMANENT;
+
+ reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
+ relpersistence);
CommandCounterIncrement();
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index ff80b09..f285026 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -588,7 +588,8 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
*/
finish_heap_swap(tableOid, OIDNewHeap, is_system_catalog,
swap_toast_by_content, false, true,
- frozenXid, cutoffMulti);
+ frozenXid, cutoffMulti,
+ OldHeap->rd_rel->relpersistence);
}
@@ -1474,7 +1475,8 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
bool check_constraints,
bool is_internal,
TransactionId frozenXid,
- MultiXactId cutoffMulti)
+ MultiXactId cutoffMulti,
+ char newrelpersistence)
{
ObjectAddress object;
Oid mapped_tables[4];
@@ -1518,6 +1520,12 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
reindex_flags = REINDEX_REL_SUPPRESS_INDEX_USE;
if (check_constraints)
reindex_flags |= REINDEX_REL_CHECK_CONSTRAINTS;
+
+ if (newrelpersistence == RELPERSISTENCE_UNLOGGED)
+ reindex_flags |= REINDEX_REL_FORCE_UNLOGGED;
+ else if (newrelpersistence == RELPERSISTENCE_PERMANENT)
+ reindex_flags |= REINDEX_REL_FORCE_PERMANENT;
+
reindex_relation(OIDOldHeap, reindex_flags);
/*
diff --git a/src/ba
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Mon, Aug 25, 2014 at 2:55 PM, Alvaro Herrera wrote: > > Fabrízio de Royes Mello wrote: > > On Sat, Aug 23, 2014 at 8:53 AM, Michael Paquier < michael.paqu...@gmail.com> > > wrote: > > > > > > On Sat, Aug 23, 2014 at 3:32 AM, Alvaro Herrera > > > wrote: > > > > Great. Pushed. Thanks for the patch. > > > There is a typo in what has been pushed. Patch attached. > > > > > > > Thanks... I fixed that in my last patch to change 'loggedness' to > > 'persistence'. Attached. > > Thanks, pushed. I also added a comment explaining why it's okay to do > what we're doing. > Thanks... I'm working on a refactoring to pass down the relpersistence flag to finish_heap_swap... is this valid for now or I should leave it to another patch? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog: http://fabriziomello.github.io >> Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello >> Github: http://github.com/fabriziomello
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Fabrízio de Royes Mello wrote: > On Sat, Aug 23, 2014 at 8:53 AM, Michael Paquier > wrote: > > > > On Sat, Aug 23, 2014 at 3:32 AM, Alvaro Herrera > > wrote: > > > Great. Pushed. Thanks for the patch. > > There is a typo in what has been pushed. Patch attached. > > > > Thanks... I fixed that in my last patch to change 'loggedness' to > 'persistence'. Attached. Thanks, pushed. I also added a comment explaining why it's okay to do what we're doing. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Sat, Aug 23, 2014 at 8:53 AM, Michael Paquier
wrote:
>
> On Sat, Aug 23, 2014 at 3:32 AM, Alvaro Herrera
> wrote:
> > Great. Pushed. Thanks for the patch.
> There is a typo in what has been pushed. Patch attached.
>
Thanks... I fixed that in my last patch to change 'loggedness' to
'persistence'. Attached.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index d37534e..1d2fe1f 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -152,7 +152,7 @@ typedef struct AlteredTableInfo
bool new_notnull; /* T if we added new NOT NULL constraints */
bool rewrite; /* T if a rewrite is forced */
Oid newTableSpace; /* new tablespace; 0 means no change */
- bool chgLoggedness; /* T if SET LOGGED/UNLOGGED is used */
+ bool chgPersistence; /* T if SET LOGGED/UNLOGGED is used */
char newrelpersistence; /* if above is true */
/* Objects to rebuild after completing ALTER TYPE operations */
List *changedConstraintOids; /* OIDs of constraints to rebuild */
@@ -388,8 +388,8 @@ static void change_owner_recurse_to_sequences(Oid relationOid,
static void ATExecClusterOn(Relation rel, const char *indexName,
LOCKMODE lockmode);
static void ATExecDropCluster(Relation rel, LOCKMODE lockmode);
-static bool ATPrepChangeLoggedness(Relation rel, bool toLogged);
-static void ATChangeIndexesLoggedness(Oid relid, char relpersistence);
+static bool ATPrepChangePersistence(Relation rel, bool toLogged);
+static void ATChangeIndexesPersistence(Oid relid, char relpersistence);
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
char *tablespacename, LOCKMODE lockmode);
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode);
@@ -3174,19 +3174,19 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
break;
case AT_SetLogged: /* SET LOGGED */
ATSimplePermissions(rel, ATT_TABLE);
- tab->chgLoggedness = ATPrepChangeLoggedness(rel, true);
+ tab->chgPersistence = ATPrepChangePersistence(rel, true);
tab->newrelpersistence = RELPERSISTENCE_PERMANENT;
/* force rewrite if necessary */
- if (tab->chgLoggedness)
+ if (tab->chgPersistence)
tab->rewrite = true;
pass = AT_PASS_MISC;
break;
case AT_SetUnLogged: /* SET UNLOGGED */
ATSimplePermissions(rel, ATT_TABLE);
- tab->chgLoggedness = ATPrepChangeLoggedness(rel, false);
+ tab->chgPersistence = ATPrepChangePersistence(rel, false);
tab->newrelpersistence = RELPERSISTENCE_UNLOGGED;
/* force rewrite if necessary */
- if (tab->chgLoggedness)
+ if (tab->chgPersistence)
tab->rewrite = true;
pass = AT_PASS_MISC;
break;
@@ -3668,7 +3668,7 @@ ATRewriteTables(List **wqueue, LOCKMODE lockmode)
* Select persistence of transient table (same as original unless
* user requested a change)
*/
- persistence = tab->chgLoggedness ?
+ persistence = tab->chgPersistence ?
tab->newrelpersistence : OldHeap->rd_rel->relpersistence;
heap_close(OldHeap, NoLock);
@@ -3705,8 +3705,8 @@ ATRewriteTables(List **wqueue, LOCKMODE lockmode)
* because the rewrite step might read the indexes, and that would
* cause buffers for them to have the wrong setting.
*/
- if (tab->chgLoggedness)
-ATChangeIndexesLoggedness(tab->relid, tab->newrelpersistence);
+ if (tab->chgPersistence)
+ATChangeIndexesPersistence(tab->relid, tab->newrelpersistence);
/*
* Swap the physical files of the old and new heaps, then rebuild
@@ -4119,7 +4119,7 @@ ATGetQueueEntry(List **wqueue, Relation rel)
tab->relkind = rel->rd_rel->relkind;
tab->oldDesc = CreateTupleDescCopy(RelationGetDescr(rel));
tab->newrelpersistence = RELPERSISTENCE_PERMANENT;
- tab->chgLoggedness = false;
+ tab->chgPersistence = false;
*wqueue = lappend(*wqueue, tab);
@@ -10678,7 +10678,7 @@ ATExecGenericOptions(Relation rel, List *options)
* checks are skipped), otherwise true.
*/
static bool
-ATPrepChangeLoggedness(Relation rel, bool toLogged)
+ATPrepChangePersistence(Relation rel, bool toLogged)
{
Relation pg_constraint;
HeapTuple tuple;
@@ -10722,7 +10722,7 @@ ATPrepChangeLoggedness(Relation rel, bool toLogged)
/*
* Scan conrelid if changing to permanent, else confrelid. This also
- * determines whether an useful index exists.
+ * determines whether a useful index exists.
*/
ScanKeyInit(&skey[0],
toLogged ? Anum_pg_constraint_conrelid :
@@ -10792,7 +10792,7 @@ ATPrepChangeLoggedness(Relation rel, bool toLogged)
* given persistence.
*/
static void
-ATChangeIndexesLoggedness(Oid relid, char relpersistence)
+ATChangeIndexesPersistence(Oid relid, char relpersistence)
{
R
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Sat, Aug 23, 2014 at 3:32 AM, Alvaro Herrera wrote: > Great. Pushed. Thanks for the patch. There is a typo in what has been pushed. Patch attached. -- Michael diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index d37534e..1bb46ea 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -10722,7 +10722,7 @@ ATPrepChangeLoggedness(Relation rel, bool toLogged) /* * Scan conrelid if changing to permanent, else confrelid. This also - * determines whether an useful index exists. + * determines whether a useful index exists. */ ScanKeyInit(&skey[0], toLogged ? Anum_pg_constraint_conrelid : -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Fri, Aug 22, 2014 at 4:45 PM, Alvaro Herrera
wrote:
>
> BTW why is it that index_build() checks the heap's relpersistence flag
> rather than the index'?
>
I'm curious about it too... the code in src/backend/catalog/index.c is:
1975 if (heapRelation->rd_rel->relpersistence ==
RELPERSISTENCE_UNLOGGED &&
1976 !smgrexists(indexRelation->rd_smgr, INIT_FORKNUM))
1977 {
Should not to be in that way?
1975 if (indexRelation->rd_rel->relpersistence ==
RELPERSISTENCE_UNLOGGED &&
1976 !smgrexists(indexRelation->rd_smgr, INIT_FORKNUM))
1977 {
Alvaro, is this your concern? Right?
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Alvaro Herrera writes: > Robert Haas wrote: >> 1. Loggedness is not a word. I think that "persistence" or >> "relpersistence" would be better. > You want me to change that to chgPersistence and so on? No prob, just > LMK. +1 for s/loggedness/persistence/ -- I agree with Robert that it's a bit grating on the native ear. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Robert Haas writes: > 2. The patch seems to think that it can sometimes be safe to change > the relpersistence of an existing relation. Unless you can be sure > that no buffers can possibly be present in shared_buffers and nobody > will use an existing relcache entry to read a new one in, it's not, > because the buffers won't have the right BM_PERSISTENT marking. I'm > very nervous about the fact that this patch seems not to have touched > bufmgr.c, but maybe I'm missing something. Maybe I misunderstood something, but I had the impression that this was handled by assigning a new relfilenode (and hence copying all the data). So the buffers with one marking would be disjoint from the ones with the other marking. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Fabrízio de Royes Mello wrote: > On Fri, Aug 22, 2014 at 4:45 PM, Alvaro Herrera > wrote: > > I pointed out, in the email just before pushing the patch, that perhaps > > we should pass down the new relpersistence flag into finish_heap_swap, > > and from there we could pass it down to reindex_index() which is where > > it would be needed. I'm not sure it's worth the trouble, but I think we > > can still ask Fabrizio to rework that part. > I can rework this part if it's a real concern. I guess we can make a better assessment by seeing what it would take. I'm afraid it will turn out to be really ugly. Now, there's some desire to have unlogged indexes on logged tables; I guess if we have that, then eventually there will also be a desire to swap individual indexes from logged to unlogged. Perhaps whatever fix we come up with here would pave the road for that future feature. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Fri, Aug 22, 2014 at 4:45 PM, Alvaro Herrera wrote: > > > 2. The patch seems to think that it can sometimes be safe to change > > the relpersistence of an existing relation. Unless you can be sure > > that no buffers can possibly be present in shared_buffers and nobody > > will use an existing relcache entry to read a new one in, it's not, > > because the buffers won't have the right BM_PERSISTENT marking. I'm > > very nervous about the fact that this patch seems not to have touched > > bufmgr.c, but maybe I'm missing something. > > Right. Andres pointed this out previously, and the patch was updated > consequently. The only remaining case in which we do that, again AFAIR, > is for indexes, just after the table has been rewritten and just before > the indexes are reindexed. There should be no buffer access of the old > indexes at that point, so there would be no buffer marked with the wrong > flag. Also, the table is locked access-exclusively (is that a word?), > so no other transaction could possibly be reading buffers for its > indexes. > > I pointed out, in the email just before pushing the patch, that perhaps > we should pass down the new relpersistence flag into finish_heap_swap, > and from there we could pass it down to reindex_index() which is where > it would be needed. I'm not sure it's worth the trouble, but I think we > can still ask Fabrizio to rework that part. > > Maybe it is me missing something. > > BTW why is it that index_build() checks the heap's relpersistence flag > rather than the index'? > I can rework this part if it's a real concern. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog: http://fabriziomello.github.io >> Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello >> Github: http://github.com/fabriziomello
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Fabrízio de Royes Mello wrote: > On Fri, Aug 22, 2014 at 4:22 PM, Robert Haas wrote: > > 2. The patch seems to think that it can sometimes be safe to change > > the relpersistence of an existing relation. Unless you can be sure > > that no buffers can possibly be present in shared_buffers and nobody > > will use an existing relcache entry to read a new one in, it's not, > > because the buffers won't have the right BM_PERSISTENT marking. I'm > > very nervous about the fact that this patch seems not to have touched > > bufmgr.c, but maybe I'm missing something. > > Because of this concern I implement a solution pointed by Andres [1]. Actually what you did was better than what he suggested. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Robert Haas wrote: > Hmm. I confess to not having paid enough attention to this, Sorry about that. I guess I should somehow flag threads "I'm planning to commit this" so that other people can review stuff carefully. > but: > > 1. Loggedness is not a word. I think that "persistence" or > "relpersistence" would be better. Yeah, AFAICS this is only used in the variable chgLoggedness and a couple of functions. I don't think we're tense about unwordness of words we use in source code (as opposed to error messages and docs), and we have lots of russianisms left by Vadim and others, so I didn't see it as a serious issue. But then I'm not a native english speaker and I'm not bothered by it. OTOH I came up with "loggedness" on my own -- you wouldn't have seen it even in Fabrizio's latest version of the patch. Who knows, it might become a real english word one day. You want me to change that to chgPersistence and so on? No prob, just LMK. > 2. The patch seems to think that it can sometimes be safe to change > the relpersistence of an existing relation. Unless you can be sure > that no buffers can possibly be present in shared_buffers and nobody > will use an existing relcache entry to read a new one in, it's not, > because the buffers won't have the right BM_PERSISTENT marking. I'm > very nervous about the fact that this patch seems not to have touched > bufmgr.c, but maybe I'm missing something. Right. Andres pointed this out previously, and the patch was updated consequently. The only remaining case in which we do that, again AFAIR, is for indexes, just after the table has been rewritten and just before the indexes are reindexed. There should be no buffer access of the old indexes at that point, so there would be no buffer marked with the wrong flag. Also, the table is locked access-exclusively (is that a word?), so no other transaction could possibly be reading buffers for its indexes. I pointed out, in the email just before pushing the patch, that perhaps we should pass down the new relpersistence flag into finish_heap_swap, and from there we could pass it down to reindex_index() which is where it would be needed. I'm not sure it's worth the trouble, but I think we can still ask Fabrizio to rework that part. Maybe it is me missing something. BTW why is it that index_build() checks the heap's relpersistence flag rather than the index'? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Fri, Aug 22, 2014 at 4:22 PM, Robert Haas wrote: > > On Fri, Aug 22, 2014 at 2:32 PM, Alvaro Herrera > wrote: > > Fabrízio de Royes Mello wrote: > >> Em sexta-feira, 22 de agosto de 2014, Alvaro Herrera < > >> alvhe...@2ndquadrant.com> escreveu: > >> > >> > Fabrízio de Royes Mello wrote: > >> > > >> > > I forgot to mention... I did again a lot of tests using different > >> > > replication scenarios to make sure all is ok: > >> > > - slaves async > >> > > - slaves sync > >> > > - cascade slaves > >> > > >> > On v13 you mean? > >> > > >> Exactly! > > > > Great. Pushed. Thanks for the patch. > > Hmm. I confess to not having paid enough attention to this, but: > > 1. Loggedness is not a word. I think that "persistence" or > "relpersistence" would be better. > I changed to "Persistence"... > 2. The patch seems to think that it can sometimes be safe to change > the relpersistence of an existing relation. Unless you can be sure > that no buffers can possibly be present in shared_buffers and nobody > will use an existing relcache entry to read a new one in, it's not, > because the buffers won't have the right BM_PERSISTENT marking. I'm > very nervous about the fact that this patch seems not to have touched > bufmgr.c, but maybe I'm missing something. > Because of this concern I implement a solution pointed by Andres [1]. Regards, [1] http://www.postgresql.org/message-id/20140717230220.gk21...@awork2.anarazel.de -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog: http://fabriziomello.github.io >> Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello >> Github: http://github.com/fabriziomello diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index d37534e..5a233e2 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -152,7 +152,7 @@ typedef struct AlteredTableInfo bool new_notnull; /* T if we added new NOT NULL constraints */ bool rewrite; /* T if a rewrite is forced */ Oid newTableSpace; /* new tablespace; 0 means no change */ - bool chgLoggedness; /* T if SET LOGGED/UNLOGGED is used */ + bool chgPersistence; /* T if SET LOGGED/UNLOGGED is used */ char newrelpersistence; /* if above is true */ /* Objects to rebuild after completing ALTER TYPE operations */ List *changedConstraintOids; /* OIDs of constraints to rebuild */ @@ -388,8 +388,8 @@ static void change_owner_recurse_to_sequences(Oid relationOid, static void ATExecClusterOn(Relation rel, const char *indexName, LOCKMODE lockmode); static void ATExecDropCluster(Relation rel, LOCKMODE lockmode); -static bool ATPrepChangeLoggedness(Relation rel, bool toLogged); -static void ATChangeIndexesLoggedness(Oid relid, char relpersistence); +static bool ATPrepChangePersistence(Relation rel, bool toLogged); +static void ATChangeIndexesPersistence(Oid relid, char relpersistence); static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel, char *tablespacename, LOCKMODE lockmode); static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode); @@ -3174,19 +3174,19 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, break; case AT_SetLogged: /* SET LOGGED */ ATSimplePermissions(rel, ATT_TABLE); - tab->chgLoggedness = ATPrepChangeLoggedness(rel, true); + tab->chgPersistence = ATPrepChangePersistence(rel, true); tab->newrelpersistence = RELPERSISTENCE_PERMANENT; /* force rewrite if necessary */ - if (tab->chgLoggedness) + if (tab->chgPersistence) tab->rewrite = true; pass = AT_PASS_MISC; break; case AT_SetUnLogged: /* SET UNLOGGED */ ATSimplePermissions(rel, ATT_TABLE); - tab->chgLoggedness = ATPrepChangeLoggedness(rel, false); + tab->chgPersistence = ATPrepChangePersistence(rel, false); tab->newrelpersistence = RELPERSISTENCE_UNLOGGED; /* force rewrite if necessary */ - if (tab->chgLoggedness) + if (tab->chgPersistence) tab->rewrite = true; pass = AT_PASS_MISC; break; @@ -3668,7 +3668,7 @@ ATRewriteTables(List **wqueue, LOCKMODE lockmode) * Select persistence of transient table (same as original unless * user requested a change) */ - persistence = tab->chgLoggedness ? + persistence = tab->chgPersistence ? tab->newrelpersistence : OldHeap->rd_rel->relpersistence; heap_close(OldHeap, NoLock); @@ -3705,8 +3705,8 @@ ATRewriteTables(List **wqueue, LOCKMODE lockmode) * because the rewrite step might read the indexes, and that would * cause buffers for them to have the wrong setting. */ - if (tab->chgLoggedness) -ATChangeIndexesLoggedness(tab->relid, tab->newrelpersistence); + if (tab->chgPersistence) +ATChangeIndexesPersistence(tab->relid, tab->newrelpersistence); /* * Swap the physical files of the old and new heaps, then rebuild @@ -4119,7 +4119,7 @@ ATGetQueueEntry(List **wqueue, Rela
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Fri, Aug 22, 2014 at 2:32 PM, Alvaro Herrera wrote: > Fabrízio de Royes Mello wrote: >> Em sexta-feira, 22 de agosto de 2014, Alvaro Herrera < >> alvhe...@2ndquadrant.com> escreveu: >> >> > Fabrízio de Royes Mello wrote: >> > >> > > I forgot to mention... I did again a lot of tests using different >> > > replication scenarios to make sure all is ok: >> > > - slaves async >> > > - slaves sync >> > > - cascade slaves >> > >> > On v13 you mean? >> > >> Exactly! > > Great. Pushed. Thanks for the patch. Hmm. I confess to not having paid enough attention to this, but: 1. Loggedness is not a word. I think that "persistence" or "relpersistence" would be better. 2. The patch seems to think that it can sometimes be safe to change the relpersistence of an existing relation. Unless you can be sure that no buffers can possibly be present in shared_buffers and nobody will use an existing relcache entry to read a new one in, it's not, because the buffers won't have the right BM_PERSISTENT marking. I'm very nervous about the fact that this patch seems not to have touched bufmgr.c, but maybe I'm missing something. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Fri, Aug 22, 2014 at 3:32 PM, Alvaro Herrera wrote: > > Fabrízio de Royes Mello wrote: > > Em sexta-feira, 22 de agosto de 2014, Alvaro Herrera < > > alvhe...@2ndquadrant.com> escreveu: > > > > > Fabrízio de Royes Mello wrote: > > > > > > > I forgot to mention... I did again a lot of tests using different > > > > replication scenarios to make sure all is ok: > > > > - slaves async > > > > - slaves sync > > > > - cascade slaves > > > > > > On v13 you mean? > > > > > Exactly! > > Great. Pushed. Thanks for the patch. > Awesome!!! Actually I should say thank you my friend!! See you in Campinas... Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog: http://fabriziomello.github.io >> Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello >> Github: http://github.com/fabriziomello
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Fabrízio de Royes Mello wrote: > Em sexta-feira, 22 de agosto de 2014, Alvaro Herrera < > alvhe...@2ndquadrant.com> escreveu: > > > Fabrízio de Royes Mello wrote: > > > > > I forgot to mention... I did again a lot of tests using different > > > replication scenarios to make sure all is ok: > > > - slaves async > > > - slaves sync > > > - cascade slaves > > > > On v13 you mean? > > > Exactly! Great. Pushed. Thanks for the patch. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Em sexta-feira, 22 de agosto de 2014, Alvaro Herrera < alvhe...@2ndquadrant.com> escreveu: > Fabrízio de Royes Mello wrote: > > > I forgot to mention... I did again a lot of tests using different > > replication scenarios to make sure all is ok: > > - slaves async > > - slaves sync > > - cascade slaves > > On v13 you mean? > > Exactly! Fabrízio Mello -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog: http://fabriziomello.github.io >> Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello >> Github: http://github.com/fabriziomello
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Fabrízio de Royes Mello wrote: > I forgot to mention... I did again a lot of tests using different > replication scenarios to make sure all is ok: > - slaves async > - slaves sync > - cascade slaves On v13 you mean? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Thu, Aug 21, 2014 at 10:26 PM, Fabrízio de Royes Mello < fabriziome...@gmail.com> wrote: > > > On Thu, Aug 21, 2014 at 8:04 PM, Alvaro Herrera wrote: > > Andres Freund wrote: > > > > > Have you looked at the correctness of the patch itself? Last time I'd > > > looked it has fundamental correctness issues. I'd outlined a possible > > > solution, but I haven't looked since. > > > > Yeah, Fabrizio had it passing the relpersistence down to make_new_heap, > > so the transient table is created with the right setting. AFAICS it's > > good now. I'm a bit uneasy about the way it changes indexes: it just > > updates pg_class for them just before invoking the reindex in > > finish_heap_swap. I think it's correct as it stands though; at least, > > the rewrite phase happens with the right setting, so that if there are > > constraints being checked and these invoke index scans, such accesses > > would not leave buffers with the wrong setting in shared_buffers. > > > > Ok. > > > > Another option would be to pass the new relpersistence down to > > finish_heap_swap, but that would be hugely complicated AFAICS. > > > > I think isn't so complicated to do it, but will this improve something ? > Maybe I didn't understand it very well. IMHO it just complicate a > simple thing. > > > > > Here's the updated patch. > > > > Thanks Alvaro! > I forgot to mention... I did again a lot of tests using different replication scenarios to make sure all is ok: - slaves async - slaves sync - cascade slaves Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog: http://fabriziomello.github.io >> Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello >> Github: http://github.com/fabriziomello
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Thu, Aug 21, 2014 at 8:04 PM, Alvaro Herrera wrote: > Andres Freund wrote: > > > Have you looked at the correctness of the patch itself? Last time I'd > > looked it has fundamental correctness issues. I'd outlined a possible > > solution, but I haven't looked since. > > Yeah, Fabrizio had it passing the relpersistence down to make_new_heap, > so the transient table is created with the right setting. AFAICS it's > good now. I'm a bit uneasy about the way it changes indexes: it just > updates pg_class for them just before invoking the reindex in > finish_heap_swap. I think it's correct as it stands though; at least, > the rewrite phase happens with the right setting, so that if there are > constraints being checked and these invoke index scans, such accesses > would not leave buffers with the wrong setting in shared_buffers. > Ok. > Another option would be to pass the new relpersistence down to > finish_heap_swap, but that would be hugely complicated AFAICS. > I think isn't so complicated to do it, but will this improve something ? Maybe I didn't understand it very well. IMHO it just complicate a simple thing. > Here's the updated patch. > Thanks Alvaro! Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog: http://fabriziomello.github.io >> Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello >> Github: http://github.com/fabriziomello
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Tom Lane wrote:
> Alvaro Herrera writes:
> > Agreed. I am going over this patch, and the last bit I need to sort out
> > is the wording of these messages. I have temporarily settled on this:
>
> > ereport(ERROR,
> > (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
> > errmsg("cannot change logged status of table %s to
> > logged",
> > RelationGetRelationName(rel)),
> > errdetail("Table %s references unlogged table %s.",
> >RelationGetRelationName(rel),
> >RelationGetRelationName(relfk;
>
> > Note the term "logged status" to talk about whether a table is logged or
> > not. I thought about "loggedness" but I'm not sure english speakers are
> > going to love me for that. Any other ideas there?
>
> Just say "cannot change status of table %s to logged".
I like this one, thanks.
> > Yeah, there is precedent for silently doing nothing. We previously
> > threw warnings or notices, but nowadays even that is gone. Throwing an
> > error definitely seems the wrong thing.
>
> Agreed, just do nothing if it's already the right setting.
Done that way.
Andres Freund wrote:
> Have you looked at the correctness of the patch itself? Last time I'd
> looked it has fundamental correctness issues. I'd outlined a possible
> solution, but I haven't looked since.
Yeah, Fabrizio had it passing the relpersistence down to make_new_heap,
so the transient table is created with the right setting. AFAICS it's
good now. I'm a bit uneasy about the way it changes indexes: it just
updates pg_class for them just before invoking the reindex in
finish_heap_swap. I think it's correct as it stands though; at least,
the rewrite phase happens with the right setting, so that if there are
constraints being checked and these invoke index scans, such accesses
would not leave buffers with the wrong setting in shared_buffers.
Another option would be to pass the new relpersistence down to
finish_heap_swap, but that would be hugely complicated AFAICS.
Here's the updated patch.
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
*** a/doc/src/sgml/ref/alter_table.sgml
--- b/doc/src/sgml/ref/alter_table.sgml
***
*** 61,66 ALTER TABLE [ IF EXISTS ] name
--- 61,68
SET WITHOUT CLUSTER
SET WITH OIDS
SET WITHOUT OIDS
+ SET TABLESPACE new_tablespace
+ SET {LOGGED | UNLOGGED}
SET ( storage_parameter = value [, ... ] )
RESET ( storage_parameter [, ... ] )
INHERIT parent_table
***
*** 68,74 ALTER TABLE [ IF EXISTS ] name
OF type_name
NOT OF
OWNER TO new_owner
- SET TABLESPACE new_tablespace
REPLICA IDENTITY {DEFAULT | USING INDEX index_name | FULL | NOTHING}
and table_constraint_using_index is:
--- 70,75
***
*** 477,482 ALTER TABLE [ IF EXISTS ] name
--- 478,508
+ SET TABLESPACE
+
+
+ This form changes the table's tablespace to the specified tablespace and
+ moves the data file(s) associated with the table to the new tablespace.
+ Indexes on the table, if any, are not moved; but they can be moved
+ separately with additional SET TABLESPACE commands.
+ See also
+ .
+
+
+
+
+
+ SET {LOGGED | UNLOGGED}
+
+
+ This form changes the table from unlogged to logged or vice-versa
+ (see ). It cannot be applied
+ to a temporary table.
+
+
+
+
+
SET ( storage_parameter = value [, ... ] )
***
*** 589,608 ALTER TABLE [ IF EXISTS ] name
-
- SET TABLESPACE
-
-
- This form changes the table's tablespace to the specified tablespace and
- moves the data file(s) associated with the table to the new tablespace.
- Indexes on the table, if any, are not moved; but they can be moved
- separately with additional SET TABLESPACE commands.
- See also
- .
-
-
-
-
REPLICA IDENTITY
--- 615,620
*** a/src/backend/commands/cluster.c
--- b/src/backend/commands/cluster.c
***
*** 574,580 rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
heap_close(OldHeap, NoLock);
/* Create the transient table that will receive the re-ordered data */
! OIDNewHeap = make_new_heap(tableOid, tableSpace, false,
AccessExclusiveLock);
/* Copy the heap data into the new table in the desired order */
--- 574,581
heap_close(OldHeap, NoLock);
/* Create the transient table that will receive the re-ordered data */
! OIDNewHeap = make_new_heap(tableOid, tableSpace,
! OldHeap->rd_rel->relpersistence,
AccessExclusiveLock);
/* Copy the heap data i
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On 2014-08-21 16:59:17 -0400, Alvaro Herrera wrote:
> Heikki Linnakangas wrote:
>
> > In Postgres internals slang, non-permanent means temporary or
> > unlogged. But I agree we shouldn't expose users to that term; we use
> > it in the docs, and it's not used in command names either.
>
> Agreed. I am going over this patch, and the last bit I need to sort out
> is the wording of these messages. I have temporarily settled on this:
>
> ereport(ERROR,
> (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
>errmsg("cannot change logged status of table %s to
> logged",
> RelationGetRelationName(rel)),
>errdetail("Table %s references unlogged table %s.",
> RelationGetRelationName(rel),
> RelationGetRelationName(relfk;
Maybe 'cannot change persistency of table .. from unlogged to logged'; possibly
with
s/persistency/durability/?
Have you looked at the correctness of the patch itself? Last time I'd
looked it has fundamental correctness issues. I'd outlined a possible
solution, but I haven't looked since.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Alvaro Herrera writes:
> Agreed. I am going over this patch, and the last bit I need to sort out
> is the wording of these messages. I have temporarily settled on this:
> ereport(ERROR,
> (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
>errmsg("cannot change logged status of table %s to
> logged",
> RelationGetRelationName(rel)),
>errdetail("Table %s references unlogged table %s.",
> RelationGetRelationName(rel),
> RelationGetRelationName(relfk;
> Note the term "logged status" to talk about whether a table is logged or
> not. I thought about "loggedness" but I'm not sure english speakers are
> going to love me for that. Any other ideas there?
Just say "cannot change status of table %s to logged".
> Yeah, there is precedent for silently doing nothing. We previously
> threw warnings or notices, but nowadays even that is gone. Throwing an
> error definitely seems the wrong thing.
Agreed, just do nothing if it's already the right setting.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Heikki Linnakangas wrote:
> In Postgres internals slang, non-permanent means temporary or
> unlogged. But I agree we shouldn't expose users to that term; we use
> it in the docs, and it's not used in command names either.
Agreed. I am going over this patch, and the last bit I need to sort out
is the wording of these messages. I have temporarily settled on this:
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("cannot change logged status of table %s to
logged",
RelationGetRelationName(rel)),
errdetail("Table %s references unlogged table %s.",
RelationGetRelationName(rel),
RelationGetRelationName(relfk;
Note the term "logged status" to talk about whether a table is logged or
not. I thought about "loggedness" but I'm not sure english speakers are
going to love me for that. Any other ideas there?
> I wonder if throwing an error is correct behavior anyway. Other
> ALTER TABLE commands just silently do nothing in similar situations,
> e.g:
>
> lowerdb=# CREATE TABLE foo () WITH OIDS;
> CREATE TABLE
> lowerdb=# ALTER TABLE foo SET WITH OIDS;
> ALTER TABLE
>
> But if we want to throw an error anyway, I'd suggest phrasing it
> "table foo is already unlogged"
Yeah, there is precedent for silently doing nothing. We previously
threw warnings or notices, but nowadays even that is gone. Throwing an
error definitely seems the wrong thing. In the patch I have it's like
this:
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("cannot change logged status of table %s to
unlogged",
RelationGetRelationName(rel)),
errdetail("Table %s is already unlogged.",
RelationGetRelationName(rel;
but I think I will just take that out as a whole and set a flag to
indicate nothing is to be done.
(This also means that setting tab->rewrite while analyzing the command
is the wrong thing to do. Instead, the test for tab->rewrite should
have an || tab->chgLoggedness test, and we set chgLoggedness off if we
see that it's a no-op. That way we avoid a pointless table rewrite and
a pointless error in a multi-command ALTER TABLE that has a no-op SET
LOGGED subcommand among other things.)
I changed the doc item in ALTER TABLE,
SET {LOGGED | UNLOGGED}
This form changes the table from unlogged to logged or vice-versa
(see ). It cannot be applied
to a temporary table.
I removed the fact that it needs ACCESS EXCLUSIVE because that's already
mentioned in the introductory paragraph. I also removed the phrase that
it requires a table rewrite, because on reading existing text I noticed
that we don't document which forms cause rewrites. Perhaps we should
document that somehow, but adding it to only one item seems wrong.
I will post an updated patch as soon as I fix a bug I introduced in the
check for FKs.
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On 08/21/2014 05:04 PM, Thom Brown wrote: On 21 August 2014 14:45, Fabrízio de Royes Mello wrote: On Thu, Aug 21, 2014 at 5:23 AM, Christoph Berg wrote: Re: Thom Brown 2014-08-20 7u7tsgl4s5jh1a+shq_ny7gorzc_g_yj7...@mail.gmail.com> "ERROR: table test is not permanent" Perhaps this would be better as "table test is unlogged" as "permanent" doesn't match the term used in the DDL syntax. I was also wondering that, but then figured that when ALTER TABLE SET UNLOGGED is invoked on temp tables, the error message "is not permanent" was correct while the apparent opposite "is unlogged" is wrong. Thom, Christoph is right... make no sense the message... see the example: fabrizio=# create temp table foo(); CREATE TABLE fabrizio=# alter table foo set unlogged; ERROR: table foo is unlogged The previous message is better: fabrizio=# create temp table foo(); CREATE TABLE fabrizio=# alter table foo set unlogged; ERROR: table foo is not permanent fabrizio=# fabrizio=# create unlogged table foo2(); CREATE TABLE fabrizio=# alter table foo2 set unlogged; ERROR: table foo2 is not permanent To me, that's even more confusing: CREATE TEMP TABLE test(); CREATE UNLOGGED TABLE test2(); # ALTER TABLE test SET LOGGED; ERROR: table test is not unlogged # ALTER TABLE test SET UNLOGGED; ERROR: table test is not permanent # ALTER TABLE test2 SET UNLOGGED; ERROR: table test2 is not permanent They're being rejected for different reasons but the error message is identical. Permanent suggests the opposite of temporary, and unlogged tables aren't temporary. In Postgres internals slang, non-permanent means temporary or unlogged. But I agree we shouldn't expose users to that term; we use it in the docs, and it's not used in command names either. I wonder if throwing an error is correct behavior anyway. Other ALTER TABLE commands just silently do nothing in similar situations, e.g: lowerdb=# CREATE TABLE foo () WITH OIDS; CREATE TABLE lowerdb=# ALTER TABLE foo SET WITH OIDS; ALTER TABLE But if we want to throw an error anyway, I'd suggest phrasing it "table foo is already unlogged" - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On 21 August 2014 14:45, Fabrízio de Royes Mello wrote: > > On Thu, Aug 21, 2014 at 5:23 AM, Christoph Berg wrote: > > > > Re: Thom Brown 2014-08-20 7u7tsgl4s5jh1a+shq_ny7gorzc_g_yj7...@mail.gmail.com> > > > "ERROR: table test is not permanent" > > > > > > Perhaps this would be better as "table test is unlogged" as "permanent" > > > doesn't match the term used in the DDL syntax. > > > > I was also wondering that, but then figured that when ALTER TABLE SET > > UNLOGGED is invoked on temp tables, the error message "is not > > permanent" was correct while the apparent opposite "is unlogged" is > > wrong. > > > > Christoph > > -- > > c...@df7cb.de | http://www.df7cb.de/ > > Thom, > > Christoph is right... make no sense the message... see the example: > > fabrizio=# create temp table foo(); > CREATE TABLE > fabrizio=# alter table foo set unlogged; > ERROR: table foo is unlogged > > The previous message is better: > > fabrizio=# create temp table foo(); > CREATE TABLE > fabrizio=# alter table foo set unlogged; > ERROR: table foo is not permanent > fabrizio=# > fabrizio=# create unlogged table foo2(); > CREATE TABLE > fabrizio=# alter table foo2 set unlogged; > ERROR: table foo2 is not permanent > To me, that's even more confusing: CREATE TEMP TABLE test(); CREATE UNLOGGED TABLE test2(); # ALTER TABLE test SET LOGGED; ERROR: table test is not unlogged # ALTER TABLE test SET UNLOGGED; ERROR: table test is not permanent # ALTER TABLE test2 SET UNLOGGED; ERROR: table test2 is not permanent They're being rejected for different reasons but the error message is identical. Permanent suggests the opposite of temporary, and unlogged tables aren't temporary. -- Thom
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Thu, Aug 21, 2014 at 5:23 AM, Christoph Berg wrote:
>
> Re: Thom Brown 2014-08-20
> > "ERROR: table test is not permanent"
> >
> > Perhaps this would be better as "table test is unlogged" as "permanent"
> > doesn't match the term used in the DDL syntax.
>
> I was also wondering that, but then figured that when ALTER TABLE SET
> UNLOGGED is invoked on temp tables, the error message "is not
> permanent" was correct while the apparent opposite "is unlogged" is
> wrong.
>
> Christoph
> --
> c...@df7cb.de | http://www.df7cb.de/
Thom,
Christoph is right... make no sense the message... see the example:
fabrizio=# create temp table foo();
CREATE TABLE
fabrizio=# alter table foo set unlogged;
ERROR: table foo is unlogged
The previous message is better:
fabrizio=# create temp table foo();
CREATE TABLE
fabrizio=# alter table foo set unlogged;
ERROR: table foo is not permanent
fabrizio=#
fabrizio=# create unlogged table foo2();
CREATE TABLE
fabrizio=# alter table foo2 set unlogged;
ERROR: table foo2 is not permanent
Patch attached.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 69a1e14..397b4e6 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -59,16 +59,17 @@ ALTER TABLE [ IF EXISTS ] name
ENABLE ALWAYS RULE rewrite_rule_name
CLUSTER ON index_name
SET WITHOUT CLUSTER
+SET {LOGGED | UNLOGGED}
SET WITH OIDS
SET WITHOUT OIDS
SET ( storage_parameter = value [, ... ] )
+SET TABLESPACE new_tablespace
RESET ( storage_parameter [, ... ] )
INHERIT parent_table
NO INHERIT parent_table
OF type_name
NOT OF
OWNER TO new_owner
-SET TABLESPACE new_tablespace
REPLICA IDENTITY {DEFAULT | USING INDEX index_name | FULL | NOTHING}
and table_constraint_using_index is:
@@ -447,6 +448,20 @@ ALTER TABLE [ IF EXISTS ] name
+SET {LOGGED | UNLOGGED}
+
+
+ This form changes the table persistence type from unlogged to permanent or
+ from permanent to unlogged (see ).
+
+
+ Changing the table persistence type acquires an ACCESS EXCLUSIVE lock
+ and rewrites the table contents and associated indexes into new disk files.
+
+
+
+
+
SET WITH OIDS
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index b1c411a..7f497c7 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -574,7 +574,8 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
heap_close(OldHeap, NoLock);
/* Create the transient table that will receive the re-ordered data */
- OIDNewHeap = make_new_heap(tableOid, tableSpace, false,
+ OIDNewHeap = make_new_heap(tableOid, tableSpace,
+ OldHeap->rd_rel->relpersistence,
AccessExclusiveLock);
/* Copy the heap data into the new table in the desired order */
@@ -601,7 +602,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
* data, then call finish_heap_swap to complete the operation.
*/
Oid
-make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
+make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
LOCKMODE lockmode)
{
TupleDesc OldHeapDesc;
@@ -613,7 +614,6 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
Datum reloptions;
bool isNull;
Oid namespaceid;
- char relpersistence;
OldHeap = heap_open(OIDOldHeap, lockmode);
OldHeapDesc = RelationGetDescr(OldHeap);
@@ -636,16 +636,10 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
if (isNull)
reloptions = (Datum) 0;
- if (forcetemp)
- {
+ if (relpersistence == RELPERSISTENCE_TEMP)
namespaceid = LookupCreationNamespace("pg_temp");
- relpersistence = RELPERSISTENCE_TEMP;
- }
else
- {
namespaceid = RelationGetNamespace(OldHeap);
- relpersistence = OldHeap->rd_rel->relpersistence;
- }
/*
* Create the new heap, using a temporary name in the same namespace as
@@ -1146,6 +1140,7 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
Oid relfilenode1,
relfilenode2;
Oid swaptemp;
+ char swaprelpersistence;
CatalogIndexState indstate;
/* We need writable copies of both pg_class tuples. */
@@ -1177,6 +1172,10 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
relform1->reltablespace = relform2->reltablespace;
relform2->reltablespace = swaptemp;
+ swaprelpersistence = relform1->relpersistence;
+ relform1->relpersistence = relform2->relpersistence;
+ relform2->relpersistence = swaprelpersistence;
+
/* Also swap toast links, if we're swapping by links */
if (!swap_toast_by_content)
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Re: Thom Brown 2014-08-20 > "ERROR: table test is not permanent" > > Perhaps this would be better as "table test is unlogged" as "permanent" > doesn't match the term used in the DDL syntax. I was also wondering that, but then figured that when ALTER TABLE SET UNLOGGED is invoked on temp tables, the error message "is not permanent" was correct while the apparent opposite "is unlogged" is wrong. Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Wed, Aug 20, 2014 at 12:35 PM, Thom Brown wrote:
>
> Hi Fabrizio,
>
> + This form changes the table persistence type from unlogged to
permanent or
> + from unlogged to permanent (see ).
>
> Shouldn't this read "unlogged to permanent or from permanent to unlogged"?
>
Fixed.
> "ERROR: table test is not permanent"
>
> Perhaps this would be better as "table test is unlogged" as "permanent"
doesn't match the term used in the DDL syntax.
>
Fixed.
> Gave the patch a quick test-drive on a replicated instance, and it
appears to operate as described.
>
Thanks for your review.
Att,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 69a1e14..397b4e6 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -59,16 +59,17 @@ ALTER TABLE [ IF EXISTS ] name
ENABLE ALWAYS RULE rewrite_rule_name
CLUSTER ON index_name
SET WITHOUT CLUSTER
+SET {LOGGED | UNLOGGED}
SET WITH OIDS
SET WITHOUT OIDS
SET ( storage_parameter = value [, ... ] )
+SET TABLESPACE new_tablespace
RESET ( storage_parameter [, ... ] )
INHERIT parent_table
NO INHERIT parent_table
OF type_name
NOT OF
OWNER TO new_owner
-SET TABLESPACE new_tablespace
REPLICA IDENTITY {DEFAULT | USING INDEX index_name | FULL | NOTHING}
and table_constraint_using_index is:
@@ -447,6 +448,20 @@ ALTER TABLE [ IF EXISTS ] name
+SET {LOGGED | UNLOGGED}
+
+
+ This form changes the table persistence type from unlogged to permanent or
+ from permanent to unlogged (see ).
+
+
+ Changing the table persistence type acquires an ACCESS EXCLUSIVE lock
+ and rewrites the table contents and associated indexes into new disk files.
+
+
+
+
+
SET WITH OIDS
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index b1c411a..7f497c7 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -574,7 +574,8 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
heap_close(OldHeap, NoLock);
/* Create the transient table that will receive the re-ordered data */
- OIDNewHeap = make_new_heap(tableOid, tableSpace, false,
+ OIDNewHeap = make_new_heap(tableOid, tableSpace,
+ OldHeap->rd_rel->relpersistence,
AccessExclusiveLock);
/* Copy the heap data into the new table in the desired order */
@@ -601,7 +602,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
* data, then call finish_heap_swap to complete the operation.
*/
Oid
-make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
+make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
LOCKMODE lockmode)
{
TupleDesc OldHeapDesc;
@@ -613,7 +614,6 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
Datum reloptions;
bool isNull;
Oid namespaceid;
- char relpersistence;
OldHeap = heap_open(OIDOldHeap, lockmode);
OldHeapDesc = RelationGetDescr(OldHeap);
@@ -636,16 +636,10 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
if (isNull)
reloptions = (Datum) 0;
- if (forcetemp)
- {
+ if (relpersistence == RELPERSISTENCE_TEMP)
namespaceid = LookupCreationNamespace("pg_temp");
- relpersistence = RELPERSISTENCE_TEMP;
- }
else
- {
namespaceid = RelationGetNamespace(OldHeap);
- relpersistence = OldHeap->rd_rel->relpersistence;
- }
/*
* Create the new heap, using a temporary name in the same namespace as
@@ -1146,6 +1140,7 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
Oid relfilenode1,
relfilenode2;
Oid swaptemp;
+ char swaprelpersistence;
CatalogIndexState indstate;
/* We need writable copies of both pg_class tuples. */
@@ -1177,6 +1172,10 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
relform1->reltablespace = relform2->reltablespace;
relform2->reltablespace = swaptemp;
+ swaprelpersistence = relform1->relpersistence;
+ relform1->relpersistence = relform2->relpersistence;
+ relform2->relpersistence = swaprelpersistence;
+
/* Also swap toast links, if we're swapping by links */
if (!swap_toast_by_content)
{
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 5130d51..a49e66f 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -147,6 +147,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
DestReceiver *dest;
bool concurrent;
LOCKMODE lockmode;
+ char relpersistence;
/* Determine strength of lock needed. */
concurrent = stmt->concurrent;
@@ -233,9 +234,15 @@
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On 17 August 2014 21:45, Fabrízio de Royes Mello wrote: > > On Mon, Jul 28, 2014 at 2:24 PM, Fabrízio de Royes Mello < > fabriziome...@gmail.com> wrote: > > > > > > On Mon, Jul 28, 2014 at 1:41 PM, Christoph Berg wrote: > > > > > > Re: Fabrízio de Royes Mello 2014-07-28 > > > > > There are something that should I do on this patch yet? > > > > > > I haven't got around to have a look at the newest incarnation yet, but > > > I plan to do that soonish. (Of course that shouldn't stop others from > > > doing that as well if they wish.) > > > > > > > Thanks! > > > > Updated version. > Hi Fabrizio, + This form changes the table persistence type from unlogged to permanent or + from unlogged to permanent (see ). Shouldn't this read "unlogged to permanent or from permanent to unlogged"? "ERROR: table test is not permanent" Perhaps this would be better as "table test is unlogged" as "permanent" doesn't match the term used in the DDL syntax. Gave the patch a quick test-drive on a replicated instance, and it appears to operate as described. -- Thom
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Mon, Jul 28, 2014 at 2:24 PM, Fabrízio de Royes Mello <
fabriziome...@gmail.com> wrote:
>
>
> On Mon, Jul 28, 2014 at 1:41 PM, Christoph Berg wrote:
> >
> > Re: Fabrízio de Royes Mello 2014-07-28
> > > There are something that should I do on this patch yet?
> >
> > I haven't got around to have a look at the newest incarnation yet, but
> > I plan to do that soonish. (Of course that shouldn't stop others from
> > doing that as well if they wish.)
> >
>
> Thanks!
>
Updated version.
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 69a1e14..2d131df 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -59,16 +59,17 @@ ALTER TABLE [ IF EXISTS ] name
ENABLE ALWAYS RULE rewrite_rule_name
CLUSTER ON index_name
SET WITHOUT CLUSTER
+SET {LOGGED | UNLOGGED}
SET WITH OIDS
SET WITHOUT OIDS
SET ( storage_parameter = value [, ... ] )
+SET TABLESPACE new_tablespace
RESET ( storage_parameter [, ... ] )
INHERIT parent_table
NO INHERIT parent_table
OF type_name
NOT OF
OWNER TO new_owner
-SET TABLESPACE new_tablespace
REPLICA IDENTITY {DEFAULT | USING INDEX index_name | FULL | NOTHING}
and table_constraint_using_index is:
@@ -447,6 +448,20 @@ ALTER TABLE [ IF EXISTS ] name
+SET {LOGGED | UNLOGGED}
+
+
+ This form changes the table persistence type from unlogged to permanent or
+ from unlogged to permanent (see ).
+
+
+ Changing the table persistence type acquires an ACCESS EXCLUSIVE lock
+ and rewrites the table contents and associated indexes into new disk files.
+
+
+
+
+
SET WITH OIDS
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index b1c411a..7f497c7 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -574,7 +574,8 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
heap_close(OldHeap, NoLock);
/* Create the transient table that will receive the re-ordered data */
- OIDNewHeap = make_new_heap(tableOid, tableSpace, false,
+ OIDNewHeap = make_new_heap(tableOid, tableSpace,
+ OldHeap->rd_rel->relpersistence,
AccessExclusiveLock);
/* Copy the heap data into the new table in the desired order */
@@ -601,7 +602,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
* data, then call finish_heap_swap to complete the operation.
*/
Oid
-make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
+make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
LOCKMODE lockmode)
{
TupleDesc OldHeapDesc;
@@ -613,7 +614,6 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
Datum reloptions;
bool isNull;
Oid namespaceid;
- char relpersistence;
OldHeap = heap_open(OIDOldHeap, lockmode);
OldHeapDesc = RelationGetDescr(OldHeap);
@@ -636,16 +636,10 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
if (isNull)
reloptions = (Datum) 0;
- if (forcetemp)
- {
+ if (relpersistence == RELPERSISTENCE_TEMP)
namespaceid = LookupCreationNamespace("pg_temp");
- relpersistence = RELPERSISTENCE_TEMP;
- }
else
- {
namespaceid = RelationGetNamespace(OldHeap);
- relpersistence = OldHeap->rd_rel->relpersistence;
- }
/*
* Create the new heap, using a temporary name in the same namespace as
@@ -1146,6 +1140,7 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
Oid relfilenode1,
relfilenode2;
Oid swaptemp;
+ char swaprelpersistence;
CatalogIndexState indstate;
/* We need writable copies of both pg_class tuples. */
@@ -1177,6 +1172,10 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
relform1->reltablespace = relform2->reltablespace;
relform2->reltablespace = swaptemp;
+ swaprelpersistence = relform1->relpersistence;
+ relform1->relpersistence = relform2->relpersistence;
+ relform2->relpersistence = swaprelpersistence;
+
/* Also swap toast links, if we're swapping by links */
if (!swap_toast_by_content)
{
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 5130d51..a49e66f 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -147,6 +147,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
DestReceiver *dest;
bool concurrent;
LOCKMODE lockmode;
+ char relpersistence;
/* Determine strength of lock needed. */
concurrent = stmt->concurrent;
@@ -233,9 +234,15 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
/* Concurrent refresh builds new data in temp tablespace, an
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Mon, Jul 28, 2014 at 1:41 PM, Christoph Berg wrote: > > Re: Fabrízio de Royes Mello 2014-07-28 > > There are something that should I do on this patch yet? > > I haven't got around to have a look at the newest incarnation yet, but > I plan to do that soonish. (Of course that shouldn't stop others from > doing that as well if they wish.) > Thanks! -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog sobre TI: http://fabriziomello.blogspot.com >> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Re: Fabrízio de Royes Mello 2014-07-28 > There are something that should I do on this patch yet? I haven't got around to have a look at the newest incarnation yet, but I plan to do that soonish. (Of course that shouldn't stop others from doing that as well if they wish.) Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Wed, Jul 23, 2014 at 5:48 PM, Fabrízio de Royes Mello <
fabriziome...@gmail.com> wrote:
>
>
> On Tue, Jul 22, 2014 at 3:29 PM, Fabrízio de Royes Mello <
fabriziome...@gmail.com> wrote:
> >
> > On Tue, Jul 22, 2014 at 12:01 PM, Fabrízio de Royes Mello <
fabriziome...@gmail.com> wrote:
> > >
> > >
> > >
> > > On Thu, Jul 17, 2014 at 8:02 PM, Andres Freund
wrote:
> > > >
> > > > > That means should I "FlushRelationBuffers(rel)" before change the
> > > > > relpersistence?
> > > >
> > > > I don't think that'd help. I think what this means that you simply
> > > > cannot change the relpersistence of the old relation before the heap
> > > > swap is successful. So I guess it has to be something like
(pseudocode):
> > > >
> > > > OIDNewHeap = make_new_heap(..);
> > > > newrel = heap_open(OIDNewHeap, AEL);
> > > >
> > > > /*
> > > > * Change the temporary relation to be unlogged/logged. We have to
do
> > > > * that here so buffers for the new relfilenode will have the right
> > > > * persistency set while the original filenode's buffers won't get
read
> > > > * in with the wrong (i.e. new) persistency setting. Otherwise a
> > > > * rollback after the rewrite would possibly result with buffers
for the
> > > > * original filenode having the wrong persistency setting.
> > > > *
> > > > * NB: This relies on swap_relation_files() also swapping the
> > > > * persistency. That wouldn't work for pg_class, but that can't be
> > > > * unlogged anyway.
> > > > */
> > > > AlterTableChangeCatalogToLoggedOrUnlogged(newrel);
> > > > FlushRelationBuffers(newrel);
> > > > /* copy heap data into newrel */
> > > > finish_heap_swap();
> > > >
> > > > And then in swap_relation_files() also copy the persistency.
> > > >
> > > >
> > > > That's the best I can come up right now at least.
> > > >
> > >
> > > Isn't better if we can set the relpersistence as an argument to
"make_new_heap" ?
> > >
> > > I'm thinking to change the make_new_heap:
> > >
> > > From:
> > >
> > > make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
> > >LOCKMODE lockmode)
> > >
> > > To:
> > >
> > > make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
> > >LOCKMODE lockmode)
> > >
> > > That way we can create the new heap with the appropriate
relpersistence, so in the swap_relation_files also copy the persistency, of
course.
> > >
> > > And after copy the heap data to the new table (ATRewriteTable) change
relpersistence of the OldHeap's indexes, because in the "finish_heap_swap"
they'll be rebuild.
> > >
> > > Thoughts?
> > >
> >
> > The attached patch implement my previous idea based on Andres thoughts.
> >
>
> I don't liked the last version of the patch, especially this part:
>
> +/* check if SetUnlogged or SetLogged exists in subcmds */
> +for(pass = 0; pass < AT_NUM_PASSES; pass++)
> +{
> +List *subcmds = tab->subcmds[pass];
> +ListCell*lcmd;
> +
> +if (subcmds == NIL)
> +continue;
> +
> +foreach(lcmd, subcmds)
> +{
> +AlterTableCmd *cmd = (AlterTableCmd *) lfirst(lcmd);
> +
> +if (cmd->subtype == AT_SetUnLogged || cmd->subtype
== AT_SetLogged)
> +{
> +/*
> + * Change the temporary relation to be
unlogged/logged. We have to do
> + * that here so buffers for the new relfilenode
will have the right
> + * persistency set while the original filenode's
buffers won't get read
> + * in with the wrong (i.e. new) persistency
setting. Otherwise a
> + * rollback after the rewrite would possibly
result with buffers for the
> + * original filenode having the wrong
persistency setting.
> + *
> + * NB: This relies on swap_relation_files() also
swapping the
> + * persistency. That wouldn't work for pg_class,
but that can't be
> + * unlogged anyway.
> + */
> +if (cmd->subtype == AT_SetUnLogged)
> +newrelpersistence = RELPERSISTENCE_UNLOGGED;
> +
> +isSetLoggedUnlogged = true;
> +}
> +}
> +}
>
>
> So I did a refactoring adding new items to AlteredTableInfo to pass the
information through the phases.
>
Hi all,
There are something that should I do on this patch yet?
Regards
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Tue, Jul 22, 2014 at 3:29 PM, Fabrízio de Royes Mello <
fabriziome...@gmail.com> wrote:
>
> On Tue, Jul 22, 2014 at 12:01 PM, Fabrízio de Royes Mello <
fabriziome...@gmail.com> wrote:
> >
> >
> >
> > On Thu, Jul 17, 2014 at 8:02 PM, Andres Freund
wrote:
> > >
> > > > That means should I "FlushRelationBuffers(rel)" before change the
> > > > relpersistence?
> > >
> > > I don't think that'd help. I think what this means that you simply
> > > cannot change the relpersistence of the old relation before the heap
> > > swap is successful. So I guess it has to be something like
(pseudocode):
> > >
> > > OIDNewHeap = make_new_heap(..);
> > > newrel = heap_open(OIDNewHeap, AEL);
> > >
> > > /*
> > > * Change the temporary relation to be unlogged/logged. We have to do
> > > * that here so buffers for the new relfilenode will have the right
> > > * persistency set while the original filenode's buffers won't get
read
> > > * in with the wrong (i.e. new) persistency setting. Otherwise a
> > > * rollback after the rewrite would possibly result with buffers for
the
> > > * original filenode having the wrong persistency setting.
> > > *
> > > * NB: This relies on swap_relation_files() also swapping the
> > > * persistency. That wouldn't work for pg_class, but that can't be
> > > * unlogged anyway.
> > > */
> > > AlterTableChangeCatalogToLoggedOrUnlogged(newrel);
> > > FlushRelationBuffers(newrel);
> > > /* copy heap data into newrel */
> > > finish_heap_swap();
> > >
> > > And then in swap_relation_files() also copy the persistency.
> > >
> > >
> > > That's the best I can come up right now at least.
> > >
> >
> > Isn't better if we can set the relpersistence as an argument to
"make_new_heap" ?
> >
> > I'm thinking to change the make_new_heap:
> >
> > From:
> >
> > make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
> >LOCKMODE lockmode)
> >
> > To:
> >
> > make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
> >LOCKMODE lockmode)
> >
> > That way we can create the new heap with the appropriate
relpersistence, so in the swap_relation_files also copy the persistency, of
course.
> >
> > And after copy the heap data to the new table (ATRewriteTable) change
relpersistence of the OldHeap's indexes, because in the "finish_heap_swap"
they'll be rebuild.
> >
> > Thoughts?
> >
>
> The attached patch implement my previous idea based on Andres thoughts.
>
I don't liked the last version of the patch, especially this part:
+/* check if SetUnlogged or SetLogged exists in subcmds */
+for(pass = 0; pass < AT_NUM_PASSES; pass++)
+{
+List *subcmds = tab->subcmds[pass];
+ListCell*lcmd;
+
+if (subcmds == NIL)
+continue;
+
+foreach(lcmd, subcmds)
+{
+AlterTableCmd *cmd = (AlterTableCmd *) lfirst(lcmd);
+
+if (cmd->subtype == AT_SetUnLogged || cmd->subtype ==
AT_SetLogged)
+{
+/*
+ * Change the temporary relation to be
unlogged/logged. We have to do
+ * that here so buffers for the new relfilenode
will have the right
+ * persistency set while the original filenode's
buffers won't get read
+ * in with the wrong (i.e. new) persistency
setting. Otherwise a
+ * rollback after the rewrite would possibly
result with buffers for the
+ * original filenode having the wrong persistency
setting.
+ *
+ * NB: This relies on swap_relation_files() also
swapping the
+ * persistency. That wouldn't work for pg_class,
but that can't be
+ * unlogged anyway.
+ */
+if (cmd->subtype == AT_SetUnLogged)
+newrelpersistence = RELPERSISTENCE_UNLOGGED;
+
+isSetLoggedUnlogged = true;
+}
+}
+}
So I did a refactoring adding new items to AlteredTableInfo to pass the
information through the phases.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 69a1e14..2d131df 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -59,16 +59,17 @@ ALTER TABLE [ IF EXISTS ] name
ENABLE ALWAYS RULE rewrite_rule_name
CLUSTER ON index_name
SET WITHOUT CLUSTER
+SET {LOGGED | UNLOGGED}
SET WITH OIDS
SET WITHOUT OIDS
SET ( storage_parameter =
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Tue, Jul 22, 2014 at 12:01 PM, Fabrízio de Royes Mello <
fabriziome...@gmail.com> wrote:
>
>
>
> On Thu, Jul 17, 2014 at 8:02 PM, Andres Freund
wrote:
> >
> > > That means should I "FlushRelationBuffers(rel)" before change the
> > > relpersistence?
> >
> > I don't think that'd help. I think what this means that you simply
> > cannot change the relpersistence of the old relation before the heap
> > swap is successful. So I guess it has to be something like (pseudocode):
> >
> > OIDNewHeap = make_new_heap(..);
> > newrel = heap_open(OIDNewHeap, AEL);
> >
> > /*
> > * Change the temporary relation to be unlogged/logged. We have to do
> > * that here so buffers for the new relfilenode will have the right
> > * persistency set while the original filenode's buffers won't get read
> > * in with the wrong (i.e. new) persistency setting. Otherwise a
> > * rollback after the rewrite would possibly result with buffers for the
> > * original filenode having the wrong persistency setting.
> > *
> > * NB: This relies on swap_relation_files() also swapping the
> > * persistency. That wouldn't work for pg_class, but that can't be
> > * unlogged anyway.
> > */
> > AlterTableChangeCatalogToLoggedOrUnlogged(newrel);
> > FlushRelationBuffers(newrel);
> > /* copy heap data into newrel */
> > finish_heap_swap();
> >
> > And then in swap_relation_files() also copy the persistency.
> >
> >
> > That's the best I can come up right now at least.
> >
>
> Isn't better if we can set the relpersistence as an argument to
"make_new_heap" ?
>
> I'm thinking to change the make_new_heap:
>
> From:
>
> make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
>LOCKMODE lockmode)
>
> To:
>
> make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
>LOCKMODE lockmode)
>
> That way we can create the new heap with the appropriate relpersistence,
so in the swap_relation_files also copy the persistency, of course.
>
> And after copy the heap data to the new table (ATRewriteTable) change
relpersistence of the OldHeap's indexes, because in the "finish_heap_swap"
they'll be rebuild.
>
> Thoughts?
>
The attached patch implement my previous idea based on Andres thoughts.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 69a1e14..2d131df 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -59,16 +59,17 @@ ALTER TABLE [ IF EXISTS ] name
ENABLE ALWAYS RULE rewrite_rule_name
CLUSTER ON index_name
SET WITHOUT CLUSTER
+SET {LOGGED | UNLOGGED}
SET WITH OIDS
SET WITHOUT OIDS
SET ( storage_parameter = value [, ... ] )
+SET TABLESPACE new_tablespace
RESET ( storage_parameter [, ... ] )
INHERIT parent_table
NO INHERIT parent_table
OF type_name
NOT OF
OWNER TO new_owner
-SET TABLESPACE new_tablespace
REPLICA IDENTITY {DEFAULT | USING INDEX index_name | FULL | NOTHING}
and table_constraint_using_index is:
@@ -447,6 +448,20 @@ ALTER TABLE [ IF EXISTS ] name
+SET {LOGGED | UNLOGGED}
+
+
+ This form changes the table persistence type from unlogged to permanent or
+ from unlogged to permanent (see ).
+
+
+ Changing the table persistence type acquires an ACCESS EXCLUSIVE lock
+ and rewrites the table contents and associated indexes into new disk files.
+
+
+
+
+
SET WITH OIDS
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index b1c411a..7f497c7 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -574,7 +574,8 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
heap_close(OldHeap, NoLock);
/* Create the transient table that will receive the re-ordered data */
- OIDNewHeap = make_new_heap(tableOid, tableSpace, false,
+ OIDNewHeap = make_new_heap(tableOid, tableSpace,
+ OldHeap->rd_rel->relpersistence,
AccessExclusiveLock);
/* Copy the heap data into the new table in the desired order */
@@ -601,7 +602,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
* data, then call finish_heap_swap to complete the operation.
*/
Oid
-make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
+make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
LOCKMODE lockmode)
{
TupleDesc OldHeapDesc;
@@ -613,7 +614,6 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
Datum reloptions;
bool isNull;
Oid namespaceid;
- char relpersistence;
OldHeap = heap_open(OIDOldHeap, lockmode);
OldHeapDesc = RelationGetDescr(OldHeap);
@@ -636,16 +636,10 @@
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Thu, Jul 17, 2014 at 8:02 PM, Andres Freund wrote: > > > That means should I "FlushRelationBuffers(rel)" before change the > > relpersistence? > > I don't think that'd help. I think what this means that you simply > cannot change the relpersistence of the old relation before the heap > swap is successful. So I guess it has to be something like (pseudocode): > > OIDNewHeap = make_new_heap(..); > newrel = heap_open(OIDNewHeap, AEL); > > /* > * Change the temporary relation to be unlogged/logged. We have to do > * that here so buffers for the new relfilenode will have the right > * persistency set while the original filenode's buffers won't get read > * in with the wrong (i.e. new) persistency setting. Otherwise a > * rollback after the rewrite would possibly result with buffers for the > * original filenode having the wrong persistency setting. > * > * NB: This relies on swap_relation_files() also swapping the > * persistency. That wouldn't work for pg_class, but that can't be > * unlogged anyway. > */ > AlterTableChangeCatalogToLoggedOrUnlogged(newrel); > FlushRelationBuffers(newrel); > /* copy heap data into newrel */ > finish_heap_swap(); > > And then in swap_relation_files() also copy the persistency. > > > That's the best I can come up right now at least. > Isn't better if we can set the relpersistence as an argument to "make_new_heap" ? I'm thinking to change the make_new_heap: From: make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp, LOCKMODE lockmode) To: make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence, LOCKMODE lockmode) That way we can create the new heap with the appropriate relpersistence, so in the swap_relation_files also copy the persistency, of course. And after copy the heap data to the new table (ATRewriteTable) change relpersistence of the OldHeap's indexes, because in the "finish_heap_swap" they'll be rebuild. Thoughts? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog sobre TI: http://fabriziomello.blogspot.com >> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Mon, Jul 21, 2014 at 9:51 AM, Andres Freund wrote: > > On 2014-07-16 20:45:15 -0300, Fabrízio de Royes Mello wrote: > > > The rewrite will read in the 'old' contents - but because it's done > > > after the pg_class.relpersistence is changed they'll all not be marked > > > as BM_PERMANENT in memory. Then the ALTER TABLE is rolled back, > > > including the relpersistence setting. Which will unfortunately leave > > > pages with the wrong persistency setting in memory, right? > > > > > > > That means should I "FlushRelationBuffers(rel)" before change the > > relpersistence? > > Did my explanation clarify the problem + possible solution sufficiently? > Yes, your explanation was enough. I didn't had time to working on it yet. But I hope working on it today or tomorrow at least. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog sobre TI: http://fabriziomello.blogspot.com >> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On 2014-07-16 20:45:15 -0300, Fabrízio de Royes Mello wrote: > > The rewrite will read in the 'old' contents - but because it's done > > after the pg_class.relpersistence is changed they'll all not be marked > > as BM_PERMANENT in memory. Then the ALTER TABLE is rolled back, > > including the relpersistence setting. Which will unfortunately leave > > pages with the wrong persistency setting in memory, right? > > > > That means should I "FlushRelationBuffers(rel)" before change the > relpersistence? Did my explanation clarify the problem + possible solution sufficiently? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Re: Fabrízio de Royes Mello 2014-07-16 > Anyway I think all is ok now. Is this ok for you? Hi Fabrízio, it's ok for me now, though Andres' concerns seem valid. > > > +SET TABLESPACE class="PARAMETER">new_tablespace > > > RESET ( class="PARAMETER">storage_parameter [, ... ] ) > > > INHERIT parent_table > > > NO INHERIT class="PARAMETER">parent_table > > > OF type_name > > > NOT OF > > > OWNER TO new_owner > > > -SET TABLESPACE class="PARAMETER">new_tablespace > > > > That should get a footnote in the final commit message. > > > > Sorry, I didn't understand what you meant. I meant to say that when this patch gets committed, the commit message might want to explain that while we are at editing this doc part, we moved SET TABLESPACE to the place where it really should be. > > I think we are almost there :) > > Yeah... thanks a lot for your help. Welcome. I'll look forward to use this in production :) Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On 2014-07-16 20:45:15 -0300, Fabrízio de Royes Mello wrote: > On Wed, Jul 16, 2014 at 7:26 PM, Andres Freund > wrote: > > > > On 2014-07-16 20:53:06 +0200, Andres Freund wrote: > > > On 2014-07-16 20:25:42 +0200, Andres Freund wrote: > > > > Hi, > > > > > > > > I quickly looked at this patch and I think there's major missing > pieces > > > > around buffer management and wal logging. > > > > > > > > a) Currently buffers that are in memory marked as > > > >permanent/non-permanent aren't forced out to disk/pruned from > cache, > > > >not even when they're dirty. > > > > b) When converting from a unlogged to a logged table the relation > needs > > > >to be fsynced. > > > > c) Currently a unlogged table changed into a logged one will be > > > >corrupted on a standby because its contents won't ever be WAL > logged. > > > > > > Forget that, didn't notice that you're setting tab->rewrite = true. > > > > So, while that danger luckily isn't there I think there's something > > similar. Consider: > > > > CREATE TABLE blub(...); > > INSERT INTO blub ...; > > > > BEGIN; > > ALTER TABLE blub SET UNLOGGED; > > ROLLBACK; > > > > The rewrite will read in the 'old' contents - but because it's done > > after the pg_class.relpersistence is changed they'll all not be marked > > as BM_PERMANENT in memory. Then the ALTER TABLE is rolled back, > > including the relpersistence setting. Which will unfortunately leave > > pages with the wrong persistency setting in memory, right? > > > > That means should I "FlushRelationBuffers(rel)" before change the > relpersistence? I don't think that'd help. I think what this means that you simply cannot change the relpersistence of the old relation before the heap swap is successful. So I guess it has to be something like (pseudocode): OIDNewHeap = make_new_heap(..); newrel = heap_open(OIDNewHeap, AEL); /* * Change the temporary relation to be unlogged/logged. We have to do * that here so buffers for the new relfilenode will have the right * persistency set while the original filenode's buffers won't get read * in with the wrong (i.e. new) persistency setting. Otherwise a * rollback after the rewrite would possibly result with buffers for the * original filenode having the wrong persistency setting. * * NB: This relies on swap_relation_files() also swapping the * persistency. That wouldn't work for pg_class, but that can't be * unlogged anyway. */ AlterTableChangeCatalogToLoggedOrUnlogged(newrel); FlushRelationBuffers(newrel); /* copy heap data into newrel */ finish_heap_swap(); And then in swap_relation_files() also copy the persistency. That's the best I can come up right now at least. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Wed, Jul 16, 2014 at 7:26 PM, Andres Freund wrote: > > On 2014-07-16 20:53:06 +0200, Andres Freund wrote: > > On 2014-07-16 20:25:42 +0200, Andres Freund wrote: > > > Hi, > > > > > > I quickly looked at this patch and I think there's major missing pieces > > > around buffer management and wal logging. > > > > > > a) Currently buffers that are in memory marked as > > >permanent/non-permanent aren't forced out to disk/pruned from cache, > > >not even when they're dirty. > > > b) When converting from a unlogged to a logged table the relation needs > > >to be fsynced. > > > c) Currently a unlogged table changed into a logged one will be > > >corrupted on a standby because its contents won't ever be WAL logged. > > > > Forget that, didn't notice that you're setting tab->rewrite = true. > > So, while that danger luckily isn't there I think there's something > similar. Consider: > > CREATE TABLE blub(...); > INSERT INTO blub ...; > > BEGIN; > ALTER TABLE blub SET UNLOGGED; > ROLLBACK; > > The rewrite will read in the 'old' contents - but because it's done > after the pg_class.relpersistence is changed they'll all not be marked > as BM_PERMANENT in memory. Then the ALTER TABLE is rolled back, > including the relpersistence setting. Which will unfortunately leave > pages with the wrong persistency setting in memory, right? > That means should I "FlushRelationBuffers(rel)" before change the relpersistence? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog sobre TI: http://fabriziomello.blogspot.com >> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On 2014-07-16 20:53:06 +0200, Andres Freund wrote: > On 2014-07-16 20:25:42 +0200, Andres Freund wrote: > > Hi, > > > > I quickly looked at this patch and I think there's major missing pieces > > around buffer management and wal logging. > > > > a) Currently buffers that are in memory marked as > >permanent/non-permanent aren't forced out to disk/pruned from cache, > >not even when they're dirty. > > b) When converting from a unlogged to a logged table the relation needs > >to be fsynced. > > c) Currently a unlogged table changed into a logged one will be > >corrupted on a standby because its contents won't ever be WAL logged. > > Forget that, didn't notice that you're setting tab->rewrite = true. So, while that danger luckily isn't there I think there's something similar. Consider: CREATE TABLE blub(...); INSERT INTO blub ...; BEGIN; ALTER TABLE blub SET UNLOGGED; ROLLBACK; The rewrite will read in the 'old' contents - but because it's done after the pg_class.relpersistence is changed they'll all not be marked as BM_PERMANENT in memory. Then the ALTER TABLE is rolled back, including the relpersistence setting. Which will unfortunately leave pages with the wrong persistency setting in memory, right? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Wed, Jul 16, 2014 at 3:53 PM, Andres Freund wrote: > > On 2014-07-16 20:25:42 +0200, Andres Freund wrote: > > Hi, > > > > I quickly looked at this patch and I think there's major missing pieces > > around buffer management and wal logging. > > > > a) Currently buffers that are in memory marked as > >permanent/non-permanent aren't forced out to disk/pruned from cache, > >not even when they're dirty. > > b) When converting from a unlogged to a logged table the relation needs > >to be fsynced. > > c) Currently a unlogged table changed into a logged one will be > >corrupted on a standby because its contents won't ever be WAL logged. > > Forget that, didn't notice that you're setting tab->rewrite = true. > :-) -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog sobre TI: http://fabriziomello.blogspot.com >> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On 2014-07-16 20:25:42 +0200, Andres Freund wrote: > Hi, > > I quickly looked at this patch and I think there's major missing pieces > around buffer management and wal logging. > > a) Currently buffers that are in memory marked as >permanent/non-permanent aren't forced out to disk/pruned from cache, >not even when they're dirty. > b) When converting from a unlogged to a logged table the relation needs >to be fsynced. > c) Currently a unlogged table changed into a logged one will be >corrupted on a standby because its contents won't ever be WAL logged. Forget that, didn't notice that you're setting tab->rewrite = true. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Hi, I quickly looked at this patch and I think there's major missing pieces around buffer management and wal logging. a) Currently buffers that are in memory marked as permanent/non-permanent aren't forced out to disk/pruned from cache, not even when they're dirty. b) When converting from a unlogged to a logged table the relation needs to be fsynced. c) Currently a unlogged table changed into a logged one will be corrupted on a standby because its contents won't ever be WAL logged. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Re: Fabrízio de Royes Mello 2014-07-15
> > What about the wqueue mechanism, though? Isn't that made exactly for
> > the kind of catalog updates you are doing?
> >
>
> Works, but this mechanism create a new entry in pg_class for toast, so it's
> a little different than use the cluster_rel that generate a new
> relfilenode. The important is both mechanisms create new datafiles.
Ok, I had thought that any catalog changes in AT should be queued
using this mechanism to be executed later by ATExecCmd(). The queueing
only seems to be used for the cases that recurse into child tables,
which we don't.
> Merged both to a single ATPrepSetLoggedOrUnlogged(Relation rel, bool
> toLogged);
Thanks.
> But they aren't duplicated... the first opens "con->confrelid" and the
> other opens "con->conrelid" according "toLogged" branch.
Oh sorry. I had looked for that, but still missed it.
> I removed because they are not so useful than I was thinking before.
> Actually they just bloated our test cases.
Nod.
> > I think the tests could also use a bit more comments, notably the
> > commands that are expected to fail. So far I haven't tried to read
> > them but trusted that they did the right thing. (Though with the
> > SELECTs removed, it's pretty readable now.)
> >
>
> Added some comments.
Thanks, looks nice now.
> +SET TABLESPACE class="PARAMETER">new_tablespace
> RESET ( storage_parameter
> [, ... ] )
> INHERIT parent_table
> NO INHERIT parent_table
> OF type_name
> NOT OF
> OWNER TO new_owner
> -SET TABLESPACE class="PARAMETER">new_tablespace
That should get a footnote in the final commit message.
> @@ -2857,6 +2860,8 @@ AlterTableGetLockLevel(List *cmds)
> case AT_AddIndexConstraint:
> case AT_ReplicaIdentity:
> case AT_SetNotNull:
> + case AT_SetLogged:
> + case AT_SetUnLogged:
> cmd_lockmode = AccessExclusiveLock;
> break;
>
> @@ -3248,6 +3253,13 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd
> *cmd,
> /* No command-specific prep needed */
> pass = AT_PASS_MISC;
> break;
> + case AT_SetLogged:
> + case AT_SetUnLogged:
> + ATSimplePermissions(rel, ATT_TABLE);
> + ATPrepSetLoggedOrUnlogged(rel, (cmd->subtype ==
> AT_SetLogged)); /* SET {LOGGED | UNLOGGED} */
> + pass = AT_PASS_MISC;
> + tab->rewrite = true;
> + break;
> default:/* oops */
> elog(ERROR, "unrecognized alter table type: %d",
>(int) cmd->subtype);
> @@ -3529,6 +3541,12 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
> Relation rel,
> case AT_GenericOptions:
> ATExecGenericOptions(rel, (List *) cmd->def);
> break;
> + case AT_SetLogged:
> + AlterTableSetLoggedOrUnlogged(rel, true);
> + break;
> + case AT_SetUnLogged:
> + AlterTableSetLoggedOrUnlogged(rel, false);
> + break;
> default:/* oops */
> elog(ERROR, "unrecognized alter table type: %d",
>(int) cmd->subtype);
I'd move all these next to the AT_DropCluster things like in all the
other lists.
>
> /*
> + * ALTER TABLE SET {LOGGED | UNLOGGED}
> + *
> + * Change the table persistence type from unlogged to permanent by
> + * rewriting the entire contents of the table and associated indexes
> + * into new disk files.
> + *
> + * The ATPrepSetLoggedOrUnlogged function check all precondictions
preconditions (without trailing space :)
> + * to perform the operation:
> + * - check if the target table is unlogged/permanente
permanent
> + * - check if not exists a foreign key to/from other unlogged/permanent
if no ... exists
> + */
> +static void
> +ATPrepSetLoggedOrUnlogged(Relation rel, bool toLogged)
> +{
> + charrelpersistence;
> +
> + relpersistence = (toLogged) ? RELPERSISTENCE_UNLOGGED :
> + RELPERSISTENCE_PERMANENT;
> +
> + /* check if is an unlogged or permanent relation */
> + if (rel->rd_rel->relpersistence != relpersistence)
> + ereport(ERROR,
> + (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
> + errmsg("table %s is not %s",
> + RelationGetRelationName(rel),
> + (toLogged) ? "unlogged" :
> "permanent")));
I think this will break translation of the error message; you will
likely need to provide two full strings. (I don
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Tue, Jul 15, 2014 at 3:04 PM, Christoph Berg wrote:
>
> Hi Fabrízio,
>
> thanks for the speedy new version.
>
You're welcome... If all happen ok I would like to have this patch commited
before the GSoC2014 ends.
> > > I've just tried some SET (UN)LOGGED operations with altering column
> > > types in the same operation, that works. But:
> > >
> > > Yes, you should use the existing table rewriting machinery, or at
> > > least clearly document (in comments) why it doesn't work for you.
> > >
> > > Also looking at ATController, there's a wqueue mechanism to queue
> > > catalog updates. You should probably use this, too, or again document
> > > why it doesn't work for you.
> > >
> >
> > This works... fixed!
>
> Thanks.
>
> What about the wqueue mechanism, though? Isn't that made exactly for
> the kind of catalog updates you are doing?
>
Works, but this mechanism create a new entry in pg_class for toast, so it's
a little different than use the cluster_rel that generate a new
relfilenode. The important is both mechanisms create new datafiles.
> > > You miss the symmetric case the other way round. When changing a table
> > > to unlogged, you need to make sure no other permanent table is
> > > referencing our table.
> > >
> >
> > Ohh yeas... sorry... you're completely correct... fixed!
>
> Can you move ATPrepSetUnLogged next to ATPrepSetLogged as both
> reference AlterTableSetLoggedCheckForeignConstraints now, and fix the
> comment on ATPrepSetUnLogged to also mention FKs? I'd also reiterate
> my proposal to merge these into one function, given they are now doing
> the same checks.
>
Merged both to a single ATPrepSetLoggedOrUnlogged(Relation rel, bool
toLogged);
> In AlterTableSetLoggedCheckForeignConstraints, move "relfk =
> relation_open..." out of the "if" because it's duplicated, and also for
> symmetry with relation_close().
>
But they aren't duplicated... the first opens "con->confrelid" and the
other opens "con->conrelid" according "toLogged" branch.
> The function needs comments. It is somewhat clear that
> self-referencing FKs will be skipped, but the two "if (toLogged)"
> branches should be annotated to say which case they are really about.
>
Fixed.
> Instead of just switching the argument order in the errmsg arguments,
> the error text should be updated to read "table %s is referenced
> by permanent table %s". At the moment the error text is wrong because
> the table logged1 is not yet unlogged:
>
> +ALTER TABLE logged1 SET UNLOGGED;
> +ERROR: table logged2 references unlogged table logged1
>
> -> table logged1 is referenced by permanent table logged2
>
Sorry... my mistake... fixed
> Compared to v3, you've removed a lot of "SELECT t.relpersistence..."
> from the regression tests, was that intended?
>
I removed because they are not so useful than I was thinking before.
Actually they just bloated our test cases.
> I think the tests could also use a bit more comments, notably the
> commands that are expected to fail. So far I haven't tried to read
> them but trusted that they did the right thing. (Though with the
> SELECTs removed, it's pretty readable now.)
>
Added some comments.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 69a1e14..2d131df 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -59,16 +59,17 @@ ALTER TABLE [ IF EXISTS ] name
ENABLE ALWAYS RULE rewrite_rule_name
CLUSTER ON index_name
SET WITHOUT CLUSTER
+SET {LOGGED | UNLOGGED}
SET WITH OIDS
SET WITHOUT OIDS
SET ( storage_parameter = value [, ... ] )
+SET TABLESPACE new_tablespace
RESET ( storage_parameter [, ... ] )
INHERIT parent_table
NO INHERIT parent_table
OF type_name
NOT OF
OWNER TO new_owner
-SET TABLESPACE new_tablespace
REPLICA IDENTITY {DEFAULT | USING INDEX index_name | FULL | NOTHING}
and table_constraint_using_index is:
@@ -447,6 +448,20 @@ ALTER TABLE [ IF EXISTS ] name
+SET {LOGGED | UNLOGGED}
+
+
+ This form changes the table persistence type from unlogged to permanent or
+ from unlogged to permanent (see ).
+
+
+ Changing the table persistence type acquires an ACCESS EXCLUSIVE lock
+ and rewrites the table contents and associated indexes into new disk files.
+
+
+
+
+
SET WITH OIDS
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 5dc4d18..184784c 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -384,6 +384,9 @@ static void change_owner_recurse_to_sequences(Oid relationOid,
Oid newOwnerId, LOCKMODE lockmode);
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Hi Fabrízio, thanks for the speedy new version. Re: Fabrízio de Royes Mello 2014-07-15 > > > > > + > > > > > + if (pass == AT_PASS_SET_LOGGED_UNLOGGED) > > > > > + > > > ATPostAlterSetLoggedUnlogged(RelationGetRelid(rel)); > > > > > > > > This must be done before relation_close() which releases all locks. > > > > You didn't address that. I'm not sure about it, but either way, this > > deserves a comment on the lock level necessary. > > > > Actually relation_close(rel, NoLock) don't release the locks. > > See src/backend/access/heap/heapam.c:1167 Oh there was one "not" too much for me, sorry. Anyway, this isn't relevant anymore. :) > > I've just tried some SET (UN)LOGGED operations with altering column > > types in the same operation, that works. But: > > > > Yes, you should use the existing table rewriting machinery, or at > > least clearly document (in comments) why it doesn't work for you. > > > > Also looking at ATController, there's a wqueue mechanism to queue > > catalog updates. You should probably use this, too, or again document > > why it doesn't work for you. > > > > This works... fixed! Thanks. What about the wqueue mechanism, though? Isn't that made exactly for the kind of catalog updates you are doing? > > You miss the symmetric case the other way round. When changing a table > > to unlogged, you need to make sure no other permanent table is > > referencing our table. > > > > Ohh yeas... sorry... you're completely correct... fixed! Can you move ATPrepSetUnLogged next to ATPrepSetLogged as both reference AlterTableSetLoggedCheckForeignConstraints now, and fix the comment on ATPrepSetUnLogged to also mention FKs? I'd also reiterate my proposal to merge these into one function, given they are now doing the same checks. In AlterTableSetLoggedCheckForeignConstraints, move "relfk = relation_open..." out of the "if" because it's duplicated, and also for symmetry with relation_close(). The function needs comments. It is somewhat clear that self-referencing FKs will be skipped, but the two "if (toLogged)" branches should be annotated to say which case they are really about. Instead of just switching the argument order in the errmsg arguments, the error text should be updated to read "table %s is referenced by permanent table %s". At the moment the error text is wrong because the table logged1 is not yet unlogged: +ALTER TABLE logged1 SET UNLOGGED; +ERROR: table logged2 references unlogged table logged1 -> table logged1 is referenced by permanent table logged2 > > > > The comment on heap_open() suggests that you could directly invoke > > > > relation_open() because you know this is a toast table, similarly for > > > > index_open(). (I can't say which is better style.) > > > > > > > > > > I don't know which is better style too... other opinions?? > > > > Both are used several times in tablecmds.c, so both are probably fine. > > (Didn't check the contexts, though.) > > > > Then we can leave that way. Is ok for you? Yes. It was just a minor nitpick anyway. Compared to v3, you've removed a lot of "SELECT t.relpersistence..." from the regression tests, was that intended? I think the tests could also use a bit more comments, notably the commands that are expected to fail. So far I haven't tried to read them but trusted that they did the right thing. (Though with the SELECTs removed, it's pretty readable now.) Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Mon, Jul 14, 2014 at 3:31 PM, Christoph Berg wrote:
>
> Oh I wasn't aware of the wiki page, I had just read the old thread.
> Thanks for the pointer.
>
:-)
Thanks again for your review!
> > > > diff --git a/doc/src/sgml/ref/alter_table.sgml
> > b/doc/src/sgml/ref/alter_table.sgml
> > > > index 69a1e14..424f2e9 100644
> > > > --- a/doc/src/sgml/ref/alter_table.sgml
> > > > +++ b/doc/src/sgml/ref/alter_table.sgml
> > > > @@ -58,6 +58,7 @@ ALTER TABLE [ IF EXISTS ] > class="PARAMETER">name
> > > > ENABLE REPLICA RULE > class="PARAMETER">rewrite_rule_name
> > > > ENABLE ALWAYS RULE > class="PARAMETER">rewrite_rule_name
> > > > CLUSTER ON index_name
> > > > +SET {LOGGED | UNLOGGED}
> > > > SET WITHOUT CLUSTER
> > > > SET WITH OIDS
> > > > SET WITHOUT OIDS
> > >
> > > This must not be between the two CLUSTER lines. I think the best spot
> > > would just be one line down, before SET WITH OIDS.
> >
> > Fixed.
>
> The (long) SET LOGGED paragraph is still between CLUSTER and SET
> WITHOUT CLUSTER.
>
Fixed.
> > > This grammar bug pops up consistently: This form *changes*...
> > >
> >
> > Fixed.
>
> Two more:
>
> + * The AlterTableChangeCatalogToLoggedOrUnlogged function perform the
> + * The AlterTableChangeIndexesToLoggedOrUnlogged function scan all
indexes
>
Fixed.
> > > > relation_close(rel, NoLock);
> > > > +
> > > > + if (pass == AT_PASS_SET_LOGGED_UNLOGGED)
> > > > +
> > ATPostAlterSetLoggedUnlogged(RelationGetRelid(rel));
> > >
> > > This must be done before relation_close() which releases all locks.
>
> You didn't address that. I'm not sure about it, but either way, this
> deserves a comment on the lock level necessary.
>
Actually relation_close(rel, NoLock) don't release the locks.
See src/backend/access/heap/heapam.c:1167
> > > Moreover, I think you can get rid of that extra PASS here.
> > > AT_PASS_ALTER_TYPE has its own pass because you can alter several
> > > columns in a single ALTER TABLE statement, but you can have only one
> > > SET (UN)LOGGED, so you can to the cluster_rel() directly in
> > > AlterTableSetLoggedOrUnlogged() (unless cluster_rel() is too intrusive
> > > and would interfere with other ALTER TABLE operations in this command,
> > > no idea).
> > >
> >
> > I had some troubles here so I decided to do in that way, but I confess
I'm
> > not comfortable with this implementation. Looking more carefully on
> > tablecmds.c code, at the ATController we have three phases and the
third is
> > 'scan/rewrite tables as needed' so my doubt is if can I use it instead
of
> > call 'cluster_rel'?
>
> I've just tried some SET (UN)LOGGED operations with altering column
> types in the same operation, that works. But:
>
> Yes, you should use the existing table rewriting machinery, or at
> least clearly document (in comments) why it doesn't work for you.
>
> Also looking at ATController, there's a wqueue mechanism to queue
> catalog updates. You should probably use this, too, or again document
> why it doesn't work for you.
>
This works... fixed!
> > > Here's the big gotcha: Just like SET LOGGED must check for outgoing
> > > FKs to unlogged tables, SET UNLOGGED must check for incoming FKs from
> > > permanent tables. This is missing.
> > >
> >
> > I don't think so... we can create an unlogged table with a FK referring
to
> > a regular table...
> > ... but is not possible create a FK from a regular table referring to an
> > unlogged table:
> > ... and a FK from an unlogged table referring other unlogged table
works:
> > So we must take carefull just when changing an unlogged table to a
regular
> > table.
> >
> > Am I correct or I miss something?
>
> You miss the symmetric case the other way round. When changing a table
> to unlogged, you need to make sure no other permanent table is
> referencing our table.
>
Ohh yeas... sorry... you're completely correct... fixed!
> > > > +AlterTableChangeCatalogToLoggedOrUnlogged(Relation rel, Relation
> > relrelation, bool toLogged)
>
> You are using "relrelation" and "relrel". I'd change all occurrences
> to "relrelation" because that's also used elsewhere.
>
Fixed.
> > > The comment on heap_open() suggests that you could directly invoke
> > > relation_open() because you know this is a toast table, similarly for
> > > index_open(). (I can't say which is better style.)
> > >
> >
> > I don't know which is better style too... other opinions??
>
> Both are used several times in tablecmds.c, so both are probably fine.
> (Didn't check the contexts, though.)
>
Then we can leave that way. Is ok for you?
Greetings,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 69a1e14..2d131df 100644
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Re: Fabrízio de Royes Mello 2014-07-12
> > ... that being the non-WAL-logging with wal_level=minimal, or more?
>
> This is the first of additional goals, but we have others. Please see [1].
Oh I wasn't aware of the wiki page, I had just read the old thread.
Thanks for the pointer.
> > > diff --git a/doc/src/sgml/ref/alter_table.sgml
> b/doc/src/sgml/ref/alter_table.sgml
> > > index 69a1e14..424f2e9 100644
> > > --- a/doc/src/sgml/ref/alter_table.sgml
> > > +++ b/doc/src/sgml/ref/alter_table.sgml
> > > @@ -58,6 +58,7 @@ ALTER TABLE [ IF EXISTS ] class="PARAMETER">name
> > > ENABLE REPLICA RULE class="PARAMETER">rewrite_rule_name
> > > ENABLE ALWAYS RULE class="PARAMETER">rewrite_rule_name
> > > CLUSTER ON index_name
> > > +SET {LOGGED | UNLOGGED}
> > > SET WITHOUT CLUSTER
> > > SET WITH OIDS
> > > SET WITHOUT OIDS
> >
> > This must not be between the two CLUSTER lines. I think the best spot
> > would just be one line down, before SET WITH OIDS.
>
> Fixed.
The (long) SET LOGGED paragraph is still between CLUSTER and SET
WITHOUT CLUSTER.
> > This grammar bug pops up consistently: This form *changes*...
> >
>
> Fixed.
Two more:
+ * The AlterTableChangeCatalogToLoggedOrUnlogged function perform the
+ * The AlterTableChangeIndexesToLoggedOrUnlogged function scan all indexes
> > This unnecessarily rewrites all the tabs, but see below.
> >
>
> I did that because the new constant AT_PASS_SET_LOGGED_UNLOGGED is larger
> than others.
Ah, ok then.
> > I'm wondering if you shouldn't make a single ATPrepSetLogged function
> > that takes and additional toLogged argument. Or alternatively get rid
> > of the if() by putting the code also into case AT_SetLogged.
> >
>
> Actually I started that way... with just one ATPrep function we have some
> additional complexity to check relpersistence, define the error message and
> to run AlterTableSetLoggedCheckForeignConstraints(rel) function. So to
> simplify the code I decided split in two small functions.
Nod.
> > > relation_close(rel, NoLock);
> > > +
> > > + if (pass == AT_PASS_SET_LOGGED_UNLOGGED)
> > > +
> ATPostAlterSetLoggedUnlogged(RelationGetRelid(rel));
> >
> > This must be done before relation_close() which releases all locks.
You didn't address that. I'm not sure about it, but either way, this
deserves a comment on the lock level necessary.
> > Moreover, I think you can get rid of that extra PASS here.
> > AT_PASS_ALTER_TYPE has its own pass because you can alter several
> > columns in a single ALTER TABLE statement, but you can have only one
> > SET (UN)LOGGED, so you can to the cluster_rel() directly in
> > AlterTableSetLoggedOrUnlogged() (unless cluster_rel() is too intrusive
> > and would interfere with other ALTER TABLE operations in this command,
> > no idea).
> >
>
> I had some troubles here so I decided to do in that way, but I confess I'm
> not comfortable with this implementation. Looking more carefully on
> tablecmds.c code, at the ATController we have three phases and the third is
> 'scan/rewrite tables as needed' so my doubt is if can I use it instead of
> call 'cluster_rel'?
I've just tried some SET (UN)LOGGED operations with altering column
types in the same operation, that works. But:
Yes, you should use the existing table rewriting machinery, or at
least clearly document (in comments) why it doesn't work for you.
Also looking at ATController, there's a wqueue mechanism to queue
catalog updates. You should probably use this, too, or again document
why it doesn't work for you.
> > Here's the big gotcha: Just like SET LOGGED must check for outgoing
> > FKs to unlogged tables, SET UNLOGGED must check for incoming FKs from
> > permanent tables. This is missing.
> >
>
> I don't think so... we can create an unlogged table with a FK referring to
> a regular table...
> ... but is not possible create a FK from a regular table referring to an
> unlogged table:
> ... and a FK from an unlogged table referring other unlogged table works:
> So we must take carefull just when changing an unlogged table to a regular
> table.
>
> Am I correct or I miss something?
You miss the symmetric case the other way round. When changing a table
to unlogged, you need to make sure no other permanent table is
referencing our table.
> > > +AlterTableChangeCatalogToLoggedOrUnlogged(Relation rel, Relation
> relrelation, bool toLogged)
You are using "relrelation" and "relrel". I'd change all occurrences
to "relrelation" because that's also used elsewhere.
> > The comment on heap_open() suggests that you could directly invoke
> > relation_open() because you know this is a toast table, similarly for
> > index_open(). (I can't say which is better style.)
> >
>
> I don't know which is better style too... other opinions??
Both are used several times in tablecmds.c, so both are probably fine.
(Didn't check the contexts, though.)
Christoph
--
c...@df7cb.de | http://www.df7cb.de/
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Hi,
here's my review for this patch.
Generally, the patch addresses a real need, tables often only turn
out to be desired as unlogged if there are performance problems in
practice, and the other way round changing an unlogged table to logged
is way more involved manually than it could be with this patch. So
yes, we want the feature.
I've tried the patch, and it works as desired, including tab
completion in psql. There are a few issues to be resolved, though.
Re: Fabrízio de Royes Mello 2014-06-26
> As part of GSoC2014 and with agreement of my mentor and reviewer (Stephen
> Frost) I've send a complement of the first patch to add the capability to
> change a regular table to unlogged.
(Fwiw, I believe this direction is the more interesting one.)
> With this patch we finish the main goals of the GSoC2014 and now we'll work
> in the additional goals.
... that being the non-WAL-logging with wal_level=minimal, or more?
> diff --git a/doc/src/sgml/ref/alter_table.sgml
> b/doc/src/sgml/ref/alter_table.sgml
> index 69a1e14..424f2e9 100644
> --- a/doc/src/sgml/ref/alter_table.sgml
> +++ b/doc/src/sgml/ref/alter_table.sgml
> @@ -58,6 +58,7 @@ ALTER TABLE [ IF EXISTS ] class="PARAMETER">name
> ENABLE REPLICA RULE class="PARAMETER">rewrite_rule_name
> ENABLE ALWAYS RULE class="PARAMETER">rewrite_rule_name
> CLUSTER ON index_name
> +SET {LOGGED | UNLOGGED}
> SET WITHOUT CLUSTER
> SET WITH OIDS
> SET WITHOUT OIDS
This must not be between the two CLUSTER lines. I think the best spot
would just be one line down, before SET WITH OIDS.
(While we are at it, SET TABLESPACE should probably be moved up to the
other SET options...)
> @@ -432,6 +433,20 @@ ALTER TABLE [ IF EXISTS ] class="PARAMETER">name
>
>
>
> +SET {LOGGED | UNLOGGED}
> +
> +
> + This form change the table persistence type from unlogged to permanent
> or
This grammar bug pops up consistently: This form *changes*...
There's trailing whitespace.
> + from unlogged to permanent by rewriting the entire contents of the
> table
> + and associated indexes into new disk files.
> +
> +
> + Changing the table persistence type acquires an ACCESS
> EXCLUSIVE lock.
> +
I'd rewrite that and add a reference:
... from unlogged to permanent (see ).
Changing the table persistence type acquires an ACCESS
EXCLUSIVE lock
and rewrites the table contents and associated indexes into new disk files.
> diff --git a/src/backend/commands/tablecmds.c
> b/src/backend/commands/tablecmds.c
> index 60d387a..9dfdca2 100644
> --- a/src/backend/commands/tablecmds.c
> +++ b/src/backend/commands/tablecmds.c
> @@ -125,18 +125,19 @@ static List *on_commits = NIL;
> * a pass determined by subcommand type.
> */
>
> -#define AT_PASS_UNSET-1 /* UNSET will
> cause ERROR */
> -#define AT_PASS_DROP 0 /* DROP (all flavors) */
> -#define AT_PASS_ALTER_TYPE 1 /* ALTER COLUMN TYPE */
> -#define AT_PASS_OLD_INDEX2 /* re-add existing
> indexes */
> -#define AT_PASS_OLD_CONSTR 3 /* re-add existing
> constraints */
> -#define AT_PASS_COL_ATTRS4 /* set other column
> attributes */
> +#define AT_PASS_UNSET-1 /*
> UNSET will cause ERROR */
> +#define AT_PASS_DROP 0 /* DROP (all
> flavors) */
> +#define AT_PASS_ALTER_TYPE 1 /* ALTER COLUMN
> TYPE */
> +#define AT_PASS_OLD_INDEX2 /* re-add
> existing indexes */
> +#define AT_PASS_OLD_CONSTR 3 /* re-add
> existing constraints */
> +#define AT_PASS_COL_ATTRS4 /* set other
> column attributes */
> /* We could support a RENAME COLUMN pass here, but not currently used */
> -#define AT_PASS_ADD_COL 5 /* ADD COLUMN */
> -#define AT_PASS_ADD_INDEX6 /* ADD indexes */
> -#define AT_PASS_ADD_CONSTR 7 /* ADD constraints,
> defaults */
> -#define AT_PASS_MISC 8 /* other stuff */
> -#define AT_NUM_PASSES9
> +#define AT_PASS_ADD_COL 5 /* ADD
> COLUMN */
> +#define AT_PASS_ADD_INDEX6 /* ADD indexes
> */
> +#define AT_PASS_ADD_CONSTR 7 /* ADD
> constraints, defaults */
> +#define AT_PASS_MISC 8 /* other stuff
> */
> +#define AT_PASS_SET_LOGGED_UNLOGGED 9 /* SET LOGGED and
> UNLOGGED */
> +#define AT_NUM_PASSES10
This unnecessarily rewrites all the tabs, but see below.
> @@ -376,6 +377,7 @@ static void ATPostAlterTypeCleanup(List **wque
Re: [HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
On Wed, Jun 11, 2014 at 1:19 PM, Fabrízio de Royes Mello <
fabriziome...@gmail.com> wrote:
> Hi all,
>
> As part of GSoC2014 I'm sending a patch to add the capability of change an
> unlogged table to logged [1].
>
>
Hi all,
As part of GSoC2014 and with agreement of my mentor and reviewer (Stephen
Frost) I've send a complement of the first patch to add the capability to
change a regular table to unlogged.
With this patch we finish the main goals of the GSoC2014 and now we'll work
in the additional goals.
Regards,
[1]
https://wiki.postgresql.org/wiki/Allow_an_unlogged_table_to_be_changed_to_logged_GSoC_2014
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 69a1e14..424f2e9 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -58,6 +58,7 @@ ALTER TABLE [ IF EXISTS ] name
ENABLE REPLICA RULE rewrite_rule_name
ENABLE ALWAYS RULE rewrite_rule_name
CLUSTER ON index_name
+SET {LOGGED | UNLOGGED}
SET WITHOUT CLUSTER
SET WITH OIDS
SET WITHOUT OIDS
@@ -432,6 +433,20 @@ ALTER TABLE [ IF EXISTS ] name
+SET {LOGGED | UNLOGGED}
+
+
+ This form change the table persistence type from unlogged to permanent or
+ from unlogged to permanent by rewriting the entire contents of the table
+ and associated indexes into new disk files.
+
+
+ Changing the table persistence type acquires an ACCESS EXCLUSIVE lock.
+
+
+
+
+
SET WITHOUT CLUSTER
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 60d387a..9dfdca2 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -125,18 +125,19 @@ static List *on_commits = NIL;
* a pass determined by subcommand type.
*/
-#define AT_PASS_UNSET -1 /* UNSET will cause ERROR */
-#define AT_PASS_DROP 0 /* DROP (all flavors) */
-#define AT_PASS_ALTER_TYPE 1 /* ALTER COLUMN TYPE */
-#define AT_PASS_OLD_INDEX 2 /* re-add existing indexes */
-#define AT_PASS_OLD_CONSTR 3 /* re-add existing constraints */
-#define AT_PASS_COL_ATTRS 4 /* set other column attributes */
+#define AT_PASS_UNSET-1 /* UNSET will cause ERROR */
+#define AT_PASS_DROP0 /* DROP (all flavors) */
+#define AT_PASS_ALTER_TYPE 1 /* ALTER COLUMN TYPE */
+#define AT_PASS_OLD_INDEX 2 /* re-add existing indexes */
+#define AT_PASS_OLD_CONSTR 3 /* re-add existing constraints */
+#define AT_PASS_COL_ATTRS 4 /* set other column attributes */
/* We could support a RENAME COLUMN pass here, but not currently used */
-#define AT_PASS_ADD_COL 5 /* ADD COLUMN */
-#define AT_PASS_ADD_INDEX 6 /* ADD indexes */
-#define AT_PASS_ADD_CONSTR 7 /* ADD constraints, defaults */
-#define AT_PASS_MISC 8 /* other stuff */
-#define AT_NUM_PASSES 9
+#define AT_PASS_ADD_COL5 /* ADD COLUMN */
+#define AT_PASS_ADD_INDEX 6 /* ADD indexes */
+#define AT_PASS_ADD_CONSTR 7 /* ADD constraints, defaults */
+#define AT_PASS_MISC8 /* other stuff */
+#define AT_PASS_SET_LOGGED_UNLOGGED 9 /* SET LOGGED and UNLOGGED */
+#define AT_NUM_PASSES10
typedef struct AlteredTableInfo
{
@@ -376,6 +377,7 @@ static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMOD
static void ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId,
char *cmd, List **wqueue, LOCKMODE lockmode,
bool rewrite);
+static void ATPostAlterSetLoggedUnlogged(Oid relid);
static void TryReuseIndex(Oid oldId, IndexStmt *stmt);
static void TryReuseForeignKey(Oid oldId, Constraint *con);
static void change_owner_fix_column_acls(Oid relationOid,
@@ -402,6 +404,13 @@ static void ATExecAddOf(Relation rel, const TypeName *ofTypename, LOCKMODE lockm
static void ATExecDropOf(Relation rel, LOCKMODE lockmode);
static void ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKMODE lockmode);
static void ATExecGenericOptions(Relation rel, List *options);
+static void ATPrepSetLogged(Relation rel);
+static void ATPrepSetUnLogged(Relation rel);
+static void ATExecSetLogged(Relation rel);
+static void ATExecSetUnLogged(Relation rel);
+
+static void AlterTableSetLoggedCheckForeignConstraints(Relation rel);
+static void AlterTableSetLoggedOrUnlogged(Relation rel, bool toLogged);
static void copy_relation_data(SMgrRelation rel, SMgrRelation dst,
ForkNumber forkNum, char relpersistence);
@@ -2854,6 +2863,8 @@ AlterTableGetLockLevel(List *cmds)
case AT_AddIndexConstraint:
case AT_ReplicaIdentity:
case AT_SetNotNull:
+ case AT_SetLogged:
+ case AT_SetUnLogged:
cmd_lockmode = AccessExclusiveLock;
break;
@@ -3245,6 +3256,15 @@ ATPrepCmd(List **wque
[HACKERS] [GSoC2014] Patch ALTER TABLE ... SET LOGGED
Hi all,
As part of GSoC2014 I'm sending a patch to add the capability of change an
unlogged table to logged [1].
I'll add it to the 9.5CF1.
Regards,
[1]
https://wiki.postgresql.org/wiki/Allow_an_unlogged_table_to_be_changed_to_logged_GSoC_2014
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 69a1e14..f822375 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -58,6 +58,7 @@ ALTER TABLE [ IF EXISTS ] name
ENABLE REPLICA RULE rewrite_rule_name
ENABLE ALWAYS RULE rewrite_rule_name
CLUSTER ON index_name
+SET LOGGED
SET WITHOUT CLUSTER
SET WITH OIDS
SET WITHOUT OIDS
@@ -432,6 +433,20 @@ ALTER TABLE [ IF EXISTS ] name
+SET LOGGED
+
+
+ This form change the table persistence type from unlogged to permanent by
+ rewriting the entire contents of the table and associated indexes into
+ new disk files.
+
+
+ Changing the table persistence type acquires an ACCESS EXCLUSIVE lock.
+
+
+
+
+
SET WITHOUT CLUSTER
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 341262b..f378f6a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -136,7 +136,8 @@ static List *on_commits = NIL;
#define AT_PASS_ADD_INDEX 6 /* ADD indexes */
#define AT_PASS_ADD_CONSTR 7 /* ADD constraints, defaults */
#define AT_PASS_MISC 8 /* other stuff */
-#define AT_NUM_PASSES 9
+#define AT_PASS_SET_LOGGED 9 /* SET LOGGED */
+#define AT_NUM_PASSES 10
typedef struct AlteredTableInfo
{
@@ -376,6 +377,7 @@ static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMOD
static void ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId,
char *cmd, List **wqueue, LOCKMODE lockmode,
bool rewrite);
+static void ATPostAlterSetLogged(Oid relid);
static void TryReuseIndex(Oid oldId, IndexStmt *stmt);
static void TryReuseForeignKey(Oid oldId, Constraint *con);
static void change_owner_fix_column_acls(Oid relationOid,
@@ -402,6 +404,8 @@ static void ATExecAddOf(Relation rel, const TypeName *ofTypename, LOCKMODE lockm
static void ATExecDropOf(Relation rel, LOCKMODE lockmode);
static void ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKMODE lockmode);
static void ATExecGenericOptions(Relation rel, List *options);
+static void ATPrepSetLogged(Relation rel);
+static void ATExecSetLogged(Relation rel);
static void copy_relation_data(SMgrRelation rel, SMgrRelation dst,
ForkNumber forkNum, char relpersistence);
@@ -2855,6 +2859,7 @@ AlterTableGetLockLevel(List *cmds)
case AT_AddIndexConstraint:
case AT_ReplicaIdentity:
case AT_SetNotNull:
+ case AT_SetLogged:
cmd_lockmode = AccessExclusiveLock;
break;
@@ -3246,6 +3251,11 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* No command-specific prep needed */
pass = AT_PASS_MISC;
break;
+ case AT_SetLogged:
+ ATSimplePermissions(rel, ATT_TABLE); /* SET LOGGED */
+ ATPrepSetLogged(rel);
+ pass = AT_PASS_SET_LOGGED;
+ break;
default:/* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
@@ -3308,6 +3318,9 @@ ATRewriteCatalogs(List **wqueue, LOCKMODE lockmode)
ATPostAlterTypeCleanup(wqueue, tab, lockmode);
relation_close(rel, NoLock);
+
+ if (pass == AT_PASS_SET_LOGGED)
+ATPostAlterSetLogged(RelationGetRelid(rel));
}
}
@@ -3527,6 +3540,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
case AT_GenericOptions:
ATExecGenericOptions(rel, (List *) cmd->def);
break;
+ case AT_SetLogged:
+ ATExecSetLogged(rel);
+ break;
default:/* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
@@ -10420,6 +10436,175 @@ ATExecGenericOptions(Relation rel, List *options)
}
/*
+ * ALTER TABLE SET LOGGED
+ *
+ * Change the table persistence type from unlogged to permanent by
+ * rewriting the entire contents of the table and associated indexes
+ * into new disk files.
+ *
+ * The ATPrepSetLogged function check all precondictions to perform
+ * the operation:
+ * - check if the target table is unlogged
+ * - check if not exists a foreign key to other unlogged table
+ */
+static void
+ATPrepSetLogged(Relation rel)
+{
+ Relation pg_constraint;
+ HeapTuple tuple;
+ SysScanDesc scan;
+ ScanKeyData skey[1];
+
+ /* check if is an unlogged relation */
+ if (rel->rd_rel->relpersistence != RELPERSISTENCE_UNLOGGED)
+ ereport(ERROR,
+(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("table %s is not unlogged",
+ RelationGetRelatio
