pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Server table rows permanently fixed (cannot be deleted or truncated)
3+ messages / 3 participants
[nested] [flat]

* Server table rows permanently fixed (cannot be deleted or truncated)
@ 2022-11-17 14:49 Fred Williams <fredmw7@gmail.com>
  2022-11-17 16:04 ` Re: Server table rows permanently fixed (cannot be deleted or truncated) David G. Johnston <david.g.johnston@gmail.com>
  2022-11-17 16:04 ` Re: Server table rows permanently fixed (cannot be deleted or truncated) Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 3+ messages in thread

From: Fred Williams @ 2022-11-17 14:49 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Server table rows permanently fixed (cannot be deleted or truncated)
  2022-11-17 14:49 Server table rows permanently fixed (cannot be deleted or truncated) Fred Williams <fredmw7@gmail.com>
@ 2022-11-17 16:04 ` David G. Johnston <david.g.johnston@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: David G. Johnston @ 2022-11-17 16:04 UTC (permalink / raw)
  To: fredmw7@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org

On Thu, Nov 17, 2022 at 8:37 AM Fred Williams <fredmw7@gmail.com> wrote:

> 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.
>
>
Some suggestions of things you can do (not mutually exclusive):
Update PostgreSQL to a supported version, v14.6
Post the output of running: \d+ "NCAASchedule"

Rename the table to something else.  Create a new table with this name.
Populate it with the data you want it to have.  Recreate objects depending
on the old table to instead depend on the newly created one. Drop the
problematic table.  (If you aren't married to the table name, you might
consider creating the new table with a new name so that nothing existing,
internal or external, is capable of referencing it).

I seriously doubt this is a PostgreSQL bug; some trigger or external
process messing with the table seems much more plausible.

Turning on statement logging could help.

David J.

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Server table rows permanently fixed (cannot be deleted or truncated)
  2022-11-17 14:49 Server table rows permanently fixed (cannot be deleted or truncated) Fred Williams <fredmw7@gmail.com>
@ 2022-11-17 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2022-11-17 16:04 UTC (permalink / raw)
  To: fredmw7@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2022-11-17 16:04 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-17 14:49 Server table rows permanently fixed (cannot be deleted or truncated) Fred Williams <fredmw7@gmail.com>
2022-11-17 16:04 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-17 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>

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