[GENERAL] Couple of design questions
Hello everyone, I'm just beginning a PHP / PostgreSQL project for someone and there are a couple of things where I am not sure the best design for the database. The first is that we want to store kind of a history of values for the past 3 days or so. We want to use this so that we can analyze how the values have changed over the last few days. The solution I thought of was having 4 columns in the table for each value, like this: somedataint, somedata_24 int, somedata_48 int, somedata_72 int, There are 3 different variables that we want to keep a history for in each row. So what I thought we could do is at some arbitrary time each day, copy the values from somedata into somedata_24, from somedata_24 into somedata_48, from somedata_48 into somedata_72, and just forget whatever was in somedata_72. My question is, how long would something like this take (relatively speaking, I don't know the hardware specs of the server exactly, it will be professionally hosted I believe) if there were around 20,000 rows? If it would take too long or be too taxing on resources, do you have any other ideas on how to handle something like this? The second thing is we are going to be having our users update the data in the database quite frequently. What will happen is that when User A wants to search the database for something, the system will first check and see what the oldest set of data it has is and then ask User A to fetch new data to replace the old stuff. Now since we expect very frequent use of this, we want a way to mark which data was requested from User A so that when User B comes along, the system doesn't request the same data from him. But we don't want to change the timestamp for when the data was updated until we actually get the information, in case the user decides he doesn't want to do a search and doesn't send the new data. One way I thought I could do this is by having a table something like this: dataset_id int, last_update timestamp, (or some other date/time field...I can never keep them straight in my head) locked bool Then, when I request a dataset from User A, I set the bool field to true and my SQL for finding the oldest one already only selects from datasets where locked is false. But I thought if PGSQL's transaction functions already did something like this, it would be easier and faster to use them. Will SELECT ... FOR UPDATE help me out here? Is there a way to check and see if a row is locked and ignore locked rows in another SELECT ... FOR UPDATE? Any input on a better way to handle this would also be appreciated. Thanks for the time. :) -Jesse
Re: [GENERAL] Anyone using ReiserFS in production work? (or advise against it?)
> "Randall" == Randall Parker <[EMAIL PROTECTED]> writes: Randall> The title says it all: Is this a foolhardy or prudent and Randall> wise move at this time? Has anyone run Postgres Randall> databases on ReiserFS volumes under heavy enough load and Randall> for long enough to get a sense of how stable it is? Well, not in production yet, but on my machine running a copy of our message switch on a reiser partition. I currently have a "stuck on TLB" (if I remember the error correctly) but this is not reiser specific (and a patch exists which I have to try when I get time). Randall> Anyone tried pulling the power plug under heavy load to Randall> find out if ReiserFS is less prone to volume corruption Randall> than ext2 under these circumstances? Hmm, pulling plug, no, but the machine has hung up 2 or 3 times while trying out pre versions of 2.2 Linux and has come up without any problems. We have just ordered 2 new servers and I am going to put reiserfs and RAID through its paces, try pulling a disk while running, pulling the power, etc. to get a good idea of what can go wrong and whats needed to fix it. I suggest you try something similar before going to production. Sincerely, Adrian Phillips -- Your mouse has moved. Windows NT must be restarted for the change to take effect. Reboot now? [OK]
Re: [GENERAL] PostgreSQL & the BSD License
At 09:34 7/07/00 -0300, The Hermit Hacker wrote: > >am investigating this right now ... > >On Fri, 7 Jul 2000, Thomas Lockhart wrote: > >> > Now, a) is easily fixable by just extending the date to 2000, but that >> > still only covers "UNIVERSITY OF CALIFORNIA", and none of the actual >> > developers ... >> >> afaik we can't unilaterally alter the original license, either for dates >> or for participants. However, we can send along a second license (or >> first, primary, license) in the same file. >> >> - Thomas FWIW, this is the information I got in answer to the question "given that the source is already under the BSD, is it even possible to change it, however slight those changes might be?" Answer: "Only in respect of those who agree to the change - that is why unincorporated bodies have rules which allow for majority etc approval to rules - which effectively constitute a contract between members - this has implications for your community - you could perhaps agree to a mechanism for defining the group and agreeing to changes with less than everyone approving - this would make updating the licence possible - rather than virtually impossible as it is now - but this introduces substantial complexity which you are trying to avoid. However I suspect that these sorts of groups will eventually have to do this to avoid becoming unwieldy unless the technology becomes out of date first" I guess the key bit is "rather than virtually impossible as it is now". So, I am now asking him how he would define our 'community': my inclination is that it has to include the WW Dev team, and almost certainly the users. Or at least those users who have 'registered', but we'll see. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.C.N. 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [GENERAL] Find all the dates in the calendar week?
> > I just didn't implement the corresponding "year" code at least partly > > because I wasn't sure what to call it. 'iyear' seems like a pretty good > > choice, or should it be 'isoyear'? 'year' is already used, obviously, > > and if 'iyear' is chosen then perhaps I should change 'week' to 'iweek' > > for consistancy. Comments? > Then we should probably rather change 'year' to something else. Standards > should be preferred. "Standards" in this case include common usage and the ISO-defined business usage of business-year/week-of-year. If we had to choose, clearly common usage wins. > Out of curiosity, what's the difference between > ISO-year and proprietary-year? I can see the week-of-year thing, but the > year of a year is always constant, no? ISO-year/week-of-year is a business-only construct, perhaps helping with payment intervals. There is some slop in the beginning and end of each calendar year, which can result in a particular day in a calendar year fitting into a different ISO-year (not the right term btw). > Btw., isn't there an SQL EXTRACT function for all of this? Shouldn't we be > thinking in terms of that? EXTRACT() is implemented with date_part(). - Thomas
[GENERAL] Sig 11 bug
Hi all, I have created a database that reliably crashes postgresql when i try a insert/update on one table but not another. Even when I pg_dump the db and reload it on a new machine/new db it still crashes. I'm not exactly sure whats up here... I'll submit a proper bug request, but I'd like to bring up this... I think I've found a serious bug... -ryan -- Ryan Rawson System Administrator Binary Environments Ltd. [EMAIL PROTECTED]
Re: [GENERAL] A Referntial integrity
Actually, you should (hopefully) be able to drop t2. That is supposed to remove the constraints referencing it (technically speaking drop table is probably supposed to take an argument to determine whether to do this, but we don't yet). If it's actually failing for you, can you give us a copy of the error message you're getting? You will need to recreate the constraint between t1 and t2 when you re-add t2 using alter table add constraint. - Original Message - From: Niraj Bhatt To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, July 06, 2000 7:29 AM Subject: [GENERAL] A Referntial integrity Hello, We are using a postgresql-7.0.2. Consider the following scenario we have created a table t1 with columns c1 : having referential integrity with t2.c1 c2 : having referential integrity with t3.c2 where t2 and t3 are different tables Assume that t2 has also got a referential integrity with t4.c3 where c3 is a column in t2 as well. Now I want to drop a constraint of table t2 that is referring to t4.c3. As per the documentation one can not drop a constraint in alter table command. In this situation I need to drop the table t2. But I can not drop this table since it has got child in table t1. Do I need to drop t1 as well ?? This one is a classical example of master detail - detail relation ship with dependent details which is very trivial in real world. Infect in more complex design such detail - detail relationship can go upto several levels. Every time dropping a table is not good. Is there any other way to do this?? Please elaborate on this Regards Niraj Bhatt
Re: [GENERAL] proposed improvements to PostgreSQL license
Camm Maguire writes: > As would mine. The major cost associated with any software project is > the migration/learning curve time. Effective guarantees that this > time investment will have a long payoff period (i.e. there will be no > forced upgrades, mandatory incompatibilities, binary/linked library > legacy issues, proprietary usurpation, and disappearance of the > product altogether) are critical to the decision to begin putting > software into production. I don't see your point. The current BSD license does, all in all, allow you to do whatever you want with the product. So there will be no: * forced upgrades (No one can force you to upgrade.) * mandatory incompatibilities (No one can mandate anything, you can fix everything yourself.) * disappearance of the product (If you have the source, you have it and can continue to develop it.) You seem to be believing that with the BSD license someone can take over the product and retroactively re-license it. This is false on both counts: No one can take it over unless he buys out the copyright of each contributor (but no open source license prevents that), and no one can revoke the currently granted license. (Well, there is a faction of legal experts that believe that gratis licenses are revokable, but then every such license would be affected.) > As I see it, the only realistic objection to the GPL comes from those > who want to sell copies of the software, or software products based > upon it. The major pragmatic objection to the GPL license has been that it creates legal hassles which we currently don't have. If we allow anyone to use the product at will then we don't have anything to enforce. If we cover our own work with a bunch of conditions then we always have to worry about what we can do with the code and what we can't. > Most people in the "business community" have no such interest, But some do and there's no point in stopping them. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [GENERAL] Two many databases...
On Thu, Jul 06, 2000 at 12:21:31PM +0200, Robert wrote: > - each client gets separate database > > - one common database for all clients, each table has client_id field > added I'm not in your situation, but I can tell you that if you went with method two, I would not recommend your services to my clients. I work with many many law firms, and the possibility of their data getting mixed up with someone elses (no matter how remote) would prevent me from recommending that path. While the maintenance is a bigger problem, I don't see it at nightmare level. The biggest problem appears to occur when making table/index level changes. I would think that it should be possible to write a wrapper for those kind of updates that would make them across the board to each client. -- Stand Fast, tjg. Timothy Grant [EMAIL PROTECTED] Chief Technology Officer www.exceptionalminds.com Red Hat Certified Engineer (503) 246-3630 Avalon Technology Group, Inc. fax (503) 246-3124 Linux...Because crashing isn't normal<
Re: [GENERAL] foreign key lost
thanks, i just discovered that before i got your message. now that i have that shovel, maybe i should whack myself with it!!! :) thanks again, mikeo At 02:34 PM 7/7/00 -0400, Tom Lane wrote: >mikeo <[EMAIL PROTECTED]> writes: >> hi, i accidently deleted a foreign key trigger from pg_trigger > >Did you reach into pg_trigger and delete the record yourself, or did >a system bug cause the problem? > >> and now cannot get at the table. i get the message: >> ERROR: RelationBuildTriggers: 1 record(s) not found for rel feature_code >> can anyone give me a shovel :) >> or a little help to get out of this please? > >You'll need to tweak the table's pg_class entry so that its reltriggers >column shows the same number of triggers that are actually there for it >in pg_trigger. A quick "UPDATE pg_class" should get you out of it. > > regards, tom lane >
Re: [GENERAL] foreign key lost
mikeo <[EMAIL PROTECTED]> writes: > hi, i accidently deleted a foreign key trigger from pg_trigger Did you reach into pg_trigger and delete the record yourself, or did a system bug cause the problem? > and now cannot get at the table. i get the message: > ERROR: RelationBuildTriggers: 1 record(s) not found for rel feature_code > can anyone give me a shovel :) > or a little help to get out of this please? You'll need to tweak the table's pg_class entry so that its reltriggers column shows the same number of triggers that are actually there for it in pg_trigger. A quick "UPDATE pg_class" should get you out of it. regards, tom lane
[GENERAL] A Referntial integrity
Hello, We are using a postgresql-7.0.2. Consider the following scenario we have created a table t1 with columns c1 : having referential integrity with t2.c1 c2 : having referential integrity with t3.c2 where t2 and t3 are different tables Assume that t2 has also got a referential integrity with t4.c3 where c3 is a column in t2 as well. Now I want to drop a constraint of table t2 that is referring to t4.c3. As per the documentation one can not drop a constraint in alter table command. In this situation I need to drop the table t2. But I can not drop this table since it has got child in table t1. Do I need to drop t1 as well ?? This one is a classical example of master detail - detail relation ship with dependent details which is very trivial in real world. Infect in more complex design such detail - detail relationship can go upto several levels. Every time dropping a table is not good. Is there any other way to do this?? Please elaborate on this Regards Niraj Bhatt
[GENERAL] foreign key lost
hi, i accidently deleted a foreign key trigger from pg_trigger and now cannot get at the table. i get the message: ERROR: RelationBuildTriggers: 1 record(s) not found for rel feature_code can anyone give me a shovel :) or a little help to get out of this please? TIA, mikeo
[GENERAL] JDBC: Encrypted connections? SSL?
With PostgreSQL is there any way to have encrypted connections to the database? Also, once the SSL patent expires in Sept or Oct of this year has any thought been given to SSL support? Could one easily implement this by picking up the source to the OpenBSD package that is for this purpose? They have some way around the patent that involves a separate download.
Re: [GENERAL] 'Zombie' tables
Steve Heaven <[EMAIL PROTECTED]> writes: > We run 6.5.2 on a Mandrake Linux 6.1 box. > The scripts create various temporary tables which are then later > dropped. Occasionally the 'DROP' doesnt work properly. The file > base// gets deleted, but the entry in pg_class > doesnt. This leads to a table that cant be dropped. (see below) The only > way out of this that we have found is to manually copy another file to > base// and then drop it. That's the standard workaround. > Is this a bug or are we doing something wrong? It's a bug. Fixed in 7.0 as far as I know. regards, tom lane
[GENERAL] view permissions problem - featuer or bug?
I have a set of tables and some views which perform calculations on those table to which no one except the group officestaff has any permissions. No problem. I tried to create views to which some particular client would have permissions. These views would be filtered versions of the private views. Herein lies the problem. If any of those underlying veiws call functions that access tables to which the user does not have permissions, or if any of those underlying views have sub-select statements (such as "where x in (select . . . )") the user gets access denied errors. Is this a feature or a bug? On the one hand, it certainly provides tight security. However, it seems like if you give someone permissions on a view, that view ought to be allows to perform whatever it needs to get the data back out regardless of other underlying permissions. Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
Re: [GENERAL] Find all the dates in the calendar week?
On Fri, 7 Jul 2000, Thomas Lockhart wrote: > > > ISO-week: week that has more than 4 day and start in Sunday. > > What about weeks that start in Monday (as here in Norway)? > > You can probably calculate that from the existing date/time code. But it > isn't ISO-8601. Karel was referring to the ISO-defined week for which > the first week of the year is that week which contains a Thursday. It > has the rather strange outcome that for some years, days in the previous > or subsequent calendar year fall into the adjacent "ISO-year". > Is anywhere on net available see this ISO date/time definition? Karel
Re: [GENERAL] PostgreSQL & the BSD License
> Now, a) is easily fixable by just extending the date to 2000, but that > still only covers "UNIVERSITY OF CALIFORNIA", and none of the actual > developers ... afaik we can't unilaterally alter the original license, either for dates or for participants. However, we can send along a second license (or first, primary, license) in the same file. - Thomas
Re: [GENERAL] Find all the dates in the calendar week?
On Thu, 6 Jul 2000, Helge Haugland wrote: > On Thu, Jul 06, 2000 at 15:21:53 +0200, Karel Zak wrote: > > ISO-week: week that has more than 4 day and start in Sunday. > > What about weeks that start in Monday (as here in Norway)? Yes, I known, in my country too. A question is how discern it --- via 'SET WEEKSTART TO Monday'? Thomas, have you some idea about mon-weeks? Karel
