"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(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)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.