indexes spanning multiple tables
Is anybody working on allowing indexes to span multiple tables?
IF not, I'll give it a try.
--
Make April 15 just another day, visit http://fairtax.org
Joshua N Pritikin <jpritikin@pobox.com> writes:
Is anybody working on allowing indexes to span multiple tables?
IF not, I'll give it a try.
Wouldn't recommend it as a project for a beginning backend hacker;
the locking considerations alone are a bit daunting.
See the archives for prior discussions.
regards, tom lane
while you weren't looking, Tom Lane wrote:
[indexes spanning multiple tables]
Wouldn't recommend it as a project for a beginning backend hacker;
the locking considerations alone are a bit daunting.
That being the case, is there a list anywhere of open/wish list/TODO
items that are suitable for beginning pg hackers? I've been over the
TODO list and found myself fairly daunted by what I see, but would
still like to take a stab at contributing.
/rls
--
:wq
Rosser Schwarz wrote:
while you weren't looking, Tom Lane wrote:
[indexes spanning multiple tables]
Wouldn't recommend it as a project for a beginning backend hacker;
the locking considerations alone are a bit daunting.That being the case, is there a list anywhere of open/wish list/TODO
items that are suitable for beginning pg hackers? I've been over the
TODO list and found myself fairly daunted by what I see, but would
still like to take a stab at contributing.
A couple of nice visible projects on the TODO list that might be
suitable for beginners:
. Add "include file" functionality in postgresql.conf
. Remove Money type, add money formatting for decimal type
But actually, the best place to start is possibly doing cleanups.
For example, gcc version 4 generates LOTS of compiler warnings. They
need clearing up. Doing that might lead to yuo look at quite a lot of
interesting code, which in turn might lead to more projects.
Plus there are always tests and docs to write ;-)
cheers
andrew
On E, 2005-08-22 at 16:01 -0400, Tom Lane wrote:
Joshua N Pritikin <jpritikin@pobox.com> writes:
Is anybody working on allowing indexes to span multiple tables?
IF not, I'll give it a try.Wouldn't recommend it as a project for a beginning backend hacker;
the locking considerations alone are a bit daunting.See the archives for prior discussions.
What could perhaps be within reach of a very determined and brilliant
beginning backend hacker :) would be an index_merge_scan or
dual_index_scan access method, which sits atop of two index scans and
fetches always the smallest one, thus enabling getting tuples in index
order from two UNION'ed tables with indexes on ordering column (or just
getting the top/bottom tuple for things like max()/min() optimisation;
I mean things like this
SELECT A FROM T1
UNION
SELECT A FROM T2
ORDER BY A
LIMIT 12;
or
CREATE TABLE TP (I SERIAL PRIMARY KEY);
CREATE TABLE TC1 () INHERITS (TP);
CREATE TABLE TC2 () INHERITS (TP);
CREATE TABLE TC3 () INHERITS (TP);
SELECT MAX(I) FROM TP;
The latter could be then made to produce the following plan
QUERY PLAN
-------------------------------------------------------------------------------
Limit
-> Index Merge Scan
-> Index Scan using tc1_pkey on tc1
-> Index Merge Scan
-> Index Scan using tc2_pkey on tc2
-> Index Scan using tc3_pkey on tc3
Together with Partition Elimination this could be used in data
warehousing for queries like 'ten most expensive sales during 1st
querter' so I CC: this to bizgres list too, which is incidentally
another list where multi-table indexes are sometimes discussed.
But I'm afraid that tweaking the planner and optimizer to use this new
access method will probably not be within powers of even a determined
and brilliant _beginning_ backend hacker :(
--
Hannu Krosing <hannu@skype.net>
If someone wants to mark easy items on the TODO list with some mark,
like %, I can apply the patch. Please patch TODO and not TODO.html.
---------------------------------------------------------------------------
Andrew Dunstan wrote:
Rosser Schwarz wrote:
while you weren't looking, Tom Lane wrote:
[indexes spanning multiple tables]
Wouldn't recommend it as a project for a beginning backend hacker;
the locking considerations alone are a bit daunting.That being the case, is there a list anywhere of open/wish list/TODO
items that are suitable for beginning pg hackers? I've been over the
TODO list and found myself fairly daunted by what I see, but would
still like to take a stab at contributing.A couple of nice visible projects on the TODO list that might be
suitable for beginners:. Add "include file" functionality in postgresql.conf
. Remove Money type, add money formatting for decimal typeBut actually, the best place to start is possibly doing cleanups.
For example, gcc version 4 generates LOTS of compiler warnings. They
need clearing up. Doing that might lead to yuo look at quite a lot of
interesting code, which in turn might lead to more projects.Plus there are always tests and docs to write ;-)
cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Mon, Aug 22, 2005 at 05:31:04PM -0400, Andrew Dunstan wrote:
A couple of nice visible projects on the TODO list that might be
suitable for beginners:. Add "include file" functionality in postgresql.conf
. Remove Money type, add money formatting for decimal type
Actually they are both bad projects. The "include file" patch was
submitted by the @mohawksoft guy whose name I can't remember; it was
rejected with good reasons. The money type was proposed for removal
some time ago, and the author also asked not to.
But actually, the best place to start is possibly doing cleanups.
For example, gcc version 4 generates LOTS of compiler warnings. They
need clearing up.
Yeah, please do.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Es fil�sofo el que disfruta con los enigmas" (G. Coli)
On Mon, Aug 22, 2005 at 05:31:04PM -0400, Andrew Dunstan wrote:
Rosser Schwarz wrote:
while you weren't looking, Tom Lane wrote:
[indexes spanning multiple tables]
Wouldn't recommend it as a project for a beginning backend hacker;
the locking considerations alone are a bit daunting.That being the case, is there a list anywhere of open/wish list/TODO
items that are suitable for beginning pg hackers? I've been over the
TODO list and found myself fairly daunted by what I see, but would
still like to take a stab at contributing.A couple of nice visible projects on the TODO list that might be
suitable for beginners:. Add "include file" functionality in postgresql.conf
. Remove Money type, add money formatting for decimal typeBut actually, the best place to start is possibly doing cleanups.
For example, gcc version 4 generates LOTS of compiler warnings. They
need clearing up. Doing that might lead to yuo look at quite a lot of
interesting code, which in turn might lead to more projects.Plus there are always tests and docs to write ;-)
Can someone turn these items into a "beginning hacker's TODO" as has
been discussed before? Or find a way to mark them on the main TODO?
If someone wants to tell me how this should be done and give me whatever
files need to be changed I'd be happy to submit a patch.
BTW, is the HTML for the website not maintained in CVS somewhere?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461
Alvaro Herrera wrote:
On Mon, Aug 22, 2005 at 05:31:04PM -0400, Andrew Dunstan wrote:
A couple of nice visible projects on the TODO list that might be
suitable for beginners:. Add "include file" functionality in postgresql.conf
. Remove Money type, add money formatting for decimal typeActually they are both bad projects. The "include file" patch was
submitted by the @mohawksoft guy whose name I can't remember; it was
rejected with good reasons. The money type was proposed for removal
some time ago, and the author also asked not to.
If the idea is bad it should be knocked off the list. If the
implementation was bad it might well be a reason someone else should
have a go.
Last I recall on money, the only issue preventing its removal was that
numeric formatting wasn't good enough.
cheers
andrew
On Mon, 2005-08-22 at 15:24 -0500, Rosser Schwarz wrote:
while you weren't looking, Tom Lane wrote:
[indexes spanning multiple tables]
Wouldn't recommend it as a project for a beginning backend hacker;
the locking considerations alone are a bit daunting.That being the case, is there a list anywhere of open/wish list/TODO
items that are suitable for beginning pg hackers? I've been over the
TODO list and found myself fairly daunted by what I see, but would
still like to take a stab at contributing.
Utility commands (CREATE, ALTER, DROP) seem to be the easiest to deal
with since they are pretty much self contained:
* Allow TRUNCATE ... CASCADE/RESTRICT
* Add a separate TRUNCATE permission
* Add COMMENT ON for all cluster global objects (roles, databases
and tablespaces)
* Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME
* Have ALTER TABLE RENAME rename SERIAL sequence names
Another source of items on the TODO list is the Unsupported Features
portion of the SQL Conformance documentation:
http://www.postgresql.org/docs/8.0/interactive/unsupported-features-sql-standard.html
Identity and Generator support or possibly simple Assertions shouldn't
have too significant of a learning curve to implement. The amount of
work may be large but you don't need to dig into the difficult to do
right bits of code like the optimizer.
Actually they are both bad projects. The "include file" patch was
submitted by the @mohawksoft guy whose name I can't remember; it was
rejected with good reasons. The money type was proposed for removal
some time ago, and the author also asked not to.
Well the money type seems it should be a domain around numeric but other
than that...
Last I recall on money, the only issue preventing its removal was that
numeric formatting wasn't good enough.
As I continue to read the email .... ;) Is it good enough now?
Sincerely,
Joshua D. Drake
cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
Hello,
Would work on one of the pl languages constitute a good place for a
beginning hacker to start?
plPerl, plPython, plRuby, and plPHP all need support for IN/OUT
parameters I believe.
Sincerely,
Joshua D. Drake
--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
Jim C. Nasby wrote:
Can someone turn these items into a "beginning hacker's TODO" as has
been discussed before? Or find a way to mark them on the main TODO?If someone wants to tell me how this should be done and give me whatever
files need to be changed I'd be happy to submit a patch.
Sure, submit a diff against doc/TODO and mark them with something like
%.
BTW, is the HTML for the website not maintained in CVS somewhere?
TODO.html is generated from doc/TODO automatically by me.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073On Mon, Aug 22, 2005 at 05:53:14PM -0400, Bruce Momjian wrote:
If someone wants to mark easy items on the TODO list with some mark,
like %, I can apply the patch. Please patch TODO and not TODO.html.
I'll take a stab at this unless someone else beats me to it; though I'm
not a coder myself so there will probably be some clean-up required.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461
Joshua D. Drake wrote:
Actually they are both bad projects. The "include file" patch was
submitted by the @mohawksoft guy whose name I can't remember; it was
rejected with good reasons. The money type was proposed for removal
some time ago, and the author also asked not to.Well the money type seems it should be a domain around numeric but other
than that...
I don't think you can do special output with DOMAIN.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Joshua D. Drake wrote:
Hello,
Would work on one of the pl languages constitute a good place for a
beginning hacker to start?plPerl, plPython, plRuby, and plPHP all need support for IN/OUT
parameters I believe.
Probably need named parameter support first, I suspect.
But I also suspect it's not beginner material - certainly the perl XS
API is daunting dunno about the others.
cheers
andrew
* Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME
That one is easy and handy.
Chris
On Mon, Aug 22, 2005 at 04:01:04PM -0400, Tom Lane wrote:
Joshua N Pritikin <jpritikin@pobox.com> writes:
Is anybody working on allowing indexes to span multiple tables?
IF not, I'll give it a try.Wouldn't recommend it as a project for a beginning backend hacker;
the locking considerations alone are a bit daunting.
OK but since I'm not convinced that I can't succeed, I'm going to
continue working on a patch.
--
Make April 15 just another day, visit http://fairtax.org
On Mon, Aug 22, 2005 at 06:48:52PM -0400, Rod Taylor wrote:
Another source of items on the TODO list is the Unsupported Features
portion of the SQL Conformance documentation:http://www.postgresql.org/docs/8.0/interactive/unsupported-features-sql-standard.html
Maybe we should just have a generic link from TODO to that info? It
doesn't seem to make sense to keep the same info in two places...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461
On Mon, Aug 22, 2005 at 07:09:10PM -0400, Bruce Momjian wrote:
Jim C. Nasby wrote:
Can someone turn these items into a "beginning hacker's TODO" as has
been discussed before? Or find a way to mark them on the main TODO?If someone wants to tell me how this should be done and give me whatever
files need to be changed I'd be happy to submit a patch.Sure, submit a diff against doc/TODO and mark them with something like
%.
Here's my stab at marking items. I picked items that I thought would
either be well-contained or that would be pretty straightforward. But
since I'm not very familiar with the code itself a lot of these could be
way off-base. I tried to err on the side of marking things that might be
boarderline since presumably it's easier for someone to see a marked
item and veto it rather than look at the entire list and try and find
new items. In any case, it wouldn't hurt for someone to make another
pass after this is applied and look for easy items that I missed.
BTW, while I was doing this it struck me that it might make sense to
have a difficulty ranking of, say 1-5, instead of just marking beginner
items. Thoughts?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461
