Bruce Momjian [Wed, 25 Jun 2003 01:11:09 +0000 (01:11 +0000)]
- Corrections for tables, columns with uppercase characters
- Don't attempt to convert partial or expressional unique indexes
- Don't attempt to convert unique indexes based on a non-default
opclasses
- Untested prevention of conversion of non-btree indexes unique
indexes. Untested as postgresql doesn't allow hash, gist, or rtree
based indexes to be unique.
rbt=# create unique index t on a using hash (col);
ERROR: DefineIndex: access method "hash" does not support UNIQUE
indexes
rbt=# create unique index t on a using gist (col);
ERROR: DefineIndex: access method "gist" does not support UNIQUE
indexes
rbt=# select version();
version
------------------------------------------------------------------------
PostgreSQL 7.4devel on i386-unknown-freebsd4.8, compiled by GCC 2.95.4
Bruce Momjian [Wed, 25 Jun 2003 01:09:24 +0000 (01:09 +0000)]
This is a bug in python interface module,
postgresql-7.3.3/src/interfaces/python/pg.py.
_quote() function fails due to integer overflow if input d is larger
than max integer.
In the case where the column type is "BIGINT", the input d may very well
be larger than max integer while its type, t, is labeled 'int'.
The conversion on line 19, return "%d" % int(d), will fail due to
"OverflowError: long int too large to convert to int".
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
[1] create a table with a column type 'BIGINT'.
[2] use pg.DB.insert() to insert a value that is larger than max integer
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
Just changing the conversion at line 19 of pg.py to long(d) instead of
int(d) should fix it. The following is a patch:
Bruce Momjian [Wed, 25 Jun 2003 01:08:13 +0000 (01:08 +0000)]
>> If a transaction marks a tuple for update and later commits without
>> actually having updated the tuple, [...] can we simply
>> set the HEAP_XMAX_INVALID hint bit of the tuple?
>
>AFAICS this is a reasonable thing to do.
Thanks for the confirmation. Here's a patch which also contains some
more noncritical changes to tqual.c:
. make code more readable by introducing local variables for xvac
. no longer two separate branches for aborted and crashed.
The actions were the same in all cases.
Bruce Momjian [Tue, 24 Jun 2003 23:29:25 +0000 (23:29 +0000)]
Most of the synopsis areas for DROP commands use "name" as the
identifier, while some areas do not.
The attached converts be below to "name":
conversion_name
index_name
The below have an existing, initdb supplied, entity named "name". As
such, it could be confusing for the reader to see that identifier used
in the example.
Bruce Momjian [Tue, 24 Jun 2003 23:25:44 +0000 (23:25 +0000)]
In an attempt to simplify my life I'm submitting this patch that
restructures the deferred trigger queue. The fundamental change is to
put all the static variables to hold the deferred triggers in a single
structure.
Bruce Momjian [Tue, 24 Jun 2003 22:59:46 +0000 (22:59 +0000)]
Jim C. Nasby wrote:
> Second argument to metaphone is suposed to set the limit on the
> number of characters to return, but it breaks on some phrases:
>
> usps=# select metaphone(a,3),metaphone(a,4),metaphone(a,20) from
> (select 'Hello world'::varchar AS a) a;
> HLW | HLWR | HLWRLT
>
> usps=# select metaphone(a,3),metaphone(a,4),metaphone(a,20) from
> (select 'A A COMEAUX MEMORIAL'::varchar AS a) a;
> AKM | AKMKS | AKMKSMMRL
>
> In every case I've found that does this, the 4th and 5th letters are
> always 'KS'.
Nice catch.
There was a bug in the original metaphone algorithm from CPAN. Patch
attached (while I was at it I updated my email address, changed the
copyright to PGDG, and removed an unnecessary palloc). Here's how it
looks now:
regression=# select metaphone(a,4) from (select 'A A COMEAUX
MEMORIAL'::varchar AS a) a;
metaphone
-----------
AKMK
(1 row)
regression=# select metaphone(a,5) from (select 'A A COMEAUX
MEMORIAL'::varchar AS a) a;
metaphone
-----------
AKMKS
(1 row)
Bruce Momjian [Tue, 24 Jun 2003 22:21:24 +0000 (22:21 +0000)]
Add ipv6 address parsing support to 'inet' and 'cidr' data types.
Regression tests for IPv6 operations added.
Documentation updated to document IPv6 bits.
Stop treating IPv4 as an "unsigned int" and IPv6 as an array of
characters. Instead, always use the array of characters so we
can have one function fits all. This makes bitncmp(), addressOK(),
and several other functions "just work" on both address families.
add family() function which returns integer 4 or 6 for IPv4 or
IPv6. (See examples below) Note that to add this new function
you will need to dump/initdb/reload or find the correct magic
to add the function to the postgresql function catalogs.
IPv4 addresses always sort before IPv6.
On disk we use AF_INET for IPv4, and AF_INET+1 for IPv6 addresses.
This prevents the need for a dump and reload, but lets IPv6 parsing
work on machines without AF_INET6.
Tom Lane [Sun, 22 Jun 2003 22:04:55 +0000 (22:04 +0000)]
Revise hash join and hash aggregation code to use the same datatype-
specific hash functions used by hash indexes, rather than the old
not-datatype-aware ComputeHashFunc routine. This makes it safe to do
hash joining on several datatypes that previously couldn't use hashing.
The sets of datatypes that are hash indexable and hash joinable are now
exactly the same, whereas before each had some that weren't in the other.
Tom Lane [Sun, 22 Jun 2003 05:48:26 +0000 (05:48 +0000)]
Remove a lot of desperately obsolete material (which was all out of sight,
out of mind, because it'd been commented out years ago). Try to bring the
remains up to a reasonable level of currency, and give it all approximately
the same high level of abstraction.
Bruce Momjian [Sun, 22 Jun 2003 05:01:17 +0000 (05:01 +0000)]
Add:
> * Allow current datestyle to restrict dates; prevent month/day swapping
> from making invalid dates valid
> * Prevent month/day swapping of ISO dates to make invalid dates valid
Tom Lane [Fri, 20 Jun 2003 21:58:02 +0000 (21:58 +0000)]
Fix for extended-query protocol: in event of error, backend was issuing
a ReadyForQuery (Z message) immediately and then another one after the
Sync message arrives. Suppress the first one to make it work per spec.
Tom Lane [Thu, 19 Jun 2003 23:22:40 +0000 (23:22 +0000)]
Disallow dollar sign in operator names, instead allow it as a non-first
character in identifiers. The first change eliminates the current need
to put spaces around parameter references, as in "x<=$2". The second
change improves compatibility with Oracle and some other RDBMSes. This
was discussed and agreed to back in January, but did not get done.
Tom Lane [Tue, 17 Jun 2003 23:12:36 +0000 (23:12 +0000)]
Make FLOAT(p) measure the precision p in bits, not decimal digits, to
match the SQL standard. Document FLOAT and FLOAT(p) notations in
datatype.sgml. Per recent pghackers discussion.
Tom Lane [Mon, 16 Jun 2003 02:03:38 +0000 (02:03 +0000)]
Allow GROUP BY, ORDER BY, DISTINCT targets to be unknown literals,
silently resolving them to type TEXT. This is comparable to what we
do when faced with UNKNOWN in CASE, UNION, and other contexts. It gets
rid of this and related annoyances:
select distinct f1, '' from int4_tbl;
ERROR: Unable to identify an ordering operator '<' for type unknown
This was discussed many moons ago, but no one got round to fixing it.
Tom Lane [Sun, 15 Jun 2003 22:51:45 +0000 (22:51 +0000)]
Adjust nestloop-with-inner-indexscan plan generation so that we catch
some cases of redundant clauses that were formerly not caught. We have
to special-case this because the clauses involved never get attached to
the same join restrictlist and so the existing logic does not notice
that they are redundant.
Tom Lane [Sun, 15 Jun 2003 16:42:08 +0000 (16:42 +0000)]
Cause GROUP BY clause to adopt ordering operators from ORDER BY when
both clauses specify the same targets, rather than always using the
default ordering operator. This allows 'GROUP BY foo ORDER BY foo DESC'
to be done with only one sort step.
Bruce Momjian [Thu, 12 Jun 2003 07:52:51 +0000 (07:52 +0000)]
Attached is a patch that enhances the output of psql's HTML mode.
The output now validates as HTML 4.01 Strict, XHTML 1.0 strict,
and XHTML 1.1 (assuming you wrap it in a valid html/body document).
It also wraps the output of PGRES_COMMAND_OK if the HTML tag is on,
for full compliance: this is why html_escaped_print has to be
externalized.