Configure Ignite Cache with Postgresql as 3rd party persistence using table with composite primary key

Question

I would like to configure an Ignite cache. The cache should be loaded with data from a postgresql database. I managed to configure some caches for tables that have a simple (one column) primary key. Now I am running into some problems configuring the cache for a table with a composite primary key (consisting of 2 columns). I am using the tpc-h database schema. The problems occur when caching the table "lineitem". The primary key consists of the two columns "l_orderkey" and "l_linenumber". I tried following configuration:



    
    
        
        
        
        
    
    
    
        
            
                
                
                    
                    
                    
                    
                        
                            
                            
                                
                            
                            
                                
                                    
                                        
                                        
                                        
                                        
                                        
                                        
                                        
                                            
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                            
                                        
                                        
                                            
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                                
                                                    
                                                        
                                                    
                                                    
                                                    
                                                    
                                                
                                            
                                        
                                    
                                
                            
                        
                    
                    
                    
                    
                    
                        
                            
                                
                                
                                
                                    
                                        l_orderkey
                                        l_linenumber

LineitemKey.java

public class LineitemKey {
    private long l_orderkey;
    private long l_linenumber;
    
    public LineitemKey(long l_orderkey, long l_linenumber) {
        this.l_linenumber = l_linenumber;
        this.l_orderkey = l_orderkey;
    }
    
    public LineitemKey() {};
    
    public long getL_orderkey() {
        return l_orderkey;
    }
    public void setL_orderkey(long l_orderkey) {
        this.l_orderkey = l_orderkey;
    }
    public long getL_linenumber() {
        return l_linenumber;
    }
    public void setL_linenumber(long l_linenumber) {
        this.l_linenumber = l_linenumber;
    }
}

Lineitem.java

import java.io.Serializable;
import java.sql.Date;

public class Lineitem implements Serializable {
    private static final long serialVersionUID = 1L;
    private long l_orderkey;
    private long l_partkey;
    private long l_suppkey;
    private long l_linenumber;
    private double l_quantity;
    private double l_extendedprice;
    private double l_discount;
    private double l_tax;
    private char l_returnflag;
    private char l_linestatus;
    private Date l_shipdate;
    private Date l_commitdate;
    private Date l_receiptdate;
    private String l_shipinstruct;
    private String l_shipmode;
    private String l_comment;   
    
    public Lineitem() {
    }

    public long getL_orderkey() {
        return l_orderkey;
    }

    public void setL_orderkey(long l_orderkey) {
        this.l_orderkey = l_orderkey;
    }

    public long getL_partkey() {
        return l_partkey;
    }

    public void setL_partkey(long l_partkey) {
        this.l_partkey = l_partkey;
    }

    public long getL_suppkey() {
        return l_suppkey;
    }

    public void setL_suppkey(long l_suppkey) {
        this.l_suppkey = l_suppkey;
    }

    public long getL_linenumber() {
        return l_linenumber;
    }

    public void setL_linenumber(long l_linenumber) {
        this.l_linenumber = l_linenumber;
    }

    public double getL_quantity() {
        return l_quantity;
    }

    public void setL_quantity(double l_quantity) {
        this.l_quantity = l_quantity;
    }

    public double getL_extendedprice() {
        return l_extendedprice;
    }

    public void setL_extendedprice(double l_extendedprice) {
        this.l_extendedprice = l_extendedprice;
    }

    public double getL_discount() {
        return l_discount;
    }

    public void setL_discount(double l_discount) {
        this.l_discount = l_discount;
    }

    public double getL_tax() {
        return l_tax;
    }

    public void setL_tax(double l_tax) {
        this.l_tax = l_tax;
    }

    public char getL_returnflag() {
        return l_returnflag;
    }

    public void setL_returnflag(char l_returnflag) {
        this.l_returnflag = l_returnflag;
    }

    public char getL_linestatus() {
        return l_linestatus;
    }

    public void setL_linestatus(char l_linestatus) {
        this.l_linestatus = l_linestatus;
    }

    public Date getL_shipdate() {
        return l_shipdate;
    }

    public void setL_shipdate(Date l_shipdate) {
        this.l_shipdate = l_shipdate;
    }

    public Date getL_commitdate() {
        return l_commitdate;
    }

    public void setL_commitdate(Date l_commitdate) {
        this.l_commitdate = l_commitdate;
    }

    public Date getL_receiptdate() {
        return l_receiptdate;
    }

    public void setL_receiptdate(Date l_receiptdate) {
        this.l_receiptdate = l_receiptdate;
    }

    public String getL_shipinstruct() {
        return l_shipinstruct;
    }

    public void setL_shipinstruct(String l_shipinstruct) {
        this.l_shipinstruct = l_shipinstruct;
    }

    public String getL_shipmode() {
        return l_shipmode;
    }

    public void setL_shipmode(String l_shipmode) {
        this.l_shipmode = l_shipmode;
    }

    public String getL_comment() {
        return l_comment;
    }

    public void setL_comment(String l_comment) {
        this.l_comment = l_comment;
    }

}

This seemed like a correct configuration to me. Running the cluster with this config resulted in an empty cache. No data was loaded from postgresql to the cache. If I pass a configuration with only one column as the primary key to the node, only some but not all rows get loaded to the cache. For the selected (single) primary key row only one row per key.

Question: How do I configure the cache to use the composite key to identify each row?

user_11272685 · Answer

Looks like a misconfiguration somewhere. Take a look at the source code test, there is PERSON_COMPLEX definition:

storeTypes[2] = new JdbcType();
storeTypes[2].setDatabaseSchema("PUBLIC");
storeTypes[2].setDatabaseTable("PERSON_COMPLEX");
storeTypes[2].setKeyType("org.apache.ignite.cache.store.jdbc.model.PersonComplexKey");
storeTypes[2].setKeyFields(
  new JdbcTypeField(Types.INTEGER, "ID", int.class, "id"),
  new JdbcTypeField(Types.INTEGER, "ORG_ID", int.class, "orgId"),
  new JdbcTypeField(Types.INTEGER, "CITY_ID", int.class, "cityId"));

Key POJO and the DDL:

stmt.executeUpdate("CREATE TABLE IF NOT EXISTS " +
    "Person_Complex (id integer not null, org_id integer not null, city_id integer not null, " +
    "name varchar(50), salary integer, PRIMARY KEY(id, org_id, city_id))");

Configure Ignite Cache with Postgresql as 3rd party persistence using table with composite primary key

1 Answers