composite_primary_key_test.go 5.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146
  1. package pgserver
  2. import (
  3. "strings"
  4. "testing"
  5. )
  6. // TestCompositePrimaryKeyWireBehavior drives a composite key through the
  7. // simple-query protocol: DDL, duplicate and NOT NULL SQLSTATEs, full-key reads,
  8. // PRAGMA table_info ordinals, and the information_schema key columns.
  9. func TestCompositePrimaryKeyWireBehavior(t *testing.T) {
  10. c, client := newDataConnection(t)
  11. query := func(sql string) []*Message {
  12. return runQuery(t, c, client, &Message{Type: MsgQuery, Data: append([]byte(sql), 0)})
  13. }
  14. if msgs := query("CREATE TABLE pairs (a TEXT, b TEXT, v TEXT, PRIMARY KEY (a, b))"); errorCode(msgs[0]) != "" {
  15. t.Fatalf("CREATE TABLE failed: %s", errorMessage(msgs[0]))
  16. }
  17. if msgs := query("INSERT INTO pairs (a, b, v) VALUES ('x', 'y', 'one')"); errorCode(msgs[0]) != "" {
  18. t.Fatalf("INSERT failed: %s", errorMessage(msgs[0]))
  19. }
  20. if msgs := query("INSERT INTO pairs (a, b, v) VALUES ('x', 'y', 'two')"); errorCode(msgs[0]) != ErrCodeUniqueViolation {
  21. t.Fatalf("duplicate composite key SQLSTATE = %q, want 23505", errorCode(msgs[0]))
  22. } else if got, want := errorMessage(msgs[0]), "Execution error: duplicate primary key: ('x','y')"; got != want {
  23. t.Fatalf("duplicate composite key message = %q, want %q", got, want)
  24. }
  25. // A numeric composite key renders as a tuple, not the length-prefixed
  26. // durable encoding.
  27. if msgs := query("CREATE TABLE numbered (a INTEGER, b INTEGER, PRIMARY KEY (a, b))"); errorCode(msgs[0]) != "" {
  28. t.Fatalf("CREATE TABLE numbered failed: %s", errorMessage(msgs[0]))
  29. }
  30. query("INSERT INTO numbered (a, b) VALUES (1, 11)")
  31. if msgs := query("INSERT INTO numbered (a, b) VALUES (1, 11)"); errorCode(msgs[0]) != ErrCodeUniqueViolation {
  32. t.Fatalf("duplicate numeric composite key SQLSTATE = %q, want 23505", errorCode(msgs[0]))
  33. } else if got, want := errorMessage(msgs[0]), "Execution error: duplicate primary key: (1,11)"; got != want {
  34. t.Fatalf("duplicate numeric composite key message = %q, want %q", got, want)
  35. }
  36. if code := errorCode(query("INSERT INTO pairs (a, v) VALUES ('x', 'missing b')")[0]); code != "23502" {
  37. t.Fatalf("missing key component SQLSTATE = %q, want 23502", code)
  38. }
  39. // Only the accepted row survived the rejected inserts.
  40. selectRows := query("SELECT v FROM pairs WHERE a = 'x' AND b = 'y'")
  41. var dataRows []*Message
  42. for _, msg := range selectRows {
  43. if msg.Type == MsgDataRow {
  44. dataRows = append(dataRows, msg)
  45. }
  46. }
  47. if len(dataRows) != 1 {
  48. t.Fatalf("full-key select returned %d rows, want 1", len(dataRows))
  49. }
  50. if values := dataRowValues(t, dataRows[0]); len(values) != 1 || values[0] != "one" {
  51. t.Fatalf("full-key select values = %v, want [one]", values)
  52. }
  53. // PRAGMA table_info reports the pk ordinal, not a bare flag.
  54. var info [][]string
  55. for _, msg := range query("PRAGMA table_info(pairs)") {
  56. if msg.Type == MsgDataRow {
  57. info = append(info, dataRowValues(t, msg))
  58. }
  59. }
  60. if len(info) != 3 {
  61. t.Fatalf("table_info rows = %d, want 3", len(info))
  62. }
  63. wantPK := []string{"1", "2", "0"}
  64. for i, row := range info {
  65. if row[5] != wantPK[i] {
  66. t.Fatalf("table_info row %d pk = %s, want %s", i, row[5], wantPK[i])
  67. }
  68. }
  69. // KEY_COLUMN_USAGE lists one row per key column with its ordinal.
  70. var usage [][]string
  71. for _, msg := range query("SELECT column_name, ordinal_position FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name = 'pairs'") {
  72. if msg.Type == MsgDataRow {
  73. usage = append(usage, dataRowValues(t, msg))
  74. }
  75. }
  76. if len(usage) != 2 {
  77. t.Fatalf("KEY_COLUMN_USAGE rows = %d, want 2", len(usage))
  78. }
  79. if usage[0][0] != "a" || usage[0][1] != "1" || usage[1][0] != "b" || usage[1][1] != "2" {
  80. t.Fatalf("KEY_COLUMN_USAGE = %v, want [[a 1] [b 2]]", usage)
  81. }
  82. }
  83. func TestCompositePrimaryKeyDDLRejections(t *testing.T) {
  84. c, client := newDataConnection(t)
  85. for _, sql := range []string{
  86. "CREATE TABLE t1 (a TEXT PRIMARY KEY, b TEXT PRIMARY KEY)",
  87. "CREATE TABLE t2 (a TEXT PRIMARY KEY, b TEXT, PRIMARY KEY (b))",
  88. "CREATE TABLE t3 (a TEXT, PRIMARY KEY (a, a))",
  89. "CREATE TABLE t4 (a TEXT, b TEXT GENERATED ALWAYS AS (a) STORED, PRIMARY KEY (b))",
  90. "CREATE TABLE t5 (a TEXT)",
  91. "ALTER TABLE t5 ADD COLUMN id INTEGER PRIMARY KEY",
  92. } {
  93. msgs := runQuery(t, c, client, &Message{Type: MsgQuery, Data: append([]byte(sql), 0)})
  94. if strings.HasPrefix(sql, "CREATE TABLE t5") {
  95. if code := errorCode(msgs[0]); code != "" {
  96. t.Fatalf("%s SQLSTATE = %q, want success", sql, code)
  97. }
  98. continue
  99. }
  100. if code := errorCode(msgs[0]); code != ErrCodeFeatureNotSupported {
  101. t.Fatalf("%s SQLSTATE = %q, want 0A000", sql, code)
  102. }
  103. }
  104. }
  105. // A key column that takes its value from a constant DEFAULT must resolve before
  106. // conflict matching, including over the wire.
  107. func TestCompositePrimaryKeyDefaultedKeyUpsertOverWire(t *testing.T) {
  108. c, client := newDataConnection(t)
  109. query := func(sql string) []*Message {
  110. return runQuery(t, c, client, &Message{Type: MsgQuery, Data: append([]byte(sql), 0)})
  111. }
  112. query("CREATE TABLE konst (a TEXT DEFAULT 'd', b TEXT DEFAULT 'e', v TEXT, PRIMARY KEY (a, b))")
  113. query("INSERT INTO konst (b, v) VALUES ('y', 'one')")
  114. if code := errorCode(query("INSERT INTO konst (b, v) VALUES ('y', 'two') ON CONFLICT (a, b) DO UPDATE SET v = excluded.v")[0]); code != "" {
  115. t.Fatalf("defaulted-key upsert SQLSTATE = %q, want success", code)
  116. }
  117. rows := query("SELECT v FROM konst WHERE a = 'd' AND b = 'y'")
  118. var dataRows []*Message
  119. for _, msg := range rows {
  120. if msg.Type == MsgDataRow {
  121. dataRows = append(dataRows, msg)
  122. }
  123. }
  124. if len(dataRows) != 1 {
  125. t.Fatalf("upsert read returned %d rows, want 1", len(dataRows))
  126. }
  127. if values := dataRowValues(t, dataRows[0]); len(values) != 1 || values[0] != "two" {
  128. t.Fatalf("upsert result = %v, want [two]", values)
  129. }
  130. // Assigning a key column is rejected, not applied as a ghost row.
  131. if code := errorCode(query("INSERT INTO konst (b, v) VALUES ('y', 'x') ON CONFLICT (a, b) DO UPDATE SET a = 'other'")[0]); code != ErrCodeFeatureNotSupported {
  132. t.Fatalf("key-assignment upsert SQLSTATE = %q, want 0A000", code)
  133. }
  134. }