Bruce Momjian [Thu, 27 Mar 2003 16:58:21 +0000 (16:58 +0000)]
It may not be obvious to you, but the plpython regression tests
include output that vary depending on the python build one is
running. Basically, the order of keys in a dictionary is
non-deterministic, and that part of the test fails for me regularly.
I rewrote the test to work around this problem, and include a patch
file with that change and the change to the expected otuput as well.
Bruce Momjian [Thu, 27 Mar 2003 16:57:39 +0000 (16:57 +0000)]
New \d format:
Example:
test=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
a | integer | not null
Indexes:
"test_pkey" PRIMARY KEY btree (a)
Check Constraints:
"$2" CHECK (a > 1)
Foreign Key Constraints:
"$1" FOREIGN KEY (a) REFERENCES parent(b)
Rules:
myrule AS ON INSERT TO test DO INSTEAD NOTHING
Triggers:
"asdf asdf" AFTER INSERT OR DELETE ON test FOR EACH STATEMENT EXECUTE
PROCEDURE update_pg_pwd_and_pg_group(),
mytrigger AFTER INSERT OR DELETE ON test FOR EACH ROW EXECUTE PROCEDURE
update_pg_pwd_and_pg_group()
I have minimised the double quoting of identifiers as much as I could
easily, and I will submit another patch when I have time to work on it that
will use a 'fmtId' function to determine it exactly.
I think it's a significant improvement in legibility...
Obviously the table example above is slightly degenerate in that not many
tables in production have heaps of (non-constraint) triggers and rules.
Bruce Momjian [Thu, 27 Mar 2003 16:51:29 +0000 (16:51 +0000)]
This patch implements holdable cursors, following the proposal
(materialization into a tuple store) discussed on pgsql-hackers earlier.
I've updated the documentation and the regression tests.
Notes on the implementation:
- I needed to change the tuple store API slightly -- it assumes that it
won't be used to hold data across transaction boundaries, so the temp
files that it uses for on-disk storage are automatically reclaimed at
end-of-transaction. I added a flag to tuplestore_begin_heap() to control
this behavior. Is changing the tuple store API in this fashion OK?
- in order to store executor results in a tuple store, I added a new
CommandDest. This works well for the most part, with one exception: the
current DestFunction API doesn't provide enough information to allow the
Executor to store results into an arbitrary tuple store (where the
particular tuple store to use is chosen by the call site of
ExecutorRun). To workaround this, I've temporarily hacked up a solution
that works, but is not ideal: since the receiveTuple DestFunction is
passed the portal name, we can use that to lookup the Portal data
structure for the cursor and then use that to get at the tuple store the
Portal is using. This unnecessarily ties the Portal code with the
tupleReceiver code, but it works...
The proper fix for this is probably to change the DestFunction API --
Tom suggested passing the full QueryDesc to the receiveTuple function.
In that case, callers of ExecutorRun could "subclass" QueryDesc to add
any additional fields that their particular CommandDest needed to get
access to. This approach would work, but I'd like to think about it for
a little bit longer before deciding which route to go. In the mean time,
the code works fine, so I don't think a fix is urgent.
- (semi-related) I added a NO SCROLL keyword to DECLARE CURSOR, and
adjusted the behavior of SCROLL in accordance with the discussion on
-hackers.
- (unrelated) Cleaned up some SGML markup in sql.sgml, copy.sgml
Bruce Momjian [Thu, 27 Mar 2003 16:45:51 +0000 (16:45 +0000)]
* Make pg_get_triggerdef documentation consistent with other pg_get_
functions
* Document pg_conversion_is_visible() which was created in one of my
previous patches and didn't get documented for some reason
Bruce Momjian [Thu, 27 Mar 2003 16:45:01 +0000 (16:45 +0000)]
Attached are two patches for psql's tab-completion.c.
The first cleans up a couple of minor errors and ommissions
and adds tab completion support to more slash commands, e.g.
\dv.
The second is an attempt to add tab completion for schemas
and fully qualified relation names (e.g. public.mytable ).
I think this covers the TODO-item:
"Allow psql to do table completion for SELECT * FROM schema_part and table
completion for SELECT * FROM schema_name."
This happens via union selects querying:
- relation_name in current search path;
- schema_name;
- schema.relation_name
matching the current input string.
E.g:
SELECT p[TAB]
will produce a list of all appropriate relation names in the current search
path which begin with 'p', and also all schema names which begin with 'p';
\d pub[TAB]
will produce any relation names in the current search path and also
any schema names beginning with 'pub';
\d public.[TAB]
will produce a list of all relations in the schema 'public';
\d public.my[TAB]
produces all relation names beginning with 'my' in schema 'public'.
It seems to work for me; comments, suggestions, particularly regarding
the coding and queries, are very welcome.
Note that tables, indexes, views and sequences relations in the
'pg_catalog' namespace are excluded even though they are in
the current search path. I found not doing this produced annoying behaviour
when expanding names beginning with 'p'. People who work with system
tables a lot may not like this though; I can look for another solution
if necessary.
Tom Lane [Tue, 25 Mar 2003 03:16:41 +0000 (03:16 +0000)]
plpgsql can assign to subscripted variables now, e.g.
x[42] := whatever;
The facility is pretty primitive because it doesn't do array slicing and
it has the same semantics as array update in SQL (array must already
be non-null, etc). But it's a start.
Dave Cramer [Tue, 25 Mar 2003 02:28:45 +0000 (02:28 +0000)]
added DISTINCT to the query to get cross reference. This is required when two columns in a table are both foreign keys to another table. From Peter Royal proyal@pace2020.com
Tom Lane [Tue, 25 Mar 2003 00:34:24 +0000 (00:34 +0000)]
Factor out duplicate code for computing values of PLpgSQL_datum items.
This is to help localize the changes needed for adding a new kind of
PLpgSQL_datum (like, say, an array element...)
Tom Lane [Mon, 24 Mar 2003 22:40:14 +0000 (22:40 +0000)]
Ignore SIGXFSZ (if platform has it), so that ulimit violations work like
disk-full conditions instead of provoking a backend crash. Per suggestion
from Frederic Surleau.
Tom Lane [Mon, 24 Mar 2003 21:42:33 +0000 (21:42 +0000)]
Modify keys_are_unique optimization to release buffer pins before it
returns NULL. This avoids out-of-buffers failures during many-way
indexscans, as in Shraibman's complaint of 21-Mar.
Tom Lane [Sun, 23 Mar 2003 23:01:03 +0000 (23:01 +0000)]
Adjust amrescan code so that it's allowed to call index_rescan with a
NULL key pointer, indicating that the existing scan key should be reused.
This behavior isn't used yet but will be needed for my planned fix to
the keys_are_unique code.
Tom Lane [Sun, 23 Mar 2003 05:14:37 +0000 (05:14 +0000)]
Instead of storing pg_statistic stavalues entries as text strings, store
them as arrays of the internal datatype. This requires treating the
stavalues columns as 'anyarray' rather than 'text[]', which is not 100%
kosher but seems to work fine for the purposes we need for pg_statistic.
Perhaps in the future 'anyarray' will be allowed more generally.
Tom Lane [Sat, 22 Mar 2003 17:11:25 +0000 (17:11 +0000)]
Department of second thoughts: probably shouldn't use nth() to get the
appropriate targetlist entry out of the subquery. Use an explicit search
like we do everywhere else.
Tom Lane [Sat, 22 Mar 2003 01:49:38 +0000 (01:49 +0000)]
Adjust subquery qual pushdown rules to be more forgiving: if a qual
refers to a non-DISTINCT output column of a DISTINCT ON subquery, or
if it refers to a function-returning-set, we cannot push it down.
But the old implementation refused to push down *any* quals if the
subquery had any such 'dangerous' outputs. Now we just look at the
output columns actually referenced by each qual expression. More code
than before, but probably no slower since we don't make unnecessary checks.
Peter Eisentraut [Fri, 21 Mar 2003 17:18:34 +0000 (17:18 +0000)]
Make "win" a separate port from "cygwin". This means you can now
configure under native Windows (MinGW that is), but you won't get very far
compiling yet. The dynaloader files are from Jan Wieck's patch set.
Tom Lane [Fri, 21 Mar 2003 01:58:05 +0000 (01:58 +0000)]
Reimplement NUMERIC datatype using base-10000 arithmetic; also improve
some of the algorithms for higher functions. I see about a factor of ten
speedup on the 'numeric' regression test, but it's unlikely that that test
is representative of real-world applications.
initdb forced due to change of on-disk representation for NUMERIC.
Bruce Momjian [Thu, 20 Mar 2003 20:05:32 +0000 (20:05 +0000)]
This is not the only place in the system catalogs where NULL is
effectively used to mean a default value that could also be spelled
out explicitly. (ACLs behave that way, and useconfig/datconfig
do too IIRC.)
It's a bit of a hack, but it saves table space and backend code ---
without this convention the default would have to be inserted "manually"
since we have no mechanism to supply defaults when C code is forming a
new catalog tuple.
I'm inclined to leave the code alone. But Alvaro is right that it'd be
good to point out the 'infinity' option in the CREATE USER and ALTER
USER man pages. (Doc patch please?)
Bruce Momjian [Thu, 20 Mar 2003 19:00:01 +0000 (19:00 +0000)]
The documentation for SELECT is incorrect in a sense: the syntax for a
join is defined as:
from_item [ NATURAL ] join_type from_item
[ ON join_condition | USING ( join_column_list ) ]
However, if the join_type is an INNER or OUTER join, an ON, USING, or
NATURAL clause *must* be specified (it's not optional, as that segment
of the docs suggest).
I'm not exactly sure what the best way to fix this is, so I've attached
a patch adding a FIXME comment to the relevant section of the SGML. If
anyone has any ideas on the proper way to outline join syntax, please
speak up.
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Bruce Momjian [Thu, 20 Mar 2003 18:53:18 +0000 (18:53 +0000)]
Now that the CLUSTER ALL machinery is in place, the clusterdb script can
be simplified (I'd thought that it can even be removed). This patch
does that.
Bruce Momjian [Thu, 20 Mar 2003 18:14:46 +0000 (18:14 +0000)]
I have updated my pg_autovacuum program (formerly pg_avd, the name
changed as per discussion on the patches list).
This version should be a good bit better. It addresses all the issues
pointed out by Neil Conway. Vacuum and Analyze are now handled
separately. It now monitors for xid wraparound. The number of database
connections and queries has been significantly reduced compared the
previous version. I have moved it from bin to contrib. More detail on
the changes are in the TODO file.
I have not tested the xid wraparound code as I have to let my AthlonXP
1600 run select 1 in a tight loop for approx. two days in order to
perform the required 500,000,000 xacts.
Bruce Momjian [Thu, 20 Mar 2003 07:05:21 +0000 (07:05 +0000)]
At present, dates are put into a dump in the format specified by the
default datestyle. This is not portable between installations.
This patch sets DATESTYLE to ISO at the start of a pg_dump, so that the
dates written into the dump will be restorable onto any database,
regardless of how its default datestyle is set.
Bruce Momjian [Thu, 20 Mar 2003 07:02:11 +0000 (07:02 +0000)]
Todo items:
Add ALTER SEQUENCE to modify min/max/increment/cache/cycle values
Also updated create sequence docs to mention NO MINVALUE, & NO MAXVALUE.
New Files:
doc/src/sgml/ref/alter_sequence.sgml
src/test/regress/expected/sequence.out
src/test/regress/sql/sequence.sql
ALTER SEQUENCE is NOT transactional. It behaves similarly to setval().
It matches the proposed SQL200N spec, as well as Oracle in most ways --
Oracle lacks RESTART WITH for some strange reason.
Bruce Momjian [Thu, 20 Mar 2003 06:46:30 +0000 (06:46 +0000)]
Attached is an update to contrib/tablefunc. It implements a new hashed
version of crosstab. This fixes a major deficiency in real-world use of
the original version. Easiest to undestand with an illustration:
Original crosstab:
-------------------------------------------------------------------
SELECT * FROM crosstab(
'SELECT rowid, attribute, val FROM cth ORDER BY 1,2',4)
AS c(rowid text, temperature text, test_result text, test_startdate
text, volts text);
rowid | temperature | test_result | test_startdate | volts
-------+-------------+-------------+----------------+--------
test1 | 42 | PASS | 2.6987 |
test2 | 53 | FAIL | 01 March 2003 | 3.1234
(2 rows)
Hashed crosstab:
-------------------------------------------------------------------
SELECT * FROM crosstab(
'SELECT rowid, attribute, val FROM cth ORDER BY 1',
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
AS c(rowid text, temperature int4, test_result text, test_startdate
timestamp, volts float8);
rowid | temperature | test_result | test_startdate | volts
-------+-------------+-------------+---------------------+--------
test1 | 42 | PASS | | 2.6987
test2 | 53 | FAIL | 2003-03-01 00:00:00 | 3.1234
(2 rows)
Notice that the original crosstab slides data over to the left in the
result tuple when it encounters missing data. In order to work around
this you have to be make your source sql do all sorts of contortions
(cartesian join of distinct rowid with distinct attribute; left join
that back to the real source data). The new version avoids this by
building a hash table using a second distinct attribute query.
The new version also allows for "extra" columns (see the README) and
allows the result columns to be coerced into differing datatypes if they
are suitable (as shown above).
In testing a "real-world" data set (69 distinct rowid's, 27 distinct
categories/attributes, multiple missing data points) I saw about a
5-fold improvement in execution time (from about 2200 ms old, to 440 ms
new).
I left the original version intact because: 1) BC, 2) it is probably
slightly faster if you know that you have no missing attributes.
README and regression test adjustments included. If there are no
objections, please apply.
Bruce Momjian [Thu, 20 Mar 2003 06:43:35 +0000 (06:43 +0000)]
I'm continuing to work on cleaning up code in psql. As things appear
now, my changes seem to work. Some possible minor bugs got squished
on the way but I can't be sure without more feedback from people who
really put the code to the test.
The new patch mostly simplifies variable handling and reduces code
duplication. Changes in the command parser eliminate some redundant
variables (boolean state + depth counter), replaces some
"else if" constructs with switches, and so on. It is meant to be
applied together with my previous patch, although I hope they don't
conflict; I went back to the CVS version for this one.
One more thing I thought should perhaps be changed: an IGNOREEOF
value of n will ignore only n-1 EOFs. I didn't want to touch this
for fear of breaking existing applications, but it does seem a tad
illogical.
Bruce Momjian [Thu, 20 Mar 2003 06:26:30 +0000 (06:26 +0000)]
It has been tested only against CVS backend, however. Some checking of the
changes to the SQL to retrieve attributes for older versions of Postgres is
probably wise. Also, please make sure that I have mapped the storage types
to the correct storage names, as this is relatively poorly documented.
I think that this patch might need to be considered for back-porting to
7.3.3 since at the moment, people will be losing valuable information after
upgrades.
Will dump:
CREATE TABLE test (
a text,
b text,
c text,
d text
);
ALTER TABLE ONLY test ALTER COLUMN a SET STATISTICS 55;
ALTER TABLE ONLY test ALTER COLUMN a SET STORAGE PLAIN;
ALTER TABLE ONLY test ALTER COLUMN b SET STATISTICS 1000;
ALTER TABLE ONLY test ALTER COLUMN c SET STORAGE EXTERNAL;
ALTER TABLE ONLY test ALTER COLUMN d SET STORAGE MAIN;
Bruce Momjian [Thu, 20 Mar 2003 06:03:00 +0000 (06:03 +0000)]
Attached is a patch that limits the range tested by horology to
what is capable using integer-datatime timestamps. It does attempt
to exercise the maximum allowable timestamp range.
Also is a small error check when converting a timestamp from external
to internal format that prevents out of range timestamps from being
entered.
Files patched:
Index: src/backend/utils/adt/timestamp.c
Added range check to prevent out of range timestamps
from being used.
Index: src/test/regress/sql/horology.sql
Index: src/test/regress/expected/horology-no-DST-before-1970.out
Index: src/test/regress/expected/horology-solaris-1947.out
Limited range of timestamps being checked to
Jan 1, 4713 BC to Dec 31, 294276
In creating this patch, I have seen some definite problems with integer
timestamps and how they react when used near their limits. For example,
the following statement gives the correct result:
SELECT timestamp without time zone 'Jan 1, 4713 BC'
+ interval '109203489 days' AS "Dec 31, 294276";
However, this statement which is the logical inverse of the above
gives incorrect results:
SELECT timestamp without time zone '12/31/294276'
- timestamp without time zone 'Jan 1, 4713 BC' AS "109203489 Days";
Bruce Momjian [Thu, 20 Mar 2003 06:00:12 +0000 (06:00 +0000)]
Here's some changes I made last night to psql's common.c (as found in
7.3.2). It removes some code duplication and #ifdeffing, and some
unstructured ugliness such as tacky breaks and an unneeded continue.
Breaks up a large function into smaller functions and reduces required
nesting levels, and kills a variable or two.
Bruce Momjian [Thu, 20 Mar 2003 05:19:26 +0000 (05:19 +0000)]
Peter found bug in the to_char() routine for PL/MI options. This
patch fix it -- but this patch doesn't contains tests or docs fixes. I
will send it later.
Fixed outputs:
select to_char(x, '9999.999') as x,
to_char(x, 'S9999.999') as s,
to_char(x, 'SG9999.999') as sg,
to_char(x, 'MI9999.999') as mi,
to_char(x, 'PL9999.999') as pl,
to_char(x, 'PLMI9999.999') as plmi,
to_char(x, '9999.999SG') as sg2,
to_char(x, '9999.999PL') as pl2,
to_char(x, '9999.999MI') as mi2 from num;
Bruce Momjian [Thu, 20 Mar 2003 05:18:15 +0000 (05:18 +0000)]
> > - Move SEQ_MAXVALUE, SEQ_MINVALUE definitions to sequence.h
> >
> > - Add check in pg_dump to see if the value returned is the max /min
> > values and replace with NO MAXVALUE, NO MINVALUE.
> >
> > - Change START and INCREMENT to use START WITH and INCREMENT BY syntax.
> > This makes it a touch easier to port to other databases with sequences
> > (Oracle). PostgreSQL supports both syntaxes already.
>
> + char bufm[100],
> + bufx[100];
>
> This seems to be an arbitary size. Why not set it to the actual maximum
> length?
>
> Also:
>
> + snprintf(bufm, 100, INT64_FORMAT, SEQ_MINVALUE);
> + snprintf(bufx, 100, INT64_FORMAT, SEQ_MAXVALUE);
>
> sizeof(bufm), sizeof(bufx) is probably the more
> maintenance-friendly/standard way to do it.
I changed the code to use sizeof - but will wait for a response from
Peter before changing the size. It's consistent throughout the sequence
code to be 100 for this purpose.
Bruce Momjian [Thu, 20 Mar 2003 05:00:14 +0000 (05:00 +0000)]
(Now featuring documentation: fixed some typos, expanded the
Envrironment and Files section, explained exactly what -w
does)
This is a patch which allows pg_ctl to make an intelligent
guess as to the proper port when running 'psql -l' to
determine if the database has started up (the -w flag).
The environment variable PGPORT is used. If that is not found,
it checks if a specific port has been set inside the postgresql.conf
file. If it is has not, it uses the port that Postgres was
compiled with.
Bruce Momjian [Thu, 20 Mar 2003 04:52:35 +0000 (04:52 +0000)]
This trivial cleans up a little bit of the code in
src/test/regress/regress.c (e.g. removing K & R style parameter
declarations, improving sprintf() usage, etc.)
Bruce Momjian [Thu, 20 Mar 2003 04:51:44 +0000 (04:51 +0000)]
> I can see a couple possible downsides: (a) the library might have some
> weird behavior across fork boundaries; (b) the additional memory space
> that has to be duplicated into child processes will cost something per
> child launch, even if the child never uses it. But these are only
> arguments that it might not *always* be a prudent thing to do, not that
> we shouldn't give the DBA the tool to do it if he wants. So fire away.
Here is a patch for the above, including a documentation update. It
creates a new GUC variable "preload_libraries", that accepts a list in
the form:
If ":initfunc" is omitted or not found, no initialization function is
executed, but the library is still preloaded. If "$libdir/mylib" isn't
found, the postmaster refuses to start.
In my testing with PL/R, it reduces the first call to a PL/R function
(after connecting) from almost 2 seconds, down to about 8 ms.
Bruce Momjian [Thu, 20 Mar 2003 04:49:18 +0000 (04:49 +0000)]
> Mph. It fails for me too when I use --enable-integer-datetimes. Looks
> like that patch still needs some work...
Yeah. I'm really, really, *really* sorry for submitting it in the state
it was in. I shouldn't have done that just before moving to another
country. I found the problem last night, but couldn't get to a Net
connection until now.
The problem is in src/bin/psql/common.c, around line 250-335 somewhere
depending on the version. The 2nd and 3rd clauses of the "while" loop
condition:
Bruce Momjian [Thu, 20 Mar 2003 04:39:27 +0000 (04:39 +0000)]
Just some fixups to a couple contrib directories I was trying out.
. replace CREATE OR REPLACE AGGREGATE with a separate DROP and CREATE
. add DROP for all CREATE OPERATORs
. use IMMUTABLE and STRICT instead of WITH (isStrict)
. add IMMUTABLE and STRICT to int_array_aggregate's accumulator function
Peter Eisentraut [Tue, 18 Mar 2003 22:19:47 +0000 (22:19 +0000)]
Reimplement create and drop scripts in C, to reduce repetitive
connections, increase robustness, add NLS, and prepare for Windows port.
(vacuumdb and clusterdb will follow later.)
Peter Eisentraut [Tue, 18 Mar 2003 22:15:44 +0000 (22:15 +0000)]
Make the printing code somewhat more independent by not relying on
functions and global variables from the rest of psql. Also clean up some
data type mismatches created by the last pager patch.