NewBie Question. Multiple inserts fail after 1024 operations in a single transaction)

Vertica depracated (i.e. removed) the WOS completly in Vertica 10 so you are now inserting directly into ROS.

Each INSERT statement is creating a new ROS container.

Example:

dbadmin=> CREATE TABLE mark (c1 INT);
CREATE TABLE

dbadmin=> SELECT COUNT(DISTINCT storage_oid) ros_cnt FROM storage_containers WHERE projection_name = 'mark_super';
 ros_cnt
---------
       0
(1 row)

dbadmin=> INSERT INTO mark SELECT 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT COUNT(DISTINCT storage_oid) ros_cnt FROM storage_containers WHERE projection_name = 'mark_super';
 ros_cnt
---------
       1
(1 row)

dbadmin=> INSERT INTO mark SELECT 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT COUNT(DISTINCT storage_oid) ros_cnt FROM storage_containers WHERE projection_name = 'mark_super';
 ros_cnt
---------
       2
(1 row)

dbadmin=> INSERT INTO mark SELECT 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT COUNT(DISTINCT storage_oid) ros_cnt FROM storage_containers WHERE projection_name = 'mark_super';
 ros_cnt
---------
       3
(1 row)

Eventually the Tuple Mover will reduce the container count.

Here I am doing manually:

dbadmin=> SELECT do_tm_task('mergeout', 'mark');
                              do_tm_task
----------------------------------------------------------------------
 Task: mergeout
(Table: public.mark) (Projection: public.mark_super)

(1 row)

dbadmin=> SELECT COUNT(DISTINCT storage_oid) ros_cnt FROM storage_containers WHERE projection_name = 'mark_super';
 ros_cnt
---------
       1
(1 row)

You may need to rethink what it is you are trying to achieve.

Can you more gracefully handle the initial COPY statement errors so that you don't have to revert to using indivual INSERT statements as this is not the best use case for a columnar database like Vertica.

What is the max number of INSERT statement that you'd have to run? There is a configuration parameter named ContainersPerProjectionLimit that defaults to 1024 and you can increase it.

dbadmin=> SELECT parameter_name, default_value, description FROM configuration_parameters WHERE parameter_name = 'ContainersPerProjectionLimit';
        parameter_name        | default_value |                                      description
------------------------------+---------------+----------------------------------------------------------------------------------------
 ContainersPerProjectionLimit | 1024          | Number of ROS containers that are allowed before new ROSs are prevented (ROS pushback)
(1 row)

But that comes with this warning:

Increasing this parameter's value can cause serious degradation of database performance. Vertica strongly recommends that you not modify this parameter without first consulting with Customer Support professionals.

Jim

Thanks for correcting my understanding.. I had got the impression that it was a ROS per transaction from somewhere. The problem I am hitting with COPY is a memory limitation in this case (see my other post).

https://forum.vertica.com/discussion/242201/copy-statement-fails-in-10-1-works-in-9-0#latest

I also have another scenario where I currently have to resort to inserts which is dealing with an empty string. If I have ,"", in my CSV this seems to result in a NULL, rather than an empty string being inserted (even with trim=false).

In both of these scenarios I am forced to fallback to row by row inserts.

It appears that if I manually invoke the tuple mover it is an asynchronous operation. Is there a way to get notified (as distinct from polling to ensure that the TUPLE moving has completed it's task.

Just to complicate matters I am using the Postgres Node.js driver (PG) to work with Vertica

Jim

Tried triggering the Tuple Mover... I think it's failing with the same issue that my copy operation fails with...

Result {
  command: 'undefined',
  rowCount: null,
  oid: null,
  rows: [
    [
      'Task: mergeout\n' +
        '(Table: WWI_Application1.People) (Projection: WWI_Application1.People_super)\n' +
        'On node v_docker_node0001:\n' +
        '  ERROR 3587:  Insufficient resources to execute plan on pool general [Request Too Large:Memory(KB) Exceeded: Requested = 93875058, Free = 54918343 (Limit = 54918343, Used = 0)]\n'
    ]
  ],
  fields: [
    Field {
      name: 'do_tm_task',
      tableID: 0,
      columnID: 0,
      dataTypeID: 9,
      dataTypeSize: -1,
      dataTypeModifier: -1,
      format: 'text'
    }
  ],
  _parsers: [ [Function: noParse] ],
  _types: TypeOverrides {
    _types: {
      getTypeParser: [Function: getTypeParser],
      setTypeParser: [Function: setTypeParser],
      arrayParser: [Object],
      builtins: [Object]
    },
    text: {},
    binary: {}
  },
  RowCtor: null,
  rowAsArray: true,
  parseRow: [Function (anonymous)]
}