On Mon, Jul 14, 2014 at 3:31 PM, Christoph Berg <[email protected]> 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 ] <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
> > >
> > > 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
--- 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 60d387a..7be5cfa 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -384,6 +384,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 ATPrepSetLogged(Relation rel);
+static void ATPrepSetUnLogged(Relation rel);
+static void AlterTableSetLoggedCheckForeignConstraints(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);
@@ -2854,6 +2858,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 +3251,16 @@ 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_MISC;
+ tab->rewrite = true;
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
@@ -3526,6 +3542,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);
@@ -10419,6 +10441,225 @@ 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, true);
+}
+
+static void
+AlterTableSetLoggedCheckForeignConstraints(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);
+
+ 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);
+
+ 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);
+
+ if (RelationGetRelid(rel) != con->conrelid &&
+ relfk->rd_rel->relpersistence == RELPERSISTENCE_PERMANENT)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("table %s references unlogged table %s",
+ RelationGetRelationName(relfk),
+ RelationGetRelationName(rel))));
+ }
+
+ 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))));
+
+ /* check incoming fk constraints */
+ AlterTableSetLoggedCheckForeignConstraints(rel, false);
+}
+
+/*
+ * 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..859a615 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -2426,3 +2426,58 @@ 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);
+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)
+
+DROP TABLE unlogged3;
+DROP TABLE unlogged2;
+DROP TABLE unlogged1;
+-- set unlogged
+CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
+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;
+ERROR: table logged2 references unlogged table logged1
+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)
+
+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..2775d6a 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -1624,3 +1624,31 @@ 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);
+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;
+DROP TABLE unlogged3;
+DROP TABLE unlogged2;
+DROP TABLE unlogged1;
+
+-- set unlogged
+CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
+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;
+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;
+DROP TABLE logged3;
+DROP TABLE logged2;
+DROP TABLE logged1;
+
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
