public inbox for [email protected]  

help / color / mirror / Atom feed

Executing stored procs
3+ messages / 2 participants
[nested] [flat]

* Executing stored procs
@ 2020-01-15 06:36 Anthony Waye <[email protected]>
  2020-01-15 12:05 ` Re: Executing stored procs Daniele Varrazzo <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: Anthony Waye @ 2020-01-15 06:36 UTC (permalink / raw)
  To: psycopg

Hi,

I've been currently trying to execute stored procs with psycopg2 on an AWS redshift cluster but no matter what I do I can't figure out the syntax for how to get it to successfully execute.

CREATE OR REPLACE PROCEDURE stg_customervip.sp_STG_Customer (
                                                                    p_Audit_RunID IN bigint ,
                                                                    p_Audit_RunDTS IN timestamp
                                                                    )
AS $$
BEGIN
TRUNCATE table stg_customervip.Customer;

INSERT INTO stg_customervip.Customer
       (
        ,Audit_RunID
        ,Audit_RunDTS
        )
SELECT
        ,p_Audit_RunID  AS Audit_RunID
        ,p_Audit_RunDTS AS Audit_RunDTS
FROM stg_customervip_temp.customer;
END;
$$ LANGUAGE plpgsql;

try:
    redshift_conn = psycopg2.connect(dbname=args.redshift_database_name, host=args.redshift_address,
                                        port=args.redshift_port, user=redshift_creds['username'], password=redshift_creds['password'])
except Exception as e:
    raise(e)

rand = random.randrange(
    15, 45)
timestamp = datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
for proc in stored_procs_obj:
    qry_str = sql.SQL("CALL {proc}({int}::bigint, {timestamp}::timestamp)").format(
        proc=sql.Identifier(proc['schemaname'], proc['procedurename']), int=sql.Literal(rand), timestamp=sql.Literal(timestamp))
    logger.info(qry_str.as_string(redshift_conn))
    logger.info(redshift_conn.closed)
    redshift_conn.reset()
    try:
        with redshift_conn.cursor() as cursor:
            cursor.execute(qry_str)
            redshift_conn.commit()
            logger.info("Query successfully committed")
    except Exception as e:
        redshift_conn.close()
        send_sfn_failure(args.task_token, "Exception", str(e))
        raise

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.

I've also attempted to try: SELECT "stg_customervip"."sp_stg_customer"(37::bigint, '2020-01-15 05:52:31'::timestamp) but that returns: [Amazon](500310) Invalid operation: stg_customervip.sp_stg_customer(bigint, timestamp without time zone) is a procedure;

I did notice there was a callproc() function available to call a function or a procedure but that doesn't make it any further

        param1 = str(rand) + "::bigint"
        param2 = timestamp + "::timestamp"

        try:
            with redshift_conn.cursor() as cursor:
                cursor.callproc(proc['fullprocedurename'], (param1, param2))
                redshift_conn.commit()
                logger.info("Query successfully committed")
        except Exception as e:
            redshift_conn.close()
            send_sfn_failure(args.task_token, "Exception", str(e))
            raise

returns:
psycopg2.errors.WrongObjectType: stg_connect.sp_stg_store("unknown", "unknown") is a procedure
HINT: To call a procedure, use CALL.

Has anyone encountered this or has any suggerstions?

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.




Attachments:

  [image/png] image001.png (3.8K, ../../SY2PR01MB269862BBF4F6FC0813C11AC386370@SY2PR01MB2698.ausprd01.prod.outlook.com/3-image001.png)
  download | view image

  [image/png] image002.png (1.3K, ../../SY2PR01MB269862BBF4F6FC0813C11AC386370@SY2PR01MB2698.ausprd01.prod.outlook.com/4-image002.png)
  download | view image

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Executing stored procs
  2020-01-15 06:36 Executing stored procs Anthony Waye <[email protected]>
@ 2020-01-15 12:05 ` Daniele Varrazzo <[email protected]>
  2020-01-16 01:01   ` RE: Executing stored procs Anthony Waye <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: Daniele Varrazzo @ 2020-01-15 12:05 UTC (permalink / raw)
  To: Anthony Waye <[email protected]>; +Cc: psycopg

On Wed, Jan 15, 2020 at 7:37 AM Anthony Waye <[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, ../../CA+mi_8ZhBBvo7pn4_EzJ2joBRXxYhygABz2RQ9AK7Vg0Wkd7eQ@mail.gmail.com/3-image001.png)
  download | view image

  [image/png] image002.png (1.3K, ../../CA+mi_8ZhBBvo7pn4_EzJ2joBRXxYhygABz2RQ9AK7Vg0Wkd7eQ@mail.gmail.com/4-image002.png)
  download | view image

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* RE: Executing stored procs
  2020-01-15 06:36 Executing stored procs Anthony Waye <[email protected]>
  2020-01-15 12:05 ` Re: Executing stored procs Daniele Varrazzo <[email protected]>
@ 2020-01-16 01:01   ` Anthony Waye <[email protected]>
  0 siblings, 0 replies; 3+ messages in thread

From: Anthony Waye @ 2020-01-16 01:01 UTC (permalink / raw)
  To: Daniele Varrazzo <[email protected]>; +Cc: psycopg

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2020-01-16 01:01 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-01-15 06:36 Executing stored procs Anthony Waye <[email protected]>
2020-01-15 12:05 ` Daniele Varrazzo <[email protected]>
2020-01-16 01:01   ` Anthony Waye <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox