From: Tom Lane <tgl@sss.pgh.pa.us>
To: fredmw7@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Server table rows permanently fixed (cannot be deleted or truncated)
Date: Thu, 17 Nov 2022 11:04:15 -0500
Message-ID: <1834626.1668701055@sss.pgh.pa.us> (raw)
In-Reply-To: <CAJ8mXMoKCyXhxo=d_qjxvSNSC8w1yDbw-WRgSKz40_bei8EzcA@mail.gmail.com>
References: <CAJ8mXMoKCyXhxo=d_qjxvSNSC8w1yDbw-WRgSKz40_bei8EzcA@mail.gmail.com>
Fred Williams <fredmw7@gmail.com> writes:
> I am unable to delete or edit a specific range of table rows (I'll call
> them corrupted rows) for a specific primary key. If I truncate the entire
> table, the corrupted rows remain. In the corrupted rows, I can successfully
> update non-primary key fields, but not primary-key fields.
> *Miscellaneous*
> 1. The problem does not occur on a test server using the same exact
> table.
> 2. If I rename the table, I can remove the corrupted rows and/or update
> the primary keys (such as changing the DateTime). However, when I rename it
> back, the corrupted rows reappear!
TBH, I'm suspecting pilot error. I wonder whether you have another
table by the same name in a different schema, which is the one
containing the "corrupted" rows, and after you rename this table
out of the way you're unintentionally accessing the other one.
That theory doesn't explain the rows surviving TRUNCATE, but
maybe inheritance could --- IIRC, TRUNCATE will not touch child
tables. So maybe the alias table is also an inheritance child
of the one you are modifying?
This command in psql would clarify much:
postgres=# \d+ *."NCAASchedule"
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, fredmw7@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Server table rows permanently fixed (cannot be deleted or truncated)
In-Reply-To: <1834626.1668701055@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox