Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Show stopper ? (was: Re: "cruising" or "browsing" through tables using an index / ordering)

From: The Hermit Hacker <scrappy(at)hub(dot)org>
To: Jan Vicherek <honza(at)ied(dot)com>
Cc: Dustin Sallings <dustin(at)spy(dot)net>, pgsql-questions(at)postgreSQL(dot)org, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] Show stopper ? (was: Re: "cruising" or "browsing" through tables using an index / ordering)
Date: 1998-01-24 01:32:03
Message-ID: Pine.BSF.3.96.980123211013.28536f-100000@thelab.hub.org (view raw or flat)
Thread:
Lists: pgsql-hackers
On Fri, 23 Jan 1998, Jan Vicherek wrote:

> On Fri, 23 Jan 1998, The Hermit Hacker wrote:
> 
> > 	I'm curious, but can the "Big (commercial) Boys" do this?  If so,
> > can you please provide an example of which and how?
> 
>     Hmm, well, the one we are switching from does this ;-) (Informix 3.3
> ALL-II C interface). It's not SQL, tho.

	Okay, well, ummm...now you are comparing apples to oranges
then...if you wanted a non-SQL engine to replace Informix, PostgreSQL
isn't what you are looking for :(

> > all I've seen is ... and several examples on how to accomplish it using
> > PostgreSQL, but that's it... 
> 
>    Wait, have you seen here an example that accomplishes this which
> wouldn't need the whole table copied and wouldn't lock the table against
> updates ?

	First off, *nothing* you are going to be able to accomplish in
*any* SQL engine is going to prevent locking the table against
updates...the code that Bruce put in this afternoon for v6.3 is going to
reduce the possibility of the lock causing a deadlock, but that is about
it...the lock will still be created.

> > 	Why not put a sequence field on the table so that you can do:
> > 	select * from table where rowid = n;     -or-
> > 	select * from table where rowid = n - 1; -or-
> > 	select * from table where rowid = n + 1; -or-
> > 	select * from table where rowid >= n and rowid <= n+x;
> > 
> > 	And create the index on rowid?
> 
>    Because I also need to be able to INSERT rows.  That would require
> renumeration of half the table (remember, it's 40MB, 400,000 rows) every
> time I do an INSERT. 

	Okay, you are confusing INSERT then...INSERT in SQL just adds a
row, that's it...it doesn't perform any "sorting" on it...that's what the
ORDER BY command does...

	...but, I now understand what *you* mean by INSERT...

>   I *still* think that there *has to* be a way to find a value that is
> immediatelly next to one I have. This seems like such a primitive
> operation. Even the backend must be doing it on some operations, it would
> seem.

	Not possible...INSERT into a table doesn't "merge" the record
between its lower/higher bounds...it just adds it to the very end of the
table.  And an index doesn't "sort" the data either...that is what the
ORDER BY clause is for...

>  Maybe even in SQL. Maybe something like (I'm not an SQL expert) : "SELECT
> IndexField from MyTable where InxdexField > 'my_current_value' and
> IndexField < ("all IndexFields that are bigger than the IndexField
> searched for")

	From your sample above, is your first SQL call going to pull out
all 40MB of data in one select statement, then your second 40MB minus the
first X records?  

	What you want to do is:

begin
  declare mycursor cursor for select * from pg-user order by <somefield>;
  move $forward  in FOO;
  fetch $retrieve in FOO;
  close foo;
end;

	Basically, take your table, move to the $forward record in it,
grab the next $retrieve records and then release the table.

	Your first time through, $forward might just equal 0...but when
you run it the second time through, you pass it back a $forward value
equal to $forward + $retrieve, so that you start at where you ended the
first time.  This is how I deal with one of my projects, where I have to
do exactly what you are looking for...

	About the only part of the SELECT above is the ORDER BY, and alot
of that is as much a restriction on your hardware then anything...the
major performance boost in an ORDER BY is memory..keeping it off the hard
drive.
	
>   Important : I'm not looking for a "pure SQL" solution. I'm writing a C
> emulation library, so if it can be achieved via a call to a C Postgres
> function, it would be great.

	You'd be better off looking at something like GDBM (which, by the
way, also creates a lock against updates while another is reading the
database)...unless I'm missing something, you aren't looking at doing
anything that *requires* an SQL engine :(

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy(at)hub(dot)org           secondary: scrappy(at){freebsd|postgresql}.org 



In response to

Responses

pgsql-hackers by date

Next:From: Jan VicherekDate: 1998-01-24 03:41:50
Subject: Attn PG gurus / coders : New approach for ORDER BY ? (was: Re: Show stopper ?)
Previous:From: Jan VicherekDate: 1998-01-23 23:19:28
Subject: Re: [HACKERS] Show stopper ? (was: Re: "cruising" or "browsing" through tables using an index / ordering)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group