How about a proper TEMPORARY TABLESPACE?

* Matheus de Oliveira (matioli.matheus@gmail.com) wrote:

Hi Hackers,

Having read only the subject- +1 from me on the idea. Maybe +1000.

I was facing a situation were we wanted to set temp_tablespaces to a
tablespace on a ephemeral disk (yes, it is AWS ephemeral disk), and I know
many users have faced the same situation. Although it seems safe to create
a tablespace on ephemeral disks if you use it to store only temporary files
(either created by queries or temp tables), PostgreSQL does not have such
information, and can't safely prevent a careless user of creating a
non-temporary relation on this tablespace.

Right.

So, what you guys think about letting PG know somehow that a tablespace is
temporary?

PG would need to enforce that it's only used for temporary objects as
well, of course.. Or at least, that was my thinking on this.

I have took some small time to make a PoC just to see if that is doable.
And so I did a new syntax like:

CREATE TABLESPACE spcname [TEMP | TEMPORARY] LOCATION ...

So, if TEMP or TEMPORARY is present, I mark a new column at pg_tablespace
as true. On every table creation or moving to a new tablespace, I just
check this, and fails if the tablespace is "temporary" but the
"relpersistence" says the table is not.

Not sure about that specific syntax (don't we have SET options now?) but
I do like the general idea.

The other part is, every time some query or relation is asked to be stored
on this tablespace, I create (on-demand) the PG_TEMP_FILES_DIR inside of it
(also if it is temporary).

The attached patch (and also on my Github, [1]), shows the PoC. For now,
I'm not worried about the code quality, there are yet a lot of work to be
done there (like ALTER TABLESPACE, better testing, use relcache, etc...),
and it is my first hacking on PG (so I'm a newbie). But I'd like to hear
from you guys if such feature is desirable and if I could starting working
on that for real. Also some thoughts about better way of implementing it.

Yeah, +1 here and it should go into an appropriate commitfest to get a
proper review. This would be *really* nice to have.

Thanks,

Stephen

On Wed, Jun 18, 2014 at 9:00 AM, Stephen Frost <sfrost@snowman.net> wrote:

I have took some small time to make a PoC just to see if that is doable.
And so I did a new syntax like:

CREATE TABLESPACE spcname [TEMP | TEMPORARY] LOCATION ...

So, if TEMP or TEMPORARY is present, I mark a new column at

pg_tablespace

as true. On every table creation or moving to a new tablespace, I just
check this, and fails if the tablespace is "temporary" but the
"relpersistence" says the table is not.

Not sure about that specific syntax (don't we have SET options now?) but
I do like the general idea.

Maybe something like that:

CREATE TABLESPACE spcname LOCATION '/foo/bar' WITH (only_temp_relations =
true);

Have in mind you must take care if you use ALTER TABLESPACE spcname SET
(...) to guarantee that exists only temp objects stored in the target
tablespace, and if exists a regular object you must throw an exception.

Makes sense?

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

* Fabrízio de Royes Mello (fabriziomello@gmail.com) wrote:

On Wed, Jun 18, 2014 at 9:00 AM, Stephen Frost <sfrost@snowman.net> wrote:

Not sure about that specific syntax (don't we have SET options now?) but
I do like the general idea.

Maybe something like that:

CREATE TABLESPACE spcname LOCATION '/foo/bar' WITH (only_temp_relations =
true);

Yeah, that's more along the lines of what I was thinking.

Have in mind you must take care if you use ALTER TABLESPACE spcname SET
(...) to guarantee that exists only temp objects stored in the target
tablespace, and if exists a regular object you must throw an exception.

Makes sense?

Yes. I'd definitely like to see an ALTER TABLESPACE option, with an
ERROR that lists out all of the non-temporary objects which are found
(and lists any other databases which have objects in those
tablespaces..). That would allow administrators who have existing
notionally temporary-only tablespaces to go clean things up to make them
actually temporary-only.

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

Yes. I'd definitely like to see an ALTER TABLESPACE option, with an
ERROR that lists out all of the non-temporary objects which are found
(and lists any other databases which have objects in those
tablespaces..). That would allow administrators who have existing
notionally temporary-only tablespaces to go clean things up to make them
actually temporary-only.

That seems just about impossible from a concurrency standpoint
(ie, what if someone is creating a new table in the tablespace
concurrently with your check? Possibly in a different database?)

I would certainly suggest that the first version of the patch not
undertake to allow this property to be ALTERed; the cost-benefit
ratio isn't there IMO.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jun 18, 2014 at 1:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Stephen Frost <sfrost@snowman.net> writes:

Yes. I'd definitely like to see an ALTER TABLESPACE option, with an
ERROR that lists out all of the non-temporary objects which are found
(and lists any other databases which have objects in those
tablespaces..). That would allow administrators who have existing
notionally temporary-only tablespaces to go clean things up to make them
actually temporary-only.

That seems just about impossible from a concurrency standpoint
(ie, what if someone is creating a new table in the tablespace
concurrently with your check? Possibly in a different database?)

You are correct, I completely forgot that CREATE TABLESPACE is not
transaction safe.

I would certainly suggest that the first version of the patch not
undertake to allow this property to be ALTERed; the cost-benefit
ratio isn't there IMO.

+1

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

Yes. I'd definitely like to see an ALTER TABLESPACE option, with an
ERROR that lists out all of the non-temporary objects which are found
(and lists any other databases which have objects in those
tablespaces..). That would allow administrators who have existing
notionally temporary-only tablespaces to go clean things up to make them
actually temporary-only.

That seems just about impossible from a concurrency standpoint
(ie, what if someone is creating a new table in the tablespace
concurrently with your check? Possibly in a different database?)

Yeah, that's definitely an annoying complexity.

I would certainly suggest that the first version of the patch not
undertake to allow this property to be ALTERed; the cost-benefit
ratio isn't there IMO.

I suppose scheduling downtime to do the check manually across all
databases, then drop and recreate the tablespace, would work. As
someone who's working with a couple of these cases, it'd be awful nice
if there was a way PG would handle it for me.

Thanks,

Stephen

Stephen Frost wrote:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

Yes. I'd definitely like to see an ALTER TABLESPACE option, with an
ERROR that lists out all of the non-temporary objects which are found
(and lists any other databases which have objects in those
tablespaces..). That would allow administrators who have existing
notionally temporary-only tablespaces to go clean things up to make them
actually temporary-only.

I would certainly suggest that the first version of the patch not
undertake to allow this property to be ALTERed; the cost-benefit
ratio isn't there IMO.

I suppose scheduling downtime to do the check manually across all
databases, then drop and recreate the tablespace, would work. As
someone who's working with a couple of these cases, it'd be awful nice
if there was a way PG would handle it for me.

I wonder if some form of NOT VALID marking could be useful here. Of
course, this is not a constraint. But a mechanism of a similar spirit
seems apropos. It seems reasonable to leave such a thing for future
improvement.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jun 18, 2014 at 4:53 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

Stephen Frost wrote:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

Yes. I'd definitely like to see an ALTER TABLESPACE option, with an
ERROR that lists out all of the non-temporary objects which are

found

(and lists any other databases which have objects in those
tablespaces..). That would allow administrators who have existing
notionally temporary-only tablespaces to go clean things up to make

them

actually temporary-only.

I would certainly suggest that the first version of the patch not
undertake to allow this property to be ALTERed; the cost-benefit
ratio isn't there IMO.

I suppose scheduling downtime to do the check manually across all
databases, then drop and recreate the tablespace, would work. As
someone who's working with a couple of these cases, it'd be awful nice
if there was a way PG would handle it for me.

I wonder if some form of NOT VALID marking could be useful here. Of
course, this is not a constraint. But a mechanism of a similar spirit
seems apropos. It seems reasonable to leave such a thing for future
improvement.

+1

Then, to summarize Matheus must do:
* use an option instead of change the syntax and catalog to indicate that a
tablespace is used to store temp objects
* throw an exception if we try ALTER the option "only_temp_relations"
* add regression tests
* add documentation

And, of course, register to the next open commitfest [1]https://commitfest.postgresql.org/ to get detailed
feedback and review.

Regards,

[1]: https://commitfest.postgresql.org/

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

I was going to answer each message, but Fabrízio summarized everything so
far really nicely. Thanks Fabrízio.

On Wed, Jun 18, 2014 at 5:25 PM, Fabrízio de Royes Mello <
fabriziomello@gmail.com> wrote:

Then, to summarize Matheus must do:
* use an option instead of change the syntax and catalog to indicate that
a tablespace is used to store temp objects

Yes. I myself wasn't sure TEMPORARY syntax would be good, but I would just
like to hear more about. Storing as an options seems nice to me.

* throw an exception if we try ALTER the option "only_temp_relations"

I think we should postpone the ALTER option, perhaps as another patch.
Wasn't that what was decided?

I'll investigate the options better before going this route. Let me work on
CREATE first.

* add regression tests
* add documentation

And, of course, register to the next open commitfest [1] to get detailed
feedback and review.

Noted. It will be on the next commitfest. Just knowing some people do want
this make me more comfortable on working better.

Best regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

On Wed, Jun 18, 2014 at 10:39 PM, Matheus de Oliveira <
matioli.matheus@gmail.com> wrote:

I was going to answer each message, but Fabrízio summarized everything so

far really nicely. Thanks Fabrízio.

You're welcome. :-)

On Wed, Jun 18, 2014 at 5:25 PM, Fabrízio de Royes Mello <

fabriziomello@gmail.com> wrote:

Then, to summarize Matheus must do:
* use an option instead of change the syntax and catalog to indicate

that a tablespace is used to store temp objects

Yes. I myself wasn't sure TEMPORARY syntax would be good, but I would

just like to hear more about. Storing as an options seems nice to me.

Are there somebody that reject this idea? Thoughts?

* throw an exception if we try ALTER the option "only_temp_relations"

I think we should postpone the ALTER option, perhaps as another patch.

Wasn't that what was decided?

Don't worry about that, we can help you. ;-)

I'll investigate the options better before going this route. Let me work

on CREATE first.

See this files:
- src/include/commands/tablespace.h (TableSpaceOpts struct at line 35)
- src/backend/access/common/reloptions.c (tablespace_reloptions at line
1333)

* add regression tests
* add documentation

And, of course, register to the next open commitfest [1] to get detailed

feedback and review.

Noted. It will be on the next commitfest. Just knowing some people do

want this make me more comfortable on working better.

Nice... I would like to be a reviewer.

Att,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 06/18/2014 08:00 PM, Stephen Frost wrote:

PG would need to enforce that it's only used for temporary objects
as well, of course.. Or at least, that was my thinking on this.

A way to put UNLOGGED objects in such a space and have them recovered
if they vanish would also be valuable, IMO.

Not necessarily in the same patch, I'd just rather keep it in mind so
any chosen design doesn't preclude adding that later.

- --
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJTpmsOAAoJELBXNkqjr+S2SNUH/A1zor2WUbQg3PTyD+6wyxlM
v6iMpH9zaeMWYyavIHM9N8a+h7hl9ajKDPMuK0vYLMs83W88LXI4T/t1iJlgZ3Po
WhyjjdNonChn5v1NEJncb4KgqmRQxyWY43rzgEgofSJQgjFbo8UAwoD8Xa32ghDu
4dx2ijYqYsx/hC1Gb1u/HbQ9rX+OkTA0DwljdUgPqPorW/mn5RVscz9Qo5p/W+XH
tdvpu9CKRi4AFPkshZOij+Mu3My863K1+k7sHUpRcsRGJT/AIO1V0wUQE2PuWTAV
5InU5vw/1C6CsYpH4v++XvpeTOnQ4QrMnllh99UTxV91UXN+jx/ENv1SYhBfYGE=
=JGYF
-----END PGP SIGNATURE-----

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 06/22/2014 01:35 PM, Craig Ringer wrote:

A way to put UNLOGGED objects in such a space and have them
recovered if they vanish would also be valuable, IMO.

By which I mean "re-created empty, as if after a crash".

- --
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJTpmuwAAoJELBXNkqjr+S284oIALPSZVCkqv/A6Oriu48n9LOm
KI0cnyiv+kJ8Kor8kDIbweM6DdaUaP0msQOj3vRh8t1GGldF1hmEo83IH/t+sx4t
3B2SUPmHQX5QHa27zmzCk8qAhYKYyUy/HOI0u2DxLm5fGtxpzCjyojo4k8+YkSgM
b17eDGrAzlxXJF9BiRj4SBITOxekdgml0yF6J0UPWk98N4cTaXXsMv5r/QBlyXOm
/3bdZ0rx0extLcuH+x9iUo7aXC4qCgFJ1ZEzp3xSVGVxU7VA3vQl1O1OR7GHRKJb
E3SqS6RiHpWqT2JxD1su/a5XblpF/d5/EfXNxSY7etdNBBarHF5Q6xCNDNard8Y=
=30iz
-----END PGP SIGNATURE-----

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Craig Ringer (craig@2ndquadrant.com) wrote:

On 06/18/2014 08:00 PM, Stephen Frost wrote:

PG would need to enforce that it's only used for temporary objects
as well, of course.. Or at least, that was my thinking on this.

A way to put UNLOGGED objects in such a space and have them recovered
if they vanish would also be valuable, IMO.

Interesting idea.

Not necessarily in the same patch, I'd just rather keep it in mind so
any chosen design doesn't preclude adding that later.

Perhaps we need a more complex definition than just "temporary
tablespace", as in, we should have a way for users to say "this
tablespace is allowed to have objects of type X, Y, Z"? I can see a
couple of ways to do that and I don't think it'd require much in the way
of changes to the current patch...

Thanks,

Stephen

On Wed, Jun 18, 2014 at 9:39 PM, Matheus de Oliveira
<matioli.matheus@gmail.com> wrote:

Then, to summarize Matheus must do:
* use an option instead of change the syntax and catalog to indicate that
a tablespace is used to store temp objects

Yes. I myself wasn't sure TEMPORARY syntax would be good, but I would just
like to hear more about. Storing as an options seems nice to me.

I kind of like the TEMPORARY syntax, by analogy with what we do for
tables. On the other hand, this situation isn't quite analogous: the
tablespace itself is permanent; it's only the objects inside the
tablespace that are temporary. Hmm.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jun 23, 2014 at 1:17 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Wed, Jun 18, 2014 at 9:39 PM, Matheus de Oliveira
<matioli.matheus@gmail.com> wrote:

Then, to summarize Matheus must do:
* use an option instead of change the syntax and catalog to indicate

that

a tablespace is used to store temp objects

Yes. I myself wasn't sure TEMPORARY syntax would be good, but I would

just

like to hear more about. Storing as an options seems nice to me.

I kind of like the TEMPORARY syntax, by analogy with what we do for
tables. On the other hand, this situation isn't quite analogous: the
tablespace itself is permanent; it's only the objects inside the
tablespace that are temporary. Hmm.

The suggested syntax is about "TEMP LOCATION" and not "TEMP TABLESPACE"...

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

On Sun, Jun 22, 2014 at 2:35 AM, Craig Ringer <craig@2ndquadrant.com> wrote:

A way to put UNLOGGED objects in such a space and have them recovered
if they vanish would also be valuable, IMO.

Not necessarily in the same patch, I'd just rather keep it in mind so
any chosen design doesn't preclude adding that later.

The idea is nice, but I think you should think more about it. Were would we
put the "init" files in this case? It surely can't be in the tablespace.

Best regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

Hi Hackers,

I have worked on that patch a little more. So now I have functional patch
(although still WIP) attached. The feature works as following:

- Added a boolean parameter "only_temp_files" to pg_tablespace.spcoptions;
- This parameter can be set to true only during CREATE TABLESPACE, not on
ALTER TABLESPACE (I have thought of ways of implementing the latter, and
I'd like to discuss it more latter);
- On the creation of relations, it is checked if it is a
temporary-tablespace, and an error occurs when it is and the relation is
not temporary (temp table or index on a temp table);
- When a temporary file (either relation file or sort/agg file) is created
inside a temporary-tablespace, the entire directories structure is created
on-demand (e.g. if pg_tblspc/<oid>/<TABLESPACE_VERSION_DIRECTORY> is
missing, it is created on demand) it is done on
OpenTemporaryFileInTablespace, at fd.c (I wonder if shouldn't we do that
for any tablespace) and on TablespaceCreateDbspace, at tablespace.c.

I still haven't change documentation, as I think I need some insights about
the changes. I have some more thoughts about the syntax and I still think
that "TEMP LOCATION" syntax is better suited for this patch. First because
of the nature of the changes I made, it seems more suitable to a column on
pg_tablespace rather than an option. Second because no ALTER is available
(so far) and I think it is odd to have an option that can't be changed.
Third, I think "TEMP" keyword is more clear and users can be more used to
it.

Thoughts?

I'm going to add the CF app entry next. Could I get some review now or
after discussion about how things are going (remember I'm a newbie on this,
so I'm a little lost)?

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

On 06/29/2014 02:19 AM, Matheus de Oliveira wrote:

Hi Hackers,

I have worked on that patch a little more. So now I have functional
patch (although still WIP) attached. The feature works as following:

- Added a boolean parameter "only_temp_files" to pg_tablespace.spcoptions;
- This parameter can be set to true only during CREATE TABLESPACE, not
on ALTER TABLESPACE (I have thought of ways of implementing the latter,
and I'd like to discuss it more latter);
- On the creation of relations, it is checked if it is a
temporary-tablespace, and an error occurs when it is and the relation is
not temporary (temp table or index on a temp table);
- When a temporary file (either relation file or sort/agg file) is
created inside a temporary-tablespace, the entire directories structure
is created on-demand (e.g. if
pg_tblspc/<oid>/<TABLESPACE_VERSION_DIRECTORY> is missing, it is created
on demand) it is done on OpenTemporaryFileInTablespace, at fd.c (I
wonder if shouldn't we do that for any tablespace) and on
TablespaceCreateDbspace, at tablespace.c.

Right now PostgreSQL appears to rely on the absence of the tablespace
directory as a flag to tell it "don't start up, something's badly wrong
here".

If the user creates the tablespace directory, it figures they at least
know what they're doing and carries on starting up with the empty
tablespace. It's treated as an admin statement - "I know it's gone, it's
not coming back, just carry on as best you can".

If Pg were to auto-create the directory, then if (say) a mount of a
tablespace dir failed at boot, Pg would still happily start up and might
create files in the tablespace, despite there being inaccessible
tables/indexes/etc on the not-mounted volume that's *supposed* to be the
tablespace storage. That'd create plenty of mess.

So no, I don't think Pg should auto-create the tablespace directory if
it's missing for non-temporary tablespaces. Not unless the current (less
than friendly) behaviour around lost tablespaces is replaced with
something like an `ignore_missing_tablespaces` or
`drop_missing_tablespaces` GUC - akin to our existing
`zero_missing_pages` recovery GUC.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jun 30, 2014 at 5:14 AM, Craig Ringer <craig@2ndquadrant.com> wrote:

Right now PostgreSQL appears to rely on the absence of the tablespace
directory as a flag to tell it "don't start up, something's badly wrong
here".

Well, in fact the behavior is just to give a LOG message:

LOG: could not open tablespace directory
"pg_tblspc/100607/PG_9.3_201306121": No such file or directory

But it starts fine. No?

I ask because I'm relying on that. My patch does not on startup, so in the
absence of the tablespace directory, the above LOG is shown even for
"temporary-tablespace", and it tries to create the directory when any
process tries to use it. As we don't have catalog access on startup, it
will require me to use some kind of _init fork for tablespace if we want it
to be re-created during startup. Should we bother about this?

If the user creates the tablespace directory, it figures they at least

know what they're doing and carries on starting up with the empty
tablespace. It's treated as an admin statement - "I know it's gone, it's
not coming back, just carry on as best you can".

Well, I think for a tablespace like that it is okay to create it on-demand
(note that I have done this only for "temp", not ordinary ones).

If Pg were to auto-create the directory, then if (say) a mount of a
tablespace dir failed at boot, Pg would still happily start up and might
create files in the tablespace, despite there being inaccessible
tables/indexes/etc on the not-mounted volume that's *supposed* to be the
tablespace storage. That'd create plenty of mess.

So no, I don't think Pg should auto-create the tablespace directory if
it's missing for non-temporary tablespaces.

Ok. I agree. Let's create only for temporary ones (as done by the patch
now).

Not unless the current (less
than friendly) behaviour around lost tablespaces is replaced with
something like an `ignore_missing_tablespaces` or
`drop_missing_tablespaces` GUC - akin to our existing
`zero_missing_pages` recovery GUC.

You meant `zero_damaged_pages`, I think. I don't think that is really
necessary in this case.

Anyway, I was thinking about some way to this tablespace to also allow
creation of non-temporary indexes, and after a crash we could mark such
indexes as `NOT VALID` (perhaps `zero_damaged_pages` could be of help
here?!). That should be part of another patch thought, and would require
more thoughts. But I think that would be a great improvement for some
environments, like those on AWS with their ephemeral SSDs.

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres