[Question]Failed to run workload with postgre

[ayiyaliing]

When I run hammerdb against postgre db , the error logs like below, Is there some reason why the function can't be found? Thanks

Vuser 6:FINISHED FAILED
Vuser 7:Processing 100000000 transactions with output suppressed...
Vuser 8:Processing 100000000 transactions with output suppressed...
Vuser 9:Processing 100000000 transactions with output suppressed...
Error in Virtual User 8: ERROR: function payment(integer, integer, integer, integer, integer, integer, integer, unknown, unknown, integer) does not exist
LINE 1: select payment(1,9,3,2,874,0,723,'','0',0)
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Vuser 8:FINISHED FAILED
Error in Virtual User 7: ERROR: function payment(integer, integer, integer, integer, integer, integer, integer, unknown, unknown, integer) does not exist
LINE 1: select payment(3,2,2,6,2815,1,1307,'ESEPRIPRES','0',0)
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Vuser 7:FINISHED FAILED
Vuser 10:Processing 100000000 transactions with output suppressed...
Vuser 11:Processing 100000000 transactions with output suppressed...
Error in Virtual User 11: ERROR: function neword(integer, integer, integer, integer, integer, integer) does not exist
LINE 1: select neword(1,3,3,1380,12,0)
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Vuser 11:FINISHED FAILED
Error in Virtual User 10: ERROR: function payment(integer, integer, integer, integer, integer, integer, integer, unknown, unknown, integer) does not exist
LINE 1: select payment(3,7,2,6,2507,1,3001,'ESEANTIATION','0',0)
^

[sm-shaw]

Firstly make sure that the schema build finished correctly. if this is the case then be aware that there is an option to use stored procedures instead of functions, if you created stored procedures and then run the driver script with functions then you would also see the error above so also try setting PostgreSQL Stored procedures in the driver script to see if you have created stored procedures instead.

[kanyakumarishankaranarayananTR]

if { $ora_compatible eq "true" } => i see this condition set in you pgoltp.tcl file.. what is the use of this parameter ora_compatible ? I see the code creates procedures only if ora_compatible is true as per the code. Please advise.

[sm-shaw]

The documentation explains this - see table 4.5 here
Are you sure the build is stuck? It will only run as fast as your database is configured to and you are loading with only 1 virtual user - try waiting to see if it really is 'stuck'

[kanyakumarishankaranarayananTR]

I tried building the schema again and it did create complete with no errors atleast on the console. Is there no log that gets generated for the schema build? The functions were missing again, i tried to create them manually my copying the code from pgoltp.tcl file, still no luck gives the same error. I can see the functions very much under tpcc_h schema with right privileges too.

Error in Virtual User 2: ERROR: function neword(integer, integer, integer, integer, integer, integer) does not exist
LINE 1: ...ER, INTEGER, INTEGER, INTEGER, INTEGER) as select neword($1,...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Vuser 2:FINISHED FAILED

[sm-shaw]

Please note that all the information is in the Documentation
Example build script including logging:

dbset db pg
dbset bm TPC-C
diset tpcc pg_count_ware 1
diset tpcc pg_num_vu 1
diset tpcc pg_superuser postgres
diset tpcc pg_superuserpass postgres
vuset logtotemp 1
buildschema

Example output in the log from running the script

Hammerdb Log @ Wed May 12 11:24:02 BST 2021
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
Vuser 1:CREATING TPCC SCHEMA
Vuser 1:CREATING DATABASE tpcc under OWNER tpcc
Vuser 1:CREATING TPCC TABLES
Vuser 1:Loading Item
Vuser 1:Loading Items - 10000
Vuser 1:Loading Items - 20000
Vuser 1:Loading Items - 30000
Vuser 1:Loading Items - 40000
Vuser 1:Loading Items - 50000
Vuser 1:Loading Items - 60000
Vuser 1:Loading Items - 70000
Vuser 1:Loading Items - 80000
Vuser 1:Loading Items - 90000
Vuser 1:Loading Items - 100000
Vuser 1:Item done
Vuser 1:Start:Wed May 12 11:24:12 BST 2021
Vuser 1:Loading Warehouse
Vuser 1:Loading Stock Wid=1
Vuser 1:Loading Stock - 20000
Vuser 1:Loading Stock - 40000
Vuser 1:Loading Stock - 60000
Vuser 1:Loading Stock - 80000
Vuser 1:Loading Stock - 100000
Vuser 1:Stock done
Vuser 1:Loading District
Vuser 1:District done
Vuser 1:Loading Customer for DID=1 WID=1
Vuser 1:Customer Done
Vuser 1:Loading Customer for DID=2 WID=1
Vuser 1:Customer Done
Vuser 1:Loading Customer for DID=3 WID=1
Vuser 1:Customer Done
Vuser 1:Loading Customer for DID=4 WID=1
Vuser 1:Customer Done
Vuser 1:Loading Customer for DID=5 WID=1
Vuser 1:Customer Done
Vuser 1:Loading Customer for DID=6 WID=1
Vuser 1:Customer Done
Vuser 1:Loading Customer for DID=7 WID=1
Vuser 1:Customer Done
Vuser 1:Loading Customer for DID=8 WID=1
Vuser 1:Customer Done
Vuser 1:Loading Customer for DID=9 WID=1
Vuser 1:Customer Done
Vuser 1:Loading Customer for DID=10 WID=1
Vuser 1:Customer Done
Vuser 1:Loading Orders for D=1 W=1
Vuser 1:...1000
Vuser 1:...2000
Vuser 1:...3000
Vuser 1:Orders Done
Vuser 1:Loading Orders for D=2 W=1
Vuser 1:...1000
Vuser 1:...2000
Vuser 1:...3000
Vuser 1:Orders Done
Vuser 1:Loading Orders for D=3 W=1
Vuser 1:...1000
Vuser 1:...2000
Vuser 1:...3000
Vuser 1:Orders Done
Vuser 1:Loading Orders for D=4 W=1
Vuser 1:...1000
Vuser 1:...2000
Vuser 1:...3000
Vuser 1:Orders Done
Vuser 1:Loading Orders for D=5 W=1
Vuser 1:...1000
Vuser 1:...2000
Vuser 1:...3000
Vuser 1:Orders Done
Vuser 1:Loading Orders for D=6 W=1
Vuser 1:...1000
Vuser 1:...2000
Vuser 1:...3000
Vuser 1:Orders Done
Vuser 1:Loading Orders for D=7 W=1
Vuser 1:...1000
Vuser 1:...2000
Vuser 1:...3000
Vuser 1:Orders Done
Vuser 1:Loading Orders for D=8 W=1
Vuser 1:...1000
Vuser 1:...2000
Vuser 1:...3000
Vuser 1:Orders Done
Vuser 1:Loading Orders for D=9 W=1
Vuser 1:...1000
Vuser 1:...2000
Vuser 1:...3000
Vuser 1:Orders Done
Vuser 1:Loading Orders for D=10 W=1
Vuser 1:...1000
Vuser 1:...2000
Vuser 1:...3000
Vuser 1:Orders Done
Vuser 1:End:Wed May 12 11:24:47 BST 2021
Vuser 1:CREATING TPCC INDEXES
Vuser 1:CREATING TPCC FUNCTIONS
Vuser 1:GATHERING SCHEMA STATISTICS
Vuser 1:TPCC SCHEMA COMPLETE

Example listing that shows the functions have been created:

./bin/psql -d tpcc
tpcc=# \df
                                                                       List of functions
 Schema |    Name     | Result data type |                                             Argument data types                                              | Type 
--------+-------------+------------------+--------------------------------------------------------------------------------------------------------------+------
 public | dbms_random | integer          | integer, integer                                                                                             | func
 public | delivery    | integer          | integer, integer                                                                                             | func
 public | neword      | numeric          | integer, integer, integer, integer, integer, integer                                                         | func
 public | ostat       | SETOF record     | integer, integer, integer, integer, character varying                                                        | func
 public | payment     | integer          | integer, integer, integer, integer, integer, integer, numeric, character varying, character varying, numeric | func
 public | slev        | integer          | integer, integer, integer                                                                                    | func
(6 rows)

[kanyakumarishankaranarayananTR]

Many thanks, that helps. Let me try again and see if all goes well.

[ayiyaliing]

Thanks, it solved, I still have a question, when the test run is finished if I can generate a report as xml format or not?

[sm-shaw]

If you run hammerdb as a web service all of the functions that run underneath are identical to the GUI and CLI however it stores the output in JSON format in a SQLite database that can be extracted later. The JSON format could be converted to XML if desired. More information is here https://www.hammerdb.com/blog/uncategorized/using-hammerdb-as-a-web-service/. Although not a feature at present or an Issue for future development it would be possible to add the SQLite based store to the GUI and CLI.

[ayiyaliing]

Thanks , Is there a way how can I use the sqlite database? or How can I access the db to extracted the json file.

[sm-shaw]

Currently the sqlite database is only accessible when using the web service interface. The documentation for this feature is here https://www.hammerdb.com/docs/ch09.html and the specific part on retrieving output here https://www.hammerdb.com/docs/ch09s05.html.

[sm-shaw]

closed as no further update