// Insert inserts rows into the database. // Privileges: INSERT on table // Notes: postgres requires INSERT. No "on duplicate key update" option. // mysql requires INSERT. Also requires UPDATE on "ON DUPLICATE KEY UPDATE". func (p *planner) Insert(n *parser.Insert, autoCommit bool) (planNode, *roachpb.Error) { // TODO(marcb): We can't use the cached descriptor here because a recent // update of the schema (e.g. the addition of an index) might not be // reflected in the cached version (yet). Perhaps schema modification // routines such as CREATE INDEX should not return until the schema change // has been pushed everywhere. tableDesc, pErr := p.getTableLease(n.Table) if pErr != nil { return nil, pErr } if err := p.checkPrivilege(&tableDesc, privilege.INSERT); err != nil { return nil, roachpb.NewError(err) } var cols []ColumnDescriptor // Determine which columns we're inserting into. if n.DefaultValues() { cols = tableDesc.Columns } else { var err error if cols, err = p.processColumns(&tableDesc, n.Columns); err != nil { return nil, roachpb.NewError(err) } } // Number of columns expecting an input. This doesn't include the // columns receiving a default value. numInputColumns := len(cols) // Construct a map from column ID to the index the value appears at within a // row. colIDtoRowIndex := map[ColumnID]int{} for i, c := range cols { colIDtoRowIndex[c.ID] = i } // Add the column if it has a DEFAULT expression. addIfDefault := func(col ColumnDescriptor) { if col.DefaultExpr != nil { if _, ok := colIDtoRowIndex[col.ID]; !ok { colIDtoRowIndex[col.ID] = len(cols) cols = append(cols, col) } } } // Add any column that has a DEFAULT expression. for _, col := range tableDesc.Columns { addIfDefault(col) } // Also add any column in a mutation that is WRITE_ONLY and has // a DEFAULT expression. for _, m := range tableDesc.Mutations { if m.State != DescriptorMutation_WRITE_ONLY { continue } if col := m.GetColumn(); col != nil { addIfDefault(*col) } } // Verify we have at least the columns that are part of the primary key. primaryKeyCols := map[ColumnID]struct{}{} for i, id := range tableDesc.PrimaryIndex.ColumnIDs { if _, ok := colIDtoRowIndex[id]; !ok { return nil, roachpb.NewUErrorf("missing %q primary key column", tableDesc.PrimaryIndex.ColumnNames[i]) } primaryKeyCols[id] = struct{}{} } // Construct the default expressions. The returned slice will be nil if no // column in the table has a default expression. defaultExprs, err := p.makeDefaultExprs(cols) if err != nil { return nil, roachpb.NewError(err) } // Replace any DEFAULT markers with the corresponding default expressions. n.Rows = p.fillDefaults(defaultExprs, cols, n) // Transform the values into a rows object. This expands SELECT statements or // generates rows from the values contained within the query. rows, pErr := p.makePlan(n.Rows, false) if pErr != nil { return nil, pErr } if expressions := len(rows.Columns()); expressions > numInputColumns { return nil, roachpb.NewUErrorf("INSERT has more expressions than target columns: %d/%d", expressions, numInputColumns) } primaryIndex := tableDesc.PrimaryIndex primaryIndexKeyPrefix := MakeIndexKeyPrefix(tableDesc.ID, primaryIndex.ID) marshalled := make([]interface{}, len(cols)) b := p.txn.NewBatch() result := &valuesNode{} var qvals qvalMap if n.Returning != nil { result.columns = make([]ResultColumn, len(n.Returning)) table := tableInfo{ columns: makeResultColumns(cols, 0), } qvals = make(qvalMap) for i, c := range n.Returning { expr, err := resolveQNames(&table, qvals, c.Expr) if err != nil { return nil, roachpb.NewError(err) } n.Returning[i].Expr = expr typ, err := expr.TypeCheck(p.evalCtx.Args) if err != nil { return nil, roachpb.NewError(err) } name := string(c.As) if name == "" { name = expr.String() } result.columns[i] = ResultColumn{ Name: name, Typ: typ, } } } for rows.Next() { rowVals := rows.Values() result.rows = append(result.rows, parser.DTuple(nil)) // The values for the row may be shorter than the number of columns being // inserted into. Generate default values for those columns using the // default expressions. for i := len(rowVals); i < len(cols); i++ { if defaultExprs == nil { rowVals = append(rowVals, parser.DNull) continue } d, err := defaultExprs[i].Eval(p.evalCtx) if err != nil { return nil, roachpb.NewError(err) } rowVals = append(rowVals, d) } // Check to see if NULL is being inserted into any non-nullable column. for _, col := range tableDesc.Columns { if !col.Nullable { if i, ok := colIDtoRowIndex[col.ID]; !ok || rowVals[i] == parser.DNull { return nil, roachpb.NewUErrorf("null value in column %q violates not-null constraint", col.Name) } } } // Check that the row value types match the column types. This needs to // happen before index encoding because certain datum types (i.e. tuple) // cannot be used as index values. for i, val := range rowVals { // Make sure the value can be written to the column before proceeding. var mErr error if marshalled[i], mErr = marshalColumnValue(cols[i], val, p.evalCtx.Args); mErr != nil { return nil, roachpb.NewError(mErr) } } if p.prepareOnly { continue } primaryIndexKey, _, eErr := encodeIndexKey( &primaryIndex, colIDtoRowIndex, rowVals, primaryIndexKeyPrefix) if eErr != nil { return nil, roachpb.NewError(eErr) } // Write the secondary indexes. indexes := tableDesc.Indexes // Also include the secondary indexes in mutation state WRITE_ONLY. for _, m := range tableDesc.Mutations { if m.State == DescriptorMutation_WRITE_ONLY { if index := m.GetIndex(); index != nil { indexes = append(indexes, *index) } } } secondaryIndexEntries, eErr := encodeSecondaryIndexes( tableDesc.ID, indexes, colIDtoRowIndex, rowVals) if eErr != nil { return nil, roachpb.NewError(eErr) } for _, secondaryIndexEntry := range secondaryIndexEntries { if log.V(2) { log.Infof("CPut %s -> %v", secondaryIndexEntry.key, secondaryIndexEntry.value) } b.CPut(secondaryIndexEntry.key, secondaryIndexEntry.value, nil) } // Write the row sentinel. sentinelKey := keys.MakeNonColumnKey(primaryIndexKey) if log.V(2) { log.Infof("CPut %s -> NULL", roachpb.Key(sentinelKey)) } // This is subtle: An interface{}(nil) deletes the value, so we pass in // []byte{} as a non-nil value. b.CPut(sentinelKey, []byte{}, nil) // Write the row columns. for i, val := range rowVals { col := cols[i] if _, ok := primaryKeyCols[col.ID]; ok { // Skip primary key columns as their values are encoded in the row // sentinel key which is guaranteed to exist for as long as the row // exists. continue } if marshalled[i] != nil { // We only output non-NULL values. Non-existent column keys are // considered NULL during scanning and the row sentinel ensures we know // the row exists. key := keys.MakeColumnKey(primaryIndexKey, uint32(col.ID)) if log.V(2) { log.Infof("CPut %s -> %v", roachpb.Key(key), val) } b.CPut(key, marshalled[i], nil) } } if n.Returning == nil { continue } qvals.populateQVals(rowVals) resrow := make(parser.DTuple, len(n.Returning)) for i, c := range n.Returning { d, err := c.Expr.Eval(p.evalCtx) if err != nil { return nil, roachpb.NewError(err) } resrow[i] = d } result.rows[len(result.rows)-1] = resrow } if pErr := rows.PErr(); pErr != nil { return nil, pErr } if p.prepareOnly { return nil, nil } if isSystemConfigID(tableDesc.GetID()) { // Mark transaction as operating on the system DB. p.txn.SetSystemConfigTrigger() } if autoCommit { // An auto-txn can commit the transaction with the batch. This is an // optimization to avoid an extra round-trip to the transaction // coordinator. pErr = p.txn.CommitInBatch(b) } else { pErr = p.txn.Run(b) } if pErr != nil { return nil, convertBatchError(&tableDesc, *b, pErr) } return result, nil }