postgres
diff --git a/‎src/backend/catalog/pg_depend.c
Lines changed: 25 additions & 8 deletions b/‎src/backend/catalog/pg_depend.c
Lines changed: 25 additions & 8 deletions
diff --git a/‎src/backend/commands/tablecmds.c
Lines changed: 2 additions & 2 deletions b/‎src/backend/commands/tablecmds.c
Lines changed: 2 additions & 2 deletions
diff --git a/‎src/backend/parser/parse_utilcmd.c
Lines changed: 5 additions & 7 deletions b/‎src/backend/parser/parse_utilcmd.c
Lines changed: 5 additions & 7 deletions
diff --git a/‎src/backend/rewrite/rewriteHandler.c
Lines changed: 1 addition & 1 deletion b/‎src/backend/rewrite/rewriteHandler.c
Lines changed: 1 addition & 1 deletion
diff --git a/‎src/include/catalog/dependency.h
Lines changed: 2 additions & 2 deletions b/‎src/include/catalog/dependency.h
Lines changed: 2 additions & 2 deletions
diff --git a/‎src/test/regress/expected/identity.out
Lines changed: 5 additions & 0 deletions b/‎src/test/regress/expected/identity.out
Lines changed: 5 additions & 0 deletions
diff --git a/‎src/test/regress/sql/identity.sql
Lines changed: 8 additions & 0 deletions b/‎src/test/regress/sql/identity.sql
Lines changed: 8 additions & 0 deletions
@@ -705,10 +705,11 @@ sequenceIsOwned(Oid seqId, char deptype, Oid *tableId, int32 *colId)
 
 /*
  * Collect a list of OIDs of all sequences owned by the specified relation,
- * and column if specified.
+ * and column if specified.  If deptype is not zero, then only find sequences
+ * with the specified dependency type.
  */
-List *
-getOwnedSequences(Oid relid, AttrNumber attnum)
+static List *
+getOwnedSequences_internal(Oid relid, AttrNumber attnum, char deptype)
 {
 	List	   *result = NIL;
 	Relation	depRel;
@@ -750,7 +751,8 @@ getOwnedSequences(Oid relid, AttrNumber attnum)
 			(deprec->deptype == DEPENDENCY_AUTO || deprec->deptype == DEPENDENCY_INTERNAL) &&
 			get_rel_relkind(deprec->objid) == RELKIND_SEQUENCE)
 		{
-			result = lappend_oid(result, deprec->objid);
+			if (!deptype || deprec->deptype == deptype)
+				result = lappend_oid(result, deprec->objid);
 		}
 	}
 
@@ -762,17 +764,32 @@ getOwnedSequences(Oid relid, AttrNumber attnum)
 }
 
 /*
- * Get owned sequence, error if not exactly one.
+ * Collect a list of OIDs of all sequences owned (identity or serial) by the
+ * specified relation.
+ */
+List *
+getOwnedSequences(Oid relid)
+{
+	return getOwnedSequences_internal(relid, 0, 0);
+}
+
+/*
+ * Get owned identity sequence, error if not exactly one.
  */
 Oid
-getOwnedSequence(Oid relid, AttrNumber attnum)
+getIdentitySequence(Oid relid, AttrNumber attnum, bool missing_ok)
 {
-	List	   *seqlist = getOwnedSequences(relid, attnum);
+	List	   *seqlist = getOwnedSequences_internal(relid, attnum, DEPENDENCY_INTERNAL);
 
 	if (list_length(seqlist) > 1)
 		elog(ERROR, "more than one owned sequence found");
 	else if (list_length(seqlist) < 1)
-		elog(ERROR, "no owned sequence found");
+	{
+		if (missing_ok)
+			return InvalidOid;
+		else
+			elog(ERROR, "no owned sequence found");
+	}
 
 	return linitial_oid(seqlist);
 }
 
@@ -1689,7 +1689,7 @@ ExecuteTruncateGuts(List *explicit_rels, List *relids, List *relids_logged,
 		foreach(cell, rels)
 		{
 			Relation	rel = (Relation) lfirst(cell);
-			List	   *seqlist = getOwnedSequences(RelationGetRelid(rel), 0);
+			List	   *seqlist = getOwnedSequences(RelationGetRelid(rel));
 			ListCell   *seqcell;
 
 			foreach(seqcell, seqlist)
@@ -6635,7 +6635,7 @@ ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE
 	table_close(attrelation, RowExclusiveLock);
 
 	/* drop the internal sequence */
-	seqid = getOwnedSequence(RelationGetRelid(rel), attnum);
+	seqid = getIdentitySequence(RelationGetRelid(rel), attnum, false);
 	deleteDependencyRecordsForClass(RelationRelationId, seqid,
 									RelationRelationId, DEPENDENCY_INTERNAL);
 	CommandCounterIncrement();
 
@@ -1083,7 +1083,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
 			 * find sequence owned by old column; extract sequence parameters;
 			 * build new create sequence command
 			 */
-			seq_relid = getOwnedSequence(RelationGetRelid(relation), attribute->attnum);
+			seq_relid = getIdentitySequence(RelationGetRelid(relation), attribute->attnum, false);
 			seq_options = sequence_options(seq_relid);
 			generateSerialExtraStmts(cxt, def,
 									 InvalidOid, seq_options, true,
@@ -3165,7 +3165,7 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
 					if (attnum != InvalidAttrNumber &&
 						TupleDescAttr(tupdesc, attnum - 1)->attidentity)
 					{
-						Oid			seq_relid = getOwnedSequence(relid, attnum);
+						Oid			seq_relid = getIdentitySequence(relid, attnum, false);
 						Oid			typeOid = typenameTypeId(pstate, def->typeName);
 						AlterSeqStmt *altseqstmt = makeNode(AlterSeqStmt);
 
@@ -3216,7 +3216,6 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
 					ListCell   *lc;
 					List	   *newseqopts = NIL;
 					List	   *newdef = NIL;
-					List	   *seqlist;
 					AttrNumber	attnum;
 
 					/*
@@ -3237,14 +3236,13 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
 
 					if (attnum)
 					{
-						seqlist = getOwnedSequences(relid, attnum);
-						if (seqlist)
+						Oid			seq_relid = getIdentitySequence(relid, attnum, true);
+
+						if (seq_relid)
 						{
 							AlterSeqStmt *seqstmt;
-							Oid			seq_relid;
 
 							seqstmt = makeNode(AlterSeqStmt);
-							seq_relid = linitial_oid(seqlist);
 							seqstmt->sequence = makeRangeVar(get_namespace_name(get_rel_namespace(seq_relid)),
 															 get_rel_name(seq_relid), -1);
 							seqstmt->options = newseqopts;
 
@@ -1130,7 +1130,7 @@ build_column_default(Relation rel, int attrno)
 	{
 		NextValueExpr *nve = makeNode(NextValueExpr);
 
-		nve->seqid = getOwnedSequence(RelationGetRelid(rel), attrno);
+		nve->seqid = getIdentitySequence(RelationGetRelid(rel), attrno, false);
 		nve->typeId = att_tup->atttypid;
 
 		return (Node *) nve;
 
@@ -209,8 +209,8 @@ extern long changeDependenciesOn(Oid refClassId, Oid oldRefObjectId,
 extern Oid	getExtensionOfObject(Oid classId, Oid objectId);
 
 extern bool sequenceIsOwned(Oid seqId, char deptype, Oid *tableId, int32 *colId);
-extern List *getOwnedSequences(Oid relid, AttrNumber attnum);
-extern Oid	getOwnedSequence(Oid relid, AttrNumber attnum);
+extern List *getOwnedSequences(Oid relid);
+extern Oid	getIdentitySequence(Oid relid, AttrNumber attnum, bool missing_ok);
 
 extern Oid	get_constraint_index(Oid constraintId);
 
 
@@ -399,3 +399,8 @@ CREATE TABLE itest_child PARTITION OF itest_parent (
 ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
 ERROR:  identity columns are not supported on partitions
 DROP TABLE itest_parent;
+-- test that sequence of half-dropped serial column is properly ignored
+CREATE TABLE itest14 (id serial);
+ALTER TABLE itest14 ALTER id DROP DEFAULT;
+ALTER TABLE itest14 ALTER id ADD GENERATED BY DEFAULT AS IDENTITY;
+INSERT INTO itest14 (id) VALUES (DEFAULT);
@@ -254,3 +254,11 @@ CREATE TABLE itest_child PARTITION OF itest_parent (
     f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY
 ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
 DROP TABLE itest_parent;
+
+
+-- test that sequence of half-dropped serial column is properly ignored
+
+CREATE TABLE itest14 (id serial);
+ALTER TABLE itest14 ALTER id DROP DEFAULT;
+ALTER TABLE itest14 ALTER id ADD GENERATED BY DEFAULT AS IDENTITY;
+INSERT INTO itest14 (id) VALUES (DEFAULT);