pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Fujii Masao <masao.fujii@oss.nttdata.com>
To: tomohiro hiramitsu <hiramit.tm@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: Kasahara Tatsuhito <kasahara.tatsuhito@gmail.com>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Andres Freund <andres@anarazel.de>
Cc: skoposov@ed.ac.uk, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16722: PG hanging on COPY when table has close to 2^32 toasts in the table.
Date: Fri, 19 Mar 2021 01:32:29 +0900
Message-ID: <fb3f67af-f23c-63aa-0c21-fb1c67dfaeef@oss.nttdata.com> (raw)
In-Reply-To: <CAOR2cEaMmOhUMhL2GTJgxSEoEzL7xO5h5XUiJ4LoEy5M4-yssg@mail.gmail.com>
References: <CAP0=ZVLue4DBqV5cSD63JbXryV7+UKXdukfoLyEOpw+2EHRFCg@mail.gmail.com>
	<CAP0=ZVKdHgLGvFJHE_6x2PTrw50_FLqPUSdRNzVTKSb_y8naCQ@mail.gmail.com>
	<CAOR2cEb4Suwnk+rvC-Ea1BmOKXTg-nV--N9f+rRo4TEZztN7bg@mail.gmail.com>
	<20210301.112452.1902584192027811521.horikyota.ntt@gmail.com>
	<666801.1614567656@sss.pgh.pa.us>
	<CAOR2cEYyXx_oHiiAri7sHHqyEofWcfi9-fDoQMiNKYwvN_ZTmA@mail.gmail.com>
	<b1bf70bd-334c-afcf-8bca-db611e9e0ad9@oss.nttdata.com>
	<CAOR2cEYFYujtGfYiuPKJT-QwHJEE-86dVRXhbZ7ARUZA_F7fYw@mail.gmail.com>
	<CAOR2cEaMmOhUMhL2GTJgxSEoEzL7xO5h5XUiJ4LoEy5M4-yssg@mail.gmail.com>



On 2021/03/17 14:30, tomohiro hiramitsu wrote:
> v4-0001-GetNewOidWithIndex_log_output.patch

Thanks for updating the patch! I applied the following and cosmetic changes
to the patch. Attached is the updated version of the patch.

+			ereport(LOG,
+				(errmsg("still finding an unused OID within relation \"%s\"",
+		ereport(LOG,
+			(errmsg("the new OID has been assigned in relation \"%s\" after \"%llu\" retries",

WARNING is more suitable for these kinds of messages?
I changed the log level here to WARNING.

+#define GETNEWOID_NOTIFY_MINVAL 1000000
+#define GETNEWOID_NOTIFY_MAXVAL 100000000

If we use WARNING, GETNEWOID_WARN_THRESHOLD and GETNEWOID_WARN_MAX_INTERVAL
sound better to me for the meanings of them. I renamed them.

+	if (retry_count >= GETNEWOID_NOTIFY_MINVAL)

">=" should be ">"? Otherwise, the message for completion of OID assignment
can be logged even when no message "still finding ..." is output. I fixed this.

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Attachments:

v5-0001-GetNewOidWithIndex_log_output.patch (text/plain, 2.4K) download diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c index e2ed80a5de..a90ab3614c 100644 --- a/src/backend/catalog/catalog.c +++ b/src/backend/catalog/catalog.c @@ -47,6 +47,13 @@ #include "utils/snapmgr.h" #include "utils/syscache.h" +/* + * Parameters to determine when to emit a warning in + * GetNewOidWithIndex() + */ +#define GETNEWOID_WARN_THRESHOLD 1000000 +#define GETNEWOID_WARN_MAX_INTERVAL 100000000 + /* * IsSystemRelation * True iff the relation is either a system catalog or a toast table. @@ -318,6 +325,8 @@ GetNewOidWithIndex(Relation relation, Oid indexId, AttrNumber oidcolumn) SysScanDesc scan; ScanKeyData key; bool collides; + uint64 retries = 0; + uint64 retries_before_warn = GETNEWOID_WARN_THRESHOLD; /* Only system relations are supported */ Assert(IsSystemRelation(relation)); @@ -353,8 +362,48 @@ GetNewOidWithIndex(Relation relation, Oid indexId, AttrNumber oidcolumn) collides = HeapTupleIsValid(systable_getnext(scan)); systable_endscan(scan); + + /* + * Warn users that we iterate more than GETNEWOID_WARN_THRESHOLD but + * have not yet found OID unused in the relation. Then repeat warning + * with exponentially increasing intervals until we iterate more than + * GETNEWOID_WARN_MAX_INTERVAL. Finally repeat warning every + * GETNEWOID_WARN_MAX_INTERVAL unless an unused OID is found. This + * logic is necessary not to fill up the server log with the similar + * messages. + */ + if (retries >= retries_before_warn) + { + ereport(WARNING, + (errmsg("still finding an unused OID within relation \"%s\"", + RelationGetRelationName(relation)), + errdetail("OID candidates were checked \"%llu\" times, but no unused OID is yet found.", + (unsigned long long) retries))); + + /* + * Double the number of retries to do before warning next until it + * reaches GETNEWOID_WARN_MAX_INTERVAL. + */ + if (retries_before_warn * 2 <= GETNEWOID_WARN_MAX_INTERVAL) + retries_before_warn *= 2; + else + retries_before_warn += GETNEWOID_WARN_MAX_INTERVAL; + } + + retries++; } while (collides); + /* + * If at least one warning is emitted, also warn the completion of OID + * assignment. + */ + if (retries > GETNEWOID_WARN_THRESHOLD) + { + ereport(WARNING, + (errmsg("new OID has been assigned in relation \"%s\" after \"%llu\" retries", + RelationGetRelationName(relation), (unsigned long long) retries))); + } + return newOid; }

view thread (28+ messages)  latest in thread

Message-ID: <fb3f67af-f23c-63aa-0c21-fb1c67dfaeef@oss.nttdata.com>
Permalink:  ../fb3f67af-f23c-63aa-0c21-fb1c67dfaeef@oss.nttdata.com/
Also on:    postgresql.org/message-id/fb3f67af-f23c-63aa-0c21-fb1c67dfaeef@oss.nttdata.com

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: masao.fujii@oss.nttdata.com, hiramit.tm@gmail.com, tgl@sss.pgh.pa.us, horikyota.ntt@gmail.com, kasahara.tatsuhito@gmail.com, magnus@hagander.net, andres@anarazel.de, pgsql-bugs@lists.postgresql.org
  Subject: Re: BUG #16722: PG hanging on COPY when table has close to 2^32 toasts in the table.
  In-Reply-To: <fb3f67af-f23c-63aa-0c21-fb1c67dfaeef@oss.nttdata.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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