Thread: [ postfixadmin-Bugs-2986880 ] Quta's stored as integers, should be bigint.
Brought to you by:
christian_boltz,
gingerdog
From: SourceForge.net <no...@so...> - 2010-04-14 01:21:05
|
Bugs item #2986880, was opened at 2010-04-14 01:21 Message generated for change (Tracker Item Submitted) made by n0d3 You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=2986880&group_id=191583 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: Database Group: None Status: Open Resolution: None Priority: 5 Private: No Submitted By: oliver (n0d3) Assigned to: Nobody/Anonymous (nobody) Summary: Quta's stored as integers, should be bigint. Initial Comment: Quta's in the database may become to small soon. Currently quota's can be stored as any integer, and be multiplied externally. Thus having a quota of '10' could mean anything from 10 bytes, to 10GB depending on what multiplication factor is used in postfixadmin's config.inc.php. Postfix itself talks to the table via a select statement, that's just how it does that, and currently, with postfix the following query would be sent to postfix; postfix=> select quota from mailbox; quota ------- 10 (1 row) Doing the same external multiplication trick works, as long as it stays under 2^32, the maximum size for an integer; So for example: postfix=> select (quota *1024 *1024) from mailbox; ?column? ---------- 10485760 (1 row) Doesn't cause any issue really, so where is the problem? Well let's say this user is allowed to have some absurd quota, say 10GB. (Remember, Google already offers 1GB quotas and 2GB quotas exist for webmail providers. Having certain users with 10GB shouldn't be all that awkward. Trying a 10GB quota however fails horribly. postfix=> select (quota *1024 *1024 *1024) from mailbox; ERROR: integer out of range Changing the quota per user field from an Integer to a bigint solves the issue; ALTER TABLE mailbox ALTER quota TYPE bigint; postfix=> select (quota *1024 *1024 *1024) from mailbox; ?column? ------------- 10737418240 (1 row) In conclusion, this means quota's would have never worked if they where bigger then the type of the table, so using external multipliers would have only worked for anything not dependent on the SQL query (which postfix is). Actually, having an external multiplier is useless, though it allows for pretty printing. There could be many reasons actually been thought of to store the quota in bytes actually. Changing the type to bigint actually allows for absurd high quota's 9223372036854774784 or 8Exabyte on a per user level is maybe to high (even in a million years from now, I don't see anybody using 8Exabyte mailboxes) but could pose a problem, far far in the future for quota for domains or maxquota for all domains combined? So I would recommend, drop external multipliers, store quota's in bytes, pretty print them where needed and convert integers to bigints where needed (e.g. quota for per user, domain etc). ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=2986880&group_id=191583 |
From: SourceForge.net <no...@so...> - 2010-05-18 11:49:14
|
Bugs item #2986880, was opened at 2010-04-14 03:21 Message generated for change (Comment added) made by christian_boltz You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=2986880&group_id=191583 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: Database Group: None >Status: Closed >Resolution: Wont Fix Priority: 5 Private: No Submitted By: oliver (n0d3) Assigned to: Nobody/Anonymous (nobody) Summary: Quta's stored as integers, should be bigint. Initial Comment: Quta's in the database may become to small soon. Currently quota's can be stored as any integer, and be multiplied externally. Thus having a quota of '10' could mean anything from 10 bytes, to 10GB depending on what multiplication factor is used in postfixadmin's config.inc.php. Postfix itself talks to the table via a select statement, that's just how it does that, and currently, with postfix the following query would be sent to postfix; postfix=> select quota from mailbox; quota ------- 10 (1 row) Doing the same external multiplication trick works, as long as it stays under 2^32, the maximum size for an integer; So for example: postfix=> select (quota *1024 *1024) from mailbox; ?column? ---------- 10485760 (1 row) Doesn't cause any issue really, so where is the problem? Well let's say this user is allowed to have some absurd quota, say 10GB. (Remember, Google already offers 1GB quotas and 2GB quotas exist for webmail providers. Having certain users with 10GB shouldn't be all that awkward. Trying a 10GB quota however fails horribly. postfix=> select (quota *1024 *1024 *1024) from mailbox; ERROR: integer out of range Changing the quota per user field from an Integer to a bigint solves the issue; ALTER TABLE mailbox ALTER quota TYPE bigint; postfix=> select (quota *1024 *1024 *1024) from mailbox; ?column? ------------- 10737418240 (1 row) In conclusion, this means quota's would have never worked if they where bigger then the type of the table, so using external multipliers would have only worked for anything not dependent on the SQL query (which postfix is). Actually, having an external multiplier is useless, though it allows for pretty printing. There could be many reasons actually been thought of to store the quota in bytes actually. Changing the type to bigint actually allows for absurd high quota's 9223372036854774784 or 8Exabyte on a per user level is maybe to high (even in a million years from now, I don't see anybody using 8Exabyte mailboxes) but could pose a problem, far far in the future for quota for domains or maxquota for all domains combined? So I would recommend, drop external multipliers, store quota's in bytes, pretty print them where needed and convert integers to bigints where needed (e.g. quota for per user, domain etc). ---------------------------------------------------------------------- >Comment By: Christian Boltz (christian_boltz) Date: 2010-05-18 13:49 Message: As long as you use one of the usual multipication factors in config.inc.php ('1024000' or '1048576'), the mailbox quota is already stored in bytes. Also the quota fields are already bigint - IIRC already in the 2.3 release, for sure in SVN. Remaining issue: the domain-level quota ($CONF(maxquota)) is stored in MB instead of bytes. However, that's just used internally in postfixadmin, and changing it to bytes would probably cause more harm than good on upgrade. Therefore: - closing as "out of date" for the bigint field type because this is already implemented - closing as "wontfix" for changing domain-level maxquota to bytes instead of MB If you disagree or still have "small" integer quota fields with 2.3 or the upcoming 2.3.1 release (don't forget to run setup.php after upgrade), feel free to reopen this ticket ;-) ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=2986880&group_id=191583 |
From: SourceForge.net <no...@so...> - 2010-09-01 00:04:01
|
Bugs item #3057081, was opened at 2010-09-01 00:03 Message generated for change (Tracker Item Submitted) made by n0d3 You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=3057081&group_id=191583 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: Database Group: v2.3.1 Status: Open Resolution: None Priority: 5 Private: No Submitted By: oliver (n0d3) Assigned to: Nobody/Anonymous (nobody) Summary: Quta's stored as integers, should be bigint. Initial Comment: This is a copy (re-opening) of 2986880. I"m opening a new ticket because I can't seem to find an edit button. >From there: "If you disagree or still have "small" integer quota fields with 2.3 or the upcoming 2.3.1 release (don't forget to run setup.php after upgrade), feel free to reopen this ticket ;-)" I just did a fresh install of 2.3.2 and the issue is still not fixed (for pgsql). postfix_new=> \d mailbox; Table "public.mailbox" Column | Type | Modifiers ------------+--------------------------+---------------------------------------- username | character varying(255) | not null password | character varying(255) | not null default ''::character varying name | character varying(255) | not null default ''::character varying maildir | character varying(255) | not null default ''::character varying quota | integer | not null default 0 created | timestamp with time zone | default now() modified | timestamp with time zone | default now() active | boolean | not null default true domain | character varying(255) | local_part | character varying(255) | not null Indexes: "mailbox_key" PRIMARY KEY, btree (username) "mailbox_domain_idx" btree (domain) "mailbox_username_active" btree (username, active) Foreign-key constraints: "mailbox_domain_fkey1" FOREIGN KEY (domain) REFERENCES domain(domain) Adding a user with anything over a 4GiB mailbox gives the following error: DEBUG INFORMATION: Invalid query: ERROR: value "8589934592" is out of range for type integer LINE 1: ...','test','test.tld/te...@te...d/','test.tld','858993459... ^ ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=3057081&group_id=191583 |
From: SourceForge.net <no...@so...> - 2011-02-17 21:36:56
|
Bugs item #3057081, was opened at 2010-09-01 02:03 Message generated for change (Comment added) made by christian_boltz You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=3057081&group_id=191583 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: Database Group: v2.3.1 Status: Open Resolution: None Priority: 5 Private: No Submitted By: oliver (n0d3) Assigned to: Nobody/Anonymous (nobody) Summary: Quta's stored as integers, should be bigint. Initial Comment: This is a copy (re-opening) of 2986880. I"m opening a new ticket because I can't seem to find an edit button. >From there: "If you disagree or still have "small" integer quota fields with 2.3 or the upcoming 2.3.1 release (don't forget to run setup.php after upgrade), feel free to reopen this ticket ;-)" I just did a fresh install of 2.3.2 and the issue is still not fixed (for pgsql). postfix_new=> \d mailbox; Table "public.mailbox" Column | Type | Modifiers ------------+--------------------------+---------------------------------------- username | character varying(255) | not null password | character varying(255) | not null default ''::character varying name | character varying(255) | not null default ''::character varying maildir | character varying(255) | not null default ''::character varying quota | integer | not null default 0 created | timestamp with time zone | default now() modified | timestamp with time zone | default now() active | boolean | not null default true domain | character varying(255) | local_part | character varying(255) | not null Indexes: "mailbox_key" PRIMARY KEY, btree (username) "mailbox_domain_idx" btree (domain) "mailbox_username_active" btree (username, active) Foreign-key constraints: "mailbox_domain_fkey1" FOREIGN KEY (domain) REFERENCES domain(domain) Adding a user with anything over a 4GiB mailbox gives the following error: DEBUG INFORMATION: Invalid query: ERROR: value "8589934592" is out of range for type integer LINE 1: ...','test','test.tld/te...@te...d/','test.tld','858993459... ^ ---------------------------------------------------------------------- >Comment By: Christian Boltz (christian_boltz) Date: 2011-02-17 22:36 Message: Ah, PostgreSQL - that explains it. My fix was for MySQL... David, can you give me an ALTER TABLE query to change the quota field in the mailbox table to bigint? (I'll then handle the needed changes in upgrade.php.) ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=3057081&group_id=191583 |
From: SourceForge.net <no...@so...> - 2011-02-17 21:45:03
|
Bugs item #3057081, was opened at 2010-09-01 00:03 Message generated for change (Comment added) made by gingerdog You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=3057081&group_id=191583 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: Database Group: v2.3.1 Status: Open Resolution: None Priority: 5 Private: No Submitted By: oliver (n0d3) Assigned to: Nobody/Anonymous (nobody) Summary: Quta's stored as integers, should be bigint. Initial Comment: This is a copy (re-opening) of 2986880. I"m opening a new ticket because I can't seem to find an edit button. >From there: "If you disagree or still have "small" integer quota fields with 2.3 or the upcoming 2.3.1 release (don't forget to run setup.php after upgrade), feel free to reopen this ticket ;-)" I just did a fresh install of 2.3.2 and the issue is still not fixed (for pgsql). postfix_new=> \d mailbox; Table "public.mailbox" Column | Type | Modifiers ------------+--------------------------+---------------------------------------- username | character varying(255) | not null password | character varying(255) | not null default ''::character varying name | character varying(255) | not null default ''::character varying maildir | character varying(255) | not null default ''::character varying quota | integer | not null default 0 created | timestamp with time zone | default now() modified | timestamp with time zone | default now() active | boolean | not null default true domain | character varying(255) | local_part | character varying(255) | not null Indexes: "mailbox_key" PRIMARY KEY, btree (username) "mailbox_domain_idx" btree (domain) "mailbox_username_active" btree (username, active) Foreign-key constraints: "mailbox_domain_fkey1" FOREIGN KEY (domain) REFERENCES domain(domain) Adding a user with anything over a 4GiB mailbox gives the following error: DEBUG INFORMATION: Invalid query: ERROR: value "8589934592" is out of range for type integer LINE 1: ...','test','test.tld/te...@te...d/','test.tld','858993459... ^ ---------------------------------------------------------------------- >Comment By: GingerDog (gingerdog) Date: 2011-02-17 21:45 Message: alter table mailbox alter column quota type bigint; ---------------------------------------------------------------------- Comment By: Christian Boltz (christian_boltz) Date: 2011-02-17 21:36 Message: Ah, PostgreSQL - that explains it. My fix was for MySQL... David, can you give me an ALTER TABLE query to change the quota field in the mailbox table to bigint? (I'll then handle the needed changes in upgrade.php.) ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=3057081&group_id=191583 |
From: SourceForge.net <no...@so...> - 2011-02-18 22:05:50
|
Bugs item #3057081, was opened at 2010-09-01 02:03 Message generated for change (Comment added) made by christian_boltz You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=3057081&group_id=191583 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: Database Group: v2.3.1 Status: Open Resolution: None Priority: 5 Private: No Submitted By: oliver (n0d3) Assigned to: Nobody/Anonymous (nobody) Summary: Quta's stored as integers, should be bigint. Initial Comment: This is a copy (re-opening) of 2986880. I"m opening a new ticket because I can't seem to find an edit button. >From there: "If you disagree or still have "small" integer quota fields with 2.3 or the upcoming 2.3.1 release (don't forget to run setup.php after upgrade), feel free to reopen this ticket ;-)" I just did a fresh install of 2.3.2 and the issue is still not fixed (for pgsql). postfix_new=> \d mailbox; Table "public.mailbox" Column | Type | Modifiers ------------+--------------------------+---------------------------------------- username | character varying(255) | not null password | character varying(255) | not null default ''::character varying name | character varying(255) | not null default ''::character varying maildir | character varying(255) | not null default ''::character varying quota | integer | not null default 0 created | timestamp with time zone | default now() modified | timestamp with time zone | default now() active | boolean | not null default true domain | character varying(255) | local_part | character varying(255) | not null Indexes: "mailbox_key" PRIMARY KEY, btree (username) "mailbox_domain_idx" btree (domain) "mailbox_username_active" btree (username, active) Foreign-key constraints: "mailbox_domain_fkey1" FOREIGN KEY (domain) REFERENCES domain(domain) Adding a user with anything over a 4GiB mailbox gives the following error: DEBUG INFORMATION: Invalid query: ERROR: value "8589934592" is out of range for type integer LINE 1: ...','test','test.tld/te...@te...d/','test.tld','858993459... ^ ---------------------------------------------------------------------- >Comment By: Christian Boltz (christian_boltz) Date: 2011-02-18 23:05 Message: For the records: quota and maxquota in the domain table also needed a change to bigint. Commited to SVN r968. I'll also backport it to the 2.3 branch later. Note that you have to run setup.php to do the database changes. ---------------------------------------------------------------------- Comment By: GingerDog (gingerdog) Date: 2011-02-17 22:45 Message: alter table mailbox alter column quota type bigint; ---------------------------------------------------------------------- Comment By: Christian Boltz (christian_boltz) Date: 2011-02-17 22:36 Message: Ah, PostgreSQL - that explains it. My fix was for MySQL... David, can you give me an ALTER TABLE query to change the quota field in the mailbox table to bigint? (I'll then handle the needed changes in upgrade.php.) ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=3057081&group_id=191583 |
From: SourceForge.net <no...@so...> - 2011-02-18 22:06:17
|
Bugs item #3057081, was opened at 2010-09-01 02:03 Message generated for change (Settings changed) made by christian_boltz You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=3057081&group_id=191583 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: Database Group: v2.3.1 >Status: Closed >Resolution: Fixed Priority: 5 Private: No Submitted By: oliver (n0d3) Assigned to: Nobody/Anonymous (nobody) Summary: Quta's stored as integers, should be bigint. Initial Comment: This is a copy (re-opening) of 2986880. I"m opening a new ticket because I can't seem to find an edit button. >From there: "If you disagree or still have "small" integer quota fields with 2.3 or the upcoming 2.3.1 release (don't forget to run setup.php after upgrade), feel free to reopen this ticket ;-)" I just did a fresh install of 2.3.2 and the issue is still not fixed (for pgsql). postfix_new=> \d mailbox; Table "public.mailbox" Column | Type | Modifiers ------------+--------------------------+---------------------------------------- username | character varying(255) | not null password | character varying(255) | not null default ''::character varying name | character varying(255) | not null default ''::character varying maildir | character varying(255) | not null default ''::character varying quota | integer | not null default 0 created | timestamp with time zone | default now() modified | timestamp with time zone | default now() active | boolean | not null default true domain | character varying(255) | local_part | character varying(255) | not null Indexes: "mailbox_key" PRIMARY KEY, btree (username) "mailbox_domain_idx" btree (domain) "mailbox_username_active" btree (username, active) Foreign-key constraints: "mailbox_domain_fkey1" FOREIGN KEY (domain) REFERENCES domain(domain) Adding a user with anything over a 4GiB mailbox gives the following error: DEBUG INFORMATION: Invalid query: ERROR: value "8589934592" is out of range for type integer LINE 1: ...','test','test.tld/te...@te...d/','test.tld','858993459... ^ ---------------------------------------------------------------------- Comment By: Christian Boltz (christian_boltz) Date: 2011-02-18 23:05 Message: For the records: quota and maxquota in the domain table also needed a change to bigint. Commited to SVN r968. I'll also backport it to the 2.3 branch later. Note that you have to run setup.php to do the database changes. ---------------------------------------------------------------------- Comment By: GingerDog (gingerdog) Date: 2011-02-17 22:45 Message: alter table mailbox alter column quota type bigint; ---------------------------------------------------------------------- Comment By: Christian Boltz (christian_boltz) Date: 2011-02-17 22:36 Message: Ah, PostgreSQL - that explains it. My fix was for MySQL... David, can you give me an ALTER TABLE query to change the quota field in the mailbox table to bigint? (I'll then handle the needed changes in upgrade.php.) ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937964&aid=3057081&group_id=191583 |