Message-ID: <4EFD3D32.9060902@greenpeace.org>
Date: Fri, 30 Dec 2011 17:25:22 +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.23) Gecko/20110921 Lightning/1.0b2 Thunderbird/3.1.15
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Current transaction is aborted,
	commands ignored until end of transaction block
Content-Type: multipart/signed; protocol="application/pkcs7-signature";
	micalg=sha1; boundary="------------ms000206060405060804000007"

This is a cryptographically signed message in MIME format.

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

Hi,

Maybe there is a simple solution for PostgreSQL behaviour that is
annoying me...

I've got users making updates to a master table and a number of detail
tables. All changes to the master record and related detail records are
encapsulated in a transaction so everything can be rolled back if
necessary and also to lock those master and related records for the user
making the changes.

When they do something that violates a constraint (for example adding a
duplicate detail record where that is not allowed), PostgreSQL aborts
the transaction. What I would much rather have is that PostgreSQL
returns an error but does not cancel the transaction as it's perfectly
OK (from a user's point of view) to try to do something that violates a
constraint.

What annoys me is that I don't think that a constraint violation made by
a user should result in an aborted transaction. There is probably a very
good reason to do that however the logic escapes me...

Of course I can start testing existing values in the database before
accepting them in the user interface but that's putting the horse behind
the cart. I much rather use the constraints at the database level to
tell me a particular update can't be done and do that without loosing
everything else I happened to have done in that transaction until that
point.

Any suggestions?

Jan


--------------ms000206060405060804000007
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
hkiG9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTExMTIzMDA0MjUyMlowIwYJKoZIhvcNAQkEMRYE
FL7jlhiOsY+WNxLAdpsQ/P80+10BMF8GCSqGSIb3DQEJDzFSMFAwCwYJYIZIAWUDBAECMAoG
CCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIAgDANBggqhkiG9w0DAgIBQDAHBgUrDgMCBzANBggq
hkiG9w0DAgIBKDCCARMGCSsGAQQBgjcQBDGCAQQwggEAMIH4MSEwHwYDVQQKExhHcmVlbnBl
YWNlIEludGVybmF0aW9uYWwxHjAcBgNVBAsTFUNlcnRpZmljYXRlIEF1dGhvcml0eTEzMDEG
CSqGSIb3DQEJARYkY2VydGlmaWNhdGUuYXV0aG9yaXR5QGdyZWVucGVhY2Uub3JnMRIwEAYD
VQQHEwlBbXN0ZXJkYW0xFjAUBgNVBAgTDU5vcnRoIEhvbGxhbmQxCzAJBgNVBAYTAk5MMUUw
QwYDVQQDEzxHcmVlbnBlYWNlIEdsb2JhbCBBdXRoZW50aWNhdGlvbiBhbmQgRW5jcnlwdGlv
biBSb290IENBIDIwMTACAyACKjCCARUGCyqGSIb3DQEJEAILMYIBBKCCAQAwgfgxITAfBgNV
BAoTGEdyZWVucGVhY2UgSW50ZXJuYXRpb25hbDEeMBwGA1UECxMVQ2VydGlmaWNhdGUgQXV0
aG9yaXR5MTMwMQYJKoZIhvcNAQkBFiRjZXJ0aWZpY2F0ZS5hdXRob3JpdHlAZ3JlZW5wZWFj
ZS5vcmcxEjAQBgNVBAcTCUFtc3RlcmRhbTEWMBQGA1UECBMNTm9ydGggSG9sbGFuZDELMAkG
A1UEBhMCTkwxRTBDBgNVBAMTPEdyZWVucGVhY2UgR2xvYmFsIEF1dGhlbnRpY2F0aW9uIGFu
ZCBFbmNyeXB0aW9uIFJvb3QgQ0EgMjAxMAIDIAIqMA0GCSqGSIb3DQEBAQUABIICAEM45cbl
mkDV/gIoLmIovN7NpRx4AedwT8RuH70Q4JwLCvWnn00HOt3j5i1p/67P5YX4P4AS7IlwYCsk
5z9pAEL7YBwUHnJll8XU/4ZhVBldD6kiHQB/VraqaMjntXozJBwWN9xMR3nwte6j8HtOORU9
/uk2/i2diu8pc/4tAuCqT3ixdO/SwkV24DytGHxYA6qP5hj5xEJIE+pseQnP6PrpLOdxYvKX
qI7t9Uj7w680AJG0jsyy1GLxHqPoSAGvcwBtIuH1ntPbqLCDq1eKWgj7hFEKTHaV477ZwYEh
KV0/r/SkMoyFATcKvTNG8poERA6FHWxpuB2P3YBOgA5fXGolUjRgsfh1tpybnWRQZY3+PRNH
/+uTOu+bCR/OhL6nP9pux9fm2oMAE4VxK0cVUPzXByrxhsvgeUlTw/COdZY1dTwYMBNjZrCC
pVA7FfliMLyyPVjy02TfXkAHI7erBiQ8b8dmzoHcINWHmle+T8gnExNiEGUXczA+gclqIxjt
3Q4z3TH4HixuKbBPCRW77rGOMPUgsISsr6TyZdsgaCa9WO1K6zeUZvRS4m7b003pgP/aWCS/
NRwcdvt3yFKACwDW5N5Ax5KxRKdaAQYOFQSVUV7Q0mzgFMeZm2XOU0De0CAXhuooMulRXRmz
dU7+7o8VXi0Kc8Bbo/tu1H+6gREFAAAAAAAA
--------------ms000206060405060804000007--