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 ] <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 | UNLOGGED}
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 | UNLOGGED}</literal></term>
+ <listitem>
+ <para>
+ 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.
+ </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 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_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 */
/* 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_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_PASS_SET_LOGGED_UNLOGGED 9 /* SET LOGGED and UNLOGGED */
+#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 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 **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);
+ if (cmd->subtype == AT_SetLogged)
+ ATPrepSetLogged(rel); /* SET LOGGED */
+ else
+ ATPrepSetUnLogged(rel); /* SET UNLOGGED */
+ pass = AT_PASS_SET_LOGGED_UNLOGGED;
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
@@ -3307,6 +3327,9 @@ ATRewriteCatalogs(List **wqueue, LOCKMODE lockmode)
ATPostAlterTypeCleanup(wqueue, tab, lockmode);
relation_close(rel, NoLock);
+
+ if (pass == AT_PASS_SET_LOGGED_UNLOGGED)
+ ATPostAlterSetLoggedUnlogged(RelationGetRelid(rel));
}
}
@@ -3526,6 +3549,12 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
case AT_GenericOptions:
ATExecGenericOptions(rel, (List *) cmd->def);
break;
+ case AT_SetLogged:
+ ATExecSetLogged(rel);
+ break;
+ case AT_SetUnLogged:
+ ATExecSetUnLogged(rel);
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
@@ -10419,6 +10448,226 @@ 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)
+{
+ /* 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))));
+
+ /* check fk constraints */
+ AlterTableSetLoggedCheckForeignConstraints(rel);
+}
+
+static void
+AlterTableSetLoggedCheckForeignConstraints(Relation rel)
+{
+ Relation pg_constraint;
+ HeapTuple tuple;
+ SysScanDesc scan;
+ ScanKeyData skey[1];
+
+ /*
+ * 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 UNLOGGED
+ *
+ * Change the table persistence type from permanent to unlogged by
+ * rewriting the entire contents of the table and associated indexes
+ * into new disk files.
+ *
+ * The ATPrepSetUnLogged function check all precondictions to perform
+ * the operation:
+ * - check if the target table is permanent
+ */
+static void
+ATPrepSetUnLogged(Relation rel)
+{
+ /* check if is an permanent relation */
+ if (rel->rd_rel->relpersistence != RELPERSISTENCE_PERMANENT)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("table %s is not permanent",
+ RelationGetRelationName(rel))));
+}
+
+/*
+ * The AlterTableChangeCatalogToLoggedOrUnlogged function perform the
+ * catalog changes, i.e. update pg_class.relpersistence to 'p' or 'u'
+ */
+static void
+AlterTableChangeCatalogToLoggedOrUnlogged(Relation rel, Relation relrelation, bool toLogged)
+{
+ 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 ==
+ ((toLogged) ? RELPERSISTENCE_UNLOGGED : RELPERSISTENCE_PERMANENT));
+
+ pg_class_form->relpersistence = toLogged ?
+ RELPERSISTENCE_PERMANENT : RELPERSISTENCE_UNLOGGED;
+
+ 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 execute the code to change a relation from
+ * a unlogged to persistence state
+ */
+static void
+ATExecSetLogged(Relation rel)
+{
+ AlterTableSetLoggedOrUnlogged(rel, true);
+}
+
+/*
+ * ALTER TABLE <name> SET UNLOGGED
+ *
+ * The ATExecSetUnLogged execute the code to change a relation from
+ * a persistence to unlogged state
+ */
+static void
+ATExecSetUnLogged(Relation rel)
+{
+ AlterTableSetLoggedOrUnlogged(rel, false);
+}
+
+/*
+ * The AlterTableSetLoggedOrUnlogged function contains the main logic
+ * of the operation, changing the catalog for main heap, toast and indexes
+ */
+static void
+AlterTableSetLoggedOrUnlogged(Relation rel, bool toLogged)
+{
+ 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 */
+ AlterTableChangeCatalogToLoggedOrUnlogged(rel, relrel, toLogged);
+
+ /* toast heap, if any */
+ if (OidIsValid(rel->rd_rel->reltoastrelid))
+ {
+ Relation toastrel;
+
+ toastrel = heap_open(rel->rd_rel->reltoastrelid, AccessShareLock);
+ AlterTableChangeCatalogToLoggedOrUnlogged(toastrel, relrel, toLogged);
+ 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);
+
+ AlterTableChangeCatalogToLoggedOrUnlogged(indxrel, relrel, toLogged);
+
+ index_close(indxrel, AccessShareLock);
+ }
+
+ systable_endscan(scan);
+ heap_close(indexRelation, AccessShareLock);
+
+ heap_close(relrel, RowExclusiveLock);
+}
+
+/*
+ * ALTER TABLE <name> SET { LOGGED | UNLOGGED }
+ *
+ * The ATPostAlterSetLoggedUnlogged function is called after all to
+ * guarantee that heap is closed to perform the cluster_rel
+ */
+static void
+ATPostAlterSetLoggedUnlogged(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 605c9b4..a784d73 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -566,7 +566,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
@@ -2201,6 +2201,20 @@ 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 TABLE <name> SET UNLOGGED */
+ | SET UNLOGGED
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetUnLogged;
+ $$ = (Node *)n;
+ }
| alter_generic_options
{
AlterTableCmd *n = makeNode(AlterTableCmd);
@@ -12967,6 +12981,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 be5c3c5..f2a2759 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1641,7 +1641,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", "UNLOGGED", NULL};
COMPLETE_WITH_LIST(list_TABLESET);
}
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ff126eb..dc9f8fa 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1331,7 +1331,9 @@ 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 */
+ AT_SetUnLogged /* SET UNLOGGED */
} 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 9b89e58..516627e 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -2426,3 +2426,60 @@ 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
+DROP TABLE unlogged3;
+DROP TABLE unlogged2;
+DROP TABLE unlogged1;
+-- set unlogged
+CREATE TABLE logged1(f1 SERIAL PRIMARY KEY);
+SELECT relname, relpersistence, oid = relfilenode AS original_relfilenode FROM pg_class WHERE relname ~ '^logged1' ORDER BY 1;
+ relname | relpersistence | original_relfilenode
+----------------+----------------+----------------------
+ logged1 | p | t
+ logged1_f1_seq | p | t
+ logged1_pkey | p | t
+(3 rows)
+
+CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); -- fk reference
+CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); -- self fk reference
+ALTER TABLE logged3 SET UNLOGGED;
+ALTER TABLE logged2 SET UNLOGGED;
+ALTER TABLE logged1 SET UNLOGGED;
+SELECT relname, relpersistence, oid = relfilenode AS original_relfilenode FROM pg_class WHERE relname ~ '^logged1' ORDER BY 1;
+ relname | relpersistence | original_relfilenode
+----------------+----------------+----------------------
+ logged1 | u | f
+ logged1_f1_seq | p | t
+ logged1_pkey | u | f
+(3 rows)
+
+ALTER TABLE logged1 SET UNLOGGED;
+ERROR: table logged1 is not permanent
+DROP TABLE logged3;
+DROP TABLE logged2;
+DROP TABLE logged1;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 22a2dd0..26f7375 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -1624,3 +1624,32 @@ 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;
+DROP TABLE unlogged3;
+DROP TABLE unlogged2;
+DROP TABLE unlogged1;
+
+-- set unlogged
+CREATE TABLE logged1(f1 SERIAL PRIMARY KEY);
+SELECT relname, relpersistence, oid = relfilenode AS original_relfilenode FROM pg_class WHERE relname ~ '^logged1' ORDER BY 1;
+CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); -- fk reference
+CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); -- self fk reference
+ALTER TABLE logged3 SET UNLOGGED;
+ALTER TABLE logged2 SET UNLOGGED;
+ALTER TABLE logged1 SET UNLOGGED;
+SELECT relname, relpersistence, oid = relfilenode AS original_relfilenode FROM pg_class WHERE relname ~ '^logged1' ORDER BY 1;
+ALTER TABLE logged1 SET UNLOGGED;
+DROP TABLE logged3;
+DROP TABLE logged2;
+DROP TABLE logged1;
+
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
