Thread: 7.4 Wishlist

7.4 Wishlist

From
"Christopher Kings-Lynne"
Date:
Hi guys,

Just out of interest, if someone was going to pay you to hack on Postgres
for 6 months, what would you like to code for 7.4?

My ones are:

* Compliant ADD COLUMN
* Integrated full text indexes
* pg_dump dependency ordering

What would you guys do?  Even if it isn't feasible right now...

Chris



Re: 7.4 Wishlist

From
Hannu Krosing
Date:
Christopher Kings-Lynne kirjutas R, 29.11.2002 kell 23:51:
> Hi guys,
> 
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?
> 
> My ones are:
> 
> * Compliant ADD COLUMN
> * Integrated full text indexes
> * pg_dump dependency ordering
> 
> What would you guys do?  Even if it isn't feasible right now...

As I don't have a permanent job starting next year (my main employer
went bust), I'm planning to do more on postgreSQL anyway (at least until
I run out of money ;)

I have done some (or sometimes a lot of) brain-twitching on items in the
following list, but very little actual useful coding on most.

My personal todo list is:

"Application server support"
----------------------------
* better XML integration   - XML(*) aggregate function returning XML representation of subquery 
 - XML input/output to/from tables
 - XML searchable/indexable in fields)

* Overhaul of OO features (moving closer to SQL99)
 - type/table inheritance, 
   table inheritance would be done using SQL99's UNDER and would be   single inheritance, stored in single logical
table,possibly   subdivided in physical tables reusing our current huge table 1GB   split mechanisms
 
   type inheritance would be done using SQL99's LIKE and would be   multiple inheritance and would reuse as much as
possiblethe   current code for ADD/DROP/RENAME column
 
   - check constraints would apply to both type and table inheritance
   - pk/fk constraints would apply only to table inheritance
 - types as base of tables,
 - study feasibility of reference types,
 - dynamic invocation of table function on queries over hierarchies


* WITH (as part of query/view)

* WITH RECURSIVE for recursive queries

* better NOTIFY (with optional argument, using shared memory instead of tables)


General stuff
-------------

* making array types btree-indexable in a general way

* study feasibility of using SQL99's ARRAY syntax for arrays

Data warehousing
----------------
* bitmap indexes,
 - using bitmap "indexes" internally for star joins
 - real bitmap indexes
 - clustered multiple bitmap indexes especially   clustering on group of bitmap indexes

* clustering in general - specifying pages to be filled only to a certain percentage in clustered tables so that
updatedtuples can be placed near original ones if needed and parallel vacuum can then reclaim the space and keep table
clusteredwith less shuffling.
 

* OLAP features  - WINDOW clause, PARTITION BY  - GROUPING SETS, ROLLUP, CUBE, () 


WAL-based master-slave replication
----------------------------------

* if someone is not doing it (which I hope is not true ;)


UNICODE / Localization
----------------------

* UTEXT, UCHAR, UVARCHAR types using IBM's ICU, stored in UTF-16 or SCSU

* fast LIKE, ILIKE, REGEX code for UTF-16, possibly lifted from python2

* field-level localization, again using ICU


FE/BE protocol
--------------

all can be worked on independently

* try to find a better wire protocol from existing ones (X-window system seems simple enough, perhaps DB2's DRDA) or
fixthe existing one for high performance (mainly make sure that as big chunks as possible have preceeding length), make
iteasy to send out-of-band/optional data (Notifications, info on actual query performance (so one can visualize it for
user), ...)
 

* standardize a fire-level binary protocol for field types (currently whatever is stored is sent)

* work on making python use this protocol and port some postgres datatypes (initially timestamp/date/time and varbit)to
python
 


Really Dark Arts
------------------

* making backend internals available to a scripting language (for me it means python ;) for making more parts
(especiallyplanner/optimizer) more easily hackable
 

* using the stuff from previous point ;)


And that's all ;)

----------------
Hannu Krosing




Re: 7.4 Wishlist

From
"Matthew T. O'Connor"
Date:
pg_dump, our upgrade process is painful enough having to do a dump, reload.
I think we should be able to guarantee (or at least let much closer to it)
that the process works in all cases.

Personally pg_upgrade would be even nicer.

----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
To: <pgsql-hackers@postgresql.org>
Sent: Friday, November 29, 2002 1:51 PM
Subject: [HACKERS] 7.4 Wishlist


> Hi guys,
>
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?
>
> My ones are:
>
> * Compliant ADD COLUMN
> * Integrated full text indexes
> * pg_dump dependency ordering
>
> What would you guys do?  Even if it isn't feasible right now...
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>




Re: 7.4 Wishlist

From
Daniele Orlandi
Date:
Christopher Kings-Lynne wrote:
> Hi guys,
> 
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?

Replication. Replication. Replication. Replication. Replication.
Replication. Replication. Replication. Replication. Replication.
Replication. Replication. Replication. Replication. Replication.

Well, jokes apart, I think this is one of the most needed features to 
me. Currently I'm using strange voodoo to replicate some tables on other 
machines in order to spread load and resilency. Compared to what I am 
doing now a good master to slave replication would be heaven.

I understand that a good replication is painful but in my experience, if 
you start by integrating some rude, experimental implementation in the 
mainstream PostgreSQL the rest will come by itself.

For example, RI was something I wouldn't consider "production level" in 
7.2, but was a start, now in 7.3 is much much better, probably complete 
in the most important parts.

Other wishes (not as important as the replication issue) are:

- Better granularity of security and access control, like in mysql.

- Ability to reset the state of an open backend, including aborting open 
transaction to allow for better connection pooling and reusing, maybe 
giving the client the ability to switch between users...

Bye!

--  Daniele Orlandi Planet Srl



Re: 7.4 Wishlist

From
"Christopher Kings-Lynne"
Date:
Wow Hannu - your list puts mine to shame!

> "Application server support"
> ----------------------------
> * better XML integration
>
>   - XML(*) aggregate function returning XML representation of subquery
>
>   - XML input/output to/from tables
>
>   - XML searchable/indexable in fields)

I've had thoughts about XML too.  Since XML is hierachical, imagine being
able to index xml using contrib/ltree or something!

ie. We create a new 'xml' column type.

We create a new indexing scheme for it based on ltree & gist.

You index the xml column.

Then you can do sort of XPath queries:

SELECT * FROM requests WHERE xml_xpath('/request/owner/name', datafield) =
'Bob';

And it would be indexed.  Imaging being able to pull up all XML documents
that had certain properties, etc.

MS-SQL has a SELECT ... FOR XML clause, but we could always just create
function called xml_select() or something now that we can return recordsets.

Chris



Re: 7.4 Wishlist

From
Philip Warner
Date:
At 10:51 AM 29/11/2002 -0800, Christopher Kings-Lynne wrote:
>* pg_dump dependency ordering

I've actually started working on pg_dump in the background, but if you want 
to do it let me know.

In terms of things I would like to see:

- background/integrated vacuum (not just an overwriting storage manager)

- insert/update...returning

- function result caches -- assuming I can demonstrate that they are a Good 
Thing.

- COPY TO/FROM with a list of columns (maybe we have it?) - it's useful for 
making metadata changes then reloading data (Inserts are much slower).




----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



Re: 7.4 Wishlist

From
"Christopher Kings-Lynne"
Date:
> I've actually started working on pg_dump in the background, but if you
want
> to do it let me know.

Nah - you can do it!  I don't have much time to code Postgres as it is.
I'll stick to working on making ADD COLUMN compliant.

> In terms of things I would like to see:
>
> - background/integrated vacuum (not just an overwriting storage manager)

Work has started on this already, which is neat.

> - insert/update...returning

That would be so useful for our web app where we're always needing to get
the primary key as opposed to oid.  We keep needing to requery.

> - function result caches -- assuming I can demonstrate that they are a
Good
> Thing.
>
> - COPY TO/FROM with a list of columns (maybe we have it?) - it's useful
for
> making metadata changes then reloading data (Inserts are much slower).

We do already have it in 7.3:

http://developer.postgresql.org/docs/postgres/sql-copy.html

Chris



Re: 7.4 Wishlist

From
Philip Warner
Date:
At 04:56 PM 29/11/2002 -0800, Christopher Kings-Lynne wrote:
> >
> > - COPY TO/FROM with a list of columns (maybe we have it?) - it's useful
>for
> > making metadata changes then reloading data (Inserts are much slower).
>
>We do already have it in 7.3:

Excellent. Then I just need to add support in pg_dump.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



Re: 7.4 Wishlist

From
"Christopher Kings-Lynne"
Date:
> > > - COPY TO/FROM with a list of columns (maybe we have it?) - it's
useful
> >for
> > > making metadata changes then reloading data (Inserts are much slower).
> >
> >We do already have it in 7.3:
>
> Excellent. Then I just need to add support in pg_dump.

Hmmm.  I could have sworn that someone (Neil?) already did that?

Chris



Locale-dependent case conversion in {identifier}

From
"Nicolai Tufar"
Date:
Comment in {identifier} section in src/backend/parser/scan.l states:                                    [...]
*Note: here we use a locale-dependent case conversion,          * which seems appropriate under SQL99 rules, whereas
     * the keyword comparison was NOT locale-dependent.          */
 

And in ScanKeywordLookup() in src/backend/parser/keywords.c:
       /*        * Apply an ASCII-only downcasing.      We must not use tolower()
since it        * may produce the wrong translation in some locales (eg, Turkish),        * and we don't trust
isupper()very much either.  In an ASCII-based        * encoding the tests against A and Z are sufficient, but we also
    * check isupper() so that we will work correctly under EBCDIC.  The        * actual case conversion step should
workfor either ASCII or
 
EBCDIC.        */

And I happen to have bad luck to use PostgreSQL with Turkish locale. And, as
you
may know our "I" is not your "I":
   pgsql=# create table a(x char(1));   CREATE TABLE   pgsql=# grant SELECT ON a to PUBLIC;   ERROR:  user "public"
doesnot exist   pgsql=#
 

Oracle, the second best database I have does seem to convert relation names
in
locale-dependent fassion:
  SQL> alter session set NLS_LANGUAGE='TURKISH';  Session altered.  SQL> create table a(x char(1));  Table created.
SQL>grant select on a to PUBLIC;  Grant succeeded.
 

Further, if I try to create a table in oracle using Turkish-specific
characters,
it is creating it alright, without trying to make them upper-case as it
usually does.

So I have changed lower-case conversion code in scan.l to make it purely
ASCII-based
as in keywords.c. Mini-patch is given below. Please bear in mind that it is
my first
attempt at hacking PostgreSQL code, so there can be some mistakes.

Regards,
Nick


diff -Nur src/backend/parser/scan.l.orig src/backend/parser/scan.l
--- src/backend/parser/scan.l.orig      Sat Nov 30 02:54:06 2002
+++ src/backend/parser/scan.l   Sat Nov 30 02:57:45 2002
@@ -551,9 +551,12 @@                                       ident = pstrdup(yytext);
 for (i = 0; ident[i]; i++)                                       {
 
-                                               if (isupper((unsigned char)
ident[i]))
-                                                       ident[i] =
tolower((unsigned char) ident[i]);
+                                               char            ch =
ident[i];
+                                               if (ch >= 'A' && ch <= 'Z'
&& isupper((unsigned char) ch))
+                                                       ch += 'a' - 'A';
+                                                       ident[i] = ch;                                       }
+                                       ident[i] = '\0';                                       if (i >= NAMEDATALEN)
               {                                               int len;
 




Re: 7.4 Wishlist

From
Philip Warner
Date:
At 05:33 PM 29/11/2002 -0800, Christopher Kings-Lynne wrote:
>Hmmm.  I could have sworn that someone (Neil?) already did that?

Not AFAICT - at least based on looking at the manual. I'll check the code.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



Re: Locale-dependent case conversion in {identifier}

From
Tom Lane
Date:
"Nicolai Tufar" <ntufar@apb.com.tr> writes:
> So I have changed lower-case conversion code in scan.l to make it purely
> ASCII-based.
> as in keywords.c. Mini-patch is given below.

Rather than offering a patch, you need to convince us why our reading of
the SQL standard is wrong.  ("Oracle does it that way" is not an
argument that will carry a lot of weight.)

SQL99 states that identifier case conversions are done on the basis of
the Unicode upper/lower case equivalences, so it seems clear that they
intend more than ASCII-only conversion for identifiers.  Locale-based
conversion might not be an exact implementation of the spec, but it's
surely closer than ASCII-only.
        regards, tom lane


Re: 7.4 Wishlist

From
Alvaro Herrera
Date:
On Fri, Nov 29, 2002 at 10:51:26AM -0800, Christopher Kings-Lynne wrote:

> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?

Well, nobody is paying me, but I want to 

- fix the btree problem leaking unused pages (I think I'm getting near, I just haven't had free time during the last
month). This one is a must to me.
 

- try different regexp algorithms, compare efficiency.  Both Henry Spencer's new code for Tcl, and Baeza-Navarro
shift-orapproach (can be much faster than "traditional" regex engines) (do people care for allowing "search with
errors",similar to what agrep and nrgrep do?)
 

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Nunca confiaré en un traidor.  Ni siquiera si el traidor lo he creado yo"
(Barón Vladimir Harkonnen)


Re: Locale-dependent case conversion in {identifier}

From
Nicolai Tufar
Date:
By no means I would try to convince that your reading of
the SQL standards is wrong. What I am trying to tell is
that Turkish alphabet is broken beyond repair. And since
there is absolutely no way to change our alphabet, we
may can code a workaround in the code.

So i do not claim that your code is wrong. It is
behaviang according to specification. But unfortunately
folks at SQL99 probably were not aware of the woes
of Turkish "I".

The very special case of letter "I" in Turkish is not
only PostgreSQL's problem. Many java programs have
failed miserably trying to open files with "I"s in
pathnames.

So basically, there are two letters "I" in Trukish.
The wone is with dot on top and another is without.
The with dot on top walways has the dot and the one
without never has it. Simple. The problem is
with the standard Latin "I". So why small "i" does
have a dot and capital "I" does not?

Standard conversion is
Lower: "I" -> "y'" and "Y'" -> "i".
Upper: "y'"  -> "I" and "i" -> "Y'".
(font may not be displayed correctly in your mail reader)

Historically programs that operate in Turkish locale have
chosen to hardcode the capitalisation of "i" in system
messages and identifier names like this:

Lower: "I" -> "i" and "Y'" -> "i".
Upper: "y'"  -> "I" and "i" -> "I".

With this, no matter what kind of "I" you used in names,
it is always going to end up a valid ASCII character.

Would it be acceptable if I submit a path that applies this
special logic in src/backend/parser/scan.l if the locale is "tr_TR"?

Because for many folks setting locale to Turkish would
render their database unusable. For, god forbid, if your
sql has a column name written in capitlas including "I".
It is not working. So I deeply believe that PostgreSQL community
have to provide a workaround for this problem.

So what should I do?

Best regards,
Nick




Tom Lane wrote:
> "Nicolai Tufar" <ntufar@apb.com.tr> writes:
> 
>>So I have changed lower-case conversion code in scan.l to make it purely
>>ASCII-based.
>>as in keywords.c. Mini-patch is given below.
> 
> 
> Rather than offering a patch, you need to convince us why our reading of
> the SQL standard is wrong.  ("Oracle does it that way" is not an
> argument that will carry a lot of weight.)
> 
> SQL99 states that identifier case conversions are done on the basis of
> the Unicode upper/lower case equivalences, so it seems clear that they
> intend more than ASCII-only conversion for identifiers.  Locale-based
> conversion might not be an exact implementation of the spec, but it's
> surely closer than ASCII-only.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly





Re: 7.4 Wishlist

From
Neil Conway
Date:
On Fri, 2002-11-29 at 18:06, Daniele Orlandi wrote:
> - Better granularity of security and access control, like in mysql.

Can you be more specific on exactly what features you'd like to see?

> - Ability to reset the state of an open backend, including aborting open 
> transaction to allow for better connection pooling and reusing

IIRC, it's been suggested that we can implement this by passing back the
transaction state as part of the FE/BE protocol -- if we're doing a
protocol change for 7.4, this could be part of it.

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC





Re: 7.4 Wishlist

From
Neil Conway
Date:
On Fri, 2002-11-29 at 13:51, Christopher Kings-Lynne wrote:
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?

Here's some of my current TODO list:

* FOR EACH STATEMENT triggers (already done)

* column lists for UPDATE triggers (will be done soon)

* Improve the buffer manager's replacement algorithm (LRU-K, perhaps?)

* Implement support for hugetlb pages on linux 2.5

* Finish off PITR (if no one else does ...)

* Asynchronous notification improvements (optional message, store
notifications in shared memory)

* Rowtype assignment in PL/PgSQL

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC





Re: 7.4 Wishlist

From
Hans-Jürgen Schönig
Date:
What I'd like to have in future versions of PostgreSQL:
   - replication, replication, ... (you have seen that before). i guess 
most people would like to see that.
   - a dblink like system for connecting to remote database systems 
(not just PostgreSQL???)       something like CREATE REMOTE VIEW would be damn good.       it would solve many problem
whenit comes to migration
 
   - tablespaces (the directory based stuff which has been discussed on 
the list)      
   - somebody has mentioned XML before. the XPath stuff would be really 
cool
   - PL/Sh should be in contrib. i know that the core team has decided 
not to put it in the core but contrib would be fine (I keep forgetting 
the URL of Peters website :( ...)
   - packages: is there a way to define a set of functions as a package 
so that they can be removed using just one DROP PACKAGE or so? would be 
nice for huge projects
   - urgent: being able to use PL/Perl in combination with SPI (There 
is a Pg-SPI but it is 0.01 - see  
http://search.cpan.org/author/APILOS/DBD-PgSPI-0.01/PgSPI.pm). a full 
and reliable implementation would be fine.
   - preforking for faster startup
   - declare MySQL as evil *g*.

       Thanks a lot :)
           Hans




Re: 7.4 Wishlist

From
Hans-Jürgen Schönig
Date:
Oops, there is something I have forgotten:
   - "nicing" backends: this would be nice for administration tasks
   - CREATE DATABASE ... WITH MAXSIZE (many providers would like to see 
that; quotas are painful in this case - especially when porting the 
database to a different or a second server)
   Hans



Re: 7.4 Wishlist

From
"Al Sutton"
Date:
My list is;

Point to Point and Broadcast replication
----------------------------------------
With point to point you specify multiple endpoints, with broadcast you can
specify a subnet address and the updates are broadcast over that subnet.

The difference being that point to point works well for cross network
replication, or where you have a few replicants. I have multiple database
servers which could have a deadicated class C network that they are all on,
by broadcasting updates you can cutdown the amount of traffic on that net by
a factor of n minus 1 (where n is the number of servers involved).

Ability to use raw partitions
----------------------------

I've not seen an install of PostgreSQL yet that didn't put the database
files onto a filesystem, so I'm assuming it's the only way of doing it. By
using the filesystem the files are at the mercy of filesystem handler code
as to where they end up on the disk, and thus the speed of access will
always have some dependancy on the speed of the filesystem.

With a raw partition it would be possible to use two devices (e.g. /dev/hde
and /dev/hdg on an eight channel ide linux box), and PostgreSQL could then
ensure the WALs were located on one the disk with the entries running
sequentally, and that the database files were located on the other disk in
the most appropriate location (e.g. index data starting near the center of
the disk, and user table data starting near the outside).

Win32 Port
------------
I've explained the reasons before. Apart from that it's always useful to
open PostgreSQL up to a larger audience.



----- Original Message -----
From: "Daniele Orlandi" <daniele@orlandi.com>
To: <pgsql-hackers@postgresql.org>
Sent: Friday, November 29, 2002 11:06 PM
Subject: [mail] Re: [HACKERS] 7.4 Wishlist


> Christopher Kings-Lynne wrote:
> > Hi guys,
> >
> > Just out of interest, if someone was going to pay you to hack on
Postgres
> > for 6 months, what would you like to code for 7.4?
>
> Replication. Replication. Replication. Replication. Replication.
> Replication. Replication. Replication. Replication. Replication.
> Replication. Replication. Replication. Replication. Replication.
>
> Well, jokes apart, I think this is one of the most needed features to
> me. Currently I'm using strange voodoo to replicate some tables on other
> machines in order to spread load and resilency. Compared to what I am
> doing now a good master to slave replication would be heaven.
>
> I understand that a good replication is painful but in my experience, if
> you start by integrating some rude, experimental implementation in the
> mainstream PostgreSQL the rest will come by itself.
>
> For example, RI was something I wouldn't consider "production level" in
> 7.2, but was a start, now in 7.3 is much much better, probably complete
> in the most important parts.
>
> Other wishes (not as important as the replication issue) are:
>
> - Better granularity of security and access control, like in mysql.
>
> - Ability to reset the state of an open backend, including aborting open
> transaction to allow for better connection pooling and reusing, maybe
> giving the client the ability to switch between users...
>
> Bye!
>
> --
>   Daniele Orlandi
>   Planet Srl
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>




Re: 7.4 Wishlist

From
Kaare Rasmussen
Date:
> And that's all ;)
> ----------------
> Hannu Krosing

- and what will you do after January? ;-)

Just kidding. I hope you have a big fat bank account if you want to finish 
all that!

-- 
Kaare Rasmussen            --Linux, spil,--        Tlf:        3816 2582
Kaki Data                tshirts, merchandize      Fax:        3816 2501
Howitzvej 75               Åben 12.00-18.00        Email: kar@kakidata.dk
2000 Frederiksberg        Lørdag 12.00-16.00       Web:      www.suse.dk


Re: 7.4 Wishlist

From
"Alice Lottini"
Date:
Hi,
although I'm just a novice in this mailing list I'd like to give my
contribution to the 7.4 wishlist.
I'd like to add to the PostgreSQL code some new low-level, primitive
fuctions in order to give native support to FP-based algorithms for rule
mining (Frequent Pattern Growth and extensions such as CLOSET and so on).
As a matter of fact, this is more than just a wish to me... this is the task
I have to accomplish for my thesis (I'm going to degree in Informatics
Engineering at the Politecnico di Torino, Italy on next July), and so I can
assure you that this will be done (and working) by the end of June.
Obviously, any kind of hint and suggestion by you guruz is welcome! :)
Bye, alice

----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
To: <pgsql-hackers@postgresql.org>
Sent: Friday, November 29, 2002 7:51 PM
Subject: [HACKERS] 7.4 Wishlist


> Hi guys,
>
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?
>
> My ones are:
>
> * Compliant ADD COLUMN
> * Integrated full text indexes
> * pg_dump dependency ordering
>
> What would you guys do?  Even if it isn't feasible right now...
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


______________________________________________________________________
Scarica il nuovo Yahoo! Messenger: con webcam, nuove faccine e tante altre novità.
http://it.yahoo.com/mail_it/foot/?http://it.messenger.yahoo.com/


Re: 7.4 Wishlist

From
snpe
Date:
On Friday 29 November 2002 06:51 pm, Christopher Kings-Lynne wrote:
> Hi guys,
>
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?
>
> My ones are:
>
> * Compliant ADD COLUMN
> * Integrated full text indexes
> * pg_dump dependency ordering
>
> What would you guys do?  Even if it isn't feasible right now...
>
> Chris
>

My wishlist :- savepoint- cursor out of a transaction- distributed databases and replication (two phase commit)- only
sharelock in foreign keys - prepare/execute on backend level- error in a statement break a statement, not complete
transaction

regards
Haris Peco


Re: Locale-dependent case conversion in {identifier}

From
Hannu Krosing
Date:
On Sat, 2002-11-30 at 01:40, Nicolai Tufar wrote:
> And I happen to have bad luck to use PostgreSQL with Turkish locale. And, as
> you
> may know our "I" is not your "I":
> 
>     pgsql=# create table a(x char(1));
>     CREATE TABLE
>     pgsql=# grant SELECT ON a to PUBLIC;
>     ERROR:  user "public" does not exist
>     pgsql=#
> 
> Oracle, the second best database I have does seem to convert relation names
> in
> locale-dependent fassion:
> 
>    SQL> alter session set NLS_LANGUAGE='TURKISH';
>    Session altered.
>    SQL> create table a(x char(1));
>    Table created.
>    SQL> grant select on a to PUBLIC;
>    Grant succeeded.

could it just be that we store identifiers in lower case, whereas most others
(including SQL spec IIRC)have them in upper case ?

Could you try the grant in both databases also in lower case ?

i.e.:

grant select on a to public;

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




Re: Locale-dependent case conversion in {identifier}

From
Hannu Krosing
Date:
On Sat, 2002-11-30 at 07:57, Nicolai Tufar wrote:
> With this, no matter what kind of "I" you used in names,
> it is always going to end up a valid ASCII character.
> 
> Would it be acceptable if I submit a path that applies this
> special logic in src/backend/parser/scan.l if the locale is "tr_TR"?
> 
> Because for many folks setting locale to Turkish would
> render their database unusable. For, god forbid, if your
> sql has a column name written in capitlas including "I".
> It is not working. So I deeply believe that PostgreSQL community
> have to provide a workaround for this problem.
> 
> So what should I do?

In SQL in general and in postgreSQL in particular, you can always use
quoted names and thus escape the stupidities of case conversion:

grant SELECT ON "a" to "public";

should work everywhere (except Oracle and other DB's where it should begrant SELECT ON "A" to "PUBLIC";
)

I can't help you on Win32/VMS filenames ;)

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



about ODBC

From
Horacio Miranda
Date:
Someone know any ODBC driver without problem with UPPER CASE ?
--                Saludos Horacio Miranda.                    hmiranda@yale.cl
------------------------------------------------------------
PostgreSQL.  Because life's too short to learn Oracle.    :)                     Billy O'Connor
            IBM --> Immer Backup Machen




Re: 7.4 Wishlist

From
Hannu Krosing
Date:
On Sat, 2002-11-30 at 05:55, Alvaro Herrera wrote:
> On Fri, Nov 29, 2002 at 10:51:26AM -0800, Christopher Kings-Lynne wrote:
> 
> > Just out of interest, if someone was going to pay you to hack on Postgres
> > for 6 months, what would you like to code for 7.4?
> 
> Well, nobody is paying me, but I want to 
> 
> - fix the btree problem leaking unused pages (I think I'm getting near,
>   I just haven't had free time during the last month).  This one is a
>   must to me.
> 
> - try different regexp algorithms, compare efficiency.  Both Henry
>   Spencer's new code for Tcl, and Baeza-Navarro shift-or approach (can
>   be much faster than "traditional" regex engines)

Perhaps bigger effect could be possible if we could could make
LIKE/REGEXP use indexes - perhaps some approach based on trigrams could
be usable here ?

>   (do people care for allowing "search with errors", similar to what
>   agrep and nrgrep do?)

Yes, especially if integrated with some full text index scheme.

-- 
Hannu Krosing <hannu@tm.ee>


Re: Locale-dependent case conversion in {identifier}

From
Tom Lane
Date:
Nicolai Tufar <ntufar@apb.com.tr> writes:
> Historically programs that operate in Turkish locale have
> chosen to hardcode the capitalisation of "i" in system
> messages and identifier names like this:

> Lower: "I" -> "i" and "Y'" -> "i".
> Upper: "y'"  -> "I" and "i" -> "I".

If that's the behavior you want, why don't you set up a variant locale
definition that does it that way?  That would fix *all* your locale-
dependent programs, not just Postgres ...

> Would it be acceptable if I submit a path that applies this
> special logic in src/backend/parser/scan.l if the locale is "tr_TR"?

It really seems like an inappropriate wart to me :-(

> Because for many folks setting locale to Turkish would
> render their database unusable. For, god forbid, if your
> sql has a column name written in capitlas including "I".
> It is not working.

I am not seeing why this is any worse than the universal problems of
using upper-case letters without double-quoting 'em.  If you
consistently spell the name the same way, you will not have a problem;
if you don't, you might have a problem, but why is it worse than
anyone else's?
        regards, tom lane


Re: 7.4 Wishlist

From
Bruce Momjian
Date:
Hans-J�rgen Sch�nig wrote:
> What I'd like to have in future versions of PostgreSQL:
>
>     - PL/Sh should be in contrib. i know that the core team has decided 
> not to put it in the core but contrib would be fine (I keep forgetting 
> the URL of Peters website :( ...)

I like PL/Sh too, but too many people are concerned it isn't
transaction-safe and has poor performance.  I want it in /contrib, but
Peter, the author, doesn't want it in there, so there isn't much we can
do.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: 7.4 Wishlist

From
Hannu Krosing
Date:
On Sat, 2002-11-30 at 16:13, Bruce Momjian wrote:
> Hans-Jürgen Schönig wrote:
> > What I'd like to have in future versions of PostgreSQL:
> >
> >     - PL/Sh should be in contrib. i know that the core team has decided
> > not to put it in the core but contrib would be fine (I keep forgetting
> > the URL of Peters website :( ...)

You could put the URL in /contrib

> I like PL/Sh too, but too many people are concerned it isn't
> transaction-safe and has poor performance.  I want it in /contrib, but
> Peter, the author, doesn't want it in there, so there isn't much we can
> do.

perhaps the URL and a file WARNING.TXT ;)

--
Hannu Krosing <hannu@tm.ee>


Re: 7.4 Wishlist

From
Bruce Momjian
Date:
Hannu Krosing wrote:
> On Sat, 2002-11-30 at 16:13, Bruce Momjian wrote:
> > Hans-J?rgen Sch?nig wrote:
> > > What I'd like to have in future versions of PostgreSQL:
> > >
> > >     - PL/Sh should be in contrib. i know that the core team has decided 
> > > not to put it in the core but contrib would be fine (I keep forgetting 
> > > the URL of Peters website :( ...)
> 
> You could put the URL in /contrib

The URL used to be main site under:
http://www.us.postgresql.org/interfaces.html

but I don't see it there anymore.  In fact, that page needs updating
because some projects have moved.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: 7.4 Wishlist

From
Stephan Szabo
Date:
On Fri, 29 Nov 2002, Christopher Kings-Lynne wrote:

> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?

> What would you guys do?  Even if it isn't feasible right now...

Hmm, mine would probably be fixing foreign keys (along with making it
work with inheritance and match partial) and check constraints with
subselects.



Re: 7.4 Wishlist

From
"Gabor Csuri"
Date:
Hi All,
here is my wishlist /very short/ :

* Oracle syntax support in OUTER JOIN

Thanks, Gabor


> Hans-Jürgen Schönig wrote:
> > What I'd like to have in future versions of PostgreSQL:
> >
> >     - PL/Sh should be in contrib. i know that the core team has decided
> > not to put it in the core but contrib would be fine (I keep forgetting
> > the URL of Peters website :( ...)
>
> I like PL/Sh too, but too many people are concerned it isn't
> transaction-safe and has poor performance.  I want it in /contrib, but
> Peter, the author, doesn't want it in there, so there isn't much we can
> do.
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square,
> Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html



Re: 7.4 Wishlist

From
Oleg Bartunov
Date:
Me and Teodor hope to work on contrib/ltree to add support for sort of
xml. Any ideas are welcome !
Regards,
    Oleg
On Fri, 29 Nov 2002, Christopher Kings-Lynne wrote:

> Wow Hannu - your list puts mine to shame!
>
> > "Application server support"
> > ----------------------------
> > * better XML integration
> >
> >   - XML(*) aggregate function returning XML representation of subquery
> >
> >   - XML input/output to/from tables
> >
> >   - XML searchable/indexable in fields)
>
> I've had thoughts about XML too.  Since XML is hierachical, imagine being
> able to index xml using contrib/ltree or something!
>
> ie. We create a new 'xml' column type.
>
> We create a new indexing scheme for it based on ltree & gist.
>
> You index the xml column.
>
> Then you can do sort of XPath queries:
>
> SELECT * FROM requests WHERE xml_xpath('/request/owner/name', datafield) =
> 'Bob';
>
> And it would be indexed.  Imaging being able to pull up all XML documents
> that had certain properties, etc.
>
> MS-SQL has a SELECT ... FOR XML clause, but we could always just create
> function called xml_select() or something now that we can return recordsets.
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
Regards,    Oleg
_____________________________________________________________
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: 7.4 Wishlist

From
bpalmer
Date:
> * Compliant ADD COLUMN

I've missed the thread (if there was one),  how is it non-compliant?

Thanks,
- Brandon

----------------------------------------------------------------------------c: 917-697-8665
              h: 201-798-4983b. palmer,  bpalmer@crimelabs.net           pgp:crimelabs.net/bpalmer.pgp5
 



Re: 7.4 Wishlist

From
Rod Taylor
Date:
On Sat, 2002-11-30 at 15:06, bpalmer wrote:
> > * Compliant ADD COLUMN
>
> I've missed the thread (if there was one),  how is it non-compliant?

ALTER TABLE .. ADD COLUMN colname integer DEFAULT 42 NOT NULL
CHECK(colname <= 42) REFERENCES tab2 ON DELETE CASCADE;

Can't do the above in a single statement.  It takes five statements.

It's something I'd like to see added as well.

--
Rod Taylor <rbt@rbt.ca>

Re: 7.4 Wishlist

From
Dan Langille
Date:
Can you see this tying in with my recent hack of contrib/ltree to work
with a wider range of node names?

On Sat, 30 Nov 2002, Oleg Bartunov wrote:

> Me and Teodor hope to work on contrib/ltree to add support for sort of
> xml. Any ideas are welcome !



Re: Locale-dependent case conversion in {identifier}

From
"Nicolai Tufar"
Date:
----- Original Message -----
From: "Hannu Krosing" <hannu@tm.ee>
To: "Nicolai Tufar" <ntufar@apb.com.tr>
Cc: <pgsql-hackers@postgresql.org>
Sent: Saturday, November 30, 2002 5:41 PM
Subject: Re: [HACKERS] Locale-dependent case conversion in {identifier}

            [ ... ]
>
> could it just be that we store identifiers in lower case, whereas most
others
> (including SQL spec IIRC)have them in upper case ?

That seem to be the case. All the databases I used, automaticaly convert
identifiers to upper case.
And they all do it using ASCII-only conversion.

>
> Could you try the grant in both databases also in lower case ?
>
> i.e.:
>
> grant select on a to public;

The statement works in both databases. But the problem is that it was
pg_dumpall who created SQL statements with PUBLIC. Why
pg_dumpall does not enclose identifiers in quotes, like:

REVOKE ALL ON TABLE "tamara2" FROM "public";     insted of
REVOKE ALL ON TABLE tamara2 FROM PUBLIC;
as it does now.

I will make an attempt to modify pg_dump accordingly, and will send a
patch to the list.


Regards,
Nic



Re: 7.4 Wishlist

From
Joe Conway
Date:
Christopher Kings-Lynne wrote:
> Hi guys,
> 
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?
> 
> My ones are:
> 
> * Compliant ADD COLUMN
> * Integrated full text indexes
> * pg_dump dependency ordering
> 
> What would you guys do?  Even if it isn't feasible right now...

Well, I might as well join in the fun. Here's my personal TODO for 7.4 (with 
much wishing that I'll actually have the time to do most if not all of it ;-))

* continue to improve usability of bytea datatype  - easier explicit casting between bytea and text
* stored procedures (procedures not functions)  - no return value, but allow projection of results similar to SHOW ALL
 (i.e. also similar to MSSQL and Sybase)  - CREATE PROCEDURE sp_my_stored_proc() AS '...' LANGUAGE '...';  - CALL
sp_my_stored_proc;
* array related improvements (note: some of this may exist in contrib as    I haven't looked too close yet, but I'm
aimingfor these to be internal    backend functions)  - function to return users in a group as rows instead of as an
array - generic table function to unspool an array into rows [and columns for    2 dim array]  - split --  split string
intoarray on delimiter  - implode -- join array elements into a string using given string delimiter  - array_contains
--Return TRUE if a value exists in an array  - array_search -- Searches the array for a given value and returns the
correspondingkey if successful
 
* PL/R  - new PL interface to R (statistical analysis package based on the S    language)
* improvements to contrib/tablefunc  - enhanced crosstab functionality  - possibly enhanced connectby functionality
(maynot be worth it if    RECURSIVE JOIN functionality makes it into 7.4)
 
* improvements to dblink  - see details on other recently sent message (ability to connect to    non-PostgreSQL
databases)
* revisit table function scan issues (i.e. tuplestore vs streaming vs  portal based)

Things not on my list, but that I'm keenly interested in (in no particular order):
- native win32 port
- two-phase commit
- PITR
- replication
- recursive joins (CONNECT BY PRIOR ... START WITH ...)

Joe




Re: 7.4 Wishlist

From
David Wheeler
Date:
On Saturday, November 30, 2002, at 01:44  PM, Joe Conway wrote:

> * continue to improve usability of bytea datatype
>   - easier explicit casting between bytea and text

This wouldn't happen to include the idea of somehow eliminating the 
difference between how text strings are delimited and how bytea strings 
are delimited, would it?

Best,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e                                               Jabber:
Theory@jabber.org



Re: 7.4 Wishlist

From
ow
Date:
Cross-db queries.




__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com


Re: 7.4 Wishlist

From
Joe Conway
Date:
David Wheeler wrote:
> On Saturday, November 30, 2002, at 01:44  PM, Joe Conway wrote:
>> * continue to improve usability of bytea datatype
>>   - easier explicit casting between bytea and text
> 
> This wouldn't happen to include the idea of somehow eliminating the 
> difference between how text strings are delimited and how bytea strings 
> are delimited, would it?

Not quite sure what you mean by delimiter -- are you referring to double
escaping vs single escaping?

Joe



Re: 7.4 Wishlist

From
David Wheeler
Date:
On Saturday, November 30, 2002, at 04:14  PM, Joe Conway wrote:

> Not quite sure what you mean by delimiter -- are you referring to 
> double
> escaping vs single escaping?

Oh crap, yes, that's exactly what I meant.

s/delimited/escaped/g;

Sorry. :-)

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e                                               Jabber:
Theory@jabber.org



Re: 7.4 Wishlist

From
"Christopher Kings-Lynne"
Date:
> > My ones are:
> >
> > * Compliant ADD COLUMN
> > * Integrated full text indexes
> > * pg_dump dependency ordering
> >
> > What would you guys do?  Even if it isn't feasible right now...

Actually - I think I might add MODIFY COLUMN to that list.  Just look at the
list of poor buggers in the interactive docs who can't change their column
types.  Guess that means I'd need to bring in attlognum's tho.

Chris



Hard-coded PUBLIC in pg_dump

From
Nicolai Tufar
Date:
src/bin/pg_dump/pg_dump.c happen to have hard-coded PUBLIC role name.
It completly breaks dumps when run with Turksh locale setting. In my
opinion making it lower-case would do much good and no harm. A mini
patch is given below.

On the other hand, I was thinking about wrapping all the identifiers in
dump files in single quotes. It is done in "SET SESSION AUTHORIZATION"
clause. Is there a reason for not doing this with table and colum names?

Regards,
Nic




*** ./src/bin/pg_dump/pg_dump.c.orig    Sun Dec  1 03:23:56 2002
--- ./src/bin/pg_dump/pg_dump.c    Sun Dec  1 03:24:48 2002
***************
*** 4918,4924 ****      * wire-in knowledge about the default public privileges for different      * kinds of objects.
   */
 
!     appendPQExpBuffer(sql, "REVOKE ALL ON %s %s FROM PUBLIC;\n",                       type, name);      /* Make a
workingcopy of acls so we can use strtok */
 
--- 4918,4924 ----      * wire-in knowledge about the default public privileges for different      * kinds of objects.
   */
 
!     appendPQExpBuffer(sql, "REVOKE ALL ON %s %s FROM public;\n",                       type, name);      /* Make a
workingcopy of acls so we can use strtok */
 
***************
*** 4980,4986 ****                 if (eqpos == tok)                 {                     /* Empty left-hand side
means"PUBLIC" */
 
!                     appendPQExpBuffer(sql, "PUBLIC;\n");                 }                 else if (strncmp(tok,
"group", strlen("group ")) == 0)                     appendPQExpBuffer(sql, "GROUP %s;\n",
 
--- 4980,4986 ----                 if (eqpos == tok)                 {                     /* Empty left-hand side
means"PUBLIC" */
 
!                     appendPQExpBuffer(sql, "public;\n");                 }                 else if (strncmp(tok,
"group", strlen("group ")) == 0)                     appendPQExpBuffer(sql, "GROUP %s;\n",
 




Re: Hard-coded PUBLIC in pg_dump

From
"Christopher Kings-Lynne"
Date:
> src/bin/pg_dump/pg_dump.c happen to have hard-coded PUBLIC role name.
> It completly breaks dumps when run with Turksh locale setting. In my
> opinion making it lower-case would do much good and no harm. A mini
> patch is given below.


Hmmmm...does putting double quotes (eg. "PUBLIC") around the public word fix
it?

> On the other hand, I was thinking about wrapping all the identifiers in
> dump files in single quotes. It is done in "SET SESSION AUTHORIZATION"
> clause. Is there a reason for not doing this with table and colum names?

You can't put single quotes around table and column names.  You need to use
double quotes as they are identifiers rather than literals.

Bear in mind that some improvements have been made in Postgres 7.3 with
regards to quoting, so have you checked 7.3?

Chris





Re: Hard-coded PUBLIC in pg_dump

From
"Nicolai Tufar"
Date:
----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
To: "Nicolai Tufar" <ntufar@apb.com.tr>; <pgsql-hackers@postgresql.org>
Sent: Sunday, December 01, 2002 4:05 AM
Subject: Re: [HACKERS] Hard-coded PUBLIC in pg_dump


>
> Hmmmm...does putting double quotes (eg. "PUBLIC") around the public word
fix
> it?

No:   apb=> GRANT SELECT ON TABLE maras2.esya TO "PUBLIC";   ERROR:  user "PUBLIC" does not exist   apb=> GRANT SELECT
ONTABLE maras2.esya TO 'PUBLIC';   ERROR:  parser: parse error at or near "'PUBLIC'" at character 38   apb=> GRANT
SELECTON TABLE maras2.esya TO public;   GRANT   apb=>
 

The problem here is case conversion from capital "I" to lower-case "i".
In Turkish locale tolower('I') is not equal to 'i'. So, since "public" role
is lower-case internally, why would we not make it lower-case in dump file.


>
> You can't put single quotes around table and column names.  You need to
use
> double quotes as they are identifiers rather than literals.
>
> Bear in mind that some improvements have been made in Postgres 7.3 with
> regards to quoting, so have you checked 7.3?

I stand corrected. It is indeed has to be double-quoted.

7.3 is quoting only SET SESSION AUTHORIZATION 'role' clause in my dump.
Possibly,
because it has been added recently. Old code does not quote anything.

>
> Chris

Regards,
Nic.



Re: 7.4 Wishlist

From
Neil Conway
Date:
On Sat, 2002-11-30 at 12:47, Stephan Szabo wrote:
> check constraints with subselects.

Have we decided how this would even work? Last I heard, Tom still had
some major reservations about the practicality of implementing these --
for example, would you re-evaluate all constraints that SELECT from a
table when the table changes?

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC





Re: 7.4 Wishlist

From
Joe Conway
Date:
David Wheeler wrote:
> On Saturday, November 30, 2002, at 04:14  PM, Joe Conway wrote:
>> Not quite sure what you mean by delimiter -- are you referring to double
>> escaping vs single escaping?
> 
> Oh crap, yes, that's exactly what I meant.
> 
> s/delimited/escaped/g;
> 

That is one thing I'd like to take a look at. I think the problem is that 
certain byte-sequence/multibyte-encoding combinations are illegal, so it's not 
as simple an issue as it might first appear.

Joe



Re: 7.4 Wishlist

From
David Wheeler
Date:
On Saturday, November 30, 2002, at 07:24  PM, Joe Conway wrote:

> That is one thing I'd like to take a look at. I think the problem is 
> that certain byte-sequence/multibyte-encoding combinations are 
> illegal, so it's not as simple an issue as it might first appear.

My understanding is that the nul character is legal in a byte sequence, 
but if it's not properly escaped, it'll be parsed as the end of the 
statement. Unfortunately, I think that it's a very tough problem to 
solve.

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e                                               Jabber:
Theory@jabber.org



Re: Hard-coded PUBLIC in pg_dump

From
Tom Lane
Date:
Nicolai Tufar <ntufar@apb.com.tr> writes:
> src/bin/pg_dump/pg_dump.c happen to have hard-coded PUBLIC role name.

As it should.  I think the real problem here is the hack in gram.y:

grantee:    ColId               {                   PrivGrantee *n = makeNode(PrivGrantee);                   /* This
hacklets us avoid reserving PUBLIC as a keyword*/                   if (strcmp($1, "public") == 0)
n->username = NULL;                   else                       n->username = $1;                   n->groupname =
NULL;                  $$ = (Node *)n;               }
 

If the parser treated PUBLIC as an actual keyword, you'd not be having
this problem, because keywords are case-folded on an ASCII-only basis
(which is consistent with the SQL99 spec, amazingly enough).

We put in the above hack after someone complained that PUBLIC didn't use
to be a reserved word ... but considering that SQL92 clearly lists it as
a reserved word, there's not a lot of ground for that complaint to stand
on.

I'd prefer shifting PUBLIC back to the true-keyword category over any
of the other workarounds you've suggested ...
        regards, tom lane


Re: Hard-coded PUBLIC in pg_dump

From
"Nicolai Tufar"
Date:
----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
> ... but considering that SQL92 clearly lists it as
> a reserved word, there's not a lot of ground for that complaint to stand
> on.
> 
> I'd prefer shifting PUBLIC back to the true-keyword category over any
> of the other workarounds you've suggested ...

It will work for me.
But why not change PUBLIC in pg_dump output to lower-case as well?

> 
> regards, tom lane

Nic.



Re: 7.4 Wishlist

From
Tom Lane
Date:
Joe Conway <mail@joeconway.com> writes:
>    - possibly enhanced connectby functionality (may not be worth it if
>      RECURSIVE JOIN functionality makes it into 7.4)

Several of my Red Hat cohorts are pretty interested in making the
RECURSIVE query stuff work for 7.4.  (The fact that they're ex-DB2
folk might explain their preference for the SQL99 syntax, nee DB2
syntax, over Oracle's CONNECT BY ... but I'm with them ;-(.  Oracle's
recursive-join syntax is nearly as bad as their outer-join syntax.)
        regards, tom lane


Re: [GENERAL] Segmentation fault while COPY in 7.3

From
"Nicolai Tufar"
Date:
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>


> Ohhh ...
>
> Nicolai, are you running with a client encoding different from server
> encoding?

Got it!
Gentlemen, thank you very much for assistance. The body of evidence was
slowly
growing, then, finaly Tom Lan's message have enlightened me.

It all started with initdb's warning that it can not set client's connection
to 'LATIN5'.
Okay, I said, maybe some system files are not installed. Then I tried to
create a database
with ENCODING='LATIN5'. I did not pay much attention either since my
template1 and
template0 are in LATIN5. Then on Tom's suggestion I tried to change client
encoding:

aaa=# \encoding
LATIN5
aaa=# \encoding SQL_ASCII
SQL_ASCII: invalid encoding name or conversion procedure not found
aaa=# \encoding LATIN5
LATIN5: invalid encoding name or conversion procedure not found
aaa=# \encoding SQL_ASCii
aaa=# \encoding
SQL_ASCII
aaa=# \encoding LATiN5
aaa=# \encoding
LATIN5
aaa=#

So it all falls back to Turkish "I" problem I mentioned earlier. To
perform COPY successfully I had to set client locale to 'LATiN5'
(note quotes and lower-case "i"). Any other combinations result
in segmentation fault.

Now, would it be right to change locale name handling to use ASCII-only
lower and upper-case conversions?

Many thanks to Tom Lane and Joe Conway.


I've got to get some sleep :-)
Regards,
Nic.




Re: 7.4 Wishlist

From
Stephan Szabo
Date:
On 30 Nov 2002, Neil Conway wrote:

> On Sat, 2002-11-30 at 12:47, Stephan Szabo wrote:
> > check constraints with subselects.
>
> Have we decided how this would even work? Last I heard, Tom still had
> some major reservations about the practicality of implementing these --
> for example, would you re-evaluate all constraints that SELECT from a
> table when the table changes?

You'd have to either do it in all cases or come up with something that was
smart enough to limit the cases to some extent based on the expression. I
doubt that it'd perform terribly well, especially at first.  I can't see
any justification for doing it as insert/update on main table only since
by my reading of the spec the constraint is logically checked at the end
of each statement (or transaction) even if we would normally not do so in
practice when we know the constraint shouldn't be violated.

Of course this was in the general set of, if I had months and months and
nothing else to do (like work) then I'd want to look at it because I think
it'd be useful.



Re: 7.4 Wishlist

From
Florian Weimer
Date:
"Matthew T. O'Connor" <matthew@zeut.net> writes:

> pg_dump, our upgrade process is painful enough having to do a dump,
> reload.  I think we should be able to guarantee (or at least let
> much closer to it) that the process works in all cases.

I would already be happy if pg_dump backed up my databases correctly,
so that I dont have to reorder SQL statements manually in the dump
before psql can execute it.

-- 
Florian Weimer                       Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart           http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT                          fax +49-711-685-5898


Re: 7.4 Wishlist

From
Tom Lane
Date:
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On 30 Nov 2002, Neil Conway wrote:
>> Have we decided how this would even work? Last I heard, Tom still had
>> some major reservations about the practicality of implementing these --
>> for example, would you re-evaluate all constraints that SELECT from a
>> table when the table changes?

> You'd have to either do it in all cases or come up with something that was
> smart enough to limit the cases to some extent based on the expression. I
> doubt that it'd perform terribly well, especially at first.

Note that you can get the "stupid" semantics (run the subselect only
when the constrained table changes) today: just hide the subselect in
a user-defined function that's called from the constraint expression.
Or put the whole check in a trigger instead of using a constraint.

I don't think we should bother with direct support of subselects in
constraints unless we can come up with an implementation that is
significantly better than what you can accomplish with these
workarounds.
        regards, tom lane


Re: [GENERAL] Segmentation fault while COPY in 7.3

From
Tom Lane
Date:
"Nicolai Tufar" <ntufar@apb.com.tr> writes:
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
>> Ohhh ...
>>
>> Nicolai, are you running with a client encoding different from server
>> encoding?

> Got it!

Okay, the problem is a double free of memory in COPY OUT.  I've applied
the following patch for 7.3.1.  (A better fix would be for COPY OUT to
run in a local memory context that it could reset every so often, like
once per row, and then we could remove these risky retail pfree's entirely.
I plan to do that instead in development sources.)

            regards, tom lane

*** src/backend/commands/copy.c.orig    Fri Oct 18 20:25:36 2002
--- src/backend/commands/copy.c    Sun Dec  1 12:28:24 2002
***************
*** 1470,1478 ****
      char       *string;
      char        c;
      char        delimc = delim[0];
-
      bool        same_encoding;
!     char       *string_start;
      int            mblen;
      int            i;

--- 1470,1477 ----
      char       *string;
      char        c;
      char        delimc = delim[0];
      bool        same_encoding;
!     char       *string_start = NULL;
      int            mblen;
      int            i;

***************
*** 1481,1492 ****
      {
          string = (char *) pg_server_to_client((unsigned char *) server_string,
                                                strlen(server_string));
!         string_start = string;
      }
      else
      {
          string = server_string;
-         string_start = NULL;
      }

      for (; (c = *string) != '\0'; string += mblen)
--- 1480,1491 ----
      {
          string = (char *) pg_server_to_client((unsigned char *) server_string,
                                                strlen(server_string));
!         if (string != server_string)
!             string_start = string;
      }
      else
      {
          string = server_string;
      }

      for (; (c = *string) != '\0'; string += mblen)

Re: 7.4 Wishlist

From
Stephan Szabo
Date:
On Sun, 1 Dec 2002, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > On 30 Nov 2002, Neil Conway wrote:
> >> Have we decided how this would even work? Last I heard, Tom still had
> >> some major reservations about the practicality of implementing these --
> >> for example, would you re-evaluate all constraints that SELECT from a
> >> table when the table changes?
>
> > You'd have to either do it in all cases or come up with something that was
> > smart enough to limit the cases to some extent based on the expression. I
> > doubt that it'd perform terribly well, especially at first.
>
> Note that you can get the "stupid" semantics (run the subselect only
> when the constrained table changes) today: just hide the subselect in
> a user-defined function that's called from the constraint expression.
> Or put the whole check in a trigger instead of using a constraint.
>
> I don't think we should bother with direct support of subselects in
> constraints unless we can come up with an implementation that is
> significantly better than what you can accomplish with these
> workarounds.

Well, the problem is that user defined triggers trying to do the real
semantics for update/insert on the "other" tables of the constraint seem
to me like they'll have the same issues as foreign keys do currently,
either you'll be forced to write something too strong and deadlock alot,
or you'll write something too weak and end up with constraint violations
with concurrent transactions unless you basically write a very low level C
function to do it for you.  I guess this, since in general, the non-action
foreign keys really are just check constraints with a subselect
effectively.



Re: Hard-coded PUBLIC in pg_dump

From
Bruce Momjian
Date:
Tom Lane wrote:
> If the parser treated PUBLIC as an actual keyword, you'd not be having
> this problem, because keywords are case-folded on an ASCII-only basis
> (which is consistent with the SQL99 spec, amazingly enough).
> 
> We put in the above hack after someone complained that PUBLIC didn't use
> to be a reserved word ... but considering that SQL92 clearly lists it as
> a reserved word, there's not a lot of ground for that complaint to stand
> on.
> 
> I'd prefer shifting PUBLIC back to the true-keyword category over any
> of the other workarounds you've suggested ...

PUBLIC doesn't seem like a very common column name --- seems safe to
make it reserved.  We made 'value' reserved in 7.3, and that was a much
more common one.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Hard-coded PUBLIC in pg_dump

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> PUBLIC doesn't seem like a very common column name --- seems safe to
> make it reserved.  We made 'value' reserved in 7.3, and that was a much
> more common one.

I'm still quite unhappy about 'value', and would like to look into
making it unreserved again.  This business does show that there are some
pitfalls in that, though :-(
        regards, tom lane


Re: Hard-coded PUBLIC in pg_dump

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We made 'value' reserved in 7.3, and that was a much
> more common one.

BTW, you mean "current" not "7.3".  That patch has still got some
serious problems anyway:

7.3:

regression=# select value;
ERROR:  Attribute "value" not found

HEAD:

regression=# select value;
server closed the connection unexpectedly       This probably means the server terminated abnormally       before or
whileprocessing the request.
 
The connection to the server was lost. Attempting reset: Failed.
        regards, tom lane


Re: Hard-coded PUBLIC in pg_dump

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > We made 'value' reserved in 7.3, and that was a much
> > more common one.
> 
> BTW, you mean "current" not "7.3".  That patch has still got some
> serious problems anyway:

Yes, I realized later it was current. I was fixing the dbdpg regression
tests, and git bitten by that, and forgot I was using current and not
7.3.

> 
> 7.3:
> 
> regression=# select value;
> ERROR:  Attribute "value" not found
> 
> HEAD:
> 
> regression=# select value;
> server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.

Yow!

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Hard-coded PUBLIC in pg_dump

From
David Wheeler
Date:
On Sunday, December 1, 2002, at 10:49  AM, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> PUBLIC doesn't seem like a very common column name --- seems safe to
>> make it reserved.  We made 'value' reserved in 7.3, and that was a 
>> much
>> more common one.
>
> I'm still quite unhappy about 'value', and would like to look into
> making it unreserved again.  This business does show that there are 
> some
> pitfalls in that, though :-(

Actually, I don't think it's reserved in 7.3, only in the 7.4 
development sources. Otherwise, Bricolage would fail hard, and it 
doesn't. So there's some time to play with this issue, I think.

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e                                               Jabber:
Theory@jabber.org



Re: Hard-coded PUBLIC in pg_dump

From
Rod Taylor
Date:
> > regression=# select value;
> > ERROR:  Attribute "value" not found
> >
> > HEAD:
> >
> > regression=# select value;
> > server closed the connection unexpectedly
> >         This probably means the server terminated abnormally
> >         before or while processing the request.
> > The connection to the server was lost. Attempting reset: Failed.
>
> Yow!

I believe these are fixed in the patch I sent in last week.

--
Rod Taylor <rbt@rbt.ca>

Re: Hard-coded PUBLIC in pg_dump

From
Rod Taylor
Date:
> > regression=# select value;
> > ERROR:  Attribute "value" not found
> >
> > HEAD:
> >
> > regression=# select value;
> > server closed the connection unexpectedly
> >         This probably means the server terminated abnormally
> >         before or while processing the request.
> > The connection to the server was lost. Attempting reset: Failed.
>
> Yow!

I believe these are fixed in the patch I sent in last week.

--
Rod Taylor <rbt@rbt.ca>

Re: 7.4 Wishlist

From
Joe Conway
Date:
David Wheeler wrote:
> My understanding is that the nul character is legal in a byte sequence, 
> but if it's not properly escaped, it'll be parsed as the end of the 
> statement. Unfortunately, I think that it's a very tough problem to solve.

No question wrt '\0' bytes -- they would have to be escaped when casting from 
bytea to text.

The harder issue is that there are apparently many other multiple byte 
sequences that, while valid in an ASCII encoding, are not valid in one or more 
multibyte encodings. See this thread:

http://archives.postgresql.org/pgsql-hackers/2002-04/msg00236.php

This is why currently all "non printable characters" are escaped (which I 
think is all bytes > 127). Text on the other hand is already known to be valid 
for a particular encoding, so it doesn't need escaping.

I'm not sure what happens when the backend encoding and client encoding don't 
match -- I'd guess there is some probability of invalid byte sequences in that 
case too.

Joe



7.4 Wishlist

From
Lee Kindness
Date:
Christopher Kings-Lynne writes:> Just out of interest, if someone was going to pay you to hack on Postgres> for 6
months,what would you like to code for 7.4?
 

Well judging by the hoards on Slashdot, it would appear that
replication is the hot enhancement...
Slashdot | PostgreSQL 7.3 Releasedhttp://developers.slashdot.org/article.pl?sid=02/11/30/1815200

Lee.


Re: 7.4 Wishlist

From
Karel Zak
Date:
On Fri, Nov 29, 2002 at 10:51:26AM -0800, Christopher Kings-Lynne wrote:
> Hi guys,
> 
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?
This is interesting discussion.. my wish: * error codes. It's very interesting that nobody other wants it...

   Karel

-- Karel Zak  <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/


Re: 7.4 Wishlist

From
Prasanna Phadke
Date:
<p>How about giving OLAP (Dimension / Measure) functionality to PG.<p><img height="28"
src="http://sg.yimg.com/i/aa/icons/28/cricket.gif"width="28" /> Catch all the cricket action. Download <a
href="http://in.sports.yahoo.com/cricket/tracker.html"target="_blank"> Yahoo! Score tracker</a> 

Re: 7.4 Wishlist

From
Bruce Momjian
Date:
Joe Conway wrote:
> David Wheeler wrote:
> > My understanding is that the nul character is legal in a byte sequence, 
> > but if it's not properly escaped, it'll be parsed as the end of the 
> > statement. Unfortunately, I think that it's a very tough problem to solve.
> 
> No question wrt '\0' bytes -- they would have to be escaped when casting from 
> bytea to text.
> 
> The harder issue is that there are apparently many other multiple byte 
> sequences that, while valid in an ASCII encoding, are not valid in one or more 
> multibyte encodings. See this thread:
> 
> http://archives.postgresql.org/pgsql-hackers/2002-04/msg00236.php
> 
> This is why currently all "non printable characters" are escaped (which I 
> think is all bytes > 127). Text on the other hand is already known to be valid 
> for a particular encoding, so it doesn't need escaping.
> 
> I'm not sure what happens when the backend encoding and client encoding don't 
> match -- I'd guess there is some probability of invalid byte sequences in that 
> case too.

I think there is some idea of changing the frontend/backend protocol to
prevent the need for escaping > \127 characters.  I believe it is
currently only required when the frontend/backend protocol have
different encodings.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: 7.4 Wishlist

From
"Magnus Naeslund(f)"
Date:
Mysql is planning on making this work:
SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id.

Do we have anything like it (After a discussion with Tom i figure no).
User variables is nice, especially in these kind of queries.

Nice would be to be able to use them as in C (almost everywhere):

SELECT id, @x FROM table_name t where (@x := date_part('days'. now() -
f.created)) > 100;

As Tom said in earlier mail, it might not be that big of a win in this
case, but if uses expensive functions, it will.

Magnus



Re: 7.4 Wishlist

From
"Christopher Kings-Lynne"
Date:
> Mysql is planning on making this work:
> 
>  SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id.
> 
> Do we have anything like it (After a discussion with Tom i figure no).
> User variables is nice, especially in these kind of queries.

Well of course they have to make that work - they don't have subselects :P

Chris



Re: 7.4 Wishlist

From
"Magnus Naeslund(f)"
Date:
Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
>> Mysql is planning on making this work:
>>
>>  SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY
>> id.
>>
>> Do we have anything like it (After a discussion with Tom i figure
>> no). User variables is nice, especially in these kind of queries.
>
> Well of course they have to make that work - they don't have
> subselects :P
>
> Chris

Yeah, but there is a point about running count(*) one time too many.
Say if i would like to get a prettyprinting query like this:

SELECT id, @a:=COUNT(*), sum_col::text||'/'@a::text FROM table_name;

That would be DAMN expensive doing with a subselect:

SELECT id, sum_col||'/'||(select COUNT(*) from tabe_name) FROM
table_name;

I know this example suck eggs, but you get the point where it hurts,
right?

Magnus - sorry for the dupe, chris



Re: 7.4 Wishlist

From
"Christopher Kings-Lynne"
Date:
> Yeah, but there is a point about running count(*) one time too many.
> Say if i would like to get a prettyprinting query like this:
>
> SELECT id, @a:=COUNT(*), sum_col::text||'/'@a::text FROM table_name;
>
> That would be DAMN expensive doing with a subselect:
>
> SELECT id, sum_col||'/'||(select COUNT(*) from tabe_name) FROM
> table_name;
>
> I know this example suck eggs, but you get the point where it hurts,
> right?

Are you sure that postgres evaluates that subselect more than once?  It
looks to me like it returns a constant result for every row and hence it
will be evaluated once per statement, not once per row.  I'm no expert tho.
Can someone answer this?

And if the subselect changes for each row (ie. it's a correlated subquery)
then you cannot use the variable anyway.

It seems to me that if postgres doesn't consider count(*) as a constant then
perhaps it should be taught to?  Should be safe shouldn't it?  I guess if a
function in your select statemnt is inserting a row then there's trouble.
But if there is, then the sum/count(*) is nonsensical anyway.

Chris



Re: 7.4 Wishlist

From
Stephan Szabo
Date:
On Mon, 2 Dec 2002, Magnus Naeslund(f) wrote:

> Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> >> Mysql is planning on making this work:
> >>
> >>  SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY
> >> id.
> >>
> >> Do we have anything like it (After a discussion with Tom i figure
> >> no). User variables is nice, especially in these kind of queries.
> >
> > Well of course they have to make that work - they don't have
> > subselects :P
> >
> > Chris
>
> Yeah, but there is a point about running count(*) one time too many.
> Say if i would like to get a prettyprinting query like this:
>
> SELECT id, @a:=COUNT(*), sum_col::text||'/'@a::text FROM table_name;
>
> That would be DAMN expensive doing with a subselect:
>
> SELECT id, sum_col||'/'||(select COUNT(*) from tabe_name) FROM
> table_name;
>
> I know this example suck eggs, but you get the point where it hurts,
> right?


If you use a scalar subquery, yes, but I think a subselect in from would
help, maybe something like (if you want the total count)

select table_name.id, sum(sum_col)||'/'||t2.count from table_name, (select
count(*) as count from table_name) as t2 group by table_name.id,t2.count;

or (if you want each count the counter per group) either
select id, sum(sum_col)||'/'||count(*) from table_name
group by id;

or
select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
(select id, count(*) as count from table_name group by id) as t2 where
table_name.id=t2.id group by table_name.id,t2.count;



Re: 7.4 Wishlist

From
"Magnus Naeslund(f)"
Date:
Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> Are you sure that postgres evaluates that subselect more than once?
> It looks to me like it returns a constant result for every row and
> hence it will be evaluated once per statement, not once per row.  I'm
> no expert tho. Can someone answer this?
>
> And if the subselect changes for each row (ie. it's a correlated
> subquery) then you cannot use the variable anyway.
>
> It seems to me that if postgres doesn't consider count(*) as a
> constant then perhaps it should be taught to?  Should be safe
> shouldn't it?  I guess if a function in your select statemnt is
> inserting a row then there's trouble. But if there is, then the
> sum/count(*) is nonsensical anyway.
>
> Chris
>

It looks like it (7.2.x):

# time psql genline -c "select id from xxxx" > /dev/null
real    0m0.694s
user    0m0.147s
sys     0m0.025s
# time psql genline -c "select id,id||'/'||(select count(*) from xxxx)
as x from xxxx" > /dev/null

real    0m2.202s
user    0m0.263s
sys     0m0.040s

# time psql genline -c "select id,(select count(*) from bildsekvens) as
x from xxxx" > /dev/null

real    0m1.479s
user    0m0.254s
sys     0m0.047s

They were taken from a busy system, but i ran the several times showing
about the same result.

Magnus



Re: 7.4 Wishlist

From
"Magnus Naeslund(f)"
Date:
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
>
> If you use a scalar subquery, yes, but I think a subselect in from
> would help, maybe something like (if you want the total count)
>
> select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
> (select count(*) as count from table_name) as t2 group by
> table_name.id,t2.count;
>
> or (if you want each count the counter per group) either
>
>  select id, sum(sum_col)||'/'||count(*) from table_name
> group by id;
>
> or
>
>  select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
> (select id, count(*) as count from table_name group by id) as t2 where
> table_name.id=t2.id group by table_name.id,t2.count;
>

Give it up already, i was MAKING A POINT, not trying to make an
optimized count(*) thing :)
There are other examples that you cannot get around, that will be
evaluated more than once when a local "user variable" would make it not
need to.

Magnus



Re: 7.4 Wishlist

From
Stephan Szabo
Date:
On Mon, 2 Dec 2002, Magnus Naeslund(f) wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> >
> > If you use a scalar subquery, yes, but I think a subselect in from
> > would help, maybe something like (if you want the total count)
> >
> > select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
> > (select count(*) as count from table_name) as t2 group by
> > table_name.id,t2.count;
> >
> > or (if you want each count the counter per group) either
> >
> >  select id, sum(sum_col)||'/'||count(*) from table_name
> > group by id;
> >
> > or
> >
> >  select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
> > (select id, count(*) as count from table_name group by id) as t2 where
> > table_name.id=t2.id group by table_name.id,t2.count;
> >
>
> Give it up already, i was MAKING A POINT, not trying to make an
> optimized count(*) thing :)
> There are other examples that you cannot get around, that will be
> evaluated more than once when a local "user variable" would make it not
> need to.

For most cases sticking an expression in a subselect in from works to not
re-evaluate it (unless it's correlated in which case I don't think local
variables help).  It might not be as optimal in all cases, but probably is
sufficient in most cases.



Re: 7.4 Wishlist

From
Hannu Krosing
Date:
Magnus Naeslund(f) kirjutas T, 03.12.2002 kell 03:18:
> It looks like it (7.2.x):
> 
> # time psql genline -c "select id from xxxx" > /dev/null
> real    0m0.694s
> user    0m0.147s
> sys     0m0.025s
> # time psql genline -c "select id,id||'/'||(select count(*) from xxxx)
> as x from xxxx" > /dev/null
> 
> real    0m2.202s
> user    0m0.263s
> sys     0m0.040s
> 
> # time psql genline -c "select id,(select count(*) from bildsekvens) as
> x from xxxx" > /dev/null
> 
> real    0m1.479s
> user    0m0.254s
> sys     0m0.047s

what is the time for

select id,x from xxxx,      (select count(*) as x from bildsekvens) c ;



> They were taken from a busy system, but i ran the several times showing
> about the same result.
> 
> Magnus
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
-- 
Hannu Krosing <hannu@tm.ee>


Re: 7.4 Wishlist

From
"Magnus Naeslund(f)"
Date:
Hannu Krosing <hannu@tm.ee> wrote:
> Magnus Naeslund(f) kirjutas T, 03.12.2002 kell 03:18:
>> It looks like it (7.2.x):
>>
>> # time psql genline -c "select id from xxxx" > /dev/null
>> real    0m0.694s
>> user    0m0.147s
>> sys     0m0.025s
>> # time psql genline -c "select id,id||'/'||(select count(*) from
>> xxxx) as x from xxxx" > /dev/null
>>
>> real    0m2.202s
>> user    0m0.263s
>> sys     0m0.040s
>>
>> # time psql genline -c "select id,(select count(*) from bildsekvens)
>> as x from xxxx" > /dev/null
>>
>> real    0m1.479s
>> user    0m0.254s
>> sys     0m0.047s
>
> what is the time for
>
> select id,x
>   from xxxx,
>        (select count(*) as x from bildsekvens) c ;
>
>
>

time psql genline -c "select id,x from xxxx, (select count(*) as x from
xxxx) c ;" > /dev/null

real    0m1.354s
user    0m0.268s
sys     0m0.028s

The xxxx and the other table is the same table, sorry i screwed up in
the last cut'n'paste operation.
As i said it's a loaded system, the figures vary a little bit between
runs.

Magnus




Re: 7.4 Wishlist

From
Gavin Sherry
Date:
I want to see:

i) proper resource management a-la Oracle. This would allow a DBA to
limited the amount of time any given user spends in the parser, planner or
executor. It would be limited with a more sophisticated user system,
including things like CREATE USER PROFILE ...

ii) Auditing. Who accessed what, how, when and for how long with the
ability to store queries, planner stats, executor stats and what ever else
we can tie to a query. Very useful for debugging and security. You can get
this from the logs but it is non trivial to perform adhoc reporting and
statistical analysis. Why not store it.. in a database? :-)

iii) SQL99 error code

iv) Updatable and insertable-into views (per SQL99). Perhaps a two stage
implementation: i) rules ii) have the planner/executor handle it, instead
of the rewriter. The latter will take more coding, and might touch too
much of the code, considering the other significant changes planned for
7.4.

v) Better PL/PgSQL parser and memory handling

vi) A larger number of case studies on the advocacy site, with a larger
degree of financial and ROI analysis, all that jazz

vii) Collections of information about migrating from other significant
platforms: oracle, db2, sybase, interbase, SQL server -- a very popular
request on irc.openprojects.net

viii) General advocacy, particularly in pushing mainstream IT media
coverage, conferences and university usage -- both for teaching SQL and
for teach database engineering concepts for senior undergrads.

I've no idea how much time I can put into these, but they're on my TODO
list.

Gavin



Re: 7.4 Wishlist

From
"Christopher Kings-Lynne"
Date:
> i) proper resource management a-la Oracle. This would allow a DBA to
> limited the amount of time any given user spends in the parser, planner or
> executor. It would be limited with a more sophisticated user system,
> including things like CREATE USER PROFILE ...

Hehehe - yeah this would be neat.  Would be somewhat better than MySQL's
MAX_QUERIES_PER_HOUR setting :P

> vii) Collections of information about migrating from other significant
> platforms: oracle, db2, sybase, interbase, SQL server -- a very popular
> request on irc.openprojects.net

There's lots of good information on this on techdocs.postgresql.org.  BTW,
what happened to the PostgreSQL portal site that someone was working on?
It'd be very interested in taking that on...

> viii) General advocacy, particularly in pushing mainstream IT media
> coverage, conferences and university usage -- both for teaching SQL and
> for teach database engineering concepts for senior undergrads.

Definitely.  How about a resource for college/uni professors on how to use
PostgreSQL in their courses?

Chris



Re: 7.4 Wishlist

From
Gavin Sherry
Date:
On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote:

> > viii) General advocacy, particularly in pushing mainstream IT media
> > coverage, conferences and university usage -- both for teaching SQL and
> > for teach database engineering concepts for senior undergrads.
> 
> Definitely.  How about a resource for college/uni professors on how to use
> PostgreSQL in their courses?

I might get together with some of the lecturers I've worked with in
Sydney to give such a document some weight. I must say, the problem is not
a technical one though. I've given talks to 3rd and 4th year students
about PostgreSQL -- technical, conceptual, political talks... you name
it. Out of 200 odd students, only about 5-10 actually seem interested. Its
terrible.

Why aren't they interested? They think that if they study Oracle
(instead) for 6 months they'll walk straight into a job with an extremely
high salary. Its a myth, but I cannot shake that perception.

In fact, things got very heated when two universities in Sydney moved
their SQL courses from Oracle and Sybase to PostgreSQL. Enrollments will
be down next year for the courses and Australian universities are heavily
geared toward bums on seats not facilitation of education.

Gavin




Re: 7.4 Wishlist

From
Mike Mascari
Date:
Gavin Sherry wrote:
> I want to see:
> 
> i) proper resource management a-la Oracle. This would allow a DBA to
> limited the amount of time any given user spends in the parser, planner or
> executor. It would be limited with a more sophisticated user system,
> including things like CREATE USER PROFILE ...

Amen:


http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3DB99C0A.70900%40mascari.com&rnum=1&prev=/groups%3Fq%3DCREATE%2BPROFILE%2BMike%2BMascari%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8

To avoid unnecessary cycles being spent on loading the profile 
after session authorization, we could have a GUC as was 
suggested to turn the feature on or off. This mirrors Oracle, 
where you have to set RESOURCE_LIMIT in your init[SID].ora file 
before PROFILEs are enforced. Some people like sticking 
everything in postgresql.conf though, including resource limits. 
I'm not sure how remote administration is supposed to work under 
such a scenario though...

Mike Mascari
mascarm@mascari.com




Re: 7.4 Wishlist

From
"Christopher Kings-Lynne"
Date:
> I might get together with some of the lecturers I've worked with in
> Sydney to give such a document some weight. I must say, the problem is not
> a technical one though. I've given talks to 3rd and 4th year students
> about PostgreSQL -- technical, conceptual, political talks... you name
> it. Out of 200 odd students, only about 5-10 actually seem interested. Its
> terrible.

I've given a talk in the 2002 honours lecture series at UWA about Postgres
and some of the things it can do.  All of those guys were interested.
Especially since the deptartment does a lot of work in genetic algoriithms.

Tell me when you start working on a document - I'm happy to help.  Since I'm
only just out of Uni, I'd like to write a set of possible assignments and
learning outcomes and how you can use postgres to support them.

My girlfriend is a PhD student at UWA CS dept :) plus I won the honours
scholarship there a year or two back, so I can get interest from the dept,
including the databases lecturer.  Might help for another point of view and
feedback.

> Why aren't they interested? They think that if they study Oracle
> (instead) for 6 months they'll walk straight into a job with an extremely
> high salary. Its a myth, but I cannot shake that perception.

That's tragic.  Teaching kids to admin oracle is something you do in Tafe,
or an Oracle course, not a university.  Anyway, what kind of course teaches
you about how to admin oracle as opposed to teaching you about ACID
properties, MVCC, distributed transactions and partitioning?  Most of which
can be demonstrated with Postgres.  We learnt about relational model,
algebra and calculus well before learning about SQL!

Hell, my Uni (UWA) actually uses MS Access for crying out loud!  We learn
heaps of theory for 'real' databases (as above), but then our semester
project is to implement in MS Access a bunch of tables and queries for a UN
aid mission, for example.  Not once do you have to use SQL - you just use
the query builder.  How lame!

I have friends who have worked with people who've gone thru the oracle
course.  They say it's frustrating because they only understand what they've
been told to understand and have a lack of knowledge about basic, database
principles.

> In fact, things got very heated when two universities in Sydney moved
> their SQL courses from Oracle and Sybase to PostgreSQL. Enrollments will
> be down next year for the courses and Australian universities are heavily
> geared toward bums on seats not facilitation of education.

Universities are supposed to have a tradition of open source support.  Just
imagine if the professors could not only teach about how to do SQL, but ALSO
teach kids how a parser and executor and rewriter work by looking at the
actual source code!

Imagine those kids who go on to do honours, masters and PHD's in database
theory, indexing or whatever who could end up contributing to Postgres? ;)

What a sell!  (For a real uni, that is ;) )

Chris



PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

From
Gavin Sherry
Date:
On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote:

> I've given a talk in the 2002 honours lecture series at UWA about Postgres
> and some of the things it can do.  All of those guys were interested.
> Especially since the deptartment does a lot of work in genetic algoriithms.

Excellent. Can you put that talk online somewhere?

> Tell me when you start working on a document - I'm happy to help.  Since I'm
> only just out of Uni, I'd like to write a set of possible assignments and
> learning outcomes and how you can use postgres to support them.
> 
> My girlfriend is a PhD student at UWA CS dept :) plus I won the honours
> scholarship there a year or two back, so I can get interest from the dept,
> including the databases lecturer.  Might help for another point of view and
> feedback.

Excellent. Are there any other people involved in PostgreSQL and
universities or educational institutions? If so we could put something
together about experiences for the advocacy Web site.

> That's tragic.  Teaching kids to admin oracle is something you do in Tafe,
> or an Oracle course, not a university.  Anyway, what kind of course teaches
> you about how to admin oracle as opposed to teaching you about ACID
> properties, MVCC, distributed transactions and partitioning?  Most of which
> can be demonstrated with Postgres.  We learnt about relational model,
> algebra and calculus well before learning about SQL!

Your interest in this is clearly the same as mine: Universities
(should) teach concept not product. I'm disgusted that this is not the
case.

If other people are interested we could work on this in January when I am
over your way, as discussed in private email.

Gavin



Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

From
Justin Clift
Date:
Gavin Sherry wrote:
> On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote:
> 
> 
>>I've given a talk in the 2002 honours lecture series at UWA about Postgres
>>and some of the things it can do.  All of those guys were interested.
>>Especially since the deptartment does a lot of work in genetic algoriithms.
> 
> 
> Excellent. Can you put that talk online somewhere?
> 
> 
>>Tell me when you start working on a document - I'm happy to help.  Since I'm
>>only just out of Uni, I'd like to write a set of possible assignments and
>>learning outcomes and how you can use postgres to support them.
>>
>>My girlfriend is a PhD student at UWA CS dept :) plus I won the honours
>>scholarship there a year or two back, so I can get interest from the dept,
>>including the databases lecturer.  Might help for another point of view and
>>feedback.
> 
> 
> Excellent. Are there any other people involved in PostgreSQL and
> universities or educational institutions? If so we could put something
> together about experiences for the advocacy Web site.

Is this the kind of thing that the Techdocs Guides area would be good 
for?  (http://techdocs.postgresql.org/guides)

:-)

Regards and best wishes,

Justin Clift


<snip>
> 
> Gavin
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org




Re: 7.4 Wishlist

From
Bruce Momjian
Date:
Magnus Naeslund(f) wrote:
> >  select id, sum(sum_col)||'/'||count(*) from table_name
> > group by id;
> >
> > or
> >
> >  select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
> > (select id, count(*) as count from table_name group by id) as t2 where
> > table_name.id=t2.id group by table_name.id,t2.count;
> >
> 
> Give it up already, i was MAKING A POINT, not trying to make an
> optimized count(*) thing :)
> There are other examples that you cannot get around, that will be
> evaluated more than once when a local "user variable" would make it not
> need to.

Here's an even slimmer query that makes a var:test=> select var1.* from (select 1) as var1; ?column? ----------
1(1row)
 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: 7.4 Wishlist

From
"Magnus Naeslund(f)"
Date:
Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> Magnus Naeslund(f) wrote:
>>>  select id, sum(sum_col)||'/'||count(*) from table_name
>>> group by id;
>>>
>>> or
>>>
>>>  select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
>>> (select id, count(*) as count from table_name group by id) as t2
>>> where table_name.id=t2.id group by table_name.id,t2.count;
>>>
>>
>> Give it up already, i was MAKING A POINT, not trying to make an
>> optimized count(*) thing :)
>> There are other examples that you cannot get around, that will be
>> evaluated more than once when a local "user variable" would make it
>> not need to.
>
> Here's an even slimmer query that makes a var:
>
> test=> select var1.* from (select 1) as var1;
> ?column?
> ----------
>         1
> (1 row)

Good!
Now convert this query so that it only evaluates the date_part thing
ONCE:

select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) > 20;

I hope you all are kidding me in not seeing the real issue i'm trying to
show here.

Cheers
Magnus




Re: 7.4 Wishlist

From
"Christopher Kings-Lynne"
Date:
> Good!
> Now convert this query so that it only evaluates the date_part thing
> ONCE:
>
> select t.id, date_part('days',now()-t.stamp) from table_name t where
> date_part('days',now()-t.stamp) > 20;

Yes, it's a bit of a bugger that one.

> I hope you all are kidding me in not seeing the real issue i'm trying to
> show here.

I see, yes.  I guess the MySQL idea is to make it explicit to the parser
which parts of the expression are constant and equivalent.

Still, if the parser was smart enough, it could handle the above example
automatically.  However, I think it would be an O(n^2) problem to solve -
matching all subexpressions against all other subexpressions to find the
ones that match...so the MySQL idea is a valid one.

One trick that many people don't know is that you can do it fast in the
GROUP BY clause, just not the WHERE clause:

select t.id, date_part('days',now()-t.stamp) from table_name t group by 2;

Which is something I just discovered recently.

Chris




Re: 7.4 Wishlist

From
Bruce Momjian
Date:
Magnus Naeslund(f) wrote:
> Good!
> Now convert this query so that it only evaluates the date_part thing
> ONCE:
> 
> select t.id, date_part('days',now()-t.stamp) from table_name t where
> date_part('days',now()-t.stamp) > 20;
> 
> I hope you all are kidding me in not seeing the real issue i'm trying to
> show here.
> 
> Cheers
> Magnus
> 
> 
> 

Does this work?
SELECT t.id, x.date_part FROM table_name t, (select date_part('days',now()-t.stamp)) as xWHERE x.date_part > 20;

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: 7.4 Wishlist

From
Stephan Szabo
Date:
On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:

> Now convert this query so that it only evaluates the date_part thing
> ONCE:

That's not a good idea as long as t.stamp varies from row to row. ;)
Perhaps once per row, maybe... :)

> select t.id, date_part('days',now()-t.stamp) from table_name t where
> date_part('days',now()-t.stamp) > 20;

Potentially I think something like this would do it:
select t.id, t.foo from (select id, date_part('days', now()-stamp)
as foo from table_name except select null, null) as t where foo>20;

It's not really an optimization given the required except, but if there
was some way to tell the system not to push clauses down into a subselect
you wouldn't even need that.



Re: 7.4 Wishlist

From
"Magnus Naeslund(f)"
Date:
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
> 
>> Now convert this query so that it only evaluates the date_part thing
>> ONCE:
> 
> That's not a good idea as long as t.stamp varies from row to row. ;)
> Perhaps once per row, maybe... :)
> 

I give up



Re: 7.4 Wishlist

From
Stephan Szabo
Date:
On Tue, 3 Dec 2002, Bruce Momjian wrote:

> Magnus Naeslund(f) wrote:
> > Good!
> > Now convert this query so that it only evaluates the date_part thing
> > ONCE:
> >
> > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > date_part('days',now()-t.stamp) > 20;
> >
> > I hope you all are kidding me in not seeing the real issue i'm trying to
> > show here.
>
> Does this work?
>
>     SELECT t.id, x.date_part
>     FROM table_name t, (select date_part('days',now()-t.stamp)) as x
>     WHERE x.date_part > 20;

No, because the values in x are correlated to the particular row in
table_name, so I think you have to make it one big subselect in from.  In
addition the optimizer is "smart" enough to push the condition down in
most cases which I think will force the function to be called twice unless
you trigger one of its cases that prevent it from doing so. That's an
optimizer hint I'd like (don't push conditions into this subquery,
really...). :)




Re: 7.4 Wishlist

From
Tom Lane
Date:
"Magnus Naeslund(f)" <mag@fbab.net> writes:
> Mysql is planning on making this work:
>  SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id.

We're supposed to spend our time emulating nonstandard features that
don't even exist yet?  I think I have better things to do ...
        regards, tom lane


Re: 7.4 Wishlist

From
Dennis Björklund
Date:
On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:

> Now convert this query so that it only evaluates the date_part thing
> ONCE:
> 
> select t.id, date_part('days',now()-t.stamp) from table_name t where
> date_part('days',now()-t.stamp) > 20;

Something like this could work:

select * from (select t.id, date_part('days',now()-t.stamp) AS d         from table_name t) AS t1where t1.d > 20;

That aside I also would like some sort of local names. Something like the
let construct used in many functional languages (not exaclty what you want
above, but still):

let t1 = select * from foo;   t2 = select * from bar;
in select * from t1 natural join t2;

But even though I would like to give name to subexpressions like above, I
still think postgresql should stick to standards as close as possible.

-- 
/Dennis



Re: 7.4 Wishlist

From
Karel Zak
Date:
On Mon, Dec 02, 2002 at 12:48:38PM -0800, Christopher Kings-Lynne wrote:
> But if there is, then the sum/count(*) is nonsensical anyway.
You must to use it in SERIALIZABLE transaction isolation. 
   Karel

-- Karel Zak  <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/


Re: 7.4 Wishlist

From
Hannu Krosing
Date:
On Tue, 2002-12-03 at 09:20, Dennis Björklund wrote:
> On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
>
> > Now convert this query so that it only evaluates the date_part thing
> > ONCE:
> >
> > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > date_part('days',now()-t.stamp) > 20;
>
> Something like this could work:
>
> select *
>   from (select t.id, date_part('days',now()-t.stamp) AS d
>           from table_name t) AS t1
>  where t1.d > 20;
>
> That aside I also would like some sort of local names. Something like the
> let construct used in many functional languages (not exaclty what you want
> above, but still):
>
> let t1 = select * from foo;
>     t2 = select * from bar;
> in select * from t1 natural join t2;
>
> But even though I would like to give name to subexpressions like above, I
> still think postgresql should stick to standards as close as possible.

the standard way of doing it would be SQL99's WITH :

with t1 as (select * from foo)    t2 as (select * from bar)
select * from t1 natural join t2;

you can even use preceeding queries

with t1 as (select a,b from foo)    t1less as (select a,b from t1 where a < 0)    t1zero as (select a,b from t1 where a
=0) 
select * from t1zero, t1less, where t1zero.b = t1less.a;

Having working WITH clause is also a prerequisite to implementing SQL99
recursive queries (where each query in WITH clause sees all other
queries in the WITH clause)

I sent a patch to this list recently that implements the above syntax,
but I currently dont have knowledge (nor time to aquire it), so if
someone else does not do it it will have to wait until January.

OTOH, I think that turning my parsetree to a plan would be quite easy
for someone familiar with turning parestrees into plans ;)

I offer to check if it works in current (and make it work again if it
does not) if someone would be willing to hold my hand in implementation
parsetree-->plan part ;).

I think that for non-recursive queries this is all that needs to be
done, i.e. the plan would not care if the subqueries were from FROM,
from WITH or from separately defined views.

--
Hannu Krosing <hannu@tm.ee>


Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

From
Jan Hartmann
Date:
I'm certainly interested! I am working here on Geographical Information 
Systems with PostgreSQL/PostGIS with the Minnesota MapServer, with a lot 
of regular database work thrown in. PostgreSQL has great potential for 
teaching databases and SQL, and  when the native Windows port is ready, 
it will also be ideal for smaller, individual teaching projects (e.g. at 
home).

Jan Hartmann
Department of Geography
University of Amsterdam
jhart@frw.uva.nl

Gavin Sherry wrote:
> On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote:
> 
> 
>>I've given a talk in the 2002 honours lecture series at UWA about Postgres
>>and some of the things it can do.  All of those guys were interested.
>>Especially since the deptartment does a lot of work in genetic algoriithms.
> 
> 
> Excellent. Can you put that talk online somewhere?
> 
> 
>>Tell me when you start working on a document - I'm happy to help.  Since I'm
>>only just out of Uni, I'd like to write a set of possible assignments and
>>learning outcomes and how you can use postgres to support them.
>>
>>My girlfriend is a PhD student at UWA CS dept :) plus I won the honours
>>scholarship there a year or two back, so I can get interest from the dept,
>>including the databases lecturer.  Might help for another point of view and
>>feedback.
> 
> 
> Excellent. Are there any other people involved in PostgreSQL and
> universities or educational institutions? If so we could put something
> together about experiences for the advocacy Web site.
> 
> 
>>That's tragic.  Teaching kids to admin oracle is something you do in Tafe,
>>or an Oracle course, not a university.  Anyway, what kind of course teaches
>>you about how to admin oracle as opposed to teaching you about ACID
>>properties, MVCC, distributed transactions and partitioning?  Most of which
>>can be demonstrated with Postgres.  We learnt about relational model,
>>algebra and calculus well before learning about SQL!
> 
> 
> Your interest in this is clearly the same as mine: Universities
> (should) teach concept not product. I'm disgusted that this is not the
> case.
> 
> If other people are interested we could work on this in January when I am
> over your way, as discussed in private email.
> 
> Gavin
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 



Re: 7.4 Wishlist

From
Dennis Björklund
Date:
On 3 Dec 2002, Hannu Krosing wrote:

> the standard way of doing it would be SQL99's WITH :

Great! I havn't looked too much at sql99 yet so I've missed this. It's 
exactly what I want. Now I know what I will use in the future (when it's 
all implemented).

-- 
/Dennis



Re: 7.4 Wishlist

From
Bruce Momjian
Date:
Is WITH a TODO item?

---------------------------------------------------------------------------

Hannu Krosing wrote:
> On Tue, 2002-12-03 at 09:20, Dennis Bj?rklund wrote:
> > On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
> > 
> > > Now convert this query so that it only evaluates the date_part thing
> > > ONCE:
> > > 
> > > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > > date_part('days',now()-t.stamp) > 20;
> > 
> > Something like this could work:
> > 
> > select *
> >   from (select t.id, date_part('days',now()-t.stamp) AS d
> >           from table_name t) AS t1
> >  where t1.d > 20;
> > 
> > That aside I also would like some sort of local names. Something like the
> > let construct used in many functional languages (not exaclty what you want
> > above, but still):
> > 
> > let t1 = select * from foo;
> >     t2 = select * from bar;
> > in select * from t1 natural join t2;
> > 
> > But even though I would like to give name to subexpressions like above, I
> > still think postgresql should stick to standards as close as possible.
> 
> the standard way of doing it would be SQL99's WITH :
> 
> with t1 as (select * from foo)
>      t2 as (select * from bar)
> select * from t1 natural join t2;
> 
> you can even use preceeding queries
> 
> with t1 as (select a,b from foo)
>      t1less as (select a,b from t1 where a < 0)
>      t1zero as (select a,b from t1 where a = 0)
> select * from t1zero, t1less, where t1zero.b = t1less.a;
> 
> Having working WITH clause is also a prerequisite to implementing SQL99
> recursive queries (where each query in WITH clause sees all other
> queries in the WITH clause)
> 
> I sent a patch to this list recently that implements the above syntax,
> but I currently dont have knowledge (nor time to aquire it), so if
> someone else does not do it it will have to wait until January.
> 
> OTOH, I think that turning my parsetree to a plan would be quite easy
> for someone familiar with turning parestrees into plans ;)
> 
> I offer to check if it works in current (and make it work again if it
> does not) if someone would be willing to hold my hand in implementation
> parsetree-->plan part ;). 
> 
> I think that for non-recursive queries this is all that needs to be
> done, i.e. the plan would not care if the subqueries were from FROM,
> from WITH or from separately defined views.
> 
> -- 
> Hannu Krosing <hannu@tm.ee>
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: 7.4 Wishlist

From
Hannu Krosing
Date:
On Tue, 2002-12-03 at 16:00, Bruce Momjian wrote:
> Is WITH a TODO item?

It is disguised as 

Exotic Features
===============

* Add sql3 recursive unions

Which was added at my request in dark times, possibly when PostgreSQL
was called postgres95 ;)

This should be changed  to two items

* Add SQL99 WITH clause to SELECT

* Add SQL99 WITH RECURSIVE to SELECT


> ---------------------------------------------------------------------------
> 
> Hannu Krosing wrote:
> > On Tue, 2002-12-03 at 09:20, Dennis Bj?rklund wrote:
> > > On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
> > > 
> > > > Now convert this query so that it only evaluates the date_part thing
> > > > ONCE:
> > > > 
> > > > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > > > date_part('days',now()-t.stamp) > 20;
> > > 
> > > Something like this could work:
> > > 
> > > select *
> > >   from (select t.id, date_part('days',now()-t.stamp) AS d
> > >           from table_name t) AS t1
> > >  where t1.d > 20;
> > > 
> > > That aside I also would like some sort of local names. Something like the
> > > let construct used in many functional languages (not exaclty what you want
> > > above, but still):
> > > 
> > > let t1 = select * from foo;
> > >     t2 = select * from bar;
> > > in select * from t1 natural join t2;
> > > 
> > > But even though I would like to give name to subexpressions like above, I
> > > still think postgresql should stick to standards as close as possible.
> > 
> > the standard way of doing it would be SQL99's WITH :
> > 
> > with t1 as (select * from foo)
> >      t2 as (select * from bar)
> > select * from t1 natural join t2;
> > 
> > you can even use preceeding queries
> > 
> > with t1 as (select a,b from foo)
> >      t1less as (select a,b from t1 where a < 0)
> >      t1zero as (select a,b from t1 where a = 0)
> > select * from t1zero, t1less, where t1zero.b = t1less.a;
> > 
> > Having working WITH clause is also a prerequisite to implementing SQL99
> > recursive queries (where each query in WITH clause sees all other
> > queries in the WITH clause)
> > 
> > I sent a patch to this list recently that implements the above syntax,
> > but I currently dont have knowledge (nor time to aquire it), so if
> > someone else does not do it it will have to wait until January.
> > 
> > OTOH, I think that turning my parsetree to a plan would be quite easy
> > for someone familiar with turning parestrees into plans ;)
> > 
> > I offer to check if it works in current (and make it work again if it
> > does not) if someone would be willing to hold my hand in implementation
> > parsetree-->plan part ;). 
> > 
> > I think that for non-recursive queries this is all that needs to be
> > done, i.e. the plan would not care if the subqueries were from FROM,
> > from WITH or from separately defined views.
> > 
> > -- 
> > Hannu Krosing <hannu@tm.ee>
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> > 
-- 
Hannu Krosing <hannu@tm.ee>


Re: 7.4 Wishlist

From
Bruce Momjian
Date:
TODO updated.  Thanks for the clarification.

---------------------------------------------------------------------------

Hannu Krosing wrote:
> On Tue, 2002-12-03 at 16:00, Bruce Momjian wrote:
> > Is WITH a TODO item?
> 
> It is disguised as 
> 
> Exotic Features
> ===============
> 
> * Add sql3 recursive unions
> 
> Which was added at my request in dark times, possibly when PostgreSQL
> was called postgres95 ;)
> 
> This should be changed  to two items
> 
> * Add SQL99 WITH clause to SELECT
> 
> * Add SQL99 WITH RECURSIVE to SELECT
> 
> 
> > ---------------------------------------------------------------------------
> > 
> > Hannu Krosing wrote:
> > > On Tue, 2002-12-03 at 09:20, Dennis Bj?rklund wrote:
> > > > On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
> > > > 
> > > > > Now convert this query so that it only evaluates the date_part thing
> > > > > ONCE:
> > > > > 
> > > > > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > > > > date_part('days',now()-t.stamp) > 20;
> > > > 
> > > > Something like this could work:
> > > > 
> > > > select *
> > > >   from (select t.id, date_part('days',now()-t.stamp) AS d
> > > >           from table_name t) AS t1
> > > >  where t1.d > 20;
> > > > 
> > > > That aside I also would like some sort of local names. Something like the
> > > > let construct used in many functional languages (not exaclty what you want
> > > > above, but still):
> > > > 
> > > > let t1 = select * from foo;
> > > >     t2 = select * from bar;
> > > > in select * from t1 natural join t2;
> > > > 
> > > > But even though I would like to give name to subexpressions like above, I
> > > > still think postgresql should stick to standards as close as possible.
> > > 
> > > the standard way of doing it would be SQL99's WITH :
> > > 
> > > with t1 as (select * from foo)
> > >      t2 as (select * from bar)
> > > select * from t1 natural join t2;
> > > 
> > > you can even use preceeding queries
> > > 
> > > with t1 as (select a,b from foo)
> > >      t1less as (select a,b from t1 where a < 0)
> > >      t1zero as (select a,b from t1 where a = 0)
> > > select * from t1zero, t1less, where t1zero.b = t1less.a;
> > > 
> > > Having working WITH clause is also a prerequisite to implementing SQL99
> > > recursive queries (where each query in WITH clause sees all other
> > > queries in the WITH clause)
> > > 
> > > I sent a patch to this list recently that implements the above syntax,
> > > but I currently dont have knowledge (nor time to aquire it), so if
> > > someone else does not do it it will have to wait until January.
> > > 
> > > OTOH, I think that turning my parsetree to a plan would be quite easy
> > > for someone familiar with turning parestrees into plans ;)
> > > 
> > > I offer to check if it works in current (and make it work again if it
> > > does not) if someone would be willing to hold my hand in implementation
> > > parsetree-->plan part ;). 
> > > 
> > > I think that for non-recursive queries this is all that needs to be
> > > done, i.e. the plan would not care if the subqueries were from FROM,
> > > from WITH or from separately defined views.
> > > 
> > > -- 
> > > Hannu Krosing <hannu@tm.ee>
> > > 
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
> > > 
> -- 
> Hannu Krosing <hannu@tm.ee>
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

From
"Marc G. Fournier"
Date:
On Tue, 3 Dec 2002, Justin Clift wrote:

> > Excellent. Are there any other people involved in PostgreSQL and
> > universities or educational institutions? If so we could put something
> > together about experiences for the advocacy Web site.
>
> Is this the kind of thing that the Techdocs Guides area would be good
> for?  (http://techdocs.postgresql.org/guides)

Seems that any discussions about "experiences" belongs on Advocacy, no?



Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

From
Vince Vielhaber
Date:
On Tue, 3 Dec 2002, Marc G. Fournier wrote:

> On Tue, 3 Dec 2002, Justin Clift wrote:
>
> > > Excellent. Are there any other people involved in PostgreSQL and
> > > universities or educational institutions? If so we could put something
> > > together about experiences for the advocacy Web site.
> >
> > Is this the kind of thing that the Techdocs Guides area would be good
> > for?  (http://techdocs.postgresql.org/guides)
>
> Seems that any discussions about "experiences" belongs on Advocacy, no?

Where have you been?  The lines of distinction between all of the lists
have gotten so blurred it hardly makes a difference.

Vince.
--   http://www.meanstreamradio.com       http://www.unknown-artists.com        Internet radio: It's not file sharing,
it'sjust radio.
 



Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

From
"Marc G. Fournier"
Date:
On Tue, 3 Dec 2002, Vince Vielhaber wrote:

> On Tue, 3 Dec 2002, Marc G. Fournier wrote:
>
> > On Tue, 3 Dec 2002, Justin Clift wrote:
> >
> > > > Excellent. Are there any other people involved in PostgreSQL and
> > > > universities or educational institutions? If so we could put something
> > > > together about experiences for the advocacy Web site.
> > >
> > > Is this the kind of thing that the Techdocs Guides area would be good
> > > for?  (http://techdocs.postgresql.org/guides)
> >
> > Seems that any discussions about "experiences" belongs on Advocacy, no?
>
> Where have you been?  The lines of distinction between all of the lists
> have gotten so blurred it hardly makes a difference.

Actually, there are lines, Justin just occasionally appears to 'blur' them
until I get a chance to refresh them ... eh Justin?:)




Re: 7.4 Wishlist

From
Kevin Brown
Date:
Al Sutton wrote:
> Point to Point and Broadcast replication
> ----------------------------------------
> With point to point you specify multiple endpoints, with broadcast you can
> specify a subnet address and the updates are broadcast over that subnet.
> 
> The difference being that point to point works well for cross network
> replication, or where you have a few replicants. I have multiple database
> servers which could have a deadicated class C network that they are all on,
> by broadcasting updates you can cutdown the amount of traffic on that net by
> a factor of n minus 1 (where n is the number of servers involved).

Yech.  Now you can't use TCP anymore, so the underlying replication
code has to handle all the issues that TCP deals with transparently,
like error checking, retransmits, data windows, etc.  I don't think
it's wise to assume that your transport layer is 100% reliable.

Further, this doesn't even address the problem of bringing up a leaf
server that's been down a while.  It can be significantly out of date
relative to the other servers on the subnet.

I suspect you'll be better off implementing a replication protocol
that has the leaf nodes keeping each other up to date, to minimize the
traffic coming from the next level up.  Then you can use TCP for the
connections but minimize the traffic generated by any given node.

> Ability to use raw partitions
> ----------------------------
> 
> I've not seen an install of PostgreSQL yet that didn't put the database
> files onto a filesystem, so I'm assuming it's the only way of doing it. By
> using the filesystem the files are at the mercy of filesystem handler code
> as to where they end up on the disk, and thus the speed of access will
> always have some dependancy on the speed of the filesystem.
> 
> With a raw partition it would be possible to use two devices (e.g. /dev/hde
> and /dev/hdg on an eight channel ide linux box), and PostgreSQL could then
> ensure the WALs were located on one the disk with the entries running
> sequentally, and that the database files were located on the other disk in
> the most appropriate location (e.g. index data starting near the center of
> the disk, and user table data starting near the outside).

Yeah, but now you have to worry about optimizing placement of blocks,
optimizing writes, etc.  These are things the OS should worry about,
not the database server.

If you're really that concerned about these issues, store the WAL on
one (empty) filesystem and the tables on another (empty and separate)
filesystem.  With any reasonable filesystem you'll get reasonably
close to optimal performance, especially if the filesystem code is
capable of analyzing the write patterns and adapting itself
accordingly.

In short, I'd much rather spend the effort improving the filesystem
(where everyone can benefit) than improving PostgreSQL (where only
PostgreSQL users can benefit) for this item.

The one good reason for making it possible to use raw partitions is to
make it possible to use the PostgreSQL engine as a filesystem!  :-)


> Win32 Port
> ------------
> I've explained the reasons before. Apart from that it's always useful to
> open PostgreSQL up to a larger audience.

Agreed.


- Kevin Brown



Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

From
"Dan Langille"
Date:
On 3 Dec 2002 at 15:08, Vince Vielhaber wrote:

> Where have you been?  The lines of distinction between all of the
> lists have gotten so blurred it hardly makes a difference.

So consider this a wake up call.
-- 
Dan Langille : http://www.langille.org/



Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

From
Hans-Jürgen Schönig
Date:
Hi ...

I just wanted to admit that an important collegue in Vienna already uses
PostgreSQL instead of Oracle which makes me really proud :).

We have done a training course this year and they use PostgreSQL instead 
of free Oracle
I am happy that Austrian students are tortured with the things I have 
taught this year *g*..
   Hans


-- 
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at 
<http://cluster.postgresql.at>, www.cybertec.at 
<http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>



Re: 7.4 Wishlist

From
Rod Taylor
Date:
> The one good reason for making it possible to use raw partitions is to
> make it possible to use the PostgreSQL engine as a filesystem!  :-)

Hmm..  Something just doesn't seem right about that thought ;)

CREATE DATABASE filesystem;
\c filesystem
CREATE EXPORT /mnt AS NFS;
\q

mount_nfs -o port=5432 localhost:/mnt /mnt
ls /mnt/pg_class



--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Re: 7.4 Wishlist

From
Peter Eisentraut
Date:
Joe Conway writes:

> That is one thing I'd like to take a look at. I think the problem is that
> certain byte-sequence/multibyte-encoding combinations are illegal, so it's not
> as simple an issue as it might first appear.

The bytea type really shouldn't come even close to having to care about
this.

Actually, if you want to improve the ugly bytea literal syntax, implement
the standard BLOB type.

-- 
Peter Eisentraut   peter_e@gmx.net



Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

From
Justin Clift
Date:
Marc G. Fournier wrote:
> On Tue, 3 Dec 2002, Justin Clift wrote:
> 
> 
>>>Excellent. Are there any other people involved in PostgreSQL and
>>>universities or educational institutions? If so we could put something
>>>together about experiences for the advocacy Web site.
>>
>>Is this the kind of thing that the Techdocs Guides area would be good
>>for?  (http://techdocs.postgresql.org/guides)
> 
> 
> Seems that any discussions about "experiences" belongs on Advocacy, no?

Good point.

Have put a *really basic* Zwiki framework at:

http://advocacy.postgresql.org/documents

It's the same collaborative software used for the PostgreSQL Guides 
section, but without the look+feel added.

If you want to start editing stuff right away, then feel free to use it.  If you'd like it to look better first though,
thenit'll be a few days...
 

:-)

Regards and best wishes,

Justin Clift


-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi



Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

From
Justin Clift
Date:
Marc G. Fournier wrote:
<snip>
> Actually, there are lines, Justin just occasionally appears to 'blur' them
> until I get a chance to refresh them ... eh Justin?:)

[innocent whistle]

+ Justin



Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

From
Manuel Cabido
Date:
Hi:
  We at the Department of Information Technology of the Mindanao State
University-Iligan Institute of Technology (MSU-IIT) in Iligan City,
Philippines had been using PostgreSQL since 1998 in teaching courses in
Databases, SQL, and as a support tool in teaching Software Engineering and
Web Application Development. We are even utilizing it as our database
backend in all applications we developed in-house like Payroll, Student
Enrollment, Financial Applications, etc.  At the rate PostgreSQL is
performing, we are not for any reason tempted to migrate to another
database for the next ten years.
  THANKS TO THE POSTGRESQL DEVELOPMENT TEAM. 
  We Salute YOU!


PROF. MANUEL C. CABIDO
Chair
Department of Information Technology
MSU-IIT
Iligan City 9200
Philippines



Broadcast replication (Was Re: 7.4 Wishlist)

From
"Al Sutton"
Date:
----- Original Message -----
From: "Kevin Brown" <kevin@sysexperts.com>
To: <pgsql-hackers@postgresql.org>
Sent: Tuesday, December 03, 2002 8:49 PM
Subject: [mail] Re: [HACKERS] 7.4 Wishlist


> Al Sutton wrote:
> > Point to Point and Broadcast replication
> > ----------------------------------------
> > With point to point you specify multiple endpoints, with broadcast you
can
> > specify a subnet address and the updates are broadcast over that subnet.
> >
> > The difference being that point to point works well for cross network
> > replication, or where you have a few replicants. I have multiple
database
> > servers which could have a deadicated class C network that they are all
on,
> > by broadcasting updates you can cutdown the amount of traffic on that
net by
> > a factor of n minus 1 (where n is the number of servers involved).
>
> Yech.  Now you can't use TCP anymore, so the underlying replication
> code has to handle all the issues that TCP deals with transparently,
> like error checking, retransmits, data windows, etc.  I don't think
> it's wise to assume that your transport layer is 100% reliable.
>
> Further, this doesn't even address the problem of bringing up a leaf
> server that's been down a while.  It can be significantly out of date
> relative to the other servers on the subnet.
>
> I suspect you'll be better off implementing a replication protocol
> that has the leaf nodes keeping each other up to date, to minimize the
> traffic coming from the next level up.  Then you can use TCP for the
> connections but minimize the traffic generated by any given node.
>

I wasn't saying that ALL replication traffic must be broadcast, if a
specific server needs a refresh when it comes then point to point is fine
because only one machine needs the data, and thus broadcasting it to all
would load machines with data they didn't need.

The aim of using broadcast is to cut down the ongoing traffic, say, for
example, I have a cluster of ten database servers I can connect them onto a
dedicated LAN shared only by database servers and I would see 10% of the
traffic I would get if I were using point to point (this is assuming that
the addition of error checking, retransmits, etc. to the broadcast protocol
adds a similiar overhead per packet as TCP point to point).

If others wish to know more about this I can prepare an overview for how I
see it working.

[Other points snipped]




Re: 7.4 Wishlist

From
Kevin Brown
Date:
Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> Hi guys,
> 
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?

Well, on top of the oft-requested replication support and savepoint
support, I'd like to see UPDATE, er, updated to be able to make use of
cursors.

I'd also like to see (if this is even possible) a transaction
isolation mode that would make it possible for multiple concurrent
updates to the same row to happen without blocking each other (I
imagine one way to make this possible would be for the last
transaction to commit to be the one that "wins".  Each transaction
that commits gets its updates written so that other transactions that
begin after they commit will see them, of course).  Neither "read
committed" nor "serialized" modes offer this.  Don't know if it's
possible, but it would be nice (such that a transaction sees the
database as if it has it all to itself and doesn't block on
updates)...


- Kevin


Re: 7.4 Wishlist

From
Bruce Momjian
Date:
Kevin Brown wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> > Hi guys,
> > 
> > Just out of interest, if someone was going to pay you to hack on Postgres
> > for 6 months, what would you like to code for 7.4?
> 
> Well, on top of the oft-requested replication support and savepoint
> support, I'd like to see UPDATE, er, updated to be able to make use of
> cursors.

I think this could be easily done by using the tid of the cursor row for
the update, assuming there is a clear tid for the SELECT.  Jan has
talked about doing that.

> I'd also like to see (if this is even possible) a transaction
> isolation mode that would make it possible for multiple concurrent
> updates to the same row to happen without blocking each other (I
> imagine one way to make this possible would be for the last
> transaction to commit to be the one that "wins".  Each transaction
> that commits gets its updates written so that other transactions that
> begin after they commit will see them, of course).  Neither "read
> committed" nor "serialized" modes offer this.  Don't know if it's
> possible, but it would be nice (such that a transaction sees the
> database as if it has it all to itself and doesn't block on
> updates)...

How would you do the update if you don't know of the transaction commits
or aborts?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: 7.4 Wishlist

From
Kevin Brown
Date:
Bruce Momjian wrote:
> Kevin Brown wrote:
> > I'd also like to see (if this is even possible) a transaction
> > isolation mode that would make it possible for multiple concurrent
> > updates to the same row to happen without blocking each other (I
> > imagine one way to make this possible would be for the last
> > transaction to commit to be the one that "wins".  Each transaction
> > that commits gets its updates written so that other transactions that
> > begin after they commit will see them, of course).  Neither "read
> > committed" nor "serialized" modes offer this.  Don't know if it's
> > possible, but it would be nice (such that a transaction sees the
> > database as if it has it all to itself and doesn't block on
> > updates)...
> 
> How would you do the update if you don't know of the transaction commits
> or aborts?

Maybe I should explain what I'm thinking a little further.

What I'm proposing is a transaction isolation model where each
transaction thinks that it's the only one making changes to the
database.  That assumption obviously fails *outside* of a transaction,
but inside a transaction it should be possible to maintain the
illusion.

We already get this with serialized transaction isolation mode, with
one caveat: when an update conflicts with that of another transaction
that committed while the transaction of interest is in progress, the
transaction of interest gets rolled back immediately with a
serialization error.

What I'm proposing is to extend the illusion to updates.  A
transaction running in this new isolation mode (call it "full
isolation" or something) never sees updates that other committed
transactions perform, whether they would conflict or not.  The view
the running transaction sees of the database is a snapshot in time, as
it were, plus any updates the transaction itself has made.

Now, there are a couple of approaches we can take at transaction
commit that I can think of right now:

1.  If there were any serialization errors, abort the transaction at   commit time.  This allows transactions to safely
makechanges to their   local view of the database without compromising serialization.  This   probably wouldn't yield
anybenefits over the serializable isolation   level except that it would make it possible to perform experiments   on a
databasethat currently can't be performed (since serializable   isolation aborts the transaction at the first
serialization  error).  Applications would be easier to write since there would   be only one point during a
transactionthat the application would   have to check for unexpected errors: the commit.
 

2.  We commit the changed rows.  Updates only happen to rows that   exist at commit time.  Rows which the transaction
deletedand which   still exist at commit time are deleted.  Referential integrity   rules are enforced, of course, so
it'spossible for the database to   retain some of its sanity even in the face of this model.  But the   overall state
ofthe database will probably be unpredictable (in   this scenario, the last concurrent transaction to commit "wins",
moreor less).
 

3.  We do something more sophisticated than 1 or 2.  Perhaps something   analogous to the branch merge functions that
CVSand other concurrent   version control systems can perform, where multiple branches are   finally merged into a
singleunified source snapshot.  I have no idea   how this would work for real, or if it's even possible (I believe
CVSrequires manual intervention to resolve branch conflicts during   a merge, an option that would obviously not be
availableto us).
 

How useful would it be?  Beats me.  Like I said, you could perform
some "what if" games with a database this way that you currently
can't, but I don't know how useful that would be.  On thinking about
it a bit, it seems option 1 would be the most useful and perhaps the
most sensible.


Of course, perhaps the whole thing is just another stupid idea...




Re: 7.4 Wishlist

From
Kevin Brown
Date:
Tom Lane wrote:
> "Magnus Naeslund(f)" <mag@fbab.net> writes:
> > Mysql is planning on making this work:
> >  SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id.
> 
> We're supposed to spend our time emulating nonstandard features that
> don't even exist yet?  I think I have better things to do ...

MySQL doesn't have it, but I believe MS SQL does (and thus Sybase
probably does as well).

I agree with others that variables would be quite handy, especially if
they persist between statements (and they might be even handier if
they persist between transactions).

That's not to say that you don't have better things to work on,
though.  :-)

-- 
Kevin Brown                          kevin@sysexperts.com
This is your .signature virus: < begin 644 .signature (9V]T8VAA(0K0z end >    This is your .signature virus on drugs:
<>       Any questions?
 


Re: 7.4 Wishlist

From
Bruce Momjian
Date:
Kevin Brown wrote:
> How useful would it be?  Beats me.  Like I said, you could perform
> some "what if" games with a database this way that you currently
> can't, but I don't know how useful that would be.  On thinking about
> it a bit, it seems option 1 would be the most useful and perhaps the
> most sensible.
> 
> 
> Of course, perhaps the whole thing is just another stupid idea...

We would need to have some people who want this first.  We don't add
stuff of questionable value because then the feature set becomes
confusing to end users.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: 7.4 Wishlist

From
"Stephen L."
Date:
Hi, if I may add to the wishlist for 7.4 in order of importance. Some items
may have been mentioned or disputed already but I think they are quite
important:

1. Avoid needing REINDEX after large insert/deletes or make REINDEX not use
exclusive lock on table.
2. Automate VACUUM in background and make database more
interactive/responsive during long VACUUMs
3. Replication
4. Point-in-time recovery
5. Maintain automatic clustering (CLUSTER) even after subsequent
insert/updates.
6. Compression between client/server interface like in MySQL

Thanks,

Stephen
jleelim(at)hotmail.com




Re: 7.4 Wishlist

From
Greg Copeland
Date:
On Tue, 2002-12-10 at 09:36, Stephen L. wrote:
> 6. Compression between client/server interface like in MySQL
> 

Mammoth is supposed to be donating their compression efforts back to
this project, or so I've been told.  I'm not exactly sure of their
time-line as I've slept since my last conversation with them.  The
initial feedback that I've gotten back from them on this subject is that
the compression has been working wonderfully for them with excellent
results.  IIRC, in their last official release, they announced their
compression implementation.  So, I'd think that it would be available
for 7.4 of 7.5 time frame.


-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting



Re: [mail] Re: 7.4 Wishlist

From
"Al Sutton"
Date:
Would it be possible to make compression an optional thing, with the default
being off?

I'm in a position that many others may be in where the link between my app
server and my database server isn't the bottleneck, and thus any time spent
by the CPU performing compression and decompression tasks is CPU time that
is in effect wasted.

If a database is handling numerous small queries/updates and the
request/response packets are compressed individually, then the overhead of
compression and decompression may result in slower performance compared to
leaving the request/response packets uncompressed.

Al.

----- Original Message -----
From: "Greg Copeland" <greg@CopelandConsulting.Net>
To: "Stephen L." <jleelim@hotmail.com>
Cc: "PostgresSQL Hackers Mailing List" <pgsql-hackers@postgresql.org>
Sent: Tuesday, December 10, 2002 4:56 PM
Subject: [mail] Re: [HACKERS] 7.4 Wishlist


> On Tue, 2002-12-10 at 09:36, Stephen L. wrote:
> > 6. Compression between client/server interface like in MySQL
> >
>
> Mammoth is supposed to be donating their compression efforts back to
> this project, or so I've been told.  I'm not exactly sure of their
> time-line as I've slept since my last conversation with them.  The
> initial feedback that I've gotten back from them on this subject is that
> the compression has been working wonderfully for them with excellent
> results.  IIRC, in their last official release, they announced their
> compression implementation.  So, I'd think that it would be available
> for 7.4 of 7.5 time frame.
>
>
> --
> Greg Copeland <greg@copelandconsulting.net>
> Copeland Computer Consulting
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>




Re: [mail] Re: 7.4 Wishlist

From
Greg Copeland
Date:
On Tue, 2002-12-10 at 11:25, Al Sutton wrote:
> Would it be possible to make compression an optional thing, with the default
> being off?
> 

I'm not sure.  You'd have to ask Command Prompt (Mammoth) or wait to see
what appears.  What I originally had envisioned was a per database and
user permission model which would better control use.  Since compression
can be rather costly for some use cases, I also envisioned it being
negotiated where only the user/database combo with permission would be
able to turn it on.  I do recall that compression negotiation is part of
the Mammoth implementation but I don't know if it's a simple capability
negotiation or part of a larger scheme.

The reason I originally imagined a user/database type approach is
because I would think only a subset of a typical installation would be
needing compression.  As such, this would help prevent users from
arbitrarily chewing up database CPU compressing data because:o datasets are uncompressable or poorly compresseso
environmentcpu is at a premiumo is in a bandwidth rich environment
 


> I'm in a position that many others may be in where the link between my app
> server and my database server isn't the bottleneck, and thus any time spent
> by the CPU performing compression and decompression tasks is CPU time that
> is in effect wasted.

Agreed.  This is why I'd *guess* that Mammoth's implementation does not
force compression.

> 
> If a database is handling numerous small queries/updates and the
> request/response packets are compressed individually, then the overhead of
> compression and decompression may result in slower performance compared to
> leaving the request/response packets uncompressed.

Again, this is where I'm gray on their exact implementation.  It's
possible they implemented a compressed stream even though I'm hoping
they implemented a per packet compression scheme (because adaptive
compression becomes much more capable and powerful; in both
algorithmically and logistical use).  An example of this would be to
avoid any compression on trivially sized result sets. Again, this is
another area where I can imagine some tunable parameters.

Just to be on the safe side, I'm cc'ing Josh Drake at Command Prompt
(Mammoth) to see what they can offer up on it.  Hope you guys don't
mind.


Greg



> ----- Original Message -----
> From: "Greg Copeland" <greg@CopelandConsulting.Net>
> To: "Stephen L." <jleelim@hotmail.com>
> Cc: "PostgresSQL Hackers Mailing List" <pgsql-hackers@postgresql.org>
> Sent: Tuesday, December 10, 2002 4:56 PM
> Subject: [mail] Re: [HACKERS] 7.4 Wishlist
> 
> 
> > On Tue, 2002-12-10 at 09:36, Stephen L. wrote:
> > > 6. Compression between client/server interface like in MySQL
> > >
> >
> > Mammoth is supposed to be donating their compression efforts back to
> > this project, or so I've been told.  I'm not exactly sure of their
> > time-line as I've slept since my last conversation with them.  The
> > initial feedback that I've gotten back from them on this subject is that
> > the compression has been working wonderfully for them with excellent
> > results.  IIRC, in their last official release, they announced their
> > compression implementation.  So, I'd think that it would be available
> > for 7.4 of 7.5 time frame.
> >
> >
> > --
> > Greg Copeland <greg@copelandconsulting.net>
> > Copeland Computer Consulting
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting



Re: [mail] Re: 7.4 Wishlist

From
Bruce Momjian
Date:
Greg Copeland wrote:
> On Tue, 2002-12-10 at 11:25, Al Sutton wrote:
> > Would it be possible to make compression an optional thing, with the default
> > being off?
> > 
> 
> I'm not sure.  You'd have to ask Command Prompt (Mammoth) or wait to see
> what appears.  What I originally had envisioned was a per database and
> user permission model which would better control use.  Since compression
> can be rather costly for some use cases, I also envisioned it being
> negotiated where only the user/database combo with permission would be
> able to turn it on.  I do recall that compression negotiation is part of
> the Mammoth implementation but I don't know if it's a simple capability
> negotiation or part of a larger scheme.

I haven't heard anything about them contributing it.  Doesn't mean it
will not happen, just that I haven't heard it.

I am not excited about per-db/user compression because of the added
complexity of setting it up, and even set up, I can see cases where some
queries would want it, and others not.  I can see using GUC to control
this.  If you enable it and the client doesn't support it, it is a
no-op.  We have per-db and per-user settings, so GUC would allow such
control if you wish.

Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO,
meaning it would determine if there was value in the compression and do
it only when it would help.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: [mail] Re: 7.4 Wishlist

From
Greg Copeland
Date:
On Tue, 2002-12-10 at 13:38, Bruce Momjian wrote:

> I haven't heard anything about them contributing it.  Doesn't mean it
> will not happen, just that I haven't heard it.
> 

This was in non-mailing list emails that I was told this by Joshua Drake
at Command Prompt.  Of course, that doesn't have to mean it will be
donated for sure but nonetheless, I was told it will be.

Here's a quote from one of the emails.  I don't think I'll be too far
out of line posting this.  On August 9, 2002, Joshua Drake said, "One we
plan on releasing this code to the developers after 7.3 comes out. We
want to be good members of the community but we have to keep a slight
commercial edge (wait to you see what we are going to do to vacuum)."

Obviously, I don't think that was official speak, so I'm not holding
them to the fire, nonetheless, that's what was said.  Additional follow
ups did seem to imply that they were very serious about this and REALLY
want to play nice as good shared source citizens.


> I am not excited about per-db/user compression because of the added
> complexity of setting it up, and even set up, I can see cases where some
> queries would want it, and others not.  I can see using GUC to control
> this.  If you enable it and the client doesn't support it, it is a
> no-op.  We have per-db and per-user settings, so GUC would allow such
> control if you wish.
> 

I never thought beyond the need for what form an actual implementation
of this aspect would look like.  The reason for such a concept would be
to simply limit the number of users that can be granted compression.  If
you have a large user base all using compression or even a small user
base where very large result sets are common, I can imagine your
database server becoming CPU bound.  The database/user thinking was an
effort to allow the DBA to better manage the CPU effect.

> Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO,
> meaning it would determine if there was value in the compression and do
> it only when it would help.

Yes, that makes sense and was something I had originally envisioned. 
Simply stated, some installations may never want compression while
others may want it for every connection.  Beyond that, I believe there
needs to be something of a happy medium where a DBA can better control
who and what is taking his CPU away (e.g. only that one remote location
being fed via ISDN).  If GUC can fully satisfy, I certainly won't argue
against it.


-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting



Re: [mail] Re: 7.4 Wishlist

From
Kyle
Date:
Without getting into too many details, why not send toast data to
non-local clients?  Seems that would be the big win.  The data is
already compressed, so the server wouldn't pay cpu time to recompress
anything.  And since toast data is relatively large anyway, it's the
stuff you'd want to compress before putting it on the wire anyway.

If this is remotely possible let me know, I might be interested in
taking a look at it.

-Kyle

Bruce Momjian wrote:
> 
> I am not excited about per-db/user compression because of the added
> complexity of setting it up, and even set up, I can see cases where some
> queries would want it, and others not.  I can see using GUC to control
> this.  If you enable it and the client doesn't support it, it is a
> no-op.  We have per-db and per-user settings, so GUC would allow such
> control if you wish.
> 
> Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO,
> meaning it would determine if there was value in the compression and do
> it only when it would help.


Re: [mail] Re: 7.4 Wishlist

From
Greg Copeland
Date:
This has been brought up a couple of times now.  Feel free to search the
old archives for more information.  IIRC, it would of made the
implementation more problematic, or so I think it was said.

When I originally brought the topic (compression) up, it was not well
received.  As such, it may of been thought that additional effort on
such an implementation would not be worth the return on a feature which
most seemingly didn't see any purpose in supporting in the first place. 
You need to keep in mind that many simply advocated using a compressing
ssh tunnel.

Seems views may of changed some since then so it may be worth
revisiting.  Admittedly, I have no idea what would be required to move
the toast data all the way through like that.  Any idea?  Implementing a
compression stream (which seems like what was done for Mammoth) or even
packet level compression were both something that I could comfortably
put my arms around in a timely manner.  Moving toast data around wasn't.


Greg


On Tue, 2002-12-10 at 18:45, Kyle wrote:
> Without getting into too many details, why not send toast data to
> non-local clients?  Seems that would be the big win.  The data is
> already compressed, so the server wouldn't pay cpu time to recompress
> anything.  And since toast data is relatively large anyway, it's the
> stuff you'd want to compress before putting it on the wire anyway.
> 
> If this is remotely possible let me know, I might be interested in
> taking a look at it.
> 
> -Kyle
> 
> Bruce Momjian wrote:
> > 
> > I am not excited about per-db/user compression because of the added
> > complexity of setting it up, and even set up, I can see cases where some
> > queries would want it, and others not.  I can see using GUC to control
> > this.  If you enable it and the client doesn't support it, it is a
> > no-op.  We have per-db and per-user settings, so GUC would allow such
> > control if you wish.
> > 
> > Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO,
> > meaning it would determine if there was value in the compression and do
> > it only when it would help.

-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting



Re: [mail] Re: 7.4 Wishlist

From
"Al Sutton"
Date:
I'd like to show/register interest.

I can see it being very useful when combined with replication for situations
where the replicatiant databases are geographically seperated (i.e. Disaster
Recover sites or systems maintaining replicants in order to reduce the
distance from user to app to database). The bandwidth cost savings from
compressing the replication information would be immensly useful.

Al.

----- Original Message -----
From: "Joshua D. Drake" <jd@commandprompt.com>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "Greg Copeland" <greg@CopelandConsulting.Net>; "Al Sutton"
<al@alsutton.com>; "Stephen L." <jleelim@hotmail.com>; "PostgresSQL Hackers
Mailing List" <pgsql-hackers@postgresql.org>
Sent: Tuesday, December 10, 2002 8:04 PM
Subject: Re: [mail] Re: [HACKERS] 7.4 Wishlist


> Hello,
>
>    We would probably be open to contributing it if there was interest.
> There wasn't interest initially.
>
> Sincerely,
>
> Joshua Drake
>
>
> Bruce Momjian wrote:
> > Greg Copeland wrote:
> >
> >>On Tue, 2002-12-10 at 11:25, Al Sutton wrote:
> >>
> >>>Would it be possible to make compression an optional thing, with the
default
> >>>being off?
> >>>
> >>
> >>I'm not sure.  You'd have to ask Command Prompt (Mammoth) or wait to see
> >>what appears.  What I originally had envisioned was a per database and
> >>user permission model which would better control use.  Since compression
> >>can be rather costly for some use cases, I also envisioned it being
> >>negotiated where only the user/database combo with permission would be
> >>able to turn it on.  I do recall that compression negotiation is part of
> >>the Mammoth implementation but I don't know if it's a simple capability
> >>negotiation or part of a larger scheme.
> >
> >
> > I haven't heard anything about them contributing it.  Doesn't mean it
> > will not happen, just that I haven't heard it.
> >
> > I am not excited about per-db/user compression because of the added
> > complexity of setting it up, and even set up, I can see cases where some
> > queries would want it, and others not.  I can see using GUC to control
> > this.  If you enable it and the client doesn't support it, it is a
> > no-op.  We have per-db and per-user settings, so GUC would allow such
> > control if you wish.
> >
> > Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO,
> > meaning it would determine if there was value in the compression and do
> > it only when it would help.
> >
>
> --
> <COMPANY>CommandPrompt - http://www.commandprompt.com </COMPANY>
> <CONTACT>       <PHONE>+1.503.222-2783</PHONE>          </CONTACT>
>
>