pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedHigh table creation rate results in “File exists” error
3+ messages / 3 participants
[nested] [flat]
* High table creation rate results in “File exists” error
@ 2020-01-21 13:04 Radek Slupik <radek@channable.com>
0 siblings, 1 reply; 3+ messages in thread
From: Radek Slupik @ 2020-01-21 13:04 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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.
Kind regards,
Radek Slupik
Channable
[1]:
https://www.postgresql.org/message-id/CAP_rwwmVcFqZJ72WkyxcFrGPY6XpDSUj_UrxfCMnRYT4hCGfBg%40mail.gma...
Attachments:
file-exists-error.log (text/x-log, 853B) download
2020-01-14 04:31:17.522 CET [15026] myrole@mydatabase ERROR: could not create file "base/17006/1362480685": File exists
2020-01-14 04:31:17.522 CET [15026] myrole@mydatabase STATEMENT:
CREATE UNLOGGED TABLE "project_12345_t"
( "id" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (SEQUENCE NAME "project_12345_t_id_sequence")
, "%a" "text", "%b" "text", "%c" "text", "%d" "text", "%e" "text"
, "%f" "text", "%g" "text", "%h" "text", "%i" "text", "%j" "text"
, "%k" "text", "%l" "text", "%m" "text", "%n" "text", "%o" "text"
, "%p" "jsonb", "%q" "text", "%r" "text", "%s" "text", "%t" "text"
, "%u" "text", "%v" "text", "%w" "text"
, CONSTRAINT "project_12345_t_pkey" PRIMARY KEY ("id") )
WITH ( autovacuum_enabled = false
, toast.autovacuum_enabled = false);
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: High table creation rate results in “File exists” error
@ 2020-01-21 13:52 Tomas Vondra <tomas.vondra@2ndquadrant.com>
parent: Radek Slupik <radek@channable.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tomas Vondra @ 2020-01-21 13:52 UTC (permalink / raw)
To: Radek Slupik <radek@channable.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: High table creation rate results in “File exists” error
@ 2020-01-22 17:30 Ruud van Asseldonk <ruud@channable.com>
parent: Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 0 replies; 3+ messages in thread
From: Ruud van Asseldonk @ 2020-01-22 17:30 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: Radek Slupik <radek@channable.com>; pgsql-bugs@lists.postgresql.org
Hi, thanks for looking into this!
On Wed, 22 Jan 2020 at 18:07, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
>
> So what was the actual rate of table creation? How long did it take to
> get to this state?
Currently we create tables at about 1.8 Hz. We drop them at the same
rate, almost all tables are short-lived (between 1d and 1h). We
started seeing this error roughly 1.5 years after we started using
this system, but in the beginning the rate was not as high as it is
now.
I don’t have all historical occurrences at hand, but in the past 14
days, the error has occurred twice.
> What kernel/filesystem was used? What did lsof say?
We use kernel 4.15.0-1036-gcp and the file system is ext4. Lsof does
not currently list the file in the most recent error message, but I
don't know what the state was at the time of the error.
Best,
Ruud
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-01-22 17:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-01-21 13:04 High table creation rate results in “File exists” error Radek Slupik <radek@channable.com>
2020-01-21 13:52 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2020-01-22 17:30 ` Ruud van Asseldonk <ruud@channable.com>
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