Re: BUG #16722: PG hanging on COPY when table has close to 2^32 toasts in the table. - Mailing list pgsql-bugs

Hi

> Oh meh. Yeah, I didn't think about the actual implementation of doing it :/
>
> That said, I'd really prefer to see *some* kind of counter that could
> get people noticing this a bit earlier, rather than just bugging them
> in the logfile once it's gone over a threshold.  A statistics counter
> maybe, but I guess that'd have to be tracked at a per-table level to
> be really useful, which would make it potentially fairly expensive to
> keep around...

I think the statistics on OID assignments per-table is a good idea, but how about simply logging the event to notify users as the first step?
I think it's easy to implement and helpful for users.

First of all, I would like to provide a way for the user to know that it is taking a long time to generate the OID, and provide an opportunity for the user to deal with this problem with statement_timeout etc.
This is an interim measure, but I think it's better than it is.

Since it is complicated to handle WaitEvent and statistics for each table, I first considered a patch that simply outputs logs, giving priority to avoiding a hang state without outputting logs.

* v01-0001-OID_log_output.patch
   In this patch, GetNewOidWithIndex outputs a log when it loops too many times to assign a new OID.
   Also, if the log is output even once, it will be output to the log when the OID is assigned.

   * This patch does not cause an error even if it loops too many times.
     How much time can be tolerated in the OID search loop depends on the user, so I think that the user should decide the time to make an error with statement_timeout.

   * The log output interval threshold increases by "* = 2"(exponential backoff) and the maximum number of log outputs is set to 5.
     The reason for setting this threshold is to reduce the number of log outputs and output the log before stopping at statement_timeout etc. I think the appropriate value for this threshold is controversial.
     Since it is not necessary to keep logging, the upper limit is set to 5 times.

   * The log output interval threshold is set to 1 million for the first log output.
     In my environment, it takes about 4 seconds to loop 1 million times, and the 5th log is output after about 2 minutes.I think this threshold is just right considering statements_timeout, but it's controversial.

Log output does not need to continue output during OID assignment. I think the first few and the last log are enough.

I will add this patch to the commitfest. I look forward to your feedback about the patch.


By the way
How about distributing OIDs so that the GetNewOidWithIndex function doesn't take long to find an available OID?
For example, skip one and assign an OID.

As a side effect of this method, the OID progresses faster.
(Maybe the btree index will be fragmented faster)

It may be better to include skips only for TOAST chunk_id, as this method may have other side effects.

regards.
--
Tomohiro Hiramitsu
NTT Open Source Software Center