[ADMIN] Broken index? (the revenge)
I don't think this reached the list, so I thought I'd send it again...
On 2001.09.11 14:35 Paul Green wrote:
Hi again,
Ok, for those of you who have been following my troubles with my indexes on
my site, this may interest you. I'd also appreciate any help once again,
because now I am completely foxed. It looks like shortly I will be
upgrading to 7.1.3 in a hope that this error will go away, but I'd be
interested to know why this is happening in 7.0 and whether it still
happens in 7.1.3.
In response to advice to reconstruct the table and reindex, I did the
following in psql:
--
player_stats=> CREATE SEQUENCE player1_serial START 1;
player_stats=> create table player1 (id int4 default
nextval('player1_serial'::text) NOT NULL, name varchar(50) not null,
password varchar(50), icqnumber varchar(20), emailaddress varchar(255),
flatname varchar(50), PRIMARY KEY (id));
player_stats=> create unique index player1_name_key on player1 using btree
(name);
--
I then ran the java program below using the postgres jdbc driver
(jdbc7.0-1.2.jar):
--
import java.io.*;
import java.lang.*;
import java.sql.*;
public class RecreatePlayer {
private final static boolean DEBUG=true;
public static void main(String args[]) {
try
{
DBConn db = new DBConn("/home/httpd/conf/DBConn.conf");
String SQLString = "SELECT * FROM Player";
ResultSet rs = db.doSQL(SQLString);
int counter=0;
int failed=0;
while(rs.next()) {
counter++;
if(DEBUG) System.out.println("INSERTING
PLAYER "+counter+": "+rs.getString("name"));
SQLString = "INSERT INTO player1 (id,name,flatname)
VALUES
("+rs.getString("id")+",'"+replaceChars(rs.getString("name")).trim()+"',LOWER('"+replaceChars(rs.getString("name")).trim()+"'))";
if (!db.doSQLUpdate(SQLString))
failed++;
}
System.out.println(counter+" players inserted");
System.out.println(failed+" players FAILED to be
inserted");
}
catch(SQLException e)
{
System.err.println("SQLException: "+e);
}
}
private static String replaceChars(String input) {
if (input == null)
return "";
StringBuffer tempString = new StringBuffer();
int x=0;
while(x < input.length()) {
if(input.charAt(x) == '\'') tempString.append('\'');
tempString.append(input.charAt(x));
x++;
}
return tempString.toString().trim();
}
}
NOTE: doSQLUpdate fragment :=
Statement stmt = conn.createStatement();
stmt.setQueryTimeout(120);
if(stmt.executeUpdate(sqlStr) > 0)
return true;
else
return false;
--
The doSQLUpdate(String) simply asks the driver to use the current
connection and send the query returning true/false depending on whether it
was successful or unsuccessful. Unsuccessful attemps are usually to do with
the 'name' of the player already existing. After running this program, the
last output was:
--
177967 players inserted
611 players FAILED to be inserted
--
So, I thought the new index had done its job and kept duplicates out of the
system, until I executed these queries to check:
--
player_stats=> select count(distinct name) from player1;
count
176835
(1 row)
player_stats=> select count(name) from player1;
count
177356
(1 row)
--
As you can see the numbers are *different*. I tried to drop the index and
reaply using:
--
create unique index player1_name_key on player1 using btree (name);
ERROR: Cannot create unique index. Table contains non-unique values
--
But as you can see, I got an error message.
*ALL* of the records were inserted at the same time through the same
connection and these errors still occured, so there can't be a problem with
locale and such. I originaly thought that this error may be due to having
multiple non-unique inserts in a transaction, but each of these inserts was
added through its own transaction. Does anyone have any idea why this is
happening? Is it the JDBC driver, my installation, my version of pgsql? Any
help would be much appreciated!
Cheers!
--
Paul Green
Programmer
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Can't get postmaster to restart after removing WAL files
You can try running pg_resetxlog (situated in the contrib directory in the source). It will reset the wal files and will allow you to restart the postmaster. It is not the recommended way but I used in in dire emergencies and it worked. Normally, when you fill up the drive because of WALs, you should try to free up 16MB. If the drive is the same as the database, you can move one file from the database directory to another partition, create a soft link to the file, and restart the database. Once the WAL files are removed by the postmaster, you should stop it, and copy back the file. Try to upgrade to 7.1.3 since it solves some issues where long transactions take up an unordinated amount of space unecessary. As regards to the space required by the WAL files this depends on the configuration (postgresql.conf). There is a paramter which controls the amount of WAL files to create (each file takes 16MB). Hope it helps Robert ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Can't get postmaster to restart after removing WAL files
> You can try running pg_resetxlog (situated in the contrib directory in the > source). It will reset the wal files and will allow you to restart the > postmaster. I just did an initdb as suggested by Randy since the data was easy to recreate, but this is good to know as a fallback for future situations! > Try to upgrade > to 7.1.3 since it solves some issues where long transactions take up an > unordinated amount of space unecessary. I was running v7.1.3 & only had two 41Mb databases running in the cluster, which was why I was kinda' caught off guard by having 48Mb used in WAL files. > As regards to the space required by the WAL files this depends on the > configuration (postgresql.conf). There is a paramter which controls the > amount of WAL files to create (each file takes 16MB). This helps, but I'm a bit confused- What if I'm runnning an enormous update & I really need 4 WAL files, but I've set the limit at 3? Will an fsync be forced so that the first file will be deleted & the space reused? It sounds like I can limit the space used to match what I have available, but I'm not sure if this will cause a problem if I'm too stingy on my space allocation. Thanks Robert & others for your help with this! -Nick - Nick Fankhauser Business: [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788 doxpop - Court records at your fingertips - http://www.doxpop.com/ Personal: [EMAIL PROTECTED] http://www.fankhausers.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Another WAL question
Robert F. mentioned that there is a config parameter controlling the number of WAL files in postgresql.conf. I just looked at the Doc on this & I found WAL_FILES, which appears to specify the number of files to create in advance, but it is no clear to me that this limits the total number of WAL files created. Can this number be exceeded as needed by the postmaster? If so, is there another parameter that I'm missing to set a limit? Are old WAL files deleted or re-used automatically when all of their contents have been flushed to the DB, or should we be watching these & either archiving or deleting old ones? TIA, -Nick - Nick Fankhauser Business: [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788 doxpop - Court records at your fingertips - http://www.doxpop.com/ Personal: [EMAIL PROTECTED] http://www.fankhausers.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Can't get postmaster to restart after removing WAL files
This depends on the type of update. If you commit the transaction regularly, files will be re-used. On the other hand, if you commit the transaction at the end like what the COPY command (pg_dump) does, extra WAL files will be created as needed (even though the limit has been reached). Regards Robert "Nick Fankhauser" To: <[EMAIL PROTECTED]> com> Subject: RE: Can't get postmaster to restart after removing WAL files 12/09/2001 16:12 Please respond to nickf > You can try running pg_resetxlog (situated in the contrib directory in the > source). It will reset the wal files and will allow you to restart the > postmaster. I just did an initdb as suggested by Randy since the data was easy to recreate, but this is good to know as a fallback for future situations! > Try to upgrade > to 7.1.3 since it solves some issues where long transactions take up an > unordinated amount of space unecessary. I was running v7.1.3 & only had two 41Mb databases running in the cluster, which was why I was kinda' caught off guard by having 48Mb used in WAL files. > As regards to the space required by the WAL files this depends on the > configuration (postgresql.conf). There is a paramter which controls the > amount of WAL files to create (each file takes 16MB). This helps, but I'm a bit confused- What if I'm runnning an enormous update & I really need 4 WAL files, but I've set the limit at 3? Will an fsync be forced so that the first file will be deleted & the space reused? It sounds like I can limit the space used to match what I have available, but I'm not sure if this will cause a problem if I'm too stingy on my space allocation. Thanks Robert & others for your help with this! -Nick - Nick Fankhauser Business: [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788 doxpop - Court records at your fingertips - http://www.doxpop.com/ Personal: [EMAIL PROTECTED] http://www.fankhausers.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Another WAL question
In 7.1.3 and 7.2, there are only 2-3 WAL files kept because there is no need to keep them after a checkpoint. Is there any need to have these WAL config paramaters anymore? We currently have in postgresql.conf: #wal_buffers = 8# min 4 #wal_files = 0 # range 0-64 #wal_sync_method = fsync # fsync or fdatasync or open_sync or open_datasync # Note: default wal_sync_method varies across platforms #wal_debug = 0 # range 0-16 #commit_delay = 0 # range 0-10 #commit_siblings = 5 # range 1-1000 #checkpoint_segments = 3 # in logfile segments (16MB each), min 1 #checkpoint_timeout = 300 # in seconds, range 30-3600 > Robert F. mentioned that there is a config parameter controlling the number > of WAL files in postgresql.conf. > > I just looked at the Doc on this & I found WAL_FILES, which appears to > specify the number of files to create in advance, but it is no clear to me > that this limits the total number of WAL files created. Can this number be > exceeded as needed by the postmaster? If so, is there another parameter that > I'm missing to set a limit? > > Are old WAL files deleted or re-used automatically when all of their > contents have been flushed to the DB, or should we be watching these & > either archiving or deleting old ones? > > TIA, > > -Nick > > - > Nick Fankhauser > > Business: > [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788 > doxpop - Court records at your fingertips - http://www.doxpop.com/ > > Personal: > [EMAIL PROTECTED] http://www.fankhausers.com > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Another WAL question
> In 7.1.3 and 7.2, there are only 2-3 WAL files kept because there is no > need to keep them after a checkpoint. This answers my original question- it sounds like after 3, they get recycled or deleted, so if I reserve 48Mb (3*16) for these, I should be OK. ...Right? > Is there any need to have these > WAL config paramaters anymore? I'd say all of the parameters you list below still make sense, as they all control *how* the 2-3 WAL files are used. WAL_FILES might as well be 0-3 instead of 0-64, but I can still see an advantage to creating these in advance to allocate the space. > We currently have in postgresql.conf: > > #wal_buffers = 8# min 4 > #wal_files = 0 # range 0-64 > #wal_sync_method = fsync # fsync or fdatasync or open_sync or > open_datasync > # Note: default wal_sync_method varies across platforms > #wal_debug = 0 # range 0-16 > #commit_delay = 0 # range 0-10 > #commit_siblings = 5 # range 1-1000 > #checkpoint_segments = 3 # in logfile segments (16MB each), min 1 > #checkpoint_timeout = 300 # in seconds, range 30-3600 Thanks for the good answers on this! -Nick ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] problems using pg_dump and datestyle format
Hi all, I have changed the host I run PostgreSQL using pg_dump to move the data. Is there any issue related to datastyle or locales and pg_dump ?? I think that all dates on my columns got modified some how ! I used to make a query like set datestyle 'sql,european'; select * from mytbl where date < 'dd/mm/'; but that's not working anymore because I'm getting a PostgreSQL Error: 1 (ERROR: Bad date external representation '08/18/2001' ) please help ! -- Leandro Rodrigo Saad Cruz IT - Inter Business Tecnologia e Servicos (IB) begin:vcard n:Saad Cruz;Leandro x-mozilla-html:FALSE org:Inter Business;IT adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;0 fn:Leandro Saad Cruz end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [ADMIN] Another WAL question
> > In 7.1.3 and 7.2, there are only 2-3 WAL files kept because > > there is no need to keep them after a checkpoint. Is there > > any need to have these WAL config paramaters anymore? > > I missed what's you propose to remove. I am not proposing to remove anything. I just want to make sure they are all still valid now that we recycle the WAL segments. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[ADMIN] Another WAL question (sort of)
Two sort of WAL related questions 1) Is point-in-time recovery using a pg_dumpall and the associated WAL files still planned for 7.2? 2) When is 7.2 planned to be released? -crl -- Chad R. Larson (CRL22)[EMAIL PROTECTED] Eldorado Computing, Inc. 602-604-3100 5353 North 16th Street, Suite 400 Phoenix, Arizona 85016-3228 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Another WAL question (sort of)
> Two sort of WAL related questions > > 1) Is point-in-time recovery using a pg_dumpall and the associated WAL > files still planned for 7.2? No, sorry, hopefully 7.3. > > 2) When is 7.2 planned to be released? > -crl Nov/Dec? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
