search

ldbc / ldbc_snb_interactive_v1_impls

Reference implementations for LDBC Social Network Benchmark's Interactive workload.
https://ldbcouncil.org/benchmarks/snb-interactive
Apache License 2.0
97 stars 85 forks source link

postgres: foreign key errors during create-validation-parameters.sh #380

Closed petere closed 1 year ago

petere commented 1 year ago

I'm trying this out with default settings.

I used ldbc_snb_datagen_hadoop main branch to generate the data. I did cp params-csv-merge-foreign.ini params.ini, left everything else the same, then ran the docker command from the README to produce the data. That worked.

Then I'm in this repository. I am using tag 1.0.0. I ran scripts/build.sh and installed psycopg2 as instructed. I set POSTGRES_CSV_DIR as appropriate. Then ran scripts/load-in-one-step.sh successfully. Then I ran driver/create-validation-parameters.sh, which failed very quickly like

Picked up JAVA_TOOL_OPTIONS: -Djava.awt.headless=true
CreateValidationParamsMode  Loaded Workload: org.ldbcouncil.snb.driver.workloads.interactive.LdbcSnbInteractiveWorkload
Unable to load query from file: queries/interactive-complex-3-duration-as-function.sqlUnable to load query from file: queries/interactive-complex-4-duration-as-function.sqlUnable to load query from file: queries/interactive-complex-7-with-second.sqlUnable to load query from file: queries/interactive-update-1.sqlUnable to load query from file: queries/interactive-update-4.sqlUnable to load query from file: queries/interactive-update-6.sqlUnable to load query from file: queries/interactive-update-7.sqlUnable to load query from file: queries/interactive-update-6-add-post-content.sqlUnable to load query from file: queries/interactive-update-6-add-post-imagefile.sqlSLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
CreateValidationParamsMode  Loaded DB: org.ldbcouncil.snb.impls.workloads.postgres.interactive.PostgresInteractiveDb
CreateValidationParamsMode  Retrieving operation stream for workload: LdbcSnbInteractiveWorkload
WorkloadStreams  Scanned 0 of 0 - OFFSET
WorkloadStreams  Scanned 10,002 of 10,000 - RUN
CreateValidationParamsMode  Driver Configuration
CreateValidationParamsMode  Workload Start Time:    2023-04-14 - 13:18:52.251
Parameters:
    Mode:                           create_validation
    Name:                           LDBC-SNB
    DB:                             org.ldbcouncil.snb.impls.workloads.postgres.interactive.PostgresInteractiveDb
    Workload:                       org.ldbcouncil.snb.driver.workloads.interactive.LdbcSnbInteractiveWorkload
    Operation Count:                10,000
    Warmup Count:                   0
    Skip Count:                     0
    Worker Threads:                 1
    Status Display Interval:        00:01.000 (m:s.ms)
    Time Unit:                      MILLISECONDS
    Results Directory:              /Users/peter/devel/ldbc/ldbc_snb_interactive_impls/postgres/results
    Time Compression Ratio:         0.0010000
    Validation Parameter Size:      50
    Validation Serialization Check: true
    Validation Database File:       validation_params.csv
    Spinner Sleep Duration:         00:00.000 (m:s.ms) / 0 (ms)
    Print Help:                     false
    Ignore Scheduled Start Times:   true
    User-defined parameters:
        databaseName = ldbcsnb
        endpoint = jdbc:postgresql://localhost:5432/ldbcsnb
        flush_log = false
        jdbcDriver = org.postgresql.ds.PGPoolingDataSource
        ldbc.snb.interactive.LdbcQuery10_enable = true
        ldbc.snb.interactive.LdbcQuery10_freq = 1
        ldbc.snb.interactive.LdbcQuery10_interleave = 4477
        ldbc.snb.interactive.LdbcQuery11_enable = true
        ldbc.snb.interactive.LdbcQuery11_freq = 1
        ldbc.snb.interactive.LdbcQuery11_interleave = 4477
        ldbc.snb.interactive.LdbcQuery12_enable = true
        ldbc.snb.interactive.LdbcQuery12_freq = 1
        ldbc.snb.interactive.LdbcQuery12_interleave = 4477
        ldbc.snb.interactive.LdbcQuery13_enable = true
        ldbc.snb.interactive.LdbcQuery13_freq = 1
        ldbc.snb.interactive.LdbcQuery13_interleave = 4477
        ldbc.snb.interactive.LdbcQuery14_enable = true
        ldbc.snb.interactive.LdbcQuery14_freq = 1
        ldbc.snb.interactive.LdbcQuery14_interleave = 4477
        ldbc.snb.interactive.LdbcQuery1_enable = true
        ldbc.snb.interactive.LdbcQuery1_freq = 1
        ldbc.snb.interactive.LdbcQuery1_interleave = 4477
        ldbc.snb.interactive.LdbcQuery2_enable = true
        ldbc.snb.interactive.LdbcQuery2_freq = 1
        ldbc.snb.interactive.LdbcQuery2_interleave = 4477
        ldbc.snb.interactive.LdbcQuery3_enable = true
        ldbc.snb.interactive.LdbcQuery3_freq = 1
        ldbc.snb.interactive.LdbcQuery3_interleave = 4477
        ldbc.snb.interactive.LdbcQuery4_enable = true
        ldbc.snb.interactive.LdbcQuery4_freq = 1
        ldbc.snb.interactive.LdbcQuery4_interleave = 4477
        ldbc.snb.interactive.LdbcQuery5_enable = true
        ldbc.snb.interactive.LdbcQuery5_freq = 1
        ldbc.snb.interactive.LdbcQuery5_interleave = 4477
        ldbc.snb.interactive.LdbcQuery6_enable = true
        ldbc.snb.interactive.LdbcQuery6_freq = 1
        ldbc.snb.interactive.LdbcQuery6_interleave = 4477
        ldbc.snb.interactive.LdbcQuery7_enable = true
        ldbc.snb.interactive.LdbcQuery7_freq = 1
        ldbc.snb.interactive.LdbcQuery7_interleave = 4477
        ldbc.snb.interactive.LdbcQuery8_enable = true
        ldbc.snb.interactive.LdbcQuery8_freq = 1
        ldbc.snb.interactive.LdbcQuery8_interleave = 4477
        ldbc.snb.interactive.LdbcQuery9_enable = true
        ldbc.snb.interactive.LdbcQuery9_freq = 1
        ldbc.snb.interactive.LdbcQuery9_interleave = 4477
        ldbc.snb.interactive.LdbcShortQuery1PersonProfile_enable = true
        ldbc.snb.interactive.LdbcShortQuery2PersonPosts_enable = true
        ldbc.snb.interactive.LdbcShortQuery3PersonFriends_enable = true
        ldbc.snb.interactive.LdbcShortQuery4MessageContent_enable = true
        ldbc.snb.interactive.LdbcShortQuery5MessageCreator_enable = true
        ldbc.snb.interactive.LdbcShortQuery6MessageForum_enable = true
        ldbc.snb.interactive.LdbcShortQuery7MessageReplies_enable = true
        ldbc.snb.interactive.LdbcUpdate1AddPerson_enable = true
        ldbc.snb.interactive.LdbcUpdate2AddPostLike_enable = true
        ldbc.snb.interactive.LdbcUpdate3AddCommentLike_enable = true
        ldbc.snb.interactive.LdbcUpdate4AddForum_enable = true
        ldbc.snb.interactive.LdbcUpdate5AddForumMembership_enable = true
        ldbc.snb.interactive.LdbcUpdate6AddPost_enable = true
        ldbc.snb.interactive.LdbcUpdate7AddComment_enable = true
        ldbc.snb.interactive.LdbcUpdate8AddFriendship_enable = true
        ldbc.snb.interactive.parameters_dir = test-data/substitution_parameters/
        ldbc.snb.interactive.scale_factor = 1
        ldbc.snb.interactive.short_read_dissipation = 0.2
        ldbc.snb.interactive.update_interleave = 4477
        ldbc.snb.interactive.updates_dir = test-data/update_streams/
        log_delayed = true
        password = mysecretpassword
        peer_identifiers =
        printQueryNames = true
        printQueryResults = false
        printQueryStrings = false
        queryDir = queries
        results_log = false
        user = postgres
        workload_statistics = false

CreateValidationParamsMode  Generating database validation file: /Users/peter/devel/ldbc/ldbc_snb_interactive_impls/postgres/validation_params.csv
########### LdbcUpdate3AddCommentLike
########### LdbcUpdate3AddCommentLike
Client  Client terminated unexpectedly
org.ldbcouncil.snb.driver.ClientException: Error encountered duration validation parameter creation
    at org.ldbcouncil.snb.driver.client.CreateValidationParamsMode.startExecutionAndAwaitCompletion(CreateValidationParamsMode.java:193)
    at org.ldbcouncil.snb.driver.Client.main(Client.java:65)
Caused by: org.ldbcouncil.snb.driver.ClientException: Error trying to write validation parameters to CSV file writer
    at org.ldbcouncil.snb.driver.client.CreateValidationParamsMode.startExecutionAndAwaitCompletion(CreateValidationParamsMode.java:182)
    ... 1 more
Caused by: org.ldbcouncil.snb.driver.generator.GeneratorException: Error executing operation to retrieve validation result
Db: org.ldbcouncil.snb.impls.workloads.postgres.interactive.PostgresInteractiveDb
Operation: LdbcUpdate3AddCommentLike{personId=2199023255589, commentId=343597393168, creationDate=Thu Nov 25 12:25:02 GMT 2010}
    at org.ldbcouncil.snb.driver.validation.ValidationParamsGenerator.doNext(ValidationParamsGenerator.java:106)
    at org.ldbcouncil.snb.driver.validation.ValidationParamsGenerator.doNext(ValidationParamsGenerator.java:31)
    at org.ldbcouncil.snb.driver.generator.Generator.hasNext(Generator.java:26)
    at org.ldbcouncil.snb.driver.validation.ValidationParamsToCsvRows.hasNext(ValidationParamsToCsvRows.java:36)
    at org.ldbcouncil.snb.driver.client.CreateValidationParamsMode.startExecutionAndAwaitCompletion(CreateValidationParamsMode.java:163)
    ... 1 more
Caused by: org.ldbcouncil.snb.driver.DbException: org.postgresql.util.PSQLException: ERROR: insert or update on table "likes" violates foreign key constraint "likes_l_personid_fkey"
  Detail: Key (l_personid)=(2199023255589) is not present in table "person".
    at org.ldbcouncil.snb.impls.workloads.postgres.operationhandlers.PostgresUpdateOperationHandler.executeOperation(PostgresUpdateOperationHandler.java:32)
    at org.ldbcouncil.snb.impls.workloads.postgres.operationhandlers.PostgresUpdateOperationHandler.executeOperation(PostgresUpdateOperationHandler.java:14)
    at org.ldbcouncil.snb.driver.validation.ValidationParamsGenerator.doNext(ValidationParamsGenerator.java:101)
    ... 5 more
Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "likes" violates foreign key constraint "likes_l_personid_fkey"
  Detail: Key (l_personid)=(2199023255589) is not present in table "person".
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:134)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
    at org.ldbcouncil.snb.impls.workloads.postgres.operationhandlers.PostgresUpdateOperationHandler.executeOperation(PostgresUpdateOperationHandler.java:30)
    ... 7 more

Now, if I use POSTGRES_CSV_DIR=pwd/test-data/ instead, it all works. So is there some kind of incompatibility with how I'm using the Hadoop-based data generation?

szarnyasg commented 1 year ago

Hi @petere, please edit the driver/create-validation-parameters.properties file and set the ldbc.snb.interactive.updates_dir to point to the directory with the SF1 update streams (updateStream_0_0_forum.csv and updateStream_0_0_person.csv) produced by the Datagen. Also ensure that ldbc.snb.interactive.parameters_dir points to the parameters used by SF1 – this is also produced by the Datagen and placed in the substitution_parameters directory.

I'll clarify this in the README(s).

petere commented 1 year ago

Ok, after I made the corresponding changes in postgres/driver/create-validation-parameters.properties, postgres/driver/validate.properties, and postgres/driver/benchmark.properties, I can run all three steps against my generated data.

szarnyasg commented 1 year ago

Excellent! For the larger data sets, the generator can be rather slow – consider using the pre-generated ones. They are linked at https://github.com/ldbc/data-sets-surf-repository#snb-interactive-v1-csvcompositemergeforeign-serializer-using-stringdateformatter