7.2 items

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

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

I'm not sure about this one, but it should be possible to change the type of
a column without vacuum, to reoorder the columns, change the name,...

It would allow people who design GUIs to PG to do a very nice designer
interface (like on MS SQL7.0).

Also on the design part, the UML standard and others allow the drawing of
interfaces (ArgoUML, Dia,..). Someone could work out an export from UML
graph to PG Table Creation.

I'm still working on a new geographic type (geoobj) for PG follwing ISO
standards. Except the standard is still a draft. So I can't meet any
deadline... (fmaps.sourceforge.net)

Franck Martin
Network and Database Development Officer
SOPAC South Pacific Applied Geoscience Commission
Fiji
E-mail: franck@sopac.org <mailto:franck@sopac.org>
Web site: http://www.sopac.org/ <http://www.sopac.org/&gt;
Support FMaps: http://fmaps.sourceforge.net/ <http://fmaps.sourceforge.net/&gt;

This e-mail is intended for its addresses only. Do not forward this e-mail
without approval. The views expressed in this e-mail may not be necessarily
the views of SOPAC.

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Friday, 11 May 2001 5:21
To: PostgreSQL-development
Subject: [HACKERS] 7.2 items

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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

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

* 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

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

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

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

* 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.

* 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

* 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

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.

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
-----------------------------------------------------------------

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

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

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

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

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

* 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

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

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

Other than that, I'm mostly thinking about performance improvements
for 7.2, not features

My wish list includes incorporating the ideas brought forward in this
post
http://www.ca.postgresql.org/mhonarc/pgsql-hackers/2000-09/msg00513.html
which discusses a patch that allows queries to return data from
the index scan directly. In the thread it was noted that this should
be optional in that their is a small storage overhead but preliminary
results were showing a potential 75X increase in performance. Oracle
has a similar index only table. A special index type that includes a
tid would be really nice and I think worth investigating.
Best Regards,
Carl Garland
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com

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

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

This issue was brought up before as well but after searching the
archives I couldn't find original post, but it didnt seem to be
addressed by any core hackers. From the linux kernel mailing lists:
http://www.appwatch.com/lists/linux-kernel/Week-of-Mon-20010305/026326.html

Tridge and I tried out the postgresql benchmark you used here and this
contention is due to a bug in postgres. From a quick strace, we found
the threads do a load of select(0, NULL, NULL, NULL, {0,0}). Basically all
threads are pounding on schedule().

Our guess is that the app has some form of userspace synchronisation
(semaphores/spinlocks). I'd argue that the app needs to be fixed not the
kernel, or a more valid test case is put forwards. :)

And later here
http://www.appwatch.com/lists/linux-kernel/Week-of-Mon-20010305/027408.html
:

Thanks for looking into postgresql/pgbench related locking. Yes,
apparently postgresql uses a synchronization scheme that uses select()
to effect delays for backing off while attempting to acquire a lock.
However, it seems to me that runqueue lock contention was not entirely due
to postgresql code, since it was largely alleviated by the multiqueue
scheduler patch.

Im not saying that the multiqueue scheduler patch isn't needed, just that
this test case is caused by a bug in postgres. We shouldn't run around
fixing symptoms - dropping the contention in the runqueue lock might not
change the overall performance of the benchmark, on the other hand

fixing the spinlocks in postgres probably will.

Might be worth a look from core members to see if there really are
issues here, the thread is about 8 msgs.

Best Regards,
Carl Garland
On the other hand, if postgres still pounds on the runqueue lock after
the bug has been fixed then we need to look at the multiqueue patch.

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com

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

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.

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

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

Franck Martin wrote:

I think OID should be truly unique in the world as to make it easier for
replication. If OID are real unique number (not in a table, not in a
database, but in the world) then replication can be easily built with
OIDs...

Exactly! That is what the Mariposa project did - they made OIDs uniqe
and
consisting of 32bit site id + 32bit local OID. I guess this could be
split
some other way too, like 20 bit site id + 44bit local or any other.

IMHO the best would be a scheme of 32bit site id + 32bit local, but each
site can get additional site ids from some central (for a supersite)
table
when it sees that it is near runnig out of oids.

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

I think OID should be truly unique in the world as to make it easier for
replication. If OID are real unique number (not in a table, not in a
database, but in the world) then replication can be easily built with
OIDs...

Cheers.

Franck Martin
Network and Database Development Officer
SOPAC South Pacific Applied Geoscience Commission
Fiji
E-mail: franck@sopac.org <mailto:franck@sopac.org>
Web site: http://www.sopac.org/
<http://www.sopac.org/&gt; Support FMaps: http://fmaps.sourceforge.net/
<http://fmaps.sourceforge.net/&gt;

This e-mail is intended for its addresses only. Do not forward this e-mail
without approval. The views expressed in this e-mail may not be necessarily
the views of SOPAC.

-----Original Message-----
From: Lincoln Yeoh [mailto:lyeoh@pop.jaring.my]
Sent: Monday, 14 May 2001 3:45
To: Bruce Momjian; PostgreSQL-development
Subject: [HACKERS] Re: 7.2 items

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?

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

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.

---------------------------(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

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

#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:

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.

Hannu Krosing wrote:

Franck Martin wrote:

I think OID should be truly unique in the world as to make it easier for
replication. If OID are real unique number (not in a table, not in a
database, but in the world) then replication can be easily built with
OIDs...

Exactly! That is what the Mariposa project did - they made OIDs uniqe
and
consisting of 32bit site id + 32bit local OID. I guess this could be
split
some other way too, like 20 bit site id + 44bit local or any other.

IMHO the best would be a scheme of 32bit site id + 32bit local, but each
site can get additional site ids from some central (for a supersite)
table
when it sees that it is near runnig out of oids.

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

As I'm thinking about it there is a utility called uuidgen which generates
such numbers.

On my Mandrake distro it is part of the e2fsprogs package. Orbit uses it to
generate unique numbers too.

--------------
The uuidgen program creates a new universally unique identifier (UUID)
using the libuuid(3)
library. The new UUID can reasonably be considered unique among all
UUIDs created on the
local system, and among UUIDs created on other systems in the past and
in the future.

There are two types of UUID's which uuidgen can generate: time-based
UUID's and random-based
UUID's. By default uuidgen will generate a random-based UUID if a
high-quality random number
generator is present. Otherwise, it will chose a time-based UUID.
It is possible to force
the generation of one of these two UUID types by using the -r or -t
options.

The UUID of the form 1b4e28ba-2fa1-11d2-883f-b9a761bde3fb (in
printf(3) format
"%08x-%04x-%04x-%04x-%012x") is output to the standard output.
-------------------

Cheers.
Franck@sopac.org

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

* 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

Franck Martin <franck@sopac.org> writes:

The uuidgen program creates a new universally unique identifier (UUID)
using the libuuid(3)
library. The new UUID can reasonably be considered unique among all
UUIDs created on the
local system, and among UUIDs created on other systems in the past and
in the future.

"Reasonably considered"?

In other words, this is a 64-bit random number generator. Sorry, I
think the odds of collision would be uncomfortably high if we were to
use such a thing for OIDs ... certainly so on installations that are
worried about running out of 32-bit OIDs. It sounds to me like uuidgen
is built on the assumption that only relatively small numbers of IDs
will be demanded from it.

regards, tom lane

Franck Martin wrote:

I think OID should be truly unique in the world as to make it easier for
replication. If OID are real unique number (not in a table, not in a
database, but in the world) then replication can be easily built with
OIDs...

The Apache server has a UNIQUE_ID implementation and it is really
unique in the world, I use it for my web apps. Their implementation
is really simple an works fine. It is 19 alphanumeric bytes long.

Regards,

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
-----------------------------------------------------------------

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

Tom Lane <tgl@sss.pgh.pa.us> writes:

Franck Martin <franck@sopac.org> writes:

The uuidgen program creates a new universally unique identifier (UUID)
using the libuuid(3)
library. The new UUID can reasonably be considered unique among all
UUIDs created on the
local system, and among UUIDs created on other systems in the past and
in the future.

"Reasonably considered"?

In other words, this is a 64-bit random number generator. Sorry, I
think the odds of collision would be uncomfortably high if we were to
use such a thing for OIDs ... certainly so on installations that are
worried about running out of 32-bit OIDs. It sounds to me like uuidgen
is built on the assumption that only relatively small numbers of IDs
will be demanded from it.

uuidgen with the -t option generates a UUID which includes the current
time and the Ethernet hardware address. The value is about as
globally unique as it is possible to create in 128 bits. The same
algorithm is used by DCE, and a variant is used by DCOM. To be used
properly, you need to coordinate on one machine to ensure that
different processes on that machine don't generate the same UUID.

Here is a description:
http://www.opengroup.org/onlinepubs/9629399/apdxa.htm#tagcjh_20

Ian

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

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

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

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

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

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

* 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/

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

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

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

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

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.

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.

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

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)---------------------------

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';

And in addition,

If you submitted the query:

SELECT * FROM addresses WHERE state = 'OH'
AND areacode = '614'

Then, with bitmap indexes, the bitmaps are just logically ANDed
together, and the final bitmap determines the matching rows.

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From: mlw [SMTP:markw@mohawksoft.com]

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';

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

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

And in addition,

If you submitted the query:

SELECT * FROM addresses WHERE state = 'OH'
AND areacode = '614'

Then, with bitmap indexes, the bitmaps are just logically ANDed
together, and the final bitmap determines the matching rows.

Message added to TODO.detail. The use of AND seems like a very nice
optimization possible with bitmap indexes. Thanks.

-- 
  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

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

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

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

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

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

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

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

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

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

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 |/

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