Thread: Sorting a table...

Sorting a table...

From
Jesse Scott
Date:
Hello all, I'm new to this list so forgive me if this question get's asked
a lot. (I did search the archives...)

I have a table of users where I store some basic info along with all my
authentication stuff.  I wrote a web interface in PHP3 for viewing and
modifying this table, my PHP page just goes through the table sequentially
from row 0 till the end displaying a good portion of the info on each line.
 Then, using radio buttons you select the user and the desired action,
click a button and then do your editing.  Once the user has completed the
editing, I perform an UPDATE on that row with the new values, but now that
row is moved to the end of the table, making the page that lists the users
list them out of order (one of the fields is a sequential ID number).  Is
there anyway to do a "sort by" or something similar?  Or do I need to set
the ID number to be some sort of index type?

Any help would be appreciated.

TIA!

-Jesse


Re: [GENERAL] Sorting a table...

From
Ed Loehr
Date:
I think you're looking for the 'ORDER BY' clause to use in your SELECT, e.g.,

    SELECT * FROM person ORDER BY id

More examples, options, and explanation can be found at

    http://www.postgresql.org/docs/postgres/sql-select.htm

Cheers.
Ed Loehr

Jesse Scott wrote:

> ... Once the user has completed the
> editing, I perform an UPDATE on that row with the new values, but now that
> row is moved to the end of the table, making the page that lists the users
> list them out of order (one of the fields is a sequential ID number).  Is
> there anyway to do a "sort by" or something similar?  Or do I need to set
> the ID number to be some sort of index type?


Re: [GENERAL] Sorting a table...

From
Stan Jacobs
Date:
> Hello all, I'm new to this list so forgive me if this question get's asked
> a lot. (I did search the archives...)

A standard SQL reference might help a lot, since this is more in the
domain of SQL syntax than PostgreSQL help.


> list them out of order (one of the fields is a sequential ID number).  Is
> there anyway to do a "sort by" or something similar?  Or do I need to set
> the ID number to be some sort of index type?

Just add the 'ORDER BY' tag to the end of your query....

    e.g. 'SELECT * FROM users ORDER BY seq_id_number'

... and it will return the results sorted by seq_id_number.  (Of course,
replace this variable with whatever your sequential id number field is
actually called.)


    HTH,

    - Stan -



"FATAL 1: my bits moved right off the end of the world!"

From
Ed Loehr
Date:
I have run into this error message a few times now in various circumstances
(using pgsql v6.5.2 on Linux 2.2.5-15smp):

    "FATAL 1:  my bits moved right off the end of the world!"

Does anyone have a clue as to what scenarios cause this?  (It apparently comes
from line 506 of .../src/backend/access/nbtree/nbtpage.c)

I've had the luxury of being early in development and being able to rebuild my
DB to remove the symptom, but that will soon change.

Thanks in advance,
Ed Loehr


Re: [GENERAL] "FATAL 1: my bits moved right off the end of the world!"

From
Peter Eisentraut
Date:
This is getting to be our favourite error message...

It is caused by a corrupted B-Tree index. Drop and recreate that one.

On Tue, 30 Nov 1999, Ed Loehr wrote:

> I have run into this error message a few times now in various circumstances
> (using pgsql v6.5.2 on Linux 2.2.5-15smp):
>
>     "FATAL 1:  my bits moved right off the end of the world!"
>
> Does anyone have a clue as to what scenarios cause this?  (It apparently comes
> from line 506 of .../src/backend/access/nbtree/nbtpage.c)
>
> I've had the luxury of being early in development and being able to rebuild my
> DB to remove the symptom, but that will soon change.
>
> Thanks in advance,
> Ed Loehr
>
>
> ************
>
>

--
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Re: [GENERAL] "FATAL 1: my bits moved right off the end of the world!"

From
Bruce Momjian
Date:
> I have run into this error message a few times now in various circumstances
> (using pgsql v6.5.2 on Linux 2.2.5-15smp):
>
>     "FATAL 1:  my bits moved right off the end of the world!"
>
> Does anyone have a clue as to what scenarios cause this?  (It apparently comes
> from line 506 of .../src/backend/access/nbtree/nbtpage.c)
>
> I've had the luxury of being early in development and being able to rebuild my
> DB to remove the symptom, but that will soon change.

Drop index and recreate.  7.0 will say that specifically.

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

Re: [GENERAL] "FATAL 1: my bits moved right off the end of theworld!"

From
Ed Loehr
Date:
Peter Eisentraut wrote:

> This is getting to be our favourite error message...
>
> It is caused by a corrupted B-Tree index. Drop and recreate that one.

Thanks.  Unfortunately, the lack of context to the error message makes it difficult
to identify which index is "that one."  The message was last showing up during the
process of dropping/recreating a series of triggers and functions via "psql -f"
without any table inserts/updates.

Cheers.
Ed Loehr

>
>
> On Tue, 30 Nov 1999, Ed Loehr wrote:
>
> > I have run into this error message a few times now in various circumstances
> > (using pgsql v6.5.2 on Linux 2.2.5-15smp):
> >
> >     "FATAL 1:  my bits moved right off the end of the world!"
> >
> > Does anyone have a clue as to what scenarios cause this?  (It apparently comes
> > from line 506 of .../src/backend/access/nbtree/nbtpage.c)
> >
> > I've had the luxury of being early in development and being able to rebuild my
> > DB to remove the symptom, but that will soon change.
> >
> > Thanks in advance,
> > Ed Loehr
> >
> >
> > ************
> >
> >
>
> --
> Peter Eisentraut                  Sernanders vaeg 10:115
> peter_e@gmx.net                   75262 Uppsala
> http://yi.org/peter-e/            Sweden
>
> ************


Re: [GENERAL] "FATAL 1: my bits moved right off the end of theworld!"

From
Bruce Momjian
Date:
> Peter Eisentraut wrote:
>
> > This is getting to be our favourite error message...
> >
> > It is caused by a corrupted B-Tree index. Drop and recreate that one.
>
> Thanks.  Unfortunately, the lack of context to the error message makes it difficult
> to identify which index is "that one."  The message was last showing up during the
> process of dropping/recreating a series of triggers and functions via "psql -f"
> without any table inserts/updates.
>

I have fixed 7.0 so it will show the index name.

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

Re: [GENERAL] "FATAL 1: my bits moved right off the end of theworld!"

From
Ed Loehr
Date:
Bruce Momjian wrote:

> > Peter Eisentraut wrote:
> >
> > > This is getting to be our favourite error message...
> > >
> > > It is caused by a corrupted B-Tree index. Drop and recreate that one.
> >
> > Thanks.  Unfortunately, the lack of context to the error message makes it difficult
> > to identify which index is "that one."  The message was last showing up during the
> > process of dropping/recreating a series of triggers and functions via "psql -f"
> > without any table inserts/updates.
>
> I have fixed 7.0 so it will show the index name.

It sounds like this B-tree index corruption happens often enough to gain fame.  I am
curious as to what the full impact of this is on a production database.  Would I be
correct in assuming are consequences are (1) an unusable index, resulting in (2)
inaccessible data, resulting in (3) an unusable database until the index is dropped and
recreated?

If the least drastic corrective solution is to drop and recreate the index, that leaves
me wondering:
    1) Does anyone know what is causing the corrupted B-tree?
    2) How difficult would it be to automate the process of index rebuilding at the
point the corruption is detected?  How could that be done otherwise in an automated
fashion?
    3) What are other people doing to deal with this?

Cheers.
Ed


Re: [GENERAL] "FATAL 1: my bits moved right off the end of theworld!"

From
Ed Loehr
Date:
Answering part of my own questions, it appears the latest thinking on this problem (in
http://www.deja.com/getdoc.xp?AN=542994084) is that it stems from indexed columns greater
than half a page (~4K), and/or a previous insert having been interrupted by elog(ERROR) or
a backend crash.  The latter explanation seems more plausible, as I do not have any
columns approaching 512 bytes, much less 4K.  Glad to hear it may be coming from an error
rather than normal usage, though it'd be nice to optionally have corrupted indices
automatically rebuilt.

Cheers.
Ed

Ed Loehr wrote:

> Bruce Momjian wrote:
>
> > > Peter Eisentraut wrote:
> > >
> > > > This is getting to be our favourite error message...
> > > >
> > > > It is caused by a corrupted B-Tree index. Drop and recreate that one.
> > >
> > > Thanks.  Unfortunately, the lack of context to the error message makes it difficult
> > > to identify which index is "that one."  The message was last showing up during the
> > > process of dropping/recreating a series of triggers and functions via "psql -f"
> > > without any table inserts/updates.
> >
> > I have fixed 7.0 so it will show the index name.
>
> It sounds like this B-tree index corruption happens often enough to gain fame.  I am
> curious as to what the full impact of this is on a production database.  Would I be
> correct in assuming are consequences are (1) an unusable index, resulting in (2)
> inaccessible data, resulting in (3) an unusable database until the index is dropped and
> recreated?
>
> If the least drastic corrective solution is to drop and recreate the index, that leaves
> me wondering:
>     1) Does anyone know what is causing the corrupted B-tree?
>     2) How difficult would it be to automate the process of index rebuilding at the
> point the corruption is detected?  How could that be done otherwise in an automated
> fashion?
>     3) What are other people doing to deal with this?
>
> Cheers.
> Ed


Re: [GENERAL] "FATAL 1: my bits moved right off the end of theworld!"

From
"Moray McConnachie"
Date:
With all this talk of rebuilding indices, the following script does it
for me:

#!/bin/sh
pg_dump -s databasename > filename
perl -pi -e 'unless (/CREATE.*INDEX/) {s/.*//;chomp;}' filename
perl -pi.create -e 's/CREATE.*?INDEX(.*?\s)ON.*/DROP INDEX$1\;/i;'
filename
mv filename filename.drop
psql -d databasename -c '\i filename.drop'
psql -d databasename -c '\i filename.create'

Of course there are bound to be a million ways of scripting this
without perl, but using awk or similar.

You could cron this up - I don't know if regular rebuilding
of indices helps minimise corruption?

Yours,
Moray

----------------------------------------------------------------------
----------------
Moray.McConnachie@computing-services.oxford.ac.uk


Attachment

Re: [GENERAL] "FATAL 1: my bits moved right off the end of theworld!"

From
Ed Loehr
Date:
Thanks.  As your example demonstrates, automating a rebuild of indices via cron/perl/DBI is pretty straighforward.
Unfortunately, reliability and 
usability concerns in my application strongly suggest that the corrupted index really needs to be identified and
rebuiltin real-time to allow the 
original failed queries to be automatically retried to success, albeit a bit slower.  Taking the system off-line to
rebuildthe corrupted index is *very* 
undesirable.  If I could get a true error indication in real-time from DBI/DBD::Pg that this was the problem, I think I
couldrebuild the index on the 
fly.  Not sure enough PG error information and/or codes are being returned to sufficiently identify the problem,
though.

Cheers.
Ed

Moray McConnachie wrote:

> With all this talk of rebuilding indices, the following script does it
> for me:
>
> #!/bin/sh
> pg_dump -s databasename > filename
> perl -pi -e 'unless (/CREATE.*INDEX/) {s/.*//;chomp;}' filename
> perl -pi.create -e 's/CREATE.*?INDEX(.*?\s)ON.*/DROP INDEX$1\;/i;'
> filename
> mv filename filename.drop
> psql -d databasename -c '\i filename.drop'
> psql -d databasename -c '\i filename.create'
>
> Of course there are bound to be a million ways of scripting this
> without perl, but using awk or similar.
>
> You could cron this up - I don't know if regular rebuilding
> of indices helps minimise corruption?
>
> Yours,
> Moray
>
> ----------------------------------------------------------------------
> ----------------
> Moray.McConnachie@computing-services.oxford.ac.uk
>
>   ----------------------------------------------------------------------
>                                                                             Name: Re [GENERAL] FATAL 1 my bits moved
rightoff the end of theworld!.txt 
>    Re [GENERAL] FATAL 1 my bits moved right off the end of theworld!.txt    Type: Plain Text (text/plain)
>                                                                         Encoding: quoted-printable