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 ] <replaceable class="PARAMETER">name</replaceable>
ENABLE REPLICA RULE <replaceable class="PARAMETER">rewrite_rule_name</replaceable>
ENABLE ALWAYS RULE <replaceable class="PARAMETER">rewrite_rule_name</replaceable>
CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable>
+ SET LOGGED
SET WITHOUT CLUSTER
SET WITH OIDS
SET WITHOUT OIDS
@@ -432,6 +433,20 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
</varlistentry>
<varlistentry>
+ <term><literal>SET LOGGED</literal></term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ <para>
+ Changing the table persistence type acquires an <literal>ACCESS EXCLUSIVE</literal> lock.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>SET WITHOUT CLUSTER</literal></term>
<listitem>
<para>
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 <name> 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",
+ RelationGetRelationName(rel))));
+
+ /*
+ * Fetch the constraint tuple from pg_constraint.
+ */
+ pg_constraint = heap_open(ConstraintRelationId, AccessShareLock);
+
+ ScanKeyInit(&skey[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+
+ scan = systable_beginscan(pg_constraint, ConstraintRelidIndexId, true,
+ NULL, 1, skey);
+
+ while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ {
+ Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple);
+ if (con->contype == CONSTRAINT_FOREIGN)
+ {
+ Relation relfk = relation_open(con->confrelid, AccessShareLock);
+
+ if (RelationGetRelid(rel) != con->confrelid &&
+ relfk->rd_rel->relpersistence != RELPERSISTENCE_PERMANENT)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("table %s references unlogged table %s",
+ RelationGetRelationName(rel),
+ RelationGetRelationName(relfk))));
+
+ relation_close(relfk, AccessShareLock);
+ }
+ }
+
+ systable_endscan(scan);
+
+ heap_close(pg_constraint, AccessShareLock);
+}
+
+/*
+ * ALTER TABLE <name> SET LOGGED
+ *
+ * The ATUpdateToLogged function perform the catalog changes,
+ * i.e. update pg_class.relpersistence to 'p'
+ */
+static void
+ATUpdateToLogged(Relation rel, Relation relrelation)
+{
+ HeapTuple tuple;
+ Form_pg_class pg_class_form;
+
+ tuple = SearchSysCacheCopy1(RELOID,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u",
+ RelationGetRelid(rel));
+
+ pg_class_form = (Form_pg_class) GETSTRUCT(tuple);
+
+ Assert(pg_class_form->relpersistence == RELPERSISTENCE_UNLOGGED);
+
+ pg_class_form->relpersistence = RELPERSISTENCE_PERMANENT;
+
+ simple_heap_update(relrelation, &tuple->t_self, tuple);
+
+ /* keep catalog indexes current */
+ CatalogUpdateIndexes(relrelation, tuple);
+
+ heap_freetuple(tuple);
+}
+
+/*
+ * ALTER TABLE <name> SET LOGGED
+ *
+ * The ATExecSetLogged function contains the main logic of the operation,
+ * changing the catalog for main heap, toast and indexes
+ */
+static void
+ATExecSetLogged(Relation rel)
+{
+ Oid relid;
+ Relation indexRelation;
+ ScanKeyData skey;
+ SysScanDesc scan;
+ HeapTuple indexTuple;
+ Relation relrel;
+
+ relid = RelationGetRelid(rel);
+
+ /* open pg_class to update relpersistence */
+ relrel = heap_open(RelationRelationId, RowExclusiveLock);
+
+ /* main heap */
+ ATUpdateToLogged(rel, relrel);
+
+ /* toast heap, if any */
+ if (OidIsValid(rel->rd_rel->reltoastrelid))
+ {
+ Relation toastrel;
+
+ toastrel = heap_open(rel->rd_rel->reltoastrelid, AccessShareLock);
+ ATUpdateToLogged(toastrel, relrel);
+ heap_close(toastrel, AccessShareLock);
+ }
+
+ /* Prepare to scan pg_index for entries having indrelid = this rel. */
+ indexRelation = heap_open(IndexRelationId, AccessShareLock);
+ ScanKeyInit(&skey,
+ Anum_pg_index_indrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ relid);
+
+ scan = systable_beginscan(indexRelation, IndexIndrelidIndexId, true,
+ NULL, 1, &skey);
+
+ while (HeapTupleIsValid(indexTuple = systable_getnext(scan)))
+ {
+ Form_pg_index index = (Form_pg_index) GETSTRUCT(indexTuple);
+ Relation indxrel = index_open(index->indexrelid, AccessShareLock);
+
+ ATUpdateToLogged(indxrel, relrel);
+
+ index_close(indxrel, AccessShareLock);
+ }
+
+ systable_endscan(scan);
+ heap_close(indexRelation, AccessShareLock);
+
+ heap_close(relrel, RowExclusiveLock);
+}
+
+/*
+ * ALTER TABLE <name> SET LOGGED
+ *
+ * The ATPostAlterSetLogged function is called after all to
+ * guarantee that heap is closed to perform the cluster_rel
+ */
+static void
+ATPostAlterSetLogged(Oid relid)
+{
+ /* rebuild the relation using CLUSTER algorithm */
+ cluster_rel(relid, InvalidOid, false, false);
+}
+
+/*
* Execute ALTER TABLE SET SCHEMA
*/
Oid
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7b9895d..7b8c2f5 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -563,7 +563,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
LABEL LANGUAGE LARGE_P LAST_P LATERAL_P LC_COLLATE_P LC_CTYPE_P
LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL
- LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P
+ LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOGGED
MAPPING MATCH MATERIALIZED MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
@@ -2198,6 +2198,13 @@ alter_table_cmd:
n->def = $3;
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> SET LOGGED */
+ | SET LOGGED
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetLogged;
+ $$ = (Node *)n;
+ }
| alter_generic_options
{
AlterTableCmd *n = makeNode(AlterTableCmd);
@@ -12921,6 +12928,7 @@ unreserved_keyword:
| LOCAL
| LOCATION
| LOCK_P
+ | LOGGED
| MAPPING
| MATCH
| MATERIALIZED
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 3bb727f..ac4ff60 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1575,7 +1575,7 @@ psql_completion(const char *text, int start, int end)
pg_strcasecmp(prev_wd, "SET") == 0)
{
static const char *const list_TABLESET[] =
- {"(", "WITHOUT", "TABLESPACE", "SCHEMA", NULL};
+ {"(", "WITHOUT", "TABLESPACE", "SCHEMA", "LOGGED", NULL};
COMPLETE_WITH_LIST(list_TABLESET);
}
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 7e560a1..a97a0f8 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1317,7 +1317,8 @@ typedef enum AlterTableType
AT_AddOf, /* OF <type_name> */
AT_DropOf, /* NOT OF */
AT_ReplicaIdentity, /* REPLICA IDENTITY */
- AT_GenericOptions /* OPTIONS (...) */
+ AT_GenericOptions, /* OPTIONS (...) */
+ AT_SetLogged /* SET LOGGED */
} AlterTableType;
typedef struct ReplicaIdentityStmt
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 61fae22..b62ce0e 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -231,6 +231,7 @@ PG_KEYWORD("localtime", LOCALTIME, RESERVED_KEYWORD)
PG_KEYWORD("localtimestamp", LOCALTIMESTAMP, RESERVED_KEYWORD)
PG_KEYWORD("location", LOCATION, UNRESERVED_KEYWORD)
PG_KEYWORD("lock", LOCK_P, UNRESERVED_KEYWORD)
+PG_KEYWORD("logged", LOGGED, UNRESERVED_KEYWORD)
PG_KEYWORD("mapping", MAPPING, UNRESERVED_KEYWORD)
PG_KEYWORD("match", MATCH, UNRESERVED_KEYWORD)
PG_KEYWORD("materialized", MATERIALIZED, UNRESERVED_KEYWORD)
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index a182176..696295e 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -2421,3 +2421,29 @@ TRUNCATE old_system_table;
ALTER TABLE old_system_table DROP CONSTRAINT new_system_table_pkey;
ALTER TABLE old_system_table DROP COLUMN othercol;
DROP TABLE old_system_table;
+-- set logged
+CREATE UNLOGGED TABLE unlogged1(f1 SERIAL PRIMARY KEY);
+SELECT relname, relpersistence, oid = relfilenode AS original_relfilenode FROM pg_class WHERE relname ~ '^unlogged1' ORDER BY 1;
+ relname | relpersistence | original_relfilenode
+------------------+----------------+----------------------
+ unlogged1 | u | t
+ unlogged1_f1_seq | p | t
+ unlogged1_pkey | u | t
+(3 rows)
+
+CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- fk reference
+CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self fk reference
+ALTER TABLE unlogged3 SET LOGGED;
+ALTER TABLE unlogged2 SET LOGGED;
+ERROR: table unlogged2 references unlogged table unlogged1
+ALTER TABLE unlogged1 SET LOGGED;
+SELECT relname, relpersistence, oid = relfilenode AS original_relfilenode FROM pg_class WHERE relname ~ '^unlogged1' ORDER BY 1;
+ relname | relpersistence | original_relfilenode
+------------------+----------------+----------------------
+ unlogged1 | p | f
+ unlogged1_f1_seq | p | t
+ unlogged1_pkey | p | f
+(3 rows)
+
+ALTER TABLE unlogged1 SET LOGGED;
+ERROR: table unlogged1 is not unlogged
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 3f641f9..1294fa0 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -1618,3 +1618,14 @@ TRUNCATE old_system_table;
ALTER TABLE old_system_table DROP CONSTRAINT new_system_table_pkey;
ALTER TABLE old_system_table DROP COLUMN othercol;
DROP TABLE old_system_table;
+
+-- set logged
+CREATE UNLOGGED TABLE unlogged1(f1 SERIAL PRIMARY KEY);
+SELECT relname, relpersistence, oid = relfilenode AS original_relfilenode FROM pg_class WHERE relname ~ '^unlogged1' ORDER BY 1;
+CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- fk reference
+CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self fk reference
+ALTER TABLE unlogged3 SET LOGGED;
+ALTER TABLE unlogged2 SET LOGGED;
+ALTER TABLE unlogged1 SET LOGGED;
+SELECT relname, relpersistence, oid = relfilenode AS original_relfilenode FROM pg_class WHERE relname ~ '^unlogged1' ORDER BY 1;
+ALTER TABLE unlogged1 SET LOGGED;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
