Re: Tablespace storage bug...

Thanks for the reply...

This seems odd default behavior. A person coming from outside
Postgres-land would expect that if you create a table using a tablespace,
all the storage for that table would go to the tablespace, and not have
bits of the table stored elsewhere. Sure, a lawyerly reading of the gory
details of the CREATE TABLE statement in the manual may lead one to figure
this out - or not - but it's still pretty weird behavior, and different
from behavior of other db's.

I'd guess that this won't change (25 years of existing user scripts
depending on this behavior, etc), but at least put something online that
says that if you don't specifically declare a tablespace for your index
storage, the PK will be stored in the default tablespace associated with
the db the table is created in. Something like that may have saved me a
few hours of futzing with all the other things that cause a db to randomly
eat disk space :)

Anyway, thanks again for the reply, and it's good to see PG take off after
having worked on it a lot 30+ years ago :)

Greg

On Wed, Dec 4, 2024 at 5:09 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Wednesday, December 4, 2024, Greg Kemnitz <gjkemnitz(at)gmail(dot)com> wrote:
>
>> In PG version 16.6.1.pgdg24.04+1 on Ubuntu:
>>
>> CREATE TABLE xxx (a int, b int, PRIMARY KEY (a)) TABLESPACE xyz;
>>
>> will store the table's base storage in xyz, but will store the table's
>> PRIMARY KEY storage in pg_default.
>>
>> This seems a weird behavior and caused a load of a six billion row table
>> to crash as the / partition unexpectedly ran out of space.
>>
>> Workaround:
>>
>
> Read the docs for the “using index tablespace” clause.
>
> https://www.postgresql.org/docs/current/sql-createtable.html
>
> David J.
>
>