[GENERAL] bool and NOT
Hi, how should I write this: todolist=# select name from tasks_t where id = 5 and NOT done; ERROR: argument to NOT is type 'numeric', not 'bool' bis später... Sascha
Re: [GENERAL] Columns in pg_shadow?
"Michael A. Mayo" <[EMAIL PROTECTED]> writes: > I have been looking for information on the meaning of each column in > pg_shadow, but have been unable to find any thus far. It would be good if > someone could enlighten me, or point me to existing documentation. The > columns I am unsure of are listed below: > usecreatedb: > Ability to create new databases? Check. > usetrace: > ? Searching through the sources shows that this flag is not used anywhere. The catalogs.sgml doc file defines it as "can user set trace flags?" but whatever code it controlled must be long dead... > usesuper: > I assume this makes the user a "superuser," but I am not sure exactly > what that means. A superuser is God as far as the database is concerned: she's not subject to any protection checks, and there are some security-critical operations like backend COPY that are only allowed to superusers. Pretty much the same concept as being "root" on a Unix system. > usecatupd: > Ability to change system tables? Right. The point of this flag is to let superusers be slightly less Godlike: by turning off her usecatupd flag, a superuser can revoke her right to alter system tables via direct INSERT/UPDATE/DELETE commands, and thereby avoid foolish mistakes. Or that seemed to be the plan anyway. Since there's no really convenient interface for twiddling this flag, I doubt anyone actually bothers to change it. It starts out the same as one's usesuper flag, and probably stays that way... BTW, the easiest way to learn about this sort of stuff is to scan the source code --- and if you don't have a handy tool for that, allow me to recommend "glimpse", http://glimpse.cs.arizona.edu/. I wasn't too sure about usetrace or usecatupd either, but it took just a few seconds to examine their uses and learn what I said above. (For fans of the One True Editor: I have an emacs macro that invokes glimpse in the same way as grep is called by the standard "grep" macro, so that you can step through all the hits with C-x `. Let me know if you need it.) regards, tom lane
Re: [GENERAL] Auto-uppercase inserted column
Bruce Momjian wrote: > Is there a way to automatically uppercase a column upon insert or > update? > > I don't think rules can do that because of the problem of recursion. > The only way I think it can be done is using triggers. Is that correct? > Since triggers are available in postgreSQL, that should be the way, yes. You have to have access to the NEW values in your trigger, and then you should say something like NEW.field := upper(NEW.field) ; In older database systems this would be done in the entry system, but then you have a coupling between your application and the database. Good luck. Jurgen Defurne [EMAIL PROTECTED]
[GENERAL] Columns in pg_shadow?
I have been looking for information on the meaning of each column in pg_shadow, but have been unable to find any thus far. It would be good if someone could enlighten me, or point me to existing documentation. The columns I am unsure of are listed below: usecreatedb: Ability to create new databases? usetrace: ? usesuper: ? I assume this makes the user a "superuser," but I am not sure exactly what that means. usecatupd: Ability to change system tables? Thanks, -Mike
Re: [GENERAL] Zip Code Proximity
Does anyone happen to have a copy of the zips.zip file from the census bureau? Their FTP server is uncooperative. Dustin Sallings wrote: > > On Thu, 18 May 2000, Jeff Hoffmann wrote: > > I tried to get a few permutations of that to work, but with no > luck. The following will give a list of all places sorted by how far away > from my house they are: > > select zipcode, city, state, point(latitude, longitude) as point > from zips > order by point_distance(location_of(95051), point(latitude,longitude)) > > I defined the function location_of for my own convenience: > > create function location_of(integer) returns point as > 'select point(latitude, longitude) from zips where zipcode = $1 ' > language 'sql' > > # > select zip, location <@> '(lat, lon)'::box > # > from zipcodes > # > order by location <@> '(lat, lon)'::box > # > limit 10; > # > > # > # oops, typo. those boxes should be points. plus, it looks like you can > # get zipcodes & lat-longs from the census at: > # > # http://ftp.census.gov/geo/www/gazetteer/places.html > # > # > > -- > dustin sallingsThe world is watching America, > http://2852210114/~dustin/ and America is watching TV. -- Paul Dlug Unix/Web Programmer
[GENERAL] Re: [HACKERS] Postgresql OO Patch
Tom Lane wrote: > It's kinda fuzzy, but in practice I'd say the readers of pgsql-hackers > and maybe pgsql-general. One more time for the mailing list... Hands up if you have objections to the patch I recently submitted for postgresql. It fixes the long standing bit-rot / bug that DELETE and UPDATE don't work on inheritance hierarchies, and it adds the ONLY syntax as mentioned in SQL3 and as implemented by Informix. The downside is it breaks compatibility with the old inheritance syntax. But there is a backward compatibility mode. I.e. "SELECT * FROM foobar*" becomes "SELECT * FROM foobar", and "SELECT * from foobar" becomes "SELECT * FROM ONLY foobar". Benefits: *) SQL3 says it. *) Informix does it. *) If you never used inheritance it doesn't affect you. *) Performance is unaffected. *) There is a backwards compatibility mode via SET. *) My own experience says strongly that this will greatly reduce programmer bugs because the default is much more common (laziness usually leads us to discard the "*" to the detriment of future inheritance data model changes.) *) It is more OO since by default a IS A . Disadvantage: *) You need to make a one line change to any programs that use inheritance to include the back-compatibility SET mode.
Re: [GENERAL] Question about databases in alternate locations...
> Having the > ability to organize tables, indices, etc into tablespaces, and then > distributing the datafiles in some quasi intelligent fashion is truly pretty > powerful. Great feedback! Everyone will agree that there is no problem with the overall goal. We're just working out the details, and your use-case with Oracle should and will be one of the use-cases that any improvements should actually improve :) - Thomas -- Thomas Lockhart [EMAIL PROTECTED] South Pasadena, California
Re: [GENERAL] beginner Table data type question
Richard, For constraints see: http://www.postgresql.org/doxlist.html then select Documentation then select Integrated Document then select Alter Table For Column definitions: I believe some other implementations of SQL allow you to alter columns as long as you are keeping the same basic datatype and increasing the size, but require a copy-drop-add-copy if you are changing the basic datatype or decreasing the size. Postgres requires a copy-drop-add-copy for any change to column definitions. David Boerwinkle -Original Message- From: Richard Smith <[EMAIL PROTECTED]> To: [EMAIL PROTECTED]@postgreSQL.org <[EMAIL PROTECTED]@postgreSQL.org> Date: Friday, May 19, 2000 11:12 AM Subject: [GENERAL] beginner Table data type question >Hi, I just start using pgsql and I am new to SQL also. I have read lots >of >documents on this and I cant seem to find the answer, perhaps you can >help me. > >Say I have a table: > >pgsql=> \d test_table > >test_table >-- >f1| CHAR (40) >f2| CHAR (40) > >Somewhere down the line I need to change the data type on f2 to CHAR >(50) and add constrains like UNIQUE. What is the best was to change >tables in this way? >Can I just make a new table the way I want and COPY the data out of the >old table and COPY it back in? do I use CAST ? Is there some ALTER >TABLE tablename MODIFY command I can use? > >This question has been bugging me for awhile. Because I am new to SQL I >find that I make bad design choices about data types and such all the >time. So I need to know the best way to change them. > >Richard
[GENERAL] Auto-uppercase inserted column
Is there a way to automatically uppercase a column upon insert or update? I don't think rules can do that because of the problem of recursion. The only way I think it can be done is using triggers. Is that correct? -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] simple C function
On Fri, May 19, 2000 at 05:54:55PM +0200, [EMAIL PROTECTED] wrote:
> Hi,
> I'm trying to write a simple C function:
>
> char *pg_crypt (char *pass) {
> char *salt="xyz";
> char *res;
> res = (char *) palloc(14);
> res=crypt(pass,salt);
> return res;
> }
you can't pass char pointers around like that for pgsql functions.
Here's my version of the above function. It includes random salt
selection if you don't supply it. (Hmm, I suppose I should put
this is contrib, eh? I did start with someone elses boilerplate,
so I'm not sure about the #define at the top.)
I compile it on linux with gcc as so:
gcc -fPIC -shared -I /usr/local/pgsql/include -L /usr/local/pgsql/lib \
-o sqlcrypt.so sqlcrypt.c
And install it like so:
CREATE FUNCTION "sqlcrypt" (text,text ) RETURNS text AS '/usr/local/lib/sqlcrypt
.so' LANGUAGE 'C';
CREATE FUNCTION "sqlcrypt" (text ) RETURNS text AS 'select sqlcrypt($1,)' LA
NGUAGE 'SQL';
Ross
--
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
#define _XOPEN_SOURCE
#include
#include
#include
#include
#include
text *sqlcrypt(text *key, text *salt);
/*sql create function sqlcrypt(text,text) returns text as 'DESTLIB' language 'c'*/
char *crypt(const char *key, const char *salt);
int rand(void);
void srand(unsigned int seed);
text *sqlcrypt(text *key, text *salt)
{
text *ret;
char pass[] = "123456789";
char s[] = "...";
char salts[] = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789./";
int j,k;
struct timeval tv;
s[2]=0;
bzero(pass,9);
if ((VARSIZE(salt)-VARHDRSZ) < 2)
{
gettimeofday(&tv,0);
srand((unsigned int)(tv.tv_usec));
j=(rand() % 64);
k=(rand() % 64);
s[0]=salts[j];
s[1]=salts[k];
}
else
{
memcpy(s,VARDATA(salt),2);
}
ret = palloc(VARHDRSZ + 13);
bzero(ret,VARHDRSZ + 13);
VARSIZE(ret) = (VARHDRSZ + 13);
if ((VARSIZE(key)-VARHDRSZ) < 8)
{
memcpy(pass,VARDATA(key),VARSIZE(key)-VARHDRSZ);
}
else
{
memcpy(pass,VARDATA(key),8) ;
}
memcpy(VARDATA(ret), crypt(pass,s),13);
return ret;
}
[HACKERS] Re: [GENERAL] Question about databases in alternate locations...
Thomas Lockhart writes: > So pg_location would hold the full path (absolute or logical) to every > file resource in every database? Or would it hold only a list of > allowed paths? Or only a list of resources for each database (~1 row > per database) and then table-specific info would be stored somewhere > local to the database itself? > Is a list of allowed paths really necessary? If initlocation has already been run so a directory tree with the proper structure and permissions exists there'd be no new security hole (ie, I couldn't ask the backend to create a database on any arbitrary partition; only one that's already been prepared by the administrator). I'd like to see a list of resources per database, with any table-specific info stored locally. > ALTER TABLE SET LOCATION=... > and/or > ALTER DATABASE SET LOCATION=... > should help administration and scalability. > Definitely. Of course, I'd want to make sure any new LOCATION had been prepared by the administrator. > But hard to do? If pg_location has 5000 entries, and you've scattered > tables all over the place (perhaps a bad decision, but we *should* > have the flexibility to do that) then it might be very error prone > when working with absolute paths imho. > I'd think that a pg_location entry wouldn't be necessary for the majority of tables -- the default location would be just like it is now, under the database directory. Creating a database directory in one place and scattering the tables all over creation would definitely be a Bad Decision, IMHO, but it would be doable. > Putting absolute path names as pointers to tables or data areas. I'm > getting the sense I'm in a minority (in a group of 3? ;) in this > discussion, but imho having some decoupling between logical paths in > the database and actual paths outside is A Good Thing. Always has been > a mark of good design in my experience. > How about requiring an absolute path for the data(base) area, and allowing relative paths for the tables? Actually, if you want ALTER DATABASE SET LOCATION=... to move tables, you'd either have to require relative paths for the tables or ignore tables that have absolute paths, right? Hmm. And all I originally wanted was an easier way to create a database in an alternate location :-). - Rich -- Richard Kuhns [EMAIL PROTECTED] PO Box 6249 Tel: (765)477-6000 \ 100 Sawmill Roadx319 Lafayette, IN 47903 (800)489-4891 /
RE: [GENERAL] Question about databases in alternate locations...
I've worked with various versions of Oracle for several years and can share some of my experiences with their "system catalog" implementation. They use a fairly simple design in which a database instance consists of 1 .. n tablespaces (that can contain any type of database object) which in turn consists of 1 .. n datafiles. There is a system table which essentially holds the name of the physical file (full path), file_id, tablespace it belongs to, and sizing information. Our database (110 Gig) is split up into 24 tablespaces and these are further split into 73 datafiles. Moving the physical location of a datafile is fairly straight forward. 1) ALTER TABLESPACE foo OFFLINE 2) Move the physical file using OS command 3) ALTER TABLESPACE foo RENAME DATAFILE '/old/file' TO '/new/file' 4) ALTER TABLESPACE foo ONLINE Most of our datafiles run about 2 Gig, so the longest part of this is actually doing the move. One headache is if you want to completely change the locations of ALL your files. This involves editing all of the paths and is definitely prone to error. This is also where you rabidly curse the DBA who decided to have path names that are 140 characters long! A second headache is moving databases from one server to another. You are then forced into having the exact same file structure on the second machine. This can be somewhat amusing if you have a different hardware configuration which doesn't have the same number of disks, etc. This second problem is further complicated by some of the backup solutions available for Oracle. The one that we have uses the system catalog to locate and backup the appropriate files. This again means that if you want to restore the backup to another server, it must be configured exactly as the first. I think that Thomas' fear of having thousands of entries in the system catalog for datafiles is alleviated in the Oracle implementation by the use of the tablespace. Tablespaces can contain any number of database objects, so by using a reasonable tablespace layout, one can keep the number of actual datafiles to a manageable level. One thing that has been definitely useful is the ability to do load balancing based on what tablespaces are "hot". Our system is somewhat of a cross between OLTP and a data warehouse (don't get me started) so the data becomes pretty static after, say, about 30 days. By monitoring which datafiles are being accessed the most, they can be moved to different locations on the storage array to avoid contention and maximize throughput. My first reaction to the suggestion of a pg_location like table was "ARGH, NO!", but after nursing my sprained back from that violent knee jerk reaction and actually thinking about it, I talked myself into thinking it'd probably be a good idea. If we had our online system built on top of Postgres, we would need a filesystem with 110+ Gig of disk space and there would be roughly 3,500 files in its single data directory. Having the ability to organize tables, indices, etc into tablespaces, and then distributing the datafiles in some quasi intelligent fashion is truly pretty powerful. Phil Culberson
[GENERAL] Why is the JDBC driver re-arranged?
Hi, Prior to 6.5, when I ran $ jar tvf jdbc6.5-1.2.jar I got .. 5309 Wed Sep 15 21:45:44 CST 1999 postgresql/Driver.class .. Now in 7.0, when I ran $ jar tvf jdbc7.0-1.2.jar I got .. 5341 Mon May 15 16:07:48 CST 2000 org/postgresql/Driver.class .. Is there any particular reason to rearrange postgresql.Driver to be org.postgresql.Driver? The system I am using is Red Hat Linux 6.2 with Kernel 2.2.14-6.1.1 on an i586 Thanks in advance. Michael
[GENERAL] How to stop syslogd@mylinux displaying message?
Hi, After installing postgresql-7.0-1, I got the following message whenever I login/connect to postmaster via JDBC, Message from syslogd@mylinux at Fri May 19 20:48:11 2000 ... mylinux Message from syslogd@mylinux at Fri May 19 20:59:11 2000 ... mylinux I have never seen this kind of message prior to 7.0. Is there any way to get rid of this message? The system I am using is Red Hat Linux 6.2 with Kernel 2.2.14-6.1.1 on an i586 Thanks in advance. Michael
[GENERAL] simple C function
Hi,
I'm trying to write a simple C function:
char *pg_crypt (char *pass) {
char *salt="xyz";
char *res;
res = (char *) palloc(14);
res=crypt(pass,salt);
return res;
}
CREATE FUNCTION pg_crypt(text) RETURNS text as
'/home/mazek/pgsql/pg_crypt.so' LANGUAGE 'c';
psql just got stuck and I can't even DROP this function, because psql
hangs. Can You give me a hint me where should I look for errors. How to
get rid of this function without reinitializing $PGDATA and destroying
other data (accordind to former discussion:) ).
LINUX RH6.2,PG 7.0
Marcin Mazurek
--
administrator
MULTINET SA o/Poznan
http://www.multinet.pl/
[GENERAL] beginner Table data type question
Hi, I just start using pgsql and I am new to SQL also. I have read lots of documents on this and I cant seem to find the answer, perhaps you can help me. Say I have a table: pgsql=> \d test_table test_table -- f1| CHAR (40) f2| CHAR (40) Somewhere down the line I need to change the data type on f2 to CHAR (50) and add constrains like UNIQUE. What is the best was to change tables in this way? Can I just make a new table the way I want and COPY the data out of the old table and COPY it back in? do I use CAST ? Is there some ALTER TABLE tablename MODIFY command I can use? This question has been bugging me for awhile. Because I am new to SQL I find that I make bad design choices about data types and such all the time. So I need to know the best way to change them. Richard
Re: [GENERAL] PostgreSQL book completed though chapter 10
Bruce Momjian wrote: > > I have completed the first draft of my book through chapter 10. > > New chapters include: > > Chapter 7, Numbering rows: OID's, sequences > > Chapter 8, Combining Selects: UNION, subqueries > > Chapter 9, Data Types: types, functions, operators, arrays > > Chapter 10,Transactions and Locks: transactions, locking > > The books is accessible at: > > http://www.postgresql.org/docs/awbook.html > > Comments welcomed. > > -- > Bruce Momjian| http://www.op.net/~candle > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 I just Finished reading your book, I did work along with the text up to chapter 4. I hope to finish that up when I get the time. It is looking great. I am a pgsql beginner and this book has help me out a lot. I know that data modeling is a huge subject, but you might want to add a small chapter on that subject. I look forward to reading more drafts, and thank you for the great work so far. Richrad
Re: [GENERAL] Performance
> I think the "VACUUM ANALYZE" solution should be given great prominence in > the FAQ. > > Possibly add to: > 4.9) My queries are slow or don't make use of the indexes. Why? > http://www.postgresql.org/docs/faq-english.html#4.9 > > New first lines: > Make sure relevant indexes exist (see 4.8) then try VACUUM ANALYZE from psql. > > If that doesn't work, read the rest. And if THAT doesn't clear things up, > then it's something which the mailing lists and developers probably want to > know. Good, I added one sentence to the top of the FAQ answer stating VACUUM ANALYZE and try again. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
