pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Radek Slupik <radek@channable.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: High table creation rate results in “File exists” error
Date: Tue, 21 Jan 2020 14:52:13 +0100
Message-ID: <20200121135213.3ud6frut457d7yyp@development> (raw)
In-Reply-To: <CAAMrnERXphFTr+6k61Bd6Q7HJ7gzSfTW=JLEtqsDATWb0JfXug@mail.gmail.com>
References: <CAAMrnERXphFTr+6k61Bd6Q7HJ7gzSfTW=JLEtqsDATWb0JfXug@mail.gmail.com>

On Tue, Jan 21, 2020 at 02:04:24PM +0100, Radek Slupik wrote:
>Hi,
>
>We occasionally run into an internal error when creating tables. The error
>message is «could not create file "base/17006/2851173576": File exists»
>with SQLSTATE 58P02. The attachment is included with this email contains
>the PostgreSQL logs. Note that some identifiers are renamed in the logs
>because they contain sensitive information, but identifier names are
>unlikely the source of the error.
>
>An old mailing list thread [1] suggests a problem related to OID
>wraparound. Our use case involves creating and dropping thousands of tables
>every day. The CREATE TABLE statements for these tables are similar to the
>one in the attached logs, but with different table names and columns.
>Nonetheless, the error is difficult to reproduce as it happens only
>sporadically. Retrying the CREATE TABLE statement fixes the problem
>temporarily.
>
>Some more information about our PostgreSQL instance and usage:
>
>   - PostgreSQL version: PostgreSQL 10.6 (Ubuntu 10.6-1.pgdg16.04+1) on
>   x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0
>   20160609, 64-bit.
>   - The DBMS did not crash or abruptly shut down recently.
>   - The file base/17006/2851173576 was actually in use by an index, as
>   indicated by pg_class.relfilenode.
>   - Autovacuum is disabled for these tables. Logging is disabled for these
>   tables upon creation, but enabled after filling them with data.
>   - The query “SELECT max(oid) FROM pg_class” reports 4,294,603,797, which
>   is close to 2³² - 1.
>

So what was the actual rate of table creation? How long did it take to
get to this state?

What kernel/filesystem was used? What did lsof say?


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services





view thread (3+ messages)  latest in thread

Message-ID: <20200121135213.3ud6frut457d7yyp@development>
Permalink:  ../20200121135213.3ud6frut457d7yyp@development/
Also on:    postgresql.org/message-id/20200121135213.3ud6frut457d7yyp@development

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: tomas.vondra@2ndquadrant.com, radek@channable.com
  Subject: Re: High table creation rate results in “File exists” error
  In-Reply-To: <20200121135213.3ud6frut457d7yyp@development>
  References: <CAAMrnERXphFTr+6k61Bd6Q7HJ7gzSfTW=JLEtqsDATWb0JfXug@mail.gmail.com> <20200121135213.3ud6frut457d7yyp@development>

* 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