public inbox for [email protected]
help / color / mirror / Atom feedFrom: Anthony Waye <[email protected]>
To: Daniele Varrazzo <[email protected]>
Cc: [email protected] <[email protected]>
Subject: RE: Executing stored procs
Date: Thu, 16 Jan 2020 01:01:30 +0000
Message-ID: <SY2PR01MB2698CF26823EECB6643F097386360@SY2PR01MB2698.ausprd01.prod.outlook.com> (raw)
In-Reply-To: <CA+mi_8ZhBBvo7pn4_EzJ2joBRXxYhygABz2RQ9AK7Vg0Wkd7eQ@mail.gmail.com>
References: <SY2PR01MB269862BBF4F6FC0813C11AC386370@SY2PR01MB2698.ausprd01.prod.outlook.com>
<CA+mi_8ZhBBvo7pn4_EzJ2joBRXxYhygABz2RQ9AK7Vg0Wkd7eQ@mail.gmail.com>
Ah awesome! Thanks Daniele that appears to have worked!
Thanks
Anthony Waye
Senior Systems Engineer - DevOps (Microsoft)
[cid:[email protected]]
M 0400167098
D 0732307463
Level 3, 192 Ann Street, Brisbane, QLD 4000
arq.group<http://www.arq.group/;
[cid:[email protected]]<https://www.linkedin.com/company/arqgroup/;
The information contained in this email message may be confidential. If you are not the intended recipient any use, distribution, disclosure or copying of this information is prohibited. If you receive this email in error, please tell us by return email and delete it and any attachments from your system.
From: Daniele Varrazzo <[email protected]>
Sent: Wednesday, 15 January 2020 10:06 PM
To: Anthony Waye <[email protected]>
Cc: [email protected]
Subject: Re: Executing stored procs
On Wed, Jan 15, 2020 at 7:37 AM Anthony Waye <[email protected]<mailto:[email protected]>> 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
Attachments:
[image/png] image001.png (3.8K, ../SY2PR01MB2698CF26823EECB6643F097386360@SY2PR01MB2698.ausprd01.prod.outlook.com/3-image001.png)
download | view image
[image/png] image002.png (1.3K, ../SY2PR01MB2698CF26823EECB6643F097386360@SY2PR01MB2698.ausprd01.prod.outlook.com/4-image002.png)
download | view image
view thread (3+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: RE: Executing stored procs
In-Reply-To: <SY2PR01MB2698CF26823EECB6643F097386360@SY2PR01MB2698.ausprd01.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox