User rowset limits

You can place a limit on the number of rows a query can return and thus restrict resources for large result sets. Specifying a rowset limit when you create a user or a group automatically limits the rows that are returned so that users do not have to append a limit clause to their SQL queries.

You can also impose rowset limits on both individual users and groups. In addition, users can set their own rowset limits. The admin user does not have a limit on the number of rows a query can return.

If rowset limits were applied to the results of a query, the nzsql command displays a message and the system writes a notice to the /nz/kit/log/postgres/pg.log file. An example follows, but note that the 100 records are not shown in the example:

MYDB.ADMIN(MYUSR)=> select * from ne_orders;
NOTICE:  Rowset limit of 100 applied
 O_ORDERKEY | O_CUSTKEY | O_ORDERSTATUS 
------------+-----------+---------------
         96 |  32333333 | F             
        128 |  22186669 | F             
...

A sample pg.log message follows:

2015-08-10 07:53:02.534444 EDT [13417]  DEBUG:  QUERY: select * from ne_orders;
2015-08-10 07:53:02.534949 EDT [13417]  NOTICE:  Rowset limit of 100 applied

Client users who access the NPS host can use APIs such as the ODBC SQLGetDiagRec() or JDBC getwarning to capture the rowset limit message.