Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1irhQs-0003UY-OO for psycopg@arkaria.postgresql.org; Wed, 15 Jan 2020 12:05:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1irhQr-0006oq-Dm for psycopg@arkaria.postgresql.org; Wed, 15 Jan 2020 12:05:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1irhQq-0006oi-SJ for psycopg@lists.postgresql.org; Wed, 15 Jan 2020 12:05:57 +0000 Received: from mail-lj1-x22f.google.com ([2a00:1450:4864:20::22f]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1irhQk-0004MR-Vt for psycopg@postgresql.org; Wed, 15 Jan 2020 12:05:55 +0000 Received: by mail-lj1-x22f.google.com with SMTP id y6so18303967lji.0 for ; Wed, 15 Jan 2020 04:05:50 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=cYinyw+kDmrTTUWsgDQ9qUMzEYQyWW9mJC//beGzWss=; b=HDrJZ/vUcu1wutOOid2YpeOFSPuJORZPbw4UaoCc7gZHjqG2e3lwMRt0UJe9ryMBI6 f44lMYZ8zmJcgFvyPTGWKWvGA+UXHcnSUhEIjCF+MirLov5FKtVJ1QLvv3fHIvQEQs7v bo6+wHpQD+d01xSmEofaVUzxNit8JxsZSgnayuvDIW0uJeXAnXsAo0VGYHGPqATK4mGj UeOaNfzTbh86U0LpeVSpEyukzd9X+qJuSKDvdfekhoMUJNzJ8Qml9LQRK6UlwbrlaIuB PPWkQhkBdHXF8oEplFznNS1YC4bIglwyAz3VeUm+5YS56i6EGeLDgbeet2ihAMdgFKI+ R+ug== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=cYinyw+kDmrTTUWsgDQ9qUMzEYQyWW9mJC//beGzWss=; b=B9ta4He1XRapzC6M4Dre6Ck+9SYJVfOmH/+FJ3LL5eQc2M0uzgirE7OmdYlrsnvv4/ QmCXzC6elLa+NbdAdSJ/8KzfMAkwRi86SwjViP3Hp113nTVJzeFy10VnlSMJikacQLMj pGFE8pcSCihk6K2Fsbx3owuPuz7xVGoKGuc6N9ulzUinxlwIgGgxtuHRUfn/tWeNBt5O 0s9ypLF2lfJ2YSp4XndeNXhed3UJ0YGO7x7BHMksNngvTgwPYf+myHHWLYX3eY3CuMVw 9bhGck89RqtYR4lRIMTaKuu8gATksqgJe82+7r37Ju7yhhb770aULMoMLEMJ5Q5tTkaK Q7ow== X-Gm-Message-State: APjAAAVo2a4iZYny7NONeh22QtSsyypZyteMSkbBExTeTRbNbn7MFoxX 5ski6LUqp+jitq7rotcVOACOuoZC0hclu9+yCFw= X-Google-Smtp-Source: APXvYqwGbs8goUNi0odSi7pkB0TLZwCwJWiPF4Dj1KCH/PkEN54Qb52GafbQJnCYcRcBnSg6in/lygZRXGPuB/u9/Io= X-Received: by 2002:a2e:6c06:: with SMTP id h6mr1512098ljc.246.1579089949527; Wed, 15 Jan 2020 04:05:49 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Daniele Varrazzo Date: Wed, 15 Jan 2020 12:05:37 +0000 Message-ID: Subject: Re: Executing stored procs To: Anthony Waye Cc: "psycopg@postgresql.org" Content-Type: multipart/related; boundary="00000000000082b2c8059c2c870a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --00000000000082b2c8059c2c870a Content-Type: multipart/alternative; boundary="00000000000082b2c6059c2c8709" --00000000000082b2c6059c2c8709 Content-Type: text/plain; charset="UTF-8" On Wed, Jan 15, 2020 at 7:37 AM Anthony Waye wrote: > Essentially results in a SQL query of: CALL > "stg_customervip"."sp_stg_customer"(37::bigint, '2020-01-15 > 05:52:31'::timestamp) > > If I take that query and run it directly in redshift it runs successfully > but via psycopg2 it returns: > > > > psycopg2.errors.FeatureNotSupported: TRUNCATE cannot be invoked from a > procedure that is executing in an atomic context. > > HINT: Try calling the procedure as a top-level call i.e. not from within > an explicit transaction block. Or, if this procedure (or one of its > ancestors in the call chain) was created with SET config options, recreate > the procedure without them. > > CONTEXT: SQL statement "TRUNCATE table stg_customervip.Customer" > > > > While that error sounds legitimate I think it might be a redherring > because it does execute successfully if I do it manually against redshift. > Probably if you run it manually you do it outside a transaction. Psycopg starts a transaction automatically (no, I don't think it's a good idea, but it's part of the specs) http://initd.org/psycopg/docs/usage.html#transactions-control Try setting `redshift_conn.autocommit = True` after connection creation, and do without the `commit()`s. -- Daniele > --00000000000082b2c6059c2c8709 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Wed, Jan 15, 2020 at 7:37 AM Anthony Waye <anthony.waye@ar= q.group> wrote:
=C2=A0

Essentially results in a SQL query of: CALL "st= g_customervip"."sp_stg_customer"(37::bigint, '2020-01-15= 05:52:31'::timestamp)

If I take that query and run it directly in redshift= it runs successfully but via psycopg2 it returns:

=C2=A0<= u>

psycopg2.errors.FeatureNotSupported: TRUNCATE cannot be invoked from= a procedure that is executing in an atomic context.

= HINT= : Try calling the procedure as a top-level call i.e. not from within an exp= licit transaction block. Or, if this procedure (or one of its ancestors in the call chain) was created with SET config op= tions, recreate the procedure without them.

CONTEXT: SQL statement "TRUNCATE table stg_customervip.Customer= "

=C2=A0

While that error sounds legitimate I think it might = be a redherring because it does execute successfully if I do it manually ag= ainst redshift.


Probably if= you run it manually you do it outside a transaction. Psycopg starts a tran= saction automatically (no, I don't think it's a good idea, but it&#= 39;s part of the specs)


Try setting= `redshift_conn.autocommit =3D True` after connection creation, and do with= out the `commit()`s.

-- Daniele
--00000000000082b2c6059c2c8709-- --00000000000082b2c8059c2c870a Content-Type: image/png; name="image001.png" Content-Disposition: inline; filename="image001.png" Content-Transfer-Encoding: base64 Content-ID: <16fa9151f4e4cff311> X-Attachment-Id: 16fa9151f4e4cff311 iVBORw0KGgoAAAANSUhEUgAAAHIAAABaCAYAAABkIgImAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJ bWFnZVJlYWR5ccllPAAAAyhpVFh0WE1MOmNvbS5hZG9iZS54bXAAAAAAADw/eHBhY2tldCBiZWdp bj0i77u/IiBpZD0iVzVNME1wQ2VoaUh6cmVTek5UY3prYzlkIj8+IDx4OnhtcG1ldGEgeG1sbnM6 eD0iYWRvYmU6bnM6bWV0YS8iIHg6eG1wdGs9IkFkb2JlIFhNUCBDb3JlIDUuNi1jMDY3IDc5LjE1 Nzc0NywgMjAxNS8wMy8zMC0yMzo0MDo0MiAgICAgICAgIj4gPHJkZjpSREYgeG1sbnM6cmRmPSJo dHRwOi8vd3d3LnczLm9yZy8xOTk5LzAyLzIyLXJkZi1zeW50YXgtbnMjIj4gPHJkZjpEZXNjcmlw dGlvbiByZGY6YWJvdXQ9IiIgeG1sbnM6eG1wPSJodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAv IiB4bWxuczp4bXBNTT0iaHR0cDovL25zLmFkb2JlLmNvbS94YXAvMS4wL21tLyIgeG1sbnM6c3RS ZWY9Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEuMC9zVHlwZS9SZXNvdXJjZVJlZiMiIHhtcDpD cmVhdG9yVG9vbD0iQWRvYmUgUGhvdG9zaG9wIENDIDIwMTUgKE1hY2ludG9zaCkiIHhtcE1NOklu c3RhbmNlSUQ9InhtcC5paWQ6RDUwQjk1OTE0RkZEMTFFOEIzQzdDMkI5NDAwODIxMTUiIHhtcE1N OkRvY3VtZW50SUQ9InhtcC5kaWQ6RDUwQjk1OTI0RkZEMTFFOEIzQzdDMkI5NDAwODIxMTUiPiA8 eG1wTU06RGVyaXZlZEZyb20gc3RSZWY6aW5zdGFuY2VJRD0ieG1wLmlpZDpENTBCOTU4RjRGRkQx MUU4QjNDN0MyQjk0MDA4MjExNSIgc3RSZWY6ZG9jdW1lbnRJRD0ieG1wLmRpZDpENTBCOTU5MDRG RkQxMUU4QjNDN0MyQjk0MDA4MjExNSIvPiA8L3JkZjpEZXNjcmlwdGlvbj4gPC9yZGY6UkRGPiA8 L3g6eG1wbWV0YT4gPD94cGFja2V0IGVuZD0iciI/Pmu4eXcAAAu7SURBVHja7F0LsFVVGV6Xi2LI SxBJUfJxbyCKTdDFqCRw0q4KMmj5KDVNMqIyNIzUQh1I8zEaiI6aCjqQpoXiTRQ1mhKfVzMhkSzl qSB6rxdNUCJO/9f59rDd7P2vtffZB87B9c38c/Gs/VzfWv/6X3tZUygUjEf1o53vAk+khyfSwxPp 4Yn0RHp4Ij08kR6eSE+khyfSwxPp4Yn08ETuVGhfX1/ve6E86CYyVGSACDq5s0hHkfdE1om8LNIs 8rzIf0smsoRzZ4sck9C2SeQQkZacOmW5SJcM56GDWkVWiDwl8gd23JYykddB5BSRM0WOEKl1OKdN 5B6R20SezXrjmrq6uizn7SuyzDIQfixybU4dhJftmtO1VvG5buSAy2uJ+q7IxSK9S7jOoyITRV7Y XmvkOQ6zGS9WU4Eqbz+R60QWiXw2h+tBbT7NgdG7xGsdxVl5tcgu5SYSNxjjcNynRY6s4DWsr8gT IseWcI2RIs+JNORpt4hMEPmzSK9yEjlaZG/HY79X4QbJJ0TuFRmY4Vysg/dlXLtdMERkIZexshCZ hpxRKUjfUYAlOZdWpiuOF7nV0ZgpBTBg5on0yNtq7S8yLKWagBqeXMaX/ZXIhwltXajivyiym8V4 u0TkPMclY5Yjia20lJ+h5fwhB0w/kS+LDHe4DtyXO0VGiBTyslqni3w/ZUevFDlIZHOZrFZ0zHrL +T1FLrNok40ktFU5Bp3+pMhgy/3W8H63i/zHMoBw3BkOk+oHIjfkoVo7iZyutL+V8HufEg2KPIBn GyfyM8t6Odpyne84kHgHZ+3NFhKB1SJn85r/shw7RTN+0hB5mrKwvyIySTl3bIWsh79kRCUJwyzO /s8t159MI+jfKZ8LfuMXRBZbIkXn50HkOKVthshvRT5IaP+qyAEVQCQiPXcr7X2Utm+I7GPpg0kl ao1RimYLONi9FCKP4KIbh81UJ++I3G+JfFQCliptGlHfUtpgyPwwh2dbZlnHsbydUAqR2mx8iIu7 oXWVhG+L7FoBRHa1zNg47MHBnASo3Pdzer45jBRprk8mInsljQLi1tC/HxF5Q7EcT6wAIodbZkQc hip99bZFXacFXIxr0j6/C5FjlJm0lg5reETPqmCjB6G0k5T2ZsWXS8KDDtZpWsxTfGMEB3qnJRK+ zTkWU3tzzG9GGdmH7CASB9E51xzwB5QISxKeLsOzbrRYsAemJfI4xZIr0OGNYonR82rbc1bWkkBk JpCP3Es59q+mGABPMv2TsCrF88C9GWiJMoUNKOP6PDYiNSNnIf3HONxp8Ud3LxNxyMK/SllNA+Q5 WoK2tNBFFs2UhA0png9ZDSS23+Ngv4QDLQ6bLO/pTCTUyVFK+21K212KjsdoOrVMRLaj2jmQ60gH x/PgA863qDqjWLRp0Z7r9aUcaIs52MIEdVfOfzcNkbhwjXKh3ynnIl7ZVAWRHuBxU4xjalintPXL 4RkOpfqHOr3QFMOFfZXj33AlEhc6yzLjbH7THRbDo6ECSITbcLSDevyHZd3LC5jdl3PJ2j/hmC1x z5NE5KkWlXG7w0M9LPJmhc5KjPyv8z0/cDi+2eLX5Z1z1ZLJS+MmUXtFrSZhccQq/alJzrBr/hU6 EQVabduRQFiYSMM9ZNKl1UDk+oSoEPrwAqMEtHPGfFdrDCmVz6Uwcr5ENyUtoL4Rv5ya40u+S03Q mNCOwqsuJn1uFMf/3hTDjHHAGjvTFAu6NDzP/u1Zwjve5xrZ0VyOTZbITVrkXWkH3xY505XKMbeI fCbDtX+ttO1C489GENyNXjRuJlueMw5/p3FmJXJPo4ewkN1oybHjDzbFkoc8gdjnKYofhhqdORnc BkRw/qS011Mb9HIYbC+ZYsrrAK7VSx2fAa7bPi5EnkmVl8V3zIpyGD2I4kxU2uFn/sakL57CWqiV 9w9khMi1ImILZzJiuec6eAL7chDuphFpyxnC0nusDJ0+2qSo30yBqXzpJDRS1aUB1rjplmMwYxBI X2CKiWKXcBzW4Ovplr1kOfZwkWnRH8PFV4205pJwGSMRJmYW93N4Oa3eB+GxK5T2rMVXXRk5qVPU HAbS3BRkgpiFJjm0FgXcmxdpMU/hvzV05hJmK+4ewQGzDZGI/I9UVAAq4ZZnnB1Ig61WjIEVVHdb ciYSwGcBTyozYz0tyVdSvA+C708YPSti7XwFCC3Os5CJPusfBDMC1fopiwvxxxJIDKzdu5R23P+Y MvldL3D9McqshUnfKcU1EbL7SgojJS0Qpz6BVqrWZ+Oja+RYo8dd8zByZu4AoyfsOmhuE0b2jJSu 0Ar60A+X6ZmhKU62RJ4uoF/8f/Kg9s5WDm4xyUVVaWeG5jDD0utTRjJhyC1R2r9mimmmNGjhc//I 2Iuks2CJ0dNr3QLu2tGP0RzZ2SY5JZUWWiC93JV2G0iWZuJfQZWZNggxjQbftSa/IqwA11tU+FlB 542zXChP33GW0cNj5a60Q3GyVrpSS/8yi2ZA/RJix59k52Ldbc3hmTcbvV4WPuggWK22CMc7OXdm V8t6vD7Geu2mrF9tRvm4RVFJ2nq40bhlRVywHy3y7owOrclwjRqjl5tsyPrpuUeFwW/P4on08ER6 eCI9PJGeSI/KR/sqfGZkBpCTQ2oMURQkcl9n2x4Jvm9HnocviVEQdpgpJoERYkNCIK4cEklcpKpQ FY9qwKeMXjYZ+MeoG4pLPgfPgGuEI2UoE+nE5wq+dO5L6cggxiKbr1xNMxJO8XiShs2EkE1B6g15 vjkkttXER1Ou4e9I9KKICjnBGTw/WjqBSMljvC5izLP53yghwY5ZSemwRbxH/4T24BmiBVxH8/e7 GTjAvRCSm8t3/Jspptgad4YZWcOOR9VdUNGGjkM4Dx+gIjnssh3ZJBKFZDO2TEFeMRxpQf3QPM6E VSQasxa1RSM5kBqMW1FzWiAsiIR1T5KI90PZDT7bQLEY8phncGBVLZHjSSJU3LFUp2GgRnamw3VA Imp5roppQ+HZvSQRBdjjIioQxVX4LA979tzETs0TAzioGjgLw4MYn7VjPyFUADabmCR4NahWrCEX 89+nx5BoqIKmOFzr2QQSgfM4G1ApN8Zsm/H5JwcRYrD4oqwc33meFiHRmK3ZlRs4yC6q1jUShk0P rhuPKsdNN/YNbB9R2kbx75WKYfEqZy1myfE5vydU6QKlPdgydaSJCfhXA5H786+tugyW6lrLMVr7 QfzbbLlGc+T4vGDboxX7GyAz1N3E5I+rgcjg20SXskLb95DazskFxz6piRy/PV3Bdkn3rgYig/KM IRYyD6XBkhWBAfF5y3FDIscHeN/Sp10iAzMKW3kltkXrTDforWok8mX6fVApP1FmydUl3ieobYVh VasMlhM5I6IbR7zGvw0Jzxf8vjzh2igVGaE838TIc1ZlQOB8GjKXivzCfHRPvN70KxtLvMc0mv+Y kbPMtuWR8OWaaEWj9ij6sWlTaCDUx1jEmFHrGCFKwgyz7cZM4OhCBhIw6y+vZj9yAV2CW2h+T6A7 gE6t48vCtyulpLKFgQUEBPAREIqw5jPqgnDZkewvRJXitjqdyc4eTBeiicbJoJDanGCSC9kWc2nA 9R8PBQSG0bDaRN/1tWomMugo+IHYLmw4/bgNfPGb2XFjTWllic+QiKvojnwz1NZGF+BKE/+lFwjC 5olTed7JoTZ8Poca1HuUe6/kPVE1h2LtoaE2RKLO1WZzNdfs1ESsN6ikv9A9GJzD9ffkYOlIA+NF 4/6/l0Ch1GGcUa/TYEuymI9jxOjB0Bq5N7VABxpVy/IweSsFu0Y6Mkxirdm6l2pTTvd7m7M9C9o4 qLJijUlZbVctxs4As3ULzOhmS3vROEFweTVV08cO1TIjYWig8Bd70VxHY6KVKmwgVdCbXGPaPJGV i6lUc1jwkUI6PNSGzYMQ/8Q212vNxxTVauwgG9+Ds6/VeJiaQqHge2EngC++8kR6eCI9PJEenkhP pIcn0sMT6eGJ9ER6eCI9dij+J8AAEsJdB+ZmvBgAAAAASUVORK5CYII= --00000000000082b2c8059c2c870a Content-Type: image/png; name="image002.png" Content-Disposition: inline; filename="image002.png" Content-Transfer-Encoding: base64 Content-ID: <16fa9151f4f5b16b22> X-Attachment-Id: 16fa9151f4f5b16b22 iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJ bWFnZVJlYWR5ccllPAAAAyhpVFh0WE1MOmNvbS5hZG9iZS54bXAAAAAAADw/eHBhY2tldCBiZWdp bj0i77u/IiBpZD0iVzVNME1wQ2VoaUh6cmVTek5UY3prYzlkIj8+IDx4OnhtcG1ldGEgeG1sbnM6 eD0iYWRvYmU6bnM6bWV0YS8iIHg6eG1wdGs9IkFkb2JlIFhNUCBDb3JlIDUuNi1jMDY3IDc5LjE1 Nzc0NywgMjAxNS8wMy8zMC0yMzo0MDo0MiAgICAgICAgIj4gPHJkZjpSREYgeG1sbnM6cmRmPSJo dHRwOi8vd3d3LnczLm9yZy8xOTk5LzAyLzIyLXJkZi1zeW50YXgtbnMjIj4gPHJkZjpEZXNjcmlw dGlvbiByZGY6YWJvdXQ9IiIgeG1sbnM6eG1wPSJodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAv IiB4bWxuczp4bXBNTT0iaHR0cDovL25zLmFkb2JlLmNvbS94YXAvMS4wL21tLyIgeG1sbnM6c3RS ZWY9Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEuMC9zVHlwZS9SZXNvdXJjZVJlZiMiIHhtcDpD cmVhdG9yVG9vbD0iQWRvYmUgUGhvdG9zaG9wIENDIDIwMTUgKE1hY2ludG9zaCkiIHhtcE1NOklu c3RhbmNlSUQ9InhtcC5paWQ6RDk4NEQ3MEU1MDEyMTFFOEIzQzdDMkI5NDAwODIxMTUiIHhtcE1N OkRvY3VtZW50SUQ9InhtcC5kaWQ6RDk4NEQ3MEY1MDEyMTFFOEIzQzdDMkI5NDAwODIxMTUiPiA8 eG1wTU06RGVyaXZlZEZyb20gc3RSZWY6aW5zdGFuY2VJRD0ieG1wLmlpZDpEOTg0RDcwQzUwMTIx MUU4QjNDN0MyQjk0MDA4MjExNSIgc3RSZWY6ZG9jdW1lbnRJRD0ieG1wLmRpZDpEOTg0RDcwRDUw MTIxMUU4QjNDN0MyQjk0MDA4MjExNSIvPiA8L3JkZjpEZXNjcmlwdGlvbj4gPC9yZGY6UkRGPiA8 L3g6eG1wbWV0YT4gPD94cGFja2V0IGVuZD0iciI/Pq8cAD4AAAF2SURBVHjaYjxz5owOAwPDIiA2 ZKAvOAHE8UxAYsEAWA4CFkA8l2mALIcBXZADmAbQAQwsOMSfAfFUIP4OxHFAbEBPBzyFWvgGyp8C xJuB2J0WDsAW/BORLAeB30DcTqsQwOaAn1jEvtLTAeFYxCPo6QArIF4PxDZArA3EvUBcRO9c4AfF A5YNyQWfgfgAED8HYmFoCGqQGgVbgZgRDXcgyVdikf8PzSky0JBLB+IQINYEYlMgvkSKA8gBFUBc BcSfsMidAWIXaOFGMwd0E5B/DcRdtHTAf6hZelDMiEXNelo6QASIrwDxRSjei8XsR0D8nlYOyIAm OBhwBGJXHJUcTRxgj0VMG4vYF1o5QAiLGBsWsd+0cgArNesCuoJRB4w6YMAdwAjsmv1HE/sIxHfQ xKSBWAKp1fwCTV4LiDmxtK7R1akDMQ+yXdgaJPxAbIzH0dJQTAgQpY4JWpMNaBo4O4D2nwVFQQwQ zwBifTpbfgqIMwECDADhAj/xR/QIxwAAAABJRU5ErkJggg== --00000000000082b2c8059c2c870a--