[BUGS] Unlogged tables cannot be truncated twice
Wow, this one took a bit to narrow down. Here's the failing case: # create unlogged table foo (a text); CREATE TABLE # begin; BEGIN #* truncate table foo; TRUNCATE TABLE #* truncate table foo; ERROR: could not create file "base/19131/19183_init": File exists Very reproducible. The column types matter: if the only column is an INT, for example, the problem does not occur. -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 pgp7FCBOAEQDc.pgp Description: PGP signature
Re: [BUGS] Failed pgbench: setrandom invalid maximum number 0
> > but the problem seems to only occur in >= 8.4. But it's also Good news and bad news. The good news is that it started occuring on all versions of pgbench, which makes it more likely to be a problem with my system rather than pgbench (although that error message sure is inscrutable). The bad news is I had to reboot the box for other reasons and I cannot duplicate the issue. Yet. :) -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 pgpDnyhGQdLj3.pgp Description: PGP signature
Re: [BUGS] 9.1 plperlu bug with null rows in trigger hash
Excerpts from Alvaro Herrera's message of sáb may 28 01:06:42 -0400 2011: > Excerpts from Alex Hunsaker's message of vie may 27 12:14:25 -0400 2011: > > On Mon, May 23, 2011 at 20:08, Greg Sabino Mullane > > wrote: > > > On Mon, May 23, 2011 at 05:04:40PM -0600, Alex Hunsaker wrote: > > > ... > > >> Greg, can you confirm the attached fixes it for you? > > > > > > Yes, seems to have done the job, thank you. > > > > Thanks for testing! > > > > [ Does a little dance to try and attract a -commiter ] > > Okay, I'll handle it :-) Pushed, thanks. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Failed pgbench: setrandom invalid maximum number 0
> setrandom: invalid maximum number 0 Okay, nevermind, user error: some other process was deleting a row from pgbench_branches, and the pgbench.c happily set scale to 0 from the 'select count(*) from pgbench_branches' query inside of it, until it gives the totally not-helpful error seen above later on as it sets :scale to 0, multiplies the tpc_b default number of branches by zero, and then complains when the minimum number of branches (1) is less than the max allowed (0). Oddly enough, the code checks for the count(*) < 0 but not <= 0. -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 pgpsWYz5znX55.pgp Description: PGP signature
Re: [BUGS] Unlogged tables cannot be truncated twice
Excerpts from Greg Sabino Mullane's message of lun may 30 12:00:43 -0400 2011: > Wow, this one took a bit to narrow down. Here's the failing case: > > # create unlogged table foo (a text); > CREATE TABLE > # begin; > BEGIN > #* truncate table foo; > TRUNCATE TABLE > #* truncate table foo; > ERROR: could not create file "base/19131/19183_init": File exists > > Very reproducible. The column types matter: if the only column > is an INT, for example, the problem does not occur. So 19183 is the toast table OID? -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Unlogged tables cannot be truncated twice
On Monday, May 30, 2011 11:18:20 PM Alvaro Herrera wrote:
> Excerpts from Greg Sabino Mullane's message of lun may 30 12:00:43 -0400
2011:
> > Wow, this one took a bit to narrow down. Here's the failing case:
> >
> > # create unlogged table foo (a text);
> > CREATE TABLE
> > # begin;
> > BEGIN
> > #* truncate table foo;
> > TRUNCATE TABLE
> > #* truncate table foo;
> > ERROR: could not create file "base/19131/19183_init": File exists
> >
> > Very reproducible. The column types matter: if the only column
> > is an INT, for example, the problem does not occur.
>
> So 19183 is the toast table OID?
Nope. Its any index.
You can provoke it with:
begin;create unlogged table foo (a int primary key);truncate foo;rollback;
or
begin;create unlogged table foo (a text);truncate foo;rollback;
The problem is this tidbit from tablecmds.c's ExecuteTruncate:
/*
* Normally, we need a transaction-safe truncation here.
However, if
* the table was either created in the current (sub)transaction
or has
* a new relfilenode in the current (sub)transaction, then we
can just
* truncate it in-place, because a rollback would cause the
whole
* table or the current physical file to be thrown away anyway.
*/
if (rel->rd_createSubid == mySubid ||
rel->rd_newRelfilenodeSubid == mySubid)
{
/* Immediate, non-rollbackable truncation is OK */
heap_truncate_one_rel(rel);
}
in combination with index.c's index_build:
/*
* If this is an unlogged index, we need to write out an init fork for
it.
*/
if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED)
{
RegProcedure ambuildempty = indexRelation->rd_am->ambuildempty;
RelationOpenSmgr(indexRelation);
smgrcreate(indexRelation->rd_smgr, INIT_FORKNUM, false);
OidFunctionCall1(ambuildempty, PointerGetDatum(indexRelation));
}
Andres
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Unlogged tables cannot be truncated twice
2011/5/31 Andres Freund :
> On Monday, May 30, 2011 11:18:20 PM Alvaro Herrera wrote:
>> Excerpts from Greg Sabino Mullane's message of lun may 30 12:00:43 -0400
> 2011:
>> > Wow, this one took a bit to narrow down. Here's the failing case:
>> >
>> > # create unlogged table foo (a text);
>> > CREATE TABLE
>> > # begin;
>> > BEGIN
>> > #* truncate table foo;
>> > TRUNCATE TABLE
>> > #* truncate table foo;
>> > ERROR: could not create file "base/19131/19183_init": File exists
>> >
>> > Very reproducible. The column types matter: if the only column
>> > is an INT, for example, the problem does not occur.
>>
>> So 19183 is the toast table OID?
> Nope. Its any index.
>
> You can provoke it with:
> begin;create unlogged table foo (a int primary key);truncate foo;rollback;
> or
> begin;create unlogged table foo (a text);truncate foo;rollback;
>
>
> The problem is this tidbit from tablecmds.c's ExecuteTruncate:
>
> /*
> * Normally, we need a transaction-safe truncation here.
> However, if
> * the table was either created in the current
> (sub)transaction or has
> * a new relfilenode in the current (sub)transaction, then we
> can just
> * truncate it in-place, because a rollback would cause the
> whole
> * table or the current physical file to be thrown away anyway.
> */
> if (rel->rd_createSubid == mySubid ||
> rel->rd_newRelfilenodeSubid == mySubid)
> {
> /* Immediate, non-rollbackable truncation is OK */
> heap_truncate_one_rel(rel);
> }
>
> in combination with index.c's index_build:
>
>
> /*
> * If this is an unlogged index, we need to write out an init fork for
> it.
> */
> if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED)
> {
> RegProcedure ambuildempty = indexRelation->rd_am->ambuildempty;
>
> RelationOpenSmgr(indexRelation);
> smgrcreate(indexRelation->rd_smgr, INIT_FORKNUM, false);
> OidFunctionCall1(ambuildempty, PointerGetDatum(indexRelation));
> }
>
I remove my own explanations as we conclude on the same thing.
Attached is the fix by adding a (!reindex) in the index.c if().
It looks enough from early testing.
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index a0898e0..3564d52 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1743,7 +1743,7 @@ index_build(Relation heapRelation,
/*
* If this is an unlogged index, we need to write out an init fork for it.
*/
- if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED)
+ if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED && !isreindex)
{
RegProcedure ambuildempty = indexRelation->rd_am->ambuildempty;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Unlogged tables cannot be truncated twice
On Tuesday, May 31, 2011 01:56:05 AM Cédric Villemain wrote:
> I remove my own explanations as we conclude on the same thing.
> Attached is the fix by adding a (!reindex) in the index.c if().
Thats imo wrong because it will break a plain REINDEX?
I think one possible correct fix would be the attached:
Andres
From c22d067cfe233428631b52f7bed2d0013e7f4712 Mon Sep 17 00:00:00 2001
From: Andres Freund
Date: Tue, 31 May 2011 02:02:19 +0200
Subject: [PATCH] Fix file existing errors uppon recreation of an unlogged
tables indexes _init fork
The error could only occur if a relation got truncated that got a new
relfilenode in the same transaction before because in that case
heap_truncate_one_rel gets called instead of setting a new relfilenode
for all relevant relations.
To fix that drop the relations _init fork in
storage.c:RelationTruncate as the other forks are handled there as
well.
BEGIN;CREATE UNLOGGED TABLE foo (a int PRIMARY KEY);TRUNCATE foo; ROLLBACK;
---
src/backend/catalog/storage.c |6 ++
1 files changed, 6 insertions(+), 0 deletions(-)
diff --git a/src/backend/catalog/storage.c b/src/backend/catalog/storage.c
index 57987be..7da63ba 100644
--- a/src/backend/catalog/storage.c
+++ b/src/backend/catalog/storage.c
@@ -252,6 +252,7 @@ RelationTruncate(Relation rel, BlockNumber nblocks)
{
bool fsm;
bool vm;
+ bool initfork;
/* Open it at the smgr level if not already done */
RelationOpenSmgr(rel);
@@ -273,6 +274,11 @@ RelationTruncate(Relation rel, BlockNumber nblocks)
if (vm)
visibilitymap_truncate(rel, nblocks);
+ /* the init fork will be recreated in index_build */
+ initfork = smgrexists(rel->rd_smgr, INIT_FORKNUM);
+ if (initfork)
+ smgrdounlink(rel->rd_smgr, INIT_FORKNUM, false);
+
/*
* We WAL-log the truncation before actually truncating, which means
* trouble if the truncation fails. If we then crash, the WAL replay
--
1.7.5.rc1.16.g9db1.dirty
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Unlogged tables cannot be truncated twice
On Tuesday, May 31, 2011 02:14:00 AM Andres Freund wrote:
> On Tuesday, May 31, 2011 01:56:05 AM Cédric Villemain wrote:
> > I remove my own explanations as we conclude on the same thing.
> > Attached is the fix by adding a (!reindex) in the index.c if().
>
> Thats imo wrong because it will break a plain REINDEX?
>
> I think one possible correct fix would be the attached:
My version was wrong as well because it did not observe RelationTruncate's
nblocks argument. That function is used to "shorten" the relation in vacuum.
So dropping the init fork there is not a good idea.
So I think it is the simpler version of simply checking the existance of the
fork before creating is ok.
Andres
From bd7240ab8b331c261d6a5215bee906f8c63f0019 Mon Sep 17 00:00:00 2001
From: Andres Freund
Date: Tue, 31 May 2011 02:02:19 +0200
Subject: [PATCH] Fix file existing errors uppon recreation of an unlogged
tables indexes _init fork
The error could only occur if a relation got truncated that got a new
relfilenode in the same transaction before because in that case
heap_truncate_one_rel gets called instead of setting a new relfilenode
for all relevant relations.
To fix that simply check for the existance of the fork before recreating it.
To reproduce:
BEGIN;CREATE UNLOGGED TABLE foo (a int PRIMARY KEY);TRUNCATE foo; ROLLBACK;
---
src/backend/catalog/index.c |3 ++-
src/backend/catalog/storage.c |2 --
2 files changed, 2 insertions(+), 3 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index a0898e0..d833a09 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1743,7 +1743,8 @@ index_build(Relation heapRelation,
/*
* If this is an unlogged index, we need to write out an init fork for it.
*/
- if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED)
+ if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED
+ && !smgrexists(indexRelation->rd_smgr, FSM_FORKNUM))
{
RegProcedure ambuildempty = indexRelation->rd_am->ambuildempty;
diff --git a/src/backend/catalog/storage.c b/src/backend/catalog/storage.c
index 57987be..68a7b30 100644
--- a/src/backend/catalog/storage.c
+++ b/src/backend/catalog/storage.c
@@ -265,8 +265,6 @@ RelationTruncate(Relation rel, BlockNumber nblocks)
/* Truncate the FSM first if it exists */
fsm = smgrexists(rel->rd_smgr, FSM_FORKNUM);
- if (fsm)
- FreeSpaceMapTruncateRel(rel, nblocks);
/* Truncate the visibility map too if it exists. */
vm = smgrexists(rel->rd_smgr, VISIBILITYMAP_FORKNUM);
--
1.7.5.rc1.16.g9db1.dirty
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Unlogged tables cannot be truncated twice
On Tuesday, May 31, 2011 02:35:58 AM Andres Freund wrote:
> On Tuesday, May 31, 2011 02:14:00 AM Andres Freund wrote:
> > On Tuesday, May 31, 2011 01:56:05 AM Cédric Villemain wrote:
> > > I remove my own explanations as we conclude on the same thing.
> > > Attached is the fix by adding a (!reindex) in the index.c if().
> >
> > Thats imo wrong because it will break a plain REINDEX?
>
> > I think one possible correct fix would be the attached:
> My version was wrong as well because it did not observe RelationTruncate's
> nblocks argument. That function is used to "shorten" the relation in
> vacuum. So dropping the init fork there is not a good idea.
>
> So I think it is the simpler version of simply checking the existance of
> the fork before creating is ok.
Gna. gnargl. Coffe. Bed. ;)
There was an accidental hunk I added while removing some whitespace. That
would not have been good on a real commit.
Argh.
From 1dbef0a907ec5e87da61a7ffb505ba082f2e11f8 Mon Sep 17 00:00:00 2001
From: Andres Freund
Date: Tue, 31 May 2011 02:02:19 +0200
Subject: [PATCH] Fix file existing errors uppon recreation of an unlogged
tables indexes _init fork
The error could only occur if a relation got truncated that got a new
relfilenode in the same transaction before because in that case
heap_truncate_one_rel gets called instead of setting a new relfilenode
for all relevant relations.
To fix that simply check for the existance of the fork before recreating it.
To reproduce:
BEGIN;CREATE UNLOGGED TABLE foo (a int PRIMARY KEY);TRUNCATE foo; ROLLBACK;
---
src/backend/catalog/index.c |3 ++-
1 files changed, 2 insertions(+), 1 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index a0898e0..d833a09 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1743,7 +1743,8 @@ index_build(Relation heapRelation,
/*
* If this is an unlogged index, we need to write out an init fork for it.
*/
- if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED)
+ if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED
+ && !smgrexists(indexRelation->rd_smgr, FSM_FORKNUM))
{
RegProcedure ambuildempty = indexRelation->rd_am->ambuildempty;
--
1.7.5.rc1.16.g9db1.dirty
From 1dbef0a907ec5e87da61a7ffb505ba082f2e11f8 Mon Sep 17 00:00:00 2001
From: Andres Freund
Date: Tue, 31 May 2011 02:02:19 +0200
Subject: [PATCH] Fix file existing errors uppon recreation of an unlogged
tables indexes _init fork
The error could only occur if a relation got truncated that got a new
relfilenode in the same transaction before because in that case
heap_truncate_one_rel gets called instead of setting a new relfilenode
for all relevant relations.
To fix that simply check for the existance of the fork before recreating it.
To reproduce:
BEGIN;CREATE UNLOGGED TABLE foo (a int PRIMARY KEY);TRUNCATE foo; ROLLBACK;
---
src/backend/catalog/index.c |3 ++-
1 files changed, 2 insertions(+), 1 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index a0898e0..d833a09 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1743,7 +1743,8 @@ index_build(Relation heapRelation,
/*
* If this is an unlogged index, we need to write out an init fork for it.
*/
- if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED)
+ if (heapRelation->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED
+ && !smgrexists(indexRelation->rd_smgr, FSM_FORKNUM))
{
RegProcedure ambuildempty = indexRelation->rd_am->ambuildempty;
--
1.7.5.rc1.16.g9db1.dirty
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Unlogged tables cannot be truncated twice
Excerpts from Andres Freund's message of lun may 30 20:47:49 -0400 2011: > On Tuesday, May 31, 2011 02:35:58 AM Andres Freund wrote: > > On Tuesday, May 31, 2011 02:14:00 AM Andres Freund wrote: > > > On Tuesday, May 31, 2011 01:56:05 AM Cédric Villemain wrote: > > > > I remove my own explanations as we conclude on the same thing. > > > > Attached is the fix by adding a (!reindex) in the index.c if(). > > > > > > Thats imo wrong because it will break a plain REINDEX? > > > > > I think one possible correct fix would be the attached: > > My version was wrong as well because it did not observe RelationTruncate's > > nblocks argument. That function is used to "shorten" the relation in > > vacuum. So dropping the init fork there is not a good idea. > > > > So I think it is the simpler version of simply checking the existance of > > the fork before creating is ok. Hmm, I wonder if what we should be doing here is observe isreindex in index_build to avoid creating the init fork. Doing smgr access at that level seems wrong. > Gna. gnargl. Coffe. Bed. ;) > > There was an accidental hunk I added while removing some whitespace. That > would not have been good on a real commit. > > Argh. Hah :-) -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] 9.1 plperlu bug with null rows in trigger hash
On Mon, May 30, 2011 at 11:02, Alvaro Herrera wrote: > Excerpts from Alvaro Herrera's message of sáb may 28 01:06:42 -0400 2011: >> Excerpts from Alex Hunsaker's message of vie may 27 12:14:25 -0400 2011: >> > On Mon, May 23, 2011 at 20:08, Greg Sabino Mullane >> > wrote: >> > > On Mon, May 23, 2011 at 05:04:40PM -0600, Alex Hunsaker wrote: >> > > ... >> > >> Greg, can you confirm the attached fixes it for you? >> > > >> > > Yes, seems to have done the job, thank you. >> > >> > Thanks for testing! >> > >> > [ Does a little dance to try and attract a -commiter ] >> >> Okay, I'll handle it :-) > > Pushed, thanks. Great thanks! -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
