Thread

Re: 7.2 items

Martin Marques <martin@bugs.unl.edu.ar> — 2001-05-10T14:52:03Z

I left the ones that look importante for my needs.

On Jue 10 May 2001 20:20, Bruce Momjian wrote:
> Here is a small list of big TODO items.  I was wondering which ones
> people were thinking about for 7.2?
>
> ---------------------------------------------------------------------------
>
> * Point-in-time data recovery using backup and write-ahead log
> * Allow row re-use without vacuum (Vadim)

Yhis one is not very important for my, but I guess there are people out there 
that have heavy updates on there DB and would be delighted with this.

> * Add the concept of dataspaces/tablespaces [tablespaces]

What would this be?

What I'm about to write has nothing (at least I think) to do with this, but I 
would like the database directoies to have the name of the databases, as it 
was before, if it's posible.
It makes it easier to find out with database is growing from the command line.

> * Allow better control over user privileges [privileges]

If this is related with the views and privileges, I'm on this one!

> * Allow elog() to return error codes, module name, file name, line
>   number, not just messages [elog]
> * Make binary/file in/out interface for TOAST columns
> * Large object interface improvements
> * Add ALTER TABLE DROP COLUMN feature [drop]
> * Add ALTER TABLE ... DROP CONSTRAINT
> * Automatically drop constraints/functions when object is dropped

Saludos... :-)

-- 
El mejor sistema operativo es aquel que te da de comer.
Cuida tu dieta.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

7.2 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-05-10T17:20:59Z

Here is a small list of big TODO items.  I was wondering which ones
people were thinking about for 7.2?

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

* Add replication of distributed databases [replication]
	o automatic fallover
	o load balancing
	o master/slave replication
	o multi-master replication
	o partition data across servers
	o sample implementation in contrib/rserv
	o queries across databases or servers (two-phase commit)
* Point-in-time data recovery using backup and write-ahead log
* Allow row re-use without vacuum (Vadim)
* Add the concept of dataspaces/tablespaces [tablespaces]
* Allow better control over user privileges [privileges]
* Allow elog() to return error codes, module name, file name, line
  number, not just messages [elog]
* Allow international error message support and add error codes [elog]
* Make binary/file in/out interface for TOAST columns
* Large object interface improvements
* Allow inherited tables to inherit index, UNIQUE constraint, and primary key
  [inheritance]
* Add ALTER TABLE DROP COLUMN feature [drop]
* Add ALTER TABLE ... DROP CONSTRAINT
* Automatically drop constraints/functions when object is dropped

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

Re: 7.2 items

Andrew McMillan <andrew@catalyst.net.nz> — 2001-05-10T20:27:13Z

Bruce Momjian wrote:
> 
> * Add replication of distributed databases [replication]
>         o automatic fallover

Shouldn't that be 'failover'?  I don't know if I want automatic 'fallover'!

:-)
					Andrew.
-- 
_____________________________________________________________________
           Andrew McMillan, e-mail: Andrew@catalyst.net.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64(21)635-694, Fax: +64(4)499-5596, Office: +64(4)499-2267xtn709

Re: 7.2 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-05-10T20:29:18Z

> Bruce Momjian wrote:
> > 
> > * Add replication of distributed databases [replication]
> >         o automatic fallover
> 
> Shouldn't that be 'failover'?  I don't know if I want automatic 'fallover'!

Just one letter, but a huge difference.  :-)

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

Re: 7.2 items

john@mwk.co.nz — 2001-05-10T22:41:57Z



> Here is a small list of big TODO items.  I was wondering which ones
> people were thinking about for 7.2?

The need for stored procedures that return a record set.
This is required to migrate from MSSQL, Interbase and others.
This is a commonly requested item.

Nested Transactions. This allows the logging of the execution of a failed
SQL
statement even if the rest of the transaction is rolled back.

Statement Level Triggers. Useful but not critically important.

Full text indexing.

Pre parsed queries with variable substitutions.

Regards

Re: 7.2 items

Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-10T22:54:27Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Here is a small list of big TODO items.  I was wondering which ones
> people were thinking about for 7.2?

Peter E. had implied that he wanted to tackle the elog issues for 7.2,
but I'm not sure if he's committed to it or not.

I am wanting to see SQL schemas happen, and it's possible that
tablespaces should be dealt with in combination with that.

Other than that, I'm mostly thinking about performance improvements
for 7.2, not features ... as far as my personal plans go, that is.

			regards, tom lane

Re: 7.2 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-05-10T23:54:25Z

> > * Point-in-time data recovery using backup and write-ahead
> log > * Allow row re-use without vacuum (Vadim)
> 
> Yhis one is not very important for my, but I guess there are
> people out there that have heavy updates on there DB and would
> be delighted with this.

Yes, this important especially for databases that have to be up 24
hours a day.

> 
> > * Add the concept of dataspaces/tablespaces [tablespaces]
> 
> What would this be?
> 
> What I'm about to write has nothing (at least I think) to do
> with this, but I would like the database directoies to have the
> name of the databases, as it was before, if it's posible.  It
> makes it easier to find out with database is growing from the
> command line.

We have a /contrib utility called oid2name for that.

> > * Allow better control over user privileges [privileges]
> 
> If this is related with the views and privileges, I'm on this
> one!

Not sure what the problem is there.  We already implement privileges on
views that are separate from the base tables.

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

Re: 7.2 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-05-10T23:58:04Z

> Full text indexing.
> 

This one is already done using GIST.  The GIST improvements are in 7.1,
and I assume full text indexing will be more fully integrated into
PostgreSQL in 7.2. 

The PostgreSQL web search engine is using it now.  Oleg and team did the
work.

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

Re: 7.2 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-05-10T23:58:32Z

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Here is a small list of big TODO items.  I was wondering which ones
> > people were thinking about for 7.2?
> 
> Peter E. had implied that he wanted to tackle the elog issues for 7.2,
> but I'm not sure if he's committed to it or not.

I put Peter E on that one with a question mark.

> 
> I am wanting to see SQL schemas happen, and it's possible that
> tablespaces should be dealt with in combination with that.

Updated TODO.

> 
> Other than that, I'm mostly thinking about performance improvements
> for 7.2, not features ... as far as my personal plans go, that is.

Seems you already started.

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

Re: 7.2 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-05-11T00:01:33Z

> Other than that, I'm mostly thinking about performance improvements
> for 7.2, not features ... as far as my personal plans go, that is.

I saw a few juicy TODO items I will tackle, though people will
certainly be cleaning up after me.  :-)

I have reorganized the TODO list to make smaller groupings.

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

Re: 7.2 items

Alfred Perlstein <bright@wintelcom.net> — 2001-05-11T00:40:40Z

* Bruce Momjian <pgman@candle.pha.pa.us> [010510 17:02] wrote:
> > > * Point-in-time data recovery using backup and write-ahead
> > log > * Allow row re-use without vacuum (Vadim)
> > 
> > Yhis one is not very important for my, but I guess there are
> > people out there that have heavy updates on there DB and would
> > be delighted with this.
> 
> Yes, this important especially for databases that have to be up 24
> hours a day.

Sorry for jumping in here, but any ideas on the expected date
that will become available?

-- 
-Alfred Perlstein - [alfred@freebsd.org]
Instead of asking why a piece of software is using "1970s technology,"
start asking why software is ignoring 30 years of accumulated wisdom.

RE: 7.2 items

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2001-05-11T01:30:33Z

> * Add ALTER TABLE ... DROP CONSTRAINT

I am working on this function at the moment, hoping to add the dropping of
CHECK constraints.  However, it'll take me a while because I keep having to
look up all the functions being called to see what they do, etc.

What I'm thinking it that I'll try and at least get the structure all done
and even compiling then the patch will have to be reviewed. (I'm doing it to
stretch my programming muscles after working in PHP for so long!)

Chris

Re: 7.2 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-05-11T01:34:51Z

> > * Add ALTER TABLE ... DROP CONSTRAINT
> 
> I am working on this function at the moment, hoping to add the dropping of
> CHECK constraints.  However, it'll take me a while because I keep having to
> look up all the functions being called to see what they do, etc.
> 
> What I'm thinking it that I'll try and at least get the structure all done
> and even compiling then the patch will have to be reviewed. (I'm doing it to
> stretch my programming muscles after working in PHP for so long!)

Good idea.  Certain people are great at looking at a patch and telling
exactly how to improve it.

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

Re: 7.2 items

Lincoln Yeoh <lyeoh@pop.jaring.my> — 2001-05-11T03:52:00Z

At 01:20 PM 10-05-2001 -0400, Bruce Momjian wrote:
>* Allow row re-use without vacuum (Vadim)

Will this do away with the need for a lazy vacuum?

>> Full text indexing.
>> 
>
>This one is already done using GIST.  The GIST improvements are in 7.1,
>and I assume full text indexing will be more fully integrated into
>PostgreSQL in 7.2. 

I hope it will. What will the interface be like? 

Right now I still don't know how to do FTI in 7.1 using _postgresql_ built
in GIST :(. 

Any pointers to the relevant postgresql docs? 

Cheerio,
Link.

Re: 7.2 items

Martin Marques <martin@bugs.unl.edu.ar> — 2001-05-11T05:19:24Z

Quoting Bruce Momjian <pgman@candle.pha.pa.us>:

> > > * Point-in-time data recovery using backup and write-ahead
> > log > * Allow row re-use without vacuum (Vadim)
> > 
> > Yhis one is not very important for my, but I guess there are
> > people out there that have heavy updates on there DB and would
> > be delighted with this.
> 
> Yes, this important especially for databases that have to be up 24
> hours a day.

I always thought that VACUUM was (especially) for 2 main reasons there:
	- Clean de tuples marked for deletion.
	- Make the new statistics. (-z)

Lots of tuples get marked for deletion on UPDATE and DELETE, am I right?
> > 
> > > * Add the concept of dataspaces/tablespaces [tablespaces]
> > 
> > What would this be?
> > 
> > What I'm about to write has nothing (at least I think) to do
> > with this, but I would like the database directoies to have the
> > name of the databases, as it was before, if it's posible.  It
> > makes it easier to find out with database is growing from the
> > command line.
> 
> We have a /contrib utility called oid2name for that.

I'll check that. :-)

> > > * Allow better control over user privileges [privileges]
> > 
> > If this is related with the views and privileges, I'm on this
> > one!
> 
> Not sure what the problem is there.  We already implement privileges on
> views that are separate from the base tables.

I personally have not had any problems, but heard on the general list. Could
have been bad configuration, or wrong GRANTS. I didn't follow the thread so
closely.

Saludos... :-)

-- 
El mejor sistema operativo es aquel que te da de comer.
Cuida tu dieta.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Re: Re: 7.2 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-05-11T11:04:29Z

> At 01:20 PM 10-05-2001 -0400, Bruce Momjian wrote:
> >* Allow row re-use without vacuum (Vadim)
> 
> Will this do away with the need for a lazy vacuum?
> 
> >> Full text indexing.
> >> 
> >
> >This one is already done using GIST.  The GIST improvements are in 7.1,
> >and I assume full text indexing will be more fully integrated into
> >PostgreSQL in 7.2. 
> 
> I hope it will. What will the interface be like? 

Wish I knew.  :-)

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

Re: 7.2 items

Thomas O'Connell <tfo@monsterlabs.com> — 2001-05-11T15:25:41Z

Bruce Momjian wrote:

> Here is a small list of big TODO items.  I was wondering which ones
> people were thinking about for 7.2?
>
> * Allow inherited tables to inherit index, UNIQUE constraint, and primary key
>   [inheritance]

i was wondering if there was any thought still being given to Oliver 
Elphick's post from a while back that is still in TODO.detail 
[inheritance]: 
http://candle.pha.pa.us/mhonarc/todo.detail/inheritance/msg00010.html

i kind of feel as though the inheritance semantics for postgres at the 
moment are not fully fleshed out, and including further features without 
having a full plan for the semantics doesn't seem to advance the effort 
of making postgres a true Object-Relational DBMS.

for my part, as a user, i am excited that inheritance is available even 
in a limited fashion, but where i use it, i have basically had to invent 
my own semantics for referential integrity based on a suite of triggers. 
this issue is addressed in Oliver's post, but i was wondering if such 
issues were still a part of the development dialogue since Oliver's post 
was the last in TODO.detail [inheritance] and seemed to merit no 
response (or any that i could find in the mailing list archives).

-tfo

Re: 7.2 items

Ned Wolpert <ned.wolpert@knowledgenet.com> — 2001-05-11T15:36:49Z

> From: <john@mwk.co.nz>
> Date: Fri, 11 May 2001 10:41:57 +1200
> 
> > Here is a small list of big TODO items.  I was wondering which ones
> > people were thinking about for 7.2?
> 
> The need for stored procedures that return a record set.
> This is required to migrate from MSSQL, Interbase and others.
> This is a commonly requested item.

This would be very useful, as well as the "RETURNING" clause that is
supported elsewhere with inserts.

-- 
Virtually, 
Ned Wolpert <ned.wolpert@knowledgenet.com>

D08C2F45:  28E7 56CB 58AC C622 5A51  3C42 8B2B 2739 D08C 2F45

Re: 7.2 items

Peter Eisentraut <peter_e@gmx.net> — 2001-05-12T21:21:44Z

Tom Lane writes:

> Peter E. had implied that he wanted to tackle the elog issues for 7.2,
> but I'm not sure if he's committed to it or not.

Well...

* Automatically add filename, line, function name:  Easy to code, lots of
  labour.  Should be lumped in with some other large change.

* Error codes:  I think there are only a handful of key messages that
  users (programs) need to detect cleanly, mostly constraint violations.
  The rest are "the query you sent is wrong -- fix your application" and
  "something went really wrong -- manual repair needed"

  So maybe this could be a smallish change.

* Translation:  If we want to use gettext I can get started.  I don't
  think I'm interested in using any other interface.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: 7.2 items

Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-13T00:00:42Z

Peter Eisentraut <peter_e@gmx.net> writes:
> * Translation:  If we want to use gettext I can get started.  I don't
>   think I'm interested in using any other interface.

I have no objection to the gettext API, but I was and still am concerned
about depending on GNU gettext's code, because of license conflicts.
There is a BSD-license gettext clone project, but it doesn't look to be
very far along.

			regards, tom lane

Re: 7.2 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-05-13T00:54:27Z

> * Translation:  If we want to use gettext I can get started.  I don't
>   think I'm interested in using any other interface.
> 

License?

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

Re: Re: 7.2 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-05-13T01:43:16Z

I think we just need someone to start a discussion then generate a patch
to match.

> Bruce Momjian wrote:
> 
> > Here is a small list of big TODO items.  I was wondering which ones
> > people were thinking about for 7.2?
> >
> > * Allow inherited tables to inherit index, UNIQUE constraint, and primary key
> >   [inheritance]
> 
> 
> i was wondering if there was any thought still being given to Oliver 
> Elphick's post from a while back that is still in TODO.detail 
> [inheritance]: 
> http://candle.pha.pa.us/mhonarc/todo.detail/inheritance/msg00010.html
> 
> i kind of feel as though the inheritance semantics for postgres at the 
> moment are not fully fleshed out, and including further features without 
> having a full plan for the semantics doesn't seem to advance the effort 
> of making postgres a true Object-Relational DBMS.
> 
> for my part, as a user, i am excited that inheritance is available even 
> in a limited fashion, but where i use it, i have basically had to invent 
> my own semantics for referential integrity based on a suite of triggers. 
> this issue is addressed in Oliver's post, but i was wondering if such 
> issues were still a part of the development dialogue since Oliver's post 
> was the last in TODO.detail [inheritance] and seemed to merit no 
> response (or any that i could find in the mailing list archives).
> 
> -tfo
> 
> 
> 
> 
> 
> 
> ---------------------------(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) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: 7.2 items

Oleg Bartunov <oleg@sai.msu.su> — 2001-05-13T20:35:26Z

I'd like to have partial sorting implemented in 7.2.
While it's rather narrow optimization for case ORDER BY ... LIMIT ...
it has big (in my opinion) impact to Web application.
We get up to  6x performance improvement in our experiments with our very
crude patch for 7.1. The idea is very simple - stop sorting when we get
requested rows. Unfortunately, our knowledge of internals is poor and
we need some help.

	Regards,
		Oleg

On Thu, 10 May 2001, Bruce Momjian wrote:

> Here is a small list of big TODO items.  I was wondering which ones
> people were thinking about for 7.2?
>
> ---------------------------------------------------------------------------
>
> * Add replication of distributed databases [replication]
> 	o automatic fallover
> 	o load balancing
> 	o master/slave replication
> 	o multi-master replication
> 	o partition data across servers
> 	o sample implementation in contrib/rserv
> 	o queries across databases or servers (two-phase commit)
> * Point-in-time data recovery using backup and write-ahead log
> * Allow row re-use without vacuum (Vadim)
> * Add the concept of dataspaces/tablespaces [tablespaces]
> * Allow better control over user privileges [privileges]
> * Allow elog() to return error codes, module name, file name, line
>   number, not just messages [elog]
> * Allow international error message support and add error codes [elog]
> * Make binary/file in/out interface for TOAST columns
> * Large object interface improvements
> * Allow inherited tables to inherit index, UNIQUE constraint, and primary key
>   [inheritance]
> * Add ALTER TABLE DROP COLUMN feature [drop]
> * Add ALTER TABLE ... DROP CONSTRAINT
> * Automatically drop constraints/functions when object is dropped
>
>

	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.2 items

Lincoln Yeoh <lyeoh@pop.jaring.my> — 2001-05-14T03:44:39Z

At 01:20 PM 10-05-2001 -0400, Bruce Momjian wrote:
>Here is a small list of big TODO items.  I was wondering which ones
>people were thinking about for 7.2?
>
>---------------------------------------------------------------------------

Well since you asked, here's my wish list for Postgresql 7.2.

1) Full text index to be used by LIKE queries.
e.g.
create index myfti_idx on mytable ( mysoundex(story,'british english')
fti_ops);
Usage:
select * from mytable where mysoundex(story,'british english') like
'%tomato%';
select * from mytable where mysoundex(story,'us english') like '%either%';
select * from mytable where mysynonym(story) like '%excellent%';

First select indexed. Other selects not indexed.

2) Some form of synchronous "wait" which blocks till an event happens (no
need to poll at all).
e.g.
WAIT('sendmessagetomain');

NOTIFY('sendmessagetomain') gets things going. If not possible to reuse
NOTIFY, then something else will do.

This allows many programs on various hosts to wait for an event before
doing things.

The present async-io stuff has traces of polling left, can't be done in a
transaction and can't be used with Perl DBI (and maybe other standard DB
interfaces). 

3) And the notorious VACUUM and VACUUM analyze :).
How about:
VACUUM <table> lazy; (don't lock table)
VACUUM <table> [hardworking];
analyze <table>  [randomsample];
analyze <table> full;

Probably syntax should be different so as not to increase the number of
reserved words.

4) Not really important to me but can serial be a proper type or something
so that drop table will drop the linked sequence as well? 
Maybe:
 serial = old serial for compatibility
 serial4 = new serial
 serial8 = new serial using bigint
(OK so 2 billion is big, but...)

5) How will the various rollovers be handled e.g. OID, TID etc? What
happens if OIDs are not unique? As things get faster and bigger a nonunique
OID in a table might just happen.

Cheerio,
Link.

Re: todo - I want the elog() thingy

Hannu Krosing <hannu@tm.ee> — 2001-05-14T05:03:22Z

james@spunkysoftware.com wrote:
> 
> > > * Allow elog() to return error codes, module name, file name, line
> > >   number, not just messages [elog]
> 
> I bags this one. A nice relatively easy place for me to start hacken' the
> Postges. Which source tree do I diff and patch against? Er, I have no idea
> how to use these diff and patch things but I know that a manual exists.
> 
> How do I get the CVS source tree? Surely I don't have to download the whole
> thing every day? I only have 1KB/sec of connectivity and it's extremely
> expensive ($300/month).

see the page:

http://www.ca.postgresql.org/devel-corner/docs/postgres/cvs.html

the lnks are near the end of Developer's Corner page

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

Re: Re: 7.2 items

Hannu Krosing <hannu@tm.ee> — 2001-05-14T05:24:57Z

Lincoln Yeoh wrote:
> 
> 
> 2) Some form of synchronous "wait" which blocks till an event happens (no
> need to poll at all).
> e.g.
> WAIT('sendmessagetomain');
> 
> NOTIFY('sendmessagetomain') gets things going. If not possible to reuse
> NOTIFY, then something else will do.
> 
> This allows many programs on various hosts to wait for an event before
> doing things.
> 
> The present async-io stuff has traces of polling left, can't be done in a
> transaction and can't be used with Perl DBI (and maybe other standard DB
> interfaces).

What do you do if you are waiting on come other message - drop it,
reorder 
messages, something else ?

> 3) And the notorious VACUUM and VACUUM analyze :).
> How about:
> VACUUM <table> lazy; (don't lock table)
> VACUUM <table> [hardworking];
> analyze <table>  [randomsample];
> analyze <table> full;
> 
> Probably syntax should be different so as not to increase the number of
> reserved words.

Maybe some SET variable ?

SET VACUUM TO "LAZY";
SET VACUUM TO "ANALYZE EVERYTHING YOU CAN IN 15 MINUTES";

> 4) Not really important to me but can serial be a proper type or something
> so that drop table will drop the linked sequence as well?
> Maybe:
>  serial = old serial for compatibility
>  serial4 = new serial
>  serial8 = new serial using bigint
> (OK so 2 billion is big, but...)
> 
> 5) How will the various rollovers be handled e.g. OID, TID etc? What
> happens if OIDs are not unique? As things get faster and bigger a nonunique
> OID in a table might just happen.

OID's should _not_ be allowed to be non-unique, it is like spending
resources 
on "what if 2+2=5" scenarios.

I think that all system *IDs should be allowed to be 64 bits - XID reuse
is 
a kludge that can serve the immediate problem of DB freezing when
running out 
of transaction IDs - but I don't like it as a long-term solution.

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

Re: 7.2 items

Karel Zak <zakkr@zf.jcu.cz> — 2001-05-14T07:40:09Z

On Sat, May 12, 2001 at 11:21:44PM +0200, Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > Peter E. had implied that he wanted to tackle the elog issues for 7.2,
> > but I'm not sure if he's committed to it or not.
> 
> Well...
> 
> * Automatically add filename, line, function name:  Easy to code, lots of
>   labour.  Should be lumped in with some other large change.
> 
> * Error codes:  I think there are only a handful of key messages that
>   users (programs) need to detect cleanly, mostly constraint violations.
>   The rest are "the query you sent is wrong -- fix your application" and
>   "something went really wrong -- manual repair needed"
> 
>   So maybe this could be a smallish change.
> 
> * Translation:  If we want to use gettext I can get started.  I don't
>   think I'm interested in using any other interface.

 What dissect this work to two parts? First implement error codes and later
translation. IMHO transaction hasn't big importance (and will encapsulate
in elog() stuff) and is possible speculate about it later. Do you plannig 
gettext stuff as a ./configure option? 

			Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Re: 7.2 items

Franck Martin <franck@sopac.org> — 2001-05-14T08:24:26Z

#ifdef ENABLE_NLS
#  include <libintl.h>
#  define _(String) gettext (String)
#  define N_(String) (String)
#else
/* Stubs that do something close enough. */
#  define textdomain(String)
#  define gettext(String) (String)
#  define dgettext(Domain,Message) (Message)
#  define dcgettext(Domain,Message,Type) (Message)
#  define bindtextdomain(Domain,Directory)
#  define _(String) (String)
#  define N_(String) (String)
#endif

Just add the above code to each file, and each time that you use a string "my
string" encapsulate it with _("my string"). gettext will parse the code and
extract all the strings for future translation.

Cheers

Karel Zak wrote:

> On Sat, May 12, 2001 at 11:21:44PM +0200, Peter Eisentraut wrote:
> > Tom Lane writes:
> >
> > > Peter E. had implied that he wanted to tackle the elog issues for 7.2,
> > > but I'm not sure if he's committed to it or not.
> >
> > Well...
> >
> > * Automatically add filename, line, function name:  Easy to code, lots of
> >   labour.  Should be lumped in with some other large change.
> >
> > * Error codes:  I think there are only a handful of key messages that
> >   users (programs) need to detect cleanly, mostly constraint violations.
> >   The rest are "the query you sent is wrong -- fix your application" and
> >   "something went really wrong -- manual repair needed"
> >
> >   So maybe this could be a smallish change.
> >
> > * Translation:  If we want to use gettext I can get started.  I don't
> >   think I'm interested in using any other interface.
>
>  What dissect this work to two parts? First implement error codes and later
> translation. IMHO transaction hasn't big importance (and will encapsulate
> in elog() stuff) and is possible speculate about it later. Do you plannig
> gettext stuff as a ./configure option?
>
>                         Karel
>
> --
>  Karel Zak  <zakkr@zf.jcu.cz>
>  http://home.zf.jcu.cz/~zakkr/
>
>  C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

Re: Re: 7.2 items

Lincoln Yeoh <lyeoh@pop.jaring.my> — 2001-05-14T09:24:31Z

At 10:24 AM 14-05-2001 +0500, Hannu Krosing wrote:
>Lincoln Yeoh wrote:
>> 2) Some form of synchronous "wait" which blocks till an event happens (no
>> need to poll at all).
>> e.g.
>> WAIT('sendmessagetomain');
>> 
>> NOTIFY('sendmessagetomain') gets things going. If not possible to reuse
>> NOTIFY, then something else will do.
>> 
>> This allows many programs on various hosts to wait for an event before
>> doing things.
>> 
>> The present async-io stuff has traces of polling left, can't be done in a
>> transaction and can't be used with Perl DBI (and maybe other standard DB
>> interfaces).
>
>What do you do if you are waiting on come other message - drop it,
>reorder 
>messages, something else ?

Since the proposed WAIT is to block on a particular message immediately,
you can't really wait on other messages at the same time. Multiple WAITs
will just be done in the order issued. 

It can be considered to be a waste of one backend/db connection, but it
allows some things to be much simpler - each program just does one thing,
and hopefully does it well. 

WAIT should return a TRUE if successful - received desired event and
stopped blocking, and a FALSE if not - something else happened (SIGTERM,
backend disconnected/died), and stopped blocking.

Hmm hang on, what will happen if pgsql is shutdown. Tons of WAITing
processes waking up at the same time? Use FreeBSD? :).

Cheerio,
Link.

Re: 7.2 items

mlw <markw@mohawksoft.com> — 2001-05-14T12:43:27Z

Lincoln Yeoh wrote:
> 
> At 01:20 PM 10-05-2001 -0400, Bruce Momjian wrote:
> >Here is a small list of big TODO items.  I was wondering which ones
> >people were thinking about for 7.2?
> >
> >---------------------------------------------------------------------------
> 
> Well since you asked, here's my wish list for Postgresql 7.2.
> 
> 1) Full text index to be used by LIKE queries.
> e.g.
> create index myfti_idx on mytable ( mysoundex(story,'british english')
> fti_ops);
> Usage:
> select * from mytable where mysoundex(story,'british english') like
> '%tomato%';
> select * from mytable where mysoundex(story,'us english') like '%either%';
> select * from mytable where mysynonym(story) like '%excellent%';
> 
> First select indexed. Other selects not indexed.

This is not as easy as it looks. Full text search requires one of two
approaches, either a trigger function which updates a full text index on insert
or update, or a system which periodically scans a database and builds a full
text index. The fulltextindex method that is in contrib and my FTSS system are
examples of both respectively.

Either way it is a bit of overhead, and typically outside normal SQL. Most
people would not want the amount of overhead required to maintain a full text
index on each insert or update.

Also, I have been trying to talk the guys into doing some things with indexes,
but my understanding is that indexes are one of the last bastions of black
magic in Postgres.

-- 
42 was the answer, 49 was too soon.
------------------------
http://www.mohawksoft.com

Re: todo - I want the elog() thingy

james@spunkysoftware.com — 2001-05-14T12:44:04Z

> > * Allow elog() to return error codes, module name, file name, line
> >   number, not just messages [elog]

I bags this one. A nice relatively easy place for me to start hacken' the
Postges. Which source tree do I diff and patch against? Er, I have no idea
how to use these diff and patch things but I know that a manual exists.

How do I get the CVS source tree? Surely I don't have to download the whole
thing every day? I only have 1KB/sec of connectivity and it's extremely
expensive ($300/month).

Can I just download the files for elog() and do it that way, and I'll write
some driver function to unit test it, and send the patch when I'm done to
the patches list.

Any developers got some tips for me?

---
James



----- Original Message -----
From: "Oleg Bartunov" <oleg@sai.msu.su>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "PostgreSQL-development" <pgsql-hackers@postgresql.org>
Sent: Sunday, May 13, 2001 9:35 PM
Subject: Re: [HACKERS] 7.2 items


> I'd like to have partial sorting implemented in 7.2.
> While it's rather narrow optimization for case ORDER BY ... LIMIT ...
> it has big (in my opinion) impact to Web application.
> We get up to  6x performance improvement in our experiments with our very
> crude patch for 7.1. The idea is very simple - stop sorting when we get
> requested rows. Unfortunately, our knowledge of internals is poor and
> we need some help.
>
> Regards,
> Oleg
>
> On Thu, 10 May 2001, Bruce Momjian wrote:
>
> > Here is a small list of big TODO items.  I was wondering which ones
> > people were thinking about for 7.2?
> >
>
> --------------------------------------------------------------------------
-
> >
> > * Add replication of distributed databases [replication]
> > o automatic fallover
> > o load balancing
> > o master/slave replication
> > o multi-master replication
> > o partition data across servers
> > o sample implementation in contrib/rserv
> > o queries across databases or servers (two-phase commit)
> > * Point-in-time data recovery using backup and write-ahead log
> > * Allow row re-use without vacuum (Vadim)
> > * Add the concept of dataspaces/tablespaces [tablespaces]
> > * Allow better control over user privileges [privileges]
> > * Allow elog() to return error codes, module name, file name, line
> >   number, not just messages [elog]
> > * Allow international error message support and add error codes [elog]
> > * Make binary/file in/out interface for TOAST columns
> > * Large object interface improvements
> > * Allow inherited tables to inherit index, UNIQUE constraint, and
primary key
> >   [inheritance]
> > * Add ALTER TABLE DROP COLUMN feature [drop]
> > * Add ALTER TABLE ... DROP CONSTRAINT
> > * Automatically drop constraints/functions when object is dropped
> >
> >
>
> 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
>
>
> ---------------------------(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.2 items

Martin Marques <martin@bugs.unl.edu.ar> — 2001-05-14T15:13:10Z

Quoting Peter Eisentraut <peter_e@gmx.net>:

> Patrick Welche writes:
> 
> > > > What's missing with it?
> > >
> > > * portability
> > >
> > > At first glance, uses strlcat and strlcpy.  Didn't look further.
> >
> > As I said, I didn't change anything within the GNU make source to get it
> to
> > work.
> 
> I am talking about the source of the thing (libintl) itself.

[snip]

Sorry if I enter in a rush....

whats wrong with GNU gettext?

Saludos... :-)

-- 
El mejor sistema operativo es aquel que te da de comer.
Cuida tu dieta.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Re: 7.2 items

Patrick Welche <prlw1@newn.cam.ac.uk> — 2001-05-14T17:13:35Z

On Sat, May 12, 2001 at 08:00:42PM -0400, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > * Translation:  If we want to use gettext I can get started.  I don't
> >   think I'm interested in using any other interface.
> 
> I have no objection to the gettext API, but I was and still am concerned
> about depending on GNU gettext's code, because of license conflicts.
> There is a BSD-license gettext clone project, but it doesn't look to be
> very far along.

What's missing with it? (eg managed to force gmake's configure to use it
rather than its own, and didn't have to fiddle anything for it to just work)

% ldd `which gmake` 
/usr/local/bin/gmake:
         -lutil.5 => /usr/lib/libutil.so.5
         -lkvm.5 => /usr/lib/libkvm.so.5
         -lintl.0 => /usr/lib/libintl.so.0    << BSD license lib
         -lc.12 => /usr/lib/libc.so.12
% env LANGUAGE=fr gmake
gmake: *** Pas de cibles spécifiées et aucun makefile n'a été trouvé. Arrêt.

Cheers,

Patrick

Re: 7.2 items

Thomas Swan <tswan@olemiss.edu> — 2001-05-14T19:08:42Z

Please, consider a BLOB column type without having to do lo_import, 
lo_export.

Re: 7.2 items

Peter Eisentraut <peter_e@gmx.net> — 2001-05-14T19:36:56Z

Patrick Welche writes:

> > I have no objection to the gettext API, but I was and still am concerned
> > about depending on GNU gettext's code, because of license conflicts.
> > There is a BSD-license gettext clone project, but it doesn't look to be
> > very far along.
>
> What's missing with it?

* portability

At first glance, uses strlcat and strlcpy.  Didn't look further.

* dedication to portability

Only plans to support *BSD.

* source code availability

Didn't find anything outside NetBSD CVS and the CVS rep where they got it
from.

* documentation

Related to above.

* English support forum

Only Japanese mailing list available.

If you can address these things we might have a winner, otherwise we might
have to fork it.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: 7.2 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-05-14T19:44:27Z

> If you can address these things we might have a winner, otherwise we might
> have to fork it.

I am going to have to ask for clarification on that last point.  Are
you suggesting we have two versions?

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

Re: 7.2 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-05-14T19:44:57Z

> Please, consider a BLOB column type without having to do lo_import, 
> lo_export.

Yep, big needed item.

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

Re: 7.2 items

Patrick Welche <prlw1@newn.cam.ac.uk> — 2001-05-14T20:18:15Z

On Mon, May 14, 2001 at 09:36:56PM +0200, Peter Eisentraut wrote:
> Patrick Welche writes:
> 
> > > I have no objection to the gettext API, but I was and still am concerned
> > > about depending on GNU gettext's code, because of license conflicts.
> > > There is a BSD-license gettext clone project, but it doesn't look to be
> > > very far along.
> >
> > What's missing with it?
> 
> * portability
> 
> At first glance, uses strlcat and strlcpy.  Didn't look further.

As I said, I didn't change anything within the GNU make source to get it to
work. grep strlcat on GNU make, which you must have in order to build
postgresql, returns nothing, however grep gettext does. I chose gmake as an
example which is probably written with portability in mind.

> * dedication to portability
> 
> Only plans to support *BSD.

What does this imply?

HISTORY
     The functions are implemented by Citrus project, based on the documenta-
     tions for GNU gettext.

> * source code availability
> 
> Didn't find anything outside NetBSD CVS and the CVS rep where they got it
> from.

>From libintl.h

/*-
 * Copyright (c) 2000 Citrus Project,
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 * 1. Redistributions of source code must retain the above copyright
 *    notice, this list of conditions and the following disclaimer.
 * 2. Redistributions in binary form must reproduce the above copyright
 *    notice, this list of conditions and the following disclaimer in the
 *    documentation and/or other materials provided with the distribution.
 *
 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND
 * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE
 * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
 * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
 * OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
 * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
 * LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
 * OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
 * SUCH DAMAGE.
 */

which I think counts as a postgresql compatible license? Is that what you
meant?

> * documentation
> 
> Related to above.

The HISTORY bit was quoted from the gettext man page.. What more
documentation is required? AFAIK it's meant to be a direct replacement..

> * English support forum
> 
> Only Japanese mailing list available.

Yes, I wondered about that to.. Luckily PostgreSQL is international!

Cheers,

Patrick

Re: 7.2 items

Peter Eisentraut <peter_e@gmx.net> — 2001-05-14T20:36:30Z

Patrick Welche writes:

> > > What's missing with it?
> >
> > * portability
> >
> > At first glance, uses strlcat and strlcpy.  Didn't look further.
>
> As I said, I didn't change anything within the GNU make source to get it to
> work.

I am talking about the source of the thing (libintl) itself.

> > * dedication to portability
> >
> > Only plans to support *BSD.
>
> What does this imply?

It implies it won't easily work on non-BSD platforms, which makes it
unusable to many folks.

> > * source code availability
> >
> > Didn't find anything outside NetBSD CVS and the CVS rep where they got it
> > from.

> which I think counts as a postgresql compatible license? Is that what you
> meant?

No, I meant I can't find the source code anywhere in a polished form.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: 7.2 items

Alfred Perlstein <bright@wintelcom.net> — 2001-05-14T20:55:33Z

* Peter Eisentraut <peter_e@gmx.net> [010514 13:39] wrote:
> Patrick Welche writes:
> 
> > > I have no objection to the gettext API, but I was and still am concerned
> > > about depending on GNU gettext's code, because of license conflicts.
> > > There is a BSD-license gettext clone project, but it doesn't look to be
> > > very far along.
> >
> > What's missing with it?
> 
> * portability
> 
> At first glance, uses strlcat and strlcpy.  Didn't look further.
> 
> * dedication to portability
> 
> Only plans to support *BSD.
> 
> * source code availability
> 
> Didn't find anything outside NetBSD CVS and the CVS rep where they got it
> from.
> 
> * documentation
> 
> Related to above.
> 
> * English support forum
> 
> Only Japanese mailing list available.
> 
> 
> If you can address these things we might have a winner, otherwise we might
> have to fork it.

Please don't fork it.  If you base off the FreeBSD gettext I will
merge your changes into ours as long as they follow the style of
the existing code.

I'd really like to see a "bsd userland" out there not tied to a
particular version of UNIX so this means a lot to me.

-- 
-Alfred Perlstein - [alfred@freebsd.org]
Daemon News Magazine in your snail-mail! http://magazine.daemonnews.org/

Re: 7.2 items

Peter Eisentraut <peter_e@gmx.net> — 2001-05-14T21:09:40Z

Karel Zak writes:

> > * Translation:  If we want to use gettext I can get started.  I don't
> >   think I'm interested in using any other interface.
>
>  What dissect this work to two parts? First implement error codes and later
> translation. IMHO transaction hasn't big importance (and will encapsulate
> in elog() stuff) and is possible speculate about it later.

It's important to me.  And it's not contained to elog(), I want to
translate the whole thing, including all the frontends.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: 7.2 items

Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-14T23:26:25Z

Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> On Sat, May 12, 2001 at 08:00:42PM -0400, Tom Lane wrote:
>> There is a BSD-license gettext clone project, but it doesn't look to be
>> very far along.

> What's missing with it?

Where did you find an actual release meant for public consumption?
I had a hard time even finding a CVS server.

No release history == not very far along, in my book.

			regards, tom lane

Re: 7.2 items

Max Khon <fjoe@newst.net> — 2001-05-15T05:50:57Z

hi, there!

On Mon, 14 May 2001, Peter Eisentraut wrote:

> > > I have no objection to the gettext API, but I was and still am concerned
> > > about depending on GNU gettext's code, because of license conflicts.
> > > There is a BSD-license gettext clone project, but it doesn't look to be
> > > very far along.
> >
> > What's missing with it?
> 
> * portability
> 
> At first glance, uses strlcat and strlcpy.  Didn't look further.

you can pull strlcat and strlcpy from *BSD source tree either
they are pretty portable :)

/fjoe

Re: 7.2 items

Peter Eisentraut <peter_e@gmx.net> — 2001-05-15T17:11:49Z

Tom Lane writes:

> Where did you find an actual release meant for public consumption?

NetBSD is using it in production.  FreeBSD too?  Some people from those
camps offered to cooperate in adopting this for our uses, so it's worth a
try.  I'll see if I can make a self-contained portable package out of that
code later this week.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: 7.2 items

tih@kpnqwest.no — 2001-06-07T11:08:17Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:

> Here is a small list of big TODO items.  I was wondering which ones
> people were thinking about for 7.2?

A friend of mine wants to use PostgreSQL instead of Oracle for a large
application, but has run into a snag when speed comparisons looked
good until the Oracle folks added a couple of BITMAP indexes.  I can't
recall seeing any discussion about that here -- are there any plans?

-tih
-- 
The basic difference is this: hackers build things, crackers break them.

Re: 7.2 items

mlw <markw@mohawksoft.com> — 2001-06-07T11:36:41Z

Tom Ivar Helbekkmo wrote:
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
> > Here is a small list of big TODO items.  I was wondering which ones
> > people were thinking about for 7.2?
> 
> A friend of mine wants to use PostgreSQL instead of Oracle for a large
> application, but has run into a snag when speed comparisons looked
> good until the Oracle folks added a couple of BITMAP indexes.  I can't
> recall seeing any discussion about that here -- are there any plans?

I have tried to bring this up in several different forms, and hardly ever get a
nibble.

Bitmap indexes are great for text searching. Perhaps you can use
"fulltextindex" in the contrib directory. It isn't as fast as a bitmap index,
and the syntax would be different, but it would be perform better.

Re: 7.2 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-06-07T15:03:02Z

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
> > Here is a small list of big TODO items.  I was wondering which ones
> > people were thinking about for 7.2?
> 
> A friend of mine wants to use PostgreSQL instead of Oracle for a large
> application, but has run into a snag when speed comparisons looked
> good until the Oracle folks added a couple of BITMAP indexes.  I can't
> recall seeing any discussion about that here -- are there any plans?

It is not on our list and I am not sure what they do.

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

Re: 7.2 items

Rod Taylor <rod.taylor@inquent.com> — 2001-06-07T16:05:56Z

Compare price of implementation.

For that $100k on the oracle license you can toss in a few more gigs
of memory and a few extra CPU's and perhaps 15k drives rather than 10k
ones :)

Then toss in the monthly support contracts between Oracle & Great
Bridge (or Pgsql.inc if you can get anyone on their staff) and all of
a sudden you can afford to upgrade the hardware more often and have a
developer write some work arounds for the slower parts of the program.

Anyway, the point is to compare the 2 products where the price is
similar (installation wide).  If postgres is faster, then reduce the
hardware till they are a similar speed level.  Now you do a price
comparison and take it to business.  'Cheaper software, but slightly
more expensive hardware means a lower priced package with similar
performance'.
--
Rod Taylor

Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. You cannot imagine why you ever felt
otherwise.

----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>
To: "Tom Ivar Helbekkmo" <tih@kpnQwest.no>
Cc: "PostgreSQL-development" <pgsql-hackers@postgresql.org>
Sent: Thursday, June 07, 2001 11:03 AM
Subject: Re: [HACKERS] 7.2 items


> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >
> > > Here is a small list of big TODO items.  I was wondering which
ones
> > > people were thinking about for 7.2?
> >
> > A friend of mine wants to use PostgreSQL instead of Oracle for a
large
> > application, but has run into a snag when speed comparisons looked
> > good until the Oracle folks added a couple of BITMAP indexes.  I
can't
> > recall seeing any discussion about that here -- are there any
plans?
>
> It is not on our list and I am not sure what they do.
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@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
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>

Re: 7.2 items

mlw <markw@mohawksoft.com> — 2001-06-07T18:36:59Z

Bruce Momjian wrote:

> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >
> > > Here is a small list of big TODO items.  I was wondering which ones
> > > people were thinking about for 7.2?
> >
> > A friend of mine wants to use PostgreSQL instead of Oracle for a large
> > application, but has run into a snag when speed comparisons looked
> > good until the Oracle folks added a couple of BITMAP indexes.  I can't
> > recall seeing any discussion about that here -- are there any plans?
>
> It is not on our list and I am not sure what they do.

Do you have access to any Oracle Documentation? There is a good explanation
of them.

However, I will try to explain.

If you have a table, locations. It has 1,000,000 records.

In oracle you do this:

create bitmap index bitmap_foo on locations (state) ;

For each unique value of 'state' oracle will create a bitmap with 1,000,000
bits in it. With a one representing a match and a zero representing no
match. Record '0' in the table is represented by bit '0' in the bitmap,
record '1' is represented by bit '1', record two by bit '2' and so on.

In a table where comparatively few different values are to be indexed in a
large table, a bitmap index can be quite small and not suffer the N * log(N)
disk I/O most tree based indexes suffer. If the bitmap is fairly sparse or
dense (or have periods of denseness and sparseness), it can be compressed
very efficiently as well.

When the statement:

select * from locations where state = 'MA';

Is executed, the bitmap is read into memory in very few disk operations.
(Perhaps even as few as one or two). It is a simple operation of rifling
through the bitmap for '1's that indicate the record has the property,
'state' = 'MA';

Re: Re: 7.2 items

Oleg Bartunov <oleg@sai.msu.su> — 2001-06-07T19:38:20Z

I think it's possible to implement bitmap indexes with a little
effort using GiST. at least I know one implementation
http://www.it.iitb.ernet.in/~rvijay/dbms/proj/
if you have interests you could implement bitmap indexes yourself
unfortunately, we're very busy

	Oleg
On Thu, 7 Jun 2001, mlw wrote:

> Bruce Momjian wrote:
>
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > >
> > > > Here is a small list of big TODO items.  I was wondering which ones
> > > > people were thinking about for 7.2?
> > >
> > > A friend of mine wants to use PostgreSQL instead of Oracle for a large
> > > application, but has run into a snag when speed comparisons looked
> > > good until the Oracle folks added a couple of BITMAP indexes.  I can't
> > > recall seeing any discussion about that here -- are there any plans?
> >
> > It is not on our list and I am not sure what they do.
>
> Do you have access to any Oracle Documentation? There is a good explanation
> of them.
>
> However, I will try to explain.
>
> If you have a table, locations. It has 1,000,000 records.
>
> In oracle you do this:
>
> create bitmap index bitmap_foo on locations (state) ;
>
> For each unique value of 'state' oracle will create a bitmap with 1,000,000
> bits in it. With a one representing a match and a zero representing no
> match. Record '0' in the table is represented by bit '0' in the bitmap,
> record '1' is represented by bit '1', record two by bit '2' and so on.
>
> In a table where comparatively few different values are to be indexed in a
> large table, a bitmap index can be quite small and not suffer the N * log(N)
> disk I/O most tree based indexes suffer. If the bitmap is fairly sparse or
> dense (or have periods of denseness and sparseness), it can be compressed
> very efficiently as well.
>
> When the statement:
>
> select * from locations where state = 'MA';
>
> Is executed, the bitmap is read into memory in very few disk operations.
> (Perhaps even as few as one or two). It is a simple operation of rifling
> through the bitmap for '1's that indicate the record has the property,
> 'state' = 'MA';
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>

	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: Re: 7.2 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-06-07T20:04:42Z

> I think it's possible to implement bitmap indexes with a little
> effort using GiST. at least I know one implementation
> http://www.it.iitb.ernet.in/~rvijay/dbms/proj/
> if you have interests you could implement bitmap indexes yourself
> unfortunately, we're very busy
> 

I have added this thread to TODO.detail and TODO:

	* Add bitmap indexes [performance]     

Very interesting to use GIST for this.  

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

Re: 7.2 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-06-26T15:36:28Z

> Bruce Momjian wrote:
> > 
> > > Please, consider a BLOB column type without having to do lo_import,
> > > lo_export.
> > 
> > Yep, big needed item.
> 
> as we have now and unlimited rowlength it seems to be more of an 
> interface issue than the actual implementation one (mod seek/read).
> 
> Is there an ISO/ANSI SQL interface to BLOB's defined someplace ?

Yes, clearly interface.  Someone is working on code to insert/export
binary stuff using bytea and base64 encoding.  Seems like a good idea.

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

Re: 7.2 items

Hannu Krosing <hannu@tm.ee> — 2001-06-26T15:45:27Z

Bruce Momjian wrote:
> 
> > Please, consider a BLOB column type without having to do lo_import,
> > lo_export.
> 
> Yep, big needed item.

as we have now and unlimited rowlength it seems to be more of an 
interface issue than the actual implementation one (mod seek/read).

Is there an ISO/ANSI SQL interface to BLOB's defined someplace ?

----------
Hannu

Re: Re: 7.2 items

Hannu Krosing <hannu@tm.ee> — 2001-06-27T05:11:57Z

Bruce Momjian wrote:
> 
> > Bruce Momjian wrote:
> > >
> > > > Please, consider a BLOB column type without having to do lo_import,
> > > > lo_export.
> > >
> > > Yep, big needed item.
> >
> > as we have now and unlimited rowlength it seems to be more of an
> > interface issue than the actual implementation one (mod seek/read).
> >
> > Is there an ISO/ANSI SQL interface to BLOB's defined someplace ?
> 
> Yes, clearly interface.  Someone is working on code to insert/export
> binary stuff using bytea and base64 encoding.  Seems like a good idea.

Will there also be support current file-like BLOB ops like seek and 
read(n) ?

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

Re: Re: 7.2 items

alex@pilosoft.com — 2001-06-27T10:31:58Z

On Wed, 27 Jun 2001, Hannu Krosing wrote:

> Bruce Momjian wrote:
> > 
> > > Bruce Momjian wrote:
> > > >
> > > > > Please, consider a BLOB column type without having to do lo_import,
> > > > > lo_export.
> > > >
> > > > Yep, big needed item.
> > >
> > > as we have now and unlimited rowlength it seems to be more of an
> > > interface issue than the actual implementation one (mod seek/read).
> > >
> > > Is there an ISO/ANSI SQL interface to BLOB's defined someplace ?
> > 
> > Yes, clearly interface.  Someone is working on code to insert/export
> > binary stuff using bytea and base64 encoding.  Seems like a good idea.
> 
> Will there also be support current file-like BLOB ops like seek and 
> read(n) ?

Sure, via substring().  Unfortunately, TOASTed tuple must be detoasted
completely, and you cannot get any performance boost by, for example,
reading first 8k out of a 500k bytea value. All 500k must be detoasted
first.

-alex

Re: Re: 7.2 items

Hannu Krosing <hannu@tm.ee> — 2001-06-27T12:43:33Z

Alex Pilosov wrote:
> 
> On Wed, 27 Jun 2001, Hannu Krosing wrote:
> 
> > Bruce Momjian wrote:
> > >
> > > > Bruce Momjian wrote:
> > > > >
> > > > > > Please, consider a BLOB column type without having to do lo_import,
> > > > > > lo_export.
> > > > >
> > > > > Yep, big needed item.
> > > >
> > > > as we have now and unlimited rowlength it seems to be more of an
> > > > interface issue than the actual implementation one (mod seek/read).
> > > >
> > > > Is there an ISO/ANSI SQL interface to BLOB's defined someplace ?
> > >
> > > Yes, clearly interface.  Someone is working on code to insert/export
> > > binary stuff using bytea and base64 encoding.  Seems like a good idea.
> >
> > Will there also be support current file-like BLOB ops like seek and
> > read(n) ?
> 
> Sure, via substring().

That would _not_ be seek()+read() by default but can be possibly
implemented 
as such in cooperation with toast machinery.

> Unfortunately, TOASTed tuple must be detoasted
> completely, and you cannot get any performance boost by, for example,
> reading first 8k out of a 500k bytea value. All 500k must be detoasted
> first.

I suspect that this can be avoided with a smarter toast-aware substring 
and possibly also disallowing compression (just using spreading overs 
multiple toast-table records).

IIRC there exists machinery (if not interface) for influencing TOAST
processor 
decisions to compress or not.

AFAIK, oracle LONGs have some smart schema where you can acess them in
some 
smart ways if the cursor is on current row, but you will get full
bytestrings
if you request more than one row at a time, i.e. some compined BLOB/LONG
scheme.

I think this is worth considering, specially for seek/read/write type
operations.

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

Re: Re: 7.2 items

Peter Eisentraut <peter_e@gmx.net> — 2001-06-27T15:04:49Z

Bruce Momjian writes:

> > Please, consider a BLOB column type without having to do lo_import,
> > lo_export.
>
> Yep, big needed item.

Maybe we could make the BLOB type a wrapper around the lo_* functions?
The BLOB value would only store the oid.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: Re: 7.2 items

Peter Eisentraut <peter_e@gmx.net> — 2001-06-27T15:06:20Z

Hannu Krosing writes:

> as we have now and unlimited rowlength it seems to be more of an
> interface issue than the actual implementation one (mod seek/read).

unlimited = 2 GB, IIRC

> Is there an ISO/ANSI SQL interface to BLOB's defined someplace ?

It's basically no different from regular character strings, i.e.,
substring(), position(), ||, etc.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: Re: 7.2 items

Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-27T15:40:12Z

Peter Eisentraut <peter_e@gmx.net> writes:
> Bruce Momjian writes:
> Please, consider a BLOB column type without having to do lo_import,
> lo_export.
>> 
>> Yep, big needed item.

> Maybe we could make the BLOB type a wrapper around the lo_* functions?
> The BLOB value would only store the oid.

What for/why bother?  A toastable bytea column would do just as well.
What we need is an easy-to-use set of access functions, which we haven't
got in either case (without additional work).

I'd prefer to see that work invested in access functions for toasted
columns, because LOs have all sorts of administrative problems ---
security and garbage collection, to name two.  We don't really want to
encourage their use in the long term.

			regards, tom lane

Re: Re: 7.2 items

Peter Eisentraut <peter_e@gmx.net> — 2001-06-27T16:29:51Z

Tom Lane writes:

> > Maybe we could make the BLOB type a wrapper around the lo_* functions?
> > The BLOB value would only store the oid.
>
> What for/why bother?  A toastable bytea column would do just as well.

There's still a 1 or 2 GB limit for data stored in that.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: Re: 7.2 items

Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-27T16:44:29Z

Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> What for/why bother?  A toastable bytea column would do just as well.

> There's still a 1 or 2 GB limit for data stored in that.

1 Gb, I believe ... but LOs are not a lot better; they'd max out at 2 or
at most 4 Gb, depending on whether the code always treats offsets as
unsigned.

			regards, tom lane

Re: Re: 7.2 items

Hannu Krosing <hannu@tm.ee> — 2001-06-27T18:33:35Z

Peter Eisentraut wrote:
> 
> Hannu Krosing writes:
> 
> > as we have now and unlimited rowlength it seems to be more of an
> > interface issue than the actual implementation one (mod seek/read).
> 
> unlimited = 2 GB, IIRC

Yes, about that unlimited ;)

> > Is there an ISO/ANSI SQL interface to BLOB's defined someplace ?
> 
> It's basically no different from regular character strings, i.e.,
> substring(), position(), ||, etc.

So no standard seek/read/write type interface ?

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

Re: Re: 7.2 items

Peter Eisentraut <peter_e@gmx.net> — 2001-06-27T18:54:54Z

Tom Lane writes:

> Peter Eisentraut <peter_e@gmx.net> writes:
> > Tom Lane writes:
> >> What for/why bother?  A toastable bytea column would do just as well.
>
> > There's still a 1 or 2 GB limit for data stored in that.
>
> 1 Gb, I believe ... but LOs are not a lot better; they'd max out at 2 or
> at most 4 Gb, depending on whether the code always treats offsets as
> unsigned.

That can be fixed by adding a 64-bit aware equivalent of the existing lo_*
functions.  I suppose it'd be a lot harder to make regular data types
handle long values.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: Re: 7.2 items

Peter Eisentraut <peter_e@gmx.net> — 2001-06-27T21:55:29Z

Hannu Krosing writes:

> > > Is there an ISO/ANSI SQL interface to BLOB's defined someplace ?
> >
> > It's basically no different from regular character strings, i.e.,
> > substring(), position(), ||, etc.
>
> So no standard seek/read/write type interface ?

SQL is not a procedural language, so this has to be expected.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: Re: 7.2 items

Philip Warner <pjw@rhyme.com.au> — 2001-06-27T23:25:37Z

At 23:55 27/06/01 +0200, Peter Eisentraut wrote:
>Hannu Krosing writes:
>
>> > > Is there an ISO/ANSI SQL interface to BLOB's defined someplace ?
>> >
>> > It's basically no different from regular character strings, i.e.,
>> > substring(), position(), ||, etc.
>>
>> So no standard seek/read/write type interface ?
>
>SQL is not a procedural language, so this has to be expected.
>

Wouldn't this logic also imply that there would be no cursor positioning?
No update cursors etc? seek, read, write don't seem that different to
MOVE/FETCH/UPDATE.

You also missed out mentioning the character overlay functions (which I
don't think we have), that allow updates of parts of BLOBs.


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

Re: Re: 7.2 items

Hannu Krosing <hannu@tm.ee> — 2001-06-28T06:33:24Z

Peter Eisentraut wrote:
> 
> Hannu Krosing writes:
> 
> > > > Is there an ISO/ANSI SQL interface to BLOB's defined someplace ?
> > >
> > > It's basically no different from regular character strings, i.e.,
> > > substring(), position(), ||, etc.
> >
> > So no standard seek/read/write type interface ?
> 
> SQL is not a procedural language, so this has to be expected.

SQL is about 3-5 languages which share some syntax DDL,DML,DQL,cursor
manipulation,...

And we do currently have seek/read/write for LOs, possibly as a relict
from postquel.

We also have PL/PGSQL and other PL's that can be used from wihin SQL, so
for me the 
borders between different languages seem quite blurred. 

What I hoped the standard would have is something like cursor ops on a
field in on 
outer cursors current record.

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