pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17518: Getting Error "new multixact has more than one updating member" when trying to delete records.
6+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2022-11-24 2cf41cd309 de95928ea0 b20e381422 5a185246f9 f63f29733e 8e7c86785a Make multixact error message more explicit
* BUG #17518: Getting Error "new multixact has more than one updating member" when trying to delete records.
@ 2022-06-14 15:17 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 6+ messages in thread
From: PG Bug reporting form @ 2022-06-14 15:17 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: rkrier@cleo.com
The following bug has been logged on the website:
Bug reference: 17518
Logged by: Bob Krier
Email address: rkrier@cleo.com
PostgreSQL version: 12.10
Operating system: AWS RDS
Description:
We are getting an error executing a delete statement from JDBC like
follows:
DELETE FROM "EBI"."LOG_OF_CONNECTION" WHERE "DATE" < {some java date in
milliseconds}. The error is as follows:
---> "SQLException: ERROR: new multixact has more than one updating
member"
I've searched the web for this particular error and was only able to find
the source file
"https://github.com/postgres/postgres/blob/master/src/backend/access/transam/multixact.c";,
but I don't know the conditions that cause it. Is there any remedy to
this?
We have a table defined as follows:
CREATE TABLE "LOG_OF_CONNECTION" (
"ID" bpchar(32) NOT NULL,
"TYPE" int4 NOT NULL,
"STATUS" int4 NULL,
"DATE" int8 NULL,
"MARKED_FOR_PURGE" int2 NULL,
"CONNECTION_DIRECTION" varchar(1) NULL,
"TARGET_DBP" bpchar(32) NULL,
"ENDPOINT_ID" bpchar(32) NULL,
"METHOD_MESSAGES" bytea NULL,
"PERSISTENT_STORAGE_REQUESTED" int4 NULL,
"PROCESS_USER_REF" int8 NULL,
"CONNECTION_NUMBER" int4 NULL,
"NOTIFICATION_TYPE" varchar(256) NULL,
"TIME_SENT" int8 NULL,
"CALC_RETURN_TIME" int8 NULL,
"TIME_RECEIVED" int8 NULL,
"IS_PROCESSED" int4 NOT NULL DEFAULT 0,
"ENDPOINT_NAME" varchar(1024) NULL,
"TRADING_PARTNER_ID" varchar(1024) NULL,
"TRANSFER_ID" varchar(1024) NULL,
"LOG_ORIGIN_OBJECT" varchar(1024) NULL,
CONSTRAINT "LOG_OF_CONN_PK" PRIMARY KEY ("ID", "TYPE")
);
CREATE INDEX by_date_log_of_connection ON "EBI"."LOG_OF_CONNECTION" USING
btree ("DATE", "ID");
CREATE INDEX by_status_date_id3 ON "EBI"."LOG_OF_CONNECTION" USING btree
("ID", "TYPE", "STATUS", "DATE" DESC);
CREATE INDEX edi_late_ack_log_of_connection ON "EBI"."LOG_OF_CONNECTION"
USING btree ("ID");
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17518: Getting Error "new multixact has more than one updating member" when trying to delete records.
@ 2022-06-25 23:15 Dave Cramer <davecramer@postgres.rocks>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 6+ messages in thread
From: Dave Cramer @ 2022-06-25 23:15 UTC (permalink / raw)
To: rkrier@cleo.com; pgsql-bugs@lists.postgresql.org
Sorry for the late reply.
Thiis has nothing to do with JDBC. The error is coming from the backend
https://github.com/postgres/postgres/blob/00377b9a02b89a831ae50e1c718d34565356698f/src/backend/acces...
Dave Cramer
www.postgres.rocks
On Tue, 14 Jun 2022 at 12:03, PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17518
> Logged by: Bob Krier
> Email address: rkrier@cleo.com
> PostgreSQL version: 12.10
> Operating system: AWS RDS
> Description:
>
> We are getting an error executing a delete statement from JDBC like
> follows:
>
> DELETE FROM "EBI"."LOG_OF_CONNECTION" WHERE "DATE" < {some java date in
> milliseconds}. The error is as follows:
> ---> "SQLException: ERROR: new multixact has more than one updating
> member"
>
> I've searched the web for this particular error and was only able to find
> the source file
> "
> https://github.com/postgres/postgres/blob/master/src/backend/access/transam/multixact.c
> ",
> but I don't know the conditions that cause it. Is there any remedy to
> this?
>
>
> We have a table defined as follows:
> CREATE TABLE "LOG_OF_CONNECTION" (
> "ID" bpchar(32) NOT NULL,
> "TYPE" int4 NOT NULL,
> "STATUS" int4 NULL,
> "DATE" int8 NULL,
> "MARKED_FOR_PURGE" int2 NULL,
> "CONNECTION_DIRECTION" varchar(1) NULL,
> "TARGET_DBP" bpchar(32) NULL,
> "ENDPOINT_ID" bpchar(32) NULL,
> "METHOD_MESSAGES" bytea NULL,
> "PERSISTENT_STORAGE_REQUESTED" int4 NULL,
> "PROCESS_USER_REF" int8 NULL,
> "CONNECTION_NUMBER" int4 NULL,
> "NOTIFICATION_TYPE" varchar(256) NULL,
> "TIME_SENT" int8 NULL,
> "CALC_RETURN_TIME" int8 NULL,
> "TIME_RECEIVED" int8 NULL,
> "IS_PROCESSED" int4 NOT NULL DEFAULT 0,
> "ENDPOINT_NAME" varchar(1024) NULL,
> "TRADING_PARTNER_ID" varchar(1024) NULL,
> "TRANSFER_ID" varchar(1024) NULL,
> "LOG_ORIGIN_OBJECT" varchar(1024) NULL,
> CONSTRAINT "LOG_OF_CONN_PK" PRIMARY KEY ("ID", "TYPE")
> );
> CREATE INDEX by_date_log_of_connection ON "EBI"."LOG_OF_CONNECTION" USING
> btree ("DATE", "ID");
> CREATE INDEX by_status_date_id3 ON "EBI"."LOG_OF_CONNECTION" USING btree
> ("ID", "TYPE", "STATUS", "DATE" DESC);
> CREATE INDEX edi_late_ack_log_of_connection ON "EBI"."LOG_OF_CONNECTION"
> USING btree ("ID");
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17518: Getting Error "new multixact has more than one updating member" when trying to delete records.
@ 2022-06-25 23:51 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2022-06-25 23:51 UTC (permalink / raw)
To: rkrier@cleo.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, Jun 14, 2022 at 9:03 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17518
> Logged by: Bob Krier
> Email address: rkrier@cleo.com
> PostgreSQL version: 12.10
> Operating system: AWS RDS
> Description:
>
> We are getting an error executing a delete statement from JDBC like
> follows:
>
> DELETE FROM "EBI"."LOG_OF_CONNECTION" WHERE "DATE" < {some java date in
> milliseconds}. The error is as follows:
> ---> "SQLException: ERROR: new multixact has more than one updating
> member"
>
If you try this via psql you don't see the error though?
Do you get the same error if you delete a single row using a PK instead of
a bunch of rows via a date inequality?
Does it seem to matter which rows are being deleted in general, or how
many? How about columns?
Can you construct a self-contained minimal reproducer?
Does it manifest on standalone community PostgreSQL or just RDS?
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: BUG #17518: Getting Error "new multixact has more than one updating member" when trying to delete records.
@ 2022-06-27 16:36 Krier, Bob <rkrier@cleo.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Krier, Bob @ 2022-06-27 16:36 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Hi David,
Thanks for the response.
* I haven’t tried via psql. I only have a snapshot of the customer’s database that I can restore in RDS. It is very time consuming to set this up. I’ve corrected the problem by running VACUUM (FULL, FREEZE, VERBOSE, ANALYZE, INDEX_CLEANUP, TRUNCATE) on the table and the problem is resolved.
* Deleting by date where it only matches to 1 row seems to work. If I match on more than one, it seems to fail. I’m not sure what conditions make it happen. I have not tried to delete by PK. I’m not sure what you mean by “How about columns?”. You don’t specify columns on a delete statement.
* Can I construct a self-contained minimal reproducer?: No unfortunately exporting the table and importing it to another Postgres instance does not reproduce the issue. Again the vacuum above corrects the issue.
* Does it manifiest standalone…? Again I only have the snapshot to restore on RDS and can reproduce it that way.
Again, the issue is resolved. Thanks for your attention to this!
Bob
Bob
Krier
Cleo |
Principal Software Engineer II
Tel: 815-986-6759
Email: rkrier@cleo.com
|
Web: www.cleo.com
Request a Demo |
Take a tour of CIC Cockpit with RADAR
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Saturday, June 25, 2022 4:52 PM
To: Krier, Bob <rkrier@cleo.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17518: Getting Error "new multixact has more than one updating member" when trying to delete records.
On Tue, Jun 14, 2022 at 9:03 AM PG Bug reporting form <noreply@postgresql.org<mailto:noreply@postgresql.org>> wrote:
The following bug has been logged on the website:
Bug reference: 17518
Logged by: Bob Krier
Email address: rkrier@cleo.com<mailto:rkrier@cleo.com>
PostgreSQL version: 12.10
Operating system: AWS RDS
Description:
We are getting an error executing a delete statement from JDBC like
follows:
DELETE FROM "EBI"."LOG_OF_CONNECTION" WHERE "DATE" < {some java date in
milliseconds}. The error is as follows:
---> "SQLException: ERROR: new multixact has more than one updating
member"
If you try this via psql you don't see the error though?
Do you get the same error if you delete a single row using a PK instead of a bunch of rows via a date inequality?
Does it seem to matter which rows are being deleted in general, or how many? How about columns?
Can you construct a self-contained minimal reproducer?
Does it manifest on standalone community PostgreSQL or just RDS?
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17518: Getting Error "new multixact has more than one updating member" when trying to delete records.
@ 2022-06-27 17:11 David G. Johnston <david.g.johnston@gmail.com>
parent: Krier, Bob <rkrier@cleo.com>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2022-06-27 17:11 UTC (permalink / raw)
To: Krier, Bob <rkrier@cleo.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, Jun 27, 2022 at 9:36 AM Krier, Bob <rkrier@cleo.com> wrote:
> Hi David,
>
>
>
> Thanks for the response.
>
>
>
> - I haven’t tried via psql. I only have a snapshot of the customer’s
> database that I can restore in RDS. It is very time consuming to set this
> up. I’ve corrected the problem by running VACUUM (FULL, FREEZE,
> VERBOSE, ANALYZE, INDEX_CLEANUP, TRUNCATE) on the table and the
> problem is resolved.
>
>
Once you say "FULL" the rest of those options (except verbose) don't matter.
>
> -
> - Deleting by date where it only matches to 1 row seems to work. If I
> match on more than one, it seems to fail. I’m not sure what conditions
> make it happen. I have not tried to delete by PK. I’m not sure what you
> mean by “How about columns?”. You don’t specify columns on a delete
> statement.
>
> Not sure I was thinking clearly on that one - but ultimately you could
try "ALTER TABLE ... DROP COLUMN" though I'm doubting it would be productive
> - Can I construct a self-contained minimal reproducer?: No
> unfortunately exporting the table and importing it to another Postgres
> instance does not reproduce the issue. Again the vacuum above corrects
> the issue.
> - Does it manifiest standalone…? Again I only have the snapshot to
> restore on RDS and can reproduce it that way.
>
>
>
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17518: Getting Error "new multixact has more than one updating member" when trying to delete records.
@ 2022-06-28 19:33 Dave Cramer <davecramer@postgres.rocks>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Dave Cramer @ 2022-06-28 19:33 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Krier, Bob <rkrier@cleo.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, 27 Jun 2022 at 13:11, David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Mon, Jun 27, 2022 at 9:36 AM Krier, Bob <rkrier@cleo.com> wrote:
>
>> Hi David,
>>
>>
>>
>> Thanks for the response.
>>
>>
>>
>> - I haven’t tried via psql. I only have a snapshot of the customer’s
>> database that I can restore in RDS. It is very time consuming to set this
>> up. I’ve corrected the problem by running VACUUM (FULL, FREEZE,
>> VERBOSE, ANALYZE, INDEX_CLEANUP, TRUNCATE) on the table and the
>> problem is resolved.
>>
>>
> Once you say "FULL" the rest of those options (except verbose) don't
> matter.
>
>
>>
>> -
>> - Deleting by date where it only matches to 1 row seems to work. If
>> I match on more than one, it seems to fail. I’m not sure what conditions
>> make it happen. I have not tried to delete by PK. I’m not sure what you
>> mean by “How about columns?”. You don’t specify columns on a delete
>> statement.
>>
>> Not sure I was thinking clearly on that one - but ultimately you could
> try "ALTER TABLE ... DROP COLUMN" though I'm doubting it would be
> productive
>
>
>> - Can I construct a self-contained minimal reproducer?: No
>> unfortunately exporting the table and importing it to another Postgres
>> instance does not reproduce the issue. Again the vacuum above corrects
>> the issue.
>> - Does it manifiest standalone…? Again I only have the snapshot to
>> restore on RDS and can reproduce it that way.
>>
>>
Sounds to me like a corrupt table which is fixed by vacuuming.
Dave Cramer
>
>> -
>>
>>
>>
> David J.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-06-28 19:33 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-14 15:17 BUG #17518: Getting Error "new multixact has more than one updating member" when trying to delete records. PG Bug reporting form <noreply@postgresql.org>
2022-06-25 23:15 ` Dave Cramer <davecramer@postgres.rocks>
2022-06-25 23:51 ` David G. Johnston <david.g.johnston@gmail.com>
2022-06-27 16:36 ` Krier, Bob <rkrier@cleo.com>
2022-06-27 17:11 ` David G. Johnston <david.g.johnston@gmail.com>
2022-06-28 19:33 ` Dave Cramer <davecramer@postgres.rocks>
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