I am running 2 create table queries and 1 insert query in a transaction.
I’m not getting a response from the transaction and show queries shows the RELEASE SAVEPOINT cockroach_restart query.
This is the transaction code I am using: https://pastebin.com/raw/20LNHuy4
And these are the queries being executed: https://pastebin.com/raw/xT3QqkAA
Is something wrong with the transaction code?
Oh and the queries are being sent individually.
With further investigation, this issue still occurs if I remove the insert query.
The issue does not occur if I send the two create table queries together and omit the insert query.
If I send all 3 queries together, the transaction does end but the insert query is not executed apparently.
Hi @pooper. Thanks for sharing the transaction code and queries. I’ll look for someone on this end who can help you work this out, but in the meantime, would you mind sharing the full code for executing the transaction you mentioned?
Also, have you looked through our code samples featuring transaction retry functions? The Node.js samples are here.
I’m unable to reproduce this problem. Can you create an issue with some information on how to reproduce the problem. I tried
root@:26257/test> BEGIN; SAVEPOINT cockroach_restart;
Now adding input for a multi-line SQL transaction client-side.
Press Enter two times to send the SQL text collected so far to the server, or Ctrl+C to cancel.
You can also use \show to display the statements entered so far.
-> BEGIN; SAVEPOINT retry_t; CREATE TABLE IF NOT EXISTS queues ( id STRING NOT NULL, round INT NOT NULL DEFAULT 0:::INT, start_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), end_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), timeout INTERVAL NOT NULL DEFAULT '10s':::INTERVAL, "interval" INTERVAL NOT NULL DEFAULT '5s':::INTERVAL, task_size INT NOT NULL DEFAULT 500:::INT, task_timeout INTERVAL NOT NULL DEFAULT '10s':::INTERVAL, task_bin_size INT NOT NULL DEFAULT 50:::INT, task_team_size INT NOT NULL DEFAULT 5:::INT, input_max_age INT NOT NULL DEFAULT 5:::INT, CONSTRAINT "primary" PRIMARY KEY (id ASC), FAMILY "primary" (id, round, start_time, end_time, timeout, "interval", task_size, task_timeout, task_bin_size, task_team_size, input_max_age) ); CREATE TABLE IF NOT EXISTS tasks ( id INT NOT NULL DEFAULT unique_rowid(), qu
->
SAVEPOINT
Time: 336.05µs
root@:26257/test OPEN>
root@:26257/test OPEN>
root@:26257/test OPEN>
root@:26257/test OPEN>
root@:26257/test OPEN>
root@:26257/test OPEN> ROLLBACK;
ROLLBACK
Time: 473.355µs
root@:26257/test> BEGIN; SAVEPOINT cockroach_restart;
Now adding input for a multi-line SQL transaction client-side.
Press Enter two times to send the SQL text collected so far to the server, or Ctrl+C to cancel.
You can also use \show to display the statements entered so far.
-> CREATE TABLE IF NOT EXISTS queues (
-> id STRING NOT NULL,
-> round INT NOT NULL DEFAULT 0:::INT,
-> start_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
-> end_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
-> timeout INTERVAL NOT NULL DEFAULT '10s':::INTERVAL,
-> "interval" INTERVAL NOT NULL DEFAULT '5s':::INTERVAL,
-> task_size INT NOT NULL DEFAULT 500:::INT,
-> task_timeout INTERVAL NOT NULL DEFAULT '10s':::INTERVAL,
-> task_bin_size INT NOT NULL DEFAULT 50:::INT,
-> task_team_size INT NOT NULL DEFAULT 5:::INT,
-> input_max_age INT NOT NULL DEFAULT 5:::INT,
-> CONSTRAINT "primary" PRIMARY KEY (id ASC),
-> FAMILY "primary" (id, round, start_time, end_time, timeout, "interval", task_size, task_timeout, task_bin_size, task_team_size, input_max_age)
-> );
-> CREATE TABLE IF NOT EXISTS tasks (
-> id INT NOT NULL DEFAULT unique_rowid(),
-> queue STRING NOT NULL,
-> round INT NOT NULL,
-> "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
-> worker STRING NOT NULL DEFAULT '':::STRING,
-> start_index INT NOT NULL,
-> end_index INT NOT NULL,
-> complete BOOL NOT NULL DEFAULT false,
-> bin_size INT NOT NULL DEFAULT 50:::INT,
-> team_size INT NOT NULL DEFAULT 5:::INT,
-> CONSTRAINT "primary" PRIMARY KEY (id ASC),
-> CONSTRAINT fk_queue FOREIGN KEY (queue) REFERENCES queues (id),
-> UNIQUE INDEX unique_tasks_idx (queue ASC, round ASC, start_index ASC, end_index ASC),
-> FAMILY "primary" (id, queue, round, "timestamp", worker, start_index, end_index, complete, bin_size, team_size)
-> );
-> INSERT INTO queues (id, round, start_time, end_time, timeout, "interval", task_size, task_timeout, task_team_size, input_max_age, task_bin_size) VALUES
-> ('default', 83090, '2017-11-24 15:19:54.638115+00:00', '2017-11-24 15:19:56.178586+00:00', '10s', '5s', 500, '10s', 5, 35, 50);
-> RELEASE SAVEPOINT cockroach_restart;
-> COMMIT;
COMMIT
Time: 51.157432ms
root@:26257/test>
root@:26257/test> show tables;
+--------+
| Table |
+--------+
| kv |
| kv2 |
| queues |
| tasks |
+--------+
(4 rows)
Time: 2.622336ms
root@:26257/test> select * from queues;
+---------+-------+----------------------------------+----------------------------------+---------+----------+-----------+--------------+---------------+----------------+---------------+
| id | round | start_time | end_time | timeout | interval | task_size | task_timeout | task_bin_size | task_team_size | input_max_age |
+---------+-------+----------------------------------+----------------------------------+---------+----------+-----------+--------------+---------------+----------------+---------------+
| default | 83090 | 2017-11-24 15:19:54.638115+00:00 | 2017-11-24 15:19:56.178586+00:00 | 10s | 5s | 500 | 10s | 50 | 5 | 35 |
+---------+-------+----------------------------------+----------------------------------+---------+----------+-----------+--------------+---------------+----------------+---------------+
(1 row)
Time: 6.845366ms
I tried this on a new database and had no issues.
Trying it on my existing database, I get this error:
root@:26257/victoria> BEGIN; SAVEPOINT cockroach_restart;
Now adding input for a multi-line SQL transaction client-side.
Press Enter two times to send the SQL text collected so far to the server, or Ctrl+C to cancel.
You can also use \show to display the statements entered so far.
-> CREATE TABLE IF NOT EXISTS queues (
-> id STRING NOT NULL,
-> round INT NOT NULL DEFAULT 0:::INT,
-> start_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
-> end_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
-> timeout INTERVAL NOT NULL DEFAULT '10s':::INTERVAL,
-> "interval" INTERVAL NOT NULL DEFAULT '5s':::INTERVAL,
-> task_size INT NOT NULL DEFAULT 500:::INT,
-> task_timeout INTERVAL NOT NULL DEFAULT '10s':::INTERVAL,
-> task_bin_size INT NOT NULL DEFAULT 50:::INT,
-> task_team_size INT NOT NULL DEFAULT 5:::INT,
-> input_max_age INT NOT NULL DEFAULT 5:::INT,
-> CONSTRAINT "primary" PRIMARY KEY (id ASC),
-> FAMILY "primary" (id, round, start_time, end_time, timeout, "interval", task_size, task_timeout, task_bin_size, task_team_size, input_max_age)
-> );
-> CREATE TABLE IF NOT EXISTS tasks (
-> id INT NOT NULL DEFAULT unique_rowid(),
-> queue STRING NOT NULL,
-> round INT NOT NULL,
-> "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
-> worker STRING NOT NULL DEFAULT '':::STRING,
-> start_index INT NOT NULL,
-> end_index INT NOT NULL,
-> complete BOOL NOT NULL DEFAULT false,
-> bin_size INT NOT NULL DEFAULT 50:::INT,
-> team_size INT NOT NULL DEFAULT 5:::INT,
-> CONSTRAINT "primary" PRIMARY KEY (id ASC),
-> CONSTRAINT fk_queue FOREIGN KEY (queue) REFERENCES queues (id),
-> UNIQUE INDEX unique_tasks_idx (queue ASC, round ASC, start_index ASC, end_index ASC),
-> FAMILY "primary" (id, queue, round, "timestamp", worker, start_index, end_index, complete, bin_size, team_size)
-> );
-> INSERT INTO queues (id, round, start_time, end_time, timeout, "interval", task_size, task_timeout, task_team_size, input_max_age, task_bin_size) VALUES
-> ('default', 83090, '2017-11-24 15:19:54.638115+00:00', '2017-11-24 15:19:56.178586+00:00', '10s', '5s', 500, '10s', 5, 35, 50);
->
pq: SAVEPOINT COCKROACH_RESTART needs to be the first statement in a transaction
root@:26257 ERROR> rollback;
It makes no difference if I send BEGIN and SAVEPOINT COCKROACH_RESTART together or separately. It still errors saying it expects SAVEPOINT COCKROACH_RESTART first.
If I drop the database and recreate it with the same name, the error still occurs.
If I create a database with a different name, the error does not occur.
I can give access to the cockroachdb instance if you want to try it or debug it.
Sending the queries in the transaction individually doesn’t give an error but gets stuck on releasing the savepoint, which was my original problem:
root@:26257/victoria> begin;
Now adding input for a multi-line SQL transaction client-side.
Press Enter two times to send the SQL text collected so far to the server, or Ctrl+C to cancel.
You can also use \show to display the statements entered so far.
-> savepoint cockroach_restart;
-> CREATE TABLE IF NOT EXISTS queues (
-> id STRING NOT NULL,
-> round INT NOT NULL DEFAULT 0:::INT,
-> start_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
-> end_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
-> timeout INTERVAL NOT NULL DEFAULT '10s':::INTERVAL,
-> "interval" INTERVAL NOT NULL DEFAULT '5s':::INTERVAL,
-> task_size INT NOT NULL DEFAULT 500:::INT,
-> task_timeout INTERVAL NOT NULL DEFAULT '10s':::INTERVAL,
-> task_bin_size INT NOT NULL DEFAULT 50:::INT,
-> task_team_size INT NOT NULL DEFAULT 5:::INT,
-> input_max_age INT NOT NULL DEFAULT 5:::INT,
-> CONSTRAINT "primary" PRIMARY KEY (id ASC),
-> FAMILY "primary" (id, round, start_time, end_time, timeout, "interval", task_size, task_timeout, task_bin_size, task_team_size, input_max_age)
-> );
->
CREATE TABLE
Time: 206.840541ms
root@:26257/victoria OPEN> CREATE TABLE IF NOT EXISTS tasks (
-> id INT NOT NULL DEFAULT unique_rowid(),
-> queue STRING NOT NULL,
-> round INT NOT NULL,
-> "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
-> worker STRING NOT NULL DEFAULT '':::STRING,
-> start_index INT NOT NULL,
-> end_index INT NOT NULL,
-> complete BOOL NOT NULL DEFAULT false,
-> bin_size INT NOT NULL DEFAULT 50:::INT,
-> team_size INT NOT NULL DEFAULT 5:::INT,
-> CONSTRAINT "primary" PRIMARY KEY (id ASC),
-> CONSTRAINT fk_queue FOREIGN KEY (queue) REFERENCES queues (id),
-> UNIQUE INDEX unique_tasks_idx (queue ASC, round ASC, start_index ASC, end_index ASC),
-> FAMILY "primary" (id, queue, round, "timestamp", worker, start_index, end_index, complete, bin_size, team_size)
-> );
CREATE TABLE
Time: 565.483262ms
root@:26257/victoria OPEN> INSERT INTO queues (id, round, start_time, end_time, timeout, "interval", task_size, task_timeout, task_team_size, input_max_age, task_bin_size) VALUES
-> ('default', 83090, '2017-11-24 15:19:54.638115+00:00', '2017-11-24 15:19:56.178586+00:00', '10s', '5s', 500, '10s', 5, 35, 50) ON CONFLICT (id) DO NOTHING;
INSERT 1
Time: 45.664096ms
root@:26257/victoria OPEN> release savepoint cockroach_restart;
- no response here, just stuck
Once it’s stuck, the database stops responding to queries. Even cockroach sql doesn’t respond.