Thread: Tablespace limit feature

Hi,

Can PostgreSQL's tablespace limit space in MB? Or exists another way to limit space in a table of a tablespace??

Thanks and Regards

On 11/04/2014 07:05 AM, Alejandro Carrillo wrote:
> Hi,
>
>
> Can PostgreSQL's tablespace limit space in MB? Or exists another way to
> limit space in a table of a tablespace??

Not that I know of.

What exactly is the issue you are facing?

Maybe there is another way to achieve what you want.

>
>
> Thanks and Regards
>


--
Adrian Klaver
adrian.klaver@aklaver.com

* Alejandro Carrillo (fasterzip@yahoo.es) wrote:
> Can PostgreSQL's tablespace limit space in MB? Or exists another way to limit space in a table of a tablespace??

You can set up quotas on the underlying filesystem, but that will limit
the entire tablespace.

Having quotas and limits for users is something which I've wanted for a
long time, but it's not likely to happen any time particularly soon..

    Thanks,

        Stephen

Le 4 nov. 2014 16:29, "Stephen Frost" <sfrost@snowman.net> a écrit :
>
> * Alejandro Carrillo (fasterzip@yahoo.es) wrote:
> > Can PostgreSQL's tablespace limit space in MB? Or exists another way to limit space in a table of a tablespace??
>
> You can set up quotas on the underlying filesystem, but that will limit
> the entire tablespace.
>
> Having quotas and limits for users is something which I've wanted for a
> long time, but it's not likely to happen any time particularly soon..
>

Lack of time or technical issue? For the latter, may I ask which issue you see? (I might be interesting in pursuing this)

* Guillaume Lelarge (guillaume@lelarge.info) wrote:
> Le 4 nov. 2014 16:29, "Stephen Frost" <sfrost@snowman.net> a écrit :
> > Having quotas and limits for users is something which I've wanted for a
> > long time, but it's not likely to happen any time particularly soon..
>
> Lack of time or technical issue? For the latter, may I ask which issue you
> see? (I might be interesting in pursuing this)

Lack of time, but there's definitely a lot of questions about what the
implementation would actually look like and how it'd work..

    Thanks,

        Stephen

I need to limit the tablespace file because I need to control the hard disk space used in a tablespace. Anybody knows how to do this? Or if it this in the PostgreSQL roadmap?

Thanks

Le 4 nov. 2014 18:00, "Alejandro Carrillo" <fasterzip@yahoo.es> a écrit :
>
> I need to limit the tablespace file because I need to control the hard disk space used in a tablespace. Anybody knows how to do this? Or if it this in the PostgreSQL roadmap?
>

Well, Stephen already answered this. There's no way yet to do this with PostgreSQL. You might get something like this with filesystem quotas.

And it's not on PostgreSQL roadmap, because there's no such thing. I don't know anyone working on this.

>
> Thanks
>
> El Martes 4 de noviembre de 2014 11:52, Guillaume Lelarge <guillaume@lelarge.info> escribió:
>>
>>
>>
>> Le 4 nov. 2014 16:29, "Stephen Frost" <sfrost@snowman.net> a écrit :
>> >
>> > * Alejandro Carrillo (fasterzip@yahoo.es) wrote:
>> > > Can PostgreSQL's tablespace limit space in MB? Or exists another way to limit space in a table of a tablespace??
>> >
>> > You can set up quotas on the underlying filesystem, but that will limit
>> > the entire tablespace.
>> >
>> > Having quotas and limits for users is something which I've wanted for a
>> > long time, but it's not likely to happen any time particularly soon..
>> >
>>
>> Lack of time or technical issue? For the latter, may I ask which issue you see? (I might be interesting in pursuing this)
>>
>>
>>

Le 4 nov. 2014 17:59, "Stephen Frost" <sfrost@snowman.net> a écrit :
>
> * Guillaume Lelarge (guillaume@lelarge.info) wrote:
> > Le 4 nov. 2014 16:29, "Stephen Frost" <sfrost@snowman.net> a écrit :
> > > Having quotas and limits for users is something which I've wanted for a
> > > long time, but it's not likely to happen any time particularly soon..
> >
> > Lack of time or technical issue? For the latter, may I ask which issue you
> > see? (I might be interesting in pursuing this)
>
> Lack of time, but there's definitely a lot of questions about what the
> implementation would actually look like and how it'd work..
>

Yeah. I guess there are a lot of questions yet to be answered. But AFAIUI, there's no real technical issue. More like discussions to agree on what it should do and how.

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

On Tue, 4 Nov 2014 17:00:54 +0000 (UTC), Alejandro Carrillo
(fasterzip@yahoo.es) wrote about "Re: [GENERAL] Tablespace limit
feature" (in
<1781968257.697829.1415120454794.JavaMail.yahoo@jws11113.mail.ir2.yahoo.com>):

> I need to limit the tablespace file because I need to control the
> hard disk space used in a tablespace. Anybody knows how to do this?
> Or if it this in the PostgreSQL roadmap?

The way I handle this is to allocate each tablespace its own logical
volume or partition and to mount it as /var/db/<tablespace>.  This
causes the filesystem itself to limit the amount of disk space the
tablespace can use.
- --
Regards,

Dave  [RLU #314465]
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
david.w.noon@googlemail.com (David W Noon)
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iEYEARECAAYFAlRZCzIACgkQogYgcI4W/5Q9PQCfXAnKwoRTOUmRZVJsjVn2ysZH
brgAnRudOLOUGSuIVmY7xiRqSZCWL6l/
=v2yc
-----END PGP SIGNATURE-----

Le 4 nov. 2014 18:00, "Alejandro Carrillo" <fasterzip@yahoo.es> a écrit :
>
> I need to limit the tablespace file because I need to control the hard disk space used in a tablespace. Anybody knows how to do this? Or if it this in the PostgreSQL roadmap?
>

Well, Stephen already answered this. There's no way yet to do this with PostgreSQL. You might get something like this with filesystem quotas.

And it's not on PostgreSQL roadmap, because there's no such thing. I don't know anyone working on this.

>
> Thanks
>
> El Martes 4 de noviembre de 2014 11:52, Guillaume Lelarge <guillaume@lelarge.info> escribió:
>>
>>
>>
>> Le 4 nov. 2014 16:29, "Stephen Frost" <sfrost@snowman.net> a écrit :
>> >
>> > * Alejandro Carrillo (fasterzip@yahoo.es) wrote:
>> > > Can PostgreSQL's tablespace limit space in MB? Or exists another way to limit space in a table of a tablespace??
>> >
>> > You can set up quotas on the underlying filesystem, but that will limit
>> > the entire tablespace.
>> >
>> > Having quotas and limits for users is something which I've wanted for a
>> > long time, but it's not likely to happen any time particularly soon..
>> >
>>
>> Lack of time or technical issue? For the latter, may I ask which issue you see? (I might be interesting in pursuing this)
>>
>>
>>

* Guillaume Lelarge (guillaume@lelarge.info) wrote:
> Yeah. I guess there are a lot of questions yet to be answered. But AFAIUI,
> there's no real technical issue. More like discussions to agree on what it
> should do and how.

Didn't mean to imply there was some specific technical issue.  We have
other limits (temp_file_limit), after all.  I think the bigger issue
will be convincing others that it's a useful enough feature to warrent
the infrastucture to support it (a new catalog table would be needed,
for one thing).  I'm all for it, but others may not be.

    Thanks,

        Stephen

On 11/4/2014 9:00 AM, Alejandro Carrillo wrote:
> I need to limit the tablespace file because I need to control the hard
> disk space used in a tablespace

so what happens if a query does something that violates the quota?

I can see one difficulty in implementation would be just keeping track
of how much space is used in a tablespace, thats not done right now at
all as far as I know.   every time a new block is allocated, a
tablespace size catalog table would have to be updated.... and oh no,
what happens if THAT table grows and overflows the quota of the default
tablespace where the catalog is maintained?



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

* John R Pierce (pierce@hogranch.com) wrote:
> On 11/4/2014 9:00 AM, Alejandro Carrillo wrote:
> >I need to limit the tablespace file because I need to control the
> >hard disk space used in a tablespace
>
> so what happens if a query does something that violates the quota?

It gets cancelled.  See temp_file_limit.

> I can see one difficulty in implementation would be just keeping
> track of how much space is used in a tablespace, thats not done
> right now at all as far as I know.   every time a new block is
> allocated, a tablespace size catalog table would have to be
> updated.... and oh no, what happens if THAT table grows and
> overflows the quota of the default tablespace where the catalog is
> maintained?

There are a bunch of questions, but I doubt we'd apply the quota to
system catalogs regardless.  That does lead to an interesting question
about quotas for number of tables, or indexes, or whatever, but I tend
to doubt that's as much of an issue.  Such a quota isn't to address
individuals who are actively attempting to attack the system (there's
lots of ways to cause problems if you've got SQL-level access) but
rather just to manage space limitations among different individuals
using the system.

    Thanks,

        Stephen

But This feature is very necessary to control the amount space expensed by a postgresql user or tablespace. This feature could be used in PostgreSQL Sharing Hosting, see: Shared Database Hosting - PostgreSQL wiki

Alejandro Carrillo escribió:
>
> But This feature is very necessary to control the amount space expensed by a postgresql user or tablespace. This
featurecould be used in PostgreSQL Sharing Hosting, see: Shared Database Hosting - PostgreSQL wiki 

Sure.  Rub hits the road when it comes to actually implementing this,
though -- the issue of enforcing the limit without creating a bottleneck
to table extension is not trivial to overcome, as I recall.  Right now
it might not matter tremendously much given that we only extending a
page at a time, but there's also been talk about extending in larger
chunks and that could become a problem.

If someone were to indulge in actual thinking and less handwaving, I'm
sure it would be welcome.

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

On Tue, 4 Nov 2014 18:43:38 +0000
Brent Wood <Brent.Wood@niwa.co.nz> wrote:

>
> Looking at the behaviour of char & varchar types, there seems to be an issue. Can anyone explain this behaviour? Is
therea bug of some sort? 
>
> According to the docs http://www.postgresql.org/docs/9.3/static/datatype-character.html)(:
> " If the string to be stored is shorter than the declared length, values of type character will be space-padded;
valuesof type character varying will simply store the shorter string." 
>
> Yet chars are not being padded, in fact they lose trailing spaces which are retained by varchars. They also return
length()'sless than the defined length... which should not be the case for a padded string as defined in the
documentation.
>
> fish=# create table test(var3 varchar(3),cha3 char(3));
> CREATE TABLE
> fish=# insert into test values('1','1');
> INSERT 0 1
> fish=# insert into test values('2 ','2 '); -- one space
> INSERT 0 1
> fish=# insert into test values('3  ','3  '); --two spaces
> INSERT 0 1
> fish=# select var3||':' as var3, cha3||':' as char3 from test;
> var3 | char3
> ------+-------
> 1:   | 1:
> 2 :  | 2:
> 3  : | 3:
> (3 rows)
> test=# select length(var3) as v_lgth, length(cha3) as c_length from test;
> v_lgth | c_length
> --------+----------
>      1 |        1
>      2 |        1
>      3 |        1
>
> So, in summary, varchar stores whatever feed to it and keeps trailing spaces to max length, char type will trim off
trailingspaces, and stor a string shorter than the specified length.. 

Your conclusion is wrong. The spaces _are_stored_. You've missed some
possibilities.

In all of your examples above, the || operator casts the char to varchar before
executing. The _cast_ from char to varchar is what trims the spaces.

You can see this visually with psql with something like this:

db=# select '3'::char(33);
              bpchar
-----------------------------------
 3
(1 row)

db=# select '3'::char(33)::varchar;
 varchar
---------
 3
(1 row)

I seem to remember discussion about this actually being correct behavior per the
SQL standard, but I could be wrong on this count. Quite frankly, I don't see any
reason for anyone using char any more. If I had to guess, I would guess that char
is in the standard because at the time of creation there were systems that could
heavily optimize access to fix-width fields, and that it's still in the standard
becuase nobody is sure how to clean cruft out of the standard.

--
Bill Moran
I need your help to succeed:
http://gamesbybill.com

On 11/04/2014 10:43 AM, Brent Wood wrote:
>
> Looking at the behaviour of char & varchar types, there seems to be an
> issue. Can anyone explain this behaviour? Is there a bug of some sort?
>
> According to the docs
> http://www.postgresql.org/docs/9.3/static/datatype-character.html)(:
> " If the string to be stored is shorter than the declared length, values
> of type character will be space-padded; values of type character varying
> will simply store the shorter string."
>
> Yet chars are not being padded, in fact they lose trailing spaces which
> are retained by varchars. They also return length()'s less than the
> defined length... which should not be the case for a padded string as
> defined in the documentation.
>
> fish=# create table test(var3 varchar(3),cha3 char(3));
> CREATE TABLE
> fish=# insert into test values('1','1');
> INSERT 0 1
> fish=# insert into test values('2 ','2 '); -- one space
> INSERT 0 1
> fish=# insert into test values('3  ','3  '); --two spaces
> INSERT 0 1
> fish=# select var3||':' as var3, cha3||':' as char3 from test;
> var3 | char3
> ------+-------
> 1:   | 1:
> 2 :  | 2:
> 3  : | 3:
> (3 rows)
> test=# select length(var3) as v_lgth, length(cha3) as c_length from test;
> v_lgth | c_length
> --------+----------
>       1 |        1
>       2 |        1
>       3 |        1
>
> So, in summary, varchar stores whatever feed to it and keeps trailing
> spaces to max length, char type will trim off trailing spaces, and stor
> a string shorter than the specified length..

I believe what you are seeing is explained here:

http://www.postgresql.org/docs/9.3/interactive/datatype-character.html

"Values of type character are physically padded with spaces to the
specified width n, and are stored and displayed that way. However, the
padding spaces are treated as semantically insignificant. Trailing
spaces are disregarded when comparing two values of type character, and
they will be removed when converting a character value to one of the
other string types. Note that trailing spaces are semantically
significant in character varying and text values, and when using pattern
matching, e.g. LIKE, regular expressions."

>
> Brent Wood
>
> Programme leader: Environmental Information Delivery
> NIWA
> DDI:  +64 (4) 3860529
>
> Brent Wood
> Principal Technician - GIS and Spatial Data Management
> Programme Leader - Environmental Information Delivery
>
> +64-4-386-0529| 301 Evans Bay Parade, Greta Point, Wellington|
> www.niwa.co.nz <http://www.niwa.co.nz>
>
> NIWA <http://www.niwa.co.nz>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/4/14, 2:10 PM, Alvaro Herrera wrote:
> Alejandro Carrillo escribió:
>> >
>> >But This feature is very necessary to control the amount space expensed by a postgresql user or tablespace. This
featurecould be used in PostgreSQL Sharing Hosting, see: Shared Database Hosting - PostgreSQL wiki 
> Sure.  Rub hits the road when it comes to actually implementing this,
> though -- the issue of enforcing the limit without creating a bottleneck
> to table extension is not trivial to overcome, as I recall.

And index extension. And visibility map extension. And, and, and.

It's definitely a non-trivial problem to solve, and unfortunately there are extremely few hosting environment providers
activein the community to even ask for the kinds of features they need, which means this just doesn't get much
attention.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

* Jim Nasby (Jim.Nasby@BlueTreble.com) wrote:
> On 11/4/14, 2:10 PM, Alvaro Herrera wrote:
> >Alejandro Carrillo escribió:
> >>>
> >>>But This feature is very necessary to control the amount space expensed by a postgresql user or tablespace. This
featurecould be used in PostgreSQL Sharing Hosting, see: Shared Database Hosting - PostgreSQL wiki 
> >Sure.  Rub hits the road when it comes to actually implementing this,
> >though -- the issue of enforcing the limit without creating a bottleneck
> >to table extension is not trivial to overcome, as I recall.
>
> And index extension. And visibility map extension. And, and, and.

Indexes I could agree with.  I'm not sure that the VM would be
worthwhile to include explicitly; as its size is dictated directly by
the number of pages in the heap, we might be able to include it
implicitly.  There are definitely big questions around how to track
space used and how to figure out if we've reached the limit, in an
efficient and correct way.

> It's definitely a non-trivial problem to solve, and unfortunately there are extremely few hosting environment
providersactive in the community to even ask for the kinds of features they need, which means this just doesn't get
muchattention. 

Agreed.  There are also larger problems (ran into yet another case of
"wait, roles aren't database-specific?!" just today..) than the quota
issue for hosting providers.

    Thanks,

        Stephen