From: kurt thepw.com <kurt@thepw.com>
To: Colin 't Hart <colinthart@gmail.com>, PostgreSQL General
	<pgsql-general@lists.postgresql.org>
Subject: Re: Two sequences associated with one identity column
Thread-Topic: Two sequences associated with one identity column
Thread-Index: AQHcSMcgK4OvlbsN6Eigz34vjyB0QbTZBl4AgAABSKuAAAOCgIAADrqa
Date: Wed, 29 Oct 2025 13:16:51 +0000
Message-ID:
 <BN8PR07MB5905EB3838C147873902EA1DD3FAA@BN8PR07MB5905.namprd07.prod.outlook.com>
References:
 <CAMon-aTbZC5k2pLpijk5x+h__LV08DzpoJtHBgZGkOKJNJ91pw@mail.gmail.com>
 <aQICcMvkp96kenL8@depesz.com>
 <CAMon-aTMgbwC9j-wY8iX5kZ4=KXzCXc-Wpwx3viid8hQ_bJkoQ@mail.gmail.com>
 <aQIDbBO0nJTiMd5C@depesz.com>
 <CAMon-aT-DyO6mesMub_dYTo+mZ6yQ8kHMrMCCz19qrV4rUKiZQ@mail.gmail.com>
In-Reply-To:
 <CAMon-aT-DyO6mesMub_dYTo+mZ6yQ8kHMrMCCz19qrV4rUKiZQ@mail.gmail.com>
Accept-Language: en-US
Content-Language: en-US
msip_labels:
authentication-results: dkim=none (message not signed)
 header.d=none;dmarc=none action=none header.from=thepw.com;
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: BN8PR07MB5905:EE_|DS4PR07MB11370:EE_
x-ms-office365-filtering-correlation-id: 66fd9e1c-df1d-4664-6c96-08de16ed6cb2
x-ms-exchange-senderadcheck: 1
x-ms-exchange-antispam-relay: 0
x-microsoft-antispam:
 BCL:0;ARA:13230040|376014|366016|1800799024|8096899003|7053199007|38070700021;
x-microsoft-antispam-message-info:
 =?Windows-1252?Q?98F0boktUvJAsMh0BAaRchPJbTrC6VoB+s8E37pJMDDqguZ6FYKywp1B?=
 =?Windows-1252?Q?hBXCNRdlljuY3X2TIfV9kkj4MzCqT7+Jn0L39F908k9RM8Q+ugqAfDZJ?=
 =?Windows-1252?Q?ArcT30GTdpgkehGQl3TCGjlxziKqW8xV/yWoqMrJsxaS+RSUMnZrR7uv?=
 =?Windows-1252?Q?xFxmAvQvuXnb1UDC5IKWsBG9Wayk5UK3SnExZtjWCKl/QmDgQt49IJDw?=
 =?Windows-1252?Q?sAuKK2pK0GjfxfiVD5MjvXgl2U1fLdaygXC4ZqmXPRZT8yeWjQmXo4/R?=
 =?Windows-1252?Q?ATG+yXRVQXu18UgJtlH+amjon8bWDhbk5r7JIaD/MuVGb0JKm78gN5OW?=
 =?Windows-1252?Q?46qij5/GpRgRFxCnbm4yrGsAuASR/9ct/G8sIIZQHCzlGKkgEqJ9F6rs?=
 =?Windows-1252?Q?oW8skWHtvigIcl5vgkZvID9zKZBTQ75Agqhn8ABMTB9IBeYN2CQzPgL+?=
 =?Windows-1252?Q?5Fei1JjfbC/IFKywQiVyCdCBL8L/H7/5sfKM5YOTS+e45dmWC5Ejf75d?=
 =?Windows-1252?Q?ScQ7yfljS9SYZGSHt5Cq9r3W7meifIErq98cqKSd9Gqz3AXfTuBKeel6?=
 =?Windows-1252?Q?w4qKCKX93L6Tikp3jTfqmRxNkVQq9pf37uW3t8J2fln6S6yt6uoywvry?=
 =?Windows-1252?Q?BT3JRGqRAGH0ei1VQbLBMY/bJLXO3a8jr7qWEpVvA8a5MZCB8qYsBEBb?=
 =?Windows-1252?Q?EivJv3Mic97nqkFzWABwgaWK4V3hQcC7mKVlM/0nDxgNCdlpSmDQu/tc?=
 =?Windows-1252?Q?C0hNRE8Z6ZpY7D0XH89iLvUryPw6mVzPS5B5Z+Os/zqEEJ+okY36SuiQ?=
 =?Windows-1252?Q?p5McmEnAfUTPX12CHfJTej/qiyAkCSDMt+sJQdAptH56jHqDqjkWjFfe?=
 =?Windows-1252?Q?Ut1vg17WkzAwonKwFrbY1Lz/0PV7nVJG8y5trNYT0CdqHwGMFCjQemS/?=
 =?Windows-1252?Q?coTDoNbcvXECac4OdQF1OCHCv8hfn+DjTbieFT3ceBA+y5QyTnPiy/R4?=
 =?Windows-1252?Q?xh4VhegVo8Ea+LcbwSfD3xvv5rRt7+twfWPDZ0FKyqcdTi+1SlvDxWt+?=
 =?Windows-1252?Q?raGQL7f4Q3bZ0niLZ2KsiMsC35t9RdW7PPB2saPZL9AXIe3h2A6MuPcl?=
 =?Windows-1252?Q?/bHL5urhLoEkSpKDJqJGEzWf6dyfCiLwNWrWed61QpH5vHO8ihnMShiq?=
 =?Windows-1252?Q?19NHwQyecBC0ZUtmL2oo/5+Vo3Qo75i0pZ2UXavqQEZ03fuI4zfjnD+t?=
 =?Windows-1252?Q?Wc3uZChUVbNSAqms5DuSj/1ubgibDJ1ntDjmevC7+LJmxghhMuNQVK3/?=
 =?Windows-1252?Q?YXbVSWFU/LyKWQ/+jatrRL/48RiDaSIQP+6s5BIWN2t4Tr/cuakeoKql?=
 =?Windows-1252?Q?T+ziKemksDM5zhVJnX8SCS8GFoUEqDnk1Giy14QXTTZqsQlDSlSSJvPc?=
 =?Windows-1252?Q?DIJwOXAFfD/GSIdkOxwqGbgs9V8E8HYSKM09zxtGV1c2ZuSxIxrPniOD?=
 =?Windows-1252?Q?T7No7J9FQ3rlyI8KjNFNF1seNZAVHQqfeek56UHNPRvPF+XWQ880xkVF?=
 =?Windows-1252?Q?EPVHvvXLO8m6+rlJZ2cvar4eRKnuuOYzEFqFWICJXQo9wPUXeg0WMAWL?=
 =?Windows-1252?Q?9TV8+bCZMoEgSICQGUp239rB?=
x-forefront-antispam-report:
 CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:BN8PR07MB5905.namprd07.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(376014)(366016)(1800799024)(8096899003)(7053199007)(38070700021);DIR:OUT;SFP:1102;
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?Windows-1252?Q?4vu4HQTvNvYdUbZdMHea51HeRcOBug1780Z6WAInm+aH8BgI7mHygonK?=
 =?Windows-1252?Q?qde0q12qCOSh5S7JQ91bi+PPnWkO2pNDP3HBH+8UFLKykbeWIcj6HQtP?=
 =?Windows-1252?Q?Le9BFPU8+x/nuu1R6pkuJ1xB1vQzdtY0RF52ib5XmXCdPF+t44KPVcjv?=
 =?Windows-1252?Q?Ph51RRI2jukwG+mP7hBIZLiKT4EcNKG7mAREpVF2jSiKKIK6CLP+oReV?=
 =?Windows-1252?Q?cLoz5KryOk5EMNqcod0BoCJ/iT7cfxmLvZjdLAagKoe0PdQ3Anay19i0?=
 =?Windows-1252?Q?TN5VvSWOklCWJgGYYLXi3gBsUjBPtMfJOnKu3hfa0WfmYn30XVVauVuZ?=
 =?Windows-1252?Q?VAU07xQ9A1ORnWZK1ZTVfnGQLii8PV/ldmBeT95zmUDpozzC1iBQ7XHV?=
 =?Windows-1252?Q?Zs34VXSGAn20225jvPO0uSUAQBSnRZpvujvp1OMMlvsNGVuqxYC8k90S?=
 =?Windows-1252?Q?OAqEx241VoxQT9k3AwhXxr29ICq+lWmUtigbRBYpZCi8XokGS1L3sVXh?=
 =?Windows-1252?Q?nxoVmo4Cy4kp8TQVRNUuze7bVdT+bOVBGswrinm4VEshDx4z6eR2dQfP?=
 =?Windows-1252?Q?TVESxQ4WQ67yOgkM+oKWlNFtiGipMLWrke+oV5tdIrV+lH7pnAb1h/Ox?=
 =?Windows-1252?Q?NiNr2DGqeBX3jQ46sX6ntQ+8xz2T8dRqWoRyMFo++vK3f6SO82RqwEU+?=
 =?Windows-1252?Q?Hpb6KyKDcg4+iHQ6DGqH2zmWqyqUJNRzQSWEWQ1nWixWpkBcAH+k+6ex?=
 =?Windows-1252?Q?DnKLaY7/8xioYOWeugKwa2rRNokymAbt944vfY5JwuOqM9L24/kpSym/?=
 =?Windows-1252?Q?xNwyz0S/Y5CKkTzOTVJfX7INQp/P8W6k3Sr1c6zaYUJJ5vXD/jKW4gWl?=
 =?Windows-1252?Q?iG8liLNo8hXU9r9VD6vx4IMCCrfpeIsRJX5vJwhZ9FS+RmUFNI3hBsqY?=
 =?Windows-1252?Q?qoJuLDVVH8Q8hpjV5iMn6TkauasHh4ol/vIbzAxuFRztDxI4cFMUo3nk?=
 =?Windows-1252?Q?dJhEQHQkFbHCyT02JA76SQFeaFI1x0BtyGISrhckqlROKFuIrDm73DS5?=
 =?Windows-1252?Q?N2betBohnvXIKTkBQ/qO399MsEW1Jok4bhgiamqs0NgGuMACcQdaT1OE?=
 =?Windows-1252?Q?ZOaDiquQ8oKdwVHO29eB1+gy7tBsLWsujbEN7lcBqQaZBWW+Tz42Eg0y?=
 =?Windows-1252?Q?0KbY5AcsW7N0vxB1uqnVlbl4t4/jL+6gWxtq56yG2iOgQ5S5ffu0U288?=
 =?Windows-1252?Q?5DvwrPgJyac24cJZFfWck2+u33oh0Xo3znS5bjMixKXVOuWS6Bb/hqAw?=
 =?Windows-1252?Q?OpGCLvgn7A9gXUn0DpYpnz895yCtePR7bNNheeLDz62P7AH5yR3vpsc6?=
 =?Windows-1252?Q?//ICEvZ55048eNZ/Uau81oz4RJ5rgXjKwEzcZSmJyyqKKQCaoykSwyl3?=
 =?Windows-1252?Q?YGlVvBmuj+q5rTrnXfVIOO1aQGlED700dkhJMNvEjeQXnatXJmPv3xxU?=
 =?Windows-1252?Q?cWpJwtAYmIS37qPv12uA1PKDeAPfO02gjkFEUbgGy45K7dPckWRhJCuV?=
 =?Windows-1252?Q?p/4PREzyEsd9oglmSUd2CwQZzaveYu6qRe+JK1pyGPRCulU3EeBlHKoe?=
 =?Windows-1252?Q?gh8mcWJZwef4TZmbl70u7UsM5kb7YLHFi6v+azLon6unGXohhJAp3uRU?=
 =?Windows-1252?Q?krETJePvHTU=3D?=
Content-Type: multipart/alternative;
	boundary="_000_BN8PR07MB5905EB3838C147873902EA1DD3FAABN8PR07MB5905namp_"
MIME-Version: 1.0
X-OriginatorOrg: thepw.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: BN8PR07MB5905.namprd07.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 66fd9e1c-df1d-4664-6c96-08de16ed6cb2
X-MS-Exchange-CrossTenant-originalarrivaltime: 29 Oct 2025 13:16:51.9370
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 2b55f125-3527-485e-ac77-3a5c553302cd
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: /Is1mYPbVIjXDjsJJNA91jQXTZenAhPDh1GAdCft65rjRSNvrz+sKiQa1l6NCQ9O
X-MS-Exchange-Transport-CrossTenantHeadersStamped: DS4PR07MB11370
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/BN8PR07MB5905EB3838C147873902EA1DD3FAA%40BN8PR07MB5905.namprd07.prod.outlook.com>
Precedence: bulk

--_000_BN8PR07MB5905EB3838C147873902EA1DD3FAABN8PR07MB5905namp_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable


     If this is a development database, perhaps you can do a schema-only pg=
_dump of it in plain text format, manually edit out the offending second se=
quence from  the resulting SQL file, and restore it into a new database.

Yours,

Kurt Reimer
________________________________
From: Colin 't Hart <colinthart@gmail.com>
Sent: Wednesday, October 29, 2025 8:20 AM
To: PostgreSQL General <pgsql-general@lists.postgresql.org>
Subject: Re: Two sequences associated with one identity column

Again as I wrote above, drop identity complains about more than one sequenc=
e.

I have no idea how this customer arrived at this situation or if it
affects other environments (this is actually a dev database that we're
trying to upgrade as the first step in an upgrade project).

I suspect the dump will just show two sequences that need to be
imported and it will fail on the second one. I'll make a dump.

/Colin

On Wed, 29 Oct 2025 at 13:07, hubert depesz lubaczewski
<depesz@depesz.com> wrote:
>
> On Wed, Oct 29, 2025 at 01:04:48PM +0100, Colin 't Hart wrote:
> > Thanks. But as I wrote above, trying to alter either of the two
> > sequences and specifying "owned by none" results in the error.
>
> Sorry, missed that.
>
> Can you please provide pg_dump output from this db, just schema, just
> this one table, and both sequences?
>
> Or, how did you arrive at this situation?
>
> Did you try to alter table =85 alter column =85 drop identity;
>
> Best regards,
>
> depesz
>


--_000_BN8PR07MB5905EB3838C147873902EA1DD3FAABN8PR07MB5905namp_
Content-Type: text/html; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DWindows-1=
252">
<style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo=
ttom:0;} </style>
</head>
<body dir=3D"ltr">
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
<br>
&nbsp; &nbsp; &nbsp;If this is a development database, perhaps you can do a=
 schema-only pg_dump of it in plain text format, manually edit out the offe=
nding second sequence from&nbsp; the resulting SQL file, and restore it int=
o a new database.<br>
<br>
Yours,</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
Kurt Reimer</div>
<div id=3D"appendonsend"></div>
<hr style=3D"display:inline-block;width:98%" tabindex=3D"-1">
<div id=3D"divRplyFwdMsg" dir=3D"ltr"><font face=3D"Calibri, sans-serif" st=
yle=3D"font-size:11pt" color=3D"#000000"><b>From:</b> Colin 't Hart &lt;col=
inthart@gmail.com&gt;<br>
<b>Sent:</b> Wednesday, October 29, 2025 8:20 AM<br>
<b>To:</b> PostgreSQL General &lt;pgsql-general@lists.postgresql.org&gt;<br=
>
<b>Subject:</b> Re: Two sequences associated with one identity column</font=
>
<div>&nbsp;</div>
</div>
<div class=3D"BodyFragment"><font size=3D"2"><span style=3D"font-size:11pt;=
">
<div class=3D"PlainText">Again as I wrote above, drop identity complains ab=
out more than one sequence.<br>
<br>
I have no idea how this customer arrived at this situation or if it<br>
affects other environments (this is actually a dev database that we're<br>
trying to upgrade as the first step in an upgrade project).<br>
<br>
I suspect the dump will just show two sequences that need to be<br>
imported and it will fail on the second one. I'll make a dump.<br>
<br>
/Colin<br>
<br>
On Wed, 29 Oct 2025 at 13:07, hubert depesz lubaczewski<br>
&lt;depesz@depesz.com&gt; wrote:<br>
&gt;<br>
&gt; On Wed, Oct 29, 2025 at 01:04:48PM +0100, Colin 't Hart wrote:<br>
&gt; &gt; Thanks. But as I wrote above, trying to alter either of the two<b=
r>
&gt; &gt; sequences and specifying &quot;owned by none&quot; results in the=
 error.<br>
&gt;<br>
&gt; Sorry, missed that.<br>
&gt;<br>
&gt; Can you please provide pg_dump output from this db, just schema, just<=
br>
&gt; this one table, and both sequences?<br>
&gt;<br>
&gt; Or, how did you arrive at this situation?<br>
&gt;<br>
&gt; Did you try to alter table =85 alter column =85 drop identity;<br>
&gt;<br>
&gt; Best regards,<br>
&gt;<br>
&gt; depesz<br>
&gt;<br>
<br>
<br>
</div>
</span></font></div>
</body>
</html>

--_000_BN8PR07MB5905EB3838C147873902EA1DD3FAABN8PR07MB5905namp_--