algorithm.  This is a good deal slower than our old roundoff-error-prone
code for long inputs, so we keep the old code for use in the transcendental
functions, where everything is approximate anyway.  Also create a
user-accessible function div(numeric, numeric) to provide access to the
exact result of trunc(x/y) --- since the regular numeric / operator will
round off its result, simply computing that expression in SQL doesn't
reliably give the desired answer.  This fixes bug #3387 and various related
corner cases, and improves the usefulness of PG for high-precision integer
arithmetic.

the same outputs as stddev_samp() and var_samp() respectively.

an array of strings rather than an array of integers, and allow any simple
constant or identifier to be used in typmods; for example
	create table foo (f1 widget(42,'23skidoo',point));
Of course the typmodin function has still got to pack this info into a
non-negative int32 for storage, but it's still a useful improvement in
flexibility, especially considering that you can do nearly anything if you
are willing to keep the info in a side table.  We can get away with this
change since we have not yet released a version providing user-definable
typmods.  Per discussion.

large inputs.  Also cause it to error out immediately if the result will
overflow, instead of grinding through a lot of calculation first.
Per gripe from Jim Nasby.

from the other string-category types; this eliminates a lot of surprising
interpretations that the parser could formerly make when there was no directly
applicable operator.

Create a general mechanism that supports casts to and from the standard string
types (text,varchar,bpchar) for *every* datatype, by invoking the datatype's
I/O functions.  These new casts are assignment-only in the to-string direction,
explicit-only in the other, and therefore should create no surprising behavior.
Remove a bunch of thereby-obsoleted datatype-specific casting functions.

The "general mechanism" is a new expression node type CoerceViaIO that can
actually convert between *any* two datatypes if their external text
representations are compatible.  This is more general than needed for the
immediate feature, but might be useful in plpgsql or other places in future.

This commit does nothing about the issue that applying the concatenation
operator || to non-text types will now fail, often with strange error messages
due to misinterpreting the operator as array concatenation.  Since it often
(not always) worked before, we should either make it succeed or at least give
a more user-friendly error; but details are still under debate.

Peter Eisentraut and Tom Lane

numerics as "oprcanhash", and make the corresponding system catalog
updates. As a result, hash indexes, hashed aggregation, and hash
joins can now be used with the numeric type. Bump the catversion.

The only tricky aspect to doing this is writing a correct hash
function: it's possible for two Numerics to be equal according to
their equality operator, but have different in-memory bit patterns.
To cope with this, the hash function doesn't consider the Numeric's
"scale" or "sign", and explictly skips any leading or trailing
zeros in the Numeric's digit buffer (the current implementation
should suppress any such zeros, but it seems unwise to rely upon
this). See discussion on pgsql-patches for more details.

Get rid of VARATT_SIZE and VARATT_DATA, which were simply redundant with
VARSIZE and VARDATA, and as a consequence almost no code was using the
longer names.  Rename the length fields of struct varlena and various
derived structures to catch anyplace that was accessing them directly;
and clean up various places so caught.  In itself this patch doesn't
change any behavior at all, but it is necessary infrastructure if we hope
to play any games with the representation of varlena headers.
Greg Stark and Tom Lane

as a performance enhancement.

Mark Kirkwood

The implementation is somewhat ugly logic-wise, but I don't see an
easy way to make it more concise.

When writing this, I noticed that my previous implementation of
width_bucket() doesn't handle NaN correctly:

    postgres=# select width_bucket('NaN', 1, 5, 5);
     width_bucket
    --------------
                6
    (1 row)

AFAICS SQL:2003 does not define a NaN value, so it doesn't address how
width_bucket() should behave here. The patch changes width_bucket() so
that ereport(ERROR) is raised if NaN is specified for the operand or the
lower or upper bounds to width_bucket(). For float8, NaN is disallowed
for any of the floating-point inputs, and +/- infinity is disallowed
for the histogram bounds (but allowed for the operand).

Update docs and regression tests, bump the catversion.

back-stamped for this.

about typmod representation for standard types out into type-specific
typmod I/O functions.  Teodor Sigaev, with some editorialization by
Tom Lane.

David Fetter

have no other gods before c.h'.  Also remove some demonstrably redundant
#include lines, mostly of <errno.h> which was added to c.h years ago.

var_samp(), stddev_pop(), and stddev_samp(). var_samp() and stddev_samp()
are just renamings of the historical Postgres aggregates variance() and
stddev() -- the latter names have been kept for backward compatibility.
This patch includes updates for the documentation and regression tests.
The catversion has been bumped.

NB: SQL2003 requires that DISTINCT not be specified for any of these
aggregates. Per discussion on -patches, I have NOT implemented this
restriction: if the user asks for stddev(DISTINCT x), presumably they
know what they are doing.

the format on Tuple(Numeric) and the format to calculate(NumericVar)
are different. I understood that to reduce I/O. However, when many
comparisons or calculations of NUMERIC are executed, the conversion
of Numeric and NumericVar becomes a bottleneck.

It is profile result when "create index on NUMERIC column" is executed:

  %   cumulative   self              self     total
 time   seconds   seconds    calls   s/call   s/call  name
 17.61     10.27    10.27 34542006     0.00     0.00  cmp_numerics
 11.90     17.21     6.94 34542006     0.00     0.00  comparetup_index
  7.42     21.54     4.33 71102587     0.00     0.00  AllocSetAlloc
  7.02     25.64     4.09 69084012     0.00     0.00  set_var_from_num
  4.87     28.48     2.84 69084012     0.00     0.00  alloc_var
  4.79     31.27     2.79 142205745     0.00     0.00  AllocSetFreeIndex
  4.55     33.92     2.65 34542004     0.00     0.00  cmp_abs
  4.07     36.30     2.38 71101189     0.00     0.00  AllocSetFree
  3.83     38.53     2.23 69084012     0.00     0.00  free_var

The create index command executes many comparisons of Numeric values.
Functions other than comparetup_index spent a lot of cycles for
conversion from Numeric to NumericVar.

An attached patch enables the comparison of Numeric values without
executing conversion to NumericVar. The execution time of that SQL
becomes half.

o Test SQL (index_test table has 1,000,000 tuples)
 create index index_test_idx on index_test(num_col);

o Test results (executed the test five times)
(1)PentiumIII
 original: 39.789s  36.823s  36.737s  37.752s  37.019s
 patched : 18.560s  19.103s  18.830s  18.408s  18.853s
  4.07     36.30     2.38 71101189     0.00     0.00  AllocSetFree
  3.83     38.53     2.23 69084012     0.00     0.00  free_var

The create index command executes many comparisons of Numeric values.
Functions other than comparetup_index spent a lot of cycles for
conversion from Numeric to NumericVar.

An attached patch enables the comparison of Numeric values without
executing conversion to NumericVar. The execution time of that SQL
becomes half.

o Test SQL (index_test table has 1,000,000 tuples)
 create index index_test_idx on index_test(num_col);

o Test results (executed the test five times)
(1)PentiumIII
 original: 39.789s  36.823s  36.737s  37.752s  37.019s
 patched : 18.560s  19.103s  18.830s  18.408s  18.853s

(2)Pentium4
 original: 16.349s  14.997s  12.979s  13.169s  12.955s
 patched :  7.005s   6.594s   6.770s   6.740s   6.828s

(3)Itanium2
 original: 15.392s  15.447s  15.350s  15.370s  15.417s
 patched :  7.413s   7.330s   7.334s   7.339s   7.339s

(4)Ultra Sparc
 original: 64.435s  59.336s  59.332s  58.455s  59.781s
 patched : 28.630s  28.666s  28.983s  28.744s  28.595s

Atsushi Ogawa

instead of 10^0.

comment line where output as too long, and update typedefs for /lib
directory.  Also fix case where identifiers were used as variable names
in the backend, but as typedefs in ecpg (favor the backend for
indenting).

Backpatch to 8.1.X.

functionality, but I still need to make another pass looking at places
that incidentally use arrays (such as ACL manipulation) to make sure they
are null-safe.  Contrib needs work too.
I have not changed the behaviors that are still under discussion about
array comparison and what to do with lower bounds.

optional arguments as text input functions, ie, typioparam OID and
atttypmod.  Make all the datatypes that use typmod enforce it the same
way in typreceive as they do in typinput.  This fixes a problem with
failure to enforce length restrictions during COPY FROM BINARY.

Division rounding was causing incorrect results.  Test case:

	test=> SELECT 12345678901234567890 % 123;
	 ?column?
	----------
	       78
	(1 row)

Was returning -45.

functions. This patch optimizes int2_sum(), int4_sum(), float4_accum()
and float8_accum() to avoid needing to copy the transition function's
state for each input tuple of the aggregate. In an extreme case
(e.g. SELECT sum(int2_col) FROM table where table has a single column),
it improves performance by about 20%. For more complex queries or tables
with wider rows, the relative performance improvement will not be as
significant.

performance hack Tom introduced recently. This means we can avoid
copying the transition array for each input tuple if these functions
are invoked as aggregate transition functions.

To test the performance improvement, I created a 1 million row table
with a single int4 column. Without the patch, SELECT avg(col) FROM
table took about 4.2 seconds (after the data was cached); with the
patch, it took about 3.2 seconds. Naturally, the performance
improvement for a less trivial query (or a table with wider rows)
would be relatively smaller.

bigint variants).  Clean up some inconsistencies in error message wording.
Fix scanint8 to allow trailing whitespace in INT64_MIN case.  Update
int8-exp-three-digits.out, which seems to have been ignored by the last
couple of people to modify the int8 regression test, and remove
int8-exp-three-digits-win32.out which is thereby exposed as redundant.

right error code previously, and this patch applies an analogous change
to numeric_sqrt().

error codes for certain error conditions, as specified by SQL2003.

a variant of the function for the 'numeric' datatype; it would be possible
to add additional variants for other datatypes, but I haven't done so yet.

This commit includes regression tests and minimal documentation; if we
want developers to actually use this function in applications, we'll
probably need to document what it does more fully.

conversion of basic ASCII letters.  Remove all uses of strcasecmp and
strncasecmp in favor of new functions pg_strcasecmp and pg_strncasecmp;
remove most but not all direct uses of toupper and tolower in favor of
pg_toupper and pg_tolower.  These functions use the same notions of
case folding already developed for identifier case conversion.  I left
the straight locale-based folding in place for situations where we are
just manipulating user data and not trying to match it to built-in
strings --- for example, the SQL upper() function is still locale
dependent.  Perhaps this will prove not to be what's wanted, but at
the moment we can initdb and pass regression tests in Turkish locale.

Per recent proposal to pghackers.

is asked to assign a variable to itself, it will result in doing a
memcpy() on an entirely-overlapping memory range, which results in
undefined behavior according to ANSI C. That said, it is unlikely to
actually do anything bad on any sane libc, but this keeps valgrind quiet.