2

Not sure if this is a Supabase issue or a psycopg2 issue honestly and would love some help debugging.

I have the following code:

args = [('HaurGlass','60000','2022-10-20T21:15:39.751Z','10130506261','ac76e8db-ace0-40df-b6fa-f470641805e9','ad43639e-f66e-49d5-8fe8-d1ce5cd26193','{}')]

statement = ('''
      INSERT INTO %s (%s) VALUES %s ON CONFLICT (company_id, crm_id)
      DO UPDATE SET (%s)=(%s) RETURNING crm_id, id''')
statement = cur.mogrify(statement,
      (AsIs(db_table), AsIs(','.join(keys)),
       AsIs("%s"), AsIs(','.join(update_keys)), 
       AsIs(','.join(excluded_keys))))
output = execute_values(cur, statement, args, fetch=True)

The weird thing is that if args is <=100 rows in length, this query works without any problems. As soon as I increase the length of args to 101 rows or more, my Postgres logs show:

INSERT INTO licenses (name,value,subscription_end,crm_id,company_id,csm_id,custom_data) VALUES ('HaurGlass','60000','2022-10-20T21:15:39.751Z','10130506261','ac76e8db-ace0-40df-b6fa-f470641805e9','ad43639e-f66e-49d5-8fe8-d1ce5cd26193','{}')...

which would be good, except that it's immediately followed by:

INSERT INTO licenses (name,value,subscription_end,crm_id,company_id,csm_id,custom_data) VALUES ('HaurGlass','60000','2022-10-20T21:15:39.751Z','10130506261','ac76e8db-ace0-40df-b6fa-f470641805e9',NULL,'{}'),...

I've also confirmed that the number of records in the second "NULLifying" query is exactly equal to len(args)-100.

Any idea what is going on?

gkv
  • 360
  • 1
  • 8
  • Per here [execute_values](https://www.psycopg.org/docs/extras.html#fast-execution-helpers) `page_size`(default 100) is the number of values per statement. If you have more then 100 records then the next batch of 100 or less will be a new statement, until all the records are used up. – Adrian Klaver Sep 29 '22 at 21:37

1 Answers1

2

OK so it turns out I was missing the page_size parameter. All I had to do was: output = execute_values(cur, statement, args, fetch=True, page_size=len(args))

gkv
  • 360
  • 1
  • 8
  • 1
    Really no need to do this `execute_values` will run through all the values in batches of `page_size`(default 100). – Adrian Klaver Sep 29 '22 at 21:39
  • Yeah I hoped for that behavior, but not sure how to explain the NULL values I was seeing? – gkv Sep 30 '22 at 16:42
  • They where in the data? In the way `args` was constructed? You would have to show that in your question. – Adrian Klaver Sep 30 '22 at 19:32
  • Updated to show a sample args (no NULL values) – gkv Oct 01 '22 at 17:14
  • I confirmed @Adrian Klaver is correct that even you have items over default `page_size=100`, `execute_values` will auto split and execute more than one statement. I think something else caused the original issue. – Hongbo Miao Aug 30 '23 at 22:51