Re: [GENERAL] Getting multiple field unique index to distinguish NULLs.
On Fri, 10 Sep 1999, Tom Lane wrote: > Stuart Rison <[EMAIL PROTECTED]> writes: > > Now this would work except that there's a problem with pl/pgSQL such that > > when you pass several values to a plpgsql function and one of them is > > NULL, then all values passed to the function become NULL. > > Yes. That's not plpgsql's fault; there's a basic design limitation in > the function-call interface (only one isnull flag gets passed). There > has been some talk on the pghackers list of fixing this, but I do not > know if it will happen any time soon or not. Fixing that interface > would mean revising *all* the code that implements any SQL function or > operator, so it'd be pretty tedious --- and it'd break any C-coded > user functions, too. It'll probably get done eventually, maybe even > for 6.6, but don't hold your breath... > > regards, tom lane > Aha! Thanks a lot for that Tom, I've been wondering for ages if this NULL behaviour was just a version things (I've got 6.4.2) or a more profound 'problem'. Hum... how unfortunate that it's the latter. Personally, and this is completely sellfish -I use functions quite a lot- I'd love to see this fixed asap! Alas, can't do it myself, I barely managed to get 'hello world' to work in C (he weeps)... Whilst I'm on a whimsical trip, my other top wishes remain: 1) outer joins (not 'workarounds') 2) referential integrity (not refint.c) Still PG is top-tastic all the same. Thanks to all Hackers out there! Cheers, S. Stuart C. G. Rison Department of Biochemistry and Molecular Biology Gower Street, London, WC1E 6BT, United Kingdom Tel. 0207 504 2303, Fax. 0207 380 7193 e-mail: [EMAIL PROTECTED]
Re: [GENERAL] shutdown gracefully & single user mode?
Jeff, Bryan, Teodor: thank you all so much!!! one related/furthur question: >0. LOCK TABLE IN EXCLUSIVE MODE this is what I'm concerned. Now, the "shutdown" a table gracefully is solved. How about the whole postmaster (in some other situation), how can I shut it down GRACEFULLY (i.e., I know that the data will not be corrupted because of my "shutdown". Also, how do I know that there is no user connected? BTW, It's a cgi-db). amy __ Get Your Private, Free Email at http://www.hotmail.com
Re: [GENERAL] max() question
> > On Tue, 14 Sep 1999, you wrote: > > I have a table with and email address and a datetime timestamp. What > > would be the easiest way to select the 3 earliest timestamps (earliest > > being furthest in the past from the current date) > > lots of ways =) though the first that comes to mind is the use of cursors. > order by DATEFIELD asc and just grab the first three.. or if that don't work for > ya (for whatever reason)... you could select the min(DATEFIELD), then select the > min(DATEFIELD) where DATEFIELD > (that last select's return), then again for > the third... this is convoluted, but if you are doing this over a network, a > cursor may not be the easiest thing to implement, depending on what you are > using. as for performance of this? i dunno. cursor would be best. Hows about: select * from table order by timestamp limit 3 That should do it ~Michael
Re: [GENERAL] max() question
On Tue, 14 Sep 1999, you wrote: > I have a table with and email address and a datetime timestamp. What > would be the easiest way to select the 3 earliest timestamps (earliest > being furthest in the past from the current date) lots of ways =) though the first that comes to mind is the use of cursors. order by DATEFIELD asc and just grab the first three.. or if that don't work for ya (for whatever reason)... you could select the min(DATEFIELD), then select the min(DATEFIELD) where DATEFIELD > (that last select's return), then again for the third... this is convoluted, but if you are doing this over a network, a cursor may not be the easiest thing to implement, depending on what you are using. as for performance of this? i dunno. cursor would be best. -- Aaron J. Seigo Sys Admin
[GENERAL] Permission problem with COPY FROM
Hi, Each time I try to insert an ascii file with the COPY FROM command, I get the following message: "ERROR: COPY command, running in backend with effective uid 501 (that's Postgres), could not open file '/usr/local/.../cltclr001' for reading. Error: Permission not allowed (13)." What rights do I have to put to process the COPY command inside PSQL. I have try nearly everything, actual rights: uog+rw even on the directory. What's wrong. Stephane FILLON
[GENERAL] BUG with UNIQUE clause
Hi,
The UNIQUE constraint doesn't work on a field if
I use a DEFAULT clause on a table.
The following table works with UNIQUE
constraint:
create table cltclt001(
tcid int2,
tcnom text unique
);
but this one accept several same tcnom
value:
create table cltclt001(
tcid int2 default
nextval('cltcls001'),
tcnom text unique
);
What's wrong with my table ?
Thanks in advance.
Stephane FILLON
[GENERAL] max() question
I have a table with and email address and a datetime timestamp. What would be the easiest way to select the 3 earliest timestamps (earliest being furthest in the past from the current date)
Re: [GENERAL] shutdown gracefully & single user mode?
amy cheng wrote: > > hi, all experts there, greetings! > > Just minutes ago, my boss found out one of the attributes in a > table is too short (varchar 64 for url), we need to make > it wider to 85 A.S.A.P. Seems that alter table can not do it. > So, I used pg_dump, (how to do it gracefully?) immediately drop the table, > shutdown the postmaster (not necessary?) and change the dumped table, and > then restart the postmaster, use pgsql to reload the data. Our database is > not large YET. So, it took ONLY 10 minutes to re-load. > > the job was done. But I feel nervous -- because I expect this will > happen again SOON. What is the "standard" procedure for postgreSQL to > do such kind of things? more specifically (maybe there are some other > ways?): > > 1) how to shutdown postmaster gracefully (I simply used > kill proID. I feel lucky that I do not need to use -9 ! ) so > that I'm sure the data is not corrupted? > 2) how to "shutdown"(drop) just one table gracefully? > 3) any simpler way (alter-table-like) to widden the attribute? > 4) single user mode? > > thanks in advance!!! > gee :) why don't you just : 0. LOCK TABLE IN EXCLUSIVE MODE 1. create a new table with the field adjusted to the needed size. 2. INSERT INTO new_table SELECT ... FROM old_table ... 3. DROP TABLE old_table 4. ALTER TABLE new_table RENAME TO old_table eventually within a transaction. Also a SELECT INTO may be a shortcut, but I didn't test that. -- CIMPOESU Teodor, Web Programmer @ DIGICOM S.A. Bucharest, Romania @ Internet, site development @ [EMAIL PROTECTED],+(401)-330.47.28 official home page ~ http://www.digiro.net/ Internet web page ~ http://internet.digiro.net/
Re: [GENERAL] shutdown gracefully & single user mode?
> hi, all experts there, greetings! > > Just minutes ago, my boss found out one of the attributes in a > table is too short (varchar 64 for url), we need to make > it wider to 85 A.S.A.P. Seems that alter table can not do it. > So, I used pg_dump, (how to do it gracefully?) immediately drop the table, > shutdown the postmaster (not necessary?) and change the dumped table, and > then restart the postmaster, use pgsql to reload the data. Our database is > not large YET. So, it took ONLY 10 minutes to re-load. > > the job was done. But I feel nervous -- because I expect this will > happen again SOON. What is the "standard" procedure for postgreSQL to > do such kind of things? more specifically (maybe there are some other > ways?): > > 1) how to shutdown postmaster gracefully (I simply used > kill proID. I feel lucky that I do not need to use -9 ! ) so > that I'm sure the data is not corrupted? > 2) how to "shutdown"(drop) just one table gracefully? > 3) any simpler way (alter-table-like) to widden the attribute? > 4) single user mode? I usually create a new table, do an insert of data from a select on the old table, then rename the tables. Don't forget to recreate any indexes. I usually write it all up in a single script and run it on a test database before hand. For small tables or tables that don't under normal usage this works reasonably well. For larger more criticle tables I would be tempted to pull the ethernet plug during the changeover. Course that depends on how your system is laid out. NOTE: I don't rely on the alter table add column feature. It seems to ignore the new default value entirely (even on data added after the alteration). Also I suggest using the 'text' data type. It has no length limit other than the 8K tuple size.
[GENERAL] shutdown gracefully & single user mode?
hi, all experts there, greetings! Just minutes ago, my boss found out one of the attributes in a table is too short (varchar 64 for url), we need to make it wider to 85 A.S.A.P. Seems that alter table can not do it. So, I used pg_dump, (how to do it gracefully?) immediately drop the table, shutdown the postmaster (not necessary?) and change the dumped table, and then restart the postmaster, use pgsql to reload the data. Our database is not large YET. So, it took ONLY 10 minutes to re-load. the job was done. But I feel nervous -- because I expect this will happen again SOON. What is the "standard" procedure for postgreSQL to do such kind of things? more specifically (maybe there are some other ways?): 1) how to shutdown postmaster gracefully (I simply used kill proID. I feel lucky that I do not need to use -9 ! ) so that I'm sure the data is not corrupted? 2) how to "shutdown"(drop) just one table gracefully? 3) any simpler way (alter-table-like) to widden the attribute? 4) single user mode? thanks in advance!!! amy __ Get Your Private, Free Email at http://www.hotmail.com
Re: [GENERAL] two phase commit?
>We still don't know if Postgresql can do it, do we? no, we do not know ;-) anybody there please? amy __ Get Your Private, Free Email at http://www.hotmail.com
RE: [GENERAL] Getting multiple field unique index to distinguish NULLs.
On Fri, 10 Sep 1999, omid omoomi wrote: > hi evey body, > I've some comments : > 1) I believe that there are some problems in table and db design and you are > doing the job in its hard way! There seems to be no primarry key defined on > the table. you can compose it at the end of your DDL(create table) > statement,like this : > primary key ( level1,level2,level3,level4 ) > this would make so many things be handeled transparently by the database( > i.e indexing and ...) . are yes, but that would still fail not work in my case. unless... > 2) Try to define some default values for each of columns again at your DDL > statements. This will avoid null values from being assigned to the columns.( > important: let it be different for each column,i.e 0 for leve1,1 for > level2,2for level3,3 for level4, other wise you may face some problems with > dupplication errors! ) Precicely, this seems to be the general concensus, I need to have some non-NULL empty value token. Thanks to everybody who e-mailed help/suggestions. regards, S. > > >From: "Jackson, DeJuan" <[EMAIL PROTECTED]> > >To: Stuart Rison <[EMAIL PROTECTED]>,pgsql-general > ><[EMAIL PROTECTED]>,pgsql-sql <[EMAIL PROTECTED]> > >Subject: RE: [GENERAL][SQL] Getting multiple field unique index to disting > >uish NULLs. > >Date: Fri, 10 Sep 1999 10:44:38 -0500 > > > >Use 0 or -1 instead of NULL. > > DEJ > > > > > -Original Message- > > > From: Stuart Rison [SMTP:[EMAIL PROTECTED]] > > > Sent: Friday, September 10, 1999 8:02 AM > > > To: pgsql-general; pgsql-sql > > > Subject: [GENERAL][SQL] Getting multiple field unique index to > > > distinguish NULLs. > > > > > > Dear All, > > > > > > Consider the following table: > > > > > > myscheme_id|level1|level2|level3|level4|function > > > ---+--+--+--+--+--- > > > 11| 4| 5| 1| 3|Long John Silver > > > 12| 1242| 3|44| 5|Metabolism > > > 13| 1| 2| 3| 4|Transport > > > 1| 1| 4| 3| |Energy > > > 9| 1| 2| 3| 1|Signaling > > > 3| 1| 2| 3| 2|test1 > > > 18| 1| 2| | |test2 > > > 19| 1| 2| | |test3 > > > 21| 1| 2| | |test4 > > > > > > This is essentially a hierarchical key set-up; each function can be > > > identified by a key (which can be thought of as the concatenation of > > > level1, level2, level3 and level4). > > > > > > But you can add a function at any level (i.e. only level1 must be given) > > > so: > > > > > > INSERT INTO myscheme_funcat (level1,function) VALUES (1,'Top level > > > function'); > > > > > > is OK. > > > > > > I am trying to get the database to reject INSERTS for keys already > > > occupied > > > > > > CREATE UNIQUE INDEX myscheme_idx ON myscheme_funcat > > > (level1,level2,level3,level4); > > > > > > The problem is that the indexing considers all NULLs to be distinct > >thus: > > > > > > INSERT INTO myscheme_funcat (level1,level2,level3,level4,function) > >VALUES > > > (4,5,76,NULL,'OK'); > > > INSERT 1044737 1 > > > > > > but so does > > > INSERT INTO myscheme_funcat (level1,level2,level3,level4,function) > >VALUES > > > > > > (4,5,76,NULL,'Should fail because position 4.5.76 already occupied'); > > > INSERT 1044738 1 > > > > > > Works because 4,5,76,NULL is considered DISTINCT from 4,5,76,NULL (all > > > NULLs are different). > > > > > > So, any ideas, workarounds etc.?? > > > > > > cheers, > > > > > > S. > > > > > > ### Please Note New Details ### > > > Stuart C. G. Rison > > > Department of Biochemistry and Molecular Biology > > > Gower Street, London, WC1E 6BT, United Kingdom > > > Tel. 0207 504 2303, Fax. 0207 380 7193 > > > e-mail: [EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > > > __ > Get Your Private, Free Email at http://www.hotmail.com > > > ### Please Note New Details ### Stuart C. G. Rison Department of Biochemistry and Molecular Biology Gower Street, London, WC1E 6BT, United Kingdom Tel. 0207 504 2303, Fax. 0207 380 7193 e-mail: [EMAIL PROTECTED]
