On Wed, Aug 20, 2014 at 12:35 PM, Thom Brown <t...@linux.com> wrote:
>
> Hi Fabrizio,
>
> + This form changes the table persistence type from unlogged to
permanent or
> + from unlogged to permanent (see <xref
linkend="SQL-CREATETABLE-UNLOGGED">).
>
> 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 ] <replaceable class="PARAMETER">name</replaceable>
ENABLE ALWAYS RULE <replaceable class="PARAMETER">rewrite_rule_name</replaceable>
CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable>
SET WITHOUT CLUSTER
+ SET {LOGGED | UNLOGGED}
SET WITH OIDS
SET WITHOUT OIDS
SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
+ SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )
INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
OF <replaceable class="PARAMETER">type_name</replaceable>
NOT OF
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
- SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
REPLICA IDENTITY {DEFAULT | USING INDEX <replaceable class="PARAMETER">index_name</replaceable> | FULL | NOTHING}
<phrase>and <replaceable class="PARAMETER">table_constraint_using_index</replaceable> is:</phrase>
@@ -447,6 +448,20 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
</varlistentry>
<varlistentry>
+ <term><literal>SET {LOGGED | UNLOGGED}</literal></term>
+ <listitem>
+ <para>
+ This form changes the table persistence type from unlogged to permanent or
+ from permanent to unlogged (see <xref linkend="SQL-CREATETABLE-UNLOGGED">).
+ </para>
+ <para>
+ Changing the table persistence type acquires an <literal>ACCESS EXCLUSIVE</literal> lock
+ and rewrites the table contents and associated indexes into new disk files.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>SET WITH OIDS</literal></term>
<listitem>
<para>
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, and does diff. */
if (concurrent)
+ {
tableSpace = GetDefaultTablespace(RELPERSISTENCE_TEMP);
+ relpersistence = RELPERSISTENCE_TEMP;
+ }
else
+ {
tableSpace = matviewRel->rd_rel->reltablespace;
+ relpersistence = matviewRel->rd_rel->relpersistence;
+ }
owner = matviewRel->rd_rel->relowner;
@@ -244,7 +251,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
* it against access by any other process until commit (by which time it
* will be gone).
*/
- OIDNewHeap = make_new_heap(matviewOid, tableSpace, concurrent,
+ OIDNewHeap = make_new_heap(matviewOid, tableSpace, relpersistence,
ExclusiveLock);
LockRelationOid(OIDNewHeap, AccessExclusiveLock);
dest = CreateTransientRelDestReceiver(OIDNewHeap);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index c86b999..d4a1365 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -151,6 +151,8 @@ 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 toLoggedUnlogged; /* T if SET (UN)LOGGED is used */
+ char newrelpersistence; /* if toLoggedUnlogged is T then the new relpersistence */
/* Objects to rebuild after completing ALTER TYPE operations */
List *changedConstraintOids; /* OIDs of constraints to rebuild */
List *changedConstraintDefs; /* string definitions of same */
@@ -383,6 +385,10 @@ static void change_owner_recurse_to_sequences(Oid relationOid,
Oid newOwnerId, LOCKMODE lockmode);
static void ATExecClusterOn(Relation rel, const char *indexName, LOCKMODE lockmode);
static void ATExecDropCluster(Relation rel, LOCKMODE lockmode);
+static void ATPrepSetLoggedUnlogged(Relation rel, bool toLogged);
+static void AlterTableSetLoggedUnloggedCheckForeignConstraints(Relation rel, bool toLogged);
+static void AlterTableChangeCatalogToLoggedOrUnlogged(Oid relid, Relation relrelation, char relpersistence);
+static void AlterTableChangeIndexesToLoggedOrUnlogged(Oid relid, char relpersistence);
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
char *tablespacename, LOCKMODE lockmode);
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode);
@@ -2948,6 +2954,11 @@ AlterTableGetLockLevel(List *cmds)
cmd_lockmode = ShareUpdateExclusiveLock;
break;
+ case AT_SetLogged:
+ case AT_SetUnLogged:
+ cmd_lockmode = AccessExclusiveLock;
+ break;
+
case AT_ValidateConstraint: /* Uses MVCC in
* getConstraints() */
cmd_lockmode = ShareUpdateExclusiveLock;
@@ -3160,6 +3171,18 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* No command-specific prep needed */
pass = AT_PASS_MISC;
break;
+ case AT_SetLogged: /* SET LOGGED */
+ case AT_SetUnLogged: /* SET UNLOGGED */
+ ATSimplePermissions(rel, ATT_TABLE);
+ ATPrepSetLoggedUnlogged(rel, (cmd->subtype == AT_SetLogged));
+ tab->rewrite = true; /* force the rewrite of the relation */
+ tab->toLoggedUnlogged = true;
+ if (cmd->subtype == AT_SetLogged)
+ tab->newrelpersistence = RELPERSISTENCE_PERMANENT;
+ else
+ tab->newrelpersistence = RELPERSISTENCE_UNLOGGED;
+ pass = AT_PASS_MISC;
+ break;
case AT_AddOids: /* SET WITH OIDS */
ATSimplePermissions(rel, ATT_TABLE);
if (!rel->rd_rel->relhasoids || recursing)
@@ -3430,6 +3453,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
case AT_DropCluster: /* SET WITHOUT CLUSTER */
ATExecDropCluster(rel, lockmode);
break;
+ case AT_SetLogged: /* SET LOGGED */
+ case AT_SetUnLogged: /* SET UNLOGGED */
+ break;
case AT_AddOids: /* SET WITH OIDS */
/* Use the ADD COLUMN code, unless prep decided to do nothing */
if (cmd->def != NULL)
@@ -3631,8 +3657,21 @@ ATRewriteTables(List **wqueue, LOCKMODE lockmode)
heap_close(OldHeap, NoLock);
+ /*
+ * 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.
+ */
+
/* Create transient table that will receive the modified data */
- OIDNewHeap = make_new_heap(tab->relid, NewTableSpace, false,
+ OIDNewHeap = make_new_heap(tab->relid, NewTableSpace, tab->newrelpersistence,
lockmode);
/*
@@ -3643,6 +3682,12 @@ ATRewriteTables(List **wqueue, LOCKMODE lockmode)
ATRewriteTable(tab, OIDNewHeap, lockmode);
/*
+ * Change the relpersistence of the OldHeap's indexes before reindex
+ */
+ if (tab->toLoggedUnlogged)
+ AlterTableChangeIndexesToLoggedOrUnlogged(tab->relid, tab->newrelpersistence);
+
+ /*
* Swap the physical files of the old and new heaps, then rebuild
* indexes and discard the old heap. We can use RecentXmin for
* the table's new relfrozenxid because we rewrote all the tuples
@@ -4052,6 +4097,8 @@ ATGetQueueEntry(List **wqueue, Relation rel)
tab->relid = relid;
tab->relkind = rel->rd_rel->relkind;
tab->oldDesc = CreateTupleDescCopy(RelationGetDescr(rel));
+ tab->newrelpersistence = RELPERSISTENCE_PERMANENT;
+ tab->toLoggedUnlogged = false;
*wqueue = lappend(*wqueue, tab);
@@ -10430,6 +10477,194 @@ ATExecGenericOptions(Relation rel, List *options)
}
/*
+ * ALTER TABLE <name> 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 ATPrepSetLoggedUnlogged function check all preconditions
+ * to perform the operation:
+ * - check if the target table is unlogged/permanent
+ * - check if no foreign key exists to/from other unlogged/permanent
+ * table
+ */
+static void
+ATPrepSetLoggedUnlogged(Relation rel, bool toLogged)
+{
+ char relpersistence;
+
+ 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( (toLogged) ? "table %s is not unlogged" :
+ "table %s is unlogged",
+ RelationGetRelationName(rel))));
+
+ /* check fk constraints */
+ AlterTableSetLoggedUnloggedCheckForeignConstraints(rel, toLogged);
+}
+
+/*
+ * AlterTableSetLoggedUnloggedCheckForeignConstraints: checks foreign key
+ * constraints consistency when changing relation persistence.
+ *
+ * Throws exceptions:
+ *
+ * - when changing to permanent (toLogged = true) then checks if no
+ * foreign key to another unlogged table exists.
+ *
+ * - when changing to unlogged (toLogged = false) then checks if do
+ * foreign key from another permanent table exists.
+ *
+ * Self-referencing foreign keys are skipped from the check.
+ */
+static void
+AlterTableSetLoggedUnloggedCheckForeignConstraints(Relation rel, bool toLogged)
+{
+ Relation pg_constraint;
+ HeapTuple tuple;
+ SysScanDesc scan;
+ ScanKeyData skey[1];
+
+ /*
+ * Fetch the constraint tuple from pg_constraint.
+ */
+ pg_constraint = heap_open(ConstraintRelationId, AccessShareLock);
+
+ /* scans conrelid if toLogged is true else scans confreld */
+ ScanKeyInit(&skey[0],
+ ((toLogged) ? Anum_pg_constraint_conrelid : Anum_pg_constraint_confrelid),
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+
+ scan = systable_beginscan(pg_constraint,
+ /* don't use index to scan if changing to unlogged */
+ ((toLogged) ? ConstraintRelidIndexId : InvalidOid),
+ toLogged, 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;
+
+ if (toLogged)
+ {
+ relfk = relation_open(con->confrelid, AccessShareLock);
+
+ /*
+ * Skip if self-referencing foreign key or check if a foreign key
+ * to an unlogged table exists
+ */
+ 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))));
+ }
+ else
+ {
+ relfk = relation_open(con->conrelid, AccessShareLock);
+
+ /*
+ * Skip if self-referencing foreign key or check if a foreign key
+ * from a permanent table exists
+ */
+ if (RelationGetRelid(rel) != con->conrelid &&
+ relfk->rd_rel->relpersistence == RELPERSISTENCE_PERMANENT)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("table %s is referenced by permanent table %s",
+ RelationGetRelationName(relfk),
+ RelationGetRelationName(rel))));
+ }
+
+ relation_close(relfk, AccessShareLock);
+ }
+ }
+
+ systable_endscan(scan);
+
+ heap_close(pg_constraint, AccessShareLock);
+}
+
+/*
+ * The AlterTableChangeCatalogToLoggedOrUnlogged function performs the
+ * catalog changes, i.e. update pg_class.relpersistence to 'p' or 'u'
+ */
+static void
+AlterTableChangeCatalogToLoggedOrUnlogged(Oid relid, Relation relrelation, char relpersistence)
+{
+ HeapTuple tuple;
+ Form_pg_class pg_class_form;
+ Relation rel;
+
+ /* open relation */
+ rel = relation_open(relid, AccessExclusiveLock);
+
+ 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);
+ pg_class_form->relpersistence = relpersistence;
+ simple_heap_update(relrelation, &tuple->t_self, tuple);
+
+ /* keep catalog indexes current */
+ CatalogUpdateIndexes(relrelation, tuple);
+
+ heap_freetuple(tuple);
+ heap_close(rel, AccessExclusiveLock);
+}
+
+/*
+ * The AlterTableChangeIndexesToLoggedOrUnlogged function scans all indexes
+ * of a relation to change the relpersistence of each one
+ */
+static void
+AlterTableChangeIndexesToLoggedOrUnlogged(Oid relid, char relpersistence)
+{
+ Relation indexRelation;
+ Relation relrelation;
+ ScanKeyData skey;
+ SysScanDesc scan;
+ HeapTuple indexTuple;
+
+ /* open pg_class to update relpersistence */
+ relrelation = heap_open(RelationRelationId, RowExclusiveLock);
+
+ /* Prepare to scan pg_index for entries having indrelid = relid. */
+ 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);
+ AlterTableChangeCatalogToLoggedOrUnlogged(index->indexrelid, relrelation, relpersistence);
+ }
+
+ systable_endscan(scan);
+ heap_close(indexRelation, AccessShareLock);
+ heap_close(relrelation, RowExclusiveLock);
+}
+
+/*
* Execute ALTER TABLE SET SCHEMA
*/
Oid
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a113809..bc5913a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -577,7 +577,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
LABEL LANGUAGE LARGE_P LAST_P LATERAL_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
@@ -2048,6 +2048,20 @@ alter_table_cmd:
n->name = NULL;
$$ = (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 TABLE <name> ENABLE TRIGGER <trig> */
| ENABLE_P TRIGGER name
{
@@ -12992,6 +13006,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 b4f1856..021017d 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1646,7 +1646,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/commands/cluster.h b/src/include/commands/cluster.h
index 0ada3d6..f7730a9 100644
--- a/src/include/commands/cluster.h
+++ b/src/include/commands/cluster.h
@@ -25,7 +25,7 @@ extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid,
bool recheck, LOCKMODE lockmode);
extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal);
-extern Oid make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
+extern Oid make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
LOCKMODE lockmode);
extern void finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
bool is_system_catalog,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 8364bef..ca68590 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1307,6 +1307,8 @@ typedef enum AlterTableType
AT_ChangeOwner, /* change owner */
AT_ClusterOn, /* CLUSTER ON */
AT_DropCluster, /* SET WITHOUT CLUSTER */
+ AT_SetLogged, /* SET LOGGED */
+ AT_SetUnLogged, /* SET UNLOGGED */
AT_AddOids, /* SET WITH OIDS */
AT_AddOidsRecurse, /* internal to commands/tablecmds.c */
AT_DropOids, /* SET WITHOUT OIDS */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index b52e507..17888ad 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -230,6 +230,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..bb88c7d 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -2426,3 +2426,90 @@ 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, f2 TEXT);
+-- check relpersistence of an unlogged table
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
+UNION ALL
+SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
+UNION ALL
+SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1'
+ORDER BY relname;
+ relname | relkind | relpersistence
+------------------+---------+----------------
+ toast index | i | u
+ toast table | t | u
+ unlogged1 | r | u
+ unlogged1_f1_seq | S | p
+ unlogged1_pkey | i | u
+(5 rows)
+
+CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- foreign key
+CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key
+ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key
+ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists
+ERROR: table unlogged2 references unlogged table unlogged1
+ALTER TABLE unlogged1 SET LOGGED;
+-- check relpersistence of an unlogged table after changing to permament
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
+UNION ALL
+SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
+UNION ALL
+SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1'
+ORDER BY relname;
+ relname | relkind | relpersistence
+------------------+---------+----------------
+ toast index | i | p
+ toast table | t | p
+ unlogged1 | r | p
+ unlogged1_f1_seq | S | p
+ unlogged1_pkey | i | p
+(5 rows)
+
+DROP TABLE unlogged3;
+DROP TABLE unlogged2;
+DROP TABLE unlogged1;
+-- set unlogged
+CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
+-- check relpersistence of a permanent table
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1'
+UNION ALL
+SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
+UNION ALL
+SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^logged1'
+ORDER BY relname;
+ relname | relkind | relpersistence
+----------------+---------+----------------
+ logged1 | r | p
+ logged1_f1_seq | S | p
+ logged1_pkey | i | p
+ toast index | i | p
+ toast table | t | p
+(5 rows)
+
+CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); -- foreign key
+CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); -- self-referencing foreign key
+ALTER TABLE logged1 SET UNLOGGED; -- fails because a foreign key from a permanent table exists
+ERROR: table logged2 is referenced by permanent table logged1
+ALTER TABLE logged3 SET UNLOGGED; -- skip self-referencing foreign key
+ALTER TABLE logged2 SET UNLOGGED;
+ALTER TABLE logged1 SET UNLOGGED;
+-- check relpersistence of a permanent table after changing to unlogged
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1'
+UNION ALL
+SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
+UNION ALL
+SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^logged1'
+ORDER BY relname;
+ relname | relkind | relpersistence
+----------------+---------+----------------
+ logged1 | r | u
+ logged1_f1_seq | S | p
+ logged1_pkey | i | u
+ toast index | i | u
+ toast table | t | u
+(5 rows)
+
+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..27a8e26 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -1624,3 +1624,53 @@ 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, f2 TEXT);
+-- check relpersistence of an unlogged table
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
+UNION ALL
+SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
+UNION ALL
+SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1'
+ORDER BY relname;
+CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- foreign key
+CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key
+ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key
+ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists
+ALTER TABLE unlogged1 SET LOGGED;
+-- check relpersistence of an unlogged table after changing to permament
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
+UNION ALL
+SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
+UNION ALL
+SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1'
+ORDER BY relname;
+DROP TABLE unlogged3;
+DROP TABLE unlogged2;
+DROP TABLE unlogged1;
+-- set unlogged
+CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
+-- check relpersistence of a permanent table
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1'
+UNION ALL
+SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
+UNION ALL
+SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^logged1'
+ORDER BY relname;
+CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); -- foreign key
+CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); -- self-referencing foreign key
+ALTER TABLE logged1 SET UNLOGGED; -- fails because a foreign key from a permanent table exists
+ALTER TABLE logged3 SET UNLOGGED; -- skip self-referencing foreign key
+ALTER TABLE logged2 SET UNLOGGED;
+ALTER TABLE logged1 SET UNLOGGED;
+-- check relpersistence of a permanent table after changing to unlogged
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1'
+UNION ALL
+SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
+UNION ALL
+SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^logged1'
+ORDER BY relname;
+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
