Обсуждение: Sorting a table...
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
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?
> 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 -
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
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
> 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
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 > > ************
> 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
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
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
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
Вложения
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