Обсуждение: Application user name attribute on connection pool
Hello, I'm not member of this list, but because the 9th version of PostgreSQL is incoming, I would like to ask if there is possibility to add session property of application user - this property, in contrast to login name, should be setted by driver (e.g. JDBC) to current user logged into application server. This for example, will allow triggers to store real user name in history tables or log subsystem (in future) to write who deleted this or that. Similarly solution is in Oracle. I would like to ask, about your opinion about numeric type. I implemented binary read for numeric type in JDBC and I saw, that numeric type is stored inside database as array of shorts no greater then nbase (currently 10000). In my opinion this isn't high performance method for two reasons: 1. Arithmetic operations could take more time. 2. It's generally about JDBC and other drivers, transmitting numeric value is complicated and leaks performance for client side, as for long numbers many multiplications and additions must occur. I think about writing something like numeric2 which internally will be represented as the array of ints without nbase. In this context I would like to ask about your opinion 1. If this behaviour can be useful? I imagine performance increase on storing and retrieving values, similarly arithmetic should be faster. (currently 10001 + 10001 requires 4 operations: 2 additions of 1, and 2 additions of 1 from 1*10000 and carry move operations, if this value will be stored without nbase, with full bits then addition even in short will take 1 operation 10001+10001 + carry move). 2. Will this decrease other performances? I think that text processing will be much slower, but will this decrease engine performance, as the text conversion is required when creating type? Kind regards, Radek.
On Mon, Aug 2, 2010 at 07:30, <rsmogura@softperience.pl> wrote: > Hello, > > I'm not member of this list, but because the 9th version of PostgreSQL is > incoming, I would like to ask if there is possibility to add session > property of application user Not for 9.0 its more or less already cut. > - this property, in contrast to login name, > should be setted by driver (e.g. JDBC) to current user logged into > application server. This for example, will allow triggers to store real > user name in history tables or log subsystem (in future) to write who > deleted this or that. Similarly solution is in Oracle. You might be able to (ab)use the new application_name GUC, see http://www.postgresql.org/docs/9.0/static/runtime-config-logging.html#GUC-APPLICATION-NAME. I have used custom GUCS, per session temporary tables and giving each user an actual database 'role' for this in the past. Any of those with appropriate triggers works fairly well. I find normally you end up needing a temp table anyways as you end up wanting to log more than just the user (for me its the "page_load" or the "action").
On 08/02/10 6:30 AM, rsmogura@softperience.pl wrote: > I would like to ask, about your opinion about numeric type. I implemented > binary read for numeric type in JDBC and I saw, that numeric type is stored > inside database as array of shorts no greater then nbase (currently 10000). > In my opinion this isn't high performance method for two reasons: > 1. Arithmetic operations could take more time. > 2. It's generally about JDBC and other drivers, transmitting numeric value > is complicated and leaks performance for client side, as for long numbers > many multiplications and additions must occur. > I think about writing something like numeric2 which internally will be > represented as the array of ints without nbase. In this context I would > like to ask about your opinion > 1. If this behaviour can be useful? I imagine performance increase on > storing and retrieving values, similarly arithmetic should be faster. > (currently 10001 + 10001 requires 4 operations: 2 additions of 1, and 2 > additions of 1 from 1*10000 and carry move operations, if this value will > be stored without nbase, with full bits then addition even in short will > take 1 operation 10001+10001 + carry move). > 2. Will this decrease other performances? I think that text processing > will be much slower, but will this decrease engine performance, as the text > conversion is required when creating type? how would you handle scale factors? numeric represents a BCD data type, with a decimal fractional component. how would you represent, say, 10000.001 in your version? how would you add 1.001 to 10000.01 in your binary representation? PostgreSQL already has BIGINT aka INT8, which are 8 bytes, and can represent integers up to like 9 billion billion (eg, 9 * 10^18).
> how would you handle scale factors? numeric represents a BCD data
> type, with a decimal fractional component. how would you represent,
> say, 10000.001 in your version? how would you add 1.001 to 10000.01
> in your binary representation?
I think about datastructure something like this
[precision 16bits][scale 15 bits][1 bit sign]int[n] (here n can be always
calculeted as the (size of datatype - 8) / 4.
In this way the number 10000.001 will be stored as the single element array
8,3,+,{10000001}
If scale is same typically in aggregate, then it's just adding this array of
integers.
If scales aren't same then one of argument's must be multiplied by 10^(scales
diff).
In this way the result of 1.001 + 10000.01 will be
1001 + 1000001*10 with scale 3.
I think there is no big algorithmic difference beteween nbase encoding, and
encoding on full bytes - becuase in nbase encoding the carry in addition you
take as the (a+b)/1000. Here the difference is only that carry will be taken
from shifting longs eg:
long l = a[0] + b[0];
carry = L >> 32;
s[0] = l & 0xffffff;
> PostgreSQL already has BIGINT aka INT8, which are 8 bytes, and can
> represent integers up to like 9 billion billion (eg, 9 * 10^18).
But I think about numbers with precision - you can use float for moneys, etc
(rounding problems), and dividing each value in application by some scale
isn't nice, too.
On 2 Aug 2010, at 23:43, Radosław Smogura wrote: >> PostgreSQL already has BIGINT aka INT8, which are 8 bytes, and can >> represent integers up to like 9 billion billion (eg, 9 * 10^18). > But I think about numbers with precision - you can use float for moneys, etc > (rounding problems), and dividing each value in application by some scale > isn't nice, too. Most people don't use float for monetary values. Have a look at the NUMERIC type: http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4c57b0dc286217280628589!
Usually bighouse financial systems use BIGINT and a field to store position-of-decimal point to track arbitrary precision currency values... That's the "right way" to do it. I believe for mom-and-pop stuff, you can satisfy the auditors if you use NUMERIC(,2) and implement round-to-even (banker's rounding), though... On 2010-08-03 08:01:34AM +0200, Alban Hertroys wrote: > On 2 Aug 2010, at 23:43, Radosław Smogura wrote: > > >> PostgreSQL already has BIGINT aka INT8, which are 8 bytes, and can > >> represent integers up to like 9 billion billion (eg, 9 * 10^18). > > But I think about numbers with precision - you can use float for moneys, etc > > (rounding problems), and dividing each value in application by some scale > > isn't nice, too. > > > Most people don't use float for monetary values. > Have a look at the NUMERIC type: http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html > > Alban Hertroys > > -- > If you can't see the forest for the trees, > cut the trees and you'll see there is no forest. > > > !DSPAM:737,4c57b0dc286217280628589! > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- =========================================================== Peter C. Lai | Bard College at Simon's Rock Systems Administrator | 84 Alford Rd. Information Technology Svcs. | Gt. Barrington, MA 01230 USA peter AT simons-rock.edu | (413) 528-7428 ===========================================================