Unexpected behavior pg8000

[KULINAR847]

https://github.com/KULINAR847/ChudoBagPg8000

[tlocke]

Hi @KULINAR847, I've had a look but what would really help us is if you could provide a Minumum Reproducible Example. What we're looking for is the smallest bit of code that will cause the bug to appear.

[KULINAR847]

Hi Tony! I made a simple example. I committed it! And corrected README.md https://github.com/KULINAR847/ChudoBagPg8000

Best Wishes, Ivan Konoplev

On Wed, Nov 10, 2021 at 10:03 PM Tony Locke @.***> wrote:

Hi @KULINAR847 https://github.com/KULINAR847, I've had a look but what would really help us is if you could provide a Minumum Reproducible Example https://stackoverflow.com/help/minimal-reproducible-example. What we're looking for is the smallest bit of code that will cause the bug to appear.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/tlocke/pg8000/issues/93#issuecomment-965649417, or unsubscribe https://github.com/notifications/unsubscribe-auth/AJW5MGTYSP26ZYHPSRLLRYTULK6XJANCNFSM5HXJCPWQ . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[tlocke]

I think we need to do a bit more narrowing down to isolate the bug in pg8000. My suggestion would be to put everything in one file, and have the SQL as literal strings. The thing is that you're doing quite a bit of processing before the SQL is executed, but we want to end up with an example where literal strings are being executed by pg8000.

[KULINAR847]

Collected everything into one file and SQL as strings. Corrected README.md. https://github.com/KULINAR847/ChudoBagPg8000 https://github.com/KULINAR847/ChudoBagPg8000

On Thu, Nov 11, 2021 at 10:06 PM Tony Locke @.***> wrote:

I think we need to do a bit more narrowing down to isolate the bug in pg8000. My suggestion would be to put everything in one file, and have the SQL as literal strings. The thing is that you're doing quite a bit of processing before the SQL is executed, but we want to end up with an example where literal strings are being executed by pg8000.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/tlocke/pg8000/issues/93#issuecomment-966552981, or unsubscribe https://github.com/notifications/unsubscribe-auth/AJW5MGXWPJMHN5YS7BJKEJ3ULQH4RANCNFSM5HXJCPWQ . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[tlocke]

Thanks that's a lot easier to understand now. So we've got:

import os
import pg8000

ddl = r'''
DO $$ BEGIN
CREATE OR REPLACE FUNCTION public.function (
  out code integer
)
RETURNS integer AS
$body$
try:
    pass
except urllib.error.HTTPError as e1:
    plpy.execute("SELECT public.errwrite('getRecipe', 12, ARRAY['%s','%s','%s'])" % (e1.code, e1.reason.replace("\\\\","\\").replace("'","\""), e1.read().decode('utf-8').replace("\\\\","\\").replace("'","\"")))
    return [123, e1.reason]

except Exception as e2:
    #plpy.execute("SELECT public.errwrite('getRecipe', 13, ARRAY['%s'])" % str(e2).replace("\\\\","\\").replace("'","\""))
    plpy.execute("SELECT public.errwrite('getRecipe', 13, ARRAY['%s'])" % str(e2).replace("\\\\","\\").replace("'","\""))
    return [1232, str(e2)]
$body$
LANGUAGE 'plpython3u'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
ALTER FUNCTION public.function (out code integer)
  OWNER TO postgres;
END$$;
'''

if __name__ == '__main__':
    try:
        ddl = ddl.replace(' % ', ' %% ')

        conn = pg8000.connect(user='postgres', password='pass', database='test_db', host='localhost', port=5432, timeout=0.500)
        cursor = conn.cursor()
        cursor.execute(ddl)

        conn.commit()
        cursor.close()
        conn.close()

    except Exception as e:
        print(e)
        input()

So looking at this I realized that pg8000 is parsing the query and potentially rewriting it, even though there aren't any parameters. I've fixed this now and it's in version 1.23.0. So now, in your code the line:

    ddl = ddl.replace(' % ', ' %% ')

should be removed for use with version 1.23.0. Let me know if you still have a problem after trying with version 1.23.0. Also, thanks very much for the bug reporting, it's revealed a big problem with pg8000!

[KULINAR847]

Yes, all work fine. Closes: #93

On Sat, Nov 13, 2021 at 1:28 PM Tony Locke @.***> wrote:

Thanks that's a lot easier to understand now. So we've got:

import os import pg8000

ddl = r''' DO $$ BEGIN CREATE OR REPLACE FUNCTION public.function ( out code integer ) RETURNS integer AS $body$ try: pass except urllib.error.HTTPError as e1: plpy.execute("SELECT public.errwrite('getRecipe', 12, ARRAY['%s','%s','%s'])" % (e1.code, e1.reason.replace("\\","\").replace("'","\""), e1.read().decode('utf-8').replace("\\","\").replace("'","\""))) return [123, e1.reason]

except Exception as e2:

plpy.execute("SELECT public.errwrite('getRecipe', 13, ARRAY['%s'])" % str(e2).replace("\\","\").replace("'","\""))

plpy.execute("SELECT public.errwrite('getRecipe', 13, ARRAY['%s'])" % str(e2).replace("\\\\","\\").replace("'","\""))
return [1232, str(e2)]

$body$ LANGUAGE 'plpython3u' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100; ALTER FUNCTION public.function (out code integer) OWNER TO postgres; END$$; '''

if name == 'main': try: ddl = ddl.replace(' % ', ' %% ')

    conn = pg8000.connect(user='postgres', password='pass', database='test_db', host='localhost', port=5432, timeout=0.500)
    cursor = conn.cursor()
    cursor.execute(ddl)

    conn.commit()
    cursor.close()
    conn.close()

except Exception as e:
    print(e)
    input()

So looking at this I realized that pg8000 is parsing the query and potentially rewriting it, even though there aren't any parameters. I've fixed this now and it's in version 1.23.0. So now, in your code the line:

ddl = ddl.replace(' % ', ' %% ')

should be removed for use with version 1.23.0. Let me know if you still have a problem after trying with version 1.23.0. Also, thanks very much for the bug reporting, it's revealed a big problem with pg8000!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/tlocke/pg8000/issues/93#issuecomment-968017340, or unsubscribe https://github.com/notifications/unsubscribe-auth/AJW5MGWN7FITXPCDUCKLWDLULY4T7ANCNFSM5HXJCPWQ . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.