[HACKERS] Change request ...
Even though many of the list members of [EMAIL PROTECTED] suggest that the following is an expected behaviour, my experience in other databases doesn't permit me accept it as such. I am putting this for the kind consideration of this list Description : I have two tables with the same data , While I issue an update command to increment the value of a unique field by 1, the statement fails in one table and will succeed in the other table. Following is the script to reproduce the behaviour. CREATE TABLE master1 ( m1 INT primary key , m2 INT unique ) ; INSERT INTO master1 VALUES ( 1, 1 ) ; INSERT INTO master1 VALUES ( 2, 2) ; UPDATE master1 SET m2 = m2 + 1; ERROR: duplicate key violates unique constraint master1_m2_key SQL state: 23505 CREATE TABLE master2 ( m1 INT primary key , m2 INT unique ) ; INSERT INTO master2 VALUES ( 2, 2 ) ; INSERT INTO master2 VALUES ( 1, 1) ; UPDATE master2 SET m2 = m2 + 1; Am I right in requesting to this forum to make this update a success in either case. Anoo S Visit our Website at http://www.rmesi.co.in This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications. Freedom of Information Act 2000 This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI's commercial interests. This email has been scanned for viruses by Trend ScanMail.
Re: [HACKERS] Change request ...
Anoo Sivadasan Pillai wrote: Even though many of the list members of [EMAIL PROTECTED] suggest that the following is an expected behaviour, my experience in other databases doesn't permit me accept it as such. I am putting this for the kind consideration of this list I think it's more of a known limitation than anything else. Description : I have two tables with the same data , While I issue an update command to increment the value of a unique field by 1, the statement fails in one table and will succeed in the other table. Following is the script to reproduce the behaviour. AFAIK (and I'm not a developer) the problem is in two parts: 1. The only way to enforce UNIQUE at the moment is via a unique index. 2. A unique index enforces its requirements immediately. The reasons why it's not been addressed yet are: 1. Most unique constraints are on keys which aren't updated, so many people never have this problem. 2. It's quite a bit of work to solve. 3. There is a work-around (x=-x; x=-x + 1) It's already on the TODO: http://www.postgresql.org/docs/faqs.TODO.html Search for Allow DEFERRABLE and end-of-statement UNIQUE constraints If you can program in C or can fund someone who can, I'm sure people would like to see it fixed for version 8.4. Don't underestimate the work involved though. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Change request ...
On Thu, Sep 27, 2007 at 02:48:52PM +0530, Anoo Sivadasan Pillai wrote: Description : I have two tables with the same data , While I issue an update command to increment the value of a unique field by 1, the statement fails in one table and will succeed in the other table. Following is the script to reproduce the behaviour. Correct. Unique constraints are not deferrable. I'm sure there are people who would like it fixed, but there is currently not even a proposal on how to do it. It's a hard problem, for which there are well-known work-arounds. In practice this problem don't come up too often. It's been on the TODO list for at least 5 years... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Change request ...
Martijn van Oosterhout wrote: On Thu, Sep 27, 2007 at 02:48:52PM +0530, Anoo Sivadasan Pillai wrote: Description : I have two tables with the same data , While I issue an update command to increment the value of a unique field by 1, the statement fails in one table and will succeed in the other table. Following is the script to reproduce the behaviour. Correct. Unique constraints are not deferrable. I'm sure there are people who would like it fixed, but there is currently not even a proposal on how to do it. It's a hard problem, for which there are well-known work-arounds. In practice this problem don't come up too often. It's been on the TODO list for at least 5 years... Wow, I was not aware of this limitation. MySQL hacks around this issue by allowing an ORDER BY in UPDATE (and DELETE) statements. regards, Lukas ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Change request ...
On Thu, Sep 27, 2007 at 12:15:24PM +0200, Lukas Kahwe Smith wrote: It's been on the TODO list for at least 5 years... Wow, I was not aware of this limitation. MySQL hacks around this issue by allowing an ORDER BY in UPDATE (and DELETE) statements. There is a similar workaround for postgres too, but it's a hack, it won't work in the general case. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Change Request: \pset pager off in pg_dumpall
Your pg_dump's actually invoke the pager? Are you manually starting psql, then doing \i dumpfile? Why would you do that rather than psql template1 dumpfile? Because I'm a dork :-). Seriously, sometimes it's useful. The most useful reason (and I wish you could turn it on with psql file) is the line number in the file where any errors occur. (TODO item?) Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Change Request: \pset pager off in pg_dumpall
Larry Rosenman [EMAIL PROTECTED] writes: On Mon, 11 Aug 2003, Bruce Momjian wrote: Larry Rosenman wrote: Can we modify pg_dumpall (or pg_dump?) to include a \pset pager off to prevent the setval() calls from halting an interactive \i of the dump file? Your pg_dump's actually invoke the pager? Seriously, sometimes it's useful. Are you using any nonstandard settings in ~/.psqlrc? I've never seen this happen myself. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Change Request: \pset pager off in pg_dumpall
Christopher Kings-Lynne writes: The most useful reason (and I wish you could turn it on with psql file) is the line number in the file where any errors occur. psql -f file will do that. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Change Request: \pset pager off in pg_dumpall
--On Tuesday, August 12, 2003 09:30:39 -0400 Tom Lane [EMAIL PROTECTED] wrote: Larry Rosenman [EMAIL PROTECTED] writes: Nope. There is no .psqlrc. It seems to be new with 7.4cvs. (dunno about earlier 7.4), but it definitely did NOT happen with 7.3.x Hmph. There have been some changes in 7.4 psql's pager support, but I can't see anything there that looks like it would cause this. And it (still) doesn't happen on my machine. Would you trace through PageOutput and see why it's deciding to use the pager for short output? seems to do it for ANY SRF, if that helps any. I.E. \d, and show all;. LER regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Change Request: \pset pager off in pg_dumpall
Larry Rosenman [EMAIL PROTECTED] writes: Nope. There is no .psqlrc. It seems to be new with 7.4cvs. (dunno about earlier 7.4), but it definitely did NOT happen with 7.3.x Hmph. There have been some changes in 7.4 psql's pager support, but I can't see anything there that looks like it would cause this. And it (still) doesn't happen on my machine. Would you trace through PageOutput and see why it's deciding to use the pager for short output? regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Change Request: \pset pager off in pg_dumpall
On Mon, 11 Aug 2003, Bruce Momjian wrote: Larry Rosenman wrote: Can we modify pg_dumpall (or pg_dump?) to include a \pset pager off to prevent the setval() calls from halting an interactive \i of the dump file? Your pg_dump's actually invoke the pager? Are you manually starting psql, then doing \i dumpfile? Why would you do that rather than psql template1 dumpfile? Because I'm a dork :-). Seriously, sometimes it's useful. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Change Request: \pset pager off in pg_dumpall
Can we modify pg_dumpall (or pg_dump?) to include a \pset pager off to prevent the setval() calls from halting an interactive \i of the dump file? Thanks, LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Change Request: \pset pager off in pg_dumpall
Larry Rosenman wrote: Can we modify pg_dumpall (or pg_dump?) to include a \pset pager off to prevent the setval() calls from halting an interactive \i of the dump file? Your pg_dump's actually invoke the pager? Are you manually starting psql, then doing \i dumpfile? Why would you do that rather than psql template1 dumpfile? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Change Request: \pset pager off in pg_dumpall
--On Monday, August 11, 2003 20:36:11 -0400 Tom Lane [EMAIL PROTECTED] wrote: Larry Rosenman [EMAIL PROTECTED] writes: On Mon, 11 Aug 2003, Bruce Momjian wrote: Larry Rosenman wrote: Can we modify pg_dumpall (or pg_dump?) to include a \pset pager off to prevent the setval() calls from halting an interactive \i of the dump file? Your pg_dump's actually invoke the pager? Seriously, sometimes it's useful. Are you using any nonstandard settings in ~/.psqlrc? I've never seen this happen myself. Nope. There is no .psqlrc. It seems to be new with 7.4cvs. (dunno about earlier 7.4), but it definitely did NOT happen with 7.3.x LER regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
