Priorities for 6.6

Tom Lane wrote:

I don't know what people have had in mind for 6.6, but I propose that
there ought to be three primary objectives for our next release:

1. Eliminate arbitrary restrictions on tuple size.

This is not primary for me -:)
Though, it's required by PL/pgSQL and so... I agreed that
this problem must be resolved in some way. Related TODO items:

* Allow compression of large fields or a compressed field type
* Allow large text type to use large objects(Peter)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
I like it very much, though I don't like that LO are stored
in separate files. This is known as "multi-representation" feature
in Illustra.

2. Eliminate arbitrary restrictions on query size (textual
length/complexity that is).

Yes, this is quite annoyning thing.

3. Cure within-statement memory leaks, so that processing large numbers
of tuples in one statement is reliable.

Quite significant!

All of these are fairly major projects, and it might be that we get
little or nothing else done if we take these on. But these are the
problems we've been hearing about over and over and over. I think
fixing these would do more to improve Postgres than almost any other
work we might do.

Comments? Does anyone have a different list of pet peeves? Is there
any chance of getting everyone to subscribe to a master plan like this?

No chance -:))

This is what I would like to see in 6.6:

1. Referential integrity.
2. Dirty reads (will be required by 1. if we'll decide to follow
the way proposed by Jan - using rules, - though there is another
way I'll talk about later; dirty reads are useful anyway).
3. Savepoints (they are my primary wish-to-implement thing).
4. elog(ERROR) must return error-codes, not just messages!
This is very important for non-interactive application...
in conjuction with 3. -:)

Vadim

On Fri, 4 Jun 1999, Vadim Mikheev wrote:

* Allow compression of large fields or a compressed field type

This one looks cool...

All of these are fairly major projects, and it might be that we get
little or nothing else done if we take these on. But these are the
problems we've been hearing about over and over and over. I think
fixing these would do more to improve Postgres than almost any other
work we might do.

Comments? Does anyone have a different list of pet peeves? Is there
any chance of getting everyone to subscribe to a master plan like this?

No chance -:))

have to agree with Vadim here...the point that has *always* been stressed
here is that if something is important to you, fix it. Don't expect
anyone else to fall into some sort of "party line" or scheduale, cause
then ppl lose the enjoyment in what they are doing *shrug*

for instance, out of the three things you listed, the only one that I'd
consider an issue is the third, as I've never hit the first two
limitations ...*shrug*

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

This is what I would like to see in 6.6:

1. Referential integrity.

Bingo. Item #1. Period. End of story. Everything else pales in
comparison. We just get too many requests for this, though I think it
an insignificant feature myself. Jan, I believe you have some ideas on
this. (Like an elephant, I never forget.)

4. elog(ERROR) must return error-codes, not just messages!
This is very important for non-interactive application...
in conjuction with 3. -:)

Added to TODO.

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

Jan Wieck writes (over in pgsql-sql):

* WE STILL NEED THE GENERAL TUPLE SPLIT CAPABILITY!!! *

I've been thinking about making this post for a while ... with 6.5
almost out the door, I guess now is a good time.

I don't know what people have had in mind for 6.6, but I propose that
there ought to be three primary objectives for our next release:

1. Eliminate arbitrary restrictions on tuple size.

2. Eliminate arbitrary restrictions on query size (textual
length/complexity that is).

3. Cure within-statement memory leaks, so that processing large numbers
of tuples in one statement is reliable.

I think the other hot item for 6.6 is outer joins.

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

Bruce Momjian wrote:

I think the other hot item for 6.6 is outer joins.

I would like to have 48 hours in day -:)

Vadim

Bruce Momjian wrote:

I think the other hot item for 6.6 is outer joins.

I would like to have 48 hours in day -:)

Vadim

You and I are off the hook. Jan volunteered for foreign keys, and
Thomas for outer joins. We can relax. :-)

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

Bruce Momjian wrote:

Bruce Momjian wrote:

I think the other hot item for 6.6 is outer joins.

I would like to have 48 hours in day -:)

Vadim

You and I are off the hook. Jan volunteered for foreign keys, and
Thomas for outer joins. We can relax. :-)

I volunteered for savepoints -:))

Vadim

Bruce Momjian wrote:

Bruce Momjian wrote:

I think the other hot item for 6.6 is outer joins.

I would like to have 48 hours in day -:)

Vadim

You and I are off the hook. Jan volunteered for foreign keys, and
Thomas for outer joins. We can relax. :-)

I volunteered for savepoints -:))

Oh.

Hey, I thought you were going to sleep?

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

Bruce Momjian wrote:

I think the other hot item for 6.6 is outer joins.

I would like to have 48 hours in day -:)

Vadim

You and I are off the hook. Jan volunteered for foreign keys, and
Thomas for outer joins. We can relax. :-)

I volunteered for savepoints -:))

Oh.

Hey, I thought you were going to sleep?

I just try to have at least 25 hours in day :)

Vadim

I volunteered for savepoints -:))

Oh.

Hey, I thought you were going to sleep?

I just try to have at least 25 hours in day :)

Just have some pelmeni and go to sleep.

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

Bruce Momjian wrote:

I volunteered for savepoints -:))

Oh.

Hey, I thought you were going to sleep?

I just try to have at least 25 hours in day :)

Just have some pelmeni and go to sleep.

-:)))

Vadim

Vadim Mikheev <vadim@krs.ru> writes:

Tom Lane wrote:

1. Eliminate arbitrary restrictions on tuple size.

This is not primary for me -:)

Fair enough; it's not something I need either. But I see complaints
about it constantly on the mailing lists; a lot of people do need it.

* Allow large text type to use large objects(Peter)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
I like it very much, though I don't like that LO are stored
in separate files.

But, but ... if we fixed the tuple-size problem then people could stop
using large objects at all, and instead just put their data into tuples.
I hate to see work going into improving LO support when we really ought
to be phasing out the whole feature --- it's got *so* many conceptual
and practical problems ...

any chance of getting everyone to subscribe to a master plan like this?

No chance -:))

Yeah, I know ;-). But I was hoping to line up enough people so that
these things have some chance of getting done. I doubt that any of
these projects can be implemented by just one or two people; they all
affect too much of the code. (For instance, eliminating query-size
restrictions will require looking at all of the interface libraries,
psql, pg_dump, and probably other apps, even though the fixes in
the backend should be somewhat localized.)

regards, tom lane

At 05:39 PM 6/3/99 -0400, Tom Lane wrote:

But, but ... if we fixed the tuple-size problem then people could stop
using large objects at all, and instead just put their data into tuples.
I hate to see work going into improving LO support when we really ought
to be phasing out the whole feature --- it's got *so* many conceptual
and practical problems ...

Making them go away would be a real blessing. Oracle folk
bitch about CLOBS and BLOBS and the like, too. They're a
pain.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, and other goodies at
http://donb.photo.net

Don Baccus wrote:

At 05:39 PM 6/3/99 -0400, Tom Lane wrote:

* Allow large text type to use large objects(Peter)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
I like it very much, though I don't like that LO are stored
in separate files. This is known as "multi-representation" feature
in Illustra.

But, but ... if we fixed the tuple-size problem then people could stop
using large objects at all, and instead just put their data into tuples.
I hate to see work going into improving LO support when we really ought
to be phasing out the whole feature --- it's got *so* many conceptual
and practical problems ...

Making them go away would be a real blessing. Oracle folk
bitch about CLOBS and BLOBS and the like, too. They're a
pain.

Note: I told about "multi-representation" feature, not just about
LO/CLOBS/BLOBS support. "Multi-representation" means that server
stores tuple fields sometime inside the main relation file,
sometime outside of it, but this is hidden from user and so
people "just put their data into tuples". I think that putting
big fields outside of main relation file is very good thing.
BTW, this approach also allows what you are proposing - why not
put not too big field (~ 8K or so) to another block of main file?
BTW, I don't like using LOs as external storage.

Implementation seems easy:

struct varlena
{
int32 vl_len;
char vl_dat[1];
};

1. make vl_len uint32;
2. use vl_len & 0x80000000 as flag that underlying data is
in another place;
3. put oid of external "relation" (where data is stored),
blocknumber and item position (something else?) to vl_dat.
...
...
...

Vadim

Implementation seems easy:

struct varlena
{
int32 vl_len;
char vl_dat[1];
};

1. make vl_len uint32;
2. use vl_len & 0x80000000 as flag that underlying data is
in another place;
3. put oid of external "relation" (where data is stored),
blocknumber and item position (something else?) to vl_dat.
...

Yes, it would be very nice to have this.

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

At 10:56 AM 6/4/99 +0800, Vadim Mikheev wrote:

Note: I told about "multi-representation" feature, not just about
LO/CLOBS/BLOBS support. "Multi-representation" means that server
stores tuple fields sometime inside the main relation file,
sometime outside of it, but this is hidden from user and so
people "just put their data into tuples". I think that putting
big fields outside of main relation file is very good thing.

Yes, it is, though "big" is relative (as computers grow). The
key is to hide the details of where things are stored from the
user, so the user doesn't really have to know what is "big"
(today) vs. "small" (tomorrow or today, for that matter). I
don't think it's so much the efficiency hit of having big
items stored outside the main relation file, as the need for
the user to know what's "big" and what's "small", that's the
problem.

I mean, my background is as a compiler writer for high-level
languages...call me a 1970's idealist if you will, but I
really think such things should be hidden from the user.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, and other goodies at
http://donb.photo.net

At 10:56 AM 6/4/99 +0800, Vadim Mikheev wrote:

Note: I told about "multi-representation" feature, not just about
LO/CLOBS/BLOBS support. "Multi-representation" means that server
stores tuple fields sometime inside the main relation file,
sometime outside of it, but this is hidden from user and so
people "just put their data into tuples".

OK, in my first response I didn't pick up on your generalization,
but I did respond with a generalization that implementation
details should be hidden from the user.

Which is what you're saying.

As a compiler writer, this is more or less what I devoted my
life to 20 years ago...of course, reasonable efficiency is
a pre-condition if you're going to hide details from the
user...

I'll back off a bit, though, and say that a lot of DB users
really don't need an enterprise engine like Oracle (i.e.
something that requires a suite of $100K/yr DBAs :)

There's a niche for a solid reliable, rich feature set,
reasonably well-performing db out there, and this niche
is ever-growing with the web.

With $500 web servers sitting on $29.95/mo DSL lines,
as does mine (http://donb.photo.net/tweeterdom), who
wants to pay $6K to Oracle?

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, and other goodies at
http://donb.photo.net

Dear All,

It seems to me that there are a bunch of related issues that probably need to be tied together (and forgotten about?):

1. A 'nice' user interface for blobs
2. Text fields stored as blobs
3. Naming issues for 'system' tables etc.
4. pg_dump support for blobs and other 'internal' structures.
5. Blob storage in files Vs. a 'nicer' storage medium.
6. The tuple-size problem(?)

Points (1) & (2) are really the same thing; if you provide a nice interface to blobs: "select len(blob_field) from ...." and "select blob_field from ...", then any discussion of the messiness associated with blobs will go away. Personally, I would hate to lose the ability to store a blob's data using a series of 'lo_write' calls: one system I work on (not in PG) has blob data as large as 24MB which makes blob_write functionality essential.

Points (3) & (4) recognize that there are a number issues floating around that relate to the basic inappropriateness of using SQL to reload the data structures of an existing database. I have only used a few commercial DBs, but the ones I have used uniformly have a 'dump' that produces data files in it's own format. There is no question that having pg_dump produce a schema and/or INSERT statements is nice, but a new option needs to be added to allow raw exports, and a new pg_load utility needs to be written. Cross-version compatibility between export formats must also be maintained (obviously).

Point (5) recognizes that storing 'large' data in the same area that a row is stored in will remove any benefits of clustering, so a method of handling blob data needs to be found, irrespective of whether PG still supports blobs as such. I don't know how PG handles large text fields - some commercial systems allow the user to 'map' specific fields to separate data files. The current system (storing blobs in files) is fine except in so far as it *looks* messy, produces *huge* directories, and is slow for many small blobs (file open/read/close per row).

I don't know anything about the 'tuple-size' problem (point 6), but it may also relate to a solution for storing blob-data (or specific columns) in alternate locations.

I hope this is not all static...

Philip Warner.

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

Tom Lane wrote:

I don't know what people have had in mind for 6.6, but I propose that
there ought to be three primary objectives for our next release:

1. Eliminate arbitrary restrictions on tuple size.

2. Eliminate arbitrary restrictions on query size (textual
length/complexity that is).

3. Cure within-statement memory leaks, so that processing large numbers
of tuples in one statement is reliable.

I would add a few that I think would be important:

A. Add outer joins

B. Add the possibility to prepare statements and then execute them
with a set of arguments. This already exists in SPI but for many
C/S apps it would be desirable to have this in the fe/be protocol
as well

C. Look over the protocol and unify the _binary_ representations of
datatypes on wire. in fact each type already has two sets of
in/out conversion functions in its definition tuple, one for disk and
another for net, it's only that until now they are the same for
all types and thus probably used wromg in some parts of code.

D. After B. and C., add a possibility to insert binary data
in "(small)binary" field without relying on LOs or expensive
(4x the size) quoting. Allow any characters in said binary field

E. to make 2. and B., C, D. possible, some more fundamental changes in
fe/be-protocol may be needed. There seems to be some effort for a new
fe/be communications mechanism using CORBA.
But my proposal would be to adopt the X11 protocol which is quite
light
but still very clean, well understood and which can transfer
arbitrary
data in an efficient way.
There are even "low bandwidth" variants of it for using over
really slow links. Also some kinds of "out of band" provisions exist,
that are used by window managers.
It should also be trivial to adapt crypto wrappers/proxies (such as
the
one in ssh)
The protocol is described in a document available from
http://www.x.org

F. As a lousy alternative to 1. fix the LO storage. Currently _all_ of
the LO files are kept in the same directory as the tables and
indexes.
this can bog down the whole database quite fast if one lots of LOs
and
a file system that does linear scans on open (like ext2).
A sheme where LOs are kept in subdirectories based on the hex
representation of their oids would avoid that (so LO with OID
0x12345678
would be stored in $PG_DATA/DBNAME/LO/12/34/56/78.lo or maybe
reversed
$PG_DATA/DBNAME/LO/78/56/34/12.lo to distribute them more evenly in
"buckets"

All of these are fairly major projects, and it might be that we get
little or nothing else done if we take these on.

But then, the other things to do _are_ little compared to these ;)

But these are the problems we've been hearing about over and over and
over.

The LO thing (and lack of decent full-text indexing) is what has kept me
using hybrid solutions where I keep the LO data and home-grown full-text
indexes in file system outside of the database.

I think fixing these would do more to improve Postgres than
almost any other work we might do.

Amen!

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