Message-ID: <4EFE407D.6090404@greenpeace.org>
Date: Sat, 31 Dec 2011 11:51:41 +1300
From: Jan Bakuwel <jan.bakuwel@greenpeace.org>
Organization: Greenpeace (http://www.greenpeace.org)
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.24) Gecko/20111108 Lightning/1.0b2 Thunderbird/3.1.16
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Current transaction is aborted, commands ignored until
	end of transaction block
References: <4EFD3D32.9060902@greenpeace.org>
	<13131805-BCBB-42DF-953B-27EE36AAF213@yahoo.com>
	<4EFD79BA.50508@greenpeace.org>
	<201112301044.33861.leif@solumslekt.org>
In-Reply-To: <201112301044.33861.leif@solumslekt.org>
Content-Type: multipart/signed; protocol="application/pkcs7-signature";
	micalg=sha1; boundary="------------ms060907090506060901070302"

This is a cryptographically signed message in MIME format.

--------------ms060907090506060901070302
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi Leif,

On 30/12/11 22:44, Leif Biberg Kristensen wrote:
>  Fredag 30. desember 2011 09.43.38 skrev Jan Bakuwel :
>
>> Would be nice to have an option in PostgreSQL something along the line=
s
>> of:  'abort-transaction-on-constraint-violation =3D false'....
> That option is called MySQL with MyISAM tables.
>
> Seriously, if the user encounters a constraint violation, that is IMO a=
=20
> symptom of bad design. Such conditions should be checked and caught _be=
fore_=20
> the transaction begins.

Really?

One of my detail tables here is a list of codes. The design currently is
so that you are not allowed to add two identical codes in that table for
a particular related master record, ie. if you try it raises a
constraint violation (duplicate key). Users try anyway (you know those
pesky users doing things they're not supposed to do).

Why would that a bad design?

I simply want to tell the user: sorry you can't do this because it
violates a constraint (duplicate key).

Sometimes they try to delete something that has other records referring
to it and the database design is so that it won't cascade delete (for
various reasons). In that case I want to tell them: sorry you can't do
this because there are related records.

In a well designed system, you'd have those constraints at the database
level not the application level and use exception handling to deal with
these, not write tests to find out the possible error conditions
beforehand. Of course it's possible to write code around all of this
(and I'm starting to realise that is what I might have to do) but I
consider that bad design.

I don't claim to know all other RDBMS but I think PostgreSQL might be
one of the few (or only one) that considers a constraint violation
something really really really serious... so serious that the
transaction will have to be aborted. Quite a few other RDBMS will give
you the error but will also allow you to continue on your merry way (and
not loose everything you've done up to that point).

Why-o-why have the PostgreSQL developers decided to do it this way...?

regards,
Jan


--------------ms060907090506060901070302
Content-Type: application/pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"
Content-Description: S/MIME Cryptographic Signature

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIQHjCC
CAswggXzoAMCAQICAyACKjANBgkqhkiG9w0BAQUFADCB+DEhMB8GA1UEChMYR3JlZW5wZWFj
ZSBJbnRlcm5hdGlvbmFsMR4wHAYDVQQLExVDZXJ0aWZpY2F0ZSBBdXRob3JpdHkxMzAxBgkq
hkiG9w0BCQEWJGNlcnRpZmljYXRlLmF1dGhvcml0eUBncmVlbnBlYWNlLm9yZzESMBAGA1UE
BxMJQW1zdGVyZGFtMRYwFAYDVQQIEw1Ob3J0aCBIb2xsYW5kMQswCQYDVQQGEwJOTDFFMEMG
A1UEAxM8R3JlZW5wZWFjZSBHbG9iYWwgQXV0aGVudGljYXRpb24gYW5kIEVuY3J5cHRpb24g
Um9vdCBDQSAyMDEwMB4XDTExMDcxOTA5MjExNloXDTE2MDcxNzA5MjExNlowgb0xCzAJBgNV
BAYTAk5MMRYwFAYDVQQIEw1Ob3J0aCBIb2xsYW5kMSEwHwYDVQQKExhHcmVlbnBlYWNlIElu
dGVybmF0aW9uYWwxHjAcBgNVBAsTFUNlcnRpZmljYXRlIEF1dGhvcml0eTESMBAGA1UEBxMJ
QW1zdGVyZGFtMRQwEgYDVQQDEwtqYW4uYmFrdXdlbDEpMCcGCSqGSIb3DQEJARYaamFuLmJh
a3V3ZWxAZ3JlZW5wZWFjZS5vcmcwggIiMA0GCSqGSIb3DQEBAQUAA4ICDwAwggIKAoICAQCy
0wcYLAIQbP9MSESY1RaR/f8Ram8TIOPhajUK8dvMFqSb/7/21hPdsON0o21iPcfQP7pLPRTo
FVpW2CnCfJuCzMZVx7n/o+T47S8sN2YzgqkUPd63HmPO4AIE8zdWOmG6aCuy5wttWitGPr3D
krJzRA/WRbhKc+ebKJAfFMr8N9kKPK6tIMRcn52iZbMQUbGeAHzKXhrT9QH7p+r5V+89L8fJ
YmI2zne7rIj8db3rxyZbsA11M5bXgDpJFeIwtibnBC/Jt00LViq8HHBM/eZUEwdPse90n17d
7tLeBuwZ7Dckkm+lugzhR2hnibVHtAdAquIotkgUqh3UQH4g2ZA0pPHwxf2xLfmFqVJj3Ode
VmKkYsj/P16V1lQpXqX//7eRm1N9j8kKllig8N3jkBdZFwXjRtbe6DUnZQFrVBuIKfA63RHN
sb6qas47RFOvVtblUY/DW2f6zCm6EsHtcMpNj8vxSeF5MUpO2y/YRXEDermXOyu/40yv8tXT
YwIdTfba/EvO4EBnXm6IXGnUSp8NEAVhc0/kB9vJ+pq9XO/UHFvo9fOeu3T7KgMNN31QhxlG
D2FGh5f8LlTNktRsdyMYCF2m85wUFV2GQOW3etGnEY4T0aLacikbWF/zwPYns9nnpvtTeKyP
fGloh30zyo00OVqhNnREH7A48rSp4qYJRwIDAQABo4IB1TCCAdEwCQYDVR0TBAIwADAdBgNV
HQ4EFgQU7Sg0TwH6c2T23HGF9Dhnjr/uEecwEQYJYIZIAYb4QgEBBAQDAgSwMCUGA1UdEQQe
MByBGmphbi5iYWt1d2VsQGdyZWVucGVhY2Uub3JnMEoGA1UdJQRDMEEGCCsGAQUFBwMBBggr
BgEFBQcDAgYIKwYBBQUHAwQGCisGAQQBgjcKAwMGCisGAQQBgjcKAwQGCWCGSAGG+EIEATCB
uAYJYIZIAYb4QgENBIGqFoGnR3JlZW5wZWFjZSBpcyBhbiBpbmRlcGVuZGVudCBnbG9iYWwg
Y2FtcGFpZ25pbmcgb3JnYW5pc2F0aW9uIHRoYXQgYWN0cyB0byBjaGFuZ2UgYXR0aXR1ZGVz
IGFuZCBiZWhhdmlvdXIsIHRvIHByb3RlY3QgYW5kIGNvbnNlcnZlIHRoZSBlbnZpcm9ubWVu
dCBhbmQgdG8gcHJvbW90ZSBwZWFjZS4wZAYDVR0fBF0wWzBZoFegVYZTaHR0cDovL2NybC5n
cmVlbnBlYWNlLm9yZy9HcmVlbnBlYWNlR2xvYmFsQXV0aGVudGljYXRpb25BbmRFbmNyeXB0
aW9uUm9vdENBMjAxMC5jcmwwDQYJKoZIhvcNAQEFBQADggIBABeaDIOCNVnvH3b+KqlidkTj
UKc51rm7JvxOhm84tgEJmuVk4NlAwxfXWKTGjVwzlvOG3OhpoxSc7rOnYTsxU27NDPbkRcIN
GcOIsgrmwlhmUzMpgQ+bN9CyHbaEdNKm5Aa/OTXvu0kSsgEz4Q/1Z+yk5G3+dpP32DY1waq9
zy3XPOBUTjX/A2y/LyaxFxnipBpt+mbA1Zd39s11A/aGbFpC5HrMJd6WH5669B/U7gwMdIxE
15P2O9hjfMT/BxfmWsa7rDvC+RuUtD1rQxXgAYwwnJaiHHl23Cwl5UiDABbK+Ox6kg6PPpzs
/vfPi7z0Cv7wwNsI7NMqVb2LTLbd3nmiqy7HSJCb4WyOh3jK9Aih7wH1BhFbIbygiP9B7UWX
ng8PVqzuhwJV8Ef2emJY4bAvcstayqY5X0QQHgFDXO3PYXbWbUtzplIQXgV9JQNdS5zPNw95
O2dMTWCVaHQ+vKSziF0sudjrUbI+w5dH6dai6y7h8+7kqZ3EOs3NFU3bSn34o+22Ly04xpSN
DLyQY3opgG5KVXJf5/GSPtC+mGIZm5O6Mgi4TWZecov37Gpm0d5G3tgZz8/i5Imdaq6zzRqW
LUR9K/UdXDis1kkLZZdrjkop/UEchhkIE87snUIr27FPPkpK2l+suNYKHrK+AGKA9cWVmXjX
wlBSrr/bnhxZMIIICzCCBfOgAwIBAgIDIAIqMA0GCSqGSIb3DQEBBQUAMIH4MSEwHwYDVQQK
ExhHcmVlbnBlYWNlIEludGVybmF0aW9uYWwxHjAcBgNVBAsTFUNlcnRpZmljYXRlIEF1dGhv
cml0eTEzMDEGCSqGSIb3DQEJARYkY2VydGlmaWNhdGUuYXV0aG9yaXR5QGdyZWVucGVhY2Uu
b3JnMRIwEAYDVQQHEwlBbXN0ZXJkYW0xFjAUBgNVBAgTDU5vcnRoIEhvbGxhbmQxCzAJBgNV
BAYTAk5MMUUwQwYDVQQDEzxHcmVlbnBlYWNlIEdsb2JhbCBBdXRoZW50aWNhdGlvbiBhbmQg
RW5jcnlwdGlvbiBSb290IENBIDIwMTAwHhcNMTEwNzE5MDkyMTE2WhcNMTYwNzE3MDkyMTE2
WjCBvTELMAkGA1UEBhMCTkwxFjAUBgNVBAgTDU5vcnRoIEhvbGxhbmQxITAfBgNVBAoTGEdy
ZWVucGVhY2UgSW50ZXJuYXRpb25hbDEeMBwGA1UECxMVQ2VydGlmaWNhdGUgQXV0aG9yaXR5
MRIwEAYDVQQHEwlBbXN0ZXJkYW0xFDASBgNVBAMTC2phbi5iYWt1d2VsMSkwJwYJKoZIhvcN
AQkBFhpqYW4uYmFrdXdlbEBncmVlbnBlYWNlLm9yZzCCAiIwDQYJKoZIhvcNAQEBBQADggIP
ADCCAgoCggIBALLTBxgsAhBs/0xIRJjVFpH9/xFqbxMg4+FqNQrx28wWpJv/v/bWE92w43Sj
bWI9x9A/uks9FOgVWlbYKcJ8m4LMxlXHuf+j5PjtLyw3ZjOCqRQ93rceY87gAgTzN1Y6Ybpo
K7LnC21aK0Y+vcOSsnNED9ZFuEpz55sokB8Uyvw32Qo8rq0gxFyfnaJlsxBRsZ4AfMpeGtP1
Afun6vlX7z0vx8liYjbOd7usiPx1vevHJluwDXUzlteAOkkV4jC2JucEL8m3TQtWKrwccEz9
5lQTB0+x73SfXt3u0t4G7BnsNySSb6W6DOFHaGeJtUe0B0Cq4ii2SBSqHdRAfiDZkDSk8fDF
/bEt+YWpUmPc515WYqRiyP8/XpXWVClepf//t5GbU32PyQqWWKDw3eOQF1kXBeNG1t7oNSdl
AWtUG4gp8DrdEc2xvqpqzjtEU69W1uVRj8NbZ/rMKboSwe1wyk2Py/FJ4XkxSk7bL9hFcQN6
uZc7K7/jTK/y1dNjAh1N9tr8S87gQGdebohcadRKnw0QBWFzT+QH28n6mr1c79QcW+j18567
dPsqAw03fVCHGUYPYUaHl/wuVM2S1Gx3IxgIXabznBQVXYZA5bd60acRjhPRotpyKRtYX/PA
9iez2eem+1N4rI98aWiHfTPKjTQ5WqE2dEQfsDjytKnipglHAgMBAAGjggHVMIIB0TAJBgNV
HRMEAjAAMB0GA1UdDgQWBBTtKDRPAfpzZPbccYX0OGeOv+4R5zARBglghkgBhvhCAQEEBAMC
BLAwJQYDVR0RBB4wHIEaamFuLmJha3V3ZWxAZ3JlZW5wZWFjZS5vcmcwSgYDVR0lBEMwQQYI
KwYBBQUHAwEGCCsGAQUFBwMCBggrBgEFBQcDBAYKKwYBBAGCNwoDAwYKKwYBBAGCNwoDBAYJ
YIZIAYb4QgQBMIG4BglghkgBhvhCAQ0EgaoWgadHcmVlbnBlYWNlIGlzIGFuIGluZGVwZW5k
ZW50IGdsb2JhbCBjYW1wYWlnbmluZyBvcmdhbmlzYXRpb24gdGhhdCBhY3RzIHRvIGNoYW5n
ZSBhdHRpdHVkZXMgYW5kIGJlaGF2aW91ciwgdG8gcHJvdGVjdCBhbmQgY29uc2VydmUgdGhl
IGVudmlyb25tZW50IGFuZCB0byBwcm9tb3RlIHBlYWNlLjBkBgNVHR8EXTBbMFmgV6BVhlNo
dHRwOi8vY3JsLmdyZWVucGVhY2Uub3JnL0dyZWVucGVhY2VHbG9iYWxBdXRoZW50aWNhdGlv
bkFuZEVuY3J5cHRpb25Sb290Q0EyMDEwLmNybDANBgkqhkiG9w0BAQUFAAOCAgEAF5oMg4I1
We8fdv4qqWJ2RONQpznWubsm/E6Gbzi2AQma5WTg2UDDF9dYpMaNXDOW84bc6GmjFJzus6dh
OzFTbs0M9uRFwg0Zw4iyCubCWGZTMymBD5s30LIdtoR00qbkBr85Ne+7SRKyATPhD/Vn7KTk
bf52k/fYNjXBqr3PLdc84FRONf8DbL8vJrEXGeKkGm36ZsDVl3f2zXUD9oZsWkLkeswl3pYf
nrr0H9TuDAx0jETXk/Y72GN8xP8HF+ZaxrusO8L5G5S0PWtDFeABjDCclqIceXbcLCXlSIMA
Fsr47HqSDo8+nOz+98+LvPQK/vDA2wjs0ypVvYtMtt3eeaKrLsdIkJvhbI6HeMr0CKHvAfUG
EVshvKCI/0HtRZeeDw9WrO6HAlXwR/Z6YljhsC9yy1rKpjlfRBAeAUNc7c9hdtZtS3OmUhBe
BX0lA11LnM83D3k7Z0xNYJVodD68pLOIXSy52OtRsj7Dl0fp1qLrLuHz7uSpncQ6zc0VTdtK
ffij7bYvLTjGlI0MvJBjeimAbkpVcl/n8ZI+0L6YYhmbk7oyCLhNZl5yi/fsambR3kbe2BnP
z+LkiZ1qrrPNGpYtRH0r9R1cOKzWSQtll2uOSin9QRyGGQgTzuydQivbsU8+SkraX6y41goe
sr4AYoD1xZWZeNfCUFKuv9ueHFkxggYbMIIGFwIBATCCAQAwgfgxITAfBgNVBAoTGEdyZWVu
cGVhY2UgSW50ZXJuYXRpb25hbDEeMBwGA1UECxMVQ2VydGlmaWNhdGUgQXV0aG9yaXR5MTMw
MQYJKoZIhvcNAQkBFiRjZXJ0aWZpY2F0ZS5hdXRob3JpdHlAZ3JlZW5wZWFjZS5vcmcxEjAQ
BgNVBAcTCUFtc3RlcmRhbTEWMBQGA1UECBMNTm9ydGggSG9sbGFuZDELMAkGA1UEBhMCTkwx
RTBDBgNVBAMTPEdyZWVucGVhY2UgR2xvYmFsIEF1dGhlbnRpY2F0aW9uIGFuZCBFbmNyeXB0
aW9uIFJvb3QgQ0EgMjAxMAIDIAIqMAkGBSsOAwIaBQCgggLuMBgGCSqGSIb3DQEJAzELBgkq
hkiG9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTExMTIzMDIyNTE0MVowIwYJKoZIhvcNAQkEMRYE
FG9b3VK9CQeqcJ3B0UBMiJQ2u4RVMF8GCSqGSIb3DQEJDzFSMFAwCwYJYIZIAWUDBAECMAoG
CCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIAgDANBggqhkiG9w0DAgIBQDAHBgUrDgMCBzANBggq
hkiG9w0DAgIBKDCCARMGCSsGAQQBgjcQBDGCAQQwggEAMIH4MSEwHwYDVQQKExhHcmVlbnBl
YWNlIEludGVybmF0aW9uYWwxHjAcBgNVBAsTFUNlcnRpZmljYXRlIEF1dGhvcml0eTEzMDEG
CSqGSIb3DQEJARYkY2VydGlmaWNhdGUuYXV0aG9yaXR5QGdyZWVucGVhY2Uub3JnMRIwEAYD
VQQHEwlBbXN0ZXJkYW0xFjAUBgNVBAgTDU5vcnRoIEhvbGxhbmQxCzAJBgNVBAYTAk5MMUUw
QwYDVQQDEzxHcmVlbnBlYWNlIEdsb2JhbCBBdXRoZW50aWNhdGlvbiBhbmQgRW5jcnlwdGlv
biBSb290IENBIDIwMTACAyACKjCCARUGCyqGSIb3DQEJEAILMYIBBKCCAQAwgfgxITAfBgNV
BAoTGEdyZWVucGVhY2UgSW50ZXJuYXRpb25hbDEeMBwGA1UECxMVQ2VydGlmaWNhdGUgQXV0
aG9yaXR5MTMwMQYJKoZIhvcNAQkBFiRjZXJ0aWZpY2F0ZS5hdXRob3JpdHlAZ3JlZW5wZWFj
ZS5vcmcxEjAQBgNVBAcTCUFtc3RlcmRhbTEWMBQGA1UECBMNTm9ydGggSG9sbGFuZDELMAkG
A1UEBhMCTkwxRTBDBgNVBAMTPEdyZWVucGVhY2UgR2xvYmFsIEF1dGhlbnRpY2F0aW9uIGFu
ZCBFbmNyeXB0aW9uIFJvb3QgQ0EgMjAxMAIDIAIqMA0GCSqGSIb3DQEBAQUABIICAFJTZfzi
zkOLNmzB4x/cUK1+aeEHahrEsukD5hS/LQje+opX/IWHAEZ4Uc2VN7g7AUvp11jRbvleBqro
+pkZmvhlncoB+fJmXn9ZYjKTuVFkb9eVFO15SP16EZkJtx9lPfKPcgTl+itEMRLPzRmkBiqT
m4FO6Skn+8UB0/cWQZjfYhl9vTZ6dqU1RSfbHB+4GPbIz5RXZLTH9aCEncGqzqL+3Dm9t4b5
Cqm9zyCz24IH0AgNPGo15Adzg/3gtuVe1Yazh9jGBfKnvsrUrLW8llUCfH2LBi/Tt0jmjMAr
2QrMja5rsZ7Nm2wnzZxsp4FCU4Y9tTYf5mYFwOT+udzHH9+TS4U1E/iLoTzw1HBQAbYebU0d
XWLlNFR0MR/nA9jAbLorhpG0RlFAssx8USUThatIn5JG0qiLQo1lKEtWS+Q1b7dOexsgFt1K
yNvvlX3asoeTV7k6ZH+IibpaBgI7mBZcgQjlAdhQMPhzNfUW6JmJ1VFStu4WuHq14DHfpVww
IlKKY/I2tkXO32a+JeEVSF5U0c4sDefbbsPOZyYqbRtMdZA1cmkFV6Mi4bPWsVLCSdPP79Gw
C29rybwcDlHpgNyRJNYU1cXEvXEFKw+3osMbG6YMs8uS64GE2VVWZGFpTcRlqnxF/ajcWg+T
KRkgCKzG2MH9Qn9viWFb8+IKkuWYAAAAAAAA
--------------ms060907090506060901070302--