Bruce Momjian [Wed, 28 Dec 2005 16:47:21 +0000 (16:47 +0000)]
Mention "table" in "violates foreign key constraint" message that was
lacking it. Perhaps it was suppressed because of line length
considerations, but "table" should appear.
Bruce Momjian [Wed, 28 Dec 2005 03:25:32 +0000 (03:25 +0000)]
Add regression tests for CSV and \., and add automatic quoting of a
single column dump that has a \. value, so the load works properly. I
also added documentation describing this issue.
Tom Lane [Wed, 28 Dec 2005 01:30:02 +0000 (01:30 +0000)]
Implement SQL-compliant treatment of row comparisons for < <= > >= cases
(previously we only did = and <> correctly). Also, allow row comparisons
with any operators that are in btree opclasses, not only those with these
specific names. This gets rid of a whole lot of indefensible assumptions
about the behavior of particular operators based on their names ... though
it's still true that IN and NOT IN expand to "= ANY". The patch adds a
RowCompareExpr expression node type, and makes some changes in the
representation of ANY/ALL/ROWCOMPARE SubLinks so that they can share code
with RowCompareExpr.
I have not yet done anything about making RowCompareExpr an indexable
operator, but will look at that soon.
Andrew Dunstan [Tue, 27 Dec 2005 23:54:01 +0000 (23:54 +0000)]
Increase amount of shared buffers initdb tries to allocate to 4000,
and add logic to try max_fsm_pages up to 200000, plus accompanying minor
docs changes.
Bruce Momjian [Tue, 27 Dec 2005 18:10:48 +0000 (18:10 +0000)]
Our code had:
if (c == '\\' && cstate->line_buf.len == 0)
The problem with that is the because of the input and _output_
buffering, cstate->line_buf.len could be zero even if we are not on the
first character of a line. In fact, for a typical line, it is zero for
all characters on the line. The proper solution is to introduce a
boolean, first_char_in_line, that we set as we enter the loop and clear
once we process a character.
I have restructured the line-reading code in copy.c by:
o merging the CSV/non-CSV functions into a single function
o used macros to centralize and clarify the buffering code
o updated comments
o renamed client_encoding_only to encoding_embeds_ascii
o added a high-bit test to the encoding_embeds_ascii test for
performance
o in CSV mode, allow a backslash followed by a non-period to
continue being processed as a data value
There should be no performance impact from this patch because it is
functionally equivalent. If you apply the patch you will see copy.c is
much clearer in this area now and might suggest additional
optimizations.
I have also attached a 8.1-only patch to fix the CSV \. handling bug
with no code restructuring.
Bruce Momjian [Mon, 26 Dec 2005 05:14:52 +0000 (05:14 +0000)]
Add:
* %Make row-wise comparisons work per SQL spec
Right now, '(a, b) < (1, 2)' is processed as 'a < 1 and b < 2', but
the SQL standard requires it to be processed as a column-by-column
comparison, so the proper comparison is '(a < 1) OR (a = 1 AND b < 2)'.
and removed CSIGNBIT and mapped it uses to HIGHBIT. I have also added
uses for IS_HIGHBIT_SET where appropriate. This change is
purely for code clarity.
Tatsuo Ishii [Sat, 24 Dec 2005 09:35:36 +0000 (09:35 +0000)]
Fix long standing Asian multibyte charsets bug.
See:
Subject: [HACKERS] bugs with certain Asian multibyte charsets
From: Tatsuo Ishii <ishii@sraoss.co.jp>
To: pgsql-hackers@postgresql.org
Date: Sat, 24 Dec 2005 18:25:33 +0900 (JST)
Tom Lane [Fri, 23 Dec 2005 22:34:22 +0000 (22:34 +0000)]
Fix make_relative_path() to support cases where target_path and bin_path
differ by more than the last directory component. Instead of insisting
that they match up to the last component, accept whatever common prefix
they have, and try to replace the non-matching part of bin_path with
the non-matching part of target_path in the actual executable's path.
In one way this is tighter than the old code, because it insists on
a match to the part of bin_path we want to substitute for, rather than
blindly stripping one directory component from the executable's path.
Per gripe from Martin Pitt and subsequent discussion.
Tatsuo Ishii [Fri, 23 Dec 2005 02:11:02 +0000 (02:11 +0000)]
Fix for rearranging encoding id ISO-8859-5 to ISO-8859-8.
Also make the code more robust by searching for target encoding
in the internal charset map.
Problem reported by Sagi Bashari on 2005/12/21.
See "[BUGS] BUG #2120: Crash when doing UTF8<->ISO_8859_8 encoding conversion"
on pgsql-bugs list for more details.
Tom Lane [Fri, 23 Dec 2005 01:16:38 +0000 (01:16 +0000)]
Add an officially exported libpq function to encrypt passwords, and
modify the previous \password patch to use it instead of depending
on a not-officially-exported function. Per discussion.
Tom Lane [Thu, 22 Dec 2005 22:50:00 +0000 (22:50 +0000)]
Adjust string comparison so that only bitwise-equal strings are considered
equal: if strcoll claims two strings are equal, check it with strcmp, and
sort according to strcmp if not identical. This fixes inconsistent
behavior under glibc's hu_HU locale, and probably under some other locales
as well. Also, take advantage of the now-well-defined behavior to speed up
texteq, textne, bpchareq, bpcharne: they may as well just do a bitwise
comparison and not bother with strcoll at all.
NOTE: affected databases may need to REINDEX indexes on text columns to be
sure they are self-consistent.
Tom Lane [Tue, 20 Dec 2005 02:30:36 +0000 (02:30 +0000)]
Teach planner how to rearrange join order for some classes of OUTER JOIN.
Per my recent proposal. I ended up basing the implementation on the
existing mechanism for enforcing valid join orders of IN joins --- the
rules for valid outer-join orders are somewhat similar.
Bruce Momjian [Sat, 17 Dec 2005 19:03:24 +0000 (19:03 +0000)]
Remove item:
< * Allow star join optimizations
<
< While our bitmap scan allows multiple indexes to be joined to get
< to heap rows, a star joins allows multiple dimension _tables_ to
< be joined to index into a larger main fact table. The join is
< usually performed by either creating a cartesian product of all
< the dimmension tables and doing a single join on that product or
< using subselects to create bitmaps of each dimmension table match
< and merge the bitmaps to perform the join on the fact table. Some
< of these algorithms might be patented.
Bruce Momjian [Sat, 17 Dec 2005 18:07:01 +0000 (18:07 +0000)]
Update:
< * Flush cached query plans when the dependent objects change or
< when the cardinality of parameters changes dramatically
> * Flush cached query plans when the dependent objects change,
> when the cardinality of parameters changes dramatically, or
> when new ANALYZE statistics are available
Bruce Momjian [Sat, 17 Dec 2005 17:04:41 +0000 (17:04 +0000)]
Add mention of possible patent problems with star joins, per Joshua
Drake:
< and merge the bitmaps to perform the join on the fact table.
> and merge the bitmaps to perform the join on the fact table. Some
> of these algorithms might be patented.
Bruce Momjian [Sat, 17 Dec 2005 16:43:11 +0000 (16:43 +0000)]
Add:
* Allow star join optimizations
While our bitmap scan allows multiple indexes to be joined to get
to heap rows, a star joins allows multiple dimension _tables_ to
be joined to index into a larger main fact table. The join is
usually performed by either creating a cartesian product of all
the dimmension tables and doing a single join on that product or
using subselects to create bitmaps of each dimmension table match
and merge the bitmaps to perform the join on the fact table.
Bruce Momjian [Sat, 17 Dec 2005 04:10:47 +0000 (04:10 +0000)]
Update:
< * Flush cached query plans when the dependent objects change
> * Flush cached query plans when the dependent objects change or
> when the cardinality of parameters changes dramatically
Tom Lane [Fri, 16 Dec 2005 04:03:40 +0000 (04:03 +0000)]
Rethink prior patch to filter out dead backend entries from the pgstats
file. The original code probed the PGPROC array separately for each PID,
which was not good for large numbers of backends: not only is the runtime
O(N^2) but most of it is spent holding ProcArrayLock. Instead, take the
lock just once and copy the active PIDs into an array, then use qsort
and bsearch so that the lookup time is more like O(N log N).
Tom Lane [Wed, 14 Dec 2005 17:06:28 +0000 (17:06 +0000)]
Defend against crash while processing Describe Statement or Describe Portal
messages, when client attempts to execute these outside a transaction (start
one) or in a failed transaction (reject message, except for COMMIT/ROLLBACK
statements which we can handle). Per report from Francisco Figueiredo Jr.
Tom Lane [Wed, 14 Dec 2005 16:28:32 +0000 (16:28 +0000)]
Fix problem with whole-row Vars referencing sub-select outputs, per
example from Jim Dew. Add some simple regression tests, since this is
an area we seem to break regularly :-(
Bruce Momjian [Mon, 12 Dec 2005 13:36:36 +0000 (13:36 +0000)]
Update wording, query -> statement:
< * %Allow pooled connections to list all prepared queries
> * %Allow pooled connections to list all prepared statements
28c28
< the queries prepared in the current session.
> the statements prepared in the current session. 143c143
< o Allow a warm standby system to also allow read-only queries
> o Allow a warm standby system to also allow read-only statements 404c404
< * Add GUC to issue notice about queries that use unjoined tables
> * Add GUC to issue notice about statements that use unjoined tables 490c490
< Another idea would be to allow actual SELECT queries in a COPY.
> Another idea would be to allow actual SELECT statements in a COPY. 554c554
< o Allow function argument names to be queries from PL/PgSQL
> o Allow function argument names to be statements from PL/PgSQL 591c591
< o Improve psql's handling of multi-line queries
> o Improve psql's handling of multi-line statements
< Currently, while \e saves a single query as one entry, interactive
< queries are saved one line at a time. Ideally all queries
> Currently, while \e saves a single statement as one entry, interactive
> statements are saved one line at a time. Ideally all statements 665c665
< o Allow query results to be automatically batched to the client
> o Allow statement results to be automatically batched to the client 667c667
< Currently, all query results are transfered to the libpq
> Currently, all statement results are transfered to the libpq 672c672
< One complexity is that a query like SELECT 1/col could error
> One complexity is that a statement like SELECT 1/col could error 739c739
< * Allow queries across databases or servers with transaction
> * Allow statements across databases or servers with transaction
< inheritance, allow it to work for UPDATE and DELETE queries, and allow
< it to be used for all queries with little performance impact
> inheritance, allow it to work for UPDATE and DELETE statements, and allow
> it to be used for all statements with little performance impact 876c876
< * Consider automatic caching of queries at various levels:
> * Consider automatic caching of statements at various levels: 947c947
< a single session using multiple threads to execute a query faster.
> a single session using multiple threads to execute a statement faster. 1025c1025
< * Log queries where the optimizer row estimates were dramatically
> * Log statements where the optimizer row estimates were dramatically 1146c1146
< of result sets using new query protocol
> of result sets using new statement protocol
Tom Lane [Sun, 11 Dec 2005 21:02:18 +0000 (21:02 +0000)]
Divide the lock manager's shared state into 'partitions', so as to
reduce contention for the former single LockMgrLock. Per my recent
proposal. I set it up for 16 partitions, but on a pgbench test this
gives only a marginal further improvement over 4 partitions --- we need
to test more scenarios to choose the number of partitions.
Tom Lane [Sat, 10 Dec 2005 19:21:03 +0000 (19:21 +0000)]
Teach deparsing of CASE expressions to cope with the simplified forms
that simplify_boolean_equality() may leave behind. This is only relevant
if the user writes something a bit silly, like CASE x=y WHEN TRUE THEN.
Per example from Michael Fuhr; may or may not explain bug #2106.
Bruce Momjian [Fri, 9 Dec 2005 04:50:40 +0000 (04:50 +0000)]
I reconfirmed MS-VC6. Thank you for wonderful correspondence.
However, Another problem newly occurred.
This solves the problem of snprintf and vsnprintf.
Tom Lane [Fri, 9 Dec 2005 01:22:04 +0000 (01:22 +0000)]
Simplify lock manager data structures by making a clear separation between
the data defining the semantics of a lock method (ie, conflict resolution
table and ancillary data, which is all constant) and the hash tables
storing the current state. The only thing we give up by this is the
ability to use separate hashtables for different lock methods, but there
is no need for that anyway. Put some extra fields into the LockMethod
definition structs to clean up some other uglinesses, like hard-wired
tests for DEFAULT_LOCKMETHOD and USER_LOCKMETHOD. This commit doesn't
do anything about the performance issues we were discussing, but it clears
away some of the underbrush that's in the way of fixing that.
Bruce Momjian [Thu, 8 Dec 2005 21:36:50 +0000 (21:36 +0000)]
FAQ_AIX in 8.1.0 contains outdated information about how to deal with
postgres problems due to readline. The attached patch replaces that
section of it with better ways of handling the problem.
Bruce Momjian [Thu, 8 Dec 2005 21:35:36 +0000 (21:35 +0000)]
there's a paragraph in the ALTER TABLE reference page that reads:
DROP CONSTRAINT
This form drops constraints on a table. Currently, constraints on tables
are not required to have unique names, so there may be more than one
constraint matching the specified name. All matching constraints will be
dropped.
To my knowledge, it is no longer possible to create constraints with the
same name for the same relation. When you create a constraint and specify
the same name explictly, an error is raised. Implicit constraint creation
won't choose an existing name either and up to now you could not rename a
constraint. Renaming works with the patch I sent in a few hours ago but this
patch as well won't allow constraints with identical names on the same
relation.
The attached patch thus removes the note in the docs.
Bruce Momjian [Thu, 8 Dec 2005 21:33:58 +0000 (21:33 +0000)]
> 1) I'm proposing a patch to do the DROP FUNCTION argument tab completion.
> Now, the arguments of the drop function can be tab completed. for example
>
> drop function strpos (
> <press tab>
> drop FUNCTION strpos (text, text)
>
> or:
>
> wsdb=# drop FUNCTION length (
> bit) bytea) character) lseg) path) text)
> <press c>
> wsdb# DROP FUNCTION length ( character)
>
> I think that this patch should be rather useful. At it least I hate
> always to type all the arguments of the dropped functions.
>
> 2) Also some fixes applied for the
> CREATE INDEX syntax
>
> now the parenthesises are inserted by tab pressing.
> suppose I have the table q3c:
Tom Lane [Thu, 8 Dec 2005 19:19:22 +0000 (19:19 +0000)]
Fix bgwriter's failure to release buffer pins and open files after an
error. This probably explains bug #2099 and could also account for
mysterious VACUUM hangups.