Initial source is "Design Data Intensive Application"

• Chapter 2 "Data Model and Query Languages"
• Chapter 3 "Storage and Retrieval"

blocked by #34
to run automation tests server has to implement PostgreSQL Wire Protocol just enough to communicate with psql and rust-postgres driver

#67 Saving column names in system namespace

Things to consider:

• types representation
• referencing per table and cross table constraints
• referencing Primary Key indexes

Setup and describe integration compatibility tests with psql from docker container, instead of locally installed

Initial source is "Design Data Intensive Application"

• Chapter 6 "Partitioning"

Documentations:

• CockroachDB Architecture
• PostgreSQL internals

Books:

• Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems
• Database Internals: A Deep Dive into How Distributed Data Systems Work
• Database System Concepts

Papers:

• Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databases
• Amazon Aurora: On Avoiding Distributed Consensus for I/Os, Commits, and Membership Changes

Video courses:

• CMU Intro to Database Systems 2019
• CMU Intro to Database Systems 2018
• CMU Intro to Database Systems 2017
• CMU Advanced Database Systems 2019
• CMU Advanced Database Systems 2018
• CMU Advanced Database Systems 2017
• CMU Advanced Database Systems 2016

Repositories:

• hermitage - What are the differences between the transaction isolation levels in databases? This is a suite of test cases which differentiate isolation levels

Blogs:

• Martin Kleppmann
• Jepsen
• SQL-99 CaretDB
• Murat Demirbas

Blog Posts:

• Amazon Aurora ascendant: How we designed a cloud-native relational database

Conferences:

• EuroSys

Slides:

• Impossibility in area of Distributed Computing

Research Groups:

• Das Lab Harvard
• Das Lab Illinois
• CMU
• SIGMOD
• VLDB
• CIDRDB
• EDBICDT
• Microsoft Research
• HAS Lab

Epic #15
Blocked by #2

SQL to reproduce:

create schema SMOKE_QUERIES;

create table SMOKE_QUERIES.NOT_NULLABLE_TABLE (
    column_1 smallint check (column_1 > 100)
);

insert into SMOKE_QUERIES.NOT_NULLABLE_TABLE values (99);

Expected result:

ERROR: check 'column value > 100' constraint is violated

The problem with this is that check constraint should use predicate evaluation logic and should be persisted to system.columns table

• create schemas
• drop schemas
• creates tables
• drop tables
• inserts a single row
• selects all values
• deletes all records
• updates all records

options like ssl, gssenc and sspi encryptions has to be extendable and configurable by implementations

It could be travis-ci or GitHub actions
Depends on #24

Under Epic #29

It should send non hardcoded value when client Simple Query type request is coming

• Primary Key
• Unique
• Foreign Key

Test cases:

• create table with FK to PK on another table
• create table with FK to neither PK nor Unique column on another table
• Multiple columns PK
• Multiple columns Unique
• Multiple columns FK
• FK with wrong reference type

Currently client dumps data from server as UTF8 string.
If select query was sent client has to print selected data in human readable format like

+------+------+
| col1 | col2 |
+------+------+
|     1|     2|
+------+------+

Storage frontend does not handle situations when Backend stores less records than requested. (e.g. does not try to roll back writes by removing them or writing old values in case of update)

• integer types smallint, integer and bigint #77
• character string types char(n) and varchar(n) #89
• boolean type #107
• numeric types decimal(p, s) and its alias numeric(p, s) #90
• approximate types real and double precision #91
• date time types time [without timezone], time with timezone, timestamp [without timezone], timestamp with timezone and date
• time interval #93

1. create_schema - what if sled won't be able to create Db
2. create_table - what if sled won't be able to open_tree normally
3. drop_table - what if sled won't be able to drop_tree normally
4. insert_into -
   1. what if sled won't be able to open_tree normally
   2. what if sled won't be able to insert normally
5. select_all_from -
   1. what if sled won't be able to open_tree normally
   2. what if sled won't be able to read data in iter normally
6. update_all -
   1. open_tree
   2. fetch key with iter().keys()
7. delete_all_from -
   1. open_tree
   2. fetch key with iter().keys()

First part of WA is in tests itself - test client send query that can't be parsed and then second client opens up a connection to server Node
Second part of WA is in sql engine - it returns QueryResult::Terminate if query is not parsable.
Third part is in Node - it sets state into STOPPED when gets QueryResult::Terminate from sql engine

Possible ways to fix it are:

1. try to use async tokio-postgres if it doesn't have same problem as synchronous sfackler/rust-postgres#613
2. use PostgreSQL drivers of other language and write tests in that lang (e.g. java, python or ruby)

Currently, database is developed as to store all metadata about schemas and tables in-memory only. If restart process all data would be lost. Technically it is saved on disk with sled, but storage wouldn't find it on disk so will be thinking that there is nothing exists.

Currently, database supports only type constraints (implemented list see #17).

SQL to reproduce:

create schema SMOKE_QUERIES;

create table SMOKE_QUERIES.NOT_NULLABLE_TABLE (
    column_1 smallint not null
);

insert into SMOKE_QUERIES.NOT_NULLABLE_TABLE values (null);

Expected result:

ERROR: not null constraint for column_1 is violated

When implementing the task see #124 (comment) conversation to support null values in the storage

Optionally database should support violation of multiple constraints

Currently server can work with single client - even worse it shuts down when client disconnects

Currently, database supports only type constraints (implemented list see #17).

SQL to reproduce:

create schema SMOKE_QUERIES;

create table SMOKE_QUERIES.NOT_NULLABLE_TABLE (
    column_1 smallint constraint column_1_is_never_null not null
);

insert into SMOKE_QUERIES.NOT_NULLABLE_TABLE values (null);

Expected result:

ERROR: constraint 'column_1_is_never_null' is violated

When implementing the task see #124 (comment) conversation to support null values in the storage

Optionally database should support violation of multiple constraints

• creates schemas
• drops schemas
• creates tables
• drops tables
• inserts a single row
• selects all values
• deletes all records
• updates all records

Initial source is "Design Data Intensive Application"

• Chapter 7 "Transactions"
• Chapter 9 "Consistency and Consensus"

Some PoC is required to investigate is there any parts that should be extracted into protocol API and implemented by database backend.
As a code reference we can take a look at https://github.com/MaterializeInc/materialize/blob/master/src/pgwire/src/server.rs#L49

Initial question is: what the hell is it? - Protocol, Strategy

Initial source "Design Data Intensive Application" Chapter 5 "Replication"
Paragraphs:

• "Handling Write Conflicts"
• "Detecting Concurrent Writes"

• Simple Query #31
• TLS #32
• Authentication #30
• Extended Query #177

The primary reason for this is to have more convenient and readable unit tests for client server communication

• implement simplest possible interaction with psql client
• Provide documentation of local setup (for now it is only local)
• Provide documentation of PostgreSQL queries list for testing compatibility

How to reproduce:

1. Run server app
2. Run client app
3. Press ^C in client app
4. Server does not deregister Token(n)

Server fails on empty SQL query

steps to reproduce:

• start server
• start client
• type help and hit enter in client app
• wait for server response
• hit enter in client app

client trace with the command RUST_LOG=trace cargo run --bin client

 INFO  client > Starting client
help
 DEBUG client > typed command "help\n"
 DEBUG client > command send to server
 DEBUG client > 255 server result code
Ok("")

 DEBUG client > typed command "\n"
 DEBUG client > command send to server
 DEBUG client > 69 server result code
Unknown server code 69. Exiting!

server trace with the command RUST_LOG=trace cargo run --bin server

 INFO  server > Starting server on port 7000
 INFO  server > SQL engine has been created
 INFO  server > Network buffer of 256 size created
 DEBUG server > Connection 127.0.0.1:58240 is accepted
 TRACE server > 4 bytes read from network connection
 DEBUG server > help
 DEBUG server > Received from 127.0.0.1:58240 client
 DEBUG sqlparser::parser > Parsing sql 'help'...
 DEBUG server            > Query execution result
 DEBUG server            > Err(UnimplementedBranch("ParserError(\"Expected a keyword at the beginning of a statement, found: help\")"))
 TRACE server            > 0 bytes read from network connection
 DEBUG server            > 
 DEBUG server            > Received from 127.0.0.1:58240 client
 DEBUG sqlparser::parser > Parsing sql ''...
 DEBUG server            > Query execution result
 DEBUG server            > Err(UnimplementedBranch("UNIMPLEMENTED HANDLING OF \nNone\n STATEMENT!"))
 TRACE server            > 0 bytes read from network connection
 DEBUG server            > 
 DEBUG server            > Received from 127.0.0.1:58240 client
 DEBUG sqlparser::parser > Parsing sql ''...
 DEBUG server            > Query execution result
 DEBUG server            > Err(UnimplementedBranch("UNIMPLEMENTED HANDLING OF \nNone\n STATEMENT!"))
Error: Os { code: 32, kind: BrokenPipe, message: "Broken pipe" }

Blocked by #194

Currently, database does not handle authentication at all. For now we can:

• create users table in system namespace during FrontendStore initialisation
• make single record with admin user and simple password like 123
• program adjust authentication flow with protocol - here be dragons.
  One of an idea add Authenticator trait to protocol and impl in node module that glue PG wire protocol with other part of backend

Epic #15
Blocked by #2

SQL to reproduce:

create schema SMOKE_QUERIES;

create table SMOKE_QUERIES.NOT_NULLABLE_TABLE (
    column_1 smallint constraint column_1_greater_than_100 check (column_1 > 100)
);

insert into SMOKE_QUERIES.NOT_NULLABLE_TABLE values (99);

Expected result:

ERROR: 'constraint column_1_greater_than_100' is violated

The problem with this is that check constraint should use predicate evaluation logic and should be persisted to system.columns table

• Non-null for all data types
  • anonymous #25
  • named #26
• Check for numbers
  • anonymous #27
  • named #28

Check constraint is blocked by #2

See PostgreSQL docs https://www.postgresql.org/docs/12/ddl-constraints.html

Currently, sql_handler::Handler uses unimplemented! macro. It is not user friendly and better to send ErrorResponse with Currently "your sql query" is not supported message

• PostgreSQL Wire protocol
• test_helpers
• core

How to reproduce:

1. Run example from README
2. In client up press ^C
   Server app is crashed with backtrace:

thread 'main' panicked at 'Unexpected error: Connection reset by peer (os error 54)', src/bin/server.rs:124:25
stack backtrace:
   0: backtrace::backtrace::libunwind::trace
             at /Users/runner/.cargo/registry/src/github.com-1ecc6299db9ec823/backtrace-0.3.44/src/backtrace/libunwind.rs:86
   1: backtrace::backtrace::trace_unsynchronized
             at /Users/runner/.cargo/registry/src/github.com-1ecc6299db9ec823/backtrace-0.3.44/src/backtrace/mod.rs:66
   2: std::sys_common::backtrace::_print_fmt
             at src/libstd/sys_common/backtrace.rs:78
   3: <std::sys_common::backtrace::_print::DisplayBacktrace as core::fmt::Display>::fmt
             at src/libstd/sys_common/backtrace.rs:59
   4: core::fmt::write
             at src/libcore/fmt/mod.rs:1063
   5: std::io::Write::write_fmt
             at src/libstd/io/mod.rs:1426
   6: std::sys_common::backtrace::_print
             at src/libstd/sys_common/backtrace.rs:62
   7: std::sys_common::backtrace::print
             at src/libstd/sys_common/backtrace.rs:49
   8: std::panicking::default_hook::{{closure}}
             at src/libstd/panicking.rs:204
   9: std::panicking::default_hook
             at src/libstd/panicking.rs:224
  10: std::panicking::rust_panic_with_hook
             at src/libstd/panicking.rs:470
  11: rust_begin_unwind
             at src/libstd/panicking.rs:378
  12: std::thread::local::fast::Key<T>::try_initialize
  13: server::main
             at src/bin/server.rs:124
  14: std::rt::lang_start::{{closure}}
             at /rustc/4fb7144ed159f94491249e86d5bbd033b5d60550/src/libstd/rt.rs:67
  15: std::rt::lang_start_internal::{{closure}}
             at src/libstd/rt.rs:52
  16: std::panicking::try::do_call
             at src/libstd/panicking.rs:303
  17: __rust_maybe_catch_panic
             at src/libpanic_unwind/lib.rs:86
  18: std::panicking::try
             at src/libstd/panicking.rs:281
  19: std::panic::catch_unwind
             at src/libstd/panic.rs:394
  20: std::rt::lang_start_internal
             at src/libstd/rt.rs:51
  21: std::rt::lang_start
             at /rustc/4fb7144ed159f94491249e86d5bbd033b5d60550/src/libstd/rt.rs:67
  22: server::main
note: Some details are omitted, run with `RUST_BACKTRACE=full` for a verbose backtrace.

Process finished with exit code 101