Thread: "duplicate key value": version 8.4 vs 8.1
Hello, I am a Postgres newbie using a C program to add records to a Postgres database. When I run it against an 8.4.4 version of Postgres, data inserted into a table which violate an unique index are reported back to the program via sqlca.sqlcode as -403 ('duplicate key value violates unique constraint "my_index"'). However, when I run the same program against an 8.1.2 database, the error isn't trapped. I'm explicitly looking for a -403 code via sqlca.sqlcode so am assuming that the error code is different in this version of the database. I've had a look on Teh Interwebs around to try and find out what the equivalent code is for 8.1.2 but have had no success so far. Could anyone point me in the right direction, please? Thanks in advance for any assistance, Mark
mountain oaf <mountainoaf@gmail.com> writes: > When I run it against an 8.4.4 version of Postgres, data inserted into > a table which violate an unique index are reported back to the program > via sqlca.sqlcode as -403 ('duplicate key value violates unique > constraint "my_index"'). > However, when I run the same program against an 8.1.2 database, the > error isn't trapped. I'm explicitly looking for a -403 code via > sqlca.sqlcode so am assuming that the error code is different in this > version of the database. AFAICS the ecpg code around that hasn't changed since 8.1, so I think your assumption is in error. You might try looking into the server log to see what it thinks is happening. regards, tom lane
> AFAICS the ecpg code around that hasn't changed since 8.1, so I think > your assumption is in error. You might try looking into the server > log to see what it thinks is happening. I've just done a test on the 8.1.2 database where I purposely added a duplicate record and the sqlca.sqlcode returned a -400 error. Mark
mountain oaf <mountainoaf@gmail.com> writes: >> AFAICS the ecpg code around that hasn't changed since 8.1, so I think >> your assumption is in error. �You might try looking into the server >> log to see what it thinks is happening. > I've just done a test on the 8.1.2 database where I purposely added a > duplicate record and the sqlca.sqlcode returned a -400 error. So, the server is returning some error code, but it's not 23505. Again, I'd suggest looking at the server log to see what it thinks is happening. My bet is that you're trying to use some feature that doesn't exist in 8.1 ... regards, tom lane
> So, the server is returning some error code, but it's not 23505. > Again, I'd suggest looking at the server log to see what it thinks > is happening. My bet is that you're trying to use some feature > that doesn't exist in 8.1 ... Not sure that I can gain access to the server logs, but thanks for the suggestion. I'm not doing anything arcane - as described, I a, just trying to insert a record which breaks a unique index's integrity and using sqlca.sqlcode to report the result return when doing so. Mark
On Thu, Oct 21, 2010 at 10:59:50AM +1300, mountain oaf wrote: > Hello, > > I am a Postgres newbie using a C program to add records to a Postgres database. > > When I run it against an 8.4.4 version of Postgres, data inserted into > a table which violate an unique index are reported back to the program > via sqlca.sqlcode as -403 ('duplicate key value violates unique > constraint "my_index"'). > > However, when I run the same program against an 8.1.2 database, the > error isn't trapped. I'm explicitly looking for a -403 code via > sqlca.sqlcode so am assuming that the error code is different in this > version of the database. > > I've had a look on Teh Interwebs around to try and find out what the > equivalent code is for 8.1.2 but have had no success so far. Could > anyone point me in the right direction, please? > > Thanks in advance for any assistance, > > Mark > Hi Mark, You may want to try the latest version of 8.1, 8.1.22 or 20 bugfix releases more current. As has been oft repeated here, they do not release them for fun. They fix real and critical bugs. Cheers, Ken
> You may want to try the latest version of 8.1, 8.1.22 or 20 bugfix > releases more current. As has been oft repeated here, they do not > release them for fun. They fix real and critical bugs. Thanks for the thought. Unfortunately this is not an option as it is running a client's system at a remote site. Mark
mountain oaf <mountainoaf@gmail.com> writes: >> You may want to try the latest version of 8.1, 8.1.22 or 20 bugfix >> releases more current. As has been oft repeated here, they do not >> release them for fun. They fix real and critical bugs. > Thanks for the thought. Unfortunately this is not an option as it is > running a client's system at a remote site. You're being very irresponsible to your client if you're not leaning on them, *hard*, to run a less obsolete version. Or are you hoping to collect additional consulting fees to help them get out of trouble when that old version eats their data? regards, tom lane
mountain oaf wrote: >> You may want to try the latest version of 8.1, 8.1.22 or 20 bugfix >> releases more current. As has been oft repeated here, they do not >> release them for fun. They fix real and critical bugs. > > Thanks for the thought. Unfortunately this is not an option as it is > running a client's system at a remote site. Upgrades do not require on-site access. Every production system should have a backup; so I keep a second server on standby. For upgrades; I upgrade the backup system; test my apps on the upgraded system; then with only a few minutes downtime I resync the data and make backup hardware productions server. Then I upgrade the original system; test it and migrate back again. This process tests the disaster recovery process with every upgrade. This process only requires on-site visit when upgrading the OS; never packages like Postgresql.