Horton

Thread

Commits

  1. Make multixact error message more explicit

  2. CREATE INDEX: use the original userid for more ACL checks.

  1. BUG #17518: Getting Error "new multixact has more than one updating member" when trying to delete records.

    The Post Office <noreply@postgresql.org> — 2022-06-14T15:17:46Z

    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");

  2. Re: BUG #17518: Getting Error "new multixact has more than one updating member" when trying to delete records.

    Dave Cramer <davecramer@postgres.rocks> — 2022-06-25T23:15:14Z

    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/access/transam/multixact.c#L803

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");
>
>

  3. Re: BUG #17518: Getting Error "new multixact has more than one updating member" when trying to delete records.

    David G. Johnston <david.g.johnston@gmail.com> — 2022-06-25T23:51:59Z

    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.

  4. RE: BUG #17518: Getting Error "new multixact has more than one updating member" when trying to delete records.

    Krier, Bob <rkrier@cleo.com> — 2022-06-27T16:36:03Z

    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.

  5. Re: BUG #17518: Getting Error "new multixact has more than one updating member" when trying to delete records.

    David G. Johnston <david.g.johnston@gmail.com> — 2022-06-27T17:11:21Z

    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.

  6. Re: BUG #17518: Getting Error "new multixact has more than one updating member" when trying to delete records.

    Dave Cramer <davecramer@postgres.rocks> — 2022-06-28T19:33:27Z

    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.
>