Re: [GENERAL] Re: [HACKERS] [Fwd: SGVLLUG Oracle and Informix on Linux]
On Thu, 23 Jul 1998, The Web Administrator wrote: Nope.. Oracle has a background process which re-allocates free space..It does get fragmented, and the only real way to unfrag is to export (dump) and import.No Vacuum, at least on 7.3.2 So, essentially, our VACUUM command provides functionality that Oracle *doesn't* have, right? Marc G. Fournier [EMAIL PROTECTED] Systems Administrator, Acadia University "These are my opinions, which are not necessarily shared by my employer"
Re: [GENERAL] How about this LOGO?
On Fri, 24 Jul 1998, Brett W. McCoy wrote: On Fri, 24 Jul 1998, The Hermit Hacker wrote: Hey, my preference is an elephant. Big, strong, reliable and never forgets... Too many connotations with the GOP... Enlighten me...what is "the GOP"?
Re: [GENERAL] How about this LOGO?
On Fri, 24 Jul 1998, The Hermit Hacker wrote: Too many connotations with the GOP... Enlighten me...what is "the GOP"? Sorry. The US Republican Party, which uses an elephant for their mascot. Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "The Number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] How about this LOGO?
Too many connotations with the GOP... Enlighten me...what is "the GOP"? Sorry. The US Republican Party, which uses an elephant for their mascot. I wouldn't let that stop you from using an elephant. I think that's a pretty far leap. And I don't expect anyone would associate the two unless you used the exact same elephant logo. Bruce Tong | Got me an office; I'm there late at night. Systems Programmer | Just send me e-mail, maybe I'll write. Electronic Vision / FITNE | [EMAIL PROTECTED]| -- Joe Walsh for the 21st Century
[GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
Bruce Tong wrote: Another window appeared and said "Error while executing the query. ERROR: The field being ordered by must appear in the target list (#1)." Since I hadn't specified any sorting, the "order by" part of the message had me confused. I decided to have MS Access show me the SQL it had generated: I finally figured out what the heck is going on with this order by problem in MS Access. I tested your query on a computer that has older versions of the Microsoft Jet db engine on it and guess what? It does the same thing you described! The reason that I used to have the problem and it miraculously went away was because I installed Visual Basic 5.0 and it upgraded the MS Jet dll's. Your right about the snapshot. It still tries to throw the order by clause in there. But, here's a simple workaround that you can easily try. When looking at your query in Access, right click on the fields area and select "Totals". This will add a group by clause but it won't have any real effect, other than it will let your query run! So, there are two things you must do to fully fix the order by problem: 1. Upgrade the MS Jet DLL (this is the real problem here) 2. Get the patch for the order by/group by from our web site (it will be in postgres 6.4 also) Interestingly, even if you get the postgres patch like we have here, the older MS Jet engine will still not let you sort the columns the way you want, because it always throws that same old order by in there. The working version of \windows\system\MSJET35.DLL is 3.51.0623.4 The broken version I tested was 3.50.3602.4 I'm not sure at what release they fixed this problem. I will dig around on MS web site. Does anyone know if its OK to redistribute the MS JET DB Engine DLL, like with our ODBC driver for example??? (That thing is big too, 1MB!!!) For that matter, is there a simple way to get it from Microsoft? Byron
Re: [GENERAL] Sufficient Primary Key?
On Fri, 24 Jul 1998, Dan Delaney wrote: I don't like doing that because I don't want to store the entire name in every other table which needs to be linked with this one. So, for instance, in the table that keeps track of what authors go to what book, I want it to just have two fields, the Book's primary key and the Author's primary key, and I'd prefer those primary keys to be nice and small instead of, say, the entire title and copyright year for the book and the entire first and last name and date of birth for the author. See what I mean? I see your point, and didn't realize that you had a bunch of other tables linked. I have actually done something similar to what you want to do, but with drug numbers and product codes, mainly because using the drug numbers alone would involve duplicates, and matching approval records to patent records. There's only so much one can normalize. Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "The Number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] What about Unicode?
Does PostgreSQL support Unicode text fields? If not, that would be AWEFULLY nice! Especially now that so many OSs (the MacOS for one) and standards (HTML for one) are beginning to use it. Yeah, I'll probably eventually try to exchange data with a Magic Cap OS based PDA which also uses Unicode. We currently translate to ASCII, but that causes some problems so supporting Unicode would be nice for me, but not essential. Bruce Tong | Got me an office; I'm there late at night. Systems Programmer | Just send me e-mail, maybe I'll write. Electronic Vision / FITNE | [EMAIL PROTECTED]| -- Joe Walsh for the 21st Century
Re: [DOCS] Re: [GENERAL] Re: [HACKERS] [Fwd: SGVLLUG Oracle and Informix on Linux]
In fact, they are handled by SQL: CREATE DATABASE and DROP DATABASE. The
createdb and destroydb tools just call these SQL statements
Let's remove the "I don't want to think" utilities like
{create,destroy}{db,user} and force DBA's to actually use the *proper*
functions.
While the man pages indicate that these invoke psql, and that a postmaster
must be running, and somebody really smart could infer that that means that
there is SQL to do the action, it would be much, much better if the man
pages explicitly stated that it was merely a shortcut to using the sql.
I think only doing it the SQL way would be fine. Documentation would, of
course, have to cover it. I want, no need, to know what functionality
belongs to SQL and what belongs to PostgreSQL. I've certainly not got any
qualms about dropping into psql to do things. I like psql.
They have to connect to template1 to do the work. Currently, they don't
need to know template1 even exists, so it seems like an added burden. I
will add a mention to the createdb, destroydb man pages. createuser
does psql too.
--
Bruce Momjian | 830 Blythe Avenue
[EMAIL PROTECTED] | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup.| (610) 853-3000(h)
Re: [GENERAL] How about this LOGO?
On Fri, 24 Jul 1998, Bruce Tong wrote: Too many connotations with the GOP... Enlighten me...what is "the GOP"? Sorry. The US Republican Party, which uses an elephant for their mascot. I wouldn't let that stop you from using an elephant. I think that's a pretty far leap. And I don't expect anyone would associate the two unless you used the exact same elephant logo. And, unless you are in the US, you wouldn't know anyway :)
[GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
Bruce Tong wrote: The working version of \windows\system\MSJET35.DLL is 3.51.0623.4 The broken version I tested was 3.50.3602.4 I'm not sure at what release they fixed this problem. I will dig around on MS web site. Does anyone know if its OK to redistribute the MS JET DB Engine DLL, like with our ODBC driver for example??? (That thing is big too, 1MB!!!) For that matter, is there a simple way to get it from Microsoft? Yes, I found the updated jet database engine on Microsoft site. http://support.microsoft.com/download/support/mslfiles/Jet35upd.exe I don't know the answers to these questions, but if you're just doing this to save me time there's no reason to put yourself out as that's something I should do for myself. If you're planning on distributing the DLLs along with the driver, that sounds like a great idea, assuming they'll let you. Well, this Access order by problem has bothered us and many others that use the odbc front end with postgres for quite some time. Its a great relief to finally have a clear understanding of the problem! I think we will probably just include a link to the above site off of our site and mention this problem. I take it I don't have to gather the log information you requested? No, thanks anyway. Byron
Re: [DOCS] Re: [GENERAL] Re: [HACKERS] [Fwd: SGVLLUG Oracle and Informix on Linux]
They have to connect to template1 to do the work. Currently, they don't need to know template1 even exists, so it seems like an added burden. I will add a mention to the createdb, destroydb man pages. createuser does psql too. And as a result, I didn't know what template1 was for until now, and I fear there's more to it than just this. Up until this point, I assumed "template1" was an example database which could be copied, or something. At least that's what a template is to me. Okay, I've suspected there was more to "template1" for a little while now, but I'd not gotten around to looking into it more. Still, my first impression was it was a sample database. ;) Maybe a name like "master" would be clearer, or maybe that means something else to someone. Bruce Tong | Got me an office; I'm there late at night. Systems Programmer | Just send me e-mail, maybe I'll write. Electronic Vision / FITNE | [EMAIL PROTECTED]| -- Joe Walsh for the 21st Century
Re: [GENERAL] Re: [HACKERS] [Fwd: SGVLLUG Oracle and Informix on Linux]
Herve Lefebvre wrote: ---Marc Fournier [EMAIL PROTECTED] wrote: On Thu, 23 Jul 1998, The Web Administrator wrote: Nope.. Oracle has a background process which re-allocates free space..It does get fragmented, and the only real way to unfrag is to export (dump) and import.No Vacuum, at least on 7.3.2 So, essentially, our VACUUM command provides functionality that Oracle *doesn't* have, right? I don't think any fragmentation occurs under Oracle, if dataspaces are allocated on raw-devices (wich is a functionnality PG doesn't have). Of course, most DBA's I know never use raw devices, because of issues such as backups, etc... and even then, (but I am no expert here) I understand that data still gets fragmented because of it's implementation of data blocks, and it's rules for when to use the next block etc.. It has to allow for records which expand and contract, so it leaves room initially to allow for this so that records never span across two data blocks etc..Getting out my trusty Oracle Manual Under Fragmentation To determine the amount of fragmentation... select tablespace_name, sum(bytes), max(bytes), count(tablespace_name) from dba_free_space group by tablespace_name order by tablespace_name / If the number of contigous blocks is greater than 10 to 15, you should defrag the tablespace. The steps are 1) export data from the tablespace with the 'exp' utility. For indexes, capture the SQL to recreate them 2) Drop all the objects 3) Drop the tablespace 4) Recreate the tablespace 5) Import the data back into the tablespace with the 'imp' utility. Maunally recreate indexes by using their SQL scripts. Having sorted all that out... I think we should stop asking what 'Others' have, and just concentrate on making Postgres the best database out there, and let 'Others' implement Postgres Features.. IMHO, to often we are so intent on copying the 'Big Guys' that qwe forget out own strengths to the point that we start burdening clean code with garbage for compatibilities sake, rather than looking at it from scratch and seeing what the most effecient method is... The boys at Postgres have so far done a good job of creating an effecient Database, let's keep it that way, rather than created a Clone of some commercial software that might have years of compatibility issues that forced implementation of methods that may not be the most effecient.. So let's stop comparing Postgres to the rest.. If there is something we need to do with our Databases that we can't, then these are the issues we should address only.. It's like saying let's unix should use back slashes instead of forward slashes.. just because that is what windows/dos does... Or that we should recode Ultima to use a Doom perspective... Let Postgres stand on it's own merits, and not it's Oracle Compatibility.. Imagine what would have happened if Linus Torvald whould have decided that Linux needed to be Windows compatible.. The code would not have been the work of art it is today.. Well enough editorializing.. That's my last note on the Postgres vs. the rest argument.. -- ++ Michael - System Administrator Working in Cheap Canadian Dollars Unix Administration - WebSite Hosting - Network Services - Programming Wizard Internet Services - TechnoWizard Computers - Wizard Tower TechnoServices -- (604) 589-0037 Beautiful British Columbia, Canada ++
Re: [GENERAL] Re: [HACKERS] [Fwd: SGVLLUG Oracle and Informix on Linux]
On Fri, 24 Jul 1998, The Web Administrator wrote: The boys at Postgres have so far done a good job of creating an effecient Database, let's keep it that way, rather than created a Clone of some commercial software that might have years of compatibility issues that forced implementation of methods that may not be the most effecient.. So let's stop comparing Postgres to the rest.. If there is something we need to do with our Databases that we can't, then these are the issues we should address only.. PostgreSQL is the first real database server I've ever used. I've not used Oracle or Sybase or anything like that, so the only thing I can compare it with is desktop database systems like MS-Access, and all I can say is that there' s no comparison. :-) I don't want PostgreSQL to be like Access, nor anything else for that matter, but just be what it is. Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "The Number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] Re: [HACKERS] [Fwd: SGVLLUG Oracle and Informix on Linux]
On Fri, 24 Jul 1998, Richard Lynch wrote: At 8:28 AM 7/24/98, Marc Fournier wrote: So, essentially, our VACUUM command provides functionality that Oracle *doesn't* have, right? Yes, but yours doesn't run automatically. NAIVE Ideally, when one created a database, one could specify vacuum frequency and/or time slot, and PostgreSQL would just do it... /NAIVE That's actually a good thought. Once we have it so that table-space is re-usable (ie. fill in the blanks), then going one step further and having something similar to that to 'vacuum' out fragmentation would be most cool...
Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
At 10:36 AM 7/24/98, Byron Nikolaidis wrote: Bruce Tong wrote: Another window appeared and said "Error while executing the query. ERROR: The field being ordered by must appear in the target list (#1)." Since I hadn't specified any sorting, the "order by" part of the message had me confused. I decided to have MS Access show me the SQL it had generated: I finally figured out what the heck is going on with this order by problem in MS Access. I tested your query on a computer that has older versions of the Microsoft Jet db engine on it and guess what? It does the same thing you described! The reason that I used to have the problem and it miraculously went away was because I installed Visual Basic 5.0 and it upgraded the MS Jet dll's. FWIW: I seldom do much more than add the tables in MS Access's OPINION stupid /OPINION query design thingy. I immediately use the menu to view SQL and just type the SQL I want. Obviously, different folks think different ways, and maybe you actually understand and like that MS Access point and click interface that I hate, but I want to be sure you're at least aware that if it sticks an ORDER BY in there that you never asked for, you can just rip the damn thing out in the SQL view and then run the query... At least, that's how I make it give me the SQL I really want, instead of the SQL it thinks I want, which it never gets right anyway. Hope that helps. -- -- -- "TANSTAAFL" Rich [EMAIL PROTECTED]
Re: [GENERAL] Nested tables
On Fri, 24 Jul 1998, Ferruccio Zamuner wrote: CREATE TABLE foo ( x int2 ); CREATE TABLE bar ( y foo, z int ); I read on some Postgres mailing digest that I had to put the OID of instance of foo into y field of bar. So I tried. The problem is that when I submit the following query: SELECT foo.x, bar.z WHERE foo.oid=bar.y; trying to catch int fields either from bar and from its instance of foo, Postgres replies that bar.y (of type foo) and foo.oid (of type oid) cannot be compared. I've also tried to cast them in many ways but the answer never changes! You could declare bar.y to be of type oid instead of type foo.
Re: [GENERAL] Re: [HACKERS] [Fwd: SGVLLUG Oracle and Informix on Linux]
On Fri, 24 Jul 1998, Richard Lynch wrote: At 8:28 AM 7/24/98, Marc Fournier wrote: So, essentially, our VACUUM command provides functionality that Oracle *doesn't* have, right? Yes, but yours doesn't run automatically. NAIVE Ideally, when one created a database, one could specify vacuum frequency and/or time slot, and PostgreSQL would just do it... /NAIVE That's actually a good thought. Once we have it so that table-space is re-usable (ie. fill in the blanks), then going one step further and having something similar to that to 'vacuum' out fragmentation would be most cool... Maybe I'm just still being naive, but I'm not seeing the dependency of automation of vacuum on table-space reusability. Or is it just that the there's only one hacker who can do it, and table-space reusability is way more important? [Oui. D'Accord.] If there is a dependency, I'd be interested in learning what it is. If it's just a man-power thing, then I'm just wasting your time. Sorry. PS: I checked the "\h create database" It don't say nothin' about needing to connect to template1 to create a database, nor does it say that it will copy the current database as a starter, which may or may not be true... The docs at postgresql.org also don't say nothin' about connecting to template1. -- -- -- "TANSTAAFL" Rich [EMAIL PROTECTED]
Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
Richard Lynch wrote: FWIW: I seldom do much more than add the tables in MS Access's OPINION stupid /OPINION query design thingy. I immediately use the menu to view SQL and just type the SQL I want. Obviously, different folks think different ways, and maybe you actually understand and like that MS Access point and click interface that I hate, but I want to be sure you're at least aware that if it sticks an ORDER BY in there that you never asked for, you can just rip the damn thing out in the SQL view and then run the query... At least, that's how I make it give me the SQL I really want, instead of the SQL it thinks I want, which it never gets right anyway. Hope that helps. Thanks, I would probably agree with you, but it makes no difference if I like it or not, its our customers who want the graphical interface and FINALLY, we have a fix for it! They are most likely not the types who are gonna drop down to the pound sign and type some SQL into the monitor! And BTW, if you look at the SQL, you wouldn't even see the ORDER BY thing because this clause is placed on the "keyset" query, which is the collection of keys it uses to dynamically access rows in the query result (i.e. the Dynaset). Actually, I understand your hate of the Microsoft thing, but I only recently realized the benefit and power of this Jet database engine thing that Access uses. For example, it can update the results of a query that has several tables joined, right while your looking at the row on the screen, automatically translating the SQL2 syntax (i.e., INNER JOIN ... ON ...) into standard SQL that Postgres can use. Byron
Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
FWIW: I seldom do much more than add the tables in MS Access's OPINION stupid /OPINION query design thingy. I immediately use the menu to view SQL and just type the SQL I want. Obviously, different folks think different ways, and maybe you actually understand and like that MS Access point and click interface that I hate, but I want to be sure you're at least aware that if it sticks an ORDER BY in there that you never asked for, you can just rip the damn thing out in the SQL view and then run the query... At least, that's how I make it give me the SQL I really want, instead of the SQL it thinks I want, which it never gets right anyway. Since I'm learning SQL in my spare time, I tend to use these feature in MS Access and PgAccess to point me in the right direction or sometimes confirm, or deny my assertions. I like psql, but its not the kind of tool which suggests other alternatives. It just says "this part is bogus." That's fine, but when I fail to get it right after a dozen attempts, its nice to let something else take a stab at it. Bruce Tong | Got me an office; I'm there late at night. Systems Programmer | Just send me e-mail, maybe I'll write. Electronic Vision / FITNE | [EMAIL PROTECTED]| -- Joe Walsh for the 21st Century
Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
Since I'm learning SQL in my spare time, I tend to use these feature in MS Access and PgAccess to point me in the right direction or sometimes confirm, or deny my assertions. I like psql, but its not the kind of tool which suggests other alternatives. It just says "this part is bogus." That's fine, but when I fail to get it right after a dozen attempts, its nice to let something else take a stab at it. Please tell use what else psql can tell the user. We have \h and \d commands. -- Bruce Momjian | 830 Blythe Avenue [EMAIL PROTECTED] | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup.| (610) 853-3000(h)
