Thread: Upgrades for 6.4.1

Upgrades for 6.4.1

From
Bruce Momjian
Date:
I have made the needed changes for 6.4.1.

Would people please review the attached TODO list, and tell me what TODO
items can be removed, and if the attached 6.4.1 changes are correct.

I suspect a few of the TODO items I added after 6.4 was release have
been fixed, but am not sure.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
TODO list for PostgreSQL
========================
Last updated:        Fri Dec 18 00:15:58 EST 1998

Current maintainer:    Bruce Momjian (maillist@candle.pha.pa.us)

The most recent version of this document can be viewed at
the PostgreSQL WWW site, http://www.postgreSQL.org.

THE CHANGES FOR 6.4.1 APPEAR AT THE END OF THIS DOCUMENT

A dash(-) marks changes to be in the next release.

Developers who have claimed items are:
-------------------------------------
    * Billy is Billy G. Allie <Bill.Allie@mug.org>
    * Brook is Brook Milligan <brook@trillium.NMSU.Edu>
    * Bruce is Bruce Momjian<maillist@candle.pha.pa.us>
    * Bryan is Bryan Henderson<bryanh@giraffe.netgate.net>
    * D'Arcy is D'Arcy J.M. Cain <darcy@druid.net>
    * Dan is Dan McGuirk <mcguirk@indirect.com>
    * Darren is Darren King <darrenk@insightdist.com>
    * David is David Hartwig <daveh@insightdist.com>
    * Edmund is Edmund Mergl <E.Mergl@bawue.de>
    * Goran is Goran Thyni <goran@bildbasen.se>
    * Henry is Henry B. Hotz <hotz@jpl.nasa.gov>
    * Jan is Jan Wieck <wieck@sapserv.debis.de>
    * Jun is Jun Kuwamura <juk@rccm.co.jp>
    * Maarten is Maarten Boekhold <maartenb@dutepp0.et.tudelft.nl>
     * Marc is Marc Fournier <scrappy@hub.org>
     * Martin is Martin S. Utesch <utesch@aut.tu-freiberg.de>
    * Massimo Dal Zotto <dz@cs.unitn.it>
    * Michael is Michael Meskes <meskes@debian.org>
    * Oleg is Oleg Bartunov <oleg@sai.msu.su>
    * Paul is Paul M. Aoki <aoki@CS.Berkeley.EDU>
    * Peter is Peter T Mount <peter@retep.org.uk>
    * Phil is Phil Thompson <phil@river-bank.demon.co.uk>
    * Ryan is Ryan Kirkpatrick <rkirkpat@nag.cs.colorado.edu>
    * Soo-Ho Ok <shok@detc.dongeui-tc.ac.kr>
    * Stefan Simkovics <ssimkovi@rainbow.studorg.tuwien.ac.at>
    * Sven is Sven Verdoolaege <skimo@breughel.ufsia.ac.be>
    * Tatsuo is Tatsuo Ishii <t-ishii@sra.co.jp>
    * Tom is Tom Lane <tgl@sss.pgh.pa.us>
    * Thomas is Thomas Lockhart <tgl@mythos.jpl.nasa.gov>
    * TomH is Tom I Helbekkmo <tih@Hamartun.Priv.NO>
    * Vadim is "Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>

RELIABILITY
-----------
* Overhaul mdmgr/smgr to fix double unlinking and double opens, cleanup
* Overhaul bufmgr/lockmgr/transaction manager
* Remove EXTEND?
* Can lo_export()/lo_import() read/write anywhere, causing a security problem?
* Tables that start with xinv confused to be large objects
* Two and three dimmensional arrays display improperly, missing {}
* GROUP BY in INSERT INTO table SELECT * FROM table2 fails
* Prevent auto-table reference, like SELECT table.col WHERE col = 3 (?)
* SELECT * FROM table WHERE int4_column = '1' fails
* SELECT a[1] FROM test fails, it needs test.a[1]
* UPDATE table SET table.value = 3 fails
* User who can create databases can modify pg_database table
* optimizer memory exhaustion with many OR's
* elog() does not free all its memory(Jan)
* views on subselects fail
* disallow inherited columns with the same name as new columns
* recover or force failure when disk space is exhausted
* default char() value not to full length crashes server on some OS's
* allow UPDATE using aggregate to affect all rows, not just one
* computations in views fail:
    create view test as select usesysid * usesysid from pg_shadow;
* views containing aggregates sometimes fail(Jan)
* ALTER TABLE ADD COLUMN does not honor DEFAULT, add CONSTRAINT
* SELECT DISTINCT i FROM dtest ORDER BY j generates strange output
* fix memory leak in aborted transactions
* array index references without table name cause problems
* aggregates on array indexes crash backend
* subqueries containing HAVING return incorrect results
* DEFAULT handles single quotes in value by requiring too many quotes
* make CURSOR valid even after you hit end of cursor
* views with spaces in view name fail when referenced
* plpgsql does not handle quoted mixed-case identifiers
* do not allow bpchar column creation without length

ENHANCEMENTS
------------
* Replace table-level locking with row or page-level locking(Vadim)
* Transaction log, so re-do log can be on a separate disk
* Allow transaction commits with rollback with no-fsync performance
* More access control over who can create tables and access the database
* Add full ANSI SQL capabilities
    * add OUTER joins, left and right (Thomas)
    * add INTERSECTS, SUBTRACTS(Stephan)
    * add temporary tables
    * add sql3 recursive unions
    * add the concept of dataspaces
    * add DECIMAL, NUMERIC, DOUBLE PRECISION, BIT, BIT VARYING
     * NCHAR (as distinguished from ordinary varchar),
    * DOMAIN capability
* Allow compression of large fields or a compressed field type
* Fix the rules system(Jan,Soo-Ho)
    * add CONSTRAINT
* Full set of text operations and functions
    * word searches, concat,max() on text, char
* Large objects
    * Fix large object mapping scheme, own reltype(Peter)
    * Allow large text type to use large objects(Peter)
    * not to stuff everything as files in a single directory
    * Fix large object memory leaks
    * delete orphaned large objects
* Better interface for adding to pg_group
* Make MONEY/DECIMAL have a defined precision
* Fix tables >2G, or report error when 2G size reached
    (fix lseek()/off_t, mdextend()/RELSEG_SIZE)
* Add REGEX internationalization
* allow row re-use without vacuum, maybe?(Vadim)
* Add word index for text fields, maybe with trigrams, i.e.:
    * ' (cat | dog) & ! fox ' meaning text has cat aor dog, but not fox
* Populate backend status area and write program to dump status data
* Add ALTER TABLE DROP/ALTER COLUMN feature
* Allow INSERT INTO ... SELECT to convert column types
* Add syslog functionality(Marc)
* Add STDDEV/VARIANCE() function for standard deviation computation/variance
* add UNIQUE capability to non-btree indexes
* make number of backends a config parameter, storage/sinvaladt.h:MaxBackendId
* certain indexes will not shrink, i.e. oid indexes with many inserts
* make NULL's come out at the beginning or end depending on the ORDER BY direction
* change the library/backend interface to use network byte order
* Restore unused oid's on backend exit if no one else has gotten oids
* remove non-standard types from the system, and make them loadable
* have UPDATE/DELETE clean out indexes
* allow WHERE restriction on ctid
* allow pg_descriptions when creating types, tables, columns, and functions
* Fix compile and security of Kerberos/GSSAPI code
* Allow psql to print nulls as distinct from ""(?)
* Allow variable casts with BETWEEN 'today'::asbtime AND 'today'::abstime
* Allow INSERT INTO ... SELECT ... FROM view to work
* Make VACUUM on database not lock pg_class
* Make VACUUM ANALYZE only use a readlock
* Allow cursors to be DECLAREd/OPENed/CLOSEed outside transactions
* Allow installation data block size and max tuple size configuration(Darren)
* Allow views on a UNION
* Allow DISTINCT on view
* Allow views of aggregate columns
* Allow variable block sizes(Darren)
* System tables are now more update-able from SQL(Jan)
* New pg_shadow file, pg_user is now a view of pg_shadow(Jan)
* Allow flag to control COPY input/output of NULLs
* Allow CLUSTER on all tables at once, and improve CLUSTER
* Change all references of Postgres to PostgreSQL, including binary names
* Add ELOG_TIMESTAMPS to elog()(?)
* Change LOCK tablename to LOCK TABLE tablename(?)
* Allow max tuple length to be changed(Darren)
* Have psql with no database name not connect to username as default(?)
* Allow subqueries in target list
* Granting permissions to another user looses permissions for the owner
* Allow queries across multiple databases
* Add replication of distributed databases
* Allow table destruction/alter to be rolled back
* Add pg_attribute.atttypmod/Resdom->restypmod to PGresult structure
* Generate error on CREATE OPERATOR of ~~, ~ and and ~*
* Allow constraint NULL just as we honor NOT NULL
* Add version number in startup banners for psql and postmaster
* Restructure storing of GRANT permission information to allow +-=
* allow psql \copy to allow delimiters
* allow international error message support and add error codes
* allow usernames with dashes(GRANT fails)
* add a function to return the last inserted oid, for use in psql scripts
* allow creation of functional indexes to use default types
* put sort files, large objects in their on directory
* CREATE VIEW myview (name) AS SELECT lname FROM wages fails
* do autocommit so always in a transaction block
* add SIMILAR TO to allow character classes, 'pg_[a-c]%'
* multi-verion concurrency control(Vadim)
* improve reporting of syntax errors by showing location of error in query
* allow chaining of pages to allow >8k tuples
* no min/max for oid type
* remove un-needed conversion functions
* redesign the function call interface to handle NULLs better(Jan)
* permissions on indexes - prevent them?
* allow multiple generic operators in expressions without the use of parentheses
* document/trigger/rule so changes to pg_shadow create pg_pwd
* improve group handling
* generate postmaster pid file and remove flock/fcntl lock code
* improve PRIMARY KEY handling
* add ability to specifiy location of lock/socket files
* psql \d on index with char()/varchar() fields shows improper length
* disallow LOCK outside a transaction, change message to LOCK instead of DELETE
* Fix roundoff problems in "cash" datatype
* fix any sprintf() overruns
* add portable vsnprintf()
* auto-destroy sequence on SERIAL removal
* CREATE TABLE inside aborted transaction causes stray table file
* allow user to define char1 column

PERFORMANCE
-----------
* Use indexes in ORDER BY for restrictive data sets, min(), max()
* Allow LIMIT ability on single-table queries that have no ORDER BY or
    a matching index
* Pull requested data directly from indexes, bypassing heap data
* Prevent psort() usage when query already using index matching ORDER BY
* Optimizing disjunctive queries
* Fix bushy-plans
* Prevent fsync in SELECT-only queries
* Cache most recent query plan(s?)
* Shared catalog cache, reduce lseek()'s by caching table size in shared area
* Allow compression of log and meta data
* Add FILLFACTOR to index creation
* update pg_statistic table to remove operator column
* make index creation use psort code, because it is now faster(Vadim)
* remove fork()/exec() of backend and make it just fork()
* Add base table name to \d index
* Allow char() not to use variable-sized header to reduce disk size
* Do async I/O to do better read-ahead of data
* Fix optmizer problem with self-table joins
* Fix memory exhaustion when using many OR's
* Use spin locks only on multi-CPU systems, yield CPU instead
* Get faster regex() code from Henry Spencer <henry@zoo.utoronto.ca>
    when it is available
* use mmap() rather than SYSV shared memory(?)
* use index to restrict rows returned by multi-key index when used with
    non-consecutive keys or OR clauses, so fewer heap accesses
* use index with constants on functions

DOCUMENTATION
-------------
* Update usermanual source(many)
* added features used in grammer but not in docs, like :: and CAST
* Add keyword list to documentation, already in /tools
* Add 'man pgsql' to show all manual page names
* Add use of 'const' for varibles in source tree


=============================================================================

CHANGES IN THE 6.4.1 RELEASE
----------------------------
Add pg_dump -N flag to force double quotes around identifiers.  This is
    the default
Fix for NOT in where clause causing crash(Bruce)
EXPLAIN VERBOSE coredump fix(Vadim)
Fix shared-library problems on Linux
Fix test for table existance to allow mixed-case and whitespace in
    the table name
Fix a couple of pg_dump bugs
Fix for creating tables with constraints when table name is mixed-case(Billy)
Configure matches template/.similar entries better(Tom)
Change builtin function names from SPI_* to spi_*
OR WHERE clause fix(Vadim)
Fixes for mixed-case table names(Billy)
contrib/linux/postgres.init.csh/sh fix
libpq memory overrun fix
SunOS fixes(Tom)
Change exp() behavior to generate error on underflow
pg_dump fixes for memory leak, inheritance constraints, layout change
update pgaccess to 0.93
Fix prototype for 64-bit platforms
Multi-byte fixes(Tatsuo)
New ecpg man page
Fix memory overruns(Tatsuo)
Fix for lo_import() crash(Bruce)
Better search for install program(Tom)
Timezone fixes(Tom)
HPUX fixes(Tom)
Use implicit type coersion for matching DEFAULT values
Add routines to help with single-byte (internal) character type(Thomas)
Compilation of libpq for Win32 fixes(Magnus)
Upgrade to PyGreSQL 2.2(D'Arcy)

Re: [HACKERS] Upgrades for 6.4.1

From
"Thomas G. Lockhart"
Date:
> * Two and three dimmensional arrays display improperly, missing {}

My simple test case shows as many brackets as I expect. Can someone
remember what this was about? Or at least take the extra "m" out of
"dimensional"? :)

> * SELECT * FROM table WHERE int4_column = '1' fails

postgres=> select * from x where i = '1';
i
-
1
(1 row)

Works now, no? The new type coersion stuff...

> * default char() value not to full length crashes server on some OS's

postgres=> create table x (c char(10) default 'abc');
CREATE
postgres=> insert into x default values;
INSERT 901452 1
postgres=> select * from x;
c
---
abc
(1 row)

I distinctly recall fixing this, or watching someone else do it...

> * SELECT DISTINCT i FROM dtest ORDER BY j generates strange output

In my simple test case, it orders by j, then only shows i. Is that
strange? What did it used to do? The current behavior makes sense, if it
should be allowed at all...

> * views with spaces in view name fail when referenced

Still a problem.

> * plpgsql does not handle quoted mixed-case identifiers

Ditto.

> * do not allow bpchar column creation without length

? char is now the same as char(1), which should solve this, if that's
what it means...

> ENHANCEMENTS
> ------------
> * Add full ANSI SQL capabilities
>         * add OUTER joins, left and right (Thomas)
>         * add INTERSECTS, SUBTRACTS(Stephan)
>         * add temporary tables
>         * add sql3 recursive unions
>         * add the concept of dataspaces
>         * add DECIMAL, NUMERIC, DOUBLE PRECISION, BIT, BIT VARYING

We've got DOUBLE PRECISION, DECIMAL, and NUMERIC (the latter two are
brain-damaged though)

>         * add CONSTRAINT

Already here, from Vadim.

> * Full set of text operations and functions
>         * word searches, concat,max() on text, char

Not sure about "word searches", others are done. v6.4.1 will require a
dump/reload to find min/max for string types. In fact, can someone test
to make sure it is ok that I added these to the pg_aggregate system
table? And a couple of support functions to pg_proc? Since they weren't
available before, and since everything else still works, I thought it
might be OK to include them. But I can rip them out of the system tables
if that would be best for v6.4.1.

> * Add word index for text fields, maybe with trigrams, i.e.:
>   * ' (cat | dog) & ! fox ' meaning text has cat aor dog, but not fox

How is this different from the "word searches" above?

> * Allow INSERT INTO ... SELECT to convert column types

Probably does. The new type coersion stuff :)

> * remove non-standard types from the system, and make them loadable

This made the ToDo list, but I'm not sure we want to do it. Native types
still have a few advantages over loadable types, and our types are one
of our strongest features...

> * Allow variable casts with BETWEEN 'today'::asbtime AND 'today'::abstime

postgres=> select 'yes' where 'now'::datetime   between 'today'::datetime and 'tomorrow'::datetime;
?column?
--------
yes
(1 row)

What did this item mean??

> * Change all references of Postgres to PostgreSQL, including binary 
>     names

I've standardized all Postgres docs to use "Postgres" within the body,
and "PostgreSQL" for titles and in the introduction.

> * Change LOCK tablename to LOCK TABLE tablename(?)

Both syntaxes are now legal and accepted.

> * Generate error on CREATE OPERATOR of ~~, ~ and and ~*

? Because gram.y mucks around with them?

> * Allow constraint NULL just as we honor NOT NULL

Fundamental yacc problem with this as I recall. Gives rise to
shift/reduce problems since it is ambiguous with other uses of "NULL" in
the same area.

> * do autocommit so always in a transaction block

This is already the case, right? Every statement has an implicit
begin/end around it, unless it is within an explicit begin/end. Or does
this mean something else?

> * no min/max for oid type

Fixed. New type coersion stuff.

> * remove un-needed conversion functions

? An explicit conversion function is always/usually faster than one
which requires multiple steps.

> DOCUMENTATION
> -------------
> * Add keyword list to documentation, already in /tools

Done. In the User's Guide chapter on "Syntax".

> CHANGES IN THE 6.4.1 RELEASE
> ----------------------------
> Add pg_dump -N flag to force double quotes around identifiers.  This is
>         the default

(Thomas) in case you have room :)

> Fix test for table existance to allow mixed-case and whitespace in
>         the table name

Ditto. At least when combined with Billy's fixes to make a complete set.

> Fix for creating tables with constraints when table name is mixed-case(Billy)
> Fixes for mixed-case table names(Billy)

These all sure sound similar...

> contrib/linux/postgres.init.csh/sh fix

(Thomas)

> Change exp() behavior to generate error on underflow

(Tom), right?

> Timezone fixes(Tom)

? Not sure we see an improvement yet, if this is the date->datetime
conversion problem reported by Oleg.

> Use implicit type coersion for matching DEFAULT values

(Thomas) but wasn't this mentioned above somewhere? Maybe not...
                   - Tom


Re: [HACKERS] Upgrades for 6.4.1

From
Bruce Momjian
Date:
> > * Two and three dimmensional arrays display improperly, missing {}
>
> My simple test case shows as many brackets as I expect. Can someone
> remember what this was about? Or at least take the extra "m" out of
> "dimensional"? :)

Bug report attached.  It is an old one.

>
> > * SELECT * FROM table WHERE int4_column = '1' fails
>
> postgres=> select * from x where i = '1';
> i
> -
> 1
> (1 row)
>
> Works now, no? The new type coersion stuff...

Item removed.

>
> > * default char() value not to full length crashes server on some OS's
>
> postgres=> create table x (c char(10) default 'abc');
> CREATE
> postgres=> insert into x default values;
> INSERT 901452 1
> postgres=> select * from x;
> c
> ---
> abc
> (1 row)
>
> I distinctly recall fixing this, or watching someone else do it...

Removed.

>
> > * SELECT DISTINCT i FROM dtest ORDER BY j generates strange output
>
> In my simple test case, it orders by j, then only shows i. Is that
> strange? What did it used to do? The current behavior makes sense, if it
> should be allowed at all...

Removed.

>
> > * views with spaces in view name fail when referenced
>
> Still a problem.

Ok.

>
> > * plpgsql does not handle quoted mixed-case identifiers
>
> Ditto.

Ok.

>
> > * do not allow bpchar column creation without length
>
> ? char is now the same as char(1), which should solve this, if that's
> what it means...

Try:
    create table x(y bpchar);

and try pg_dumping it.

>
> > ENHANCEMENTS
> > ------------
> > * Add full ANSI SQL capabilities
> >         * add OUTER joins, left and right (Thomas)
> >         * add INTERSECTS, SUBTRACTS(Stephan)
> >         * add temporary tables
> >         * add sql3 recursive unions
> >         * add the concept of dataspaces
> >         * add DECIMAL, NUMERIC, DOUBLE PRECISION, BIT, BIT VARYING
>
> We've got DOUBLE PRECISION, DECIMAL, and NUMERIC (the latter two are
> brain-damaged though)

Removed.

>
> >         * add CONSTRAINT
>
> Already here, from Vadim.

Removed.

>
> > * Full set of text operations and functions
> >         * word searches, concat,max() on text, char
>
> Not sure about "word searches", others are done. v6.4.1 will require a
> dump/reload to find min/max for string types. In fact, can someone test
> to make sure it is ok that I added these to the pg_aggregate system
> table? And a couple of support functions to pg_proc? Since they weren't
> available before, and since everything else still works, I thought it
> might be OK to include them. But I can rip them out of the system tables
> if that would be best for v6.4.1.

Removed.

>
> > * Add word index for text fields, maybe with trigrams, i.e.:
> >   * ' (cat | dog) & ! fox ' meaning text has cat aor dog, but not fox
>
> How is this different from the "word searches" above?

Removed.

>
> > * Allow INSERT INTO ... SELECT to convert column types
>
> Probably does. The new type coersion stuff :)

Removed.

>
> > * remove non-standard types from the system, and make them loadable
>
> This made the ToDo list, but I'm not sure we want to do it. Native types
> still have a few advantages over loadable types, and our types are one
> of our strongest features...

Removed.

>
> > * Allow variable casts with BETWEEN 'today'::asbtime AND 'today'::abstime
>
> postgres=> select 'yes' where 'now'::datetime
>    between 'today'::datetime and 'tomorrow'::datetime;
> ?column?
> --------
> yes
> (1 row)
>
> What did this item mean??

Some problem with OR gram.y processing.  Fixed, I guess.

>
> > * Change all references of Postgres to PostgreSQL, including binary
> >     names
>
> I've standardized all Postgres docs to use "Postgres" within the body,
> and "PostgreSQL" for titles and in the introduction.

Removed.

>
> > * Change LOCK tablename to LOCK TABLE tablename(?)
>
> Both syntaxes are now legal and accepted.

Removed.

>
> > * Generate error on CREATE OPERATOR of ~~, ~ and and ~*
>
> ? Because gram.y mucks around with them?

Ok.

>
> > * Allow constraint NULL just as we honor NOT NULL
>
> Fundamental yacc problem with this as I recall. Gives rise to
> shift/reduce problems since it is ambiguous with other uses of "NULL" in
> the same area.

Ok.

>
> > * do autocommit so always in a transaction block
>
> This is already the case, right? Every statement has an implicit
> begin/end around it, unless it is within an explicit begin/end. Or does
> this mean something else?

Not the same.  Withouth autocommit on, you start in a transaction, and
have to issue a commit to commit the changes, and you are back in a new
transaction.  Ingres SQL does this.

>
> > * no min/max for oid type
>
> Fixed. New type coersion stuff.

Removed.

>
> > * remove un-needed conversion functions
>
> ? An explicit conversion function is always/usually faster than one
> which requires multiple steps.

Added word 'where appropriate'.  We just have tons of them. Would be
nice to pair them down a little.

>
> > DOCUMENTATION
> > -------------
> > * Add keyword list to documentation, already in /tools
>
> Done. In the User's Guide chapter on "Syntax".

Removed.

>
> > CHANGES IN THE 6.4.1 RELEASE
> > ----------------------------
> > Add pg_dump -N flag to force double quotes around identifiers.  This is
> >         the default
>
> (Thomas) in case you have room :)


Ok.

>
> > Fix test for table existance to allow mixed-case and whitespace in
> >         the table name
>
> Ditto. At least when combined with Billy's fixes to make a complete set.

Ok.  I loose the committers name in the script I wrote, and I am not
sure who did it.  We also commit other's patches, so even knowing the
committers name sometimes doesn't help.  I know my fixes, however. :-)

>
> > Fix for creating tables with constraints when table name is mixed-case(Billy)
> > Fixes for mixed-case table names(Billy)
>
> These all sure sound similar...

Removed.

>
> > contrib/linux/postgres.init.csh/sh fix
>
> (Thomas)

Added

>
> > Change exp() behavior to generate error on underflow
>
> (Tom), right?
OK.

>
> > Timezone fixes(Tom)
>
> ? Not sure we see an improvement yet, if this is the date->datetime
> conversion problem reported by Oleg.

We'll see.

>
> > Use implicit type coersion for matching DEFAULT values
>
> (Thomas) but wasn't this mentioned above somewhere? Maybe not...

Not mentioned.  Updated RELEASE tree and web page with new TODO copy.


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] Upgrades for 6.4.1

From
Hannu Krosing
Date:
Bruce Momjian wrote:
> >
> > > ENHANCEMENTS
> > > ------------
> > > * Add full ANSI SQL capabilities
> > >         * add OUTER joins, left and right (Thomas)
> > >         * add INTERSECTS, SUBTRACTS(Stephan)
> > >         * add temporary tables
> > >         * add sql3 recursive unions
> > >         * add the concept of dataspaces
> > >         * add DECIMAL, NUMERIC, DOUBLE PRECISION, BIT, BIT VARYING
> >
> > We've got DOUBLE PRECISION, DECIMAL, and NUMERIC (the latter two are
> > brain-damaged though)
> 
> Removed.

If it's brain-damaged should'nt it be justr Replaced instead of Removed 

> 
> >
> > >         * add CONSTRAINT
> >
> > Already here, from Vadim.
> Removed.
> 

AFAIK, CONSTRAINTS ar currently available only at table create time ?

So once you have (or have.nt) them, you must stay with it ;(

creating/dropping constraints should be made to work in ALTER TABLE as
well


------------------
Hannu


Re: [HACKERS] Upgrades for 6.4.1

From
Bruce Momjian
Date:
> Bruce Momjian wrote:
> > >
> > > > ENHANCEMENTS
> > > > ------------
> > > > * Add full ANSI SQL capabilities
> > > >         * add OUTER joins, left and right (Thomas)
> > > >         * add INTERSECTS, SUBTRACTS(Stephan)
> > > >         * add temporary tables
> > > >         * add sql3 recursive unions
> > > >         * add the concept of dataspaces
> > > >         * add DECIMAL, NUMERIC, DOUBLE PRECISION, BIT, BIT VARYING
> > >
> > > We've got DOUBLE PRECISION, DECIMAL, and NUMERIC (the latter two are
> > > brain-damaged though)
> > 
> > Removed.
> 
> If it's brain-damaged should'nt it be justr Replaced instead of Removed 

We just need to add better precision to the type.

> 
> > 
> > >
> > > >         * add CONSTRAINT
> > >
> > > Already here, from Vadim.
> > Removed.
> > 
> 
> AFAIK, CONSTRAINTS ar currently available only at table create time ?
> 
> So once you have (or have.nt) them, you must stay with it ;(
> 
> creating/dropping constraints should be made to work in ALTER TABLE as
> well

Already on list:
* ALTER TABLE ADD COLUMN does not honor DEFAULT, add CONSTRAINT

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] Upgrades for 6.4.1

From
Brook Milligan
Date:
Would people please review the attached TODO list, and tell me what TODO  items can be removed, and if the attached
6.4.1changes are correct.
 

It looks from your list like a couple of patches I sent in have not
made it in for one reason or another, though I haven't checked the
source yet but never saw any acknowledgement.

Here are descriptions:

- the first patch is just to preven listing the perl warning in the make output unless it is actually emitted by the
make. this may prevent new users from being confused by the warning in their output when in fact is is just echoing
partof a command not actually run.
 

- the second patch (to 2 files) just enables building/installing pgaccess if TCL and TK are available.  a Makefile is
createdto do this, but you may wish to change the heading information in it since I just copied another Makefile to use
asa template.
 

I hope these make it into 6.4.1.

Cheers,
Brook

===========================================================================
$NetBSD$

--- interfaces/Makefile.orig    Tue Oct 27 14:27:36 1998
+++ interfaces/Makefile    Fri Nov  6 23:12:08 1998
@@ -48,11 +48,11 @@perl5/Makefile: perl5/Makefile.PL    cd perl5 && perl Makefile.PL
-install-perl5:
+install-perl5: perl5/Makefile    $(MAKE) -C perl5 clean    cd perl5 && POSTGRES_HOME="$(POSTGRESDIR)" perl Makefile.PL
  $(MAKE) -C perl5 all
 
-    if [ -w `sed -n -e 's/^ *SITELIBEXP *= *//p' perl5/Makefile` ]; then \
+    @if [ -w `sed -n -e 's/^ *SITELIBEXP *= *//p' perl5/Makefile` ]; then \        $(MAKE) $(MFLAGS) -C perl5 install;
\       rm -f perl5/Makefile; \    else \
 
===========================================================================
$NetBSD$

--- bin/Makefile.orig    Sat Jul 25 22:31:08 1998
+++ bin/Makefile    Mon Nov  9 08:35:00 1998
@@ -25,6 +25,9 @@# TCL/TK programs#ifeq ($(USE_TCL), true)
+ifeq ($(USE_TK), true)
+    DIRS += pgaccess
+endif    DIRS += pgtclshendif
$NetBSD$

--- bin/pgaccess/Makefile.orig    Fri Nov  6 23:43:38 1998
+++ bin/pgaccess/Makefile    Fri Nov  6 23:46:26 1998
@@ -0,0 +1,21 @@
+#-------------------------------------------------------------------------
+#
+# Makefile
+#    Makefile for pgaccess
+#
+# Copyright (c) 1994, Regents of the University of California
+#
+# IDENTIFICATION
+#    $Header: /usr/local/cvsroot/pgsql/src/interfaces/libpgtcl/Makefile.in,v 1.33 1998/10/19 00:00:41 tgl Exp $
+#
+#-------------------------------------------------------------------------
+
+SRCDIR= ../..
+include $(SRCDIR)/Makefile.global
+
+.PHONY: all clean
+all clean: 
+
+.PHONY: install
+install: pgaccess.tcl
+    $(INSTALL) $(INSTL_EXE_OPTS) pgaccess.tcl $(BINDIR)/pgaccess
===========================================================================


Re: [HACKERS] Upgrades for 6.4.1

From
Bruce Momjian
Date:
Applied.

Both of them somehow were not applied.  Not sure how I missed them.

Sorry and thanks.

>    Would people please review the attached TODO list, and tell me what TODO
>    items can be removed, and if the attached 6.4.1 changes are correct.
> 
> It looks from your list like a couple of patches I sent in have not
> made it in for one reason or another, though I haven't checked the
> source yet but never saw any acknowledgement.
> 
> Here are descriptions:
> 
> - the first patch is just to preven listing the perl warning in the
>   make output unless it is actually emitted by the make.  this may
>   prevent new users from being confused by the warning in their output
>   when in fact is is just echoing part of a command not actually run.
> 
> - the second patch (to 2 files) just enables building/installing
>   pgaccess if TCL and TK are available.  a Makefile is created to do
>   this, but you may wish to change the heading information in it since
>   I just copied another Makefile to use as a template.
> 
> I hope these make it into 6.4.1.


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] Upgrades for 6.4.1

From
Bruce Momjian
Date:
Constantin, should you add this Makefile to the pgaccess distribution?

I am not sure.  It is in the PostgreSQL source tree.

> 
> --- bin/pgaccess/Makefile.orig    Fri Nov  6 23:43:38 1998
> +++ bin/pgaccess/Makefile    Fri Nov  6 23:46:26 1998
> @@ -0,0 +1,21 @@
> +#-------------------------------------------------------------------------
> +#
> +# Makefile
> +#    Makefile for pgaccess
> +#
> +# Copyright (c) 1994, Regents of the University of California
> +#
> +# IDENTIFICATION
> +#    $Header: /usr/local/cvsroot/pgsql/src/interfaces/libpgtcl/Makefile.in,v 1.33 1998/10/19 00:00:41 tgl Exp $
> +#
> +#-------------------------------------------------------------------------
> +
> +SRCDIR= ../..
> +include $(SRCDIR)/Makefile.global
> +
> +.PHONY: all clean
> +all clean: 
> +
> +.PHONY: install
> +install: pgaccess.tcl
> +    $(INSTALL) $(INSTL_EXE_OPTS) pgaccess.tcl $(BINDIR)/pgaccess
> ===========================================================================
> 
> 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] Upgrades for 6.4.1

From
Constantin Teodorescu
Date:
Bruce Momjian wrote:
> 
> Constantin, should you add this Makefile to the pgaccess distribution?
> 
> I am not sure.  It is in the PostgreSQL source tree.

Yes. I will install pgaccess.tcl as executable pgaccess and it seems ok.
I will add it to my usual tar.gz

-- 
Constantin Teodorescu
FLEX Consulting Braila, ROMANIA


Re: [HACKERS] Upgrades for 6.4.1

From
jwieck@debis.com (Jan Wieck)
Date:
> > > >         * add DECIMAL, NUMERIC, DOUBLE PRECISION, BIT, BIT VARYING
> > >
> > > We've got DOUBLE PRECISION, DECIMAL, and NUMERIC (the latter two are
> > > brain-damaged though)
> >
> > Removed.
>
> If it's brain-damaged should'nt it be justr Replaced instead of Removed

    NUMERIC  and  DECIMAL  are identical, but should be different
    from INTEGER (what they are in Postgres for now).

    All databases share the definition

        NUMERIC [(precision [, scale] )]

    And NUMERIC (NUMBER in Oracle :-) is defined  as  a  datatype
    that  uses exact representation of arbitrary precise numbers.
    But everyone has different legal value  ranges  and  defaults
    for  it.   The  ranges  for  the  precision  (number of total
    digits) varies from 38 (Oracle) to over 1000 sometimes.

    I'll hack around a little on it to see  what's  possible  for
    us.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

Help with Documentation?

From
Clark Evans
Date:
Since I need to learn PosgreSQL, I'd like to help 
with documentation.  It's the best way to learn... 

To start, can't seem to get the doc/src/sgml 
to compile.  What version of jade do I need 
and what version of the DocBook DTD. Is there 
a link where I can download?

After I am able to re-build the documentation,
could I get access to the CVS server so I can
update documentation?

My long term goal is to put documentation
in the database itself, and to provide tools
to import/export SGML documents to PostgreSQL
and to crate a "document server" that
is accessable from the web.

Best,

Clark


Re: [HACKERS] Upgrades for 6.4.1

From
jwieck@debis.com (Jan Wieck)
Date:
I wrote:

>     NUMERIC  and  DECIMAL  are identical, but should be different
>     from INTEGER (what they are in Postgres for now).
>
>     All databases share the definition
>
>         NUMERIC [(precision [, scale] )]
>
> [...]
>
>     I'll hack around a little on it to see  what's  possible  for
>     us.

    Easy - and the type coersion stuff helps alot!

    Up  to  now  (1.5  hours  hacking) I have a NUMERIC type that
    handles  '+'  completely,  including  overflow   checks   and
    rounding.

    Subtract  will be trivial, because the core funcitons already
    exist for the add. Multiply and divide will  take  some  time
    and then there are all the comparision operators, an operator
    class   and   all   the   type   conversion   (int<->numeric,
    float<->numeric ...).

    Another  tricky  part I expect when telling the parser that a
    literal NUMERIC must not be enclosed into single quotes.

    The only ugly thing is, that I needed to  put  the  precision
    AND  the  scale together into atttypmod (I limited both to 99
    for now and put them as prec<<8 |  scale  into).  So  pg_dump
    will need attention later.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

Re: [HACKERS] Upgrades for 6.4.1

From
Bruce Momjian
Date:
>     The only ugly thing is, that I needed to  put  the  precision
>     AND  the  scale together into atttypmod (I limited both to 99
>     for now and put them as prec<<8 |  scale  into).  So  pg_dump
>     will need attention later.

Atttypmod was expanded to 32 bits for exactly that use.  I recommend
16-bits for each option.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] Upgrades for 6.4.1

From
"Billy G. Allie"
Date:
Bruce Momjian wrote:
> 
> --ELM913959223-29577-0_
> Content-Type: text/plain; charset=US-ASCII
> Content-Transfer-Encoding: 7bit
> 
> I have made the needed changes for 6.4.1.
> 
> Would people please review the attached TODO list, and tell me what TODO
> items can be removed, and if the attached 6.4.1 changes are correct.

Hi Bruce,

Did my patches to psql to correct handle mixed case table names make it into 6.4.1.  I can re-send the patches if
needed.

-- 
____       | Billy G. Allie    | Domain....: Bill.Allie@mug.org
|  /|      | 7436 Hartwell     | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/  |LLIE  | (313) 582-1540    | 



Re: [HACKERS] Upgrades for 6.4.1

From
Bruce Momjian
Date:
> Bruce Momjian wrote:
> > 
> > --ELM913959223-29577-0_
> > Content-Type: text/plain; charset=US-ASCII
> > Content-Transfer-Encoding: 7bit
> > 
> > I have made the needed changes for 6.4.1.
> > 
> > Would people please review the attached TODO list, and tell me what TODO
> > items can be removed, and if the attached 6.4.1 changes are correct.
> 
> Hi Bruce,
> 
> Did my patches to psql to correct handle mixed case table names make it into 6.4.1.  I can re-send the patches if
needed.

Yes, I believe I applied those patches.  The list of changes includes
several mixed-case fixes.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] Help with Documentation?

From
"Thomas G. Lockhart"
Date:
> Since I need to learn PosgreSQL, I'd like to help
> with documentation.

Great. Welcome.

> To start, can't seem to get the doc/src/sgml
> to compile.  What version of jade do I need
> and what version of the DocBook DTD. Is there
> a link where I can download?

Look in Appendix A of the "integrated" postgres docs (postgres.html). It
describes two methods of installation; from sources and from RPM (for
linux). A few days ago we re-discovered how to install and run on
FreeBSD using available "ports". Linux and FreeBSD are easy; from
sources is more difficult. A detailed description for FreeBSD is in the
works, but the info can be posted now I would think if that is your
platform.

I'm planning on implementing "automatic builds" on the current docs tree
on postgresql.org so that fresh working docs are available routinely.
All the pieces are in place now, just need to put the framework in
place.

As an aside, it is possible to contribute docs without having the
ability to build the output. For the Postgres project, it doesn't matter
much, but imho the jade/docbook toolset is so powerful and useful that
it would be a good opportunity for you to learn a tool for other
projects too.

> After I am able to re-build the documentation,
> could I get access to the CVS server so I can
> update documentation?

At the moment, a relatively limited number of contributors have direct
*write* access to the CVS server. Typically, one gets cvs access after
hammering the current committers hard enough with patches that they get
tired of handling them. Everyone has CVSup and anon-CVS access to read
the server.

As the de facto current docs coordinator, I'll make sure contributions
are used, and would welcome any suggestions (especially backed with
work! :) on document structure and style.

btw, no document contributions or updates have ever been rejected, lost,
or unused.

> My long term goal is to put documentation
> in the database itself, and to provide tools
> to import/export SGML documents to PostgreSQL
> and to crate a "document server" that
> is accessable from the web.

Sounds like a neat project.
                   - Tom


Re: [HACKERS] Upgrades for 6.4.1

From
"Thomas G. Lockhart"
Date:
>     Easy - and the type coersion stuff helps alot!
>     Up  to  now  (1.5  hours  hacking) I have a NUMERIC type that
>     handles  '+'  completely,  including  overflow   checks   and
>     rounding.

Neat. I was poking around waiting on a freeware extended-precision
numerical package, but hadn't found anything with a BSD-style license.

>     Another  tricky  part I expect when telling the parser that a
>     literal NUMERIC must not be enclosed into single quotes.

This is probably the worst part, since you would hate to take the hit
representing everything as extended precision even if the actual range
is int4/float8. Perhaps we can read as those, but if we get a failure
then escalate to your extended precision type(s). The automatic type
conversion stuff should convert later if necessary, so it might be
transparent and relatively fast.

>     The only ugly thing is, that I needed to  put  the  precision
>     AND  the  scale together into atttypmod (I limited both to 99
>     for now and put them as prec<<8 |  scale  into).  So  pg_dump
>     will need attention later.

As Bruce points out, that was anticipated (but I agree it's ugly). Shift
over 16 bits if you want...

Congrats!
                       - Tom


CVS Web?

From
Clark Evans
Date:
Is this running anywhere?

Clark


Re: [HACKERS] CVS Web?

From
The Hermit Hacker
Date:
Just installed the cgi for it at http://www.postgresql.org/cgi/cvsweb ...
I leave it to our esteemed webmaster to make it look good :)

On Sat, 19 Dec 1998, Clark Evans wrote:

> Is this running anywhere?
> 
> Clark
> 

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 



Re: [HACKERS] Upgrades for 6.4.1

From
Oleg Bartunov
Date:
On Sat, 19 Dec 1998, Thomas G. Lockhart wrote:

> Date: Sat, 19 Dec 1998 02:58:32 +0000
> From: "Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
> To: Jan Wieck <jwieck@debis.com>
> Cc: hannu@trust.ee, maillist@candle.pha.pa.us, hackers@postgreSQL.org
> Subject: Re: [HACKERS] Upgrades for 6.4.1
> 
> >     Easy - and the type coersion stuff helps alot!
> >     Up  to  now  (1.5  hours  hacking) I have a NUMERIC type that
> >     handles  '+'  completely,  including  overflow   checks   and
> >     rounding.
> 
> Neat. I was poking around waiting on a freeware extended-precision
> numerical package, but hadn't found anything with a BSD-style license.

Did you try search SAL ( Sci. Application for Linux) ?
I just did search for 'precision' and found abot 12 items. Don't know
about license bit some of them like
<a href="http://www.hut.fi/~mtommila/apfloat/">apfloat - A C++ High Performance 
Arbitrary Precision Arithmetic Package</a> are freeware.
>From web page:
Apfloat is a high performance arbitrary precision package. That means you can do calculations involving
millions of digits with it. It uses Number Theoretic Transforms . It's simple to use. It's fast. It's freeware.



> 
> >     Another  tricky  part I expect when telling the parser that a
> >     literal NUMERIC must not be enclosed into single quotes.
> 
> This is probably the worst part, since you would hate to take the hit
> representing everything as extended precision even if the actual range
> is int4/float8. Perhaps we can read as those, but if we get a failure
> then escalate to your extended precision type(s). The automatic type
> conversion stuff should convert later if necessary, so it might be
> transparent and relatively fast.
> 
> >     The only ugly thing is, that I needed to  put  the  precision
> >     AND  the  scale together into atttypmod (I limited both to 99
> >     for now and put them as prec<<8 |  scale  into).  So  pg_dump
> >     will need attention later.
> 
> As Bruce points out, that was anticipated (but I agree it's ugly). Shift
> over 16 bits if you want...
> 
> Congrats!
> 
>                         - Tom
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83



Re: [HACKERS] Upgrades for 6.4.1

From
Tom Lane
Date:
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
>> * SELECT DISTINCT i FROM dtest ORDER BY j generates strange output

> In my simple test case, it orders by j, then only shows i. Is that
> strange?

The thing that is "strange" is that you get nonunique values of i,
which is definitely a bit unexpected for "SELECT DISTINCT":

play=> SELECT * FROM dtest;
i| j
-+--
1|10
1|11
1|12
2|20
3|30
(5 rows)

play=> SELECT DISTINCT i FROM dtest ORDER BY j;
i
-
1
1
1
2
3
(5 rows)

The reason that this is happening is that the "distinct" filter is
actually being run on i,j not just i (see "distinct + order by" thread
in the hackers archives around 8-Nov-98).

I don't know whether the SQL standard defines how this combination of
features ought to work ... but our current behavior seems fairly
surprising...


>> * Allow constraint NULL just as we honor NOT NULL

> Fundamental yacc problem with this as I recall. Gives rise to
> shift/reduce problems since it is ambiguous with other uses of "NULL" in
> the same area.

More to the point, what possible use would a column constrained to NULL
be?  Might as well just not have it in the table...
        regards, tom lane


Re: [HACKERS] Upgrades for 6.4.1

From
Bruce Momjian
Date:
> "Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> >> * SELECT DISTINCT i FROM dtest ORDER BY j generates strange output
> 
> > In my simple test case, it orders by j, then only shows i. Is that
> > strange?
> 
> The thing that is "strange" is that you get nonunique values of i,
> which is definitely a bit unexpected for "SELECT DISTINCT":
> I don't know whether the SQL standard defines how this combination of
> features ought to work ... but our current behavior seems fairly
> surprising...

Re-added to TODO list.

> 
> 
> >> * Allow constraint NULL just as we honor NOT NULL
> 
> > Fundamental yacc problem with this as I recall. Gives rise to
> > shift/reduce problems since it is ambiguous with other uses of "NULL" in
> > the same area.
> 
> More to the point, what possible use would a column constrained to NULL
> be?  Might as well just not have it in the table...

It just says the column _may_ accept nulls.  It is the default anyway.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


RE: [HACKERS] Upgrades for 6.4.1

From
"Stupor Genius"
Date:
> > >> * SELECT DISTINCT i FROM dtest ORDER BY j generates strange output
> > 
> > > In my simple test case, it orders by j, then only shows i. Is that
> > > strange?
> > 
> > The thing that is "strange" is that you get nonunique values of i,
> > which is definitely a bit unexpected for "SELECT DISTINCT":
> > I don't know whether the SQL standard defines how this combination of
> > features ought to work ... but our current behavior seems fairly
> > surprising...
> 
> Re-added to TODO list.

I don't know if I would necessarily call it "strange", more like
"understandable" if you consider the fact that the ORDER BY is being
done first on the data set rather than the distinct.

It would seem to me that the distinct should apply first though.
I would expect the ORDER BY clause to order whatever tuples are
returned by the SELECT, and that would imply doing DISTINCT first.

Just my two cents...
darrenk


Re: [HACKERS] Upgrades for 6.4.1

From
Clark Evans
Date:
Hope you don't mind my feedback on this one.  If so, 
I'll stick to the general list.

> play=> SELECT DISTINCT i FROM dtest ORDER BY j;

> I don't know whether the SQL standard defines how this combination of
> features ought to work ... but our current behavior seems fairly
> surprising...

It suprised me.  Perhaps the query should be re-written using 
a from "select clause" (which is an instant view) on it's way 
down the pipe, like:

SELECT DISTINCT i FROM (SELECT i FROM dest ORDER BY j);


> More to the point, what possible use would a column constrained to NULL
> be?  Might as well just not have it in the table...

Backward compatibility with a client appliation (which has the 
column hard coded in a select statement), only you want to make 
sure that it isn't used...  I've actually done this in real life.  
The client was deployed to thousands of users, and was being phased
out, yet we had to support some reporting it did.  
*shudder*  Don't remind me.


SELECT DISTINCT i FROM dtest ORDER BY j

From
Clark Evans
Date:
>From P121 "A Guide to the SQL Standard, C.J. Date, 1997",

S is defined as:

> SNO SNAME STATUS CITY
> --- ----- ------ ------
> 0S1 SMITH 000020 LONDON
> 0S2 JONES 000010 PARIS
> 0S3 BLAKE 000030 PARIS
> 0S4 CLARK 000020 LONDON
> 0S5 ADAMS 000030 ATHENS

and SP is defined as:

> SNO PNO QTY
> --- --- ---
> 0S1 0P1 300
> 0S1 0P2 200
> 0S1
> 0S2
> 0S2   etc...
> 0S3
> 0S4
> 0S5
The book says:

> Note that each order-item must identify a column 
> of T itself, not just a column of some table from 
> which T is derived.  Thus, for example, the following
> is ***ILLEGAL***:
>
> DELCARE Z CURSOR FOR
>     SELECT S.SNO
>       FROM S
>   ORDER BY CITY
>                              -- *** ILLEGAL *** !!!
>

The book is rather unclear as to what "T" means... although
I assume that it means the relation T (SNO), and not the
table S (SNO,SNAME,STATUS,CITY).   Thus, if you want CITY 
to go in the order by, then you must include it in the 
select list.  

But wait!  Oracle allows the above query!  From what I 
understand though, the database engine implicitly includes
the CITY in the internal processing, the information 
is merely discarded after the order by and not returned.

Thus, it is a very useful, pratical short hand for:

SELECT SNO FROM (  SELECT S.SNO, S.CITY      FROM S  ORDER BY CITY )

I suggest that it might be useful to consider it in this 
manner...


So, assuming that this is a "good" interpretation of
the above item.  Let's consider how a DISTINCT fits in.
Clearly, it has no use in the inner query, where the
ordering occurs.

Thus, the distinct occurs on the outside, like:

SELECT DISTINCT i FROM (  SELECT i, j      FROM dtest  ORDER BY i )

Nice, hunh?  Well not exactly.  Consider this data:

i j
- -
B 3
A 1
A 5

Thus, after the inner query, we have the following:

i j
- -
A 1
B 3
A 5


Now we take the the DISTINCT i right?

What is the anwer?  The book talks about this in
the very next paragraph:

> Finally, if no ORDER BY clause is specified, the rows
> of T will hve an implementation-dependent ordering.  
> Likewise, if an ORDER BY clause is specified but does not
> define a "total" ordering, then the relative order of
> rows within T that have the same value for the order-item(s)
> will again be implemetnation-dependent.  For example:
>
> DELCARE W CURSOR FOR
>     SELECT SP.SNO, SP.PNO, SP.QTY
>       FROM SP
>   ORDER BY SNO
>
> Here the relevant order of SP rows with the same PNO value
> will be inplementation-dependent
>  

Since a "total" ordering was not provided in this 
case, and assuming that the "shorthand" above
is allowed, the answer is implementation-dependent.

i
-
A
B

or

i
-
B
A


Hope this helps. 

:) Clark


Re: [HACKERS] SELECT DISTINCT i FROM dtest ORDER BY j

From
Tom Lane
Date:
Clark Evans <clark.evans@manhattanproject.com> writes:
>> From P121 "A Guide to the SQL Standard, C.J. Date, 1997":
>> Note that each order-item must identify a column 
>> of T itself, not just a column of some table from 
>> which T is derived.  Thus, for example, the following
>> is ***ILLEGAL***:
>> 
>> DELCARE Z CURSOR FOR
>> SELECT S.SNO
>> FROM S
>> ORDER BY CITY
>>                      -- *** ILLEGAL *** !!!

How interesting.  I believe that in fact Postgres used to refuse
such queries, and that the ability to order by a field not present
in the result is new in 6.4.  Maybe now we are starting to find out
why the SQL spec forbids it ;-)

> But wait!  Oracle allows the above query!  From what I 
> understand though, the database engine implicitly includes
> the CITY in the internal processing, the information 
> is merely discarded after the order by and not returned.

Right, that's how Postgres does it too.


Meanwhile darrenk wrote:
> It would seem to me that the distinct should apply first though.
> I would expect the ORDER BY clause to order whatever tuples are
> returned by the SELECT, and that would imply doing DISTINCT first.

The trouble is that if you have several tuples with the same i and
different j, doing the DISTINCT first implies throwing away all but
one of those tuples.  Which one do you keep?  It matters because
some of those tuples might sort differently than others.

As far as I can see, this combination of features is not well-defined on
its surface.  You have to make some additional assumptions (about which
of the possible j values is kept for sorting) in order to define a
unique result.
        regards, tom lane


Re: [HACKERS] SELECT DISTINCT i FROM dtest ORDER BY j

From
Bruce Momjian
Date:
> The trouble is that if you have several tuples with the same i and
> different j, doing the DISTINCT first implies throwing away all but
> one of those tuples.  Which one do you keep?  It matters because
> some of those tuples might sort differently than others.
> 
> As far as I can see, this combination of features is not well-defined on
> its surface.  You have to make some additional assumptions (about which
> of the possible j values is kept for sorting) in order to define a
> unique result.

That is an interesting argument.  It suggests it should not be a TODO
item.  I can see cases where you would want to do the DISTINCT before
the ORDER BY, so the current behavior seems correct.

Removing it from TODO list.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] Upgrades for 6.4.1

From
jwieck@debis.com (Jan Wieck)
Date:
>
> >     The only ugly thing is, that I needed to  put  the  precision
> >     AND  the  scale together into atttypmod (I limited both to 99
> >     for now and put them as prec<<8 |  scale  into).  So  pg_dump
> >     will need attention later.
>
> Atttypmod was expanded to 32 bits for exactly that use.  I recommend
> 16-bits for each option.

    I  tried  that  first  and it failed. Now that you said that,
    I've found in nodes/parsenodes.h that  the  typmod  field  of
    TypeName is still int16.

    I'll  change  that  to  int32  after  having  checked that it
    doesn't break anything.

    I also read the comment from Oleg  about  the  other  numeric
    package  out.   Haven't  taken  a look at it, but I think for
    Postgres I  would  have  to  build  a  complete  new  set  of
    functions from scratch anyway. The reasons for that are:

    1.  I want to store the digits of the NUMERIC type as nibbles
        in the tuple to save space (so  it  will  become  a  real
        packed format). My first hack used one byte per digit and
        I think it's wasted space.

    2.  I want to use the VARSIZE, one combined int16 holding the
        sign  and  the  number of digits before the decimal point
        and one int16 holding the digits  to  DISPLAY  after  the
        decimal  point  as  metadata describing the precision and
        calc-/display scale.

        None of the packages I've seen up to now has  a  separate
        DISPLAY  scale  in  the  number itself. They all use some
        global scale variable.

    3.  The   packed   internal   format   will   need   to    be
        unpacked/packed  for speed on complex operations (divide,
        multiply, log etc.). While add  and  subtract  should  be
        possible directly on the packed format.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #