Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Question about client_min_messages and SQLSTATE 42000 #16

Open
rossj-cargotel opened this issue Aug 12, 2016 · 2 comments
Open

Question about client_min_messages and SQLSTATE 42000 #16

rossj-cargotel opened this issue Aug 12, 2016 · 2 comments
Assignees
@psoo
Labels
question

Comments

@rossj-cargotel
Copy link

One PostgreSQL 9.5.3...

I have a function I'm working on using The failing query works fine with psql on postgres and sqlcmd on informix.

select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id;

If client_min_messages is set to NOTICE or DEBUG1, I get the following:

postgres@cargotel [local]# select * from quickbooks_sync(' ');
WARNING:  opened informix connection with warnings
DETAIL:  informix SQLSTATE 01I01: "Database has transactions "
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
WARNING:  informix_fdw: could not start transaction: "Syntax error or access violation ", SQLSTATE 42000
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
WARNING:  opened informix connection with warnings
DETAIL:  informix SQLSTATE 01I01: "Database has transactions "
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
WARNING:  informix_fdw: could not start transaction: "Syntax error or access violation ", SQLSTATE 42000
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
 quickbooks_sync
-----------------

(1 row)

Time: 1159.637 ms

If I set client_min_messages to DEBUG2 or greater, the query works just fine:

postgres@cargotel [local]# SET client_min_messages TO DEBUG2;
SET
Time: 0.124 ms
postgres@cargotel [local]#
postgres@cargotel [local]# select * from quickbooks_sync(' ');
DEBUG:  uuid = <PLyResult status=5 nrows=1 rows=[{'split_part': '86e83e43'}]>
CONTEXT:  PL/Python function "quickbooks_sync"
DEBUG:  timestamp = <PLyResult status=5 nrows=1 rows=[{'split_part': '1471018930'}]>
CONTEXT:  PL/Python function "quickbooks_sync"
DEBUG:  txn_id = <PLyResult status=5 nrows=1 rows=[{'split_part': '86e83e43'}]>-<PLyResult status=5 nrows=1 rows=[{'split_part': '1471018930'}]>
CONTEXT:  PL/Python function "quickbooks_sync"
DEBUG:  time_created = <PLyResult status=5 nrows=1 rows=[{'to_char': '2016-08-12T12:22:10-04:00'}]>
CONTEXT:  PL/Python function "quickbooks_sync"
DEBUG:  informix connection dsn "jplm@cargodevnet"
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  reusing cached informix connection "nobodyjplmcargodevnet"
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
WARNING:  opened informix connection with warnings
DETAIL:  informix SQLSTATE 01I01: "Database has transactions "
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  informix database connection using transactions
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
WARNING:  informix_fdw: could not start transaction: "Syntax error or access violation ", SQLSTATE 42000
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  predicate for pushdown:
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  prepare query "SELECT *, rowid FROM load_trx"
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  declare cursor "nobodyjplmcargodevnet_cur4_15"
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  informix connection dsn "jplm@cargodevnet"
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  reusing cached informix connection "nobodyjplmcargodevnet"
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
WARNING:  opened informix connection with warnings
DETAIL:  informix SQLSTATE 01I01: "Database has transactions "
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  informix database connection using transactions
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
WARNING:  informix_fdw: could not start transaction: "Syntax error or access violation ", SQLSTATE 42000
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  predicate for pushdown:
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  prepare query "SELECT *, rowid FROM trxmain_inserts"
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  declare cursor "nobodyjplmcargodevnet_cur4_16"
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  populate descriptor area for statement "nobodyjplmcargodevnet_stmt4_15"
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  get descriptor column count 24
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  open cursor "nobodyjplmcargodevnet_cur4_15"
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  populate descriptor area for statement "nobodyjplmcargodevnet_stmt4_16"
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  get descriptor column count 2
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  open cursor "nobodyjplmcargodevnet_cur4_16"
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  informix fdw scan end
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  informix fdw scan end
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  informix_fdw: undo declare
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  informix_fdw: undo prepare
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  informix_fdw: undo declare
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  informix_fdw: undo prepare
CONTEXT:  SQL statement "select distinct(load_id) from jplm_dev.load_trx where trxmain_id in (select id from jplm_dev.trxmain_inserts) order by load_id"
PL/Python function "quickbooks_sync"
DEBUG:  Loads = <PLyResult status=5 nrows=33 rows=[{'load_id': 27141}, {'load_id': 27149}, {'load_id': 27391}, {'load_id': 27393}, {'load_id': 27414}, {'load_id': 27436}, {'load_id': 27440}, {'load_id': 27451}, {'load_id': 27452}, {'load_id': 27620}, {'load_id': 27745}, {'load_id': 27764}, {'load_id': 27766}, {'load_id': 28673}, {'load_id': 28839}, {'load_id': 28841}, {'load_id': 28846}, {'load_id': 28847}, {'load_id': 29058}, {'load_id': 29063}, {'load_id': 29064}, {'load_id': 29065}, {'load_id': 29261}, {'load_id': 29262}, {'load_id': 29264}, {'load_id': 29265}, {'load_id': 29266}, {'load_id': 29268}, {'load_id': 29277}, {'load_id': 29278}, {'load_id': 29279}, {'load_id': 29280}, {'load_id': 29586}]>
CONTEXT:  PL/Python function "quickbooks_sync"
LOG:  duration: 1020.693 ms  statement: select * from quickbooks_sync(' ');
 quickbooks_sync
-----------------

(1 row)

Time: 1020.803 ms

For now I'll just set client_min_messages to DEBUG2 but this seems very odd.
Jeff
@rossj-cargotel
Copy link
Author

Well, actually I see plpy.notice sending back load_ids so the query is running but the 42000 error is still there with DEBUG2 as well.

@psoo
Copy link
Contributor

psoo commented Aug 13, 2016

Uhm, this is hard to tell off-hand. Do you have any example code for me, which allows me to replay this behavior?

@psoo psoo added the question label Aug 13, 2016
@psoo psoo self-assigned this Aug 13, 2016
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@psoo psoo

Labels
question
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@psoo @rossj-cargotel