QH_USER (user ID) field is empty

Answer

If you perform a SELECT statement of the _v_qryhist (query history) table and the results include a record in which the QH_USER (user ID) field is empty, it is likely that that user ID was deleted from database.

This is demonstrated in the following example:

1. Open a session of nzsql (as the admin user) and create a new user (qhist_test), database (qhist_test_db), table inside the created database (qhist_test) and grant the user that you created privileges to objects:

[user1@<nps_server> ~]$ nzsql
SYSTEM(ADMIN)=> CREATE USER qhist_test WITH PASSWORD 'test';
CREATE USER
SYSTEM(ADMIN)=> CREATE DATABASE qhist_test_db;
CREATE DATABASE
SYSTEM(ADMIN)=> \c qhist_test_db
You are now connected to database qhist_test_db.
QHIST_TEST_DB(ADMIN)=> CREATE TABLE qhist_test (test int);
CREATE TABLE
QHIST_TEST_DB(ADMIN)=> GRANT ALL ON QHIST_TEST TO qhist_test;
GRANT
QHIST_TEST_DB(ADMIN)=> GRANT ALL ON QHIST_TEST_DB TO qhist_test;
GRANT

user1@<nps_server> ~]$ nzsql -u qhist_test -pw test -d qhist_test_db
QHIST_TEST_DB(QHIST_TEST)=> SELECT * FROM QHIST_TEST;
TEST
------
(0 rows)

SYSTEM(ADMIN)=> \c SYSTEM
SYSTEM(ADMIN)=> SELECT QH_USER, QH_TSUBMIT from _v_qryhist ORDER BY QH_TSUBMIT DESC limit 3;
QH_USER |     QH_TSUBMIT
------------+---------------------
QHIST_TEST | 2010-06-25 08:42:19
ADMIN      | 2010-06-25 08:41:14
ADMIN      | 2010-06-25 08:41:14
(5 rows)

SYSTEM(ADMIN)=> DROP USER qhist_test;
DROP USER

SYSTEM(ADMIN)=> SELECT QH_USER, QH_TSUBMIT from _v_qryhist ORDER BY QH_TSUBMIT DESC limit 3;
 QH_USER |     QH_TSUBMIT
 --------+---------------------
         | 2010-10-04 09:18:52
         | 2010-10-04 09:18:50
 ADMIN   | 2010-10-04 08:29:54
 (3 rows)