Обсуждение: psql -c does not honor ON_ERROR_STOP
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
This seems like either a psql bug or maybe just a documentation bug. I
know the psql docs say that -c behavior can be surprising, but I find
the below surprising even after reading the docs a couple of times.
Given that ON_ERROR_STOP defaults to off, it seems like these two
cases should both result in two rows inserted.
8<----------------------
SQL="begin; insert into foo values(1); commit; begin; insert into foo
values(1); commit; begin; insert into foo values(2); commit;"
psql test -c "drop table if exists foo"
psql test -c "create table foo(id int primary key)"
psql test -c "${SQL}"
ERROR: duplicate key value violates unique constraint "foo_pkey"
DETAIL: Key (id)=(1) already exists.
psql test -c "select * from foo"id
- ---- 1
(1 row)
psql test -c "truncate foo"
echo "${SQL}" | psql test
BEGIN
INSERT 0 1
COMMIT
BEGIN
ERROR: duplicate key value violates unique constraint "foo_pkey"
DETAIL: Key (id)=(1) already exists.
ROLLBACK
BEGIN
INSERT 0 1
COMMIT
psql test -c "select * from foo"id
- ---- 1 2
(2 rows)
8<----------------------
Thoughts?
Joe
- --
Joseph E Conway
credativ LLC
270 E Douglas Ave
El Cajon, CA 92020
Office: +1 619 270 8787
Mobile: +1 619 843 8340
===================================
USA: http://www.credativ.us
Canada: http://www.credativ.ca
Germany: http://www.credativ.de
Netherlands: http://www.credativ.nl
UK: http://www.credativ.co.uk
India: http://www.credativ.in
===================================
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1
iQIcBAEBAgAGBQJUrClgAAoJEDfy90M199hld0IP/2mFzLZqN+wfK3gfBF6qe0W/
8Q1UYLC9n8uAPCBHWnhVuy+ypSjOqqmCrQCBwR4I1wpF0ni7gRwcTJ2pGYf3rh1o
HVCGqWfduDtRrdOjUaGVkSaNGdc73IjLQ+WFRAmfFAVpBpc3GkyWT3PG2I4+81IY
mDRxMgvf7n3NgzdKiCO7tci4dw4vvFOsmgCSA8opacYD/wPlx+IWGCr8uU6Ub9f7
S0ouKCTmfCGVzDjNs0HKkTtVrGmjqeS4c3dl56mFJf8Tuc0t9S2wSDUW2DmYWHu/
+zTLLo8XOzUnP6Uz1DXLzt4tBJSjNBgEgVx6OVe/MUdM/6+nrlI1VVHEC2EWZSEf
5OisX4kkIY1eqLj8ffTWUlCnyPFCb/5L3zVGFvxWL+FsenOxgTzrpGwEdPlOea1O
GYchUJlHT90CyTpqb/xHHpyhgy3u2raWTgD2lXgDJnzyUCrWSXXalgadrpyT6DnZ
JpIBW2vh00boOQOAy5yFwFFZ6r8XcUJzZEZbAdbsUY9f7BGq23mrrEW9QgEzj5VX
LEr8EKEK8VAa9VByBTl9Dch0MtcIP+8t9xYOhuF+winRujDpgrMIYiFjy0lhogWj
I885fWTU4sHWDOVOAxbVMILji+b6jALRuCQ6QcDq5Gx6ZIZeSZHfLGVNZO5LyxWR
gh8B3yMsshnNm3wCDrr/
=Te2e
-----END PGP SIGNATURE-----
Joe Conway <joe.conway@credativ.com> writes:
> This seems like either a psql bug or maybe just a documentation bug. I
> know the psql docs say that -c behavior can be surprising, but I find
> the below surprising even after reading the docs a couple of times.
> Given that ON_ERROR_STOP defaults to off, it seems like these two
> cases should both result in two rows inserted.
-c submits the entire string to the backend in one PQexec(); therefore
ON_ERROR_STOP cannot have any impact on its behavior. The backend will
abandon processing the whole string upon first error, embedded begin/
commit commands notwithstanding.
There's been repeated discussion of changing -c so that the string is
split apart and processed more like it would be if it'd been read from
stdin. However, given the number of ways you can already submit a
string via stdin, this wouldn't be buying any new functionality. Even
discounting backwards-compatibility considerations, such a change would
make it completely impossible to test multiple-commands-per-PQexec
scenarios using psql. So I'm inclined to leave it alone.
Possibly the point is worth documenting explicitly, though.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 01/06/2015 12:36 PM, Tom Lane wrote: > -c submits the entire string to the backend in one PQexec(); > therefore ON_ERROR_STOP cannot have any impact on its behavior. > The backend will abandon processing the whole string upon first > error, embedded begin/ commit commands notwithstanding. > > There's been repeated discussion of changing -c so that the string > is split apart and processed more like it would be if it'd been > read from stdin. However, given the number of ways you can already > submit a string via stdin, this wouldn't be buying any new > functionality. Even discounting backwards-compatibility > considerations, such a change would make it completely impossible > to test multiple-commands-per-PQexec scenarios using psql. So I'm > inclined to leave it alone. > > Possibly the point is worth documenting explicitly, though. OK, thanks. I'll submit a proposal for an update to the docs. Joe - -- Joseph E Conway credativ LLC 270 E Douglas Ave El Cajon, CA 92020 Office: +1 619 270 8787 Mobile: +1 619 843 8340 =================================== USA: http://www.credativ.us Canada: http://www.credativ.ca Germany: http://www.credativ.de Netherlands: http://www.credativ.nl UK: http://www.credativ.co.uk India: http://www.credativ.in =================================== -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBAgAGBQJUrTo5AAoJEDfy90M199hl+7AP/jdG9IvJ6jH6GmjTQ8DLQZVu QiIJYpuE0A5Zr6bNukksX4ItDrvhuERwlLMQelTWhByYYh0CYEQf/4dPs2vPn+QG ZXRS6CE9YSQ+umab1O05Bdft8CqrQ682C12PxogInwy38NzmspTAbcGDq+Yve9Z4 EMpMN4Jz0sK77N0acTw/HqLNQMwaGSeUAh/1jKnKB85RKpLgkHmTpuQhrQwZ7mfY CJdUbyBUj9PGTHkEtUaRtUU9FF+KAax2OX6KDaC8neZ9YF1hMv4CkA5jOr8Hm2yL JQJCjjC1wCt5e4VN+/d0lNsBj7/CCLPpzy4rnOUEEJOCqe/n6dc65tC72B2zirTH 5gWHkI3rC4EeoWVm9oxutEmhG5ocLv7aTTr7ZUXwEPHk+U3MMbgFfrz1VF6z6Ymp t6LcWztBR+VLlXPiHwCSDNKenSimyDWACuIJcGArTf5BZmekRGpLW03bgnJkjXSZ IEXyqcAGw7Tac7t12cZ/f4sDsWWM+yOOIdq82SsXJ052P1patl5l1H1aU5Tek7WE qYwEFOMD8e2bCqOHm8Ij94ZymfN5eirscFL9fcM1QkHHgKsXh5ShUCTwtxHWYlMC yth50q7mdmrW3Lych1ry0bYBecjF/dOlgjv19hWHZXmuCXU+5uhwbJUzvHOzioiJ SfbED2GriwGDudEtgHnb =qdOf -----END PGP SIGNATURE-----