func (t TransfersFDW) prepare_master() {
dbconf, err := pgx.ParseDSN(cfg.ConnStrs[0])
checkErr(err)
conn, err := pgx.Connect(dbconf)
checkErr(err)
defer conn.Close()
exec(conn, "CREATE EXTENSION postgres_fdw")
for i, connstr := range cfg.ConnStrs[1:] {
conf, _ := pgx.ParseDSN(connstr)
server_sql := fmt.Sprintf(
"CREATE SERVER dtm%d FOREIGN DATA WRAPPER postgres_fdw options(dbname '%s', host '%s', port '%d')",
i, conf.Database, conf.Host, conf.Port)
foreign_sql := fmt.Sprintf(
"CREATE FOREIGN TABLE t_fdw%d() inherits (t) server dtm%d options(table_name 't')",
i, i)
mapping_sql := fmt.Sprintf(
"CREATE USER MAPPING for %s SERVER dtm%d options (user '%s')",
conf.User, i, conf.User)
exec(conn, server_sql)
exec(conn, foreign_sql)
exec(conn, mapping_sql)
}
if dbconf.User != "" {
}
}
func (t Readers) writer(id int, cCommits chan int, cAborts chan int, wg *sync.WaitGroup) {
var updates = 0
var conns []*pgx.Conn
for _, connstr := range cfg.ConnStrs {
dbconf, err := pgx.ParseDSN(connstr)
checkErr(err)
conn, err := pgx.Connect(dbconf)
checkErr(err)
defer conn.Close()
conns = append(conns, conn)
}
for updates < cfg.IterNum {
acc := rand.Intn(cfg.AccountsNum)
if cfg.UseDtm {
xid := execQuery(conns[0], "select dtm_begin_transaction()")
for i := 1; i < len(conns); i++ {
exec(conns[i], "select dtm_join_transaction($1)", xid)
}
}
for _, conn := range conns {
exec(conn, "begin transaction isolation level "+cfg.Isolation)
exec(conn, "update t set v = v + 1 where u=$1", acc)
}
commit(conns...)
updates++
}
// cCommits <- updates
wg.Done()
}
func main() {
var err error
log.SetOutput(os.Stdout)
kingpin.MustParse(appCmdLine.Parse(os.Args[1:]))
loadConfig(*configPathArg)
dbConfig, err = pgx.ParseDSN(config.Postgres.Dsn)
if err != nil {
log.Fatalln("Could not parse DSN: ", err)
}
interrupt = make(chan os.Signal, 1)
go signalHanlder()
log.Println("pgturtle started.")
for {
checkConnection()
_, err := db.WaitForNotification(time.Minute)
if err != nil && err != pgx.ErrNotificationTimeout {
log.Println("Could not wait for notification:", err)
}
process()
}
}
func (t TransfersFDW) reader(wg *sync.WaitGroup, cFetches chan int, inconsistency *bool) {
var sum int64
var prevSum int64 = 0
dbconf, err := pgx.ParseDSN(cfg.ConnStrs[0])
checkErr(err)
conn, err := pgx.Connect(dbconf)
checkErr(err)
defer conn.Close()
for running {
exec(conn, "select dtm_begin_transaction()")
exec(conn, "begin transaction isolation level "+cfg.Isolation)
sum = execQuery64(conn, "select sum(v) from t")
if sum != prevSum {
fmt.Printf("Total=%d\n", sum)
fmt.Printf("inconsistency!\n")
*inconsistency = true
prevSum = sum
}
exec(conn, "commit")
}
conn.Close()
wg.Done()
}
func (t TransfersTS) writer(id int, cCommits chan int, cAborts chan int, wg *sync.WaitGroup) {
var conns []*pgx.Conn
var nGlobalTrans = 0
var snapshot int64
var csn int64
if len(cfg.ConnStrs) == 1 {
cfg.ConnStrs.Set(cfg.ConnStrs[0])
}
for _, connstr := range cfg.ConnStrs {
dbconf, err := pgx.ParseDSN(connstr)
checkErr(err)
conn, err := pgx.Connect(dbconf)
checkErr(err)
defer conn.Close()
conns = append(conns, conn)
}
for i := 0; i < cfg.IterNum; i++ {
gtid := strconv.Itoa(id) + "." + strconv.Itoa(i)
amount := 2*rand.Intn(2) - 1
from_acc := cfg.Writers.StartId + 2*id + 1
to_acc := cfg.Writers.StartId + 2*id + 2
conn1 := conns[rand.Intn(len(conns))]
conn2 := conns[rand.Intn(len(conns))]
if conn1 == conn2 {
continue
}
exec(conn1, "begin transaction")
exec(conn2, "begin transaction")
snapshot = _execQuery(conn1, "select dtm_extend($1)", gtid)
snapshot = _execQuery(conn2, "select dtm_access($1, $2)", snapshot, gtid)
exec(conn1, "update t set v = v - $1 where u=$2", amount, from_acc)
exec(conn2, "update t set v = v + $1 where u=$2", amount, to_acc)
exec(conn1, "prepare transaction '" + gtid + "'")
exec(conn2, "prepare transaction '" + gtid + "'")
exec(conn1, "select dtm_begin_prepare($1)", gtid)
exec(conn2, "select dtm_begin_prepare($1)", gtid)
csn = _execQuery(conn1, "select dtm_prepare($1, 0)", gtid)
csn = _execQuery(conn2, "select dtm_prepare($1, $2)", gtid, csn)
exec(conn1, "select dtm_end_prepare($1, $2)", gtid, csn)
exec(conn2, "select dtm_end_prepare($1, $2)", gtid, csn)
exec(conn1, "commit prepared '" + gtid + "'")
exec(conn2, "commit prepared '" + gtid + "'")
nGlobalTrans++
}
cCommits <- nGlobalTrans
cAborts <- 0
wg.Done()
}
func (t TransfersTS) reader(wg *sync.WaitGroup, cFetches chan int, inconsistency *bool) {
var prevSum int64 = 0
var snapshot int64
var conns []*pgx.Conn
for _, connstr := range cfg.ConnStrs {
dbconf, err := pgx.ParseDSN(connstr)
checkErr(err)
conn, err := pgx.Connect(dbconf)
checkErr(err)
defer conn.Close()
conns = append(conns, conn)
}
for running {
var sum int64 = 0
var xid int32
for _, conn := range conns {
exec(conn, "begin transaction")
}
for i, conn := range conns {
if cfg.UseDtm {
if i == 0 {
// xid = execQuery(conn, "select dtm_begin_transaction()")
snapshot = _execQuery(conn, "select dtm_extend()")
} else {
// exec(conn, "select dtm_join_transaction($1)", xid)
snapshot = _execQuery(conn, "select dtm_access($1)", snapshot)
}
}
exec(conn, "begin transaction isolation level " + cfg.Isolation)
sum += _execQuery(conn, "select sum(v) from t")
}
for _, conn := range conns {
sum += _execQuery(conn, "select sum(v) from t")
}
commit(conns...)
if (sum != prevSum) {
fmt.Printf("Total=%d xid=%d\n", sum, xid)
if (prevSum != 0) {
fmt.Printf("inconsistency!\n")
*inconsistency = true
}
prevSum = sum
}
}
wg.Done()
}
func TestParseDSN(t *testing.T) {
t.Parallel()
tests := []struct {
url string
connParams pgx.ConnConfig
}{
{
url: "user=jack password=secret host=localhost port=5432 dbname=mydb",
connParams: pgx.ConnConfig{
User: "******",
Password: "******",
Host: "localhost",
Port: 5432,
Database: "mydb",
},
},
{
url: "user=jack host=localhost port=5432 dbname=mydb",
connParams: pgx.ConnConfig{
User: "******",
Host: "localhost",
Port: 5432,
Database: "mydb",
},
},
{
url: "user=jack host=localhost dbname=mydb",
connParams: pgx.ConnConfig{
User: "******",
Host: "localhost",
Database: "mydb",
},
},
}
for i, tt := range tests {
connParams, err := pgx.ParseDSN(tt.url)
if err != nil {
t.Errorf("%d. Unexpected error from pgx.ParseDSN(%q) => %v", i, tt.url, err)
continue
}
if !reflect.DeepEqual(connParams, tt.connParams) {
t.Errorf("%d. expected %#v got %#v", i, tt.connParams, connParams)
}
}
}
func (t Readers) prepare_one(connstr string, wg *sync.WaitGroup) {
dbconf, err := pgx.ParseDSN(connstr)
checkErr(err)
conn, err := pgx.Connect(dbconf)
checkErr(err)
defer conn.Close()
if cfg.UseDtm {
exec(conn, "drop extension if exists pg_dtm")
exec(conn, "create extension pg_dtm")
}
exec(conn, "drop table if exists t cascade")
exec(conn, "create table t(u int primary key, v int)")
exec(conn, "insert into t (select generate_series(0,$1-1), $2)", cfg.AccountsNum, 0)
wg.Done()
}
func (t TransfersFDW) writer(id int, cCommits chan int, cAborts chan int, wg *sync.WaitGroup) {
var err error
var nAborts = 0
var nCommits = 0
var myCommits = 0
dbconf, err := pgx.ParseDSN(cfg.ConnStrs[0])
checkErr(err)
conn, err := pgx.Connect(dbconf)
checkErr(err)
defer conn.Close()
start := time.Now()
for myCommits < cfg.IterNum {
amount := 1
from_acc := cfg.Writers.StartId + 2*id + 1
to_acc := cfg.Writers.StartId + 2*id + 2
if cfg.UseDtm {
exec(conn, "select dtm_begin_transaction()")
}
exec(conn, "begin transaction isolation level "+cfg.Isolation)
ok1 := execUpdate(conn, "update t set v = v - $1 where u=$2", amount, from_acc)
ok2 := execUpdate(conn, "update t set v = v + $1 where u=$2", amount, to_acc)
if !ok1 || !ok2 {
exec(conn, "rollback")
nAborts += 1
} else {
exec(conn, "commit")
nCommits += 1
myCommits += 1
}
if time.Since(start).Seconds() > 1 {
cCommits <- nCommits
cAborts <- nAborts
nCommits = 0
nAborts = 0
start = time.Now()
}
}
cCommits <- nCommits
cAborts <- nAborts
wg.Done()
}
func NewPgxPool(url string) (*pgx.ConnPool, error) {
dbcfg := pgx.ConnPoolConfig{
MaxConnections: maxConnectionsFlag.Value(),
}
var err error
if url == "" {
dbcfg.ConnConfig, err = pgx.ParseEnvLibpq()
} else if strings.HasPrefix(url, "postgresql://") {
dbcfg.ConnConfig, err = pgx.ParseURI(url)
} else {
dbcfg.ConnConfig, err = pgx.ParseDSN(url)
}
if err != nil {
return nil, err
}
return pgx.NewConnPool(dbcfg)
}
func (t Readers) reader(wg *sync.WaitGroup, cFetches chan int, inconsistency *bool) {
var fetches = 0
var conns []*pgx.Conn
var sum int32 = 0
for _, connstr := range cfg.ConnStrs {
dbconf, err := pgx.ParseDSN(connstr)
checkErr(err)
conn, err := pgx.Connect(dbconf)
checkErr(err)
defer conn.Close()
conns = append(conns, conn)
}
for running {
acc := rand.Intn(cfg.AccountsNum)
con := rand.Intn(len(conns))
sum += execQuery(conns[con], "select v from t where u=$1", acc)
fetches++
}
// cFetches <- fetches
wg.Done()
}
func (t TransfersFDW) prepare_slave(id int, connstr string, wg *sync.WaitGroup) {
dbconf, err := pgx.ParseDSN(connstr)
checkErr(err)
conn, err := pgx.Connect(dbconf)
checkErr(err)
defer conn.Close()
if len(dbconf.User) == 0 {
fmt.Println("ERROR: FDW test need explicit usernames in connection strings")
os.Exit(2)
}
if cfg.UseDtm {
exec(conn, "drop extension if exists pg_dtm")
exec(conn, "create extension pg_dtm")
}
exec(conn, "drop extension if exists postgres_fdw cascade")
exec(conn, "drop table if exists t cascade")
exec(conn, "create table t(u int primary key, v int)")
exec(conn, "insert into t (select $3*generate_series(0,$1-1) + $4, $2)",
cfg.AccountsNum, 0, len(cfg.ConnStrs), id)
wg.Done()
}
func TestParseDSN(t *testing.T) {
t.Parallel()
tests := []struct {
url string
connParams pgx.ConnConfig
}{
{
url: "user=jack password=secret host=localhost port=5432 dbname=mydb sslmode=disable",
connParams: pgx.ConnConfig{
User: "******",
Password: "******",
Host: "localhost",
Port: 5432,
Database: "mydb",
RuntimeParams: map[string]string{},
},
},
{
url: "user=jack password=secret host=localhost port=5432 dbname=mydb sslmode=prefer",
connParams: pgx.ConnConfig{
User: "******",
Password: "******",
Host: "localhost",
Port: 5432,
Database: "mydb",
TLSConfig: &tls.Config{
InsecureSkipVerify: true,
},
UseFallbackTLS: true,
FallbackTLSConfig: nil,
RuntimeParams: map[string]string{},
},
},
{
url: "user=jack password=secret host=localhost port=5432 dbname=mydb",
connParams: pgx.ConnConfig{
User: "******",
Password: "******",
Host: "localhost",
Port: 5432,
Database: "mydb",
TLSConfig: &tls.Config{
InsecureSkipVerify: true,
},
UseFallbackTLS: true,
FallbackTLSConfig: nil,
RuntimeParams: map[string]string{},
},
},
{
url: "user=jack host=localhost port=5432 dbname=mydb",
connParams: pgx.ConnConfig{
User: "******",
Host: "localhost",
Port: 5432,
Database: "mydb",
TLSConfig: &tls.Config{
InsecureSkipVerify: true,
},
UseFallbackTLS: true,
FallbackTLSConfig: nil,
RuntimeParams: map[string]string{},
},
},
{
url: "user=jack host=localhost dbname=mydb",
connParams: pgx.ConnConfig{
User: "******",
Host: "localhost",
Database: "mydb",
TLSConfig: &tls.Config{
InsecureSkipVerify: true,
},
UseFallbackTLS: true,
FallbackTLSConfig: nil,
RuntimeParams: map[string]string{},
},
},
{
url: "user=jack host=localhost dbname=mydb application_name=pgxtest search_path=myschema",
connParams: pgx.ConnConfig{
User: "******",
Host: "localhost",
Database: "mydb",
TLSConfig: &tls.Config{
InsecureSkipVerify: true,
},
UseFallbackTLS: true,
FallbackTLSConfig: nil,
RuntimeParams: map[string]string{
"application_name": "pgxtest",
"search_path": "myschema",
},
},
},
}
for i, tt := range tests {
connParams, err := pgx.ParseDSN(tt.url)
if err != nil {
t.Errorf("%d. Unexpected error from pgx.ParseDSN(%q) => %v", i, tt.url, err)
continue
}
if !reflect.DeepEqual(connParams, tt.connParams) {
t.Errorf("%d. expected %#v got %#v", i, tt.connParams, connParams)
}
}
}
func (t Transfers) writer(id int, cCommits chan int, cAborts chan int, wg *sync.WaitGroup) {
var nAborts = 0
var nCommits = 0
var myCommits = 0
var conns []*pgx.Conn
if len(cfg.ConnStrs) == 1 {
cfg.ConnStrs.Set(cfg.ConnStrs[0])
}
for _, connstr := range cfg.ConnStrs {
dbconf, err := pgx.ParseDSN(connstr)
checkErr(err)
conn, err := pgx.Connect(dbconf)
checkErr(err)
defer conn.Close()
conns = append(conns, conn)
}
start := time.Now()
for myCommits < cfg.IterNum {
amount := 1
from_acc := cfg.Writers.StartId + 2*id + 1
to_acc := cfg.Writers.StartId + 2*id + 2
src := conns[rand.Intn(len(conns))]
dst := conns[rand.Intn(len(conns))]
if src == dst {
continue
}
if cfg.UseDtm {
xid := execQuery(src, "select dtm_begin_transaction()")
exec(dst, "select dtm_join_transaction($1)", xid)
}
parallel_exec(
[]*pgx.Conn{src, dst},
[]string{"begin transaction isolation level " + cfg.Isolation,
"begin transaction isolation level " + cfg.Isolation})
ok := true
sql1 := "update t set v = v - " +
strconv.Itoa(amount) + " where u=" + strconv.Itoa(from_acc)
sql2 := "update t set v = v + " +
strconv.Itoa(amount) + " where u=" + strconv.Itoa(to_acc)
ok = parallel_exec([]*pgx.Conn{src, dst}, []string{sql1, sql2})
if ok {
commit(src, dst)
nCommits += 1
myCommits += 1
} else {
exec(src, "rollback")
exec(dst, "rollback")
nAborts += 1
}
if time.Since(start).Seconds() > 1 {
cCommits <- nCommits
cAborts <- nAborts
nCommits = 0
nAborts = 0
start = time.Now()
}
}
cCommits <- nCommits
cAborts <- nAborts
wg.Done()
}
func (t TransfersTS) writer(id int, cCommits chan int, cAborts chan int, wg *sync.WaitGroup) {
var nGlobalTrans = 0
var snapshot int64
var csn int64
nWriters := cfg.Writers.Num
if len(cfg.ConnStrs) == 1 {
cfg.ConnStrs.Set(cfg.ConnStrs[0])
}
// for _, connstr := range cfg.ConnStrs {
// dbconf, err := pgx.ParseDSN(connstr)
// checkErr(err)
// conn, err := pgx.Connect(dbconf)
// checkErr(err)
// defer conn.Close()
// conns = append(conns, conn)
// }
dbconf1, err := pgx.ParseDSN(cfg.ConnStrs[id%len(cfg.ConnStrs)])
checkErr(err)
conn1, err := pgx.Connect(dbconf1)
checkErr(err)
defer conn1.Close()
dbconf2, err := pgx.ParseDSN(cfg.ConnStrs[(id+1)%len(cfg.ConnStrs)])
checkErr(err)
conn2, err := pgx.Connect(dbconf2)
checkErr(err)
defer conn2.Close()
for i := 0; i < cfg.IterNum; i++ {
gtid := strconv.Itoa(cfg.Writers.StartId) + "." + strconv.Itoa(id) + "." + strconv.Itoa(i)
amount := 2*rand.Intn(2) - 1
from_acc := rand.Intn((cfg.AccountsNum-nWriters)/nWriters)*nWriters + id
to_acc := rand.Intn((cfg.AccountsNum-nWriters)/nWriters)*nWriters + id
exec(conn1, "begin transaction")
exec(conn2, "begin transaction")
if cfg.UseDtm {
snapshot = _execQuery(conn1, "select dtm_extend($1)", gtid)
snapshot = _execQuery(conn2, "select dtm_access($1, $2)", snapshot, gtid)
}
exec(conn1, "update t set v = v - $1 where u=$2", amount, from_acc)
exec(conn2, "update t set v = v + $1 where u=$2", amount, to_acc)
exec(conn1, "prepare transaction '"+gtid+"'")
exec(conn2, "prepare transaction '"+gtid+"'")
if cfg.UseDtm {
exec(conn1, "select dtm_begin_prepare($1)", gtid)
exec(conn2, "select dtm_begin_prepare($1)", gtid)
csn = _execQuery(conn1, "select dtm_prepare($1, 0)", gtid)
csn = _execQuery(conn2, "select dtm_prepare($1, $2)", gtid, csn)
exec(conn1, "select dtm_end_prepare($1, $2)", gtid, csn)
exec(conn2, "select dtm_end_prepare($1, $2)", gtid, csn)
}
exec(conn1, "commit prepared '"+gtid+"'")
exec(conn2, "commit prepared '"+gtid+"'")
nGlobalTrans++
}
cCommits <- nGlobalTrans
cAborts <- 0
wg.Done()
}