On Tue, Jul 15, 2014 at 3:04 PM, Christoph Berg <c...@df7cb.de> 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 ] <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 unlogged to permanent (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/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);
static void ATExecClusterOn(Relation rel, const char *indexName, LOCKMODE lockmode);
static void ATExecDropCluster(Relation rel, LOCKMODE lockmode);
+static void ATPrepSetLoggedOrUnlogged(Relation rel, bool toLogged);
+static void AlterTableSetLoggedUnloggedCheckForeignConstraints(Relation rel, bool toLogged);
+static void AlterTableSetLoggedOrUnlogged(Relation rel, bool toLogged);
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
char *tablespacename, LOCKMODE lockmode);
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode);
@@ -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);
@@ -10424,6 +10442,225 @@ 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 ATPrepSetLoggedOrUnlogged function check all precondictions
+ * to perform the operation:
+ * - check if the target table is unlogged/permanente
+ * - check if not exists a foreign key to/from other unlogged/permanent
+ * table
+ */
+static void
+ATPrepSetLoggedOrUnlogged(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("table %s is not %s",
+ RelationGetRelationName(rel),
+ (toLogged) ? "unlogged" : "permanent")));
+
+ /* check fk constraints */
+ AlterTableSetLoggedUnloggedCheckForeignConstraints(rel, toLogged);
+}
+
+/*
+ * AlterTableSetLoggedUnloggedCheckForeignConstraints: checks for Foreign Key
+ * constraints consistency when changing from unlogged to permanent or
+ * from permanent to unlogged.
+ *
+ * Throws an exception when:
+ *
+ * - if changing to permanent (toLogged = true) then checks if doesn't
+ * exists a foreign key to another unlogged table.
+ *
+ * - if changing to unlogged (toLogged = false) then checks if doesn't
+ * exists a foreign key from another permanent table.
+ *
+ * Self 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,
+ ((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 FK or check if exists a FK to an unlogged table */
+ 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 FK or check if exists a FK from a permanent table */
+ 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, bool toLogged)
+{
+ HeapTuple tuple;
+ Form_pg_class pg_class_form;
+ Relation rel;
+
+ /* open relation */
+ rel = relation_open(relid, AccessShareLock);
+
+ 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);
+ heap_close(rel, AccessShareLock);
+}
+
+/*
+ * The AlterTableChangeIndexesToLoggedOrUnlogged function scans all indexes
+ * of a relation to change the relpersistence of each one
+ */
+static void
+AlterTableChangeIndexesToLoggedOrUnlogged(Oid relid, Relation relrelation, bool toLogged)
+{
+ Relation indexRelation;
+ ScanKeyData skey;
+ SysScanDesc scan;
+ HeapTuple indexTuple;
+
+ /* 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, toLogged);
+ }
+
+ systable_endscan(scan);
+ heap_close(indexRelation, AccessShareLock);
+}
+
+/*
+ *
+ * ALTER TABLE <name> SET {LOGGED | UNLOGGED}
+ *
+ * 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)
+{
+ Relation relrelation;
+ Oid relid;
+
+ /* get relation's oid */
+ relid = RelationGetRelid(rel);
+
+ /* open pg_class to update relpersistence */
+ relrelation = heap_open(RelationRelationId, RowExclusiveLock);
+
+ /* main heap */
+ AlterTableChangeCatalogToLoggedOrUnlogged(relid, relrelation, toLogged);
+
+ /* indexes */
+ AlterTableChangeIndexesToLoggedOrUnlogged(relid, relrelation, toLogged);
+
+ /* toast heap, if any */
+ if (OidIsValid(rel->rd_rel->reltoastrelid))
+ {
+ /* toast */
+ AlterTableChangeCatalogToLoggedOrUnlogged(rel->rd_rel->reltoastrelid, relrelation, toLogged);
+
+ /* toast index */
+ AlterTableChangeIndexesToLoggedOrUnlogged(rel->rd_rel->reltoastrelid, relrelation, toLogged);
+ }
+
+ 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 24e60b7..56a42c3 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1643,7 +1643,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 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..f462548 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -2426,3 +2426,62 @@ 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, 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; -- skip self FK reference
+ALTER TABLE unlogged2 SET LOGGED; -- fails because exists a FK to an unlogged table
+ERROR: table unlogged2 references unlogged table unlogged1
+ALTER TABLE unlogged1 SET LOGGED;
+-- check relpersistence of an unlogged table after changed to permament
+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)
+
+DROP TABLE unlogged3;
+DROP TABLE unlogged2;
+DROP TABLE unlogged1;
+-- set unlogged
+CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
+-- check relpersistence of an permanent table
+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 logged1 SET UNLOGGED; -- fails because exists a FK from a permanent table
+ERROR: table logged2 is referenced by permanent table logged1
+ALTER TABLE logged3 SET UNLOGGED; -- skip self FK reference
+ALTER TABLE logged2 SET UNLOGGED;
+ALTER TABLE logged1 SET UNLOGGED;
+-- check relpersistence of a permanent table after changed to 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)
+
+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..3e30ca8 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -1624,3 +1624,35 @@ 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, 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; -- skip self FK reference
+ALTER TABLE unlogged2 SET LOGGED; -- fails because exists a FK to an unlogged table
+ALTER TABLE unlogged1 SET LOGGED;
+-- check relpersistence of an unlogged table after changed to permament
+SELECT relname, relpersistence, oid = relfilenode AS original_relfilenode FROM pg_class WHERE relname ~ '^unlogged1' ORDER BY 1;
+DROP TABLE unlogged3;
+DROP TABLE unlogged2;
+DROP TABLE unlogged1;
+
+-- set unlogged
+CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
+-- check relpersistence of an permanent table
+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 logged1 SET UNLOGGED; -- fails because exists a FK from a permanent table
+ALTER TABLE logged3 SET UNLOGGED; -- skip self FK reference
+ALTER TABLE logged2 SET UNLOGGED;
+ALTER TABLE logged1 SET UNLOGGED;
+-- check relpersistence of a permanent table after changed to unlogged
+SELECT relname, relpersistence, oid = relfilenode AS original_relfilenode FROM pg_class WHERE relname ~ '^logged1' ORDER BY 1;
+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
