Re: [ADMIN] Changing column modifiers?
ALTER TABLE authors ADD CHECK (NOT authorfirstname = NULL); Should that be: ALTER TABLE authors ADD CHECK (NOT authorfirstname IS NULL); ? Anyway, how about something like the following: UPDATE pg_attribute SET attnotnull='t' WHERE attname='field_name' AND attrelid in (SELECT pg_class.oid FROM pg_class WHERE relkind='r' AND relname='table_name'); Where field_name is the field name and table_name is the table name. Hope this helps, - Stuart > -Original Message- > From: Arne Weiner [SMTP:[EMAIL PROTECTED]] > Sent: Monday, September 10, 2001 10:06 AM > To: [EMAIL PROTECTED] > Subject: Re: Changing column modifiers? > > > It seems that it is not possible to alter column constraints and > table constraints do not include NOT NUL. > But you can use CHECK as workaround: > > ALTER TABLE authors ADD CHECK (NOT authorfirstname = NULL); > > Arne. > > Tim Boring wrote: > > > > Can you change a column modifier after you've created the table? For > > example, I have a table called "authors" with the following columns: > > authorid, authorfirstname, authorlastname, authormi, statecode, country, > > born, deceased. > > > > When I created the table, I forgot to make the "authorfirstname" and > > "authorlastname" columns NOT NULL. I've looked at ALTER TABLE but I > > don't see a way to use it to add NOT NULL...maybe I'm just missing the > > obvious? > > > > Any help/suggestions are appreciated. > > > > Thanks, > > Tim > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Can't get postmaster to restart after removing WAL files
Hi all- I caused a crash by filling up the filesystem that pg_xlog lives on whilst doing a vacuum analyze. When I looked at the remains, there were 3 WAL files out there & no space left. I tried simply restarting (/etc/init.d/postgresql start) in the hopes that the mess would get cleaned, but with no luck. The startup script reported success, but no postmaster was running. I tried removing the WAL files & restarting again with the same results- the startup script reports success, but postmaster isn't running. I'm not worried about the data- this is on a demo system, so I can regenerate it quickly, but I can't even get the postmaster started so I can begin the restore. I guess I've learned that removing a WAL file is a Bad Idea (tm)... but now I need a good idea to get me back to the starting point. Any ideas? TIA- -Nick - Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788 doxpop - Court records at your fingertips - http://www.doxpop.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [ADMIN] Can't get postmaster to restart after removing WAL files
Since you can recreate or restore the data, erase everything in your $PGDATA directory and reinitialize the database. While you are at it, you should consider putting $PGDATA on a filesystem with more space. -- Randy Hall - Red Hat Certified Engineer - Ex-Great Bridge PostgreSQL Expert Resume: http://members.home.net/rthall3 - Original Message - From: "Nick Fankhauser" <[EMAIL PROTECTED]> To: "pgsql-admin" <[EMAIL PROTECTED]> Sent: Tuesday, September 11, 2001 7:16 AM Subject: [ADMIN] Can't get postmaster to restart after removing WAL files > Hi all- > > I caused a crash by filling up the filesystem that pg_xlog lives on whilst > doing a vacuum analyze. When I looked at the remains, there were 3 WAL files > out there & no space left. > > I tried simply restarting (/etc/init.d/postgresql start) in the hopes that > the mess would get cleaned, but with no luck. The startup script reported > success, but no postmaster was running. > > I tried removing the WAL files & restarting again with the same results- the > startup script reports success, but postmaster isn't running. > > I'm not worried about the data- this is on a demo system, so I can > regenerate it quickly, but I can't even get the postmaster started so I can > begin the restore. I guess I've learned that removing a WAL file is a Bad > Idea (tm)... but now I need a good idea to get me back to the starting > point. > > Any ideas? > > TIA- > > -Nick > > > - > Nick Fankhauser > > [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788 > doxpop - Court records at your fingertips - http://www.doxpop.com/ > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
How much space should I allocate for WAL files? (was: RE: [ADMIN] Can't get postmaster to restart after removing WAL files)
> Since you can recreate or restore the data, erase everything in > your $PGDATA directory and reinitialize the database. Thanks! I'll give that a try. > While you are at it, you should > consider putting $PGDATA on a filesystem with more space. Doing that has just become a priority for me! I'd like to approach this logically instead of just taking a shot in the dark. Is there any way to predict in advance how much space I'll need? -Nick > -- > Randy Hall - Red Hat Certified Engineer - Ex-Great Bridge > PostgreSQL Expert > Resume: http://members.home.net/rthall3 > > - Original Message - > From: "Nick Fankhauser" <[EMAIL PROTECTED]> > To: "pgsql-admin" <[EMAIL PROTECTED]> > Sent: Tuesday, September 11, 2001 7:16 AM > Subject: [ADMIN] Can't get postmaster to restart after removing WAL files > > > > Hi all- > > > > I caused a crash by filling up the filesystem that pg_xlog > lives on whilst > > doing a vacuum analyze. When I looked at the remains, there were 3 WAL > files > > out there & no space left. > > > > I tried simply restarting (/etc/init.d/postgresql start) in the > hopes that > > the mess would get cleaned, but with no luck. The startup > script reported > > success, but no postmaster was running. > > > > I tried removing the WAL files & restarting again with the same results- > the > > startup script reports success, but postmaster isn't running. > > > > I'm not worried about the data- this is on a demo system, so I can > > regenerate it quickly, but I can't even get the postmaster started so I > can > > begin the restore. I guess I've learned that removing a WAL > file is a Bad > > Idea (tm)... but now I need a good idea to get me back to the starting > > point. > > > > Any ideas? > > > > TIA- > > > > -Nick > > > > > > - > > Nick Fankhauser > > > > [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788 > > doxpop - Court records at your fingertips - http://www.doxpop.com/ > > > > > > ---(end of broadcast)--- > > TIP 6: Have you searched our list archives? > > > > http://www.postgresql.org/search.mpl > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] Broken index? (the revenge!)
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
Jippii Midlands
85 London Road,
Leicester,
England,
LE2 0PF.
tel: 0116 2230662
fax: 0116 2221305
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
