Re: four minor proposals for 9.5

2014-03-20 7:25 GMT+01:00 Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>:

> On 20/03/14 13:28, Josh Berkus wrote:
>
> 3. relation limit - possibility to set session limit for maximum size of
>>> relations. Any relation cannot be extended over this limit in session,
>>> when
>>> this value is higher than zero. Motivation - we use lot of queries like
>>> CREATE TABLE AS SELECT .. , and some very big results decreased a disk
>>> free
>>> space too much. It was high risk in our multi user environment.
>>> Motivation
>>> is similar like temp_files_limit.
>>>
>>
>> I'd think the size of the relation you were creating would be difficult
>> to measure. Also, would this apply to REINDEX/VACUUM FULL/ALTER? Or
>> just CREATE TABLE AS/SELECT INTO?
>>
>>
> Also I think this would probably only make sense for TEMPORARY tables -
> otherwise you can get this sort of thing going on:
>
> - you create a table and you have set a relation size limit
> - you commit and keep working
> - I add a whole lot of rows to your new table (taking it over the limit)
> - you go to add some more rows to this table...
>

you cannot to across session limit and is not important if you do inserts
more times or once.

This patch is very simple - it is only one safeguard against too low space
on disc in very dynamic multi user enironment

--- ./src/backend/storage/smgr/md.c 2014-02-26 17:29:36.864189192 +0100
***************
*** 27,32 ****
--- 27,33 ----
#include "storage/bufmgr.h"
#include "storage/relfilenode.h"
#include "storage/smgr.h"
+ #include "utils/guc.h"
#include "utils/hsearch.h"
#include "utils/memutils.h"
#include "pg_trace.h"
***************
*** 180,185 ****
--- 181,191 ----
static BlockNumber _mdnblocks(SMgrRelation reln, ForkNumber forknum,
MdfdVec *seg);

+ /*
+ * limits for relations size
+ */
+ int max_blocks;

/*
* mdinit() -- Initialize private state for magnetic disk storage
manager.
***************
*** 475,480 ****
--- 481,494 ----
Assert(blocknum >= mdnblocks(reln, forknum));
#endif

+ if (max_blocks != -1 && blocknum > (BlockNumber) max_blocks)
+ ereport(ERROR,
+ (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("cannot extend file beyond %u
blocks",
+ max_blocks),
+ errhint("Session file limit defined by
\"hard_relation_limit\" (%s) is over.",
+
GetConfigOptionByName("hard_relation_limit", NULL))));
+

>
> Should you now be stopped working? Does this feature need to track *who*
> added which chunks of a table (suspect very difficult to do sensibly)?
>
> Regards
>
> Mark
>