Server table rows permanently fixed (cannot be deleted or truncated)

From: Fred Williams <fredmw7(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Server table rows permanently fixed (cannot be deleted or truncated)
Date: 2022-11-17 14:49:34
Message-ID: CAJ8mXMoKCyXhxo=d_qjxvSNSC8w1yDbw-WRgSKz40_bei8EzcA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

PostgreSQL version 12.2

*Problem*
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!

*Table info*

CREATE TABLE public."NCAASchedule" (
"GameDateTime" timestamp NOT NULL,
"Week" int2 NOT NULL,
"HomeTeam" int2 NOT NULL,
"AwayTeam" int2 NOT NULL,
"Line" numeric(5, 1) NULL,
"OverUnder" numeric(5, 1) NULL,
"HomeTeamScore" int2 NULL,
"AwayTeamScore" int2 NULL,
"OpeningLine" numeric(5, 1) NULL,
"BowlID" int2 NULL,
"Control" int2 NULL,
CONSTRAINT ncaaschedule_pk PRIMARY KEY ("GameDateTime", "Week", "HomeTeam",
"AwayTeam")
);

Thanks for any help you can provide.

Fred Williams

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message hubert depesz lubaczewski 2022-11-17 15:55:34 Re: WAL segments removed from primary despite the fact that logical replication slot needs it.
Previous Message Masahiko Sawada 2022-11-17 14:22:12 Re: WAL segments removed from primary despite the fact that logical replication slot needs it.